Rewrite, normal execution vs. EXPLAIN ANALYZE
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
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
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
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
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
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
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
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
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
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
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
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.
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
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
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
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.
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
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
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
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
