Thread: 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 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, skippingObserved Output:
ERROR:Â Â relation "udf_customer_update_trigger" does not existEnvironment:
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.
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 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)
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