Re: [HACKERS] [PATCHES] Dbsize backend integration
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 06 July 2005 04:11 To: Tom Lane Cc: Dave Page; Christopher Kings-Lynne; Robert Treat; Dawid Kuroczko; Andreas Pflug; PostgreSQL-patches; PostgreSQL-development Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration Tom Lane wrote: pg_relation_size plus pg_complete_relation_size is fine. Ship it... OK. Updated version attached. Regards, Dave. dbsize.c Description: dbsize.c dbsize.patch Description: dbsize.patch ---(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
Re: [HACKERS] pgcrypto 3des failure, OpenSSL 0.9.8, Solaris 9/sparc
On Tue, Jul 05, 2005 at 07:20:48PM -0400, Tom Lane wrote: Marko Kreen marko@l-t.ee writes: Ok Tom, you win. It is indeed possible to make it work, and the resulting makefile is even cleaner than before. Following patch thus autoconfigures pgcrypto. It drops the possibility to use libc's crypt, which was pointless. Applied with a little extra hacking (completely overriding the original values of PG_CPPFLAGS and SHLIB_LINK was unwise, and in fact guaranteed to fail on machines where OpenSSL isn't in the default location). Your PG_CPPFLAGS change does not work - it gets always RAND_SILLY. If I change it from := to =, make complains about recursion. -- marko Index: contrib/pgcrypto/Makefile === RCS file: /opt/arc/cvs2/pgsql/contrib/pgcrypto/Makefile,v retrieving revision 1.16 diff -u -c -r1.16 Makefile *** contrib/pgcrypto/Makefile 5 Jul 2005 23:18:44 - 1.16 --- contrib/pgcrypto/Makefile 6 Jul 2005 10:37:26 - *** *** 16,22 CF_TESTS = $(if $(subst no,,$(with_openssl)), $(OSSL_TESTS)) CF_CFLAGS = $(if $(subst no,,$(with_openssl)), $(OSSL_CFLAGS), $(INT_CFLAGS)) ! PG_CPPFLAGS := $(CF_CFLAGS) -I$(srcdir) $(PG_CPPFLAGS) SRCS = pgcrypto.c px.c px-hmac.c px-crypt.c misc.c random.c \ crypt-gensalt.c crypt-blowfish.c crypt-des.c \ --- 16,22 CF_TESTS = $(if $(subst no,,$(with_openssl)), $(OSSL_TESTS)) CF_CFLAGS = $(if $(subst no,,$(with_openssl)), $(OSSL_CFLAGS), $(INT_CFLAGS)) ! PG_CPPFLAGS = $(CF_CFLAGS) -I$(srcdir) SRCS = pgcrypto.c px.c px-hmac.c px-crypt.c misc.c random.c \ crypt-gensalt.c crypt-blowfish.c crypt-des.c \ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] timezone changes break windows and cygwin
Tom Lane said: Bruce Momjian pgman@candle.pha.pa.us writes: OK, now we have a problem. :-( No kidding. I said to begin with that this plan to use target-specific configuration knowledge to build a program executable by the host would not work. I'm for reverting Peter's initial patch; maybe we can someday find an answer, but this ain't it. +1. cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timezone changes break windows and cygwin
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: OK, now we have a problem. :-( No kidding. I said to begin with that this plan to use target-specific configuration knowledge to build a program executable by the host would not work. I'm for reverting Peter's initial patch; maybe we can someday find an answer, but this ain't it. Yea, I knew my original NO_PGPORT wasn't going to be the last, but now we are stuck. Add to this something Magnus mentioned that I did not see until just now. The backend links in timezone/SUBSYS.o, which doesn't use pgport, so you have pgport versions and native versions of some functions in the same backend binary. I am not sure that will always work. Add to that, are those object files fully compatible with the backend? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] timezone changes break windows and cygwin
Bruce Momjian pgman@candle.pha.pa.us writes: Add to this something Magnus mentioned that I did not see until just now. The backend links in timezone/SUBSYS.o, which doesn't use pgport, so you have pgport versions and native versions of some functions in the same backend binary. I am not sure that will always work. Add to that, are those object files fully compatible with the backend? The patch was never supposed to change timezone/SUBSYS.o! Only the zic executable. If it's having any side effects on what goes into SUBSYS.o, it's simply wrong. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] timezone changes break windows and cygwin
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Add to this something Magnus mentioned that I did not see until just now. The backend links in timezone/SUBSYS.o, which doesn't use pgport, so you have pgport versions and native versions of some functions in the same backend binary. I am not sure that will always work. Add to that, are those object files fully compatible with the backend? The patch was never supposed to change timezone/SUBSYS.o! Only the zic executable. If it's having any side effects on what goes into SUBSYS.o, it's simply wrong. Well, my NO_PGPORT is affecting timezone/SUBSYS.o because there is no way to compile zic without removing pgport library symbols from the object files. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: [HACKERS] timezone changes break windows and cygwin
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The patch was never supposed to change timezone/SUBSYS.o! Only the zic executable. If it's having any side effects on what goes into SUBSYS.o, it's simply wrong. Well, my NO_PGPORT is affecting timezone/SUBSYS.o because there is no way to compile zic without removing pgport library symbols from the object files. We might have to move zic into a separate directory so that it can be compiled on its own with its own copies of the relevant .o files. In the meantime though it's utterly clear that this entire series of patches is a failed experiment. Please revert the lot. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] timezone changes break windows and cygwin
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: The patch was never supposed to change timezone/SUBSYS.o! Only the zic executable. If it's having any side effects on what goes into SUBSYS.o, it's simply wrong. Well, my NO_PGPORT is affecting timezone/SUBSYS.o because there is no way to compile zic without removing pgport library symbols from the object files. We might have to move zic into a separate directory so that it can be compiled on its own with its own copies of the relevant .o files. In the meantime though it's utterly clear that this entire series of patches is a failed experiment. Please revert the lot. I can pull out NO_PGPORT, but what commit causes the original problem? Was it only one? (I wasn't watching.) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timezone changes break windows and cygwin
Bruce Momjian pgman@candle.pha.pa.us writes: Tom Lane wrote: In the meantime though it's utterly clear that this entire series of patches is a failed experiment. Please revert the lot. I can pull out NO_PGPORT, but what commit causes the original problem? Was it only one? (I wasn't watching.) This one: 2005-07-03 14:54 petere * configure, configure.in, doc/src/sgml/installation.sgml, src/Makefile.global.in, src/timezone/Makefile: Support cross compilation by compiling zic with a native compiler. This relies on the output of zic being platform independent, but that is currently the case. and all the subsequent changes touching src/timezone/ regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] GiST notice
It should be mentioned in documentation that after pgsql's crash GiST indexes may restore some incorrect way: with invalid tuples. Of course, not every time. Index will work absolutly correct but possibly with some performance degradation (not big). 'Vacuum full' resolves this problem and repairs invalid tuples. If that problem is detected during recovery, postgres says to log : LOG: Detected incomplete insert into GiST index 1663/16385/16458; It's desirable to vacuum or reindex index More, if usial vacuum will say on such index: NOTICE: It's desirable to vacuum full or reindex GiST index 'idx' due to crash recovery Sorry, but my English doesn't make it possible to write correct phrase to documentation. May be thats phrases too... Just for reminder, I found strange trap on vacuum running concurrently with a lot of other queries: http://www.pgsql.ru/db/mw/msg.html?mid=2077426 http://www.pgsql.ru/db/mw/msg.html?mid=2078029 In short: it caused approximatly one time per 2-4 million statements (with my scripts at http://www.sigaev.ru/gist/, PIII/1133 MHz and Quad Xeon/500MHz), I got traps: TRAP: FailedAssertion(!((*curpage)-offsets_used == num_tuples), File: vacuum.c, Line: 2766) LOG: server process (PID 15847) was terminated by signal 6 It's definitly bug in a vaccum code, I got the same trap without any GiST indexes (to reproduce, just comment out 'create index' command in my script). -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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
Re: [HACKERS] [INTERFACES] By Passed Domain Constraints
Robert Perry [EMAIL PROTECTED] writes: I have also been bitten by the problem you are describing. But, that one is a problem even when called from psql if I am not mistaken. Does psql not use pqlib? Perhaps it is something about PQexecParams that is the problem. I will test in a little while. [ thinks about it... ] If you've declared the function input parameter as a domain type and then write a parameterized query like ... function($1) ... and don't specify any particular datatype for the parameter symbol, I think the backend will infer the domain type as the parameter type. Which would also allow bypassing the domain checks. You could work around this by explicitly specifying the parameter type as text or varchar or whatever the domain's base type is. I wonder though if we oughtn't change the backend so that the inferred type of a parameter symbol is never a domain, but the domain's base type. That would force the proper application of CoerceToDomain inside the constructed query parsetree. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pgcrypto 3des failure, OpenSSL 0.9.8, Solaris 9/sparc
Marko Kreen marko@l-t.ee writes: ! PG_CPPFLAGS := $(CF_CFLAGS) -I$(srcdir) $(PG_CPPFLAGS) ... ! PG_CPPFLAGS = $(CF_CFLAGS) -I$(srcdir) Ah, right. Actually we don't need -I$(srcdir) either, since pgxs.mk adds that automatically. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] GiST notice
Teodor Sigaev wrote: It should be mentioned in documentation that after pgsql's crash GiST indexes may restore some incorrect way: with invalid tuples. Of course, not every time. Index will work absolutly correct but possibly with some performance degradation (not big). 'Vacuum full' resolves this problem and repairs invalid tuples. Can you also use reindex? Sincerely, Joshua D. Drake If that problem is detected during recovery, postgres says to log : LOG: Detected incomplete insert into GiST index 1663/16385/16458; It's desirable to vacuum or reindex index More, if usial vacuum will say on such index: NOTICE: It's desirable to vacuum full or reindex GiST index 'idx' due to crash recovery Sorry, but my English doesn't make it possible to write correct phrase to documentation. May be thats phrases too... Just for reminder, I found strange trap on vacuum running concurrently with a lot of other queries: http://www.pgsql.ru/db/mw/msg.html?mid=2077426 http://www.pgsql.ru/db/mw/msg.html?mid=2078029 In short: it caused approximatly one time per 2-4 million statements (with my scripts at http://www.sigaev.ru/gist/, PIII/1133 MHz and Quad Xeon/500MHz), I got traps: TRAP: FailedAssertion(!((*curpage)-offsets_used == num_tuples), File: vacuum.c, Line: 2766) LOG: server process (PID 15847) was terminated by signal 6 It's definitly bug in a vaccum code, I got the same trap without any GiST indexes (to reproduce, just comment out 'create index' command in my script). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GiST notice
On Wed, 6 Jul 2005, Joshua D. Drake wrote: Teodor Sigaev wrote: It should be mentioned in documentation that after pgsql's crash GiST indexes may restore some incorrect way: with invalid tuples. Of course, not every time. Index will work absolutly correct but possibly with some performance degradation (not big). 'Vacuum full' resolves this problem and repairs invalid tuples. Can you also use reindex? of course ! Sincerely, Joshua D. Drake If that problem is detected during recovery, postgres says to log : LOG: Detected incomplete insert into GiST index 1663/16385/16458; It's desirable to vacuum or reindex index More, if usial vacuum will say on such index: NOTICE: It's desirable to vacuum full or reindex GiST index 'idx' due to crash recovery Sorry, but my English doesn't make it possible to write correct phrase to documentation. May be thats phrases too... Just for reminder, I found strange trap on vacuum running concurrently with a lot of other queries: http://www.pgsql.ru/db/mw/msg.html?mid=2077426 http://www.pgsql.ru/db/mw/msg.html?mid=2078029 In short: it caused approximatly one time per 2-4 million statements (with my scripts at http://www.sigaev.ru/gist/, PIII/1133 MHz and Quad Xeon/500MHz), I got traps: TRAP: FailedAssertion(!((*curpage)-offsets_used == num_tuples), File: vacuum.c, Line: 2766) LOG: server process (PID 15847) was terminated by signal 6 It's definitly bug in a vaccum code, I got the same trap without any GiST indexes (to reproduce, just comment out 'create index' command in my script). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(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] timezone changes break windows and cygwin
Tom Lane wrote: I can pull out NO_PGPORT, but what commit causes the original problem? Was it only one? (I wasn't watching.) This one: 2005-07-03 14:54 petere Attached is the patch, in case someone wants to revert it. (I'd do it myself but I suppose this would interfere with rolling back the subsequent patches.) I apologize for all the trouble. -- Peter Eisentraut http://developer.postgresql.org/~petere/ diff -ur ../cvs-pgsql/configure ./configure --- ../cvs-pgsql/configure 2005-07-01 20:16:43.0 +0200 +++ ./configure 2005-07-03 20:12:54.531260250 +0200 @@ -314,7 +314,7 @@ # include unistd.h #endif -ac_subst_vars='SHELL PATH_SEPARATOR PACKAGE_NAME PACKAGE_TARNAME PACKAGE_VERSION PACKAGE_STRING PACKAGE_BUGREPORT exec_prefix prefix program_transform_name bindir sbindir libexecdir datadir sysconfdir sharedstatedir localstatedir libdir includedir oldincludedir infodir mandir build_alias host_alias target_alias DEFS ECHO_C ECHO_N ECHO_T LIBS configure_args build build_cpu build_vendor build_os host host_cpu host_vendor host_os PORTNAME docdir enable_nls WANTED_LANGUAGES default_port enable_shared enable_rpath enable_debug CC CFLAGS LDFLAGS CPPFLAGS ac_ct_CC EXEEXT OBJEXT CPP GCC TAS autodepend INCLUDES enable_thread_safety with_tcl with_perl with_python with_krb5 krb_srvtab with_pam with_bonjour with_openssl EGREP ELF_SYS LDFLAGS_SL AWK FLEX FLEXFLAGS LN_S LD with_gnu_ld ld_R_works RANLIB ac_ct_RANLIB LORDER TAR STRIP ac_ct_STRIP STRIP_STATIC_LIB STRIP_SHARED_LIB YACC YFLAGS PERL perl_archlibexp perl_privlibexp perl_useshrplib perl_embed_ldflags PYTHON python_version python_configdir python_includespec python_libdir python_libspec python_additional_libs HAVE_IPV6 LIBOBJS acx_pthread_config PTHREAD_CC PTHREAD_LIBS PTHREAD_CFLAGS HAVE_POSIX_SIGNALS MSGFMT MSGMERGE XGETTEXT localedir TCLSH TCL_CONFIG_SH TCL_INCLUDE_SPEC TCL_LIB_FILE TCL_LIBS TCL_LIB_SPEC TCL_SHARED_BUILD TCL_SHLIB_LD_LIBS NSGMLS JADE have_docbook DOCBOOKSTYLE COLLATEINDEX SGMLSPL vpath_build LTLIBOBJS' +ac_subst_vars='SHELL PATH_SEPARATOR PACKAGE_NAME PACKAGE_TARNAME PACKAGE_VERSION PACKAGE_STRING PACKAGE_BUGREPORT exec_prefix prefix program_transform_name bindir sbindir libexecdir datadir sysconfdir sharedstatedir localstatedir libdir includedir oldincludedir infodir mandir build_alias host_alias target_alias DEFS ECHO_C ECHO_N ECHO_T LIBS configure_args build build_cpu build_vendor build_os host host_cpu host_vendor host_os PORTNAME docdir enable_nls WANTED_LANGUAGES default_port enable_shared enable_rpath enable_debug CC CFLAGS LDFLAGS CPPFLAGS ac_ct_CC EXEEXT OBJEXT CPP GCC CC_FOR_BUILD TAS autodepend INCLUDES enable_thread_safety with_tcl with_perl with_python with_krb5 krb_srvtab with_pam with_bonjour with_openssl EGREP ELF_SYS LDFLAGS_SL AWK FLEX FLEXFLAGS LN_S LD with_gnu_ld ld_R_works RANLIB ac_ct_RANLIB LORDER TAR STRIP ac_ct_STRIP STRIP_STATIC_LIB STRIP_SHARED_LIB YACC YFLAGS PERL perl_archlibexp perl_privlibexp perl_useshrplib perl_embed_ldflags PYTHON python_version python_configdir python_includespec python_libdir python_libspec python_additional_libs HAVE_IPV6 LIBOBJS acx_pthread_config PTHREAD_CC PTHREAD_LIBS PTHREAD_CFLAGS HAVE_POSIX_SIGNALS MSGFMT MSGMERGE XGETTEXT localedir TCLSH TCL_CONFIG_SH TCL_INCLUDE_SPEC TCL_LIB_FILE TCL_LIBS TCL_LIB_SPEC TCL_SHARED_BUILD TCL_SHLIB_LD_LIBS NSGMLS JADE have_docbook DOCBOOKSTYLE COLLATEINDEX SGMLSPL vpath_build LTLIBOBJS' ac_subst_files='' # Initialize some variables set by options. @@ -3306,6 +3306,17 @@ # +# Native compiler +# + +if test -z $CC_FOR_BUILD; then + CC_FOR_BUILD=$CC +fi + + + + +# # Set up TAS assembly code if needed; the template file has now had its # chance to request this. # @@ -22892,6 +22903,7 @@ s,@OBJEXT@,$OBJEXT,;t t s,@CPP@,$CPP,;t t s,@GCC@,$GCC,;t t +s,@CC_FOR_BUILD@,$CC_FOR_BUILD,;t t s,@TAS@,$TAS,;t t s,@autodepend@,$autodepend,;t t s,@INCLUDES@,$INCLUDES,;t t diff -ur ../cvs-pgsql/configure.in ./configure.in --- ../cvs-pgsql/configure.in 2005-07-01 20:17:30.0 +0200 +++ ./configure.in 2005-07-03 19:20:00.587380507 +0200 @@ -304,6 +304,17 @@ # +# Native compiler +# + +if test -z $CC_FOR_BUILD; then + CC_FOR_BUILD=$CC +fi + +AC_SUBST(CC_FOR_BUILD) + + +# # Set up TAS assembly code if needed; the template file has now had its # chance to request this. # diff -ur ../cvs-pgsql/doc/src/sgml/installation.sgml ./doc/src/sgml/installation.sgml --- ../cvs-pgsql/doc/src/sgml/installation.sgml 2005-06-30 18:31:47.0 +0200 +++ ./doc/src/sgml/installation.sgml 2005-07-03 20:06:54.560270579 +0200 @@ -993,6 +993,23 @@ userinput./configure CC=/opt/bin/gcc CFLAGS='-O2 -pipe'/ /screen /para + +para + If you are cross-compilingindextermprimarycross + compilation/primary/indexterm (compiling on one architecture + with the intent to run on a different architecture), special care + needs to be taken. First of all, specify the host architecture + (the architecture PostgreSQL will run
Re: [HACKERS] timezone changes break windows and cygwin
Tom Lane wrote: We might have to move zic into a separate directory so that it can be compiled on its own with its own copies of the relevant .o files. Up next: rewriting zic in Perl :-) No seriously, when the dust has settled I think we should just put in a note into the makefile (or perhaps at the end of configure) to the effect you are cross-compiling, you will have problems here and here as I had previously proposed. Those who are just looking for a cross build of libpq should be content with that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Checkpoint cost, looks like it is WAL/CRC
On Wed, 2005-06-29 at 23:23 -0400, Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: Uh, what exactly did you cut out? I suggested dropping the dumping of full page images, but not removing CRCs altogether ... Attached is the patch I used. OK, thanks for the clarification. So it does seem that dumping full page images is a pretty big hit these days. Yes the performance results are fairly damning. That's a shame, I convinced myself that the CRC32 and block-hole compression was enough. The 50% performance gain isn't the main thing for me. The 10 sec drop in response time immediately after checkpoint is the real issue. Most sites are looking for good response as an imperative, rather than throughput. Overall, IMHO we must do something about this for 8.1. Possibly something more for 8.2 also, but definitely *something* now. (In defense of the original idea, I believe it was not such a hit at the time --- but as we continue to improve performance, things that weren't originally at the top of the profile become significant.) No defense required. As you say, it was the best idea at the time. It seems like we have two basic alternatives: 1. Offer a GUC to turn off full-page-image dumping, which you'd use only if you really trust your hardware :-( 2. Think of a better defense against partial-page writes. I like #2, or would if I could think of a better defense. Ideas anyone? Well, I'm all for #2 if we can think of one that will work. I can't. Option #1 seems like the way forward, but I don't think it is sufficiently safe just to have the option to turn things off. With wal_changed_pages= off *any* crash would possibly require an archive recovery, or a replication rebuild. It's good that we now have PITR, but we do also have other options for availability. Users of replication could well be amongst the first to try out this option. The problem is that you just wouldn't *know* whether the possibly was yes or no. The temptation would be to assume no and just continue, which could lead to data loss. And that would lead to a lack of trust in PostgreSQL and eventual reputational loss. Would I do an archive recovery, or would I trust that RAID array had written everything properly? With an irate Web Site Manager saying you think? it might? maybe? You mean you don't know??? If we pick option #1, it *must* also include a way of deciding whether a crash recovery has succeeded, or not. Other commentators have mentioned the torn_pages option for other DBMS. It seems we also need an indicator. That should be a non-optional feature of 8.1 and then perhaps an optional feature in later releases when we have more experience to say that turning it off is OK in some circumstances. We could implement the torn-pages option, but that seems a lot of work. Another way of implementing a tell-tale would be to append the LSN again as a data page trailer as the last 4 bytes of the page. Thus the LSN would be both the first and last item on the data page. Any partial write would fail to update the LSN trailer and we would be able to see that a page was torn. That's considerably easier than trying to write a torn page tell-tale to each 512 byte sector of a page as SQLServer does. During recovery, if a full page image is not available, we would read the page from the database and check that the first and last LSNs match. If they do, then the page is not torn and recovery can be successful. If they do not match, then we attempt to continue recovery, but issue a warning that torn page has been detected and a full archive recovery is recommended. It is likely that the recovery itself will fail almost immediately following this, since changes will try to be made to a page in the wrong state to receive it, but there's no harm in trying Like this specific idea or not, I'm saying that we need a tell-tale: a way of knowing whether we have a torn page, or not. That way we can safely continue to rely upon crash recovery. Tom, I think you're the only person that could or would be trusted to make such a change. Even past the 8.1 freeze, I say we need to do something now on this issue. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] timezone changes break windows and cygwin
Peter Eisentraut wrote: Tom Lane wrote: I can pull out NO_PGPORT, but what commit causes the original problem? Was it only one? (I wasn't watching.) This one: 2005-07-03 14:54 petere Attached is the patch, in case someone wants to revert it. (I'd do it myself but I suppose this would interfere with rolling back the subsequent patches.) I apologize for all the trouble. I backed it out using CVS because this patch didn't contain the SGML changes that also needed to be reverted, I think. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: [HACKERS] timezone changes break windows and cygwin
OK, I have backed out Peter's change, and the NO_PGPORT workarounds. --- Peter Eisentraut wrote: Tom Lane wrote: We might have to move zic into a separate directory so that it can be compiled on its own with its own copies of the relevant .o files. Up next: rewriting zic in Perl :-) No seriously, when the dust has settled I think we should just put in a note into the makefile (or perhaps at the end of configure) to the effect you are cross-compiling, you will have problems here and here as I had previously proposed. Those who are just looking for a cross build of libpq should be content with that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs wrote: On Wed, 2005-06-29 at 23:23 -0400, Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: Uh, what exactly did you cut out? I suggested dropping the dumping of full page images, but not removing CRCs altogether ... Attached is the patch I used. OK, thanks for the clarification. So it does seem that dumping full page images is a pretty big hit these days. Yes the performance results are fairly damning. That's a shame, I convinced myself that the CRC32 and block-hole compression was enough. The 50% performance gain isn't the main thing for me. The 10 sec drop in response time immediately after checkpoint is the real issue. Most sites are looking for good response as an imperative, rather than throughput. Yep. No defense required. As you say, it was the best idea at the time. It seems like we have two basic alternatives: 1. Offer a GUC to turn off full-page-image dumping, which you'd use only if you really trust your hardware :-( 2. Think of a better defense against partial-page writes. I like #2, or would if I could think of a better defense. Ideas anyone? Well, I'm all for #2 if we can think of one that will work. I can't. Option #1 seems like the way forward, but I don't think it is sufficiently safe just to have the option to turn things off. Well, I added #1 yesterday as 'full_page_writes', and it has the same warnings as fsync (namely, on crash, be prepared to recovery or check your system thoroughly. As far as #2, my posted proposal was to write the full pages to WAL when they are written to the file system, and not when they are first modified in the shared buffers --- the goal being that it will even out the load, and it will happen in a non-critical path, hopefully by the background writer or at checkpoint time. With wal_changed_pages= off *any* crash would possibly require an archive recovery, or a replication rebuild. It's good that we now have PITR, but we do also have other options for availability. Users of replication could well be amongst the first to try out this option. Seems it is similar to fsync in risk, which is not a new option. The problem is that you just wouldn't *know* whether the possibly was yes or no. The temptation would be to assume no and just continue, which could lead to data loss. And that would lead to a lack of trust in PostgreSQL and eventual reputational loss. Would I do an archive recovery, or would I trust that RAID array had written everything properly? With an irate Web Site Manager saying you think? it might? maybe? You mean you don't know??? That is a serious problem, but the same problem we have in turning off fsync. During recovery, if a full page image is not available, we would read the page from the database and check that the first and last LSNs match. If they do, then the page is not torn and recovery can be successful. If they do not match, then we attempt to continue recovery, but issue a warning that torn page has been detected and a full archive recovery is recommended. It is likely that the recovery itself will fail almost immediately following this, since changes will try to be made to a page in the wrong state to receive it, but there's no harm in trying I like the idea of checking the page during recovery so we don't have to check all the pages, just certain pages. Like this specific idea or not, I'm saying that we need a tell-tale: a way of knowing whether we have a torn page, or not. That way we can safely continue to rely upon crash recovery. Tom, I think you're the only person that could or would be trusted to make such a change. Even past the 8.1 freeze, I say we need to do something now on this issue. I think if we document full_page_writes as similar to fsync in risk, we are OK for 8.1, but if something can be done easily, it sounds good. Now that we have a GUC we can experiment with the full page write load and see how it can be improved. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Schedule for 8.1 feature freeze
Bruce Momjian wrote:
Andrew Dunstan wrote:
There is also the tiny patch to trap lexical warnings I submitted not
long ago still outstanding.
OK, I missed that one. I see it at:
http://archives.postgresql.org/pgsql-patches/2005-06/msg00280.php
I applied the attached patch from the following URL. The URL does not
have separate file names for the regression tests so I did not add
those.
Thanks.
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/pl/plperl/GNUmakefile
===
RCS file: /cvsroot/pgsql/src/pl/plperl/GNUmakefile,v
retrieving revision 1.22
retrieving revision 1.23
diff -c -r1.22 -r1.23
*** src/pl/plperl/GNUmakefile 24 May 2005 17:07:41 - 1.22
--- src/pl/plperl/GNUmakefile 6 Jul 2005 22:33:39 - 1.23
***
*** 1,5
# Makefile for PL/Perl
! # $PostgreSQL: pgsql/src/pl/plperl/GNUmakefile,v 1.22 2005/05/24 17:07:41 tgl
Exp $
subdir = src/pl/plperl
top_builddir = ../../..
--- 1,5
# Makefile for PL/Perl
! # $PostgreSQL: pgsql/src/pl/plperl/GNUmakefile,v 1.23 2005/07/06 22:33:39
momjian Exp $
subdir = src/pl/plperl
top_builddir = ../../..
***
*** 37,43
SHLIB_LINK = $(perl_embed_ldflags) $(BE_DLLLIBS)
REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl
! REGRESS = plperl plperl_trigger plperl_shared
include $(top_srcdir)/src/Makefile.shlib
--- 37,43
SHLIB_LINK = $(perl_embed_ldflags) $(BE_DLLLIBS)
REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-language=plperl
! REGRESS = plperl plperl_trigger plperl_shared plperl_elog
include $(top_srcdir)/src/Makefile.shlib
Index: src/pl/plperl/plperl.c
===
RCS file: /cvsroot/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.79
retrieving revision 1.80
diff -c -r1.79 -r1.80
*** src/pl/plperl/plperl.c 3 Jul 2005 21:56:16 - 1.79
--- src/pl/plperl/plperl.c 6 Jul 2005 22:33:39 - 1.80
***
*** 33,39
* ENHANCEMENTS, OR MODIFICATIONS.
*
* IDENTIFICATION
! * $PostgreSQL: pgsql/src/pl/plperl/plperl.c,v 1.79 2005/07/03 21:56:16
tgl Exp $
*
**/
--- 33,39
* ENHANCEMENTS, OR MODIFICATIONS.
*
* IDENTIFICATION
! * $PostgreSQL: pgsql/src/pl/plperl/plperl.c,v 1.80 2005/07/06 22:33:39
momjian Exp $
*
**/
***
*** 190,195
--- 190,197
, -e,
/* all one string follows (no commas please) */
SPI::bootstrap(); use vars qw(%_SHARED);
+ sub ::plperl_warn { my $msg = shift; elog(NOTICE, $msg); }
+ $SIG{__WARN__} = \\::plperl_warn;
sub ::mkunsafefunc {return eval(qq[ sub { $_[0] $_[1] } ]); }
};
***
*** 197,202
--- 199,206
, -e,
/* all one string follows (no commas please) */
SPI::bootstrap(); use vars qw(%_SHARED);
+ sub ::plperl_warn { my $msg = shift; elog(NOTICE, $msg); }
+ $SIG{__WARN__} = \\::plperl_warn;
sub ::mkunsafefunc {return eval(
qq[ sub { use strict; $_[0] $_[1] } ]); }
};
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Schedule for 8.1 feature freeze
Bruce Momjian wrote: Bruce Momjian wrote: Andrew Dunstan wrote: There is also the tiny patch to trap lexical warnings I submitted not long ago still outstanding. OK, I missed that one. I see it at: http://archives.postgresql.org/pgsql-patches/2005-06/msg00280.php I applied the attached patch from the following URL. The URL does not have separate file names for the regression tests so I did not add those. Never mind, I found the right patch. Will add regressions. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Wed, 2005-07-06 at 18:22 -0400, Bruce Momjian wrote: Well, I added #1 yesterday as 'full_page_writes', and it has the same warnings as fsync (namely, on crash, be prepared to recovery or check your system thoroughly. Yes, which is why I comment now that the GUC alone is not enough. There is no way to check your system thoroughly. If there is a certain way of knowing torn pages had *not* occurred, then I would be happy. As far as #2, my posted proposal was to write the full pages to WAL when they are written to the file system, and not when they are first modified in the shared buffers --- the goal being that it will even out the load, and it will happen in a non-critical path, hopefully by the background writer or at checkpoint time. The page must be written before the changes to the page are written, so that they are available sequentially in the log for replay. The log and the database are not connected, so we cannot do it that way. If the page is written out of sequence from the changes to it, how would recovery know where to get the page from? ISTM there is mileage in your idea of trying to shift the work to another time. My thought is which blocks exactly are the ones being changed?. Maybe that would lead to a reduction. With wal_changed_pages= off *any* crash would possibly require an archive recovery, or a replication rebuild. It's good that we now have PITR, but we do also have other options for availability. Users of replication could well be amongst the first to try out this option. Seems it is similar to fsync in risk, which is not a new option. Risk is not acceptable. We must have certainty, either way. Why have two GUCs? Why not just have one GUC that does both at the same time? When would you want one but not the other? risk_data_loss_to_gain_performance = true I think if we document full_page_writes as similar to fsync in risk, we are OK for 8.1, but if something can be done easily, it sounds good. Documenting something simply isn't enough. I simply cannot advise anybody ever to use the new GUC. If their data was low value, they wouldn't even be using PostgreSQL, they'd use a non-transactional DBMS. I agree we *must* have the GUC, but we also *must* have a way for crash recovery to tell us for certain that it has definitely worked, not just maybe worked. Best regards, Simon Riggs ---(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
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Tom, I think you're the only person that could or would be trusted to make such a change. Even past the 8.1 freeze, I say we need to do something now on this issue. I think if we document full_page_writes as similar to fsync in risk, we are OK for 8.1, but if something can be done easily, it sounds good. Now that we have a GUC we can experiment with the full page write load and see how it can be improved. Question, with this option if the power goes out will I just roll through the transaction logs like normal? Or are we talking the potential to have to use something like pg_resetxlog or similar? If it is just roll through the transaction logs then I have no problem with it, let the user decide the level of reliance they have. If it can cause actual, need to restore from backup level damage then it is a literall no go IMHO. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs wrote: I agree we *must* have the GUC, but we also *must* have a way for crash recovery to tell us for certain that it has definitely worked, not just maybe worked. Doesn't the same argument apply to the existing fsync = off case? i.e. we already have a case where we don't provide a crash-recovery guarantee. -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [INTERFACES] By Passed Domain Constraints
You could work around this by explicitly specifying the parameter type as text or varchar or whatever the domain's base type is. I wonder though if we oughtn't change the backend so that the inferred type of a parameter symbol is never a domain, but the domain's base type. That would force the proper application of CoerceToDomain inside the constructed query parsetree. Remember we have similar weirdness when returning domain types from stored procs :( Chris ---(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] Mistake in latest plperl patch
Per Andrew (he is having email problems): http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=viperdt=2005-07-07%2001:10:01 Joshua D. Drkae ---(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
[HACKERS] PQescapeIdentifier
How about a PQescapeIdentifier function in libpq? :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2005-07-06 at 18:22 -0400, Bruce Momjian wrote: Well, I added #1 yesterday as 'full_page_writes', and it has the same warnings as fsync (namely, on crash, be prepared to recovery or check your system thoroughly. Yes, which is why I comment now that the GUC alone is not enough. There is no way to check your system thoroughly. If there is a certain way of knowing torn pages had *not* occurred, then I would be happy. I agree with Simon that this isn't much of a solution: no one who cares about their data will dare turn off the option, and therefore the possible performance gain is just hot air. I do not see a better alternative at the moment :-( but we should keep thinking about it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
On Wed, Jul 06, 2005 at 21:48:44 +0100, Simon Riggs [EMAIL PROTECTED] wrote: We could implement the torn-pages option, but that seems a lot of work. Another way of implementing a tell-tale would be to append the LSN again as a data page trailer as the last 4 bytes of the page. Thus the LSN would be both the first and last item on the data page. Any partial write would fail to update the LSN trailer and we would be able to see that a page was torn. That's considerably easier than trying to write a torn page tell-tale to each 512 byte sector of a page as SQLServer does. Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Bruno Wolff III [EMAIL PROTECTED] writes: Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? I don't think you can possibly assume that. If the block crosses a cylinder boundary then it's certainly an unsafe assumption, and even within a cylinder (no seek required) I'm pretty sure that disk drives have understood write the next sector that passes under the heads for decades. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Checkpoint cost, looks like it is WAL/CRC
Tom Lane wrote: Bruno Wolff III [EMAIL PROTECTED] writes: Are you sure about that? That would probably be the normal case, but are you promised that the hardware will write all of the sectors of a block in order? I don't think you can possibly assume that. If the block crosses a cylinder boundary then it's certainly an unsafe assumption, and even within a cylinder (no seek required) I'm pretty sure that disk drives have understood write the next sector that passes under the heads for decades. SCSI tagged queueing certainly allows 512-byte blocks to be reordered during writes. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 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] Mistake in latest plperl patch
Joshua D. Drake wrote: Per Andrew (he is having email problems): http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=viperdt=2005-07-07%2001:10:01 Thanks, fixed (I hope). -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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/faq
Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes
On Fri, 24 Jun 2005 09:21:56 -0700 Josh Berkus josh@agliodbs.com wrote: Jim, Josh, is this something that could be done in the performance lab? That's the idea. Sadly, OSDL's hardware has been having critical failures of late (I'm still trying to get test results on the checkpointing thing) and the GreenPlum machines aren't up yet. I'm on the verge of having a 4-way opteron system with 4 Adaptec 2200s scsi controllers attached to eight 10-disk 36GB arrays ready. I believe there are software tools that'll let you reconfigure the luns from linux so you wouldn't need physical access. Anyone want time on the system? Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
