Re: [HACKERS] Question about MVCC-unsafe commands

[Antonin Houska]

Robert Haas  wrote:
> On Tue, Aug 23, 2016 at 10:10 AM, Antonin Houska  wrote:

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

Indeed, CLUSTER was the command I picked for my experiments. I didn't expect
such a subtle difference. Thanks for answer.

-- 
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Question about MVCC-unsafe commands

[Robert Haas]

On Tue, Aug 23, 2016 at 10:10 AM, Antonin Houska  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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Question about MVCC-unsafe commands

[Antonin Houska]

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?

[1] https://www.postgresql.org/docs/9.6/static/mvcc-caveats.html
-- 
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers