[BUGS] LOAD broken?
I've got a C function in a .so that I use in postgres. While developing it I want to reload the dynamic library. The docs says that LOAD should do the trick but nothing happens when I use it. Have LOAD stoped working and is it now a NOP? I've tried LOAD both when the function exists in pg, and when I first drop the function, do the load and then create the function again. But it makes no difference. I run redhat 9, maybe load works in other dists/operating systems? -- /Dennis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] pl/pgsql problem with search_path
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
Re: [BUGS] Unclear documentation (IMMUTABLE functions)
Not my place to say, but... I think this likely should be worded something like this (if true): ... Also note that the current_timestamp family of functions qualify as stable, since their values do not change within SQL statement, and to be more concise the current_timestamp functions do not change within a transaction. Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Neil Conway > Sent: Friday, September 05, 2003 2:32 PM > To: Stephan Szabo > Cc: Daniel Schreiber; [EMAIL PROTECTED] > Subject: Re: [BUGS] Unclear documentation (IMMUTABLE functions) > > > On Fri, 2003-09-05 at 10:01, Stephan Szabo wrote: > > This is the section in create function reference page about > immutable. I'd > > thought it was clear, but do you have a better suggested wording? > > While we're on the subject, this adjacent paragraph of the docs seems > unclear: > > STABLE indicates that within a single table scan the function > will consistently return the same result for the same argument > values, but that its result could change across SQL > statements. > This is the appropriate selection for functions whose results > depend on database lookups, parameter variables (such as the > current time zone), etc. Also note that the current_timestamp > family of functions qualify as stable, since their > values do not > change within a transaction. > > So, can a STABLE function change across SQL statements (as > the beginning > of the paragraph implies), or across transactions (as the end of the > paragraph implies)? > > -Neil > > > > ---(end of > broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pl/pgsql problem with search_path
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
Re: [BUGS] pl/pgsql problem with search_path
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". regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] pl/pgsql problem with search_path
Eugene Chow wrote: > 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. Yep, that's the workaround mentioned in the FAQ. -- 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
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. -- 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 7: don't forget to increase your free space map settings
Re: [BUGS] LOAD broken?
Dennis Bjorklund <[EMAIL PROTECTED]> writes:
> I've got a C function in a .so that I use in postgres. While developing it
> I want to reload the dynamic library. The docs says that LOAD should do
> the trick but nothing happens when I use it.
You're right, this seems not to work on Linux. I've applied the
attached patch to CVS tip.
regards, tom lane
*** src/backend/utils/fmgr/dfmgr.c.orig Sun Aug 3 23:01:07 2003
--- src/backend/utils/fmgr/dfmgr.c Sat Sep 6 22:13:25 2003
***
*** 177,183
load_file(char *filename)
{
DynamicFileList *file_scanner,
! *p;
struct stat stat_buf;
char *fullname;
--- 178,185
load_file(char *filename)
{
DynamicFileList *file_scanner,
! *prv,
! *nxt;
struct stat stat_buf;
char *fullname;
***
*** 196,226
(errcode_for_file_access(),
errmsg("could not access file \"%s\": %m",
fullname)));
! if (file_list != (DynamicFileList *) NULL)
{
! if (SAME_INODE(stat_buf, *file_list))
{
! p = file_list;
! file_list = p->next;
! pg_dlclose(p->handle);
! free((char *) p);
}
else
! {
! for (file_scanner = file_list;
!file_scanner->next != (DynamicFileList *) NULL;
!file_scanner = file_scanner->next)
! {
! if (SAME_INODE(stat_buf, *(file_scanner->next)))
! {
! p = file_scanner->next;
! file_scanner->next = p->next;
! pg_dlclose(p->handle);
! free((char *) p);
! break;
! }
! }
! }
}
load_external_function(fullname, (char *) NULL, false, (void *) NULL);
--- 198,224
(errcode_for_file_access(),
errmsg("could not access file \"%s\": %m",
fullname)));
! /*
!* We have to zap all entries in the list that match on either filename
!* or inode, else load_external_function() won't do anything.
!*/
! prv = NULL;
! for (file_scanner = file_list; file_scanner != NULL; file_scanner = nxt)
{
! nxt = file_scanner->next;
! if (strcmp(fullname, file_scanner->filename) == 0 ||
! SAME_INODE(stat_buf, *file_scanner))
{
! if (prv)
! prv->next = nxt;
! else
! file_list = nxt;
! pg_dlclose(file_scanner->handle);
! free((char *) file_scanner);
! /* prv does not change */
}
else
! prv = file_scanner;
}
load_external_function(fullname, (char *) NULL, false, (void *) NULL);
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
