[Pgpool-general] Problem with tables with same name

[Tatsuo Ishii]

Great!
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> It works now. Thanks!
> 
> On 10 August 2010 01:14, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> > Marcin,
> >
> > I have made one line patch to make pgpool consider schema search
> > path. Please apply this and see if it works.
> > --
> > Tatsuo Ishii
> > SRA OSS, Inc. Japan
> > English: http://www.sraoss.co.jp/index_en.php
> > Japanese: http://www.sraoss.co.jp
> >
> >> Don't think so. It's set to '"$user",public'. But the order of schemas
> >> should result with insert into wrong table, not in adding _just_ one
> >> column from table from scheme buffor to my insert into table from
> >> scheme public, shouldn't it? As i wrote before - problem isn't that my
> >> insert goes into wrong table - problem is that with pgpool on it adds
> >> to my insert some random column from other table.
> >>
> >> On 6 August 2010 16:04, Glyn Astill <glynastill at yahoo.co.uk> wrote:
> >> >
> >> > Surely this depends on what order you have the schemas detailed in the search_path variable.
> >> >
> >> >
> >> > --- On Fri, 6/8/10, Marcin Kwiatkowski <marcik4 at gmail.com> wrote:
> >> >
> >> >> From: Marcin Kwiatkowski <marcik4 at gmail.com>
> >> >> Subject: [Pgpool-general] Problem with tables with same name
> >> >> To: pgpool-general at pgfoundry.org
> >> >> Date: Friday, 6 August, 2010, 14:20
> >> >> Hello
> >> >>
> >> >> I'm using pgpool in replication mode and i have a big
> >> >> problem with
> >> >> tables with same name. It happens with postgres 8.4.4 and
> >> >> pgpool-II
> >> >> 2.3.3. I'm attaching a simplified dump of a database. It
> >> >> has 2 tables:
> >> >> mytable in schema public and in schema buffor. They both
> >> >> have very
> >> >> different columns. And when i try to make an insert:
> >> >>
> >> >> "INSERT INTO mytable (p5, p9, p8, p6, p7, p2, p3, p4, p10,
> >> >> p11, p12,
> >> >> p13, p14, p15, p17, p18, p1) values ('2010-08-04
> >> >> 15:38:43.808000
> >> >> +02:00:00', '2010-08-04 15:38:43.791000 +02:00:00', NULL,
> >> >> NULL, true,
> >> >> 599112, 599113, 1, NULL, NULL, NULL, NULL, NULL, null,
> >> >> NULL, NULL,
> >> >> 299299);"
> >> >>
> >> >> which should go into public.mytable i get an error:
> >> >> "ERROR:  column "p59" of relation "mytable" does not
> >> >> exist
> >> >> LINE 1: ... p17, p18, p1) values ('2010-08-04
> >> >> 15:38:43.808000 +02:00:00..."
> >> >>
> >> >> The problem that there's no column p59 in public.mytable -
> >> >> it exists
> >> >> only in buffor.mytable.
> >> >>
> >> >> Pgpool log says:
> >> >> "SimpleQuery: Error or notice message from backend: : DB
> >> >> node id: 0
> >> >> backend pid: 18429 statement: INSERT INTO "mytable"("p5",
> >> >> "p9", "p8",
> >> >> "p6", "p7", "p2", "p3", "p4", "p10", "p11", "p12", "p13",
> >> >> "p14",
> >> >> "p15", "p17", "p18", "p1", "p59") VALUES ('2010-08-04
> >> >> 15:38:43.808000
> >> >> +02:00:00','2010-08-04 15:38:43.791000
> >> >> +02:00:00',NULL,NULL,'t'::bool,599112,599113,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,299299,'2010-08-06
> >> >> 15:14:37.669948+02') message: column "p59" of relation
> >> >> "mytable" does
> >> >> not exist"
> >> >>
> >> >> It seems then that it's pgpool which added that column to
> >> >> my insert.
> >> >> It works ok when i explicit write public.mytable in insert
> >> >> but that
> >> >> insert is autogenerated by an hibernate so i can't
> >> >> workaround it that
> >> >> way.That insert also works ok if i run it on same postgres
> >> >> but without
> >> >> pgpool.
> >> >>
> >> >> Any idea how to fix it?
> >> >>
> >> >> --
> >> >> Pozdrawiam
> >> >> Marcin Kwiatkowski
> >> >>
> >> >> -----Inline Attachment Follows-----
> >> >>
> >> >> _______________________________________________
> >> >> Pgpool-general mailing list
> >> >> Pgpool-general at pgfoundry.org
> >> >> http://pgfoundry.org/mailman/listinfo/pgpool-general
> >> >>
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >> --
> >> Pozdrawiam
> >> Marcin Kwiatkowski
> >> _______________________________________________
> >> Pgpool-general mailing list
> >> Pgpool-general at pgfoundry.org
> >> http://pgfoundry.org/mailman/listinfo/pgpool-general
> >
> > *** pool_timestamp.c.orig       2010-02-17 23:00:14.000000000 +0900
> > --- pool_timestamp.c    2010-08-10 08:09:14.000000000 +0900
> > ***************
> > *** 110,116 ****
> >  #define ATTRDEFQUERY "SELECT attname, coalesce(d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%', false)" \
> >        " FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " \
> >        " LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)" \
> > !       " WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.relname = '%s'" \
> >        " ORDER BY a.attnum"
> >
> >        if (!ts_relcache)
> > --- 110,116 ----
> >  #define ATTRDEFQUERY "SELECT attname, coalesce(d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%', false)" \
> >        " FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " \
> >        " LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)" \
> > !       " WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid = '%s'::regclass::oid" \
> >        " ORDER BY a.attnum"
> >
> >        if (!ts_relcache)
> >
> >
> 
> -- 
> Pozdrawiam
> Marcin Kwiatkowski