Horton

Re: Declarative partitioning - another take

Dmitry Ivanov <d.ivanov@postgrespro.ru>

View thread View on postgr.es
From: Dmitry Ivanov <d.ivanov@postgrespro.ru>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Dmitry Ivanov <d.ivanov@postgrespro.ru>, Michael Paquier <michael.paquier@gmail.com>, Andres Freund <andres@anarazel.de>, Erik Rijkers <er@xs4all.nl>, Amit Langote <amitlangote09@gmail.com>, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>, Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>, Pg Hackers <pgsql-hackers@postgresql.org>, <pgsql-hackers-owner@postgresql.org>
Date: 2016-12-15T15:40:11Z
Lists: pgsql-hackers

Attachments

Hi everyone,

Looks like "sql_inheritance" GUC is affecting partitioned tables:

explain (costs off) select * from test;
          QUERY PLAN          
------------------------------
 Append
   ->  Seq Scan on test
   ->  Seq Scan on test_1
   ->  Seq Scan on test_2
   ->  Seq Scan on test_1_1
   ->  Seq Scan on test_1_2
   ->  Seq Scan on test_1_1_1
   ->  Seq Scan on test_1_2_1
(8 rows)


set sql_inheritance = off;


explain (costs off) select * from test;
    QUERY PLAN    
------------------
 Seq Scan on test
(1 row)


I might be wrong, but IMO this should not happen. Queries involving update, 
delete etc on partitioned tables are basically broken. Moreover, there's no 
point in performing such operations on a parent table that's supposed to be 
empty at all times.

I've come up with a patch which fixes this behavior for UPDATE, DELETE, 
TRUNCATE and also in transformTableEntry(). It might be hacky, but it gives 
an idea.

I didn't touch RenameConstraint() and renameatt() since this would break 
ALTER TABLE ONLY command.


-- 
Dmitry Ivanov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Commits

  1. Fix typo.

  2. Document trigger-firing behavior for inheritance/partitioning.

  3. Fire per-statement triggers on partitioned tables.

  4. Set ecxt_scantuple correctly for tuple routing.

  5. Fix interaction of partitioned tables with BulkInsertState.

  6. Avoid core dump for empty prepared statement in an aborted transaction.

  7. Fix some problems in check_new_partition_bound().

  8. Remove unnecessary arguments from partitioning functions.

  9. Fix reporting of constraint violations for table partitioning.

  10. Fix tuple routing in cases where tuple descriptors don't match.

  11. Invalid parent's relcache after CREATE TABLE .. PARTITION OF.

  12. Doc: improve documentation about inheritance.