Long running idle transactions from executeChain

[fxtmkelly]

Hello,

In our current setup we have mostly tasks configured are of kind PROGRAM. Those tasks launch python scripts which connect to the DB and manage their own connections and transactions. They run for hours at a time archiving table partitions to AWS S3.

An issue we're having is that inside the executeChain logic, it calls begin transaction and that transaction remains open and idle for the duration of the chain/task. The last executed query on that transaction is SELECT txid_current() which seems to indicate it hasn't executed anything since calling begin.

These long transactions from pg_timetable are causing all the typical issues you'd see with long running transactions. I'm curious if anyone else has run into this or found a way to work around it? Based on the logic, it seems you should be able to avoid a transaction all together on tasks of kind PROGRAM or move the transaction boundary only around tasks with kind SQL or BUILTIN.

[pashagolub]

Hello!

The chain executed in transaction, yes. This is by design. Can you, please, provide a chain definition for investigation?

[fxtmkelly]

No problem, here's the SQL and JSON representation of the chain, task, and parameters:

INSERT INTO timetable.chain (chain_id, chain_name, run_at, max_instances, timeout, live, self_destruct, exclusive_execution, client_name, on_error) VALUES (5, 'archive_partitions', '40 0 1/1 * *', null, 0, true, false, false, null, null);
INSERT INTO timetable.task (task_id, chain_id, task_order, task_name, kind, command, run_as, database_connection, ignore_error, autonomous, timeout) VALUES (5, 5, 1, 'archive_partitions_script', 'PROGRAM', 'python3', null, null, true, false, 0);
INSERT INTO timetable.parameter (task_id, order_id, value) VALUES (5, 1, '["/scripts/python/archive_partitions.py", "-c", "/home/pg_timetable/.pyenv"]');

{
    "name": "archive_partitions",
    "run_at": "40 0 1/1 * *",
    "live": true,
    "tasks": [
      {
        "name": "archive_partitions_script",
        "order": 1,
        "kind": "PROGRAM",
        "command": "python3",
        "ignore_error": true,
        "parameters": [
          {
            "order": 1,
            "value": [
              "/scripts/python/archive_partitions.py",
              "-c",
              "/home/pg_timetable/.pyenv"
            ]
          }
        ]
      }
    ]
  }

The /scripts/python/archive_partitions.py Python script manages it's own DB connections and transactions.

This is the pg_stat_activity output for the pg_timetable session for that chain. It sits idle in transaction for the duration of the task with the last executed query being SELECT txid_current().

pid	user	query_start	query_time	xact_time	pid-2	usename	application_name	client_addr	client_port	xact_start	query_start-2	state_change	wait_event_type	wait_event	state	backend_xid	backend_xmin	query
68635	redacted	2025-01-24 00:40:04.259272+00	00:40:11.634585	00:40:11.636468	68635	redacted	pg_timetable	redacted	2061	2025-01-24 00:40:04.257389+00	2025-01-24 00:40:04.259272+00	2025-01-24 00:40:04.259279+00	Client	ClientRead	idle in transaction	1020378180	1020378170	SELECT txid_current()

As far as I can tell, the last query in that idle_in_transaction session is from

pg_timetable/internal/pgengine/transaction.go

Lines 18 to 24 in 7e2460d

	func (pge *PgEngine) StartTransaction(ctx context.Context) (tx pgx.Tx, txid int64, err error) {
	if tx, err = pge.ConfigDb.Begin(ctx); err != nil {
	return
	}
	err = tx.QueryRow(ctx, "SELECT txid_current()").Scan(&txid)
	return
	}

Let me know if any additional information would be useful.

Thanks!

[pashagolub]

Would you please try #675 if it works for you?

[fxtmkelly]

Thanks for the quick turn around, I'm testing those changes now.

[fxtmkelly]

As far as I can tell from testing, your changes resolved the issues we were having with xmin and vacuum from that long running transaction. 🙌

[pashagolub]

Thanks a lot for pointing this out!

[fxtmkelly]

No problem, thank you for the quick fix. It’s much appreciated!

[pashagolub]

Addressed in #675