Обсуждение: v9.1, DROP TRIGGER IF EXISTS behaving oddly

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


Thanks,
Michael

"Williamson, Michael" <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.

            regards, tom lane

On 01/13/2016 02:14 PM, Williamson, Michael wrote:
> 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
>

Is that happening on all triggers you are trying to DROP, or just this one?

If just this one, you might want to check to see if the trigger was
created with a quoted mixed case name and therefore needs to have the
exact case quoted when used in the DROP TRIGGER. Something else to look
at is whether there is more then one customer table and you need to
schema qualify the name.

>
> Thanks,
> Michael
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/13/2016 02:24 PM, Tom Lane wrote:
> "Williamson, Michael" <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

>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/13/2016 02:51 PM, David Rowley wrote:
> On 14 January 2016 at 11:32, Adrian Klaver <adrian.klaver@aklaver.com
> <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=b152c6cd0de1827ba58756e24e18110cf902182a

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


--
Adrian Klaver
adrian.klaver@aklaver.com

On 01/13/2016 03:25 PM, Williamson, Michael wrote:

Ccing list
>>
>> Is that happening on all triggers you are trying to DROP, or just
>> this one?
>
> All, consistently.
>>
>> If just this one, you might want to check to see if the trigger was
>> created with a quoted mixed case name and therefore needs to have
>> the
>> exact case quoted when used in the DROP TRIGGER. Something else to
>> look
>> at is whether there is more then one customer table and you need to
>> schema qualify the name.
>>
>
> The triggers are all being defined in unquoted lowercase.  Also, these
> triggers happen call functions, but I doubt that is relevant.

So is the below the actual error you are getting:

Observed Output:
ERROR:  relation "udf_customer_update_trigger" does not exist

>
> Michael
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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
> 
>

On 01/14/2016 01:47 PM, Williamson, Michael wrote:
> 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.

That is the part Tom and David where trying to explain to me and I was
not getting, the behavior has changed.

Some testing revealed this:

test=# select version();
                                                              version


----------------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.0.20 on i686-pc-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)


test=# drop trigger if exists test_trg on test_tbl;
ERROR:  relation "test_tbl" does not exist


test=# select version();
                                                            version


-----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.4.5 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.3 20140627 [gcc-4_8-branch revision 212064], 32-bit
(1 row)


test=# drop trigger if exists test_trg on test_tbl;
NOTICE:  relation "test_tbl" does not exist, skipping
DROP TRIGGER

In either case though IF EXISTS did the right thing, so I am wondering
if upgrading to a more recent version of 9.1 will help you.


>
> Michael
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com