Обсуждение: problems selecting from altered table
Hi! I noticed that some of the functions I created don't work anymore. So I tired to reproduce the problem and this is what I came up with. create table test( id serial primary key, tekst text); insert into test values(1,'1'),(2,'2'); create function sel_test() returns test as $$ declare red record; begin select * into red from test limit 1; return red; end $$ language plpgsql; select * from sel_test(); This all works ok, but if I do alter table test drop column tekst; then select * from sel_test(); doesn't work anymore Even if I recreate the function. Is this a known problem? Regards, Rikard
In response to Rikard Pavelic <rikard.pavelic@zg.htnet.hr>: > > I noticed that some of the functions I created don't work anymore. > So I tired to reproduce the problem and this is what I came up with. > > create table test( > id serial primary key, > tekst text); > > insert into test values(1,'1'),(2,'2'); > > create function sel_test() returns test as > $$ > declare red record; > begin > select * into red from test limit 1; > return red; > end > $$ language plpgsql; > > select * from sel_test(); > > This all works ok, but if I do > > alter table test drop column tekst; > > then select * from sel_test(); doesn't work anymore > Even if I recreate the function. > > Is this a known problem? Have you tried altering the table, then disconnect and reconnect, then run your stored procedure? If that works, then the problem has to do with OID caching, which is known. -- Bill Moran http://www.potentialtech.com
Rikard Pavelic wrote: > create function sel_test() returns test as > $$ > declare red record; > begin > select * into red from test limit 1; > return red; > end > $$ language plpgsql; > alter table test drop column tekst; > > then select * from sel_test(); doesn't work anymore > Even if I recreate the function. > > Is this a known problem? Not known by me, certainly. It appears to be related to the definition of red as type "record". If you define it as type "test" it all seems OK. Or, you can set up a variable red2 type "test" and copy the value into that, and that works. I do know that dropping a column just flags it as dropped, it doesn't automatically update the on-disk representation. That suggests to me there's something in the "RETURN" or "SELECT INTO" statements that's not checking for the flag and deducing the wrong type. Trying to access a dropped column via assignment or RAISE NOTICE gives errors, which makes me think it's the RETURN statement. Any developers care to comment? -- Richard Huxton Archonet Ltd
Bill Moran wrote: > Have you tried altering the table, then disconnect and reconnect, then run > your stored procedure? > > If that works, then the problem has to do with OID caching, which is known. > > Yes, I even tried restarting postgres, but nothing helped. Regards, Rikard
Richard Huxton <dev@archonet.com> writes: > Any developers care to comment? plpgsql isn't very bright about tuple descriptors containing dropped columns. The immediate problem is that compatible_tupdesc() doesn't think a tupdesc containing one column matches one containing the same column plus a dropped column; but fixing that would just allow subsequent processing to crash :-(. It needs a fair amount of work. I think the other PLs are no better. regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Any developers care to comment? > > plpgsql isn't very bright about tuple descriptors containing dropped > columns. The immediate problem is that compatible_tupdesc() doesn't > think a tupdesc containing one column matches one containing the same > column plus a dropped column; but fixing that would just allow > subsequent processing to crash :-(. It needs a fair amount of work. > I think the other PLs are no better. OK, I think (after a bit of playing) I see what you mean. If I do the following: CREATE TYPE tt AS (id int); CREATE OR REPLACE FUNCTION sel_test2() RETURNS tt AS $$ ... Then it all works OK, because the SELECT inside the function is returning one int and so is the function. -- Richard Huxton Archonet Ltd