Thread: "Multiple statements are an issue with pgPool-II in its replication mode" again
"Multiple statements are an issue with pgPool-II in its replication mode" again
From
TAKATSUKA Haruka
Date:
Hi, pgadmin-team. This fix to execute "Set ..." and "SELECT ..." separately was overwritten. Now pgAdmin-III 1.12.1 cannot connect pgpool-II replication cluster. (*) Is it has any reason ? (e.g. There are many multiple-statements and they decide to give up ...) 2009-07-20 GL 1.12.0 Multiple statements are an issue with pgPool-II in its replication mode. * commitdiff: (fix multiple statements) http://git.postgresql.org/gitweb?p=pgadmin3.git;a=commitdiff;h=ef994af1e7a4bbd24f29fd99f452cbe315786286 * diff to current: (use multiple statements again) http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blobdiff;f=pgadmin/db/pgConn.cpp;h=ea4aaa5b436f14494170c0994db23669ac953b00;hp=e4132817f6a896500689407829d75c2f92b95f57;hb=HEAD;hpb=ef994af1e7a4bbd24f29fd99f452cbe315786286 (*) pgpool-II execute multiple-SQLs at all backend nodes according to its feature and to run 'SELECT ... WHERE oid = 12345'generate diffrent answers. It causes an error. ______________________________________________________________________TAKATSUKA Haruka harukat@sraoss.co.jp SRA OSS, Inc.http://www.sraoss.co.jp/
Re: "Multiple statements are an issue with pgPool-II in its replication mode" again
From
Guillaume Lelarge
Date:
Le 22/11/2010 07:38, TAKATSUKA Haruka a écrit : > [...] > This fix to execute "Set ..." and "SELECT ..." separately was > overwritten. Now pgAdmin-III 1.12.1 cannot connect pgpool-II > replication cluster. (*) > > Is it has any reason ? > (e.g. There are many multiple-statements and they decide to give up ...) > No-one decided to give up on this. You should at least be able to connect. > 2009-07-20 GL 1.12.0 Multiple statements are an issue with pgPool-II in > its replication mode. > > * commitdiff: (fix multiple statements) > http://git.postgresql.org/gitweb?p=pgadmin3.git;a=commitdiff;h=ef994af1e7a4bbd24f29fd99f452cbe315786286 > > * diff to current: (use multiple statements again) > http://git.postgresql.org/gitweb?p=pgadmin3.git;a=blobdiff;f=pgadmin/db/pgConn.cpp;h=ea4aaa5b436f14494170c0994db23669ac953b00;hp=e4132817f6a896500689407829d75c2f92b95f57;hb=HEAD;hpb=ef994af1e7a4bbd24f29fd99f452cbe315786286 > I forgot pgPool when I commited this. I should probably add a comment in this function :) Will fix this. > (*) pgpool-II execute multiple-SQLs at all backend nodes according to > its feature and to run 'SELECT ... WHERE oid = 12345' generate diffrent > answers. It causes an error. > 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 -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Re: "Multiple statements are an issue with pgPool-II in its replication mode" again
From
TAKATSUKA Haruka
Date:
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 notkeep 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, deleterows - Error or not 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 rowsanytime) "SELECT * FROM pg_enum WHERE oid = 12345"--> OK (but in case pgpool-II "load balance mode", cause inconsistent outputsometime) ______________________________________________________________________harukat@sraoss.co.jp SRA OSS, Inc http://www.sraoss.co.jp
Re: "Multiple statements are an issue with pgPool-II in its replication mode" again
From
Guillaume Lelarge
Date:
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). -- Guillaumehttp://www.postgresql.frhttp://dalibo.com
Re: "Multiple statements are an issue with pgPool-II in its replication mode" again
From
Dave Page
Date:
On Thu, Nov 25, 2010 at 7:57 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
>
> 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).
Maybe not, but we do use OIDs in a bunch of places where things will
break if pgPool returns results from a different server - for example,
for refreshing a single object we often do something like:
type = typeFactory.CreateObjects(coll, 0, wxT("\n AND t.oid=") + GetOidStr());
Fixing all those instances would take some work, but would at least be
feasible. What I don't see an easy fix for, is that we also use xmin
to detect external changes to some object types.
--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: "Multiple statements are an issue with pgPool-II in its replication mode" again
From
Guillaume Lelarge
Date:
Le 25/11/2010 09:41, Dave Page a écrit :
> On Thu, Nov 25, 2010 at 7:57 AM, Guillaume Lelarge
> <guillaume@lelarge.info> wrote:
>>
>> 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).
>
> Maybe not, but we do use OIDs in a bunch of places where things will
> break if pgPool returns results from a different server - for example,
> for refreshing a single object we often do something like:
>
> type = typeFactory.CreateObjects(coll, 0, wxT("\n AND t.oid=") + GetOidStr());
>
> Fixing all those instances would take some work, but would at least be
> feasible.
Actually, I don't see how you can do that. The OIDs are used there as a
unique identifier for this object.
> What I don't see an easy fix for, is that we also use xmin
> to detect external changes to some object types.
You mean for functions? yeah, that would be another issue.
--
Guillaumehttp://www.postgresql.frhttp://dalibo.com