A function in our application (should be killed) allows operators to change session parameters on a running connection. Since the connection is usually always in production It is very busy, so the “SET SESSION bla-bla” query will always crash the pgsql_connection process.
Before the crash, pgsql_connection sends “terminate” to the backend through pgsql_sock (wrapper of tcp socket) (‘X’) signal. At the same time, another erlang process (let’s call it “worker”) is waiting for a response from the postgres backend using the same socket.
The problem now is: receiving a client After the “terminate” signal of the end, even if “OK” has been sent in the “COMMIT” statement, can the back end cancel the last transaction?
Because if possible, the staff will have the opportunity to report the successfully written transaction to the main application process, and the transaction has indeed been cancelled.
Or, where can I read about More details on this? The document says (http://www.postgresql.org/docs/9.2/static/protocol-flow.html):
For either normal or abnormal termination, any open transaction is
rolled back, not committed. One should note however that if a frontend
disconnects while a non-SELECT query is being processed, the backend
will probably finish the query before noticing the disconnection. If
the query is outside any transaction block (BEGIN… COMMIT sequence)
then its results might be committed before the disconnection is
recognized.
– not a clear Declaration.
Now the question: is it possible that upon receiving a “Terminate” signal from a client, backend can cancel last transaction even if it has sent an “OK” on “COMMIT” statement already?
Fundamentally impossible. If it has already Submit, then it has been submitted, and there is no turning back. This is the meaning of “submit”.
The only time that Pg may return success before the submission hits the disk is persistent, if you set synchronous_commit = off Come tell it.
Such as If you find that any different situation occurs, it is most likely because you are trying to share a single connection between multiple processes (when you establish a connection before fork()) without proper locking or other mutual exclusion to ensure that the connection is locked A command is on the fly.
Please note that, and vice versa, this may be what you are considering in the quoted document paragraph. If the client disappears after issuing the commit command (crash, loss of connection, etc. ), you can commit the transaction without returning a successful OK to the client.
What the application is doing, where it sends out-of-sync messages on the wire protocol is completely destroyed. It guarantees that it will Causes unpredictable problems. The protocol is a bit robust, so you are unlikely to get something like an accidental submission, but you are likely to interrupt the transaction or suddenly disconnect the entire session.
If you need to be able to rollback /Abort the committed transaction, then there will be a problem with your application design. When you say COMMIT, you are not ready to commit. If the application process crashes or the entire server is committing the transaction Pg and whatever you need to do If you crash between things, you will encounter the same problem.
If you cannot fix the application design to avoid this, then you will have to use a two-phase transaction, directly use PREPARE TRANSACTION and then COMMIT PREPARED , Or indirectly through the XA API. This will incur a lot of cost in terms of performance and management overhead, but if you need to complete special work after the database is submitted but before “real completion”, this is the only option.
The document you quoted is discussing the case where the application sends a COMMIT but disconnects before the receiving backend confirms the submission. Because TCP/IP is buffered, there is no guarantee that the COMMIT will be flushed to Pg. If you do, then There is no guarantee that it will not be accompanied by a RST that terminates the connection. Therefore, in this specific case, it is somewhat uncertain whether the transaction will be submitted. This is a problem application needs to have a way to check whether the last one was submitted when resuming work Unit of work, or if it can’t use two-phase transactions. The documentation you quoted doesn’t mention canceling the commit after completion, because you can’t. Forever.
Assuming that the application must perform some kind of additional Work, such as moving files or sending emails or working on another data store, then you may need a two-phase transaction. Even then, unless all parties in a distributed transaction support two-phase commit, you are vulnerable to attack , Because your “other bits” can be completed, then your worker or server may send to the database to complete the second stage submission before the confirmation is completed.
You can keep your own two in the DB Submit the log in stages, instead of using the real 2PC:
>Whether the main database is working properly and the records are written to the work As a log sheet, it says “I have completed the work in the database, and I am about to complete the next part of the work”.
>do the next part; and
>update the work log to indicate that the next part has been completed.
…but this has the same problem, the crash between part 2 and part 3 caused the app to forget that it executed part 2 and repeat it at startup. If you can’t stand this situation, you need Find a way to make the Part 2 submission complete verifiable, so you can tell if it has been completed, or find a way to make it possible for a two-phase submission.
To learn about this topic For more information, please read about XA, distributed transactions, two-phase commit, etc.
We run postgres server v9.2.8 and use epgsql (erlang) as Client library. In some cases, we produce in the production environment but cannot reproduce it in the development environment, and we are losing data.
A function in our application (should Killed) allows operators to change session parameters on a running connection. Since the connection is usually always busy in production, the “SET SESSION bla-bla” query will always crash the pgsql_connection process.
Before the crash, pgsql_connection sends a “terminate” (‘X’) signal to the backend through pgsql_sock (wrapper for tcp socket). At the same time another erlang process (let’s call it “worker”) is waiting for postgres The end uses the same socket response.
The problem now is: after receiving the “terminate” signal from the client, even if “OK” has been sent in the “COMMIT” statement, the backend can cancel Is the last transaction?
Because if possible, the staff will have the opportunity to report the successfully written transaction to the main application process, and the transaction has indeed been cancelled.
Or, where can I read about More details on this? The document says (http://www.postgresql.org/docs/9.2/static/protocol-flow.html):
For either normal or abnormal termination, any open transaction is
rolled back, not committed. One should note however that if a frontend
disconnects while a non-SELECT query is being processed, the backend
will probably finish the query before noticing the disconnection. If
the query is outside any transaction block (BEGIN… COMMIT sequence)
then its results might be committed before the disconnection is
recognized.
– not a clear Declaration.
Now the question: is it possible that upon receiving a “Terminate” signal from a client, backend can cancel last transaction even if it has sent an “OK” on “COMMIT” statement already?
It is fundamentally impossible. If it has been committed, then it has been committed and there is no turning back. This It means “submit”.
The only time that Pg may return success before the commit hits the disk is persistent, if you tell it by setting synchronous_commit = off.
If You find that any different situation occurs, then it is most likely because you are trying to share a single connection between multiple processes (when you establish a connection before fork()) and it is not correct Locking or other mutual exclusion to ensure that a command is in flight when the connection is locked.
Please note that, and vice versa, this may be what you are considering in the quoted document paragraph. If the client is in After issuing the commit command and disappearing (crash, loss of connection, etc.), you can submit the transaction without returning a successful OK to the client.
What the application is doing, it sends out of synchronization on the wire protocol The place of the message is completely broken. It is guaranteed to cause unpredictable problems. The protocol is a bit robust, so you are unlikely to get something like an accidental submission, but you are likely to interrupt the transaction or suddenly disconnect the entire session.
If you need to be able to roll back/abort the committed transaction, then your application design will have a problem. When you say COMMIT, you are not ready to commit. If the application process crashes or the entire server If you crash between the Pg that commits the transaction and whatever you need to do, you will have the same problem.
If you cannot fix the application design to avoid this, then you will have to use Two-phase transaction, directly use PREPARE TRANSACTION and then COMMIT PREPARED, or indirectly through XA API. This will incur a lot of cost in terms of performance and management overhead, but if you need to complete special work after the database is submitted but before “real completion” , This is the only option.
The document you quoted is discussing the case where the application sends a COMMIT but disconnects before the receiving backend confirms the submission. Because TCP/IP is buffered, COMMIT cannot be guaranteed Is flushed to Pg, if you do, there is no guarantee that it will not be accompanied by the RST that terminates the connection. Therefore, in this particular case, it is somewhat uncertain whether the transaction will be submitted. This is a problem for applications that need to have a method To check if the last unit of work was committed when resuming work, or if it cannot use a two-phase transaction. The document you quoted does not mention canceling the commit after completion, because you can’t. Forever.
Assuming The application must perform some kind of additional work after submission, such as moving files or sending emails or working on another data store, then you may need a two-phase transaction. Even then, unless all parties in the distributed transaction Supports two-phase submission, otherwise you are vulnerable to attack, because your “other bits” can be completed, then your worker or server may send to the database to complete the second stage of the submission before the confirmation is completed.
< p>You can keep your own two-phase commit log in the DB instead of using the real 2PC:
>Whether the main database is working properly and write the records to the work log table, which says “I have Complete the work in the database, I am about to complete the next part of the work”.
>do the next part; and
>update the work log to indicate that the next part has been completed .
…but this has the same problem, the crash between part 2 and part 3 caused the app to forget that it executed part 2 and repeat it at startup. If you can’t stand this Circumstances, you need to find a way to make the Part 2 submission complete and verifiable, so that you can judge whether it has been completed, or find a way to enable it to make a two-stage submission.
To understand For more information on this topic, please read about XA, distributed transactions, two-phase commit, etc.