From: | Robert Haas <robertmhaas(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 hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Using results from INSERT ... RETURNING |
Date: | 2009-10-04 11:55:25 |
Message-ID: | 603c8f070910040455t57a29da9pa41acd0c73f05282@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Fri, Oct 2, 2009 at 7:37 AM, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> Robert Haas wrote:
>>
>> Now the point here is that I eventually want to be able to write
>> something like this:
>>
>> with foo as (insert into project (name) values ('Writeable CTEs')
>> returning id) select * from foo;
>>
>> ...but how does this get me any closer? It seems to me that the plan
>> for THAT statement has to be a CTE scan over top of BOTH of the
>> inserts, but here I have two insert nodes that comprise two separate
>> plans. The DML node, as presently implemented, supports a list of
>> plans, but they all have to be of the same type, so it's really only
>> useful for handling append, and as previously discussed, it's not
>> clear that the proposed handling is any better than what we already
>> have.
>
> I don't think you should be able to do this. I'm not too familiar with
> rules, but in your example the rule doesn't modify the output of the
> INSERT .. RETURNING so I think it shouldn't do that here either. How I
> see it is that in your example the INSERT INTO shadow would be added to
> the top level instead of the CTE and the plan would look something like
> this:
>
> ------------------------------------------------
> CTE Scan on foo (cost=0.01..0.03 rows=1 width=4)
> CTE foo
> -> Insert (cost=0.00..0.01 rows=1 width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
>
> Insert (cost=0.00..0.01 rows=1 width=0)
> -> Result (cost=0.00..0.01 rows=1 width=0)
>
> so you would get the RETURNING output from the CTE and the INSERT to the
> shadow table would be executed separately.
Yeah, I think you're right.
> I'm not saying that we don't
> want to provide the means to do this, but writeable CTEs alone aren't
> meant to handle this.
Well, I think a patch to implement writeable CTEs is probably going to
have to handle this case - I don't think we can just ignore rewrite
rules when processing a CTE. But it does seem to be beyond the scope
of the current patch.
I'm going to go ahead and mark this Ready for Committer. Thanks for
your patience.
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2009-10-04 12:14:23 | Re: Using results from INSERT ... RETURNING |
Previous Message | Simon Riggs | 2009-10-04 11:19:56 | Hot Standby 0.2.2 |