Обсуждение: 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;
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;
Josh Kupershmidt <schmiddy@gmail.com> writes:
> 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.
> Can anyone say whether the error message I'm seeing is valid?
It's unsurprising, anyway. The code firing the trigger would be holding
the relation open. I'm not sure whether there's a good reason for
referring to the table by OID instead of name there, but this is a
pretty low-level failure anyway.
There are any number of reasons why we'd not be likely to try to support
this. What if the trigger tries to refer to NEW or OLD afterwards, when
the information about the table's rowtype is already gone? What if
there are more trigger firings pending for the table?
regards, tom lane