Hi,
I've looked at implementing writeable CTEs on top of my DML node patch
(repo here: git://git.postgresql.org/git/writeable_cte.git ) and
encountered a few conundrums. You can see what I've done in the
"actually_write" branch of that repo.
- Currently we only store the OIDs of the result relations we're going to be operating on. The executor then decides
whetherto open the indices for the result relations or not based on the type of the top-level statement, but in the
futurewe could have CTE subqueries operating on the result relations. Propagating result relation OIDs from the CTE
subqueriesleads to possibly having the same relations opened multiple times. Even if this isn't a problem, we don't
know whether to open the indices or not.
1) If we want to have only a single ResultRelationInfo per result relation, eliminating the duplicates from a
listwould be pretty slow if we have a huge number of result relations. On the other hand, a hash (or
similar)data structure would probably be an overkill. Updating a huge number of tables would probably
alreadybe painfully slow.
Even if we didn't want to eliminate duplicate ResultRelationInfo structures, we currently don't know what
operationswe want to perform on which result relation, so:
2) we could unconditionally open indices for every result relation, or: 3) we could emit some info about
whatwe're going to do along with the OID of the result relation.
#1 would force some changes to parts of the code. For example, we'd need to move the RETURNING projection info from
ResultRelationInfoto the DML node, but I was thinking of doing that even if we chose #2 or #3. There are also some
otherparts that would need to be touched, but that is the biggest issue I'm aware of.
- The DML subqueries should go through rewrite. I've looked at this, and we could teach the rewrite subsystem to take
alook at the queries of top-level CTEs and rewrite them if necessary.
