<div dir="ltr"><div dir="ltr"><br></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Feb 20, 2020 at 4:16 AM Steve Singer <<a href="mailto:steve@ssinger.info" target="_blank">steve@ssinger.info</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">On Thu, 20 Feb 2020, Richard Yen wrote:<br>
<br>
So with your patch, what happens on node C?<br>
Do the replication triggers fire there as well?<br></blockquote><div><br></div><div>No, on node C, the replication triggers do not fire (i.e., sl_log_{1,2} are empty). This is because the triggers themselves are disabled on the tables (Slony's doing, not mine):</div><div><br></div>pgbench=# select tgname, tgenabled from pg_trigger where tgname like '_slony_example%' order by 2;<br> tgname | tgenabled <br>--------------------------------+-----------<br> _slony_example_truncatetrigger | D<br> _slony_example_logtrigger | D<br> _slony_example_truncatetrigger | D<br> _slony_example_logtrigger | D<br> _slony_example_truncatetrigger | D<br> _slony_example_logtrigger | D<br> _slony_example_truncatedeny | O<br> _slony_example_truncatedeny | O<br> _slony_example_denyaccess | O<br> _slony_example_denyaccess | O<br> _slony_example_denyaccess | O<br> _slony_example_truncatedeny | O<br>(12 rows)<br><div><br></div><div>Granted, my patch is like taking the guardrails off, but I'd argue that the ENABLE/DISABLE syntax on PG's ALTER TABLE command is quite comprehensive, in terms of letting Slony do what it needs to do to make sure the triggers are properly turned on/off, based on the publisher/subscriber status.</div><div><br></div><div>There are other locations where there's this redundancy of 1) setting triggers to fire on replica-only and 2) enforcing replica role identity in the C-side of the trigger code (i.e., logApply):</div><div><br></div><div>pgbench=# \d _slony_example.sl_log_1;<br> Table "_slony_example.sl_log_1"<br> Column | Type | Collation | Nullable | Default <br>------------------+---------+-----------+----------+---------<br> log_origin | integer | | |</div><div>...<snip>...</div><div>Triggers firing on replica only:<br> apply_trigger BEFORE INSERT ON _slony_example.sl_log_1 FOR EACH ROW EXECUTE PROCEDURE _slony_example.logapply('_slony_example')<br></div><div><br></div><div>To avoid inserting more risk, I didn't take those checks out--just enough to get Slony working nicely with Logical Replication.</div><div><br></div><div>With that said, getting Slony to work with Logical Replication (with my patch installed) isn't a trivial task. Between the "set add table" slonik command and the "subscribe set" slonik command, there needs to be a manual execution of "ALTER TABLE ENABLE TRIGGER [ALWAYS|REPLICA]" on each *_logtrigger and *_truncatetrigger for each table, along with an "ALTER TABLE DISABLE TRIGGER apply_trigger" for sl_log_{1,2} on the Slony-provider/LR-subscriber side (node B in my example). But at least with my patch, it becomes *possible* to set up such an architecture.</div><div><br></div><div>Thanks for considering,</div><div>--Richard</div><div><br></div><div><br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
<br>
<br>
> I recently came across a customer who sought to set up Slony on the tail end of a cascaded-replication<br>
> setup:<br>
> Node A -> Logical Replication -> Node B -> Slony -> Node C<br>
> <br>
> We discovered that this does not work because the client (WAL replay process) logs in as a Replica user,<br>
> but logTrigger won't fire unless replication role is Origin.<br>
> <br>
> Seeing that the "replication role == Origin" check is a bit dated, and Slony will enable/disable triggers<br>
> based on subscriber/provider status, I'm thinking that the attached patch might be worth a discussion. <br>
> After all, the "replication role == Origin" check basically negates all the replication role features<br>
> that are now baked into Postgres core.<br>
> <br>
> Please let me know if there are other factors to consider.<br>
> <br>
> Cheers,<br>
> --Richard<br>
> <br>
> <br>
> <br>
></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div><span style="color:rgb(85,85,85);font-family:arial,helvetica,sans-serif;font-size:12px">Richard Yen</span><br></div><span style="color:rgb(85,85,85);font-family:arial,helvetica,sans-serif;font-size:12px">Principal Support Engineer</span><span style="font-family:arial,helvetica,sans-serif;font-size:12px"><br><br></span><span style="color:rgb(85,85,85);font-family:arial,helvetica,sans-serif;font-size:12px">______________________________</span><span style="color:rgb(85,85,85);font-family:arial,helvetica,sans-serif;font-size:12px">______________________________</span><br style="color:rgb(85,85,85);font-family:arial,helvetica,sans-serif;font-size:12px"><div dir="ltr"><font color="#555555" face="monospace, monospace"><span style="font-size:12px">PRIVACY & CONFIDENTIALITY NOTICE</span></font></div><div dir="ltr"><font color="#555555" face="monospace, monospace"><span style="font-size:12px"><br></span></font></div><div dir="ltr"><font color="#555555" face="monospace, monospace"><span style="font-size:12px">Please ensure that any data relative to individual or entity privacy is not in violation of the General Data Protection Regulation Act, as outlined at <a href="https://gdpr-info.eu/" target="_blank">https://gdpr-info.eu/</a>. EnterpriseDB process requires that information which may be relevant to your technical discourse are submitted via either the Customer Portal through secure credentials, or provided via DropBox as provided by EnterpriseDB.</span></font></div><div dir="ltr"><font color="#555555" face="monospace, monospace"><span style="font-size:12px"><br></span></font></div><div dir="ltr"><font color="#555555" face="monospace, monospace"><span style="font-size:12px">This e-mail transmission and any documents, files, or previous e-mail messages appended or attached to it, may contain information that is confidential or legally privileged. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that you must not read this transmission and that any disclosure, copying, printing, distribution, or use of the information contained or attached to this transmission is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify the sender by telephone or return e-mail message and delete the original transmission, its attachments, and any copies without reading or saving in any manner. Thank you.</span></font></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div></div>