Re: Question about MVCC-unsafe commands

On Tue, Aug 23, 2016 at 10:10 AM, Antonin Houska <ah(at)cybertec(dot)at> wrote:
> I'm failing to understand why [1] mentions "table-rewriting forms of ALTER
> TABLE" besides TRUNCATE command.
>
> For TRUNCATE it seems clear: if transaction A takes the snapshot before it
> accesses the table first time (typically because isolation level is at least
> REPEATABLE READ) and transaction B manages to commit TRUNCATE soon enough,
> then A sees pg_class entry of the table already affected by B, which has the
> new (empty) relfilenode. (The original pg_class entry is no longer visible by
> catalog snapshot, nor does it contain valid OID of the original relfilenode.)
>
> But IMO heap rewriting changes neither table contents, nor visibility. Can
> anyone explain what I miss?

CLUSTER preserves xmin/xmax when rewriting, but ALTER TABLE does not.

rhaas=# create table foo (a int);
CREATE TABLE
rhaas=# insert into foo values (1);
INSERT 0 1
rhaas=# insert into foo values (2);
INSERT 0 1
rhaas=# select xmin, a from foo;
xmin | a
------+---
934 | 1
935 | 2
(2 rows)

rhaas=# alter table foo alter a type text;
ALTER TABLE
rhaas=# select xmin, a from foo;
xmin | a
------+---
936 | 1
936 | 2
(2 rows)

This is sad.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company