[HACKERS] PostgreSQL 8.2 : regression failures on IA-64
Hello List, I have already posted an e-mail on the general mailing list but on the advice of Devrim Gunduz ;-) , I try on this mailing list. I try to generate the RPM from the src.rpm downloaded on the postgresql.org web site. I work on an IA-64 server with Red Hat Enterprise Linux 4 AS update 2. As root, the first command I launched was : [EMAIL PROTECTED] SOURCES]# rpm -i postgresql-8.2.0-2PGDG.src.rpm [EMAIL PROTECTED] SOURCES]# ls -ltr ../SPECS total 28 -rw-r--r-- 1 root root 24840 Dec 5 06:04 postgresql-8.2.spec [EMAIL PROTECTED] SOURCES]# ls -ltr total 24176 -rw-r--r-- 1 root root 12459207 Dec 2 20:25 postgresql-8.2.0.tar.bz2 -rw-r--r-- 1 root root 897 Dec 2 20:28 pg_config.h -rw-r--r-- 1 root root 1539 Dec 2 20:28 Makefile.regress -rwxr-xr-x 1 root root 56 Dec 2 20:28 filter-requires-perl-Pg.sh -rw-r--r-- 1 root root 1631 Dec 2 20:28 postgresql-logging.patch -rw-r--r-- 1 root root 7529 Dec 2 20:28 postgresql.init -rw-r--r-- 1 root root 85 Dec 2 20:28 postgresql-bashprofile -rw-r--r-- 1 root root 1757 Dec 2 20:28 rpm-pgsql.patch -rw-r--r-- 1 root root15598 Dec 2 20:28 README.rpm-dist -rw-r--r-- 1 root root 2563 Dec 2 20:28 postgresql-test.patch -rw-r--r-- 1 root root 919 Dec 2 20:28 postgresql-perl-rpath.patch -rw-r--r-- 1 root root 141 Dec 2 20:28 postgresql.pam -rw-r--r-- 1 root root 12198114 Dec 6 17:18 postgresql-8.2.0-2PGDG.src.rpm Then from the SPECS directory, with the spec file that you can find attached, I launched as root : rpmbuild -ba postgresql-8.2.spec Then, as root, I changed the access to these directories : root# chmod -R a+w src/test/regress root# chmod -R a+w contrib/spi And as postgres, I launched the regression tests. root# su - postgres -bash-3.00$ gmake check The tests for create_function_1 and triggers fail... test create_function_1... FAILED ... ... ... triggers ... FAILED I see at http://archives.postgresql.org/pgsql-ports/2006-11/msg00011.php that this problem already appear on 64 bits but I am note sure that it is the same problem... You can find attach the files regression.diff and regression.out In the file regression.diff, my first problem seems to be linked with the function autoinc() : *** ./expected/create_function_1.outThu Dec 7 14:37:28 2006 --- ./results/create_function_1.outThu Dec 7 14:41:21 2006 *** *** 25,40 NOTICE: argument type city_budget is only a shell CREATE FUNCTION check_primary_key () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so LANGUAGE C; CREATE FUNCTION check_foreign_key () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so LANGUAGE C; CREATE FUNCTION autoinc () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so LANGUAGE C; CREATE FUNCTION funny_dup17 () RETURNS trigger AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regress/regress.so' --- 25,41 NOTICE: argument type city_budget is only a shell CREATE FUNCTION check_primary_key () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so' LANGUAGE C; CREATE FUNCTION check_foreign_key () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so' LANGUAGE C; CREATE FUNCTION autoinc () RETURNS trigger ! AS '/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so' LANGUAGE C; *+ ERROR: could not find function autoinc in file /SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so* I checked the file contrib/spi/refint.c : it does not contain a function called autoinc... The two others errors reported in regression.diff are : delete from tttest where price_id = 2; select * from tttest; price_id | price_val | price_on | price_off --+---+--+--- ! 1 | 1 | 10 |99 ! 3 | 3 | 30 |99 ! 2 | 2 | 20 |40 (3 rows) -- what do we see ? --- 150,175 for each row execute procedure autoinc (price_on, ttdummy_seq); *+ ERROR: function autoinc() does not exist* insert into tttest values (1, 1, null); insert into tttest values (2, 2, null); insert into tttest values (3, 3, 0); select * from tttest; price_id | price_val | price_on | price_off --+---+--+--- ! 1 | 1 | |99 ! 2 | 2 | |99 ! 3 | 3 |0 |99 (3 rows) delete from tttest where price_id = 2; *+ ERROR: ttdummy (tttest): price_on must be
Re: [HACKERS] Load distributed checkpoint
One thing I do worry about is if both postgresql and the OS are both delaying write()s in the hopes of collapsing them at the same time. If so, this would cause both to be experience bigger delays than expected, and make checkpoints worse. That is my concern. Letting 30 seconds worth of dirty pages accumulate between checkpoints and then trying to smooth the writes within checkpoint code seems like a doomed effort. 30 seconds ??? You are supposed to avoid excessive checkpoints. If you are reducing checkpoint_timeout to avoid the spike, you are imho definitely tuning the wrong knob. You are supposed to increase checkpoint_timeout as far as you can to still have an acceptable recovery delay after a crash. If you cannot afford a huge spike during checkpoint, say every 30 minutes, you have to make bgwriter more aggressive. This would generally be true for both of the variants, db driven direct io and buffered filesystem io. Andreas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Grouped Index Tuples
Ron Mayer wrote: Jim C. Nasby wrote: On usage, ISTM it would be better to turn on GIT only for a clustered index and not the PK? I'm guessing your automatic case is intended for SERIAL PKs, but maybe it would be better to just make that explicit. Not necessarily; since often (in my tables at least) the data for come columns has some local grouping of similar values even though it's not the clustered index. Yes, there's a lot of cases like that. My real goal is to make it cheap enough in the case where there is no clustering, that we could just enable it on all indexes by default. At the moment, it looks like it's indeed near-zero cost when the table is in random order, but the CPU overhead is too great in many workloads to have it always enabled. More autotuning logic would be needed, or a significant reduction in overhead. But as it is, you can always turn it on explicitly if you think it'd help. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] unixware and --with-ldap
Hi Tom, Thanks for your help, I will try it ASAP and report maybe tonighgt CET. Also, Makefile.port needs a little patch that I'll send to. On Mon, 11 Dec 2006, Tom Lane wrote: Date: Mon, 11 Dec 2006 11:26:14 -0500 From: Tom Lane [EMAIL PROTECTED] To: Andrew Dunstan [EMAIL PROTECTED] Cc: ohp@pyrenet.fr, pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] unixware and --with-ldap Andrew Dunstan [EMAIL PROTECTED] writes: The right way to do this I think is to put an entry adjusting LIBS in src/makefiles/Makefile.unixware, but first it looks like we need to propagate the with-ldap switch into src/Makefile.global The Makefile is far too late --- this has to be adjusted at configure time, else configure will conclude that libldap isn't available. (I assume that Unixware's linker is sane enough that the link test will fail without the supporting libraries.) I'd be inclined to do something like add EXTRA_LDAP_LIBS=-llber -lresolv in src/template/unixware, and then in configure.in add this where needed: if test $with_ldap = yes ; then _LIBS=$LIBS if test $PORTNAME != win32; then AC_CHECK_LIB(ldap, ldap_bind, [$EXTRA_LDAP_LIBS], [AC_MSG_ERROR([library 'ldap' is required for LDAP])]) LDAP_LIBS_BE=-lldap $EXTRA_LDAP_LIBS if test $enable_thread_safety = yes; then # on some platforms ldap_r fails to link without PTHREAD_LIBS AC_CHECK_LIB(ldap_r, ldap_simple_bind, [$EXTRA_LDAP_LIBS], [AC_MSG_ERROR([library 'ldap_r' is required for LDAP])], [$PTHREAD_LIBS]) LDAP_LIBS_FE=-lldap_r $EXTRA_LDAP_LIBS else LDAP_LIBS_FE=-lldap $EXTRA_LDAP_LIBS fi else AC_CHECK_LIB(wldap32, ldap_bind, [], [AC_MSG_ERROR([library 'wldap32' is required for LDAP])]) LDAP_LIBS_FE=-lwldap32 LDAP_LIBS_BE=-lwldap32 fi LIBS=$_LIBS fi I'm not in a position to test that though ... regards, tom lane -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: ohp@pyrenet.fr -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 8.2 : regression failures on IA-64
Hi!
I think this was caused by a description mistake of postgresql-test.patch.
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION check_foreign_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
Single quotes (') of end of line is insufficient.
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
We have to use autoinc.so. (not refint.so)
# And You had better fix
/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regress/ and
/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/
in /usr/lib/pgsql/test/regress/.
best regards.
DANTE Alexandra wrote:
Hello List,
I have already posted an e-mail on the general mailing list but on the
advice of Devrim Gunduz ;-) , I try on this mailing list.
I try to generate the RPM from the src.rpm downloaded on the
postgresql.org web site. I work on an IA-64 server with Red Hat
Enterprise Linux 4 AS update 2.
As root, the first command I launched was :
[EMAIL PROTECTED] SOURCES]# rpm -i postgresql-8.2.0-2PGDG.src.rpm
[EMAIL PROTECTED] SOURCES]# ls -ltr ../SPECS
total 28
-rw-r--r-- 1 root root 24840 Dec 5 06:04 postgresql-8.2.spec
[EMAIL PROTECTED] SOURCES]# ls -ltr
total 24176
-rw-r--r-- 1 root root 12459207 Dec 2 20:25 postgresql-8.2.0.tar.bz2
-rw-r--r-- 1 root root 897 Dec 2 20:28 pg_config.h
-rw-r--r-- 1 root root 1539 Dec 2 20:28 Makefile.regress
-rwxr-xr-x 1 root root 56 Dec 2 20:28 filter-requires-perl-Pg.sh
-rw-r--r-- 1 root root 1631 Dec 2 20:28 postgresql-logging.patch
-rw-r--r-- 1 root root 7529 Dec 2 20:28 postgresql.init
-rw-r--r-- 1 root root 85 Dec 2 20:28 postgresql-bashprofile
-rw-r--r-- 1 root root 1757 Dec 2 20:28 rpm-pgsql.patch
-rw-r--r-- 1 root root15598 Dec 2 20:28 README.rpm-dist
-rw-r--r-- 1 root root 2563 Dec 2 20:28 postgresql-test.patch
-rw-r--r-- 1 root root 919 Dec 2 20:28 postgresql-perl-rpath.patch
-rw-r--r-- 1 root root 141 Dec 2 20:28 postgresql.pam
-rw-r--r-- 1 root root 12198114 Dec 6 17:18
postgresql-8.2.0-2PGDG.src.rpm
Then from the SPECS directory, with the spec file that you can find
attached, I launched as root :
rpmbuild -ba postgresql-8.2.spec
Then, as root, I changed the access to these directories :
root# chmod -R a+w src/test/regress
root# chmod -R a+w contrib/spi
And as postgres, I launched the regression tests.
root# su - postgres
-bash-3.00$ gmake check
The tests for create_function_1 and triggers fail...
test create_function_1... FAILED
... ... ...
triggers ... FAILED
I see at http://archives.postgresql.org/pgsql-ports/2006-11/msg00011.php
that this problem already appear on 64 bits but I am note sure that it
is the same problem...
You can find attach the files regression.diff and regression.out
In the file regression.diff, my first problem seems to be linked with
the function autoinc() :
*** ./expected/create_function_1.outThu Dec 7 14:37:28 2006
--- ./results/create_function_1.outThu Dec 7 14:41:21 2006
***
*** 25,40
NOTICE: argument type city_budget is only a shell
CREATE FUNCTION check_primary_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION check_foreign_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION funny_dup17 ()
RETURNS trigger
AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regress/regress.so'
--- 25,41
NOTICE: argument type city_budget is only a shell
CREATE FUNCTION check_primary_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so'
LANGUAGE C;
CREATE FUNCTION check_foreign_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so'
LANGUAGE C;
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so'
LANGUAGE C;
*+ ERROR: could not find function autoinc in file
/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so*
I checked
Re: [HACKERS] PostgreSQL 8.2 : regression failures on IA-64
Hi, On Tue, 2006-12-12 at 19:57 +0900, Tatsuhito Kasahara wrote: I think this was caused by a description mistake of postgresql-test.patch. Yeah, I just figured that while I was looking at what Tom did for FC-7 RPMs. I committed the new patch to CVS. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] PostgreSQL 8.2 : regression failures on IA-64
Thank you very much : it works
-bash-3.00$ gmake check 21 |tee traces_check8.2.0_gcc_121206_v2.log
... ... ...
test create_function_1... ok
triggers ... ok
... ... ...
===
All 103 tests passed.
===
The problems were those you found :
- I replaced AS '@abs_spidir@/[EMAIL PROTECTED]@' by AS
'@abs_spidir@/[EMAIL PROTECTED]@'
- I added ' at lines 58, 63, 68
By doing this, the regression tests are OK !
Regards,
Alexandra
Tatsuhito Kasahara wrote:
Hi!
I think this was caused by a description mistake of
postgresql-test.patch.
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION check_foreign_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
Single quotes (') of end of line is insufficient.
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
We have to use autoinc.so. (not refint.so)
# And You had better fix
/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regress/
and
/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/
in /usr/lib/pgsql/test/regress/.
best regards.
DANTE Alexandra wrote:
Hello List,
I have already posted an e-mail on the general mailing list but on
the advice of Devrim Gunduz ;-) , I try on this mailing list.
I try to generate the RPM from the src.rpm downloaded on the
postgresql.org web site. I work on an IA-64 server with Red Hat
Enterprise Linux 4 AS update 2.
As root, the first command I launched was :
[EMAIL PROTECTED] SOURCES]# rpm -i postgresql-8.2.0-2PGDG.src.rpm
[EMAIL PROTECTED] SOURCES]# ls -ltr ../SPECS
total 28
-rw-r--r-- 1 root root 24840 Dec 5 06:04 postgresql-8.2.spec
[EMAIL PROTECTED] SOURCES]# ls -ltr
total 24176
-rw-r--r-- 1 root root 12459207 Dec 2 20:25 postgresql-8.2.0.tar.bz2
-rw-r--r-- 1 root root 897 Dec 2 20:28 pg_config.h
-rw-r--r-- 1 root root 1539 Dec 2 20:28 Makefile.regress
-rwxr-xr-x 1 root root 56 Dec 2 20:28 filter-requires-perl-Pg.sh
-rw-r--r-- 1 root root 1631 Dec 2 20:28 postgresql-logging.patch
-rw-r--r-- 1 root root 7529 Dec 2 20:28 postgresql.init
-rw-r--r-- 1 root root 85 Dec 2 20:28 postgresql-bashprofile
-rw-r--r-- 1 root root 1757 Dec 2 20:28 rpm-pgsql.patch
-rw-r--r-- 1 root root15598 Dec 2 20:28 README.rpm-dist
-rw-r--r-- 1 root root 2563 Dec 2 20:28 postgresql-test.patch
-rw-r--r-- 1 root root 919 Dec 2 20:28
postgresql-perl-rpath.patch
-rw-r--r-- 1 root root 141 Dec 2 20:28 postgresql.pam
-rw-r--r-- 1 root root 12198114 Dec 6 17:18
postgresql-8.2.0-2PGDG.src.rpm
Then from the SPECS directory, with the spec file that you can find
attached, I launched as root :
rpmbuild -ba postgresql-8.2.spec
Then, as root, I changed the access to these directories :
root# chmod -R a+w src/test/regress
root# chmod -R a+w contrib/spi
And as postgres, I launched the regression tests.
root# su - postgres
-bash-3.00$ gmake check
The tests for create_function_1 and triggers fail...
test create_function_1... FAILED
... ... ...
triggers ... FAILED
I see at
http://archives.postgresql.org/pgsql-ports/2006-11/msg00011.php that
this problem already appear on 64 bits but I am note sure that it is
the same problem...
You can find attach the files regression.diff and regression.out
In the file regression.diff, my first problem seems to be linked
with the function autoinc() :
*** ./expected/create_function_1.outThu Dec 7 14:37:28 2006
--- ./results/create_function_1.outThu Dec 7 14:41:21 2006
***
*** 25,40
NOTICE: argument type city_budget is only a shell
CREATE FUNCTION check_primary_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION check_foreign_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION autoinc ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so
LANGUAGE C;
CREATE FUNCTION funny_dup17 ()
RETURNS trigger
AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regress/regress.so'
--- 25,41
NOTICE: argument type city_budget is only a shell
CREATE FUNCTION check_primary_key ()
RETURNS trigger
! AS
'/SLONY_PGS/PostgreSQL_8.2.0/BUILD/postgresql-8.2.0/src/test/regres/../../../contrib/spi/refint.so'
Re: [HACKERS] Load distributed checkpoint
On Tue, Dec 12, 2006 at 3:22 AM, in message [EMAIL PROTECTED], Zeugswetter Andreas ADI SD [EMAIL PROTECTED] wrote: One thing I do worry about is if both postgresql and the OS are both delaying write()s in the hopes of collapsing them at the same time. If so, this would cause both to be experience bigger delays than expected, and make checkpoints worse. That is my concern. Letting 30 seconds worth of dirty pages accumulate between checkpoints and then trying to smooth the writes within checkpoint code seems like a doomed effort. 30 seconds ??? You are supposed to avoid excessive checkpoints. If you are reducing checkpoint_timeout to avoid the spike, you are imho definitely tuning the wrong knob. Sorry for the fuzzy language -- I was intending to describe a situation where dirty pages accumulate by checkpoint time which would take 30 seconds to write to disk. We were into this situation (and worse) with the default bgwriter settings. you have to make bgwriter more aggressive. This is what I've been saying. I've also been saying that if the PostgreSQL way is to let the file system handle the caching and I/O scheduling, we should trust it to know what to do with dirty pages, and not try to second-guess it. (Of course there are knobs to tune the file system if needed.) Our checkpoint performance issues went away when we went to settings which basically never leave a dirty page hidden from the file system for more than two seconds. -Kevin ---(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
[HACKERS] coalesce and aggregate functions
Is this a bug, or don't I understand coalesce()? create table test (a int, b int); insert into test values (1,null); insert into test values (2,1); insert into test values (2,2); select * from test; -- returns: select sum(b) from test where a=1; -- null select sum(b) from test where a=2; -- 3 select coalesce(0,sum(b)) from test where a=1; -- 0 select coalesce(0,sum(b)) from test where a=2; -- 0 delete from test where a=1; select coalesce(0,sum(b)) from test where a=2; -- 0 ! So when I use coalesce() with sum(), I always get the constant. I would have expected it only in the case where sum() returns null.. What am I missing? Cheers, Patrick (PostgreSQL 8.2devel of 21st November 2006) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] coalesce and aggregate functions
Patrick Welche wrote: Is this a bug, or don't I understand coalesce()? create table test (a int, b int); insert into test values (1,null); insert into test values (2,1); insert into test values (2,2); select * from test; -- returns: select sum(b) from test where a=1; -- null select sum(b) from test where a=2; -- 3 select coalesce(0,sum(b)) from test where a=1; -- 0 select coalesce(0,sum(b)) from test where a=2; -- 0 delete from test where a=1; select coalesce(0,sum(b)) from test where a=2; -- 0 ! So when I use coalesce() with sum(), I always get the constant. I would have expected it only in the case where sum() returns null.. Coalesce returns the first non-null argument. In your example, 0 is always the first non-null argument. You should be doing this instead: select coalesce(sum(b),0) from test where a=2; to get the desired effect. BTW: This type of questions really belong to pgsql-general or pgsql-novice, this list is for discussing development of PostgreSQL itself. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] coalesce and aggregate functions
Patrick Welche [EMAIL PROTECTED] writes: Is this a bug, or don't I understand coalesce()? select coalesce(0,sum(b)) from test where a=2; -- 0 ! So when I use coalesce() with sum(), I always get the constant. I would have expected it only in the case where sum() returns null.. Coalesce will return the first argument if it's not null. You may be thinking about the arguments in reverse order? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] coalesce and aggregate functions
COALESCE returns the leftmost non-null value. Perhaps what you wanted was sum(coalesce(b,0)) instead of coalesce(0,sum(b)) On Tue, Dec 12, 2006 at 9:22 AM, in message [EMAIL PROTECTED], Patrick Welche [EMAIL PROTECTED] wrote: Is this a bug, or don't I understand coalesce()? create table test (a int, b int); insert into test values (1,null); insert into test values (2,1); insert into test values (2,2); select * from test; -- returns: select sum(b) from test where a=1; -- null select sum(b) from test where a=2; -- 3 select coalesce(0,sum(b)) from test where a=1; -- 0 select coalesce(0,sum(b)) from test where a=2; -- 0 delete from test where a=1; select coalesce(0,sum(b)) from test where a=2; -- 0 ! So when I use coalesce() with sum(), I always get the constant. I would have expected it only in the case where sum() returns null.. What am I missing? ---(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: [HACKERS] unixware and --with-ldap
Olivier PRENANT wrote: When I swithed to the newest version og pgbuildfarm, I noticed that --with-ldap (now by defaut) didn't work on UnixWare. This is because, on Unixware, ldap needs lber and resolv. Is libldap a static library on that system? Or do shared libraries on Unixware generally 'not remember' the libraries they were linked against (this would be strange). Or was libldap not linked against liblber and libresolv? Tom Lane suggested to change configure.in like this: AC_CHECK_LIB(ldap_r, ldap_simple_bind, [$EXTRA_LDAP_LIBS], [AC_MSG_ERROR([library 'ldap_r' is required for LDAP])], [$PTHREAD_LIBS]) Shouldn't that be AC_CHECK_LIB(ldap_r, ldap_simple_bind, [], [AC_MSG_ERROR([library 'ldap_r' is required for LDAP])], [$PTHREAD_LIBS $EXTRA_LDAP_LIBS]) Yours, Laurenz Albe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Ottawa PGCon needs a program committee
All, You may or may not be aware that Dan Langille of FreeBSD (and BSDCan) is running a PostgreSQL conference in Ottawa this May: http://www.pgcon.org/2007/ In one week, PGCon will start accepting paper and tutorial submissions. Dan needs a committee ... ideally 4-6 people ... from the community to evaluate and vote on the submissions. Members of this committee will need to use the online voting system (Rails+PG software Pentabarf, most likely) and discuss the submissions by e-mail from December 19th to January 15th. Please volunteer off-list to me and Dan. Thanks! --Josh Berkus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] unixware and --with-ldap
Albe Laurenz [EMAIL PROTECTED] writes: Shouldn't that be AC_CHECK_LIB(ldap_r, ldap_simple_bind, [], [AC_MSG_ERROR([library 'ldap_r' is required for LDAP])], [$PTHREAD_LIBS $EXTRA_LDAP_LIBS]) Ooops, of course. Like I said, untested ;-) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] coalesce and aggregate functions
On Tue, Dec 12, 2006 at 03:33:04PM +, Heikki Linnakangas wrote: BTW: This type of questions really belong to pgsql-general or pgsql-novice, this list is for discussing development of PostgreSQL itself. ^^ Indeed - I am truly feeling like a novice now... Cheers, Patrick ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Load distributed checkpoint
I have thought a while about this and I have some ideas. Ideally, we would be able to trickle the sync of individuals blocks during the checkpoint, but we can't because we rely on the kernel to sync all dirty blocks that haven't made it to disk using fsync(). We could trickle the fsync() calls, but that just extends the amount of data we are writing that has been dirtied post-checkpoint. In an ideal world, we would be able to fsync() only part of a file at a time, and only those blocks that were dirtied pre-checkpoint, but I don't see that happening anytime soon (and one reason why many commercial databases bypass the kernel cache). So, in the real world, one conclusion seems to be that our existing method of tuning the background writer just isn't good enough for the average user: #bgwriter_delay = 200ms # 10-1ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round These settings control what the bgwriter does, but they do not clearly relate to the checkpoint timing, which is the purpose of the bgwriter, and they don't change during the checkpoint interval, which is also less than ideal. If set to aggressively, it writes too much, and if too low, the checkpoint does too much I/O. We clearly need more bgwriter activity as the checkpoint approaches, and one that is more auto-tuned, like many of our other parameters. I think we created these settings to see how they worked in the field, so it probably time to reevaluate them based on field reports. I think the bgwriter should keep track of how far it is to the next checkpoint, and use that information to increase write activity. Basically now, during a checkpoint, the bgwriter does a full buffer scan and fsync's all dirty files, so it changes from the configuration parameter-defined behavior right to 100% activity. I think it would be ideal if we could ramp up the writes so that when it is 95% to the next checkpoint, it can be operating at 95% of the activity it would do during a checkpoint. My guess is if we can do that, we will have much smoother performance because we have more WAL writes just after checkpoint for newly-dirtied pages, and the new setup will give us more write activity just before checkpoint. One other idea is for the bgwriter to use O_DIRECT or O_SYNC to avoid the kernel cache, so we are sure data will be on disk by checkpoint time. This was avoided in the past because of the expense of second-guessing the kernel disk I/O scheduling algorithms. --- Tom Lane wrote: Kevin Grittner [EMAIL PROTECTED] writes: Jim C. Nasby [EMAIL PROTECTED] wrote: Generally, I try and configure the all* settings so that you'll get 1 clock-sweep per checkpoint_timeout. It's worked pretty well, but I don't have any actual tests to back that methodology up. We got to these numbers somewhat scientifically. I studied I/O patterns under production load and figured we should be able to handle about 800 writes in per 200 ms without causing problems. I have to admit that I based the percentages and the ratio between all and lru on gut feel after musing over the documentation. I like Kevin's settings better than what Jim suggests. If the bgwriter only makes one sweep between checkpoints then it's hardly going to make any impact at all on the number of dirty buffers the checkpoint will have to write. The point of the bgwriter is to reduce the checkpoint I/O spike by doing writes between checkpoints, and to have any meaningful impact on that, you'll need it to make the cycle several times. Another point here is that you want checkpoints to be pretty far apart to minimize the WAL load from full-page images. So again, a bgwriter that's only making one loop per checkpoint is not gonna be doing much. I wonder whether it would be feasible to teach the bgwriter to get more aggressive as the time for the next checkpoint approaches? Writes issued early in the interval have a much higher probability of being wasted (because the page gets re-dirtied later). But maybe that just reduces to what Takahiro-san already suggested, namely that checkpoint-time writes should be done with the same kind of scheduling the bgwriter uses outside checkpoints. We still have the problem that the real I/O storm is triggered by fsync() not write(), and we don't have a way to spread out the consequences of fsync(). regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if
Re: [HACKERS] Load distributed checkpoint
Tom Lane wrote: I like Kevin's settings better than what Jim suggests. If the bgwriter only makes one sweep between checkpoints then it's hardly going to make any impact at all on the number of dirty buffers the checkpoint will have to write. The point of the bgwriter is to reduce the checkpoint I/O spike by doing writes between checkpoints, and to have any meaningful impact on that, you'll need it to make the cycle several times. Another point here is that you want checkpoints to be pretty far apart to minimize the WAL load from full-page images. So again, a bgwriter that's only making one loop per checkpoint is not gonna be doing much. I missed the previous message but it sounds like you're operating under a different set of assumptions than the original poster. If you do a single sweep through all of the buffers *and sync them* then you've just finished a checkpoint -- the *previous* checkpoint. Not the subsequent one. That is, rather than trying to spread the load of the checkpoint out by getting the writes into the kernel sooner but make no attempt to sync them until checkpoint time, start the checkpoint as soon as the previous checkpoint finishes, and dribble the blocks of the checkpoint out slowly throughout an entire checkpoint cycle syncing them immediately using O_SYNC/ODIRECT. It's a fundamental shift in the idea of the purpose of bgwriter. Instead of trying to suck i/o away from the subsequent checkpoint it would be responsible for all the i/o of the previous checkpoint which would still be in progress for the entire time of checkpoint_timeout. It would only complete when bgwriter had finished doing its one full sweep. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Load distributed checkpoint
Gregory Stark [EMAIL PROTECTED] writes: It's a fundamental shift in the idea of the purpose of bgwriter. Instead of trying to suck i/o away from the subsequent checkpoint it would be responsible for all the i/o of the previous checkpoint which would still be in progress for the entire time of checkpoint_timeout. It would only complete when bgwriter had finished doing its one full sweep. I think that's basically the same as the original suggestion, which is to do checkpoints using less than the full I/O bandwidth of the machine --- tying it exactly to the default bgwriter rate may or may not be appropriate. The difficulty with such schemes is that if you go over to using O_DIRECT writes instead of fsync in the bgwriter, it's hard to avoid doing the same when a random backend has to write a dirty buffer --- yet you'd really rather that such a backend not have to wait for the ensuing I/O. And this gets a lot worse if checkpoints are slowed down, because it gets more likely that the bufmgr will run out of clean buffers and have to do a write() from a backend. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] unixware and --with-ldap
On Tue, Dec 12, 2006 at 04:42:50PM +0100, Albe Laurenz wrote: Or do shared libraries on Unixware generally 'not remember' the libraries they were linked against (this would be strange). It could be that whoever compiled libldap there did not include the dependancies at link time. It is legal to build a shared library without resolving all the symbols. libreadline was for a long time in the same position on many linux distributions. The upstream makefile did not specify -lncurses or -ltermcap so every user of readline had to specify that, rather than getting it automatically. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Grouped Index Tuples
Heikki Linnakangas wrote: Jim C. Nasby wrote: On Thu, Dec 07, 2006 at 10:30:11AM +, Heikki Linnakangas wrote: I've cut a new version of the GIT patch I posted earlier, and collected all my dispersed todo-lists, post-it notes, performance results, supplementary patches etc. I had to a single web-page: http://community.enterprisedb.com/git/ Perhaps the most interesting stuff apart from the patch itself is the performance results. I've run some CPU bound tests to measure the extra CPU overhead it causes. The CPU overhead is significant, the worst case being a select of a single row from a table with just one integer column. However, the I/O savings are also the greatest for that same test case, as the table grows and the test becomes I/O bound. I don't have the numbers now, but earlier runs showed that the duration of the test was roughly halved, which makes sense because the patch reduced the index size so that it fit in memory, reducing the number of physical I/Os required per select from 2 to 1. ISTM that if we want to enable GIT automatically, we need a way to either reduce the CPU overhead, or have a smart heuristic to tune the feature so that it's only enabled when it's beneficial. The maintain_cluster_order patch is useful by itself, and handles an existing TODO regarding pulling pages out of WAL in a specified order to maintain clustering. Pull pages out of WAL? That must be a typo... I assume he meant FSM (free space map). -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] psql commandline conninfo
I have been working on providing psql with the ability to accept a libpq conninfo string, so that the following now works for me: psql conn:service=sname user=uname Instead of providing yet another switch, I overloaded the dbname parameter so that if it has the recognised prefix the remainder is treated as a conninfo string. I have 3 questions: 1. Is this a good way to go, or should we just provide yet another switch? 2. If this is ok, what should the prefix be? is conn: ok? 3. Should we append settings from other switches to the conninfo (e.g. -U or -h), or should we just ignore them? If we ignore them should we warn about that if they are present? cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql commandline conninfo
Andrew Dunstan [EMAIL PROTECTED] writes: I have been working on providing psql with the ability to accept a libpq conninfo string, so that the following now works for me: psql conn:service=sname user=uname Perhaps this should be implemented in libpq, not at the psql level? Otherwise you're going to have to do it over for each client program. 2. If this is ok, what should the prefix be? is conn: ok? I'd prefer to dispense with the conn:, so that this looks somehow designed in rather than bolted on after the fact. I'm tempted to suggest that if the dbname includes = it should be considered a conninfo string; perhaps also after checking keyword validity. 3. Should we append settings from other switches to the conninfo (e.g. -U or -h), or should we just ignore them? If we ignore them should we warn about that if they are present? Do we complain about duplicate keywords in conninfo now? I think not, so appending the other switches would have the result of overriding what is in conninfo, which is probably reasonable. (Actually, if you implement this in libpq, there's probably no need to form the appended string explicitly --- just process the other options of PQsetdbLogin() after the conninfo.) regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] EXPLAIN ANALYZE
Richard Huxton wrote: Simon Riggs wrote: Intermediate results are always better than none at all. I do understand what a partial execution would look like - frequently it is the preparatory stages that slow a query down - costly sorts, underestimated hash joins etc. Other times it is loop underestimation, which can usually be seen fairly quickly. Surely all you're interested in is where the actual plan differs from the expected plan? Could you not just have a mode that issues NOTICEs when expected/actual number of rows differ by more than a set amount? You'd probably want two NOTICEs - one when the threshold is exceeded, one when the node completes. Right, we already have a TODO: * Have EXPLAIN ANALYZE highlight poor optimizer estimates I was thinking we could issue NOTICE when the estimates differed from the actual by a specified percentage, and that NOTICE could be issued while the query is still processing, assuming the stage completes before the query does. This seems much easier than doing protocol changes. TODO updated: * Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and actual row counts differ by a specified percentage -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql commandline conninfo
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I have been working on providing psql with the ability to accept a libpq conninfo string, so that the following now works for me: psql conn:service=sname user=uname Perhaps this should be implemented in libpq, not at the psql level? Otherwise you're going to have to do it over for each client program. Just as well I haven't spent much time on it, eh? 2. If this is ok, what should the prefix be? is conn: ok? I'd prefer to dispense with the conn:, so that this looks somehow designed in rather than bolted on after the fact. well, I thought this made it look slightly URLish, a bit like a jbdc URL. But OK. no big deal. I'm tempted to suggest that if the dbname includes = it should be considered a conninfo string; perhaps also after checking keyword validity. Now I look at fe-connect.c more closely, I'm tempted just to try parsing the dbname param as a conninfo string, and if it doesn't work fall back on a plain dbname. I could greatly reduce the chance of following the failure path by just looking for an = but I think anything more is likely to be redundant. 3. Should we append settings from other switches to the conninfo (e.g. -U or -h), or should we just ignore them? If we ignore them should we warn about that if they are present? Do we complain about duplicate keywords in conninfo now? I think not, so appending the other switches would have the result of overriding what is in conninfo, which is probably reasonable. (Actually, if you implement this in libpq, there's probably no need to form the appended string explicitly --- just process the other options of PQsetdbLogin() after the conninfo.) OK. I think this just falls out. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql commandline conninfo
On Tue, Dec 12, 2006 at 05:44:07PM -0500, Andrew Dunstan wrote: Now I look at fe-connect.c more closely, I'm tempted just to try parsing the dbname param as a conninfo string, and if it doesn't work fall back on a plain dbname. I could greatly reduce the chance of following the failure path by just looking for an = but I think anything more is likely to be redundant. Does that mean that: psql -d service=myservice should Just Work(tm)? That would be nice. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[HACKERS] Better management of mergejoinable operators
I noticed today that process_implied_equality() still contains an ugly hack that should have been got rid of awhile ago: it assumes that mergejoinable operators are named =. This has been a bogus assumption for several releases, as illustrated by this failure: regression=# select * from text_tbl a,text_tbl b,text_tbl c where a.f1 ~=~ b.f1 and b.f1 ~=~ c.f1; ERROR: equality operator for types text and text should be merge-joinable, but isn't It can also be fooled by schema-search-path issues, if the needed operator exists but isn't in the path. Since we've not heard complaints from the field about this, I'm not feeling urgent about having a back-patchable solution, but I want to find one going forward. What is actually needed in this function is to be able to find a mergejoinable equality operator whose oprlsortop and oprrsortop are the two sortops already known for the input pathkey columns. We have a couple of problems doing that though: first, with the present system catalog layout there seems no way to do that short of a seqscan through all of pg_operator; and second, what if there's not a unique answer, ie, multiple equality operators alleging the same lsortop/rsortop? Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) So what I'm thinking about is a unique index on oprlsortop/oprrsortop; that would both allow efficient search, and prevent multiple answers. Now we can't do that directly because most of the entries in pg_operator in fact contain zeroes in these columns, and would cause uniqueness failures. Probably the cleanest answer would be to allow these two columns to be NULL, not zero, when not meaningful; but that would be a bit of a mess to implement because of the code's assumption of fixed layout for pg_operator tuples. What I'm considering doing is moving the oprlsortop/oprrsortop/ oprltcmpop/oprgtcmpop fields out of pg_operator and into a new auxiliary catalog, named say pg_mergejoinop, that would have entries only for mergejoinable equality operators. This would have the same kind of relationship to pg_operator that pg_aggregate has to pg_proc: if a pg_operator entry has oprcanmerge true, then there's an extension row for it in pg_mergejoinop. The catalog would be fairly small and cheap to search (48 entries in a default install, as of CVS head), and could support a unique index to constrain the oprlsortop/oprrsortop columns. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql commandline conninfo
Martijn van Oosterhout kleptog@svana.org writes: Does that mean that: psql -d service=myservice should Just Work(tm)? That would be nice. Even more to the point, psql service=myservice which is why we want to overload dbname rather than any of the other PQsetdbLogin parameters for this --- dbname has pride of place in the command line syntax for several of the client programs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql commandline conninfo
Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
Does that mean that:
psql -d service=myservice
should Just Work(tm)? That would be nice.
Even more to the point,
psql service=myservice
which is why we want to overload dbname rather than any of the other
PQsetdbLogin parameters for this --- dbname has pride of place in the
command line syntax for several of the client programs.
regards, tom lane
Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-)
cheers
andrew
Index: src/interfaces/libpq/fe-connect.c
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.339
diff -c -r1.339 fe-connect.c
*** src/interfaces/libpq/fe-connect.c 21 Nov 2006 16:28:00 - 1.339
--- src/interfaces/libpq/fe-connect.c 12 Dec 2006 22:49:28 -
***
*** 567,572
--- 567,573
const char *pwd)
{
PGconn *conn;
+ bool have_conninfo = false;
/*
* Allocate memory for the conn structure
***
*** 575,585
if (conn == NULL)
return NULL;
/*
* Parse an empty conninfo string in order to set up the same defaults
!* that PQconnectdb() would use.
*/
! if (!connectOptions1(conn, ))
return conn;
/*
--- 576,609
if (conn == NULL)
return NULL;
+ /*
+* Have we got something that might be a conninfo string?
+* If so, try that first.
+*/
+ if (dbName strchr(dbName,'='))
+ {
+ if(connectOptions1(conn,dbName))
+ {
+ /* it was a conninfo string */
+ have_conninfo = true;
+ }
+ else
+ {
+ /* put things back the way they were so we can try
again */
+ freePGconn(conn);
+ conn = makeEmptyPGconn();
+ if (conn == NULL)
+ return NULL;
+
+ }
+ }
+
/*
* Parse an empty conninfo string in order to set up the same defaults
!* that PQconnectdb() would use. Skip this if we already found a
!* conninfo string.
*/
! if (!have_conninfo !connectOptions1(conn, ))
return conn;
/*
***
*** 613,619
conn-pgtty = strdup(pgtty);
}
! if (dbName dbName[0] != '\0')
{
if (conn-dbName)
free(conn-dbName);
--- 637,643
conn-pgtty = strdup(pgtty);
}
! if (!have_conninfo dbName dbName[0] != '\0')
{
if (conn-dbName)
free(conn-dbName);
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] libpq.a in a universal binary
I am trying to create the libpq.a as a universal binary (both ppc and intel macs). Does anyone have any information on this process? Thanks, Ted ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql commandline conninfo
Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 7:56 , Tom Lane wrote: Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) So what I'm thinking about is a unique index on oprlsortop/oprrsortop; that would both allow efficient search, and prevent multiple answers. I think this makes sense. Would this be affected at all by equality of text strings, taking into account locale? Or would there be equality for text in each locale (so oprlsortop and oprrsortop would always be not only the same type (text) but also of the same locale)? I'd think this is would be the case so it wouldn't end up being a problem. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better management of mergejoinable operators
Michael Glaesemann [EMAIL PROTECTED] writes: On Dec 13, 2006, at 7:56 , Tom Lane wrote: Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) I think this makes sense. Would this be affected at all by equality of text strings, taking into account locale? If it is, then we'd have far greater problems to deal with than just this one --- the entire operator/function structure is built on the assumption that there is, say, only one = between any two datatypes. I think if locale wants actually different operators then it will have to make strings of different locales be distinct datatypes. It's probably a lot more practical to keep text as just one datatype and store the locale indicator as part of each value. (There's also been some blue sky thoughts about trying to keep it in typmod, but that wouldn't result in multiple operators either.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Concurrent connections in psql
I mentioned this a while back, now that 8.2 is out perhaps others will be more interested in new code. Currently Postgres regression tests only test functionality within a single session. There are no regression tests that test the transaction semantics or locking behaviour across multiple transactions. I modified psql to allow you to open multiple connections and switch between them with a sort of csh job control style interface. It actually works out pretty well. It's fairly easy to write regression tests for basic 2-client or 3-client cases. The actual user interface may need some discussion though. I didn't want to play the name game so I just prefixed all my commands with c and figured we can always rename them later. And experience with actually writing the tests shows that the explicit \cwait command which was needed to eliminate (in practice if not in theory) race conditions in regression tests turns out to be more flexibility than necessary. Since you end up having to insert one in precisely predictable locations -- namely after every asynchronous command and after every connection switch -- perhaps it would be simpler to just have a \pset cwait command that automatically introduces timeouts in precisely those places. A brief explanation including an example regression test (the SAVEPOINT locking bug discovered recently) and the patch here: http://community.enterprisedb.com/concurrent/index.html -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql commandline conninfo
On Dec 12, 2006, at 3:37 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? I would personally prefer a real option over a prefix, i.e. -- dbconn=service=foo though the inline conninfo string in place of the dbname would be ideal. Perhaps like Tom suggests, if the value matches a conninfo regex (slightly more rigid than just containing an equals character) then we assume it is a conninfo string, but never try it as a dbname. If someone has a database named like a conninfo string (c'mon folks ;^) then they would need to pass it as explicitly an argument to '-d' or '--dbname', not as a bare argument. This is not completely b/w compatible of course, but IMO the added convenience outweighs the incompatibility. -Casey ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better management of mergejoinable operators
I wrote: Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) BTW, I think it's possible to prove that there need never be two for the case of both sides the same datatype. If we have a sortop A B on a single datatype, then its commutator is well defined: A B if and only if B A. And by the trichotomy law, A = B must be true in exactly those cases for which neither A B nor A B. So there is only one possible behavior for an equality operator that is consistent with the sortop. (This is, in fact, the reason that we can get away with considering a single sortop as fully specifying a sort order.) This argument doesn't immediately go through if A and B are of different datatypes, but it's pretty hard to think of a case where it wouldn't hold. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] EXPLAIN ANALYZE
On Tue, 2006-12-12 at 17:30 -0500, Bruce Momjian wrote: * Have EXPLAIN ANALYZE highlight poor optimizer estimates TODO updated: * Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and actual row counts differ by a specified percentage I don't think this is an improvement. The old wording describes a broad set of possible improvements. Your new text describes one way of implementing a subset of the former TODO wording. -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] psql commandline conninfo
Casey Duncan wrote: On Dec 12, 2006, at 3:37 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? I would personally prefer a real option over a prefix, i.e. -- dbconn=service=foo though the inline conninfo string in place of the dbname would be ideal. Perhaps like Tom suggests, if the value matches a conninfo regex (slightly more rigid than just containing an equals character) then we assume it is a conninfo string, but never try it as a dbname. If someone has a database named like a conninfo string (c'mon folks ;^) then they would need to pass it as explicitly an argument to '-d' or '--dbname', not as a bare argument. You are confusing two things here. The way the patch is written it simply interprets the parameter passed to libpq - it has no idea what was used (if anything) on the commandline. The alternative, as Tom pointed out, is to patch every client. I'm inclined to say we should go back almost to my original suggestion: a param that starts with conn: and contains an = is conclusively presumed to be a conninfo string. The workaround for a db name like that (say conn:foo=bar) is to use conn:dbname='conn:foo=bar'. You'll soon get tired of that and rename the db to something sane :-) cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] psql commandline conninfo
On Dec 12, 2006, at 5:16 PM, Andrew Dunstan wrote: Casey Duncan wrote: On Dec 12, 2006, at 3:37 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? I would personally prefer a real option over a prefix, i.e. -- dbconn=service=foo though the inline conninfo string in place of the dbname would be ideal. Perhaps like Tom suggests, if the value matches a conninfo regex (slightly more rigid than just containing an equals character) then we assume it is a conninfo string, but never try it as a dbname. If someone has a database named like a conninfo string (c'mon folks ;^) then they would need to pass it as explicitly an argument to '-d' or '--dbname', not as a bare argument. You are confusing two things here. The way the patch is written it simply interprets the parameter passed to libpq - it has no idea what was used (if anything) on the commandline. The alternative, as Tom pointed out, is to patch every client. I was speaking from and end-user point of view, but I see your point. It's certainly attractive to just patch libpq and be done. However, that does have the side-effect of implicitly propagating the behavior to all libpg client software. That may be more unpleasantly surprising to more people then just changing the built-in postgresql client utilities. But then again it could also be considered a feature 8^) -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql commandline conninfo
Casey Duncan wrote: I was speaking from and end-user point of view, but I see your point. It's certainly attractive to just patch libpq and be done. However, that does have the side-effect of implicitly propagating the behavior to all libpg client software. That may be more unpleasantly surprising to more people then just changing the built-in postgresql client utilities. But then again it could also be considered a feature 8^) We change libpq from time to time. Besides, how many DBs are there that match the name pattern /^conn:.*=/ ? My guess is mighty few. So I don't expect lots of surprise. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] LOCK_DEBUG breaks compile in 8.2 (and possibly later)
I just noticed that defining LOCK_DEBUG causes a compile failure (have not delved into how to fix, but thought it would be worth noting at this point!): gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -I. -I../../../../src/include -c -o guc.o guc.c guc.c:1310: error: `FirstNormalObjectId' undeclared here (not in a function) guc.c:1310: error: initializer element is not constant ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 8:45 , Tom Lane wrote: the entire operator/function structure is built on the assumption that there is, say, only one = between any two datatypes. You mean only on = between any two values of a given datatype? Or is there something else I'm missing? So what you're doing will just reinforce that. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql commandline conninfo
Andrew Dunstan [EMAIL PROTECTED] writes: We change libpq from time to time. Besides, how many DBs are there that match the name pattern /^conn:.*=/ ? My guess is mighty few. So I don't expect lots of surprise. Um, but how many DB names have an = in them at all? Basically what this proposal is about is migrating from separated dbname/user/host/port/etc parameters to a unified conninfo parameter. That seems to me like a good long-term objective, and so I'm willing to break a few eggs on the way to the omelet, as long as we're not breaking any very likely usages. So: who here has a database with = in the name? And hands up if you've got a database whose name begins with conn:? I'm betting zero response rate on both of those, so see no reason to contort the long-term definition for a very marginal difference in the extent of backwards compatibility ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better management of mergejoinable operators
On Dec 13, 2006, at 12:33 , Michael Glaesemann wrote: On Dec 13, 2006, at 8:45 , Tom Lane wrote: the entire operator/function structure is built on the assumption that there is, say, only one = between any two datatypes. You mean only on = between any two values of a given datatype? Ignore that. :) if that were true, you wouldn't need to have both left and right argument types. I think I got it now. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] EXPLAIN ANALYZE
Neil Conway wrote: On Tue, 2006-12-12 at 17:30 -0500, Bruce Momjian wrote: * Have EXPLAIN ANALYZE highlight poor optimizer estimates TODO updated: * Have EXPLAIN ANALYZE issue NOTICE messages when the estimated and actual row counts differ by a specified percentage I don't think this is an improvement. The old wording describes a broad set of possible improvements. Your new text describes one way of implementing a subset of the former TODO wording. Well, we can still do a broader implementation if we want it. Do you have any suggestions? Basically, the more specific, the more likely we will get someone to do it, and we can always add more details. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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
[HACKERS] 8.1.5 release note
As usual, following item in the 8.1.5 release note is pretty vague: * Efficiency improvements in hash tables and bitmap index scans(Tom) Especially I'm wondering what was actually improved in bitmap index scans. I see several commit messages regarding bitmap index scans, but I cannot figure out which one is related to the item. Any idea? -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] recovery.conf parsing problems
While testing a PITR recovery, I discovered that recovery.conf doesn't seem to allow specifying ' in the command string, making it hard to protect the restore_command against problematic filenames (whitespace etc.). This doesn't seem to be a problem for archive_command, which allows \' (e.g. archive_command = '/path/to/script \'%f\' \'%p\''). Should this be fixed? -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Grouped Index Tuples
On Tue, Dec 12, 2006 at 03:26:32PM -0500, Bruce Momjian wrote: Heikki Linnakangas wrote: The maintain_cluster_order patch is useful by itself, and handles an existing TODO regarding pulling pages out of WAL in a specified order to maintain clustering. Pull pages out of WAL? That must be a typo... I assume he meant FSM (free space map). Yup. Brainfart. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Better management of mergejoinable operators
On 2006-12-13, Tom Lane [EMAIL PROTECTED] wrote: I wrote: Right offhand I cannot see a reason why there should be different equality operators with the same sortops. (If anyone can come up with a plausible scenario for that, stop me here...) BTW, I think it's possible to prove that there need never be two for the case of both sides the same datatype. If we have a sortop A B on a single datatype, then its commutator is well defined: A B if and only if B A. And by the trichotomy law, A = B must be true in exactly those cases for which neither A B nor A B. So there is only one possible behavior for an equality operator that is consistent with the sortop. Counterexample even for a single data type: define an operator x =* y which is true when 2x = y. This is mergejoinable using the following operators: SORT1 = , SORT2 = , LTCMP = (2x y), RTCMP = (2x y) (which is of course the same sortops as for regular =). The LTCMP and GTCMP operators imply a unique join operator due to trichotomy, but this is not true for the sortops. While the above is a bit contrived, I think non-contrived examples could be found too. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(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: [HACKERS] LOCK_DEBUG breaks compile in 8.2 (and possibly later)
Mark Kirkwood [EMAIL PROTECTED] writes: I just noticed that defining LOCK_DEBUG causes a compile failure Still another demonstration that Bruce's approach to removing unused #includes does not work. Patched in HEAD ... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Better management of mergejoinable operators
Andrew - Supernews [EMAIL PROTECTED] writes: On 2006-12-13, Tom Lane [EMAIL PROTECTED] wrote: BTW, I think it's possible to prove that there need never be two for the case of both sides the same datatype. Counterexample even for a single data type: define an operator x =* y which is true when 2x = y. This is mergejoinable using the following operators: SORT1 = , SORT2 = , LTCMP = (2x y), RTCMP = (2x y) (which is of course the same sortops as for regular =). I think not --- the corresponding sort operators would have to be 2x y etc, else the trichotomy law fails, and so do all standard sort algorithms. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Load distributed checkpoint
On Fri, Dec 08, 2006 at 11:43:27AM -0500, Tom Lane wrote: Kevin Grittner [EMAIL PROTECTED] writes: Jim C. Nasby [EMAIL PROTECTED] wrote: Generally, I try and configure the all* settings so that you'll get 1 clock-sweep per checkpoint_timeout. It's worked pretty well, but I don't have any actual tests to back that methodology up. We got to these numbers somewhat scientifically. I studied I/O patterns under production load and figured we should be able to handle about 800 writes in per 200 ms without causing problems. I have to admit that I based the percentages and the ratio between all and lru on gut feel after musing over the documentation. I like Kevin's settings better than what Jim suggests. If the bgwriter only makes one sweep between checkpoints then it's hardly going to make any impact at all on the number of dirty buffers the checkpoint will have to write. The point of the bgwriter is to reduce the checkpoint I/O spike by doing writes between checkpoints, and to have any meaningful impact on that, you'll need it to make the cycle several times. It would be good if the docs included more detailed info on how exactly the bgwriter goes about flushing stuff to disk. You can certainly read them and think that the bgwriter just goes through and issues writes for any dirty buffers it finds. Though, looking at BgBufferSync, I think it actually does write out pages during the all scan, regardless of what usage_count says. I wonder whether it would be feasible to teach the bgwriter to get more aggressive as the time for the next checkpoint approaches? Writes issued early in the interval have a much higher probability of being wasted (because the page gets re-dirtied later). But maybe that just reduces to what Takahiro-san already suggested, namely that checkpoint-time writes should be done with the same kind of scheduling the bgwriter uses outside checkpoints. We still have the problem that the real I/O storm is triggered by fsync() not write(), and we don't have a way to spread out the consequences of fsync(). Would the ramp-up of write activity push the kernel to actually write stuff? My understanding is that most OSes have a time limit on how long they'll let a write-request sit in cache, so ISTM a better way to smooth out disk IO is to write things in a steady stream. If the bgwriter takes the buffer access counter into account when deciding what to write out, it might make sense to write more recently accessed pages as checkpoint nears. The idea being that odds are good those buffers are about to get flushed by BufferSync() anyway. Also, I have a dumb question... BgBufferSync uses buf_id1 to keep track of what buffer the bgwriter_all scan is looking at, which means that it should remember where it was at the end of the last scan; yet it's initialized to 0 every time BgBufferSync is called. Is there someplace else that is remembering where the complete scan is leaving off when bgwriter_all_percent or bgwriter_all_maxpages is hit? Or does the scan in fact just keep re-scanning the beginning of the buffers? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Better management of mergejoinable operators
On 2006-12-13, Tom Lane [EMAIL PROTECTED] wrote: Andrew - Supernews [EMAIL PROTECTED] writes: On 2006-12-13, Tom Lane [EMAIL PROTECTED] wrote: BTW, I think it's possible to prove that there need never be two for the case of both sides the same datatype. Counterexample even for a single data type: define an operator x =* y which is true when 2x = y. This is mergejoinable using the following operators: SORT1 = , SORT2 = , LTCMP = (2x y), RTCMP = (2x y) (which is of course the same sortops as for regular =). I think not --- the corresponding sort operators would have to be 2x y etc, else the trichotomy law fails, and so do all standard sort algorithms. No, because if x x' then 2x 2x'. Or to put it another way, doing a merge join on (2x = y) simply requires matching the sorted lists of x's and y's against each other in a different place, rather than changing the sort order of either. You're suffering from a fundamental confusion between the ltcmp/rtcmp operators (which indeed must be trichotomous with the join operator) and the sort operators. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] psql commandline conninfo
Tom Lane wrote: We change libpq from time to time. Besides, how many DBs are there that match the name pattern /^conn:.*=/ ? My guess is mighty few. So I don't expect lots of surprise. Um, but how many DB names have an = in them at all? Basically what this proposal is about is migrating from separated dbname/user/host/port/etc parameters to a unified conninfo parameter. That seems to me like a good long-term objective, and so I'm willing to break a few eggs on the way to the omelet, as long as we're not breaking any very likely usages. So: who here has a database with = in the name? And hands up if you've got a database whose name begins with conn:? I'm betting zero response rate on both of those, so see no reason to contort the long-term definition for a very marginal difference in the extent of backwards compatibility ... I second the idea to have libpq interpret a database name with = in it as a connection parameter string. The conn: seems artificial and difficult to remember to me. As to the problem of cryptic error messages from psql, can't we improve libpq's error response if it gets a database name that causes problems when parsed as a connection parameter string? That would take care of that. Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] A question about ExplainOnePlan()
Hi All,
In ExplainOnePlan(), we are calling ExecutorStart() and ExecutorEnd()
even if we are not doing EXPLAIN ANALYZE. Whereas, ExecutorRun() is called
only if we are ANALYZEing.
Can we avoid calls to Executor{Start|End}() here, or is it necessary to
call them even for non-ANALYZE case?
Regards,
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | yahoo }.com
