Thread: Re: [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" <damjan.pipan@siol.net> To: <pgsql-general@postgresql.org> 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 >
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
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
Joe Conway <mail@joeconway.com> 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
Tom Lane wrote: > Joe Conway <mail@joeconway.com> 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 kevin@sysexperts.com
Is there any TODO's here? --------------------------------------------------------------------------- Tom Lane wrote: > Joe Conway <mail@joeconway.com> 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 > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> > 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