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

Re: top-level DML under CTEs

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
Cc: Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: top-level DML under CTEs
Date: 2010-09-14 23:02:25
Message-ID: 19924.1284505345@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-rrreviewers
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?

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-09-14 23:26:26
Subject: Re: Pseudoconstant quals versus the join removal patch
Previous:From: Robert HaasDate: 2010-09-14 22:49:29
Subject: Re: top-level DML under CTEs

pgsql-rrreviewers by date

Next:From: Hitoshi HaradaDate: 2010-09-15 01:04:06
Subject: Re: top-level DML under CTEs
Previous:From: Robert HaasDate: 2010-09-14 22:49:29
Subject: Re: top-level DML under CTEs

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