public inbox for [email protected]
help / color / mirror / Atom feedProper way to keep count of statements executed within current transaction
2+ messages / 2 participants
[nested] [flat]
* Proper way to keep count of statements executed within current transaction
@ 2019-05-24 20:49 Podrigal, Aron <[email protected]>
2019-05-24 20:56 ` Re: Proper way to keep count of statements executed within current transaction Christophe Pettus <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Podrigal, Aron @ 2019-05-24 20:49 UTC (permalink / raw)
To: psycopg
Hi
I've seen many libraries using psycopg2 wrapping execution of statements
and in case of connection failure, it reconnects and submits the query
again.
Doing so in every case can lead to unexpected results when the connection
was reset after the first statement within a transaction.
Consider the following:
BEGIN; -- or autocommit = False
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- User code does some processing with the returned value.
-- At this point the the server was restarted and connection is closed
-- The users code submits
UPDATE accounts SET balance = x WHERE id = 1;
-- The UPDATE query fails due to lost connection, so the library code which
wraps the statement reconnects and submits the query again.
My question is, what is the correct way to detect whether any statements
were executed successfully prior to the connection being lost. And decide
whether it is safe to automatically reconnect and resubmit the query ?
--
-
Aron Podrigal
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Proper way to keep count of statements executed within current transaction
2019-05-24 20:49 Proper way to keep count of statements executed within current transaction Podrigal, Aron <[email protected]>
@ 2019-05-24 20:56 ` Christophe Pettus <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Christophe Pettus @ 2019-05-24 20:56 UTC (permalink / raw)
To: Podrigal, Aron <[email protected]>; +Cc: psycopg
> On May 24, 2019, at 13:49, Podrigal, Aron <[email protected]> wrote:
>
> My question is, what is the correct way to detect whether any statements were executed successfully prior to the connection being lost.
You can't. This is exactly why you have transactions: In the case of a lost connection, you can assume the transaction was aborted, and retry the transaction from the start.
--
-- Christophe Pettus
[email protected]
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2019-05-24 20:56 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-24 20:49 Proper way to keep count of statements executed within current transaction Podrigal, Aron <[email protected]>
2019-05-24 20:56 ` Christophe Pettus <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox