Обсуждение: Hot-standby and canceled queries
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.
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
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.