Re: [PATCHES] New variable server_version_num
On Tue, Aug 01, 2006 at 04:25:00PM -0400, Christopher Browne wrote: ?column? | setting --+- 5432 | 7.4.13 (1 row) ?column? | setting --+- 5533 | 7.4.10 (1 row) ?column? | setting --+- 5532 | 8.0.5 (1 row) ?column? | setting --+-- 5882 | 8.2devel (1 row) If I wanted to, it oughtn't be difficult to string smash those settings into something very nearly useful... It may or may not be difficult, depending on your definition of 'difficult,' but it's very easy and reproducible to get something that can be cast to integer and compared that way. The existence of version-number-comparison libraries like version.pm points to the idea that it is, in fact, difficult to compare versions in general. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Replication Documentation
Hello, Peter Eisentraut wrote: 1. post information on pgsql-general 1.a. solicit comments 2. put information page on web site 3. link from documentation to web site I don't remember such a clear agreement either. I'm glad Chris has written something. And posting it to -docs seems a much better fit, IMHO. Also, I think we didn't really agree on where exactly to put what information. See my previous mail on -hackers for my opinion on that. I don't think this sort of material belongs directly into the PostgreSQL documentation. I agree with that. Regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway [EMAIL PROTECTED] writes: This patch retains the memory consumption savings but doesn't break any regression tests... I'm unconvinced that retail pfree's are the way to go. I just did some profiling of this test case: insert into foo values (0,0,0), (1,1,1), (2,2,2), (3,3,3), ... one million rows ... (97,97,97), (98,98,98), (99,99,99); using CVS tip, and got these oprofile results: samples %symbol name 3974210.1656 base_yyparse 38338 9.8065 XLogInsert 28247 7.2253 AllocSetAlloc 20490 5.2411 expression_tree_walker 16822 4.3029 ExecInitExpr 16469 4.2126 base_yylex 14789 3.7829 PageAddItem 11174 2.8582 LWLockAcquire 11167 2.8564 LWLockRelease 9195 2.3520 RewriteQuery 9120 2.3328 AllocSetFree 7788 1.9921 ExecInitValuesScan 7596 1.9430 ExecEvalConst 7586 1.9404 lappend 6860 1.7547 ValuesNext 6261 1.6015 heap_fill_tuple 6141 1.5708 MemoryContextAllocZeroAligned 5619 1.4373 fix_expr_references_walker 5613 1.4357 transformExpressionList 5269 1.3478 heap_insert 5177 1.3242 contain_vars_of_level_walker 4601 1.1769 heap_form_tuple 4345 1.1114 ExecutorRun 4299 1.0996 hash_any 4201 1.0746 MemoryContextAlloc 4061 1.0388 check_stack_depth It's slightly depressing that there's not more time being spent in places we can easily tweak, but anyway the salient point to me is that AllocSetFree is already chewing a nontrivial part of the runtime. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: Joe Conway [EMAIL PROTECTED] writes: This patch retains the memory consumption savings but doesn't break any regression tests... I'm unconvinced that retail pfree's are the way to go. I just did some profiling of this test case: snip It's slightly depressing that there's not more time being spent in places we can easily tweak, but anyway the salient point to me is that AllocSetFree is already chewing a nontrivial part of the runtime. That's undoubtedly true, and important for the case that isn't memory constrained (but where I'm already seeing us perform relatively well). But once we start the machine swapping, runtime goes in the toilet. And without addressing the memory leak somehow, we will start a machine swapping significantly earlier than mysql. Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] Allow commenting of variables in postgresql.conf to -
Peter Eisentraut wrote: The way I see it, combining a feature change with a code refactoring and random white space changes is a pretty optimal way to get your patch rejected. Please submit patches for these items separately. OK. I split patch to two parts. Part one is refactoring of set_config_options function. Part two implements feature Allow commenting of variables in postgresql.conf to restore them to defaults. Zdenek diff -r -c pgsql/src/backend/utils/misc/guc-file.l pgsql_1/src/backend/utils/misc/guc-file.l *** pgsql/src/backend/utils/misc/guc-file.l Thu Jul 27 10:30:41 2006 --- pgsql_1/src/backend/utils/misc/guc-file.l Wed Aug 2 13:35:27 2006 *** *** 50,56 static bool ParseConfigFile(const char *config_file, const char *calling_file, int depth, GucContext context, int elevel, struct name_value_pair **head_p, ! struct name_value_pair **tail_p); static void free_name_value_list(struct name_value_pair * list); static char *GUC_scanstr(const char *s); --- 50,57 static bool ParseConfigFile(const char *config_file, const char *calling_file, int depth, GucContext context, int elevel, struct name_value_pair **head_p, ! struct name_value_pair **tail_p, ! int *varcount); static void free_name_value_list(struct name_value_pair * list); static char *GUC_scanstr(const char *s); *** *** 114,121 void ProcessConfigFile(GucContext context) { ! int elevel; struct name_value_pair *item, *head, *tail; Assert(context == PGC_POSTMASTER || context == PGC_SIGHUP); --- 115,124 void ProcessConfigFile(GucContext context) { ! int elevel, i; struct name_value_pair *item, *head, *tail; + bool *apply_list = NULL; + int varcount = 0; Assert(context == PGC_POSTMASTER || context == PGC_SIGHUP); *** *** 134,158 if (!ParseConfigFile(ConfigFileName, NULL, 0, context, elevel, ! head, tail)) goto cleanup_list; /* Check if all options are valid */ ! for (item = head; item; item = item-next) { ! if (!set_config_option(item-name, item-value, context, ! PGC_S_FILE, false, false)) goto cleanup_list; } /* If we got here all the options checked out okay, so apply them. */ ! for (item = head; item; item = item-next) ! { ! set_config_option(item-name, item-value, context, ! PGC_S_FILE, false, true); ! } ! cleanup_list: free_name_value_list(head); } --- 137,192 if (!ParseConfigFile(ConfigFileName, NULL, 0, context, elevel, ! head, tail, varcount)) goto cleanup_list; + /* Can we allocate memory here, what about leaving here prematurely? */ + apply_list = (bool *) palloc(sizeof(bool) * varcount); + /* Check if all options are valid */ ! for (item = head, i = 0; item; item = item-next, i++) { ! bool isEqual, isContextOk; ! ! if (!verify_config_option(item-name, item-value, context, ! PGC_S_FILE, isEqual, isContextOk)) ! { ! ereport(elevel, ! (errcode(ERRCODE_CANT_CHANGE_RUNTIME_PARAM), ! errmsg(configuration file is invalid))); goto cleanup_list; + } + + if( isContextOk == false ) + { + apply_list[i] = false; + if( context == PGC_SIGHUP ) + { + if ( isEqual == false ) + ereport(elevel, + (errcode(ERRCODE_CANT_CHANGE_RUNTIME_PARAM), + errmsg(parameter \%s\ cannot be changed after server start; configuration file change ignored, + item-name))); + } + else + /* if it is boot phase, context must be valid for all + * configuration item. */ + goto cleanup_list; + } + else + apply_list[i] = true; } /* If we got here all the options checked out okay, so apply them. */ ! for (item = head, i = 0; item; item = item-next, i++) ! if (apply_list[i]) ! set_config_option(item-name, item-value, context, ! PGC_S_FILE, false, true); ! ! cleanup_list: ! if (apply_list) ! pfree(apply_list); free_name_value_list(head); } *** *** 189,201 ParseConfigFile(const char *config_file, const char *calling_file, int depth, GucContext context, int elevel, struct name_value_pair **head_p, ! struct name_value_pair **tail_p) { ! bool OK = true; ! char abs_path[MAXPGPATH]; ! FILE *fp; YY_BUFFER_STATE lex_buffer; ! int token; /* * Reject too-deep include nesting depth. This is just a safety check --- 223,236 ParseConfigFile(const char *config_file, const char *calling_file, int depth, GucContext context, int elevel, struct name_value_pair **head_p, ! struct name_value_pair **tail_p, ! int *varcount) { ! bool OK = true; ! char abs_path[MAXPGPATH]; ! FILE *fp; YY_BUFFER_STATE lex_buffer; ! inttoken; /* * Reject too-deep include nesting depth. This is just a safety check *** *** 289,295
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway [EMAIL PROTECTED] writes: That's undoubtedly true, and important for the case that isn't memory constrained (but where I'm already seeing us perform relatively well). But once we start the machine swapping, runtime goes in the toilet. And without addressing the memory leak somehow, we will start a machine swapping significantly earlier than mysql. I'm not arguing that we don't need to work on the memory usage ... just that I'm not very happy with that particular approach. I wonder whether there is any reasonable way to determine which data structures are responsible for how much space ... in my test I'm seeing MessageContext: 822075440 total in 104 blocks; 4510280 free (1 chunks); 817565160 used ExecutorState: 8024624 total in 3 blocks; 20592 free (12 chunks); 8004032 used so it seems mostly not the executor's fault, but that's not much to go on. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: I wonder whether there is any reasonable way to determine which data structures are responsible for how much space ... in my test I'm seeing MessageContext: 822075440 total in 104 blocks; 4510280 free (1 chunks); 817565160 used ExecutorState: 8024624 total in 3 blocks; 20592 free (12 chunks); 8004032 used so it seems mostly not the executor's fault, but that's not much to go on. I was doing it by sprinkling MemoryContextStats() in various places. I'll spend some time again later today and see if I can narrow it down to specific data structures using that. It shouldn't be too hard -- the patch I sent last night only pfrees a few structures, and they represent the bulk of what we need to clean up. Joe ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: I wonder whether there is any reasonable way to determine which data structures are responsible for how much space ... in my test I'm seeing I was doing it by sprinkling MemoryContextStats() in various places. Yeah, I've just been doing that and some hand analysis too. What I get (on a 64-bit machine) is that essentially all the space goes into lists of A_Const lists: 32000 lists of Const lists: 32000 transformInsertRow extra lists: 14400 I think we could safely list_free the input list in transformInsertRow as your patch suggests, which would buy back the 144M part. But I don't believe it's safe at all to free the raw_parser output --- the grammar sometimes makes multiple links to the same subtree, eg in BETWEEN. In any case the patch as proposed wouldn't catch all the detritus for any case more complicated than a simple integer constant. The way that the list memory usage works (again, 64-bit machine) is sizeof(List) = 24 sizeof(ListCell) = 16 sizeof(A_Const) = 32 Each of these nodes will have 16 bytes palloc overhead, and the List header will be rounded up to 32 bytes as well, so we have total space for a 3-element integer list of 32+16 + (16+16 + 32+16) * 3 Add in 16+16 for the associated ListCell of the top list-of-lists, and you come to 320 bytes per sublist. Const happens to also be 32 bytes so the transformed lists are the same size. It's interesting to reflect on the fact that this comes to 184 bytes of useful data and 136 bytes of palloc overhead per row ... not sure if we can do much about the overhead though. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Tom Lane wrote: Yeah, I've just been doing that and some hand analysis too. What I get (on a 64-bit machine) is that essentially all the space goes into lists of A_Const lists: 32000 lists of Const lists: 32000 transformInsertRow extra lists: 14400 I think we could safely list_free the input list in transformInsertRow as your patch suggests, which would buy back the 144M part. But I don't believe it's safe at all to free the raw_parser output --- the grammar sometimes makes multiple links to the same subtree, eg in BETWEEN. In any case the patch as proposed wouldn't catch all the detritus for any case more complicated than a simple integer constant. :-( The way that the list memory usage works (again, 64-bit machine) is sizeof(List) = 24 sizeof(ListCell) = 16 sizeof(A_Const) = 32 Each of these nodes will have 16 bytes palloc overhead, and the List header will be rounded up to 32 bytes as well, so we have total space for a 3-element integer list of 32+16 + (16+16 + 32+16) * 3 Add in 16+16 for the associated ListCell of the top list-of-lists, and you come to 320 bytes per sublist. Const happens to also be 32 bytes so the transformed lists are the same size. What if we built an array of A_Const nodes instead of a List? Maybe we could use something akin to appendStringInfo()/enlargeStringInfo() to build the array of nodes and enlarge it in chunks. Joe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway wrote: What if we built an array of A_Const nodes instead of a List? Maybe we could use something akin to appendStringInfo()/enlargeStringInfo() to build the array of nodes and enlarge it in chunks. In inval.c you find this: /* * To minimize palloc traffic, we keep pending requests in successively- * larger chunks (a slightly more sophisticated version of an expansible * array). All request types can be stored as SharedInvalidationMessage * records. The ordering of requests within a list is never significant. */ typedef struct InvalidationChunk { struct InvalidationChunk *next; /* list link */ int nitems; /* # items currently stored in chunk */ int maxitems; /* size of allocated array in this chunk */ SharedInvalidationMessage msgs[1]; /* VARIABLE LENGTH ARRAY */ } InvalidationChunk;/* VARIABLE LENGTH STRUCTURE */ Which might give you an idea ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Joe Conway [EMAIL PROTECTED] writes: What if we built an array of A_Const nodes instead of a List? Maybe we could use something akin to appendStringInfo()/enlargeStringInfo() to build the array of nodes and enlarge it in chunks. For lists this short I'm not sure how much of a win it'd be. It's interesting though to think about doing something like that within the List abstraction itself. We did a fastlist hack once before and it was a crock ... don't want to do that again. But now that we've got a distinction between List and ListCell you could imagine that a List header has a small private array of ListCells ... tuning the size might be tricky though. Another thing we could consider is flattening the double-level list into a single list ... probably be a pain notationally, but it'd save one List header and one ListCell per VALUES sublist. And it would offer more traction for an array-inside-Lists optimization. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] SQL conformance, optional feature T621
Hello, After my Tom's patch implementing the multi-argument aggregates it seems that we have fully completed the optional feature T621 of the SQL 2003 standart: 195) Specifications for Feature T621, ?Enhanced numeric functions?: a) Subclause 6.27, ?numeric value function?: i) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a natural logarithm. ii) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain an exponential function. iii) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a power function. iv) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a square root. v) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a floor function. vi) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a ceiling function. b) Subclause 10.9, ?aggregate function?: i) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a computational operation that immediately contains STDDEV_POP, STDDEV_SAMP, VAR_POP, or VAR_SAMP. ii) Without Feature T621, ?Enhanced numeric functions?, conforming SQL language shall not contain a binary set function type, where binary set function type ::= COVAR_POP | COVAR_SAMP | CORR | REGR_SLOPE | REGR_INTERCEPT | REGR_COUNT | REGR_R2 | REGR_AVGX | REGR_AVGY | REGR_SXX | REGR_SYY | REGR_SXY Objections ? So, I'm sending the patch for the sql_features.txt Regards, Sergey *** Sergey E. Koposov Max Planck Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: [EMAIL PROTECTED]Index: ../src/backend/catalog/sql_features.txt === RCS file: /projects/cvsroot/pgsql/src/backend/catalog/sql_features.txt,v retrieving revision 1.14 diff -c -r1.14 sql_features.txt *** ../src/backend/catalog/sql_features.txt 3 Mar 2006 04:31:07 - 1.14 --- ../src/backend/catalog/sql_features.txt 31 Jul 2006 09:14:51 - *** *** 429,435 T611 Elementary OLAP operations NO T612 Advanced OLAP operationsNO T613 SamplingNO ! T621 Enhanced numeric functions NO T631 IN predicate with one list element NO T641 Multiple column assignment NO T651 SQL-schema statements in SQL routines NO --- 429,435 T611 Elementary OLAP operations NO T612 Advanced OLAP operationsNO T613 SamplingNO ! T621 Enhanced numeric functions YES T631 IN predicate with one list element NO T641 Multiple column assignment NO T651 SQL-schema statements in SQL routines NO ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PATCHES] GIN vs. statistics collector
I think there's a call to pgstat_count_index_scan missing in GIN. Currently, the idx_scan column of pg_stat_*_indexes is stuck at zero for GIN indexes. Patch attached. Thanks, andreas Index: src/backend/access/gin/ginscan.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/gin/ginscan.c,v retrieving revision 1.3 diff -c -r1.3 ginscan.c *** src/backend/access/gin/ginscan.c 14 Jul 2006 14:52:16 - 1.3 --- src/backend/access/gin/ginscan.c 2 Aug 2006 23:07:30 - *** *** 15,20 --- 15,21 #include postgres.h #include access/genam.h #include access/gin.h + #include pgstat.h #include utils/memutils.h *** *** 155,160 --- 156,163 if ( so-nkeys == 0 ) elog(ERROR, Gin doesn't support full scan due to it's awful inefficiency); + + pgstat_count_index_scan(scan-xs_pgstat_info); } Datum ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Values list-of-targetlists patch for comments (was Re: [PATCHES]
Docs and regression tests attached. One slightly annoying thing is this: --- regression=# declare foo cursor with hold for VALUES(1,2), (3, 4); DECLARE CURSOR regression=# declare foo2 cursor with hold for (VALUES(1,2), (3, 4)) as foo(i, j); ERROR: syntax error at or near as LINE 1: ...e foo2 cursor with hold for (VALUES(1,2), (3, 4)) as foo(i, ... --- Now, we can just rewrite the second query as: --- declare foo2 cursor with hold for select * from (VALUES(1,2), (3, 4)) as foo(i, j); --- but it's not immediately obvious. Not worth busting up the grammar for it, though. And, it's not spec. GavinIndex: doc/src/sgml/ref/declare.sgml === RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/declare.sgml,v retrieving revision 1.37 diff -c -p -r1.37 declare.sgml *** doc/src/sgml/ref/declare.sgml 26 Feb 2006 03:20:46 - 1.37 --- doc/src/sgml/ref/declare.sgml 3 Aug 2006 04:18:28 - *** DECLARE liahona CURSOR FOR SELECT * FROM *** 275,280 --- 275,288 See xref linkend=sql-fetch endterm=sql-fetch-title for more examples of cursor usage. /para + + para +The cursor replaceable class=parameterquery/ clause can also +be a literalVALUES/ list: + programlisting + DECLARE cols CURSOR FOR VALUES(1,2), (3,4); + /programlisting + /para /refsect1 refsect1 Index: doc/src/sgml/ref/delete.sgml === RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/delete.sgml,v retrieving revision 1.26 diff -c -p -r1.26 delete.sgml *** doc/src/sgml/ref/delete.sgml22 Jan 2006 05:20:33 - 1.26 --- doc/src/sgml/ref/delete.sgml3 Aug 2006 03:26:58 - *** DELETE FROM [ ONLY ] replaceable class= *** 117,122 --- 117,128 in the replaceable class=PARAMETERusinglist/replaceable, unless you wish to set up a self-join. /para + + para + The replaceable class=PARAMETERusinglist/ may also contain a + literalVALUES/ list, evaluating to one or more rows. These + rows may also be referenced in the literalWHERE/ clause. +/para /listitem /varlistentry *** DELETE FROM films WHERE kind lt;gt; 'M *** 191,196 --- 197,213 DELETE FROM films; /programlisting /para + + para +Delete films made after 1990 which are 'Horror' and films made +after 2000 which are 'Crime'. To do this, we use a literalVALUES/ +list in the literalUSING/ clause. + programlisting + DELETE FROM films USING (VALUES('1990-01-01, 'Horror'), ('2000-01-01', 'Crime)) + AS det (year, kind) WHERE films.date_prod = det.year AND + films.kind = det.kind; + /programlisting + /para /refsect1 refsect1 Index: doc/src/sgml/ref/insert.sgml === RCS file: /usr/local/cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v retrieving revision 1.30 diff -c -p -r1.30 insert.sgml *** doc/src/sgml/ref/insert.sgml17 Nov 2005 22:14:51 - 1.30 --- doc/src/sgml/ref/insert.sgml2 Aug 2006 22:40:14 - *** PostgreSQL documentation *** 21,27 refsynopsisdiv synopsis INSERT INTO replaceable class=PARAMETERtable/replaceable [ ( replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ! { DEFAULT VALUES | VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) | replaceable class=PARAMETERquery/replaceable } /synopsis /refsynopsisdiv --- 21,27 refsynopsisdiv synopsis INSERT INTO replaceable class=PARAMETERtable/replaceable [ ( replaceable class=PARAMETERcolumn/replaceable [, ...] ) ] ! { DEFAULT VALUES | VALUES ( { replaceable class=PARAMETERexpression/replaceable | DEFAULT } [, ...] ) [, ( ... ) ] | replaceable class=PARAMETERquery/replaceable } /synopsis /refsynopsisdiv *** INSERT INTO replaceable class=PARAMETE *** 30,37 para commandINSERT/command inserts new rows into a table. !One can insert a single row specified by value expressions, !or several rows as a result of a query. /para para --- 30,37 para commandINSERT/command inserts new rows into a table. !One can insert one or more rows specified by value expressions, !or zero or more rows resulting from a query. /para para *** INSERT INTO films VALUES *** 162,167 --- 162,177 /para para +Insert multiple rows into a table literalfilms/: + + programlisting + INSERT INTO films VALUES + ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), + ('HG120', 'The Dinner Game', 140, '1998-10-12', 'Comedy'); + /programlisting + /para + + para In this example, the literallen/literal column is omitted and therefore it will have the default value: Index: doc/src/sgml/ref/select.sgml