[Pgpool-general] Problem with tables with same name

[Marcin Kwiatkowski]

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