agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedwhen transaction is committed in postgres
2+ messages / 2 participants
[nested] [flat]
* when transaction is committed in postgres
@ 2018-03-29 06:46 abctevez <swqshapu@163.com>
2018-03-29 11:29 ` Re: when transaction is committed in postgres Samed YILDIRIM <samed@reddoc.net>
0 siblings, 1 reply; 2+ messages in thread
From: abctevez @ 2018-03-29 06:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: when transaction is committed in postgres
2018-03-29 06:46 when transaction is committed in postgres abctevez <swqshapu@163.com>
@ 2018-03-29 11:29 ` Samed YILDIRIM <samed@reddoc.net>
0 siblings, 0 replies; 2+ messages in thread
From: Samed YILDIRIM @ 2018-03-29 11:29 UTC (permalink / raw)
To: abctevez <swqshapu@163.com>; pgsql-sql
<div>Hi Richard,</div><div> </div><div>There are more than one option you can use.</div><div> </div><div><strong>1. Two-phase commit</strong></div><div>You can prepare your transactions with unique id before commit.</div><div>- 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).</div><div>- 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).</div><div> </div><div><a href="https://www.postgresql.org/docs/10/static/sql-prepare-transaction.html">https://www.postgresq...; <strong>Track commit timestamp</strong></div><div>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.</div><div> </div><div><div><a href="https://www.postgresql.org/docs/10/static/functions-info.html">https://www.postgresql.org/doc...; <strong>txid_status() function</strong></div><div>If you are using PostgreSQL 10, you can check your transaction status (committed, aborted or in progress) directly with transaction id.</div><div> </div><div><a href="https://www.postgresql.org/docs/10/static/functions-info.html">https://www.postgresql.org/doc... regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div>29.03.2018, 09:59, "abctevez" <swqshapu@163.com>:</div><blockquote type="cite"><div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial;"><div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial;"><div> Hello,I read the introduction of transaction on the postgresql official website. It notes that transaction is committed when commit call.</div><div> 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</div><div> </div><div>Thanks advances</div><div>Richard</div></div> <p><span title="neteasefooter"> </span></p></div> <p><span title="neteasefooter"> </span></p></blockquote>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-03-29 11:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-29 06:46 when transaction is committed in postgres abctevez <swqshapu@163.com>
2018-03-29 11:29 ` Samed YILDIRIM <samed@reddoc.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox