Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
Dropping the BEGIN has cleared up the issue. Thank you. On 4/3/2012 9:50 AM, John R Pierce wrote: On 04/03/12 9:40 AM, Tom Lane wrote: Alex Matzinger writes: > The connection that is executing the SELECT 1 are generally open for 1-5 > hours before they are killed. The specific connection only executes > SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no > other query is executed. Lose the "BEGIN" and it will probably work more nicely. indeed,a 1-5 hour long transaction means VACUUM can't clean up anything newer than the oldest active transaction, and thats not per database, thats cluster-wide. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
On 04/03/12 9:40 AM, Tom Lane wrote: Alex Matzinger writes: > The connection that is executing the SELECT 1 are generally open for 1-5 > hours before they are killed. The specific connection only executes > SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no > other query is executed. Lose the "BEGIN" and it will probably work more nicely. indeed,a 1-5 hour long transaction means VACUUM can't clean up anything newer than the oldest active transaction, and thats not per database, thats cluster-wide. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
Alex Matzinger wrote: > The connection that is executing the SELECT 1 are generally open > for 1-5 hours before they are killed. The specific connection > only executes SELECT 1. The transaction is simply BEGIN, and then > SELECT 1's, no other query is executed. Don't do that. In particular, never put more into a single database transaction than is required for integrity; and there is no apparent reason why running a series of "SELECT 1" statements needs to be in a single transaction. (It's not blindingly obvious why you would want to do it in general, but I can imagine it possibly being useful for monitoring purposes.) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
Alex Matzinger writes: > The connection that is executing the SELECT 1 are generally open for 1-5 > hours before they are killed. The specific connection only executes > SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no > other query is executed. Lose the "BEGIN" and it will probably work more nicely. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
The connection that is executing the SELECT 1 are generally open for 1-5 hours before they are killed. The specific connection only executes SELECT 1. The transaction is simply BEGIN, and then SELECT 1's, no other query is executed. The updates we make to the primary database are quiet large, usually several megabytes. Alex On 4/2/2012 11:49 PM, Heikki Linnakangas wrote: On 03.04.2012 02:23, amatzin...@experts-exchange.com wrote: On a hot standby database, while the primary is being updated, Postgres will randomly kill a process which is performing a "Select 1" command. The error is this: 2012-04-02 13:36:13.269 PDT,"smxuser","smxprd1",39523,"127.0.0.1:57893",4f79ffad.9a63,1,"",2012-04-02 12:36:13 PDT,3/32,0,FATAL,40001,"terminating connection due to conflict with recovery","User query might have needed to see row versions that must be removed.","In a moment you should be able to reconnect to the database and repeat your command.",,,"" We have 5 hot standby's set up, which all preform this SELECT 1, and postgres kills them across all standby's. There should never be a situation that SELECT 1 is in conflict with data, as it it never using any table in the database. The system doesn't make a difference between queries like "SELECT 1" that don't access any tables, and those that do. Even if "SELECT 1" doesn't access any tables, a subsequent statement in the same transaction might. I'm assuming that those "SELECT 1"s were issued in transactions that had been open for a long time, because you shouldn't get recovery conflicts with very short transactions, in practice anyway. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6571: Postgres Kills 'Select 1' query randomly on hot standby databases
On 03.04.2012 02:23, amatzin...@experts-exchange.com wrote: On a hot standby database, while the primary is being updated, Postgres will randomly kill a process which is performing a "Select 1" command. The error is this: 2012-04-02 13:36:13.269 PDT,"smxuser","smxprd1",39523,"127.0.0.1:57893",4f79ffad.9a63,1,"",2012-04-02 12:36:13 PDT,3/32,0,FATAL,40001,"terminating connection due to conflict with recovery","User query might have needed to see row versions that must be removed.","In a moment you should be able to reconnect to the database and repeat your command.",,,"" We have 5 hot standby's set up, which all preform this SELECT 1, and postgres kills them across all standby's. There should never be a situation that SELECT 1 is in conflict with data, as it it never using any table in the database. The system doesn't make a difference between queries like "SELECT 1" that don't access any tables, and those that do. Even if "SELECT 1" doesn't access any tables, a subsequent statement in the same transaction might. I'm assuming that those "SELECT 1"s were issued in transactions that had been open for a long time, because you shouldn't get recovery conflicts with very short transactions, in practice anyway. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
