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

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

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 plac

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

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 FO

Re: [HACKERS] WIP: Deferrable unique constraints

Jeff Davis 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 cl

Re: [HACKERS] WIP: Deferrable unique constraints

On Tue, Jul 28, 2009 at 12:04 AM, Tom Lane wrote: > Greg Stark writes: >> For foreign keys I was picturing some way to issue an SQL statement >> like "SELECT from tabletocheck where ctid in () and >> not exists (select 1 from referenced_table where pk = >> tabletocheck.fk)" and then somehow pass t

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 v

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 tr

Re: [HACKERS] WIP: Deferrable unique constraints

Dean Rasheed writes: > 2009/7/27 Jeff Davis : >> 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

Re: [HACKERS] WIP: Deferrable unique constraints

Greg Stark writes: > For foreign keys I was picturing some way to issue an SQL statement > like "SELECT from tabletocheck where ctid in () 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 pr

Re: [HACKERS] WIP: Deferrable unique constraints

On Mon, Jul 27, 2009 at 7:51 PM, Tom Lane 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

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 po

Re: [HACKERS] WIP: Deferrable unique constraints

2009/7/27 Jeff Davis : > 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 shoul

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, on

Re: [HACKERS] WIP: Deferrable unique constraints

[ still poking around in this patch ] Jeff Davis 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

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 bina

Re: [HACKERS] potential bug with query optimizer and functions

On Mon, Jul 27, 2009 at 3:15 PM, Zach Conrad 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 he

[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, compile

Re: [HACKERS] WIP: Deferrable unique constraints

2009/7/27 Tom Lane : > (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. B

Re: [HACKERS] WIP: Deferrable unique constraints

2009/7/27 Tom Lane : > Jeff Davis 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

Re: [HACKERS] WIP: Deferrable unique constraints

Dean Rasheed 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

Re: [HACKERS] WIP: Deferrable unique constraints

Jeff Davis 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 ge

Re: [HACKERS] WIP: Deferrable unique constraints

2009/7/27 Jeff Davis : > 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

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

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 unf

Re: [HACKERS] proposal: support empty string as separator for string_to_array

2009/7/27 Tom Lane : > Pavel Stehule 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 reques

Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?

On Mon, Jul 27, 2009 at 12:02 PM, Andrew Dunstan wrote: > Merlin Moncure wrote: >> >> On Mon, Jul 27, 2009 at 10:20 AM, Tom Lane wrote: >> >>> >>> Alvaro Herrera writes: >>> We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA

Re: [HACKERS] proposal: support empty string as separator for string_to_array

Pavel Stehule 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 e

Re: [HACKERS] proposal: support empty string as separator for string_to_array

On Mon, Jul 27, 2009 at 12:42 PM, Pavel Stehule wrote: > 2009/7/25 Merlin Moncure : >> On Fri, Jul 24, 2009 at 11:40 PM, Pavel Stehule >> wrote: >>> Hello >>> >>> I have one idea, that should simplify string to char array >>> transformation. The base is idea: between every char is empty string, >>

Re: [HACKERS] WIP: Deferrable unique constraints

Dean Rasheed 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 exe

Re: [HACKERS] proposal: support empty string as separator for string_to_array

2009/7/27 Kevin Grittner : > Pavel Stehule 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

Re: [HACKERS] proposal: support empty string as separator for string_to_array

Pavel Stehule 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 lis

Re: [HACKERS] proposal: support empty string as separator for string_to_array

2009/7/25 Merlin Moncure : > On Fri, Jul 24, 2009 at 11:40 PM, Pavel Stehule > 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 funct

Re: [HACKERS] Review: Revise parallel pg_restore's scheduling heuristic

Andrew Dunstan 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

Re: [HACKERS] Review: Revise parallel pg_restore's scheduling heuristic

Kevin Grittner wrote: Andrew Dunstan 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 ha

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 Lane wrote: Alvaro Herrera 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 c

Re: [HACKERS] Review: Revise parallel pg_restore's scheduling heuristic

Andrew Dunstan 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 perf

Re: [HACKERS] When is a record NULL?

On Mon, Jul 27, 2009 at 9:48 AM, Kevin Grittner 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:

Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?

Merlin Moncure writes: > On Mon, Jul 27, 2009 at 10:20 AM, Tom Lane 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

Re: [HACKERS] Non-blocking communication between a frontend and a backend (pqcomm)

Fujii Masao writes: > On Sat, Jul 25, 2009 at 8:39 AM, Tom Lane 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 >>

Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?

On Mon, Jul 27, 2009 at 10:20 AM, Tom Lane wrote: > Alvaro Herrera 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 >> btr

Re: [HACKERS] Review: support for multiplexing SIGUSR1

Fujii Masao writes: > On Mon, Jul 27, 2009 at 2:43 AM, Tom Lane 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 Pr

Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?

Alvaro Herrera 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 proposin

Re: [HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on

Magnus Hagander 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

[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 Me

Re: [HACKERS] When is a record NULL?

Sam Mason 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 academ

Re: [HACKERS] autogenerating headers & bki stuff

Peter Eisentraut 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 >> s

Re: [HACKERS] When is a record NULL?

Greg Stark wrote: > Kevin Grittner 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 whi

[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 ...I'd ran the patched 8.4 version and PostgreSQL ran successfully with

Re: [HACKERS] mixed, named notation support

--On Sonntag, Juli 26, 2009 06:17:49 +0200 Pavel Stehule 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) T

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.

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 tim

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

Re: [HACKERS] CommitFest Status Summary - 2009-07-25

--On Sonntag, Juli 26, 2009 15:43:28 -0400 Robert Haas 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.o

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

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 so

[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 pgcry

Re: [HACKERS] query - change in gistentryinit between 8.1 and 8.2

Hi, Pavel Stehule 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 compres

Re: [HACKERS] autogenerating headers & bki stuff

On Mon, Jul 27, 2009 at 4:17 AM, Peter Eisentraut 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

Re: [HACKERS] improvements for dict_xsyn extended synonym dictionary - RRR

Andres Freund 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

[HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on

On Sat, Jul 25, 2009 at 19:50, Tom Lane 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

Re: [HACKERS] Non-blocking communication between a frontend and a backend (pqcomm)

Hi, On Sat, Jul 25, 2009 at 8:39 AM, Tom Lane 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 mig

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

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@postgre

Re: [HACKERS] Non-blocking communication between a frontend and a backend (pqcomm)

Hi, On Sun, Jul 26, 2009 at 6:42 AM, Robert Haas 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 w

Re: [HACKERS] Review: support for multiplexing SIGUSR1

Hi, Thanks for reviewing the patch! On Mon, Jul 27, 2009 at 2:43 AM, Tom Lane 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

Re: [HACKERS] Multicore builds on MSVC

On Fri, Jul 24, 2009 at 21:33, Dave Page wrote: > On Fri, Jul 24, 2009 at 8:07 PM, Magnus Hagander 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 backpatc