Bug or Feature - plpgsql odity

[Uwe C. Schroeder]

-----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.newcolum n 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: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/yrmrjqGXBvRToM4 RAm1EAJoCQGXhYG hdAGexNX5QaHI8M tAGAACgjWjC
3y04n9FTpEeX8Fx EgkEU5Cw=
=okfE
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"Uwe C. Schroeder" <uw*@oss4u.co m> 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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Uwe C. Schroeder]

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

On Sunday 30 November 2003 08:15 pm, Tom Lane wrote:

"Uwe C. Schroeder" <uw*@oss4u.co m> 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: [phone] San Bruno, CA 94066
Cell: [phone] United States
Fax: [phone]
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/ysJjjqGXBvRToM4 RAuq1AJ9LgkhYXE z19+0I8ou8/N7AeKPEgwCffzBT
XvJPiExWzZVyDAK jLUsbW4M=
=IQTs
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Uwe C. Schroeder" <uw*@oss4u.co m> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster