Re: transaction confusion

Sim Zacks <sim(at)compulab(dot)co(dot)il> writes:
> This function, when run in 2 separate sessions at the same time,
> causes a duplicate key error because popartid is a primary key.
> ...
> The next one is a bit longer. I would expect that if it was run in 2
> separate sessions that 1) it would generate the same error as the
> first one generated,

I would expect that too, assuming that it actually inserts the same set
of rows into the table as the first one does (how sure are you of that?).
I wonder whether you are testing them both under identical conditions.
In particular, is the initial DELETE really doing anything or is the
table usually empty to start with anyway? If there is something to
delete then that ought to serialize the two sessions, leading to no
error (because the second guy in will wait to see if the first guy
commits his deletion).

[ eyeballs second function some more... ] Actually, the second
function is guaranteed to be serialized by that initial
"update systemsettings" --- I assume that's a one-row table? The second
guy in will be unable to get past that until the first guy commits, and
then he'll see the first guy's updates and there will be no error.
I think the reason you are seeing failures in the first function is
that the initial DELETE is a no-op so it doesn't serialize anything,
and then there is conflict when the two INSERTs proceed in parallel.

regards, tom lane