[HACKERS] Patch: Tie stats options to autovacuum in postgresql.conf
PostgreSQLers, I just ran into an issue where a client thought that autovacuum was running but it wasn't. This is because it's not fatal when autovacuum is on but stats_start_collector and/or stats_row_level is off. I suspect that there's a reason that it's not fatal, so I thought that it might be useful to give folks just a little bit of help by telling them in postgresql.conf that they need to enable them in order for autovacuum to work. If this patch is not correctly formatted or against the proper file, please let me know and I'll make the necessary modifications. Thanks, David vacuam_stats.patch Description: Binary data ---(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] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 20:51, Tom Lane wrote: Um ... design sanity and consistency, maybe? Not that I think PL/SQL is any paragon of those virtues, but surely "we'll invent any feature we feel like whether it's sane or not" is not a recipe for a maintainable language. Yes, sanity is important, I agree. (No, I'm not particularly in favor of the BY feature mentioned upthread, either.) Pity. I thought it was a good idea. Best, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 19:52, Tom Lane wrote: Distant ancestors aren't particularly relevant here. What plpgsql tries to be is a ripoff^H^H^H^H^H^Hsincere flattery of Oracle's PL/SQL. If modifying the loop variable is disallowed in PL/SQL, I'm all for disallowing it in plpgsql, otherwise not. Even if PL/SQL disallows it, why would you not allow it in PL/pgSQL? So that it's easier to migrate from PostgreSQL to Oracle? If you only care about Oracle to PostgreSQL (and who wouldn't?), then it in fact seems desirable for PL/pgSQL to be a superset of PL/SQL. But that's must MYH. Best, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 17:51, Jaime Casanova wrote: i have posted a patch to add a BY clause to the for statement (integer version), with the BY clause you can specify an increment value... it's in the unapplied patches list waiting for review... http://candle.pha.pa.us/mhonarc/patches/msg3.html Ah, /me likes! Any chance that'll get in for 8.2? Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 17:02, Mark Dilger wrote: It seems this has been answered before, by Tom Lane: http://archives.postgresql.org/pgsql-novice/2006-04/msg00138.php Ah, cool, then it *is* known. Thanks, David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 16:53, Mark Dilger wrote: Sorry, I meant to say that it should only be a no-op according to the language specification, as I understand it. The fact that it works suggests to me that the implementation of PL/pgsql has been modified (or broken?). Whether the change is a bug or an intentional feature change, I don't know. Ah, yes, I agree, and that's why I posted my query. mark P.S., Looking through the documentation, I don't immediately see the spec for how a regular '=' is supposed to work, but assignment is documented as using the ':=' syntax and says nothing about '='. Exactly. But I use = all the time for comparision: IF FOUND = TRUE THEN END IF Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 16:42, Mark Dilger wrote: So I don't know why it works for you. I wrote the following, and it also increments the variable: CREATE OR REPLACE FUNCTION weird () RETURNS SETOF INT AS $$ DECLARE i integer; BEGIN i := 0; return next i; i = i + 1; return next i; i = i + 1; return next i; return; END; $$ LANGUAGE plpgsql; So I don't think it has anything to do with loop variables, specifically. Indeed. It appears that, contrary to what I previously thought, := also works: CREATE OR REPLACE FUNCTION inc_by_two( upfrom int, upto int ) RETURNS SETOF INT AS $$ BEGIN FOR i IN upfrom..upto LOOP RETURN NEXT i; i := i + 1; END LOOP; END; $$ LANGUAGE 'plpgsql'; try=# select * from inc_by_two(1,11); inc_by_two 1 3 5 7 9 11 (6 rows) Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL 'i = i + 1' Syntax
On May 16, 2006, at 16:30, Andrew Dunstan wrote: It ought to be illegal to modify the loop control variable anyway, IMNSHO - it certainly is in Ada, the distant antecedent of pl/pgsql. I agree, but I must say that it's incredibly useful to be able to increment by two as I go through a loop: FOR i IN 1 + offset .. 11 + offset LOOP total := total + substring(ean, i, 1)::INTEGER; i = i + 1; END LOOP; Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] PL/pgSQL 'i = i + 1' Syntax
Hellow PostgreSQL hackers, Quick question. Why does the 'i = i + 1' syntax work in this PL/pgSQL function? try=# CREATE OR REPLACE FUNCTION inc_by_two( try(#upfrom int, try(#upto int try(# ) RETURNS SETOF INT AS $$ try$# BEGIN try$# FOR i IN upfrom..upto LOOP try$# RETURN NEXT i; try$# i = i + 1; try$# END LOOP; try$# END; try$# $$ LANGUAGE 'plpgsql'; CREATE FUNCTION try=# select * from inc_by_two(1,10); inc_by_two 1 3 5 7 9 (5 rows) Someone posted a PL/pgSQL function in my blog with this syntax, which is how I know about it, but I couldn't find it documented anywhere. Is it a special exception for loop variables, perhaps? Thanks, David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestion: Which Binary?
On Apr 21, 2006, at 13:54, Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. Cool, thanks Bruce. Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: Which Binary?
On Apr 3, 2006, at 14:37, Tom Lane wrote: I would suggest handling this strictly as an addition to our installation.sgml docs. Finally got 'round to this. Patch attached. There are quite a few environment variables in the list that Peter sent to me that I know nothing about. These I've listed, but the documentation for them is full of "??"s. You can either fill them in, leave them out, or tell me where to learn what they mean and I'll resubmit the patch. And by the way, Tom, I really appreciate the time you take to answer my questions and point me to where I can create a patch to help the project. It's people like you who create really successful open- source projects, just by being so responsive and helpful. Now that's leadership! Best, David Index: doc/src/sgml/installation.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.253 diff -u -r1.253 installation.sgml --- doc/src/sgml/installation.sgml 18 Apr 2006 22:52:07 - 1.253 +++ doc/src/sgml/installation.sgml 20 Apr 2006 18:13:58 - @@ -1011,6 +1011,253 @@ ./configure CC=/opt/bin/gcc CFLAGS='-O2 -pipe' + +Here's a list of some of the more influential environment variables: + + + + + + + AWK=/path/to/awk + + + /path/to/awk is the full path to awk. + + + + + + CC=/path/to/cc + + + /path/to/cc is the full path to your C compiler, such + as gcc. + + + + + + CFLAGS=FLAGS + + + FLAGS is the a list of flags to pass to the C + compiler. + + + + + + COLLATEINDEX=?? + + + ?? is ??. + + + + + + CPP=/path/to/cpp + + + /path/to/cpp is the full path to your C preprocessor, + such as gcc. + + + + + + CPPFLAGS=FLAGS + + + FLAGS is the a list of flags to pass to the C + preprocessor. + + + + + + DOCBOOKSTYLE=?? + + + ?? is ??. + + + + + + JADE=?? + + + ?? is ??. + + + + + + LDFLAGS=FLAGS + + + FLAGS is the a list of flags to pass to the link file + editor. + + + + + + LDFLAGS_SL=?? + + + ?? is ??. + + + + + + LORDER=/path/to/lorder + + + /path/to/lorder is the full path to your dependency + listing program. + + + + + + MSFFMT=?? + + + ?? is ??. + + + + + + MSGMERGE=?? + + + ?? is ??. + + + + + + NSGMLS=?? + + + ?? is ??. + + + + + + PERL=/path/to/perl + + + /path/to/perl is the full path to your Perl 5 + interpreter. Note that this wil be used to determine the dependencies + for building PL/Perl. + + + + + + PTHREAD_CC=?? + + + ?? is ??. + + + + + + PYTHON=/path/to/python + + + /path/to/python is the full path to your Python + interpreter. Note that this wil be used to determine the dependencies + for building PL/Python. + + + + + + RANLIB=/path/to/ranlib + + + /path/to/ranlib is the full path to your archive + library table of contents editor. + + + + + + SGMLSPL=?? + + + ?? is ??. + + + + + + STRIP=/path/to/strip + + + /path/to/strip is the full path to your strip program. + + + + + + TCL=/path/to/tcl + + + /path/to/tcl is the full path to your TCL + interpreter. Note that this wil be used to determine the dependencies + for building PL/TCL. + + + + + + TAR=/path/to/tar + + + /path/to/tar is the full path to your tar program. + + + + + + TCLISH=/path/to/tclish + + + /path/to/tclish is the full path to your tclish program. + + + + + + XGETTEXT=/path/to/xgettext + + + /path/to/xgettext is the full path to your xgettext program. + + + + + + YACC=/path/to/yacc + + + /path/to/yacc is the full path to your yacc program. + +
Re: [HACKERS] FOUND not set by EXECUTE?
On Apr 8, 2006, at 14:38, Tom Lane wrote: It *is* documented: the manual lists the statements that affect FOUND, and EXECUTE is not among them. Whether it should be is another question, but that's a definition disagreement (a/k/a enhancement proposal) not a bug. I think that: a. It should be (it'd be very useful, without a doubt). b. Until it is, the docs should explicitly mention that EXECUTE does not affect found. No, EXECUTE is not in the list, and UPDATE, INSERT, and DELETE are, and although I'm using them in an EXECUTE statement rather than directly in the PL/pgSQL, it still seemed rather confusing, because they're still UPDATE, INSERT, and DELETE. So yes, it's a definition disagreement, but I think that things could be clearer. Thanks, David ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] FOUND not set by EXECUTE?
I had expected the FOUND PL/pgSQL variable to be set by an UPDATE statement executed by an EXECUTE statement, but it doesn't appear to work: try=# drop table try; DROP TABLE try=# CREATE TABLE try ( try(# id integer try(# ); CREATE TABLE try=# INSERT INTO try VALUES (1); INSERT 0 1 try=# CREATE OR REPLACE FUNCTION try_me () RETURNS VOID AS $$ try$# DECLARE try$# rcount integer; try$# BEGIN try$#EXECUTE 'UPDATE try SET ID = 12'; try$#RAISE NOTICE 'Found: %', FOUND; try$#GET DIAGNOSTICS rcount = ROW_COUNT; try$#RAISE NOTICE 'Row Count: %', rcount; try$# END; try$# $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE FUNCTION try=# SELECT try_me(); NOTICE: Found: f NOTICE: Row Count: 1 try_me (1 row) Note that FOUND is false, but the ROW_COUNT fetched by GET DIAGNOSTICS is set to 1. So shouldn't FOUND be true? Or does it just not work with EXECUTE and need to be documented as such? Or am I just missing something obvious? Thanks, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: Which Binary?
On Apr 3, 2006, at 13:49, David Wheeler wrote: configure is autogenerated. You can't patch in there. Oh. Duh. I'll grep for it. Hrm. Is there a file somewhere from which the environment variable section is generated? Or is it just created by autoconf? Thanks, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion: Which Binary?
On Apr 3, 2006, at 13:44, Peter Eisentraut wrote: configure is autogenerated. You can't patch in there. Oh. Duh. I'll grep for it. D ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: Which Binary?
On Apr 3, 2006, at 13:37, Alvaro Herrera wrote: Too verbose :-( How about putting the most important in configure, and the rest in a text file? Configure can then say "Some of them are here, the rest can be found in file such-and-such". Yeah, I'll create a table for INSTALL. Best, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
On Apr 3, 2006, at 13:08, Peter Eisentraut wrote: I'm not sure if this list is complete, but it's a good approximation: Thanks. How's this, then? Best, David --- configure 06 Mar 2006 09:41:42 -0800 1.485 +++ configure 03 Apr 2006 13:31:10 -0700 @@ -897,16 +897,36 @@ --with-gnu-ld assume the C compiler uses GNU ld [default=no] Some influential environment variables: + AWK awk program CC C compiler command CFLAGS C compiler flags - LDFLAGS linker flags, e.g. -L if you have libraries in a - nonstandard directory + COLLATEINDEX + + CPP C preprocessor CPPFLAGSC/C++ preprocessor flags, e.g. -I if you have headers in a nonstandard directory - CPP C preprocessor - LDFLAGS_SL DOCBOOKSTYLE + + JADE + LDFLAGS linker flags, e.g. -L if you have libraries in a + nonstandard directory + LDFLAGS_SL location of DocBook stylesheets + LORDER lorder program + MSFFMT + MSGMERGE + NSGMLS + PERLperl program (used by --with-perl) + PTHREAD_CC + PYTHON python program (used by --with-python) + RANLIB ranlib program + SGMLSPL + STRIP strip programq + TCL tcl program (used by --with-tcl) + TAR tar program + TCLSH tcl shell program + XGETTEXTxgettext program + YACCyacc program Use these variables to override the choices made by `configure' or to help it to find libraries and programs with nonstandard names/locations. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion: Which Binary?
On Apr 1, 2006, at 16:37, Tom Lane wrote: Just to clarify my point: what'd make sense to me is to describe this generic autoconf behavior, and maybe include a small table listing some of the more-likely-to-be-useful variables. ("configure --help" already does that, on a very small scale.) It doesn't make much sense to me to document two specific variables in a way that fails to draw the reader's attention to the fact that there are many other ones. After all, the reader might have some other problem to solve than "use this perl". If he knows that there might be a way to solve it by setting a variable, he's ahead of the game. Agreed. I've started with this, at least, in ./configure --help *** configure 06 Mar 2006 09:41:42 -0800 1.485 --- configure 03 Apr 2006 12:41:47 -0700 *** *** 907,912 --- 907,915 LDFLAGS_SL DOCBOOKSTYLE location of DocBook stylesheets + PERLlocation of perl executable + PYTHON location of python executable + TCL location of tcl executable Use these variables to override the choices made by `configure' or to help it to find libraries and programs with nonstandard names/locations. But I'm not sure what other variables are supported. I'd *really* like to know, for example, if there's a READLINE variable, so that I can point it at GNU readline instead of Mac OS X's crappy readline. And are there also variables for tclconfig, krb5, pam, ldap, bonjour, openssl, zlib, and ld? And if so, what do they point at, since some of these are not execurables (e.g., readline)? I'll submit a more complete patch, along with a patch to INSTALL, once I get a more complete list via replies to the above questions from you kind folks. Thanks! David ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Suggestion: Which Binary?
On Apr 2, 2006, at 17:47, Robert Treat wrote: ISTM that by any measure of the general population, David Wheeler is a hard-core geek. :-) Actually by most measures of the "programming/ oss community" he is a hard core geek. But he still got tripped up by this. A lot of people never get passed ./configure;make;make install even though they do a lot of coding on oss projects. Why turn these people away? /me blushes Maybe I am a hard-core OSS hacker, I'm just not a C hacker (and not familiar with the OSS C distribution stuff). But I'd be happy to work on a patch that creates a table like that described by Tom. I think that would help folks like me (and even soft core OSS hackers ;-)) a lot. Cheers, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
On Apr 1, 2006, at 15:49, Peter Eisentraut wrote: Well, you got one of them, but I still have my doubts about "/usr/bin/per5.8.6". LOL! God, what an oaf! David Index: doc/src/sgml/installation.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.252 diff -u -r1.252 installation.sgml --- doc/src/sgml/installation.sgml 5 Jan 2006 03:01:32 - 1.252 +++ doc/src/sgml/installation.sgml 31 Mar 2006 22:08:07 - @@ -182,6 +182,14 @@ PL/Perl you need a full Perl installation, including the libperl library and the header files. + configure will collect this information + from whatever perl is in your PATH; + if you'd like it to use an alternate perl, simply + specify its location via the PERL environment + variable, e.g., PERL=/usr/bin/perl5.8.6 + + + Since PL/Perl will be a shared library, the libperl libperl library must be a shared library @@ -219,6 +227,11 @@ Python 1.6 and later; users of earlier versions of Python will need to install it. + configure will collect this information + from whatever python is in your PATH; + if you'd like it to use an alternate python, simply + specify its location via the PYTHON environment + variable, e.g., PYTHON=/usr/bin/python2.3 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestion: Which Binary?
On Apr 1, 2006, at 15:39, David Wheeler wrote: So, pray tell, what have I screwed up? I would of course be happy to submit a corrected patch. Sorry, I'm an idiot. New version attached. Best, David Index: doc/src/sgml/installation.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.252 diff -u -r1.252 installation.sgml --- doc/src/sgml/installation.sgml 5 Jan 2006 03:01:32 - 1.252 +++ doc/src/sgml/installation.sgml 31 Mar 2006 22:08:07 - @@ -182,6 +182,14 @@ PL/Perl you need a full Perl installation, including the libperl library and the header files. + configure will collect this information + from whatever perl is in your PATH; + if you'd like it to use an alternate perl, simply + specify its location via the PERL environment + variable, e.g., PERL=/usr/bin/per5.8.6 + + + Since PL/Perl will be a shared library, the libperl libperl library must be a shared library @@ -219,6 +227,11 @@ Python 1.6 and later; users of earlier versions of Python will need to install it. + configure will collect this information + from whatever python is in your PATH; + if you'd like it to use an alternate python, simply + specify its location via the PYTHON environment + variable, e.g., PYTHON=/usr/bin/python2.3 ---(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] Suggestion: Which Binary?
On Apr 1, 2006, at 06:58, Peter Eisentraut wrote: Next time you submit a patch, please consider reading it before sending it out. I just read the patch, and it looks fine to me. No typos that I noticed. I might have screwed up the SGML stuff, but I know even less about SGML than I do about autoconf and configure. :-) So, pray tell, what have I screwed up? I would of course be happy to submit a corrected patch. Best, David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 20:02, Tom Lane wrote: You can give `configure' initial values for configuration parameters by setting variables in the command line or in the environment. Here is an example: ./configure CC=c89 CFLAGS=-O2 LIBS=-lposix This isn't super helpful, of course, since it doesn't say exactly which variables any particular autoconf script responds to. But pretty much all of the programs that a configure script searches for are reflected as variables. A quick grep through our configure script for the phrase "Let the user override" finds a couple dozen hits, and that's just for programs, never mind non-program variables. Right, but me, while I compile lots of stuff, I don't understand configure or autconf at all. So I was completely unaware of this feature. I'm very pleased to know it now, of course. But I don't see how it could be any harm to add notes to the INSTALL file explicitly letting users know how to do stuff like this. The INSTALL file is, after all, what folks like me read when looking for information beyond ./configure && make && make install. It would have helped me a lot, and I see no disadvantage to including it. Am I missing something? Thanks, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 16:01, Tom Lane wrote: It seems rather pointless to document two instances of what is in fact a generic autoconf-script behavior ... I'm sorry to be such a moron about this, but what exactly is that behavior? That you can specify an environment variable for whatever * is in --with-*? Thanks, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 15:52, Tom Lane wrote: The more usual way to handle this sort of thing is to put each version of perl in a different directory, and then you can alter PATH while running configure to pick which one you want. I've got several versions of perl on this machine that I select that way ... it doesn't require any special smarts on the part of the perl-using program, and it scales to handle multiple versions of other things like Tcl, too. I would normally do that, as well, but in this case, I wanted my self- compiled Perl to always be what runs (as a general rule), so I had it hard link itself in /usr/bin as well as /usr/local/bin. It is only in this one case where I need the stock Perl to be found that things get wonky for me. :-) Best, David ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 12:40, Josh Berkus wrote: In support of David's suggestion, I'll point out that most other OSS software configuration scripts (Apache, PHP, etc.) I deal with supports the above syntax. Yes, but even the environment variables get me what I want. I therefore respectfully submit the attached patch to document them in the INSTALL file. Best, David Index: doc/src/sgml/installation.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/installation.sgml,v retrieving revision 1.252 diff -u -r1.252 installation.sgml --- doc/src/sgml/installation.sgml 5 Jan 2006 03:01:32 - 1.252 +++ doc/src/sgml/installation.sgml 31 Mar 2006 22:08:07 - @@ -182,6 +182,14 @@ PL/Perl you need a full Perl installation, including the libperl library and the header files. + configure will collect this information + from whatever perl is in your PATH; + if you'd like it to use an alternate perl, simply + specify its location via the PERL environment + variable, e.g., PERL=/usr/bin/per5.8.6 + + + Since PL/Perl will be a shared library, the libperl libperl library must be a shared library @@ -219,6 +227,11 @@ Python 1.6 and later; users of earlier versions of Python will need to install it. + configure will collect this information + from whatever python is in your PATH; + if you'd like it to use an alternate python, simply + specify its location via the PYTHON environment + variable, e.g., PYTHON=/usr/bin/per5.8.6 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Suggestion: Which Binary?
On Mar 31, 2006, at 12:05, Seneca Cunningham wrote: Like passing PERL=/usr/bin/perl5.8.6 to configure? Is that currently supported? Because, if so, it's documented AFAICT. Best, David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Suggestion: Which Binary?
Dear PostgreSQL Hackers, I recently ran into an issue where I was having trouble compiling PostgreSQL with PL/Perl. Although Mac OS X 10.4 comes with a dynamic Perl, I long ago compiled my own Perl, which is static. So /usr/bin/ perl was my static Perl, and /usr/bin/perl5.8.6 is the stock Perl. But of course, PostgreSQL's configure script was just executing 'perl' and finding it in the path, thus getting my static Perl which, of course, wouldn't work. I got 'round this by temporarily moving things around: rm /usr/bin/perl ln /usr/bin/perl5.8.6 /usr/bin/perl ./configure --with-perl rm /usr/bin/perlo ln /usr/local/bin/perl5.8.8 /usr/bin/perl But that's a PITA. I'd much rather have been able to tell configure *which* perl to use: ./configure --with-perl=/usr/bin/perl5.8.6 Would it be possible to add support for an optional argument to the PL/* options (--with-perl,--with-python, --with-tcl) so that we can get it to use the correct binary without having to resort to any shenanigans? Just an idea. Thanks! David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Segfault Exiting psql
On Jul 15, 2005, at 5:13 PM, David Wheeler wrote: No. I'm using the readline that comes with Tiger, FWIW. If you tell me how to create a stack trace, I'll post it somewhere for you to see. I don't know C, myself. Didn't see a reply to this, but if it makes a difference, I just learned that the readline library that comes with Tiger (no prior version of Mac OS X included readline) is not GNU readline. readline.h starts with: /* $NetBSD: readline.h,v 1.11 2004/01/17 17:57:40 christos Exp $ */ /*- * Copyright (c) 1997 The NetBSD Foundation, Inc. * All rights reserved. Might this be the source of the segfaults when exiting psql? Best, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Segfault Exiting psql
On Jul 15, 2005, at 4:55 PM, Tom Lane wrote: This noise wasn't there ever before; I do not know if it's just that Apple's tools got pickier recently, or if we've broken something. I think they're new to Tiger, because I saw some warnings I ignored when I built Pg. Second, at exit from an interactive psql session, regression=# \q could not save history to file "/Users/tgl/.psql_history": Invalid argument $ The file is written, but it seems not to contain the expected contents. This might be the fault of my not-so-up-to-date readline library. Not sure if your symptom could also be blamed on readline --- have you tried getting a stack trace from the crash? No. I'm using the readline that comes with Tiger, FWIW. If you tell me how to create a stack trace, I'll post it somewhere for you to see. I don't know C, myself. Thanks! David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Segfault Exiting psql
PostgreSQL hackers: 8.0.3 is working great for me on Mac OS X Tiger, but I do get a segfault every time I exit pssql: psql(7466) malloc: *** error for object 0x1806600: incorrect checksum for freed object - object was probably modified after being freed, break at szone_error to debug psql(7466) malloc: *** set a breakpoint in szone_error to debug zsh: segmentation fault /usr/local/pgsql/bin/psql -U postgres -d sharky Anyone seen this before? Compiled with gcc 4.0 (Apple's). Thanks! David ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 5:02 PM, Jan Wieck wrote: The multi-action rules usually come into play when someone attempts to make join-views updatable. Not an easy problem, granted, but most of the time I have found a combination of rules together with ON UPDATE/DELETE CASCADE constraints or even user defined triggers absolutely sufficient. The INSERT and UPDATE case is handled by rules as usual. And in the DELETE case the rule just deletes the critical rows and the ON DELETE CASCADE constraints do the rest. Yes, this is what I'm finding, too. But it would be good if the documentation better reflected that this is how it works. Regards, David ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 2:58 PM, Tom Lane wrote: ... which indeed can be a feature, not a bug, depending on what you're doing ... Absolutely. An INSERT rule I have looks like this: CREATE RULE insert_one AS ON INSERT TO one WHERE NEW.id IS NULL DO INSTEAD ( INSERT INTO _simple (id, guid, state, name, description) VALUES (NEXTVAL('seq_kinetic'), NEW.guid, NEW.state, NEW.name, NEW.description); INSERT INTO simple_one (id, bool) VALUES (CURRVAL('seq_kinetic'), NEW.bool); ); The call to NEXTVAL() in the first statement sets up a value I use in the second via CURRLVA(). Cheers, David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 2:43 PM, Rob Butler wrote: For this particular scenario, can't you just create two ON DELETE rules? The first would delete from b, the second from a. Perhaps an example with a scenario like this can be added to the doc's? No, that approach has the same problem. Once the first rule deletes a record, it's gone from the view, so the second delete wouldn't know how to do it. So, the short answer is you can only perform one query in a rule, but you can have multiple rules defined to do what you need. No, you can have multiple queries--you just have to understand that those that come first might have an effect on those that come later. Can you call a stored proc from a rule? You could pass the old.id to the stored proc and do as many queries as you like in there without worry that the old.id would go away. Yes, that would be one solution. Another would be to have an ON DELETE CASCADE on the foreign key constraint. Then, to delete the record from both tables, you just delete it from the primary key table. Just some thoughts. It does suck that old.id goes away. Any way of preventing that from happening? Doesn't sound like it. But your suggestion to use a function is a good one. (Although Tom did say something about volatile functions...). Regards, David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 11:20 AM, Tom Lane wrote: The problem is that OLD is effectively a macro for the view, and once you've deleted one of the rows, that ID is no longer present anywhere in the view. Sometimes you can work around this by making the join an outer join, but that's certainly a kluge. Yah. I don't think it's fixable without a fundamental rethinking of the feature. Well, I'm not to worried about it for my current needs, but I can sure see how it would be unexpected and really bite someone. So has anyone else done any rethinking of rules? Cheers, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 12:35 PM, Tom Lane wrote: One possibility for an alternative mechanism is to allow triggers on views --- but I'm not sure exactly how this would work, or if it would solve all the problems. At the very least it would answer the "data stability" issue, since I suppose the trigger would receive precomputed rows that wouldn't change while it executed. FWIW, SQLite allows triggers on views. They work very much like Pg's rules (only I think that its INSTEAD OF syntax short-circuits the execution of other triggers) in that it allows you to use a series of SQL statements that will be executed--no function definition required. http://www.sqlite.org/lang_createtrigger.html Works great! Cheers, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 25, 2005, at 11:00 PM, Tom Lane wrote: DO INSTEAD means that the *original* query will not execute; it does not suppress actions produced by other rules. Ah! If we did not define it that way, I think your example would have to error out --- how would you choose which INSTEAD rule wins? The documentation says that they evaluate in alphabetical order by name. So I would expect that the first one to have its WHERE statement evaluate to true would short-circuit the execution of the remaining rules. Regards, David ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 25, 2005, at 11:37 PM, Tom Lane wrote: (I have been thinking more and more that we should consider a wholesale redesign of the rule mechanism, because it sure seems not to answer the needs/expectations of a lot of people out there. But I am not talking about marginal questions like what INSTEAD means --- it seems to me that there's some very fundamental disconnect between what the rewriter does and what people want. I don't have any specific substitute proposal, which is why I've not brought it up...) I think that people are likely to confuse rules and triggers. The other issue is that they are not documented in such a way as to make them simple to understand. But beyond that, although I like Neil's suggestion better, rules work pretty well for what I need them for--the ability to INSERT, UPDATE, or DELETE on a view. Come to my presentation at OSCON this summer to see what I'm doing with them. :-) Cheers, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DO INSTEAD and conditional rules
On Apr 26, 2005, at 8:55 AM, Tom Lane wrote: Well, they handle simple situations OK, but we keep seeing people get burnt as soon as they venture into interesting territory. For instance, if the view is a join, you can't easily make a rule that turns a delete into deletions of both joined rows. And you'll get burnt if you try to insert any volatile functions, because of the multiple-evaluation issue. Etc. sharky=# CREATE TABLE a ( sharky(# id int, sharky(# name text sharky(# ); CREATE TABLE sharky=# CREATE TABLE b ( sharky(# a_id int, sharky(# rank text sharky(# ); CREATE TABLE sharky=# sharky=# CREATE VIEW ab AS sharky-# SELECT id, name, rank sharky-# FROM a, b sharky-# WHERE a.id = b.a_id sharky-# ; CREATE VIEW sharky=# CREATE RULE delete_ab AS sharky-# ON DELETE TO ab DO INSTEAD ( sharky(# DELETE FROM b sharky(# WHERE a_id = OLD.id; sharky(# sharky(# DELETE FROM a sharky(# WHERE id = OLD.id; sharky(# ); CREATE RULE sharky=# sharky=# sharky=# insert into a values (1, 'test'); INSERT 597795 1 sharky=# insert into b values (1, 'sergeant'); INSERT 597796 1 sharky=# select * from ab; id | name | rank +--+-- 1 | test | sergeant (1 row) sharky=# delete from ab; DELETE 0 sharky=# select * from ab; id | name | rank +--+-- (0 rows) sharky=# select * from a; id | name +-- 1 | test (1 row) sharky=# select * from b; a_id | rank --+-- (0 rows) Ah, yes, you're right, that is...unexpected. Perhaps OLD can contain its values for the duration of the RULE's statements? I'm assuming that what's happening is that OLD.id is NULL after the first of the two DELETE statements... Like I said, I don't have a better idea. Just a vague feeling of dissatisfaction. I'd call it a bug. ;-) Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 18, 2004, at 3:12 PM, Bruce Momjian wrote: It was just added to CVS! Awesome! Abhijit++ Bruce++ Tom++ Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 14, 2004, at 6:50 PM, Abhijit Menon-Sen wrote: I thought about that for a while, but I couldn't find anything that is actually broken or confused by the patch. I could be missing something obvious, though, so I'd appreciate another set of eyes looking at it. Does anyone have any ideas? Not I, but I still have my fingers crossed that this will go in in time for 8.0. I think that Tom said it looked good when you first posted the patch. So, core hackers, is it going in or not? The dynamic language driver developers will thank you for it! Many thanks, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 5, 2004, at 10:47 AM, Dann Corbit wrote: Create a 64 bit hash (e.g. UMAC) of the prepared statement (removing hardwired parameters as needed so that "SELECT Col1, col2 FROM Some_Table where FOO = 'BAR'" becomes "SELECT COL1, COL2 FROM SOME_TABLE WHERE FOO = ?", form consistent capitalization of the statement by capitalizing all keywords and non-quoted column names and then form a hash. Create a hash table of skiplists that contain the prepared statement and the prepared statement handle (the hash modulo or bitmasked with some number is the index to which skiplist to store the data in). Then, when you get a query, if it is not already prepared, prepare it and store it in the list. If you find it in the list just reuse it. Of course, it only works with sticky cursors. For something like TPC benchmarks, it can mean very large savings in time. Any time you have a storm of small, similar queries, think 'prepared statement' Yes, this is how the Perl DBI works. And with Abhijit's patch, DBD::Pg (the DBI driver for PostgreSQL) will finally be able to take advantage of it. Regards, David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Oct 5, 2004, at 9:59 AM, Abhijit Menon-Sen wrote: I decided against bundling the two operations together. Here's a patch to add PQprepare() and PQsendPrepare() in a fairly self-contained way. Also attached is a test program à la testlibpq3.c that I used to test the change. This should be all that's needed for DBD::Pg to prepare a statement without pre-specifying types. Ah, fantastic news, Abhijit! This is very exciting for DBD::Pg development, as well as other dynamic language libraries that plan to use prepare(), I expect. I very much look forward to Beta 4 hoping that this patch makes it in. Thanks for doing this! Regards, David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 20, 2004, at 12:34 AM, Bruce Momjian wrote: I think we should favor libpq usage wherever possible and only re-implement it in the native language when required, like for jdbc/java. I think having all interfaces take advantage of libpq improvements and features is a major win. If we need to add things to libpq to make it easier, fine, but that is minor work compared to maintaining separate wire protocol for each interface language. I don't normally post "me too" posts, but I think that what Bruce says here is extremely important. The more drivers can rely on a single, well-developed, and stable API to create a variety of drivers, the less work *everyone* has to do. I think that this kind of pragmatic componentization (to coin a phrase) can only be to the benefit of PostgreSQL. Regards, David PS: And for those who really want a Pure Perl implementation of a PostgreSQL driver in Perl, I suggest you take a look at helping out with DBD::PgPP: http://search.cpan.org/dist/DBD-PgPP/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 19, 2004, at 9:13 PM, Abhijit Menon-Sen wrote: OK, how about adding a PQprepare (PQcreatePrepared?) function like this? PGresult * PQprepare(PGconn *conn, const char *stmtName, const char *query, int nParams, const Oid *paramTypes) { ... PQprepare would construct a Parse message to create a prepared statement named stmtName from the given query, with nParams types pre-declared. It could be called by DBD::Pg with nParams == 0 to let the server infer all of the parameter types. Sounds damn good to me, Abhihit, thanks! I suppose an asynchronous equivalent would also be needed. (Yes, I'm volunteering to write both functions.) Woot! :-) Regards, David ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 18, 2004, at 1:09 PM, Oliver Jowett wrote: Well, obviously. I haven't modified the backend code to accept 'unknown' in PREPARE.. Right, and that's what we're looking for. My point was the client does *not* need to know the type inferred by the PREPARE in the 'unknown' case to make use of the resulting statement. It can pass all parameters as text and use the type inference that happens on EXECUTE -- as is happening in the EXECUTE quoted above. Yes, that's fine, but it's PREPARE that's at issue here, not EXECUTE. Regards, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 17, 2004, at 6:23 PM, Oliver Jowett wrote: template1=> prepare s1(int) as select typname from pg_type where oid = $1; PREPARE template1=> execute s1('16'); typname - bool (1 row) You're still telling it the type via that int. Regards, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 17, 2004, at 2:21 PM, Tom Lane wrote: Okay, here we have Bruce saying he'll add it to open items: http://archives.postgresql.org/pgsql-hackers/2004-08/msg00147.php so I guess it is the same thing. Good, that will give us some political cover for squeezing it into 8.0 ;-). Ah, okay, so Jan and I weren't misunderstanding each other, then. :-) Thanks Tom. Assuming that anyone steps up and does the work, that is. So...any volunteers? I suspect that other driver projects would be happy to have it, too. Cheers, David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 17, 2004, at 1:54 PM, Tom Lane wrote: David Wheeler <[EMAIL PROTECTED]> writes: if I understand a previous post from you, this functionality should be added to libpq, but you haven't had time and aren't sure what the API should look like, anyway, is that correct? Right. Proposals welcome. Wish I spoke C! But I don't think it makes sense to support PREPARE in DBD::Pg without this functionality. :-( Is this the task labeled "allow libpq to check parameterized data types" here?: http://candle.pha.pa.us/cgi-bin/pgopenitems I think that was something else, but memory is fuzzy. I'm less sure, since I was the one who asked Jan Wieck about this at OSCON, for which I believe that this is the relevant discussion: http://archives.postgresql.org/pgsql-hackers/2004-08/msg00136.php http://archives.postgresql.org/pgsql-hackers/2004-08/msg00130.php But maybe Jan and I misunderstood each other? Regards, David ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Sep 17, 2004, at 1:32 PM, Tom Lane wrote: Well, that's the problem: you can't. The SQL PREPARE command doesn't have any provision for dealing with unspecified parameters. I think this is reasonable since if it could, it would still have no way to tell you what it resolved the parameter types as. The shortcoming here is really in libpq and not in the backend: the protocol-level Parse operation *is* designed to handle this scenario, but libpq isn't letting you get at it. Great, thank you, Tom, that's exactly the answer I was looking for. And if I understand a previous post from you, this functionality should be added to libpq, but you haven't had time and aren't sure what the API should look like, anyway, is that correct? Is this the task labeled "llow libpq to check parameterized data types" here?: http://candle.pha.pa.us/cgi-bin/pgopenitems Regards, David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
On Wed, 15 Sep 2004 13:50:43 -0400, Tom Lane wrote: > The describe statement part could be much trickier but DBD::Pg doesn't really > need that for basic functionality. Doesn't it? I thought the problem was that they couldn't find out what datatypes the parameters got resolved as. That may not be important if they are happy with always shipping text strings, but if they want to move over to using binary transmission of parameter values then they gotta know the parameter types. I just finished reading through this thread in the archives, and just wanted to jump in here for a moment, if I could, to ask a couple of questions to get a few things straight, at least in my mind. To get initial PREPARE support, I don't think that the other DBD::Pg developers mind sending strings to the PostgreSQL server in a SQL PREPARE statement. I certainly don't. It will be great when the binary API is there in libpq to exploit, but until it is, strings are fine. This is how DBD::Oracle works, for what it's worth (it sends VARCHAR bound parameters to the server, and the server figures out the appropriate data type). The question, in my mind, is it currently possible to do this without specifying the type of every placeholder? As Greg Stark points out, it would be nice to have an API to get this information from the server, but it's not really important in the short run if the server just takes care of unknowns. I'm told that it does, but no one has been able to help us with the syntax, AFAIK. What we want, I think, is what Greg Stark asks about here: http://archives.postgresql.org/pgsql-hackers/2004-09/msg00399.php And Greg Sabino Mullane was asking about the appropriate syntax here: http://archives.postgresql.org/pgsql-hackers/2004-09/msg00372.php If we could just get this figured out and know that we can rely on the server to determine the appropriate syntax for this, we'll have what we need to get PREPAREd statements working in the next release of DBD::Pg. Something like this (although this doesn't seem to work): PREPARE st(unknown) AS INSERT INTO foobar(a) VALUES ($1::unknown); > From what I'm reading now it seems the "read every record before > returning" behaviour is rooted in the libpq interface. Right. Again that's probably something that could be handled by exposing more/different API, but no one has stepped up to design it. I think that would be great, but I'm hoping it's not required to solve our immediate problem. Regards and thanks for the help, David ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Darwin Startup Script Patch
Clearly I can't spell. Regards, David --- postgresql-7.4RC1/contrib/start-scripts/PostgreSQL.darwin Mon Dec 9 13:26:07 2002 +++ /Users/david/Desktop/PostgreSQL.darwin Wed Nov 5 17:53:23 2003 @@ -18,9 +18,9 @@ # # POSTGRESQLSERVER=-YES- # -# The startup bundle will now be read to go. To prevent this script from +# The startup bundle will now be ready to go. To prevent this script from # starting PostgreSQL at system startup, simply change that line in -# /etc/hostconfig to: +# /etc/hostconfig back to: # # POSTGRESQLSERVER=-NO- # -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://www.kineticode.com/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] Kineticode. Setting knowledge in motion.[sm] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Darwin/Mac OS X Startup Script
All, I've simplified the Darwin/Mac OS X startup script I submitted earlier in the year. This version has only the two files required by the Darwin startup bundle design. Plus the sh script now uses Darwin-standard functions to start up PostgreSQL, and it checks for the presence of a variable in /etc/hostconfig, as do other Darwin startup scripts. I suggest that a new directory be created, contrib/start-scripts/darwin, and that these two files be put into it. Folks who want to use the script can read the comments inside it to figure out how to use it. Enjoy, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] Darwin.tar.gz Description: GNU Zip compressed data ---(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] Hard-coded PUBLIC in pg_dump
On Sunday, December 1, 2002, at 10:49 AM, Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: PUBLIC doesn't seem like a very common column name --- seems safe to make it reserved. We made 'value' reserved in 7.3, and that was a much more common one. I'm still quite unhappy about 'value', and would like to look into making it unreserved again. This business does show that there are some pitfalls in that, though :-( Actually, I don't think it's reserved in 7.3, only in the 7.4 development sources. Otherwise, Bricolage would fail hard, and it doesn't. So there's some time to play with this issue, I think. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Saturday, November 30, 2002, at 07:24 PM, Joe Conway wrote: That is one thing I'd like to take a look at. I think the problem is that certain byte-sequence/multibyte-encoding combinations are illegal, so it's not as simple an issue as it might first appear. My understanding is that the nul character is legal in a byte sequence, but if it's not properly escaped, it'll be parsed as the end of the statement. Unfortunately, I think that it's a very tough problem to solve. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.4 Wishlist
On Saturday, November 30, 2002, at 04:14 PM, Joe Conway wrote: Not quite sure what you mean by delimiter -- are you referring to double escaping vs single escaping? Oh crap, yes, that's exactly what I meant. s/delimited/escaped/g; Sorry. :-) David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 7.4 Wishlist
On Saturday, November 30, 2002, at 01:44 PM, Joe Conway wrote: * continue to improve usability of bytea datatype - easier explicit casting between bytea and text This wouldn't happen to include the idea of somehow eliminating the difference between how text strings are delimited and how bytea strings are delimited, would it? Best, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(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] Boolean casting in 7.3 -> changed?
On Wednesday, November 27, 2002, at 04:34 PM, David Walker wrote: Does this mean that in the future '342' may not be valid as an insert into a numeric field and that we should be using 342 instead? I didn't see an answer to this question, but I sincerely hope that the answer is "no." Otherwise, dynamic interfaces are going to have a much harder time. Take DBI (and DBD::Pg), for example. Most DBI users don't specify a data type when using placeholders. Therefore, DBD::Pg (and other DBDs, including DBD::Oracle) assume that the data types are strings. So it's not unusual for DBD::Pg to execute a query like this: INSERT INTO foo (numtype, varchartype, datetime, inttype) VALUES ('23.4', 'string', '2002-11-30 00:00:00', '12'); In order to allow the flexibility to remain, AFAICT PostgreSQL has to continue to allow strings to be converted to numbers on the back end. Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [INTERFACES] Interface update for 7.3
On Wednesday, November 27, 2002, at 02:46 AM, Jason E. Stewart wrote: David, could you take a look at 11quoting.t? I'm pretty sure you wrote this test (didn't you?). I converted the array of tests to a hash table so that I could give them names. However, I could only give the tests names like 'one', 'two', etc. Perhaps you can think of something more descriptive. No, I didn't write it. I just added the test to make sure that SQL_BINARY threw an exception. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(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] Request from eWeek for 7.3 comments
I am the maintainer and lead developer for Bricolage, an enterprise-class open-source content management system built on a PostgreSQL database. The Bricolage developers really look forward to 7.3's new features, which, like every release of PostgreSQL, set a new standard against which other databases measure themselves. Particularly important for Bricolage are the ability to drop columns and the new support for prepared SQL statements. Every major release of Bricolage requires changes to the database, often including the removal or change of a table column. Now that PostgreSQL can drop columns, future Bricolage upgrades can change database columns without leaving deprecated columns in the database. Furthermore, Bricolage runs in an Apache/mod_perl environment where many of the same database queries are executed many times over. The new support for prepared statements in PostgreSQL 7.3 will greatly enhance performance by reducing the number of times each of those SQL statements is prepared by PostgreSQL to once per Apache process -- for the lifetime of the process. These enhancements in PostgreSQL are great because they'll improve not just the database, but all applications that are built upon it. The speed with which PostgreSQL continues to develop and provide trickle-down benefits to the applications that depend upon it is simply second-to-none. Regards, David PS: eWeek has covered Bricolage here: http://www.eweek.com/article2/0,3959,652977,00.asp -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] On Tuesday, November 26, 2002, at 03:20 PM, Bruce Momjian wrote: I just spoke with Lisa Vaas from eWeek. She is writing an article on the upcoming PostgreSQL 7.3 release. (The release of 7.3 is scheduled for tomorrow.) She would like comments from users about the upcoming 7.3 features, listed at: http://developer.postgresql.org/docs/postgres/release.html#RELEASE-7-3 If you are interested, please reply to this email with any comments you might have. I have directed replies to her email address. She would like comments within the next few hours, until midnight EST. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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] 7.3rc2 Test Failures
On Tuesday, November 26, 2002, at 07:12 AM, Neil Conway wrote: Looks like a problem on your end... Oh, the message finally got through, did it? I chatted with Bruce yesterday and ran the tests again and they all passed. Thanks, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 7.3rc2 Test Failures
regression.diffs Description: Binary data regression.out Description: Binary data -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
On Monday, November 18, 2002, at 08:58 AM, Tom Lane wrote: Thinking about this, it occurs to me that there's no good reason why we couldn't allow parameter symbols ($n) to be considered type UNKNOWN initially. The type interpretation algorithms would then treat them just like quoted literal constants. After parsing finishes, PREPARE could scan the tree to see what type each symbol had been cast to. (You'd have to raise an error if multiple appearances of the same symbol had been cast to different types, but that'd be an uncommon case.) This form of PREPARE would presumably need some way of reporting back the types it had determined for the symbols; anyone have a feeling for the appropriate API for that? If I'm understanding you correctly this approach would make it much easier on dynamic drivers such as DBI and JDBC. Ideally, in DBI, I'd be able to do something like this: PREPARE my_stmt AS SELECT foo, bar FROM bat WHERE foo = $1 AND bar = $2; EXECUTE my_stmt('foo_val', 'bar_val'); It would be the responsibility of the PostgreSQL PREPARE parser to handle the data typing of $1 and $2, and the responsibility of the DBI client to pass in data of the appropriate type. Is this along the lines of what you're thinking, Tom? Regards, David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(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