Обсуждение: BUG #7641: ERROR: must specify relation and object name when function contains DROP TRIGGER

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

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

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