Skip site navigation (1) Skip section navigation (2)

Re: top-level DML under CTEs

From: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
To: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: top-level DML under CTEs
Date: 2010-09-23 06:12:51
Message-ID: AANLkTinmR6TxkBfXK7v72Zd9vxRF3k6FPdSGuA+xPSXN@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-rrreviewers
2010/9/23 Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>:
> On 2010-09-17 4:48 AM, Hitoshi Harada wrote:
>>
>> 2010/9/15 Hitoshi Harada<umi(dot)tanuki(at)gmail(dot)com>:
>>>
>>> Well, I didn't know it is allowed. That would look like the way to go.
>>
>> I made changes to the previous version, so that it avoids to resolve
>> CTE name duplication.
>
> This patch still doesn't address the issue Tom raised here:
> http://archives.postgresql.org/pgsql-hackers/2010-09/msg00753.php
>
> For WITH .. INSERT .. WITH .. SELECT ..; this patch works OK, but not so
> much for VALUES:
>
> =# CREATE RULE barrule AS ON UPDATE TO bar DO INSTEAD
> -# WITH RECURSIVE t AS (SELECT -1)
> -# INSERT INTO bar
> -# WITH t AS (SELECT 1)
> -# VALUES((SELECT * FROM t));
> CREATE RULE
>
> =# \d bar
>      Table "public.bar"
>  Column |  Type   | Modifiers
> --------+---------+-----------
>  a      | integer |
> Rules:
>    barrule AS
>    ON UPDATE TO bar DO INSTEAD  WITH RECURSIVE t AS (
>         SELECT 1
>        ), t AS (
>         SELECT (-1)
>        )
>  INSERT INTO bar (a)  WITH RECURSIVE t AS (
>                 SELECT 1
>                ), t AS (
>                 SELECT (-1)
>                )
>
>  VALUES (( SELECT t."?column?"
>           FROM t))

I ran the sql and recognized what is wrong. In VALUES case, the WITH
table "t" is copied in one list and shown up in the both of
INSERT-level WITH and SELECT-level WITH. Since the transformation of
WITH clause to cheat postgres is in the parser stage currently, I
wonder if this should be done in the rewriter or the planner stage.

Thanks for the report. Next time, please point the clear problem in
English aside the sample.

Regards,

-- 
Hitoshi Harada

In response to

Responses

pgsql-hackers by date

Next:From: Simon RiggsDate: 2010-09-23 06:20:19
Subject: Re: Configuring synchronous replication
Previous:From: Magnus HaganderDate: 2010-09-23 06:09:20
Subject: Re: Git cvsserver serious issue

pgsql-rrreviewers by date

Next:From: Marko TiikkajaDate: 2010-09-23 09:22:54
Subject: Re: top-level DML under CTEs
Previous:From: Marko TiikkajaDate: 2010-09-22 23:59:15
Subject: Re: top-level DML under CTEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group