Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UUy64-0007kk-A2 for pgsql-admin@arkaria.postgresql.org; Wed, 24 Apr 2013 11:42:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UUy63-0004JY-R6 for pgsql-admin@arkaria.postgresql.org; Wed, 24 Apr 2013 11:42:47 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UUy62-0004JS-Kx for pgsql-admin@postgresql.org; Wed, 24 Apr 2013 11:42:46 +0000 Received: from www.ipv6.roaringpenguin.com ([2607:f748:1200:fb:70:38:112:54] helo=colo3.roaringpenguin.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UUy5z-0000Zz-Ky for pgsql-admin@postgresql.org; Wed, 24 Apr 2013 11:42:45 +0000 Received: from vanadium.roaringpenguin.com ([192.168.10.23]) by colo3.roaringpenguin.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id r3OBgfts032067 for ; Wed, 24 Apr 2013 07:42:41 -0400 Received: from shishi.roaringpenguin.com (dfs@shishi.roaringpenguin.com [192.168.2.3]) by vanadium.roaringpenguin.com (8.14.3/8.14.3/Debian-9.4) with ESMTP id r3OBgdZP028245 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES128-SHA bits=128 verify=NOT) for ; Wed, 24 Apr 2013 07:42:40 -0400 Date: Wed, 24 Apr 2013 07:42:38 -0400 From: "David F. Skoll" To: pgsql-admin@postgresql.org Subject: Hot-standby and canceled queries Message-ID: <20130424074238.2aa7f764@shishi.roaringpenguin.com> Organization: Roaring Penguin Software Inc. X-Mailer: Claws Mail 3.7.6 (GTK+ 2.20.1; x86_64-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit DKIM-Signature: v=1; a=rsa-sha1; c=relaxed; d=roaringpenguin.com; h=date :from:to:subject:message-id:mime-version:content-type :content-transfer-encoding; s=main; bh=fEAw6EiX+yb8fi67f8Wn+sKu3 ds=; b=n/UldsVOkhr+0kcxA0cKqU0ivQZqOUah+GAtBso5ngNURVBuKkZJKxFhW UEAaFdhUm5+7kJb2bn5iVafw8FXQWjjm+RuGFHeWuLs0Rwlk57LOzVzMGYXovwTw HTYLYfASpApkEhl0yCMSLP7PdyHTLVZE6s4H2+FnWXdBM26MgUwtLf7Y9g+bUCvP UT5lJvDdvTL9m9e72Ei7LEk0xLCUVsXsCjgqGsTR5CNAUm734EUJFGEzS+HbW3kU XvXowv4id3mAgig75tBQqEC4hniVKet0QxS3iaePkAgpYEo5Hp7q/ZWQoi42lwty Md8sHwkW75VfdVoxBOFcSy/gbPHXw== X-Scanned-By: CanIt (www . roaringpenguin . com) X-Scanned-By: MIMEDefang 2.73 on 192.168.10.23 X-Spam-Score: undef - relay 192.168.10.23 marked with skip_spam_scan X-CanIt-Geo: No geolocation information available for 192.168.10.23 X-CanItPRO-Stream: outgoing (inherits from default) X-CanIt-Archive-Cluster: SQVyZJxqklY5buiWXYCN4T/BjiM X-CanIt-Archived-As: base/20130424 / 01JrLGFfk X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org 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