Thread: [BUGS] BUG #14619: Before update trigger on foreign table fails to updatevalues

The following bug has been logged on the website:

Bug reference:      14619
Logged by:          Andrew Wheelwright
Email address:      andrew.wheelwright@familysearch.org
PostgreSQL version: 9.6.2
Operating system:   CentOS 6.7
Description:

We recently upgraded our databases from 9.4 to 9.6.2.

I'm noticing a problem with some updates to foreign tables I wasn't seeing
before the upgrade (doesn't mean the problem wasn't there, however).

For the sake of simplicity, I'll call my servers Master and Client.  There's
a table on Master I'll call standard_values.  There's a foreign table object
on Client which points to Master's standard_values table.

The standard_values foreign table on Client has a before update trigger
ensures the updated_by field is set to session_user.  

When a user updates a record on the foreign table, (e.g. update
standard_values set standard = 'Foo' where id = 42), the updated_by field is
not set as expected.

I've defined the trigger function as follows:

create or replace function set_updated_by()
return trigger language plpgsql as $$
begin   raise debug 'Before trigger updated_by: %', new.updated_by;   new.updated_by := session_user;   raise debug
'Aftertrigger updated_by: %', new.updated_by;   return new;
 
end;
$$;

The trigger is attached to the table using the following command:

create trigger set_updated_by_before_standard_values_update   before update on standard_values   for each row execute
procedureset_updated_by();
 

From the raise debug statements, I can see that the trigger is called and
sets the appropriate value but the value doesn't make it into the table on
the foreign server.

I suspect the foreign data wrapper is ignoring the value because it wasn't
part of the original update statement.  I've observed that I can get the
correct value if I explicitly try to set a random value for 'updated_by' in
my update query.

I'm not really sure how to troubleshoot this further and would appreciate
any pointers.


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