pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Hot-standby and canceled queries 3+ messages / 2 participants [nested] [flat]
* Hot-standby and canceled queries @ 2013-04-24 11:42 David F. Skoll <dfs@roaringpenguin.com> 2013-04-24 13:01 ` Re: Hot-standby and canceled queries Simon Riggs <simon@2ndQuadrant.com> 0 siblings, 1 reply; 3+ messages in thread From: David F. Skoll @ 2013-04-24 11:42 UTC (permalink / raw) To: pgsql-admin Hi, I ran a query on a hot-standby (PostgreSQL 9.1) and it was canceled as per the documentation about vacuum cleanups on the master. Second time testing, I started a transaction on the master (with BEGIN) but my query on the hot-standby was still canceled. Is that because a transaction doesn't "really" begin with BEGIN, but only after the first real statement after the BEGIN? Is it sufficient to use: BEGIN; SELECT ... ; to start a transaction on the master, or do I need to use UPDATE or INSERT to ensure that a vacuum cleanup isn't applied until the transaction is finished? Or am I totally out of luck? The documentation says: "PostgreSQL allows cleanup of old row versions when there are no transactions that need to see them to ensure correct visibility of data according to MVCC rules." So does that mean I can still run into trouble even with an active transaction on the master? I don't really want to set max_standby_streaming_delay very high, but that will be the action of last resort. Regards, David. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hot-standby and canceled queries 2013-04-24 11:42 Hot-standby and canceled queries David F. Skoll <dfs@roaringpenguin.com> @ 2013-04-24 13:01 ` Simon Riggs <simon@2ndQuadrant.com> 2013-04-24 15:34 ` Re: Hot-standby and canceled queries David F. Skoll <dfs@roaringpenguin.com> 0 siblings, 1 reply; 3+ messages in thread From: Simon Riggs @ 2013-04-24 13:01 UTC (permalink / raw) To: David F. Skoll <dfs@roaringpenguin.com>; +Cc: pgsql-admin On 24 April 2013 12:42, David F. Skoll <dfs@roaringpenguin.com> wrote: > I ran a query on a hot-standby (PostgreSQL 9.1) and it was canceled as > per the documentation about vacuum cleanups on the master. > > Second time testing, I started a transaction on the master (with > BEGIN) but my query on the hot-standby was still canceled. > > Is that because a transaction doesn't "really" begin with BEGIN, but only > after the first real statement after the BEGIN? Is it sufficient to > use: BEGIN; SELECT ... ; to start a transaction on the master Yes >, or > do I need to use UPDATE or INSERT to ensure that a vacuum cleanup isn't > applied until the transaction is finished? SELECT is sufficient -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Hot-standby and canceled queries 2013-04-24 11:42 Hot-standby and canceled queries David F. Skoll <dfs@roaringpenguin.com> 2013-04-24 13:01 ` Re: Hot-standby and canceled queries Simon Riggs <simon@2ndQuadrant.com> @ 2013-04-24 15:34 ` David F. Skoll <dfs@roaringpenguin.com> 0 siblings, 0 replies; 3+ messages in thread From: David F. Skoll @ 2013-04-24 15:34 UTC (permalink / raw) To: pgsql-admin On Wed, 24 Apr 2013 14:01:13 +0100 Simon Riggs <simon@2ndQuadrant.com> wrote: [How to ensure query on hot-standby is not canceled by a vacuum cleanup on the master.] >> do I need to use UPDATE or INSERT to ensure that a vacuum cleanup >> isn't applied until the transaction is finished? > SELECT is sufficient Great! Thanks for your reply. Regards, David. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-04-24 15:34 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-04-24 11:42 Hot-standby and canceled queries David F. Skoll <dfs@roaringpenguin.com> 2013-04-24 13:01 ` Simon Riggs <simon@2ndQuadrant.com> 2013-04-24 15:34 ` David F. Skoll <dfs@roaringpenguin.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox