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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: top-level DML under CTEs
Date: 2010-09-15 01:15:12
Message-ID: AANLkTi=2yO5YVJkspuQWCXOPFJp=As9UJt6P=UbYAxc1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-rrreviewers
2010/9/15 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
>> On 2010-09-14 10:51 PM, Tom Lane wrote:
>>> My recollection is that whether a CTE is marked RECURSIVE or not affects
>>> its scope of visibility, so that confusing the two cases can result in
>>> flat-out incorrect parser behavior.
>
>> The worst I can think of is:
>
>> CREATE TABLE foo(a int);
>
>> WITH t AS (SELECT * FROM foo)
>> INSERT INTO bar
>> WITH RECURSIVE foo (SELECT 1 AS a)
>> SELECT * FROM t;
>
>> t will actually be populated with the results of the CTE, not the table foo.
>
>> I don't think this is a huge problem in real life, but if someone thinks
>> otherwise, I think we could just error out if the lists have a different
>> RECURSIVE definition.
>
> Wrong is wrong.  Doesn't matter whether it's "a huge problem in real life".
>
> Why is it so difficult to do this correctly?

Because INSERT INTO ... (SELECT|VALUES) is already a collection of
kludge (as comments say). It was possible to parse the two WITHs
separately, but it results in ambiguous naming issue;
parseWithClause() asserts there's only one WITH clause in the Stmt and
detects duplicated CTE name in it. It seems possible to call
parseWithClause() twice by cheating ParseState and to try to find name
duplication outside it, though it is another kludge :-(

Now that we find the worst situation, I start to think I have to take
the kludy way anyway.

Regards,



-- 
Hitoshi Harada

In response to

Responses

pgsql-hackers by date

Next:From: Fujii MasaoDate: 2010-09-15 01:33:20
Subject: Re: Latches, loop and exit
Previous:From: Hitoshi HaradaDate: 2010-09-15 01:04:06
Subject: Re: top-level DML under CTEs

pgsql-rrreviewers by date

Next:From: Tom LaneDate: 2010-09-15 03:22:44
Subject: Re: top-level DML under CTEs
Previous:From: Hitoshi HaradaDate: 2010-09-15 01:04:06
Subject: Re: top-level DML under CTEs

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