Re: "Multiple statements are an issue with pgPool-II in its replication mode" again

Le 25/11/2010 06:41, TAKATSUKA Haruka a écrit :
> Hi,
>
>> Can you give us a complete example of this issue? if this is something
>> you get when modifying an object using pgAdmin's UI, it'll be hard to
>> do. pgAdmin relies a lot on multi-statements. But probably we can figure
>> a way to do this. I kind of did this for this patch :
>>
>> http://git.postgresql.org/gitweb?p=pgadmin3.git;a=commit;h=8dc53145d50ed2f65a112700c1390db79120f6d7
>
>
> The example to show below is not *complete*, but will be useful.
>
>
> the feature (summary):
>
> * Pgpool-II replication does not keep OID same between backend nodes.
> (pgpool-II master-slave mode + slony-I also does not keep OID)
>
> * Pgpool-II run multi-statements query at all nodes.
> (Pgpool-II run simple SELECT at one of nodes)
>
> * Pgpool-II cause an error (or failover judgement) by difference in:
> - number of return rows
> - number of update, delete rows
> - Error or not
>

Interesting, I always wondered how it did, but never tried to actually
look in the code.

> example (SQL at pgpool-II replication mode):
>
> "Set some_guc TO 'xx'; SELECT * FROM pg_enum ORDER BY oid"
> --> OK (run at all nodes, but it is unnecessarily; no harm)
>
> "SELECT * FROM pg_enum ORDER BY oid"
> --> OK
>
> "Set some_guc TO 'xx'; SELECT * FROM pg_enum WHERE oid = 12345"
> --> NG (oid differ cause mismatch on number of return rows anytime)
>
> "SELECT * FROM pg_enum WHERE oid = 12345"
> --> OK (but in case pgpool-II "load balance mode",
> cause inconsistent output sometime)
>

I don't really understand. These do not come from pgAdmin, right?
because we don't filter pg_enum by oid. We sort by oid for pg_enum (and
there's no way to do otherwise before 9.1), but we don't filter by oid.
So this example should be working (once you're connected, which is your
main issue IIRC).

--
Guillaume
http://www.postgresql.fr
http://dalibo.com