Re: transaction confusion

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> Here is a simple, reproducible example that delete doesn't cause it to use serial:

If you add
raise notice 'past the delete';
between the DELETE and the insert loop, you will discover that the
second guy in fact doesn't manage to complete the DELETE until the first
one commits. He's finding the first deletable row, noticing that the
first guy has a delete in progress on it, and waiting till the first
guy commits. He then finishes scanning the table, but finds nothing he
can delete --- every tuple visible to his snapshot is already committed
deleted, and the rows added by the first guy aren't visible. Then when
he gets to the next command, he takes a new snapshot, and suddenly the
first guy's insertions *are* visible.

Basically the reason that your complicated function works is that the
update on the single-table row creates a serialization point before the
snapshot for the DELETE is taken, while the simpler function serializes
inside the DELETE --- after that snap is taken.

regards, tom lane