Re: v9.1, DROP TRIGGER IF EXISTS behaving oddly

On 14 January 2016 at 12:08, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 01/13/2016 02:51 PM, David Rowley wrote:
>
>> On 14 January 2016 at 11:32, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 01/13/2016 02:24 PM, Tom Lane wrote:
>>
>> "Williamson, Michael" <Michael(dot)Williamson(at)tamucc(dot)edu
>> <mailto:Michael(dot)Williamson(at)tamucc(dot)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

I assumed this part was a mistake in the post by Michael. I tried this on
9.1.19 and I correctly get the name of the table rather than the name of
the trigger. So even in the unlikely event that this was a bug, it's
working in the latest 9.1 minor release:

# drop trigger if exists test_trigger on testtable;
ERROR: relation "testtable" does not exist
# select version();
version

----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.1.19 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
5.2.1-22 ubuntu2) 5.2.1 20151010, 64-bit
(1 row)

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services