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(at)yahoo(dot)com> wrote:

> May I know how I can use trigger technique, to remove the table itself,
> when after delete operation, there is 0 row in the table?
>
>
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;