Re: [HACKERS] [GENERAL] problems with dropped columns
> > There are (at least) two distinct problems involved here. One is > > getting plpgsql to deal correctly with rowtypes that include dropped > > columns. The other is getting it to react when someone alters a table > > whose rowtype is relied on by already-compiled functions. I'm working on this one...I don't know all the dependencies of things off the top of my head, so it's a little bit painstakign... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] problems with dropped columns
Tom Lane wrote: > Joe Conway <[EMAIL PROTECTED]> writes: > > Taking it a bit further... > > There are (at least) two distinct problems involved here. One is > getting plpgsql to deal correctly with rowtypes that include dropped > columns. The other is getting it to react when someone alters a table > whose rowtype is relied on by already-compiled functions. > > The former problem is just a small matter of programming in plpgsql; > I'm not sure what the best way to do it is, but it's clearly just > plpgsql's issue. The latter problem calls for a ton of infrastructure > that we haven't got :-( Hmm, well... - Keeping timestamps of when the table definition was last changed and when the function was last compiled, and then having the language interpreter check the two before executing the function (and recompile it when the function is out of date) would solve the problem (and require relatively little additional infrastructure), but I would expect the performance hit to be too high to be worth it. - Alternatively, the language compiler could record a dependency between the function and the table (assuming this isn't done already), and be told to recompile the function when the table definition changes. This gets real messy when you're talking about doing this in a transaction, unless the compilation itself is something that can be rolled back (it wouldn't surprise me in the least if the compiled definition is stored in a table already and thus can be rolled back). - Alternatively, recompilation could be made a manual thing, e.g. ALTER FUNCTION blah RECOMPILE. It would still be a win for this to be transaction-capable. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] problems with dropped columns
Joe Conway <[EMAIL PROTECTED]> writes: > Taking it a bit further... There are (at least) two distinct problems involved here. One is getting plpgsql to deal correctly with rowtypes that include dropped columns. The other is getting it to react when someone alters a table whose rowtype is relied on by already-compiled functions. The former problem is just a small matter of programming in plpgsql; I'm not sure what the best way to do it is, but it's clearly just plpgsql's issue. The latter problem calls for a ton of infrastructure that we haven't got :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] problems with dropped columns
Christopher Kings-Lynne wrote: I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Taking it a bit further... CREATE TABLE fk_test (f1 int, f2 int); insert into fk_test(f1, f2) values(1, 21); insert into fk_test(f1, f2) values(2, 22); ALTER TABLE fk_test DROP COLUMN f2; ALTER TABLE fk_test ADD COLUMN f3 int; insert into fk_test(f1, f3) values(3, 33); insert into fk_test(f1, f3) values(4, 34); CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) regression=# ALTER TABLE fk_test DROP COLUMN f3; ALTER TABLE regression=# select * from test(); f1 1 2 3 4 (4 rows) regression=# ALTER TABLE fk_test ADD COLUMN f3 int; ALTER TABLE regression=# select * from test(); WARNING: Error occurred while executing PL/pgSQL function test WARNING: line 5 at return next ERROR: Wrong record type supplied in RETURN NEXT CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) Joe ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] problems with dropped columns
Christopher Kings-Lynne wrote: I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Here is a self contained example using cvs tip: CREATE TABLE fk_test (f1 int, f2 int); insert into fk_test(f1, f2) values(1, 21); insert into fk_test(f1, f2) values(2, 22); ALTER TABLE fk_test DROP COLUMN f2; ALTER TABLE fk_test ADD COLUMN f3 int; insert into fk_test(f1, f3) values(3, 33); insert into fk_test(f1, f3) values(4, 34); regression=# select * from fk_test ; f1 | f3 + 1 | 2 | 3 | 33 4 | 34 (4 rows) CREATE OR REPLACE FUNCTION test() RETURNS SETOF fk_test AS ' DECLARE rec fk_test%ROWTYPE; BEGIN FOR rec IN SELECT * FROM fk_test LOOP RETURN NEXT rec; END LOOP; RETURN; END; ' LANGUAGE 'plpgsql'; regression=# select * from test(); f1 | f3 + 1 | 2 | 3 | 4 | (4 rows) Joe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] problems with dropped columns
I want to fix this bug, however I can't see how the example below is failing... (Obeys dropped columns) I'm not up with my SRFs, so would someone be able to post a concise SQL script that demonstrates the failure? I can see in the code that it should be failing, but I need a demonstrated example... Chris - Original Message - From: "Damjan Pipan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, January 28, 2003 9:36 PM Subject: [GENERAL] problems with dropped columns > Hi! > > I have following problem: > I have created a table with some fields, then I dropped last field (integer) > and added > one extra field (integer). Then I have created a function which returns > record of table > type. I have selected a record from table and returned it, but the values in > last > field are wrong (missing). It looks like that it takes the dropped field > instead of the last field. > > Damjan > > CREATE OR REPLACE FUNCTION damjan_test111(integer) RETURNS public.fk_test AS > ' > DECLARE > rec fk_test%ROWTYPE; > siteid ALIAS FOR $1; > BEGIN > FOR rec IN SELECT * FROM public.fk_test WHERE > i = siteid LOOP > RETURN rec; > END LOOP; > END; > ' LANGUAGE 'plpgsql'; > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
