On 2017/02/19 18:53, Robert Haas wrote:
> On Fri, Feb 17, 2017 at 1:12 PM, Amit Langote wrote:
>> Do you mean that if database-wide analyze is to be run, we should also
>> exclude those RELKIND_RELATION relations that are partitions?
>>
>> So the only way to update a partition's statistics is to directly specify
>> it in the command or by autovacuum.
>
> I think if you type:
>
> ANALYZE;
>
> ...that should process all partitioned tables and all tables that are
> not themselves partitions.
OK.
> If you type:
>
> ANALYZE name;
>
> ...that should ANALYZE that relation, whatever it is. If it's a
> partitioned table, it should recurse.
To be clear, by "recurse" I assume you mean to perform ANALYZE on
individual partitions, not just collect the inheritance statistics. So
ANALYZE partitioned_table would both a) collect the inheritance statistics
for the specified table and other partitioned tables in the hierarchy, b)
ANALYZE every leaf partitions updating their statistics in pg_class.
While working on this, I noticed that autovacuum.c does not collect
RELKIND_PARTITIONED_TABLE relations, which I think is not right. It
should match what get_rel_oids() does, which in the database-wide
VACUUM/ANALYZE case collects them.
Attached updated patches:
Updated 0001 addresses the following comments:
- should recurse when vacuum/analyze is performed on a partitioned table
- database-wide vacuum should ignore partitioned tables
- database-wide analyze should ignore partitions; only the inheritance
statistics of the partitioned tables must be collected in this case
Thanks,
Amit
>From 982366eb019585438513b0c7b6e86acc74e459d1 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 6 Feb 2017 18:03:28 +0900
Subject: [PATCH 1/3] Partitioned tables are empty themselves
So, there is not much point in trying to do things to them that need
accessing files (a later commit will make it so that there is no file
at all to access.) Things that needed attention are: vacuum, analyze,
truncate, ATRewriteTables.
---
src/backend/commands/analyze.c | 39 ++++++++++++++------
src/backend/commands/tablecmds.c | 15 ++++++--
src/backend/commands/vacuum.c | 71 +++++++++++++++++++++++++++++++++----
src/backend/postmaster/autovacuum.c | 20 +++++++----
4 files changed, 118 insertions(+), 27 deletions(-)
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index ed3acb1673..12cd0110b0 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -201,8 +201,7 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
* locked the relation.
*/
if (onerel->rd_rel->relkind == RELKIND_RELATION ||
- onerel->rd_rel->relkind == RELKIND_MATVIEW ||
- onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ onerel->rd_rel->relkind == RELKIND_MATVIEW)
{
/* Regular table, so we'll use the regular row acquisition function */
acquirefunc = acquire_sample_rows;
@@ -234,6 +233,12 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
return;
}
}
+ else if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /*
+ * For partitioned tables, we want to do the recursive ANALYZE below.
+ */
+ }
else
{
/* No need for a WARNING if we already complained during VACUUM */
@@ -253,13 +258,15 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
LWLockRelease(ProcArrayLock);
/*
- * Do the normal non-recursive ANALYZE.
+ * Do the normal non-recursive ANALYZE, non-partitioned relations.
*/
- do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages,
- false, in_outer_xact, elevel);
+ if (onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
+ do_analyze_rel(onerel, options, params, va_cols, acquirefunc,
+ relpages, false, in_outer_xact, elevel);
/*
- * If there are child tables, do recursive ANALYZE.
+ * If there are child tables, do recursive ANALYZE. This includes
+ * partitioned tables.
*/
if (onerel->rd_rel->relhassubclass)
do_analyze_rel(onerel, options, params, va_cols, acquirefunc, relpages,
@@ -1316,10 +1323,20 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
continue;
}
+ /* Ignore partitioned tables as there are no tuples to collect */
+ if (childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /* Don't try to unlock the passed-in root table. */
+ if (childrel == onerel)
+ heap_close(childrel, NoLock);
+ else
+ heap_close(childrel, AccessShareLock);
+ continue;
+ }
+
/* Check table type (MATVIEW can't happen, but might as well allow) */
if (childrel->rd_rel->relkind == RELKIND_RELATION ||
- childrel->rd_rel->relkind == RELKIND_MATVIEW ||
- childrel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ childrel->rd_rel->relkind == RELKIND_MATVIEW)
{
/* Regular table, so use the regular row acquisition function */
acquirefunc = acquire_sample_rows;
@@ -1366,9 +1383,11 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
}
/*
- * If we don't have at least two tables to consider, fail.
+ * If we don't have at least one child table to consider, fail. If the
+ * relation is a partitioned table, it's not counted as a child table.
*/
- if (nrels < 2)
+ if ((onerel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && nrels < 2) ||
+ nrels < 1)
{
ereport(elevel,
(errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains no analyzable child tables",
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3cea220421..317012068b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1349,6 +1349,10 @@ ExecuteTruncate(TruncateStmt *stmt)
{
Relation rel = (Relation) lfirst(cell);
+ /* Skip partitioned tables as there is nothing to do */
+ if (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ continue;
+
/*
* Normally, we need a transaction-safe truncation here. However, if
* the table was either created in the current (sub)transaction or has
@@ -1459,7 +1463,11 @@ truncate_check_rel(Relation rel)
{
AclResult aclresult;
- /* Only allow truncate on regular tables */
+ /*
+ * Only allow truncate on regular tables and partitioned tables (although,
+ * the latter are only being included here for the following checks; no
+ * physical truncation will occur in their case.)
+ */
if (rel->rd_rel->relkind != RELKIND_RELATION &&
rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE)
ereport(ERROR,
@@ -4006,8 +4014,9 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode)
{
AlteredTableInfo *tab = (AlteredTableInfo *) lfirst(ltab);
- /* Foreign tables have no storage. */
- if (tab->relkind == RELKIND_FOREIGN_TABLE)
+ /* Foreign tables have no storage, nor do partitioned tables. */
+ if (tab->relkind == RELKIND_FOREIGN_TABLE ||
+ tab->relkind == RELKIND_PARTITIONED_TABLE)
continue;
/*
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 812fb4a48f..856673fb58 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -32,6 +32,7 @@
#include "access/xact.h"
#include "catalog/namespace.h"
#include "catalog/pg_database.h"
+#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_namespace.h"
#include "commands/cluster.h"
#include "commands/vacuum.h"
@@ -66,7 +67,7 @@ static BufferAccessStrategy vac_strategy;
/* non-export function prototypes */
-static List *get_rel_oids(Oid relid, const RangeVar *vacrel);
+static List *get_rel_oids(Oid relid, const RangeVar *vacrel, bool is_vacuum);
static void vac_truncate_clog(TransactionId frozenXID,
MultiXactId minMulti,
TransactionId lastSaneFrozenXid,
@@ -226,9 +227,12 @@ vacuum(int options, RangeVar *relation, Oid relid, VacuumParams *params,
/*
* Build list of relations to process, unless caller gave us one. (If we
- * build one, we put it in vac_context for safekeeping.)
+ * build one, we put it in vac_context for safekeeping.) Also pass
+ * whether we are going to be doing VACUUM, in which case we must include
+ * partitions in the list (considered only in the case of a database-wide
+ * invocation).
*/
- relations = get_rel_oids(relid, relation);
+ relations = get_rel_oids(relid, relation, options & VACOPT_VACUUM);
/*
* Decide whether we need to start/commit our own transactions.
@@ -378,7 +382,7 @@ vacuum(int options, RangeVar *relation, Oid relid, VacuumParams *params,
* per-relation transactions.
*/
static List *
-get_rel_oids(Oid relid, const RangeVar *vacrel)
+get_rel_oids(Oid relid, const RangeVar *vacrel, bool is_vacuum)
{
List *oid_list = NIL;
MemoryContext oldcontext;
@@ -394,6 +398,9 @@ get_rel_oids(Oid relid, const RangeVar *vacrel)
{
/* Process a specific relation */
Oid relid;
+ HeapTuple tuple;
+ Form_pg_class classForm;
+ bool include_parts;
/*
* Since we don't take a lock here, the relation might be gone, or the
@@ -406,9 +413,29 @@ get_rel_oids(Oid relid, const RangeVar *vacrel)
*/
relid = RangeVarGetRelid(vacrel, NoLock, false);
- /* Make a relation list entry for this guy */
+ /*
+ * To check whether the relation is a partitioned table, fetch its
+ * syscache entry.
+ */
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+ classForm = (Form_pg_class) GETSTRUCT(tuple);
+ include_parts = (classForm->relkind == RELKIND_PARTITIONED_TABLE);
+ ReleaseSysCache(tuple);
+
+ /*
+ * Make relation list entries for this guy and its partitions, if any.
+ * Note that the list returned by find_all_inheritors() include the
+ * passed-in OID at its head. Also note that we did not request a
+ * lock to be taken to match what would be done otherwise.
+ */
oldcontext = MemoryContextSwitchTo(vac_context);
- oid_list = lappend_oid(oid_list, relid);
+ if (include_parts)
+ oid_list = list_concat(oid_list,
+ find_all_inheritors(relid, NoLock, NULL));
+ else
+ oid_list = lappend_oid(oid_list, relid);
MemoryContextSwitchTo(oldcontext);
}
else
@@ -429,8 +456,23 @@ get_rel_oids(Oid relid, const RangeVar *vacrel)
{
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
+ /*
+ * We include partitioned tables here; depending on which
+ * operation is to be performed, caller will decide whether to
+ * process or ignore them.
+ */
if (classForm->relkind != RELKIND_RELATION &&
- classForm->relkind != RELKIND_MATVIEW)
+ classForm->relkind != RELKIND_MATVIEW &&
+ classForm->relkind != RELKIND_PARTITIONED_TABLE)
+ continue;
+
+ /*
+ * If only ANALYZE is to be performed, there is no need to include
+ * partitions in the list. In a database-wide ANALYZE, we only
+ * update the inheritance statistics of partitioned tables, not
+ * the statistics of individual partitions.
+ */
+ if (!is_vacuum && classForm->relispartition)
continue;
/* Make a relation list entry for this guy */
@@ -1350,6 +1392,21 @@ vacuum_rel(Oid relid, RangeVar *relation, int options, VacuumParams *params)
}
/*
+ * Ignore partitioned tables as there is no work to be done. Since we
+ * release the lock here, it's possible that any partitions added from
+ * this point on will not get processed, but that seems harmless.
+ */
+ if (onerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ relation_close(onerel, lmode);
+ PopActiveSnapshot();
+ CommitTransactionCommand();
+
+ /* It's OK for other commands to look at this table */
+ return true;
+ }
+
+ /*
* Get a session-level lock too. This will protect our access to the
* relation across multiple transactions, so that we can vacuum the
* relation's TOAST table (if any) secure in the knowledge that no one is
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0c5ffa086c..d7d1f5ea6f 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -1986,11 +1986,11 @@ do_autovacuum(void)
* Scan pg_class to determine which tables to vacuum.
*
* We do this in two passes: on the first one we collect the list of plain
- * relations and materialized views, and on the second one we collect
- * TOAST tables. The reason for doing the second pass is that during it we
- * want to use the main relation's pg_class.reloptions entry if the TOAST
- * table does not have any, and we cannot obtain it unless we know
- * beforehand what's the main table OID.
+ * relations, partitioned tables, and materialized views, and on the
+ * second one we collect TOAST tables. The reason for doing the second
+ * pass is that during it we want to use the main relation's
+ * pg_class.reloptions entry if the TOAST table does not have any, and we
+ * cannot obtain it unless we know beforehand what's the main table OID.
*
* We need to check TOAST tables separately because in cases with short,
* wide tables there might be proportionally much more activity in the
@@ -2013,7 +2013,8 @@ do_autovacuum(void)
bool wraparound;
if (classForm->relkind != RELKIND_RELATION &&
- classForm->relkind != RELKIND_MATVIEW)
+ classForm->relkind != RELKIND_MATVIEW &&
+ classForm->relkind != RELKIND_PARTITIONED_TABLE)
continue;
relid = HeapTupleGetOid(tuple);
@@ -2195,7 +2196,8 @@ do_autovacuum(void)
* completely unrelated to the one we saw before.
*/
if (!((classForm->relkind == RELKIND_RELATION ||
- classForm->relkind == RELKIND_MATVIEW) &&
+ classForm->relkind == RELKIND_MATVIEW ||
+ classForm->relkind == RELKIND_PARTITIONED_TABLE) &&
classForm->relpersistence == RELPERSISTENCE_TEMP))
{
UnlockRelationOid(relid, AccessExclusiveLock);
@@ -2614,6 +2616,10 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
if (classForm->relkind == RELKIND_TOASTVALUE)
doanalyze = false;
+ /* ignore VACUUM for partitioned tables */
+ if (classForm->relkind == RELKIND_PARTITIONED_TABLE)
+ dovacuum = false;
+
/* OK, it needs something done */
if (doanalyze || dovacuum)
{
--
2.11.0
>From 0ee8319aa374879bc8ad3ea5a5ef8887ad926e80 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 24 Jan 2017 14:22:34 +0900
Subject: [PATCH 2/3] Do not allocate storage for partitioned tables.
Currently, it is not possible to insert any data into a partitioned
table. So, they're empty at all times, which means it is wasteful to
allocate relfilenode and related storage objects.
---
src/backend/access/common/reloptions.c | 3 +--
src/backend/catalog/heap.c | 17 ++++++++++-------
2 files changed, 11 insertions(+), 9 deletions(-)
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 816483ef8b..ec90d2b625 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -930,7 +930,6 @@ extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
case RELKIND_RELATION:
case RELKIND_TOASTVALUE:
case RELKIND_MATVIEW:
- case RELKIND_PARTITIONED_TABLE:
options = heap_reloptions(classForm->relkind, datum, false);
break;
case RELKIND_VIEW:
@@ -940,6 +939,7 @@ extractRelOptions(HeapTuple tuple, TupleDesc tupdesc,
options = index_reloptions(amoptions, datum, false);
break;
case RELKIND_FOREIGN_TABLE:
+ case RELKIND_PARTITIONED_TABLE:
options = NULL;
break;
default:
@@ -1382,7 +1382,6 @@ heap_reloptions(char relkind, Datum reloptions, bool validate)
return (bytea *) rdopts;
case RELKIND_RELATION:
case RELKIND_MATVIEW:
- case RELKIND_PARTITIONED_TABLE:
return default_reloptions(reloptions, validate, RELOPT_KIND_HEAP);
default:
/* other relkinds are not supported */
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 41c0056556..2f5090b183 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -291,6 +291,7 @@ heap_create(const char *relname,
case RELKIND_VIEW:
case RELKIND_COMPOSITE_TYPE:
case RELKIND_FOREIGN_TABLE:
+ case RELKIND_PARTITIONED_TABLE:
create_storage = false;
/*
@@ -1345,14 +1346,13 @@ heap_create_with_catalog(const char *relname,
if (oncommit != ONCOMMIT_NOOP)
register_on_commit_action(relid, oncommit);
- if (relpersistence == RELPERSISTENCE_UNLOGGED)
- {
- Assert(relkind == RELKIND_RELATION || relkind == RELKIND_MATVIEW ||
- relkind == RELKIND_TOASTVALUE ||
- relkind == RELKIND_PARTITIONED_TABLE);
-
+ /*
+ * We do not want to create any storage objects for a partitioned
+ * table, including the init fork.
+ */
+ if (relpersistence == RELPERSISTENCE_UNLOGGED &&
+ relkind != RELKIND_PARTITIONED_TABLE)
heap_create_init_fork(new_rel_desc);
- }
/*
* ok, the relation has been cataloged, so close our relations and return
@@ -1376,6 +1376,9 @@ heap_create_with_catalog(const char *relname,
void
heap_create_init_fork(Relation rel)
{
+ Assert(rel->rd_rel->relkind == RELKIND_RELATION ||
+ rel->rd_rel->relkind == RELKIND_MATVIEW ||
+ rel->rd_rel->relkind == RELKIND_TOASTVALUE);
RelationOpenSmgr(rel);
smgrcreate(rel->rd_smgr, INIT_FORKNUM, false);
log_smgrcreate(&rel->rd_smgr->smgr_rnode.node, INIT_FORKNUM);
--
2.11.0
>From 965a6eb7e1f260a83dc713b2d23f4e6c6701d178 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 6 Feb 2017 17:26:48 +0900
Subject: [PATCH 3/3] Always plan partitioned tables as inheritance sets
...even if empty. Currently, we create scan plans for inheritance
parents in their role as an inheritance set member. Partitioned tables
do not contain any data, so it's not right to create scan plans for them.
So we need not create AppendRelInfo's for them in the planner prep phase.
That means the Append set for a partitioned table may contain zero members
if it does not have leaf partitions Currently, such a case is treated by
the current code as a non-inheritance situation and a scan plan is created
for just the parent. It is done by turning off the inh flag of the parent
RTE so that later planner phases create a simple scan plan for it. Since
we cannot create scan plans for partitioned tables, don't turn off its inh
flag even if we found that there are no Append members. Let this empty
Append member list cause later code create a constant-false Result plan.
Also, inheritance_planner() must not always assume that the 1st child
member of an inheritance set is the nominal relation. For partitioned
tables, we use the original parent RTE as the nominal relation in that
case, because we no longer create the duplicate RTE representing a
partitioned table as a child member of the inheritance set.
---
src/backend/optimizer/plan/planner.c | 15 +++++
src/backend/optimizer/prep/prepunion.c | 36 +++++++++--
src/test/regress/expected/inherit.out | 108 +++++++--------------------------
3 files changed, 68 insertions(+), 91 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3d33d46971..c18c92d0a8 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -996,10 +996,20 @@ inheritance_planner(PlannerInfo *root)
RelOptInfo *final_rel;
ListCell *lc;
Index rti;
+ RangeTblEntry *parent_rte;
Assert(parse->commandType != CMD_INSERT);
/*
+ * If the parent RTE is a partitioned table, we should use that as the
+ * nominal relation, because we do not have the duplicate parent RTE,
+ * unlike in the case of a non-partitioned inheritance parent.
+ */
+ parent_rte = rt_fetch(parentRTindex, root->parse->rtable);
+ if (parent_rte->relkind == RELKIND_PARTITIONED_TABLE)
+ nominalRelation = parentRTindex;
+
+ /*
* We generate a modified instance of the original Query for each target
* relation, plan that, and put all the plans into a list that will be
* controlled by a single ModifyTable node. All the instances share the
@@ -1214,6 +1224,11 @@ inheritance_planner(PlannerInfo *root)
* will not be otherwise referenced in the plan, doing so would give
* rise to confusing use of multiple aliases in EXPLAIN output for
* what the user will think is the "same" table.)
+ *
+ * If the parent is a partitioned table, we do not have a separate RTE
+ * representing it as a member of the inheritance set, because we do
+ * not create a scan plan for it. As mentioned at the top of this
+ * function, we make the parent RTE itself the nominal relation.
*/
if (nominalRelation < 0)
nominalRelation = appinfo->child_relid;
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index 06e843dff0..10f7494215 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1374,9 +1374,17 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
Assert(rte->rtekind == RTE_SUBQUERY);
return;
}
- /* Fast path for common case of childless table */
+ /*
+ * Fast path for common case of childless table. However, a partitioned
+ * table RTE should *always* be processed by the later code as an
+ * inheritance set, possibly an empty one in the absence of any leaf
+ * partitions. It is wrong for the later phases to try to create a scan
+ * plan for a partitioned table, which would be the case if we turn off
+ * the inh flag here.
+ */
parentOID = rte->relid;
- if (!has_subclass(parentOID))
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ !has_subclass(parentOID))
{
/* Clear flag before returning */
rte->inh = false;
@@ -1410,9 +1418,11 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* Check that there's at least one descendant, else treat as no-child
* case. This could happen despite above has_subclass() check, if table
- * once had a child but no longer does.
+ * once had a child but no longer does. Again, do not disable inheritance
+ * for a partitioned table as described above.
*/
- if (list_length(inhOIDs) < 2)
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ list_length(inhOIDs) < 2)
{
/* Clear flag before returning */
rte->inh = false;
@@ -1450,6 +1460,18 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrelation = oldrelation;
/*
+ * Ignore partitioned tables here, because we do not want them to
+ * be considered as child tables in the inheritance set. No scan
+ * plans will be created for them.
+ */
+ if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ if (newrelation != oldrelation)
+ heap_close(newrelation, lockmode);
+ continue;
+ }
+
+ /*
* It is possible that the parent table has children that are temp
* tables of other backends. We cannot safely access such tables
* (because of buffering issues), and the best thing to do seems to be
@@ -1548,9 +1570,11 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
* If all the children were temp tables, pretend it's a non-inheritance
* situation. The duplicate RTE we added for the parent table is
* harmless, so we don't bother to get rid of it; ditto for the useless
- * PlanRowMark node.
+ * PlanRowMark node. Do not disable inheritance for partitioned tables
+ * as described above.
*/
- if (list_length(appinfos) < 2)
+ if (rte->relkind != RELKIND_PARTITIONED_TABLE &&
+ list_length(appinfos) < 2)
{
/* Clear flag before returning */
rte->inh = false;
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index a8c8b28a75..ef4e049047 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1605,71 +1605,60 @@ explain (costs off) select * from list_parted;
QUERY PLAN
--------------------------------
Append
- -> Seq Scan on list_parted
-> Seq Scan on part_ab_cd
-> Seq Scan on part_ef_gh
-> Seq Scan on part_null_xy
-(5 rows)
+(4 rows)
explain (costs off) select * from list_parted where a is null;
QUERY PLAN
--------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (a IS NULL)
-> Seq Scan on part_null_xy
Filter: (a IS NULL)
-(5 rows)
+(3 rows)
explain (costs off) select * from list_parted where a is not null;
QUERY PLAN
---------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (a IS NOT NULL)
-> Seq Scan on part_ab_cd
Filter: (a IS NOT NULL)
-> Seq Scan on part_ef_gh
Filter: (a IS NOT NULL)
-> Seq Scan on part_null_xy
Filter: (a IS NOT NULL)
-(9 rows)
+(7 rows)
explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
QUERY PLAN
----------------------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> Seq Scan on part_ab_cd
Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-> Seq Scan on part_ef_gh
Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
-(7 rows)
+(5 rows)
explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
QUERY PLAN
---------------------------------------------------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> Seq Scan on part_ab_cd
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> Seq Scan on part_ef_gh
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-> Seq Scan on part_null_xy
Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
-(9 rows)
+(7 rows)
explain (costs off) select * from list_parted where a = 'ab';
QUERY PLAN
------------------------------------------
Append
- -> Seq Scan on list_parted
- Filter: ((a)::text = 'ab'::text)
-> Seq Scan on part_ab_cd
Filter: ((a)::text = 'ab'::text)
-(5 rows)
+(3 rows)
create table range_list_parted (
a int,
@@ -1689,14 +1678,9 @@ create table part_40_inf_ab partition of part_40_inf for values in ('ab');
create table part_40_inf_cd partition of part_40_inf for values in ('cd');
create table part_40_inf_null partition of part_40_inf for values in (null);
explain (costs off) select * from range_list_parted;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- -> Seq Scan on part_1_10
- -> Seq Scan on part_10_20
- -> Seq Scan on part_21_30
- -> Seq Scan on part_40_inf
-> Seq Scan on part_1_10_ab
-> Seq Scan on part_1_10_cd
-> Seq Scan on part_10_20_ab
@@ -1706,36 +1690,22 @@ explain (costs off) select * from range_list_parted;
-> Seq Scan on part_40_inf_ab
-> Seq Scan on part_40_inf_cd
-> Seq Scan on part_40_inf_null
-(15 rows)
+(10 rows)
explain (costs off) select * from range_list_parted where a = 5;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+--------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (a = 5)
- -> Seq Scan on part_1_10
- Filter: (a = 5)
-> Seq Scan on part_1_10_ab
Filter: (a = 5)
-> Seq Scan on part_1_10_cd
Filter: (a = 5)
-(9 rows)
+(5 rows)
explain (costs off) select * from range_list_parted where b = 'ab';
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_1_10
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_10_20
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_21_30
- Filter: (b = 'ab'::bpchar)
- -> Seq Scan on part_40_inf
- Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_1_10_ab
Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_10_20_ab
@@ -1744,27 +1714,19 @@ explain (costs off) select * from range_list_parted where b = 'ab';
Filter: (b = 'ab'::bpchar)
-> Seq Scan on part_40_inf_ab
Filter: (b = 'ab'::bpchar)
-(19 rows)
+(9 rows)
explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
QUERY PLAN
-----------------------------------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_1_10
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_10_20
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
- -> Seq Scan on part_21_30
- Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_1_10_ab
Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_10_20_ab
Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-> Seq Scan on part_21_30_ab
Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
-(15 rows)
+(7 rows)
/* Should select no rows because range partition key cannot be null */
explain (costs off) select * from range_list_parted where a is null;
@@ -1776,37 +1738,17 @@ explain (costs off) select * from range_list_parted where a is null;
/* Should only select rows from the null-accepting partition */
explain (costs off) select * from range_list_parted where b is null;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (b IS NULL)
- -> Seq Scan on part_1_10
- Filter: (b IS NULL)
- -> Seq Scan on part_10_20
- Filter: (b IS NULL)
- -> Seq Scan on part_21_30
- Filter: (b IS NULL)
- -> Seq Scan on part_40_inf
- Filter: (b IS NULL)
-> Seq Scan on part_40_inf_null
Filter: (b IS NULL)
-(13 rows)
+(3 rows)
explain (costs off) select * from range_list_parted where a is not null and a < 67;
QUERY PLAN
------------------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_1_10
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_10_20
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_21_30
- Filter: ((a IS NOT NULL) AND (a < 67))
- -> Seq Scan on part_40_inf
- Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_ab
Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_1_10_cd
@@ -1825,23 +1767,19 @@ explain (costs off) select * from range_list_parted where a is not null and a <
Filter: ((a IS NOT NULL) AND (a < 67))
-> Seq Scan on part_40_inf_null
Filter: ((a IS NOT NULL) AND (a < 67))
-(29 rows)
+(19 rows)
explain (costs off) select * from range_list_parted where a >= 30;
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+------------------------------------
Append
- -> Seq Scan on range_list_parted
- Filter: (a >= 30)
- -> Seq Scan on part_40_inf
- Filter: (a >= 30)
-> Seq Scan on part_40_inf_ab
Filter: (a >= 30)
-> Seq Scan on part_40_inf_cd
Filter: (a >= 30)
-> Seq Scan on part_40_inf_null
Filter: (a >= 30)
-(11 rows)
+(7 rows)
drop table list_parted cascade;
NOTICE: drop cascades to 3 other objects
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
