Re: [HACKERS] Sync Rep: First Thoughts on Code
Hi, thanks for the comment! On Mon, Dec 8, 2008 at 11:04 PM, Simon Riggs [EMAIL PROTECTED] wrote: Could we start with pictures and some descriptions first, so we know we're on the right track? I foresee no coding issues. My understanding is that we start with a normal log shipping architecture, then we switch into continuous recovery mode. So we do use pg_standby at beginning, but then it gets turned off. Yes, I also understand so. Updated sequence pictures are on wiki as per usual. Please see P3, 4. http://wiki.postgresql.org/wiki/NTT%27s_Development_Projects#Detailed_Design p.6 looks good. But what is p.7? It's even more complex than the original. Forgive me, but I don't understand that. Can you explain? p.7 shows one of the system configuration examples. Some people don't want to share an archive between two servers would probably choose this configuration, I think. If archive is not shared, some WAL files before replication starts would not be copied automatically from the primary to standby. So, we have to copy them by hand or using clusterware ..etc. This is what p.7 shows. If archive is shared, archiver on the primary would copy them automatically (p.6). What is the procedure if the standby shuts down, for example if we wish to restart server to change a parameter? Stop postgres by using immediate shutdown, and start postgres from an existing database cluster directory. When restarting postgres, if there are one or more archives, we also need to copy the WAL files after stopping replication before restarting replication. Or to reboot the system it is on. Does the primary switch back to writing files to archive? I assume that the primary always writes files to archive, that is, basically the primary doesn't switch to non-archiving mode. Of course, if archiving is disabled on the primary in any reason when restarting standby, the primary need to switch back. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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 1202
On Sun, 7 Dec 2008, Alex Hunsaker wrote: (dual core machine, --enable-debug, --enable-cassert build) pgbench -c 2 -T60 -n -f test.sql HEAD: tps = 9.674423 PATCH: tps = 9.695784 Two general suggestions here, not specific to this patch: While it's good to do most testing with debug and cassert turned on, you shouldn't report performance results with those two flags enabled. What if the patch has some large amount of overhead that only shows up when compiled with debug or asserts on? You'd end up reporting a performance loss that doesn't actually exist in a real build. Unfortunately, the only way to get good performance results is to have a parallel build done with those off, in addition to the debug/assert one used to catch bugs. The above pgbench is executing less than 600 actual tests (60 seconds @ 9.7TPS). That seems a bit short to me. If you sorted out the above and run this again, it would be good to let pgbench run for a lot longer than 1 minute, to see if the results show some more significant difference. With this few TPS, it would be nice to let that run for 30 minutes or more if you can find some time to schedule that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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: default values for function parameters
if I may request one simple change/addition, Probably trivial to add, but I don't have too much time to give away now to any other project than one that pays my debts. The default param that's in the middle. Would it be hard, or do anyone objects against adding 'default' keyword there, so one doesn't have to substitute default param 3, when he only wants to override 2nd in funct(1,2,3) ? -- 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: default values for function parameters
2008/12/9 Grzegorz Jaskiewicz [EMAIL PROTECTED]: if I may request one simple change/addition, Probably trivial to add, but I don't have too much time to give away now to any other project than one that pays my debts. The default param that's in the middle. Would it be hard, or do anyone objects against adding 'default' keyword there, so one doesn't have to substitute default param 3, when he only wants to override 2nd in funct(1,2,3) ? I don't plan it, or not yet, because I don't would to complicate rules for using it. But for 8.5 I prepare named notation and maybe mixed notation. like param1 = some, param2 = some .. Regards Pavel Stehule -- 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: default values for function parameters
Grzegorz Jaskiewicz wrote: if I may request one simple change/addition, Probably trivial to add, but I don't have too much time to give away now to any other project than one that pays my debts. The default param that's in the middle. Would it be hard, or do anyone objects against adding 'default' keyword there, so one doesn't have to substitute default param 3, when he only wants to override 2nd in funct(1,2,3) ? I don't really understand what you are talking about, but if you mean allowing default values in the middle of a parameter list, then I'd say rather not. -- 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] cvs head initdb hangs on unixware
Hi Tom,
On Mon, 8 Dec 2008, Tom Lane wrote:
Date: Mon, 08 Dec 2008 13:15:28 -0500
From: Tom Lane [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: Heikki Linnakangas [EMAIL PROTECTED],
Zdenek Kotala [EMAIL PROTECTED],
pgsql-hackers list pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] cvs head initdb hangs on unixware
[EMAIL PROTECTED] writes:
the infinite loop occurs in fsm_search_avail when called for the 32nd
time.
... which is the first time that the initial test doesn't make it fall
out immediately.
Would you add a couple more printouts, along the line of
nodeno = target;
while (nodeno 0)
{
+ fprintf(stderr, ascend at node %d value %d\n,
+ nodeno, fsmpage-fp_nodes[nodeno]);
if (fsmpage-fp_nodes[nodeno] = minvalue)
break;
/*
* Move to the right, wrapping around on same level if
necessary,
* then climb up.
*/
nodeno = parentof(rightneighbor(nodeno));
}
/*
* We're now at a node with enough free space, somewhere in the middle
of
* the tree. Descend to the bottom, following a path with enough free
* space, preferring to move left if there's a choice.
*/
while (nodeno NonLeafNodesPerPage)
{
int leftnodeno = leftchild(nodeno);
int rightnodeno = leftnodeno + 1;
bool leftok = (leftnodeno NodesPerPage)
(fsmpage-fp_nodes[leftnodeno] = minvalue);
bool rightok = (rightnodeno NodesPerPage)
(fsmpage-fp_nodes[rightnodeno] = minvalue);
+ fprintf(stderr, descend at node %d value %d, leftnode %d value %d,
rightnode %d value %d\n,
+ nodeno, fsmpage-fp_nodes[nodeno],
+ leftnodeno, fsmpage-fp_nodes[leftnodeno],
+ rightnodeno, fsmpage-fp_nodes[rightnodeno]);
if (leftok)
nodeno = leftnodeno;
else if (rightok)
nodeno = rightnodeno;
else
(I'm assuming we can print possibly-off-the-end array elements without dumping
core; which is bogus in general but I expect we can get away with it
for this purpose.)
Also, we don't really need 94MB of log to convince us it's an
infinite loop ;-)
oops, sorry
regards, tom lane
I first misread your mail, and added only the first fprintf , while I was
uploading a 400M initdb.log, I went back to add the second one.
Guess what! with the fprintf .. descending node... in place, everything
goes well. The optimizer definitly does something weird along the
definition/assignement of leftok/rightok..
--
Olivier PRENANT Tel: +33-5-61-50-97-00 (Work)
15, Chemin des Monges+33-5-61-50-97-01 (Fax)
31190 AUTERIVE +33-6-07-63-80-64 (GSM)
FRANCE Email: [EMAIL PROTECTED]
--
Make your life a dream, make your dream a reality. (St Exupery)
--
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] operator does not exist: smallint smallint[]
Euler Taveira de Oliveira [EMAIL PROTECTED] writes: I saw a report at .br mailing list [1] complaining about the message's title. I do not try to investigate it. Am I missing something? euler=# select attname from pg_attribute where attnum 0 and attnum ALL(select conkey from pg_constraint where conrelid = attrelid and contype = 'p'); ERROR: operator does not exist: smallint smallint[] It's entirely right: there's no such operator. Oh, you wanted a way to write the query correctly? I think what this person wants might be something like select attname from pg_attribute where attnum 0 and not attisdropped and not exists (select 1 from pg_constraint where attnum = ANY(conkey) and conrelid = attrelid and contype = 'p'); ... although that produces quite a lot of rows, so some additional constraint is probably wanted too. 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] WIP: default values for function parameters
Grzegorz Jaskiewicz wrote:
Ok, how about
CREATE FUNCTION FOO (one int, two float8 default 3.14, three int[]
default '{6,7,8,90}');
and than SELECT FOO( 777, DEFAULT, '{1,2,3,4,5}');
Yeah, that could be a useful feature.
--
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] cvs head initdb hangs on unixware
[EMAIL PROTECTED] napsal(a): I first misread your mail, and added only the first fprintf , while I was uploading a 400M initdb.log, I went back to add the second one. Guess what! with the fprintf .. descending node... in place, everything goes well. The optimizer definitly does something weird along the definition/assignement of leftok/rightok.. Could you generate assembler code with and without optimization of fsmSearch function? Of course without extra printf :-). It should show difference. Zdenek -- 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: default values for function parameters
Grzegorz Jaskiewicz [EMAIL PROTECTED] writes: The default param that's in the middle. Would it be hard, or do anyone objects against adding 'default' keyword there, so one doesn't have to substitute default param 3, when he only wants to override 2nd in funct(1,2,3) ? Yes, and yes. We can only allow eliminating parameters from the right, else it becomes impossibly ambiguous. 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] Sync Rep: First Thoughts on Code
On Tue, 2008-12-09 at 17:15 +0900, Fujii Masao wrote: But what is p.7? It's even more complex than the original. Forgive me, but I don't understand that. Can you explain? p.7 shows one of the system configuration examples. Some people don't want to share an archive between two servers would probably choose this configuration, I think. If archive is not shared, some WAL files before replication starts would not be copied automatically from the primary to standby. So, we have to copy them by hand or using clusterware ..etc. This is what p.7 shows. If archive is shared, archiver on the primary would copy them automatically (p.6). I agree that is the way to do it *if* the archive is not shared. But why would you want to *not* share the archive?? What is the procedure if the standby shuts down, for example if we wish to restart server to change a parameter? Stop postgres by using immediate shutdown, and start postgres from an existing database cluster directory. When restarting postgres, if there are one or more archives, we also need to copy the WAL files after stopping replication before restarting replication. Or to reboot the system it is on. Does the primary switch back to writing files to archive? I assume that the primary always writes files to archive, that is, basically the primary doesn't switch to non-archiving mode. OK, I think that clears up what I was seeing in the code. i.e. I didn't understand the modes of operation. I really like most of what you've done, though you must forgive me for saying I still don't like this. I really am with you on how tiresome that sounds. For clarity: I don't think its acceptable to have the archiver send files to the archive at the same time as we're streaming data. In normal running we should not duplicate the data paths - its just too much data volume and/or bandwidth. The cleanest way I can see is to have two modes of operation: * First mode is file-based log shipping (FLS) (i.e. warm standby) * Second mode is streaming log shipping (SLS) (wal sender to wal receiver) When we start we are in FLS mode, then we catch up to the cross-over point and we switch to SLS mode. If streaming stops, we just switch back to FLS mode. If they reconnect, we follow same procedure again. So the two modes are compatible, but are never simultaneously active except for a short period when we switch modes. If SLS mode is active then the archiver doesn't send files. If FLS mode is active, we send files. All of the places in code that currently are not optimised when XLogArchivingActive() must remain unoptimised for either FLS or SLS mode, so we need a new name for that. This makes least number of changes to existing architecture. People currently use FLS mode and understand it (!), they just add understanding of SLS mode. It's also a very straightforward architecture, which means fewer code paths and less weird bugs. (There's been enough already, as you know). So just for clarity, let me rephrase it: We set up FLS mode as we do currently. Then we initiate SLS mode. At the end of the next WAL file on primary we archive it, then turn off archiving on primary. (So for up to one WAL file we operate two modes together). If SLS mode ends, we send next WAL file via archiver. Some part of that file has already been streamed across, but that doesn't matter. (If SLS mode ends because primary is down, we obviously do nothing. If we have a split brain situation then we rely on clusterware to kill us (STONITH). So AFAICS p.6 of the architecture is all we really need. Nice, simple. -- 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] contrib/pg_stat_statements 1202
ITAGAKI Takahiro [EMAIL PROTECTED] writes: I thought that output of new counters are too wide and it brakes compatibility of EXPLAIN ANALYZE. On the other hand, we don't have to think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly added in 8.4. However, overheads should be avoided. We could have two kinds of instrumentations, time-only or all-stats. I've got a serious problem with the way that this patch is being presented. It's being named and described as though it's just another contrib module, but in fact it makes invasive, undocumented changes to the behavior of the core EXPLAIN functionality --- changes that certainly cannot be claimed to having been agreed to by the community, since most of us probably weren't aware that there was any such thing going on inside this patch. Please split this into two separate patches that can be separately evaluated. 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] WIP: default values for function parameters
Decibel! wrote: On Nov 30, 2008, at 12:04 PM, David E. Wheeler wrote: Agreed, default values should not be a part of function signatures, although it might be nice if ALTER FUNCTION to allow default values to be changed. It would be VERY nice. I routinely cut and paste an entire function header to later perform things like ALTER and GRANT so that I don't have to re-type everything. It would be a huge PITA if I had to then go and delete any default settings. That is not what David was talking about above. Currently, I don't think you can change parameter default values of an existing function. But I think that would be a useful and uncontroversial addition. -- 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] Sync Rep: First Thoughts on Code
Simon Riggs wrote: For clarity: I don't think its acceptable to have the archiver send files to the archive at the same time as we're streaming data. In normal running we should not duplicate the data paths - its just too much data volume and/or bandwidth. What if you want to run archiving for backup purposes, and also have a standby server? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] WIP: default values for function parameters
Ok, how about
CREATE FUNCTION FOO (one int, two float8 default 3.14, three int[]
default '{6,7,8,90}');
and than SELECT FOO( 777, DEFAULT, '{1,2,3,4,5}');
I have no idea what SQL standard says in that case, all I know is that
keyword DEFAULT exists in it, and is used in queries for similar
purpose.
--
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] Quick patch: Display sequence owner
Josh Williams wrote: The patch adds a query against pg_depend, then fakes an extra column owned_by in the output: Please send a context diff (diff -c) Index: src/bin/psql/describe.c === RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v retrieving revision 1.188 diff -r1.188 describe.c 917c917 seq_values = pg_malloc_zero((SEQ_NUM_COLS+1) * sizeof(*seq_values)); --- seq_values = pg_malloc_zero((SEQ_NUM_COLS+2) * sizeof(*seq_values)); -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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: default values for function parameters
2008/12/9 Grzegorz Jaskiewicz [EMAIL PROTECTED]:
Ok, how about
CREATE FUNCTION FOO (one int, two float8 default 3.14, three int[] default
'{6,7,8,90}');
and than SELECT FOO( 777, DEFAULT, '{1,2,3,4,5}');
I have no idea what SQL standard says in that case, all I know is that
keyword DEFAULT exists in it, and is used in queries for similar purpose.
SQL standard don't say anything. Leader (in this topic) is Oracle,
and there is for this case mixed notation (google) -
select foo(777, three= '{1,2,3,4,5});
it's more safe and more readable.
I did some test, and I thing so it is implementable. I had to solve
problem with hstore module. There is defined operator = too, what is
bad. But we can implemented in transformation and it should by
disabled via GUC, so it's solveable.
reagards
Pavel Stehule
--
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] cvs head initdb hangs on unixware
[EMAIL PROTECTED] writes: Guess what! with the fprintf .. descending node... in place, everything goes well. The optimizer definitly does something weird along the definition/assignement of leftok/rightok.. Hmm, so the problem is in that second loop. The trick is to pick some reasonably non-ugly code change that makes the problem go away. The first thing I'd try is to get rid of the overly cute optimization int rightnodeno = leftnodeno + 1; and make it just read int rightnodeno = rightchild(nodeno); If that doesn't work, we might try refactoring the code enough to get rid of the goto, but that looks a little bit tedious. 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] new libpq SSL connection option
Alex Hunsaker wrote: On Fri, Dec 5, 2008 at 14:22, Andrew Chernow [EMAIL PROTECTED] wrote: Alex Hunsaker wrote: On Fri, Dec 5, 2008 at 13:58, Andrew Chernow [EMAIL PROTECTED] wrote: Who anyone be opposed to ssldir = path as a connection option? Currently, there is no way to change the homedir method ~/.postgresql ... or am I missing something? I am willing to supply a patch. You mean something like the http://archives.postgresql.org/message-id/[EMAIL PROTECTED] ? yes, excately like that; apparently missed it. What is the status of that patch? I see it was left in pending review .. is the fest is over? I think all that is left is changing PGROOTCERT to PGSSLROOTCERT, agreeing to IFDEF the params out or not oh and this little bit: Magnus Hagander escribió: On Fri, Aug 1, 2008 at 13:31, Alvaro Herrera alvherre(at)commandprompt(dot)com wrote: Something that's bothering me is that PGSSLKEY is inconsistent with the sslkey conninfo parameter. PGSSLKEY specifies an engine (basically a driver for specialized hardware AFAICT) from which the key is to be loaded, but sslkey is a simple filename. This means that there's no way to load a key from hardware if you want to specify it per connection. Not that I have any such hardware, but it looks bogus. I think the above consideration needs some discussion too. Committing it as-is doesn't seem OK because you can't change it later -- it's user-visible. Here's a suggested update, which does *not* yet have documentation updates. Changes from previous patch: * Made all parameters available always and ignored for non-SSL connections * Renamed PGROOTCERT to PGSSLROOTCERT * Changes the way PGSSLKEY/sslkey is handled to this: When the string does not contain a colon, it's treated as a filename. If it does contain a colon (and on windows, if this colon is not in the second position indicating a drive letter), it's treated as engine:key as before. This should keep backwards compatibility. I would also like to look this over completely - we only support loading the KEY from the smartcard, but you still have to manually copy the certificate to your machine. I don't know exactly how you're supposed to do this in OpenSSL - some googling shows almost nobody else uses the functions quite the way we do. So I'd like to look over if we need to do more around this later, but this patch should make it possible to use keys from different files without breaking backwards compatibility with what we had before. So I'm considering that a separate step, that may not be done in time for 8.4. So. Comments? //Magnus *** a/doc/src/sgml/libpq.sgml --- b/doc/src/sgml/libpq.sgml *** *** 318,323 --- 318,367 /varlistentry varlistentry + termliteralsslcert/literal/term + listitem + para +This parameter specifies the file name of the client SSL +certificate. This option is only available if +productnamePostgreSQL/ is compiled with SSL support. + /para + /listitem + /varlistentry + + varlistentry + termliteralsslkey/literal/term + listitem + para +This parameter specifies the file name of the client SSL key. +This option is only available if productnamePostgreSQL/ is +compiled with SSL support. + /para + /listitem + /varlistentry + + varlistentry + termliteralsslrootcert/literal/term + listitem + para +This parameter specifies the file name of the root SSL certificate. +This option is only available if productnamePostgreSQL/ is +compiled with SSL support. + /para + /listitem + /varlistentry + + varlistentry + termliteralsslcrl/literal/term + listitem + para +This parameter specifies the file name of the SSL certificate +revocation list (CRL). This option is only available if +productnamePostgreSQL/ is compiled with SSL support. + /para + /listitem + /varlistentry + + varlistentry termliteralkrbsrvname/literal/term listitem para *** *** 5778,5783 myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) --- 5822,5849 listitem para indexterm +primaryenvarPGROOTCERT/envar/primary + /indexterm + envarPGROOTCERT/envar specifies the file name where the SSL + root certificate is stored. This can be overridden by the + literalsslrootcert/literal connection parameter. + /para + /listitem + + listitem + para + indexterm +primaryenvarPGSSLCRL/envar/primary + /indexterm + envarPGSSLCRL/envar specifies the file name where the SSL certificate + revocation list is
Re: [HACKERS] Multiplexing SUGUSR1
Fujii Masao wrote: Hi, On Mon, Dec 8, 2008 at 11:39 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: To set or clear the flag from PGPROC, to send or handle a signal, we have to acquire ProcArrayLock. Is that safe to do in a signal handler? No. If it's trying to do that then it's broken. In fact, if it's trying to do much of anything beyond setting a volatile flag variable in a signal handler, it's broken --- unless there are special provisions to limit where the signal trap can occur, which would be pretty much unacceptable for a multiplexed-signal implementation. Ok, I was afraid so. I think we'll need to replace the proposed bitmask with an array of sig_atomic_t flags then, and do without locking. Thanks! I updated the patch so (based on signal_handling_v2-heikki-1.patch). Thank you. Looks good to me, committed with minor changes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] Quick patch: Display sequence owner
Alvaro Herrera [EMAIL PROTECTED] writes: Josh Williams wrote: The patch adds a query against pg_depend, then fakes an extra column owned_by in the output: Please send a context diff (diff -c) Don't bother --- it's a really bad idea as designed anyway. owned_by is not a column of a sequence relation and pretending that it is one will just cause confusion. I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 ... If you really want to attach the information to the \d output for the sequence instead of the table, consider a similar footer-style display instead of making it look like something it's not. 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] new libpq SSL connection option
Magnus Hagander wrote: * Renamed PGROOTCERT to PGSSLROOTCERT +primaryenvarPGROOTCERT/envar/primary Looks like the old env name is still being used in the sgml docs. I like the flexibility this patch offers. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] WIP: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes:
select foo(777, three= '{1,2,3,4,5});
it's more safe and more readable.
... and it breaks an operator that's already in use.
I did some test, and I thing so it is implementable. I had to solve
problem with hstore module. There is defined operator = too, what is
bad. But we can implemented in transformation and it should by
disabled via GUC, so it's solveable.
What's wrong with the expr AS parameter_name syntax that we've
discussed before? (And no, having a GUC that changes the meaning
of = isn't an acceptable workaround.)
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] new libpq SSL connection option
Andrew Chernow wrote: Magnus Hagander wrote: * Renamed PGROOTCERT to PGSSLROOTCERT +primaryenvarPGROOTCERT/envar/primary Looks like the old env name is still being used in the sgml docs. Yes - I did say I hadn't updated the docs yet :-) //Magnus -- 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] multiple function execute using (func()).*
On Mon, Dec 8, 2008 at 5:15 PM, Merlin Moncure [EMAIL PROTECTED] wrote: Hello, I've been bit by this about a million times: select (func()).* executes the function once per each field in the returned tuple. See the example below: create function foo_func() returns foo as $$ declare f foo; begin raise notice '!'; return f; end; $$ language plpgsql; postgres=# select (foo_func()).*; NOTICE: ! NOTICE: ! NOTICE: ! a | b | c ---+---+--- | | (1 row) This is an anathema to any query trying to use composite types to circumvent single field subquery restrictions (for example, when using a record aggregate to choose a row). Normally you can work around this by writing it like this: select (foo_func()).*; - select * from foo_func(); Now, aside from the fact that these to forms should reasonably produce the same result, there are a couple of cases where the shorter, without 'from' version is easier to write. One example is in 'CREATE RULE', since you can't use 'new' in queries using the long form: postgres=# create or replace rule ins_foo as on insert to foo postgres-# do instead select * from add_foo(new); ERROR: subquery in FROM cannot refer to other relations of same query level CTE to the rescue. my wider problem was that I was trying to set up a rule like this: create table foo(...); create table bar(...); create view foobar as select * from foo join bar using (...); create function add_foobar(foobar) returns foobar as $$...$$ language plpgsql; create or replace rule ins_foobar as on insert to foobar do instead select (add_foobar(new)).*; The idea is that the rule calls the add function but returns the adjusted composite so that insertions to foobar behave properly in queries using 'returning'. This turned out to be quite a bugaboo. I simply refused on principle to have add_foobar() explicitly list the fields for foobar, that is, not use the composite type. The longer form, select * from func(), was completely blocked because of subquery prohibitions on touching 'new'. However, this works: create or replace rule ins_foobar as on insert to foobar do instead with fb as (select add_foobar(new) as n) select (n).* from fb; Another great use of the already awesome CTE feature! :-D merlin p.s. I still think the SQL standard is wrong, and invalidation events should re-attempt the source sql (or, the '*' concept needs get to get pushed into the plan). oh well... -- 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: default values for function parameters
2008/12/9 Tom Lane [EMAIL PROTECTED]:
Pavel Stehule [EMAIL PROTECTED] writes:
select foo(777, three= '{1,2,3,4,5});
it's more safe and more readable.
... and it breaks an operator that's already in use.
I did some test, and I thing so it is implementable. I had to solve
problem with hstore module. There is defined operator = too, what is
bad. But we can implemented in transformation and it should by
disabled via GUC, so it's solveable.
What's wrong with the expr AS parameter_name syntax that we've
discussed before? (And no, having a GUC that changes the meaning
of = isn't an acceptable workaround.)
what is acceptable workaround? I unhappy, so this symbol was used for
this minor contrib module (for this operator doesn't exists regress
test).
a) AS is used in diferent meaning now [rename] (SQL/XML), labels
b) when we implemented, then we blocking possible way, when ANSI SQL
generalise current behave
c) it's own syntax that will be muddly (viz a.)
d) both mayor databases has syntax name symbol value
@name = value
name = value
I am searching ways (or syntax) for two features named params, and
named values. Last are inspirated SQL/XML that is great (I know, so
your opinion is different). For export functions I need to send some
information about columns or labels into functions. So it's usable for
custom export functions, JSON implementation, maybe for
communications.
regards
Pavel Stehule
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] multiple function execute using (func()).*
On Dec 8, 2008, at 5:15 PM, Merlin Moncure wrote: Hello, I've been bit by this about a million times: select (func()).* executes the function once per each field in the returned tuple. See the example below: I ran into this exact problem a week or two ago. I didn't dig too far into it but I figured (func()).* was being expanded by the parser into func().a, func().b and friends. marking it stable didn't help. It can be quite surprising, especially if func() is expensive (as was my case) or has side effects. -- Jeff Trout [EMAIL PROTECTED] http://www.stuarthamm.net/ http://www.dellsmartexitin.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] posix_fadvise v22
Here's an update to eliminate two small bitrot conflicts. posix_fadvise_v22.diff.gz Description: Binary data -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- 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] operator does not exist: smallint smallint[]
Tom Lane escreveu: Euler Taveira de Oliveira [EMAIL PROTECTED] writes: I saw a report at .br mailing list [1] complaining about the message's title. I do not try to investigate it. Am I missing something? euler=# select attname from pg_attribute where attnum 0 and attnum ALL(select conkey from pg_constraint where conrelid = attrelid and contype = 'p'); ERROR: operator does not exist: smallint smallint[] It's entirely right: there's no such operator. Out of curiosity, why the other queries work? euler=# select 1::smallint ALL(array[2::smallint, 3::smallint]); ?column? -- t (1 registro) euler=# select 1::smallint ALL(array[1::smallint, 2::smallint,3::smallint]); ?column? -- f (1 registro) euler=# select 1::smallint ALL(array[1, 2, 3]); ?column? -- f (1 registro) -- Euler Taveira de Oliveira http://www.timbira.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] Multiplexing SUGUSR1
Heikki Linnakangas [EMAIL PROTECTED] writes: Thank you. Looks good to me, committed with minor changes. I don't think this patch is anywhere near ready to apply. In the first place, touching the PGPROC like that without any lock seems completely unsafe --- among other things, you're relying on an undocumented assumption that the space occupied by a PGPROC struct will never be recycled for use as anything else. It might be all right for the limited purposes at the moment, but if you are advertising this as a general purpose signal multiplexer then it will very soon not be all right. For the same reason, it seems like a bad design to set this up so that the postmaster can't possibly use the mechanism safely. (Before a couple of months ago, this wouldn't even have worked to replace the existing use of SIGUSR1.) And in the third place, this doesn't work unless one has one's hands on the target backend's PGPROC, and not merely its PID. I object to the changes in sinvaladt.c altogether, and note that this decision makes it impossible to fold SIGUSR2 handling into the multiplex code, which is another simple proof that it fails to qualify as a general-purpose multiplexer. I think we need something closer to the postmaster signal multiplexing mechanism, wherein there is a dedicated shared memory area of static layout that holds the signaling flags. And it needs to be driven off of knowing the target's PID, not anything else. 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] new libpq SSL connection option
Magnus Hagander [EMAIL PROTECTED] writes: I would also like to look this over completely - we only support loading the KEY from the smartcard, but you still have to manually copy the certificate to your machine. I don't know exactly how you're supposed to do this in OpenSSL - some googling shows almost nobody else uses the functions quite the way we do. So I'd like to look over if we need to do more around this later, but this patch should make it possible to use keys from different files without breaking backwards compatibility with what we had before. So I'm considering that a separate step, that may not be done in time for 8.4. I'm confused here. Are you proposing user-visible changes that might not get done in time for 8.4? I don't much like the idea that the API is going to remain a moving target --- once 8.4 is out you will have backwards compatibility constraints with whatever it does. It would be better to avoid extending the feature set beyond what 8.3 can do until you are certain it's right. 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] SSL BIO wrappers
Attached patch replaces the SSL BIO wrapper code we have now, with one
that directly calls the send() and recv() functions instead. THis means
that they get passed through the rewrite macros to our internal
functions on Win32, and I think this will fix some of the strange errors
that seem to be platform specific there (there are some really hard to
reproduce bug reports around that).
They're of course tightly modeled around the code from OpenSSL - found
here: http://cvs.openssl.org/fileview?f=openssl/crypto/bio/bss_sock.cv=1.15
(functions sock_read and sock_write)
So:
1) Thoughts in general?
2) Per my_sock_write - should we do the prepare read there as well, even
though it's a write? :-)
//Magnus
*** a/src/backend/libpq/be-secure.c
--- b/src/backend/libpq/be-secure.c
***
*** 394,438 wloop:
#ifdef USE_SSL
/*
! * Private substitute BIO: this wraps the SSL library's standard socket BIO
! * so that we can enable and disable interrupts just while calling recv().
! * We cannot have interrupts occurring while the bulk of openssl runs,
! * because it uses malloc() and possibly other non-reentrant libc facilities.
*
- * As of openssl 0.9.7, we can use the reasonably clean method of interposing
- * a wrapper around the standard socket BIO's sock_read() method. This relies
- * on the fact that sock_read() doesn't call anything non-reentrant, in fact
- * not much of anything at all except recv(). If this ever changes we'd
- * probably need to duplicate the code of sock_read() in order to push the
- * interrupt enable/disable down yet another level.
*/
static bool my_bio_initialized = false;
static BIO_METHOD my_bio_methods;
- static int (*std_sock_read) (BIO *h, char *buf, int size);
static int
my_sock_read(BIO *h, char *buf, int size)
{
! int res;
prepare_for_client_read();
! res = std_sock_read(h, buf, size);
client_read_ended();
return res;
}
static BIO_METHOD *
my_BIO_s_socket(void)
{
if (!my_bio_initialized)
{
memcpy(my_bio_methods, BIO_s_socket(), sizeof(BIO_METHOD));
- std_sock_read = my_bio_methods.bread;
my_bio_methods.bread = my_sock_read;
my_bio_initialized = true;
}
return my_bio_methods;
--- 394,469
#ifdef USE_SSL
/*
! * Private substitute BIO: this does the sending and receiving using send() and
! * recv() instead. This is so that we can enable and disable interrupts
! * just while calling recv(). We cannot have interrupts occurring while
! * the bulk of openssl runs, because it uses malloc() and possibly other
! * non-reentrant libc facilities. We also need to call send() and recv()
! * directly so it gets passed through the socket/signals layer on Win32.
! *
! * They are closely modelled on the original socket implementations in OpenSSL.
*
*/
static bool my_bio_initialized = false;
static BIO_METHOD my_bio_methods;
static int
my_sock_read(BIO *h, char *buf, int size)
{
! int res = 0;
prepare_for_client_read();
! if (buf != NULL)
! {
! res = recv(h-num, buf, size, 0);
! BIO_clear_retry_flags(h);
! if (res = 0)
! {
! /* If we were interrupted, tell caller to retry */
! if (errno == EINTR)
! {
! BIO_set_retry_read(h);
! }
! }
! }
client_read_ended();
return res;
}
+ static int
+ my_sock_write(BIO *h, const char *buf, int size)
+ {
+ int res = 0;
+
+ /*
+ * XXX: should we do a prepare_for_client_read here as well,
+ * even though it's not a read operation?
+ */
+
+ res = send(h-num, buf, size, 0);
+ if (res = 0)
+ {
+ if (errno == EINTR)
+ {
+ BIO_set_retry_write(h);
+ }
+ }
+
+ return res;
+ }
+
static BIO_METHOD *
my_BIO_s_socket(void)
{
if (!my_bio_initialized)
{
memcpy(my_bio_methods, BIO_s_socket(), sizeof(BIO_METHOD));
my_bio_methods.bread = my_sock_read;
+ my_bio_methods.bwrite = my_sock_write;
my_bio_initialized = true;
}
return my_bio_methods;
--
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] new libpq SSL connection option
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: I would also like to look this over completely - we only support loading the KEY from the smartcard, but you still have to manually copy the certificate to your machine. I don't know exactly how you're supposed to do this in OpenSSL - some googling shows almost nobody else uses the functions quite the way we do. So I'd like to look over if we need to do more around this later, but this patch should make it possible to use keys from different files without breaking backwards compatibility with what we had before. So I'm considering that a separate step, that may not be done in time for 8.4. I'm confused here. Are you proposing user-visible changes that might not get done in time for 8.4? I don't much like the idea that the API is going to remain a moving target --- once 8.4 is out you will have backwards compatibility constraints with whatever it does. It would be better to avoid extending the feature set beyond what 8.3 can do until you are certain it's right. I'm not proposing anything yet - I haven't read up on it. If it does change, though, only the engine-specific stuff would change AFAICT. The new functionality in this patch is all around specifying filenames, so that would not change. And most likely it would not be a change in visible behavior if I get the time to fix that - it'll either just be an under-the-hood change, or more likely an extension to the parameters. I see no reason why it should have any user-visible change at all on the stuff that's in this patch. //Magnus -- 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: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: ... and it breaks an operator that's already in use. what is acceptable workaround? I unhappy, so this symbol was used for this minor contrib module (for this operator doesn't exists regress test). If you could prove that it were *only* being used by this contrib module then I might hold still for replacing it. But you can't. The odds are good that people have custom data types using similarly-named operators. 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] operator does not exist: smallint smallint[]
Euler Taveira de Oliveira [EMAIL PROTECTED] writes: Tom Lane escreveu: It's entirely right: there's no such operator. Out of curiosity, why the other queries work? The behavior is different depending on whether the argument of ANY/ALL is a sub-SELECT or not. If it is, then the comparisons are between the LHS and the successive values produced by the sub-SELECT. If it isn't, then the RHS has to produce an array and the comparisons are to the array elements. 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 1202
On the other hand, we don't have to
think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly
added in 8.4.
Uh, it exists for me in 8.2.9.
Welcome to psql 8.2.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
portal=# explain analyze verbose select 1;
QUERY PLAN
{RESULT
:startup_cost 0.00
:total_cost 0.01
:plan_rows 1
:plan_width 0
:targetlist (
{TARGETENTRY
:expr
{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ 1 0 0 0 ]
}
:resno 1
:resname ?column?
:ressortgroupref 0
:resorigtbl 0
:resorigcol 0
:resjunk false
}
)
:qual
:lefttree
:righttree
:initPlan
:extParam (b)
:allParam (b)
:nParamExec 0
:resconstantqual
}
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.012 rows=1 loops
=1)
Total runtime: 0.244 ms
(35 rows)
...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] WIP: default values for function parameters
2008/12/9 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: ... and it breaks an operator that's already in use. what is acceptable workaround? I unhappy, so this symbol was used for this minor contrib module (for this operator doesn't exists regress test). If you could prove that it were *only* being used by this contrib module then I might hold still for replacing it. But you can't. The odds are good that people have custom data types using similarly-named operators. it means, so we must not implement any new operator? regards Pavel Stehule 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] WIP: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: If you could prove that it were *only* being used by this contrib module then I might hold still for replacing it. But you can't. The odds are good that people have custom data types using similarly-named operators. it means, so we must not implement any new operator? No, it doesn't mean any such thing. If we invented, say, int4 = int4 it would not break someone's use of = for their own custom datatype. What you're proposing would be a global redefinition of the meaning of =. This is closer to creating a new reserved word, which as I'm sure you know we try hard to avoid, even for keywords that the spec says we can reserve. The bar for making a new fully-reserved word that isn't in the spec is *very* high. 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] WIP: default values for function parameters
it means, so we must not implement any new operator? If the operator were called [EMAIL PROTECTED], I think you could make a good argument that no one else is likely using that for anything. Surely the same cannot be said of = Of course, [EMAIL PROTECTED] is not a very convenient name for an operator, but that's exactly the point: there are only a limited number of good, short names for operators, and = must be near the top of that list. ...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] WIP: default values for function parameters
2008/12/9 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: If you could prove that it were *only* being used by this contrib module then I might hold still for replacing it. But you can't. The odds are good that people have custom data types using similarly-named operators. it means, so we must not implement any new operator? No, it doesn't mean any such thing. If we invented, say, int4 = int4 it would not break someone's use of = for their own custom datatype. What you're proposing would be a global redefinition of the meaning of =. it's not true, because anybody could to define own operator on buildin types - so every new operator is risk and carry problems. So only new operator on new types are safe. All others shoud be problem - an using of any well know world carries risks. This is closer to creating a new reserved word, which as I'm sure you know we try hard to avoid, even for keywords that the spec says we can reserve. The bar for making a new fully-reserved word that isn't in the spec is *very* high. what is problematic on GUC? We use it actually for it? So we should disable or enable named_params, and when this feature will be disabled, then pg will be 100% compatible. It's better then creating some strange syntax. regards Pavel 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] Quick patch: Display sequence owner
On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote: I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 That makes more sense, though isn't that a little repetitive when default nextval(...) is visible immediately above it? Doesn't guarantee the sequence is owned by the table of course, but I'd imagine to most people it'd just be noise. Could see it being shown in the verbose version, \d+ foo.bar. I certainly like that better than making up an nonexistent column. :) If you really want to attach the information to the \d output for the sequence instead of the table, consider a similar footer-style display instead of making it look like something it's not. For the sequences themselves, it'd be nice to show somewhere, at least for tracking down stray sequences and identifying relationships. Perhaps a function to do the reverse of pg_get_serial_sequence()? Or better yet if no one else is already working on it, a more generic way to get readable information out of pg_depend? regards, tom lane - Josh Williams -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Windows buildfarm members
Following a failed BIOS flash on one of our machines, the Windows buildfarm members Mastodon, Baiji, Narwahl and Vaquita are offline. The box is under warranty, so I hope to have it fixed within a few days (timing being largely dependent upon persuading an engineer to visit at the same time as I'm in the office). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] WIP: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes: what is problematic on GUC? Basically, it's a bad idea to have GUCs that silently make significant changes in the syntactic meaning of a query. We've learned that lesson the hard way I think. There are places where we've been forced to do it because of priority-one considerations like standards compatibility (eg, standard_conforming_strings). This proposed feature doesn't carry anywhere near the weight that would make me willing to put in another such wart. 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] WIP: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: 2008/12/9 Tom Lane [EMAIL PROTECTED]: ... and it breaks an operator that's already in use. what is acceptable workaround? I unhappy, so this symbol was used for this minor contrib module (for this operator doesn't exists regress test). If you could prove that it were *only* being used by this contrib module then I might hold still for replacing it. But you can't. The odds are good that people have custom data types using similarly-named operators. it means, so we must not implement any new operator? Operators mean something specific in Postgres. You're talking about implementing a new fundamental syntax but using a token that's indistinguishable from the set of operators. This is a case where Postgres and these other databases have just diverged and copying their syntax would break with Postgres's in a major way. It just doesn't fit. Consider for example things like foo = bar foo == bar foo @ bar How would a user recognise which of these are legal operator names? Incidentally -- EDB selling Oracle compatibility may put me in a questionable position here -- the more Oracle incompatibilities in stock Postgres the better for us. But afaik we don't emulate = anyways so that hardly matters. If anything it shows how unimportant it is to worry about being compatible on this front. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] SSL BIO wrappers
Magnus Hagander [EMAIL PROTECTED] writes: Attached patch replaces the SSL BIO wrapper code we have now, with one that directly calls the send() and recv() functions instead. THis means that they get passed through the rewrite macros to our internal functions on Win32, and I think this will fix some of the strange errors that seem to be platform specific there (there are some really hard to reproduce bug reports around that). Hmm. Basically what this is doing is exactly what the comment says we didn't want to do, namely copy-and-paste the implementations of OpenSSL's socket BIO functions. How stable is that code? If the functions haven't changed textually in a long time (at least across all the OpenSSL versions we claim to support) then maybe it's okay. 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] Multiplexing SUGUSR1
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Thank you. Looks good to me, committed with minor changes. I don't think this patch is anywhere near ready to apply. Ok, I'll revert it if you feel that strongly. In the first place, touching the PGPROC like that without any lock seems completely unsafe --- among other things, you're relying on an undocumented assumption that the space occupied by a PGPROC struct will never be recycled for use as anything else. Right, it does depend on that. It might be all right for the limited purposes at the moment, but if you are advertising this as a general purpose signal multiplexer then it will very soon not be all right. For the same reason, it seems like a bad design to set this up so that the postmaster can't possibly use the mechanism safely. (Before a couple of months ago, this wouldn't even have worked to replace the existing use of SIGUSR1.) And in the third place, this doesn't work unless one has one's hands on the target backend's PGPROC, and not merely its PID. I object to the changes in sinvaladt.c altogether, and note that this decision makes it impossible to fold SIGUSR2 handling into the multiplex code, which is another simple proof that it fails to qualify as a general-purpose multiplexer. I'm surprised you feel that way. You suggested earlier (http://archives.postgresql.org/message-id/[EMAIL PROTECTED]) that a solution that only works for processes attached to shared memory would probably suffice for now. I think we need something closer to the postmaster signal multiplexing mechanism, wherein there is a dedicated shared memory area of static layout that holds the signaling flags. And it needs to be driven off of knowing the target's PID, not anything else. That seems hard, considering that we also want it to work without locking. Hmm, I presume we can use spinlocks in a signal handler? Perhaps some sort of a hash table protected by a spinlock would work. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] contrib/pg_stat_statements 1202
Robert Haas [EMAIL PROTECTED] writes: On the other hand, we don't have to think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly added in 8.4. Uh, it exists for me in 8.2.9. The current behaviour is newly added in 8.4. In 8.2 it meant something completely different and quite useless for end-users in any case, so backwards compatibility isn't important. What strikes me as a convenient approach is basically using EXPLAIN VERBOSE as a playground where we feel free to add everything we think of. If people run a command marked VERBOSE and complain it prints too much... As stuff matures and becomes indispensable we could consider moving it to the regular EXPLAIN or implement some way to specify precisely which data the user wants. Or just say XML/table data/whatever will solve the problem for us. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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] contrib/pg_stat_statements 1202
Robert Haas [EMAIL PROTECTED] writes: On the other hand, we don't have to think of compatibility in EXPLAIN ANALYZE VERBOSE because it is newly added in 8.4. Uh, it exists for me in 8.2.9. EXPLAIN VERBOSE has existed at least back to 7.0, probably further. However, we've felt free to whack around what it outputs, so maybe the backwards-compatibility issue isn't very strong. A possibly stronger complaint is that ANALYZE and VERBOSE have always been orthogonal options to EXPLAIN, and now there'd be some interaction between them. 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] Multiplexing SUGUSR1
Hi, I hope I'm not disturbing hackers at work by talking about completely unrelated things but... Le mardi 09 décembre 2008, Tom Lane a écrit : I think we need something closer to the postmaster signal multiplexing mechanism, wherein there is a dedicated shared memory area of static layout that holds the signaling flags. And it needs to be driven off of knowing the target's PID, not anything else. ...this makes me recall IMessage Queues from Postgres-R, reworked by Markus to follow your advices about postmaster and shared memory. http://archives.postgresql.org/pgsql-hackers/2008-07/msg01420.php Could it be the implementation we need for multiplexing signals from one backend some others? Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Multiplexing SUGUSR1
Dimitri Fontaine escribió: Le mardi 09 décembre 2008, Tom Lane a écrit : I think we need something closer to the postmaster signal multiplexing mechanism, wherein there is a dedicated shared memory area of static layout that holds the signaling flags. And it needs to be driven off of knowing the target's PID, not anything else. ...this makes me recall IMessage Queues from Postgres-R, reworked by Markus to follow your advices about postmaster and shared memory. http://archives.postgresql.org/pgsql-hackers/2008-07/msg01420.php Could it be the implementation we need for multiplexing signals from one backend some others? No, the signalling needed here is far simpler than Markus' IMessage stuff. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] WIP: default values for function parameters
How would a user recognise which of these are legal operator names? Incidentally -- EDB selling Oracle compatibility may put me in a questionable position here -- the more Oracle incompatibilities in stock Postgres the better for us. But afaik we don't emulate = anyways so that hardly matters. If anything it shows how unimportant it is to worry about being compatible on this front. I don't search compatibility - just searching any good syntax. And Oracle used wide used syntax - from Ada, Perl. - It isn't Oracle patent or Oracle design. And named params hasn't big sense without default params. So now is time for speaking about it. look on ADA http://archive.adaic.com/standards/83rat/html/ratl-08-03.html PL/pgSQL PL/SQL ADA so using '=' is only consistent and natural. And it is my goal. Regards Pavel Stehule -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] parallel restore vs. windows
Andrew Chernow wrote: Looks like the ArchiveHandle variable 'AH' and the TocEntry 'next_work_item' are not being deep copied at line 315 of your patch, where you prepare the RestoreArgs struct for the thread. Every thread is accessing and possibly updating the members of these structs that need to be deep copied. Each thread deals with a different TocEntry, which no other thread deals with, so there should be no need to clone it, I believe. Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). I am aware that there are some minor memory leaks, which I will remedy. cheers andrew -- 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 1202
As stuff matures and becomes indispensable we could consider moving it to the regular EXPLAIN or implement some way to specify precisely which data the user wants. Or just say XML/table data/whatever will solve the problem for us. I think some way to specify precisely which data the user wants is the way to go. The amount of data that there is to be printed is only going to continue to increase. If the only toggle is a boolean flag to display ALL or NONE of it, then every time someone proposes a new type of output, we're going to argue about whether it's useful enough to be worth the display real estate. I'm not sure what the best way is though. I don't think continuing to add keywords between EXPLAIN and the start of the query is very scalable. Putting parentheses around the option list seems like it might eliminate a lot of grammar headaches: EXPLAIN (option, option, option...) SELECT ... ...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] Multiplexing SUGUSR1
Heikki Linnakangas [EMAIL PROTECTED] writes: I'm surprised you feel that way. You suggested earlier (http://archives.postgresql.org/message-id/[EMAIL PROTECTED]) that a solution that only works for processes attached to shared memory would probably suffice for now. Well, I wasn't complaining about the dependence on being attached to shared memory. What I'm complaining about is the dependence on the rather complex PGPROC data structure. That seems hard, considering that we also want it to work without locking. Hmm, I presume we can use spinlocks in a signal handler? Perhaps some sort of a hash table protected by a spinlock would work. No, locks are right out if the postmaster is supposed to be able to use it. What I was thinking of is a simple linear array of PIDs and sig_atomic_t flags. The slots could be assigned on the basis of backendid, but callers trying to send a signal would have to scan the array looking for the matching PID. (This doesn't seem outlandishly expensive considering that one is about to do a kernel call anyway. You might be able to save a few cycles by having the PID array separate from the flag array, which should improve the cache friendliness of the scan.) Also, for those callers who do have access to a PGPROC, there could be a separate entry point that passes backendid instead of PID to eliminate the search. 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] cvs head initdb hangs on unixware
On Tue, 9 Dec 2008, Tom Lane wrote: Date: Tue, 09 Dec 2008 09:23:06 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Linnakangas [EMAIL PROTECTED], Zdenek Kotala [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] cvs head initdb hangs on unixware [EMAIL PROTECTED] writes: Guess what! with the fprintf .. descending node... in place, everything goes well. The optimizer definitly does something weird along the definition/assignement of leftok/rightok.. Hmm, so the problem is in that second loop. The trick is to pick some reasonably non-ugly code change that makes the problem go away. The first thing I'd try is to get rid of the overly cute optimization int rightnodeno = leftnodeno + 1; and make it just read int rightnodeno = rightchild(nodeno); If that doesn't work, we might try refactoring the code enough to get rid of the goto, but that looks a little bit tedious. regards, tom lane I tried that and moving leftok,rightok declaration outside the loop, and refactor the assignement code of leftok, rightok . nothing worked! Regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) -- 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 1202
I'm not sure what the best way is though. I don't think continuing to add keywords between EXPLAIN and the start of the query is very scalable. Putting parentheses around the option list seems like it might eliminate a lot of grammar headaches: Do you think it is required to invent special grammar just for presentation purposes? I guess database should not deal with presentation. Provided explain retuns table, it is up to the client to do the formatting. I do not believe it makes sense creating several different explain outputs, and redo all the work in 8.5. It still could make sense having several options for explain if that would result in *different instrumentation *(e.g. explain vs explain analyze). Regards, Vladimir Sitnikov
Re: [HACKERS] cvs head initdb hangs on unixware
Would it be reasonable to turn of optimization for this file? Ken On Tue, Dec 09, 2008 at 05:47:47PM +0100, [EMAIL PROTECTED] wrote: On Tue, 9 Dec 2008, Tom Lane wrote: Date: Tue, 09 Dec 2008 09:23:06 -0500 From: Tom Lane [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Heikki Linnakangas [EMAIL PROTECTED], Zdenek Kotala [EMAIL PROTECTED], pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] cvs head initdb hangs on unixware [EMAIL PROTECTED] writes: Guess what! with the fprintf .. descending node... in place, everything goes well. The optimizer definitly does something weird along the definition/assignement of leftok/rightok.. Hmm, so the problem is in that second loop. The trick is to pick some reasonably non-ugly code change that makes the problem go away. The first thing I'd try is to get rid of the overly cute optimization int rightnodeno = leftnodeno + 1; and make it just read int rightnodeno = rightchild(nodeno); If that doesn't work, we might try refactoring the code enough to get rid of the goto, but that looks a little bit tedious. regards, tom lane I tried that and moving leftok,rightok declaration outside the loop, and refactor the assignement code of leftok, rightok . nothing worked! Regards, -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] cvs head initdb hangs on unixware
[EMAIL PROTECTED] writes: On Tue, 9 Dec 2008, Tom Lane wrote: Hmm, so the problem is in that second loop. The trick is to pick some reasonably non-ugly code change that makes the problem go away. I tried that and moving leftok,rightok declaration outside the loop, and refactor the assignement code of leftok, rightok . nothing worked! I was afraid of that. We'd need to look at the assembly code to be sure (can you provide it?), but what I bet is happening is that the compiler is looking at the leftnodeno/rightnodeno computations and thinking it can optimize those by a strength-reduction method, failing to notice that the loop isn't a simple scan on nodeno. Now in that regard the logic isn't very much different from a binary search, which we have lots of and those have always worked. So I'm back to the theory that the goto inside the inner loop is probably contributing to the confusion somehow. 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] parallel restore vs. windows
Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). Is it okay to clone this from within the thread? The reopen() appears to mess with AH-FH, which mutltiple threads are calling fclose on. The second thread is going to fail and the first fclose() will close the main threads handle. + #ifndef WIN32 + if (fclose(AH-FH) != 0) + die_horribly(AH, modulename, could not close archive file: %s\n, +strerror(errno)); + #else How are things failing? Core dump, maybe you are seeing the above error? The non-windows path is safe from this because a) it never does an fclose and b) its a fork and has its own copy of the FH. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Parser - keyword cathegory
Hello, I'm writing my bachelor thesis and I can't find anywhere what exactly means the third parameter in ScanKeyword in pgsql/src/backend/parser/keywords.c - specificly UNRESERVED_KEYWORD, RESERVED_KEYWORD, TYPE_FUNC_NAME_KEYWORD, COL_NAME_KEYWORD. Could someone explain it to me? Thank you Kind regards Radek Strnad
Re: [HACKERS] WIP: default values for function parameters
Pavel Stehule [EMAIL PROTECTED] writes: PL/pgSQL PL/SQL ADA so using '=' is only consistent and natural. And it is my goal. [ shrug... ] Don't be too surprised when the patch gets rejected. Oracle compatibility is nice when we can get it, but we aren't going to break existing behavior for 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
Re: [HACKERS] contrib/pg_stat_statements 1202
On Tue, Dec 9, 2008 at 01:20, Greg Smith [EMAIL PROTECTED] wrote: On Sun, 7 Dec 2008, Alex Hunsaker wrote: (dual core machine, --enable-debug, --enable-cassert build) pgbench -c 2 -T60 -n -f test.sql HEAD: tps = 9.674423 PATCH: tps = 9.695784 Two general suggestions here, not specific to this patch: While it's good to do most testing with debug and cassert turned on, you shouldn't report performance results with those two flags enabled. What if the patch has some large amount of overhead that only shows up when compiled with debug or asserts on? You'd end up reporting a performance loss that doesn't actually exist in a real build. Unfortunately, the only way to get good performance results is to have a parallel build done with those off, in addition to the debug/assert one used to catch bugs. Right, which is part of the reason I noted it was a cassert build. The above pgbench is executing less than 600 actual tests (60 seconds @ 9.7TPS). That seems a bit short to me. If you sorted out the above and run this again, it would be good to let pgbench run for a lot longer than 1 minute, to see if the results show some more significant difference. With this few TPS, it would be nice to let that run for 30 minutes or more if you can find some time to schedule that. Ok thats useful to know as well, thanks! (ill go re-run them) -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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 1202
On Mon, Dec 8, 2008 at 23:28, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Alex Hunsaker [EMAIL PROTECTED] wrote: I was assigned to review this. Thanks for your reviewing. I assume that the basic concepts are ok and focus of discussion is in: - New counters in struct Instrumentation. (buffer usage and CPU usage) - Should EXPLAIN ANALYZE show those counters. Right, I would split out your next patch in 3 parts: the hooks you need, contrib module and the new counters. I think I saw older versions of the patch that did this... just got lost for this version? Performance review HEAD: tps = 9.674423 PATCH: tps = 9.695784 If it claims to improve performance, does it? Does it slow down other things? The patch should not slow down normal use if you don't use pg_stat_statements module, but it might slow down EXPLAIN ANALYZE because some fields are added in struct Instrumentation and they are counted up per tuple in EXPLAIN ANALYZE. Err yes sorry I was just following http://wiki.postgresql.org/wiki/Reviewing_a_Patch, those two did not seem pertainant so I did not answer them. -- 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] parallel restore vs. windows
Andrew Chernow wrote: Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). Is it okay to clone this from within the thread? I don't see why not. The reopen() appears to mess with AH-FH, which mutltiple threads are calling fclose on. The second thread is going to fail and the first fclose() will close the main threads handle. + #ifndef WIN32 + if (fclose(AH-FH) != 0) + die_horribly(AH, modulename, could not close archive file: %s\n, + strerror(errno)); + #else How are things failing? Core dump, maybe you are seeing the above error? The non-windows path is safe from this because a) it never does an fclose and b) its a fork and has its own copy of the FH. No, as this fragment shows, fclose() is NOT called on Windows. The program dies with a nasty dialog box when restoring a dump of the regression database after the second COPY thread disconnects. cheers andrew -- 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 1202
Yes this is one reasonable option, as is the idea of using XML or a table and making it the client's problem. Neither are going to happen for this release I think. And in any case it will always be useful to have an option to print all the available information anyways so we make as well do that with verbose. -- Greg On 9 Dec 2008, at 16:35, Robert Haas [EMAIL PROTECTED] wrote: As stuff matures and becomes indispensable we could consider moving it to the regular EXPLAIN or implement some way to specify precisely which data the user wants. Or just say XML/table data/whatever will solve the problem for us. I think some way to specify precisely which data the user wants is the way to go. The amount of data that there is to be printed is only going to continue to increase. If the only toggle is a boolean flag to display ALL or NONE of it, then every time someone proposes a new type of output, we're going to argue about whether it's useful enough to be worth the display real estate. I'm not sure what the best way is though. I don't think continuing to add keywords between EXPLAIN and the start of the query is very scalable. Putting parentheses around the option list seems like it might eliminate a lot of grammar headaches: EXPLAIN (option, option, option...) SELECT ... ...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] parallel restore vs. windows
Andrew Dunstan wrote: No, as this fragment shows, fclose() is NOT called on Windows. Oooppps. I'm the village idiot today. The program dies with a nasty dialog box when restoring a dump of the regression database after the second COPY thread disconnects. I'll poke around but apparently I need food :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] contrib/pg_stat_statements 1202
On Tue, Dec 9, 2008 at 8:53 PM, Robert Haas [EMAIL PROTECTED] wrote: On Tue, Dec 9, 2008 at 12:44 PM, Greg Stark [EMAIL PROTECTED] wrote: Yes this is one reasonable option, as is the idea of using XML or a table and making it the client's problem. Neither are going to happen for this release I think. Agreed. I 100% agree with that point. Thus I suggest output additional information into explain analyze since: 1) it will require minimal code change 2) it will be consistent with previous behaviour 3) looks like a natural EXPLAIN's feature improvement 4) will be anyway changed when table for explain will come And in any case it will always be useful to have an option to print all the available information anyways so we make as well do that with verbose. Sounds very nice. Can I ask my question once again? Why don't you want to make print all the info the default output format? As long as it comes to pgsql-performance, they used to recommend: please, provide EXPLAIN ANALYZE, and not just EXPLAIN. If the default output format is not changed in 8.4, this will transform into please, provide EXPLAIN ANALYZE VERBOSE, not just EXPLAIN ANALYZE or EXPLAIN. Do you really want that? Regards, Vladimir Sitnikov
Re: [HACKERS] cvs head initdb hangs on unixware
[EMAIL PROTECTED] writes:
FWIW, I have attached the 2 generated .s. Someone with knowledge of asm
may want to have a look..
Hmm. It looks to me like the compiler is getting confused by the
interaction between nodeno, leftnodeno, and rightnodeno. Try this
patch to see if it gets around it. (This is a tad better anyway
since it avoids examining the right child if not needed.)
regards, tom lane
Index: fsmpage.c
===
RCS file: /cvsroot/pgsql/src/backend/storage/freespace/fsmpage.c,v
retrieving revision 1.2
diff -c -r1.2 fsmpage.c
*** fsmpage.c 7 Oct 2008 21:10:11 - 1.2
--- fsmpage.c 9 Dec 2008 18:18:53 -
***
*** 243,259
*/
while (nodeno NonLeafNodesPerPage)
{
! int leftnodeno = leftchild(nodeno);
! int rightnodeno = leftnodeno + 1;
! bool leftok = (leftnodeno NodesPerPage)
! (fsmpage-fp_nodes[leftnodeno] = minvalue);
! bool rightok = (rightnodeno NodesPerPage)
! (fsmpage-fp_nodes[rightnodeno] = minvalue);
!
! if (leftok)
! nodeno = leftnodeno;
! else if (rightok)
! nodeno = rightnodeno;
else
{
/*
--- 243,262
*/
while (nodeno NonLeafNodesPerPage)
{
! int childnodeno = leftchild(nodeno);
!
! if (childnodeno NodesPerPage
! fsmpage-fp_nodes[childnodeno] = minvalue)
! {
! nodeno = childnodeno;
! continue;
! }
! childnodeno++; /* point to right child */
! if (childnodeno NodesPerPage
! fsmpage-fp_nodes[childnodeno] = minvalue)
! {
! nodeno = childnodeno;
! }
else
{
/*
--
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 1202
On Tue, Dec 9, 2008 at 12:44 PM, Greg Stark [EMAIL PROTECTED] wrote: Yes this is one reasonable option, as is the idea of using XML or a table and making it the client's problem. Neither are going to happen for this release I think. Agreed. And in any case it will always be useful to have an option to print all the available information anyways so we make as well do that with verbose. Sounds very nice. ...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] parallel restore vs. windows
Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). Is it okay to clone this from within the thread? I don't see why not. Because another thread may be modifying the memory you are trying to clone. If no one modifies the formatData struct, then why is it being deep copied to begin with. The program dies with a nasty dialog box when restoring a dump of the regression database after the second COPY thread disconnects. Sounds like the friendly and helpful GPF Dialog (General Protection Fault). This is a core dump which strongly suggests your threads are trampling over one another. Its possible that a couple threads get fired off but upon the first thread completion, something !(deep_copied) is freed/modified ... bang-bang your dead :o I tried to find this, but haven't yet. Maybe do a full deep copy in the main thread and comment out any in-thread deep copying. I wonder if that would work with no other changes. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] WIP: default values for function parameters
2008/12/9 Tom Lane [EMAIL PROTECTED]: Pavel Stehule [EMAIL PROTECTED] writes: PL/pgSQL PL/SQL ADA so using '=' is only consistent and natural. And it is my goal. [ shrug... ] Don't be too surprised when the patch gets rejected. Oracle compatibility is nice when we can get it, but we aren't going to break existing behavior for it. I believe to GUC should be a solution - I am don't understand your argument (wrong historic implementation isn't strong argument*), so .. I am not hurry, and maybe somebody will come with less controversal solution or beter solution, maybe not. Actually - variadic functions and defaults are significant step to forward and will carry comfort to application and library programmers. And I thing so named params or argument's metadata is logical next step. I should to believe so implementation will be in conformance with current standard and with ideas of standard. I invite any ideas, and I will diskus about it with respect to ADA (as origin lot of SQL construct) and, ofcourse, standard. Really - my goal isn't Oracle compatibility (it's only one efect, because Oracle use well syntax). I am primary working on libraries and external modules - and my work (variadic fce, defaults) started on JSON support. Is nice on PostgreSQL, so every feature should be customised - own objects, operators, agregates - so I would to write similar functions (with same user comfort) to SQL/XML function (without parser's patching) - it isn't possible now. best regards Pavel Stehule p.s. you can see on cvs - there are some people that develops or use orafce much more hard then me * a) this feature should be disabled in default b) should be conditional compiled c) with two, three hooks and some small changes should be implemented as external (contrib) module ~ the most worst variant 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 1202
On Sun, Dec 7, 2008 at 19:13, Alex Hunsaker [EMAIL PROTECTED] wrote: On Tue, Dec 2, 2008 at 02:35, ITAGAKI Takahiro [EMAIL PROTECTED] wrote: Here is an update version of contrib/pg_stat_statements. Hello again! I was assigned to review this. ... Some other things I accidentally left out. #define GUCNAME(name) (statistics. name) Why statistics? Would not something like stat_statements make more sense? Statistics seems fairly arbitrary... Also per the /* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */ Maybe it should be configurable, personally I would want something like # of calls / time. Mainly because I don't for instance really care that my backups get tracked but would be more interested in the things that get called most often that also take the longest. (aka the most bang for the buck, as far as optimizing those goes...) ? -- 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] parallel restore vs. windows
Andrew Chernow wrote: Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). Is it okay to clone this from within the thread? I don't see why not. Because another thread may be modifying the memory you are trying to clone. If no one modifies the formatData struct, then why is it being deep copied to begin with. The program dies with a nasty dialog box when restoring a dump of the regression database after the second COPY thread disconnects. Sounds like the friendly and helpful GPF Dialog (General Protection Fault). This is a core dump which strongly suggests your threads are trampling over one another. Its possible that a couple threads get fired off but upon the first thread completion, something !(deep_copied) is freed/modified ... bang-bang your dead :o I tried to find this, but haven't yet. Maybe do a full deep copy in the main thread and comment out any in-thread deep copying. I wonder if that would work with no other changes. I'll try. It's unfortunately not as simple as it sounds, because of the way the abstractions are arranged. I can't count the number of times I have had to stop and try to clear my head while working on this code. Thanks for the suggestion. cheers andrew -- 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] Quick patch: Display sequence owner
On Tue, Dec 9, 2008 at 10:46 AM, Josh Williams [EMAIL PROTECTED] wrote: On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote: I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 That makes more sense, though isn't that a little repetitive when default nextval(...) is visible immediately above it? actually, when a try your patch i have to look at the code to find where you put such information... i tried \dt first... Doesn't guarantee the sequence is owned by the table of course, but I'd imagine to most people it'd just be noise. Could see it being shown in the verbose version, \d+ foo.bar. that's exactly why we want the aditional info... the idea of putting it on \d+ doesn't sounds too bad... to me at least... For the sequences themselves, it'd be nice to show somewhere, at least for tracking down stray sequences and identifying relationships. in \ds maybe -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] SSL BIO wrappers
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Attached patch replaces the SSL BIO wrapper code we have now, with one that directly calls the send() and recv() functions instead. THis means that they get passed through the rewrite macros to our internal functions on Win32, and I think this will fix some of the strange errors that seem to be platform specific there (there are some really hard to reproduce bug reports around that). Hmm. Basically what this is doing is exactly what the comment says we didn't want to do, namely copy-and-paste the implementations of OpenSSL's socket BIO functions. How stable is that code? If the functions haven't changed textually in a long time (at least across all the OpenSSL versions we claim to support) then maybe it's okay. The logic in it is identical to the original import of code in OpenSSL. It originally had #ifdefs around how the BIO interface worked. That was tidied up in a commit back in 2001. I think it's fair to say it's been pretty stable. I don't read the comment as saying that, fwiw. It just says we may eventually need to do what I did now, but for other reasons. Do you have a comment around the should we prepare for read even though it's a write part? //Magnus -- 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] parallel restore vs. windows
Andrew Dunstan wrote: Andrew Chernow wrote: Parts of AH need deep cloning, notably the formatData member, which is done in _ReopenArchive(). Is it okay to clone this from within the thread? I don't see why not. Because another thread may be modifying the memory you are trying to clone. If no one modifies the formatData struct, then why is it being deep copied to begin with. The program dies with a nasty dialog box when restoring a dump of the regression database after the second COPY thread disconnects. Sounds like the friendly and helpful GPF Dialog (General Protection Fault). This is a core dump which strongly suggests your threads are trampling over one another. Its possible that a couple threads get fired off but upon the first thread completion, something !(deep_copied) is freed/modified ... bang-bang your dead :o I tried to find this, but haven't yet. Maybe do a full deep copy in the main thread and comment out any in-thread deep copying. I wonder if that would work with no other changes. I'll try. It's unfortunately not as simple as it sounds, because of the way the abstractions are arranged. I can't count the number of times I have had to stop and try to clear my head while working on this code. That's what killed me when I tried to review that stuff and figure it out. Does that indicate that the abstractions are bad and should be changed, or just that there's no reasonably way to make the abstractions both make sense for the internal API itself *and* for being threadsafe? //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] syntax for reaching into records, specifically ts_stat results
Dear Postgres Folk,
In working with tsvectors (added in 8.3), I've come to a place where
my syntax-fu has failed me. I've resorted to turning a result set of
records into strings so that I can regexp out the record fields I
need, rather than access them directly, as I'm sure it's possible to
do with the right syntactic formulation. Although my solution works,
I'm sure it's much less efficient than it could be, and hope someone
on the list can help do this the right way.
Basically, I would like to transpose a series of tsvectors (one per
row) into columns. E.g., as tsvects, I have this:
test=# select * from tsvects;
sentence_id |tsvect
-+--
1 | 'fox':3 'brown':2 'quick':1
2 | 'lazi':1 'eleph':3 'green':2
Instead I want this:
sentence_id | word | freq
-+---+--
1 | fox | 1
1 | brown | 1
1 | quick | 1
2 | lazi | 1
2 | eleph | 1
2 | green | 1
I am able to generate this with the following view, but the problem is
that to create it, I must first cast the ts_stat results to a string,
and then regexp out the pertinent pieces:
create or replace view words as
select sentence_id,
substring(stat from '^\\(([^,]+),') as word,
substring(stat from ',([^,]+)\\)$') as freq
from (select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id
= ' ||
tsvects.sentence_id)::text as stat
from tsvects
) as foo;
It seems like there should be a way to access fields in the records
returned from ts_stat directly, but I can't figure out how. Here's
the result of the subquery:
test=# select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id
= ' ||
tsvects.sentence_id)::text as stat
from tsvects;
sentence_id |stat
-+-
1 | (fox,1,1)
1 | (brown,1,1)
1 | (quick,1,1)
2 | (lazi,1,1)
2 | (eleph,1,1)
2 | (green,1,1)
(6 rows)
If I try to get at the elements (which I believe are named 'word',
'ndoc', 'nentry'), I get a variety of syntax errors:
test=# select sentence_id,
test-#stat['word'],
test-#stat['nentry']
test-# from (select sentence_id,
test(#ts_stat('select tsvect from tsvects where
sentence_id = ' ||
test(# tsvects.sentence_id) as stat
test(# from tsvects
test(#) as foo;
ERROR: cannot subscript type record because it is not an array
If I say stat.word (instead of subscripting), I get 'missing FROM-
clause entry for table stat'. If I say foo.stat.word, I get
'ERROR: schema foo does not exist'.
Any ideas on how to get into these records with resorting to text
parsing?
Thanks,
Dan
--
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] parallel restore vs. windows
Magnus Hagander [EMAIL PROTECTED] writes: Andrew Dunstan wrote: I'll try. It's unfortunately not as simple as it sounds, because of the way the abstractions are arranged. I can't count the number of times I have had to stop and try to clear my head while working on this code. That's what killed me when I tried to review that stuff and figure it out. Does that indicate that the abstractions are bad and should be changed, or just that there's no reasonably way to make the abstractions both make sense for the internal API itself *and* for being threadsafe? I think pretty much everybody except Philip Warner has found the stuff around the TOC data structure and the archiver API to be confusing. I'm not immediately sure about a better design though, at least not if you don't want to duplicate a lot of code between the plain pg_dump and the pg_dump/pg_restore cases. I don't see that this has much of anything to do with thread safety, however --- it's just a matter of too many layers of indirection IMHO. 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] syntax for reaching into records, specifically ts_stat results
try
select * from ts_stat()
btw, performance of ts_stat() was greatly improved in 8.4.
Oleg
On Tue, 9 Dec 2008, Dan Chak wrote:
Dear Postgres Folk,
In working with tsvectors (added in 8.3), I've come to a place where my
syntax-fu has failed me. I've resorted to turning a result set of records
into strings so that I can regexp out the record fields I need, rather than
access them directly, as I'm sure it's possible to do with the right
syntactic formulation. Although my solution works, I'm sure it's much less
efficient than it could be, and hope someone on the list can help do this the
right way.
Basically, I would like to transpose a series of tsvectors (one per row) into
columns. E.g., as tsvects, I have this:
test=# select * from tsvects;
sentence_id |tsvect
-+--
1 | 'fox':3 'brown':2 'quick':1
2 | 'lazi':1 'eleph':3 'green':2
Instead I want this:
sentence_id | word | freq
-+---+--
1 | fox | 1
1 | brown | 1
1 | quick | 1
2 | lazi | 1
2 | eleph | 1
2 | green | 1
I am able to generate this with the following view, but the problem is that
to create it, I must first cast the ts_stat results to a string, and then
regexp out the pertinent pieces:
create or replace view words as
select sentence_id,
substring(stat from '^\\(([^,]+),') as word,
substring(stat from ',([^,]+)\\)$') as freq
from (select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects
) as foo;
It seems like there should be a way to access fields in the records returned
from ts_stat directly, but I can't figure out how. Here's the result of the
subquery:
test=# select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects;
sentence_id |stat
-+-
1 | (fox,1,1)
1 | (brown,1,1)
1 | (quick,1,1)
2 | (lazi,1,1)
2 | (eleph,1,1)
2 | (green,1,1)
(6 rows)
If I try to get at the elements (which I believe are named 'word', 'ndoc',
'nentry'), I get a variety of syntax errors:
test=# select sentence_id,
test-#stat['word'],
test-#stat['nentry']
test-# from (select sentence_id,
test(#ts_stat('select tsvect from tsvects where sentence_id =
' ||
test(# tsvects.sentence_id) as stat
test(# from tsvects
test(#) as foo;
ERROR: cannot subscript type record because it is not an array
If I say stat.word (instead of subscripting), I get 'missing FROM-clause
entry for table stat'. If I say foo.stat.word, I get 'ERROR: schema foo
does not exist'.
Any ideas on how to get into these records with resorting to text parsing?
Thanks,
Dan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] syntax for reaching into records, specifically ts_stat results
Dan Chak [EMAIL PROTECTED] writes: If I say stat.word (instead of subscripting), I get 'missing FROM- clause entry for table stat'. If I say foo.stat.word, I get 'ERROR: schema foo does not exist'. I think the syntax you need is (stat).word etc. See Field Selection here: http://www.postgresql.org/docs/8.3/static/sql-expressions.html#AEN1679 The reason for the parens is exactly to distinguish whether the leading word is a table or column name. 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] syntax for reaching into records, specifically ts_stat results
Oleg,
This syntax works fine until I also want to get the sentence_id
column in there as well, so that I can differentiate one set of
ts_stat results from another. With the syntax where ts_stat is
treated like a table, it isn't possible to run ts_stat separately on
multiple tsvectors as I'm doing below.
Is there some generic record access syntax that I can use?
Thanks,
Dan
On Dec 9, 2008, at 3:04 PM, Oleg Bartunov wrote:
try select * from ts_stat()
btw, performance of ts_stat() was greatly improved in 8.4.
Oleg
On Tue, 9 Dec 2008, Dan Chak wrote:
Dear Postgres Folk,
In working with tsvectors (added in 8.3), I've come to a place
where my syntax-fu has failed me. I've resorted to turning a
result set of records into strings so that I can regexp out the
record fields I need, rather than access them directly, as I'm sure
it's possible to do with the right syntactic formulation. Although
my solution works, I'm sure it's much less efficient than it could
be, and hope someone on the list can help do this the right way.
Basically, I would like to transpose a series of tsvectors (one per
row) into columns. E.g., as tsvects, I have this:
test=# select * from tsvects;
sentence_id |tsvect
-+--
1 | 'fox':3 'brown':2 'quick':1
2 | 'lazi':1 'eleph':3 'green':2
Instead I want this:
sentence_id | word | freq
-+---+--
1 | fox | 1
1 | brown | 1
1 | quick | 1
2 | lazi | 1
2 | eleph | 1
2 | green | 1
I am able to generate this with the following view, but the problem
is that to create it, I must first cast the ts_stat results to a
string, and then regexp out the pertinent pieces:
create or replace view words as
select sentence_id,
substring(stat from '^\\(([^,]+),') as word,
substring(stat from ',([^,]+)\\)$') as freq
from (select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id =
' ||
tsvects.sentence_id)::text as stat
from tsvects
) as foo;
It seems like there should be a way to access fields in the records
returned from ts_stat directly, but I can't figure out how. Here's
the result of the subquery:
test=# select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id =
' ||
tsvects.sentence_id)::text as stat
from tsvects;
sentence_id |stat
-+-
1 | (fox,1,1)
1 | (brown,1,1)
1 | (quick,1,1)
2 | (lazi,1,1)
2 | (eleph,1,1)
2 | (green,1,1)
(6 rows)
If I try to get at the elements (which I believe are named 'word',
'ndoc', 'nentry'), I get a variety of syntax errors:
test=# select sentence_id,
test-#stat['word'],
test-#stat['nentry']
test-# from (select sentence_id,
test(#ts_stat('select tsvect from tsvects where
sentence_id = ' ||
test(# tsvects.sentence_id) as stat
test(# from tsvects
test(#) as foo;
ERROR: cannot subscript type record because it is not an array
If I say stat.word (instead of subscripting), I get 'missing FROM-
clause entry for table stat'. If I say foo.stat.word, I get
'ERROR: schema foo does not exist'.
Any ideas on how to get into these records with resorting to text
parsing?
Thanks,
Dan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] syntax for reaching into records, specifically ts_stat results
That works perfectly! Thanks, Dan On Dec 9, 2008, at 3:13 PM, Tom Lane wrote: Dan Chak [EMAIL PROTECTED] writes: If I say stat.word (instead of subscripting), I get 'missing FROM- clause entry for table stat'. If I say foo.stat.word, I get 'ERROR: schema foo does not exist'. I think the syntax you need is (stat).word etc. See Field Selection here: http://www.postgresql.org/docs/8.3/static/sql-expressions.html#AEN1679 The reason for the parens is exactly to distinguish whether the leading word is a table or column name. 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] Quick patch: Display sequence owner
Josh Williams wrote: On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote: I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 That makes more sense, though isn't that a little repetitive when default nextval(...) is visible immediately above it? I don't think that it is all that repetitive. It's not uncommon to see people creating sequences and assigning to default values, without setting the OWNED BY bits. It's good that this information is very visible. It's only a couple more lines in the common case anyway (if you want to save half of that overhead, make it a single line when there's a single sequence.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] syntax for reaching into records, specifically ts_stat results
On Tue, 9 Dec 2008, Dan Chak wrote:
Oleg,
This syntax works fine until I also want to get the sentence_id column in
there as well, so that I can differentiate one set of ts_stat results from
another. With the syntax where ts_stat is treated like a table, it isn't
possible to run ts_stat separately on multiple tsvectors as I'm doing below.
Is there some generic record access syntax that I can use?
write function
Thanks,
Dan
On Dec 9, 2008, at 3:04 PM, Oleg Bartunov wrote:
try select * from ts_stat()
btw, performance of ts_stat() was greatly improved in 8.4.
Oleg
On Tue, 9 Dec 2008, Dan Chak wrote:
Dear Postgres Folk,
In working with tsvectors (added in 8.3), I've come to a place where my
syntax-fu has failed me. I've resorted to turning a result set of records
into strings so that I can regexp out the record fields I need, rather
than access them directly, as I'm sure it's possible to do with the right
syntactic formulation. Although my solution works, I'm sure it's much
less efficient than it could be, and hope someone on the list can help do
this the right way.
Basically, I would like to transpose a series of tsvectors (one per row)
into columns. E.g., as tsvects, I have this:
test=# select * from tsvects;
sentence_id |tsvect
-+--
1 | 'fox':3 'brown':2 'quick':1
2 | 'lazi':1 'eleph':3 'green':2
Instead I want this:
sentence_id | word | freq
-+---+--
1 | fox | 1
1 | brown | 1
1 | quick | 1
2 | lazi | 1
2 | eleph | 1
2 | green | 1
I am able to generate this with the following view, but the problem is
that to create it, I must first cast the ts_stat results to a string, and
then regexp out the pertinent pieces:
create or replace view words as
select sentence_id,
substring(stat from '^\\(([^,]+),') as word,
substring(stat from ',([^,]+)\\)$') as freq
from (select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects
) as foo;
It seems like there should be a way to access fields in the records
returned from ts_stat directly, but I can't figure out how. Here's the
result of the subquery:
test=# select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects;
sentence_id |stat
-+-
1 | (fox,1,1)
1 | (brown,1,1)
1 | (quick,1,1)
2 | (lazi,1,1)
2 | (eleph,1,1)
2 | (green,1,1)
(6 rows)
If I try to get at the elements (which I believe are named 'word', 'ndoc',
'nentry'), I get a variety of syntax errors:
test=# select sentence_id,
test-#stat['word'],
test-#stat['nentry']
test-# from (select sentence_id,
test(#ts_stat('select tsvect from tsvects where
sentence_id = ' ||
test(# tsvects.sentence_id) as stat
test(# from tsvects
test(#) as foo;
ERROR: cannot subscript type record because it is not an array
If I say stat.word (instead of subscripting), I get 'missing FROM-clause
entry for table stat'. If I say foo.stat.word, I get 'ERROR: schema
foo does not exist'.
Any ideas on how to get into these records with resorting to text parsing?
Thanks,
Dan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] SQL/MED compatible connection manager
Peter Eisentraut wrote: Attached is my current patch after surgery. I have mainly worked on making naming better and more consistent. Thanks. Problem: You have implemented foreign-data wrappers and foreign servers as schema-qualified objects, but the standard has them outside schemas, qualified only optionally by catalogs (a.k.a. databases). I think that should be fixed. Darn. At least it is a lot easier to root out the schema support than to add it ... Will look into it. regards, Martin -- 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] syntax for reaching into records, specifically ts_stat results
ok, here is a function ( credits to Teodor )
CREATE OR REPLACE FUNCTION ts_stat(tsvector, OUT word text, OUT ndoc
integer, OUT nentry integer)
RETURNS SETOF record AS
$$
SELECT ts_stat('SELECT ' || quote_literal( $1::text ) || '::tsvector');
$$ LANGUAGE SQL RETURNS NULL ON NULL INPUT IMMUTABLE;
use it like
select id, (ts_stat(fts)).* from apod where id=1;
Oleg
On Tue, 9 Dec 2008, Oleg Bartunov wrote:
On Tue, 9 Dec 2008, Dan Chak wrote:
Oleg,
This syntax works fine until I also want to get the sentence_id column in
there as well, so that I can differentiate one set of ts_stat results from
another. With the syntax where ts_stat is treated like a table, it isn't
possible to run ts_stat separately on multiple tsvectors as I'm doing
below.
Is there some generic record access syntax that I can use?
write function
Thanks,
Dan
On Dec 9, 2008, at 3:04 PM, Oleg Bartunov wrote:
try select * from ts_stat()
btw, performance of ts_stat() was greatly improved in 8.4.
Oleg
On Tue, 9 Dec 2008, Dan Chak wrote:
Dear Postgres Folk,
In working with tsvectors (added in 8.3), I've come to a place where my
syntax-fu has failed me. I've resorted to turning a result set of
records into strings so that I can regexp out the record fields I need,
rather than access them directly, as I'm sure it's possible to do with
the right syntactic formulation. Although my solution works, I'm sure
it's much less efficient than it could be, and hope someone on the list
can help do this the right way.
Basically, I would like to transpose a series of tsvectors (one per row)
into columns. E.g., as tsvects, I have this:
test=# select * from tsvects;
sentence_id |tsvect
-+--
1 | 'fox':3 'brown':2 'quick':1
2 | 'lazi':1 'eleph':3 'green':2
Instead I want this:
sentence_id | word | freq
-+---+--
1 | fox | 1
1 | brown | 1
1 | quick | 1
2 | lazi | 1
2 | eleph | 1
2 | green | 1
I am able to generate this with the following view, but the problem is
that to create it, I must first cast the ts_stat results to a string, and
then regexp out the pertinent pieces:
create or replace view words as
select sentence_id,
substring(stat from '^\\(([^,]+),') as word,
substring(stat from ',([^,]+)\\)$') as freq
from (select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects
) as foo;
It seems like there should be a way to access fields in the records
returned from ts_stat directly, but I can't figure out how. Here's the
result of the subquery:
test=# select sentence_id,
ts_stat('select tsvect from tsvects where sentence_id = ' ||
tsvects.sentence_id)::text as stat
from tsvects;
sentence_id |stat
-+-
1 | (fox,1,1)
1 | (brown,1,1)
1 | (quick,1,1)
2 | (lazi,1,1)
2 | (eleph,1,1)
2 | (green,1,1)
(6 rows)
If I try to get at the elements (which I believe are named 'word',
'ndoc', 'nentry'), I get a variety of syntax errors:
test=# select sentence_id,
test-#stat['word'],
test-#stat['nentry']
test-# from (select sentence_id,
test(#ts_stat('select tsvect from tsvects where
sentence_id = ' ||
test(# tsvects.sentence_id) as stat
test(# from tsvects
test(#) as foo;
ERROR: cannot subscript type record because it is not an array
If I say stat.word (instead of subscripting), I get 'missing FROM-clause
entry for table stat'. If I say foo.stat.word, I get 'ERROR: schema
foo does not exist'.
Any ideas on how to get into these records with resorting to text
parsing?
Thanks,
Dan
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
Re: [HACKERS] parallel restore vs. windows
Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Andrew Dunstan wrote: I'll try. It's unfortunately not as simple as it sounds, because of the way the abstractions are arranged. I can't count the number of times I have had to stop and try to clear my head while working on this code. That's what killed me when I tried to review that stuff and figure it out. Does that indicate that the abstractions are bad and should be changed, or just that there's no reasonably way to make the abstractions both make sense for the internal API itself *and* for being threadsafe? I think pretty much everybody except Philip Warner has found the stuff around the TOC data structure and the archiver API to be confusing. I'm not immediately sure about a better design though, at least not if you don't want to duplicate a lot of code between the plain pg_dump and the pg_dump/pg_restore cases. I don't see that this has much of anything to do with thread safety, however --- it's just a matter of too many layers of indirection IMHO. It doesn't - but it makes it harder to find the issue I think :-( If it was reasonably easy, an API redesign might help that. But I haven't looked at all at the possibility of doing so, so I won't comment on if it's likely to be doable. //Magnus -- 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] SSL BIO wrappers
Magnus Hagander [EMAIL PROTECTED] writes: The logic in it is identical to the original import of code in OpenSSL. It originally had #ifdefs around how the BIO interface worked. That was tidied up in a commit back in 2001. I think it's fair to say it's been pretty stable. Fair enough. Do you have a comment around the should we prepare for read even though it's a write part? We shouldn't; in fact that would be quite wrong. 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] FOSDEM 2009 Call for Papers - deadline
Hello all, FOSDEM 2009 will take place february 7-8 2009 in Brussels, Belgium. We want to continue the great success from last year and again we have a booth, and a devroom together with the BSD groups. Please submit your talk(s) to [EMAIL PROTECTED] until 2009-01-02, include the topic and the length of the talk. You may choose between: - 50 minutes talk (~35 minutes talk + 15 minutes discussion) - 25 minutes talk (~15 minutes talk + 10 minutes discussion) - lightning talk (5 minutes, cut short) Every talk is welcome, from internal hacker discussion to real-world examples and presentations about new and shiny features. The talk committee consists of Gregory Stark, Koen Martens, Magnus Hagander and Andreas Scherbaum. More information are available at: http://wiki.postgresql.eu/wiki/FOSDEM_2009 Kind regards -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors -- 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] parallel restore vs. windows
Magnus Hagander wrote: Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: Andrew Dunstan wrote: I'll try. It's unfortunately not as simple as it sounds, because of the way the abstractions are arranged. I can't count the number of times I have had to stop and try to clear my head while working on this code. That's what killed me when I tried to review that stuff and figure it out. Does that indicate that the abstractions are bad and should be changed, or just that there's no reasonably way to make the abstractions both make sense for the internal API itself *and* for being threadsafe? I think pretty much everybody except Philip Warner has found the stuff around the TOC data structure and the archiver API to be confusing. I'm not immediately sure about a better design though, at least not if you don't want to duplicate a lot of code between the plain pg_dump and the pg_dump/pg_restore cases. I don't see that this has much of anything to do with thread safety, however --- it's just a matter of too many layers of indirection IMHO. It doesn't - but it makes it harder to find the issue I think :-( If it was reasonably easy, an API redesign might help that. But I haven't looked at all at the possibility of doing so, so I won't comment on if it's likely to be doable. //Magnus If it previously worked without threads, than in theory a deep copy of the thread_arg should fix the core dump; especially if the non-windows fork() method works with this patch. Maybe you can get away with only copying some of the members (trial-n-error), I don't think they are all being used in this context. Nothing should be copied from within the thread itself. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] parallel restore vs. windows
Tom Lane wrote: I think pretty much everybody except Philip Warner has found the stuff around the TOC data structure and the archiver API to be confusing. I'm not immediately sure about a better design though, at least not if you don't want to duplicate a lot of code between the plain pg_dump and the pg_dump/pg_restore cases. Here was I thinking it was more or less self-documenting and clear ;-). But, yes, it is complex, and I can still see no way to reduce the complexity. I should have some old notes on the code and am happy to expand them as much as necessary. If people want to nominate key areas of confusion, I will concentrate on those first. In terms of the current discussion, I am not sure I can help greatly; writing cross-platform thread code is non-trivial. One minor point: I noticed in early versions of the code that a global AH had been created -- it occurs to me that this could be problem. -- 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] parallel restore vs. windows
Philip Warner wrote: Tom Lane wrote: I think pretty much everybody except Philip Warner has found the stuff around the TOC data structure and the archiver API to be confusing. I'm not immediately sure about a better design though, at least not if you don't want to duplicate a lot of code between the plain pg_dump and the pg_dump/pg_restore cases. Here was I thinking it was more or less self-documenting and clear ;-). But, yes, it is complex, and I can still see no way to reduce the complexity. I should have some old notes on the code and am happy to expand them as much as necessary. If people want to nominate key areas of confusion, I will concentrate on those first. In terms of the current discussion, I am not sure I can help greatly; writing cross-platform thread code is non-trivial. One minor point: I noticed in early versions of the code that a global AH had been created -- it occurs to me that this could be problem. No, it's not. It's not used in any thread except the main thread. cheers andrew -- 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] Fwd: [PATCHES] Auto Partitioning Patch - WIP version 1
Hi all,
While I was trying to find the right place to add a new page on the
wiki, I found the document of Simon on partitioning requirements
(http://wiki.postgresql.org/wiki/Image:Partitioning_Requirements.pdf)
referenced from http://wiki.postgresql.org/wiki/Development_projects
I think this is a good base to start from. Should we convert the doc
into a wiki page or get the source for the doc and go from there?
I attach what I have come up with so far for the C trigger I was talking
about for efficient automatic auto-partitioning of inserts in child tables.
Emmanuel
Robert Haas wrote:
On Thu, Nov 27, 2008 at 11:09 AM, Emmanuel Cecchet [EMAIL PROTECTED] wrote:
I have been following that discussion very closely but it seems that we are
debating solutions without a good specification of the problem/requirements.
I would suggest that we collect all the partitioning requirements on a
dedicated Wiki page. There might not be a one size fits it all solution for
all requirements. We can also look at what other databases are proposing to
address these issues.
If we can prioritize features, that should also allow us to stage the
partitioning implementation.
This might be a good idea. Want to take a crack at it?
I have a prototype insert trigger in C that directly move inserts in a
master table to the appropriate child table (directly moving the tuple). Let
me know if anyone is interested.
Can't hurt to post it.
...Robert
--
Emmanuel Cecchet
FTO @ Frog Thinker
Open Source Development Consulting
--
Web: http://www.frogthinker.org
email: [EMAIL PROTECTED]
Skype: emmanuel_cecchet
### Eclipse Workspace Patch 1.0
#P Postgres-HEAD
Index: src/test/regress/regress.c
===
RCS file: /root/cvsrepo/pgsql/src/test/regress/regress.c,v
retrieving revision 1.71
diff -u -r1.71 regress.c
--- src/test/regress/regress.c 25 Mar 2008 22:42:46 - 1.71
+++ src/test/regress/regress.c 13 Nov 2008 06:11:08 -
@@ -10,6 +10,9 @@
#include utils/geo_decls.h /* includes math.h */
#include executor/executor.h /* For GetAttributeByName */
#include commands/sequence.h /* for nextval() */
+#include catalog/namespace.h
+#include executor/executor.h
+#include executor/tuptable.h
#define P_MAXDIG 12
#define LDELIM '('
@@ -732,3 +735,90 @@
*--walk = '\0';
PG_RETURN_CSTRING(result);
}
+
+
+/*
+ * Partition trigger test
+ *
+ * The trigger should be used this way:
+ * CREATE TRIGGER child_table_name
+BEFORE INSERT ON master_table
+FOR EACH ROW EXECUTE PROCEDURE partition_insert_trigger();
+ */
+
+extern Datum partition_insert_trigger(PG_FUNCTION_ARGS);
+
+PG_FUNCTION_INFO_V1(partition_insert_trigger);
+
+Datum
+partition_insert_trigger(PG_FUNCTION_ARGS)
+{
+TriggerData *trigdata = (TriggerData *) fcinfo-context;
+HeapTupletrigtuple= trigdata-tg_trigtuple;
+ char*child_table_name;
+ Relation child_table_relation;
+ Oid relation_id;
+
+/* make sure it's called as a trigger at all */
+if (!CALLED_AS_TRIGGER(fcinfo))
+elog(ERROR, partition_insert_trigger: not called by trigger manager);
+
+/* Sanity checks */
+if (!TRIGGER_FIRED_BY_INSERT(trigdata-tg_event) ||
!TRIGGER_FIRED_BEFORE(trigdata-tg_event))
+elog(ERROR, partition_insert_trigger: not called on insert before);
+
+// Child table name is either given as the unique parameter or it is the
name of the trigger
+if (trigdata-tg_trigger-tgnargs == 1)
+ child_table_name = trigdata-tg_trigger-tgargs[0];
+else
+ child_table_name = trigdata-tg_trigger-tgname;
+
+// Lookup the child relation
+relation_id = RelnameGetRelid(child_table_name);
+if (relation_id == InvalidOid)
+ elog(ERROR, partition_insert_trigger: Invalid child table %s,
child_table_name);
+child_table_relation = RelationIdGetRelation(relation_id);
+if (child_table_relation == NULL)
+ elog(ERROR, partition_insert_trigger: Failed to locate relation for
child table %s, child_table_name);
+
+{ // Check the constraints
+ TupleConstr *constr = child_table_relation-rd_att-constr;
+
+ if (constr-num_check 0)
+ {
+ ResultRelInfo *resultRelInfo;
+ TupleTableSlot *slot;
+ EState *estate= CreateExecutorState();
+
+ resultRelInfo = makeNode(ResultRelInfo);
+ resultRelInfo-ri_RangeTableIndex = 1; /* dummy */
+ resultRelInfo-ri_RelationDesc = child_table_relation;
+
+ estate-es_result_relations = resultRelInfo;
+ estate-es_num_result_relations = 1;
+ estate-es_result_relation_info = resultRelInfo;
+
+ /* Set up a tuple slot too */
+ slot = MakeSingleTupleTableSlot(trigdata-tg_relation-rd_att);
+ ExecStoreTuple(trigtuple, slot, InvalidBuffer, false);
+
+ if
[HACKERS] A question for the patch blooming filter
i have a view of code, and find a question.There is a variable named 'bloom_pruning set to be 'false' outside function 'bloom_filter_init' in file 'bloomfn.c', and there is a stataments if (bloom_pruning == false) return; at begin of the function bloom_filter_init. And i don't find any places which set variable 'bloom_pruning' to 'true', may i take it as code following the statments above in 'bloom_filter_init' never execute?
Re: [HACKERS] A question for the patch blooming filter
Hi, On Wed, Dec 10, 2008 at 10:02 AM, Unicron [EMAIL PROTECTED] wrote: i have a view of code, and find a question.There is a variable named 'bloom_pruning set to be 'false' outside function 'bloom_filter_init' in file 'bloomfn.c', and there is a stataments if (bloom_pruning == false) return; at begin of the function bloom_filter_init. And i don't find any places which set variable 'bloom_pruning' to 'true', may i take it as code following the statments above in 'bloom_filter_init' never execute? bloom_pruning is GUC, so it's set to true/false when postgresql.conf is parsed or SET command is executed. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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 1202
Tom Lane [EMAIL PROTECTED] wrote: Please split this into two separate patches that can be separately evaluated. Sure. I want to disucuss only where to add counters of buffer usage and cpu usage, or they should not be added. However, it seems to affect future of EXPLAIN ANALYZE, so we might also need to discuss about EXPLAIN. I assume we have 3 choices here: 1. Add those counters to struct Instrument. We can get statistics for each line in EXPLAIN ANALYZE, but it might have overhead to update counters. 2. Add those counters only to top instruments (one per query). We can get accumulated statistics for each query. It might be unsufficient for complex queries. 3. Should not add any counters. No changes to core, but usability of pg_stat_statement module would be very poor... Which should we take? or are there another idea? Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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 1202
Alex Hunsaker [EMAIL PROTECTED] wrote: #define GUCNAME(name) (statistics. name) Why statistics? Would not something like stat_statements make more sense? Statistics seems fairly arbitrary... Not to use duplicated statements words; variable names contains statements already. - stat_statements.max_statements - stat_statements.track_statements seem to be ugly for me, but avoiding arbitrariness might be more important. If there are agreements, I will to change the prefix. Also per the /* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */ Maybe it should be configurable, personally I would want something like # of calls / time. Mainly because I don't for instance really care that my backups get tracked but would be more interested in the things that get called most often that also take the longest. (aka the most bang for the buck, as far as optimizing those goes...) Configurability is better, but we need documentations of how to configure them and I have no clear idea for it. Also, we already have means for logging slow queries. We could use the logging for slow queries executed rarely and use the module queries executed many times. Excluding backup scripts is easy; You can create a database role for backup and disable statement-tracking for the user using ALTER ROLE. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Quick patch: Display sequence owner
On Tuesday 09 December 2008 15:49:17 Alvaro Herrera wrote: Josh Williams wrote: On Tue, 2008-12-09 at 09:32 -0500, Tom Lane wrote: I think the place that such information could most naturally be squeezed into psql's \d commands would be to add another type of footer information to \dt, eg Table foo.bar ... Indexes: bari ... Owned sequences: baz owned by col1 That makes more sense, though isn't that a little repetitive when default nextval(...) is visible immediately above it? I don't think that it is all that repetitive. It's not uncommon to see people creating sequences and assigning to default values, without setting the OWNED BY bits. It's good that this information is very visible. It's only a couple more lines in the common case anyway (if you want to save half of that overhead, make it a single line when there's a single sequence.) It feels like noise to me; showing indexes/triggers/constraints affect how you interact with a table, but whether a sequence is owned or not doesn't make a significant difference. Given we don't list other dependencies (views/functions/etc...) I'm not excited about adding this one. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.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] benchmarking the query planner
On Mon, Dec 8, 2008 at 10:24 AM, Gregory Stark [EMAIL PROTECTED] wrote: I tried a different query, trying to get quadratic growth and again failed. It The profiling results I sent the other day show an exactly-linear increase in the number of times eqjoinsel invokes FunctionCall2. Reading through the the eqjoinsel_inner loop in selfuncs.c beginning around line 2042, I think what is happening is this: since the two tables are really the same table, nvalues1 and nvalues2 are the same array, and therefore contain the same elements in the same order. As a result, for each i, we skip over the first i - 1 entries in nvalues2, which have already been matched, and then compare element i of nvalues1 to element i of nvalues2 and mark them both as matched. Although this is technically an O(n^2) algorithm, the constant is very low, because this code is Really Fast: if (hasmatch[j]) continue; To get observable quadratic behavior, I think you might need to construct two MCV arrays where all the values are the same, but the arrays are not in the same order. I believe they are sorted by frequency, so it might be sufficient to arrange things so that the N'th most common value in one table is the (statistics_target + 1 - N)'th most common value in the other. It might also help to use a function with a real slow comparison function (perhaps one intentionally constructed to be slow). ...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] PLUGINS Functionlity in Win32 build scripts
I've been assigned to review this patch, but I confess I'm a little murky on what problem it's trying to solve. Can you explain what I need to do to recreate the problem? In postgreSQL, Plugin modules should be installed in (Installation dir)lib/plugins to run properly. There is logic in src/makefiles/pgxs.mk for handling shared objects implemented as e.g. Modules variable that are installed in (Installation dir)/lib. There should be some way to handle plugins as well. We faced this issue during the integration of contrib module pldebugger ( http://pgfoundry.org/frs/?group_id=1000175 ) with the edb-postgresql code. pldebugger/Makefile handles plugins by itself, Unix/Linux don't mind it because it build through make. On windows, perl scripts are used to build the system that relies on Makefiles that uses pgxs.mk variables e.g Modules etc. It fails to build pldebugger that implements its own plugin build logic in its Makefile. Right now for us there is only one plugin module but in future there may be more.Instead of any workaround we come up to a better solution to handle plugins automatically by pgxs.mk as other shared objects are being handled on Unix and Windows. We added PLUGIN logic in pgxs.mk and windows perl build scripts so that there is no need to handle plugins separately by new contrib modules. I am a bit dubious about the idea of adding supposedly generic functionality to cater to a single client, but the bigger problem is that even after reading this I still don't really know what I'm supposed to be looking at. I think you're saying that pldebugger contains some code that could be made simpler and less easily broken if this patch were applied. Is that correct? If so, please tell me where to download the pldebugger code and which file(s) to look in for the code that could be improved. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
