Обсуждение: Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
От
Daniel Schuchardt
Дата:
Hi, here is a testcase: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$ BEGINCREATE TEMP SEQUENCE test;PERFORM testseq1();DROP SEQUENCE test;RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$ DECLARE I INTEGER; BEGINI:= nextval('test');RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); -- this works fine. SELECT testseq(); ERROR: could not open relation with OID 21152 CONTEXT: PL/pgSQL function "testseq1" line 3 at assignment SQL statement "SELECT testseq1()" PL/pgSQL function "testseq" line 3 at perform Greetings, Daniel.
Re: Error working with Temporary Sequences in plpgsql in 8.1 (8.0 works fine)
От
Jaime Casanova
Дата:
On 1/17/06, Daniel Schuchardt <daniel_schuchardt@web.de> wrote: > Hi, > > here is a testcase: > > CREATE OR REPLACE FUNCTION testseq() > RETURNS void AS > $BODY$ > BEGIN > CREATE TEMP SEQUENCE test; > PERFORM testseq1(); > DROP SEQUENCE test; > RETURN; > END; $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > ALTER FUNCTION testseq() OWNER TO postgres; > > > CREATE OR REPLACE FUNCTION testseq1() > RETURNS void AS > $BODY$ > DECLARE I INTEGER; > BEGIN > I:= nextval('test'); > RETURN; > END; $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > ALTER FUNCTION testseq1() OWNER TO postgres; > > > SELECT testseq(); > > -- this works fine. > > SELECT testseq(); > > > ERROR: could not open relation with OID 21152 > CONTEXT: PL/pgSQL function "testseq1" line 3 at assignment > SQL statement "SELECT testseq1()" > PL/pgSQL function "testseq" line 3 at perform > > > > Greetings, > > Daniel. > try this way: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$BEGIN EXECUTE 'CREATE TEMP SEQUENCE test'; PERFORM testseq1(); DROP SEQUENCE test; RETURN;END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$DECLARE I INTEGER;BEGIN EXECUTE 'select nextval(''test'')' INTO I; raise notice '%', I; RETURN;END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); SELECT testseq(); is the same problem as with temp tables, you must put their creation, and in this case even the nextval in an execute... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)
On Tue, Jan 17, 2006 at 01:28:03PM -0500, Jaime Casanova wrote: > is the same problem as with temp tables, you must put their creation, > and in this case even the nextval in an execute... Curious that it works in 8.0, though. I wonder if the failure in 8.1 is an artifact of changing sequence functions like nextval() to take a regclass argument (the sequence OID) instead of a text argument (the sequence name); that would affect what gets put in the function's cached plan. -- Michael Fuhr
A nice workaraound because EXECUTE 'select nextval(''test'')' INTO I; doesnt work in 8.0 seems to be: myid:=nextval('stvtrsid_seq'::TEXT); This seems to work in every case. Daniel Jaime Casanova schrieb: try this way: CREATE OR REPLACE FUNCTION testseq() RETURNS void AS $BODY$BEGIN EXECUTE 'CREATE TEMP SEQUENCE test'; PERFORM testseq1(); DROP SEQUENCE test; RETURN;END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq() OWNER TO postgres; CREATE OR REPLACE FUNCTION testseq1() RETURNS void AS $BODY$DECLARE I INTEGER;BEGIN EXECUTE 'select nextval(''test'')' INTO I; raise notice '%', I; RETURN;END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; ALTER FUNCTION testseq1() OWNER TO postgres; SELECT testseq(); SELECT testseq(); is the same problem as with temp tables, you must put their creation, and in this case even the nextval in an execute... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;)