Prev: patch: Add JSON datatype to PostgreSQL (GSoC, WIP)
Next: [HACKERS] reminder... beta4 is coming
From: Marko Tiikkaja on 23 Jul 2010 15:06
On 7/23/2010 10:00 PM, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 2:13 PM, Marko Tiikkaja
> <marko.tiikkaja(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on 23 Jul 2010 18:24
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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on 23 Jul 2010 15:19
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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Alvaro Herrera on 23 Jul 2010 18:20
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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on 24 Jul 2010 05:44
On 7/24/2010 1:43 AM, Robert Haas wrote:
> On Fri, Jul 23, 2010 at 6:20 PM, Alvaro Herrera
> <alvherre(a)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

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: patch: Add JSON datatype to PostgreSQL (GSoC, WIP)
Next: [HACKERS] reminder... beta4 is coming