Re: 9.5: UPDATE/DELETE .. ORDER BY .. LIMIT ..

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 talk about 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