Thread: Slony and triggers on slavenodes.
Hi,
I have a Postgresql 11 and Slony 2.2.7 setup with one master and multiple slave nodes. On one of the slaves I want a user defined trigger to fire on insert. The trigger fires a notify function.
Now to the problem.
If I create the trigger it looks like it is enabled on the slave but it do not fire.
CREATE TRIGGER <trigger_name>
AFTER INSERT
ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE <function_name>;
AFTER INSERT
ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE <function_name>;
If i try to enable it explicit with
ALTER TABLE <table_name>
ENABLE ALWAYS TRIGGER <trigger_name>;
ENABLE ALWAYS TRIGGER <trigger_name>;
or
ALTER TABLE <table_name>
ENABLE REPLICA TRIGGER <trigger_name>;
ENABLE REPLICA TRIGGER <trigger_name>;
and then look at the table definition the trigger is disabled.
Is this expected behavior in slony?
I understand that this may be tricky in replication context and that the default behavior is that triggers on slaves are disabled.
We have plans to move to logical replication which should solve this I assume, but it does not fit the schedule at the moment.
KR
Mikael Gustavsson
On 18/2/19 1:09 μ.μ., Gustavsson Mikael wrote:
P {margin-top:0;margin-bottom:0;} Hi,I have a Postgresql 11 and Slony 2.2.7 setup with one master and multiple slave nodes. On one of the slaves I want a user defined trigger to fire on insert. The trigger fires a notify function.Now to the problem.If I create the trigger it looks like it is enabled on the slave but it do not fire.CREATE TRIGGER <trigger_name>
AFTER INSERT
ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE <function_name>;If i try to enable it explicit withALTER TABLE <table_name>
ENABLE ALWAYS TRIGGER <trigger_name>;orALTER TABLE <table_name>
ENABLE REPLICA TRIGGER <trigger_name>;and then look at the table definition the trigger is disabled.
How? can you show the output of \d ? or pg_dump -x --schema-only -t <table_name> ?
Is this expected behavior in slony?I understand that this may be tricky in replication context and that the default behavior is that triggers on slaves are disabled.We have plans to move to logical replication which should solve this I assume, but it does not fit the schedule at the moment.KRMikael Gustavsson
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Hi,
I checked in pgAdmin and that turned out to be the problem. If i check with \d <table_name> the trigger is listed under "Triggers firing always:". And it fires as expected. I was misled by the UI. So it seems like it is a UI problem rather than in postgresql.
Thanks for your time.
KR /Mikael
Från: Achilleas Mantzios [achill@matrix.gatewaynet.com]
Skickat: den 18 februari 2019 12:31
Till: pgsql-general@lists.postgresql.org
Ämne: Re: Slony and triggers on slavenodes.
Skickat: den 18 februari 2019 12:31
Till: pgsql-general@lists.postgresql.org
Ämne: Re: Slony and triggers on slavenodes.
On 18/2/19 1:09 μ.μ., Gustavsson Mikael wrote:
BODY {direction: ltr;font-family: Tahoma;color: #000000;font-size: 10pt;}P {margin-top:0;margin-bottom:0;}BODY {scrollbar-base-color:undefined;scrollbar-highlight-color:undefined;scrollbar-darkshadow-color:undefined;scrollbar-track-color:undefined;scrollbar-arrow-color:undefined} Hi,I have a Postgresql 11 and Slony 2.2.7 setup with one master and multiple slave nodes. On one of the slaves I want a user defined trigger to fire on insert. The trigger fires a notify function.Now to the problem.If I create the trigger it looks like it is enabled on the slave but it do not fire.CREATE TRIGGER <trigger_name>
AFTER INSERT
ON <table_name>
FOR EACH ROW
EXECUTE PROCEDURE <function_name>;If i try to enable it explicit withALTER TABLE <table_name>
ENABLE ALWAYS TRIGGER <trigger_name>;orALTER TABLE <table_name>
ENABLE REPLICA TRIGGER <trigger_name>;and then look at the table definition the trigger is disabled.
How? can you show the output of \d ? or pg_dump -x --schema-only -t <table_name> ?
Is this expected behavior in slony?I understand that this may be tricky in replication context and that the default behavior is that triggers on slaves are disabled.We have plans to move to logical replication which should solve this I assume, but it does not fit the schedule at the moment.KRMikael Gustavsson
-- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt