Re: [HACKERS] Multicore builds on MSVC
On Fri, Jul 24, 2009 at 21:33, Dave Pagedp...@pgadmin.org wrote: On Fri, Jul 24, 2009 at 8:07 PM, Magnus Hagandermag...@hagander.net wrote: I'm going to apply this for HEAD. I'm considering backpatching as well, to speed up all build machines. Comments on that? Let's see how it goes in the BF for HEAD, and then backpatch if it looks good. I'm keen to get the potential speedup on 8.3 8.4. Applied to HEAD. -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Review: support for multiplexing SIGUSR1
Hi, Thanks for reviewing the patch! On Mon, Jul 27, 2009 at 2:43 AM, Tom Lanet...@sss.pgh.pa.us wrote: Neither of these changes seem like a good idea to me. The use of a spinlock renders the mechanism unsafe for use from the postmaster --- we do not wish the postmaster to risk getting stuck if the contents of shared memory have become corrupted, eg, there is a spinlock that looks taken. And you've completely mangled the concept of BackendId. MyBackendId is by definition the same as the index of the process's entry in the sinval ProcState array. This means that (1) storing it in the ProcState entry is silly, and (2) assigning values that don't correspond to an actual ProcState entry is dangerous. If we want to be able to signal processes that don't have a ProcState entry, it would be better to assign an independent index instead of overloading BackendId like this. OK, I'll change the mechanism to assign a ProcSignalSlot to a process, independently from ProcState, but similarly to ProcState: a process gets a ProcSignalSlot from newly-introduced freelist at startup, and returns it to freelist at exit. Since this assignment and return require the lock, I'll introduce a new LWLock for them. I'm not sure though whether we care about being able to signal such processes. It's certainly not necessary for catchup interrupts, but it might be for future applications of the mechanism. Do we have a clear idea of what the future applications are? Yes, I'm planning to use this mechanism for communication between a process which can generate the WAL records and a WAL sender process, in Synch Rep. Since not only a backend but also some auxiliary processes (e.g., bgwriter) can generate the WAL records, processes which don't have a ProcState need to receive the multiplexed signal, too. As for the locking issue, I'm inclined to just specify that uses of the mechanism must be such that receiving a signal that wasn't meant for you isn't fatal. This makes sense. I'll get rid of a spinlock from the patch and add the usage note as above. In the case of catchup interrupts the worst that can happen is you do a little bit of useless work. Are there any intended uses where it would be seriously bad to get a misdirected signal? In, at least, currently-intended use case (i.e., Synch Rep), such wrong signal is not fatal because it's only used as a hint. I agree with Jaime that the patch would be more credible if it covered more than one signal usage at the start --- these questions make it clear that the design can't happen in a vacuum without intended usages in mind. Umm... the patch should cover a notify interrupt which currently uses SIGUSR2? 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] Non-blocking communication between a frontend and a backend (pqcomm)
Hi, On Sun, Jul 26, 2009 at 6:42 AM, Robert Haasrobertmh...@gmail.com wrote: OK, I agree, I can't see what this is for either from the code that is here. I think I read a little more meaning into the title of the patch than was actually there. It seems like the appropriate thing to do is mark this returned with feedback, so I'm going to go do that. OK. I'll change and resubmit the patch with Synch Rep which uses it in the next CommitFest. 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] autogenerating headers bki stuff
On Sunday 26 July 2009 01:40:09 Tom Lane wrote: And it is going to cost us in places like how do we generate the fmgr lookup table. We rgrep the source tree for PG_FUNCTION_ARGS, extract the function name, and put them in a list. -- 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] autogenerating headers bki stuff
On Sunday 26 July 2009 20:58:30 Tom Lane wrote: The argument about optional stuff doesn't impress me. I would think that something that's going to be optionally loaded doesn't need to be considered during bootstrap mode at all. We can just have initdb run some SQL scripts (or not) during its post-bootstrap phase. Isn't that exactly what some people are proposing? Remove the nonessential things from the DATA() lines and put them into SQL scripts? -- 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] Non-blocking communication between a frontend and a backend (pqcomm)
Hi, On Sat, Jul 25, 2009 at 8:39 AM, Tom Lanet...@sss.pgh.pa.us wrote: Oh, another gripe: I'll bet a nickel that this doesn't work very nicely under SSL. Bytes available on the socket doesn't necessarily equate to decrypted payload bytes being available. Depending on how you're using secure_poll, that might be okay, but it seems like a hazard waiting to trap unwary maintainers. Is it only necessary to add the comment about how to use secure_poll? There is the assumption that secure_poll must be used with secure_write/read (e.g., in read case, pq_recvbuf instead of native recv should be called after passing pq_wait). So, it's assumed that encrypted data are resolved in those R/W functions and only decrypted data are located in buffer. 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
[HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
On Sat, Jul 25, 2009 at 19:50, Tom Lanet...@sss.pgh.pa.us wrote: m...@postgresql.org (Magnus Hagander) writes: Log Message: --- Reserve the shared memory region during backend startup on Windows, so that memory allocated by starting third party DLLs doesn't end up conflicting with it. I am wondering why failure of the various TerminateProcess calls in postmaster.c is elog(ERROR) and not elog(LOG). While that probably shouldn't happen, aborting the postmaster isn't a good response if it does. This patch introduces a new occurrence, but I see it is just copying what was there already. The case where it's doing it now is really a can't happen place, so I don't think it's a big issue there. It could be argued that if we can't terminate a process we just created (but never even started!), something is very very badly broken on the system and we might as well give up. Same for the part where we fail to ResumeThread() on the main thread of a new process. However, it seems that for example the one at line 3629 - where we're just failing to save our backend state - shouldn't be such a FATAL error. But if you actually look up into the function save_backend_variables(), it's actually hardcoded to return true... In case something goes wrong in there, there's an actual ereport(ERROR) happening deep down already (write_inheritable_socket/write_duplicated_handle). To fix that we'd just have to turn those functions all into returning boolean and log with LOG instead. AFAIK, we've had zero reports of this actually happening, so I'm not sure it's worth redesigning. Thoughts? -- Magnus Hagander Self: http://www.hagander.net/ Work: http://www.redpill-linpro.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] improvements for dict_xsyn extended synonym dictionary - RRR
Andres Freund and...@anarazel.de writes:
Hi Andres,
Thank you for review of my patch.
Some points:
- Patch looks generally sound
- lacks a bit of a motivational statement, even though one can imagine uses
The patch has initially been motivated by the request in pgsql-general
(http://archives.postgresql.org/pgsql-general/2009-02/msg00102.php).
- Imho mode=MAP should error out if keeporig is false
- I personally find the the names for the different modes a bit
nondescriptive.
One possibility would be to introduce parameters like:
- matchorig
- matchsynonym
- keeporig
- keepsynonym
That sounds way much easier to grasp for me.
Yes, I agree. In such a way user has the complete (and more straightforward)
control over the dictionary behaviour.
Here is the revised patch version, with following options:
* matchorig controls whether the original word is accepted by the
dictionary. Default is true.
* keeporig controls whether the original word is included (if true)
in results, or only its synonyms (if false). Default is true.
* matchsynonyms controls whether any of the synonyms is accepted by
the dictionary (if true). Default is false.
* keepsynonyms controls whether synonyms are returned by the
dictionary (if true). Default is true.
Defaults are set to keep default behaviour compatible with original version.
Thanks,
Sergey
Index: contrib/dict_xsyn/dict_xsyn.c
===
RCS file: /projects/cvsroot/pgsql/contrib/dict_xsyn/dict_xsyn.c,v
retrieving revision 1.6
diff -u -r1.6 dict_xsyn.c
--- contrib/dict_xsyn/dict_xsyn.c 1 Jan 2009 17:23:32 - 1.6
+++ contrib/dict_xsyn/dict_xsyn.c 27 Jul 2009 09:51:52 -
@@ -26,6 +26,7 @@
char *key; /* Word */
char *value; /* Unparsed list of synonyms, including the
* word itself */
+ int pos;/* Position of key word in original string */
} Syn;
typedef struct
@@ -33,7 +34,11 @@
int len;
Syn *syn;
+ bool matchorig;
bool keeporig;
+ bool matchsynonyms;
+ bool keepsynonyms;
+
} DictSyn;
@@ -88,6 +93,7 @@
{
char *value;
char *key;
+ char *pos;
char *end = NULL;
if (*line == '\0')
@@ -96,26 +102,39 @@
value = lowerstr(line);
pfree(line);
- key = find_word(value, end);
- if (!key)
- {
- pfree(value);
- continue;
- }
+ pos = value;
- if (cur == d-len)
+ while((key = find_word(pos, end)) != NULL)
{
- d-len = (d-len 0) ? 2 * d-len : 16;
- if (d-syn)
-d-syn = (Syn *) repalloc(d-syn, sizeof(Syn) * d-len);
- else
-d-syn = (Syn *) palloc(sizeof(Syn) * d-len);
- }
+ if (cur == d-len)
+ {
+d-len = (d-len 0) ? 2 * d-len : 16;
+if (d-syn)
+ d-syn = (Syn *) repalloc(d-syn, sizeof(Syn) * d-len);
+else
+ d-syn = (Syn *) palloc(sizeof(Syn) * d-len);
+ }
- d-syn[cur].key = pnstrdup(key, end - key);
- d-syn[cur].value = value;
+ /* Read first word only if we will match it */
+ if (pos != value || d-matchorig)
+ {
+d-syn[cur].key = pnstrdup(key, end - key);
+d-syn[cur].value = pstrdup(value);
+d-syn[cur].pos = key - value;
+
+cur++;
+ }
- cur++;
+ pos = end;
+
+ /* Don't read synonyms if we do not match them */
+ if (!d-matchsynonyms)
+ {
+break;
+ }
+ }
+
+ pfree(value);
}
tsearch_readline_end(trst);
@@ -133,23 +152,40 @@
List *dictoptions = (List *) PG_GETARG_POINTER(0);
DictSyn*d;
ListCell *l;
-
+ char *filename = NULL;
+
d = (DictSyn *) palloc0(sizeof(DictSyn));
d-len = 0;
d-syn = NULL;
+ d-matchorig = true;
d-keeporig = true;
-
+ d-matchsynonyms = false;
+ d-keepsynonyms = true;
+
foreach(l, dictoptions)
{
DefElem*defel = (DefElem *) lfirst(l);
- if (pg_strcasecmp(defel-defname, KEEPORIG) == 0)
+ if (pg_strcasecmp(defel-defname, MATCHORIG) == 0)
+ {
+ d-matchorig = defGetBoolean(defel);
+ }
+ else if (pg_strcasecmp(defel-defname, KEEPORIG) == 0)
{
d-keeporig = defGetBoolean(defel);
}
+ else if (pg_strcasecmp(defel-defname, MATCHSYNONYMS) == 0)
+ {
+ d-matchsynonyms = defGetBoolean(defel);
+ }
+ else if (pg_strcasecmp(defel-defname, KEEPSYNONYMS) == 0)
+ {
+ d-keepsynonyms = defGetBoolean(defel);
+ }
else if (pg_strcasecmp(defel-defname, RULES) == 0)
{
- read_dictionary(d, defGetString(defel));
+ /* we can't read the rules before parsing all options! */
+ filename = pstrdup(defGetString(defel));
}
else
{
@@ -160,6 +196,12 @@
}
}
+ if(filename)
+ {
+ read_dictionary(d, filename);
+ pfree(filename);
+ }
+
PG_RETURN_POINTER(d);
}
@@ -198,7 +240,6 @@
int value_length = strlen(value);
char *pos = value;
int nsyns = 0;
- bool is_first = true;
res = palloc(0);
@@ -214,8 +255,8 @@
res = repalloc(res, sizeof(TSLexeme) * (nsyns + 2));
res[nsyns].lexeme = NULL;
- /* first word is added to
Re: [HACKERS] autogenerating headers bki stuff
On Mon, Jul 27, 2009 at 4:17 AM, Peter Eisentrautpete...@gmx.net wrote: On Sunday 26 July 2009 01:40:09 Tom Lane wrote: And it is going to cost us in places like how do we generate the fmgr lookup table. We rgrep the source tree for PG_FUNCTION_ARGS, extract the function name, and put them in a list. Probably parsing the SQL would be a better idea. Otherwise, the outputs would depend on every .c file in the entire source tree. ...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] query - change in gistentryinit between 8.1 and 8.2
Hi, Pavel Stehule pavel.steh...@gmail.com writes: I try to write GiST support for one custom type and I am not sure about compress function. I don't understand where I can specify size of returned compressed key. 8.1 and older had 6. parameter for size, but this parameter is missing in new versions. The compress function is only useful for storing different values on leaves than on the column you're indexing, AFAIUI. So what you could do is make this internal type SQL visible, then use it in the STORAGE option of the CREATE OPERATOR CLASS command. I'm unsure how correct an answer this is, though... -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?
Hi, We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. I asked Marko Kreen but he is not sure about it; according to him it would be better to have general fixed-length hex types. (I guess it would be possible to implement the digest types as domains over those.) So basically we have sha1, sha-256, sha-512 etc on one hand, and hex8, hex16, hex32 on the other hand. In both cases there is a single body of code that is compiled with a macro definition that provides the data length for every separate case. (Actually in the digest code we refactored the common routines so that each type has a light wrapper calling a function that works on any length; this could also be done to the fixed-len hex code as well -- that code is pretty grotty at the moment.) Of these two choices, which one is likely to have better acceptance around here? -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Bruce Momjian írta: Hans-Juergen Schoenig wrote: hello everybody, from my side the goal of this discussion is to extract a consensus so that we can go ahead and implement this issue for 8.5. our customer here needs a solution to this problem and we have to come up with something which can then make it into PostgreSQL core. how shall we proceed with the decision finding process here? i am fine with a GUC and with an grammar extension - i just need a decision which stays unchanged. Do we have answer for Hans-Juergen here? Do we? The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. Can we go ahead implementing it? I have added a vague TODO: Consider a lock timeout parameter * http://archives.postgresql.org/pgsql-hackers/2009-05/msg00485.php -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. -- 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] CommitFest Status Summary - 2009-07-25
--On Sonntag, Juli 26, 2009 15:43:28 -0400 Robert Haas robertmh...@gmail.com wrote: I think Joe is back in the next week, but I'm not sure about Michael or Heikki. Michael is on vacation, he's back next week. -- Thanks Bernd -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan wrote: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? Consistency could also be achieved by removing NOWAIT, but I don't see you proposing that. -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Alvaro Herrera írta: Boszormenyi Zoltan wrote: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy. Syntax consistency with NOWAIT? And easy of use in diverging from default lock_timeout? Consistency could also be achieved by removing NOWAIT, but I don't see you proposing that. And you won't see me proposing any other feature removal either :-) -- Bible has answers for everything. Proof: But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil. (Matthew 5:37) - basics of digital technology. May your kingdom come - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- 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] mixed, named notation support
--On Sonntag, Juli 26, 2009 06:17:49 +0200 Pavel Stehule pavel.steh...@gmail.com wrote: Hi, I sending a little bit modified version - I removed my forgotten comment in gram.y Thanks, i'll look on it asap. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch test for Win32 shared memory issue: Success
As per the following link, http://blog.hagander.net/archives/149-Help-us-test-a-patch-for-the-Win32 -shared-memory-issue.html http://blog.hagander.net/archives/149-Help-us-test-a-patch-for-the-Win3 2-shared-memory-issue.html ...I'd ran the patched 8.4 version and PostgreSQL ran successfully without problems during some brief testing. (I'm reporting my success here as requested by the article.) My test platform is Windows XP SP2 32-bit, running on an Intel Centrino Duo processor. Robert Walker Database Administrator / System Analyst Materials Transportation Company 254-771-1204 ext 11 r.wal...@mtcworldwide.com DISCLAIMER: This communication, along with any documents, files or attachments, is intended only for the use of the addressee and may contain information that is legally privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of any information contained in or attached to this communication is strictly prohibited. If you have received this message in error, please notify the sender immediately and destroy the original communication and its attachments without reading, printing or saving in any manner. This communication does not form any contractual obligation on behalf of the sender, the sender's employer, or the employer's parent company, affiliates or subsidiaries.
Re: [HACKERS] When is a record NULL?
Greg Stark gsst...@mit.edu wrote: Kevin Grittnerkevin.gritt...@wicourts.gov wrote: impossible to write two queries which can be shown to be logically equivalent but which optimize to different access plans Personally I think that's a fine goal to aim for. Sure, but from my experience, there aren't any database products which come close to having an optimizer which can do a good enough job that it is yet feasible. I'm not sure I've even seen any which reliably treat '(a AND b) OR (c and d)' as equivalent to 'NOT ((a OR b) AND (c OR d))', much less the fairly common 'a b OR (a = b AND c d)' into 'a = b AND (a b OR c d)'. Two commonly heard arguments on this list are that: (1) a particular improvement in this area is not worth it because it would cost more in CPU time to recognize the equivalence that it would save in run time from the better plan, and (2) someone sometimes finds it useful to not recognize the equivalence so that they can coerce a certain type of plan. The latter really *is* a form of optimizer hint, it's just an undocumented, arcane hint for the Illuminati. But anyway, I didn't say that it was a bad thing toward which to strive, just that it's so far from realization that as an absolute requirement to be designated an RDBMS, it's a bit ivory tower. I'm not sure what to be considered a relational database means In that context I was talking about Codd's book where he provides a list of properties which a DBMS should have to be considered, in his opinion (as the inventor of the relational model for database management) a relational database management system. It just occurs to me that I think some of these entries were *required* for his blessing, and others were just *desirable*. I don't remember which designation he gave this point. -Kevin -- 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] autogenerating headers bki stuff
Peter Eisentraut pete...@gmx.net writes: On Sunday 26 July 2009 20:58:30 Tom Lane wrote: The argument about optional stuff doesn't impress me. I would think that something that's going to be optionally loaded doesn't need to be considered during bootstrap mode at all. We can just have initdb run some SQL scripts (or not) during its post-bootstrap phase. Isn't that exactly what some people are proposing? Remove the nonessential things from the DATA() lines and put them into SQL scripts? I think what some people are proposing is to rip inessential stuff (say, the geometry types) out of core completely. Which I'd not be against. The discussion at the moment is about whether to have first-class and second-class citizens within the core set of functions; and that I'm against. I think two notations implemented by two different toolchains will be confusing and not help maintenance that much. 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] When is a record NULL?
Sam Mason s...@samason.me.uk wrote: I've heard lots and read a few smaller articles but don't think I've got around to any of his books. Having just poked around on the Internet, I think perhaps this was his only full-fledge book, per se. The rest of his work appears to have been papers published in academia or with the ACM. to be considered a relational database, it must be impossible to write two queries which can be shown to be logically equivalent but which optimize to different access plans Sounds as though he's using a different definition than what I would use, but I'm sure I'll find out. I think that as the inventor of the relational model for database management, he felt that things were being done using the name of the technology which didn't match his vision of it. This book, and some of his papers, seem to have been geared toward preserving the integrity of his vision of RDBMS. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FW: PostGres developer Contact
Hi Please, subscribe me. Ahmed DERBAH System Analyst CBS XEROX Algiers - Algeria Tel : +213 (0)21 38 38 00 à 05 Fax : +213 (0)21 38 38 28 Portable : +213 (0)770 619 192 E-mail : ahmed.der...@cbs-xerox.com -Original Message- From: selenama...@gmail.com [mailto:selenama...@gmail.com] On Behalf Of Selena Deckelmann Sent: Sunday, July 26, 2009 5:50 PM To: Ahmed DERBAH Subject: Re: PostGres developer Contact Hi! On Sat, Jul 25, 2009 at 5:56 AM, Ahmed DERBAHahmed.der...@cbs-xerox.com wrote: My name is Ahmed DERBAH from Algeria, I worked under PostGres (1995 Linux version) in 1997 for my last year of computer sciences degrees. I developed external layer (using PostGres DLLS) that permit working with composite object and more (with SQL 3). I am able to rediscover the newest version of this database and help / contribute / work within. Sounds great! Are you subscribed to the pgsql-hackers@postgresql.org mailing list? This is where development work occurs, and the best place to have conversations about code. Our greatest need currently are for people who can review code during commitfests. So if you have time and can review, the current status of the commitfest is here: https://commitfest.postgresql.org/action/commitfest_view?id=2 Let me know if this makes sense to you... Otherwise, if there is a particular feature you're interested in working on, writing some code and proposing a change to the mailing list is the typical way to get started :) -selena -- http://chesnok.com/daily - me http://endpoint.com - work
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
Magnus Hagander mag...@hagander.net writes: To fix that we'd just have to turn those functions all into returning boolean and log with LOG instead. AFAIK, we've had zero reports of this actually happening, so I'm not sure it's worth redesigning. Thoughts? I'm not really insisting on a redesign. I'm talking about the places where the code author appears not to have understood that ERROR means FATAL, because the code keeps plugging on after it anyway. As far as I can see, using ERROR at lines 3630, 3657, 3674, and 3693 is just plain bogus, and changing to LOG there requires no other fixing. 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] [RFC] new digest datatypes, or generic fixed-len hex types?
Alvaro Herrera alvhe...@commandprompt.com writes: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. Wasn't this proposed and rejected before? (Or more to the point, why'd you bother? The advantage over bytea seems negligible.) 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] Review: support for multiplexing SIGUSR1
Fujii Masao masao.fu...@gmail.com writes: On Mon, Jul 27, 2009 at 2:43 AM, Tom Lanet...@sss.pgh.pa.us wrote: If we want to be able to signal processes that don't have a ProcState entry, it would be better to assign an independent index instead of overloading BackendId like this. OK, I'll change the mechanism to assign a ProcSignalSlot to a process, independently from ProcState, but similarly to ProcState: a process gets a ProcSignalSlot from newly-introduced freelist at startup, and returns it to freelist at exit. Since this assignment and return require the lock, I'll introduce a new LWLock for them. I think you're making things more complicated when they should be getting simpler. It strikes me that the current API of pass the BackendId if known or InvalidBackendId if not still works for processes without a BackendId, as long as you can tolerate a bit of extra search overhead for them. (You could reduce the search overhead by searching the array back to front.) So a new process index may be overkill. Do we have a clear idea of what the future applications are? Yes, I'm planning to use this mechanism for communication between a process which can generate the WAL records and a WAL sender process, in Synch Rep. Since not only a backend but also some auxiliary processes (e.g., bgwriter) can generate the WAL records, processes which don't have a ProcState need to receive the multiplexed signal, too. Huh? Wouldn't the bgwriter be *sending* the signal not receiving it? Umm... the patch should cover a notify interrupt which currently uses SIGUSR2? Getting rid of the separate SIGUSR2 handler would definitely be a good proof of concept that the mechanism works for more than one use. 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] [RFC] new digest datatypes, or generic fixed-len hex types?
On Mon, Jul 27, 2009 at 10:20 AM, Tom Lanet...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. Wasn't this proposed and rejected before? (Or more to the point, why'd you bother? The advantage over bytea seems negligible.) well, one nice things about the fixed length types is that you can keep your table from needing a toast table when you have a bytea in it. merlin -- 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] Non-blocking communication between a frontend and a backend (pqcomm)
Fujii Masao masao.fu...@gmail.com writes: On Sat, Jul 25, 2009 at 8:39 AM, Tom Lanet...@sss.pgh.pa.us wrote: Oh, another gripe: I'll bet a nickel that this doesn't work very nicely under SSL. Bytes available on the socket doesn't necessarily equate to decrypted payload bytes being available. Depending on how you're using secure_poll, that might be okay, but it seems like a hazard waiting to trap unwary maintainers. Is it only necessary to add the comment about how to use secure_poll? There is the assumption that secure_poll must be used with secure_write/read (e.g., in read case, pq_recvbuf instead of native recv should be called after passing pq_wait). So, it's assumed that encrypted data are resolved in those R/W functions and only decrypted data are located in buffer. Well, actually, this description perfectly illustrates my basic complaint: the patch breaks the API abstraction provided by pqcomm.c. Callers are encouraged/forced to deal with the next layer down, and to the extent that pqcomm.c does anything useful, callers have to allow for that too. As far as the read side of pq_wait goes, it should probably be more like * return true if any bytes are in the buffer * else try to read some bytes into the buffer, without blocking * now return true or false depending on whether bytes are in the buffer. (Or perhaps instead of true/false, return the count of available bytes.) Also, I suspect the API needs to make a distinction between no more bytes available yet and EOF (channel closed, so no more bytes ever will be available). I'm not sure about the write side. The patch isn't really addressing blocking-on-write, except in the offhand way of having a forWrite option in pq_wait, but that doesn't seem too well thought out to me. Again, the buffering pqcomm does makes a direct query of the underlying state seem pretty suspicious. 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] [RFC] new digest datatypes, or generic fixed-len hex types?
Merlin Moncure mmonc...@gmail.com writes: On Mon, Jul 27, 2009 at 10:20 AM, Tom Lanet...@sss.pgh.pa.us wrote: Wasn't this proposed and rejected before? (Or more to the point, why'd you bother? The advantage over bytea seems negligible.) well, one nice things about the fixed length types is that you can keep your table from needing a toast table when you have a bytea in it. If you don't actually use the toast table, it doesn't cost anything very noticeable ... 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] When is a record NULL?
On Mon, Jul 27, 2009 at 9:48 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: The latter really *is* a form of optimizer hint, it's just an undocumented, arcane hint for the Illuminati. Well said. ...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] Review: Revise parallel pg_restore's scheduling heuristic
Andrew Dunstan and...@dunslane.net wrote: To performance test this properly you might need to devise a test that will use a sufficiently different order of queueing items to show the difference. It would appear that I need help with devising a proper test. So far, all tests have shown no difference in performance based on the patch; I get almost twice the speed as a single job running in one database transaction either way. Can someone explain what I should try to set up to get a best case and a worst case for the patch? Our production databases don't expose any difference, but I'm willing to try to use them to seed an artificial case which will. -Kevin -- 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] [RFC] new digest datatypes, or generic fixed-len hex types?
Merlin Moncure wrote: On Mon, Jul 27, 2009 at 10:20 AM, Tom Lanet...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. Wasn't this proposed and rejected before? (Or more to the point, why'd you bother? The advantage over bytea seems negligible.) well, one nice things about the fixed length types is that you can keep your table from needing a toast table when you have a bytea in it. Can't you just set storage on the column to MAIN to stop it being stored in a toast table? 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] Review: Revise parallel pg_restore's scheduling heuristic
Kevin Grittner wrote: Andrew Dunstan and...@dunslane.net wrote: To performance test this properly you might need to devise a test that will use a sufficiently different order of queueing items to show the difference. It would appear that I need help with devising a proper test. So far, all tests have shown no difference in performance based on the patch; I get almost twice the speed as a single job running in one database transaction either way. Can someone explain what I should try to set up to get a best case and a worst case for the patch? Our production databases don't expose any difference, but I'm willing to try to use them to seed an artificial case which will. Does your test case have lots of foreign keys? 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] Review: Revise parallel pg_restore's scheduling heuristic
Andrew Dunstan and...@dunslane.net wrote: Does your test case have lots of foreign keys? 488 of them. There is some variation on individual tests, but the results look to be in the noise. When I add them all up, the patch comes out 0.0036% slower -- but that is so far into the noise as to be considered no difference in my book. -Kevin -- 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] proposal: support empty string as separator for string_to_array
2009/7/25 Merlin Moncure mmonc...@gmail.com:
On Fri, Jul 24, 2009 at 11:40 PM, Pavel Stehulepavel.steh...@gmail.com
wrote:
Hello
I have one idea, that should simplify string to char array
transformation. The base is idea: between every char is empty string,
so empty string is regular separator for string_to_array function.
This behave is inversion of array_to_string function behave:
postgres=# select array_to_string(array['a','b','c'],'');
array_to_string
-
abc
(1 row)
postgres=# select string_to_array('abc','');
string_to_array
-
{a,b,c}
(1 row)
postgres=# select regexp_split_to_array('abc', '');
regexp_split_to_array
---
{a,b,c}
(1 row)
:-)
I tested implementation and it's about 30% faster than using regexp.
I could to thing, 30% is significant reason for implementation.
regards
Pavel Stehule
merlin
--
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] proposal: support empty string as separator for string_to_array
Pavel Stehule pavel.steh...@gmail.com wrote: I tested implementation and it's about 30% faster than using regexp. Rather than making a change which could break existing applications, how about a new function string_to_array(text) which returns an array of char? -Kevin -- 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] proposal: support empty string as separator for string_to_array
2009/7/27 Kevin Grittner kevin.gritt...@wicourts.gov: Pavel Stehule pavel.steh...@gmail.com wrote: I tested implementation and it's about 30% faster than using regexp. Rather than making a change which could break existing applications, how about a new function string_to_array(text) which returns an array of char? yes, it was my idea too - or function chars_to_array Pavel -Kevin -- 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: Deferrable unique constraints
Dean Rasheed dean.a.rash...@googlemail.com writes: [ latest deferrable-unique patch ] I'm starting to look at this now. I haven't read the patch itself yet, but I went through the discussion thread. I'm not sure whether we have actually decided that we want to commit this, as opposed to treating it as an investigation exercise. The main thing that is bothering me is something Dean pointed out at the very beginning: the patch will not scale well for large numbers of conflicts. The reason this bothers me is that from what I recall of past complaints about our lack of deferred unique checks, the *primary* use case is things like update foo set id = id + 1. So I'm afraid that this might be a toy implementation that's not useful in practice. The three likely responses to this objection seem to be 1. You're right, we should reject the patch until that's fixed. 2. You're wrong, the patch is perfectly useful as-is. 3. You're right, but we should commit anyway because it will be fixed later. I don't think I'm going to believe #3 though, because there's no concrete design for a fix on the table, much less a commitment to implement it. Comments? 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] proposal: support empty string as separator for string_to_array
On Mon, Jul 27, 2009 at 12:42 PM, Pavel Stehulepavel.steh...@gmail.com wrote:
2009/7/25 Merlin Moncure mmonc...@gmail.com:
On Fri, Jul 24, 2009 at 11:40 PM, Pavel Stehulepavel.steh...@gmail.com
wrote:
Hello
I have one idea, that should simplify string to char array
transformation. The base is idea: between every char is empty string,
so empty string is regular separator for string_to_array function.
This behave is inversion of array_to_string function behave:
postgres=# select array_to_string(array['a','b','c'],'');
array_to_string
-
abc
(1 row)
postgres=# select string_to_array('abc','');
string_to_array
-
{a,b,c}
(1 row)
postgres=# select regexp_split_to_array('abc', '');
regexp_split_to_array
---
{a,b,c}
(1 row)
:-)
I tested implementation and it's about 30% faster than using regexp.
I could to thing, 30% is significant reason for implementation.
yes, I noticed that too. I was thinking though that if anything
should be done, it should be to go the other way: simple cases of
regexp_split_to_array should use the simpler algorithm in
'string_to_array'...just not the '' case, since they produce different
results.
I don't think the chars_to_array function is the way to go. One thing
that might work though is to overload the string_to_array function (or
use default parameter) to control the empty string case with an bool,
or an option or something.
merlin
--
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] proposal: support empty string as separator for string_to_array
Pavel Stehule pavel.steh...@gmail.com writes: I tested implementation and it's about 30% faster than using regexp. In a real application, that's going to be negligible compared to all the other costs involved in pushing the data around. And we still haven't seen any in-the-field requests for this functionality, so even if the gap were wider, I don't see the point of putting effort into 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] [RFC] new digest datatypes, or generic fixed-len hex types?
On Mon, Jul 27, 2009 at 12:02 PM, Andrew Dunstanand...@dunslane.net wrote: Merlin Moncure wrote: On Mon, Jul 27, 2009 at 10:20 AM, Tom Lanet...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@commandprompt.com writes: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. Wasn't this proposed and rejected before? (Or more to the point, why'd you bother? The advantage over bytea seems negligible.) well, one nice things about the fixed length types is that you can keep your table from needing a toast table when you have a bytea in it. Can't you just set storage on the column to MAIN to stop it being stored in a toast table? of course. hm. would the input/output functions for the fixed length types be faster? what is the advantage of the proposal? merlin -- 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] proposal: support empty string as separator for string_to_array
2009/7/27 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I tested implementation and it's about 30% faster than using regexp. In a real application, that's going to be negligible compared to all the other costs involved in pushing the data around. And we still haven't seen any in-the-field requests for this functionality, so even if the gap were wider, I don't see the point of putting effort into it. This is just possible optimalisation - Maybe Merlin proposal is the best - we could add this technique to regexp_split_to_array - when is pattern empty, then we could to use direct char separation - without any new function or change of current function. And somebody, that use regexp_split_to_array now will have profit too. 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] SE-PostgreSQL Specifications
s...@samason.me.uk (Sam Mason) writes: On Sun, Jul 26, 2009 at 01:42:32PM +0900, KaiGai Kohei wrote: Robert Haas wrote: In some cases, the clearance of infoamtion may be changed. We often have dome more complex requirements also. OK, so there is some other trusted entity that has unfettered access to both databases and its job is to manage these requirements. No, that's not what this implies. What this implies is along the following lines... If a user at the more secret level updates some data that had been classified at a lower level, then that data gets reclassified at the higher level. If this sort of outcome is problematic, then that suggests that maybe the attempt at sharing wasn't such a good idea. Thus, it is necessary a capability to store and manage data objects with different security labeles in a single database instance here. (If we don't want to use commercial solutions instead.) SE-PG is about doing the above in one database and allowing more rigorous checks to be done? I don't think the issue is so much about more rigorous; it's about having mandatory labelling that is handled consistently. -- (reverse (concatenate 'string ofni.sesabatadxunil @ enworbbc)) http://linuxdatabases.info/info/rdbms.html The people's revolutionary committee has decided that the name e is retrogressive, unmulticious and reactionary, and has been flushed. Please update your abbrevs. -- 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: Deferrable unique constraints
On Mon, 2009-07-27 at 13:14 -0400, Tom Lane wrote: The main thing that is bothering me is something Dean pointed out at the very beginning: the patch will not scale well for large numbers of conflicts. The way I see it, there are two strategies: (a) build up a list as you go, and check it later (b) do a check of the full table at once Is there another reasonable option? The patch seems like a reasonable implementation of (a), so what it's missing is the ability to fall back to (b) when the list gets too large (compared with available memory or relative to the table size). Are you suggesting that we wait until (b) is implemented, or do you envision something else entirely? Regards, Jeff Davis -- 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: Deferrable unique constraints
2009/7/27 Jeff Davis pg...@j-davis.com: On Mon, 2009-07-27 at 13:14 -0400, Tom Lane wrote: The main thing that is bothering me is something Dean pointed out at the very beginning: the patch will not scale well for large numbers of conflicts. I'd pefer to take option #3, and I want to try to tackle the scaling issue next. I didn't do it as part of this patch because it has already grown very big, and I think the scaling problem is largely an orthogonal issue which affects all AR triggers. The way I see it, there are two strategies: (a) build up a list as you go, and check it later (b) do a check of the full table at once Is there another reasonable option? IMO (a) is the only way to go, since you don't know until the end of an update command what proportion of rows were potential conflicts, and so whether or not to do a row-by-row or a wholesale check. I think that this will be almost entirely a patch to trigger.c, for which there is already a separate TODO item. Actually I see 2 parts to this: (1). make trigger queue scalable with easy mechanism to switchover to wholesale check (2). implement wholesale check for UNIQUE but (1) seems like to lion's share of the work. (and then maybe (3). wholesale check for RI constraints) - Dean The patch seems like a reasonable implementation of (a), so what it's missing is the ability to fall back to (b) when the list gets too large (compared with available memory or relative to the table size). Are you suggesting that we wait until (b) is implemented, or do you envision something else entirely? Regards, Jeff Davis -- 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: Deferrable unique constraints
Jeff Davis pg...@j-davis.com writes: The way I see it, there are two strategies: (a) build up a list as you go, and check it later (b) do a check of the full table at once The patch seems like a reasonable implementation of (a), so what it's missing is the ability to fall back to (b) when the list gets too large (compared with available memory or relative to the table size). Are you suggesting that we wait until (b) is implemented, or do you envision something else entirely? What's mainly bothering me is the fear that real use cases are so heavily skewed towards (b) that we shouldn't bother implementing (a) at all. In that case we'd end up throwing away a lot of this patch, or else carrying a lot of not-very-useful code. I don't have a lot of concrete evidence about this, but as I said most of the complaints we've heard are suggesting bulk updates, eg http://archives.postgresql.org/pgsql-bugs/2003-05/msg00052.php http://archives.postgresql.org/pgsql-bugs/2004-09/msg00248.php http://archives.postgresql.org/pgsql-bugs/2007-07/msg00070.php There are some suggesting the other, eg, http://archives.postgresql.org/pgsql-bugs/2008-01/msg00172.php but they're clearly in the minority. It's possible that the first group are just using simple updates to illustrate the bug, rather than as examples of what they really want to do, but I'm unconvinced of that. Anyway, I'd feel a lot better about it if there were a near-term plan to do something about (b). 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: Deferrable unique constraints
Dean Rasheed dean.a.rash...@googlemail.com writes: Actually I see 2 parts to this: (1). make trigger queue scalable with easy mechanism to switchover to wholesale check (2). implement wholesale check for UNIQUE but (1) seems like to lion's share of the work. (and then maybe (3). wholesale check for RI constraints) We already have those wholesale checks. It might take a bit of refactoring to make them conveniently callable in this context, but there shouldn't be a lot of new code there. I think that scalable is sort of missing the mark as far as the needed queue behavior goes. Lossy might be a better adjective. The main reason nobody has implemented spill-to-disk for the existing trigger queue is that by the time you've filled memory with the queue, performance already sucks, because it's gonna take forever to fire all those trigger events. Being able to make the queue even bigger isn't going to make it suck less, quite the opposite. You need some way to slide into bulk instead of retail checking strategies, just like join strategies vary depending on the number of rows involved. (In fact, in a real sense these ARE join problems ... maybe we should stop thinking of them as fire-a-bunch-of-triggers and instead think of executing a single check query with appropriate WHERE clause ...) 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: Deferrable unique constraints
2009/7/27 Tom Lane t...@sss.pgh.pa.us: Jeff Davis pg...@j-davis.com writes: The way I see it, there are two strategies: (a) build up a list as you go, and check it later (b) do a check of the full table at once The patch seems like a reasonable implementation of (a), so what it's missing is the ability to fall back to (b) when the list gets too large (compared with available memory or relative to the table size). Are you suggesting that we wait until (b) is implemented, or do you envision something else entirely? What's mainly bothering me is the fear that real use cases are so heavily skewed towards (b) that we shouldn't bother implementing (a) at all. In that case we'd end up throwing away a lot of this patch, or else carrying a lot of not-very-useful code. I have a definite use for (a), which is why I started this. I typically write web apps on top of PG using an ORM, and I've had to write some fairly intricate code to save things in the right order to avoid violating my own unique constraints. I agree that (b) is important too, but typically when I do this kind of bulk update, I drop and re-add the indexes. More importantly, if you implement (b) first, how is it ever going to help with (a)? I thought it best to do (a) first and then think about how to switchover to (b). - Dean I don't have a lot of concrete evidence about this, but as I said most of the complaints we've heard are suggesting bulk updates, eg http://archives.postgresql.org/pgsql-bugs/2003-05/msg00052.php http://archives.postgresql.org/pgsql-bugs/2004-09/msg00248.php http://archives.postgresql.org/pgsql-bugs/2007-07/msg00070.php There are some suggesting the other, eg, http://archives.postgresql.org/pgsql-bugs/2008-01/msg00172.php but they're clearly in the minority. It's possible that the first group are just using simple updates to illustrate the bug, rather than as examples of what they really want to do, but I'm unconvinced of that. Anyway, I'd feel a lot better about it if there were a near-term plan to do something about (b). 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: Deferrable unique constraints
2009/7/27 Tom Lane t...@sss.pgh.pa.us: (In fact, in a real sense these ARE join problems ... maybe we should stop thinking of them as fire-a-bunch-of-triggers and instead think of executing a single check query with appropriate WHERE clause ...) Hmm. Presumably that is the same WHERE clause as the UPDATE. But it has to execute after the update. How does it avoid re-executing functions, re-incrementing sequences, etc... ? - Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] potential bug with query optimizer and functions
While creating a function, I kept getting a large variance in runtime between the raw query vs. using the function/prepared statement. After talking with folks on #postgresql, specifically David Fetter, he thought I should mention it here. VERSION: PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1.1)4.3.2 == DOING A PREPARE, EXPLAIN ANALYZE EXECUTE == Group (cost=26857.96..37572.40 rows=4 width=32) (actual time=6234.328..7015.498 rows=2185 loops=1) - Sort (cost=26857.96..27036.57 rows=71444 width=32) (actual time=6230.165..6232.030 rows=5164 loops=1) Sort Key: gen_ts_by_scope_extra.thets, gen_ts_by_scope_extra.theinterval, dcdo.contact_output_id Sort Method: quicksort Memory: 492kB - Nested Loop Left Join (cost=1443.96..19386.46 rows=71444 width=32) (actual time=14.061..6226.787 rows=5164 loops=1) Join Filter: ((dcdo.created = gen_ts_by_scope_extra.thets) AND (dcdo.created (gen_ts_by_scope_extra.thets + gen_ts_by_scope_extra.theinterval))) - Function Scan on gen_ts_by_scope_extra (cost=0.00..260.00 rows=1000 width=24) (actual time=2.646..3.377 rows=2185 loops=1) - Materialize (cost=1443.96..1450.39 rows=643 width=16) (actual time=0.000..1.046 rows=3603 loops=2185) - Append (cost=0.00..1443.32 rows=643 width=16) (actual time=0.086..5.810 rows=3603 loops=1) - Seq Scan on data_contact_output dcdo (cost=0.00..1.20 rows=3 width=16) (actual time=0.011..0.011 rows=0 loops=1) Filter: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200904 dcdo (cost=4.96..197.97 rows=91 width=16) (actual time=0.013..0.013 rows=0 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200904_contact_output_idx (cost=0.00..4.94 rows=91 width=0) (actual time=0.011..0.011 rows=0 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200905 dcdo (cost=9.57..364.27 rows=169 width=16) (actual time=0.060..0.242 rows=202 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200905_contact_output_idx (cost=0.00..9.53 rows=169 width=0) (actual time=0.049..0.049 rows=202 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200906 dcdo (cost=9.81..381.47 rows=199 width=16) (actual time=0.528..2.600 rows=2548 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200906_contact_output_idx (cost=0.00..9.76 rows=199 width=0) (actual time=0.463..0.463 rows=2548 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200907 dcdo (cost=5.27..304.93 rows=130 width=16) (actual time=0.225..1.076 rows=853 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200907_contact_output_idx (cost=0.00..5.24 rows=130 width=0) (actual time=0.177..0.177 rows=853 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200908 dcdo (cost=4.27..11.38 rows=3 width=16) (actual time=0.005..0.005 rows=0 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200908_contact_output_idx (cost=0.00..4.27 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200909 dcdo (cost=4.27..11.38 rows=3 width=16) (actual time=0.003..0.003 rows=0 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200909_contact_output_idx (cost=0.00..4.27 rows=3 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (contact_output_id = $1) - Bitmap Heap Scan on data_contact_output_200910 dcdo (cost=4.27..11.38 rows=3 width=16) (actual time=0.003..0.003 rows=0 loops=1) Recheck Cond: (contact_output_id = $1) - Bitmap Index Scan on data_contact_output_200910_contact_output_idx
Re: [HACKERS] potential bug with query optimizer and functions
On Mon, Jul 27, 2009 at 3:15 PM, Zach Conradzach.con...@digitecinc.com wrote: While creating a function, I kept getting a large variance in runtime between the raw query vs. using the function/prepared statement. After talking with folks on #postgresql, specifically David Fetter, he thought I should mention it here. When you PREPARE with a parameter, it generates the plan without knowing specifically which value you intend to substitute at execution time. Obviously, this can affect what plan gets chosen, because it's sort of trying to make a best guess without knowing whether the actual value will be a common one or an uncommon one. I bet if you PREPARE it without the parameter, you'll get the same plan as executing it directly. ...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] SRPMs?
On Wed, 2009-07-22 at 15:06 -0400, Andrew Dunstan wrote: Where are the SRPMs to go with the binary RPMs on our download sites (or for that matter on yum.pgsqlrpms.org). http://yum.pgsqlrpms.org/srpms/ If there are missing SRPMs, please let me know. ISTM we should not be publishing binary RPMs without simultaneously publishing the corresponding SRPMs. Agreed. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] WIP: Deferrable unique constraints
[ still poking around in this patch ] Jeff Davis pg...@j-davis.com writes: 10. You're overloading tgconstrrelid to hold the constraint's index's oid, when normally it holds the referenced table. You should probably document this a little better, because I don't think that field is used to hold an index oid anywhere else. Having documented this kluge doesn't make it any more acceptable. It risks breaking any number of things that expect that field to reference a table, not an index. There seem to be two reasonable alternatives here: * Add another column to pg_trigger (and hence the Trigger data structure) to carry the index OID. * Store the index OID as a trigger argument (which means converting it to text form, and then back again for each use). The former approach would be a lot easier if anyone is trying to query pg_trigger for the triggers associated with an index, but OTOH I'm not sure anyone would really need to do that. The latter approach minimizes catalog changes at the cost of a bit of runtime inefficiency; but considering everything else that goes on to fire a deferred trigger, worrying about a strtoul call is probably silly. If we did add another column to pg_trigger, I'd be a bit tempted to add one to pg_constraint too. tgconstrrelid for RI triggers is a mirror of a pg_constraint column, and it seems like the index data perhaps should be as well. (Indeed, one of the thing that got me annoyed about this kluge in the first place was that it broke that relationship without changing the documentation.) Comments, opinions? 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: Deferrable unique constraints
On Mon, 2009-07-27 at 16:33 -0400, Tom Lane wrote: If we did add another column to pg_trigger, I'd be a bit tempted to add one to pg_constraint too. tgconstrrelid for RI triggers is a mirror of a pg_constraint column, and it seems like the index data perhaps should be as well. (Indeed, one of the thing that got me annoyed about this kluge in the first place was that it broke that relationship without changing the documentation.) That would work great for me, as I was planning to add such a column anyway for my generalized index constraints patch. Regards, Jeff Davis -- 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: Deferrable unique constraints
2009/7/27 Jeff Davis pg...@j-davis.com: On Mon, 2009-07-27 at 16:33 -0400, Tom Lane wrote: If we did add another column to pg_trigger, I'd be a bit tempted to add one to pg_constraint too. tgconstrrelid for RI triggers is a mirror of a pg_constraint column, and it seems like the index data perhaps should be as well. (Indeed, one of the thing that got me annoyed about this kluge in the first place was that it broke that relationship without changing the documentation.) That would work great for me, as I was planning to add such a column anyway for my generalized index constraints patch. Yes that seems like the most sensible option to me. - Dean -- 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] SRPMs?
Devrim GÜNDÜZ wrote: On Wed, 2009-07-22 at 15:06 -0400, Andrew Dunstan wrote: Where are the SRPMs to go with the binary RPMs on our download sites (or for that matter on yum.pgsqlrpms.org). http://yum.pgsqlrpms.org/srpms/ If there are missing SRPMs, please let me know. The postgres SRPMs are all marked beta or rc1 AFAICS. 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] WIP: Deferrable unique constraints
On Mon, Jul 27, 2009 at 7:51 PM, Tom Lanet...@sss.pgh.pa.us wrote: (In fact, in a real sense these ARE join problems ... maybe we should stop thinking of them as fire-a-bunch-of-triggers and instead think of executing a single check query with appropriate WHERE clause ...) A while back I suggested keeping the deferred trigger list in tuplestore format and executing the trigger check as a query between the materialized tuplestore of and the tuples on disk. I love the idea of doing a full SQL query but the problem is that there's no particular reason to assume that a deferred trigger list large enough to warrant a wholesale check is actually a significant percentage of the table. It might only take a few hundred or few thousand checks to warrant a bitmap index scan instead of repeated index probes but a plain SQL query with no reference back to the deferred list would have to check all the millions of rows in the table for no purpose. For foreign keys I was picturing some way to issue an SQL statement like SELECT from tabletocheck where ctid in (magic parameter) and not exists (select 1 from referenced_table where pk = tabletocheck.fk) and then somehow pass the list of ctids from the deferred list. -- greg http://mit.edu/~gsstark/resume.pdf -- 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: Deferrable unique constraints
Greg Stark gsst...@mit.edu writes: For foreign keys I was picturing some way to issue an SQL statement like SELECT from tabletocheck where ctid in (magic parameter) and not exists (select 1 from referenced_table where pk = tabletocheck.fk) and then somehow pass the list of ctids from the deferred list. I have no problem with having some magic in there --- FK checks already have to do some things that aren't expressible in standard SQL, because of snapshotting issues. However, the above still presumes that we can afford to store all the CTIDs involved. Which is more or less exactly what the trigger event queue is doing now. We need a different view about that bit, I think. Perhaps we could remember the CTIDs the transaction has touched in a bitmap (which could become lossy under memory pressure)? On a lossy page you'd need to check all the tuples to see which ones have xmins matching your transaction; but that's not too terrible and at least you're not visiting pages you don't need to. 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: Deferrable unique constraints
Dean Rasheed dean.a.rash...@googlemail.com writes: 2009/7/27 Jeff Davis pg...@j-davis.com: On Mon, 2009-07-27 at 16:33 -0400, Tom Lane wrote: If we did add another column to pg_trigger, I'd be a bit tempted to add one to pg_constraint too. That would work great for me, as I was planning to add such a column anyway for my generalized index constraints patch. Yes that seems like the most sensible option to me. Okay. I will go off and do that, then come back to Dean's patch. Proposed names: pg_trigger.tgconstrindidanalogous to tgconstrrelid pg_constraint.conuindid analogous to confrelid where the latter will be populated for any unique or pkey constraint. The former will be always 0 for the moment, but we'll start filling it in with Dean's patch. (thinks...) Actually, u for unique might be a poor choice if Jeff's patch goes in and starts using it for things that aren't exactly unique indexes. Should it be just conindid? 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] SE-PostgreSQL Specifications
Chris Browne wrote: s...@samason.me.uk (Sam Mason) writes: On Sun, Jul 26, 2009 at 01:42:32PM +0900, KaiGai Kohei wrote: Robert Haas wrote: In some cases, the clearance of infoamtion may be changed. We often have dome more complex requirements also. OK, so there is some other trusted entity that has unfettered access to both databases and its job is to manage these requirements. No, that's not what this implies. What this implies is along the following lines... If a user at the more secret level updates some data that had been classified at a lower level, then that data gets reclassified at the higher level. If this sort of outcome is problematic, then that suggests that maybe the attempt at sharing wasn't such a good idea. Theoretically, such kind of updates are not visible for lower security level users. In other word, a tuple can have multiple version depending on the security level, it might be called as polyinstantication. However, SE-PostgreSQL and SELinux which provides the security policy adopt more simple solution. Its security policy prevent any writer operations on the data object which dose not have identical security level. In other word, if the database client has classified level. He cannot write anything on both of unclassified and secret, but it is possible for classified data object. Thus, it is necessary a capability to store and manage data objects with different security labeles in a single database instance here. (If we don't want to use commercial solutions instead.) SE-PG is about doing the above in one database and allowing more rigorous checks to be done? I don't think the issue is so much about more rigorous; it's about having mandatory labelling that is handled consistently. Yes, it shall perform correctly as far as SE-PostgreSQL manages security context of database objects and makes its access control decision without any exceptions. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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: Deferrable unique constraints
On Mon, 2009-07-27 at 19:12 -0400, Tom Lane wrote: (thinks...) Actually, u for unique might be a poor choice if Jeff's patch goes in and starts using it for things that aren't exactly unique indexes. Should it be just conindid? My thoughts exactly. Regards, Jeff Davis -- 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: Deferrable unique constraints
On Tue, Jul 28, 2009 at 12:04 AM, Tom Lanet...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: For foreign keys I was picturing some way to issue an SQL statement like SELECT from tabletocheck where ctid in (magic parameter) and not exists (select 1 from referenced_table where pk = tabletocheck.fk) and then somehow pass the list of ctids from the deferred list. I have no problem with having some magic in there --- FK checks already have to do some things that aren't expressible in standard SQL, because of snapshotting issues. However, the above still presumes that we can afford to store all the CTIDs involved. Which is more or less exactly what the trigger event queue is doing now. We need a different view about that bit, I think. It wasn't clear in the SQL example but I described storing them in a tuplestore. The tuplestore would get spilled to disk automatically but the SQL query could (semi)join against it using whatever form of join is most efficient. Now that I look at that query though it's pretty clear that we don't actually have a good join type to handle this. We would need some kind of merge-join which knew that ctids from a sequential scan were in order (and could ensure that they were in fact in order). There might be a better way to write the query above in a way that didn't need anything special like that. The need to check that the inserted tuple is still live is a big part of the headache. If we could check for violations first and then go back and check any violations if there are any to see if they come from live tuples that would save a lot of work. -- greg http://mit.edu/~gsstark/resume.pdf -- 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: Deferrable unique constraints
Jeff Davis pg...@j-davis.com writes: On Mon, 2009-07-27 at 19:12 -0400, Tom Lane wrote: (thinks...) Actually, u for unique might be a poor choice if Jeff's patch goes in and starts using it for things that aren't exactly unique indexes. Should it be just conindid? My thoughts exactly. On looking closer, it appears we should populate this column for FKEY constraints too --- for example this would greatly simplify some of the information_schema views (cf _pg_underlying_index). Now those references will also point at unique indexes, but still this seems like another reason to use a relatively generic column name. conindid it is. 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] When is a record NULL?
On Jul 26, 2009, at 4:02 PM, Eric B. Ridge wrote: I'm just a random lurker, but FOUND seems to work just fine (I suppose it's PG-specific?). http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS BEGIN OPEN stuff; FETCH stuff INTO rec; WHILE FOUND LOOP RETURN NEXT rec; FETCH stuff INTO rec; END LOOP; END; Yep, that's just what I needed, thanks. I think I'll send a patch for the Cursors section of the PL/pgSQL documentation that mentions this. Would have saved me a bunch of hassle. Best, David -- 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] SRPMs?
On Mon, 2009-07-27 at 16:55 -0400, Andrew Dunstan wrote: The postgres SRPMs are all marked beta or rc1 AFAICS. Sorry about that. Script had failed at some point on the master build server(DNS issue). I'm currently rsyncing srpms, and they will be ready in a few hours. Thanks, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [HACKERS] SE-PostgreSQL Specifications
KaiGai -- I have a few suggestions which I will post in a bit, and some rather extensive edits of the existing Wiki, mostly for syntax rather than content. How do you want the latter ? I can email them offline as text, or you could set me up with a login on the wiki and I could do them in place (perhaps paragraph by paragraph) ? Thanks, and apologies for the delay ! Greg W. ps most of what I have is orthogonal to the lively discussions since Friday. -- 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] SE-PostgreSQL Specifications
Greg Williamson wrote: KaiGai -- I have a few suggestions which I will post in a bit, and some rather extensive edits of the existing Wiki, mostly for syntax rather than content. How do you want the latter ? I can email them offline as text, or you could set me up with a login on the wiki and I could do them in place (perhaps paragraph by paragraph) ? Thanks for your helps. I think it is good idea to edit wikipage because we can easily check and understand its update history. IIRC, you can create a wiki account from: http://www.postgresql.org/community/signup BTW, I changed the structure of chapters/sections in former of the documentation to reduce usage of undefined terminology and so on. Sorry, if it overlapped with your efforts. And, please note that the 3.2 Access controls section is still on work. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] multi-threaded pgbench
On Wed, 2009-07-22 at 22:23 -0400, Greg Smith wrote: Onto performance. My test system has a 16 cores of Xeon X5550 @ 2.67GHz. I created a little pgbench database (-s 10) and used the default postgresql.conf parameters for everything but max_connections for a rough initial test. To test on Windows, I set up a similar database on an 8-core 2.0GHz E5335 (closest match I have.) It's compiled against a fresh CVS pull from this morning, patched with the 20090724 updated version. I tried to mirror the tests as much as possible, including the concurrent thread counts despite having half the number of available cores. Doing that didn't have much impact on the results, but more on that later. Comparing the unpatched version to the new version running a single client thread, there's no significant performance difference: C:\pgsql85bin\pgbenchorig.exe -S -c 8 -t 10 pgbench ... tps = 19061.234215 (including connections establishing) C:\pgsql85bin\pgbench.exe -S -c 8 -t 10 pgbench tps = 18852.928562 (including connections establishing) As a basis of comparison the original pgbench was run with increasing client counts, which shows the same drop off in throughput past the 16-client sweet spot: con tps 8 18871 16 19161 24 18804 32 18670 64 17598 128 16664 However I was surprised to see these results for the patched version, running 16 worker threads (apart from the 8 client run of course.) C:\pgsql85bin\pgbench.exe -S -j 16 -c 128 -t 10 pgbench ... con tps 8 18435 (-j 8) 16 18866 24 - 32 17937 64 17016 128 15930 In all cases the patched version resulted in a lower performing output than the unpatched version. It's clearly working, at least in that it's launching the requested number of worker threads when looking at the process. Adjusting the worker thread count down to match the number of cores yielded identical results in the couple of test cases I ran. Maybe pgbench itself is less of a bottleneck in this environment, relatively speaking? - 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
