From: | Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Writeable CTEs, again |
Date: | 2009-10-22 22:08:21 |
Message-ID: | 4AE0D7D5.6090804@cs.helsinki.fi |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
Attached is a WIP patch which implements writeable CTEs. This patch has
some defects I'll be discussing below. Also, I haven't implemented the
grammar changes for using WITH ( .. RETURNING ) in non-SELECT queries
yet.
What's not obvious from the patch:
- estate->es_result_relation_info is currently only set during
EvalPlanQual(). ModifyTable nodes have an array of
ResultRelInfos they will be operating on. That array is part of
estate->es_result_relations.
- I removed resultRelations from PlannerInfo completely because I
didn't find use for it any more. That list is now stored first
in ModifyTable nodes, and then added to PlannerGlobal's
new resultRelations list during set_plan_refs().
Currently, we don't allow DO ALSO SELECT .. rules for SELECT queries.
But with this patch you could have a top-level SELECT which results in
multiple SELECTs when the DML operations inside CTEs are rewritten.
Consider this example:
=> CREATE RULE additional_select AS ON INSERT TO foo DO ALSO SELECT *
FROM bar;
=> WITH t AS (INSERT INTO foo VALUES(0) RETURNING *) SELECT * FROM t;
INSERT INTO foo VALUES(0) is ran first, but the results of that are
ignored. What you actually see is the output of SELECT * FROM bar which
is certainly surprising. What do you think should happen here?
INSERT/UPDATE/DELETE works as expected; both queries are ran but you get
the output of SELECT * FROM t;
Currently we also only allow cursors for simple SELECT queries. IMHO we
should also allow cursor for SELECT queries like the one above; the
INSERT is run to completion first, but then the user could use a cursor
to scan through the RETURNING tuples. I haven't looked into this very
thoroughly yet, but I don't see any obvious problems.
I'd appreciate any input.
Regards,
Marko Tiikkaja
Attachment | Content-Type | Size |
---|---|---|
with_returning.patch | text/x-patch | 42.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Yaroslav Tykhiy | 2009-10-22 22:35:57 | Re: Reversing flow of WAL shipping |
Previous Message | Dimitri Fontaine | 2009-10-22 20:34:53 | plpgsql EXECUTE will not set FOUND |