Re: [BUGS] pl/pgsql problem with search_path
"Bruce Momjian" <[EMAIL PROTECTED]> wrote: > Tom Lane wrote: > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > This highlights another problem with our plpgsql function caching. > > > > It's a little disturbing to think that any change in SEARCH_PATH might > > force us to discard all cached plans. That could be expensive; and > > consider a function that deliberately sets SEARCH_PATH to ensure that > > it gets the tables it wants. You wouldn't want such a function to be > > unable to cache any plans across calls (not to mention blowing away > > every other function's plans, too). > > > > We'd probably better record with each plan the SEARCH_PATH it was > > generated with. Then, as long as that matches the current setting, > > we can re-use the plan. > > > > Of course, none of this is going to happen until someone gets around to > > creating infrastructure for flushing cached plans at need. Right at the > > moment the answer is going to have to be "don't do that". > > Yep. I was just surprised it highlighted another failure of cached > plans. There is already a TODO for it ? Regards Gaetano Mendola ---(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
Re: [BUGS] pl/pgsql problem with search_path
Gaetano Mendola wrote: > "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > > Tom Lane wrote: > > > Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > This highlights another problem with our plpgsql function caching. > > > > > > It's a little disturbing to think that any change in SEARCH_PATH might > > > force us to discard all cached plans. That could be expensive; and > > > consider a function that deliberately sets SEARCH_PATH to ensure that > > > it gets the tables it wants. You wouldn't want such a function to be > > > unable to cache any plans across calls (not to mention blowing away > > > every other function's plans, too). > > > > > > We'd probably better record with each plan the SEARCH_PATH it was > > > generated with. Then, as long as that matches the current setting, > > > we can re-use the plan. > > > > > > Of course, none of this is going to happen until someone gets around to > > > creating infrastructure for flushing cached plans at need. Right at the > > > moment the answer is going to have to be "don't do that". > > > > Yep. I was just surprised it highlighted another failure of cached > > plans. > > There is already a TODO for it ? Yep: o Fix problems with complex temporary table creation/destruction without using PL/PgSQL EXECUTE, needs cache prevention/invalidation -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] pl/pgsql problem with search_path
Thanks. If I had only read the manual before posting. I solved it by
using 'execute' instead of 'select into' since the search_path is not
static in my system.
Gene
On Saturday, September 6, 2003, at 04:21 PM, Bruce Momjian wrote:
I think the problem is that the first time the function is called, it
is
compiled and cached for later use. At that time the function is bound
to the table oid, so even though you change the search path, the cached
copy still calls the old table.
If you exit psql and re-enter, or change the schema _before_ you call
the function for the first time, it should be fine.
This highlights another problem with our plpgsql function caching.
---
Eugene Chow wrote:
My plpgsql function seems to be ignoring search_path when looking for
the right table to select from. I'm running 7.3.4. Below is my test
code. Am I doing something wrong?
TIA, Gene Chow
test=> create or replace function getval() returns varchar as '
test'> declare val varchar;
test'> begin
test'> select into val value from bar limit 1;
test'> return val;
test'> end;' language 'plpgsql';
CREATE FUNCTION
test=> create table public.bar ( value varchar );
CREATE TABLE
test=> insert into public.bar values ('public value');
INSERT 4012748 1
test=> create schema foo;
CREATE SCHEMA
test=> create table foo.bar ( value varchar );
CREATE TABLE
test=> insert into foo.bar values ('foo value');
INSERT 4012754 1
test=> set search_path to foo, public;
SET
test=> select *, getval() from bar;
value | getval
---+---
foo value | foo value
(1 row)
test=> set search_path to public;
SET
test=> select *, getval() from bar;
value | getval
--+---
public value | foo value
(1 row)
---(end of
broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania
19073
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[BUGS] help with mac osx 10.2.6
Please help with a mac osx installation... I had installed 7.3.4 on osx 10.2.6 some months ago. it was a painless installation. I responded to someone on the apple help list about installing. this person was having a problem with the make stage. I didn't think to retry making on my system. I did. I can not make it now. I am getting an error [postgres:~/postgres/postgresql-7.3.4 copy.4] postgres% make make -C doc all make[1]: Nothing to be done for `all'. make -C src all make -C port all gcc -traditional-cpp -g -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/include -c -o isinf.o isinf.c In file included from /usr/include/math.h:24, from isinf.c:5: /usr/include/architecture/ppc/math.h:128: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:128: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:129: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:130: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:130: error: parse error before "else" /usr/include/architecture/ppc/math.h:131: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:132: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:140: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:140: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:141: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:142: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:142: error: parse error before "else" /usr/include/architecture/ppc/math.h:143: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:144: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:151: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:151: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:152: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:153: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:153: error: parse error before "else" /usr/include/architecture/ppc/math.h:154: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:155: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:163: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:163: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:164: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:165: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:165: error: parse error before "else" /usr/include/architecture/ppc/math.h:166: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:167: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:175: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:175: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:176: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:177: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:177: error: parse error before "else" /usr/include/architecture/ppc/math.h:178: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:179: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:187: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:187: error: parse error before "__cplusplus" /usr/include/architecture/ppc/math.h:188: error: syntax error before "inline" /usr/include/architecture/ppc/math.h:189: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:189: error: parse error before "else" /usr/include/architecture/ppc/math.h:190: error: syntax error at '#' token /usr/include/architecture/ppc/math.h:191: error: syntax error at '#' token make[2]: *** [isinf.o] Error 1 make[1]: *** [all] Error 2 make: *** [all] Error 2 any ideas... i believe the problem lies with the december 2002 update to the developers tools. Thanks, Ted __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] help with mac osx 10.2.6
Theodore Petrosky <[EMAIL PROTECTED]> writes: > any ideas... i believe the problem lies with the > december 2002 update to the developers tools. There is some recent traffic in our archives suggesting that Apple's gcc 3.3 is busted. I have not been able to check this out because my Powerbook is in the shop (still!?!?! snif). Please feel free to investigate and propose a workaround, if there is one. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
