Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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.


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group