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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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, 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?
Regards,
Sándor
Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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?
Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.
Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general