Re: top-level DML under CTEs

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