Thread: Update foreign table with trigger
Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table. As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another foreign table called TABLE_B, that's ok. At this point I have two foreign tables, TABLE_A and TABLE_B. Now, when I try to do an update on TABLE_A, I get relation "TABLE_B" does not exist. Here's what I get: update TABLE_A set updated=true where id=1234; ERROR: relation "TABLE_B" does not exist CONTEXT: Remote SQL command: UPDATE public.TABLE_A SET updated = $2 WHERE ctid = $1 PL/pgSQL function public.TABLE_A_update() line 4 at SQL statement Any hint?. Regards -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877
On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: > Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update > on that table. > > As TABLE_A has a trigger, and the trigger does an insert on another > table (TABLE_B), I had to create another foreign table called TABLE_B, > that's ok. that trigger is defined on the server that actually has table_a, right? or did you define a trigger on the FDW table ? -- john r pierce, recycling bits in santa cruz
El 26/02/16 a las 15:55, John R Pierce escribió: > On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: >> Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update >> on that table. >> >> As TABLE_A has a trigger, and the trigger does an insert on another >> table (TABLE_B), I had to create another foreign table called >> TABLE_B, that's ok. > > > that trigger is defined on the server that actually has table_a, > right? or did you define a trigger on the FDW table ? > Hi John, yes, the trigger is only defined on the foreign server. -- Leonardo M. Ramé Medical IT - Griensu S.A. Av. Colón 636 - Piso 8 Of. A X5000EPT -- Córdoba Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19 Cel.: +54 9 (011) 40871877
On 26 February 2016 at 20:02, Leonardo M. Ramé <l.rame@griensu.com> wrote:
El 26/02/16 a las 15:55, John R Pierce escribió:On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:Hi John, yes, the trigger is only defined on the foreign server.Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on that table.
As TABLE_A has a trigger, and the trigger does an insert on another table (TABLE_B), I had to create another foreign table called TABLE_B, that's ok.
that trigger is defined on the server that actually has table_a, right? or did you define a trigger on the FDW table ?
Let's check we get this right!
You have two "real" table in the remote server with a trigger doing it's job on them and on the local server you have and FDW on each remote table. Right?
Regards,
Sándor
El 26/02/16 a las 16:18, s d escribió: > > On 26 February 2016 at 20:02, Leonardo M. Ramé <l.rame@griensu.com > <mailto:l.rame@griensu.com>> wrote: > > > El 26/02/16 a las 15:55, John R Pierce escribió: > > On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: > > Hi, I created a Postgres_FDW table (TABLE_A) and need to do > an update on that table. > > As TABLE_A has a trigger, and the trigger does an insert on > another table (TABLE_B), I had to create another foreign > table called TABLE_B, that's ok. > > > > that trigger is defined on the server that actually has table_a, > right? or did you define a trigger on the FDW table ? > > Hi John, yes, the trigger is only defined on the foreign server. > > > Let's check we get this right! > You have two "real" table in the remote server with a trigger doing it's > job on them and on the local server you have and FDW on each remote > table. Right? > Yes, that's right.
On 26 February 2016 at 20:19, Leonardo M. Ramé <l.rame@griensu.com> wrote:
El 26/02/16 a las 16:18, s d escribió:
On 26 February 2016 at 20:02, Leonardo M. Ramé <l.rame@griensu.com
<mailto:l.rame@griensu.com>> wrote:
El 26/02/16 a las 15:55, John R Pierce escribió:
On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
Hi, I created a Postgres_FDW table (TABLE_A) and need to do
an update on that table.
As TABLE_A has a trigger, and the trigger does an insert on
another table (TABLE_B), I had to create another foreign
table called TABLE_B, that's ok.
that trigger is defined on the server that actually has table_a,
right? or did you define a trigger on the FDW table ?
Hi John, yes, the trigger is only defined on the foreign server.
Let's check we get this right!
You have two "real" table in the remote server with a trigger doing it's
job on them and on the local server you have and FDW on each remote
table. Right?
Yes, that's right.
Then try to do the update on the remote db directly.
In the meantime could you provide the table and trigger definitions?
El 26/02/16 a las 16:33, s d escribió: > On 26 February 2016 at 20:19, Leonardo M. Ramé <l.rame@griensu.com > <mailto:l.rame@griensu.com>> wrote: > > > > El 26/02/16 a las 16:18, s d escribió: > > > On 26 February 2016 at 20:02, Leonardo M. Ramé > <l.rame@griensu.com <mailto:l.rame@griensu.com> > <mailto:l.rame@griensu.com <mailto:l.rame@griensu.com>>> wrote: > > > El 26/02/16 a las 15:55, John R Pierce escribió: > > On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote: > > Hi, I created a Postgres_FDW table (TABLE_A) and > need to do > an update on that table. > > As TABLE_A has a trigger, and the trigger does an > insert on > another table (TABLE_B), I had to create another > foreign > table called TABLE_B, that's ok. > > > > that trigger is defined on the server that actually has > table_a, > right? or did you define a trigger on the FDW table ? > > Hi John, yes, the trigger is only defined on the foreign > server. > > > Let's check we get this right! > You have two "real" table in the remote server with a trigger > doing it's > job on them and on the local server you have and FDW on each remote > table. Right? > > > Yes, that's right. > > > Then try to do the update on the remote db directly. > > In the meantime could you provide the table and trigger definitions? > I don't understand why the trigger is run in the caller database instead of the called (foreign) one.
On 26 February 2016 at 20:42, Leonardo M. Ramé <l.rame@griensu.com> wrote:
El 26/02/16 a las 16:33, s d escribió:On 26 February 2016 at 20:19, Leonardo M. Ramé <l.rame@griensu.com
<mailto:l.rame@griensu.com>> wrote:
El 26/02/16 a las 16:18, s d escribió:
On 26 February 2016 at 20:02, Leonardo M. Ramé
<l.rame@griensu.com <mailto:l.rame@griensu.com>
<mailto:l.rame@griensu.com <mailto:l.rame@griensu.com>>> wrote:
El 26/02/16 a las 15:55, John R Pierce escribió:
On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
Hi, I created a Postgres_FDW table (TABLE_A) and
need to do
an update on that table.
As TABLE_A has a trigger, and the trigger does an
insert on
another table (TABLE_B), I had to create another
foreign
table called TABLE_B, that's ok.
that trigger is defined on the server that actually has
table_a,
right? or did you define a trigger on the FDW table ?
Hi John, yes, the trigger is only defined on the foreign
server.
Let's check we get this right!
You have two "real" table in the remote server with a trigger
doing it's
job on them and on the local server you have and FDW on each remote
table. Right?
Yes, that's right.
Then try to do the update on the remote db directly.
In the meantime could you provide the table and trigger definitions?
I don't understand why the trigger is run in the caller database instead of the called (foreign) one.
It isn't. You get this error message because the reason why the local command fails is in the remote trigger somewhere.
El 26/02/16 a las 16:49, s d escribió: > On 26 February 2016 at 20:42, Leonardo M. Ramé <l.rame@griensu.com > <mailto:l.rame@griensu.com>> wrote: > > > > > Then try to do the update on the remote db directly. > > In the meantime could you provide the table and trigger definitions? > > > I don't understand why the trigger is run in the caller database > instead of the called (foreign) one. > > > It isn't. You get this error message because the reason why the local > command fails is in the remote trigger somewhere. > But, when I update TABLE_A from the remote server everything works ok.
On 26 February 2016 at 21:02, Leonardo M. Ramé <l.rame@griensu.com> wrote:
El 26/02/16 a las 16:49, s d escribió:On 26 February 2016 at 20:42, Leonardo M. Ramé <l.rame@griensu.com
<mailto:l.rame@griensu.com>> wrote:
Then try to do the update on the remote db directly.
In the meantime could you provide the table and trigger definitions?
I don't understand why the trigger is run in the caller database
instead of the called (foreign) one.
It isn't. You get this error message because the reason why the local
command fails is in the remote trigger somewhere.
But, when I update TABLE_A from the remote server everything works ok.
I'm pretty sure we won't get further without seeing your defs.
El 26/02/16 a las 17:11, s d escribió: > On 26 February 2016 at 21:02, Leonardo M. Ramé <l.rame@griensu.com > <mailto:l.rame@griensu.com>> wrote: > > > > El 26/02/16 a las 16:49, s d escribió: > > On 26 February 2016 at 20:42, Leonardo M. Ramé > <l.rame@griensu.com <mailto:l.rame@griensu.com> > <mailto:l.rame@griensu.com <mailto:l.rame@griensu.com>>> wrote: > > > > > Then try to do the update on the remote db directly. > > In the meantime could you provide the table and trigger > definitions? > > > I don't understand why the trigger is run in the caller > database > instead of the called (foreign) one. > > > It isn't. You get this error message because the reason why the > local > command fails is in the remote trigger somewhere. > > > But, when I update TABLE_A from the remote server everything works ok. > > > I'm pretty sure we won't get further without seeing your defs. > Solved!. The indeed the problem was at the foreign server side, instead of working with TABLE_B I had to do public.TABLE_B and everything went ok.