Progress on Writeable CTEs

From: Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi>
To: PostgreSQL development <pgsql-hackers(at)postgresql(dot)org>
Subject: Progress on Writeable CTEs
Date: 2009-09-21 19:09:32
Message-ID: 4AB7CF6C.4030306@cs.helsinki.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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 whether to open the
indices for the result relations or not based on the type of the
top-level statement, but in the future we could have CTE subqueries
operating on the result relations. Propagating result relation OIDs
from the CTE subqueries leads 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 list would 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
already be painfully slow.

Even if we didn't want to eliminate duplicate ResultRelationInfo
structures, we currently don't know what operations we 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 what we'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 ResultRelationInfo to
the DML node, but I was thinking of doing that even if we chose #2 or
#3. There are also some other parts 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 a look at the
queries of top-level CTEs and rewrite them if necessary.

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2009-09-21 19:14:27 Re: SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Previous Message Tom Lane 2009-09-21 19:03:24 Re: Adding \ev view editor?