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: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: top-level DML under CTEs
Date: 2010-09-14 18:59:43
Message-ID: AANLkTimf3pedemsteh6EcbAFZOMbbKaUuEQb5AyzNrkW@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-rrreviewers
2010/9/15 Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>:
> On 2010-09-13 4:15 PM +0300, Hitoshi Harada wrote:
>>
>> 1. WITH clause atop INSERT
>> Although the previous discussion got the consensus that we forbid WITH
>> atop INSERT, it seems to me that it can be allowed. I managed to do it
>> by treating the top WITH clause (of INSERT) as if the one of SELECT
>> (or VALUES).
>
> In the email you referred to, Tom was concerned about the case where these
> WITH lists have different RECURSIVE declarations.  This patch makes both
> RECURSIVE if either of them is.  I can think of cases where that might lead
> to surprising behaviour, but the chances of any of those happening in real
> life seem pretty slim.

I might not understand the RECURSIVE issue correctly. I put my effort
to make such query

WITH RECURSIVE r AS (SELECT 1 i UNION ALL SELECT i + 1 FROM r WHERE i
< 10) INSERT INTO WITH t AS (SELECT 0) VALUES((SELECT * FROM r LIMIT
1)),((SELECT * FROM t));

look like

INSERT INTO WITH RECURSIVE r AS (SELECT 1 i UNION ALL SELECT i + 1
FROM r WHERE i < 10), t AS (SELECT 0) VALUES((SELECT * FROM r LIMIT
1)),((SELECT * FROM t));

Does that cause surprising behavior?

> but the chances of any of those happening in real
> life seem pretty slim.

The OLD/NEW issue is also near impossible to be problem in the real
life, except for the misleading error message. But once users see the
non-understandable behavior, they make lines to claim as it's a "bug".
So we need to put effort to avoid it as possible, I believe.

Regards,


-- 
Hitoshi Harada

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-09-14 19:10:11
Subject: Pseudoconstant quals versus the join removal patch
Previous:From: Markus WannerDate: 2010-09-14 18:59:10
Subject: Re: bg worker: patch 1 of 6 - permanent process

pgsql-rrreviewers by date

Next:From: Tom LaneDate: 2010-09-14 19:51:56
Subject: Re: top-level DML under CTEs
Previous:From: Marko TiikkajaDate: 2010-09-14 15:50:31
Subject: Re: top-level DML under CTEs

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