Re: For each statement trigger and update table
Erik Brandsberg <[email protected]> Mon, 6 Jan 2020 08:53:26 -0500
| Newsgroups | gmane.comp.db.postgresql.sql |
|---|---|
| Message-ID | <CAFcck8EAY_48azKkOMT14buwG6wmuT4-KTQyEA-NF71PevxQgA@mail.gmail.com> |
--0000000000006e5d6e059b78fcf7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Have you printed out what the value of pg_trigger_depth() is? Without testing, my guess is that it is starting with a value of 1, not 0, and as such prevents execution from the start. On Fri, Jan 3, 2020 at 10:46 PM Mike Martin <[email protected]> wrote: > This is the function > > CREATE OR REPLACE FUNCTION public.tagfile_upd_su() > RETURNS trigger > LANGUAGE 'plpgsql' > COST 100 > VOLATILE NOT LEAKPROOF > AS $BODY$ > BEGIN > > WITH arrfile AS(SELECT > fileid,tagfile,(regexp_split_to_array(tagfile,'/'))[2:] filearr1 FROM > tagfile_new), > arrfile2 AS(SELECT > fileid,tagfile,filearr1[1:cardinality(filearr1)-1]||regexp_matches(filear= r1[cardinality(filearr1)],'(.*)\.(.*)') > filearr > FROM arrfile) > > UPDATE tagfile tf SET filearr=3Da2.filearr > FROM arrfile2 a2 > WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.fileid=3Daf.fileid AND > af.tagfile !=3D tf.tagfile); > END > > Would really prefer not to have a row level function. The Insert version > works perfefectly. > I have tried using pg_trigger_depth, but that stops the trigger running a= t > all > > Trigger definition is > > CREATE TRIGGER tagfile_uas > AFTER UPDATE > ON public.tagfile > REFERENCING OLD TABLE tagfile_old NEW TABLE AS tagfile_new > FOR EACH STATEMENT > --WHEN (pg_trigger_depth() < 1) > EXECUTE PROCEDURE public.tagfile_upd_su() > ; > (please note commented out pg_trigger_depth which stopped trigger firing > at all > > On Sat, 4 Jan 2020 at 00:26, Rene Romero Benavides < > [email protected]> wrote: > >> Mike, please include to the mailing list as well, so others can help you >> out too. Why do you need the trigger to be FOR EACH STATEMENT? so I can >> understand your use case, even if it's simple stuff, please share with u= s >> your code. >> >> On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavides < >> [email protected]> wrote: >> >>> Oh, so you're defining transition relations (REFERENCING NEW TABLE, OLD >>> TABLE ) as in here? >>> >>> CREATE TRIGGER paired_items_update >>> AFTER UPDATE ON paired_items >>> REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab >>> FOR EACH ROW >>> EXECUTE FUNCTION check_matching_pairs(); >>> >>> >>> On Fri, Jan 3, 2020 at 5:55 PM Mike Martin <[email protected]> wrote= : >>> >>>> According to the docs, not possible to use a transition table and >>>> column list together >>>> >>>> On Fri, 3 Jan 2020, 23:39 Rene Romero Benavides, < >>>> [email protected]> wrote: >>>> >>>>> > I can give code when I get home, but it's pretty simple stuff >>>>> please do so, along with your trigger definition. Are you aware that >>>>> you can define your update trigger to fire on a specific column? >>>>> >>>>> https://www.postgresql.org/docs/current/sql-createtrigger.html >>>>> >>>>> For UPDATE events, it is possible to specify a list of columns using >>>>> this syntax: >>>>> >>>>> UPDATE OF column_name1 [, column_name2 ... ] >>>>> >>>>> >>>>> >>>>> On Fri, Jan 3, 2020 at 5:21 PM Mike Martin <[email protected]> >>>>> wrote: >>>>> >>>>>> Not sure if this is possible >>>>>> Basically I want to have a trigger which updates an array column in >>>>>> the same table when a column is updated >>>>>> This works as a row level trigger, but not as per statement >>>>>> I have hit the recursive issue (where update fires update trigger >>>>>> which fires etc) >>>>>> According to the docs I cannot use columns and relative tables >>>>>> together >>>>>> >>>>>> So any suggestions? I can give code when I get home, but it's pretty >>>>>> simple stuff >>>>>> >>>>> >>>>> >>>>> -- >>>>> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. >>>>> Thomas Alva Edison >>>>> http://pglearn.blogspot.mx/ >>>>> >>>>> >>> >>> -- >>> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. >>> Thomas Alva Edison >>> http://pglearn.blogspot.mx/ >>> >>> >> >> -- >> El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n. >> Thomas Alva Edison >> http://pglearn.blogspot.mx/ >> >> --=20 *Erik Brandsberg* [email protected] www.heimdalldata.com +1 (866) 433-2824 x 700 [image: AWS Competency Program] <https://aws.amazon.com/partners/find/partnerdetails/?n=3DHeimdall%20Data&i= d=3D001E000001d9pndIAA> --0000000000006e5d6e059b78fcf7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable <div dir=3D"ltr">Have you printed out what the value of pg_trigger_depth() = is?=C2=A0 Without testing, my guess is that it is starting with a value of = 1, not 0, and as such prevents execution from the start.</div><br><div clas= s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020= at 10:46 PM Mike Martin <<a href=3D"mailto:[email protected]">mike@r= edtux.plus.com</a>> wrote:<br></div><blockquote class=3D"gmail_quote" st= yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd= ing-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr"><div>This is the function</= div><div><br></div><div>CREATE OR REPLACE FUNCTION public.tagfile_upd_su()<= br>=C2=A0 =C2=A0 RETURNS trigger<br>=C2=A0 =C2=A0 LANGUAGE 'plpgsql'= ;<br>=C2=A0 =C2=A0 COST 100<br>=C2=A0 =C2=A0 VOLATILE NOT LEAKPROOF<br>AS $= BODY$<br>=C2=A0 =C2=A0 BEGIN<br><br> WITH arrfile AS(SELECT fileid,tagfile= ,(regexp_split_to_array(tagfile,'/'))[2:] filearr1 FROM tagfile_new= ),<br>arrfile2 AS(SELECT fileid,tagfile,filearr1[1:cardinality(filearr1)-1]= ||regexp_matches(filearr1[cardinality(filearr1)],'(.*)\.(.*)') file= arr<br>FROM arrfile)<br><br>UPDATE tagfile =C2=A0tf SET filearr=3Da2.filear= r<br>FROM arrfile2 a2<br>WHERE EXISTS (SELECT 1 FROM arrfile2 af WHERE tf.f= ileid=3Daf.fileid AND af.tagfile !=3D tf.tagfile);</div><div>END</div><div>= <br></div><div>Would really prefer not to have a row level function. The In= sert version works perfefectly.</div><div>I have tried using pg_trigger_dep= th, but that stops the trigger running at all</div><div><br></div><div>Trig= ger definition is <br></div><div><br></div><div>CREATE TRIGGER tagfile_uas<= br>=C2=A0 =C2=A0 AFTER UPDATE<br>=C2=A0 =C2=A0 ON public.tagfile<br>=C2=A0 = =C2=A0 REFERENCING OLD TABLE tagfile_old NEW TABLE AS tagfile_new<br>=C2=A0= =C2=A0 FOR EACH STATEMENT<br> --WHEN (pg_trigger_depth() < 1)<br>=C2=A0= =C2=A0 EXECUTE PROCEDURE public.tagfile_upd_su()<br> ;</div><div>(please n= ote commented out pg_trigger_depth which stopped trigger firing at all<br><= /div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_a= ttr">On Sat, 4 Jan 2020 at 00:26, Rene Romero Benavides <<a href=3D"mail= to:[email protected]" target=3D"_blank">[email protected]</a>&g= t; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0p= x 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div d= ir=3D"ltr">Mike, please include to the mailing list as well, so others can = help you out too. Why do you need the trigger to be FOR EACH STATEMENT? so = I can understand your use case, even if it's simple stuff, please share= with us your code.=C2=A0</div><br><div class=3D"gmail_quote"><div dir=3D"l= tr" class=3D"gmail_attr">On Fri, Jan 3, 2020 at 6:06 PM Rene Romero Benavid= es <<a href=3D"mailto:[email protected]" target=3D"_blank">rene.ro= [email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote" = style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pa= dding-left:1ex"><div dir=3D"ltr">Oh, so you're defining transition rela= tions (REFERENCING NEW TABLE, OLD TABLE ) as in here?<div><pre style=3D"box= -sizing:border-box;font-family:monospace,monospace;font-size:14.4px;overflo= w:auto;color:rgb(13,10,11);border-radius:0.25rem;border:1px solid rgb(206,2= 12,218);margin-top:1rem;margin-bottom:1rem;background-color:rgb(248,249,250= );padding:0.8rem">CREATE TRIGGER paired_items_update AFTER UPDATE ON paired_items REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab FOR EACH ROW EXECUTE FUNCTION check_matching_pairs();</pre></div></div><br><div clas= s=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Jan 3, 2020= at 5:55 PM Mike Martin <<a href=3D"mailto:[email protected]" target= =3D"_blank">[email protected]</a>> wrote:<br></div><blockquote class= =3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg= b(204,204,204);padding-left:1ex"><div dir=3D"auto">According to the docs, n= ot possible to use a transition table and column list together=C2=A0</div><= br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri,= 3 Jan 2020, 23:39 Rene Romero Benavides, <<a href=3D"mailto:rene.romero= [email protected]" target=3D"_blank">[email protected]</a>> wrote:<br><= /div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo= rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><di= v dir=3D"ltr">>=C2=A0 I can give code when I get home, but it's pret= ty simple stuff=C2=A0</div><div>please do so, along with your trigger defin= ition. Are you aware that you can define your update trigger to fire on a s= pecific column?</div><div><br></div><div><a href=3D"https://www.postgresql.= org/docs/current/sql-createtrigger.html" rel=3D"noreferrer" target=3D"_blan= k">https://www.postgresql.org/docs/current/sql-createtrigger.html</a>=C2=A0= =C2=A0<br></div><div><p style=3D"box-sizing:border-box;color:rgb(13,10,11);= font-family:"Open Sans",sans-serif;font-size:14.4px;margin:1rem 0= px 1rem 2rem">For=C2=A0<tt style=3D"box-sizing:border-box;border-radius:0.2= 5rem;margin:0.6rem 0px;font-size:0.9rem;color:inherit;background-color:rgb(= 248,249,250)">UPDATE</tt>=C2=A0events, it is possible to specify a list of = columns using this syntax:</p><pre style=3D"box-sizing:border-box;font-fami= ly:monospace,monospace;font-size:14.4px;overflow:auto;color:rgb(13,10,11);b= order-radius:0.25rem;border:1px solid rgb(206,212,218);margin-top:1rem;marg= in-bottom:1rem;margin-left:2rem;background-color:rgb(248,249,250);padding:0= .8rem">UPDATE OF <tt style=3D"box-sizing:border-box;font-weight:900;font-st= yle:italic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.9rem;color:i= nherit">column_name1</tt> [, <tt style=3D"box-sizing:border-box;font-weight= :900;font-style:italic;border-radius:0.25rem;margin:0.6rem 0px;font-size:0.= 9rem;color:inherit">column_name2</tt> ... ]</pre></div><div><br></div></div= ><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fr= i, Jan 3, 2020 at 5:21 PM Mike Martin <<a href=3D"mailto:[email protected]= s.com" rel=3D"noreferrer" target=3D"_blank">[email protected]</a>> wr= ote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px= 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D= "auto">Not sure if this is possible<div dir=3D"auto">Basically I want to ha= ve a trigger which updates an array column in the same table when a column = is updated</div><div dir=3D"auto">This works as a row level trigger, but no= t as per statement</div><div dir=3D"auto">I have hit the recursive issue (w= here update fires update trigger which fires etc)</div><div dir=3D"auto">Ac= cording to the docs I cannot use columns and relative tables together</div>= <div dir=3D"auto"><br></div><div dir=3D"auto">So any suggestions? I can giv= e code when I get home, but it's pretty simple stuff=C2=A0</div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" rel=3D"noreferrer" target= =3D"_blank">http://pglearn.blogspot.mx/</a><br><div style=3D"padding:0px;ma= rgin-left:0px;margin-top:0px;overflow:hidden;color:black;font-size:10px;tex= t-align:left;line-height:130%"></div><div><br></div></div> </blockquote></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" target=3D"_blank">http://p= glearn.blogspot.mx/</a><br><div style=3D"padding:0px;margin-left:0px;margin= -top:0px;overflow:hidden;color:black;font-size:10px;text-align:left;line-he= ight:130%"></div><div><br></div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= >El genio es 1% inspiraci=C3=B3n y 99% transpiraci=C3=B3n.<br>Thomas Alva E= dison<br><a href=3D"http://pglearn.blogspot.mx/" target=3D"_blank">http://p= glearn.blogspot.mx/</a><br><div style=3D"padding:0px;margin-left:0px;margin= -top:0px;overflow:hidden;color:black;font-size:10px;text-align:left;line-he= ight:130%"></div><div><br></div></div> </blockquote></div></div> </blockquote></div><br clear=3D"all"><div><br></div>-- <br><div dir=3D"ltr"= class=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div= dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"l= tr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div>= <div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir= =3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr"><div><div dir=3D"ltr">= <div><div><div><font color=3D"#444444"><font size=3D"4"><b>Erik Brandsberg<= /b></font></font></div></div><span style=3D"color:rgb(153,153,153)"><span s= tyle=3D"color:rgb(153,153,153)"><a href=3D"mailto:[email protected]" ta= rget=3D"_blank">[email protected]</a></span><font size=3D"2"><span><spa= n><font size=3D"2"><br></font></span></span><img src=3D"https://docs.google= .com/uc?export=3Ddownload&id=3D0B-iS1Keqp-YjdjQ0cG1IUTVHcWM&revid= =3D0B-iS1Keqp-YjRlBhUmZ2S3Nic2YvQmRGZ0NoOTRkWGpoTVBVPQ" width=3D"137" heigh= t=3D"21"><br></font></span></div><span style=3D"color:rgb(153,153,153)"><sp= an style=3D"color:rgb(153,153,153)"><a href=3D"http://www.heimdalldata.com"= target=3D"_blank">www.heimdalldata.com</a></span><br>+1 (866) 433-2824 x 7= 00</span></div></div><div dir=3D"ltr"><a href=3D"https://aws.amazon.com/par= tners/find/partnerdetails/?n=3DHeimdall%20Data&id=3D001E000001d9pndIAA"= target=3D"_blank"><img src=3D"https://d2908q01vomqb2.cloudfront.net/77de68= daecd823babbb58edb1c8e14d7106e83bb/2018/02/07/AWS-Competency_thumbnail-300x= 150.png" alt=3D"AWS Competency Program"></a></div></div></div></div></div><= /div></div></div></div></div></div></div></div></div></div></div></div></di= v></div></div></div></div></div></div></div></div></div></div></div></div><= /div> --0000000000006e5d6e059b78fcf7--