From 07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65 Mon Sep 17 00:00:00 2001 From: Robert Haas Date: Fri, 8 Nov 2013 12:30:43 -0500 Subject: [PATCH] Add the notion of REPLICA IDENTITY for a table. Pending patches for logical replication will use this to determine which columns of a tuple ought to be considered as its candidate key. Andres Freund, with minor, mostly cosmetic adjustments by me --- doc/src/sgml/catalogs.sgml | 24 ++ doc/src/sgml/ref/alter_table.sgml | 19 ++ src/backend/catalog/heap.c | 1 + src/backend/catalog/index.c | 1 + src/backend/commands/tablecmds.c | 222 ++++++++++++++++++ src/backend/nodes/copyfuncs.c | 14 ++ src/backend/nodes/equalfuncs.c | 12 + src/backend/parser/gram.y | 40 ++++ src/backend/utils/cache/relcache.c | 53 ++++- src/bin/pg_dump/pg_dump.c | 143 +++++++++-- src/bin/pg_dump/pg_dump.h | 2 + src/bin/psql/describe.c | 58 ++++- src/bin/psql/tab-complete.c | 31 ++- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_class.h | 32 ++- src/include/catalog/pg_index.h | 16 +- src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 8 + src/include/utils/rel.h | 7 + .../regress/expected/replica_identity.out | 183 +++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/replica_identity.sql | 79 +++++++ 23 files changed, 902 insertions(+), 49 deletions(-) create mode 100644 src/test/regress/expected/replica_identity.out create mode 100644 src/test/regress/sql/replica_identity.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 9af4697a47..9388df5ac2 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1862,6 +1862,19 @@ relations other than some materialized views) + + relreplident + char + + + Columns used to form replica identity for rows: + d = default (primary key, if any), + n = nothing, + f = all columns + i = index with indisreplident set, or default + + + relfrozenxid xid @@ -3657,6 +3670,17 @@ + + indisreplident + bool + + + If true this index has been chosen as replica identity + using ALTER TABLE ... REPLICA IDENTITY USING INDEX + ... + + + indkey int2vector diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 2609d4a8ea..89649a2aa4 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -69,6 +69,7 @@ ALTER TABLE [ IF EXISTS ] name NOT OF OWNER TO new_owner SET TABLESPACE new_tablespace + REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING} and table_constraint_using_index is: @@ -579,6 +580,24 @@ ALTER TABLE [ IF EXISTS ] name + + REPLICA IDENTITY + + + This form changes the information which is written to the write-ahead log + to identify rows which are updated or deleted. This option has no effect + except when logical replication is in use. DEFAULT records the + old values of the columns of the primary key, if any. USING INDEX + records the old values of the columns covered by the named index, which + must be unique, not partial, not deferrable, and include only columns marked + NOT NULL. FULL records the old values of all columns + in the row. NOTHING records no information about the old row. + In all cases, no old values are logged unless at least one of the columns + that would be logged differs between the old and new versions of the row. + + + + RENAME diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 64ca3121b3..a910f81666 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -793,6 +793,7 @@ InsertPgClassTuple(Relation pg_class_desc, values[Anum_pg_class_relhastriggers - 1] = BoolGetDatum(rd_rel->relhastriggers); values[Anum_pg_class_relhassubclass - 1] = BoolGetDatum(rd_rel->relhassubclass); values[Anum_pg_class_relispopulated - 1] = BoolGetDatum(rd_rel->relispopulated); + values[Anum_pg_class_relreplident - 1] = CharGetDatum(rd_rel->relreplident); values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid); values[Anum_pg_class_relminmxid - 1] = MultiXactIdGetDatum(rd_rel->relminmxid); if (relacl != (Datum) 0) diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 826e504bb4..02752406f5 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -614,6 +614,7 @@ UpdateIndexRelation(Oid indexoid, /* we set isvalid and isready the same way */ values[Anum_pg_index_indisready - 1] = BoolGetDatum(isvalid); values[Anum_pg_index_indislive - 1] = BoolGetDatum(true); + values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 670af18f4c..c8fa39d025 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -399,6 +399,7 @@ static void ATExecDropInherit(Relation rel, RangeVar *parent, LOCKMODE lockmode) static void drop_parent_dependency(Oid relid, Oid refclassid, Oid refobjid); static void ATExecAddOf(Relation rel, const TypeName *ofTypename, LOCKMODE lockmode); static void ATExecDropOf(Relation rel, LOCKMODE lockmode); +static void ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode); static void ATExecGenericOptions(Relation rel, List *options); static void copy_relation_data(SMgrRelation rel, SMgrRelation dst, @@ -2809,6 +2810,7 @@ AlterTableGetLockLevel(List *cmds) case AT_DisableTrigUser: case AT_AddIndex: /* from ADD CONSTRAINT */ case AT_AddIndexConstraint: + case AT_ReplicaIdentity: cmd_lockmode = ShareRowExclusiveLock; break; @@ -3140,6 +3142,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, cmd->subtype = AT_ValidateConstraintRecurse; pass = AT_PASS_MISC; break; + case AT_ReplicaIdentity: /* REPLICA IDENTITY ... */ + ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW); + pass = AT_PASS_MISC; + /* This command never recurses */ + /* No command-specific prep needed */ + break; case AT_EnableTrig: /* ENABLE TRIGGER variants */ case AT_EnableAlwaysTrig: case AT_EnableReplicaTrig: @@ -3440,6 +3448,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, case AT_DropOf: ATExecDropOf(rel, lockmode); break; + case AT_ReplicaIdentity: + ATExecReplicaIdentity(rel, (ReplicaIdentityStmt *) cmd->def, lockmode); + break; case AT_GenericOptions: ATExecGenericOptions(rel, (List *) cmd->def); break; @@ -10009,6 +10020,217 @@ ATExecDropOf(Relation rel, LOCKMODE lockmode) heap_close(relationRelation, RowExclusiveLock); } +/* + * relation_mark_replica_identity: Update a table's replica identity + * + * Iff ri_type = REPLICA_IDENTITY_INDEX, indexOid must be the Oid of a suitable + * index. Otherwise, it should be InvalidOid. + */ +static void +relation_mark_replica_identity(Relation rel, char ri_type, Oid indexOid, + bool is_internal) +{ + Relation pg_index; + Relation pg_class; + HeapTuple pg_class_tuple; + HeapTuple pg_index_tuple; + Form_pg_class pg_class_form; + Form_pg_index pg_index_form; + + ListCell *index; + + /* + * Check whether relreplident has changed, and update it if so. + */ + pg_class = heap_open(RelationRelationId, RowExclusiveLock); + pg_class_tuple = SearchSysCacheCopy1(RELOID, + ObjectIdGetDatum(RelationGetRelid(rel))); + if (!HeapTupleIsValid(pg_class_tuple)) + elog(ERROR, "cache lookup failed for relation \"%s\"", + RelationGetRelationName(rel)); + pg_class_form = (Form_pg_class) GETSTRUCT(pg_class_tuple); + if (pg_class_form->relreplident != ri_type) + { + pg_class_form->relreplident = ri_type; + simple_heap_update(pg_class, &pg_class_tuple->t_self, pg_class_tuple); + CatalogUpdateIndexes(pg_class, pg_class_tuple); + } + heap_close(pg_class, RowExclusiveLock); + heap_freetuple(pg_class_tuple); + + /* + * Check whether the correct index is marked indisreplident; if so, we're + * done. + */ + if (OidIsValid(indexOid)) + { + Assert(ri_type == REPLICA_IDENTITY_INDEX); + + pg_index_tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexOid)); + if (!HeapTupleIsValid(pg_index_tuple)) + elog(ERROR, "cache lookup failed for index %u", indexOid); + pg_index_form = (Form_pg_index) GETSTRUCT(pg_index_tuple); + + if (pg_index_form->indisreplident) + { + ReleaseSysCache(pg_index_tuple); + return; + } + ReleaseSysCache(pg_index_tuple); + } + + /* + * Clear the indisreplident flag from any index that had it previously, and + * set it for any index that should have it now. + */ + pg_index = heap_open(IndexRelationId, RowExclusiveLock); + foreach(index, RelationGetIndexList(rel)) + { + Oid thisIndexOid = lfirst_oid(index); + bool dirty = false; + + pg_index_tuple = SearchSysCacheCopy1(INDEXRELID, + ObjectIdGetDatum(thisIndexOid)); + if (!HeapTupleIsValid(pg_index_tuple)) + elog(ERROR, "cache lookup failed for index %u", thisIndexOid); + pg_index_form = (Form_pg_index) GETSTRUCT(pg_index_tuple); + + /* + * Unset the bit if set. We know it's wrong because we checked this + * earlier. + */ + if (pg_index_form->indisreplident) + { + dirty = true; + pg_index_form->indisreplident = false; + } + else if (thisIndexOid == indexOid) + { + dirty = true; + pg_index_form->indisreplident = true; + } + + if (dirty) + { + simple_heap_update(pg_index, &pg_index_tuple->t_self, pg_index_tuple); + CatalogUpdateIndexes(pg_index, pg_index_tuple); + InvokeObjectPostAlterHookArg(IndexRelationId, thisIndexOid, 0, + InvalidOid, is_internal); + } + heap_freetuple(pg_index_tuple); + } + + heap_close(pg_index, RowExclusiveLock); +} + +/* + * ALTER TABLE REPLICA IDENTITY ... + */ +static void +ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode) +{ + Oid indexOid; + Relation indexRel; + int key; + + if (stmt->identity_type == REPLICA_IDENTITY_DEFAULT) + { + relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true); + return; + } + else if (stmt->identity_type == REPLICA_IDENTITY_FULL) + { + relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true); + return; + } + else if (stmt->identity_type == REPLICA_IDENTITY_NOTHING) + { + relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true); + return; + } + else if (stmt->identity_type == REPLICA_IDENTITY_INDEX) + { + /* fallthrough */; + } + else + elog(ERROR, "unexpected identity type %u", stmt->identity_type); + + + /* Check that the index exists */ + indexOid = get_relname_relid(stmt->name, rel->rd_rel->relnamespace); + if (!OidIsValid(indexOid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("index \"%s\" for table \"%s\" does not exist", + stmt->name, RelationGetRelationName(rel)))); + + indexRel = index_open(indexOid, ShareLock); + + /* Check that the index is on the relation we're altering. */ + if (indexRel->rd_index == NULL || + indexRel->rd_index->indrelid != RelationGetRelid(rel)) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("\"%s\" is not an index for table \"%s\"", + RelationGetRelationName(indexRel), + RelationGetRelationName(rel)))); + /* The AM must support uniqueness, and the index must in fact be unique. */ + if (!indexRel->rd_am->amcanunique || !indexRel->rd_index->indisunique) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("cannot use non-unique index \"%s\" as replica identity", + RelationGetRelationName(indexRel)))); + /* Deferred indexes are not guaranteed to be always unique. */ + if (!indexRel->rd_index->indimmediate) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use non-immediate index \"%s\" as replica identity", + RelationGetRelationName(indexRel)))); + /* Expression indexes aren't supported. */ + if (RelationGetIndexExpressions(indexRel) != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use expression index \"%s\" as replica identity", + RelationGetRelationName(indexRel)))); + /* Predicate indexes aren't supported. */ + if (RelationGetIndexPredicate(indexRel) != NIL) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use partial index \"%s\" as replica identity", + RelationGetRelationName(indexRel)))); + /* And neither are invalid indexes. */ + if (!IndexIsValid(indexRel->rd_index)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot use invalid index \"%s\" as replica identity", + RelationGetRelationName(indexRel)))); + + /* Check index for nullable columns. */ + for (key = 0; key < indexRel->rd_index->indnatts; key++) + { + int16 attno = indexRel->rd_index->indkey.values[key]; + Form_pg_attribute attr; + + /* Of the system columns, only oid is indexable. */ + if (attno <= 0 && attno != ObjectIdAttributeNumber) + elog(ERROR, "internal column %u in unique index \"%s\"", + attno, RelationGetRelationName(indexRel)); + + attr = rel->rd_att->attrs[attno - 1]; + if (!attr->attnotnull) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("index \"%s\" cannot be used as replica identity because column \"%s\" is nullable", + RelationGetRelationName(indexRel), + NameStr(attr->attname)))); + } + + /* This index is suitable for use as a replica identity. Mark it. */ + relation_mark_replica_identity(rel, stmt->identity_type, indexOid, true); + + index_close(indexRel, NoLock); +} + /* * ALTER FOREIGN TABLE OPTIONS (...) */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 65f3b984d6..1733da633a 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3270,6 +3270,17 @@ _copyRefreshMatViewStmt(const RefreshMatViewStmt *from) return newnode; } +static ReplicaIdentityStmt * +_copyReplicaIdentityStmt(const ReplicaIdentityStmt *from) +{ + ReplicaIdentityStmt *newnode = makeNode(ReplicaIdentityStmt); + + COPY_SCALAR_FIELD(identity_type); + COPY_STRING_FIELD(name); + + return newnode; +} + static CreateSeqStmt * _copyCreateSeqStmt(const CreateSeqStmt *from) { @@ -4343,6 +4354,9 @@ copyObject(const void *from) case T_RefreshMatViewStmt: retval = _copyRefreshMatViewStmt(from); break; + case T_ReplicaIdentityStmt: + retval = _copyReplicaIdentityStmt(from); + break; case T_CreateSeqStmt: retval = _copyCreateSeqStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 4c9b05e1e4..7b29812b69 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1537,6 +1537,15 @@ _equalRefreshMatViewStmt(const RefreshMatViewStmt *a, const RefreshMatViewStmt * return true; } +static bool +_equalReplicaIdentityStmt(const ReplicaIdentityStmt *a, const ReplicaIdentityStmt *b) +{ + COMPARE_SCALAR_FIELD(identity_type); + COMPARE_STRING_FIELD(name); + + return true; +} + static bool _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) { @@ -2813,6 +2822,9 @@ equal(const void *a, const void *b) case T_RefreshMatViewStmt: retval = _equalRefreshMatViewStmt(a, b); break; + case T_ReplicaIdentityStmt: + retval = _equalReplicaIdentityStmt(a, b); + break; case T_CreateSeqStmt: retval = _equalCreateSeqStmt(a, b); break; diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 363c603848..8dc4b1cc53 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -255,6 +255,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type add_drop opt_asc_desc opt_nulls_order %type alter_table_cmd alter_type_cmd opt_collate_clause + replica_identity %type alter_table_cmds alter_type_cmds %type opt_drop_behavior @@ -2178,6 +2179,14 @@ alter_table_cmd: n->def = (Node *)$2; $$ = (Node *)n; } + /* ALTER TABLE REPLICA IDENTITY */ + | REPLICA IDENTITY_P replica_identity + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_ReplicaIdentity; + n->def = $3; + $$ = (Node *)n; + } | alter_generic_options { AlterTableCmd *n = makeNode(AlterTableCmd); @@ -2215,6 +2224,37 @@ alter_using: | /* EMPTY */ { $$ = NULL; } ; +replica_identity: + NOTHING + { + ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt); + n->identity_type = REPLICA_IDENTITY_NOTHING; + n->name = NULL; + $$ = (Node *) n; + } + | FULL + { + ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt); + n->identity_type = REPLICA_IDENTITY_FULL; + n->name = NULL; + $$ = (Node *) n; + } + | DEFAULT + { + ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt); + n->identity_type = REPLICA_IDENTITY_DEFAULT; + n->name = NULL; + $$ = (Node *) n; + } + | USING INDEX name + { + ReplicaIdentityStmt *n = makeNode(ReplicaIdentityStmt); + n->identity_type = REPLICA_IDENTITY_INDEX; + n->name = $3; + $$ = (Node *) n; + } +; + reloptions: '(' reloption_list ')' { $$ = $2; } ; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index b4cc6ad221..9d8caffcac 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -1454,6 +1454,7 @@ formrdesc(const char *relationName, Oid relationReltype, /* ... and they're always populated, too */ relation->rd_rel->relispopulated = true; + relation->rd_rel->relreplident = REPLICA_IDENTITY_NOTHING; relation->rd_rel->relpages = 0; relation->rd_rel->reltuples = 0; relation->rd_rel->relallvisible = 0; @@ -2664,6 +2665,13 @@ RelationBuildLocalRelation(const char *relname, else rel->rd_rel->relispopulated = true; + /* system relations and non-table objects don't have one */ + if (!IsSystemNamespace(relnamespace) && + (relkind == RELKIND_RELATION || relkind == RELKIND_MATVIEW)) + rel->rd_rel->relreplident = REPLICA_IDENTITY_DEFAULT; + else + rel->rd_rel->relreplident = REPLICA_IDENTITY_NOTHING; + /* * Insert relation physical and logical identifiers (OIDs) into the right * places. For a mapped relation, we set relfilenode to zero and rely on @@ -3462,7 +3470,10 @@ RelationGetIndexList(Relation relation) ScanKeyData skey; HeapTuple htup; List *result; - Oid oidIndex; + char replident = relation->rd_rel->relreplident; + Oid oidIndex = InvalidOid; + Oid pkeyIndex = InvalidOid; + Oid candidateIndex = InvalidOid; MemoryContext oldcxt; /* Quick exit if we already computed the list. */ @@ -3519,17 +3530,45 @@ RelationGetIndexList(Relation relation) Assert(!isnull); indclass = (oidvector *) DatumGetPointer(indclassDatum); - /* Check to see if it is a unique, non-partial btree index on OID */ - if (IndexIsValid(index) && - index->indnatts == 1 && - index->indisunique && index->indimmediate && + /* + * Invalid, non-unique, non-immediate or predicate indexes aren't + * interesting for neither oid indexes nor replication identity + * indexes, so don't check them. + */ + if (!IndexIsValid(index) || !index->indisunique || + !index->indimmediate || + !heap_attisnull(htup, Anum_pg_index_indpred)) + continue; + + /* Check to see if is a usable btree index on OID */ + if (index->indnatts == 1 && index->indkey.values[0] == ObjectIdAttributeNumber && - indclass->values[0] == OID_BTREE_OPS_OID && - heap_attisnull(htup, Anum_pg_index_indpred)) + indclass->values[0] == OID_BTREE_OPS_OID) oidIndex = index->indexrelid; + + /* always prefer primary keys */ + if (index->indisprimary) + pkeyIndex = index->indexrelid; + + /* explicitly chosen index */ + if (index->indisreplident) + candidateIndex = index->indexrelid; } systable_endscan(indscan); + + /* primary key */ + if (replident == REPLICA_IDENTITY_DEFAULT && + OidIsValid(pkeyIndex)) + relation->rd_replidindex = pkeyIndex; + /* explicitly chosen index */ + else if (replident == REPLICA_IDENTITY_INDEX && + OidIsValid(candidateIndex)) + relation->rd_replidindex = candidateIndex; + /* nothing */ + else + relation->rd_replidindex = InvalidOid; + heap_close(indrel, AccessShareLock); /* Now save a copy of the completed list in the relcache entry. */ diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index bc70dd6a6e..9478aacf83 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -4221,6 +4221,7 @@ getTables(Archive *fout, int *numTables) int i_toastfrozenxid; int i_relpersistence; int i_relispopulated; + int i_relreplident; int i_owning_tab; int i_owning_col; int i_reltablespace; @@ -4253,7 +4254,7 @@ getTables(Archive *fout, int *numTables) * we cannot correctly identify inherited columns, owned sequences, etc. */ - if (fout->remoteVersion >= 90300) + if (fout->remoteVersion >= 90400) { /* * Left join to pick up dependency info linking sequences to their @@ -4268,7 +4269,46 @@ getTables(Archive *fout, int *numTables) "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " "c.relpersistence, c.relispopulated, " - "c.relpages, " + "c.relreplident, c.relpages, " + "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " + "d.refobjid AS owning_tab, " + "d.refobjsubid AS owning_col, " + "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, " + "array_to_string(array_remove(array_remove(c.reloptions,'check_option=local'),'check_option=cascaded'), ', ') AS reloptions, " + "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text " + "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, " + "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions " + "FROM pg_class c " + "LEFT JOIN pg_depend d ON " + "(c.relkind = '%c' AND " + "d.classid = c.tableoid AND d.objid = c.oid AND " + "d.objsubid = 0 AND " + "d.refclassid = c.tableoid AND d.deptype = 'a') " + "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " + "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') " + "ORDER BY c.oid", + username_subquery, + RELKIND_SEQUENCE, + RELKIND_RELATION, RELKIND_SEQUENCE, + RELKIND_VIEW, RELKIND_COMPOSITE_TYPE, + RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE); + } + else if (fout->remoteVersion >= 90300) + { + /* + * Left join to pick up dependency info linking sequences to their + * owning column, if any (note this dependency is AUTO as of 8.2) + */ + appendPQExpBuffer(query, + "SELECT c.tableoid, c.oid, c.relname, " + "c.relacl, c.relkind, c.relnamespace, " + "(%s c.relowner) AS rolname, " + "c.relchecks, c.relhastriggers, " + "c.relhasindex, c.relhasrules, c.relhasoids, " + "c.relfrozenxid, tc.oid AS toid, " + "tc.relfrozenxid AS tfrozenxid, " + "c.relpersistence, c.relispopulated, " + "'d' AS relreplident, c.relpages, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4307,7 +4347,7 @@ getTables(Archive *fout, int *numTables) "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " "c.relpersistence, 't' as relispopulated, " - "c.relpages, " + "'d' AS relreplident, c.relpages, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4344,7 +4384,7 @@ getTables(Archive *fout, int *numTables) "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "c.relpages, " + "'d' AS relreplident, c.relpages, " "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4380,7 +4420,7 @@ getTables(Archive *fout, int *numTables) "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "c.relpages, " + "'d' AS relreplident, c.relpages, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4416,7 +4456,7 @@ getTables(Archive *fout, int *numTables) "c.relfrozenxid, tc.oid AS toid, " "tc.relfrozenxid AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "c.relpages, " + "'d' AS relreplident, c.relpages, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4453,7 +4493,7 @@ getTables(Archive *fout, int *numTables) "0 AS toid, " "0 AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "relpages, " + "'d' AS relreplident, relpages, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4489,7 +4529,7 @@ getTables(Archive *fout, int *numTables) "0 AS toid, " "0 AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "relpages, " + "'d' AS relreplident, relpages, " "NULL AS reloftype, " "d.refobjid AS owning_tab, " "d.refobjsubid AS owning_col, " @@ -4521,7 +4561,7 @@ getTables(Archive *fout, int *numTables) "0 AS toid, " "0 AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "relpages, " + "'d' AS relreplident, relpages, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4548,7 +4588,7 @@ getTables(Archive *fout, int *numTables) "0 AS toid, " "0 AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "relpages, " + "'d' AS relreplident, relpages, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4585,7 +4625,7 @@ getTables(Archive *fout, int *numTables) "0 AS toid, " "0 AS tfrozenxid, " "'p' AS relpersistence, 't' as relispopulated, " - "0 AS relpages, " + "'d' AS relreplident, 0 AS relpages, " "NULL AS reloftype, " "NULL::oid AS owning_tab, " "NULL::int4 AS owning_col, " @@ -4634,6 +4674,7 @@ getTables(Archive *fout, int *numTables) i_toastfrozenxid = PQfnumber(res, "tfrozenxid"); i_relpersistence = PQfnumber(res, "relpersistence"); i_relispopulated = PQfnumber(res, "relispopulated"); + i_relreplident = PQfnumber(res, "relreplident"); i_relpages = PQfnumber(res, "relpages"); i_owning_tab = PQfnumber(res, "owning_tab"); i_owning_col = PQfnumber(res, "owning_col"); @@ -4678,6 +4719,7 @@ getTables(Archive *fout, int *numTables) tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0); tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0); tblinfo[i].relispopulated = (strcmp(PQgetvalue(res, i, i_relispopulated), "t") == 0); + tblinfo[i].relreplident = *(PQgetvalue(res, i, i_relreplident)); tblinfo[i].relpages = atoi(PQgetvalue(res, i, i_relpages)); tblinfo[i].frozenxid = atooid(PQgetvalue(res, i, i_relfrozenxid)); tblinfo[i].toast_oid = atooid(PQgetvalue(res, i, i_toastoid)); @@ -4863,6 +4905,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_indnkeys, i_indkey, i_indisclustered, + i_indisreplident, i_contype, i_conname, i_condeferrable, @@ -4909,7 +4952,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) * is not. */ resetPQExpBuffer(query); - if (fout->remoteVersion >= 90000) + if (fout->remoteVersion >= 90400) { /* * the test on indisready is necessary in 9.2, and harmless in @@ -4921,7 +4964,38 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " - "t.relpages, " + "i.indisreplident, t.relpages, " + "c.contype, c.conname, " + "c.condeferrable, c.condeferred, " + "c.tableoid AS contableoid, " + "c.oid AS conoid, " + "pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, " + "(SELECT spcname FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS tablespace, " + "array_to_string(t.reloptions, ', ') AS options " + "FROM pg_catalog.pg_index i " + "JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) " + "LEFT JOIN pg_catalog.pg_constraint c " + "ON (i.indrelid = c.conrelid AND " + "i.indexrelid = c.conindid AND " + "c.contype IN ('p','u','x')) " + "WHERE i.indrelid = '%u'::pg_catalog.oid " + "AND i.indisvalid AND i.indisready " + "ORDER BY indexname", + tbinfo->dobj.catId.oid); + } + else if (fout->remoteVersion >= 90000) + { + /* + * the test on indisready is necessary in 9.2, and harmless in + * earlier/later versions + */ + appendPQExpBuffer(query, + "SELECT t.tableoid, t.oid, " + "t.relname AS indexname, " + "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " + "t.relnatts AS indnkeys, " + "i.indkey, i.indisclustered, " + "false AS indisreplident, t.relpages, " "c.contype, c.conname, " "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " @@ -4948,7 +5022,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " - "t.relpages, " + "false AS indisreplident, t.relpages, " "c.contype, c.conname, " "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " @@ -4978,7 +5052,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " - "t.relpages, " + "false AS indisreplident, t.relpages, " "c.contype, c.conname, " "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " @@ -5007,7 +5081,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, i.indisclustered, " - "t.relpages, " + "false AS indisreplident, t.relpages, " "c.contype, c.conname, " "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " @@ -5036,7 +5110,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, false AS indisclustered, " - "t.relpages, " + "false AS indisreplident, t.relpages, " "CASE WHEN i.indisprimary THEN 'p'::char " "ELSE '0'::char END AS contype, " "t.relname AS conname, " @@ -5063,7 +5137,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) "pg_get_indexdef(i.indexrelid) AS indexdef, " "t.relnatts AS indnkeys, " "i.indkey, false AS indisclustered, " - "t.relpages, " + "false AS indisreplident, t.relpages, " "CASE WHEN i.indisprimary THEN 'p'::char " "ELSE '0'::char END AS contype, " "t.relname AS conname, " @@ -5092,6 +5166,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_indnkeys = PQfnumber(res, "indnkeys"); i_indkey = PQfnumber(res, "indkey"); i_indisclustered = PQfnumber(res, "indisclustered"); + i_indisreplident = PQfnumber(res, "indisreplident"); i_relpages = PQfnumber(res, "relpages"); i_contype = PQfnumber(res, "contype"); i_conname = PQfnumber(res, "conname"); @@ -5135,6 +5210,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) parseOidArray(PQgetvalue(res, j, i_indkey), indxinfo[j].indkeys, INDEX_MAX_KEYS); indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't'); + indxinfo[j].indisreplident = (PQgetvalue(res, j, i_indisreplident)[0] == 't'); indxinfo[j].relpages = atoi(PQgetvalue(res, j, i_relpages)); contype = *(PQgetvalue(res, j, i_contype)); @@ -13408,6 +13484,28 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo) } } + /* + * dump properties we only have ALTER TABLE syntax for + */ + if ((tbinfo->relkind == RELKIND_RELATION || tbinfo->relkind == RELKIND_MATVIEW) && + tbinfo->relreplident != REPLICA_IDENTITY_DEFAULT) + { + if (tbinfo->relreplident == REPLICA_IDENTITY_INDEX) + { + /* nothing to do, will be set when the index is dumped */ + } + else if (tbinfo->relreplident == REPLICA_IDENTITY_NOTHING) + { + appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY NOTHING;\n", + fmtId(tbinfo->dobj.name)); + } + else if (tbinfo->relreplident == REPLICA_IDENTITY_FULL) + { + appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY FULL;\n", + fmtId(tbinfo->dobj.name)); + } + } + if (binary_upgrade) binary_upgrade_extension_member(q, &tbinfo->dobj, labelq->data); @@ -13579,6 +13677,15 @@ dumpIndex(Archive *fout, IndxInfo *indxinfo) fmtId(indxinfo->dobj.name)); } + /* If the index is clustered, we need to record that. */ + if (indxinfo->indisreplident) + { + appendPQExpBuffer(q, "\nALTER TABLE ONLY %s REPLICA IDENTITY USING", + fmtId(tbinfo->dobj.name)); + appendPQExpBuffer(q, " INDEX %s;\n", + fmtId(indxinfo->dobj.name)); + } + /* * DROP must be fully qualified in case same name appears in * pg_catalog diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 2c5971c516..915e82c64f 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -237,6 +237,7 @@ typedef struct _tableInfo char relkind; char relpersistence; /* relation persistence */ bool relispopulated; /* relation is populated */ + bool relreplident; /* replica identifier */ char *reltablespace; /* relation tablespace */ char *reloptions; /* options specified by WITH (...) */ char *checkoption; /* WITH CHECK OPTION */ @@ -315,6 +316,7 @@ typedef struct _indxInfo int indnkeys; Oid *indkeys; bool indisclustered; + bool indisreplident; /* if there is an associated constraint object, its dumpId: */ DumpId indexconstraint; int relpages; /* relpages of the underlying table */ diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index ed1c5fdabc..76953f21a0 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1156,6 +1156,7 @@ describeOneTableDetails(const char *schemaname, char *reloptions; char *reloftype; char relpersistence; + char relreplident; } tableinfo; bool show_modifiers = false; bool retval; @@ -1171,7 +1172,24 @@ describeOneTableDetails(const char *schemaname, initPQExpBuffer(&tmpbuf); /* Get general table info */ - if (pset.sversion >= 90100) + if (pset.sversion >= 90400) + { + printfPQExpBuffer(&buf, + "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " + "c.relhastriggers, c.relhasoids, " + "%s, c.reltablespace, " + "CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, " + "c.relpersistence, c.relreplident\n" + "FROM pg_catalog.pg_class c\n " + "LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)\n" + "WHERE c.oid = '%s';", + (verbose ? + "pg_catalog.array_to_string(c.reloptions || " + "array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', ')\n" + : "''"), + oid); + } + else if (pset.sversion >= 90100) { printfPQExpBuffer(&buf, "SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, " @@ -1276,6 +1294,8 @@ describeOneTableDetails(const char *schemaname, pg_strdup(PQgetvalue(res, 0, 8)) : NULL; tableinfo.relpersistence = (pset.sversion >= 90100) ? *(PQgetvalue(res, 0, 9)) : 0; + tableinfo.relreplident = (pset.sversion >= 90400) ? + *(PQgetvalue(res, 0, 10)) : 'd'; PQclear(res); res = NULL; @@ -1589,6 +1609,12 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBuffer(&buf, " false AS condeferrable, false AS condeferred,\n"); + + if (pset.sversion >= 90400) + appendPQExpBuffer(&buf, "i.indisidentity,\n"); + else + appendPQExpBuffer(&buf, "false AS indisidentity,\n"); + appendPQExpBuffer(&buf, " a.amname, c2.relname, " "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n" @@ -1612,9 +1638,10 @@ describeOneTableDetails(const char *schemaname, char *indisvalid = PQgetvalue(result, 0, 3); char *deferrable = PQgetvalue(result, 0, 4); char *deferred = PQgetvalue(result, 0, 5); - char *indamname = PQgetvalue(result, 0, 6); - char *indtable = PQgetvalue(result, 0, 7); - char *indpred = PQgetvalue(result, 0, 8); + char *indisidentity = PQgetvalue(result, 0, 6); + char *indamname = PQgetvalue(result, 0, 7); + char *indtable = PQgetvalue(result, 0, 8); + char *indpred = PQgetvalue(result, 0, 9); if (strcmp(indisprimary, "t") == 0) printfPQExpBuffer(&tmpbuf, _("primary key, ")); @@ -1643,6 +1670,9 @@ describeOneTableDetails(const char *schemaname, if (strcmp(deferred, "t") == 0) appendPQExpBuffer(&tmpbuf, _(", initially deferred")); + if (strcmp(indisidentity, "t") == 0) + appendPQExpBuffer(&tmpbuf, _(", replica identity")); + printTableAddFooter(&cont, tmpbuf.data); add_tablespace_footer(&cont, tableinfo.relkind, tableinfo.tablespace, true); @@ -1713,6 +1743,10 @@ describeOneTableDetails(const char *schemaname, appendPQExpBuffer(&buf, "null AS constraintdef, null AS contype, " "false AS condeferrable, false AS condeferred"); + if (pset.sversion >= 90400) + appendPQExpBuffer(&buf, ", i.indisreplident"); + else + appendPQExpBuffer(&buf, ", false AS indisreplident"); if (pset.sversion >= 80000) appendPQExpBuffer(&buf, ", c2.reltablespace"); appendPQExpBuffer(&buf, @@ -1783,12 +1817,15 @@ describeOneTableDetails(const char *schemaname, if (strcmp(PQgetvalue(result, i, 4), "t") != 0) appendPQExpBuffer(&buf, " INVALID"); + if (strcmp(PQgetvalue(result, i, 10), "t") == 0) + appendPQExpBuffer(&buf, " REPLICA IDENTITY"); + printTableAddFooter(&cont, buf.data); /* Print tablespace of the index on the same line */ if (pset.sversion >= 80000) add_tablespace_footer(&cont, 'i', - atooid(PQgetvalue(result, i, 10)), + atooid(PQgetvalue(result, i, 11)), false); } } @@ -2273,6 +2310,17 @@ describeOneTableDetails(const char *schemaname, printTableAddFooter(&cont, buf.data); } + if ((tableinfo.relkind == 'r' || tableinfo.relkind == 'm') && + tableinfo.relreplident != 'd' && tableinfo.relreplident != 'i') + { + const char *s = _("Replica Identity"); + + printfPQExpBuffer(&buf, "%s: %s", + s, + tableinfo.relreplident == 'n' ? "NOTHING" : "FULL"); + printTableAddFooter(&cont, buf.data); + } + /* OIDs, if verbose and not a materialized view */ if (verbose && tableinfo.relkind != 'm') { diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 021b6c5a00..5287d1c7c6 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1336,7 +1336,7 @@ psql_completion(char *text, int start, int end) static const char *const list_ALTER2[] = {"ADD", "ALTER", "CLUSTER ON", "DISABLE", "DROP", "ENABLE", "INHERIT", "NO INHERIT", "RENAME", "RESET", "OWNER TO", "SET", - "VALIDATE CONSTRAINT", NULL}; + "VALIDATE CONSTRAINT", "REPLICA IDENTITY", NULL}; COMPLETE_WITH_LIST(list_ALTER2); } @@ -1581,6 +1581,35 @@ psql_completion(char *text, int start, int end) COMPLETE_WITH_LIST(list_TABLEOPTIONS); } + else if (pg_strcasecmp(prev4_wd, "REPLICA") == 0 && + pg_strcasecmp(prev3_wd, "IDENTITY") == 0 && + pg_strcasecmp(prev2_wd, "USING") == 0 && + pg_strcasecmp(prev_wd, "INDEX") == 0) + { + completion_info_charp = prev5_wd; + COMPLETE_WITH_QUERY(Query_for_index_of_table); + } + else if (pg_strcasecmp(prev5_wd, "TABLE") == 0 && + pg_strcasecmp(prev3_wd, "REPLICA") == 0 && + pg_strcasecmp(prev2_wd, "IDENTITY") == 0 && + pg_strcasecmp(prev_wd, "USING") == 0) + { + COMPLETE_WITH_CONST("INDEX"); + } + else if (pg_strcasecmp(prev4_wd, "TABLE") == 0 && + pg_strcasecmp(prev2_wd, "REPLICA") == 0 && + pg_strcasecmp(prev_wd, "IDENTITY") == 0) + { + static const char *const list_REPLICAID[] = + {"FULL", "NOTHING", "DEFAULT", "USING", NULL}; + + COMPLETE_WITH_LIST(list_REPLICAID); + } + else if (pg_strcasecmp(prev3_wd, "TABLE") == 0 && + pg_strcasecmp(prev_wd, "REPLICA") == 0) + { + COMPLETE_WITH_CONST("IDENTITY"); + } /* ALTER TABLESPACE with RENAME TO, OWNER TO, SET, RESET */ else if (pg_strcasecmp(prev3_wd, "ALTER") == 0 && diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 4aa0edf399..4108f6c16c 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201310271 +#define CATALOG_VERSION_NO 201311081 #endif diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index 49c4f6f136..a1fee11fe6 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -66,6 +66,7 @@ CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO bool relhastriggers; /* has (or has had) any TRIGGERs */ bool relhassubclass; /* has (or has had) derived classes */ bool relispopulated; /* matview currently holds query results */ + char relreplident; /* see REPLICA_IDENTITY_xxx constants */ TransactionId relfrozenxid; /* all Xids < this are frozen in this rel */ TransactionId relminmxid; /* all multixacts in this rel are >= this. * this is really a MultiXactId */ @@ -93,7 +94,7 @@ typedef FormData_pg_class *Form_pg_class; * ---------------- */ -#define Natts_pg_class 28 +#define Natts_pg_class 29 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 @@ -118,10 +119,11 @@ typedef FormData_pg_class *Form_pg_class; #define Anum_pg_class_relhastriggers 22 #define Anum_pg_class_relhassubclass 23 #define Anum_pg_class_relispopulated 24 -#define Anum_pg_class_relfrozenxid 25 -#define Anum_pg_class_relminmxid 26 -#define Anum_pg_class_relacl 27 -#define Anum_pg_class_reloptions 28 +#define Anum_pg_class_relreplident 25 +#define Anum_pg_class_relfrozenxid 26 +#define Anum_pg_class_relminmxid 27 +#define Anum_pg_class_relacl 28 +#define Anum_pg_class_reloptions 29 /* ---------------- * initial contents of pg_class @@ -136,13 +138,13 @@ typedef FormData_pg_class *Form_pg_class; * Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId; * similarly, "1" in relminmxid stands for FirstMultiXactId */ -DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f t 3 1 _null_ _null_ )); +DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f t n 3 1 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f t 3 1 _null_ _null_ )); +DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f t n 3 1 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 27 0 t f f f f t 3 1 _null_ _null_ )); +DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 27 0 t f f f f t n 3 1 _null_ _null_ )); DESCR(""); -DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 f f p r 28 0 t f f f f t 3 1 _null_ _null_ )); +DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f t n 3 1 _null_ _null_ )); DESCR(""); @@ -159,4 +161,16 @@ DESCR(""); #define RELPERSISTENCE_UNLOGGED 'u' /* unlogged permanent table */ #define RELPERSISTENCE_TEMP 't' /* temporary table */ +/* default selection for replica identity (primary key or nothing) */ +#define REPLICA_IDENTITY_DEFAULT 'd' +/* no replica identity is logged for this relation */ +#define REPLICA_IDENTITY_NOTHING 'n' +/* all columns are loged as replica identity */ +#define REPLICA_IDENTITY_FULL 'f' +/* + * an explicitly chosen candidate key's columns are used as identity; + * will still be set if the index has been dropped, in that case it + * has the same meaning as 'd' + */ +#define REPLICA_IDENTITY_INDEX 'i' #endif /* PG_CLASS_H */ diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index 8d1d415939..b31d9d7643 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -42,6 +42,7 @@ CATALOG(pg_index,2610) BKI_WITHOUT_OIDS BKI_SCHEMA_MACRO bool indcheckxmin; /* must we wait for xmin to be old? */ bool indisready; /* is this index ready for inserts? */ bool indislive; /* is this index alive at all? */ + bool indisreplident; /* is this index the identity for replication? */ /* variable-length fields start here, but we allow direct access to indkey */ int2vector indkey; /* column numbers of indexed cols, or 0 */ @@ -69,7 +70,7 @@ typedef FormData_pg_index *Form_pg_index; * compiler constants for pg_index * ---------------- */ -#define Natts_pg_index 18 +#define Natts_pg_index 19 #define Anum_pg_index_indexrelid 1 #define Anum_pg_index_indrelid 2 #define Anum_pg_index_indnatts 3 @@ -82,12 +83,13 @@ typedef FormData_pg_index *Form_pg_index; #define Anum_pg_index_indcheckxmin 10 #define Anum_pg_index_indisready 11 #define Anum_pg_index_indislive 12 -#define Anum_pg_index_indkey 13 -#define Anum_pg_index_indcollation 14 -#define Anum_pg_index_indclass 15 -#define Anum_pg_index_indoption 16 -#define Anum_pg_index_indexprs 17 -#define Anum_pg_index_indpred 18 +#define Anum_pg_index_indisreplident 13 +#define Anum_pg_index_indkey 14 +#define Anum_pg_index_indcollation 15 +#define Anum_pg_index_indclass 16 +#define Anum_pg_index_indoption 17 +#define Anum_pg_index_indexprs 18 +#define Anum_pg_index_indpred 19 /* * Index AMs that support ordered scans must support these two indoption diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 78368c63c1..fc6b1d7dbd 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -362,6 +362,7 @@ typedef enum NodeTag T_CreateEventTrigStmt, T_AlterEventTrigStmt, T_RefreshMatViewStmt, + T_ReplicaIdentityStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index e5235cbf40..952fbb30dd 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1284,9 +1284,17 @@ typedef enum AlterTableType AT_DropInherit, /* NO INHERIT parent */ AT_AddOf, /* OF */ AT_DropOf, /* NOT OF */ + AT_ReplicaIdentity, /* REPLICA IDENTITY */ AT_GenericOptions /* OPTIONS (...) */ } AlterTableType; +typedef struct ReplicaIdentityStmt +{ + NodeTag type; + char identity_type; + char *name; +} ReplicaIdentityStmt; + typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ { NodeTag type; diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index 589c9a81b6..21d5871454 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -110,6 +110,13 @@ typedef struct RelationData MemoryContext rd_rulescxt; /* private memory cxt for rd_rules, if any */ TriggerDesc *trigdesc; /* Trigger info, or NULL if rel has none */ + /* + * The index chosen as the relation's replication identity or + * InvalidOid. Only set correctly if RelationGetIndexList has been + * called/rd_indexvalid > 0. + */ + Oid rd_replidindex; + /* * rd_options is set whenever rd_rel is loaded into the relcache entry. * Note that you can NOT look into rd_rel for this data. NULL means "use diff --git a/src/test/regress/expected/replica_identity.out b/src/test/regress/expected/replica_identity.out new file mode 100644 index 0000000000..60b866ad7e --- /dev/null +++ b/src/test/regress/expected/replica_identity.out @@ -0,0 +1,183 @@ +CREATE TABLE test_replica_identity ( + id serial primary key, + keya text not null, + keyb text not null, + nonkey text, + CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, + CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) +); +CREATE TABLE test_replica_identity_othertable (id serial primary key); +CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); +CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); +CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); +CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; +-- default is 'd'/DEFAULT for user created tables +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +-- but 'none' for system tables +SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; + relreplident +-------------- + n +(1 row) + +SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; + relreplident +-------------- + n +(1 row) + +---- +-- Make sure we detect inelegible indexes +---- +-- fail, not unique +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; +ERROR: cannot use non-unique index "test_replica_identity_keyab" as replica identity +-- fail, not a candidate key, nullable column +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; +ERROR: index "test_replica_identity_nonkey" cannot be used as replica identity because column "nonkey" is nullable +-- fail, hash indexes cannot do uniqueness +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; +ERROR: cannot use non-unique index "test_replica_identity_hash" as replica identity +-- fail, expression index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; +ERROR: cannot use expression index "test_replica_identity_expr" as replica identity +-- fail, partial index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; +ERROR: cannot use partial index "test_replica_identity_partial" as replica identity +-- fail, not our index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; +ERROR: "test_replica_identity_othertable_pkey" is not an index for table "test_replica_identity" +-- fail, deferrable +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; +ERROR: cannot use non-immediate index "test_replica_identity_unique_defer" as replica identity +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +---- +-- Make sure index cases succeeed +---- +-- succeed, primary key +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + i +(1 row) + +\d test_replica_identity + Table "public.test_replica_identity" + Column | Type | Modifiers +--------+---------+-------------------------------------------------------------------- + id | integer | not null default nextval('test_replica_identity_id_seq'::regclass) + keya | text | not null + keyb | text | not null + nonkey | text | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) REPLICA IDENTITY + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) + +-- succeed, nondeferrable unique constraint over nonullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; +-- succeed unique index over nonnullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + i +(1 row) + +\d test_replica_identity + Table "public.test_replica_identity" + Column | Type | Modifiers +--------+---------+-------------------------------------------------------------------- + id | integer | not null default nextval('test_replica_identity_id_seq'::regclass) + keya | text | not null + keyb | text | not null + nonkey | text | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) REPLICA IDENTITY + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) + +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + count +------- + 1 +(1 row) + +---- +-- Make sure non index cases work +---- +ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + d +(1 row) + +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + count +------- + 0 +(1 row) + +ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + f +(1 row) + +\d test_replica_identity + Table "public.test_replica_identity" + Column | Type | Modifiers +--------+---------+-------------------------------------------------------------------- + id | integer | not null default nextval('test_replica_identity_id_seq'::regclass) + keya | text | not null + keyb | text | not null + nonkey | text | +Indexes: + "test_replica_identity_pkey" PRIMARY KEY, btree (id) + "test_replica_identity_expr" UNIQUE, btree (keya, keyb, (3)) + "test_replica_identity_keyab_key" UNIQUE, btree (keya, keyb) + "test_replica_identity_nonkey" UNIQUE, btree (keya, nonkey) + "test_replica_identity_partial" UNIQUE, btree (keya, keyb) WHERE keyb <> '3'::text + "test_replica_identity_unique_defer" UNIQUE CONSTRAINT, btree (keya, keyb) DEFERRABLE + "test_replica_identity_unique_nondefer" UNIQUE CONSTRAINT, btree (keya, keyb) + "test_replica_identity_hash" hash (nonkey) + "test_replica_identity_keyab" btree (keya, keyb) +Replica Identity: FULL + +ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + relreplident +-------------- + n +(1 row) + +DROP TABLE test_replica_identity; +DROP TABLE test_replica_identity_othertable; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 1c1491c3f3..5758b07fa4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- # Another group of parallel tests # ---------- -test: privileges security_label collate matview lock +test: privileges security_label collate matview lock replica_identity # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index c4d451ab00..78348f5f86 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -98,6 +98,7 @@ test: security_label test: collate test: matview test: lock +test: replica_identity test: alter_generic test: misc test: psql diff --git a/src/test/regress/sql/replica_identity.sql b/src/test/regress/sql/replica_identity.sql new file mode 100644 index 0000000000..9d2e9a6e7e --- /dev/null +++ b/src/test/regress/sql/replica_identity.sql @@ -0,0 +1,79 @@ +CREATE TABLE test_replica_identity ( + id serial primary key, + keya text not null, + keyb text not null, + nonkey text, + CONSTRAINT test_replica_identity_unique_defer UNIQUE (keya, keyb) DEFERRABLE, + CONSTRAINT test_replica_identity_unique_nondefer UNIQUE (keya, keyb) +); + +CREATE TABLE test_replica_identity_othertable (id serial primary key); + +CREATE INDEX test_replica_identity_keyab ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_keyab_key ON test_replica_identity (keya, keyb); +CREATE UNIQUE INDEX test_replica_identity_nonkey ON test_replica_identity (keya, nonkey); +CREATE INDEX test_replica_identity_hash ON test_replica_identity USING hash (nonkey); +CREATE UNIQUE INDEX test_replica_identity_expr ON test_replica_identity (keya, keyb, (3)); +CREATE UNIQUE INDEX test_replica_identity_partial ON test_replica_identity (keya, keyb) WHERE keyb != '3'; + +-- default is 'd'/DEFAULT for user created tables +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; +-- but 'none' for system tables +SELECT relreplident FROM pg_class WHERE oid = 'pg_class'::regclass; +SELECT relreplident FROM pg_class WHERE oid = 'pg_constraint'::regclass; + +---- +-- Make sure we detect inelegible indexes +---- + +-- fail, not unique +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab; +-- fail, not a candidate key, nullable column +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_nonkey; +-- fail, hash indexes cannot do uniqueness +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_hash; +-- fail, expression index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_expr; +-- fail, partial index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_partial; +-- fail, not our index +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_othertable_pkey; +-- fail, deferrable +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_defer; + +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + +---- +-- Make sure index cases succeeed +---- + +-- succeed, primary key +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_pkey; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; +\d test_replica_identity + +-- succeed, nondeferrable unique constraint over nonullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_unique_nondefer; + +-- succeed unique index over nonnullable cols +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +ALTER TABLE test_replica_identity REPLICA IDENTITY USING INDEX test_replica_identity_keyab_key; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; +\d test_replica_identity +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + +---- +-- Make sure non index cases work +---- +ALTER TABLE test_replica_identity REPLICA IDENTITY DEFAULT; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; +SELECT count(*) FROM pg_index WHERE indrelid = 'test_replica_identity'::regclass AND indisreplident; + +ALTER TABLE test_replica_identity REPLICA IDENTITY FULL; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; +\d test_replica_identity +ALTER TABLE test_replica_identity REPLICA IDENTITY NOTHING; +SELECT relreplident FROM pg_class WHERE oid = 'test_replica_identity'::regclass; + +DROP TABLE test_replica_identity; +DROP TABLE test_replica_identity_othertable; -- 2.40.0