Hi Richard,
There are more than one option you can use.
1. Two-phase commit
You can prepare your transactions with unique id before commit.
- If the connection between client and postgres is lost at preparation phase (PREPARE TRANSACTION), you can control it with pg_prepared_xacts system view whether it can be committed. If you can see your transaction on the system view, you can commit the transaction (COMMIT PREPARED).
- If the connection between client and postgres is lost at commit phase of the prepared transaction, you can control it with using pg_prepared_xacts system view again :) If you can see your transaction id, it is waiting for commit. If you do not see, It is committed (of course if you don't execute ROLLBACK PREPARED command).
2. Track commit timestamp
Enable it (track_commit_timestamp = on) in postgresql.conf first. In your transaction, get your transaction id by using txid_current() function. If the connection between client and postgres is lost, you can check your transaction is committed or not by using pg_xact_commit_timestamp() function. If it returns null, It your transaction is not committed. If returns a timestamp, your transaction is committed.
3. txid_status() function
If you are using PostgreSQL 10, you can check your transaction status (committed, aborted or in progress) directly with transaction id.
Best regards.
Samed YILDIRIM
29.03.2018, 09:59, "abctevez" <swqshapu@163.com>:
Hello,I read the introduction of transaction on the postgresql official website. It notes that transaction is committed when commit call.
Suppose that , I execute begin、execute command and then commit using some postgres client such as jdbc or odbc, what happens if the postgresql server receive commit command and response commit message failure,like tcp shakehand?If it is still to be committed, the client has no way to know whether the transaction is committed successfully or not.How does postgresql handle such situation
Thanks advances
Richard