v9.1, DROP TRIGGER IF EXISTS behaving oddly

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 14 January 2016 at 11:32, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

Seems to have been changed in
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b152c6cd0de1827ba58756e24e18110cf902182a

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 14 January 2016 at 12:08, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general