How to make PREPARE TRANSACTION work

Question

As per Postgres Documentation - Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED or ROLLBACK PREPARED, respectively. Those commands can be issued from any session, not only the one that executed the original transaction.

I am trying to import data from csv into database tables and for this, I am using the

COPY tablename [ ( column [, ...] ) ] FROM { 'filename' }

all this is done in a shell script. Now the issue is that I am executing psql command and passing this command as parameter via the -c option ( I start transaction via the command

prepare transaction 'some-id' in that command).

I want to create a Savepoint and rollback to it incase of any errors.

After a few other tasks in the shell script, I check for errors that the previous psql statement have produced and when I then try to rollback using the command

Prepared Rollback 'transaction-id' ( in separate psql command with sql statements )

It reports "No "transaction-id" found"

Am I getting the concept wrong or missing something in the process?

Is this happening because I am issuing psql command multiple time and each is resulting in new transaction ?

Answer 1

For your prepare to work, the COPY and PREPARE must be in the same session. Since your question lacks concrete commands, I'm assuming that when you write:

Prepared Rollback 'transaction-id' ( in separate psql command with sql statements )

You're using different psql commands to COPY and PREPARE. This is wrong. Combine the COPY and PREPARE to the same session.

E.g.

$ psql -c "BEGIN; COPY tablename FROM '/tmp/sql'; PREPARE TRANSACTION 'foobar';" db
$ while /bin/not-ready-to-commit ; do sleep 1 ; done
$ psql -c "COMMIT PREPARED 'foobar';" db

The PREPARE TRANSACTION works by writing the current transaction to the disc and exiting the transaction process in the current session. This is why you need a BEGIN: it starts the transaction you want to prepare. All commands you want to be affected by the prepeare must come after the transaction has been started (in your case the COPY command). When the PREPARE TRANSACTION is issued, the transaction you are currently in is written to disk with the identifier you give. Any statements issued after the transaction is prepared are no longer part of the transaction. So doing BEGIN; PREPARE... ; COPY runs the COPY operation without a transaction.

Here's an example in psql shell:

demo=# DELETE FROM foo;
DELETE 4
demo=# BEGIN; -- start a transaction
BEGIN
DEMO=# COPY foo FROM '/tmp/sql'; -- do what you want to commit later
COPY 4
demo=# PREPARE TRANSACTION 'demo'; -- prepare the transaction
PREPARE TRANSACTION
demo=# ROLLBACK; -- this is just to show that there is no longer a transaction
NOTICE:  there is no transaction in progress
ROLLBACK
demo=# SELECT * FROM foo; -- the table is empty, copy waiting for commit
 a | b 
---+---
(0 rows)
demo=# COMMIT PREPARED 'demo'; -- do the commit
COMMIT PREPARED
demo=# SELECT * FROM foo; -- data is visible
 a | b 
---+---
 1 | 2
 3 | 4
 5 | 6
 7 | 8
(4 rows)

Edit: You must enable prepared transactions in postgresql.conf:

max_prepared_transactions = 1 # or more, zero (default) disables this feature.

If max_prepared_transactions is zero, psql reports that the transaction id is not found, but does not warn you about this feature being disabled. Psql gives a warning for PREPARE TRANSACTION but it's easy to miss if your shell scripts print stuff after the prepare statement.