Transactional DDL, but not Serializable

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Transactional DDL, but not Serializable
Date: 2011-03-25 15:46:38
Message-ID: 20110325154638.GN4116@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greetings,

We have a curious situation, consider this:

Process 1:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CRETE TABLE table1 (i integer);
INSERT INTO table1 VALUES (13);

Process 2:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
CREATE TABLE table2 (i integer);
INSERT INTO table2 VALUES (123);
COMMIT;

Process 1:
SELECT * FROM pg_class WHERE relname = 'table2'; -- no rows returned
SELECT * FROM table2; -- works?! but at least no records returned
INSERT INTO table2 VALUES (456);
-- also works.. now we have a tuple in the table which appears to
-- have been added before the table existed..
COMMIT;

This happens, of course, because we use SysCache to look up table
names to Oids and that uses SnapshotNow. In my view, this violates
the basic principle of least suprise and means that while we have
transaction DDL, but it's not really serializable (no, I don't
particularly care about that).

What I do worry about is that the bulk load discussion going on could
be shot down because of this. We won't let the earlier transaction
see any records in the table today because those tuples have an xmin
later, but if we were to insert those tuples with the frozen XID (as I
proposed in the other thread) then they'd be visible.

I don't believe fixing this would be terribly difficult and, I
believe, would be similar to what we've done else where (eg: with
indexes)- basically, add a column to pg_class with the 'createdxmin'
and then compare that against our transaction whenever we're doing
table lookups.

Thoughts?

Thanks,

Stephen

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim Nasby 2011-03-25 15:48:15 Re: Pre-set Hint bits/VACUUM FREEZE on data load..?
Previous Message Jim Nasby 2011-03-25 15:34:47 Re: Set hint bits upon eviction from BufMgr