Re: Yet another "drop table vs delete" question

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Tue, 2009-04-21 at 13:59 -0700, Christophe wrote:
>> I'm sure there is a scenario under which a separate
>> transaction could see non-MVCC behavior from TRUNCATE, but I'm
>> having trouble see what it is.

> Session1:
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SELECT * FROM foo;

> Session2:
> BEGIN;
> TRUNCATE bar;
> COMMIT;

> Session1:
> SELECT * from bar;
> COMMIT;

> In Session1, the serializable transaction sees an empty version of bar,
> even though it had tuples in at the time Session1 got its serializable
> snapshot.

Exactly.

> If Session2 does a DROP TABLE instead of TRUNCATE, Session1 will get an
> error when it tries to read "bar".

Actually, the scenario that I suppose the OP had in mind was to drop
and immediately recreate "bar" (probably in the same transaction).
If you do that, then session 1 will actually see the new version of
"bar" when it eventually gets around to examining the table --- this
is because system catalog accesses always follow SnapshotNow rules.

So there is really darn little difference between TRUNCATE and
drop/recreate. The advantage of TRUNCATE is you don't have to
run around and manually re-establish indexes, foreign keys, etc.
It's probably also a tad faster because of less catalog churn.

regards, tom lane