Message-ID: <4D75E201.20402@enterprisedb.com>
Date: Tue, 08 Mar 2011 10:00:01 +0200
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.1.16) Gecko/20101226 Icedove/3.0.11
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>
CC: daveg <daveg@sonic.net>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Re: PD_ALL_VISIBLE flag was incorrectly set happend
	during repeatable vacuum
References: <AANLkTikw==USr3+4Mt+w97P3ymux-Bhn0G7Ad0ZJHbqG@mail.gmail.com>
	<4D2D6A56.9060808@enterprisedb.com>
	<20110228212832.GH26397@sonic.net>
	<1B3CCBDD-4AC6-404A-9E26-DB4CA2554228@endpoint.com>
	<AANLkTi=xq4SV6jwA7txvrbEWocyeiU6XiTG=-zVy+7Dq@mail.gmail.com>
	<AANLkTi=Ye7AoF7_uVQfPVn-ok2VOp65mi0xU3H7kCke3@mail.gmail.com>
	<20110303071208.GA4053@sonic.net>
	<4D6F4E5D.6080507@enterprisedb.com>
	<20110304090031.GE4053@sonic.net>
	<4D710ABD.5090103@enterprisedb.com>
	<20110307235340.GA21941@sonic.net>
	<AANLkTi=54GF1MJN3FSXL2gr9xG_aB09y_taT8daUwz4Z@mail.gmail.com>
In-Reply-To: <AANLkTi=54GF1MJN3FSXL2gr9xG_aB09y_taT8daUwz4Z@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 08.03.2011 04:07, Greg Stark wrote:
> Well from that log you definitely have OldestXmin going backwards. And
> not by a little bit either. at 6:33 it set the all_visible flag and
> then at 7:01 it was almost 1.3 million transactions earlier. In fact
> to precisely the same value that was in use for a transaction at 1:38.
> That seems like a bit of a coincidence though it's not repeated
> earlier.

Yep. After staring at GetOldestXmin() again, it finally struck me how 
OldestXmin can move backwards. You need two databases for it, which 
probably explains why this has been so elusive.

Here's how to reproduce that:

CREATE DATABASE foodb;
CREATE DATABASE bardb;

session 1, in foodb:

foodb=# begin isolation level serializable;
BEGIN
foodb=# CREATE TABLE foo (a int4); -- just something to force this xact 
to have an xid
CREATE TABLE
foodb=#

(leave the transaction open)

session 2, in bardb:

bardb=# CREATE TABLE foo AS SELECT 1;
SELECT
bardb=# vacuum foo; -- to set the PD_ALL_VISIBLE flag
VACUUM

session 3, in bardb:
bardb=# begin isolation level serializable;
BEGIN
bardb=# SELECT 1;
  ?column?
----------
         1
(1 row)

(leave transaction open)

session 2, in bardb:

bardb=# vacuum foo;
WARNING:  PD_ALL_VISIBLE flag was incorrectly set in relation "foo" page 
0 (OldestXmin 803)
VACUUM
bardb=#


What there are no other transactions active in the same database, 
GetOldestXmin() returns just latestCompletedXid. When you open a 
transaction in the same database after that, its xid will be above 
latestCompletedXid, but its xmin includes transactions from all 
databases, and there might be a transaction in some other database with 
an xid that precedes the value that GetOldestXmin() returned earlier.

I'm not sure what to do about that. One idea is track two xmin values in 
proc-array, one that includes transactions in all databases, and another 
that only includes transactions in the same database. GetOldestXmin() 
(when allDbs is false) would only pay attention to the latter. It would 
add a few instructions to GetSnapshotData(), though.

Another idea is to give up on the warning when it appears that 
oldestxmin has moved backwards, and assume that it's actually fine. We 
could still warn in other cases where the flag appears to be incorrectly 
set, like if there is a deleted tuple on the page.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com