public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION.
2+ messages / 2 participants
[nested] [flat]
* [PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION.
@ 2019-12-11 12:54 Julien Rouhaud <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Julien Rouhaud @ 2019-12-11 12:54 UTC (permalink / raw)
Allow privileged users to declare that the currently installed collation
version, for a specific collation, is binary compatible with the one
that was installed when the index was built. This provides a way to
clear warnings about potentially corrupted indexes without having to use
REINDEX.
Author: Julien Rouhaud <[email protected]>
Reviewed-by: Laurenz Albe <[email protected]>
Reviewed-by: Thomas Munro <[email protected]>
Reviewed-by: Peter Eisentraut <[email protected]>
Discussion: https://postgr.es/m/CAEepm%3D0uEQCpfq_%2BLYFBdArCe4Ot98t1aR4eYiYTe%3DyavQygiQ%40mail.gmail.com
---
doc/src/sgml/ref/alter_index.sgml | 17 +++++++
src/backend/catalog/index.c | 2 +-
src/backend/commands/tablecmds.c | 47 +++++++++++++++++++
src/backend/nodes/copyfuncs.c | 1 +
src/backend/parser/gram.y | 8 ++++
src/bin/psql/tab-complete.c | 27 ++++++++++-
src/include/catalog/index.h | 3 ++
src/include/nodes/parsenodes.h | 4 +-
.../regress/expected/collate.icu.utf8.out | 20 ++++++++
src/test/regress/sql/collate.icu.utf8.sql | 11 +++++
10 files changed, 137 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml
index a5e3b06ee4..03355164b3 100644
--- a/doc/src/sgml/ref/alter_index.sgml
+++ b/doc/src/sgml/ref/alter_index.sgml
@@ -25,6 +25,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RENA
ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
ALTER INDEX <replaceable class="parameter">name</replaceable> ATTACH PARTITION <replaceable class="parameter">index_name</replaceable>
ALTER INDEX <replaceable class="parameter">name</replaceable> DEPENDS ON EXTENSION <replaceable class="parameter">extension_name</replaceable>
+ALTER INDEX <replaceable class="parameter">name</replaceable> ALTER COLLATION <replaceable class="parameter">collation_name</replaceable> REFRESH VERSION
ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> RESET ( <replaceable class="parameter">storage_parameter</replaceable> [, ... ] )
ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTER [ COLUMN ] <replaceable class="parameter">column_number</replaceable>
@@ -112,6 +113,22 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>ALTER COLLATION <replaceable class="parameter">collation_name</replaceable> REFRESH VERSION</literal></term>
+ <listitem>
+ <para>
+ This command declares that the index is compatible with the currently
+ installed version of a collation that determines its order. It is used
+ to silence warnings caused by collation version incompatibilities and
+ should be issued only if the collation ordering is known not to have
+ changed since the index was last built. Be aware that incorrect use of
+ this command can hide index corruption. If you don't know whether a
+ collation's definition has changed, using <xref linkend="sql-reindex"/>
+ is a safe alternative.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5cd3af3053..78bac65a7e 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -3130,7 +3130,7 @@ index_build(Relation heapRelation,
SetUserIdAndSecContext(save_userid, save_sec_context);
}
-static char *
+char *
index_force_collation_version(const ObjectAddress *otherObject,
const char *version,
void *userdata)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cd989c95e5..2f931e3ca4 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -93,6 +93,7 @@
#include "utils/lsyscache.h"
#include "utils/memutils.h"
#include "utils/partcache.h"
+#include "utils/pg_locale.h"
#include "utils/relcache.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
@@ -556,6 +557,7 @@ static void refuseDupeIndexAttach(Relation parentIdx, Relation partIdx,
Relation partitionTbl);
static List *GetParentedForeignKeyRefs(Relation partition);
static void ATDetachCheckNoForeignKeyRefs(Relation partition);
+static void ATExecAlterCollationRefreshVersion(Relation rel, List *coll);
/* ----------------------------------------------------------------
@@ -3926,6 +3928,10 @@ AlterTableGetLockLevel(List *cmds)
cmd_lockmode = AccessShareLock;
break;
+ case AT_AlterCollationRefreshVersion:
+ cmd_lockmode = AccessExclusiveLock;
+ break;
+
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -4093,6 +4099,12 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* This command never recurses */
pass = AT_PASS_MISC;
break;
+ case AT_AlterCollationRefreshVersion: /* ALTER COLLATION ... REFRESH
+ * VERSION */
+ ATSimplePermissions(rel, ATT_INDEX);
+ /* This command never recurses */
+ pass = AT_PASS_MISC;
+ break;
case AT_SetStorage: /* ALTER COLUMN SET STORAGE */
ATSimplePermissions(rel, ATT_TABLE | ATT_MATVIEW | ATT_FOREIGN_TABLE);
ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
@@ -4659,6 +4671,11 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
Assert(rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE);
ATExecDetachPartition(rel, ((PartitionCmd *) cmd->def)->name);
break;
+ case AT_AlterCollationRefreshVersion:
+ /* ATPrepCmd ensured it must be an index */
+ Assert(rel->rd_rel->relkind == RELKIND_INDEX);
+ ATExecAlterCollationRefreshVersion(rel, cmd->object);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
@@ -17426,3 +17443,33 @@ ATDetachCheckNoForeignKeyRefs(Relation partition)
table_close(rel, NoLock);
}
}
+
+/*
+ * ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION
+ *
+ * This overrides an existing dependency on a specific collation for a specific
+ * index to depend on the current collation version.
+ */
+static void
+ATExecAlterCollationRefreshVersion(Relation rel, List *coll)
+{
+ ObjectAddress object;
+ NewCollationVersionDependency forced_dependency;
+
+ Assert(coll != NIL);
+ forced_dependency.oid = get_collation_oid(coll, false);
+
+ /* Retrieve the current version for the CURRENT VERSION case. */
+ Assert(OidIsValid(forced_dependency.oid));
+ forced_dependency.version =
+ get_collation_version_for_oid(forced_dependency.oid);
+
+ object.classId = RelationRelationId;
+ object.objectId = rel->rd_id;
+ object.objectSubId = 0;
+ visitDependentObjects(&object, &index_force_collation_version,
+ &forced_dependency);
+
+ /* Invalidate the index relcache */
+ CacheInvalidateRelcache(rel);
+}
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2218f6e3db..db35add7bb 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3217,6 +3217,7 @@ _copyAlterTableCmd(const AlterTableCmd *from)
COPY_SCALAR_FIELD(subtype);
COPY_STRING_FIELD(name);
+ COPY_NODE_FIELD(object);
COPY_SCALAR_FIELD(num);
COPY_NODE_FIELD(newowner);
COPY_NODE_FIELD(def);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e432049f8e..3199f9e7e1 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -2594,6 +2594,14 @@ alter_table_cmd:
n->subtype = AT_NoForceRowSecurity;
$$ = (Node *)n;
}
+ /* ALTER INDEX <name> ALTER COLLATION ... REFRESH VERSION */
+ | ALTER COLLATION any_name REFRESH VERSION_P
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_AlterCollationRefreshVersion;
+ n->object = $3;
+ $$ = (Node *)n;
+ }
| alter_generic_options
{
AlterTableCmd *n = makeNode(AlterTableCmd);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index f41785f11c..71e982ffa0 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -45,6 +45,7 @@
#include "catalog/pg_am_d.h"
#include "catalog/pg_class_d.h"
+#include "catalog/pg_collation_d.h"
#include "common.h"
#include "libpq-fe.h"
#include "pqexpbuffer.h"
@@ -820,6 +821,20 @@ static const SchemaQuery Query_for_list_of_statistics = {
" (SELECT tgrelid FROM pg_catalog.pg_trigger "\
" WHERE pg_catalog.quote_ident(tgname)='%s')"
+/* the silly-looking length condition is just to eat up the current word */
+#define Query_for_list_of_colls_for_one_index \
+" SELECT DISTINCT pg_catalog.quote_ident(coll.collname) " \
+" FROM pg_catalog.pg_depend d, pg_catalog.pg_collation coll, " \
+" pg_catalog.pg_class c" \
+" WHERE (%d = pg_catalog.length('%s'))" \
+" AND d.refclassid = " CppAsString2(CollationRelationId) \
+" AND d.refobjid = coll.oid " \
+" AND d.classid = " CppAsString2(RelationRelationId) \
+" AND d.objid = c.oid " \
+" AND c.relkind = " CppAsString2(RELKIND_INDEX) \
+" AND pg_catalog.pg_table_is_visible(c.oid) " \
+" AND c.relname = '%s'"
+
#define Query_for_list_of_ts_configurations \
"SELECT pg_catalog.quote_ident(cfgname) FROM pg_catalog.pg_ts_config "\
" WHERE substring(pg_catalog.quote_ident(cfgname),1,%d)='%s'"
@@ -1715,7 +1730,8 @@ psql_completion(const char *text, int start, int end)
/* ALTER INDEX <name> */
else if (Matches("ALTER", "INDEX", MatchAny))
COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET",
- "RESET", "ATTACH PARTITION", "DEPENDS", "NO DEPENDS");
+ "RESET", "ATTACH PARTITION", "DEPENDS", "NO DEPENDS",
+ "ALTER COLLATION");
else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH"))
COMPLETE_WITH("PARTITION");
else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION"))
@@ -1765,6 +1781,15 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH("ON EXTENSION");
else if (Matches("ALTER", "INDEX", MatchAny, "DEPENDS"))
COMPLETE_WITH("ON EXTENSION");
+ /* ALTER INDEX <name> ALTER COLLATION */
+ else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLLATION"))
+ {
+ completion_info_charp = prev4_wd;
+ COMPLETE_WITH_QUERY(Query_for_list_of_colls_for_one_index);
+ }
+ /* ALTER INDEX <name> ALTER COLLATION <name> */
+ else if (Matches("ALTER", "INDEX", MatchAny, "ALTER", "COLLATION", MatchAny))
+ COMPLETE_WITH("REFRESH VERSION");
/* ALTER LANGUAGE <name> */
else if (Matches("ALTER", "LANGUAGE", MatchAny))
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 9b4de26514..46d5df1613 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -123,6 +123,9 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
extern void index_check_collation_versions(Oid relid);
+extern char *index_force_collation_version(const ObjectAddress *otherObject,
+ const char *version,
+ void *userdata);
extern void index_force_collation_versions(Oid indexid, Oid coll,
char *version);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a8da438d22..0370ad6361 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1847,7 +1847,8 @@ typedef enum AlterTableType
AT_DetachPartition, /* DETACH PARTITION */
AT_AddIdentity, /* ADD IDENTITY */
AT_SetIdentity, /* SET identity column options */
- AT_DropIdentity /* DROP IDENTITY */
+ AT_DropIdentity, /* DROP IDENTITY */
+ AT_AlterCollationRefreshVersion /* ALTER COLLATION ... REFRESH VERSION */
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -1863,6 +1864,7 @@ typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
AlterTableType subtype; /* Type of table alteration to apply */
char *name; /* column, constraint, or trigger to act on,
* or tablespace */
+ List *object; /* collation to act on if it's a collation */
int16 num; /* attribute number for columns referenced by
* number */
RoleSpec *newowner;
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 0075e85072..1843d71a38 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2054,6 +2054,26 @@ SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
-------
(0 rows)
+-- Test ALTER INDEX name ALTER COLLATION name REFRESH VERSION
+UPDATE pg_depend SET refobjversion = 'not a version'
+WHERE refclassid = 'pg_collation'::regclass
+AND objid::regclass::text = 'icuidx17_part'
+AND refobjversion IS NOT NULL;
+SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
+ objid
+---------------
+ icuidx17_part
+(1 row)
+
+ALTER INDEX icuidx17_part ALTER COLLATION "en-x-icu" REFRESH VERSION;
+SELECT objid::regclass, refobjversion = 'not a version' AS ver FROM pg_depend
+WHERE refclassid = 'pg_collation'::regclass
+AND objid::regclass::text = 'icuidx17_part';
+ objid | ver
+---------------+-----
+ icuidx17_part | f
+(1 row)
+
-- cleanup
RESET search_path;
SET client_min_messages TO warning;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index e93530af55..b3a75b29e6 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -823,6 +823,17 @@ VACUUM FULL collate_part_1;
SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
+-- Test ALTER INDEX name ALTER COLLATION name REFRESH VERSION
+UPDATE pg_depend SET refobjversion = 'not a version'
+WHERE refclassid = 'pg_collation'::regclass
+AND objid::regclass::text = 'icuidx17_part'
+AND refobjversion IS NOT NULL;
+SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
+ALTER INDEX icuidx17_part ALTER COLLATION "en-x-icu" REFRESH VERSION;
+SELECT objid::regclass, refobjversion = 'not a version' AS ver FROM pg_depend
+WHERE refclassid = 'pg_collation'::regclass
+AND objid::regclass::text = 'icuidx17_part';
+
-- cleanup
RESET search_path;
SET client_min_messages TO warning;
--
2.20.1
--o56keubw4jpehgac
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment;
filename="v27-0005-Doc-Add-Collation-Versions-section.patch"
^ permalink raw reply [nested|flat] 2+ messages in thread
* pg_upgrde failed : logical replication : alter_subscription_add_log
@ 2024-04-12 00:47 Perumal Raj <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Perumal Raj @ 2024-04-12 00:47 UTC (permalink / raw)
To: [email protected]
Hi Community
I am trying to upgrade PostgreSQL (RHEL 7) from version 13.7 to 15.6 using
pglogical.
My Standby(destination) machine has following rpms,
*postgresql13-pglogical-3.7.16-1.el7.x86_64pglogical_15-2.4.3-1.rhel7.x86_64*
And Primary(Source) has ,
*postgresql13-pglogical-3.7.16-1.el7.x86_64*
pg_upgrade check mode went fine , but it failed while running real mode.
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 13027; 1255 3375648004 FUNCTION
alter_subscription_add_log("text", "text", boolean, "regclass", "text"[],
"text"[]) postgres
pg_restore: error: could not execute query: ERROR: could not find function
"pglogical_alter_subscription_add_log" in file
"/usr/pgsql-15/lib/pglogical.so"
Command was: CREATE FUNCTION
"pglogical"."alter_subscription_add_log"("sub_name" "text", "log_name"
"text", "log_to_file" boolean DEFAULT true, "log_to_table" "regclass"
DEFAULT NULL::"regclass", "conflict_type" "text"[] DEFAULT NULL::"text"[],
"conflict_resolution" "text"[] DEFAULT NULL::"text"[]) RETURNS boolean
LANGUAGE "c"
AS '$libdir/pglogical', 'pglogical_alter_subscription_add_log';
-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pglogical" ADD FUNCTION
"pglogical"."alter_subscription_add_log"("sub_name" "text", "log_name"
"text", "log_to_file" boolean, "log_to_table" "regclass", "conflict_type"
"text"[], "conflict_resolution" "text"[]);
Am I missing any packages?
Thanks,
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2024-04-12 00:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-11 12:54 [PATCH v27 4/5] Add ALTER INDEX ... ALTER COLLATION ... REFRESH VERSION. Julien Rouhaud <[email protected]>
2024-04-12 00:47 pg_upgrde failed : logical replication : alter_subscription_add_log Perumal Raj <[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