On Mon, Feb 07, 2011 at 02:31:49PM +0100, Dimitri Fontaine wrote:
> strk <[email protected]> writes:
> > Do you have an idea on how to further debug this ?
>
> That usually goes with providing a self-contained test case⦠that is a
> minimum script that creates the function(s) and calls them.
I've finally completed the debugging phase and have
a minimal self-contained testcase showing the problem.
It has to do with INITIALLY DEFERRED constraints.
The testcase is attached.
--strk;
() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html
-- {
CREATE OR REPLACE FUNCTION makeTopo()
RETURNS void AS $$
BEGIN
CREATE SCHEMA topo;
CREATE TABLE topo.edge (
id int4 PRIMARY KEY,
ref int4,
FOREIGN KEY(ref) REFERENCES topo.edge(id)
DEFERRABLE INITIALLY DEFERRED);
-- NOTE: returning here "fixes" the bug
--RETURN;
INSERT INTO topo.edge VALUES (1,1);
END
$$ language 'plpgsql' VOLATILE STRICT;
-- }
DROP SCHEMA IF EXISTS topo CASCADE;
BEGIN;
SELECT makeTopo();
SELECT 'topo.edge'::regclass::oid;
-- NOTE: this fixes it!
-- SET CONSTRAINTS ALL IMMEDIATE;
-- ERROR: could not open relation with OID XXXXXXX
-- NOTE: XXXXX is 'topo.edge'::regclass::oid
DROP TABLE topo.edge CASCADE;
COMMIT;
-- NOTE: doing it here is fine
--DROP TABLE topo.edge CASCADE;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
