Re: [HACKERS] why do we need two snapshots per query?

2011-12-19 Thread Greg Smith
This feature has now passed through review by Dimitri with him no longer having anything to say about it. I've marked it ready for committer now. Seems the main decision left here is whether another committer wants to take a look at this, or if Robert wants to take a spin on the buildfarm

Re: [HACKERS] why do we need two snapshots per query?

2011-12-16 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: I thought about adjusting it, but I didn't see what it made sense to adjust it to. It still is the parameter used for parameter I/O and parsing/planning, so the existing text isn't wrong. It will possibly also get reused for execution, but the

Re: [HACKERS] why do we need two snapshots per query?

2011-12-13 Thread Robert Haas
On Sat, Nov 26, 2011 at 2:50 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: +     /* Done with the snapshot used for parameter I/O and parsing/planning */ +     if (snapshot_set) +             PopActiveSnapshot(); This comment needs adjusting. I thought about adjusting it, but I didn't

Re: [HACKERS] why do we need two snapshots per query?

2011-11-26 Thread Dimitri Fontaine
Hi, Robert Haas robertmh...@gmail.com writes: On Sun, Nov 13, 2011 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: In the -M extended case, we take a snapshot from exec_parse_message(), and the same two in the exec_bind_message() call that are taken in the -M prepared case.  So reducing

Re: [HACKERS] why do we need two snapshots per query?

2011-11-14 Thread Robert Haas
On Sun, Nov 13, 2011 at 9:40 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Nov 13, 2011 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: In the -M extended case, we take a snapshot from exec_parse_message(), and the same two in the exec_bind_message() call that are taken in the -M

Re: [HACKERS] why do we need two snapshots per query?

2011-11-14 Thread Greg Smith
On 11/14/2011 04:04 PM, Robert Haas wrote: Some pgbench -S numbers (SELECT-only test) from Nate Boley's 32-core box It seems like Nate Boley's system should be be credited in the 9.2 release notes. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL

Re: [HACKERS] why do we need two snapshots per query?

2011-11-14 Thread Robert Haas
On Nov 14, 2011, at 4:31 PM, Greg Smith g...@2ndquadrant.com wrote: On 11/14/2011 04:04 PM, Robert Haas wrote: Some pgbench -S numbers (SELECT-only test) from Nate Boley's 32-core box It seems like Nate Boley's system should be be credited in the 9.2 release notes. +1. Having access

Re: [HACKERS] why do we need two snapshots per query?

2011-11-14 Thread Tatsuo Ishii
On the other hand, if our goal in life is to promote the extended query protocol over the simple query protocol at all costs, then I agree that we shouldn't optimize the simple query protocol in any way. Perhaps we should even post a big notice on it that says this facility is deprecated and

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: If we could be certain that a query was being executed immediately ... that is, with the same snapshot ... then it would be possible to simplify expressions using stable functions as if they were constants. My earlier patch did exactly that. Mph. I

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Simon Riggs
On Sun, Nov 13, 2011 at 4:09 PM, Tom Lane t...@sss.pgh.pa.us wrote: As far as partitioning goes, the correct solution there is to move the partition selection to run-time, so we should not be contorting query semantics to make incremental performance improvements with the existing

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: It's all very well to refuse individual cases where linkage is required, but ISTM clear that there are many possible uses of being able to tell whether a plan is one-shot or not and nothing lost by allowing that information (a boolean) pass to the

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Robert Haas
On Sun, Nov 13, 2011 at 11:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: If we could be certain that a query was being executed immediately ... that is, with the same snapshot ... then it would be possible to simplify expressions using stable functions

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Sun, Nov 13, 2011 at 11:09 AM, Tom Lane t...@sss.pgh.pa.us wrote: I remain of the opinion that Robert's proposal is a bad idea. Wait a minute. I can understand why you think it's a bad idea to preserve a snapshot across multiple protocol messages

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Kevin Grittner
Tom Lane wrote: Robert Haas writes: I can understand why you think it's a bad idea to preserve a snapshot across multiple protocol messages (parse/bind/execute), but why or how would it be a bad idea to keep the same snapshot between planning and execution when the whole thing is being

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Robert Haas
On Sun, Nov 13, 2011 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Wait a minute.  I can understand why you think it's a bad idea to preserve a snapshot across multiple protocol messages (parse/bind/execute), but why or how would it be a bad idea to keep the same snapshot between planning

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Florian Pflug
On Nov14, 2011, at 00:13 , Robert Haas wrote: On Sun, Nov 13, 2011 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: In that case you must be of the opinion that extended query protocol is a bad idea and we should get rid of it, and the same for prepared plans of all types. What you're

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Robert Haas
On Sun, Nov 13, 2011 at 6:45 PM, Florian Pflug f...@phlo.org wrote: On Nov14, 2011, at 00:13 , Robert Haas wrote: On Sun, Nov 13, 2011 at 12:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: In that case you must be of the opinion that extended query protocol is a bad idea and we should get rid of it,

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Robert Haas
On Sun, Nov 13, 2011 at 7:37 PM, Robert Haas robertmh...@gmail.com wrote: In my experience, it's hard enough as it is to convince developers to use statement parameters instead of interpolating them into the SQL string. Once word gets out that the simple protocol is now has less locking

Re: [HACKERS] why do we need two snapshots per query?

2011-11-13 Thread Robert Haas
On Sun, Nov 13, 2011 at 8:57 PM, Robert Haas robertmh...@gmail.com wrote: In the -M extended case, we take a snapshot from exec_parse_message(), and the same two in the exec_bind_message() call that are taken in the -M prepared case.  So reducing the prepared case from two snapshots to one

Re: [HACKERS] why do we need two snapshots per query?

2011-11-12 Thread Florian Pflug
On Nov11, 2011, at 19:17 , Tom Lane wrote: But frankly I do not like any of these proposals. Making fundamental changes in long-established semantics in the name of squeezing out a few cycles is the wrong way to design software. Hm, then maybe this is one of the things to put onto the next

Re: [HACKERS] why do we need two snapshots per query?

2011-11-12 Thread Robert Haas
On Sat, Nov 12, 2011 at 5:09 PM, Florian Pflug f...@phlo.org wrote: On Nov11, 2011, at 19:17 , Tom Lane wrote: But frankly I do not like any of these proposals.  Making fundamental changes in long-established semantics in the name of squeezing out a few cycles is the wrong way to design

Re: [HACKERS] why do we need two snapshots per query?

2011-11-12 Thread Simon Riggs
On Fri, Nov 11, 2011 at 10:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Tom, in that earlier thread you said you'd be doing something in this release about that. Can you say more about what that was, and will you be doing it still?

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Can't we arrange to retain the snapshot used for parse analysis / planning and reuse it for the portal that we create just afterwards? Possibly, but what if planning takes a long time? Also, I think you're ignoring the extended-query-protocol

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Robert Haas
On Fri, Nov 11, 2011 at 10:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Can't we arrange to retain the snapshot used for parse analysis / planning and reuse it for the portal that we create just afterwards? Possibly, but what if planning takes a long

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Florian Pflug
On Nov11, 2011, at 16:18 , Robert Haas wrote: In the extend query protocol scenario, it seems to me that keeping the snapshot would be both wrong and a bad idea. It would be wrong because the user will (I think) expect the query can see all rows that were marked as committed prior to Execute

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Tom Lane
Florian Pflug f...@phlo.org writes: On Nov11, 2011, at 16:18 , Robert Haas wrote: In the extend query protocol scenario, it seems to me that keeping the snapshot would be both wrong and a bad idea. Hm, but that'd penalize clients who use the extended query protocol, which they have to if

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Florian Pflug
On Nov11, 2011, at 17:06 , Tom Lane wrote: Florian Pflug f...@phlo.org writes: On Nov11, 2011, at 16:18 , Robert Haas wrote: In the extend query protocol scenario, it seems to me that keeping the snapshot would be both wrong and a bad idea. Hm, but that'd penalize clients who use the

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Tom Lane
Florian Pflug f...@phlo.org writes: Still, optimizing only the simple protocol seems weird. Would it be sane to decree that the statement snapshot lasts until Sync is received, in extended query mode? But frankly I do not like any of these proposals. Making fundamental changes in

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Dimitri Fontaine
Robert Haas robertmh...@gmail.com writes: Considering that GetSnapshotData() is the number-one consumer of CPU time on many profiling runs I've done, this seems needlessly inefficient. Can't we arrange to retain the snapshot used for parse analysis / planning and reuse it for the portal that

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Robert Haas
On Fri, Nov 11, 2011 at 2:21 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: Considering that GetSnapshotData() is the number-one consumer of CPU time on many profiling runs I've done, this seems needlessly inefficient.  Can't we arrange to retain

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Simon Riggs
On Fri, Nov 11, 2011 at 7:21 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: Considering that GetSnapshotData() is the number-one consumer of CPU time on many profiling runs I've done, this seems needlessly inefficient.  Can't we arrange to retain

Re: [HACKERS] why do we need two snapshots per query?

2011-11-11 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes: Tom, in that earlier thread you said you'd be doing something in this release about that. Can you say more about what that was, and will you be doing it still?