pg_timetable v3 is out!

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,
• LISTEN/NOTIFY,
• 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 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:

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 worker01 and loader are chewing their tasks. For further details we can examine timetable.log, timetable.execution_log and timetable.run_status tables.

Finally

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! ❤