How to make PREPARE TRANSACTION work

Question

According to Postgres documentation. After it has been prepared, the transaction can later be completed or rolled back with PREPARED PREPARATIONS or PREPARED ROLLB, respectively. These 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 use

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

all this is done in a shell script. Now the problem is that I execute the psql and pass this command as a parameter via the -c option (I start the transaction with the command

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

I want to create a Savepoint and roll back any errors to it.

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

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

Reported as " No "transaction-id" found "

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

Is this because I issue the psql several times, and each of them leads to a new transaction?

Answer 1

To get started, the COPY and PREPARE tags must be in the same session. Since your question does not contain specific commands, I assume that when you write:

Confirmed rollback "transaction identifier" (in a separate psql command with sql statements)

You use different psql commands for COPY and PREPARE. This is not true. Combine COPY and PREPARE with the same session.

eg.

 $ 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 

PREPARE TRANSACTION works by writing the current transaction to disk and exiting the transaction process in the current session. That's why you need BEGIN : it starts the transaction you want to prepare. All the commands you want to influence should appear after the transaction has been started (in your case, the COPY command). When PREPARE TRANSACTION is issued, the transaction you are currently in is written to the disk with the identifier you specified. Any statements made after the transaction is prepared are no longer part of the transaction. So BEGIN; PREPARE... ; COPY BEGIN; PREPARE... ; COPY BEGIN; PREPARE... ; COPY starts a BEGIN; PREPARE... ; COPY operation without a transaction.

Here is 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 include 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 identifier was not found, but does not warn you of a disabled function. Psql gives a warning for PREPARE TRANSACTION , but it is easy to skip if your shell scripts print the material after the preparation statement.

Answer 2

PREPARE TRANSACTION designed for distributed transactions on multiple servers, commonly used by transaction monitors or similar application servers (for example, EJB).

Just copy your copy to a regular transaction block:

 START TRANSACTION; COPY ....; COMMIT; 

If you want to save a point in the middle, use SAVEPOINT some_name , and then you can rollback to that save point.