As you probably know, pg_timetable is the advanced PostgreSQL cron compatible scheduler already released!
Usually, any major release introduces absolutely new functionality as well as some backward incompatibilities. However, the only backward incompatibility present in this release is that the base task
SHELL changed its name to
PROGRAM. Yes, that’s all. 🙂 Now, let’s check what great features we have here. You may examine the official release page for a complete changelog with links to issues, commits, and pull requests.
Major release? Again?
If you remember, we released version 2 this spring. And now, after just half a year, we publish the next major version? But why so soon?
Well, as I have said before:
– Major versions usually change the internal format of system tables and data representation.
This time we’ve changed the core library and introduced some excellent game-changing functionality! 😱
Switch from lib/pq to jackc/pgx Golang library
Usually, it takes something fundamental to provoke developers to change the core library of a program.
I think this quote
I’m the lib/pq maintainer and I recommend using pgx. It also totally doesn’t matter they are all fine
from the lib/pq maintainer may be treated as a fundamental something.😉
All jokes aside — in reality, we need a library with PostgreSQL-specific features to replace the standard
database/sql package for one of two reasons: either the standard package does not allow the features we need, or it makes the implementation of existing features look like aerial gymnastics, e.g.:
- PostgreSQL native types,
- statement preparing and caching,
- connection pool with an after-connect hook,
- COPY native protocol,
- extendable logging, etc.
The next logical step in this area will be to switch from the heavily used jmoiron/sqlx library to georgysavva/scany. Don’t get me wrong,
sqlx is excellent software, but it’s intended for
database/sql. Thus it requires one more layer of abstraction. The switch is planned, but not yet implemented.
Reimplement session locking
As you know, it’s possible to run as many pg_timetable instances as you want against a single database instance. It is also possible to provide concurrency protection, meaning there can be no more than one scheduler working on a chain simultaneously, and a user provides the
clientname command-line option. Consequently, only one process of pg_timetable can be connected to the database with a particular
In the previous version, PostgreSQL advisory lock machinery held session locking information.
That was a tricky solution, because the
DB class from the
database/sql package is a database handle representing a pool of zero or more underlying connections. So acquiring a lock in one connection doesn’t guarantee that this particular connection cannot be closed, or that some additional underlying connection won’t be opened.
At this point, we understand that we should operate not on the level of a database session but a pg_timetable process level. In other words, each client may open several connections at once – but there should never be two pg_timetable processes with the same
clientname operating against the same database.
To achieve this, we added the special system table:
CREATE UNLOGGED TABLE timetable.active_session( client_pid BIGINT NOT NULL, client_name TEXT NOT NULL, server_pid BIGINT NOT NULL );
And now, it can be used for monitoring purposes. You can get a list of active schedulers with a query:
timetable=# SELECT * FROM timetable.active_session; client_pid | client_name | server_pid ------------+-------------+------------ 22420 | worker01 | 16548 22420 | worker01 | 16548 22420 | worker01 | 10740 (3 rows)
In this example, we see only one client with the name
worker01 and two sessions. Two first identical rows mean that the server process with
PID 16548 was the initial. This behavior may change in the future.
timetable=# SELECT * FROM timetable.active_session; client_pid | client_name | server_pid ------------+-------------+------------ 21312 | worker01 | 12428 21312 | worker01 | 12428 21312 | worker01 | 11368 16404 | loader | 9948 16404 | loader | 9948 16404 | loader | 6632 16404 | loader | 3532 16404 | loader | 9940 (8 rows)
And here we can see two clients
loader are chewing their tasks. For further details we can examine
timetable.log, timetable.execution_log and
This was the first in a series of posts dedicated to the new pg_timetable v3 features. Stay tuned for the coolest features to be highlighted:
- Asynchronous chains!
- Debug mode for developing!
- Exclusive execution mode for chains!
Stay safe, healthy, and wealthy!
Be happy! Peace! Love! ❤