Re: [HACKERS] Moving more work outside WALInsertLock

2011-12-23 Thread Heikki Linnakangas
On 16.12.2011 15:42, Heikki Linnakangas wrote: On 16.12.2011 15:03, Simon Riggs wrote: On Fri, Dec 16, 2011 at 12:50 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 16.12.2011 14:37, Simon Riggs wrote: I already proposed a design for that using page-level share locks any

Re: [HACKERS] Real-life range datasets

2011-12-23 Thread Alexander Korotkov
Hello, On Thu, Dec 22, 2011 at 12:51 PM, Benedikt Grundmann bgrundm...@janestreet.com wrote: I should be able to give you a table with the same characteristics as the instruments table but bogus data by replacing all entries in the table with random strings of the same length or something

Re: [HACKERS] Extensions and 9.2

2011-12-23 Thread Daniel Farina
On Wed, Dec 21, 2011 at 5:46 AM, Robert Haas robertmh...@gmail.com wrote: Assuming the command in question can be stuffed inside a function, the most you're gaining is a little notational convenience I can answer that one (why a full-blown mechanism for a notational convenience). It has

Re: [HACKERS] Making TEXT NUL-transparent

2011-12-23 Thread Florian Weimer
* Florian Pflug: On Nov24, 2011, at 10:54 , Florian Weimer wrote: Or is it not only about being able to *store* NULs in a text field? No, the entire core should be NUL-transparent. That's unlikely to happen. Yes, with the type input/output functions tied to NUL-terminated strings, that

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-12-23 Thread Kohei KaiGai
2011/12/22 Robert Haas robertmh...@gmail.com: On Mon, Dec 12, 2011 at 12:00 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote: The v8.option-2 add checks around examine_simple_variable, and prevent to reference statistical data, if Var node tries to reference relation with security-barrier attribute.

Re: [HACKERS] reprise: pretty print viewdefs

2011-12-23 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: I *still* spend a lot of time editing in a 25x80 window. 80 is a good choice whatever the screen size, because it's about the most efficient text width as far as eyes movements are concerned: the eye is much better at going top-bottom than left-right.

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Magnus Hagander
On Tue, Dec 20, 2011 at 14:08, Euler Taveira de Oliveira eu...@timbira.com wrote: On 20-12-2011 07:27, Magnus Hagander wrote: On Tue, Dec 6, 2011 at 19:06, Robert Haas robertmh...@gmail.com wrote: On Tue, Dec 6, 2011 at 1:00 PM, Euler Taveira de Oliveira eu...@timbira.com wrote: On 06-12-2011

[HACKERS] Another hstore_type idea

2011-12-23 Thread Marc Mamin
Hello, after reading the thread on Typed hstore proposal, I wonder if another minded extension of hstore would be benefical: add additional hstore types with numerical data type for the values. e.g.: hstore_float : text - float This should allow to add some nice aggregation function on these

Re: [HACKERS] Moving more work outside WALInsertLock

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 3:15 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 23.12.2011 10:13, Heikki Linnakangas wrote: So, here's a WIP patch of what I've been working on. And here's the patch I forgot to attach.. Fails regression tests for me. I found this in

Re: [HACKERS] [v9.2] Fix Leaky View Problem

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 5:56 AM, Kohei KaiGai kai...@kaigai.gr.jp wrote: I'd like the regression test on select_view test being committed also to detect unexpected changed in the future. How about it? Can you resend that as a separate patch? I remember there were some things I didn't like

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Robert Haas
On Thu, Dec 22, 2011 at 11:49 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Dec 21, 2011 at 5:04 AM, Pavel Stehule pavel.steh...@gmail.com wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Looks fine to me.  I'll commit this, barring objections.

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Robert Haas
2011/12/22 Tomas Vondra t...@fuzzy.cz: The gettimeofday() calls are not exactly cheap in some cases, so why to pay that price when all you need is the number of rows? Fair point. The patch attached does this: 1) adds INSTRUMENT_ROWS, a new InstrumentOption   - counts rows without timing

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tomas Vondra
On 23.12.2011 14:57, Robert Haas wrote: 2011/12/22 Tomas Vondra t...@fuzzy.cz: The gettimeofday() calls are not exactly cheap in some cases, so why to pay that price when all you need is the number of rows? Fair point. The patch attached does this: 1) adds INSTRUMENT_ROWS, a new

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Tom Lane
Magnus Hagander mag...@hagander.net writes: At this point, my question is: do we want to support the lsn data type idea or a basic function that implements the difference between LSNs? Personally I think a function is enough - it solves the only case that I've actually seen. But a datatype

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: I too think a datatype is overkill, if we're only planning on providing one function. Are there any other functions we ought to provide? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Andrew Dunstan
On 12/23/2011 10:05 AM, Tom Lane wrote: Magnus Hagandermag...@hagander.net writes: At this point, my question is: do we want to support the lsn data type idea or a basic function that implements the difference between LSNs? Personally I think a function is enough - it solves the only case

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tom Lane
Tomas Vondra t...@fuzzy.cz writes: One thing I'm wondering about is that the InstrumentOptions are not exclusive - INSTRUMENT_TIMER means 'collect timing and row counts' while INSTRUMENT_ROWS means 'collect row counts'. Wouldn't it be better to redefine the INSTRUMENT_TIMER so that it collects

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: I too think a datatype is overkill, if we're only planning on providing one function. Are there any other functions we ought to provide? Even if there are several, what exact

Re: [HACKERS] Review: Non-inheritable check constraints

2011-12-23 Thread Robert Haas
On Thu, Dec 22, 2011 at 10:54 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: I agree with Robert that this usage of ALTER TABLE ONLY is slightly different from other usages of the same command, but I disagree that this means that we need another command to do what we want to do here.

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 10:05 AM, Tom Lane t...@sss.pgh.pa.us wrote: I too think a datatype is overkill, if we're only planning on providing one function. Are there any other

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Even if there are several, what exact advantage does a datatype offer over representing LSN values as numerics? It seems to me to be adding complication and extra code (I/O

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Kevin Grittner
Kevin Grittner kevin.gritt...@wicourts.gov wrote: I would suggest you examine how to have an array of N bgwriters, then just slot the code for hinting into the bgwriter. That way a bgwriter can set hints, calc CRC and write pages in sequence on a particular block. The hinting needs to be

Re: [HACKERS] xlog location arithmetic

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 10:18 AM, Tom Lane t...@sss.pgh.pa.us wrote: Even if there are several, what exact advantage does a datatype offer over representing LSN values as numerics?  

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Alvaro Herrera
Excerpts from Tomas Vondra's message of vie dic 23 11:45:40 -0300 2011: One thing I'm wondering about is that the InstrumentOptions are not exclusive - INSTRUMENT_TIMER means 'collect timing and row counts' while INSTRUMENT_ROWS means 'collect row counts'. Wouldn't it be better to redefine

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 11:14 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thoughts? Those are good thoughts. Here's another random idea, which might be completely nuts. Maybe we could consider some kind of summarization of CLOG data, based on the idea that most transactions commit.

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: An obvious problem is that, if the abort rate is significantly different from zero, and especially if the aborts are randomly mixed in with commits rather than clustered together in small portions of the XID space, the CLOG rollup data would become

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Peter Eisentraut
On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it string_agg? All the function names are the same between text and bytea (e.g., ||, substr, position, length). It would be nice not to introduce

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Pavel Stehule
Hello 2011/12/23 Peter Eisentraut pete...@gmx.net: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it string_agg?  All the function names are the same between text and bytea (e.g., ||, substr,

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Pavel Stehule
Hello 2011/12/23 Peter Eisentraut pete...@gmx.net: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it string_agg?  All the function names are the same between text and bytea (e.g., ||, substr,

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 12:51 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it string_agg?  All the function names are the same between text and

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 12:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: An obvious problem is that, if the abort rate is significantly different from zero, and especially if the aborts are randomly mixed in with commits rather than clustered together in

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tomas Vondra
On 23.12.2011 16:14, Tom Lane wrote: Tomas Vondra t...@fuzzy.cz writes: One thing I'm wondering about is that the InstrumentOptions are not exclusive - INSTRUMENT_TIMER means 'collect timing and row counts' while INSTRUMENT_ROWS means 'collect row counts'. Wouldn't it be better to redefine

Re: [HACKERS] Another hstore_type idea

2011-12-23 Thread Christopher Browne
On Fri, Dec 23, 2011 at 7:06 AM, Marc Mamin m.ma...@intershop.de wrote: after reading the thread on Typed hstore proposal, I wonder if another minded extension of hstore would be benefical: add additional hstore types with numerical data type for the values. I would expect the primary

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Peter Eisentraut
On fre, 2011-12-23 at 13:30 -0500, Robert Haas wrote: On Fri, Dec 23, 2011 at 12:51 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 2:16 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2011-12-23 at 13:30 -0500, Robert Haas wrote: On Fri, Dec 23, 2011 at 12:51 PM, Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com wrote: Peter Eisentraut pete...@gmx.net wrote: Robert Haas wrote: Peter Eisentraut pete...@gmx.net wrote: On ons, 2011-12-21 at 11:04 +0100, Pavel Stehule wrote: this patch adds a bytea_agg aggregation. It allow fast bytea concatetation. Why not call it

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Jeff Janes
On 12/23/11, Robert Haas robertmh...@gmail.com wrote: On Fri, Dec 23, 2011 at 11:14 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Thoughts? Those are good thoughts. Here's another random idea, which might be completely nuts. Maybe we could consider some kind of summarization of

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: An obvious problem is that, if the abort rate is significantly different from zero, and especially if the aborts are randomly mixed in with commits rather than clustered together in small portions of the XID space,

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Kevin Grittner
Jeff Janes jeff.ja...@gmail.com wrote: Could we get some major OLTP users to post their CLOG for analysis? I wouldn't think there would be much security/propietary issues with CLOG data. FWIW, I got the raw numbers to do my quick check using this Ruby script (put together for me by Peter

Re: [HACKERS] Page Checksums + Double Writes

2011-12-23 Thread Tom Lane
Jeff Janes jeff.ja...@gmail.com writes: I had a perhaps crazier idea. Aren't CLOG pages older than global xmin effectively read only? Could backends that need these bypass locking and shared memory altogether? Hmm ... once they've been written out from the SLRU arena, yes. In fact you don't

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 2:16 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2011-12-23 at 13:30 -0500, Robert Haas wrote: Well, because it doesn't operate on strings. Sure, binary strings.  Both the SQL standard and the PostgreSQL documentation

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tom Lane
Tomas Vondra t...@fuzzy.cz writes: The motivation for this patch was that collection timing data often causes performance issues and in some cases it's not needed. But is this true for row counts? Perhaps more to the point, is there a use case for collecting timing data without row counts? I

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Pavel Stehule
Hello 2011/12/23 Tom Lane t...@sss.pgh.pa.us: Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 2:16 PM, Peter Eisentraut pete...@gmx.net wrote: On fre, 2011-12-23 at 13:30 -0500, Robert Haas wrote: Well, because it doesn't operate on strings. Sure, binary strings.  Both the

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Pavel Stehule
2011/12/23 Tom Lane t...@sss.pgh.pa.us: Tomas Vondra t...@fuzzy.cz writes: The motivation for this patch was that collection timing data often causes performance issues and in some cases it's not needed. But is this true for row counts? Perhaps more to the point, is there a use case for

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Kevin Grittner
Pavel Stehule pavel.steh...@gmail.com wrote: maybe we can introduce a synonym type for bytea - like binary string or bstring. The standard mentions these names for binary strings: BINARY, BINARY VARYING, or BINARY LARGE OBJECT which have a certain symmetry with: CHARACTER, CHARACTER

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes: 2011/12/23 Tom Lane t...@sss.pgh.pa.us: Tomas Vondra t...@fuzzy.cz writes: The motivation for this patch was that collection timing data often causes performance issues and in some cases it's not needed. But is this true for row counts? Perhaps

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Tomas Vondra
Dne 23.12.2011 22:37, Pavel Stehule napsal(a): 2011/12/23 Tom Lanet...@sss.pgh.pa.us: Tomas Vondrat...@fuzzy.cz writes: The motivation for this patch was that collection timing data often causes performance issues and in some cases it's not needed. But is this true for row counts? Perhaps

[HACKERS] Representation of index clause lists

2011-12-23 Thread Tom Lane
No sooner had I committed e2c2c2e8b1df7dfdb01e7e6f6191a569ce3c3195 than I started having second thoughts about the choice of representation. The requirement is to tell which index column each potential index qual is meant to be used with. I used a list-of-sublists representation, in which each

Re: [HACKERS] patch: bytea_agg

2011-12-23 Thread Alvaro Herrera
Excerpts from Pavel Stehule's message of vie dic 23 18:36:11 -0300 2011: Hello 2011/12/23 Tom Lane t...@sss.pgh.pa.us: I generally agree with Peter: string_agg makes sense here.  The only real argument against it is Pavel's point that he didn't include a delimiter parameter, but that

Re: [HACKERS] Representation of index clause lists

2011-12-23 Thread Robert Haas
On Fri, Dec 23, 2011 at 7:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Thoughts either way? OidFunctionCall11? How about making a struct out of some or all of those arguments and passing that? What about using arrays rather than Lists? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] Representation of index clause lists

2011-12-23 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Fri, Dec 23, 2011 at 7:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: Thoughts either way? OidFunctionCall11? How about making a struct out of some or all of those arguments and passing that? Well, that was what I meant by the allusion to IndexPath

Re: [HACKERS] WIP: explain analyze with 'rows' but not timing

2011-12-23 Thread Pavel Stehule
2011/12/23 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: 2011/12/23 Tom Lane t...@sss.pgh.pa.us: Tomas Vondra t...@fuzzy.cz writes: The motivation for this patch was that collection timing data often causes performance issues and in some cases it's not needed. But