Re: Disappearing Records - Mailing list pgsql-general

On Wed, Nov 02, 2005 at 10:28:56AM -0500, Tom Lane wrote:
> Looking at the code, I think that actually a regular, non-FREEZE VACUUM
> would do the "right thing" for tuples up to about 1 billion xacts past
> wrap, which is probably enough.  So the answer may be "just VACUUM".
> I'm still too lazy to test it though.

Well, I tested on 8.1beta which gets downright obnoxious about
wraparound. It refuses to do anything unless you're running in a
standalone backend. Anyway, your theory seems correct, for this version
anyway. The xmin is changed to FrozenTransactionId so it will be
visible forever more.

By plan or by accident, this certainly helps those people who run into
this, as long as previous versions work like this also...

Oh, the large positive numbers seems odd to me. I would have thought:

WARNING:  database "test" must be vacuumed within -49 transactions

would be clearer (and more alarming) than:

WARNING:  database "test" must be vacuumed within 4294967247 transactions

Have a nice day,

test=# insert into test values (1);
INSERT 0 1
test=# select ctid, xmin, xmax, cmin, cmax, * from test;
 ctid  | xmin  | xmax | cmin | cmax | value
-------+-------+------+------+------+-------
 (0,1) | 20138 |    0 |    0 |    0 |     1
(1 row)

test=# \q

-- resetxlog to a new value, create the clog so you can actually start

backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "template1" must be vacuumed within 45 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax        (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin        (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax        (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,1)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "20138"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
backend> insert into test values (2);
WARNING:  database "template1" must be vacuumed within 44 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".
         1: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "template1" must be vacuumed within 43 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "template1".
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax        (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin        (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax        (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,1)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "20138"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,2)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147484102" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "2" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

-- It's complaining about template1, so just out and fix them first
-- After coming back, it's decided it's already wrapped around, even
-- though I did a VACUUM FREEZE before adding the values. The really
-- large numbers are misleading also.

backend> insert into test values(3);
WARNING:  database "test" must be vacuumed within 4294967248 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
         1: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
backend> select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "test" must be vacuumed within 4294967247 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax        (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin        (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax        (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,1)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "20138"      (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,2)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147484102" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "2" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,3)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147503777" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "3" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

-- Hit enter a few times

backend>  select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "test" must be vacuumed within 4294967238 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax        (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin        (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax        (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,2)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147484102" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "2" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,3)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147503777" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "3" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

-- Oh no, my data's disappearing!

backend> vacuum test;
WARNING:  database "test" must be vacuumed within 4294967232 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
WARNING:  database "test" must be vacuumed within 4294967231 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
WARNING:  database "test" must be vacuumed within 4294967230 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
backend>  select ctid, xmin, xmax, cmin, cmax, * from test;
WARNING:  database "test" must be vacuumed within 4294967229 transactions
HINT:  To avoid a database shutdown, execute a full-database VACUUM in "test".
         1: ctid        (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin        (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax        (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin        (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax        (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value       (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,1)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2"  (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "1" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,2)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147484102" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "2" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----
         1: ctid = "(0,3)"      (typeid = 27, len = 6, typmod = -1, byval = f)
         2: xmin = "2147503777" (typeid = 28, len = 4, typmod = -1, byval = t)
         3: xmax = "0"  (typeid = 28, len = 4, typmod = -1, byval = t)
         4: cmin = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         5: cmax = "0"  (typeid = 29, len = 4, typmod = -1, byval = t)
         6: value = "3" (typeid = 23, len = 4, typmod = -1, byval = t)
        ----

-- Yay, it's back!!!



>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.