Writeable CTEs, again

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

Browse pgsql-hackers by date

  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