[PATCHES] [8.4] Updated WITH clause patch (non-recursive)
Attached is an updated version of Greg Stark's patch to add support for the non-recursive variant of the SQL99 WITH clause[1]. I haven't looked at the actual functionality of the patch yet (which is quite trivial) -- I just fixed up bitrot and the like. I also removed support for RECURSIVE and the search/cycle clause, along with their associated keywords -- the current patch doesn't approach anything close to adding support for the non-recursive case, so it seems like a net loss to add additional keywords for no gain in functionality. Remaining work is to review the guts of the patch (which shouldn't take long), and write documentation and regression tests. I'm personally hoping to see this get into the tree fairly early in the 8.4 cycle, pending discussion of course. -Neil [1] http://archives.postgresql.org/pgsql-patches/2007-03/msg00139.php http://archives.postgresql.org/pgsql-patches/2007-04/msg00055.php Index: src/backend/nodes/copyfuncs.c === RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/nodes/copyfuncs.c,v retrieving revision 1.387 diff -p -c -r1.387 copyfuncs.c *** src/backend/nodes/copyfuncs.c 1 Jan 2008 19:45:50 - 1.387 --- src/backend/nodes/copyfuncs.c 27 Jan 2008 07:05:29 - *** _copySelectStmt(SelectStmt *from) *** 1930,1935 --- 1930,1936 COPY_NODE_FIELD(limitOffset); COPY_NODE_FIELD(limitCount); COPY_NODE_FIELD(lockingClause); + COPY_NODE_FIELD(with_cte_list); COPY_SCALAR_FIELD(op); COPY_SCALAR_FIELD(all); COPY_NODE_FIELD(larg); Index: src/backend/nodes/equalfuncs.c === RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/nodes/equalfuncs.c,v retrieving revision 1.317 diff -p -c -r1.317 equalfuncs.c *** src/backend/nodes/equalfuncs.c 1 Jan 2008 19:45:50 - 1.317 --- src/backend/nodes/equalfuncs.c 27 Jan 2008 07:05:29 - *** _equalSelectStmt(SelectStmt *a, SelectSt *** 821,826 --- 821,827 COMPARE_NODE_FIELD(limitOffset); COMPARE_NODE_FIELD(limitCount); COMPARE_NODE_FIELD(lockingClause); + COMPARE_NODE_FIELD(with_cte_list); COMPARE_SCALAR_FIELD(op); COMPARE_SCALAR_FIELD(all); COMPARE_NODE_FIELD(larg); Index: src/backend/nodes/outfuncs.c === RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/nodes/outfuncs.c,v retrieving revision 1.322 diff -p -c -r1.322 outfuncs.c *** src/backend/nodes/outfuncs.c 9 Jan 2008 08:46:44 - 1.322 --- src/backend/nodes/outfuncs.c 27 Jan 2008 07:05:29 - *** _outSelectStmt(StringInfo str, SelectStm *** 1599,1604 --- 1599,1605 WRITE_NODE_FIELD(limitOffset); WRITE_NODE_FIELD(limitCount); WRITE_NODE_FIELD(lockingClause); + WRITE_NODE_FIELD(with_cte_list); WRITE_ENUM_FIELD(op, SetOperation); WRITE_BOOL_FIELD(all); WRITE_NODE_FIELD(larg); Index: src/backend/parser/analyze.c === RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/parser/analyze.c,v retrieving revision 1.371 diff -p -c -r1.371 analyze.c *** src/backend/parser/analyze.c 1 Jan 2008 19:45:50 - 1.371 --- src/backend/parser/analyze.c 27 Jan 2008 07:23:39 - *** transformSelectStmt(ParseState *pstate, *** 688,693 --- 688,696 /* make FOR UPDATE/FOR SHARE info available to addRangeTableEntry */ pstate->p_locking_clause = stmt->lockingClause; + /* process the WITH clause (pull CTEs into the pstate's ctenamespace) */ + transformWithClause(pstate, stmt->with_cte_list); + /* process the FROM clause */ transformFromClause(pstate, stmt->fromClause); Index: src/backend/parser/gram.y === RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/parser/gram.y,v retrieving revision 2.605 diff -p -c -r2.605 gram.y *** src/backend/parser/gram.y 1 Jan 2008 19:45:50 - 2.605 --- src/backend/parser/gram.y 27 Jan 2008 07:21:28 - *** static List *extractArgTypes(List *param *** 103,109 static SelectStmt *findLeftmostSelect(SelectStmt *node); static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount); static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v); --- 103,110 static SelectStmt *findLeftmostSelect(SelectStmt *node); static void insertSelectOptions(SelectStmt *stmt, List *sortClause, List *lockingClause, ! Node *limitOffset, Node *limitCount, ! List *with_cte_list); static Node *makeSetOp(SetOperation op, bool all, Node *larg, Node *rarg); static Node *doNegate(Node *n, int location); static void doNegateFloat(Value *v);
Re: [PATCHES] sinval contention reduction
Simon Riggs <[EMAIL PROTECTED]> writes: > On Fri, 2008-01-25 at 19:02 -0500, Tom Lane wrote: >> This seems large, complex, and untested (I note in particular a >> guaranteed-to-fail Assert). > Yes, its for discussion. How would you describe such a patch in the > future? I want to be able to differentiate patch status. "Completely untested" might be an appropriate description ... > We only clean the queue if a long run of messages is read by the oldest > message reader, so when stateP->nextMsgNum == segP->minMsgNum && number > of messages read > 25% of queue. So that is only performed by a backend > waking up to find it is behind, such as would happen if a PM signal had > been issued. You still haven't explained why that won't happen in parallel within many backends at once. I really think we need to fix things so that catchup interrupts occur serially instead of all at once. >> Do you have any evidence for performance improvement? > ISTM that it would only be worth testing when we had a rough agreement > that we had found a reasonable approach. Well, what I'd like to do for 8.4 is a considerably more invasive rethink of the signaling logic. I'm not opposed in principle to some simpler stopgap fix for 8.3, but at this late stage of the cycle there would have to be a pretty compelling performance-improvement case for it. So I'm not sure of the point of posting a patch that's not even ready for performance testing. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PATCHES] WIP: variadic function, named params
Hello,
there is one implementation of variadic functions. The base premis is
transformation type:
anyparams -> n x any
anyelements -> n x anyelement
Currently variadic functions can be only in C language. PL/pgSQL
cannot access to array of params. I implented some JSON functions
(based on Bauman's libraries for MySQL). With this extension we are
able to write some sophistic libraries.
What do you thing about this concept? Has any sense continue in this project?
Regards
Pavel Stehule
postgres=# select json_members('aaa',1,'',20);
json_members
---
"aaa":1,"":20
(1 row)
postgres=# select json_members('aaa',1);
json_members
--
"aaa":1
(1 row)
postgres=# select * from fog;
a | b | c
---+--+
| ahoj |
| ahoj | 2008-01-26
1 | | 2008-01-26
(3 rows)
postgres=# select json_object(a,b,c as cc) from fog;
json_object
--
{a:NaN,b:"ahoj",cc:null}
{a:NaN,b:"ahoj",cc:"2008-01-26"}
{a:1,b:null,cc:"2008-01-26"}
(3 rows)
postgres=# select json_object('Pavel' as name, 'Stehule' as surname);
json_object
--
{name:"Pavel",surname:"Stehule"}
(1 row)
postgres=# select json_array(a,b,c) from fog;
json_array
---
[NaN,"ahoj",null]
[NaN,"ahoj","2008-01-26"]
[1,null,"2008-01-26"]
(3 rows)
CREATE OR REPLACE FUNCTION json_array(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
CREATE OR REPLACE FUNCTION json_object(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
CREATE OR REPLACE FUNCTION json_members(anyparams) RETURNS json
AS 'MODULE_PATHNAME'
LANGUAGE C IMMUTABLE;
variadic_functions.diff.gz
Description: GNU Zip compressed data
json.tgz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] sinval contention reduction
On Fri, 2008-01-25 at 19:02 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > Patch to reduce the contention on SInvalLock, as discussed here: > > http://archives.postgresql.org/pgsql-hackers/2007-09/msg00501.php > > and > > http://archives.postgresql.org/pgsql-performance/2008-01/msg00023.php > > > For discussion. > > This seems large, complex, and untested (I note in particular a > guaranteed-to-fail Assert). Yes, its for discussion. How would you describe such a patch in the future? I want to be able to differentiate patch status. There's no point polishing it before the discussion. > I'm also wondering if it will help much, > since unless the system is already in trouble, the normal case will be > that all backends have absorbed all messages and so they'll all see > stateP->nextMsgNum == segP->minMsgNum when they first respond to a > signal. Agreed, that's why its not the only condition. In the patch, the message queue is normally cleaned when an insert gets past 50% of queue length, then again at 56%, 62% and 68%. If that doesn't help then we hit the PM signal at 70% as before. We don't attempt to clean the queue after every round of messages by each backend, which causes huge contention as we know. We only clean the queue if a long run of messages is read by the oldest message reader, so when stateP->nextMsgNum == segP->minMsgNum && number of messages read > 25% of queue. So that is only performed by a backend waking up to find it is behind, such as would happen if a PM signal had been issued. > Do you have any evidence for performance improvement? ISTM that it would only be worth testing when we had a rough agreement that we had found a reasonable approach. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
