Обсуждение: [BUGS] Error of insert Foreign table with postgres_fdw

Поиск
Список
Период
Сортировка

[BUGS] Error of insert Foreign table with postgres_fdw

От
Jaimin Pan
Дата:
Hi all,

I got error when insert a parent table by inserting a foreign table.
Environment is following:
PG 9.6.3 
CentOS6

step 1.
database1:  create one parent table: phone_pool, 100 inherits table: ph_xx.
phone_pool
ph_0
...
ph_99
(create trigger "phone_pool_insert_trigger" for phone_pool and insert into phone_pool works well. The child table got the row.)

step 2:
database2:
create foreign table phone_pool_foreign for phone_pool in database1.
when insert into phone_pool_foreign. I got error like following.
"""
insert into phone_pool_foreign(id,patrition,phone,carrier,createtime,updatetime,deleteflg)                                                                 SELECT '35A88C4B06B3', '90', '133333330', '1', to_date('2017-08-07 11:57:33.398','yyyy-mm-dd hh24:mi:ss'), to_date('2017-08-07 11:57:33.398','yyyy-mm-dd hh24:mi:ss'), 1;                                                                   
ERROR:  relation "ph_90" does not exist
CONTEXT:  Remote SQL command: INSERT INTO public. phone_pool_foreign(id, patrition, phone, carrier, createtime, updatetime, deleteflg) VALUES ($1, $2, $3, $4, $5, $6, $7)
PL/pgSQL function public.phone_pool_insert_trigger() line 93 at SQL statement
"""



Re: [BUGS] Error of insert Foreign table with postgres_fdw

От
Tom Lane
Дата:
Jaimin Pan <jaimin.pan@gmail.com> writes:
> I got error when insert a parent table by inserting a foreign table.
> ...
> (create trigger "phone_pool_insert_trigger" for phone_pool and insert into
> phone_pool works well. The child table got the row.)

> when insert into phone_pool_foreign. I got error like following.
> ERROR:  relation "ph_90" does not exist
> CONTEXT:  Remote SQL command: INSERT INTO public. phone_pool_foreign(id,
> patrition, phone, carrier, createtime, updatetime, deleteflg) VALUES ($1,
> $2, $3, $4, $5, $6, $7)
> PL/pgSQL function public.phone_pool_insert_trigger() line 93 at SQL
> statement
> """

You didn't show us either the whole trigger or the failing statement in
it, but I bet you forgot to schema-qualify the child table names.
postgres_fdw runs the remote session with a very restrictive search_path,
just pg_catalog if memory serves.  It's generally bad practice for
triggers to rely on the prevailing search path in any case.
        regards, tom lane


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Error of insert Foreign table with postgres_fdw

От
Jaimin Pan
Дата:
Thanks Tom. Add the schema "public" in trigger solve my question.

2017-08-07 21:19 GMT+08:00 Tom Lane <tgl@sss.pgh.pa.us>:
Jaimin Pan <jaimin.pan@gmail.com> writes:
> I got error when insert a parent table by inserting a foreign table.
> ...
> (create trigger "phone_pool_insert_trigger" for phone_pool and insert into
> phone_pool works well. The child table got the row.)

> when insert into phone_pool_foreign. I got error like following.
> ERROR:  relation "ph_90" does not exist
> CONTEXT:  Remote SQL command: INSERT INTO public. phone_pool_foreign(id,
> patrition, phone, carrier, createtime, updatetime, deleteflg) VALUES ($1,
> $2, $3, $4, $5, $6, $7)
> PL/pgSQL function public.phone_pool_insert_trigger() line 93 at SQL
> statement
> """

You didn't show us either the whole trigger or the failing statement in
it, but I bet you forgot to schema-qualify the child table names.
postgres_fdw runs the remote session with a very restrictive search_path,
just pg_catalog if memory serves.  It's generally bad practice for
triggers to rely on the prevailing search path in any case.

                        regards, tom lane