Thread: Bug or Feature - plpgsql odity

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


Hi,

following odity:

I have a table "quote".
In a stored proc I do a

rquote quote%ROWTYPE;

SELECT INTO rquote * FROM quote WHERE .....(some clause resulting in one
record)

Fine so far.
Now I manually add a column to the table (via psql).

After that I do a vacuum full analyze - just to make sure.

BUT: The above SELECT fails to retrieve a value for the new column. It will
always return NULL, even after I stored the procedure again (using the create
or replace syntax). Values for all other columns are OK.

If I declare the rquote variable as type RECORD everything works as expected.

Is the %ROWTYPE" cached somewhere ? Is that intentional ?
Accessing rquote.newcolumn doesn't raise an exception, but the value is NULL

Running 7.3.2 on linux.

    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/yrmrjqGXBvRToM4RAm1EAJoCQGXhYGhdAGexNX5QaHI8MtAGAACgjWjC
3y04n9FTpEeX8FxEgkEU5Cw=
=okfE
-----END PGP SIGNATURE-----

"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> Is the %ROWTYPE" cached somewhere ? Is that intentional ?

I see some fixes in the 7.4 CVS logs for dropped columns in plpgsql
rowtypes.  Not sure if they'd have any impact on added columns, but
you could try.  In general though I'd expect that plpgsql would cache
the definitions of rowtypes, the same as it does for tables.

            regards, tom lane

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

On Sunday 30 November 2003 08:15 pm, Tom Lane wrote:
> "Uwe C. Schroeder" <uwe@oss4u.com> writes:
> > Is the %ROWTYPE" cached somewhere ? Is that intentional ?
>
> I see some fixes in the 7.4 CVS logs for dropped columns in plpgsql
> rowtypes.  Not sure if they'd have any impact on added columns, but
> you could try.  In general though I'd expect that plpgsql would cache
> the definitions of rowtypes, the same as it does for tables.
>
>             regards, tom lane

So how would I force a reload of the cache ? I tried restarting postgres
alltogether, as well as recreating the procedure. Should I try a DROP and
CREATE cycle rather than a CREATE OR REPLACE ?
My understanding would be that the rowtype is compiled the moment the
procedure is created, so I'd expect a recreate of the procedure to take care
of the cache problem.

    UC

- --
Open Source Solutions 4U, LLC    2570 Fleetwood Drive
Phone:  +1 650 872 2425        San Bruno, CA 94066
Cell:   +1 650 302 2405        United States
Fax:    +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/ysJjjqGXBvRToM4RAuq1AJ9LgkhYXEz19+0I8ou8/N7AeKPEgwCffzBT
XvJPiExWzZVyDAKjLUsbW4M=
=IQTs
-----END PGP SIGNATURE-----

"Uwe C. Schroeder" <uwe@oss4u.com> writes:
> On Sunday 30 November 2003 08:15 pm, Tom Lane wrote:
>> I see some fixes in the 7.4 CVS logs for dropped columns in plpgsql
>> rowtypes.  Not sure if they'd have any impact on added columns, but
>> you could try.  In general though I'd expect that plpgsql would cache
>> the definitions of rowtypes, the same as it does for tables.

> So how would I force a reload of the cache ?

Starting a fresh session would be sufficient.  If 7.4 doesn't do what
you want after starting a fresh session, please file a bug report with
details.

            regards, tom lane