Thread: BUG #7641: ERROR: must specify relation and object name when function contains DROP TRIGGER
BUG #7641: ERROR: must specify relation and object name when function contains DROP TRIGGER
From
sergey@booksys.com
Date:
The following bug has been logged on the website: Bug reference: 7641 Logged by: Sergey Email address: sergey@booksys.com PostgreSQL version: 9.2.1 Operating system: Windows XP / Ubuntu 10.04 (Lucid) Description: = Postgres 9.2.1 Reproduced the problem on Windows XP and Ubuntu 10.04 Steps to reproduce: 1. Create a new database: = CREATE DATABASE test; = Connect to the new database. 2. CREATE TABLE test1 (id int); 3. = CREATE OR REPLACE FUNCTION drop_trigger_test() RETURNS void AS $$ BEGIN DROP TRIGGER IF EXISTS invalid_trigger ON test1; END; $$ LANGUAGE plpgsql; 4. SELECT drop_trigger_test(); Result: NOTICE: trigger "invalid_trigger" for table "test1" does not exist, skipping CONTEXT: SQL statement "DROP TRIGGER IF EXISTS invalid_trigger ON test1" PL/pgSQL function drop_trigger_test() line 3 at SQL statement 5. SELECT drop_trigger_test(); Result: ERROR: must specify relation and object name CONTEXT: SQL statement "drop trigger if exists invalid_trigger on test2" PL/pgSQL function drop_trigger_test2() line 3 at SQL statement For some reason I can run this function without error only once on a fresh connection. Did not have this problem in 8.4.x and 9.1.6
Re: BUG #7641: ERROR: must specify relation and object name when function contains DROP TRIGGER
From
Tom Lane
Date:
sergey@booksys.com writes: > CREATE OR REPLACE FUNCTION drop_trigger_test() RETURNS void AS $$ > BEGIN > DROP TRIGGER IF EXISTS invalid_trigger ON test1; > END; $$ LANGUAGE plpgsql; > 4. SELECT drop_trigger_test(); > Result: > NOTICE: trigger "invalid_trigger" for table "test1" does not exist, > skipping > CONTEXT: SQL statement "DROP TRIGGER IF EXISTS invalid_trigger ON test1" > PL/pgSQL function drop_trigger_test() line 3 at SQL statement > 5. SELECT drop_trigger_test(); > Result: > ERROR: must specify relation and object name > CONTEXT: SQL statement "drop trigger if exists invalid_trigger on test2" > PL/pgSQL function drop_trigger_test2() line 3 at SQL statement > For some reason I can run this function without error only once on a fresh > connection. Did not have this problem in 8.4.x and 9.1.6 That's a bug all right --- the does_not_exist_skipping() function thinks it's okay to trash its input data structure, so the DropStmt is corrupted for next time. Will fix, thanks for the report! regards, tom lane
Re: BUG #7641: ERROR: must specify relation and object name when function contains DROP TRIGGER
From
Sergey
Date:
Tom, There is also a minor bug when trying to do same thing with RULE (it doesn't produce an error but the notice is not correct when function is called for the second time): CREATE OR REPLACE FUNCTION drop_rule_test() RETURNS void AS $$ BEGIN DROP RULE IF EXISTS invalid_rule ON test1; END; $$ LANGUAGE plpgsql; SELECT drop_rule_test(); NOTICE: rule "invalid_rule" for relation "test1" does not exist, skipping CONTEXT: SQL statement "DROP RULE IF EXISTS invalid_rule ON test1" PL/pgSQL function drop_rule_test() line 3 at SQL statement SELECT drop_rule_test(); NOTICE: rule "test1" for relation "" does not exist, skipping CONTEXT: SQL statement "DROP RULE IF EXISTS invalid_rule ON test1" PL/pgSQL function drop_rule_test() line 3 at SQL statement Thanks, Sergey On 11/8/2012 10:14 AM, Tom Lane wrote: > sergey@booksys.com writes: >> CREATE OR REPLACE FUNCTION drop_trigger_test() RETURNS void AS $$ >> BEGIN >> DROP TRIGGER IF EXISTS invalid_trigger ON test1; >> END; $$ LANGUAGE plpgsql; >> 4. SELECT drop_trigger_test(); >> Result: >> NOTICE: trigger "invalid_trigger" for table "test1" does not exist, >> skipping >> CONTEXT: SQL statement "DROP TRIGGER IF EXISTS invalid_trigger ON test1" >> PL/pgSQL function drop_trigger_test() line 3 at SQL statement >> 5. SELECT drop_trigger_test(); >> Result: >> ERROR: must specify relation and object name >> CONTEXT: SQL statement "drop trigger if exists invalid_trigger on test2" >> PL/pgSQL function drop_trigger_test2() line 3 at SQL statement >> For some reason I can run this function without error only once on a fresh >> connection. Did not have this problem in 8.4.x and 9.1.6 > That's a bug all right --- the does_not_exist_skipping() function thinks > it's okay to trash its input data structure, so the DropStmt is > corrupted for next time. Will fix, thanks for the report! > > regards, tom lane