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

On Thu, Mar 13, 2014 at 3:49 AM, Rukh Meski <rukh(dot)meski(at)yahoo(dot)ca> wrote:
> Oops. Of course shouldn't try and change how INSERT works. Latest version attached.

I had given a brief look into this patch and found that the
implementation for Update .. ORDER BY is not appropriate for
inheritance tables.

It just tries to sort for individual tables in inheritance hierarchy
which can give wrong behaviour.

As an example try below case:
CREATE TABLE cities (
name text,
population real,
altitude int
);

CREATE TABLE capitals (
state char(2)
) INHERITS (cities);

insert rows in both tables and then try to see the plan of below
Update statement
postgres=# explain update cities set population=150 where altitude<25 order by n
ame limit 1;
QUERY PLAN
------------------------------------------------------------------------
Update on cities (cost=2.65..28.80 rows=396 width=47)
-> Sort (cost=2.65..2.74 rows=39 width=42)
Sort Key: cities.name
-> Seq Scan on cities (cost=0.00..2.45 rows=39 width=42)
Filter: (altitude < 25)
-> Sort (cost=25.16..26.05 rows=357 width=48)
Sort Key: capitals.name
-> Seq Scan on capitals (cost=0.00..23.38 rows=357 width=48)
Filter: (altitude < 25)
Planning time: 0.292 ms
(10 rows)

Here as it sorts for individual tables, the final result could be wrong.

As far as I can trace from the previous discussion of this feature,
you need to find the solution for below 2 key problems for
UPDATE ... ORDER BY:

1. How will you sort the rows from different tables in inheritance
hierarchy especially when they contain different columns as in
above example.

2. How would ModifyTable knows which table row came from.

Tom Lane has explained these problems in a very clear manner
in his below mail and shared his opinion about this feature as
well.
http://www.postgresql.org/message-id/26819.1291133045@sss.pgh.pa.us

So I think if you want to implement this feature, then lets first
try to find a solution for above problems.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com