public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v4 2/3] Allow CREATE INDEX CONCURRENTLY on partitioned table
6+ messages / 4 participants
[nested] [flat]
* [PATCH v4 2/3] Allow CREATE INDEX CONCURRENTLY on partitioned table
@ 2020-06-06 22:42 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Justin Pryzby @ 2020-06-06 22:42 UTC (permalink / raw)
Note, this effectively reverts 050098b14, so take care to not reintroduce the
bug it fixed.
---
doc/src/sgml/ref/create_index.sgml | 9 --
src/backend/commands/indexcmds.c | 109 +++++++++++++++++++------
src/test/regress/expected/indexing.out | 57 ++++++++++++-
src/test/regress/sql/indexing.sql | 16 +++-
4 files changed, 150 insertions(+), 41 deletions(-)
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 33aa64e81d..c780dc9547 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -657,15 +657,6 @@ Indexes:
cannot.
</para>
- <para>
- Concurrent builds for indexes on partitioned tables are currently not
- supported. However, you may concurrently build the index on each
- partition individually and then finally create the partitioned index
- non-concurrently in order to reduce the time where writes to the
- partitioned table will be locked out. In this case, building the
- partitioned index is a metadata only operation.
- </para>
-
</refsect2>
</refsect1>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index df3e567acb..291046cb16 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -653,17 +653,6 @@ DefineIndex(Oid relationId,
partitioned = rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE;
if (partitioned)
{
- /*
- * Note: we check 'stmt->concurrent' rather than 'concurrent', so that
- * the error is thrown also for temporary tables. Seems better to be
- * consistent, even though we could do it on temporary table because
- * we're not actually doing it concurrently.
- */
- if (stmt->concurrent)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot create index on partitioned table \"%s\" concurrently",
- RelationGetRelationName(rel))));
if (stmt->excludeOpNames)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1098,6 +1087,9 @@ DefineIndex(Oid relationId,
if (pd->nparts != 0)
flags |= INDEX_CREATE_INVALID;
}
+ else if (concurrent && OidIsValid(parentIndexId))
+ /* Initial concurrent build index partition as invalid */
+ flags |= INDEX_CREATE_INVALID;
if (stmt->deferrable)
constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE;
@@ -1140,6 +1132,10 @@ DefineIndex(Oid relationId,
CreateComments(indexRelationId, RelationRelationId, 0,
stmt->idxcomment);
+ /* save lockrelid and locktag for below */
+ heaprelid = rel->rd_lockInfo.lockRelId;
+ SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId);
+
if (partitioned)
{
/*
@@ -1150,8 +1146,17 @@ DefineIndex(Oid relationId,
*/
if (!stmt->relation || stmt->relation->inh)
{
+ /*
+ * Need to close the relation before recursing into children, so
+ * copy needed data into a longlived context.
+ */
+
+ MemoryContext ind_context = AllocSetContextCreate(PortalContext, "CREATE INDEX",
+ ALLOCSET_DEFAULT_SIZES);
+ MemoryContext oldcontext = MemoryContextSwitchTo(ind_context);
PartitionDesc partdesc = RelationGetPartitionDesc(rel);
int nparts = partdesc->nparts;
+ char *relname = pstrdup(RelationGetRelationName(rel));
Oid *part_oids = palloc(sizeof(Oid) * nparts);
bool invalidate_parent = false;
TupleDesc parentDesc;
@@ -1161,8 +1166,10 @@ DefineIndex(Oid relationId,
nparts);
memcpy(part_oids, partdesc->oids, sizeof(Oid) * nparts);
+ parentDesc = CreateTupleDescCopy(RelationGetDescr(rel));
+ table_close(rel, NoLock);
+ MemoryContextSwitchTo(oldcontext);
- parentDesc = RelationGetDescr(rel);
opfamOids = palloc(sizeof(Oid) * numberOfKeyAttributes);
for (i = 0; i < numberOfKeyAttributes; i++)
opfamOids[i] = get_opclass_family(classObjectId[i]);
@@ -1197,18 +1204,20 @@ DefineIndex(Oid relationId,
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot create unique index on partitioned table \"%s\"",
- RelationGetRelationName(rel)),
+ relname),
errdetail("Table \"%s\" contains partitions that are foreign tables.",
- RelationGetRelationName(rel))));
+ relname)));
table_close(childrel, lockmode);
continue;
}
+ oldcontext = MemoryContextSwitchTo(ind_context);
childidxs = RelationGetIndexList(childrel);
attmap =
build_attrmap_by_name(RelationGetDescr(childrel),
parentDesc);
+ MemoryContextSwitchTo(oldcontext);
foreach(cell, childidxs)
{
@@ -1334,10 +1343,16 @@ DefineIndex(Oid relationId,
createdConstraintId,
is_alter_table, check_rights, check_not_in_use,
skip_build, quiet);
+ if (concurrent)
+ {
+ PushActiveSnapshot(GetTransactionSnapshot());
+ invalidate_parent = true;
+ }
}
- pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,
- i + 1);
+ if (!concurrent)
+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,
+ i + 1);
free_attrmap(attmap);
}
@@ -1364,23 +1379,72 @@ DefineIndex(Oid relationId,
table_close(pg_index, RowExclusiveLock);
heap_freetuple(newtup);
}
+ } else
+ table_close(rel, NoLock);
+
+ if (concurrent)
+ {
+ List *childs;
+ ListCell *lc;
+ int npart = 0;
+
+ /* Reindex invalid child indexes created earlier */
+ MemoryContext ind_context = AllocSetContextCreate(PortalContext, "CREATE INDEX",
+ ALLOCSET_DEFAULT_SIZES);
+ MemoryContext oldcontext = MemoryContextSwitchTo(ind_context);
+ childs = find_inheritance_children(indexRelationId, NoLock);
+ MemoryContextSwitchTo(oldcontext);
+
+ /* Make the catalog changes visible to get_partition_parent */
+ CommandCounterIncrement();
+
+ foreach (lc, childs)
+ {
+ Oid indexrelid = lfirst_oid(lc);
+ Relation cldidx;
+ bool isvalid;
+
+ if (!OidIsValid(parentIndexId))
+ pgstat_progress_update_param(PROGRESS_CREATEIDX_PARTITIONS_DONE,
+ npart++);
+
+ cldidx = index_open(indexrelid, ShareUpdateExclusiveLock);
+ isvalid = cldidx->rd_index->indisvalid;
+ index_close(cldidx, NoLock);
+ if (isvalid)
+ continue;
+
+ /* This may be a partitioned index, which is fine too */
+ ReindexRelationConcurrently(indexrelid, 0);
+ PushActiveSnapshot(GetTransactionSnapshot());
+ }
+
+ PopActiveSnapshot();
+ CommitTransactionCommand();
+ StartTransactionCommand();
+
+ /*
+ * CIC needs to mark a partitioned index as VALID, which itself
+ * requires setting READY, which is unset for CIC (even though
+ * it's meaningless for an index without storage).
+ */
+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_READY);
+ CommandCounterIncrement();
+ index_set_state_flags(indexRelationId, INDEX_CREATE_SET_VALID);
}
/*
* Indexes on partitioned tables are not themselves built, so we're
* done here.
*/
- table_close(rel, NoLock);
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();
return address;
}
+ table_close(rel, NoLock);
if (!concurrent)
{
- /* Close the heap and we're done, in the non-concurrent case */
- table_close(rel, NoLock);
-
/* If this is the top-level index, we're done. */
if (!OidIsValid(parentIndexId))
pgstat_progress_end_command();
@@ -1388,11 +1452,6 @@ DefineIndex(Oid relationId,
return address;
}
- /* save lockrelid and locktag for below, then close rel */
- heaprelid = rel->rd_lockInfo.lockRelId;
- SET_LOCKTAG_RELATION(heaplocktag, heaprelid.dbId, heaprelid.relId);
- table_close(rel, NoLock);
-
/*
* For a concurrent build, it's important to make the catalog entries
* visible to other transactions before we start to build the index. That
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index f78865ef81..1ae58e110f 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -50,11 +50,60 @@ select relname, relkind, relhassubclass, inhparent::regclass
(8 rows)
drop table idxpart;
--- Some unsupported features
+-- CIC on partitioned table
create table idxpart (a int, b int, c text) partition by range (a);
-create table idxpart1 partition of idxpart for values from (0) to (10);
-create index concurrently on idxpart (a);
-ERROR: cannot create index on partitioned table "idxpart" concurrently
+create table idxpart1 partition of idxpart for values from (0) to (10) partition by range(a);
+create table idxpart11 partition of idxpart1 for values from (0) to (10) partition by range(a);
+create table idxpart2 partition of idxpart for values from (10) to (20);
+insert into idxpart2 values(10),(10); -- not unique
+create index concurrently on idxpart (a); -- partitioned
+create index concurrently on idxpart1 (a); -- partitioned and partition
+create index concurrently on idxpart11 (a); -- partitioned and partition, with no leaves
+create index concurrently on idxpart2 (a); -- leaf
+create unique index concurrently on idxpart (a); -- partitioned, unique failure
+ERROR: could not create unique index "idxpart2_a_idx2"
+DETAIL: Key (a)=(10) is duplicated.
+\d idxpart
+ Partitioned table "public.idxpart"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+Partition key: RANGE (a)
+Indexes:
+ "idxpart_a_idx" btree (a)
+ "idxpart_a_idx1" UNIQUE, btree (a) INVALID
+Number of partitions: 2 (Use \d+ to list them.)
+
+\d idxpart1
+ Partitioned table "public.idxpart1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+Partition of: idxpart FOR VALUES FROM (0) TO (10)
+Partition key: RANGE (a)
+Indexes:
+ "idxpart1_a_idx" btree (a)
+ "idxpart1_a_idx1" btree (a)
+ "idxpart1_a_idx2" UNIQUE, btree (a)
+Number of partitions: 1 (Use \d+ to list them.)
+
+\d idxpart2
+ Table "public.idxpart2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+ c | text | | |
+Partition of: idxpart FOR VALUES FROM (10) TO (20)
+Indexes:
+ "idxpart2_a_idx" btree (a)
+ "idxpart2_a_idx1" btree (a)
+ "idxpart2_a_idx2" UNIQUE, btree (a) INVALID
+
drop table idxpart;
-- Verify bugfix with query on indexed partitioned table with no partitions
-- https://postgr.es/m/[email protected]
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 35d159f41b..d908ee6d17 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -29,10 +29,20 @@ select relname, relkind, relhassubclass, inhparent::regclass
where relname like 'idxpart%' order by relname;
drop table idxpart;
--- Some unsupported features
+-- CIC on partitioned table
create table idxpart (a int, b int, c text) partition by range (a);
-create table idxpart1 partition of idxpart for values from (0) to (10);
-create index concurrently on idxpart (a);
+create table idxpart1 partition of idxpart for values from (0) to (10) partition by range(a);
+create table idxpart11 partition of idxpart1 for values from (0) to (10) partition by range(a);
+create table idxpart2 partition of idxpart for values from (10) to (20);
+insert into idxpart2 values(10),(10); -- not unique
+create index concurrently on idxpart (a); -- partitioned
+create index concurrently on idxpart1 (a); -- partitioned and partition
+create index concurrently on idxpart11 (a); -- partitioned and partition, with no leaves
+create index concurrently on idxpart2 (a); -- leaf
+create unique index concurrently on idxpart (a); -- partitioned, unique failure
+\d idxpart
+\d idxpart1
+\d idxpart2
drop table idxpart;
-- Verify bugfix with query on indexed partitioned table with no partitions
--
2.17.0
--JYK4vJDZwFMowpUq
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v4-0003-Implement-CLUSTER-of-partitioned-table.patch"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Add standard collation UNICODE
@ 2023-03-01 10:09 Peter Eisentraut <[email protected]>
2023-03-02 00:05 ` Re: Add standard collation UNICODE Vik Fearing <[email protected]>
2023-04-27 11:44 ` Re: Add standard collation UNICODE Daniel Verite <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Peter Eisentraut @ 2023-03-01 10:09 UTC (permalink / raw)
To: pgsql-hackers
The SQL standard defines several standard collations. Most of them are
only of legacy interest (IMO), but two are currently relevant: UNICODE
and UCS_BASIC. UNICODE sorts by the default Unicode collation algorithm
specifications and UCS_BASIC sorts by codepoint.
When collation support was added to PostgreSQL, we added UCS_BASIC,
since that could easily be mapped to the C locale. But there was no
straightforward way to provide the UNICODE collation. (Recall that
collation support came several releases before ICU support.)
With ICU support, we can provide the UNICODE collation, since it's just
the root locale. I suppose one hesitation was that ICU was not a
standard feature, so this would create variations in the default catalog
contents, or something like that. But I think now that we are drifting
to make ICU more prominent, we can just add that anyway. I think being
able to say
COLLATE UNICODE
instead of
COLLATE "und-x-icu"
or whatever it is, is pretty useful.
So, attached is a small patch to add this.
From 98fce30e4997253e5b010b6ac72d66255bc77bf6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Wed, 1 Mar 2023 10:38:19 +0100
Subject: [PATCH] Add standard collation UNICODE
---
doc/src/sgml/charset.sgml | 30 +++++++++++++++++++++++++++---
src/bin/initdb/initdb.c | 10 +++++++---
2 files changed, 34 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 3032392b80..13ec238a81 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -659,9 +659,33 @@ <title>Standard Collations</title>
</para>
<para>
- Additionally, the SQL standard collation name <literal>ucs_basic</literal>
- is available for encoding <literal>UTF8</literal>. It is equivalent
- to <literal>C</literal> and sorts by Unicode code point.
+ Additionally, two SQL standard collation names are available for encoding
+ <literal>UTF8</literal>:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>unicode</literal></term>
+ <listitem>
+ <para>
+ This collation sorts using the Unicode Collation Algorithm with the
+ Default Unicode Collation Element Table. (This is the same behavior
+ as the ICU root locale; see <xref
+ linkend="collation-managing-predefined-icu-und-x-icu"/>.) This
+ collation is only available when ICU support is configured.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ucs_basic</literal></term>
+ <listitem>
+ <para>
+ This collation sorts by Unicode code point. (This is the same
+ behavior as the libc locale specification <literal>C</literal>.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</para>
</sect3>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 7a58c33ace..525bec4b44 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1482,10 +1482,14 @@ static void
setup_collation(FILE *cmdfd)
{
/*
- * Add an SQL-standard name. We don't want to pin this, so it doesn't go
- * in pg_collation.h. But add it before reading system collations, so
- * that it wins if libc defines a locale named ucs_basic.
+ * Add SQL-standard names. We don't want to pin these, so they don't go
+ * in pg_collation.h. But add them before reading system collations, so
+ * that they win if libc defines a locale with the same name.
*/
+ PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, colliculocale)"
+ "VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'unicode', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'und');\n\n",
+ BOOTSTRAP_SUPERUSERID, COLLPROVIDER_ICU, PG_UTF8);
+
PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, collcollate, collctype)"
"VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'ucs_basic', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'C', 'C');\n\n",
BOOTSTRAP_SUPERUSERID, COLLPROVIDER_LIBC, PG_UTF8);
--
2.39.2
Attachments:
[text/plain] 0001-Add-standard-collation-UNICODE.patch (3.2K, ../../[email protected]/2-0001-Add-standard-collation-UNICODE.patch)
download | inline diff:
From 98fce30e4997253e5b010b6ac72d66255bc77bf6 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Wed, 1 Mar 2023 10:38:19 +0100
Subject: [PATCH] Add standard collation UNICODE
---
doc/src/sgml/charset.sgml | 30 +++++++++++++++++++++++++++---
src/bin/initdb/initdb.c | 10 +++++++---
2 files changed, 34 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/charset.sgml b/doc/src/sgml/charset.sgml
index 3032392b80..13ec238a81 100644
--- a/doc/src/sgml/charset.sgml
+++ b/doc/src/sgml/charset.sgml
@@ -659,9 +659,33 @@ <title>Standard Collations</title>
</para>
<para>
- Additionally, the SQL standard collation name <literal>ucs_basic</literal>
- is available for encoding <literal>UTF8</literal>. It is equivalent
- to <literal>C</literal> and sorts by Unicode code point.
+ Additionally, two SQL standard collation names are available for encoding
+ <literal>UTF8</literal>:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>unicode</literal></term>
+ <listitem>
+ <para>
+ This collation sorts using the Unicode Collation Algorithm with the
+ Default Unicode Collation Element Table. (This is the same behavior
+ as the ICU root locale; see <xref
+ linkend="collation-managing-predefined-icu-und-x-icu"/>.) This
+ collation is only available when ICU support is configured.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ucs_basic</literal></term>
+ <listitem>
+ <para>
+ This collation sorts by Unicode code point. (This is the same
+ behavior as the libc locale specification <literal>C</literal>.)
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
</para>
</sect3>
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 7a58c33ace..525bec4b44 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1482,10 +1482,14 @@ static void
setup_collation(FILE *cmdfd)
{
/*
- * Add an SQL-standard name. We don't want to pin this, so it doesn't go
- * in pg_collation.h. But add it before reading system collations, so
- * that it wins if libc defines a locale named ucs_basic.
+ * Add SQL-standard names. We don't want to pin these, so they don't go
+ * in pg_collation.h. But add them before reading system collations, so
+ * that they win if libc defines a locale with the same name.
*/
+ PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, colliculocale)"
+ "VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'unicode', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'und');\n\n",
+ BOOTSTRAP_SUPERUSERID, COLLPROVIDER_ICU, PG_UTF8);
+
PG_CMD_PRINTF("INSERT INTO pg_collation (oid, collname, collnamespace, collowner, collprovider, collisdeterministic, collencoding, collcollate, collctype)"
"VALUES (pg_nextoid('pg_catalog.pg_collation', 'oid', 'pg_catalog.pg_collation_oid_index'), 'ucs_basic', 'pg_catalog'::regnamespace, %u, '%c', true, %d, 'C', 'C');\n\n",
BOOTSTRAP_SUPERUSERID, COLLPROVIDER_LIBC, PG_UTF8);
--
2.39.2
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add standard collation UNICODE
2023-03-01 10:09 Add standard collation UNICODE Peter Eisentraut <[email protected]>
@ 2023-03-02 00:05 ` Vik Fearing <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Vik Fearing @ 2023-03-02 00:05 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; pgsql-hackers
On 3/1/23 11:09, Peter Eisentraut wrote:
> The SQL standard defines several standard collations. Most of them are
> only of legacy interest (IMO), but two are currently relevant: UNICODE
> and UCS_BASIC. UNICODE sorts by the default Unicode collation algorithm
> specifications and UCS_BASIC sorts by codepoint.
>
> When collation support was added to PostgreSQL, we added UCS_BASIC,
> since that could easily be mapped to the C locale. But there was no
> straightforward way to provide the UNICODE collation. (Recall that
> collation support came several releases before ICU support.)
>
> With ICU support, we can provide the UNICODE collation, since it's just
> the root locale. I suppose one hesitation was that ICU was not a
> standard feature, so this would create variations in the default catalog
> contents, or something like that. But I think now that we are drifting
> to make ICU more prominent, we can just add that anyway. I think being
> able to say
>
> COLLATE UNICODE
>
> instead of
>
> COLLATE "und-x-icu"
>
> or whatever it is, is pretty useful.
>
> So, attached is a small patch to add this.
I don't feel competent to review the patch (simple as it is), but +1 on
the principle.
--
Vik Fearing
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add standard collation UNICODE
2023-03-01 10:09 Add standard collation UNICODE Peter Eisentraut <[email protected]>
@ 2023-04-27 11:44 ` Daniel Verite <[email protected]>
2023-05-08 15:48 ` Re: Add standard collation UNICODE Peter Eisentraut <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Daniel Verite @ 2023-04-27 11:44 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: pgsql-hackers
Peter Eisentraut wrote:
> COLLATE UNICODE
>
> instead of
>
> COLLATE "und-x-icu"
>
> or whatever it is, is pretty useful.
>
> So, attached is a small patch to add this.
This collation has an empty pg_collation.collversion column, instead
of being set to the same value as "und-x-icu" to track its version.
postgres=# select * from pg_collation where collname='unicode' \gx
-[ RECORD 1 ]-------+--------
oid | 963
collname | unicode
collnamespace | 11
collowner | 10
collprovider | i
collisdeterministic | t
collencoding | -1
collcollate |
collctype |
colliculocale | und
collicurules |
collversion |
The original patch implements this as an INSERT in which it would be easy to
fix I guess, but in current HEAD it comes as an entry in
include/catalog/pg_collation.dat:
{ oid => '963',
descr => 'sorts using the Unicode Collation Algorithm with default
settings',
collname => 'unicode', collprovider => 'i', collencoding => '-1',
colliculocale => 'und' },
Should it be converted back into an INSERT or better left
in this file and collversion being updated afterwards?
Best regards,
--
Daniel Vérité
https://postgresql.verite.pro/
Twitter: @DanielVerite
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add standard collation UNICODE
2023-03-01 10:09 Add standard collation UNICODE Peter Eisentraut <[email protected]>
2023-04-27 11:44 ` Re: Add standard collation UNICODE Daniel Verite <[email protected]>
@ 2023-05-08 15:48 ` Peter Eisentraut <[email protected]>
2023-05-12 08:04 ` Re: Add standard collation UNICODE Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Peter Eisentraut @ 2023-05-08 15:48 UTC (permalink / raw)
To: Daniel Verite <[email protected]>; +Cc: pgsql-hackers
On 27.04.23 13:44, Daniel Verite wrote:
> This collation has an empty pg_collation.collversion column, instead
> of being set to the same value as "und-x-icu" to track its version.
> The original patch implements this as an INSERT in which it would be easy to
> fix I guess, but in current HEAD it comes as an entry in
> include/catalog/pg_collation.dat:
>
> { oid => '963',
> descr => 'sorts using the Unicode Collation Algorithm with default
> settings',
> collname => 'unicode', collprovider => 'i', collencoding => '-1',
> colliculocale => 'und' },
>
> Should it be converted back into an INSERT or better left
> in this file and collversion being updated afterwards?
How about we do it with an UPDATE command. We already do this for
pg_database in a similar way. See attached patch.
From 91f2aff04f9bf137e4ac6e7df624dde770503bfd Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 8 May 2023 17:45:46 +0200
Subject: [PATCH] initdb: Set collversion for standard collation UNICODE
Discussion: https://www.postgresql.org/message-id/[email protected]
---
src/bin/initdb/initdb.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 2c208ead01..632f6c9c72 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1695,6 +1695,13 @@ setup_description(FILE *cmdfd)
static void
setup_collation(FILE *cmdfd)
{
+ /*
+ * Set the collation version for collations defined in pg_collation.dat,
+ * except the ones where we know that the collation behavior will never
+ * change.
+ */
+ PG_CMD_PUTS("UPDATE pg_collation SET collversion = pg_collation_actual_version(oid) WHERE collname = 'unicode';\n\n");
+
/* Import all collations we can find in the operating system */
PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
}
--
2.40.0
Attachments:
[text/plain] 0001-initdb-Set-collversion-for-standard-collation-UNICOD.patch (1.1K, ../../[email protected]/2-0001-initdb-Set-collversion-for-standard-collation-UNICOD.patch)
download | inline diff:
From 91f2aff04f9bf137e4ac6e7df624dde770503bfd Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 8 May 2023 17:45:46 +0200
Subject: [PATCH] initdb: Set collversion for standard collation UNICODE
Discussion: https://www.postgresql.org/message-id/[email protected]
---
src/bin/initdb/initdb.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 2c208ead01..632f6c9c72 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1695,6 +1695,13 @@ setup_description(FILE *cmdfd)
static void
setup_collation(FILE *cmdfd)
{
+ /*
+ * Set the collation version for collations defined in pg_collation.dat,
+ * except the ones where we know that the collation behavior will never
+ * change.
+ */
+ PG_CMD_PUTS("UPDATE pg_collation SET collversion = pg_collation_actual_version(oid) WHERE collname = 'unicode';\n\n");
+
/* Import all collations we can find in the operating system */
PG_CMD_PUTS("SELECT pg_import_system_collations('pg_catalog');\n\n");
}
--
2.40.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Add standard collation UNICODE
2023-03-01 10:09 Add standard collation UNICODE Peter Eisentraut <[email protected]>
2023-04-27 11:44 ` Re: Add standard collation UNICODE Daniel Verite <[email protected]>
2023-05-08 15:48 ` Re: Add standard collation UNICODE Peter Eisentraut <[email protected]>
@ 2023-05-12 08:04 ` Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Peter Eisentraut @ 2023-05-12 08:04 UTC (permalink / raw)
To: Daniel Verite <[email protected]>; +Cc: pgsql-hackers
On 08.05.23 17:48, Peter Eisentraut wrote:
> On 27.04.23 13:44, Daniel Verite wrote:
>> This collation has an empty pg_collation.collversion column, instead
>> of being set to the same value as "und-x-icu" to track its version.
>
>> The original patch implements this as an INSERT in which it would be
>> easy to
>> fix I guess, but in current HEAD it comes as an entry in
>> include/catalog/pg_collation.dat:
>>
>> { oid => '963',
>> descr => 'sorts using the Unicode Collation Algorithm with default
>> settings',
>> collname => 'unicode', collprovider => 'i', collencoding => '-1',
>> colliculocale => 'und' },
>>
>> Should it be converted back into an INSERT or better left
>> in this file and collversion being updated afterwards?
>
> How about we do it with an UPDATE command. We already do this for
> pg_database in a similar way. See attached patch.
This has been committed.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2023-05-12 08:04 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-06 22:42 [PATCH v4 2/3] Allow CREATE INDEX CONCURRENTLY on partitioned table Justin Pryzby <[email protected]>
2023-03-01 10:09 Add standard collation UNICODE Peter Eisentraut <[email protected]>
2023-03-02 00:05 ` Re: Add standard collation UNICODE Vik Fearing <[email protected]>
2023-04-27 11:44 ` Re: Add standard collation UNICODE Daniel Verite <[email protected]>
2023-05-08 15:48 ` Re: Add standard collation UNICODE Peter Eisentraut <[email protected]>
2023-05-12 08:04 ` Re: Add standard collation UNICODE Peter Eisentraut <[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