Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT .. - Mailing list pgsql-hackers

From Marko Tiikkaja
Subject Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..
Date
Msg-id 53CC4D80.70101@joh.to
Whole thread Raw
In response to Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..  (Heikki Linnakangas <hlinnakangas@vmware.com>)
List pgsql-hackers
On 2014-06-24 11:08, Heikki Linnakangas wrote:
> IMHO this needs to work with inheritance if we are to accept it. It
> would be a rather strange limitation for no apparent reason, other than
> that we didn't bother to implement it. It doesn't seem very difficult in
> theory to add the table OID to the plan as a junk column, and use that
> in the ModifyTable node to know which table a row came from.

So I've been trying to look at this with Rukh, and it doesn't seem at 
all as easy as you make it out to be.  Consider the following example:
  =# create table foo(a int);  =# create table foo_c1(a int, b int) inherits (foo);  =# create table foo_c2(a int, c
text)inherits (foo);
 
  =# update foo set a = a+1;

Currently, the way that works is that the ModifyTable node knows of 
three plans, all with different target lists, targeting each of the 
tables separately.  To make $SUBJECT work with inheritance, we would 
somehow have to get all three different types of tuples through an 
Append node to the ModifyTable (with ctid and tableoid as junk columns).  I can see how the approach Tom and Andres
talkabout upthread could 
 
work, but that's a helluva lot of work just so we can check the 
inheritance check box for this feature, even though it's not clear 
anyone would actually want to use this on inheritance sets.  Other 
approach I can think of would be to create some kind of a Frankenstein 
tuple which would satisfy the needs of all tables in the set, but that 
sounds really awful.

Or we could just forget that we ever had inheritance and assume that 
partitioning is the only interesting use case.  But it's not clear to me 
that we could assume the output to be the same in every case even then.

If someone has a clear idea on how this could be implemented in a 
reasonable time, we'd be happy to hear about it.


.marko



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Draft release notes are up for 9.3.5
Next
From: Craig Ringer
Date:
Subject: Re: SQL MERGE is quite distinct from UPSERT