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