As you probably know, pg_timetable is the advanced PostgreSQL cron compatible scheduler already released!
Table of Contents
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.
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! 😱
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.:
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.
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 clientname.
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:
|
1 2 3 4 5 |
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:
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 worker01 and loader are chewing their tasks. For further details we can examine timetable.log, timetable.execution_log and timetable.run_status tables.
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:
Stay safe, healthy, and wealthy!
Be happy! Peace! Love! ❤
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply