Обсуждение: 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