Re: [HACKERS] pgsql: Allow SQL-language functions to return the output of an
This isn't the same thing as allowing RETURNING inside subqueries, right? greg On 31 Oct 2008, at 07:37 PM, [EMAIL PROTECTED] (Tom Lane) wrote: Log Message: --- Allow SQL-language functions to return the output of an INSERT/ UPDATE/DELETE RETURNING clause, not just a SELECT as formerly. A side effect of this patch is that when a set-returning SQL function is used in a FROM clause, performance is improved because the output is collected into a tuplestore within the function, rather than using the less efficient value-per-call mechanism. Modified Files: -- pgsql/doc/src/sgml: xfunc.sgml (r1.132 - r1.133) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/xfunc.sgml?r1=1.132r2=1.133 ) pgsql/src/backend/executor: execQual.c (r1.235 - r1.236) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/execQual.c?r1=1.235r2=1.236 ) functions.c (r1.126 - r1.127) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/executor/functions.c?r1=1.126r2=1.127 ) pgsql/src/backend/tcop: dest.c (r1.72 - r1.73) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/dest.c?r1=1.72r2=1.73 ) pgsql/src/backend/utils/fmgr: README (r1.15 - r1.16) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/fmgr/README?r1=1.15r2=1.16 ) pgsql/src/include/executor: functions.h (r1.31 - r1.32) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/executor/functions.h?r1=1.31r2=1.32 ) pgsql/src/include/nodes: execnodes.h (r1.193 - r1.194) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/nodes/execnodes.h?r1=1.193r2=1.194 ) pgsql/src/include/tcop: dest.h (r1.54 - r1.55) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/tcop/dest.h?r1=1.54r2=1.55 ) pgsql/src/test/regress/expected: rangefuncs.out (r1.19 - r1.20) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/expected/rangefuncs.out?r1=1.19r2=1.20 ) pgsql/src/test/regress/output: create_function_1.source (r1.33 - r1.34) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/output/create_function_1.source?r1=1.33r2=1.34 ) pgsql/src/test/regress/sql: rangefuncs.sql (r1.8 - r1.9) (http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/test/regress/sql/rangefuncs.sql?r1=1.8r2=1.9 ) -- Sent via pgsql-committers mailing list ([EMAIL PROTECTED] ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enable pl/python to return records based on multiple OUT params
On Sat, 2008-11-01 at 06:13 +0200, Hannu Krosing wrote:
attached is a patch which enables plpython to recognize function with
multiple OUT params as returning a record
Overrides previous patch.
Fixed some bugs, added regression tests.
This version is quite rough, though passes tests here.
I will clean it up more during commitfest.
probably still more things to do
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
? plpython/.deps
? plpython/gmon.out
? plpython/results
Index: plpython/plpython.c
===
RCS file: /projects/cvsroot/pgsql/src/pl/plpython/plpython.c,v
retrieving revision 1.114
diff -c -r1.114 plpython.c
*** plpython/plpython.c 11 Oct 2008 00:09:33 - 1.114
--- plpython/plpython.c 1 Nov 2008 12:48:44 -
***
*** 151,157
PLyTypeInfo result; /* also used to store info for trigger tuple
* type */
bool is_setof; /* true, if procedure returns result set */
! PyObject *setof; /* contents of result set. */
char **argnames; /* Argument names */
PLyTypeInfo args[FUNC_MAX_ARGS];
int nargs;
--- 151,157
PLyTypeInfo result; /* also used to store info for trigger tuple
* type */
bool is_setof; /* true, if procedure returns result set */
! PyObject *setiterator; /* contents of result set. */
char **argnames; /* Argument names */
PLyTypeInfo args[FUNC_MAX_ARGS];
int nargs;
***
*** 160,165
--- 160,167
PyObject *globals; /* data saved across calls, global scope */
PyObject *me;/* PyCObject containing pointer to this
* PLyProcedure */
+ MemoryContext ctx;
+ AttInMetadata *att_info_metadata; /* for returning composite types */
} PLyProcedure;
***
*** 237,243
static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo,
Oid tgreloid);
! static PLyProcedure *PLy_procedure_create(HeapTuple procTup, Oid tgreloid,
char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
--- 239,245
static PLyProcedure *PLy_procedure_get(FunctionCallInfo fcinfo,
Oid tgreloid);
! static PLyProcedure *PLy_procedure_create(FunctionCallInfo fcinfo,HeapTuple procTup, Oid tgreloid,
char *key);
static void PLy_procedure_compile(PLyProcedure *, const char *);
***
*** 261,269
static PyObject *PLyLong_FromString(const char *);
static PyObject *PLyString_FromString(const char *);
! static HeapTuple PLyMapping_ToTuple(PLyTypeInfo *, PyObject *);
! static HeapTuple PLySequence_ToTuple(PLyTypeInfo *, PyObject *);
! static HeapTuple PLyObject_ToTuple(PLyTypeInfo *, PyObject *);
/*
* Currently active plpython function
--- 263,271
static PyObject *PLyLong_FromString(const char *);
static PyObject *PLyString_FromString(const char *);
! static HeapTuple PLyMapping_ToTuple(AttInMetadata *, PyObject *);
! static HeapTuple PLySequence_ToTuple(AttInMetadata *, PyObject *);
! static HeapTuple PLyObject_ToTuple(AttInMetadata *, PyObject *);
/*
* Currently active plpython function
***
*** 783,789
PG_TRY();
{
! if (!proc-is_setof || proc-setof == NULL)
{
/* Simple type returning function or first time for SETOF function */
plargs = PLy_function_build_args(fcinfo, proc);
--- 785,791
PG_TRY();
{
! if (!proc-is_setof || proc-setiterator == NULL)
{
/* Simple type returning function or first time for SETOF function */
plargs = PLy_function_build_args(fcinfo, proc);
***
*** 813,819
bool has_error = false;
ReturnSetInfo *rsi = (ReturnSetInfo *) fcinfo-resultinfo;
! if (proc-setof == NULL)
{
/* first time -- do checks and setup */
if (!rsi || !IsA(rsi, ReturnSetInfo) ||
--- 815,821
bool has_error = false;
ReturnSetInfo *rsi = (ReturnSetInfo *) fcinfo-resultinfo;
! if (proc-setiterator == NULL)
{
/* first time -- do checks and setup */
if (!rsi || !IsA(rsi, ReturnSetInfo) ||
***
*** 826,844
rsi-returnMode = SFRM_ValuePerCall;
/* Make iterator out of returned object */
! proc-setof = PyObject_GetIter(plrv);
Py_DECREF(plrv);
plrv = NULL;
! if (proc-setof == NULL)
ereport(ERROR,
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg(returned object cannot be iterated),
! errdetail(SETOF must be returned as iterable object)));
}
/* Fetch next from iterator */
! plrv = PyIter_Next(proc-setof);
if (plrv)
rsi-isDone = ExprMultipleResult;
else
--- 828,846
rsi-returnMode = SFRM_ValuePerCall;
/* Make iterator out of returned object */
! proc-setiterator = PyObject_GetIter(plrv);
[HACKERS] Re: [COMMITTERS] pgsql: Unite ReadBufferWithFork, ReadBufferWithStrategy, and
On Sat, 2008-11-01 at 15:18 +0200, Heikki Linnakangas wrote: Simon Riggs wrote: On Fri, 2008-10-31 at 15:05 +, Heikki Linnakangas wrote: Log Message: --- Unite ReadBufferWithFork, ReadBufferWithStrategy, and ZeroOrReadBuffer functions into one ReadBufferExtended function, that takes the strategy and mode as argument. There's three modes, RBM_NORMAL which is the default used by plain ReadBuffer(), RBM_ZERO, which replaces ZeroOrReadBuffer, and a new mode RBM_ZERO_ON_ERROR, which allows callers to read corrupt pages without throwing an error. The FSM needs the new mode to recover from corrupt pages, which could happend if we crash after extending an FSM file, and the new page is torn. I thought you were adding the read buffer only if in cache option also? No, but if it's needed, it should now fit well into the infrastructure, as a new ReadBuffer mode. Not sure how this helps me; maybe it wasn't supposed to? I need to implement XLogReadBufferExtended to take a cleanup lock. It seems wrong to make that a ReadBufferMode, since the option refers to what happens after we do ReadBuffer and especially because we need to do RBM_ZERO and Cleanup mode at same time. It would be much better to have ReadBufferExtended take a lockmode argument also. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BufferAccessStrategy for bulk insert
Can you test whether using the buffer access strategy is a win or a
loss? Most of that gain is probably coming from the reduction in
pinning.
Patch resnapped to HEAD, with straightforward adjustments to
compensate for Heikki's changes to the ReadBuffer interface. See
attached.
New testing results, now with and without BAS:
--TRUNK--
Time: 17945.523 ms
Time: 18682.172 ms
Time: 17047.841 ms
Time: 16344.442 ms
Time: 18727.417 ms
--PATCHED--
Time: 13323.772 ms
Time: 13869.724 ms
Time: 14043.666 ms
Time: 13934.132 ms
Time: 13193.702 ms
--PATCHED with BAS disabled--
Time: 14460.432 ms
Time: 14745.206 ms
Time: 14345.973 ms
Time: 14601.448 ms
Time: 16535.167 ms
I'm not sure why the BAS seemed to be slowing things down before.
Maybe it's different if we're copying into a pre-existing table, so
that WAL is enabled? Or it could have just been a fluke - the numbers
were close. I'll try to run some additional tests if time permits.
...Robert
Index: src/backend/access/heap/heapam.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.268
diff -c -r1.268 heapam.c
*** src/backend/access/heap/heapam.c 31 Oct 2008 19:40:26 - 1.268
--- src/backend/access/heap/heapam.c 1 Nov 2008 17:17:16 -
***
*** 1798,1803
--- 1798,1827
}
}
+ /*
+ * GetBulkInsertState - set up for a bulk insert
+ */
+ BulkInsertState
+ GetBulkInsertState(void)
+ {
+ BulkInsertState bistate;
+
+ bistate = palloc(sizeof(struct BulkInsertStateData));
+ bistate-strategy = GetAccessStrategy(BAS_BULKWRITE);
+ bistate-last_pin = InvalidBuffer;
+ return bistate;
+ }
+
+ /*
+ * FreeBulkInsertState - clean up after finishing a bulk insert
+ */
+ void
+ FreeBulkInsertState(BulkInsertState bistate)
+ {
+ if (bistate-last_pin != InvalidBuffer)
+ ReleaseBuffer(bistate-last_pin);
+ FreeAccessStrategy(bistate-strategy);
+ }
/*
* heap_insert - insert tuple into a heap
***
*** 1805,1821
* The new tuple is stamped with current transaction ID and the specified
* command ID.
*
! * If use_wal is false, the new tuple is not logged in WAL, even for a
! * non-temp relation. Safe usage of this behavior requires that we arrange
! * that all new tuples go into new pages not containing any tuples from other
! * transactions, and that the relation gets fsync'd before commit.
* (See also heap_sync() comments)
*
! * use_fsm is passed directly to RelationGetBufferForTuple, which see for
! * more info.
*
! * Note that use_wal and use_fsm will be applied when inserting into the
! * heap's TOAST table, too, if the tuple requires any out-of-line data.
*
* The return value is the OID assigned to the tuple (either here or by the
* caller), or InvalidOid if no OID. The header fields of *tup are updated
--- 1829,1846
* The new tuple is stamped with current transaction ID and the specified
* command ID.
*
! * If the HEAP_INSERT_SKIP_WAL option is supplied, the new tuple is not logged
! * in WAL, even for a non-temp relation. Safe usage of this behavior requires
! * that we arrange that all new tuples go into new pages not containing any
! * tuples from other transactions, and that the relation gets fsync'd before
! * commit.
* (See also heap_sync() comments)
*
! * The HEAP_INSERT_SKIP_FSM option is passed directly to
! * RelationGetBufferForTuple, which see for more info.
*
! * Note that options will be applied when inserting into the heap's TOAST
! * table, too, if the tuple requires any out-of-line data.
*
* The return value is the OID assigned to the tuple (either here or by the
* caller), or InvalidOid if no OID. The header fields of *tup are updated
***
*** 1825,1831
*/
Oid
heap_insert(Relation relation, HeapTuple tup, CommandId cid,
! bool use_wal, bool use_fsm)
{
TransactionId xid = GetCurrentTransactionId();
HeapTuple heaptup;
--- 1850,1856
*/
Oid
heap_insert(Relation relation, HeapTuple tup, CommandId cid,
! int options, BulkInsertState bistate)
{
TransactionId xid = GetCurrentTransactionId();
HeapTuple heaptup;
***
*** 1877,1890
heaptup = tup;
}
else if (HeapTupleHasExternal(tup) || tup-t_len TOAST_TUPLE_THRESHOLD)
! heaptup = toast_insert_or_update(relation, tup, NULL,
! use_wal, use_fsm);
else
heaptup = tup;
/* Find buffer to insert this tuple into */
buffer = RelationGetBufferForTuple(relation, heaptup-t_len,
! InvalidBuffer, use_fsm);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
--- 1902,1914
heaptup = tup;
}
else if (HeapTupleHasExternal(tup) || tup-t_len TOAST_TUPLE_THRESHOLD)
! heaptup = toast_insert_or_update(relation, tup, NULL, options);
else
heaptup = tup;
/* Find buffer to insert this tuple into */
Re: [HACKERS] BufferAccessStrategy for bulk insert
On Sat, 2008-11-01 at 13:23 -0400, Robert Haas wrote: Can you test whether using the buffer access strategy is a win or a loss? Most of that gain is probably coming from the reduction in pinning. --PATCHED-- Time: 13869.724 ms (median) --PATCHED with BAS disabled-- Time: 14460.432 ms (median with outlier removed) That seems a conclusive argument in favour. Small additional performance gain. plus generally beneficial behaviour for concurrent loads. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgsql: Allow SQL-language functions to return the output of an
Greg Stark [EMAIL PROTECTED] writes: This isn't the same thing as allowing RETURNING inside subqueries, right? Right. You could fairly easily get that now by wrapping your RETURNING query into a SQL function ... but I'm not sure that we want to advertise that heavily, because the question of just when the subquery gets executed still isn't resolved with any degree of precision. It might be that the current behavior is fine, but I'm not feeling we should swear to it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Well done, Hackers
Unofficially, well done to everybody that submitted patches in time for Commit Fest. The queue has grown significantly over last few days and it's clear many people have been working very hard right up to the deadline. Depending upon how you count it there are around 5 multi-month mega patches, plus more than 50 other features. Very impressive. I count nearly a dozen new names on the list. Congratulations to you. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Simon Riggs [EMAIL PROTECTED] writes: Unofficially, well done to everybody that submitted patches in time for Commit Fest. The queue has grown significantly over last few days and it's clear many people have been working very hard right up to the deadline. Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If we actually manage to ship 8.4 within six months, *that* will be well done. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please make sure your patches are on the wiki page
Stephen, what is the status of your efforts? The latest one I could found is the colprivs_wip.20080902.diff.gz. Do you have any updated one? Snowman told me this week that he was working hard on it -- he declined to be a reviewer for that reason. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Buffer pool statistics in Explain Analyze
Hi all, Here is a patch that adds buffer pool statistics to the explain analyze output revealing the number of buffer pages hit at each and every execution step. It uses counters from storage/buffer/bufmgr.c (I believe all that counters are relevant for investigation of query performance). Here is the sample output: create table test as select i/10 as a, round(random()*1) as b from generate_series(1,10) as x(i) order by 1; create index ix_a on test(a); create index ix_b on test(b); vacuum analyze test; explain analyze select count(*) from test x, test y where a.b = 5 and y.b = x.b; Aggregate (cost=413.88..413.89 rows=1 width=0) (actual time=1.380..1.382 rows=1 loops=1 read_shared=119(111) read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0) - Nested Loop (cost=4.35..413.59 rows=118 width=0) (actual time=0.088..1.230 rows=96 loops=1 read_shared=119(111) read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0) - Index Scan using ix_a on test x (cost=0.00..8.44 rows=10 width=8) (actual time=0.010..0.028 rows=10 loops=1 read_shared=3(3) read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0) Index Cond: (a = 5) - Bitmap Heap Scan on test y (cost=4.35..40.38 rows=11 width=8) (actual time=0.034..0.080 rows=10 loops=10 read_shared=116(108) read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0) Recheck Cond: (y.b = x.b) - Bitmap Index Scan on ix_b (cost=0.00..4.34 rows=11 width=0) (actual time=0.028..0.028 rows=10 loops=10 read_shared=20(12) read_local=0(0) flush=0 local_flush=0 file_read=0 file_write=0) Index Cond: (y.b = x.b) Total runtime: 1.438 ms read_shared=116(108) for Bitmap Heap Scan means the operation fetched 116 pages into shared buffers and 108 of those 116 were buffer hits. Sincerely yours, Vladimir Sitnikov buffer_stats_in_explain_analyze.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
On Sat, 2008-11-01 at 14:22 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Unofficially, well done to everybody that submitted patches in time for Commit Fest. The queue has grown significantly over last few days and it's clear many people have been working very hard right up to the deadline. Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If we actually manage to ship 8.4 within six months, *that* will be well done. Work happens in stages. A lot of people have obviously worked very hard and I wanted to make sure their efforts do not go unnoticed. I don't see contributions and effort as a bad thing for Postgres. And CommitFests have helped in reducing the volume of patches that appear at the last deadline, just imagine what it would be like otherwise. If there was a problem anywhere in sight it would be that the development window closes for 6 months of the year, forcing developers to cram their contributions into a tight window. Patches that take many months to plan and write are simply more likely to appear at the last Commitfest of the year. I'm more than aware that much of the work from here rests on your shoulders, and I understand how you must feel. But let's give credit to the people that have worked hard to get to here and then lets give *more* credit to all the people that contribute from here. We can say thank you to each in their turn. If we can positively motivate people we will be able to spread the load rather than have the load fall on you. Believe me, none of us enjoy watching you struggle (capably) with the huge workload and responsibility. I'll do more than I've done in the past to help, both with my own work and others. Thanks in advance. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Tom, Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If you don't want patches coming at given deadlines then yes the commit fest idea is a bad idea altogether. But what is the real issue? - The core team is too small to absorb contributions or the development is not distributed enough? - There are not enough guidelines or requirements for a patch to make it to the commit fest? - There is not enough QA manpower/test cases to test the patches efficiently? - Lack of roadmap? Hard to guess what contributions are going to come? What are your views on how the process could be improved? If the commit fest does not work, what should we do instead? I think that complaining after volunteer contributors is the best way to not get any more contributions and have nice empty commit fests in the future. Maybe that's the way to go to solve the issue at hand! ;-) manu -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: [EMAIL PROTECTED] Skype: emmanuel_cecchet -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gram.y = preproc.y
I finally have a working version. Please have a look at the attached files including a changed version of gram.y and and automatically created version of preproc.y. To recreate just run awk -f parse.awk gram.y preproc.y. Is it okay to change gram.y this way? It does run the regression suite successfully. I'd like to do some more tests on the ecpg side, but this version also runs the regression suite successfully, well sort of. There are quite a lot of cosmetic differences, mostly whitespaces and some different writings of commit/begin/..., but no functional difference any more. To test you need an up-to-date HEAD because ecpglib didn't like an additional blank. Comments/improvements/bug reports welcome. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PG_PAGE_LAYOUT_VERSION 5 - time for change
Heikki Linnakangas [EMAIL PROTECTED] writes: Hmm, you're right. I think it can be made to work by storing the *end* offset of each chunk. To find the chunk containing offset X, search for the first chunk with end_offset X. Yeah, that seems like it would work, and it would disentangle us altogether from needing a hard-wired chunk size. The only downside is that it'd be a pain to convert in-place. However, if we are also going to add identifying information to the toast chunks (like the owning column's number or datatype), then you could tell whether a toast chunk had been converted by checking t_natts. So in principle a toast table could be converted a page at a time. If the converted data didn't fit you could push one of the chunks out to some new page of the file. On the whole I like this a lot better than Zdenek's original proposal http://archives.postgresql.org/pgsql-hackers/2008-10/msg00556.php which didn't seem to me to solve much of anything. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If we actually manage to ship 8.4 within six months, *that* will be well done. It looks to me like there are at least half a dozen patches submitted in the last week that are pretty half-baked and fall into the category of Let's submit something before the deadline for CommitFest, in the hopes of being allowed to finish it later. This completely shafts the process in two ways. First, anyone who gets assigned to review one of those patches might as well not bother, since the author is probably still working frantically on the patch anyway and will likely find and fix a lot of the issues that any review might turn up. Second, since the authors are frantically working on their own patches, they will have no (or diminished) time to review other people's patches, which is the whole point of CommitFest. It seems to me that Work In Progress needs to mean I need some feedback this CommitFest so I can finish it for the NEXT CommitFest and not I'd like to make an end-run around the submission deadline. On the other hand, the number of patches that fall into this category is actually not that large as a percentage of the total. A lot of the larger features have been under development for months and have been extensively discussed on -hackers or were submitted for the previous CommitFest. If you could somehow wave your magic wand and get all of those committed or rejected, I doubt the remaining list would be terribly intimidating. I have five patches in for this commitfest but I bet you (tgl) could deal with all of them in an afternoon without breaking a sweat. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Distinct types
On Friday 31 October 2008 17:01:05 Kevin Grittner wrote: I notice there's no documentation yet. Do you have a reference to a good description of the feature, consistent with your implementation? Documentation will need to be added, of course, but there isn't really a lot to describe: you create the type and use it. If not, a couple questions: (1) Can you compare a literal of the base type? No, unless you create additional casts or operators. (2) Can you explicitly cast to the base type? There is an implicit AS ASSIGNMENT cast between the base type and the distinct type in each direction. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Distinct types
Peter Eisentraut [EMAIL PROTECTED] writes: On Friday 31 October 2008 17:01:05 Kevin Grittner wrote: (1) Can you compare a literal of the base type? No, unless you create additional casts or operators. (2) Can you explicitly cast to the base type? There is an implicit AS ASSIGNMENT cast between the base type and the distinct type in each direction. Hmm ... so out-of-the-box, a distinct type would have no applicable functions/operators whatsoever. You couldn't even create an index on it. This seems a bit too impoverished to be useful. And given the known gotchas with creating functions/operators on domains, I'm not convinced someone could fix the problem by creating specialized functions for their distinct type. Even if they could fix it, having to set up a custom btree opclass in order to have an index seems to take this out of the easy to use category. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements v2
Hello, I have two concerns regarding the patch: A) I am not sure if it is good to have a single contention point (pgss-lock = LWLockAssign()). I guess that would impact scalability, especially on a multi-cpu systems. I guess the real solution will come when PostgreSQL have a pool for sql statements, hovewer it could make sense to split pgss-lock into several ones to reduce contention on it. B) I really do not like the idea of ResetBufferUsage. I do vote for eliminating ResetBufferUsage from the sources (even from the core sources) The reason is as follows: 1) No one really tries to reset current timestamp counter. Why do we reset buffer usage every now and then? 2) As new call sites of ResetBufferUsage appear it becomes more likely to fetch wrong statistics from that counters due to accidental reset. 3) When it comes to fetch buffer usage, one might use the same approach as with timings: calculate the difference between two measurements. I do not believe it is noticeably slower than reset+measure. I wish PostgreSQL had some kind of pg_session_statistics view that reports resource usage statistics for each session. For instance, it could expose buffer usage to the client, so it could get more details on resource usage. For instance, I would like to see a new tab in pgAdmin that shows total number of buffer gets, number of WAL records created, number of rows sorted and similar information after query finishes (even in plain execute mode). The second application of that statistics could be server health monitoring: provided there is an interface for ongoing integral statistics, one could create a job that takes snapshots, computes the difference and plots it on a graph. Sincerely, Vladimir Sitnikov
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1168)
KaiGai Kohei wrote: I've updated my patches, it contains a few bugfixes. [1/6] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1168.patch [2/6] http://sepgsql.googlecode.com/files/sepostgresql-pg_dump-8.4devel-3-r1168.patch [3/6] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1168.patch [4/6] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1168.patch [5/6] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1168.patch [6/6] http://sepgsql.googlecode.com/files/sepostgresql-row_acl-8.4devel-3-r1168.patch The comprehensive documentation for SE-PostgreSQL is here: http://wiki.postgresql.org/wiki/SEPostgreSQL (it is now under reworking.) List of updates: - Patches are rebased to the latest CVS HEAD. - bugfix: permission checks are ignored for per statement trigger functions - bugfix: per-statement trigger function ignored trusted function configuration - bugfix: not a proper permission check on lo_export(xxx, '/dev/null') Request for Comments: - The 4th patch is actually needed? It can be replaced by wiki page. - Do you think anything remained towards the final CommitFest? - Do you have any reviewing comment? Most of patches are unchanged from the previous vesion. If you can comment anything, I can fix them without waiting for the final commit fest. I just looked over the patch. This new version with row-level SQL security has certainly reduced the SE-Linux-specific part, which is good. It was interesting how you implemented SQL-level column-level permissions: CREATE TABLE customer ( cid integer primary key, cname varchar(32), credit varchar(32) SECURITY_CONTEXT = 'system_u:object_r:sepgsql_secret_table_t' ); I am unclear how that will behave with the column-level permissions patch someone is working on. I am wondering if your approach is clearer than the other patch because it gives a consistent right policy for rows and columns. I was wondering why you mention the NSA (U.S. National Security Agency) in the patch? +# NSA SELinux support The size of the patch is still larger but I don't see any way to reduce it: 1275 sepostgresql-docs-8.4devel-3-r1168.patch 625 sepostgresql-pg_dump-8.4devel-3-r1168.patch 829 sepostgresql-policy-8.4devel-3-r1168.patch 1736 sepostgresql-row_acl-8.4devel-3-r1168.patch 10847 sepostgresql-sepgsql-8.4devel-3-r1168.patch 1567 sepostgresql-tests-8.4devel-3-r1168.patch 16879 total -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed Patch to Improve Performance of Multi-Batch Hash Join for Skewed Data Sets
On Mon, Oct 20, 2008 at 4:42 PM, Lawrence, Ramon [EMAIL PROTECTED] wrote: We propose a patch that improves hybrid hash join's performance for large multi-batch joins where the probe relation has skew. Project name: Histojoin Patch file: histojoin_v1.patch This patch implements the Histojoin join algorithm as an optional feature added to the standard Hybrid Hash Join (HHJ). A flag is used to enable or disable the Histojoin features. When Histojoin is disabled, HHJ acts as normal. The Histojoin features allow HHJ to use PostgreSQL's statistics to do skew aware partitioning. The basic idea is to keep build relation tuples in a small in-memory hash table that have join values that are frequently occurring in the probe relation. This improves performance of HHJ when multiple batches are used by 10% to 50% for skewed data sets. The performance improvements of this patch can be seen in the paper (pages 25-30) at: http://people.ok.ubc.ca/rlawrenc/histojoin2.pdf All generators and materials needed to verify these results can be provided. This is a patch against the HEAD of the repository. This patch does not contain platform specific code. It compiles and has been tested on our machines in both Windows (MSVC++) and Linux (GCC). Currently the Histojoin feature is enabled by default and is used whenever HHJ is used and there are Most Common Value (MCV) statistics available on the probe side base relation of the join. To disable this feature simply set the enable_hashjoin_usestatmcvs flag to off in the database configuration file or at run time with the 'set' command. One potential improvement not included in the patch is that Most Common Value (MCV) statistics are only determined when the probe relation is produced by a scan operator. There is a benefit to using MCVs even when the probe relation is not a base scan, but we were unable to determine how to find statistics from a base relation after other operators are performed. This patch was created by Bryce Cutt as part of his work on his M.Sc. thesis. -- Dr. Ramon Lawrence Assistant Professor, Department of Computer Science, University of British Columbia Okanagan E-mail: [EMAIL PROTECTED] I'm interested in trying to review this patch. Having not done patch review before, I can't exactly promise grand results, but if you could provide me with the data to check your results? In the meantime I'll go read the paper. - Josh / eggyknap -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
On Sat, 2008-11-01 at 16:21 -0400, Robert Haas wrote: Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If we actually manage to ship 8.4 within six months, *that* will be well done. It looks to me like there are at least half a dozen patches submitted in the last week that are pretty half-baked Robert, Without review, how can we determine the true state of a patch? I would guess that you haven't done a review of all of them yourself to determine that. Your characterisation of other developers seems negative to me and I really do hope unfair. Nobody I know has submitted anything half-baked, but I haven't reviewed any patches yet. Review is about asking for help from your peers, not an admission of crap software. We're all seeking to learn more and be better, I hope. If this sounds like a flame, its not. I just want to be positive about the efforts of so many people who are all working together as a team. There will be some hard, honest reviews and some patches may not make it, so its a good time to say thanks for trying so hard. For myself, I've submitted a patch touching more than 60 files. Am I nervous I got something wrong? Damn right. Is there some horror lurking in there that I'm not saying, but frantically fixing now? No way. That's not the spirit. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Emmanuel Cecchet wrote: Tom, Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If you don't want patches coming at given deadlines then yes the commit fest idea is a bad idea altogether. But what is the real issue? - The core team is too small to absorb contributions or the development is not distributed enough? The list of people experienced enough to review complex patches has not grown as fast as the number of people submitting complex patches. That will probably even out over time, but it might take years. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Simon Riggs wrote: On Sat, 2008-11-01 at 16:21 -0400, Robert Haas wrote: Well done? It seems to me that we are right where we hoped not to be, ie with a ton of barely-completed (if not self-admitted WIP) patches dropped on us immediately before feature freeze. Today the commit fest idea is looking like a failure. If we actually manage to ship 8.4 within six months, *that* will be well done. It looks to me like there are at least half a dozen patches submitted in the last week that are pretty half-baked Robert, Without review, how can we determine the true state of a patch? I would guess that you haven't done a review of all of them yourself to determine that. I think a complex patch that hasn't been discussed on the hackers list recently, and that has deficiencies reported by the author can be safely described as a work-in-progress that needs feedback, rather than committed for 8.4. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Windowing Function Patch Review - Performance Comparison.
All, This is my first patch review for PostgreSQL. I did submit a patch last commit fest (Boyer-Moore) so I feel I should review one this commit fest. I'm quite new to PostgreSQL so please don't rely on me totally. I'll do my best. Heikki is also reviewing this patch which makes me feel better. My aim is to get the author has much feed back as quickly as possible. For this reason I'm going to be breaking down my reviews into the following topics. 1. Does patch apply cleanly? 2. Any compiler warnings? 3. Do the results follow the SQL standard? 4. Performance Comparison, does it perform better than alternate ways of doing things. Self joins, sub queries etc. 5. Performance, no comparison. How does it perform with larger tables? Things I probably won't attempt to review: Source code; best practises, making use of existing APIs etc. I'd rather leave that for Heikki and possibly others that join in reviewing this patch. It's not that I'm too lazy, just that I don't feel that I know the source well enough. Plus it's a complex patch. Really I should follow my list in order but I'm going to do number 4 first in order to get some quick feedback to the author. I've created some real world tests where windowing functions will be useful. I created some tables then populated with data. I then wrote 2 queries; 1 to make use of windowing functions, the other that uses a method without windowing functions. Test Results: TestNormal Windowing UOM Increase % Test 1 498.00 578.00 Trans/Sec 16.06% Test 2 336.00 481.00 Trans/Sec 43.15% Test 3 1.308.45Trans/Sec 550.00% Test 4 424.00 629.00 Trans/Sec 48.35% Test 5 8.8931052.69Trans/Hour 349114.85% Test 6 253.00 305.00 Trans/Sec 20.55% (Please see attached document for the actual tests) Note: The above results will much depend on the set of data. Most of my tests use a very small volume of data. Test 3 and 5 use more data that the other tests. It's quite obvious that the more data there is in my tests the bigger the margin between the two methods becomes. I originally ran test 3 with 4 rows to simulate a large marathon but the normal query was going to take hours... I reduced the rows to 1. Obervations: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan. Is there any reason for this? Perhaps I'm missing something. Hitoshi, can you take a look at this? Tests: Please see attached file. Perhaps there were more efficient ways for certain queries, I just couldn't think of them... Please let me know if you feel I should be conducting the review in another way. David. -- Test 1 -- Highest Salary Per Department -- -- Notes: Get the name, department and salary of highest paid department --member for each department. If the two highest paid employees --in the department get the same salary show the one with the --lowest id. create table employees ( id INT primary key, name varchar(30) not null, department varchar(30) not null, salary int not null, check (salary = 0) ); insert into employees values(1,'Jeff','IT',1); insert into employees values(2,'Sam','IT',12000); insert into employees values(3,'Richard','Manager',3); insert into employees values(4,'Ian','Manager',2); insert into employees values(5,'John','IT',6); insert into employees values(6,'Matthew','Director',6); VACUUM ANALYZE employees; -- Normal way. SELECT name,department,salary FROM employees AS e WHERE id = (SELECT id FROM employees WHERE department = e.department ORDER BY salary DESC,id ASC LIMIT 1); -- above query = 498 tps -- With windowing functions. SELECT name,department,salary FROM (SELECT name, department, salary, row_number() OVER (PARTITION BY department ORDER BY salary DESC,id ASC) AS num FROM employees ) AS t WHERE num = 1; -- above query = 578 tps -- Test 2 -- Split times problem -- -- Notes: Find the interval of time between 1 timestamp and the previous -- timestamp. The previous timestamp being the one with the next lowest id -- column value. If there is no previous timestamp show the value NULL. CREATE TABLE tstest ( id SERIAL NOT NULL PRIMARY KEY, timestamp TIMESTAMP NOT NULL ); INSERT INTO tstest (timestamp) VALUES(NOW()); INSERT INTO tstest (timestamp) SELECT MAX(timestamp) + ((random() * 100)::TEXT || ' sec ')::interval FROM tstest; INSERT INTO tstest (timestamp) SELECT MAX(timestamp) + ((random() * 100)::TEXT || ' sec ')::interval FROM tstest; INSERT INTO tstest (timestamp) SELECT
Re: [HACKERS] Well done, Hackers
On Sat, Nov 01, 2008 at 04:21:30PM -0400, Robert Haas wrote: It looks to me like there are at least half a dozen patches submitted in the last week that are pretty half-baked and fall into the category of Let's submit something before the deadline for CommitFest, in the hopes of being allowed to finish it later. Supposing this is true, one way to prevent that in future commitfest-driven releases is to send them back as not ready this time, and tell them they'll be 1st in line in the next go-round. It's worth remebering that this is the first release using the commitfest model, so there will be things to learn from the 1.0 attempt. A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1168)
Bruce Momjian wrote: KaiGai Kohei wrote: I've updated my patches, it contains a few bugfixes. I was wondering why you mention the NSA (U.S. National Security Agency) in the patch? NSA is who create SELinux originally IIRC. Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.
Here is another way to solve big marathon without window functions (and
many other kinds of windowing queries, especially those that do not
specify rows preceeding etc.).
It could be considered as a very dirty hack, however it could give you an
insight on the performance of the windowed query with indexscan instead of
seqscan.
create function var_set (text,text) returns text
as
'
select set_config (''public.''||$2||pg_backend_pid(), $1, false);
' LANGUAGE 'sql';
create function var_get (text) returns text
as
'
select current_setting(''public.''||$1||pg_backend_pid());
' LANGUAGE 'sql';
create operator (procedure = var_set, leftarg = text, rightarg = text);
create operator (procedure = var_get, rightarg = text);
-- init values
select '''prev_time', '0''dense_rank';
-- marathon query
select *
from (
select (((case when time::text = 'prev_time' then *0* else *1*
end)+('dense_rank')::int4)::text'dense_rank')::int4 as position,
runnerid, time
from big_marathon
order by time
) results
where position=*2*
Best regards,
Vladimir Sitnikov
Re: [HACKERS] Well done, Hackers
Without review, how can we determine the true state of a patch? I would guess that you haven't done a review of all of them yourself to determine that. No, I'm talking about the comments that were made when submitting the patch. A few people said things along the lines of this isn't really finished or this has major bugs. Your characterisation of other developers seems negative to me and I really do hope unfair. Nobody I know has submitted anything half-baked, but I haven't reviewed any patches yet. Review is about asking for help from your peers, not an admission of crap software. We're all seeking to learn more and be better, I hope. Sorry. I don't mean to be negative, and certainly not about the developers. I'm actually quite impressed by the number of people submitting patches, and I'm really looking forward to some of the proposed new features. My point is just that about 10% of them sound like they aren't actually done. And as I said in my previous email, I don't even think that's the main issue. For myself, I've submitted a patch touching more than 60 files. Am I nervous I got something wrong? Damn right. Is there some horror lurking in there that I'm not saying, but frantically fixing now? No way. That's not the spirit. I wasn't talking about your patches. :-) ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Well done, Hackers
Supposing this is true, one way to prevent that in future commitfest-driven releases is to send them back as not ready this time, and tell them they'll be 1st in line in the next go-round. Agreed! But I'll be as happy as anyone if it doesn't come to that. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Any reason to have heap_(de)formtuple?
Kris Jurka [EMAIL PROTECTED] writes: On Thu, 23 Oct 2008, Kris Jurka wrote: The problem with trying to deprecate it is that the vast majority of the backend is still using the old interfaces, so people looking for inspiration for their external modules will likely end up using the old interface. Like Alvaro I started this conversion a while ago, got bored, and forgot about it. If people do want this conversion done while keeping the old interface around, I can track down that patch, update it and finish it up for the next CommitFest. Here's a patch that changes everything over to the the new API and implements the old API by calling the new API. Applied with small corrections (I caught a couple of mistakes :-(). I notice that the SPI API is still largely dependent on the 'n'/' ' convention for null flags. Now that there are not so many examples of that in the core code, I think this poses a threat of serious confusion for newbie writers of add-on modules. Does anyone want to look at cleaning that up? I suppose we'd have to do it in much the same way, adding new parallel functions and deprecating the old ones. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Simple postgresql.conf wizard
One of the long-terms goals I'm working toward is wrapping a wizard
interface around the tuning guidelines described by
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server now that
those have gone through a fair amount of peer review. Attached is a first
simple version of such a wizard, implemented in Python. Right now what it
does is look the amount of memory in your system and adjust shared_buffers
and effective_cache_size. So if we started with this stock configuration:
shared_buffers = 32MB # min 128kB or
#effective_cache_size = 128MB
And run it like this (from my system with 8GB of RAM):
./pg-generate-conf /home/gsmith/data/guc/postgresql.conf
You'd get a new file with these lines in it printed to standard out:
# shared_buffers = 32MB # min 128kB or
#effective_cache_size = 128MB
shared_buffers 1931MB # pg_generate_conf wizard 2008-11-01
effective_cache_size 5793MB # pg_generate_conf wizard 2008-11-01
While I've got a pretty clear vision for what I'm doing with this next and
will kick off a pgfoundry project real soon, I wanted to throw this out as
a WIP for feedback at this point. I was ultimately hoping to one day have
something like this shipped as a contrib/ module to address the constant
requests for such a thing. I know it would be unreasonable to expect
something in this state to make it into the 8.4 contrib at this late hour.
But since it's independant of the core database stuff I figured I'd make
it available right at the wire here today in the off chance that did seem
a reasonable proposition to anybody. It is already a big improvement over
no tuning at all, and since it's a simple to change script I will rev this
based on feedback pretty fast now that the most boring guts are done.
Possible feedback topics:
-Setting the next round of values requires asking the user for some input
before making recommendations. Is it worth building a curses-based
interface to updating the values? That would be really helpful for people
with only ssh access to their server, but barring something like WCurses
I'm not sure it would help on Windows.
-How about a GUI one with Python's Tkinter interface? Now Windows isn't a
problem, but people using ssh aren't going to be as happy.
-I'm not sure if there's any useful replacement for the os.sysconf
interface I'm using to grab the memory information on the popular Windows
Python ports. Some of the other projects I looked at that tried to
abstract that OS interaction more didn't seem much better here (i.e. the
PSI library which doesn't support Windows either)
-Stepping back a bit from this particular code, is something in Python
like this ever going to be appropriate to ship as a contrib module?
There seems to be a bit more traction in this community for using Perl for
such things; I might do a Perl port of this one day but that's not going
to happen soon.
I think that's enough flametastic material now, and I do plan to join in
on patch review in penance for the disruption I've introduced here. The
next specific things I'm doing with this regardless is making it read and
respect the min/max values for settings as well as the rest of the
information avaialable from pg_settings. I eventually want to support all
the syntax suggested for pg_generate_config described at
http://wiki.postgresql.org/wiki/GUCS_Overhaul but for the moment I'm not
being so ambitious. Some of that is aimed at making a pg_generate_conf
that is capable of replacing the sample postgresql.conf file, which is a
couple of steps away from where I'm at right now.
--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD#!/usr/bin/python
pg_generate_conf
Sample usage:
pg_generate_conf config-file
Reads that config file, updates a few key configuration settings, then
writes result to standard output (so far)
import sys
import os
import datetime
class PGConfigLine:
Stores the value of a single line in the postgresql.conf file, with the
following fields:
lineNumber : integer
originalLine : string
commentSection : string
setsParameter : boolean
If setsParameter is True these will also be set:
name : string
readable : string
raw : string This is the actual value
delimiter (expectations are ' and )
def __init__(self,line,num=0):
self.originalLine=line
self.lineNumber=num
self.setsParameter=False
# Remove comments and edge whitespace
self.commentSection=
commentIndex=line.find('#')
if commentIndex = 0:
line=line[0:commentIndex]
self.commentSection=line[commentIndex:]
line=line.strip()
if line == :
return
# Split into name,value pair
equalIndex=line.find('=')
if equalIndex0:
return
(name,value)=line.split('=')
name=name.strip()
value=value.strip()
self.name=name;
self.setsParameter=True;
# Many types of values have ' '
Re: [HACKERS] Well done, Hackers
Tom, Robert, Simon, What, are people just on edge because of the US election? It looks to me like the commitfest system is going really well. Of course, we'll see how long it takes to close out 8.4. But I think we're in much better shape than we were for 8.3. We're even in better shape to reject things. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please make sure your patches are on the wiki page
KaiGai, et al, * KaiGai Kohei ([EMAIL PROTECTED]) wrote: Stephen, what is the status of your efforts? I've now got it passing the base regression tests with the actual logic included in the path. That doesn't mean it works completely, of course, but I feel like I'm making progress. Feedback, as always, is appreciated. The latest one I could found is the colprivs_wip.20080902.diff.gz. Do you have any updated one? Please find the latest attached, against current CVS head as of a few minutes ago (including the change to heap_modify_tuple). I'll also update the commitfest wiki w/ this once it's hit the archives. Thanks, Stephen colprivs_wip.20081101.diff.gz Description: Binary data signature.asc Description: Digital signature
Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.
Just a small correction: there should be time::text'prev_time' for the
calculations to be correct:
select *
from (
select (((case when time::text = 'prev_time' then *0* else *1*
end)+('dense_rank')::int4)::text'dense_rank')::int4 as position,
runnerid, time, time::text'prev_time'
from big_marathon
order by time
) results
where position=*2*
-- meter_readings
select '' 'lag';
select date, reading::numeric-(case lag when '' then null else lag
end)::numeric as used
from (
select date, 'lag' as lag, reading::text 'lag' as reading
from meter_readings
order by date
) as t
order by used asc nulls last limit *1*
Best regards,
Vladimir Sitnikov
Re: [HACKERS] WIP: Column-level Privileges
Markus, * Markus Wanner ([EMAIL PROTECTED]) wrote: Sorry, this took way longer than planned. Beleive me, I understand. :) testdb=# GRANT TRUNCATE (single_col) ON test TO malory; GRANT This has been fixed in the attached patch. Some privilege regression tests currently fail with your patch, but I think that's expected. All regression tests should pass now. Documentation and new regression tests for column level privileges are still missing. If you want, Stephen, I can work on that. If you could work on the documentation, that'd be great! I've updated the regression tests to include some testing of the column level privileges. Feel free to suggest or add to them though, and if you find anything not working as you'd expect, please let me know! There are a few outstanding items that I can think of- The error-reporting could be better (eg, give the specific column that you don't have rights on, rather than just saying you don't have rights on the relation), but I wasn't sure if people would be happy with the change to existing error messages that would imply. Basically, rather than getting told you don't have rights on the relation, you would be told you don't have rights on the first column in the relation that you don't have the necessary rights on. It's a simple change, if people are agreeable to it. Having it give the table-level message only when there aren't column-level privileges is possible, but makes the code rather ugly.. Documentation, of course. More testing, more review, etc, etc, making sure everything is working as expected, more complex queries than what I've done to make sure things happen correctly. Tom has me rather nervous based on his previous comments about the rewriter/optimizer causing problems, and I barely touched them.. I also wonder if you could use joins or something to extract information about columns you're not supposed to have access to, or where clauses, etc.. Anyhow, updated patch attached. Thanks, Stephen colprivs_wip.2008110102.diff.gz Description: Binary data signature.asc Description: Digital signature
Re: [HACKERS] WIP: Column-level Privileges
Markus, et al, * Stephen Frost ([EMAIL PROTECTED]) wrote: I also wonder if you could use joins or something to extract information about columns you're not supposed to have access to, or where clauses, etc.. welp, I've done some additional testing and there's good news and bad, I suppose. The good news is that when relations are join'd, they go through expandRelation, which adds all the columns in that relation to the 'required' set, so you have to have rights to all columns on a table to join against it in the normal way. On the other hand, you can just select out the columns you have access to in a subquery and then join against *that* and it works. updates with where clauses and inserts-with-selects seem to work correctly though, which is nice. A case I just realized might be an issue is doing a 'select 1 from x;' where you have *no* rights on x, or any columns in it, would still get you the rowcount. That might not be too hard to fix though, I'll look into it tomorrow sometime. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Windowing Function Patch Review - Performance Comparison.
2008/11/2 David Rowley [EMAIL PROTECTED]: Obervations: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan. Is there any reason for this? Perhaps I'm missing something. Hitoshi, can you take a look at this? Ah, good point. Maybe it's because I haven't paid attention to choose index_scan for upper sort node. I just put the sort node whatever the downer node is, so it might be needed to sink the information down to scan choice process that we use sort node upper. Could someone point me out how to do it, or which part of the existing code would be a good guide? Tests: Please see attached file. Perhaps there were more efficient ways for certain queries, I just couldn't think of them... Please let me know if you feel I should be conducting the review in another way. Thanks for your test. Didn't post publicly, I've also tested real problems and performed better than I thought. If you can afford it, could you add selfjoin cases? It's like: -- normal SELECT t1.id, t1.grp, count(t2) + 1 AS row_number FROM t t1 INNER JOIN t t2 ON t1.grp = t2.grp AND t1.id t2.id; -- windowing SELECT id, grp, row_number() OVER (PARTITION grp ORDER BY id) FROM t; Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Column-level Privileges
Stephen Frost [EMAIL PROTECTED] writes: ... A case I just realized might be an issue is doing a 'select 1 from x;' where you have *no* rights on x, or any columns in it, would still get you the rowcount. Well, if you have table-level select on x, I would expect that to work, even if your privs on every column of x are revoked. If the patch doesn't get this right then it needs more work ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] Solve a problem of LC_TIME of windows.
Hi. I am sorry to be a very late reaction... Hiroshi Saito [EMAIL PROTECTED] writes: From: Magnus Hagander [EMAIL PROTECTED] Also, the patch needs error checking. strftime() can fail, and the multibyte conversion functions can certainly fail. That will need to be added. I will proposal the next patch.:-) next patch is this. Regards, Hiroshi Saito pg_locale_patch-v4 Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
