ERROR: relation xxx is still open (Re: Use Trigger to Remove Table ... )

On Mon, Feb 1, 2010 at 10:38 PM, Yan Cheng Cheok <yccheok@yahoo.com> wrote:

For the record, I think having a trigger drop a table automatically when it's empty is probably a bad idea. But I tried it out anyways, and got a surprising:
ERROR:  relation 16400 is still open
when the trigger function attempted to drop the table.

I searched a bit in an attempt to learn if this error message is bogus or not for this case, and found a few threads such as:
http://archives.postgresql.org/pgsql-novice/2007-02/msg00099.php
which suggest that having a cursor open on the table, or a PL/pgSQL FOR-IN-SELECT loop open would be a legitimate(?) cause for the error. In this case, I'm just using a:
SELECT COUNT(*) INTO num_rows ...
statement. Can anyone say whether the error message I'm seeing is valid? I've tested on CVS head and 8.3.4 and got the same error.

Josh

--
-- Test case below:

BEGIN;

CREATE TABLE mytable (name text PRIMARY KEY);

CREATE OR REPLACE FUNCTION "drop_mytable_ifempty"()
RETURNS trigger AS $$
DECLARE
    num_rows  int;
BEGIN
    SELECT COUNT(*) INTO num_rows FROM "mytable";
    IF num_rows = 0 THEN
        RAISE NOTICE 'Dropping mytable!';
        DROP TABLE "mytable";
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER "drop_mytable_ifempty_trg" AFTER DELETE ON "mytable"
  FOR EACH ROW EXECUTE PROCEDURE "drop_mytable_ifempty"();

INSERT INTO "mytable" ("name" ) VALUES ('joe2');
INSERT INTO "mytable" ("name" ) VALUES ('joe');
DELETE FROM "mytable" WHERE name = 'joe';
DELETE FROM "mytable" WHERE name = 'joe2';

ROLLBACK;