On 2017/03/02 18:36, Robert Haas wrote:
> On Tue, Feb 28, 2017 at 11:35 AM, Amit Langote wrote:
>>> In acquire_inherited_sample_rows(), instead of inserting a whole
>>> stanza of logic just above the existing dispatch on relkind, I think
>>> we can get by with a very slightly update to what's already there.
>>>
>>> You can't use the result of a & b as a bool. You need to write (a &
>>> b) != 0, because the bool should always use 1 for true and 0 for
>>> false; it should not set some higher-numbered bit.
>>
>> Oops, thanks for fixing that. I suppose you are referring to this hunk in
>> the original patch:
>>
>> - relations = get_rel_oids(relid, relation);
>> + relations = get_rel_oids(relid, relation, options & VACOPT_VACUUM);
>>
>> And we need to do it this way in *this* case, because we're passing it as
>> a bool argument. I see that it's OK to do this:
>>
>> stmttype = (options & VACOPT_VACUUM) ? "VACUUM" : "ANALYZE";
>>
>> Or this:
>>
>> if (options & VACOPT_VACUUM)
>> {
>> PreventTransactionChain(isTopLevel, stmttype);
>
> In those cases it's still clearer, IMHO, to use != 0, but it's not
> necessary. However, when you're explicitly creating a value of type
> "bool", then it's necessary.
Agreed.
> Actually, looking at this again, I now think this part is wrong:
>
> + /*
> + * 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;
>
> I was thinking earlier that an ANALYZE on the parent would also update
> the statistics for each child, but now I see that's not so. So now I
Yep, the patch enables ANALYZE to be propagated to partitions when the
parent table is specified in the command. The above logic in the patch
made the database-wide ANALYZE to ignore partitions, in which case, only
the inheritance statistics would be updated. I can also see why that'd be
undesirable.
> think we should omit this logic (and change the documentation to
> match). That is, a database-wide ANALYZE should update the statistics
> for each child as well as for the parent. Otherwise direct queries
> against the children (and partitionwise joins, once we have that) are
> going to go haywire.
OK, done. I updated both analyze.sgml and vacuum.sgml to be more up to
date. Both pages previously omitted materialized views.
Attached updated patches.
Thanks,
Amit
>From 32447566c1c3bf5201e36141590f4654d144f777 Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 28 Feb 2017 13:43:59 +0900
Subject: [PATCH 1/3] Avoid useless partitioned table ops
Also, recursively perform VACUUM and ANALYZE on partitions when the
command is applied to a partitioned table.
---
doc/src/sgml/ddl.sgml | 47 ++++++++++++++++++------------------
doc/src/sgml/ref/analyze.sgml | 7 ++++--
doc/src/sgml/ref/vacuum.sgml | 4 +++-
src/backend/commands/analyze.c | 37 +++++++++++++++++++----------
src/backend/commands/tablecmds.c | 15 +++++++++---
src/backend/commands/vacuum.c | 51 +++++++++++++++++++++++++++++++++++++---
6 files changed, 116 insertions(+), 45 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index ef0f7cf727..09b5b3ff70 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3792,8 +3792,7 @@ UNION ALL SELECT * FROM measurement_y2008m01;
<title>Caveats</title>
<para>
- The following caveats apply to partitioned tables implemented using either
- method (unless noted otherwise):
+ The following caveats apply to using inheritance to implement partitioning:
<itemizedlist>
<listitem>
<para>
@@ -3803,13 +3802,6 @@ UNION ALL SELECT * FROM measurement_y2008m01;
partitions and creates and/or modifies associated objects than
to write each by hand.
</para>
-
- <para>
- This is not a problem with partitioned tables though, as trying to
- create a partition that overlaps with one of the existing partitions
- results in an error, so it is impossible to end up with partitions
- that overlap one another.
- </para>
</listitem>
<listitem>
@@ -3822,14 +3814,6 @@ UNION ALL SELECT * FROM measurement_y2008m01;
on the partition tables, but it makes management of the structure
much more complicated.
</para>
-
- <para>
- This problem exists even for partitioned tables. An <command>UPDATE</>
- that causes a row to move from one partition to another fails, because
- the new value of the row fails to satisfy the implicit partition
- constraint of the original partition. This might change in future
- releases.
- </para>
</listitem>
<listitem>
@@ -3840,8 +3824,7 @@ UNION ALL SELECT * FROM measurement_y2008m01;
<programlisting>
ANALYZE measurement;
</programlisting>
- will only process the master table. This is true even for partitioned
- tables.
+ will only process the master table.
</para>
</listitem>
@@ -3852,11 +3835,27 @@ ANALYZE measurement;
action is only taken in case of unique violations on the specified
target relation, not its child relations.
</para>
+ </listitem>
+ </itemizedlist>
+ </para>
+ <para>
+ The following caveats apply to partitioned tables created with the
+ explicit syntax:
+ <itemizedlist>
+ <listitem>
+ <para>
+ An <command>UPDATE</> that causes a row to move from one partition to
+ another fails, because the new value of the row fails to satisfy the
+ implicit partition constraint of the original partition. This might
+ change in future releases.
+ </para>
+ </listitem>
+
+ <listitem>
<para>
<command>INSERT</command> statements with <literal>ON CONFLICT</>
- clause are currently not allowed on partitioned tables, that is,
- cause error when specified.
+ clause are currently not allowed on partitioned tables.
</para>
</listitem>
@@ -3864,7 +3863,8 @@ ANALYZE measurement;
</para>
<para>
- The following caveats apply to constraint exclusion:
+ The following caveats apply to constraint exclusion, which is currently
+ used by both inheritance and partitioned tables:
<itemizedlist>
<listitem>
@@ -3898,8 +3898,7 @@ ANALYZE measurement;
during constraint exclusion, so large numbers of partitions are likely
to increase query planning time considerably. Partitioning using
these techniques will work well with up to perhaps a hundred partitions;
- don't try to use many thousands of partitions. This restriction applies
- both to inheritance and explicit partitioning syntax.
+ don't try to use many thousands of partitions.
</para>
</listitem>
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 27ab4fca42..620d2e43ff 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -63,8 +63,11 @@ ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table_name</replaceable> [
<listitem>
<para>
The name (possibly schema-qualified) of a specific table to
- analyze. If omitted, all regular tables (but not foreign tables)
- in the current database are analyzed.
+ analyze. If omitted, all regular tables, partitioned tables (but not
+ foreign tables), and materialized views in the current database are
+ analyzed. If the specified table is a partitioned table, both the
+ inheritance statistics of the partitioned table as a whole and
+ statistics of the individual partitions are updated.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index f18180a2fa..543ebcf649 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -153,7 +153,9 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ <replaceable class="PARAMETER">
<listitem>
<para>
The name (optionally schema-qualified) of a specific table to
- vacuum. Defaults to all tables in the current database.
+ vacuum. If omitted, all regular tables and materialized views in the
+ current database are vacuumed. If the specified table is a partitioned
+ table, all of its leaf partitions are vacuumed.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index ed3acb1673..a70c760341 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,10 +258,12 @@ analyze_rel(Oid relid, RangeVar *relation, int options,
LWLockRelease(ProcArrayLock);
/*
- * Do the normal non-recursive ANALYZE.
+ * Do the normal non-recursive ANALYZE. We can skip this for partitioned
+ * tables, which don't contain any rows.
*/
- 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.
@@ -1260,6 +1267,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
nrels,
i;
ListCell *lc;
+ bool has_child;
/*
* Find all members of inheritance set. We only need AccessShareLock on
@@ -1297,6 +1305,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double));
totalblocks = 0;
nrels = 0;
+ has_child = false;
foreach(lc, tableOIDs)
{
Oid childOID = lfirst_oid(lc);
@@ -1318,8 +1327,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
/* 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;
@@ -1351,13 +1359,17 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
}
else
{
- /* ignore, but release the lock on it */
- Assert(childrel != onerel);
- heap_close(childrel, AccessShareLock);
+ /*
+ * ignore, but release the lock on it. could be a partitioned
+ * table.
+ */
+ if (childrel != onerel)
+ heap_close(childrel, AccessShareLock);
continue;
}
/* OK, we'll process this child */
+ has_child = true;
rels[nrels] = childrel;
acquirefuncs[nrels] = acquirefunc;
relblocks[nrels] = (double) relpages;
@@ -1366,9 +1378,10 @@ 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 (!has_child)
{
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..3a9b965266 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"
@@ -394,6 +395,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 +410,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 +453,14 @@ 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;
/* Make a relation list entry for this guy */
@@ -1350,6 +1380,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
--
2.11.0
>From 8fe84fd968121dc17e749db8ba9d5516736dbf0b Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 6 Feb 2017 17:26:48 +0900
Subject: [PATCH 2/3] Avoid creating scan nodes for partitioned tables
* Currently, we create scan nodes for inheritance parents in their role
as an inheritance set member. Partitioned tables do not contain any
data, so it's useless to create scan nodes for them. So we need not
create AppendRelInfo's for them in the planner prep phase.
* The planner prep phase turns off inheritance on the parent RTE if
there isn't at least one child member (other than the parent itself
which in normal cases is also a child member), which means the latter
phases will not consider creating an Append plan and instead create a
scan node. Avoid this if the RTE is a partitioned table, by noticing
such cases in set_rel_size(). Per suggestion from Ashutosh Bapat.
* Since we do not add the RTE corresponding to the root partitioned
table as the 1st child member of the inheritance set,
inheritance_planner() must not assume the same when assigning
nominalRelation to a ModifyTable node.
* In ExecInitModifyTable(), use nominalRelation to initialize tuple
routing information, instead of the first resultRelInfo. We set
ModifyTable.nominalRelation to the root parent RTE in the
partitioned table case (implicitly in the INSERT case, whereas
explicitly in the UPDATE/DELETE cases).
---
src/backend/executor/nodeModifyTable.c | 18 +++--
src/backend/optimizer/path/allpaths.c | 8 ++
src/backend/optimizer/plan/planner.c | 14 +++-
src/backend/optimizer/prep/prepunion.c | 32 ++++++--
src/test/regress/expected/inherit.out | 124 ++++++++++--------------------
src/test/regress/expected/tablesample.out | 4 +-
src/test/regress/sql/inherit.sql | 8 ++
7 files changed, 108 insertions(+), 100 deletions(-)
diff --git a/src/backend/executor/nodeModifyTable.c b/src/backend/executor/nodeModifyTable.c
index 95e158970c..188ce8f8d4 100644
--- a/src/backend/executor/nodeModifyTable.c
+++ b/src/backend/executor/nodeModifyTable.c
@@ -45,6 +45,7 @@
#include "foreign/fdwapi.h"
#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parsetree.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "utils/builtins.h"
@@ -1634,7 +1635,8 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
Plan *subplan;
ListCell *l;
int i;
- Relation rel;
+ Relation nominalRel;
+ RangeTblEntry *nominalRTE;
/* check for unsupported flags */
Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -1726,9 +1728,10 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
estate->es_result_relation_info = saved_resultRelInfo;
/* Build state for INSERT tuple routing */
- rel = mtstate->resultRelInfo->ri_RelationDesc;
+ nominalRTE = rt_fetch(node->nominalRelation, estate->es_range_table);
+ nominalRel = heap_open(nominalRTE->relid, NoLock);
if (operation == CMD_INSERT &&
- rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ nominalRel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
{
PartitionDispatch *partition_dispatch_info;
ResultRelInfo *partitions;
@@ -1737,7 +1740,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
int num_parted,
num_partitions;
- ExecSetupPartitionTupleRouting(rel,
+ ExecSetupPartitionTupleRouting(nominalRel,
&partition_dispatch_info,
&partitions,
&partition_tupconv_maps,
@@ -1801,7 +1804,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
/* varno = node->nominalRelation */
mapped_wcoList = map_partition_varattnos(wcoList,
node->nominalRelation,
- partrel, rel);
+ partrel, nominalRel);
foreach(ll, mapped_wcoList)
{
WithCheckOption *wco = (WithCheckOption *) lfirst(ll);
@@ -1876,7 +1879,7 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
/* varno = node->nominalRelation */
rlist = map_partition_varattnos(returningList,
node->nominalRelation,
- partrel, rel);
+ partrel, nominalRel);
rliststate = (List *) ExecInitExpr((Expr *) rlist, &mtstate->ps);
resultRelInfo->ri_projectReturning =
ExecBuildProjectionInfo(rliststate, econtext, slot,
@@ -1897,6 +1900,9 @@ ExecInitModifyTable(ModifyTable *node, EState *estate, int eflags)
mtstate->ps.ps_ExprContext = NULL;
}
+ /* We're done using it. */
+ heap_close(nominalRel, NoLock);
+
/*
* If needed, Initialize target list, projection and qual for ON CONFLICT
* DO UPDATE.
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 633b5c1608..f2c8d27193 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -342,6 +342,14 @@ set_rel_size(PlannerInfo *root, RelOptInfo *rel,
/* Foreign table */
set_foreign_size(root, rel, rte);
}
+ else if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /*
+ * A partitioned table without leaf partitions is marked
+ * as a dummy rel.
+ */
+ set_dummy_rel_pathlist(rel);
+ }
else if (rte->tablesample != NULL)
{
/* Sampled relation */
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index ca0ae7883e..b5698f0ce7 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
@@ -1060,7 +1070,6 @@ inheritance_planner(PlannerInfo *root)
{
AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
PlannerInfo *subroot;
- RangeTblEntry *parent_rte;
RangeTblEntry *child_rte;
RelOptInfo *sub_final_rel;
Path *subpath;
@@ -1214,6 +1223,9 @@ 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 already set 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 1389db18ba..b4c65c4a90 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1364,6 +1364,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
List *inhOIDs;
List *appinfos;
ListCell *l;
+ bool has_child;
/* Does RT entry allow inheritance? */
if (!rte->inh)
@@ -1435,6 +1436,7 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/* Scan the inheritance set and expand it */
appinfos = NIL;
+ has_child = false;
foreach(l, inhOIDs)
{
Oid childOID = lfirst_oid(l);
@@ -1450,6 +1452,22 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrelation = oldrelation;
/*
+ * Since partitioned tables themselves do not contain data, later
+ * planning steps should not create scan nodes for them. So do not
+ * create the child RTE and AppendRelInfo.
+ */
+ if (newrelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ {
+ /*
+ * Don't lose the lock taken for us by find_all_inheritors()
+ * lest its partitions change under us.
+ */
+ if (newrelation != oldrelation)
+ heap_close(newrelation, NoLock);
+ 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
@@ -1461,6 +1479,9 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
continue;
}
+ /* We have a child table. */
+ has_child = true;
+
/*
* Build an RTE for the child, and attach to query's rangetable list.
* We copy most fields of the parent's RTE, but replace relation OID
@@ -1545,12 +1566,13 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
heap_close(oldrelation, NoLock);
/*
- * 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.
+ * If all the children were temp tables or if the parent is a partitioned
+ * table without any leaf partitions, pretend it's a non-inheritance
+ * situation. The duplicate RTE for the parent table we added in the
+ * non-partitioned table case is harmless, so we don't bother to get rid
+ * of it; ditto for the useless PlanRowMark node.
*/
- if (list_length(appinfos) < 2)
+ if (!has_child)
{
/* 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..55b147432f 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -1598,6 +1598,14 @@ reset enable_bitmapscan;
create table list_parted (
a varchar
) partition by list (a);
+-- test scanning partitioned table without any partitions
+explain (costs off) select * from list_parted;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
create table part_null_xy partition of list_parted for values in (null, 'xy');
@@ -1605,77 +1613,74 @@ 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,
b char(2)
) partition by range (a);
create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
+-- test scanning partitioned table without any leaf partitions
+explain (costs off) select * from range_list_parted;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
create table part_1_10_ab partition of part_1_10 for values in ('ab');
create table part_1_10_cd partition of part_1_10 for values in ('cd');
create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
@@ -1689,14 +1694,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 +1706,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 +1730,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 +1754,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 +1783,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
diff --git a/src/test/regress/expected/tablesample.out b/src/test/regress/expected/tablesample.out
index b18e420e9b..d3794140fb 100644
--- a/src/test/regress/expected/tablesample.out
+++ b/src/test/regress/expected/tablesample.out
@@ -322,12 +322,10 @@ explain (costs off)
QUERY PLAN
-------------------------------------------
Append
- -> Sample Scan on parted_sample
- Sampling: bernoulli ('100'::real)
-> Sample Scan on parted_sample_1
Sampling: bernoulli ('100'::real)
-> Sample Scan on parted_sample_2
Sampling: bernoulli ('100'::real)
-(7 rows)
+(5 rows)
drop table parted_sample, parted_sample_1, parted_sample_2;
diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql
index a8b7eb1c8d..04cc4ffa23 100644
--- a/src/test/regress/sql/inherit.sql
+++ b/src/test/regress/sql/inherit.sql
@@ -570,6 +570,10 @@ reset enable_bitmapscan;
create table list_parted (
a varchar
) partition by list (a);
+
+-- test scanning partitioned table without any partitions
+explain (costs off) select * from list_parted;
+
create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
create table part_null_xy partition of list_parted for values in (null, 'xy');
@@ -586,6 +590,10 @@ create table range_list_parted (
b char(2)
) partition by range (a);
create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
+
+-- test scanning partitioned table without any leaf partitions
+explain (costs off) select * from range_list_parted;
+
create table part_1_10_ab partition of part_1_10 for values in ('ab');
create table part_1_10_cd partition of part_1_10 for values in ('cd');
create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
--
2.11.0
>From 5417e0b9afebede5adaa82e7ea70d2906845c86a Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Tue, 24 Jan 2017 14:22:34 +0900
Subject: [PATCH 3/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 42b4ea410f..79a75152f0 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -932,7 +932,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:
@@ -942,6 +941,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:
@@ -1384,7 +1384,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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
