[Pgcluster-general] technical question

[a.mitani at sra-europe.com]

Hi,

I put following query in PGCluster.
Using table is generated by pgbench.

At first, insert from "Cluster 1"

test=# insert into history values (1,2,3,4,'now');
INSERT 0 1
test=# select * from history ;
 tid | bid | aid | delta |           mtime            | filler
-----+-----+-----+-------+----------------------------+--------
   1 |   2 |   3 |     4 | 2007-01-24 15:24:52.357065 |
(1 row)

test=# insert into history(mtime,tid,bid,aid) values ('now',10,20,(select
bid from history where tid=10));
INSERT 0 1
test=# select * from history ;
 tid | bid | aid | delta |           mtime            | filler
-----+-----+-----+-------+----------------------------+--------
   1 |   2 |   3 |     4 | 2007-01-24 15:24:52.357065 |
  10 |  20 |     |       | 2007-01-24 15:29:50.926981 |
(2 rows)

Then now, in "Cluster 2"
test=# select * from history ;
 tid | bid | aid | delta |           mtime            | filler
-----+-----+-----+-------+----------------------------+--------
   1 |   2 |   3 |     4 | 2007-01-24 15:24:52.357065 |
  10 |  20 |     |       | 2007-01-24 15:29:50.926981 |
(2 rows)

The data writing query is execute one by one in each Cluster DB.
And all Cluster DB uses the value sent from replication server as a value
of now().

Is this the answer of your question?
------------------------------------
At.Mitani

> * a.mitani at sra-europe.com <a.mitani at sra-europe.com> [070124 14:56]:
>> Hi,
>>
>> PGCluster is using a "query based relication".
>> And all data writing query is set queue in replication server.
>> Each replicated query execute from this queue one by one.
>>
>> Transaction isolation is controled in each Cluster DB it self.
>> It's a simple, isn't it :)
>
> Not, because with READ COMMITED isolation level, the data that a
> subselect returns is strictly depending upon the timing of concurrent
> transactions. This means the statement below is not deterministic not
> just because of the now, but also because of the simple subselect.
>
> Without the subquery, e.g. when the app featches the data and inserts
> it into a new query, that's not a problem for the replication, because
> the app chooses what is the right value.
>
> Andreas
> _______________________________________________
> Pgcluster-general mailing list
> Pgcluster-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgcluster-general
>