Re: v9.1, DROP TRIGGER IF EXISTS behaving oddly - Mailing list pgsql-general

Please excuse my mistake.

We were dropping a view that had the trigger on it beforehand, then in
trying to re-build the entire schema had scripts that attempted to drop
trigger if they existed before re-creating the view and triggers.  I
over-sanitized the example I posted and made it unclear.

I did some more testing and DROP TRIGGER IF EXISTS indeed works as
expected if the table/view is there and the trigger is not there.

As a follow-up, it would be nice if "IF EXISTS" could apply to either
the trigger or the relation it is applied to, as I don't think a
trigger can't exist without a relation to apply it to. 

Michael


On Wed, 2016-01-13 at 15:08 -0800, Adrian Klaver wrote:
> On 01/13/2016 02:51 PM, David Rowley wrote:
> > On 14 January 2016 at 11:32, Adrian Klaver <adrian.klaver@aklaver.c
> om
> > <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 01/13/2016 02:24 PM, Tom Lane wrote:
> >
> >         "Williamson, Michael" <Michael.Williamson@tamucc.edu
> >         <mailto:Michael.Williamson@tamucc.edu>> writes:
> >
> >             I'm attempting to drop a trigger that may or may not
> exist,
> >             so am using
> >             the "IF EXISTS" clause. Â This works fine for tables,
> views,
> >             functions,
> >             domains, and types, but for some reason seems to be
> ignored for
> >             triggers. Â I'd expect to see more about this online if
> it
> >             were a bug,
> >             so I'm thinking I may be missing something obvious.
> >
> >
> >             Example:
> >             DROP TRIGGER IF EXISTS udf_customer_update_trigger ON
> customer;
> >
> >
> >             Expected Output:
> >             NOTICE:Â Â trigger "udf_customer_update_trigger" does
> not
> >             exist, skipping
> >
> >
> >             Observed Output:
> >             ERROR:Â Â relation "udf_customer_update_trigger" does
> not exist
> >
> >
> >             Environment:
> >             CentOS 6.6
> >             postgresql91-server-9.1.14-1PGDG.rhel6.x86_64
> >
> >
> >         This has worked the way you're imagining since (I think)
> 9.4.
> >         Before
> >         that the "if exists" semantics only applied to the trigger
> itself,
> >         not to the relation.
> >
> >
> >     Alright now I am confused. Other then changing table to
> table_name I
> >     am not seeing where the below changed. In both cases a NOTICE
> is
> >     supposed to be raised.
> >
> >     http://www.postgresql.org/docs/9.1/interactive/sql-droptrigger.
> html
> >
> >     http://www.postgresql.org/docs/9.4/interactive/sql-droptrigger.
> html
> >
> >
> > Seems to have been changed in
> > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c
> 6cd0de1827ba58756e24e18110cf902182a
> 
> I will take a look at this at some point. The part that has me
> confused 
> from the original post is this:
> 
> ERROR:  relation "udf_customer_update_trigger" does not exist
> 
> If the ERROR is because the table does not exist, why not?:
> 
> ERROR:  relation "customer" does not exist
> 
> >
> > Perhaps that commit should have also made changes to the documents
> to
> > change things such as:
> >
> > Do not throw an error if the trigger does not exist. A notice is
> issued
> > in this case.
> >
> > To
> >
> > Do not throw an error if the trigger or table does not exist. A
> notice
> > is issued in this case.
> >
> > --
> >   David Rowley http://www.2ndQuadrant.com/
> >   PostgreSQL Development, 24x7 Support, Training & Services
> 
>