[SQL] Function Volatility
Hi guys, I am not sure if I am understanding volatility. My issue is better explained with a quick example. The function below expresses call durations in minutes and it is immutable. CREATE OR REPLACE FUNCTION dur2min(secs INTEGER) RETURNS INTEGER AS $$ BEGIN RAISE NOTICE 'BEEN HERE!'; RETURN CEIL(secs/60.0); END; $$ LANGUAGE 'plpgsql' IMMUTABLE; # SELECT dur2min(30) as c1, dur2min(30) as c2, dur2min(30) as c3; NOTICE: BEEN HERE! NOTICE: BEEN HERE! NOTICE: BEEN HERE! c1 | c2 | c3 ++ 1 | 1 | 1 (1 row) What bother me are the 3 "been here" messages. As the function is immutable and the parameter remains unchanged needs the planner actually execute the function 3 times? I was under the impression that under these conditions it could *reuse* the result of the first call. The manual states the planner should avoid reevaluate the function but I'm not sure what that means as it *is* executing it every time. My goal of course is that the function gets executed only once per row. I'm using 8.2.4 Thanks for your hindsight. Regards, Fernando.
[SQL] postgresql HEAD build failure
I have, for some months, been running this daily, but not taking notice
of the results:
[EMAIL PROTECTED] ~]$ cat bin/pg_build
#!/bin/bash
set -xe
cd /home/pgtest/pgsql/pgsql-2007-04-23/
ping -c4 2>/dev/null anoncvs.postgresql.org | grep -q 'bytes from'\
&& {
make distclean || :
cvs -q update
./configure --prefix=${HOME}//postgres --with-pgport=5433 --with
-pam --with-openssl \
--enable-depend \
--with-gnu-ld --with-python --with-perl
}
time make -j3 -s && time make -j3 -s install
[EMAIL PROTECTED] ~]$
It fails thus:
config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port
+ make -j3 -s
tsearchcmds.c:38:30: error: tsearch/ts_cache.h: No such file or directory
tsearchcmds.c:39:31: error: tsearch/ts_public.h: No such file or directory
tsearchcmds.c:40:30: error: tsearch/ts_utils.h: No such file or directory
tsearchcmds.c: In function ‘getTokenTypes’:
tsearchcmds.c:1676: error: ‘TSParserCacheEntry’ undeclared (first use in
this function)
tsearchcmds.c:1676: error: (Each undeclared identifier is reported only once
tsearchcmds.c:1676: error: for each function it appears in.)
tsearchcmds.c:1676: error: ‘prs’ undeclared (first use in this function)
tsearchcmds.c:1676: warning: implicit declaration of function
‘lookup_ts_parser_cache’
tsearchcmds.c:1677: error: ‘LexDescr’ undeclared (first use in this
function)
tsearchcmds.c:1677: error: ‘list’ undeclared (first use in this function)
tsearchcmds.c:1678: warning: ISO C90 forbids mixed declarations and code
tsearchcmds.c:1693: error: expected expression before ‘)’ token
tsearchcmds.c: In function ‘serialize_deflist’:
tsearchcmds.c:2002: warning: implicit declaration of function
‘CStringGetTextP’
tsearchcmds.c:2002: warning: assignment makes pointer from integer
without a cast
tsearchcmds.c: In function ‘deserialize_deflist’:
tsearchcmds.c:2098: warning: implicit declaration of function
‘TextPGetCString’
tsearchcmds.c:2098: warning: format ‘%s’ expects type ‘char *’, but
argument 2 has type ‘int’
tsearchcmds.c:2209: warning: format ‘%s’ expects type ‘char *’, but
argument 2 has type ‘int’
make[3]: *** [tsearchcmds.o] Error 1
make[2]: *** [commands-recursive] Error 2
make[2]: *** Waiting for unfinished jobs
make[1]: *** [all] Error 2
make: *** [all] Error 2
My most recent successful build:
-rwxr-xr-x 1 pgtest 1008 39322 Jun 18 07:52 postgres/bin/createdb
-rwxr-xr-x 1 pgtest 1008 39233 Jun 18 07:52 postgres/bin/clusterdb
This would be about when I switched my desktop to:
[EMAIL PROTECTED] ~]$ rpm -qif /etc/redhat-release
Name : sl-release Relocations: (not relocatable)
Version : 5.0 Vendor: Scientific Linux
Release : 4 Build Date: Sat 21 Apr 2007 12:37:20 AM WST
Install Date: Fri 15 Jun 2007 10:37:03 AM WST Build Host: norob.fnal.gov
Group : System Environment/Base Source RPM: sl-release-5.0-4.src.rpm
Size : 57652 License: GPL
Signature : DSA/SHA1, Sat 21 Apr 2007 12:37:32 AM WST, Key ID
da6ad00882fd17b2
Summary : Scientific Linux release file
Description :
Scientific Linux release files
[EMAIL PROTECTED] ~]$
SL5 is a clone (like CentOS) of RHEL5.
My guess is that this version of gcc implements new rules:
[EMAIL PROTECTED] ~]$ gcc --version
gcc (GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
[EMAIL PROTECTED] ~]$
Please don't reply off-list.
Should I report this as a bug?
--
Cheers
John Summerfield
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] postgresql HEAD build failure
John Summerfield wrote:
> I have, for some months, been running this daily, but not taking notice
> of the results:
> [EMAIL PROTECTED] ~]$ cat bin/pg_build
> #!/bin/bash
> set -xe
> cd /home/pgtest/pgsql/pgsql-2007-04-23/
> ping -c4 2>/dev/null anoncvs.postgresql.org | grep -q 'bytes from'\
> && {
> make distclean || :
> cvs -q update
> ./configure --prefix=${HOME}//postgres --with-pgport=5433 --with
> -pam --with-openssl \
> --enable-depend \
> --with-gnu-ld --with-python --with-perl
> }
> time make -j3 -s && time make -j3 -s install
> [EMAIL PROTECTED] ~]$
>
> It fails thus:
> config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port
> + make -j3 -s
> tsearchcmds.c:38:30: error: tsearch/ts_cache.h: No such file or directory
> tsearchcmds.c:39:31: error: tsearch/ts_public.h: No such file or directory
> tsearchcmds.c:40:30: error: tsearch/ts_utils.h: No such file or directory
Hmm, try deleting the build tree and start again. Sometimes, what
happens to me is that somebody adds a new directory, and my build tree
does not contain it. It's easy to fix: when I had a slower computer
what I did was create the offending directory and symlink the makefile.
Nowadays I just wipe the whole thing and let the build directory be
constructed again by configure. That gets things in sync.
Now that I look closer, however, this was always with source dirs, not
include dirs. I think the problem here is that you're not passing -d to
"cvs update".
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] postgresql HEAD build failure
Alvaro Herrera wrote:
John Summerfield wrote:
I have, for some months, been running this daily, but not taking notice
of the results:
[EMAIL PROTECTED] ~]$ cat bin/pg_build
#!/bin/bash
set -xe
cd /home/pgtest/pgsql/pgsql-2007-04-23/
ping -c4 2>/dev/null anoncvs.postgresql.org | grep -q 'bytes from'\
&& {
make distclean || :
cvs -q update
./configure --prefix=${HOME}//postgres --with-pgport=5433 --with
-pam --with-openssl \
--enable-depend \
--with-gnu-ld --with-python --with-perl
}
time make -j3 -s && time make -j3 -s install
[EMAIL PROTECTED] ~]$
It fails thus:
config.status: linking ./src/makefiles/Makefile.linux to src/Makefile.port
+ make -j3 -s
tsearchcmds.c:38:30: error: tsearch/ts_cache.h: No such file or directory
tsearchcmds.c:39:31: error: tsearch/ts_public.h: No such file or directory
tsearchcmds.c:40:30: error: tsearch/ts_utils.h: No such file or directory
Hmm, try deleting the build tree and start again. Sometimes, what
happens to me is that somebody adds a new directory, and my build tree
does not contain it. It's easy to fix: when I had a slower computer
what I did was create the offending directory and symlink the makefile.
Nowadays I just wipe the whole thing and let the build directory be
constructed again by configure. That gets things in sync.
that, I think, subverts the purpose of cvs and other SCMTs, but...
Now that I look closer, however, this was always with source dirs, not
include dirs. I think the problem here is that you're not passing -d to
"cvs update".
-d fixed the missing directories
and -P prunes obsolete ones.
Obviously I needed to read the instructions more closely:-(
And the timing of my upgrade was just a coincidence. It happens.
Thanks for your help.
---(end of broadcast)---
TIP 1: 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
Re: [SQL] postgresql HEAD build failure
John Summerfield wrote: > Alvaro Herrera wrote: >> Hmm, try deleting the build tree and start again. Sometimes, what >> happens to me is that somebody adds a new directory, and my build tree >> does not contain it. It's easy to fix: when I had a slower computer >> what I did was create the offending directory and symlink the makefile. >> Nowadays I just wipe the whole thing and let the build directory be >> constructed again by configure. That gets things in sync. > > that, I think, subverts the purpose of cvs and other SCMTs, but... No, it doesn't. Note that I suggested only deleting the *build* dir, not the source dir. I use VPATH builds -- I never delete my source dirs. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Function Volatility
"Fernando Hevia" <[EMAIL PROTECTED]> writes: > I am not sure if I am understanding volatility. You're not. > What bother me are the 3 "been here" messages. As the function is immutable > and the parameter remains unchanged needs the planner actually execute the > function 3 times? The IMMUTABLE marker is a promise from you to the system that it is safe to optimize away multiple calls to the function. It is not a promise from the system to you that the system will expend unlimited amounts of energy to detect duplicate calls. The majority of the immutable functions in Postgres are things like int4pl(), where it would obviously be silly to expend any cycles at all on looking for duplicate calls such as you show here. In practice what will happen is that each textual call will be folded to a constant separately. The advantage comes from not having to repeat the call for each row processed by a query, not from saving work within a row. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] postgresql HEAD build failure
John Summerfield <[EMAIL PROTECTED]> writes: > -d fixed the missing directories > and -P prunes obsolete ones. > Obviously I needed to read the instructions more closely:-( FWIW, I've used the following ~/.cvsrc for many years: cvs -z3 update -d -P checkout -P The usefulness of -z3 depends on the speed of your connection, but the other two lines fix obviously stupid defaults ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
