By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,663 Members | 1,375 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,663 IT Pros & Developers. It's quick & easy.

Bug or Feature - plpgsql odity

P: n/a
-----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/yrmrjqGXBvRToM4RAm1EAJoCQGXhYGhdAGexNX5QaHI8MtAGAA CgjWjC
3y04n9FTpEeX8FxEgkEU5Cw=
=okfE
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Uwe C. Schroeder" <uw*@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

---------------------------(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

Nov 12 '05 #2

P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sunday 30 November 2003 08:15 pm, Tom Lane wrote:
"Uwe C. Schroeder" <uw*@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-----
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

P: n/a
"Uwe C. Schroeder" <uw*@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

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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.