[Pgcluster-general] technical question

[Markus Schiltknecht]

Hi,

Andreas Kostyrka wrote:
> Well, how does pgcluster serialize statements?

AFAICT, per statement, ordering them by incoming time...

> And does it apply
> statements done on two connections concurrently?

Hm. I'm unclear what exactly you are asking.

I think I can give a better example, without the confusing now() 
function, which is already nondeterministic by itself:

initialize a table with:

CREATE TABLE test (a int NOT NULL);
INSERT INTO test VALUES (1);

then concurrently run this insert on multiple nodes:

UPDATE test SET a = (SELECT max(a) + 1 FROM test);

Normally, in READ COMMITTED mode, the subselect might or might not yet 
see the other's update. There's a race condition, which normally gets 
solved with a FOR UPDATE.

In SERIALIZABLE mode, this gives you a serialization error.

I don't know how exactly PgCluster handles FOR UPDATE, but if you simply 
replicate all statements and wait for every cluster node (as in 
PgCluster's reliable mode, I guess), you will get exactly the same 
behavior regarding this issue.

So, the above transaction should better be something like:

BEGIN;
x = (SELECT max(a) + 1 FROM test FOR UPDATE);
UPDATE test SET a = x;
COMMIT;

This works for both isolation levels and AFAIK with PgCluster as well. 
Mr. Mitani, please correct me if I'm wrong.

Regards

Markus