public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v26 07/10] Add Incremental View Maintenance support
13+ messages / 4 participants
[nested] [flat]
* [PATCH v26 07/10] Add Incremental View Maintenance support
@ 2020-12-22 09:40 Yugo Nagata <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Yugo Nagata @ 2020-12-22 09:40 UTC (permalink / raw)
In this implementation, AFTER triggers are used to collect
tuplestores containing transition table contents. When multiple tables
are changed, multiple AFTER triggers are invoked, then the final AFTER
trigger performs actual update of the matview. In addition, BEFORE
triggers are also used to handle global information for view
maintenance. To calculate view deltas, we need both pre-state and
post-state of base tables. Post-update states are available in AFTER
trigger, and pre-update states can be calculated by filtering inserted
tuples using cmin/xmin system columns, and append deleted tuples which
are contained in an old transition table.
This patch also allows self-join, simultaneous updates of more than
one base table, and multiple updates of the same base table.
Incrementally Maintainable Materialized Views (IMMV) can contain
duplicated tuples. Also, DISTINCT clause is supported. When IMMV is
created with DISTINCT, multiplicity of tuples is counted and stored
in "__ivm_count__" column, which is a hidden column of IMMV.
The value in __ivm_count__ is updated when IMMV is maintained
incrementally. A tuple in IMMV can be removed if and only if the
count becomes zero.
---
src/backend/access/transam/xact.c | 5 +
src/backend/commands/createas.c | 746 +++++++++++++
src/backend/commands/indexcmds.c | 40 +
src/backend/commands/matview.c | 1555 ++++++++++++++++++++++++++-
src/backend/commands/tablecmds.c | 9 +
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/parser/parse_relation.c | 18 +-
src/backend/rewrite/rewriteDefine.c | 3 +-
src/include/catalog/pg_proc.dat | 8 +
src/include/commands/createas.h | 6 +
src/include/commands/matview.h | 8 +
src/include/nodes/parsenodes.h | 2 +
15 files changed, 2364 insertions(+), 40 deletions(-)
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index 8964ddf3eb..5d9ab6b1f9 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -36,6 +36,7 @@
#include "catalog/pg_enum.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/trigger.h"
#include "common/pg_prng.h"
@@ -2777,6 +2778,7 @@ AbortTransaction(void)
AtAbort_Notify();
AtEOXact_RelationMap(false, is_parallel_worker);
AtAbort_Twophase();
+ AtAbort_IVM();
/*
* Advertise the fact that we aborted in pg_xact (assuming that we got as
@@ -5017,6 +5019,9 @@ AbortSubTransaction(void)
AbortBufferIO();
UnlockBuffers();
+ /* Clean up hash entries for incremental view maintenance */
+ AtAbort_IVM();
+
/* Reset WAL record construction state */
XLogResetInsertion();
diff --git a/src/backend/commands/createas.c b/src/backend/commands/createas.c
index 9abbb6b555..1fbcede7aa 100644
--- a/src/backend/commands/createas.c
+++ b/src/backend/commands/createas.c
@@ -32,24 +32,41 @@
#include "access/xact.h"
#include "access/xlog.h"
#include "catalog/namespace.h"
+#include "catalog/index.h"
+#include "catalog/pg_constraint.h"
+#include "catalog/pg_inherits.h"
+#include "catalog/pg_trigger.h"
+#include "catalog/pg_type.h"
#include "catalog/toasting.h"
#include "commands/createas.h"
+#include "commands/defrem.h"
#include "commands/matview.h"
#include "commands/prepare.h"
#include "commands/tablecmds.h"
+#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "commands/view.h"
#include "miscadmin.h"
+#include "optimizer/clauses.h"
+#include "optimizer/optimizer.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
#include "parser/parse_clause.h"
+#include "parser/parse_func.h"
+#include "parser/parse_type.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/rls.h"
#include "utils/snapmgr.h"
+#include "utils/syscache.h"
typedef struct
{
@@ -73,6 +90,13 @@ static bool intorel_receive(TupleTableSlot *slot, DestReceiver *self);
static void intorel_shutdown(DestReceiver *self);
static void intorel_destroy(DestReceiver *self);
+static void CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock);
+static void CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock);
+static void check_ivm_restriction(Node *node);
+static bool check_ivm_restriction_walker(Node *node, void *context);
+static Bitmapset *get_primary_key_attnos_from_query(Query *qry, List **constraintList, bool is_create);
+static Bitmapset *get_primary_key_attnos_from_query(Query *query, List **constraintList, bool is_create);
/*
* create_ctas_internal
@@ -108,6 +132,8 @@ create_ctas_internal(List *attrList, IntoClause *into)
create->oncommit = into->onCommit;
create->tablespacename = into->tableSpaceName;
create->if_not_exists = false;
+ /* Using Materialized view only */
+ create->ivm = into->ivm;
create->accessMethod = into->accessMethod;
/*
@@ -282,6 +308,21 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
save_nestlevel = NewGUCNestLevel();
}
+ if (is_matview && into->ivm)
+ {
+ /* check if the query is supported in IMMV definition */
+ if (contain_mutable_functions((Node *) query))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("mutable function is not supported on incrementally maintainable materialized view"),
+ errhint("functions must be marked IMMUTABLE")));
+
+ check_ivm_restriction((Node *) query);
+
+ /* For IMMV, we need to rewrite matview query */
+ query = rewriteQueryForIMMV(query, into->colNames);
+ }
+
if (into->skipData)
{
/*
@@ -358,11 +399,74 @@ ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *stmt,
/* Restore userid and security context */
SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ if (into->ivm)
+ {
+ Oid matviewOid = address.objectId;
+ Relation matviewRel = table_open(matviewOid, NoLock);
+
+ /*
+ * Mark relisivm field, if it's a matview and into->ivm is true.
+ */
+ SetMatViewIVMState(matviewRel, true);
+
+ if (!into->skipData)
+ {
+ /* Create an index on incremental maintainable materialized view, if possible */
+ CreateIndexOnIMMV((Query *) into->viewQuery, matviewRel, true);
+
+ /* Create triggers on incremental maintainable materialized view */
+ CreateIvmTriggersOnBaseTables((Query *) into->viewQuery, matviewOid, true);
+ }
+ table_close(matviewRel, NoLock);
+ }
}
return address;
}
+/*
+ * rewriteQueryForIMMV -- rewrite view definition query for IMMV
+ *
+ * count(*) is added for counting distinct tuples in views.
+ */
+Query *
+rewriteQueryForIMMV(Query *query, List *colNames)
+{
+ Query *rewritten;
+
+ TargetEntry *tle;
+ Node *node;
+ ParseState *pstate = make_parsestate(NULL);
+ FuncCall *fn;
+
+ rewritten = copyObject(query);
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * Convert DISTINCT to GROUP BY and add count(*) for counting distinct
+ * tuples in views.
+ */
+ if (rewritten->distinctClause)
+ {
+ rewritten->groupClause = transformDistinctClause(NULL, &rewritten->targetList, rewritten->sortClause, false);
+
+ fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle = makeTargetEntry((Expr *) node,
+ list_length(rewritten->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ rewritten->targetList = lappend(rewritten->targetList, tle);
+ rewritten->hasAggs = true;
+ }
+
+ return rewritten;
+}
+
/*
* GetIntoRelEFlags --- compute executor flags needed for CREATE TABLE AS
*
@@ -623,3 +727,645 @@ intorel_destroy(DestReceiver *self)
{
pfree(self);
}
+
+/*
+ * CreateIvmTriggersOnBaseTables -- create IVM triggers on all base tables
+ */
+void
+CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid, bool is_create)
+{
+ Relids relids = NULL;
+ bool ex_lock = false;
+ Index first_rtindex = is_create ? 1 : PRS2_NEW_VARNO + 1;
+ RangeTblEntry *rte;
+
+ /* Immediately return if we don't have any base tables. */
+ if (list_length(qry->rtable) < first_rtindex)
+ return;
+
+ /*
+ * If the view has more than one base tables, we need an exclusive lock
+ * on the view so that the view would be maintained serially to avoid
+ * the inconsistency that occurs when two base tables are modified in
+ * concurrent transactions. However, if the view has only one table,
+ * we can use a weaker lock.
+ *
+ * The type of lock should be determined here, because if we check the
+ * view definition at maintenance time, we need to acquire a weaker lock,
+ * and upgrading the lock level after this increases probability of
+ * deadlock.
+ */
+
+ rte = list_nth(qry->rtable, first_rtindex - 1);
+ if (list_length(qry->rtable) > first_rtindex ||
+ rte->rtekind != RTE_RELATION)
+ ex_lock = true;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)qry, matviewOid, &relids, ex_lock);
+
+ bms_free(relids);
+}
+
+static void
+CreateIvmTriggersOnBaseTablesRecurse(Query *qry, Node *node, Oid matviewOid,
+ Relids *relids, bool ex_lock)
+{
+ if (node == NULL)
+ return;
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *query = (Query *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, (Node *)query->jointree, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_RangeTblRef:
+ {
+ int rti = ((RangeTblRef *) node)->rtindex;
+ RangeTblEntry *rte = rt_fetch(rti, qry->rtable);
+
+ if (rte->rtekind == RTE_RELATION && !bms_is_member(rte->relid, *relids))
+ {
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_BEFORE, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_INSERT, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_DELETE, TRIGGER_TYPE_AFTER, ex_lock);
+ CreateIvmTrigger(rte->relid, matviewOid, TRIGGER_TYPE_UPDATE, TRIGGER_TYPE_AFTER, ex_lock);
+
+ *relids = bms_add_member(*relids, rte->relid);
+ }
+ }
+ break;
+
+ case T_FromExpr:
+ {
+ FromExpr *f = (FromExpr *) node;
+ ListCell *l;
+
+ foreach(l, f->fromlist)
+ CreateIvmTriggersOnBaseTablesRecurse(qry, lfirst(l), matviewOid, relids, ex_lock);
+ }
+ break;
+
+ case T_JoinExpr:
+ {
+ JoinExpr *j = (JoinExpr *) node;
+
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->larg, matviewOid, relids, ex_lock);
+ CreateIvmTriggersOnBaseTablesRecurse(qry, j->rarg, matviewOid, relids, ex_lock);
+ }
+ break;
+
+ default:
+ elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node));
+ }
+}
+
+/*
+ * CreateIvmTrigger -- create IVM trigger on a base table
+ */
+static void
+CreateIvmTrigger(Oid relOid, Oid viewOid, int16 type, int16 timing, bool ex_lock)
+{
+ ObjectAddress refaddr;
+ ObjectAddress address;
+ CreateTrigStmt *ivm_trigger;
+ List *transitionRels = NIL;
+
+ Assert(timing == TRIGGER_TYPE_BEFORE || timing == TRIGGER_TYPE_AFTER);
+
+ refaddr.classId = RelationRelationId;
+ refaddr.objectId = viewOid;
+ refaddr.objectSubId = 0;
+
+ ivm_trigger = makeNode(CreateTrigStmt);
+ ivm_trigger->relation = NULL;
+ ivm_trigger->row = false;
+
+ ivm_trigger->timing = timing;
+ ivm_trigger->events = type;
+
+ switch (type)
+ {
+ case TRIGGER_TYPE_INSERT:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_ins_before" : "IVM_trigger_ins_after");
+ break;
+ case TRIGGER_TYPE_DELETE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_del_before" : "IVM_trigger_del_after");
+ break;
+ case TRIGGER_TYPE_UPDATE:
+ ivm_trigger->trigname = (timing == TRIGGER_TYPE_BEFORE ? "IVM_trigger_upd_before" : "IVM_trigger_upd_after");
+ break;
+ default:
+ elog(ERROR, "unsupported trigger type");
+ }
+
+ if (timing == TRIGGER_TYPE_AFTER)
+ {
+ if (type == TRIGGER_TYPE_INSERT || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "__ivm_newtable";
+ n->isNew = true;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+ if (type == TRIGGER_TYPE_DELETE || type == TRIGGER_TYPE_UPDATE)
+ {
+ TriggerTransition *n = makeNode(TriggerTransition);
+ n->name = "__ivm_oldtable";
+ n->isNew = false;
+ n->isTable = true;
+
+ transitionRels = lappend(transitionRels, n);
+ }
+ }
+
+ ivm_trigger->funcname =
+ (timing == TRIGGER_TYPE_BEFORE ? SystemFuncName("IVM_immediate_before") : SystemFuncName("IVM_immediate_maintenance"));
+
+ ivm_trigger->columns = NIL;
+ ivm_trigger->transitionRels = transitionRels;
+ ivm_trigger->whenClause = NULL;
+ ivm_trigger->isconstraint = false;
+ ivm_trigger->deferrable = false;
+ ivm_trigger->initdeferred = false;
+ ivm_trigger->constrrel = NULL;
+ ivm_trigger->args = list_make2(
+ makeString(DatumGetPointer(DirectFunctionCall1(oidout, ObjectIdGetDatum(viewOid)))),
+ makeString(DatumGetPointer(DirectFunctionCall1(boolout, BoolGetDatum(ex_lock))))
+ );
+
+ address = CreateTrigger(ivm_trigger, NULL, relOid, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid, NULL, true, false);
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_IMMV);
+
+ /* Make changes-so-far visible */
+ CommandCounterIncrement();
+}
+
+/*
+ * check_ivm_restriction --- look for specify nodes in the query tree
+ */
+static void
+check_ivm_restriction(Node *node)
+{
+ check_ivm_restriction_walker(node, NULL);
+}
+
+static bool
+check_ivm_restriction_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+
+ /*
+ * We currently don't support Sub-Query.
+ */
+ if (IsA(node, SubPlan) || IsA(node, SubLink))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ /* This can recurse, so check for excessive recursion */
+ check_stack_depth();
+
+ switch (nodeTag(node))
+ {
+ case T_Query:
+ {
+ Query *qry = (Query *)node;
+ ListCell *lc;
+ List *vars;
+
+ /* if contained CTE, return error */
+ if (qry->cteList != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("CTE is not supported on incrementally maintainable materialized view")));
+ if (qry->havingQual != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg(" HAVING clause is not supported on incrementally maintainable materialized view")));
+ if (qry->sortClause != NIL) /* There is a possibility that we don't need to return an error */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ORDER BY clause is not supported on incrementally maintainable materialized view")));
+ if (qry->limitOffset != NULL || qry->limitCount != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("LIMIT/OFFSET clause is not supported on incrementally maintainable materialized view")));
+ if (qry->hasDistinctOn)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("DISTINCT ON is not supported on incrementally maintainable materialized view")));
+ if (qry->hasWindowFuncs)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("window functions are not supported on incrementally maintainable materialized view")));
+ if (qry->groupingSets != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("GROUPING SETS, ROLLUP, or CUBE clauses is not supported on incrementally maintainable materialized view")));
+ if (qry->setOperations != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("UNION/INTERSECT/EXCEPT statements are not supported on incrementally maintainable materialized view")));
+ if (list_length(qry->targetList) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("empty target list is not supported on incrementally maintainable materialized view")));
+ if (qry->rowMarks != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("FOR UPDATE/SHARE clause is not supported on incrementally maintainable materialized view")));
+
+ /* system column restrictions */
+ vars = pull_vars_of_level((Node *) qry, 0);
+ foreach(lc, vars)
+ {
+ if (IsA(lfirst(lc), Var))
+ {
+ Var *var = (Var *) lfirst(lc);
+ /* if system column, return error */
+ if (var->varattno < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("system column is not supported on incrementally maintainable materialized view")));
+ }
+ }
+
+ /* restrictions for rtable */
+ foreach(lc, qry->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ if (rte->subquery)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("subquery is not supported on incrementally maintainable materialized view")));
+
+ if (rte->tablesample != NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("TABLESAMPLE clause is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitioned table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && has_superclass(rte->relid))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("partitions is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_RELATION && find_inheritance_children(rte->relid, NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("inheritance parent is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign table is not supported on incrementally maintainable materialized view")));
+
+ if (rte->relkind == RELKIND_VIEW ||
+ rte->relkind == RELKIND_MATVIEW)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VIEW or MATERIALIZED VIEW is not supported on incrementally maintainable materialized view")));
+
+ if (rte->rtekind == RTE_VALUES)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("VALUES is not supported on incrementally maintainable materialized view")));
+
+ }
+
+ query_tree_walker(qry, check_ivm_restriction_walker, NULL, QTW_IGNORE_RANGE_TABLE);
+
+ break;
+ }
+ case T_TargetEntry:
+ {
+ TargetEntry *tle = (TargetEntry *)node;
+ if (isIvmName(tle->resname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column name %s is not supported on incrementally maintainable materialized view", tle->resname)));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ break;
+ }
+ case T_JoinExpr:
+ {
+ JoinExpr *joinexpr = (JoinExpr *)node;
+
+ if (joinexpr->jointype > JOIN_INNER)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("OUTER JOIN is not supported on incrementally maintainable materialized view")));
+
+ expression_tree_walker(node, check_ivm_restriction_walker, NULL);
+ }
+ break;
+ default:
+ expression_tree_walker(node, check_ivm_restriction_walker, (void *) context);
+ break;
+ }
+ return false;
+}
+
+/*
+ * CreateIndexOnIMMV
+ *
+ * Create a unique index on incremental maintainable materialized view.
+ * If the view definition query has a GROUP BY clause, the index is created
+ * on the columns of GROUP BY expressions. Otherwise, if the view contains
+ * all primary key attritubes of its base tables in the target list, the index
+ * is created on these attritubes. In other cases, no index is created.
+ */
+void
+CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create)
+{
+ ListCell *lc;
+ IndexStmt *index;
+ ObjectAddress address;
+ List *constraintList = NIL;
+ char idxname[NAMEDATALEN];
+ List *indexoidlist = RelationGetIndexList(matviewRel);
+ ListCell *indexoidscan;
+
+ snprintf(idxname, sizeof(idxname), "%s_index", RelationGetRelationName(matviewRel));
+
+ index = makeNode(IndexStmt);
+
+ /*
+ * We consider null values not distinct to make sure that views with DISTINCT
+ * or GROUP BY don't contain multiple NULL rows when NULL is inserted to
+ * a base table concurrently.
+ */
+ index->nulls_not_distinct = true;
+
+ index->unique = true;
+ index->primary = false;
+ index->isconstraint = false;
+ index->deferrable = false;
+ index->initdeferred = false;
+ index->idxname = idxname;
+ index->relation =
+ makeRangeVar(get_namespace_name(RelationGetNamespace(matviewRel)),
+ pstrdup(RelationGetRelationName(matviewRel)),
+ -1);
+ index->accessMethod = DEFAULT_INDEX_TYPE;
+ index->options = NIL;
+ index->tableSpace = get_tablespace_name(matviewRel->rd_rel->reltablespace);
+ index->whereClause = NULL;
+ index->indexParams = NIL;
+ index->indexIncludingParams = NIL;
+ index->excludeOpNames = NIL;
+ index->idxcomment = NULL;
+ index->indexOid = InvalidOid;
+ index->oldNode = InvalidOid;
+ index->oldCreateSubid = InvalidSubTransactionId;
+ index->oldFirstRelfilenodeSubid = InvalidSubTransactionId;
+ index->transformed = true;
+ index->concurrent = false;
+ index->if_not_exists = false;
+
+ if (query->distinctClause)
+ {
+ /* create unique constraint on all columns */
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ else
+ {
+ Bitmapset *key_attnos;
+
+ /* create index on the base tables' primary key columns */
+ key_attnos = get_primary_key_attnos_from_query(query, &constraintList, is_create);
+ if (key_attnos)
+ {
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, tle->resno - 1);
+
+ if (bms_is_member(tle->resno - FirstLowInvalidHeapAttributeNumber, key_attnos))
+ {
+ IndexElem *iparam;
+
+ iparam = makeNode(IndexElem);
+ iparam->name = pstrdup(NameStr(attr->attname));
+ iparam->expr = NULL;
+ iparam->indexcolname = NULL;
+ iparam->collation = NIL;
+ iparam->opclass = NIL;
+ iparam->opclassopts = NIL;
+ iparam->ordering = SORTBY_DEFAULT;
+ iparam->nulls_ordering = SORTBY_NULLS_DEFAULT;
+ index->indexParams = lappend(index->indexParams, iparam);
+ }
+ }
+ }
+ else
+ {
+ /* create no index, just notice that an appropriate index is necessary for efficient IVM */
+ ereport(NOTICE,
+ (errmsg("could not create an index on materialized view \"%s\" automatically",
+ RelationGetRelationName(matviewRel)),
+ errdetail("This target list does not have all the primary key columns, "
+ "or this view does not contain DISTINCT clause."),
+ errhint("Create an index on the materialized view for efficient incremental maintenance.")));
+ return;
+ }
+ }
+
+ /* If we have a compatible index, we don't need to create another. */
+ foreach(indexoidscan, indexoidlist)
+ {
+ Oid indexoid = lfirst_oid(indexoidscan);
+ Relation indexRel;
+ bool hasCompatibleIndex = false;
+
+ indexRel = index_open(indexoid, AccessShareLock);
+
+ if (CheckIndexCompatible(indexRel->rd_id,
+ index->accessMethod,
+ index->indexParams,
+ index->excludeOpNames))
+ hasCompatibleIndex = true;
+
+ index_close(indexRel, AccessShareLock);
+
+ if (hasCompatibleIndex)
+ return;
+ }
+
+ address = DefineIndex(RelationGetRelid(matviewRel),
+ index,
+ InvalidOid,
+ InvalidOid,
+ InvalidOid,
+ false, true, false, false, true);
+
+ ereport(NOTICE,
+ (errmsg("created index \"%s\" on materialized view \"%s\"",
+ idxname, RelationGetRelationName(matviewRel))));
+
+ /*
+ * Make dependencies so that the index is dropped if any base tables's
+ * primary key is dropped.
+ */
+ foreach(lc, constraintList)
+ {
+ Oid constraintOid = lfirst_oid(lc);
+ ObjectAddress refaddr;
+
+ refaddr.classId = ConstraintRelationId;
+ refaddr.objectId = constraintOid;
+ refaddr.objectSubId = 0;
+
+ recordDependencyOn(&address, &refaddr, DEPENDENCY_NORMAL);
+ }
+}
+
+
+/*
+ * get_primary_key_attnos_from_query
+ *
+ * Identify the columns in base tables' primary keys in the target list.
+ *
+ * Returns a Bitmapset of the column attnos of the primary key's columns of
+ * tables that used in the query. The attnos are offset by
+ * FirstLowInvalidHeapAttributeNumber as same as get_primary_key_attnos.
+ *
+ * If any table has no primary key or any primary key's columns is not in
+ * the target list, return NULL. We also return NULL if any pkey constraint
+ * is deferrable.
+ *
+ * constraintList is set to a list of the OIDs of the pkey constraints.
+ */
+static Bitmapset *
+get_primary_key_attnos_from_query(Query *query, List **constraintList, bool is_create)
+{
+ List *key_attnos_list = NIL;
+ ListCell *lc;
+ int i;
+ Bitmapset *keys = NULL;
+ Relids rels_in_from;
+ PlannerInfo root;
+
+
+ /*
+ * Collect primary key attributes from all tables used in query. The key attributes
+ * sets for each table are stored in key_attnos_list in order by RTE index.
+ */
+ i = 1;
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+ Bitmapset *key_attnos;
+ bool has_pkey = true;
+ Index first_rtindex = is_create ? 1 : PRS2_NEW_VARNO + 1;
+
+ /* skip NEW/OLD entries */
+ if (i >= first_rtindex)
+ {
+ /* for tables, call get_primary_key_attnos */
+ if (r->rtekind == RTE_RELATION)
+ {
+ Oid constraintOid;
+ key_attnos = get_primary_key_attnos(r->relid, false, &constraintOid);
+ *constraintList = lappend_oid(*constraintList, constraintOid);
+ has_pkey = (key_attnos != NULL);
+ }
+ /* for other RTEs, store NULL into key_attnos_list */
+ else
+ key_attnos = NULL;
+ }
+ else
+ key_attnos = NULL;
+
+ /*
+ * If any table or subquery has no primary key or its pkey constraint is deferrable,
+ * we cannot get key attributes for this query, so return NULL.
+ */
+ if (!has_pkey)
+ return NULL;
+
+ key_attnos_list = lappend(key_attnos_list, key_attnos);
+ i++;
+ }
+
+ /* Collect key attributes appearing in the target list */
+ i = 1;
+ foreach(lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) flatten_join_alias_vars(query, lfirst(lc));
+
+ if (IsA(tle->expr, Var))
+ {
+ Var *var = (Var*) tle->expr;
+ Bitmapset *attnos = list_nth(key_attnos_list, var->varno - 1);
+
+ /* check if this attribute is from a base table's primary key */
+ if (bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber, attnos))
+ {
+ /*
+ * Remove found key attributes from key_attnos_list, and add this
+ * to the result list.
+ */
+ bms_del_member(attnos, var->varattno - FirstLowInvalidHeapAttributeNumber);
+ keys = bms_add_member(keys, i - FirstLowInvalidHeapAttributeNumber);
+ }
+ }
+ i++;
+ }
+
+ /* Collect relations appearing in the FROM clause */
+ rels_in_from = pull_varnos_of_level(&root, (Node *)query->jointree, 0);
+
+ /*
+ * Check if all key attributes of relations in FROM are appearing in the target
+ * list. If an attribute remains in key_attnos_list in spite of the table is used
+ * in FROM clause, the target is missing this key attribute, so we return NULL.
+ */
+ i = 1;
+ foreach(lc, key_attnos_list)
+ {
+ Bitmapset *bms = (Bitmapset *)lfirst(lc);
+ if (!bms_is_empty(bms) && bms_is_member(i, rels_in_from))
+ return NULL;
+ i++;
+ }
+
+ return keys;
+}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index cd30f15eba..a94b8ffd7d 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -36,6 +36,7 @@
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/matview.h"
#include "commands/progress.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -1055,6 +1056,45 @@ DefineIndex(Oid relationId,
safe_index = indexInfo->ii_Expressions == NIL &&
indexInfo->ii_Predicate == NIL;
+ /*
+ * We disallow unique indexes on IVM columns of IMMVs.
+ */
+ if (RelationIsIVM(rel) && stmt->unique)
+ {
+ for (i = 0; i < indexInfo->ii_NumIndexKeyAttrs; i++)
+ {
+ AttrNumber attno = indexInfo->ii_IndexAttrNumbers[i];
+ if (attno > 0)
+ {
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+ }
+
+ if (indexInfo->ii_Expressions)
+ {
+ Bitmapset *indexattrs = NULL;
+ int varno = -1;
+
+ pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs);
+
+ while ((varno = bms_next_member(indexattrs, varno)) >= 0)
+ {
+ int attno = varno + FirstLowInvalidHeapAttributeNumber;
+ char *name = NameStr(TupleDescAttr(rel->rd_att, attno - 1)->attname);
+ if (name && isIvmName(name))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("unique index creation on IVM columns is not supported")));
+ }
+
+ }
+ }
+
+
/*
* Report index creation if appropriate (delay this till after most of the
* error checks)
diff --git a/src/backend/commands/matview.c b/src/backend/commands/matview.c
index 05e7b60059..29cf18360e 100644
--- a/src/backend/commands/matview.c
+++ b/src/backend/commands/matview.c
@@ -25,26 +25,47 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_depend.h"
+#include "catalog/pg_type.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "commands/cluster.h"
+#include "commands/defrem.h"
#include "commands/matview.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/createas.h"
#include "executor/executor.h"
#include "executor/spi.h"
+#include "executor/tstoreReceiver.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
+#include "parser/analyze.h"
+#include "parser/parser.h"
+#include "parser/parsetree.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_collate.h"
+#include "parser/parse_func.h"
#include "parser/parse_relation.h"
+#include "parser/parse_type.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
+#include "rewrite/rewriteManip.h"
+#include "rewrite/rowsecurity.h"
#include "storage/lmgr.h"
#include "storage/smgr.h"
#include "tcop/tcopprot.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
#include "utils/snapmgr.h"
#include "utils/syscache.h"
+#include "utils/typcache.h"
typedef struct
@@ -58,6 +79,50 @@ typedef struct
BulkInsertState bistate; /* bulk insert state */
} DR_transientrel;
+#define MV_INIT_QUERYHASHSIZE 16
+
+/*
+ * MV_TriggerHashEntry
+ *
+ * Hash entry for base tables on which IVM trigger is invoked
+ */
+typedef struct MV_TriggerHashEntry
+{
+ Oid matview_id; /* OID of the materialized view */
+ int before_trig_count; /* count of before triggers invoked */
+ int after_trig_count; /* count of after triggers invoked */
+
+ TransactionId xid; /* Transaction id before the first table is modified*/
+ CommandId cid; /* Command id before the first table is modified */
+
+ List *tables; /* List of MV_TriggerTable */
+ bool has_old; /* tuples are deleted from any table? */
+ bool has_new; /* tuples are inserted into any table? */
+} MV_TriggerHashEntry;
+
+/*
+ * MV_TriggerTable
+ *
+ * IVM related data for tables on which the trigger is invoked.
+ */
+typedef struct MV_TriggerTable
+{
+ Oid table_id; /* OID of the modified table */
+ List *old_tuplestores; /* tuplestores for deleted tuples */
+ List *new_tuplestores; /* tuplestores for inserted tuples */
+ List *old_rtes; /* RTEs of ENRs for old_tuplestores*/
+ List *new_rtes; /* RTEs of ENRs for new_tuplestores */
+
+ List *rte_indexes; /* List of RTE index of the modified table */
+ RangeTblEntry *original_rte; /* the original RTE saved before rewriting query */
+} MV_TriggerTable;
+
+static HTAB *mv_trigger_info = NULL;
+
+/* ENR name for materialized view delta */
+#define NEW_DELTA_ENRNAME "new_delta"
+#define OLD_DELTA_ENRNAME "old_delta"
+
static int matview_maintenance_depth = 0;
static void transientrel_startup(DestReceiver *self, int operation, TupleDesc typeinfo);
@@ -65,7 +130,9 @@ static bool transientrel_receive(TupleTableSlot *slot, DestReceiver *self);
static void transientrel_shutdown(DestReceiver *self);
static void transientrel_destroy(DestReceiver *self);
static uint64 refresh_matview_datafill(DestReceiver *dest, Query *query,
- const char *queryString);
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
+ const char *queryString);
static char *make_temptable_name_n(char *tempname, int n);
static void refresh_by_match_merge(Oid matviewOid, Oid tempOid, Oid relowner,
int save_sec_context);
@@ -73,6 +140,45 @@ static void refresh_by_heap_swap(Oid matviewOid, Oid OIDNewHeap, char relpersist
static bool is_usable_unique_index(Relation indexRel);
static void OpenMatViewIncrementalMaintenance(void);
static void CloseMatViewIncrementalMaintenance(void);
+static Query *get_matview_query(Relation matviewRel);
+
+static Query *rewrite_query_for_preupdate_state(Query *query, List *tables,
+ TransactionId xid, CommandId cid,
+ ParseState *pstate);
+static void register_delta_ENRs(ParseState *pstate, Query *query, List *tables);
+static char *make_delta_enr_name(const char *prefix, Oid relid, int count);
+static RangeTblEntry *get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ TransactionId xid, CommandId cid,
+ QueryEnvironment *queryEnv);
+static RangeTblEntry *union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_distinct(Query *query, ParseState *pstate);
+
+static void calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv);
+static Query *rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index);
+
+static void apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query, bool use_count, char *count_colname);
+static void apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys);
+static void apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname);
+static void apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list);
+static void apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname);
+static char *get_matching_condition_string(List *keys);
+static void generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop);
+
+static void mv_InitHashTables(void);
+static void clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry);
+
+static List *get_securityQuals(Oid relId, int rt_index, Query *query);
/*
* SetMatViewPopulatedState
@@ -114,6 +220,46 @@ SetMatViewPopulatedState(Relation relation, bool newstate)
CommandCounterIncrement();
}
+/*
+ * SetMatViewIVMState
+ * Mark a materialized view as IVM, or not.
+ *
+ * NOTE: caller must be holding an appropriate lock on the relation.
+ */
+void
+SetMatViewIVMState(Relation relation, bool newstate)
+{
+ Relation pgrel;
+ HeapTuple tuple;
+
+ Assert(relation->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Update relation's pg_class entry. Crucial side-effect: other backends
+ * (and this one too!) are sent SI message to make them rebuild relcache
+ * entries.
+ */
+ pgrel = table_open(RelationRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopy1(RELOID,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u",
+ RelationGetRelid(relation));
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relisivm = newstate;
+
+ CatalogTupleUpdate(pgrel, &tuple->t_self, tuple);
+
+ heap_freetuple(tuple);
+ table_close(pgrel, RowExclusiveLock);
+
+ /*
+ * Advance command counter to make the updated pg_class row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+}
+
/*
* ExecRefreshMatView -- execute a REFRESH MATERIALIZED VIEW command
*
@@ -140,9 +286,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
{
Oid matviewOid;
Relation matviewRel;
- RewriteRule *rule;
- List *actions;
Query *dataQuery;
+ Query *viewQuery;
Oid tableSpace;
Oid relowner;
Oid OIDNewHeap;
@@ -155,6 +300,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
int save_sec_context;
int save_nestlevel;
ObjectAddress address;
+ bool oldPopulated;
/* Determine strength of lock needed. */
concurrent = stmt->concurrent;
@@ -167,6 +313,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
lockmode, 0,
RangeVarCallbackOwnsTable, NULL);
matviewRel = table_open(matviewOid, NoLock);
+ oldPopulated = RelationIsPopulated(matviewRel);
/* Make sure it is a materialized view. */
if (matviewRel->rd_rel->relkind != RELKIND_MATVIEW)
@@ -188,32 +335,14 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errmsg("%s and %s options cannot be used together",
"CONCURRENTLY", "WITH NO DATA")));
- /*
- * Check that everything is correct for a refresh. Problems at this point
- * are internal errors, so elog is sufficient.
- */
- if (matviewRel->rd_rel->relhasrules == false ||
- matviewRel->rd_rules->numLocks < 1)
- elog(ERROR,
- "materialized view \"%s\" is missing rewrite information",
- RelationGetRelationName(matviewRel));
-
- if (matviewRel->rd_rules->numLocks > 1)
- elog(ERROR,
- "materialized view \"%s\" has too many rules",
- RelationGetRelationName(matviewRel));
- rule = matviewRel->rd_rules->rules[0];
- if (rule->event != CMD_SELECT || !(rule->isInstead))
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
- RelationGetRelationName(matviewRel));
+ viewQuery = get_matview_query(matviewRel);
- actions = rule->actions;
- if (list_length(actions) != 1)
- elog(ERROR,
- "the rule for materialized view \"%s\" is not a single action",
- RelationGetRelationName(matviewRel));
+ /* For IMMV, we need to rewrite matview query */
+ if (!stmt->skipData && RelationIsIVM(matviewRel))
+ dataQuery = rewriteQueryForIMMV(viewQuery,NIL);
+ else
+ dataQuery = viewQuery;
/*
* Check that there is a unique index with no WHERE clause on one or more
@@ -248,12 +377,6 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
errhint("Create a unique index with no WHERE clause on one or more columns of the materialized view.")));
}
- /*
- * The stored query was rewritten at the time of the MV definition, but
- * has not been scribbled on by the planner.
- */
- dataQuery = linitial_node(Query, actions);
-
/*
* Check for active uses of the relation in the current transaction, such
* as open scans.
@@ -294,6 +417,52 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
relpersistence = matviewRel->rd_rel->relpersistence;
}
+ /* delete immv triggers */
+ if (RelationIsIVM(matviewRel) && stmt->skipData )
+ {
+ /* use deleted trigger */
+ Relation depRel;
+ ScanKeyData key;
+ SysScanDesc scan;
+ HeapTuple tup;
+ ObjectAddresses *immv_triggers;
+
+ immv_triggers = new_object_addresses();
+
+ /*
+ * We save some cycles by opening pg_depend just once and passing the
+ * Relation pointer down to all the recursive deletion steps.
+ */
+ depRel = table_open(DependRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&key,
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(matviewOid));
+
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ NULL, 1, &key);
+ while ((tup = systable_getnext(scan)) != NULL)
+ {
+ ObjectAddress obj;
+ Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(tup);
+
+ if (foundDep->deptype == DEPENDENCY_IMMV)
+ {
+ obj.classId = foundDep->classid;
+ obj.objectId = foundDep->objid;
+ obj.objectSubId = foundDep->refobjsubid;
+ add_exact_object_address(&obj, immv_triggers);
+ }
+ }
+ systable_endscan(scan);
+
+ performMultipleDeletions(immv_triggers, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);
+
+ table_close(depRel, RowExclusiveLock);
+ free_object_addresses(immv_triggers);
+ }
+
/*
* Create the transient table that will receive the regenerated data. Lock
* it against access by any other process until commit (by which time it
@@ -313,7 +482,7 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
/* Generate the data, if wanted. */
if (!stmt->skipData)
- processed = refresh_matview_datafill(dest, dataQuery, queryString);
+ processed = refresh_matview_datafill(dest, dataQuery, NULL, NULL, queryString);
/* Make the matview match the newly generated data. */
if (concurrent)
@@ -348,6 +517,12 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
pgstat_count_heap_insert(matviewRel, processed);
}
+ if (!stmt->skipData && RelationIsIVM(matviewRel) && !oldPopulated)
+ {
+ CreateIndexOnIMMV(viewQuery, matviewRel, false);
+ CreateIvmTriggersOnBaseTables(viewQuery, matviewOid, false);
+ }
+
table_close(matviewRel, NoLock);
/* Roll back any GUC changes */
@@ -382,6 +557,8 @@ ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
*/
static uint64
refresh_matview_datafill(DestReceiver *dest, Query *query,
+ QueryEnvironment *queryEnv,
+ TupleDesc *resultTupleDesc,
const char *queryString)
{
List *rewritten;
@@ -418,7 +595,7 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
/* Create a QueryDesc, redirecting output to our tuple receiver */
queryDesc = CreateQueryDesc(plan, queryString,
GetActiveSnapshot(), InvalidSnapshot,
- dest, NULL, NULL, 0);
+ dest, NULL, queryEnv ? queryEnv: NULL, 0);
/* call ExecutorStart to prepare the plan for execution */
ExecutorStart(queryDesc, 0);
@@ -428,6 +605,9 @@ refresh_matview_datafill(DestReceiver *dest, Query *query,
processed = queryDesc->estate->es_processed;
+ if (resultTupleDesc)
+ *resultTupleDesc = CreateTupleDescCopy(queryDesc->tupDesc);
+
/* and clean up */
ExecutorFinish(queryDesc);
ExecutorEnd(queryDesc);
@@ -942,3 +1122,1308 @@ CloseMatViewIncrementalMaintenance(void)
matview_maintenance_depth--;
Assert(matview_maintenance_depth >= 0);
}
+
+/*
+ * get_matview_query - get the Query from a matview's _RETURN rule.
+ */
+static Query *
+get_matview_query(Relation matviewRel)
+{
+ RewriteRule *rule;
+ List * actions;
+
+ /*
+ * Check that everything is correct for a refresh. Problems at this point
+ * are internal errors, so elog is sufficient.
+ */
+ if (matviewRel->rd_rel->relhasrules == false ||
+ matviewRel->rd_rules->numLocks < 1)
+ elog(ERROR,
+ "materialized view \"%s\" is missing rewrite information",
+ RelationGetRelationName(matviewRel));
+
+ if (matviewRel->rd_rules->numLocks > 1)
+ elog(ERROR,
+ "materialized view \"%s\" has too many rules",
+ RelationGetRelationName(matviewRel));
+
+ rule = matviewRel->rd_rules->rules[0];
+ if (rule->event != CMD_SELECT || !(rule->isInstead))
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a SELECT INSTEAD OF rule",
+ RelationGetRelationName(matviewRel));
+
+ actions = rule->actions;
+ if (list_length(actions) != 1)
+ elog(ERROR,
+ "the rule for materialized view \"%s\" is not a single action",
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * The stored query was rewritten at the time of the MV definition, but
+ * has not been scribbled on by the planner.
+ */
+ return linitial_node(Query, actions);
+}
+
+
+/* ----------------------------------------------------
+ * Incremental View Maintenance routines
+ * ---------------------------------------------------
+ */
+
+/*
+ * IVM_immediate_before
+ *
+ * IVM trigger function invoked before base table is modified. If this is
+ * invoked firstly in the same statement, we save the transaction id and the
+ * command id at that time.
+ */
+Datum
+IVM_immediate_before(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ char *ex_lock_text = trigdata->tg_trigger->tgargs[1];
+ Oid matviewOid;
+ MV_TriggerHashEntry *entry;
+ bool found;
+ bool ex_lock;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+ ex_lock = DatumGetBool(DirectFunctionCall1(boolin, CStringGetDatum(ex_lock_text)));
+
+ /* If the view has more than one tables, we have to use an exclusive lock. */
+ if (ex_lock)
+ {
+ /*
+ * Wait for concurrent transactions which update this materialized view at
+ * READ COMMITED. This is needed to see changes committed in other
+ * transactions. No wait and raise an error at REPEATABLE READ or
+ * SERIALIZABLE to prevent update anomalies of matviews.
+ * XXX: dead-lock is possible here.
+ */
+ if (!IsolationUsesXactSnapshot())
+ LockRelationOid(matviewOid, ExclusiveLock);
+ else if (!ConditionalLockRelationOid(matviewOid, ExclusiveLock))
+ {
+ /* try to throw error by name; relation could be deleted... */
+ char *relname = get_rel_name(matviewOid);
+
+ if (!relname)
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view during incremental maintenance")));
+
+ ereport(ERROR,
+ (errcode(ERRCODE_LOCK_NOT_AVAILABLE),
+ errmsg("could not obtain lock on materialized view \"%s\" during incremental maintenance",
+ relname)));
+ }
+ }
+ else
+ LockRelationOid(matviewOid, RowExclusiveLock);
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_ENTER, &found);
+
+ /* On the first BEFORE to update the view, initialize trigger data */
+ if (!found)
+ {
+ Snapshot snapshot = GetActiveSnapshot();
+
+ entry->matview_id = matviewOid;
+ entry->before_trig_count = 0;
+ entry->after_trig_count = 0;
+ entry->xid = GetCurrentTransactionId();
+ entry->cid = snapshot->curcid;
+ entry->tables = NIL;
+ entry->has_old = false;
+ entry->has_new = false;
+ }
+
+ entry->before_trig_count++;
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * IVM_immediate_before
+ *
+ * IVM trigger function invoked after base table is modified.
+ * For each table, tuplestores of transition tables are collected.
+ * and after the last modification
+ */
+Datum
+IVM_immediate_maintenance(PG_FUNCTION_ARGS)
+{
+ TriggerData *trigdata = (TriggerData *) fcinfo->context;
+ Relation rel;
+ Oid relid;
+ Oid matviewOid;
+ Query *query;
+ Query *rewritten = NULL;
+ char *matviewOid_text = trigdata->tg_trigger->tgargs[0];
+ Relation matviewRel;
+ int old_depth = matview_maintenance_depth;
+
+ Oid relowner;
+ Tuplestorestate *old_tuplestore = NULL;
+ Tuplestorestate *new_tuplestore = NULL;
+ DestReceiver *dest_new = NULL, *dest_old = NULL;
+ Oid save_userid;
+ int save_sec_context;
+ int save_nestlevel;
+
+ MV_TriggerHashEntry *entry;
+ MV_TriggerTable *table;
+ bool found;
+
+ ParseState *pstate;
+ QueryEnvironment *queryEnv = create_queryEnv();
+ MemoryContext oldcxt;
+ ListCell *lc;
+ int i;
+
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ rel = trigdata->tg_relation;
+ relid = rel->rd_id;
+
+ matviewOid = DatumGetObjectId(DirectFunctionCall1(oidin, CStringGetDatum(matviewOid_text)));
+
+ /*
+ * On the first call initialize the hashtable
+ */
+ if (!mv_trigger_info)
+ mv_InitHashTables();
+
+ /* get the entry for this materialized view */
+ entry = (MV_TriggerHashEntry *) hash_search(mv_trigger_info,
+ (void *) &matviewOid,
+ HASH_FIND, &found);
+ Assert (found && entry != NULL);
+ entry->after_trig_count++;
+
+ /* search the entry for the modified table and create new entry if not found */
+ found = false;
+ foreach(lc, entry->tables)
+ {
+ table = (MV_TriggerTable *) lfirst(lc);
+ if (table->table_id == relid)
+ {
+ found = true;
+ break;
+ }
+ }
+ if (!found)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ table = (MV_TriggerTable *) palloc0(sizeof(MV_TriggerTable));
+ table->table_id = relid;
+ table->old_tuplestores = NIL;
+ table->new_tuplestores = NIL;
+ table->old_rtes = NIL;
+ table->new_rtes = NIL;
+ table->rte_indexes = NIL;
+ entry->tables = lappend(entry->tables, table);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* Save the transition tables and make a request to not free immediately */
+ if (trigdata->tg_oldtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->old_tuplestores = lappend(table->old_tuplestores, trigdata->tg_oldtable);
+ entry->has_old = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (trigdata->tg_newtable)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+ table->new_tuplestores = lappend(table->new_tuplestores, trigdata->tg_newtable);
+ entry->has_new = true;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new || entry->has_old)
+ {
+ CmdType cmd;
+
+ if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
+ cmd = CMD_INSERT;
+ else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
+ cmd = CMD_DELETE;
+ else if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
+ cmd = CMD_UPDATE;
+ else
+ elog(ERROR,"unsupported trigger type");
+
+ /* Prolong lifespan of transition tables to the end of the last AFTER trigger */
+ SetTransitionTablePreserved(relid, cmd);
+ }
+
+
+ /* If this is not the last AFTER trigger call, immediately exit. */
+ Assert (entry->before_trig_count >= entry->after_trig_count);
+ if (entry->before_trig_count != entry->after_trig_count)
+ return PointerGetDatum(NULL);
+
+ /*
+ * If this is the last AFTER trigger call, continue and update the view.
+ */
+
+ /*
+ * Advance command counter to make the updated base table row locally
+ * visible.
+ */
+ CommandCounterIncrement();
+
+ matviewRel = table_open(matviewOid, NoLock);
+
+ /* get view query*/
+ query = get_matview_query(matviewRel);
+
+ /* Make sure it is a materialized view. */
+ Assert(matviewRel->rd_rel->relkind == RELKIND_MATVIEW);
+
+ /*
+ * Get and push the latast snapshot to see any changes which is committed
+ * during waiting in other transactions at READ COMMITTED level.
+ */
+ PushActiveSnapshot(GetTransactionSnapshot());
+
+ /*
+ * Check for active uses of the relation in the current transaction, such
+ * as open scans.
+ *
+ * NB: We count on this to protect us against problems with refreshing the
+ * data using TABLE_INSERT_FROZEN.
+ */
+ CheckTableNotInUse(matviewRel, "refresh a materialized view incrementally");
+
+ /*
+ * Switch to the owner's userid, so that any functions are run as that
+ * user. Also arrange to make GUC variable changes local to this command.
+ * We will switch modes when we are about to execute user code.
+ */
+ relowner = matviewRel->rd_rel->relowner;
+ GetUserIdAndSecContext(&save_userid, &save_sec_context);
+ SetUserIdAndSecContext(relowner,
+ save_sec_context | SECURITY_RESTRICTED_OPERATION);
+ save_nestlevel = NewGUCNestLevel();
+
+ /*
+ * rewrite query for calculating deltas
+ */
+
+ rewritten = copyObject(query);
+
+ /* Replace resnames in a target list with materialized view's attnames */
+ i = 0;
+ foreach (lc, rewritten->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ tle->resname = pstrdup(resname);
+ i++;
+ }
+
+ /* Set all tables in the query to pre-update state */
+ rewritten = rewrite_query_for_preupdate_state(rewritten, entry->tables,
+ entry->xid, entry->cid,
+ pstate);
+ /* Rewrite for DISTINCT clause */
+ rewritten = rewrite_query_for_distinct(rewritten, pstate);
+
+ /* Create tuplestores to store view deltas */
+ if (entry->has_old)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ old_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_old = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_old,
+ old_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+
+ MemoryContextSwitchTo(oldcxt);
+ }
+ if (entry->has_new)
+ {
+ oldcxt = MemoryContextSwitchTo(TopTransactionContext);
+
+ new_tuplestore = tuplestore_begin_heap(false, false, work_mem);
+ dest_new = CreateDestReceiver(DestTuplestore);
+ SetTuplestoreDestReceiverParams(dest_new,
+ new_tuplestore,
+ TopTransactionContext,
+ false,
+ NULL,
+ NULL);
+ MemoryContextSwitchTo(oldcxt);
+ }
+
+ /* for all modified tables */
+ foreach(lc, entry->tables)
+ {
+ ListCell *lc2;
+
+ table = (MV_TriggerTable *) lfirst(lc);
+
+ /* loop for self-join */
+ foreach(lc2, table->rte_indexes)
+ {
+ int rte_index = lfirst_int(lc2);
+ TupleDesc tupdesc_old;
+ TupleDesc tupdesc_new;
+ bool use_count = false;
+ char *count_colname = NULL;
+
+ count_colname = pstrdup("__ivm_count__");
+
+ if (query->distinctClause)
+ use_count = true;
+
+ /* calculate delta tables */
+ calc_delta(table, rte_index, rewritten, dest_old, dest_new,
+ &tupdesc_old, &tupdesc_new, queryEnv);
+
+ /* Set the table in the query to post-update state */
+ rewritten = rewrite_query_for_postupdate_state(rewritten, table, rte_index);
+
+ PG_TRY();
+ {
+ /* apply the delta tables to the materialized view */
+ apply_delta(matviewOid, old_tuplestore, new_tuplestore,
+ tupdesc_old, tupdesc_new, query, use_count,
+ count_colname);
+ }
+ PG_CATCH();
+ {
+ matview_maintenance_depth = old_depth;
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ /* clear view delta tuplestores */
+ if (old_tuplestore)
+ tuplestore_clear(old_tuplestore);
+ if (new_tuplestore)
+ tuplestore_clear(new_tuplestore);
+ }
+ }
+
+ /* Clean up hash entry and delete tuplestores */
+ clean_up_IVM_hash_entry(entry);
+ if (old_tuplestore)
+ {
+ dest_old->rDestroy(dest_old);
+ tuplestore_end(old_tuplestore);
+ }
+ if (new_tuplestore)
+ {
+ dest_new->rDestroy(dest_new);
+ tuplestore_end(new_tuplestore);
+ }
+
+ /* Pop the original snapshot. */
+ PopActiveSnapshot();
+
+ table_close(matviewRel, NoLock);
+
+ /* Roll back any GUC changes */
+ AtEOXact_GUC(false, save_nestlevel);
+
+ /* Restore userid and security context */
+ SetUserIdAndSecContext(save_userid, save_sec_context);
+
+ return PointerGetDatum(NULL);
+}
+
+/*
+ * rewrite_query_for_preupdate_state
+ *
+ * Rewrite the query so that base tables' RTEs will represent "pre-update"
+ * state of tables. This is necessary to calculate view delta after multiple
+ * tables are modified. xid and cid are the transaction id and command id
+ * before the first table was modified.
+ */
+static Query*
+rewrite_query_for_preupdate_state(Query *query, List *tables,
+ TransactionId xid, CommandId cid,
+ ParseState *pstate)
+{
+ ListCell *lc;
+ int num_rte = list_length(query->rtable);
+ int i;
+
+
+ /* register delta ENRs */
+ register_delta_ENRs(pstate, query, tables);
+
+ /* XXX: Is necessary? Is this right timing? */
+ AcquireRewriteLocks(query, true, false);
+
+ i = 1;
+ foreach(lc, query->rtable)
+ {
+ RangeTblEntry *r = (RangeTblEntry*) lfirst(lc);
+
+ ListCell *lc2;
+ foreach(lc2, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc2);
+ /*
+ * if the modified table is found then replace the original RTE with
+ * "pre-state" RTE and append its index to the list.
+ */
+ if (r->relid == table->table_id)
+ {
+ lfirst(lc) = get_prestate_rte(r, table, xid, cid, pstate->p_queryEnv);
+ table->rte_indexes = lappend_int(table->rte_indexes, i);
+ break;
+ }
+ }
+
+ /* finish the loop if we processed all RTE included in the original query */
+ if (i++ >= num_rte)
+ break;
+ }
+
+ return query;
+}
+
+/*
+ * register_delta_ENRs
+ *
+ * For all modified tables, make ENRs for their transition tables
+ * and register them to the queryEnv. ENR's RTEs are also appended
+ * into the list in query tree.
+ */
+static void
+register_delta_ENRs(ParseState *pstate, Query *query, List *tables)
+{
+ QueryEnvironment *queryEnv = pstate->p_queryEnv;
+ ListCell *lc;
+ RangeTblEntry *rte;
+
+ foreach(lc, tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+ ListCell *lc2;
+ int count;
+
+ count = 0;
+ foreach(lc2, table->old_tuplestores)
+ {
+ Tuplestorestate *oldtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("old", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(oldtable);
+ enr->reldata = oldtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+ /* if base table has RLS, set security condition to enr */
+ rte->securityQuals = get_securityQuals(table->table_id, list_length(query->rtable) + 1, query);
+
+ query->rtable = lappend(query->rtable, rte);
+ table->old_rtes = lappend(table->old_rtes, rte);
+
+ count++;
+ }
+
+ count = 0;
+ foreach(lc2, table->new_tuplestores)
+ {
+ Tuplestorestate *newtable = (Tuplestorestate *) lfirst(lc2);
+ EphemeralNamedRelation enr =
+ palloc(sizeof(EphemeralNamedRelationData));
+ ParseNamespaceItem *nsitem;
+
+ enr->md.name = make_delta_enr_name("new", table->table_id, count);
+ enr->md.reliddesc = table->table_id;
+ enr->md.tupdesc = NULL;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(newtable);
+ enr->reldata = newtable;
+ register_ENR(queryEnv, enr);
+
+ nsitem = addRangeTableEntryForENR(pstate, makeRangeVar(NULL, enr->md.name, -1), true);
+ rte = nsitem->p_rte;
+ /* if base table has RLS, set security condition to enr*/
+ rte->securityQuals = get_securityQuals(table->table_id, list_length(query->rtable) + 1, query);
+
+ query->rtable = lappend(query->rtable, rte);
+ table->new_rtes = lappend(table->new_rtes, rte);
+
+ count++;
+ }
+ }
+}
+
+/*
+ * get_prestate_rte
+ *
+ * Rewrite RTE of the modified table to a subquery which represents
+ * "pre-state" table. The original RTE is saved in table->rte_original.
+ */
+static RangeTblEntry*
+get_prestate_rte(RangeTblEntry *rte, MV_TriggerTable *table,
+ TransactionId xid, CommandId cid,
+ QueryEnvironment *queryEnv)
+{
+ StringInfoData str;
+ RawStmt *raw;
+ Query *sub;
+ Relation rel;
+ ParseState *pstate;
+ char *relname;
+ int i;
+
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ /*
+ * We can use NoLock here since AcquireRewriteLocks should
+ * have locked the rel already.
+ */
+ rel = table_open(table->table_id, NoLock);
+ relname = quote_qualified_identifier(
+ get_namespace_name(RelationGetNamespace(rel)),
+ RelationGetRelationName(rel));
+ table_close(rel, NoLock);
+
+ initStringInfo(&str);
+ appendStringInfo(&str,
+ "SELECT t.* FROM %s t"
+ " WHERE (age(t.xmin) - age(%u::text::xid) > 0) OR"
+ " (t.xmin = %u AND t.cmin::text::int < %u)",
+ relname, xid, xid, cid);
+
+ for (i = 0; i < list_length(table->old_tuplestores); i++)
+ {
+ appendStringInfo(&str, " UNION ALL ");
+ appendStringInfo(&str," SELECT * FROM %s",
+ make_delta_enr_name("old", table->table_id, i));
+ }
+
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ sub = transformStmt(pstate, raw->stmt);
+
+ /* If this query has setOperations, RTEs in rtables has a subquery which contains ENR */
+ if (sub->setOperations != NULL)
+ {
+ ListCell *lc;
+
+ /* add securityQuals for tuplestores */
+ foreach (lc, sub->rtable)
+ {
+ RangeTblEntry *rte;
+ RangeTblEntry *sub_rte;
+
+ rte = (RangeTblEntry *)lfirst(lc);
+ Assert(rte->subquery != NULL);
+
+ sub_rte = (RangeTblEntry *)linitial(rte->subquery->rtable);
+ if (sub_rte->rtekind == RTE_NAMEDTUPLESTORE)
+ /* rt_index is always 1, bacause subquery has enr_rte only */
+ sub_rte->securityQuals = get_securityQuals(sub_rte->relid, 1, sub);
+ }
+ }
+
+ /* save the original RTE */
+ table->original_rte = copyObject(rte);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+ rte->security_barrier = false;
+ /* Clear fields that should not be set in a subquery RTE */
+ rte->relid = InvalidOid;
+ rte->relkind = 0;
+ rte->rellockmode = 0;
+ rte->tablesample = NULL;
+ rte->inh = false; /* must not be set for a subquery */
+
+ rte->requiredPerms = 0; /* no permission check on subquery itself */
+ rte->checkAsUser = InvalidOid;
+ rte->selectedCols = NULL;
+ rte->insertedCols = NULL;
+ rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
+
+ return rte;
+}
+
+/*
+ * make_delta_enr_name
+ *
+ * Make a name for ENR of a transition table from the base table's oid.
+ * prefix will be "new" or "old" depending on its transition table kind..
+ */
+static char*
+make_delta_enr_name(const char *prefix, Oid relid, int count)
+{
+ char buf[NAMEDATALEN];
+ char *name;
+
+ snprintf(buf, NAMEDATALEN, "__ivm_%s_%u_%u", prefix, relid, count);
+ name = pstrdup(buf);
+
+ return name;
+}
+
+/*
+ * union_ENRs
+ *
+ * Make a single table delta by unionning all transition tables of the modified table
+ * whose RTE is specified by
+ */
+static RangeTblEntry*
+union_ENRs(RangeTblEntry *rte, Oid relid, List *enr_rtes, const char *prefix,
+ QueryEnvironment *queryEnv)
+{
+ StringInfoData str;
+ ParseState *pstate;
+ RawStmt *raw;
+ Query *sub;
+ int i;
+ RangeTblEntry *enr_rte;
+
+ /* Create a ParseState for rewriting the view definition query */
+ pstate = make_parsestate(NULL);
+ pstate->p_queryEnv = queryEnv;
+ pstate->p_expr_kind = EXPR_KIND_SELECT_TARGET;
+
+ initStringInfo(&str);
+
+ for (i = 0; i < list_length(enr_rtes); i++)
+ {
+ if (i > 0)
+ appendStringInfo(&str, " UNION ALL ");
+
+ appendStringInfo(&str,
+ " SELECT * FROM %s",
+ make_delta_enr_name(prefix, relid, i));
+ }
+
+ raw = (RawStmt*)linitial(raw_parser(str.data, RAW_PARSE_DEFAULT));
+ sub = transformStmt(pstate, raw->stmt);
+
+ rte->rtekind = RTE_SUBQUERY;
+ rte->subquery = sub;
+ rte->security_barrier = false;
+ /* Clear fields that should not be set in a subquery RTE */
+ rte->relid = InvalidOid;
+ rte->relkind = 0;
+ rte->rellockmode = 0;
+ rte->tablesample = NULL;
+ rte->inh = false; /* must not be set for a subquery */
+
+ rte->requiredPerms = 0; /* no permission check on subquery itself */
+ rte->checkAsUser = InvalidOid;
+ rte->selectedCols = NULL;
+ rte->insertedCols = NULL;
+ rte->updatedCols = NULL;
+ rte->extraUpdatedCols = NULL;
+ /* if base table has RLS, set security condition to enr*/
+ enr_rte = (RangeTblEntry *)linitial(sub->rtable);
+ /* rt_index is always 1, bacause subquery has enr_rte only */
+ enr_rte->securityQuals = get_securityQuals(relid, 1, sub);
+
+ return rte;
+}
+
+/*
+ * rewrite_query_for_distinct
+ *
+ * Rewrite query for counting DISTINCT clause.
+ */
+static Query *
+rewrite_query_for_distinct(Query *query, ParseState *pstate)
+{
+ TargetEntry *tle_count;
+ FuncCall *fn;
+ Node *node;
+
+ /* Add count(*) for counting distinct tuples in views */
+ fn = makeFuncCall(list_make1(makeString("count")), NIL, COERCE_EXPLICIT_CALL, -1);
+ fn->agg_star = true;
+ if (!query->groupClause && !query->hasAggs)
+ query->groupClause = transformDistinctClause(NULL, &query->targetList, query->sortClause, false);
+
+ node = ParseFuncOrColumn(pstate, fn->funcname, NIL, NULL, fn, false, -1);
+
+ tle_count = makeTargetEntry((Expr *) node,
+ list_length(query->targetList) + 1,
+ pstrdup("__ivm_count__"),
+ false);
+ query->targetList = lappend(query->targetList, tle_count);
+ query->hasAggs = true;
+
+ return query;
+}
+
+/*
+ * calc_delta
+ *
+ * Calculate view deltas generated under the modification of a table specified
+ * by the RTE index.
+ */
+static void
+calc_delta(MV_TriggerTable *table, int rte_index, Query *query,
+ DestReceiver *dest_old, DestReceiver *dest_new,
+ TupleDesc *tupdesc_old, TupleDesc *tupdesc_new,
+ QueryEnvironment *queryEnv)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ /* Generate old delta */
+ if (list_length(table->old_rtes) > 0)
+ {
+ /* Replace the modified table with the old delta table and calculate the old view delta. */
+ lfirst(lc) = union_ENRs(rte, table->table_id, table->old_rtes, "old", queryEnv);
+ refresh_matview_datafill(dest_old, query, queryEnv, tupdesc_old, "");
+ }
+
+ /* Generate new delta */
+ if (list_length(table->new_rtes) > 0)
+ {
+ /* Replace the modified table with the new delta table and calculate the new view delta*/
+ lfirst(lc) = union_ENRs(rte, table->table_id, table->new_rtes, "new", queryEnv);
+ refresh_matview_datafill(dest_new, query, queryEnv, tupdesc_new, "");
+ }
+}
+
+/*
+ * rewrite_query_for_postupdate_state
+ *
+ * Rewrite the query so that the specified base table's RTEs will represent
+ * "post-update" state of tables. This is called after the view delta
+ * calculation due to changes on this table finishes.
+ */
+static Query*
+rewrite_query_for_postupdate_state(Query *query, MV_TriggerTable *table, int rte_index)
+{
+ ListCell *lc = list_nth_cell(query->rtable, rte_index - 1);
+
+ /* Retore the original RTE */
+ lfirst(lc) = table->original_rte;
+
+ return query;
+}
+
+/*
+ * apply_delta
+ *
+ * Apply deltas to the materialized view. In outer join cases, this requires
+ * the view maintenance graph.
+ */
+static void
+apply_delta(Oid matviewOid, Tuplestorestate *old_tuplestores, Tuplestorestate *new_tuplestores,
+ TupleDesc tupdesc_old, TupleDesc tupdesc_new,
+ Query *query, bool use_count, char *count_colname)
+{
+ StringInfoData querybuf;
+ StringInfoData target_list_buf;
+ Relation matviewRel;
+ char *matviewname;
+ ListCell *lc;
+ int i;
+ List *keys = NIL;
+
+
+ /*
+ * get names of the materialized view and delta tables
+ */
+
+ matviewRel = table_open(matviewOid, NoLock);
+ matviewname = quote_qualified_identifier(get_namespace_name(RelationGetNamespace(matviewRel)),
+ RelationGetRelationName(matviewRel));
+
+ /*
+ * Build parts of the maintenance queries
+ */
+
+ initStringInfo(&querybuf);
+ initStringInfo(&target_list_buf);
+
+ /* build string of target list */
+ for (i = 0; i < matviewRel->rd_att->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ if (i != 0)
+ appendStringInfo(&target_list_buf, ", ");
+ appendStringInfo(&target_list_buf, "%s", quote_qualified_identifier(NULL, resname));
+ }
+
+ i = 0;
+ foreach (lc, query->targetList)
+ {
+ TargetEntry *tle = (TargetEntry *) lfirst(lc);
+ Form_pg_attribute attr = TupleDescAttr(matviewRel->rd_att, i);
+ char *resname = NameStr(attr->attname);
+
+ i++;
+
+ if (tle->resjunk)
+ continue;
+
+ keys = lappend(keys, resname);
+ }
+
+ /* Start maintaining the materialized view. */
+ OpenMatViewIncrementalMaintenance();
+
+ /* Open SPI context. */
+ if (SPI_connect() != SPI_OK_CONNECT)
+ elog(ERROR, "SPI_connect failed");
+
+ /* For tuple deletion */
+ if (old_tuplestores && tuplestore_tuple_count(old_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(OLD_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_old;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(old_tuplestores);
+ enr->reldata = old_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ if (use_count)
+ /* apply old delta and get rows to be recalculated */
+ apply_old_delta_with_count(matviewname, OLD_DELTA_ENRNAME,
+ keys, count_colname);
+ else
+ apply_old_delta(matviewname, OLD_DELTA_ENRNAME, keys);
+
+ }
+ /* For tuple insertion */
+ if (new_tuplestores && tuplestore_tuple_count(new_tuplestores) > 0)
+ {
+ EphemeralNamedRelation enr = palloc(sizeof(EphemeralNamedRelationData));
+ int rc;
+
+ /* convert tuplestores to ENR, and register for SPI */
+ enr->md.name = pstrdup(NEW_DELTA_ENRNAME);
+ enr->md.reliddesc = InvalidOid;
+ enr->md.tupdesc = tupdesc_new;;
+ enr->md.enrtype = ENR_NAMED_TUPLESTORE;
+ enr->md.enrtuples = tuplestore_tuple_count(new_tuplestores);
+ enr->reldata = new_tuplestores;
+
+ rc = SPI_register_relation(enr);
+ if (rc != SPI_OK_REL_REGISTER)
+ elog(ERROR, "SPI_register failed");
+
+ /* apply new delta */
+ if (use_count)
+ apply_new_delta_with_count(matviewname, NEW_DELTA_ENRNAME,
+ keys, &target_list_buf, count_colname);
+ else
+ apply_new_delta(matviewname, NEW_DELTA_ENRNAME, &target_list_buf);
+ }
+
+ /* We're done maintaining the materialized view. */
+ CloseMatViewIncrementalMaintenance();
+
+ table_close(matviewRel, NoLock);
+
+ /* Close SPI context. */
+ if (SPI_finish() != SPI_OK_FINISH)
+ elog(ERROR, "SPI_finish failed");
+}
+
+/*
+ * apply_old_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct.
+ */
+static void
+apply_old_delta_with_count(const char *matviewname, const char *deltaname_old,
+ List *keys, const char *count_colname)
+{
+ StringInfoData querybuf;
+ char *match_cond;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH t AS (" /* collecting tid of target tuples in the view */
+ "SELECT diff.%s, " /* count column */
+ "(diff.%s OPERATOR(pg_catalog.=) mv.%s) AS for_dlt, "
+ "mv.ctid "
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s" /* tuple matching condition */
+ "), updt AS (" /* update a tuple if this is not to be deleted */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.-) t.%s "
+ "FROM t WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND NOT for_dlt "
+ "), dlt AS (" /* delete a tuple if this is to be deleted */
+ "DELETE FROM %s AS mv USING t "
+ "WHERE mv.ctid OPERATOR(pg_catalog.=) t.ctid AND for_dlt"
+ ")",
+ count_colname,
+ count_colname, count_colname,
+ matviewname, deltaname_old,
+ match_cond,
+ matviewname, count_colname, count_colname, count_colname,
+ matviewname);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_old_delta
+ *
+ * Execute a query for applying a delta table given by deltname_old
+ * which contains tuples to be deleted from to a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_old_delta(const char *matviewname, const char *deltaname_old,
+ List *keys)
+{
+ StringInfoData querybuf;
+ StringInfoData keysbuf;
+ char *match_cond;
+ ListCell *lc;
+
+ /* build WHERE condition for searching tuples to be deleted */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&keysbuf);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&keysbuf, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&keysbuf, ", ");
+ }
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "DELETE FROM %s WHERE ctid IN ("
+ "SELECT tid FROM (SELECT row_number() over (partition by %s) AS \"__ivm_row_number__\","
+ "mv.ctid AS tid,"
+ "diff.\"__ivm_count__\""
+ "FROM %s AS mv, %s AS diff "
+ "WHERE %s) v "
+ "WHERE v.\"__ivm_row_number__\" OPERATOR(pg_catalog.<=) v.\"__ivm_count__\")",
+ matviewname,
+ keysbuf.data,
+ matviewname, deltaname_old,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_DELETE)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_new_delta_with_count
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is required, that is, the view
+ * has aggregate or distinct. Also, when a table in EXISTS sub queries
+ * is modified.
+ */
+static void
+apply_new_delta_with_count(const char *matviewname, const char* deltaname_new,
+ List *keys, StringInfo target_list, const char* count_colname)
+{
+ StringInfoData querybuf;
+ StringInfoData returning_keys;
+ ListCell *lc;
+ char *match_cond = "";
+
+ /* build WHERE condition for searching tuples to be updated */
+ match_cond = get_matching_condition_string(keys);
+
+ /* build string of keys list */
+ initStringInfo(&returning_keys);
+ if (keys)
+ {
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ appendStringInfo(&returning_keys, "%s", quote_qualified_identifier("mv", resname));
+ if (lnext(keys, lc))
+ appendStringInfo(&returning_keys, ", ");
+ }
+ }
+ else
+ appendStringInfo(&returning_keys, "NULL");
+
+ /* Search for matching tuples from the view and update if found or insert if not. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "WITH updt AS (" /* update a tuple if this exists in the view */
+ "UPDATE %s AS mv SET %s = mv.%s OPERATOR(pg_catalog.+) diff.%s "
+ "FROM %s AS diff "
+ "WHERE %s " /* tuple matching condition */
+ "RETURNING %s" /* returning keys of updated tuples */
+ ") INSERT INTO %s (%s)" /* insert a new tuple if this doesn't existw */
+ "SELECT %s FROM %s AS diff "
+ "WHERE NOT EXISTS (SELECT 1 FROM updt AS mv WHERE %s);",
+ matviewname, count_colname, count_colname, count_colname,
+ deltaname_new,
+ match_cond,
+ returning_keys.data,
+ matviewname, target_list->data,
+ target_list->data, deltaname_new,
+ match_cond);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * apply_new_delta
+ *
+ * Execute a query for applying a delta table given by deltname_new
+ * which contains tuples to be inserted into a materialized view given by
+ * matviewname. This is used when counting is not required.
+ */
+static void
+apply_new_delta(const char *matviewname, const char *deltaname_new,
+ StringInfo target_list)
+{
+ StringInfoData querybuf;
+
+ /* Search for matching tuples from the view and update or delete if found. */
+ initStringInfo(&querybuf);
+ appendStringInfo(&querybuf,
+ "INSERT INTO %s (%s) SELECT %s FROM ("
+ "SELECT diff.*, generate_series(1, diff.\"__ivm_count__\") "
+ "FROM %s AS diff) AS v",
+ matviewname, target_list->data, target_list->data,
+ deltaname_new);
+
+ if (SPI_exec(querybuf.data, 0) != SPI_OK_INSERT)
+ elog(ERROR, "SPI_exec failed: %s", querybuf.data);
+}
+
+/*
+ * get_matching_condition_string
+ *
+ * Build a predicate string for looking for a tuple with given keys.
+ */
+static char *
+get_matching_condition_string(List *keys)
+{
+ StringInfoData match_cond;
+ ListCell *lc;
+
+ /* If there is no key columns, the condition is always true. */
+ if (keys == NIL)
+ return "true";
+
+ initStringInfo(&match_cond);
+ foreach (lc, keys)
+ {
+ Form_pg_attribute attr = (Form_pg_attribute) lfirst(lc);
+ char *resname = NameStr(attr->attname);
+ char *mv_resname = quote_qualified_identifier("mv", resname);
+ char *diff_resname = quote_qualified_identifier("diff", resname);
+ Oid typid = attr->atttypid;
+
+ /* Considering NULL values, we can not use simple = operator. */
+ appendStringInfo(&match_cond, "(");
+ generate_equal(&match_cond, typid, mv_resname, diff_resname);
+ appendStringInfo(&match_cond, " OR (%s IS NULL AND %s IS NULL))",
+ mv_resname, diff_resname);
+
+ if (lnext(keys, lc))
+ appendStringInfo(&match_cond, " AND ");
+ }
+
+ return match_cond.data;
+}
+
+/*
+ * generate_equals
+ *
+ * Generate an equality clause using given operands' default equality
+ * operator.
+ */
+static void
+generate_equal(StringInfo querybuf, Oid opttype,
+ const char *leftop, const char *rightop)
+{
+ TypeCacheEntry *typentry;
+
+ typentry = lookup_type_cache(opttype, TYPECACHE_EQ_OPR);
+ if (!OidIsValid(typentry->eq_opr))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be(opttype))));
+
+ generate_operator_clause(querybuf,
+ leftop, opttype,
+ typentry->eq_opr,
+ rightop, opttype);
+}
+
+/*
+ * mv_InitHashTables
+ */
+static void
+mv_InitHashTables(void)
+{
+ HASHCTL ctl;
+
+ memset(&ctl, 0, sizeof(ctl));
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(MV_TriggerHashEntry);
+ mv_trigger_info = hash_create("MV trigger info",
+ MV_INIT_QUERYHASHSIZE,
+ &ctl, HASH_ELEM | HASH_BLOBS);
+}
+
+/*
+ * AtAbort_IVM
+ *
+ * Clean up hash entries for all materialized views. This is called at
+ * transaction abort.
+ */
+void
+AtAbort_IVM()
+{
+ HASH_SEQ_STATUS seq;
+ MV_TriggerHashEntry *entry;
+
+ if (mv_trigger_info)
+ {
+ hash_seq_init(&seq, mv_trigger_info);
+ while ((entry = hash_seq_search(&seq)) != NULL)
+ clean_up_IVM_hash_entry(entry);
+ }
+}
+
+/*
+ * clean_up_IVM_hash_entry
+ *
+ * Clean up tuple stores and hash entries for a materialized view after its
+ * maintenance finished.
+ */
+static void
+clean_up_IVM_hash_entry(MV_TriggerHashEntry *entry)
+{
+ bool found;
+ ListCell *lc;
+
+ foreach(lc, entry->tables)
+ {
+ MV_TriggerTable *table = (MV_TriggerTable *) lfirst(lc);
+
+ list_free(table->old_tuplestores);
+ list_free(table->new_tuplestores);
+ }
+ list_free(entry->tables);
+
+ hash_search(mv_trigger_info, (void *) &entry->matview_id, HASH_REMOVE, &found);
+}
+
+/*
+ * isIvmName
+ *
+ * Check if this is a IVM hidden column from the name.
+ */
+bool
+isIvmName(const char *s)
+{
+ if (s)
+ return (strncmp(s, "__ivm_", 6) == 0);
+ return false;
+}
+
+/*
+ * get_securityQuals
+ *
+ * Get row security policy on a relation.
+ * This is used by IVM for copying RLS from base table to enr.
+ */
+static List *
+get_securityQuals(Oid relId, int rt_index, Query *query)
+{
+ ParseState *pstate;
+ Relation rel;
+ ParseNamespaceItem *nsitem;
+ RangeTblEntry *rte;
+ List *securityQuals;
+ List *withCheckOptions;
+ bool hasRowSecurity;
+ bool hasSubLinks;
+
+ securityQuals = NIL;
+ pstate = make_parsestate(NULL);
+
+ rel = table_open(relId, NoLock);
+ nsitem = addRangeTableEntryForRelation(pstate, rel, AccessShareLock, NULL, false, false);
+ rte = nsitem->p_rte;
+
+ get_row_security_policies(query, rte, rt_index,
+ &securityQuals, &withCheckOptions,
+ &hasRowSecurity, &hasSubLinks);
+
+ /*
+ * Make sure the query is marked correctly if row level security
+ * applies, or if the new quals had sublinks.
+ */
+ if (hasRowSecurity)
+ query->hasRowSecurity = true;
+ if (hasSubLinks)
+ query->hasSubLinks = true;
+
+ table_close(rel, NoLock);
+
+ return securityQuals;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index dc5872f988..571f5c4cb9 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -51,6 +51,7 @@
#include "commands/cluster.h"
#include "commands/comment.h"
#include "commands/defrem.h"
+#include "commands/matview.h"
#include "commands/event_trigger.h"
#include "commands/policy.h"
#include "commands/sequence.h"
@@ -3415,6 +3416,14 @@ renameatt_internal(Oid myrelid,
targetrelation = relation_open(myrelid, AccessExclusiveLock);
renameatt_check(myrelid, RelationGetForm(targetrelation), recursing);
+ /*
+ * Don't rename IVM columns.
+ */
+ if (RelationIsIVM(targetrelation) && isIvmName(oldattname))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("IVM column can not be renamed")));
+
/*
* if the 'recurse' flag is set then we are supposed to rename this
* attribute in all classes that inherit from 'relname' (as well as in
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index eb28657791..504ae3546e 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2464,6 +2464,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_SCALAR_FIELD(relkind);
COPY_SCALAR_FIELD(rellockmode);
COPY_NODE_FIELD(tablesample);
+ COPY_SCALAR_FIELD(relisivm);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
COPY_SCALAR_FIELD(jointype);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 521a87a8ea..56431f5aee 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2776,6 +2776,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_SCALAR_FIELD(relkind);
COMPARE_SCALAR_FIELD(rellockmode);
COMPARE_NODE_FIELD(tablesample);
+ COMPARE_SCALAR_FIELD(relisivm);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
COMPARE_SCALAR_FIELD(jointype);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 801c41b978..8cc0b93213 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -3259,6 +3259,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
WRITE_CHAR_FIELD(relkind);
WRITE_INT_FIELD(rellockmode);
WRITE_NODE_FIELD(tablesample);
+ WRITE_BOOL_FIELD(relisivm);
break;
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index cc6dcb7220..37dff1ec27 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1441,6 +1441,7 @@ _readRangeTblEntry(void)
READ_CHAR_FIELD(relkind);
READ_INT_FIELD(rellockmode);
READ_NODE_FIELD(tablesample);
+ READ_BOOL_FIELD(relisivm);
break;
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c
index cb9e177b5e..dcfd1f3fc0 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -36,6 +36,7 @@
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
+#include "commands/matview.h"
/*
@@ -79,7 +80,7 @@ static void expandTupleDesc(TupleDesc tupdesc, Alias *eref,
int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars);
+ List **colnames, List **colvars, bool is_ivm);
static int specialAttNum(const char *attname);
static bool isQueryUsingTempRelation_walker(Node *node, void *context);
@@ -1433,6 +1434,7 @@ addRangeTableEntry(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -1521,6 +1523,7 @@ addRangeTableEntryForRelation(ParseState *pstate,
rte->relid = RelationGetRelid(rel);
rte->relkind = rel->rd_rel->relkind;
rte->rellockmode = lockmode;
+ rte->relisivm = rel->rd_rel->relisivm;
/*
* Build the list of effective column names using user-supplied aliases
@@ -2676,7 +2679,7 @@ expandRTE(RangeTblEntry *rte, int rtindex, int sublevels_up,
expandTupleDesc(tupdesc, rte->eref,
rtfunc->funccolcount, atts_done,
rtindex, sublevels_up, location,
- include_dropped, colnames, colvars);
+ include_dropped, colnames, colvars, false);
}
else if (functypclass == TYPEFUNC_SCALAR)
{
@@ -2944,7 +2947,7 @@ expandRelation(Oid relid, Alias *eref, int rtindex, int sublevels_up,
expandTupleDesc(rel->rd_att, eref, rel->rd_att->natts, 0,
rtindex, sublevels_up,
location, include_dropped,
- colnames, colvars);
+ colnames, colvars, RelationIsIVM(rel));
relation_close(rel, AccessShareLock);
}
@@ -2961,7 +2964,7 @@ static void
expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
int rtindex, int sublevels_up,
int location, bool include_dropped,
- List **colnames, List **colvars)
+ List **colnames, List **colvars, bool is_ivm)
{
ListCell *aliascell;
int varattno;
@@ -2974,6 +2977,9 @@ expandTupleDesc(TupleDesc tupdesc, Alias *eref, int count, int offset,
{
Form_pg_attribute attr = TupleDescAttr(tupdesc, varattno);
+ if (is_ivm && isIvmName(NameStr(attr->attname)) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
if (attr->attisdropped)
{
if (include_dropped)
@@ -3127,6 +3133,10 @@ expandNSItemAttrs(ParseState *pstate, ParseNamespaceItem *nsitem,
Var *varnode = (Var *) lfirst(var);
TargetEntry *te;
+ /* if transform * into columnlist with IMMV, remove IVM columns */
+ if (rte->relisivm && isIvmName(label) && !MatViewIncrementalMaintenanceIsEnabled())
+ continue;
+
te = makeTargetEntry((Expr *) varnode,
(AttrNumber) pstate->p_next_resno++,
label,
diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c
index 185bf5fbff..d8a8b66196 100644
--- a/src/backend/rewrite/rewriteDefine.c
+++ b/src/backend/rewrite/rewriteDefine.c
@@ -776,7 +776,8 @@ checkRuleResultList(List *targetList, TupleDesc resultDesc, bool isSelect,
attr->atttypmod))));
}
- if (i != resultDesc->natts)
+ /* No check for materialized views since this could have special columns for IVM */
+ if ((!isSelect || requireColumnNameMatch) && i != resultDesc->natts)
ereport(ERROR,
(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
isSelect ?
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d8e8715ed1..2929a6872e 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11747,4 +11747,12 @@
prorettype => 'bytea', proargtypes => 'pg_brin_minmax_multi_summary',
prosrc => 'brin_minmax_multi_summary_send' },
+# IVM
+{ oid => '786', descr => 'ivm trigger (before)',
+ proname => 'IVM_immediate_before', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_before' },
+{ oid => '787', descr => 'ivm trigger (after)',
+ proname => 'IVM_immediate_maintenance', provolatile => 'v', prorettype => 'trigger',
+ proargtypes => '', prosrc => 'IVM_immediate_maintenance' },
+
]
diff --git a/src/include/commands/createas.h b/src/include/commands/createas.h
index 54a38491fb..c369b3ba5e 100644
--- a/src/include/commands/createas.h
+++ b/src/include/commands/createas.h
@@ -16,6 +16,7 @@
#include "catalog/objectaddress.h"
#include "nodes/params.h"
+#include "nodes/pathnodes.h"
#include "parser/parse_node.h"
#include "tcop/dest.h"
#include "utils/queryenvironment.h"
@@ -25,6 +26,11 @@ extern ObjectAddress ExecCreateTableAs(ParseState *pstate, CreateTableAsStmt *st
ParamListInfo params, QueryEnvironment *queryEnv,
QueryCompletion *qc);
+extern void CreateIvmTriggersOnBaseTables(Query *qry, Oid matviewOid, bool is_create);
+extern void CreateIndexOnIMMV(Query *query, Relation matviewRel, bool is_create);
+
+extern Query *rewriteQueryForIMMV(Query *query, List *colNames);
+
extern int GetIntoRelEFlags(IntoClause *intoClause);
extern DestReceiver *CreateIntoRelDestReceiver(IntoClause *intoClause);
diff --git a/src/include/commands/matview.h b/src/include/commands/matview.h
index a067da39d2..ec479db513 100644
--- a/src/include/commands/matview.h
+++ b/src/include/commands/matview.h
@@ -15,6 +15,7 @@
#define MATVIEW_H
#include "catalog/objectaddress.h"
+#include "fmgr.h"
#include "nodes/params.h"
#include "nodes/parsenodes.h"
#include "tcop/dest.h"
@@ -23,6 +24,8 @@
extern void SetMatViewPopulatedState(Relation relation, bool newstate);
+extern void SetMatViewIVMState(Relation relation, bool newstate);
+
extern ObjectAddress ExecRefreshMatView(RefreshMatViewStmt *stmt, const char *queryString,
ParamListInfo params, QueryCompletion *qc);
@@ -30,4 +33,9 @@ extern DestReceiver *CreateTransientRelDestReceiver(Oid oid);
extern bool MatViewIncrementalMaintenanceIsEnabled(void);
+extern Datum IVM_immediate_before(PG_FUNCTION_ARGS);
+extern Datum IVM_immediate_maintenance(PG_FUNCTION_ARGS);
+extern void AtAbort_IVM(void);
+extern bool isIvmName(const char *s);
+
#endif /* MATVIEW_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1617702d9d..3aa2d9f61c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1036,6 +1036,7 @@ typedef struct RangeTblEntry
char relkind; /* relation kind (see pg_class.relkind) */
int rellockmode; /* lock level that query requires on the rel */
struct TableSampleClause *tablesample; /* sampling info, or NULL */
+ bool relisivm;
/*
* Fields valid for a subquery RTE (else NULL):
@@ -2195,6 +2196,7 @@ typedef struct CreateStmt
char *tablespacename; /* table space to use, or NULL */
char *accessMethod; /* table access method */
bool if_not_exists; /* just do nothing if it already exists? */
+ bool ivm; /* incremental view maintenance is used by materialized view */
} CreateStmt;
/* ----------
--
2.17.1
--Multipart=_Mon__14_Mar_2022_19_12_17_+0900_E9HVp8j5QFkIIek.
Content-Type: text/x-diff;
name="v26-0006-Add-Incremental-View-Maintenance-support-to-psql.patch"
Content-Disposition: attachment;
filename="v26-0006-Add-Incremental-View-Maintenance-support-to-psql.patch"
Content-Transfer-Encoding: 7bit
^ permalink raw reply [nested|flat] 13+ messages in thread
* Patch for migration of the pg_commit_ts directory
@ 2025-04-03 18:27 ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: ls7777 @ 2025-04-03 18:27 UTC (permalink / raw)
To: pgsql-hackers
------==--bound.1245643.r-production-main-61.klg.yp-c.yandex.net
Content-Transfer-Encoding: 8bit
Content-Type: text/html; charset=utf-8
<div><div><div><div>Good afternoon!</div><div>During pg_upgrade migration, the pg_commit_ts directory is not copied when track_commit_timestamp = on is set. There is a patch in the attachment that fixes this.</div></div><div> </div><div>Contents & Purpose</div><div>==================</div><div>The patch copies the pg_commit_ts directory of the old cluster and updates the Latest checkpoints oldestCommitTsXid and Latest checkpoint's newestCommitTsXid for the new cluster. If required.</div><div> </div><div>Initial Run</div><div>===========</div><div>The patch can be applied on the master branch.</div><div>I don't understand what tests are needed for such a patch. The patch is very simple.</div><div> Performance</div><div>===========</div><div>The pg_upgrade operation time increases by the time the pg_commit_ts catalog is copied.</div><div> </div><div> </div></div></div>
------==--bound.1245643.r-production-main-61.klg.yp-c.yandex.net
Content-Disposition: attachment;
filename="v1-0001-Migration-of-the-pg_commit_ts-directory.patch"
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
name="v1-0001-Migration-of-the-pg_commit_ts-directory.patch"
RnJvbSA1ZmNiYWNkODcwMDI3YWZlOWQ1OWJlYzY5NmZhZGZhZTU3MjdkYjViIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTZXJnZXkgTGV2aW4gPGxzNzc3N0B5YW5kZXgucnU+CkRhdGU6
IFRodSwgMyBBcHIgMjAyNSAyMTo0NDowMSArMDUwMApTdWJqZWN0OiBbUEFUQ0ggdjFdIE1pZ3Jh
dGlvbiBvZiB0aGUgcGdfY29tbWl0X3RzIGRpcmVjdG9yeQoKLS0tCiBzcmMvYmluL3BnX3VwZ3Jh
ZGUvY29udHJvbGRhdGEuYyB8IDIzICsrKysrKysrKysrKysrKysrKysrKystCiBzcmMvYmluL3Bn
X3VwZ3JhZGUvcGdfdXBncmFkZS5jICB8ICA2ICsrKystLQogc3JjL2Jpbi9wZ191cGdyYWRlL3Bn
X3VwZ3JhZGUuaCAgfCAgMiArKwogMyBmaWxlcyBjaGFuZ2VkLCAyOCBpbnNlcnRpb25zKCspLCAz
IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL3NyYy9iaW4vcGdfdXBncmFkZS9jb250cm9sZGF0
YS5jIGIvc3JjL2Jpbi9wZ191cGdyYWRlL2NvbnRyb2xkYXRhLmMKaW5kZXggNDdlZTI3ZWM4MzUu
LjBkYTVlZTQyMjVlIDEwMDY0NAotLS0gYS9zcmMvYmluL3BnX3VwZ3JhZGUvY29udHJvbGRhdGEu
YworKysgYi9zcmMvYmluL3BnX3VwZ3JhZGUvY29udHJvbGRhdGEuYwpAQCAtMjA3LDcgKzIwNyw4
IEBAIGdldF9jb250cm9sX2RhdGEoQ2x1c3RlckluZm8gKmNsdXN0ZXIpCiAJCWNsdXN0ZXItPmNv
bnRyb2xkYXRhLmRhdGFfY2hlY2tzdW1fdmVyc2lvbiA9IDA7CiAJCWdvdF9kYXRhX2NoZWNrc3Vt
X3ZlcnNpb24gPSB0cnVlOwogCX0KLQorCWNsdXN0ZXItPmNvbnRyb2xkYXRhLmNoa3BudF9vbGRz
dENvbW1pdFRzeGlkID0gMDsKKwljbHVzdGVyLT5jb250cm9sZGF0YS5jaGtwbnRfbmV3c3RDb21t
aXRUc3hpZCA9IDA7CiAJLyogd2UgaGF2ZSB0aGUgcmVzdWx0IG9mIGNtZCBpbiAib3V0cHV0Ii4g
c28gcGFyc2UgaXQgbGluZSBieSBsaW5lIG5vdyAqLwogCXdoaWxlIChmZ2V0cyhidWZpbiwgc2l6
ZW9mKGJ1ZmluKSwgb3V0cHV0KSkKIAl7CkBAIC0zMjAsNiArMzIxLDI2IEBAIGdldF9jb250cm9s
X2RhdGEoQ2x1c3RlckluZm8gKmNsdXN0ZXIpCiAJCQljbHVzdGVyLT5jb250cm9sZGF0YS5jaGtw
bnRfbnh0bXVsdGkgPSBzdHIydWludChwKTsKIAkJCWdvdF9tdWx0aSA9IHRydWU7CiAJCX0KKwkJ
ZWxzZSBpZiAoKHAgPSBzdHJzdHIoYnVmaW4sICJMYXRlc3QgY2hlY2twb2ludCdzIG9sZGVzdENv
bW1pdFRzWGlkOiIpKSAhPSBOVUxMKQorCQl7CisJCQlwID0gc3RyY2hyKHAsICc6Jyk7CisKKwkJ
CWlmIChwID09IE5VTEwgfHwgc3RybGVuKHApIDw9IDEpCisJCQkJcGdfZmF0YWwoIiVkOiBjb250
cm9sZGF0YSByZXRyaWV2YWwgcHJvYmxlbSIsIF9fTElORV9fKTsKKworCQkJcCsrOwkJCQkvKiBy
ZW1vdmUgJzonIGNoYXIgKi8KKwkJCWNsdXN0ZXItPmNvbnRyb2xkYXRhLmNoa3BudF9vbGRzdENv
bW1pdFRzeGlkID0gc3RyMnVpbnQocCk7CisJCX0KKwkJZWxzZSBpZiAoKHAgPSBzdHJzdHIoYnVm
aW4sICJMYXRlc3QgY2hlY2twb2ludCdzIG5ld2VzdENvbW1pdFRzWGlkOiIpKSAhPSBOVUxMKQor
CQl7CisJCQlwID0gc3RyY2hyKHAsICc6Jyk7CisKKwkJCWlmIChwID09IE5VTEwgfHwgc3RybGVu
KHApIDw9IDEpCisJCQkJcGdfZmF0YWwoIiVkOiBjb250cm9sZGF0YSByZXRyaWV2YWwgcHJvYmxl
bSIsIF9fTElORV9fKTsKKworCQkJcCsrOwkJCQkvKiByZW1vdmUgJzonIGNoYXIgKi8KKwkJCWNs
dXN0ZXItPmNvbnRyb2xkYXRhLmNoa3BudF9uZXdzdENvbW1pdFRzeGlkID0gc3RyMnVpbnQocCk7
CisJCX0KIAkJZWxzZSBpZiAoKHAgPSBzdHJzdHIoYnVmaW4sICJMYXRlc3QgY2hlY2twb2ludCdz
IG9sZGVzdFhJRDoiKSkgIT0gTlVMTCkKIAkJewogCQkJcCA9IHN0cmNocihwLCAnOicpOwpkaWZm
IC0tZ2l0IGEvc3JjL2Jpbi9wZ191cGdyYWRlL3BnX3VwZ3JhZGUuYyBiL3NyYy9iaW4vcGdfdXBn
cmFkZS9wZ191cGdyYWRlLmMKaW5kZXggOTI5NWU0NmFlZDMuLjc0NmY4YTZiMDJiIDEwMDY0NAot
LS0gYS9zcmMvYmluL3BnX3VwZ3JhZGUvcGdfdXBncmFkZS5jCisrKyBiL3NyYy9iaW4vcGdfdXBn
cmFkZS9wZ191cGdyYWRlLmMKQEAgLTc1MSw2ICs3NTEsOCBAQCBjb3B5X3hhY3RfeGxvZ194aWQo
dm9pZCkKIAkJCQkJICAicGdfY2xvZyIgOiAicGdfeGFjdCIsCiAJCQkJCSAgR0VUX01BSk9SX1ZF
UlNJT04obmV3X2NsdXN0ZXIubWFqb3JfdmVyc2lvbikgPD0gOTA2ID8KIAkJCQkJICAicGdfY2xv
ZyIgOiAicGdfeGFjdCIpOworCWlmIChvbGRfY2x1c3Rlci5jb250cm9sZGF0YS5jaGtwbnRfb2xk
c3RDb21taXRUc3hpZCA+IDApCisJCQkJCSAgIGNvcHlfc3ViZGlyX2ZpbGVzKCJwZ19jb21taXRf
dHMiLCAicGdfY29tbWl0X3RzIik7CiAKIAlwcmVwX3N0YXR1cygiU2V0dGluZyBvbGRlc3QgWElE
IGZvciBuZXcgY2x1c3RlciIpOwogCWV4ZWNfcHJvZyhVVElMSVRZX0xPR19GSUxFLCBOVUxMLCB0
cnVlLCB0cnVlLApAQCAtNzczLDggKzc3NSw4IEBAIGNvcHlfeGFjdF94bG9nX3hpZCh2b2lkKQog
CWV4ZWNfcHJvZyhVVElMSVRZX0xPR19GSUxFLCBOVUxMLCB0cnVlLCB0cnVlLAogCQkJICAiXCIl
cy9wZ19yZXNldHdhbFwiIC1mIC1jICV1LCV1IFwiJXNcIiIsCiAJCQkgIG5ld19jbHVzdGVyLmJp
bmRpciwKLQkJCSAgb2xkX2NsdXN0ZXIuY29udHJvbGRhdGEuY2hrcG50X254dHhpZCwKLQkJCSAg
b2xkX2NsdXN0ZXIuY29udHJvbGRhdGEuY2hrcG50X254dHhpZCwKKwkJCSAgb2xkX2NsdXN0ZXIu
Y29udHJvbGRhdGEuY2hrcG50X29sZHN0Q29tbWl0VHN4aWQgPT0gMCA/IG9sZF9jbHVzdGVyLmNv
bnRyb2xkYXRhLmNoa3BudF9ueHR4aWQgOiBvbGRfY2x1c3Rlci5jb250cm9sZGF0YS5jaGtwbnRf
b2xkc3RDb21taXRUc3hpZCwKKwkJCSAgb2xkX2NsdXN0ZXIuY29udHJvbGRhdGEuY2hrcG50X25l
d3N0Q29tbWl0VHN4aWQgPT0gMCA/IG9sZF9jbHVzdGVyLmNvbnRyb2xkYXRhLmNoa3BudF9ueHR4
aWQgOiBvbGRfY2x1c3Rlci5jb250cm9sZGF0YS5jaGtwbnRfbmV3c3RDb21taXRUc3hpZCwKIAkJ
CSAgbmV3X2NsdXN0ZXIucGdkYXRhKTsKIAljaGVja19vaygpOwogCmRpZmYgLS1naXQgYS9zcmMv
YmluL3BnX3VwZ3JhZGUvcGdfdXBncmFkZS5oIGIvc3JjL2Jpbi9wZ191cGdyYWRlL3BnX3VwZ3Jh
ZGUuaAppbmRleCA2OWM5NjViYjdkMC4uYjU1ZTJjMmVhODQgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4v
cGdfdXBncmFkZS9wZ191cGdyYWRlLmgKKysrIGIvc3JjL2Jpbi9wZ191cGdyYWRlL3BnX3VwZ3Jh
ZGUuaApAQCAtMjM4LDYgKzIzOCw4IEBAIHR5cGVkZWYgc3RydWN0CiAJdWludDMyCQljaGtwbnRf
bnh0bXhvZmY7CiAJdWludDMyCQljaGtwbnRfb2xkc3RNdWx0aTsKIAl1aW50MzIJCWNoa3BudF9v
bGRzdHhpZDsKKwl1aW50MzIJCWNoa3BudF9vbGRzdENvbW1pdFRzeGlkOworCXVpbnQzMgkJY2hr
cG50X25ld3N0Q29tbWl0VHN4aWQ7CiAJdWludDMyCQlhbGlnbjsKIAl1aW50MzIJCWJsb2Nrc3o7
CiAJdWludDMyCQlsYXJnZXN6OwotLSAKMi40Mi4yCgo=
------==--bound.1245643.r-production-main-61.klg.yp-c.yandex.net--
Attachments:
[text/x-diff] v1-0001-Migration-of-the-pg_commit_ts-directory.patch (3.4K, ../../[email protected]/2-v1-0001-Migration-of-the-pg_commit_ts-directory.patch)
download | inline diff:
From 5fcbacd870027afe9d59bec696fadfae5727db5b Mon Sep 17 00:00:00 2001
From: Sergey Levin <[email protected]>
Date: Thu, 3 Apr 2025 21:44:01 +0500
Subject: [PATCH v1] Migration of the pg_commit_ts directory
---
src/bin/pg_upgrade/controldata.c | 23 ++++++++++++++++++++++-
src/bin/pg_upgrade/pg_upgrade.c | 6 ++++--
src/bin/pg_upgrade/pg_upgrade.h | 2 ++
3 files changed, 28 insertions(+), 3 deletions(-)
diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c
index 47ee27ec835..0da5ee4225e 100644
--- a/src/bin/pg_upgrade/controldata.c
+++ b/src/bin/pg_upgrade/controldata.c
@@ -207,7 +207,8 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.data_checksum_version = 0;
got_data_checksum_version = true;
}
-
+ cluster->controldata.chkpnt_oldstCommitTsxid = 0;
+ cluster->controldata.chkpnt_newstCommitTsxid = 0;
/* we have the result of cmd in "output". so parse it line by line now */
while (fgets(bufin, sizeof(bufin), output))
{
@@ -320,6 +321,26 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.chkpnt_nxtmulti = str2uint(p);
got_multi = true;
}
+ else if ((p = strstr(bufin, "Latest checkpoint's oldestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_oldstCommitTsxid = str2uint(p);
+ }
+ else if ((p = strstr(bufin, "Latest checkpoint's newestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_newstCommitTsxid = str2uint(p);
+ }
else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL)
{
p = strchr(p, ':');
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 9295e46aed3..746f8a6b02b 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -751,6 +751,8 @@ copy_xact_xlog_xid(void)
"pg_clog" : "pg_xact",
GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
"pg_clog" : "pg_xact");
+ if (old_cluster.controldata.chkpnt_oldstCommitTsxid > 0)
+ copy_subdir_files("pg_commit_ts", "pg_commit_ts");
prep_status("Setting oldest XID for new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
@@ -773,8 +775,8 @@ copy_xact_xlog_xid(void)
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -c %u,%u \"%s\"",
new_cluster.bindir,
- old_cluster.controldata.chkpnt_nxtxid,
- old_cluster.controldata.chkpnt_nxtxid,
+ old_cluster.controldata.chkpnt_oldstCommitTsxid == 0 ? old_cluster.controldata.chkpnt_nxtxid : old_cluster.controldata.chkpnt_oldstCommitTsxid,
+ old_cluster.controldata.chkpnt_newstCommitTsxid == 0 ? old_cluster.controldata.chkpnt_nxtxid : old_cluster.controldata.chkpnt_newstCommitTsxid,
new_cluster.pgdata);
check_ok();
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index 69c965bb7d0..b55e2c2ea84 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -238,6 +238,8 @@ typedef struct
uint32 chkpnt_nxtmxoff;
uint32 chkpnt_oldstMulti;
uint32 chkpnt_oldstxid;
+ uint32 chkpnt_oldstCommitTsxid;
+ uint32 chkpnt_newstCommitTsxid;
uint32 align;
uint32 blocksz;
uint32 largesz;
--
2.42.2
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
@ 2026-01-05 08:24 ` Hayato Kuroda (Fujitsu) <[email protected]>
2026-01-05 11:27 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-01-05 08:24 UTC (permalink / raw)
To: 'ls7777' <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
Dear Hackers,
I found that v7 needs rebased. Copyright was also updated in the attached patch.
I'm not the author of the patch though.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v8-0001-Migration-of-the-pg_commit_ts-directory.patch (8.1K, ../../TY7PR01MB14554CC93E03A125BEE334C1EF586A@TY7PR01MB14554.jpnprd01.prod.outlook.com/2-v8-0001-Migration-of-the-pg_commit_ts-directory.patch)
download | inline diff:
From 53cde45863d0fa00c6dc84a3dd02ab10a622b6bd Mon Sep 17 00:00:00 2001
From: Sergey Levin <[email protected]>
Date: Sat, 4 Oct 2025 20:22:46 +0500
Subject: [PATCH v8] Migration of the pg_commit_ts directory
---
src/bin/pg_upgrade/check.c | 24 +++++++
src/bin/pg_upgrade/controldata.c | 20 ++++++
src/bin/pg_upgrade/meson.build | 1 +
src/bin/pg_upgrade/pg_upgrade.c | 29 +++++++-
src/bin/pg_upgrade/pg_upgrade.h | 2 +
.../pg_upgrade/t/008_transfer_commit_ts.pl | 67 +++++++++++++++++++
6 files changed, 140 insertions(+), 3 deletions(-)
create mode 100644 src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index a47d553b809..fc0b720b60c 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -32,6 +32,7 @@ static void check_new_cluster_replication_slots(void);
static void check_new_cluster_subscription_configuration(void);
static void check_old_cluster_for_valid_slots(void);
static void check_old_cluster_subscription_state(void);
+static void check_new_cluster_pg_commit_ts(void);
/*
* DataTypesUsageChecks - definitions of data type checks for the old cluster
@@ -767,9 +768,32 @@ check_new_cluster(void)
check_new_cluster_replication_slots();
check_new_cluster_subscription_configuration();
+
+ check_new_cluster_pg_commit_ts();
+
}
+void
+check_new_cluster_pg_commit_ts(void)
+{
+ PGconn *conn;
+ PGresult *res;
+ bool track_commit_timestamp_on;
+
+ prep_status("Checking for new cluster configuration for commit timestamp");
+ conn = connectToServer(&new_cluster, "template1");
+ res = executeQueryOrDie(conn, "SELECT setting FROM pg_settings "
+ "WHERE name = 'track_commit_timestamp'");
+ track_commit_timestamp_on = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
+ PQclear(res);
+ PQfinish(conn);
+ if (!track_commit_timestamp_on &&
+ old_cluster.controldata.chkpnt_newstCommitTsxid > 0)
+ pg_fatal("\"track_commit_timestamp\" must be \"on\" but is set to \"off\"");
+
+ check_ok();
+}
void
report_clusters_compatible(void)
{
diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c
index aa6e8b4de5d..fa8b28adf43 100644
--- a/src/bin/pg_upgrade/controldata.c
+++ b/src/bin/pg_upgrade/controldata.c
@@ -321,6 +321,26 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.chkpnt_nxtmulti = str2uint(p);
got_multi = true;
}
+ else if ((p = strstr(bufin, "Latest checkpoint's oldestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_oldstCommitTsxid = str2uint(p);
+ }
+ else if ((p = strstr(bufin, "Latest checkpoint's newestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_newstCommitTsxid = str2uint(p);
+ }
else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL)
{
p = strchr(p, ':');
diff --git a/src/bin/pg_upgrade/meson.build b/src/bin/pg_upgrade/meson.build
index 49b1b624f25..b27477c3f8a 100644
--- a/src/bin/pg_upgrade/meson.build
+++ b/src/bin/pg_upgrade/meson.build
@@ -51,6 +51,7 @@ tests += {
't/005_char_signedness.pl',
't/006_transfer_modes.pl',
't/007_multixact_conversion.pl',
+ 't/008_transfer_commit_ts.pl',
],
'test_kwargs': {'priority': 40}, # pg_upgrade tests are slow
},
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 2127d297bfe..b761f6c86c4 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -773,6 +773,10 @@ copy_subdir_files(const char *old_subdir, const char *new_subdir)
static void
copy_xact_xlog_xid(void)
{
+ bool is_copy_commit_ts;
+ uint32 oldest_xid,
+ newest_xid;
+
/*
* Copy old commit logs to new data dir. pg_clog has been renamed to
* pg_xact in post-10 clusters.
@@ -782,6 +786,22 @@ copy_xact_xlog_xid(void)
GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
"pg_clog" : "pg_xact");
+ /*
+ * Copy old commit_timestamp data to new, if available.
+ */
+ is_copy_commit_ts =
+ (old_cluster.controldata.chkpnt_oldstCommitTsxid > 0 &&
+ old_cluster.controldata.chkpnt_newstCommitTsxid > 0);
+
+ if (is_copy_commit_ts)
+ {
+ copy_subdir_files("pg_commit_ts", "pg_commit_ts");
+ oldest_xid = old_cluster.controldata.chkpnt_oldstCommitTsxid;
+ newest_xid = old_cluster.controldata.chkpnt_newstCommitTsxid;
+ }
+ else
+ oldest_xid = newest_xid = old_cluster.controldata.chkpnt_nxtxid;
+
prep_status("Setting oldest XID for new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -u %u \"%s\"",
@@ -799,12 +819,15 @@ copy_xact_xlog_xid(void)
"\"%s/pg_resetwal\" -f -e %u \"%s\"",
new_cluster.bindir, old_cluster.controldata.chkpnt_nxtepoch,
new_cluster.pgdata);
- /* must reset commit timestamp limits also */
+
+ /*
+ * must reset commit timestamp limits also or copy from the old cluster
+ */
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -c %u,%u \"%s\"",
new_cluster.bindir,
- old_cluster.controldata.chkpnt_nxtxid,
- old_cluster.controldata.chkpnt_nxtxid,
+ oldest_xid,
+ newest_xid,
new_cluster.pgdata);
check_ok();
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index ec018e4f292..938413521cd 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -245,6 +245,8 @@ typedef struct
uint64 chkpnt_nxtmxoff;
uint32 chkpnt_oldstMulti;
uint32 chkpnt_oldstxid;
+ uint32 chkpnt_oldstCommitTsxid;
+ uint32 chkpnt_newstCommitTsxid;
uint32 align;
uint32 blocksz;
uint32 largesz;
diff --git a/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl b/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
new file mode 100644
index 00000000000..a9f0ff4ba8f
--- /dev/null
+++ b/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
@@ -0,0 +1,67 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Tests for transfer pg_commit_ts directory.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Can be changed to test the other modes
+my $mode = $ENV{PG_TEST_PG_UPGRADE_MODE} || '--copy';
+
+# Initialize old cluster
+my $old = PostgreSQL::Test::Cluster->new('old');
+$old->init;
+$old->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+$old->start;
+my $resold = $old->safe_psql(
+ 'postgres', qq{
+ create table a(a int);
+ select xid,timestamp from pg_last_committed_xact();
+});
+
+my ($xid) = $resold =~ /\s*(\d+)\s*\|.*/;
+$old->stop;
+
+# Initialize new cluster
+my $new = PostgreSQL::Test::Cluster->new('new');
+$new->init;
+
+# Setup a common pg_upgrade command to be used by all the test cases
+my @pg_upgrade_cmd = (
+ 'pg_upgrade', '--no-sync',
+ '--old-datadir' => $old->data_dir,
+ '--new-datadir' => $new->data_dir,
+ '--old-bindir' => $old->config_data('--bindir'),
+ '--new-bindir' => $new->config_data('--bindir'),
+ '--socketdir' => $new->host,
+ '--old-port' => $old->port,
+ '--new-port' => $new->port,
+ $mode);
+
+# In a VPATH build, we'll be started in the source directory, but we want
+# to run pg_upgrade in the build directory so that any files generated finish
+# in it, like delete_old_cluster.{sh,bat}.
+chdir ${PostgreSQL::Test::Utils::tmp_check};
+
+command_checks_all(
+ [@pg_upgrade_cmd], 1,
+ [qr{"track_commit_timestamp" must be "on" but is set to "off"}], [],
+ 'run of pg_upgrade for mismatch parameter track_commit_timestamp');
+
+$new->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+
+command_ok([@pg_upgrade_cmd], 'run of pg_upgrade ok');
+
+$new->start;
+my $resnew = $new->safe_psql(
+ 'postgres', qq{
+ select $xid,pg_xact_commit_timestamp(${xid}::text::xid);
+});
+$new->stop;
+ok($resold eq $resnew, "timestamp transferred successfully");
+
+done_testing();
--
2.47.3
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-01-05 11:27 ` ls7777 <[email protected]>
2026-01-06 00:41 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: ls7777 @ 2026-01-05 11:27 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
<div><div>Hi,</div><div> </div><div><div><div>I didn't quite understand what I needed to do. </div><div>My assumptions:</div><div>1. You need to download the postgresql master branch and create a patch file on it.</div><div> </div><div>2. Replace the 2025 with 2026 header comment in all с<span style="white-space:pre-wrap">hangeable</span> patch files.</div><div> </div><div>Did I understand correctly?</div></div></div></div><div> </div><div>----------------</div><div>Кому: 'ls7777' ([email protected]);</div><div>Копия: [email protected], [email protected], [email protected];</div><div>Тема: Patch for migration of the pg_commit_ts directory;</div><div>05.01.2026, 13:25, "Hayato Kuroda (Fujitsu)" <[email protected]>:</div><blockquote><p>Dear Hackers,<br /><br />I found that v7 needs rebased. Copyright was also updated in the attached patch.<br />I'm not the author of the patch though.<br /><br />Best regards,<br />Hayato Kuroda<br />FUJITSU LIMITED<br /> </p></blockquote>
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-01-05 11:27 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
@ 2026-01-06 00:41 ` Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-01-06 00:41 UTC (permalink / raw)
To: 'ls7777' <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>
Hi,
> I didn't quite understand what I needed to do.
> My assumptions:
> 1. You need to download the postgresql master branch and create a patch file on it.
This is correct. I periodically checked the commitfest app [1], and it has
reported [Needs rebase!] for weeks. Now it could be applied cleanly and tests
could be passed.
> 2. Replace the 2025 with 2026 header comment in all сhangeable patch files.
Since I cannot follow the sentence, let me clarify my understanding.
Basically, the copyright notation is maintained by the PostgreSQL community;
nothing for us to do. They are updated at the beginning of the year [2].
If you are proposing to add new files, however, they must contain the copyright
and be updated in the new year. It's not yet included in the codebase and is out
of scope for the community's maintenance.
Please ask me anything if you have more questions :-).
[1]: https://commitfest.postgresql.org/patch/6119/
[2]: https://github.com/postgres/postgres/commit/451c43974f8e199097d97624a4952ad0973cea61
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-02-20 07:58 ` Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Amit Kapila @ 2026-02-20 07:58 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: ls7777 <[email protected]>; pgsql-hackers; [email protected] <[email protected]>
On Mon, Jan 5, 2026 at 1:54 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> I found that v7 needs rebased. Copyright was also updated in the attached patch.
> I'm not the author of the patch though.
>
Can we update the use case of this patch in the commit message? One of
the use case I recall is to detect conflicts with accuracy after
upgrade. See docs [1] ("Commit timestamps and origin data are not
preserved during the upgrade. ..) I think this note needs an update
after this patch.
res = executeQueryOrDie(conn, "SELECT setting FROM pg_settings "
+ "WHERE name = 'track_commit_timestamp'");
+ track_commit_timestamp_on = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
As this is a boolean variable, what if the user sets the value of this
GUC as true, will the above work correctly? Also, _on in the variable
name appears bit odd.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
@ 2026-02-20 11:05 ` Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 11:34 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-05-27 05:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-02-20 11:05 UTC (permalink / raw)
To: 'Amit Kapila' <[email protected]>; +Cc: ls7777 <[email protected]>; pgsql-hackers; [email protected] <[email protected]>
Dear Amit,
> One of
> the use case I recall is to detect conflicts with accuracy after
> upgrade. See docs [1] ("Commit timestamps and origin data are not
> preserved during the upgrade. ..) I think this note needs an update
> after this patch.
Right, and code comments [a] should be also updated.
BTW, is there a possibility that we can export and import xmin of the conflict
slot to retain dead tuples even after the upgrade? Of course it's out-of-scope
from this thread.
> res = executeQueryOrDie(conn, "SELECT setting FROM pg_settings "
> + "WHERE name = 'track_commit_timestamp'");
> + track_commit_timestamp_on = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
>
> As this is a boolean variable, what if the user sets the value of this
> GUC as true, will the above work correctly?
Per my understanding, it is OK to use "on"/"off" notation. Below contains the
analysis.
pg_settings uses an SQL function pg_show_all_settings, and it is implemneted by
the C function show_all_settings(). And GetConfigOptionValues()->ShowGUCOption()
actualy create the output for the setting column in the view. According to the
function, the boolean would be the either "on" or "off" unless the GUC has show_hook.
```
switch (record->vartype)
{
case PGC_BOOL:
{
const struct config_bool *conf = &record->_bool;
if (conf->show_hook)
val = conf->show_hook();
else
val = *conf->variable ? "on" : "off";
}
break;
```
It mactches my experiment below.
```
$ cat data_pub/postgresql.conf | grep track_commit_timestamp
track_commit_timestamp = true # collect timestamp of transaction commit
$ psql -U postgres -p 5432
postgres=# SELECT setting FROM pg_settings WHERE name = 'track_commit_timestamp';
setting
---------
on
(1 row)
```
> Also, _on in the variable name appears bit odd.
I have two options, thought?
- commit_ts_is_enabled,
- track_commit_timestmap, same as GUC variable.
[a]: https://github.com/postgres/postgres/blob/master/src/bin/pg_upgrade/pg_upgrade.c#L215
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-02-20 11:34 ` Amit Kapila <[email protected]>
2026-02-23 04:41 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Amit Kapila @ 2026-02-20 11:34 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: ls7777 <[email protected]>; pgsql-hackers; [email protected] <[email protected]>
On Fri, Feb 20, 2026 at 4:35 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> > One of
> > the use case I recall is to detect conflicts with accuracy after
> > upgrade. See docs [1] ("Commit timestamps and origin data are not
> > preserved during the upgrade. ..) I think this note needs an update
> > after this patch.
>
> Right, and code comments [a] should be also updated.
>
So, leaving aside update_delete, copying commit_ts could be helpful to
detect some other conflicts. You may want to once test the same and
show it here as part of use case establishment.
> BTW, is there a possibility that we can export and import xmin of the conflict
> slot to retain dead tuples even after the upgrade? Of course it's out-of-scope
> from this thread.
>
Yeah, this is a good point to explore separately. The key thing we
need to evaluate is whether the rows corresponding to old_xids are
retained. We probably need to evaluate the epoch part as well in old
cluster's slot. We do call set_frozenxids() for new cluster that might
have some impact on the functionality you are looking at.
> > Also, _on in the variable name appears bit odd.
>
> I have two options, thought?
> - commit_ts_is_enabled,
This looks reasonable to me.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 11:34 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
@ 2026-02-23 04:41 ` Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-23 13:33 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-02-23 04:41 UTC (permalink / raw)
To: 'Amit Kapila' <[email protected]>; +Cc: ls7777 <[email protected]>; pgsql-hackers; [email protected] <[email protected]>
Dear Amit,
> > Right, and code comments [a] should be also updated.
> >
>
> So, leaving aside update_delete, copying commit_ts could be helpful to
> detect some other conflicts. You may want to once test the same and
> show it here as part of use case establishment.
I confirmed that {update|delete}_origin_differs could be detected with the
following steps.
0.
Constructed pub-sub replication system. track_commit_timestamp=on was set on the
subscriber, and the table below was defined on both clusters
```
Table "public.employee"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
salary | integer | | |
Indexes:
"employee_pkey" PRIMARY KEY, btree (id)
```
1.
Inserted a tuple on the publisher
```
pub=# INSERT INTO employee VALUES (1, 100);
INSERT 0 1
```
2.
UPDATEd the replicated tuple on the subscriber. Confirmed that commit timestamps
were stored.
```
sub=# SELECT * FROM pg_last_committed_xact();
xid | timestamp | roident
-----+-------------------------------+---------
738 | 2026-02-23 13:17:19.263146+09 | 1
(1 row)
sub=# UPDATE employee SET salary = 10 WHERE id = 1;
UPDATE 1
sub=# SELECT * FROM pg_last_committed_xact();
xid | timestamp | roident
-----+-------------------------------+---------
739 | 2026-02-23 13:17:33.230773+09 | 0
(1 row)
```
3.
Ran pg_upgrade to upgrade the subscriber. The new cluster must also set
track_commit_timestamp to on.
4.
Confirmed commit timestamps could be migrated.
```
new=# SELECT * FROM pg_xact_commit_timestamp_origin('739');
timestamp | roident
-------------------------------+---------
2026-02-23 13:17:33.230773+09 | 0
(1 row)
```
5.
UPDATEd the tuple on the publisher.
```
pub=# UPDATE employee SET salary = 200 WHERE id = 1;
UPDATE 1
```
6.
update_origin_differs conflict was detected on the new subscriber.
```
LOG: conflict detected on relation "public.employee": conflict=update_origin_differs
DETAIL: Updating the row that was modified locally in transaction 739 at 2026-02-23 13:17:33.230773+09: local row (1, 10), remote row (1, 200), replica identity (id)=(1).
CONTEXT: processing remote data for replication origin "pg_16402" during message type "UPDATE" for replication target relation "public.employee" in transaction 745, finished at 0/018A4C6
```
One debatable point is whether we should include this in the TAP test. Personally
It's not necessary to simplify the test; either one is OK.
Not sure it is OK, but I created updated patches and considered a commit message.
0001 was not changed from the original, and 0002 addressed comments from you and
contained the commit message. For now I added my name as one of the author, but
OK to be listed as reviewer. Most of parts were written by Sergey.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
Attachments:
[application/octet-stream] v9-0001-Migration-of-the-pg_commit_ts-directory.patch (8.1K, ../../OS9PR01MB12149F6E69BDF8ED6EF6F467AF577A@OS9PR01MB12149.jpnprd01.prod.outlook.com/2-v9-0001-Migration-of-the-pg_commit_ts-directory.patch)
download | inline diff:
From 596135c7e78d47d577c115cb56d62b98d82999f7 Mon Sep 17 00:00:00 2001
From: Sergey Levin <[email protected]>
Date: Sat, 4 Oct 2025 20:22:46 +0500
Subject: [PATCH v9 1/2] Migration of the pg_commit_ts directory
---
src/bin/pg_upgrade/check.c | 24 +++++++
src/bin/pg_upgrade/controldata.c | 20 ++++++
src/bin/pg_upgrade/meson.build | 1 +
src/bin/pg_upgrade/pg_upgrade.c | 29 +++++++-
src/bin/pg_upgrade/pg_upgrade.h | 2 +
.../pg_upgrade/t/008_transfer_commit_ts.pl | 67 +++++++++++++++++++
6 files changed, 140 insertions(+), 3 deletions(-)
create mode 100644 src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 5c73773bf0e..f10753b342e 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -34,6 +34,7 @@ static void check_new_cluster_replication_slots(void);
static void check_new_cluster_subscription_configuration(void);
static void check_old_cluster_for_valid_slots(void);
static void check_old_cluster_subscription_state(void);
+static void check_new_cluster_pg_commit_ts(void);
/*
* DataTypesUsageChecks - definitions of data type checks for the old cluster
@@ -782,9 +783,32 @@ check_new_cluster(void)
check_new_cluster_replication_slots();
check_new_cluster_subscription_configuration();
+
+ check_new_cluster_pg_commit_ts();
+
}
+void
+check_new_cluster_pg_commit_ts(void)
+{
+ PGconn *conn;
+ PGresult *res;
+ bool track_commit_timestamp_on;
+
+ prep_status("Checking for new cluster configuration for commit timestamp");
+ conn = connectToServer(&new_cluster, "template1");
+ res = executeQueryOrDie(conn, "SELECT setting FROM pg_settings "
+ "WHERE name = 'track_commit_timestamp'");
+ track_commit_timestamp_on = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
+ PQclear(res);
+ PQfinish(conn);
+ if (!track_commit_timestamp_on &&
+ old_cluster.controldata.chkpnt_newstCommitTsxid > 0)
+ pg_fatal("\"track_commit_timestamp\" must be \"on\" but is set to \"off\"");
+
+ check_ok();
+}
void
report_clusters_compatible(void)
{
diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c
index aa6e8b4de5d..fa8b28adf43 100644
--- a/src/bin/pg_upgrade/controldata.c
+++ b/src/bin/pg_upgrade/controldata.c
@@ -321,6 +321,26 @@ get_control_data(ClusterInfo *cluster)
cluster->controldata.chkpnt_nxtmulti = str2uint(p);
got_multi = true;
}
+ else if ((p = strstr(bufin, "Latest checkpoint's oldestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_oldstCommitTsxid = str2uint(p);
+ }
+ else if ((p = strstr(bufin, "Latest checkpoint's newestCommitTsXid:")) != NULL)
+ {
+ p = strchr(p, ':');
+
+ if (p == NULL || strlen(p) <= 1)
+ pg_fatal("%d: controldata retrieval problem", __LINE__);
+
+ p++; /* remove ':' char */
+ cluster->controldata.chkpnt_newstCommitTsxid = str2uint(p);
+ }
else if ((p = strstr(bufin, "Latest checkpoint's oldestXID:")) != NULL)
{
p = strchr(p, ':');
diff --git a/src/bin/pg_upgrade/meson.build b/src/bin/pg_upgrade/meson.build
index 49b1b624f25..b27477c3f8a 100644
--- a/src/bin/pg_upgrade/meson.build
+++ b/src/bin/pg_upgrade/meson.build
@@ -51,6 +51,7 @@ tests += {
't/005_char_signedness.pl',
't/006_transfer_modes.pl',
't/007_multixact_conversion.pl',
+ 't/008_transfer_commit_ts.pl',
],
'test_kwargs': {'priority': 40}, # pg_upgrade tests are slow
},
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 2127d297bfe..b761f6c86c4 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -773,6 +773,10 @@ copy_subdir_files(const char *old_subdir, const char *new_subdir)
static void
copy_xact_xlog_xid(void)
{
+ bool is_copy_commit_ts;
+ uint32 oldest_xid,
+ newest_xid;
+
/*
* Copy old commit logs to new data dir. pg_clog has been renamed to
* pg_xact in post-10 clusters.
@@ -782,6 +786,22 @@ copy_xact_xlog_xid(void)
GET_MAJOR_VERSION(new_cluster.major_version) <= 906 ?
"pg_clog" : "pg_xact");
+ /*
+ * Copy old commit_timestamp data to new, if available.
+ */
+ is_copy_commit_ts =
+ (old_cluster.controldata.chkpnt_oldstCommitTsxid > 0 &&
+ old_cluster.controldata.chkpnt_newstCommitTsxid > 0);
+
+ if (is_copy_commit_ts)
+ {
+ copy_subdir_files("pg_commit_ts", "pg_commit_ts");
+ oldest_xid = old_cluster.controldata.chkpnt_oldstCommitTsxid;
+ newest_xid = old_cluster.controldata.chkpnt_newstCommitTsxid;
+ }
+ else
+ oldest_xid = newest_xid = old_cluster.controldata.chkpnt_nxtxid;
+
prep_status("Setting oldest XID for new cluster");
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -u %u \"%s\"",
@@ -799,12 +819,15 @@ copy_xact_xlog_xid(void)
"\"%s/pg_resetwal\" -f -e %u \"%s\"",
new_cluster.bindir, old_cluster.controldata.chkpnt_nxtepoch,
new_cluster.pgdata);
- /* must reset commit timestamp limits also */
+
+ /*
+ * must reset commit timestamp limits also or copy from the old cluster
+ */
exec_prog(UTILITY_LOG_FILE, NULL, true, true,
"\"%s/pg_resetwal\" -f -c %u,%u \"%s\"",
new_cluster.bindir,
- old_cluster.controldata.chkpnt_nxtxid,
- old_cluster.controldata.chkpnt_nxtxid,
+ oldest_xid,
+ newest_xid,
new_cluster.pgdata);
check_ok();
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index ec018e4f292..938413521cd 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -245,6 +245,8 @@ typedef struct
uint64 chkpnt_nxtmxoff;
uint32 chkpnt_oldstMulti;
uint32 chkpnt_oldstxid;
+ uint32 chkpnt_oldstCommitTsxid;
+ uint32 chkpnt_newstCommitTsxid;
uint32 align;
uint32 blocksz;
uint32 largesz;
diff --git a/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl b/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
new file mode 100644
index 00000000000..a9f0ff4ba8f
--- /dev/null
+++ b/src/bin/pg_upgrade/t/008_transfer_commit_ts.pl
@@ -0,0 +1,67 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Tests for transfer pg_commit_ts directory.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Can be changed to test the other modes
+my $mode = $ENV{PG_TEST_PG_UPGRADE_MODE} || '--copy';
+
+# Initialize old cluster
+my $old = PostgreSQL::Test::Cluster->new('old');
+$old->init;
+$old->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+$old->start;
+my $resold = $old->safe_psql(
+ 'postgres', qq{
+ create table a(a int);
+ select xid,timestamp from pg_last_committed_xact();
+});
+
+my ($xid) = $resold =~ /\s*(\d+)\s*\|.*/;
+$old->stop;
+
+# Initialize new cluster
+my $new = PostgreSQL::Test::Cluster->new('new');
+$new->init;
+
+# Setup a common pg_upgrade command to be used by all the test cases
+my @pg_upgrade_cmd = (
+ 'pg_upgrade', '--no-sync',
+ '--old-datadir' => $old->data_dir,
+ '--new-datadir' => $new->data_dir,
+ '--old-bindir' => $old->config_data('--bindir'),
+ '--new-bindir' => $new->config_data('--bindir'),
+ '--socketdir' => $new->host,
+ '--old-port' => $old->port,
+ '--new-port' => $new->port,
+ $mode);
+
+# In a VPATH build, we'll be started in the source directory, but we want
+# to run pg_upgrade in the build directory so that any files generated finish
+# in it, like delete_old_cluster.{sh,bat}.
+chdir ${PostgreSQL::Test::Utils::tmp_check};
+
+command_checks_all(
+ [@pg_upgrade_cmd], 1,
+ [qr{"track_commit_timestamp" must be "on" but is set to "off"}], [],
+ 'run of pg_upgrade for mismatch parameter track_commit_timestamp');
+
+$new->append_conf('postgresql.conf', 'track_commit_timestamp = on');
+
+command_ok([@pg_upgrade_cmd], 'run of pg_upgrade ok');
+
+$new->start;
+my $resnew = $new->safe_psql(
+ 'postgres', qq{
+ select $xid,pg_xact_commit_timestamp(${xid}::text::xid);
+});
+$new->stop;
+ok($resold eq $resnew, "timestamp transferred successfully");
+
+done_testing();
--
2.43.0
[application/octet-stream] v9-0002-pg_upgrade-transfer-commit-timestamps-to-the-new-.patch (4.6K, ../../OS9PR01MB12149F6E69BDF8ED6EF6F467AF577A@OS9PR01MB12149.jpnprd01.prod.outlook.com/3-v9-0002-pg_upgrade-transfer-commit-timestamps-to-the-new-.patch)
download | inline diff:
From dcc2e14b3fc4a738a350dd193878b8365fb26ea7 Mon Sep 17 00:00:00 2001
From: Hayato Kuroda <[email protected]>
Date: Mon, 23 Feb 2026 12:14:40 +0900
Subject: [PATCH v9 2/2] pg_upgrade: transfer commit timestamps to the new
cluster
This commit preserves commit timestamps during an upgrade. The advantage of this
commit is not only to provide committed transaction information after the upgrade,
but also to allow detecting {UPDATE|DELETE}_ORIGIN_DIFFERS conflicts for tuples
that were modified before the upgrade.
Files in the pg_commit_ts directory are copied when track_commit_timestamp=on.
Also, pg_resetwal specifies the oldest and newest transaction IDs to the new
cluster.
If the old cluster enables tracking commit timestamps but the new cluster does
not, the pg_upgrade fails to avoid missing them.
Author: Sergey Levin <[email protected]>
Author: Hayato Kuroda <[email protected]>
Reviewed-by: Maxim Orlov <orlovmg.gmail.com>
---
doc/src/sgml/logical-replication.sgml | 8 ++++----
src/bin/pg_upgrade/check.c | 6 +++---
src/bin/pg_upgrade/pg_upgrade.c | 12 +++++-------
3 files changed, 12 insertions(+), 14 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 5028fe9af09..3c0db3ab233 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -2814,11 +2814,11 @@ CONTEXT: processing remote data for replication origin "pg_16395" during "INSER
<note>
<para>
- Commit timestamps and origin data are not preserved during the upgrade.
- As a result, even if
+ Parameters for physical replication slots are not preserved during the
+ upgrade. As a result, even if
<link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link>
- is enabled, the upgraded subscriber may be unable to detect conflicts or
- log relevant commit timestamps and origins when applying changes from the
+ is enabled, the upgraded subscriber may be unable to detect
+ <literal>update_deleted</literal> conflicts when applying changes from the
publisher occurred before the upgrade. Additionally, immediately after the
upgrade, the vacuum may remove the deleted rows that are required for
conflict detection. This can affect the changes that were not replicated
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index f10753b342e..32352752095 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -793,17 +793,17 @@ check_new_cluster_pg_commit_ts(void)
{
PGconn *conn;
PGresult *res;
- bool track_commit_timestamp_on;
+ bool commit_ts_is_enabled;
prep_status("Checking for new cluster configuration for commit timestamp");
conn = connectToServer(&new_cluster, "template1");
res = executeQueryOrDie(conn, "SELECT setting FROM pg_settings "
"WHERE name = 'track_commit_timestamp'");
- track_commit_timestamp_on = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
+ commit_ts_is_enabled = strcmp(PQgetvalue(res, 0, 0), "on") == 0;
PQclear(res);
PQfinish(conn);
- if (!track_commit_timestamp_on &&
+ if (!commit_ts_is_enabled &&
old_cluster.controldata.chkpnt_newstCommitTsxid > 0)
pg_fatal("\"track_commit_timestamp\" must be \"on\" but is set to \"off\"");
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index b761f6c86c4..bfec731ce6b 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -216,13 +216,11 @@ main(int argc, char **argv)
* as it only retains the dead tuples. It is created here for consistency.
* Note that the new conflict detection slot uses the latest transaction
* ID as xmin, so it cannot protect dead tuples that existed before the
- * upgrade. Additionally, commit timestamps and origin data are not
- * preserved during the upgrade. So, even after creating the slot, the
- * upgraded subscriber may be unable to detect conflicts or log relevant
- * commit timestamps and origins when applying changes from the publisher
- * occurred before the upgrade especially if those changes were not
- * replicated. It can only protect tuples that might be deleted after the
- * new cluster starts.
+ * upgrade. It means even after creating the slot, the upgraded subscriber
+ * may be unable to detect update_deleted conflicts when applying changes
+ * from the publisher occurred before the upgrade especially if those
+ * changes were not replicated. It can only protect tuples that might be
+ * deleted after the new cluster starts.
*/
if (migrate_logical_slots || old_cluster.sub_retain_dead_tuples)
{
--
2.43.0
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 11:34 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-23 04:41 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-02-23 13:33 ` ls7777 <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: ls7777 @ 2026-02-23 13:33 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; 'Amit Kapila' <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>
<div><div>Hello.</div><div> </div><div>Thanks for updating the patch. I am using a translator and will not be able to edit the documentation and comments correctly.</div><div> </div></div><div> </div><div>----------------</div><div>Кому: 'Amit Kapila' ([email protected]);</div><div>Копия: [email protected], [email protected];</div><div>Тема: Patch for migration of the pg_commit_ts directory;</div><div>23.02.2026, 09:41, "Hayato Kuroda (Fujitsu)" <[email protected]>:</div><blockquote><p>Dear Amit,<br /> </p><blockquote> > Right, and code comments [a] should be also updated.<br /> ><br /> <br /> So, leaving aside update_delete, copying commit_ts could be helpful to<br /> detect some other conflicts. You may want to once test the same and<br /> show it here as part of use case establishment.</blockquote><p><br />I confirmed that {update|delete}_origin_differs could be detected with the<br />following steps.<br /><br />0.<br />Constructed pub-sub replication system. track_commit_timestamp=on was set on the<br />subscriber, and the table below was defined on both clusters<br /><br />```<br /> Table "public.employee"<br /> Column | Type | Collation | Nullable | Default<br />--------+---------+-----------+----------+---------<br /> id | integer | | not null |<br /> salary | integer | | |<br />Indexes:<br /> "employee_pkey" PRIMARY KEY, btree (id)<br />```<br /><br /><br />1.<br />Inserted a tuple on the publisher<br /><br />```<br />pub=# INSERT INTO employee VALUES (1, 100);<br />INSERT 0 1<br />```<br /><br />2.<br />UPDATEd the replicated tuple on the subscriber. Confirmed that commit timestamps<br />were stored.<br /><br />```<br />sub=# SELECT * FROM pg_last_committed_xact();<br /> xid | timestamp | roident<br />-----+-------------------------------+---------<br /> 738 | 2026-02-23 13:17:19.263146+09 | 1<br />(1 row)<br />sub=# UPDATE employee SET salary = 10 WHERE id = 1;<br />UPDATE 1<br />sub=# SELECT * FROM pg_last_committed_xact();<br /> xid | timestamp | roident<br />-----+-------------------------------+---------<br /> 739 | 2026-02-23 13:17:33.230773+09 | 0<br />(1 row)<br />```<br /><br />3.<br />Ran pg_upgrade to upgrade the subscriber. The new cluster must also set<br />track_commit_timestamp to on.<br /><br />4.<br />Confirmed commit timestamps could be migrated.<br /><br />```<br />new=# SELECT * FROM pg_xact_commit_timestamp_origin('739');<br /> timestamp | roident<br />-------------------------------+---------<br /> 2026-02-23 13:17:33.230773+09 | 0<br />(1 row)<br />```<br /><br />5.<br />UPDATEd the tuple on the publisher.<br /><br />```<br />pub=# UPDATE employee SET salary = 200 WHERE id = 1;<br />UPDATE 1<br />```<br /><br />6.<br />update_origin_differs conflict was detected on the new subscriber.<br /><br />```<br />LOG: conflict detected on relation "public.employee": conflict=update_origin_differs<br />DETAIL: Updating the row that was modified locally in transaction 739 at 2026-02-23 13:17:33.230773+09: local row (1, 10), remote row (1, 200), replica identity (id)=(1).<br />CONTEXT: processing remote data for replication origin "pg_16402" during message type "UPDATE" for replication target relation "public.employee" in transaction 745, finished at 0/018A4C6<br />```<br /><br />One debatable point is whether we should include this in the TAP test. Personally<br />It's not necessary to simplify the test; either one is OK.<br /><br /><br />Not sure it is OK, but I created updated patches and considered a commit message.<br />0001 was not changed from the original, and 0002 addressed comments from you and<br />contained the commit message. For now I added my name as one of the author, but<br />OK to be listed as reviewer. Most of parts were written by Sergey.<br /><br />Best regards,<br />Hayato Kuroda<br />FUJITSU LIMITED<br /> </p></blockquote>
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-05-27 05:24 ` Hayato Kuroda (Fujitsu) <[email protected]>
2026-05-29 16:08 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-05-27 05:24 UTC (permalink / raw)
To: 'ls7777' <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; Masahiko Sawada <[email protected]>
Dear Sergey,
While considering the feature once again, I came up with another issue.
When pg_upgrade migrates objects to a new instance, it normally checks whether
the new instance does not have existing objects. E.g., check_new_cluster_is_empty()
checks if no tables exist, and check_new_cluster_replication_slots() ensures
there are no logical slots.
Based on that, how should we handle the commit timestamp? Since the patch simply
copies the pg_commit_ts subdir, existing entries in the new cluster will be overwritten.
So we may have to check if the commit_ts entries are empty when the pg_upgrade
command runs, but not sure how to do that.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-05-27 05:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
@ 2026-05-29 16:08 ` ls7777 <[email protected]>
2026-06-01 01:25 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: ls7777 @ 2026-05-29 16:08 UTC (permalink / raw)
To: Hayato Kuroda (Fujitsu) <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; Masahiko Sawada <[email protected]>
<div>Hi,</div><div> </div><div><div>During copying, the copy_subdir_files function cleans up the target directory with the remove_new_subdir(new_subdir, true) function.</div><div> </div></div><div><br /></div><div><br /></div><div>----------------</div>
<div>Кому: 'ls7777' ([email protected]);<br /></div>
<div>Копия: [email protected], [email protected], [email protected], Masahiko Sawada ([email protected]);<br /></div>
<div>Тема: Patch for migration of the pg_commit_ts directory;<br /></div>
<div>27.05.2026, 10:25, "Hayato Kuroda (Fujitsu)" <[email protected]>:<br /></div>
<blockquote><p>Dear Sergey,<br /><br />While considering the feature once again, I came up with another issue.<br /><br />When pg_upgrade migrates objects to a new instance, it normally checks whether<br />the new instance does not have existing objects. E.g., check_new_cluster_is_empty()<br />checks if no tables exist, and check_new_cluster_replication_slots() ensures<br />there are no logical slots.<br /><br />Based on that, how should we handle the commit timestamp? Since the patch simply<br />copies the pg_commit_ts subdir, existing entries in the new cluster will be overwritten.<br />So we may have to check if the commit_ts entries are empty when the pg_upgrade<br />command runs, but not sure how to do that.<br /><br />Best regards,<br />Hayato Kuroda<br />FUJITSU LIMITED<br /> <br /></p></blockquote>
^ permalink raw reply [nested|flat] 13+ messages in thread
* RE: Patch for migration of the pg_commit_ts directory
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-05-27 05:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-05-29 16:08 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
@ 2026-06-01 01:25 ` Hayato Kuroda (Fujitsu) <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Hayato Kuroda (Fujitsu) @ 2026-06-01 01:25 UTC (permalink / raw)
To: 'ls7777' <[email protected]>; +Cc: pgsql-hackers; [email protected] <[email protected]>; [email protected] <[email protected]>; Masahiko Sawada <[email protected]>
Dear Sergey,
> During copying, the copy_subdir_files function cleans up the target
> directory with the remove_new_subdir(new_subdir, true) function.
Correct, the function can discard existing directory. But my point was that how
do we ENSURE it's OK to remove.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-06-01 01:25 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-22 09:40 [PATCH v26 07/10] Add Incremental View Maintenance support Yugo Nagata <[email protected]>
2025-04-03 18:27 Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-05 08:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-01-05 11:27 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-01-06 00:41 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 07:58 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-20 11:05 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-20 11:34 ` Re: Patch for migration of the pg_commit_ts directory Amit Kapila <[email protected]>
2026-02-23 04:41 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-02-23 13:33 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-05-27 05:24 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
2026-05-29 16:08 ` Re: Patch for migration of the pg_commit_ts directory ls7777 <[email protected]>
2026-06-01 01:25 ` RE: Patch for migration of the pg_commit_ts directory Hayato Kuroda (Fujitsu) <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox