Horton

Thread

  1. Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-22T17:43:35Z

    Hi,

 From the code I understood that when executing a query "normally", in 
READ COMMITTED mode, we take a new snapshot for every query that comes 
out of rewrite.  But in an EXPLAIN ANALYZE, we only update the CID of 
the snapshot taken when the EXPLAIN started.

Did I misunderstand the code?  And if I didn't, why do we do this 
differently?


Regards,
Marko Tiikkaja

  2. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    David Fetter <david@fetter.org> — 2010-07-23T17:52:39Z

    On Thu, Jul 22, 2010 at 08:43:35PM +0300, Marko Tiikkaja wrote:
> Hi,
> 
> From the code I understood that when executing a query "normally",
> in READ COMMITTED mode, we take a new snapshot for every query that
> comes out of rewrite.  But in an EXPLAIN ANALYZE, we only update the
> CID of the snapshot taken when the EXPLAIN started.
> 
> Did I misunderstand the code?  And if I didn't, why do we do this
> differently?

You mentioned in IRC that this was in aid of getting wCTEs going.  How
are these things connected?

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  3. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T18:13:18Z

    On 7/23/2010 8:52 PM, David Fetter wrote:
> On Thu, Jul 22, 2010 at 08:43:35PM +0300, Marko Tiikkaja wrote:
>> Did I misunderstand the code?  And if I didn't, why do we do this
>> differently?
>
> You mentioned in IRC that this was in aid of getting wCTEs going.  How
> are these things connected?

Currently, I'm trying to make wCTEs behave a bit like RULEs do.  But if 
every rewrite product takes a new snapshot, wCTEs will behave very 
unpredictably.

But because EXPLAIN ANALYZE does *not* take a new snapshot for every 
rewrite product, I'm starting to think that maybe this isn't the 
behaviour we wanted to begin with?


Regards,
Marko Tiikkaja

  4. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-07-23T19:00:47Z

    On Fri, Jul 23, 2010 at 2:13 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
> On 7/23/2010 8:52 PM, David Fetter wrote:
>>
>> On Thu, Jul 22, 2010 at 08:43:35PM +0300, Marko Tiikkaja wrote:
>>>
>>> Did I misunderstand the code?  And if I didn't, why do we do this
>>> differently?
>>
>> You mentioned in IRC that this was in aid of getting wCTEs going.  How
>> are these things connected?
>
> Currently, I'm trying to make wCTEs behave a bit like RULEs do.  But if
> every rewrite product takes a new snapshot, wCTEs will behave very
> unpredictably.
>
> But because EXPLAIN ANALYZE does *not* take a new snapshot for every rewrite
> product, I'm starting to think that maybe this isn't the behaviour we wanted
> to begin with?

Where should I be looking in the code for this?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  5. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T19:06:09Z

    On 7/23/2010 10:00 PM, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 2:13 PM, Marko Tiikkaja
> <marko.tiikkaja@cs.helsinki.fi>  wrote:
>> Currently, I'm trying to make wCTEs behave a bit like RULEs do.  But if
>> every rewrite product takes a new snapshot, wCTEs will behave very
>> unpredictably.
>>
>> But because EXPLAIN ANALYZE does *not* take a new snapshot for every rewrite
>> product, I'm starting to think that maybe this isn't the behaviour we wanted
>> to begin with?
>
> Where should I be looking in the code for this?

ProcessQuery() and ExplainOnePlan().  ProcessQuery calls 
PushActiveSnapshot(GetTransactionSnapshot()) for every statement while 
ExplainOnePlan calls PushUpdatedSnapshot(GetActiveSnapshot()).


Regards,
Marko Tiikkaja

  6. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T19:19:53Z

    On 7/23/2010 10:06 PM, I wrote:
> ProcessQuery() and ExplainOnePlan().  ProcessQuery calls
> PushActiveSnapshot(GetTransactionSnapshot()) for every statement while
> ExplainOnePlan calls PushUpdatedSnapshot(GetActiveSnapshot()).

Here's a test case demonstrating the difference:

=# create table foo(a int);
CREATE TABLE
=# create table bar(a int);
CREATE TABLE
=# create table baz(a int);
CREATE TABLE

=# create rule foorule as on update to foo do instead (select 
pg_sleep(5); insert into bar select * from baz);
CREATE RULE

=# insert into foo values(0);


no EXPLAIN:

=# truncate bar, baz;
TRUNCATE TABLE

T1=# begin; update foo set a=a;
BEGIN
-- sleeps..

T2=# insert into baz values(0);
INSERT 0 1

-- T1 returns
  pg_sleep
----------

(1 row)

UPDATE 0

T1=# select * from bar;
  a
---
  0
(1 row)



EXPLAIN:

=# truncate bar, baz;
TRUNCATE TABLE

T1=# begin; explain analyze update foo set a=a;
BEGIN
-- sleeps..

T2=# insert into baz values(0);
INSERT 0 1

-- T1 returns
(QUERY PLAN)

T1=# select * from bar;
  a
---
(0 rows)


This may be a bit hard to follow, but essentially what happens is that 
in EXPLAIN ANALYZE, the INSERT in the rule does not see the changes made 
by T2 to baz while in the regular execution scenario it does.


Regards,
Marko Tiikkaja

  7. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-07-23T19:30:03Z

    On Fri, Jul 23, 2010 at 3:19 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
> This may be a bit hard to follow, but essentially what happens is that in
> EXPLAIN ANALYZE, the INSERT in the rule does not see the changes made by T2
> to baz while in the regular execution scenario it does.

Well that's gotta be a bug, but in what I'm not sure.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  8. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    David Fetter <david@fetter.org> — 2010-07-23T19:31:59Z

    On Fri, Jul 23, 2010 at 03:30:03PM -0400, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 3:19 PM, Marko Tiikkaja
> <marko.tiikkaja@cs.helsinki.fi> wrote:
> > This may be a bit hard to follow, but essentially what happens is
> > that in EXPLAIN ANALYZE, the INSERT in the rule does not see the
> > changes made by T2 to baz while in the regular execution scenario
> > it does.
> 
> Well that's gotta be a bug, but in what I'm not sure.

I've said it before, and I'll say it again.  User-accessible RULEs are
themselves a bug :P

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

  9. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-07-23T19:32:52Z

    On Fri, Jul 23, 2010 at 3:31 PM, David Fetter <david@fetter.org> wrote:
> On Fri, Jul 23, 2010 at 03:30:03PM -0400, Robert Haas wrote:
>> On Fri, Jul 23, 2010 at 3:19 PM, Marko Tiikkaja
>> <marko.tiikkaja@cs.helsinki.fi> wrote:
>> > This may be a bit hard to follow, but essentially what happens is
>> > that in EXPLAIN ANALYZE, the INSERT in the rule does not see the
>> > changes made by T2 to baz while in the regular execution scenario
>> > it does.
>>
>> Well that's gotta be a bug, but in what I'm not sure.
>
> I've said it before, and I'll say it again.  User-accessible RULEs are
> themselves a bug :P

Maybe so, but perhaps it would be more productive to concentrate on
solving the immediate problem first.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  10. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Andres Freund <andres@anarazel.de> — 2010-07-23T19:46:15Z

    On Fri, Jul 23, 2010 at 03:30:03PM -0400, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 3:19 PM, Marko Tiikkaja
> <marko.tiikkaja@cs.helsinki.fi> wrote:
> > This may be a bit hard to follow, but essentially what happens is that in
> > EXPLAIN ANALYZE, the INSERT in the rule does not see the changes made by T2
> > to baz while in the regular execution scenario it does.
>
> Well that's gotta be a bug, but in what I'm not sure.
One could argue that its less of a semantic change changing explain's
behaviour than the normal executors way of working...

Andres

  11. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T20:09:26Z

    On 7/23/2010 10:46 PM, Andres Freund wrote:
> On Fri, Jul 23, 2010 at 03:30:03PM -0400, Robert Haas wrote:
>> On Fri, Jul 23, 2010 at 3:19 PM, Marko Tiikkaja
>> <marko.tiikkaja@cs.helsinki.fi>  wrote:
>>> This may be a bit hard to follow, but essentially what happens is that in
>>> EXPLAIN ANALYZE, the INSERT in the rule does not see the changes made by T2
>>> to baz while in the regular execution scenario it does.
>>
>> Well that's gotta be a bug, but in what I'm not sure.
> One could argue that its less of a semantic change changing explain's
> behaviour than the normal executors way of working...

One could also argue that people usually test their code with EXPLAIN 
ANALYZE and could've made the opposite conclusion based on its output. ;-)

But I really have no idea what we should do about this.  It seems to me 
that EXPLAIN ANALYZE's behaviour is less surprising (that's actually 
what I, before yesterday, always thought happens), but I'm not too sure 
about that either.


Regards,
Marko Tiikkaja

  12. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-07-23T21:37:10Z

    Excerpts from Marko Tiikkaja's message of vie jul 23 14:13:18 -0400 2010:
> On 7/23/2010 8:52 PM, David Fetter wrote:
> > On Thu, Jul 22, 2010 at 08:43:35PM +0300, Marko Tiikkaja wrote:
> >> Did I misunderstand the code?  And if I didn't, why do we do this
> >> differently?
> >
> > You mentioned in IRC that this was in aid of getting wCTEs going.  How
> > are these things connected?
> 
> Currently, I'm trying to make wCTEs behave a bit like RULEs do.  But if 
> every rewrite product takes a new snapshot, wCTEs will behave very 
> unpredictably.

I don't think it's fair game to change the behavior of multiple-output
rules at this point.  However, I also think that it's unwise to base
wCTEs on the behavior of rules -- rules are widely considered broken and
unusable for nontrivial cases.

Also, I think that having a moving snapshot for the different parts of a
wCTE is going to mean they're unpredictable.  For predictable usage
you'll be forcing the user to always wrap them in SERIALIZABLE
transactions.

In short I think a wCTE should only advance the CID, not get a whole new
snapshot.

  13. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Kevin Grittner <kevin.grittner@wicourts.gov> — 2010-07-23T21:42:21Z

    Alvaro Herrera <alvherre@commandprompt.com> wrote:
 
> In short I think a wCTE should only advance the CID, not get a
> whole new snapshot.
 
Even after unblocking from a write conflict at the READ COMMITTED
isolation level?
 
-Kevin

  14. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T21:44:21Z

    On 7/24/10 12:37 AM +0300, Alvaro Herrera wrote:
> Excerpts from Marko Tiikkaja's message of vie jul 23 14:13:18 -0400 2010:
>> On 7/23/2010 8:52 PM, David Fetter wrote:
>>> On Thu, Jul 22, 2010 at 08:43:35PM +0300, Marko Tiikkaja wrote:
>>>> Did I misunderstand the code?  And if I didn't, why do we do this
>>>> differently?
>>>
>>> You mentioned in IRC that this was in aid of getting wCTEs going.  How
>>> are these things connected?
>>
>> Currently, I'm trying to make wCTEs behave a bit like RULEs do.  But if
>> every rewrite product takes a new snapshot, wCTEs will behave very
>> unpredictably.
>
> I don't think it's fair game to change the behavior of multiple-output
> rules at this point.  However, I also think that it's unwise to base
> wCTEs on the behavior of rules -- rules are widely considered broken and
> unusable for nontrivial cases.

I don't want to change the behaviour either, but we have two different 
behaviours right now.  We need to change at least the other.

wCTEs are not going to be based on any of the broken behaviour of rules, 
that's for sure.  What I meant is expanding a single query into multiple 
queries and running the executor separately for all of them.

> Also, I think that having a moving snapshot for the different parts of a
> wCTE is going to mean they're unpredictable.  For predictable usage
> you'll be forcing the user to always wrap them in SERIALIZABLE
> transactions.

That is *not* what has been discussed for wCTEs.  A wCTE will only 
modify the CID of the snapshot in any isolation mode.

> In short I think a wCTE should only advance the CID, not get a whole new
> snapshot.

Agreed.


Regards,
Marko Tiikkaja

  15. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T21:48:25Z

    On 7/24/10 12:42 AM +0300, Kevin Grittner wrote:
> Alvaro Herrera<alvherre@commandprompt.com>  wrote:
>
>> In short I think a wCTE should only advance the CID, not get a
>> whole new snapshot.
>
> Even after unblocking from a write conflict at the READ COMMITTED
> isolation level?

I'm not sure what you mean by this; UPDATE and DELETE can take a look at 
the new tuple but that's completely separate from the snapshot.



Regards,
Marko Tiikkaja

  16. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Alvaro Herrera <alvherre@commandprompt.com> — 2010-07-23T22:20:42Z

    Excerpts from Marko Tiikkaja's message of vie jul 23 17:44:21 -0400 2010:
> On 7/24/10 12:37 AM +0300, Alvaro Herrera wrote:
> > Excerpts from Marko Tiikkaja's message of vie jul 23 14:13:18 -0400 2010:

> > I don't think it's fair game to change the behavior of multiple-output
> > rules at this point.  However, I also think that it's unwise to base
> > wCTEs on the behavior of rules -- rules are widely considered broken and
> > unusable for nontrivial cases.
> 
> I don't want to change the behaviour either, but we have two different 
> behaviours right now.  We need to change at least the other.

It seems like it's EXPLAIN ANALYZE that needs fixing.

> wCTEs are not going to be based on any of the broken behaviour of rules, 
> that's for sure.  What I meant is expanding a single query into multiple 
> queries and running the executor separately for all of them.

Is a wCTE going to be expanded into multiple queries?

If not, it sounds like we're all agreed.

  17. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T22:24:01Z

    On 7/24/10 1:20 AM +0300, Alvaro Herrera wrote:
> Excerpts from Marko Tiikkaja's message of vie jul 23 17:44:21 -0400 2010:
>> wCTEs are not going to be based on any of the broken behaviour of rules,
>> that's for sure.  What I meant is expanding a single query into multiple
>> queries and running the executor separately for all of them.
>
> Is a wCTE going to be expanded into multiple queries?
>
> If not, it sounds like we're all agreed.

Yes, it will have to be.  I tried to make it work for 9.0 by not 
expanding, and it didn't work out too well.


Regards,
Marko Tiikkaja

  18. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-23T22:42:59Z

    On 7/24/10 1:20 AM +0300, Alvaro Herrera wrote:
> It seems like it's EXPLAIN ANALYZE that needs fixing.

Yeah, looks like it.  I see SQL functions also take a new snapshot for 
every query.


Regards,
Marko Tiikkaja

  19. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-07-23T22:43:34Z

    On Fri, Jul 23, 2010 at 6:20 PM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
> Excerpts from Marko Tiikkaja's message of vie jul 23 17:44:21 -0400 2010:
>> On 7/24/10 12:37 AM +0300, Alvaro Herrera wrote:
>> > Excerpts from Marko Tiikkaja's message of vie jul 23 14:13:18 -0400 2010:
>
>> > I don't think it's fair game to change the behavior of multiple-output
>> > rules at this point.  However, I also think that it's unwise to base
>> > wCTEs on the behavior of rules -- rules are widely considered broken and
>> > unusable for nontrivial cases.
>>
>> I don't want to change the behaviour either, but we have two different
>> behaviours right now.  We need to change at least the other.
>
> It seems like it's EXPLAIN ANALYZE that needs fixing.

I would suggest that if we're going to change this, we back-patch it
to 9.0 before beta4.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  20. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Kevin Grittner <kevin.grittner@wicourts.gov> — 2010-07-23T22:45:15Z

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> wrote:
 
> I'm not sure what you mean by this; UPDATE and DELETE can take a
> look at the new tuple but that's completely separate from the
> snapshot.
 
Never mind -- I remembered that those could operate against tuples
not in the original snapshot, but forgot that they did it without
generating an actual fresh snapshot.
 
-Kevin

  21. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-07-24T09:44:11Z

    On 7/24/2010 1:43 AM, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 6:20 PM, Alvaro Herrera
> <alvherre@commandprompt.com>  wrote:
>> It seems like it's EXPLAIN ANALYZE that needs fixing.
>
> I would suggest that if we're going to change this, we back-patch it
> to 9.0 before beta4.

I did some digging and found the commit that changed the behaviour:
http://archives.postgresql.org/pgsql-committers/2004-09/msg00155.php and 
then later Tom fixed a bug in it: 
http://archives.postgresql.org/pgsql-committers/2005-10/msg00316.php

According to the latter commit, not updating the snapshot could be 
preferable for EXPLAIN ANALYZE, but I don't see why this is.  Maybe we 
should wait until we hear from Tom?


Regards,
Marko Tiikkaja

  22. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-04T18:45:53Z

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> writes:
> According to the latter commit, not updating the snapshot could be 
> preferable for EXPLAIN ANALYZE, but I don't see why this is.  Maybe we 
> should wait until we hear from Tom?

Sorry for not catching up on my email sooner.  On the whole I'm in
agreement with the argument that wCTEs should *not* take whole new
snapshots between queries, but only advance the CID.  The points
that sway me that way are:

1. To do otherwise will decrease the predictability of wCTE results.

2. The argument for taking a new snapshot seems to be mostly
"because that's what rules do"; but it's agreed that rules have a
lot of surprising and undesirable behavior, and it's not clear that
this isn't part of that.

3. In particular I don't agree with the argument that functions taking
new snapshots between queries should be a precedent for this.  In
a function, the user thinks of the successive lines as separate queries,
and it's reasonable for them to act similarly to what would happen if
they were issued as separate top-level commands.  I do not see that that
argument applies to wCTEs, which by definition are parts of a single
command.

In any case the behavior is going to have to be documented, but I vote
for advance-CID-only so far as wCTEs are concerned.

The other thing that was being argued was whether rules should be
changed to act that way too, and if not whether EXPLAIN ANALYZE should
be fixed to make sure it emulates rule execution better.  Personally
I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE
alone, though I'm not sure if that position can command a consensus.
I seriously doubt that there are many applications out there that are
actually depending on this aspect of rule execution; if anything, there
are probably more that will see it as a bug.

			regards, tom lane

  23. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-08-04T19:26:54Z

    On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The other thing that was being argued was whether rules should be
> changed to act that way too, and if not whether EXPLAIN ANALYZE should
> be fixed to make sure it emulates rule execution better.  Personally
> I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE
> alone, though I'm not sure if that position can command a consensus.
> I seriously doubt that there are many applications out there that are
> actually depending on this aspect of rule execution; if anything, there
> are probably more that will see it as a bug.

Changing EXPLAIN ANALYZE seems a bit less likely to break things for
anyone depending on current behavior; but, on the other hand, it seems
there's a broad consensus that the best thing to do about rule
execution is deprecate it, so maybe it doesn't really matter.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  24. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-08-04T19:55:10Z

    On 8/4/2010 10:26 PM, Robert Haas wrote:
> On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane<tgl@sss.pgh.pa.us>  wrote:
>> The other thing that was being argued was whether rules should be
>> changed to act that way too, and if not whether EXPLAIN ANALYZE should
>> be fixed to make sure it emulates rule execution better.  Personally
>> I'd be in favor of changing rule execution and leaving EXPLAIN ANALYZE
>> alone, though I'm not sure if that position can command a consensus.
>> I seriously doubt that there are many applications out there that are
>> actually depending on this aspect of rule execution; if anything, there
>> are probably more that will see it as a bug.
>
> Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> anyone depending on current behavior; but, on the other hand, it seems
> there's a broad consensus that the best thing to do about rule
> execution is deprecate it, so maybe it doesn't really matter.

I'm having a hard time imagining that anyone would depend on a behaviour 
like this.


Regards,
Marko Tiikkaja

  25. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Tom Lane <tgl@sss.pgh.pa.us> — 2010-08-04T20:14:15Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I seriously doubt that there are many applications out there that are
>> actually depending on this aspect of rule execution; if anything, there
>> are probably more that will see it as a bug.

> Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> anyone depending on current behavior;

Well, the point I was trying to make is that there may well be fewer
people depending on the current behavior than there are people for whom
the current behavior is wrong, only they don't know it because they've
not seen a failure (or not seen one often enough to diagnose what's
happening).

This is of course merest speculation either way.  But I don't feel that
we need to necessarily treat rule behavior as graven in stone.

			regards, tom lane

  26. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-08-30T21:07:31Z

    Hi,

I looked at fixing this inconsistency by making all query list snapshot 
handling work like EXPLAIN ANALYZE's code does.  The only reason I went 
this way was that implementing wCTEs on top of this behaviour is a lot 
easier.

There were three places that needed fixing.  The SPI and portal logic 
changes were quite straightforward, but the SQL language function code 
previously didn't know what query trees of the execution_state list 
belonged to which query so there was no way to tell when we actually 
needed to take a new snapshot.  The approach I took was to change the 
representation of the SQL function cache to a list of execution_state 
lists, and grab a new snapshot between the lists.

The patch needs a bit more comments and some cleaning up, but I thought 
I'd get your input first.  Thoughts?


Regards,
Marko Tiikkaja

  27. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-08-30T21:30:24Z

    On 2010-08-31 12:07 AM +0300, I wrote:
> I looked at fixing this..

The previous patch had a bug in fmgr_sql() our regression tests didn't 
catch.  Fixed version attached.


Regards,
Marko Tiikkaja

  28. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2010-09-02T03:00:39Z

    On Mon, Aug 30, 2010 at 5:30 PM, Marko Tiikkaja
<marko.tiikkaja@cs.helsinki.fi> wrote:
> On 2010-08-31 12:07 AM +0300, I wrote:
>>
>> I looked at fixing this..
>
> The previous patch had a bug in fmgr_sql() our regression tests didn't
> catch.  Fixed version attached.

It would probably be a good idea to add this to the currently open CommitFest.

https://commitfest.postgresql.org/action/commitfest_view/open

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

  29. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Marko Tiikkaja <marko.tiikkaja@cs.helsinki.fi> — 2010-09-06T23:43:13Z

    On 2010-08-31 12:07 AM +0300, I wrote:
> The patch needs a bit more comments and some cleaning up  ..

Here's a cleaned up version with a bit more comments.

This patch still silently breaks pg_parse_and_rewrite().  We only use it 
in our source code for SQL-language functions, so I think we should 
deprecate it in favor of a function which would do the right thing for 
SQL functions.  Thoughts?


Regards,
Marko Tiikkaja

  30. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Bruce Momjian <bruce@momjian.us> — 2011-02-17T18:04:32Z

    Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> I seriously doubt that there are many applications out there that are
> >> actually depending on this aspect of rule execution; if anything, there
> >> are probably more that will see it as a bug.
> 
> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> > anyone depending on current behavior;
> 
> Well, the point I was trying to make is that there may well be fewer
> people depending on the current behavior than there are people for whom
> the current behavior is wrong, only they don't know it because they've
> not seen a failure (or not seen one often enough to diagnose what's
> happening).
> 
> This is of course merest speculation either way.  But I don't feel that
> we need to necessarily treat rule behavior as graven in stone.

Where are we on this?  It seems it is an issue independent of writable
common table expressions (wCTEs).

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  31. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Robert Haas <robertmhaas@gmail.com> — 2011-02-17T18:09:47Z

    On Thu, Feb 17, 2011 at 1:04 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Tom Lane wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> >> I seriously doubt that there are many applications out there that are
>> >> actually depending on this aspect of rule execution; if anything, there
>> >> are probably more that will see it as a bug.
>>
>> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
>> > anyone depending on current behavior;
>>
>> Well, the point I was trying to make is that there may well be fewer
>> people depending on the current behavior than there are people for whom
>> the current behavior is wrong, only they don't know it because they've
>> not seen a failure (or not seen one often enough to diagnose what's
>> happening).
>>
>> This is of course merest speculation either way.  But I don't feel that
>> we need to necessarily treat rule behavior as graven in stone.
>
> Where are we on this?  It seems it is an issue independent of writable
> common table expressions (wCTEs).

I believe that it's the same issue as this patch:

https://commitfest.postgresql.org/action/patch_view?id=377

The status of that patch is that Tom promised to look at it two months
ago and hasn't.  It would be nice if someone else could pick it up.
It's not good for our community to ignore patches that people have
taken the trouble to write and submit.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  32. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Bruce Momjian <bruce@momjian.us> — 2011-02-17T18:25:18Z

    Robert Haas wrote:
> On Thu, Feb 17, 2011 at 1:04 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Tom Lane wrote:
> >> Robert Haas <robertmhaas@gmail.com> writes:
> >> > On Wed, Aug 4, 2010 at 2:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> >> I seriously doubt that there are many applications out there that are
> >> >> actually depending on this aspect of rule execution; if anything, there
> >> >> are probably more that will see it as a bug.
> >>
> >> > Changing EXPLAIN ANALYZE seems a bit less likely to break things for
> >> > anyone depending on current behavior;
> >>
> >> Well, the point I was trying to make is that there may well be fewer
> >> people depending on the current behavior than there are people for whom
> >> the current behavior is wrong, only they don't know it because they've
> >> not seen a failure (or not seen one often enough to diagnose what's
> >> happening).
> >>
> >> This is of course merest speculation either way. ?But I don't feel that
> >> we need to necessarily treat rule behavior as graven in stone.
> >
> > Where are we on this? ?It seems it is an issue independent of writable
> > common table expressions (wCTEs).
> 
> I believe that it's the same issue as this patch:
> 
> https://commitfest.postgresql.org/action/patch_view?id=377
> 
> The status of that patch is that Tom promised to look at it two months
> ago and hasn't.  It would be nice if someone else could pick it up.
> It's not good for our community to ignore patches that people have
> taken the trouble to write and submit.

Oh, at least it is in the current commit-fest and was not lost.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  33. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-02-17T19:28:57Z

    Robert Haas <robertmhaas@gmail.com> writes:
> The status of that patch is that Tom promised to look at it two months
> ago and hasn't.  It would be nice if someone else could pick it up.

I'm pedaling as fast as I can, honestly.

			regards, tom lane

  34. Re: Rewrite, normal execution vs. EXPLAIN ANALYZE

    David Wheeler <david@kineticode.com> — 2011-02-17T20:17:15Z

    On Feb 17, 2011, at 11:28 AM, Tom Lane wrote:

>> The status of that patch is that Tom promised to look at it two months
>> ago and hasn't.  It would be nice if someone else could pick it up.
> 
> I'm pedaling as fast as I can, honestly

Tom leaves everything on the road.

  http://en.wiktionary.org/wiki/leave_everything_on_the_road

David