Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
This one from Ben Calvert. It uses the (imho clearer) NOW() rather than 'NOW' in a PL/PgSQL function example. Applied, thanks. Why not use CURRENT_TIMSTAMP instead of NOW() everywhere in the docs. I mean, it's standard and NOW() isn't... Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: pl/pgsql cleanup
Neil Conway [EMAIL PROTECTED] writes: On Wed, 2005-02-09 at 23:57 -0500, Tom Lane wrote: That seems like a step backwards from the current behavior [...] Hmm, fair enough. Is this better? Yeah, looks better, though I question the use of embedded in the message --- seems a bit jargony. Are you going to post a revised patch? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [Fwd: Re: [DOCS] Instructions for FreeBSD ipc config showing
Applied by Neil. Thanks. --- Mark Kirkwood wrote: As discussed on -docs. (Not really sure if I need to explicitly forward here) regards Mark -- Start of included mail From: Mark Kirkwood [EMAIL PROTECTED] Date: Tue, 01 Feb 2005 14:29:39 +1300 To: Tom Lane [EMAIL PROTECTED] cc: pgsql-docs@postgresql.org Subject: Re: [DOCS] Instructions for FreeBSD ipc config showing age Tom Lane wrote: This seems a bit awkwardly phrased: + para +Versions before 4.0 will require a kernel rebuild, see the +systemitem class=osnameNetBSD/ and systemitem +class=osnameOpenBSD/ instructions above, however the key word is +literaloptions/literal (plural) in this case. /para Perhaps FreeBSD versions before 4.0 work like NetBSD and OpenBSD (see above), except that the configuration file uses the key word options instead of option. Yeah - much nicer. Also, alphabetization would suggest putting the FreeBSD entry before the other two, not after, so maybe that should be see below. Thanks - had not considered that! Amended patch attached. regards Mark --- runtime.sgml.orig Sat Jan 29 18:30:50 2005 +++ runtime.sgml Tue Feb 1 14:25:53 2005 @@ -4351,9 +4351,51 @@ varlistentry termsystemitem class=osnameFreeBSD//term + indextermprimaryFreeBSD/secondaryIPC configuration// + listitem + para +The default settings are only suitable for small installations +(default varnameSHMMAX/varname is 32 MB). Changes can be made via +the commandsysctl/command or commandloader/command interfaces. +Firstly the ones that are settable using commandsysctl/command: +screen +prompt$/prompt userinputsystcl -w kern.ipc.shmall=32768/userinput +prompt$/prompt userinputsystcl -w kern.ipc.shmmax=134217728/userinput +prompt$/prompt userinputsystcl -w kern.ipc.semmap=256/userinput +/screen +These to be saved between reboots in filename/etc/sysctl.conf. + /para + para +The remaining sempahore settings are read only as far as +commandsysctl/command is concerned, but can be changed before boot +using the commandloader/command prompt: +screen +prompt(loader)/prompt userinputset kern.ipc.semmni=256/userinput +prompt(loader)/prompt userinputset kern.ipc.semmns=512/userinput +prompt(loader)/prompt userinputset kern.ipc.semmnu=256/userinput +/screen +Similarly these can be saved between reboots in +filename/boot/loader.conf/filename. + /para + para +You might also want to configure your kernel to lock shared +memory into RAM and prevent it from being paged out to swap. +Use the commandsysctl/ setting +literalkern.ipc.shm_use_phys/. + /para + para +systemitem class=osnameFreeBSD/ versions before 4.0 work like +systemitem class=osnameNetBSD/ and systemitem class=osname +OpenBSD/ (see below), except that the configuration file uses the +key word options instead of option. + /para + /listitem + /varlistentry + + + varlistentry termsystemitem class=osnameNetBSD//term termsystemitem class=osnameOpenBSD//term - indextermprimaryFreeBSD/secondaryIPC configuration// indextermprimaryNetBSD/secondaryIPC configuration// indextermprimaryOpenBSD/secondaryIPC configuration// listitem @@ -4364,19 +4406,16 @@ the option varnameSHMMAXPGS/ (in pages). The following shows an example of how to set the various parameters: programlisting -options SYSVSHM -options SHMMAXPGS=4096 -options SHMSEG=256 +option SYSVSHM +option SHMMAXPGS=4096 +option SHMSEG=256 -options SYSVSEM -options SEMMNI=256 -options SEMMNS=512 -options SEMMNU=256 -options SEMMAP=256 +option SYSVSEM +option SEMMNI=256 +option SEMMNS=512 +option SEMMNU=256 +option SEMMAP=256 /programlisting -(On systemitem class=osnameNetBSD/ and systemitem -class=osnameOpenBSD/ the key word is actually -literaloption/literal singular.) /para para You might also want to configure your kernel to lock shared ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- End of included mail. ---(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 -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
On Thu, 2005-02-10 at 16:32 +0900, Michael Glaesemann wrote: I realize it's a bit late, but it might not be a bad idea to use CURRENT_TIMESTAMP rather than NOW(), as it's per SQL spec. I can't say I can get very excited about it; someone is free to submit a patch if they like. -Neil ---(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
Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
Neil Conway [EMAIL PROTECTED] writes: On Thu, 2005-02-10 at 16:32 +0900, Michael Glaesemann wrote: I realize it's a bit late, but it might not be a bad idea to use CURRENT_TIMESTAMP rather than NOW(), as it's per SQL spec. I can't say I can get very excited about it; someone is free to submit a patch if they like. Considering that the example in question is embedded in the 100%-not-SQL- standard language plpgsql, I can't get excited about this either. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: pl/pgsql cleanup
On Thu, 2005-02-10 at 10:15 -0500, Tom Lane wrote: Yeah, looks better, though I question the use of embedded in the message --- seems a bit jargony. Are you going to post a revised patch? Actually the code to present error messages as in the previous message was in the previous patch, just #if 0'd out :) Attached is a revised patch that removes embedded and updates the regression tests. I'll apply this to HEAD later today barring any further suggestions for improvement. -Neil plpgsql_cleanup-27.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] reject empty string in float[48], oid
This patch changes the input routines for float4, float8 and oid to
reject empty strings (rather than treating them as 0). In 8.0 we issue
a warning about this behavior and indicate that the input will not be
accepted by a future release, so it seems reasonable to disallow the
input in 8.1. The patch also updates the regression tests.
Barring any objections, I'll apply this to HEAD tomorrow.
-Neil
Index: src/backend/utils/adt/float.c
===
RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/float.c,v
retrieving revision 1.112
diff -c -r1.112 float.c
*** src/backend/utils/adt/float.c 31 Dec 2004 22:01:21 - 1.112
--- src/backend/utils/adt/float.c 11 Feb 2005 01:10:19 -
***
*** 267,287
/*
* Check for an empty-string input to begin with, to avoid the
* vagaries of strtod() on different platforms.
- *
- * In releases prior to 8.0, we accepted an empty string as valid input
- * (yielding a float4 of 0). In 8.0, we accept empty strings, but emit
- * a warning noting that the feature is deprecated. In 8.1+, the
- * warning should be replaced by an error.
*/
if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg(deprecated input syntax for type real: \\),
! errdetail(This input will be rejected in
! a future release of PostgreSQL.)));
! PG_RETURN_FLOAT4((float4) 0.0);
! }
/* skip leading whitespace */
while (*num != '\0' isspace((unsigned char) *num))
--- 267,278
/*
* Check for an empty-string input to begin with, to avoid the
* vagaries of strtod() on different platforms.
*/
if (*num == '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg(invalid input syntax for type real: \%s\,
! orig_num)));
/* skip leading whitespace */
while (*num != '\0' isspace((unsigned char) *num))
***
*** 444,464
/*
* Check for an empty-string input to begin with, to avoid the
* vagaries of strtod() on different platforms.
- *
- * In releases prior to 8.0, we accepted an empty string as valid input
- * (yielding a float8 of 0). In 8.0, we accept empty strings, but emit
- * a warning noting that the feature is deprecated. In 8.1+, the
- * warning should be replaced by an error.
*/
if (*num == '\0')
! {
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg(deprecated input syntax for type double precision: \\),
! errdetail(This input will be rejected in
! a future release of PostgreSQL.)));
! PG_RETURN_FLOAT8(0.0);
! }
/* skip leading whitespace */
while (*num != '\0' isspace((unsigned char) *num))
--- 435,446
/*
* Check for an empty-string input to begin with, to avoid the
* vagaries of strtod() on different platforms.
*/
if (*num == '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg(invalid input syntax for type double precision: \%s\,
! orig_num)));
/* skip leading whitespace */
while (*num != '\0' isspace((unsigned char) *num))
Index: src/backend/utils/adt/oid.c
===
RCS file: /var/lib/cvs/pgsql/src/backend/utils/adt/oid.c,v
retrieving revision 1.60
diff -c -r1.60 oid.c
*** src/backend/utils/adt/oid.c 31 Dec 2004 22:01:22 - 1.60
--- src/backend/utils/adt/oid.c 11 Feb 2005 01:09:14 -
***
*** 33,50
char *endptr;
Oid result;
- /*
- * In releases prior to 8.0, we accepted an empty string as valid
- * input (yielding an OID of 0). In 8.0, we accept empty strings, but
- * emit a warning noting that the feature is deprecated. In 8.1+, the
- * warning should be replaced by an error.
- */
if (*s == '\0')
! ereport(WARNING,
! (errcode(ERRCODE_WARNING_DEPRECATED_FEATURE),
! errmsg(deprecated input syntax for type oid: \\),
! errdetail(This input will be rejected in
! a future release of PostgreSQL.)));
errno = 0;
cvt = strtoul(s, endptr, 10);
--- 33,43
char *endptr;
Oid result;
if (*s == '\0')
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
! errmsg(invalid input syntax for type oid: \%s\,
! s)));
errno = 0;
cvt = strtoul(s, endptr, 10);
Index: src/test/regress/expected/float4-exp-three-digits.out
===
RCS file: /var/lib/cvs/pgsql/src/test/regress/expected/float4-exp-three-digits.out,v
retrieving revision 1.5
diff -c -r1.5 float4-exp-three-digits.out
*** src/test/regress/expected/float4-exp-three-digits.out 15 Mar 2004 16:20:51 - 1.5
--- src/test/regress/expected/float4-exp-three-digits.out 11 Feb 2005 01:17:06 -
***
*** 31,36
--- 31,38
ERROR: invalid input syntax for type real: -
Re: [PATCHES] WIP: pl/pgsql cleanup
Neil Conway [EMAIL PROTECTED] writes: Attached is a revised patch that removes embedded and updates the regression tests. I'll apply this to HEAD later today barring any further suggestions for improvement. You've broken the FOR syntax. You may not assume that the first token of a FOR-over-SELECT is literally SELECT; it could for example be a left parenthesis starting some kind of UNION construct. This is why it's so hard to do it right, and why the old way was so messy. (As of CVS tip it also works to do something like for x in explain analyze select ... I will grant that that didn't work before today, but it wasn't plpgsql's fault that it didn't.) I suggest you go back to the old parsing code for FOR. I think it's a bad idea to entirely override the error context stack as you do in check_sql_expr(). I can see the case for removing the immediately previous entry, if you're sure it is plpgsql_compile_error_callback(), but that doesn't translate to it being a good idea to knock out any surrounding levels of context. (Also I thought you were going to reword the embedded message?) The head comment added to do_compile could stand some copy-editing :-( Otherwise it looks pretty good... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
Peter Eisentraut [EMAIL PROTECTED] writes: I was under the impression that our PL/pgSQL is at least partially an attempt to implement SQL:2003, part 4. No, it's an attempt to emulate Oracle's PL/SQL. Any similarity to spec documents dated later than the original creation of plpgsql (1998) is quite accidental... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Clarify use of NOW() in pl/pgsql docs
Tom Lane wrote: Considering that the example in question is embedded in the 100%-not-SQL- standard language plpgsql, I can't get excited about this either. I was under the impression that our PL/pgSQL is at least partially an attempt to implement SQL:2003, part 4. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
Re: [PATCHES] reject empty string in float[48], oid
Neil Conway [EMAIL PROTECTED] writes: This patch changes the input routines for float4, float8 and oid to reject empty strings (rather than treating them as 0). In 8.0 we issue a warning about this behavior and indicate that the input will not be accepted by a future release, so it seems reasonable to disallow the input in 8.1. The patch also updates the regression tests. If you're going to add regression tests, how about testing the case of a non-empty-but-all-whitespace string? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] reject empty string in float[48], oid
Neil Conway [EMAIL PROTECTED] writes: On Thu, 2005-02-10 at 20:53 -0500, Tom Lane wrote: If you're going to add regression tests, how about testing the case of a non-empty-but-all-whitespace string? AFAICS that is already tested for. [ looks again... ] Doh. Maybe you should put these tests next to those then... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] WIP: pl/pgsql cleanup
On Thu, 2005-02-10 at 20:48 -0500, Tom Lane wrote: You've broken the FOR syntax. You may not assume that the first token of a FOR-over-SELECT is literally SELECT; it could for example be a left parenthesis starting some kind of UNION construct. Yeah, I was wondering if this would break anything, I just forgot to ask about it :( Looking for two periods is pretty ugly. I was thinking we might be able to look at the for loop variable: if it was previously undeclared, it must be an integer for loop. If it was declared but is not of a row or record type, it must also be an integer for loop. But this is still ambiguous in the case of a declared row or record type -- it could either be a SELECT loop or an integer loop, and in the latter case the loop variable would shadow the variable in the enclosing block :( Unless we can figure out a better way to do this, I'll just revert to the old kludge. I think it's a bad idea to entirely override the error context stack as you do in check_sql_expr(). I can see the case for removing the immediately previous entry, if you're sure it is plpgsql_compile_error_callback(), but that doesn't translate to it being a good idea to knock out any surrounding levels of context. Yes, that's a good point. I'll change the patch to just elide the previous entry from the stack of callbacks, which is going to be plpgsql_compile_error_callback (unfortunately we can't actually verify that, AFAICS, since that callback is private to pl_comp.c) -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] reject empty string in float[48], oid
On Thu, 2005-02-10 at 21:04 -0500, Tom Lane wrote: Maybe you should put these tests next to those then... Ok, fair enough. Patch applied to HEAD. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] [Fwd: Re: [DOCS] How the planner uses statistics]
As discussed on -docs.
---BeginMessage---
Post feedback changes - thanks to all who commented!
Mark Kirkwood wrote:
I wanted to understand how the planner 'knows' how many rows are likely
to be emitted in a given stage of a query, and wrote down some examples
for my own benefit - I then wondered if this would be a good addition to
the 'Performance Tips' chapter. So... err here it is.
Comments welcome.
--- perform.sgml.orig Sat Feb 5 12:45:36 2005
+++ perform.sgmlTue Feb 8 17:15:48 2005
@@ -470,6 +470,286 @@
/sect1
+
+ sect1 id=planner-stats-how
+ titleHow the Planner Uses Statistics/title
+
+ indexterm zone=planner-stats-how
+ primarystatistics/primary
+ secondaryof the planner/secondary
+ /indexterm
+
+ para
+ This section builds on the material covered in the previous two and
+ shows how the planner uses the system statistics to estimate the number of
+ rows each stage of a query might return. We will adopt the approach of
+ showing by example, which should provide a good feel for how this works.
+ /para
+
+ para
+ Continuing with the examples drawn from the regression test
+ database (and 8.0 sources), let's start with a simple query which has
+ one restriction in its literalWHERE/literal clause:
+
+programlisting
+EXPLAIN SELECT * FROM tenk1 WHERE unique1 lt; 1000;
+
+ QUERY PLAN
+
+ Seq Scan on tenk1 (cost=0.00..470.00 rows=1031 width=244)
+ Filter: (unique1 lt; 1000)
+
+/programlisting
+
+ The planner examines the literalWHERE/literal clause condition:
+
+programlisting
+unique1 lt; 1000
+/programlisting
+
+ and looks up the restriction function for the operator
+ literallt;/literal in classnamepg_operator/classname.
+ This is held in the column structfieldoprrest/structfield,
+ and the result in this case is functionscalarltsel/function.
+ The functionscalarltsel/function function retrieves the histogram for
+ structfieldunique1/structfield from classnamepg_statistics/classname
+ - we can follow this by using the simpler classnamepg_stats/classname
+ view:
+
+programlisting
+SELECT histogram_bounds FROM pg_stats
+WHERE tablename='tenk1' AND attname='unique1';
+
+ histogram_bounds
+--
+ {1,970,1943,2958,3971,5069,6028,7007,7919,8982,9995}
+/programlisting
+
+ Next the fraction of the histogram occupied by quotelt; 1000/quote
+ is worked out. This is the selectivity. The histogram divides the range
+ into equal frequency buckets, so all we have to do is locate the bucket
+ that our value is in and count emphasispart/emphasis of it and
+ emphasisall/emphasis of the ones before. The value 1000 is clearly in
+ the second (970 - 1943) bucket, so by assuming a linear distribution of
+ values inside each bucket we can calculate the selectivity as:
+
+programlisting
+selectivity = (1 + (1000 - 970)/(1943 - 970)) / 10
+= 0.1031
+/programlisting
+
+ that is, one whole bucket plus a linear fraction of the second, divided by
+ the number of buckets. The estimated number of rows can now be calculated as
+ the product of the selectivity and the cardinality of
+ classnametenk1/classname:
+
+programlisting
+rows = 1 * 0.1031
+ = 1031
+/programlisting
+
+ /para
+
+ para
+ Next let's consider an example with a literalWHERE/literal clause using
+ the literal=/literal operator:
+
+programlisting
+EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'AT';
+
+QUERY PLAN
+--
+ Seq Scan on tenk1 (cost=0.00..470.00 rows=31 width=244)
+ Filter: (stringu1 = 'AT'::name)
+/programlisting
+
+ Again the planner examines the literalWHERE/literal clause condition:
+
+programlisting
+stringu1 = 'AT'
+/programlisting
+
+ and looks up the restriction function for literal=/literal, which is
+ functioneqsel/function. This case is a bit different, as the most
+ common values mdash; acronymMCV/acronyms, are used to determine the
+ selectivity. Let's have a look at these, with some extra columns that will
+ be useful later:
+
+programlisting
+SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM
pg_stats
+WHERE tablename='tenk1' AND attname='stringu1';
+
+null_frac | 0
+n_distinct| 672
+most_common_vals |
{FD,NH,AT,BG,EB,MO,ND,OW,BH,BJ}
+most_common_freqs |
{0.0033,0.0033,0.003,0.003,0.003,0.003,0.003,0.003,0.0027,0.0027}
+/programlisting
+
+ The selectivity is merely the frequency corresponding to 'AT':
+
+programlisting
+selectivity = 0.003
+/programlisting
+
+ The estimated number of rows is just the product of this with the
+ cardinality of classnametenk1/classname as before:
+
+programlisting
+rows = 1 * 0.003
+ = 30
+/programlisting
+
+
Re: [PATCHES] WIP: pl/pgsql cleanup
Neil Conway [EMAIL PROTECTED] writes: ... Looking for two periods is pretty ugly. I was thinking we might be able to look at the for loop variable: if it was previously undeclared, it must be an integer for loop. If it was declared but is not of a row or record type, it must also be an integer for loop. Congratulations, you just reinvented the scheme we used before 8.0. It's *not* an improvement. The dot-dot business is better. At least, I'm not going to hold still for reverting this logic when there have so far been zero field complaints about it, and there were plenty of complaints about the test based on variable datatype. Yes, that's a good point. I'll change the patch to just elide the previous entry from the stack of callbacks, which is going to be plpgsql_compile_error_callback (unfortunately we can't actually verify that, AFAICS, since that callback is private to pl_comp.c) IMHO verifying that is well worth an extern. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [Fwd: Re: [DOCS] How the planner uses statistics]
Mark Kirkwood [EMAIL PROTECTED] writes: As discussed on -docs. Hmmm ... it strikes me that someone wanting this level of detail would be better advised to look into the source code. I certainly wouldn't want to promise that a chunk of documentation like this will stay up-to-date. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [Fwd: Re: [DOCS] How the planner uses statistics]
Tom Lane wrote: Hmmm ... it strikes me that someone wanting this level of detail would be better advised to look into the source code. I did wonder about about it being better placed in 'internals' somewhere, but it seemed to follow on from the 'explain' and 'stats' sections quite well. I certainly wouldn't want to promise that a chunk of documentation like this will stay up-to-date. Yeah - that is a concern... no doco is better than wrong doco :-) Mind you, ISTM that the same objection could be leveled at the 'stats' section best wishes Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
