Re: Conditional INSERT - Mailing list pgsql-general

On 3/15/19 4:23 PM, Ken Tanzer wrote:
> On Fri, Mar 15, 2019 at 11:59 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/15/19 11:54 AM, basti wrote:
>      > this is a dns database, and the client is update the
>     _acme-challenge for
>      > LE certificates. I don't want that the client can insert "any"
>     txt record.
>      > the client should only insert data if the hostname start with
>      > _acme-challenge. i have no control on client.
>      >
>      > i have try this rule but the server reject this with a endless loop:
> 
>     To borrow a quote:
> 
>     "I had a problem so I decided to use a rule, now I have two problems."
> 
>     Do not use a rule. As suggested upstream use a BEFORE INSERT trigger,
>     you will be a lot happier.
> 
>      >
>      > CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
>      >      WHERE NEW.hostname like '_acme-challenge%'
>      >      DO INSERT INTO t_dnsadmin_records_txt VALUES (
>      >                                      NEW.domainid,
>      >                                      NEW.hostname,
>      >                                      NEW.txtdata
>      >                                  );
>      >
>      >
> 
> 
> Just curious, but wanted to follow up on whether rules are 
> across-the-board discouraged?  I've seen disparaging comments about 
> them, but I don't see any indication of that on the create rule page.

See here:
https://www.postgresql-archive.org/Deprecating-RULES-td5727689.html

The rumor crops periodically that they will be deprecated. I personally 
do not see that happening any time soon.

My issue with rules is this:

https://www.postgresql.org/docs/11/rules.html

If you can understand what really goes on in the above you are ahead of 
me. Given that my experience is that they do the unexpected as often as 
the expected so I stay away from them. Triggers I understand even when 
they error, which is the important part.

> 
> The other suggestion in this thread--a foreign key--will throw an 
> error.  Your suggestion of a before trigger might well be better (and if 
> so, why?), but is there anything particularly wrong or bad about using a 
> rule that would actually work?  Something along these lines:

The trouble is simple rarely stays simple and following logic is a lot 
easier in a trigger function then a rule. This is down mostly to the 
query rewrite that goes on in a rule. That is border line, if not 
outright, black magic. Try to follow what happens here:

https://www.postgresql.org/docs/11/rules-update.html

41.4.1.1. A First Rule Step by Step

> 
> CREATE RULE insert_acme AS ON INSERT TO t_dnsadmin_records_txt
> WHERE NOT NEW.hostname like '_acme-challenge%'
> DO INSTEAD NOTHING;
> 
> Thanks,
> Ken
> 
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com