On Thu, Mar 13, 2014 at 3:49 AM, Rukh Meski <rukh.me...@yahoo.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.1291133...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers