aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2015-03-22 13:53:11 -0400
committerTom Lane <tgl@sss.pgh.pa.us>2015-03-22 13:53:21 -0400
commitcb1ca4d800621dcae67ca6c799006de99fa4f0a5 (patch)
tree6f69242bd93e64f1529e846433c7d92dc1a2a59d /src
parent8ac356cde312693aa79f6b2fe7c46b8ed6108787 (diff)
downloadpostgresql-cb1ca4d800621dcae67ca6c799006de99fa4f0a5.tar.gz
postgresql-cb1ca4d800621dcae67ca6c799006de99fa4f0a5.zip
Allow foreign tables to participate in inheritance.
Foreign tables can now be inheritance children, or parents. Much of the system was already ready for this, but we had to fix a few things of course, mostly in the area of planner and executor handling of row locks. As side effects of this, allow foreign tables to have NOT VALID CHECK constraints (and hence to accept ALTER ... VALIDATE CONSTRAINT), and to accept ALTER SET STORAGE and ALTER SET WITH/WITHOUT OIDS. Continuing to disallow these things would've required bizarre and inconsistent special cases in inheritance behavior. Since foreign tables don't enforce CHECK constraints anyway, a NOT VALID one is a complete no-op, but that doesn't mean we shouldn't allow it. And it's possible that some FDWs might have use for SET STORAGE or SET WITH OIDS, though doubtless they will be no-ops for most. An additional change in support of this is that when a ModifyTable node has multiple target tables, they will all now be explicitly identified in EXPLAIN output, for example: Update on pt1 (cost=0.00..321.05 rows=3541 width=46) Update on pt1 Foreign Update on ft1 Foreign Update on ft2 Update on child3 -> Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46) -> Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46) -> Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46) -> Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46) This was done mainly to provide an unambiguous place to attach "Remote SQL" fields, but it is useful for inherited updates even when no foreign tables are involved. Shigeru Hanada and Etsuro Fujita, reviewed by Ashutosh Bapat and Kyotaro Horiguchi, some additional hacking by me
Diffstat (limited to 'src')
-rw-r--r--src/backend/catalog/heap.c7
-rw-r--r--src/backend/commands/analyze.c85
-rw-r--r--src/backend/commands/explain.c114
-rw-r--r--src/backend/commands/tablecmds.c36
-rw-r--r--src/backend/executor/execCurrent.c2
-rw-r--r--src/backend/executor/execMain.c79
-rw-r--r--src/backend/executor/nodeLockRows.c8
-rw-r--r--src/backend/optimizer/plan/planner.c80
-rw-r--r--src/backend/optimizer/prep/prepunion.c10
-rw-r--r--src/backend/parser/gram.y30
-rw-r--r--src/bin/pg_dump/pg_dump.c16
-rw-r--r--src/include/nodes/execnodes.h11
-rw-r--r--src/include/optimizer/planner.h3
-rw-r--r--src/test/regress/expected/foreign_data.out505
-rw-r--r--src/test/regress/expected/rowsecurity.out10
-rw-r--r--src/test/regress/expected/updatable_views.out12
-rw-r--r--src/test/regress/expected/with.out6
-rw-r--r--src/test/regress/sql/foreign_data.sql137
18 files changed, 985 insertions, 166 deletions
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 5ce43957d9f..e0dbcea0939 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -2253,13 +2253,6 @@ AddRelationNewConstraints(Relation rel,
expr = stringToNode(cdef->cooked_expr);
}
- /* Don't allow NOT VALID for foreign tables */
- if (cdef->skip_validation &&
- rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("CHECK constraints on foreign tables cannot be marked NOT VALID")));
-
/*
* Check name uniqueness, or generate a name if none was given.
*/
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 75b45f7cd5d..366c4af27fa 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -297,9 +297,8 @@ analyze_rel(Oid relid, RangeVar *relation, int options, List *va_cols,
* do_analyze_rel() -- analyze one relation, recursively or not
*
* Note that "acquirefunc" is only relevant for the non-inherited case.
- * If we supported foreign tables in inheritance trees,
- * acquire_inherited_sample_rows would need to determine the appropriate
- * acquirefunc for each child table.
+ * For the inherited case, acquire_inherited_sample_rows() determines the
+ * appropriate acquirefunc for each child table.
*/
static void
do_analyze_rel(Relation onerel, int options, List *va_cols,
@@ -1448,7 +1447,8 @@ compare_rows(const void *a, const void *b)
*
* This has the same API as acquire_sample_rows, except that rows are
* collected from all inheritance children as well as the specified table.
- * We fail and return zero if there are no inheritance children.
+ * We fail and return zero if there are no inheritance children, or if all
+ * children are foreign tables that don't support ANALYZE.
*/
static int
acquire_inherited_sample_rows(Relation onerel, int elevel,
@@ -1457,6 +1457,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
{
List *tableOIDs;
Relation *rels;
+ AcquireSampleRowsFunc *acquirefuncs;
double *relblocks;
double totalblocks;
int numrows,
@@ -1491,10 +1492,12 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
}
/*
- * Count the blocks in all the relations. The result could overflow
- * BlockNumber, so we use double arithmetic.
+ * Identify acquirefuncs to use, and count blocks in all the relations.
+ * The result could overflow BlockNumber, so we use double arithmetic.
*/
rels = (Relation *) palloc(list_length(tableOIDs) * sizeof(Relation));
+ acquirefuncs = (AcquireSampleRowsFunc *)
+ palloc(list_length(tableOIDs) * sizeof(AcquireSampleRowsFunc));
relblocks = (double *) palloc(list_length(tableOIDs) * sizeof(double));
totalblocks = 0;
nrels = 0;
@@ -1502,6 +1505,8 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
{
Oid childOID = lfirst_oid(lc);
Relation childrel;
+ AcquireSampleRowsFunc acquirefunc = NULL;
+ BlockNumber relpages = 0;
/* We already got the needed lock */
childrel = heap_open(childOID, NoLock);
@@ -1515,13 +1520,67 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
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)
+ {
+ /* Regular table, so use the regular row acquisition function */
+ acquirefunc = acquire_sample_rows;
+ relpages = RelationGetNumberOfBlocks(childrel);
+ }
+ else if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /*
+ * For a foreign table, call the FDW's hook function to see
+ * whether it supports analysis.
+ */
+ FdwRoutine *fdwroutine;
+ bool ok = false;
+
+ fdwroutine = GetFdwRoutineForRelation(childrel, false);
+
+ if (fdwroutine->AnalyzeForeignTable != NULL)
+ ok = fdwroutine->AnalyzeForeignTable(childrel,
+ &acquirefunc,
+ &relpages);
+
+ if (!ok)
+ {
+ /* ignore, but release the lock on it */
+ Assert(childrel != onerel);
+ heap_close(childrel, AccessShareLock);
+ continue;
+ }
+ }
+ else
+ {
+ /* ignore, but release the lock on it */
+ Assert(childrel != onerel);
+ heap_close(childrel, AccessShareLock);
+ continue;
+ }
+
+ /* OK, we'll process this child */
rels[nrels] = childrel;
- relblocks[nrels] = (double) RelationGetNumberOfBlocks(childrel);
- totalblocks += relblocks[nrels];
+ acquirefuncs[nrels] = acquirefunc;
+ relblocks[nrels] = (double) relpages;
+ totalblocks += (double) relpages;
nrels++;
}
/*
+ * If we don't have at least two tables to consider, fail.
+ */
+ if (nrels < 2)
+ {
+ ereport(elevel,
+ (errmsg("skipping analyze of \"%s.%s\" inheritance tree --- this inheritance tree contains no analyzable child tables",
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel))));
+ return 0;
+ }
+
+ /*
* Now sample rows from each relation, proportionally to its fraction of
* the total block count. (This might be less than desirable if the child
* rels have radically different free-space percentages, but it's not
@@ -1533,6 +1592,7 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
for (i = 0; i < nrels; i++)
{
Relation childrel = rels[i];
+ AcquireSampleRowsFunc acquirefunc = acquirefuncs[i];
double childblocks = relblocks[i];
if (childblocks > 0)
@@ -1549,12 +1609,9 @@ acquire_inherited_sample_rows(Relation onerel, int elevel,
tdrows;
/* Fetch a random sample of the child's rows */
- childrows = acquire_sample_rows(childrel,
- elevel,
- rows + numrows,
- childtargrows,
- &trows,
- &tdrows);
+ childrows = (*acquirefunc) (childrel, elevel,
+ rows + numrows, childtargrows,
+ &trows, &tdrows);
/* We may need to convert from child's rowtype to parent's */
if (childrows > 0 &&
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index a951c55ed34..315a52849c9 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2187,6 +2187,10 @@ ExplainScanTarget(Scan *plan, ExplainState *es)
/*
* Show the target of a ModifyTable node
+ *
+ * Here we show the nominal target (ie, the relation that was named in the
+ * original query). If the actual target(s) is/are different, we'll show them
+ * in show_modifytable_info().
*/
static void
ExplainModifyTarget(ModifyTable *plan, ExplainState *es)
@@ -2303,30 +2307,106 @@ ExplainTargetRel(Plan *plan, Index rti, ExplainState *es)
/*
* Show extra information for a ModifyTable node
+ *
+ * We have two objectives here. First, if there's more than one target table
+ * or it's different from the nominal target, identify the actual target(s).
+ * Second, give FDWs a chance to display extra info about foreign targets.
*/
static void
show_modifytable_info(ModifyTableState *mtstate, ExplainState *es)
{
- FdwRoutine *fdwroutine = mtstate->resultRelInfo->ri_FdwRoutine;
+ ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
+ const char *operation;
+ const char *foperation;
+ bool labeltargets;
+ int j;
- /*
- * If the first target relation is a foreign table, call its FDW to
- * display whatever additional fields it wants to. For now, we ignore the
- * possibility of other targets being foreign tables, although the API for
- * ExplainForeignModify is designed to allow them to be processed.
- */
- if (fdwroutine != NULL &&
- fdwroutine->ExplainForeignModify != NULL)
+ switch (node->operation)
{
- ModifyTable *node = (ModifyTable *) mtstate->ps.plan;
- List *fdw_private = (List *) linitial(node->fdwPrivLists);
-
- fdwroutine->ExplainForeignModify(mtstate,
- mtstate->resultRelInfo,
- fdw_private,
- 0,
- es);
+ case CMD_INSERT:
+ operation = "Insert";
+ foperation = "Foreign Insert";
+ break;
+ case CMD_UPDATE:
+ operation = "Update";
+ foperation = "Foreign Update";
+ break;
+ case CMD_DELETE:
+ operation = "Delete";
+ foperation = "Foreign Delete";
+ break;
+ default:
+ operation = "???";
+ foperation = "Foreign ???";
+ break;
+ }
+
+ /* Should we explicitly label target relations? */
+ labeltargets = (mtstate->mt_nplans > 1 ||
+ (mtstate->mt_nplans == 1 &&
+ mtstate->resultRelInfo->ri_RangeTableIndex != node->nominalRelation));
+
+ if (labeltargets)
+ ExplainOpenGroup("Target Tables", "Target Tables", false, es);
+
+ for (j = 0; j < mtstate->mt_nplans; j++)
+ {
+ ResultRelInfo *resultRelInfo = mtstate->resultRelInfo + j;
+ FdwRoutine *fdwroutine = resultRelInfo->ri_FdwRoutine;
+
+ if (labeltargets)
+ {
+ /* Open a group for this target */
+ ExplainOpenGroup("Target Table", NULL, true, es);
+
+ /*
+ * In text mode, decorate each target with operation type, so that
+ * ExplainTargetRel's output of " on foo" will read nicely.
+ */
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfoSpaces(es->str, es->indent * 2);
+ appendStringInfoString(es->str,
+ fdwroutine ? foperation : operation);
+ }
+
+ /* Identify target */
+ ExplainTargetRel((Plan *) node,
+ resultRelInfo->ri_RangeTableIndex,
+ es);
+
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ appendStringInfoChar(es->str, '\n');
+ es->indent++;
+ }
+ }
+
+ /* Give FDW a chance */
+ if (fdwroutine && fdwroutine->ExplainForeignModify != NULL)
+ {
+ List *fdw_private = (List *) list_nth(node->fdwPrivLists, j);
+
+ fdwroutine->ExplainForeignModify(mtstate,
+ resultRelInfo,
+ fdw_private,
+ j,
+ es);
+ }
+
+ if (labeltargets)
+ {
+ /* Undo the indentation we added in text format */
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ es->indent--;
+
+ /* Close the group */
+ ExplainCloseGroup("Target Table", NULL, true, es);
+ }
}
+
+ if (labeltargets)
+ ExplainCloseGroup("Target Tables", "Target Tables", false, es);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 41474575055..a9f79431e5c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -1504,10 +1504,11 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
*/
relation = heap_openrv(parent, ShareUpdateExclusiveLock);
- if (relation->rd_rel->relkind != RELKIND_RELATION)
+ if (relation->rd_rel->relkind != RELKIND_RELATION &&
+ relation->rd_rel->relkind != RELKIND_FOREIGN_TABLE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
- errmsg("inherited relation \"%s\" is not a table",
+ errmsg("inherited relation \"%s\" is not a table or foreign table",
parent->relname)));
/* Permanent rels cannot inherit from temporary ones */
if (relpersistence != RELPERSISTENCE_TEMP &&
@@ -3157,7 +3158,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
- ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
/* No command-specific prep needed */
pass = AT_PASS_MISC;
@@ -3245,14 +3246,14 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_AddOids: /* SET WITH OIDS */
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
if (!rel->rd_rel->relhasoids || recursing)
ATPrepAddOids(wqueue, rel, recurse, cmd, lockmode);
/* Recursion occurs during execution phase */
pass = AT_PASS_ADD_COL;
break;
case AT_DropOids: /* SET WITHOUT OIDS */
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* Performs own recursion */
if (rel->rd_rel->relhasoids)
{
@@ -3280,17 +3281,23 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_MISC;
break;
case AT_AddInherit: /* INHERIT */
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* This command never recurses */
ATPrepAddInherit(rel);
pass = AT_PASS_MISC;
break;
+ case AT_DropInherit: /* NO INHERIT */
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
+ /* This command never recurses */
+ /* No command-specific prep needed */
+ pass = AT_PASS_MISC;
+ break;
case AT_AlterConstraint: /* ALTER CONSTRAINT */
ATSimplePermissions(rel, ATT_TABLE);
pass = AT_PASS_MISC;
break;
case AT_ValidateConstraint: /* VALIDATE CONSTRAINT */
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* Recursion occurs during execution phase */
/* No command-specific prep needed except saving recurse flag */
if (recurse)
@@ -3318,7 +3325,6 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_EnableAlwaysRule:
case AT_EnableReplicaRule:
case AT_DisableRule:
- case AT_DropInherit: /* NO INHERIT */
case AT_AddOf: /* OF */
case AT_DropOf: /* NOT OF */
case AT_EnableRowSecurity:
@@ -4637,7 +4643,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
@@ -5533,7 +5539,7 @@ ATExecDropColumn(List **wqueue, Relation rel, const char *colName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* get the number of the attribute
@@ -5926,7 +5932,7 @@ ATAddCheckConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/*
* Call AddRelationNewConstraints to do the work, making sure it works on
@@ -7084,6 +7090,10 @@ validateCheckConstraint(Relation rel, HeapTuple constrtup)
bool isnull;
Snapshot snapshot;
+ /* VALIDATE CONSTRAINT is a no-op for foreign tables */
+ if (rel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ return;
+
constrForm = (Form_pg_constraint) GETSTRUCT(constrtup);
estate = CreateExecutorState();
@@ -7426,7 +7436,7 @@ ATExecDropConstraint(Relation rel, const char *constrName,
/* At top level, permission check was done in ATPrepCmd, else do it */
if (recursing)
- ATSimplePermissions(rel, ATT_TABLE);
+ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
@@ -9681,7 +9691,7 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode)
* Must be owner of both parent and child -- child was checked by
* ATSimplePermissions call in ATPrepCmd
*/
- ATSimplePermissions(parent_rel, ATT_TABLE);
+ ATSimplePermissions(parent_rel, ATT_TABLE | ATT_FOREIGN_TABLE);
/* Permanent rels cannot inherit from temporary ones */
if (parent_rel->rd_rel->relpersistence == RELPERSISTENCE_TEMP &&
diff --git a/src/backend/executor/execCurrent.c b/src/backend/executor/execCurrent.c
index 1c8be25da7d..d87be963a95 100644
--- a/src/backend/executor/execCurrent.c
+++ b/src/backend/executor/execCurrent.c
@@ -106,7 +106,7 @@ execCurrentOf(CurrentOfExpr *cexpr,
if (!RowMarkRequiresRowShareLock(thiserm->markType))
continue; /* ignore non-FOR UPDATE/SHARE items */
- if (RelationGetRelid(thiserm->relation) == table_oid)
+ if (thiserm->relid == table_oid)
{
if (erm)
ereport(ERROR,
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 13ceffae5c4..ad7e2072908 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -814,21 +814,22 @@ InitPlan(QueryDesc *queryDesc, int eflags)
if (rc->isParent)
continue;
+ /* get relation's OID (will produce InvalidOid if subquery) */
+ relid = getrelid(rc->rti, rangeTable);
+
switch (rc->markType)
{
case ROW_MARK_EXCLUSIVE:
case ROW_MARK_NOKEYEXCLUSIVE:
case ROW_MARK_SHARE:
case ROW_MARK_KEYSHARE:
- relid = getrelid(rc->rti, rangeTable);
relation = heap_open(relid, RowShareLock);
break;
case ROW_MARK_REFERENCE:
- relid = getrelid(rc->rti, rangeTable);
relation = heap_open(relid, AccessShareLock);
break;
case ROW_MARK_COPY:
- /* there's no real table here ... */
+ /* no physical table access is required */
relation = NULL;
break;
default:
@@ -843,6 +844,7 @@ InitPlan(QueryDesc *queryDesc, int eflags)
erm = (ExecRowMark *) palloc(sizeof(ExecRowMark));
erm->relation = relation;
+ erm->relid = relid;
erm->rti = rc->rti;
erm->prti = rc->prti;
erm->rowmarkId = rc->rowmarkId;
@@ -1911,21 +1913,9 @@ ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist)
aerm->rowmark = erm;
/* Look up the resjunk columns associated with this rowmark */
- if (erm->relation)
+ if (erm->markType != ROW_MARK_COPY)
{
- Assert(erm->markType != ROW_MARK_COPY);
-
- /* if child rel, need tableoid */
- if (erm->rti != erm->prti)
- {
- snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId);
- aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist,
- resname);
- if (!AttributeNumberIsValid(aerm->toidAttNo))
- elog(ERROR, "could not find junk %s column", resname);
- }
-
- /* always need ctid for real relations */
+ /* need ctid for all methods other than COPY */
snprintf(resname, sizeof(resname), "ctid%u", erm->rowmarkId);
aerm->ctidAttNo = ExecFindJunkAttributeInTlist(targetlist,
resname);
@@ -1934,8 +1924,7 @@ ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist)
}
else
{
- Assert(erm->markType == ROW_MARK_COPY);
-
+ /* need wholerow if COPY */
snprintf(resname, sizeof(resname), "wholerow%u", erm->rowmarkId);
aerm->wholeAttNo = ExecFindJunkAttributeInTlist(targetlist,
resname);
@@ -1943,6 +1932,16 @@ ExecBuildAuxRowMark(ExecRowMark *erm, List *targetlist)
elog(ERROR, "could not find junk %s column", resname);
}
+ /* if child rel, need tableoid */
+ if (erm->rti != erm->prti)
+ {
+ snprintf(resname, sizeof(resname), "tableoid%u", erm->rowmarkId);
+ aerm->toidAttNo = ExecFindJunkAttributeInTlist(targetlist,
+ resname);
+ if (!AttributeNumberIsValid(aerm->toidAttNo))
+ elog(ERROR, "could not find junk %s column", resname);
+ }
+
return aerm;
}
@@ -2375,31 +2374,32 @@ EvalPlanQualFetchRowMarks(EPQState *epqstate)
/* clear any leftover test tuple for this rel */
EvalPlanQualSetTuple(epqstate, erm->rti, NULL);
- if (erm->relation)
+ /* if child rel, must check whether it produced this row */
+ if (erm->rti != erm->prti)
{
- Buffer buffer;
+ Oid tableoid;
- Assert(erm->markType == ROW_MARK_REFERENCE);
+ datum = ExecGetJunkAttribute(epqstate->origslot,
+ aerm->toidAttNo,
+ &isNull);
+ /* non-locked rels could be on the inside of outer joins */
+ if (isNull)
+ continue;
+ tableoid = DatumGetObjectId(datum);
- /* if child rel, must check whether it produced this row */
- if (erm->rti != erm->prti)
+ Assert(OidIsValid(erm->relid));
+ if (tableoid != erm->relid)
{
- Oid tableoid;
+ /* this child is inactive right now */
+ continue;
+ }
+ }
- datum = ExecGetJunkAttribute(epqstate->origslot,
- aerm->toidAttNo,
- &isNull);
- /* non-locked rels could be on the inside of outer joins */
- if (isNull)
- continue;
- tableoid = DatumGetObjectId(datum);
+ if (erm->markType == ROW_MARK_REFERENCE)
+ {
+ Buffer buffer;
- if (tableoid != RelationGetRelid(erm->relation))
- {
- /* this child is inactive right now */
- continue;
- }
- }
+ Assert(erm->relation != NULL);
/* fetch the tuple's ctid */
datum = ExecGetJunkAttribute(epqstate->origslot,
@@ -2439,8 +2439,7 @@ EvalPlanQualFetchRowMarks(EPQState *epqstate)
tuple.t_len = HeapTupleHeaderGetDatumLength(td);
ItemPointerSetInvalid(&(tuple.t_self));
/* relation might be a foreign table, if so provide tableoid */
- tuple.t_tableOid = getrelid(erm->rti,
- epqstate->estate->es_range_table);
+ tuple.t_tableOid = erm->relid;
tuple.t_data = td;
/* copy and store tuple */
diff --git a/src/backend/executor/nodeLockRows.c b/src/backend/executor/nodeLockRows.c
index 48107d93567..bb6df47a95d 100644
--- a/src/backend/executor/nodeLockRows.c
+++ b/src/backend/executor/nodeLockRows.c
@@ -93,7 +93,8 @@ lnext:
elog(ERROR, "tableoid is NULL");
tableoid = DatumGetObjectId(datum);
- if (tableoid != RelationGetRelid(erm->relation))
+ Assert(OidIsValid(erm->relid));
+ if (tableoid != erm->relid)
{
/* this child is inactive right now */
ItemPointerSetInvalid(&(erm->curCtid));
@@ -174,8 +175,9 @@ lnext:
}
/* updated, so fetch and lock the updated version */
- copyTuple = EvalPlanQualFetch(estate, erm->relation, lockmode,
- erm->waitPolicy, &hufd.ctid, hufd.xmax);
+ copyTuple = EvalPlanQualFetch(estate, erm->relation,
+ lockmode, erm->waitPolicy,
+ &hufd.ctid, hufd.xmax);
if (copyTuple == NULL)
{
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 05687a48c9a..876a87ff52a 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -2224,35 +2224,7 @@ preprocess_rowmarks(PlannerInfo *root)
newrc = makeNode(PlanRowMark);
newrc->rti = newrc->prti = rc->rti;
newrc->rowmarkId = ++(root->glob->lastRowMarkId);
- if (rte->relkind == RELKIND_FOREIGN_TABLE)
- {
- /* For now, we force all foreign tables to use ROW_MARK_COPY */
- newrc->markType = ROW_MARK_COPY;
- }
- else
- {
- /* regular table, apply the appropriate lock type */
- switch (rc->strength)
- {
- case LCS_NONE:
- /* we intentionally throw an error for LCS_NONE */
- elog(ERROR, "unrecognized LockClauseStrength %d",
- (int) rc->strength);
- break;
- case LCS_FORKEYSHARE:
- newrc->markType = ROW_MARK_KEYSHARE;
- break;
- case LCS_FORSHARE:
- newrc->markType = ROW_MARK_SHARE;
- break;
- case LCS_FORNOKEYUPDATE:
- newrc->markType = ROW_MARK_NOKEYEXCLUSIVE;
- break;
- case LCS_FORUPDATE:
- newrc->markType = ROW_MARK_EXCLUSIVE;
- break;
- }
- }
+ newrc->markType = select_rowmark_type(rte, rc->strength);
newrc->allMarkTypes = (1 << newrc->markType);
newrc->strength = rc->strength;
newrc->waitPolicy = rc->waitPolicy;
@@ -2277,12 +2249,7 @@ preprocess_rowmarks(PlannerInfo *root)
newrc = makeNode(PlanRowMark);
newrc->rti = newrc->prti = i;
newrc->rowmarkId = ++(root->glob->lastRowMarkId);
- /* real tables support REFERENCE, anything else needs COPY */
- if (rte->rtekind == RTE_RELATION &&
- rte->relkind != RELKIND_FOREIGN_TABLE)
- newrc->markType = ROW_MARK_REFERENCE;
- else
- newrc->markType = ROW_MARK_COPY;
+ newrc->markType = select_rowmark_type(rte, LCS_NONE);
newrc->allMarkTypes = (1 << newrc->markType);
newrc->strength = LCS_NONE;
newrc->waitPolicy = LockWaitBlock; /* doesn't matter */
@@ -2295,6 +2262,49 @@ preprocess_rowmarks(PlannerInfo *root)
}
/*
+ * Select RowMarkType to use for a given table
+ */
+RowMarkType
+select_rowmark_type(RangeTblEntry *rte, LockClauseStrength strength)
+{
+ if (rte->rtekind != RTE_RELATION)
+ {
+ /* If it's not a table at all, use ROW_MARK_COPY */
+ return ROW_MARK_COPY;
+ }
+ else if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ /* For now, we force all foreign tables to use ROW_MARK_COPY */
+ return ROW_MARK_COPY;
+ }
+ else
+ {
+ /* Regular table, apply the appropriate lock type */
+ switch (strength)
+ {
+ case LCS_NONE:
+ /* don't need tuple lock, only ability to re-fetch the row */
+ return ROW_MARK_REFERENCE;
+ break;
+ case LCS_FORKEYSHARE:
+ return ROW_MARK_KEYSHARE;
+ break;
+ case LCS_FORSHARE:
+ return ROW_MARK_SHARE;
+ break;
+ case LCS_FORNOKEYUPDATE:
+ return ROW_MARK_NOKEYEXCLUSIVE;
+ break;
+ case LCS_FORUPDATE:
+ return ROW_MARK_EXCLUSIVE;
+ break;
+ }
+ elog(ERROR, "unrecognized LockClauseStrength %d", (int) strength);
+ return ROW_MARK_EXCLUSIVE; /* keep compiler quiet */
+ }
+}
+
+/*
* preprocess_limit - do pre-estimation for LIMIT and/or OFFSET clauses
*
* We try to estimate the values of the LIMIT/OFFSET clauses, and pass the
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index cd40afce767..51b3da21b30 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -1337,12 +1337,13 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
/*
* 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,
- * and set inh = false. Also, set requiredPerms to zero since all
- * required permissions checks are done on the original RTE.
+ * We copy most fields of the parent's RTE, but replace relation OID
+ * and relkind, and set inh = false. Also, set requiredPerms to zero
+ * since all required permissions checks are done on the original RTE.
*/
childrte = copyObject(rte);
childrte->relid = childOID;
+ childrte->relkind = newrelation->rd_rel->relkind;
childrte->inh = false;
childrte->requiredPerms = 0;
parse->rtable = lappend(parse->rtable, childrte);
@@ -1388,7 +1389,8 @@ expand_inherited_rtentry(PlannerInfo *root, RangeTblEntry *rte, Index rti)
newrc->rti = childRTindex;
newrc->prti = rti;
newrc->rowmarkId = oldrc->rowmarkId;
- newrc->markType = oldrc->markType;
+ /* Reselect rowmark type, because relkind might not match parent */
+ newrc->markType = select_rowmark_type(childrte, oldrc->strength);
newrc->allMarkTypes = (1 << newrc->markType);
newrc->strength = oldrc->strength;
newrc->waitPolicy = oldrc->waitPolicy;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 82405b9d26f..873ca79492d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2762,6 +2762,7 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->relation = $4;
n->tableElts = $6;
n->inhRelations = $8;
+ n->ofTypename = NULL;
n->constraints = NIL;
n->options = $9;
n->oncommit = $10;
@@ -2778,6 +2779,7 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
n->relation = $7;
n->tableElts = $9;
n->inhRelations = $11;
+ n->ofTypename = NULL;
n->constraints = NIL;
n->options = $12;
n->oncommit = $13;
@@ -2792,6 +2794,7 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$4->relpersistence = $2;
n->relation = $4;
n->tableElts = $7;
+ n->inhRelations = NIL;
n->ofTypename = makeTypeNameFromNameList($6);
n->ofTypename->location = @6;
n->constraints = NIL;
@@ -2808,6 +2811,7 @@ CreateStmt: CREATE OptTemp TABLE qualified_name '(' OptTableElementList ')'
$7->relpersistence = $2;
n->relation = $7;
n->tableElts = $10;
+ n->inhRelations = NIL;
n->ofTypename = makeTypeNameFromNameList($9);
n->ofTypename->location = @9;
n->constraints = NIL;
@@ -4360,32 +4364,42 @@ AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o
CreateForeignTableStmt:
CREATE FOREIGN TABLE qualified_name
'(' OptTableElementList ')'
- SERVER name create_generic_options
+ OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$4->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $4;
n->base.tableElts = $6;
- n->base.inhRelations = NIL;
+ n->base.inhRelations = $8;
+ n->base.ofTypename = NULL;
+ n->base.constraints = NIL;
+ n->base.options = NIL;
+ n->base.oncommit = ONCOMMIT_NOOP;
+ n->base.tablespacename = NULL;
n->base.if_not_exists = false;
/* FDW-specific data */
- n->servername = $9;
- n->options = $10;
+ n->servername = $10;
+ n->options = $11;
$$ = (Node *) n;
}
| CREATE FOREIGN TABLE IF_P NOT EXISTS qualified_name
'(' OptTableElementList ')'
- SERVER name create_generic_options
+ OptInherit SERVER name create_generic_options
{
CreateForeignTableStmt *n = makeNode(CreateForeignTableStmt);
$7->relpersistence = RELPERSISTENCE_PERMANENT;
n->base.relation = $7;
n->base.tableElts = $9;
- n->base.inhRelations = NIL;
+ n->base.inhRelations = $11;
+ n->base.ofTypename = NULL;
+ n->base.constraints = NIL;
+ n->base.options = NIL;
+ n->base.oncommit = ONCOMMIT_NOOP;
+ n->base.tablespacename = NULL;
n->base.if_not_exists = true;
/* FDW-specific data */
- n->servername = $12;
- n->options = $13;
+ n->servername = $13;
+ n->options = $14;
$$ = (Node *) n;
}
;
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f24fefa2753..7da5c411949 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -13748,8 +13748,9 @@ dumpTableSchema(Archive *fout, DumpOptions *dopt, TableInfo *tbinfo)
* attislocal correctly, plus fix up any inherited CHECK constraints.
* Analogously, we set up typed tables using ALTER TABLE / OF here.
*/
- if (dopt->binary_upgrade && (tbinfo->relkind == RELKIND_RELATION ||
- tbinfo->relkind == RELKIND_FOREIGN_TABLE))
+ if (dopt->binary_upgrade &&
+ (tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_FOREIGN_TABLE))
{
for (j = 0; j < tbinfo->numatts; j++)
{
@@ -13771,15 +13772,13 @@ dumpTableSchema(Archive *fout, DumpOptions *dopt, TableInfo *tbinfo)
appendPQExpBuffer(q, "ALTER TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
else
- appendPQExpBuffer(q, "ALTER FOREIGN TABLE %s ",
+ appendPQExpBuffer(q, "ALTER FOREIGN TABLE ONLY %s ",
fmtId(tbinfo->dobj.name));
-
appendPQExpBuffer(q, "DROP COLUMN %s;\n",
fmtId(tbinfo->attnames[j]));
}
else if (!tbinfo->attislocal[j])
{
- Assert(tbinfo->relkind != RELKIND_FOREIGN_TABLE);
appendPQExpBufferStr(q, "\n-- For binary upgrade, recreate inherited column.\n");
appendPQExpBufferStr(q, "UPDATE pg_catalog.pg_attribute\n"
"SET attislocal = false\n"
@@ -13985,7 +13984,8 @@ dumpTableSchema(Archive *fout, DumpOptions *dopt, TableInfo *tbinfo)
/*
* dump properties we only have ALTER TABLE syntax for
*/
- if ((tbinfo->relkind == RELKIND_RELATION || tbinfo->relkind == RELKIND_MATVIEW) &&
+ if ((tbinfo->relkind == RELKIND_RELATION ||
+ tbinfo->relkind == RELKIND_MATVIEW) &&
tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT)
{
if (tbinfo->relreplident == REPLICA_IDENTITY_INDEX)
@@ -14004,6 +14004,10 @@ dumpTableSchema(Archive *fout, DumpOptions *dopt, TableInfo *tbinfo)
}
}
+ if (tbinfo->relkind == RELKIND_FOREIGN_TABLE && tbinfo->hasoids)
+ appendPQExpBuffer(q, "\nALTER TABLE ONLY %s SET WITH OIDS;\n",
+ fmtId(tbinfo->dobj.name));
+
if (dopt->binary_upgrade)
binary_upgrade_extension_member(q, &tbinfo->dobj, labelq->data);
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 59b17f3c993..ac75f86fef7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -414,17 +414,20 @@ typedef struct EState
* ExecRowMark -
* runtime representation of FOR [KEY] UPDATE/SHARE clauses
*
- * When doing UPDATE, DELETE, or SELECT FOR [KEY] UPDATE/SHARE, we should have an
+ * When doing UPDATE, DELETE, or SELECT FOR [KEY] UPDATE/SHARE, we will have an
* ExecRowMark for each non-target relation in the query (except inheritance
- * parent RTEs, which can be ignored at runtime). See PlanRowMark for details
- * about most of the fields. In addition to fields directly derived from
- * PlanRowMark, we store curCtid, which is used by the WHERE CURRENT OF code.
+ * parent RTEs, which can be ignored at runtime). Virtual relations such as
+ * subqueries-in-FROM will have an ExecRowMark with relation == NULL. See
+ * PlanRowMark for details about most of the fields. In addition to fields
+ * directly derived from PlanRowMark, we store curCtid, which is used by the
+ * WHERE CURRENT OF code.
*
* EState->es_rowMarks is a list of these structs.
*/
typedef struct ExecRowMark
{
Relation relation; /* opened and suitably locked relation */
+ Oid relid; /* its OID (or InvalidOid, if subquery) */
Index rti; /* its range table index */
Index prti; /* parent range table index, if child */
Index rowmarkId; /* unique identifier for resjunk columns */
diff --git a/src/include/optimizer/planner.h b/src/include/optimizer/planner.h
index cd62aec08f1..b10a5040f5b 100644
--- a/src/include/optimizer/planner.h
+++ b/src/include/optimizer/planner.h
@@ -40,6 +40,9 @@ extern void add_tlist_costs_to_plan(PlannerInfo *root, Plan *plan,
extern bool is_dummy_plan(Plan *plan);
+extern RowMarkType select_rowmark_type(RangeTblEntry *rte,
+ LockClauseStrength strength);
+
extern Expr *expression_planner(Expr *expr);
extern Expr *preprocess_phv_expression(PlannerInfo *root, Expr *expr);
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 512d97ff6dc..73c02bb10f0 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -755,6 +755,7 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN;
\d+ ft1
Foreign table "public.ft1"
Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
@@ -766,7 +767,7 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
c5 | integer | | | plain | |
c6 | integer | not null | | plain | |
c7 | integer | | (p1 'v1', p2 'v2') | plain | |
- c8 | text | | (p2 'V2') | extended | |
+ c8 | text | | (p2 'V2') | plain | |
c9 | integer | | | plain | |
c10 | integer | | (p1 'v1') | plain | |
Check constraints:
@@ -784,9 +785,7 @@ ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR
ERROR: primary key constraints are not supported on foreign tables
LINE 1: ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7);
^
-ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR
-ERROR: CHECK constraints on foreign tables cannot be marked NOT VALID
-ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0);
+ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID;
ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
ERROR: "ft1" is not a table
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
@@ -794,8 +793,7 @@ ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ERROR: constraint "no_const" of relation "ft1" does not exist
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
NOTICE: constraint "no_const" of relation "ft1" does not exist, skipping
-ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
-ERROR: "ft1" is not a table
+ALTER FOREIGN TABLE ft1 SET WITH OIDS;
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR
@@ -1234,6 +1232,501 @@ DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+-- Table inheritance
+CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+);
+CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+DROP FOREIGN TABLE ft2;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+
+CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+
+ALTER FOREIGN TABLE ft2 INHERIT pt1;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+CREATE TABLE ct3() INHERITS(ft2);
+CREATE FOREIGN TABLE ft3 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) INHERITS(ft2)
+ SERVER s0;
+NOTICE: merging column "c1" with inherited definition
+NOTICE: merging column "c2" with inherited definition
+NOTICE: merging column "c3" with inherited definition
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Child tables: ct3,
+ ft3
+
+\d+ ct3
+ Table "public.ct3"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Inherits: ft2
+
+\d+ ft3
+ Foreign table "public.ft3"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+Inherits: ft2
+
+-- add attributes recursively
+ALTER TABLE pt1 ADD COLUMN c4 integer;
+ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ALTER TABLE pt1 ADD COLUMN c6 integer;
+ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ALTER TABLE pt1 ADD COLUMN c8 integer;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | | plain | |
+ c5 | integer | default 0 | plain | |
+ c6 | integer | | plain | |
+ c7 | integer | not null | plain | |
+ c8 | integer | | plain | |
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | | | plain | |
+ c5 | integer | default 0 | | plain | |
+ c6 | integer | | | plain | |
+ c7 | integer | not null | | plain | |
+ c8 | integer | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Child tables: ct3,
+ ft3
+
+\d+ ct3
+ Table "public.ct3"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | | plain | |
+ c5 | integer | default 0 | plain | |
+ c6 | integer | | plain | |
+ c7 | integer | not null | plain | |
+ c8 | integer | | plain | |
+Inherits: ft2
+
+\d+ ft3
+ Foreign table "public.ft3"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | | | plain | |
+ c5 | integer | default 0 | | plain | |
+ c6 | integer | | | plain | |
+ c7 | integer | not null | | plain | |
+ c8 | integer | | | plain | |
+Server: s0
+Inherits: ft2
+
+-- alter attributes recursively
+ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ERROR: "ft2" is not a table
+ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ALTER TABLE pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+ c4 | integer | default 0 | plain | |
+ c5 | integer | | plain | |
+ c6 | integer | not null | plain | |
+ c7 | integer | | plain | |
+ c8 | text | | external | |
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+ c4 | integer | default 0 | | plain | |
+ c5 | integer | | | plain | |
+ c6 | integer | not null | | plain | |
+ c7 | integer | | | plain | |
+ c8 | text | | | external | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Child tables: ct3,
+ ft3
+
+-- drop attributes recursively
+ALTER TABLE pt1 DROP COLUMN c4;
+ALTER TABLE pt1 DROP COLUMN c5;
+ALTER TABLE pt1 DROP COLUMN c6;
+ALTER TABLE pt1 DROP COLUMN c7;
+ALTER TABLE pt1 DROP COLUMN c8;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Child tables: ct3,
+ ft3
+
+-- add constraints recursively
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+-- connoinherit should be true for NO INHERIT constraint
+SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
+ FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid)
+ WHERE pc.relname = 'pt1'
+ ORDER BY 1,2;
+ relname | conname | contype | conislocal | coninhcount | connoinherit
+---------+---------+---------+------------+-------------+--------------
+ pt1 | pt1chk1 | c | t | 0 | t
+ pt1 | pt1chk2 | c | t | 0 | f
+(2 rows)
+
+-- child does not inherit NO INHERIT constraints
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | 10000 |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Child tables: ct3,
+ ft3
+
+DROP FOREIGN TABLE ft2; -- ERROR
+ERROR: cannot drop foreign table ft2 because other objects depend on it
+DETAIL: table ct3 depends on foreign table ft2
+foreign table ft3 depends on foreign table ft2
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP FOREIGN TABLE ft2 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table ct3
+drop cascades to foreign table ft3
+CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+-- child must have parent's INHERIT constraints
+ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ERROR: child table is missing constraint "pt1chk2"
+ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ALTER FOREIGN TABLE ft2 INHERIT pt1;
+-- child does not inherit NO INHERIT constraints
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk1" CHECK (c1 > 0) NO INHERIT
+ "pt1chk2" CHECK (c2 <> ''::text)
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+-- drop constraints recursively
+ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+-- NOT VALID case
+INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk3 CHECK (c2 <> '') NOT VALID;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk3" CHECK (c2 <> ''::text) NOT VALID
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ "pt1chk3" CHECK (c2 <> ''::text) NOT VALID
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+-- VALIDATE CONSTRAINT need do nothing on foreign tables
+ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk3;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk3" CHECK (c2 <> ''::text)
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ "pt1chk3" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+-- OID system column
+ALTER TABLE pt1 SET WITH OIDS;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk3" CHECK (c2 <> ''::text)
+Child tables: ft2
+Has OIDs: yes
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ "pt1chk3" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+Has OIDs: yes
+
+ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR
+ERROR: cannot drop inherited column "oid"
+ALTER TABLE pt1 SET WITHOUT OIDS;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ c1 | integer | not null | plain | 10000 |
+ c2 | text | | extended | |
+ c3 | date | | plain | |
+Check constraints:
+ "pt1chk3" CHECK (c2 <> ''::text)
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ c1 | integer | not null | | plain | |
+ c2 | text | | | extended | |
+ c3 | date | | | plain | |
+Check constraints:
+ "pt1chk2" CHECK (c2 <> ''::text)
+ "pt1chk3" CHECK (c2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+-- changes name of an attribute recursively
+ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+-- changes name of a constraint recursively
+ALTER TABLE pt1 RENAME CONSTRAINT pt1chk3 TO f2_check;
+\d+ pt1
+ Table "public.pt1"
+ Column | Type | Modifiers | Storage | Stats target | Description
+--------+---------+-----------+----------+--------------+-------------
+ f1 | integer | not null | plain | 10000 |
+ f2 | text | | extended | |
+ f3 | date | | plain | |
+Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+Child tables: ft2
+
+\d+ ft2
+ Foreign table "public.ft2"
+ Column | Type | Modifiers | FDW Options | Storage | Stats target | Description
+--------+---------+-----------+-------------+----------+--------------+-------------
+ f1 | integer | not null | | plain | |
+ f2 | text | | | extended | |
+ f3 | date | | | plain | |
+Check constraints:
+ "f2_check" CHECK (f2 <> ''::text)
+ "pt1chk2" CHECK (f2 <> ''::text)
+Server: s0
+FDW Options: (delimiter ',', quote '"', "be quoted" 'value')
+Inherits: pt1
+
+-- TRUNCATE doesn't work on foreign tables, either directly or recursively
+TRUNCATE ft2; -- ERROR
+ERROR: "ft2" is not a table
+TRUNCATE pt1; -- ERROR
+ERROR: "ft2" is not a table
+DROP TABLE pt1 CASCADE;
+NOTICE: drop cascades to foreign table ft2
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
ERROR: foreign-data wrapper "foo" has no handler
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index f41bef17044..44e8dab44c2 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1037,6 +1037,9 @@ EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
QUERY PLAN
-------------------------------------------
Update on t1 t1_3
+ Update on t1 t1_3
+ Update on t2 t1
+ Update on t3 t1
-> Subquery Scan on t1
Filter: f_leak(t1.b)
-> LockRows
@@ -1052,7 +1055,7 @@ EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
-> LockRows
-> Seq Scan on t3
Filter: ((a % 2) = 0)
-(16 rows)
+(19 rows)
UPDATE t1 SET b = b || b WHERE f_leak(b);
NOTICE: f_leak => bbb
@@ -1149,6 +1152,9 @@ EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
QUERY PLAN
-------------------------------------------
Delete on t1 t1_3
+ Delete on t1 t1_3
+ Delete on t2 t1
+ Delete on t3 t1
-> Subquery Scan on t1
Filter: f_leak(t1.b)
-> LockRows
@@ -1164,7 +1170,7 @@ EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
-> LockRows
-> Seq Scan on t3
Filter: ((a % 2) = 0)
-(16 rows)
+(19 rows)
DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
NOTICE: f_leak => bbbbbb_updt
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index c49e769bf83..9e7ba724710 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2064,6 +2064,10 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Update on public.t1 t1_4
+ Update on public.t1 t1_4
+ Update on public.t11 t1
+ Update on public.t12 t1
+ Update on public.t111 t1
-> Subquery Scan on t1
Output: 100, t1.b, t1.c, t1.ctid
Filter: snoop(t1.a)
@@ -2132,7 +2136,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
-> Seq Scan on public.t111 t111_4
Output: t111_4.ctid, t111_4.tableoid, t111_4.a
Filter: (t111_4.a = 3)
-(69 rows)
+(73 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a = 3;
SELECT * FROM v1 WHERE a=100; -- Nothing should have been changed to 100
@@ -2150,6 +2154,10 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Update on public.t1 t1_4
+ Update on public.t1 t1_4
+ Update on public.t11 t1
+ Update on public.t12 t1
+ Update on public.t111 t1
-> Subquery Scan on t1
Output: (t1.a + 1), t1.b, t1.c, t1.ctid
Filter: snoop(t1.a)
@@ -2218,7 +2226,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Seq Scan on public.t111 t111_4
Output: t111_4.ctid, t111_4.tableoid, t111_4.a
Filter: (t111_4.a = 8)
-(69 rows)
+(73 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
NOTICE: snooped value: 8
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 524e0ef2c68..6986f4777ef 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2086,6 +2086,10 @@ DELETE FROM a USING wcte WHERE aa = q2;
QUERY PLAN
------------------------------------------------
Delete on public.a
+ Delete on public.a
+ Delete on public.b
+ Delete on public.c
+ Delete on public.d
CTE wcte
-> Insert on public.int8_tbl
Output: int8_tbl.q2
@@ -2119,7 +2123,7 @@ DELETE FROM a USING wcte WHERE aa = q2;
Output: d.ctid, d.aa
-> CTE Scan on wcte
Output: wcte.*, wcte.q2
-(34 rows)
+(38 rows)
-- error cases
-- data-modifying WITH tries to use its own output
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index b4b999d444e..af3d531289a 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -328,19 +328,19 @@ ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 OPTIONS (SET p2 'V2', DROP p1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET STATISTICS 10000;
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 SET (n_distinct = 100);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STATISTICS -1;
+ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET STORAGE PLAIN;
\d+ ft1
-- can't change the column type if it's used elsewhere
CREATE TABLE use_ft1_column_type (x ft1);
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 SET DATA TYPE integer; -- ERROR
DROP TABLE use_ft1_column_type;
ALTER FOREIGN TABLE ft1 ADD PRIMARY KEY (c7); -- ERROR
-ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID; -- ERROR
-ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0);
+ALTER FOREIGN TABLE ft1 ADD CONSTRAINT ft1_c9_check CHECK (c9 < 0) NOT VALID;
ALTER FOREIGN TABLE ft1 ALTER CONSTRAINT ft1_c9_check DEFERRABLE; -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT ft1_c9_check;
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT no_const; -- ERROR
ALTER FOREIGN TABLE ft1 DROP CONSTRAINT IF EXISTS no_const;
-ALTER FOREIGN TABLE ft1 SET WITH OIDS; -- ERROR
+ALTER FOREIGN TABLE ft1 SET WITH OIDS;
ALTER FOREIGN TABLE ft1 OWNER TO regress_test_role;
ALTER FOREIGN TABLE ft1 OPTIONS (DROP delimiter, SET quote '~', ADD escape '@');
ALTER FOREIGN TABLE ft1 DROP COLUMN no_column; -- ERROR
@@ -536,6 +536,137 @@ DROP TRIGGER trigtest_after_row ON foreign_schema.foreign_table_1;
DROP FUNCTION dummy_trigger();
+-- Table inheritance
+CREATE TABLE pt1 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+);
+CREATE FOREIGN TABLE ft2 () INHERITS (pt1)
+ SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ pt1
+\d+ ft2
+DROP FOREIGN TABLE ft2;
+\d+ pt1
+CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+\d+ ft2
+ALTER FOREIGN TABLE ft2 INHERIT pt1;
+\d+ pt1
+\d+ ft2
+CREATE TABLE ct3() INHERITS(ft2);
+CREATE FOREIGN TABLE ft3 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) INHERITS(ft2)
+ SERVER s0;
+\d+ ft2
+\d+ ct3
+\d+ ft3
+
+-- add attributes recursively
+ALTER TABLE pt1 ADD COLUMN c4 integer;
+ALTER TABLE pt1 ADD COLUMN c5 integer DEFAULT 0;
+ALTER TABLE pt1 ADD COLUMN c6 integer;
+ALTER TABLE pt1 ADD COLUMN c7 integer NOT NULL;
+ALTER TABLE pt1 ADD COLUMN c8 integer;
+\d+ pt1
+\d+ ft2
+\d+ ct3
+\d+ ft3
+
+-- alter attributes recursively
+ALTER TABLE pt1 ALTER COLUMN c4 SET DEFAULT 0;
+ALTER TABLE pt1 ALTER COLUMN c5 DROP DEFAULT;
+ALTER TABLE pt1 ALTER COLUMN c6 SET NOT NULL;
+ALTER TABLE pt1 ALTER COLUMN c7 DROP NOT NULL;
+ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10) USING '0'; -- ERROR
+ALTER TABLE pt1 ALTER COLUMN c8 TYPE char(10);
+ALTER TABLE pt1 ALTER COLUMN c8 SET DATA TYPE text;
+ALTER TABLE pt1 ALTER COLUMN c1 SET STATISTICS 10000;
+ALTER TABLE pt1 ALTER COLUMN c1 SET (n_distinct = 100);
+ALTER TABLE pt1 ALTER COLUMN c8 SET STATISTICS -1;
+ALTER TABLE pt1 ALTER COLUMN c8 SET STORAGE EXTERNAL;
+\d+ pt1
+\d+ ft2
+
+-- drop attributes recursively
+ALTER TABLE pt1 DROP COLUMN c4;
+ALTER TABLE pt1 DROP COLUMN c5;
+ALTER TABLE pt1 DROP COLUMN c6;
+ALTER TABLE pt1 DROP COLUMN c7;
+ALTER TABLE pt1 DROP COLUMN c8;
+\d+ pt1
+\d+ ft2
+
+-- add constraints recursively
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk1 CHECK (c1 > 0) NO INHERIT;
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+-- connoinherit should be true for NO INHERIT constraint
+SELECT relname, conname, contype, conislocal, coninhcount, connoinherit
+ FROM pg_class AS pc JOIN pg_constraint AS pgc ON (conrelid = pc.oid)
+ WHERE pc.relname = 'pt1'
+ ORDER BY 1,2;
+-- child does not inherit NO INHERIT constraints
+\d+ pt1
+\d+ ft2
+DROP FOREIGN TABLE ft2; -- ERROR
+DROP FOREIGN TABLE ft2 CASCADE;
+CREATE FOREIGN TABLE ft2 (
+ c1 integer NOT NULL,
+ c2 text,
+ c3 date
+) SERVER s0 OPTIONS (delimiter ',', quote '"', "be quoted" 'value');
+-- child must have parent's INHERIT constraints
+ALTER FOREIGN TABLE ft2 INHERIT pt1; -- ERROR
+ALTER FOREIGN TABLE ft2 ADD CONSTRAINT pt1chk2 CHECK (c2 <> '');
+ALTER FOREIGN TABLE ft2 INHERIT pt1;
+-- child does not inherit NO INHERIT constraints
+\d+ pt1
+\d+ ft2
+
+-- drop constraints recursively
+ALTER TABLE pt1 DROP CONSTRAINT pt1chk1 CASCADE;
+ALTER TABLE pt1 DROP CONSTRAINT pt1chk2 CASCADE;
+
+-- NOT VALID case
+INSERT INTO pt1 VALUES (1, 'pt1'::text, '1994-01-01'::date);
+ALTER TABLE pt1 ADD CONSTRAINT pt1chk3 CHECK (c2 <> '') NOT VALID;
+\d+ pt1
+\d+ ft2
+-- VALIDATE CONSTRAINT need do nothing on foreign tables
+ALTER TABLE pt1 VALIDATE CONSTRAINT pt1chk3;
+\d+ pt1
+\d+ ft2
+
+-- OID system column
+ALTER TABLE pt1 SET WITH OIDS;
+\d+ pt1
+\d+ ft2
+ALTER TABLE ft2 SET WITHOUT OIDS; -- ERROR
+ALTER TABLE pt1 SET WITHOUT OIDS;
+\d+ pt1
+\d+ ft2
+
+-- changes name of an attribute recursively
+ALTER TABLE pt1 RENAME COLUMN c1 TO f1;
+ALTER TABLE pt1 RENAME COLUMN c2 TO f2;
+ALTER TABLE pt1 RENAME COLUMN c3 TO f3;
+-- changes name of a constraint recursively
+ALTER TABLE pt1 RENAME CONSTRAINT pt1chk3 TO f2_check;
+\d+ pt1
+\d+ ft2
+
+-- TRUNCATE doesn't work on foreign tables, either directly or recursively
+TRUNCATE ft2; -- ERROR
+TRUNCATE pt1; -- ERROR
+
+DROP TABLE pt1 CASCADE;
+
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-31 13:44:06 +0000