Thread: alter table tablename add column - breaks pl/pgsql function returns tablename

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

This is an old problem, referred to in bug #4907:


CREATE TABLE test(id INTEGER);
INSERT INTO test VALUES (1);

CREATE OR REPLACE FUNCTION test_func() returns SETOF test as $$
DECLARE res_ test;
BEGIN       FOR res_ IN SELECT * FROM test LOOP               RETURN NEXT res_;       END LOOP;
END
$$ LANGUAGE 'plpgsql';

SELECT * FROM test_func();
ALTER TABLE test ADD COLUMN foo INTEGER;
SELECT * FROM test_func();
- -- ERROR:  wrong record type supplied in RETURN NEXT

You have to run create or replace again to fix it when changing the
schema. How come it fails? Is the return type "hardcoded" when the
function is created? Is this very hard to fix? IS there a suggested
work-around?

It seems to work to change to a generic "record", but that is also more
sloppy as it is not as well typed.

Thoughts?

Palle

-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQEcBAEBAgAGBQJQkWwGAAoJEIhV+7FrxBJDc6wIAK0w3h7hvPT0zsu7YyviKL+f
vi/cL5f+B9NYRkXxQ4AJ5+Qr0xwODNvSFlCEoqrREcdcCAZHoEktunZtp0FTvNLQ
HJEemac23/YyUaYE5rFI7KIySxTEnpY6qQx+YkHW1OiDF4/X/XzEFYFjCa9x3jNi
BfrnmYP0MXCB0y+vXRpUn4vm8QW7o5m+hQE0nQ0Ni4xk+GHbGOh2q21WzYevOe6s
Gj4r8nDPMX1q0Sk4zmnNrUbudVx6YsEtj7ogAbGCg5KbB3ebjfj5eJdSAD9z/uCs
0ikfZt2kvDAkdbrz/uEaRhq91gkkjZR2+Tm1iEKJhedRsRBk2y554zw7esxkj/c=
=sVM9
-----END PGP SIGNATURE-----

On Wednesday, October 31, 2012 11:50 PM Palle Girgensohn  wrote:
Hash: SHA1

Hi!

> This is an old problem, referred to in bug #4907:


>ALTER TABLE test ADD COLUMN foo INTEGER;
> SELECT * FROM test_func();
> - -- ERROR:  wrong record type supplied in RETURN NEXT

> You have to run create or replace again to fix it when changing the
> schema. How come it fails? Is the return type "hardcoded" when the
> function is created?  I think it is because compiled body of function is created with that type of table. You will
notsee this problem if you reconnect or select from another new session.   
> Is this very hard to fix?
  Currently the compiled body is not discarded on DDL's, so I believe it is not a bug as per current implementation.
Howeverit can be thought of as a new feature. 

> IS there a suggested  work-around?   Considering DDL's are used less often, you can see how feasible it is for you
thatafter a DDL, you reconnect sessions which use functions.   I know this is not good workaround, but I am sorry
nothingelse comes to my mind for now. 
   Anybody else can think of any other better workarounds for this problem?

With Regards,
Amit Kapila.

On Thu, Nov 1, 2012 at 12:14 AM, Amit kapila <amit.kapila@huawei.com> wrote:
>> Is this very hard to fix?
>
>    Currently the compiled body is not discarded on DDL's, so I believe it is not a bug as per current
implementation.
>    However it can be thought of as a new feature.

Seems like a bug to me.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

5 nov 2012 kl. 19:36 skrev Robert Haas <robertmhaas@gmail.com>:

> On Thu, Nov 1, 2012 at 12:14 AM, Amit kapila <amit.kapila@huawei.com> wrote:
>>> Is this very hard to fix?
>>
>>   Currently the compiled body is not discarded on DDL's, so I believe it is not a bug as per current implementation.
>>   However it can be thought of as a new feature.
>
> Seems like a bug to me.
>

Please note that this problem does not go away by disconnecting and reconnecting, and other sessions get the error
immediately,so the claim that it is bound to a session is false.  

The work-around I use now is to "create or replace function ..." with a verbatim copy of what is already the defined
function.Seems stupid to me, I agree it seems like a bug.  


> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Palle Girgensohn <girgen@pingpong.net> writes:
> Please note that this problem does not go away by disconnecting and reconnecting, and other sessions get the error
immediately,so the claim that it is bound to a session is false. 
 

Huh?  The test case you provided certainly doesn't exhibit any such
behavior.  I get

regression=# SELECT * FROM test_func();
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function test_func() line 6 at RETURN NEXT
regression=# \c -
You are now connected to database "regression" as user "postgres".
regression=# SELECT * FROM test_func();id | foo 
----+----- 1 |    
(1 row)
        regards, tom lane

5 nov 2012 kl. 22:23 skrev Tom Lane <tgl@sss.pgh.pa.us>:

> Palle Girgensohn <girgen@pingpong.net> writes:
>> Please note that this problem does not go away by disconnecting and reconnecting, and other sessions get the error
immediately,so the claim that it is bound to a session is false. 
>
> Huh?  The test case you provided certainly doesn't exhibit any such
> behavior.  I get
>
> regression=# SELECT * FROM test_func();
> ERROR:  wrong record type supplied in RETURN NEXT
> CONTEXT:  PL/pgSQL function test_func() line 6 at RETURN NEXT
> regression=# \c -
> You are now connected to database "regression" as user "postgres".
> regression=# SELECT * FROM test_func();
> id | foo
> ----+-----
>  1 |
> (1 row)
>
>            regards, tom lane


Ah, sorry. Other sessions get the error immediately as well though. Would input parameters matter, or is it just the
returntype? I'll see if I can find a test case that breaks permanently, but I'm probably mistaken about that bit then.  

Palle Girgensohn <girgen@pingpong.net> writes:
> Ah, sorry. Other sessions get the error immediately as well though. Would input parameters matter, or is it just the
returntype? I'll see if I can find a test case that breaks permanently, but I'm probably mistaken about that bit then.


It's not the return value as such that's choking, it's the local
variable.  I believe the issue would appear with any local variable or
parameter of a named composite type.

The general case of this is quite difficult: should we expect that an
ALTER TYPE done (perhaps in some other session) while a function is
running would affect the *current value* of such a local variable?
There's really no practical way to implement that in the current system
structure, and certainly no way to enforce the behavior you get for row
values in regular tables, namely that the ALTER TYPE rolls back if any
row value conversion fails.

However I think we could realistically hope that subsequent function
calls would work with the up-to-date rowtype definition.  My opinion
about how to do that is to stop using the "row" code path in plpgsql for
values of named composite types, and instead treat them as "records";
that is, store a HeapTuple value plus a tuple descriptor (or something
morally equivalent such as a TupleTableSlot) and not break the value up
into a separate plpgsql variable (a/k/a PLpgSQL_datum) per column.  The
fundamental problem here is that doing that bakes the rowtype's column
set into the compiled form of the function.

There was some objection to that in the previous discussion on the
grounds of possible performance loss, but I think that objection is
at best premature; it ignores some salient facts such as
(1) some operations would get faster not slower,
(2) there is scope for performance-improvement efforts,
(3) per the old saying, code can be arbitrarily fast if it doesn't   have to give the right answer.  Objecting to this
fixwithout   proposing a more-workable alternative is useless.
 
        regards, tom lane