($INBOX_DIR/description missing)
help / color / mirror / Atom feedpg_waldump command line arguments
10+ messages / 8 participants
[nested] [flat]
* pg_waldump command line arguments
@ 2017-06-15 21:08 Robert Haas <[email protected]>
0 siblings, 2 replies; 10+ messages in thread
From: Robert Haas @ 2017-06-15 21:08 UTC (permalink / raw)
To: pgsql-hackers
pg_waldump --help claims that you run it like this:
Usage:
pg_waldump [OPTION]... [STARTSEG [ENDSEG]]
And https://www.postgresql.org/docs/10/static/pgwaldump.html agrees.
Since square brackets indicate optional arguments, this sort of makes
it sound like running pg_waldump with no arguments ought to work. But
it doesn't:
$ pg_waldump
pg_waldump: no arguments specified
Try "pg_waldump --help" for more information.
If we removed the error check that displays "pg_waldump: no arguments
specified", then it would still fail, but with a more useful error
message:
$ pg_waldump --
pg_waldump: no start WAL location given
Try "pg_waldump --help" for more information.
That message ought to perhaps be changed to say that you specified
neither the start WAL location nor the start WAL file, but even as it
stands it's certainly better than "no arguments specified".
Another problem is that if the file name you pass to pg_waldump
doesn't happen to have a name that looks like a WAL file, it fails in
a completely ridiculous fashion:
$ pg_waldump /etc/passwd
pg_waldump: FATAL: could not find file "000000017C55C16F000000FF": No
such file or directory
The problem appears to be that fuzzy_open_file() successfully opens
the file and then invokes XLogFromFileName() on the filename.
XLogFromFileName() calls sscanf() on the file name without any error
checking, which I think results in leaving private.timeline
uninitialized and setting segno to whatever preexisting garbage was in
the log and segno variables declared inside XLogFromFileName(),
resulting in an attempt to find a more or less completely random file.
A slightly broader concern is whether we need to require the start
position at all. It seems like one could locate the WAL directory
using the existing logic, then search for the earliest file. It might
be a little unclear what "earliest" means when multiple timelines are
present, but I bet we could come up with some behavior that would be
convenient for most users. It would be quite handy to be able to run
this without arguments (or just with -z) and have it process all the
WAL files that you've got on hand.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_waldump command line arguments
@ 2017-06-16 08:55 Ashutosh Bapat <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Ashutosh Bapat @ 2017-06-16 08:55 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: pgsql-hackers
On Fri, Jun 16, 2017 at 2:38 AM, Robert Haas <[email protected]> wrote:
>
> A slightly broader concern is whether we need to require the start
> position at all. It seems like one could locate the WAL directory
> using the existing logic, then search for the earliest file. It might
> be a little unclear what "earliest" means when multiple timelines are
> present, but I bet we could come up with some behavior that would be
> convenient for most users.
We already have some default behaviour defined
--
-t timeline
--timeline=timeline
Timeline from which to read log records. The default is to use the
value in startseg, if that is specified; otherwise, the default is 1.
--
So, if startseg is not provided, choose the earliest file in the
default timeline (given by -t 1 when specified).
> It would be quite handy to be able to run
> this without arguments (or just with -z) and have it process all the
> WAL files that you've got on hand.
>
+1.
--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: pg_waldump command line arguments
@ 2017-06-16 17:58 Andres Freund <[email protected]>
parent: Robert Haas <[email protected]>
1 sibling, 0 replies; 10+ messages in thread
From: Andres Freund @ 2017-06-16 17:58 UTC (permalink / raw)
To: Robert Haas <[email protected]>; +Cc: pgsql-hackers
On 2017-06-15 17:08:23 -0400, Robert Haas wrote:
> pg_waldump --help claims that you run it like this:
>
> Usage:
> pg_waldump [OPTION]... [STARTSEG [ENDSEG]]
>
> And https://www.postgresql.org/docs/10/static/pgwaldump.html agrees.
> Since square brackets indicate optional arguments, this sort of makes
> it sound like running pg_waldump with no arguments ought to work. But
> it doesn't:
Well, not really, it indicates that positional arguments are allowed,
but not required. You can get by with with -s / -e, which are sometimes
important, if you want to look at multiple timelines etc.
> A slightly broader concern is whether we need to require the start
> position at all. It seems like one could locate the WAL directory
> using the existing logic, then search for the earliest file.
"earliest file" isn't actually that trivial to determine if there's
timelines etc. But leaving that aside, it'll be frequently so much data
that'll be output, that it'd make the output pretty much useless, no? I
think if we were to add a bit more magic, it'd make more sense to parse
pg_control and start at the last flushed point nof WAL forward,
especially with -f.
> It might be a little unclear what "earliest" means when multiple
> timelines are present, but I bet we could come up with some behavior
> that would be convenient for most users. It would be quite handy to
> be able to run this without arguments (or just with -z) and have it
> process all the WAL files that you've got on hand.
With -z I agree, probably best by parsing pg_control and parsing
[checkpoint - 1, minRecoveryPoint) or such.
I'm willing to review some patches here, but I don't plan to personally
work on patches around this...
Greetings,
Andres Freund
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 10+ messages in thread
* [PATCH] Let complete segment archived immediately on standy
@ 2020-06-29 07:12 Kyotaro Horiguchi <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Kyotaro Horiguchi @ 2020-06-29 07:12 UTC (permalink / raw)
walreceiver marks a completed segment as ".ready" after any data for
the next segment comes. So standby can archive a WAL segment later
than the primary archives the same segment. Let walreceiver archive a
segment as soon as it is completed.
---
src/backend/replication/walreceiver.c | 77 +++++++++++++++------------
1 file changed, 42 insertions(+), 35 deletions(-)
diff --git a/src/backend/replication/walreceiver.c b/src/backend/replication/walreceiver.c
index d1ad75da87..06c1e3cbe4 100644
--- a/src/backend/replication/walreceiver.c
+++ b/src/backend/replication/walreceiver.c
@@ -902,44 +902,10 @@ XLogWalRcvWrite(char *buf, Size nbytes, XLogRecPtr recptr)
{
int segbytes;
- if (recvFile < 0 || !XLByteInSeg(recptr, recvSegNo, wal_segment_size))
+ if (recvFile < 0)
{
bool use_existent;
- /*
- * fsync() and close current file before we switch to next one. We
- * would otherwise have to reopen this file to fsync it later
- */
- if (recvFile >= 0)
- {
- char xlogfname[MAXFNAMELEN];
-
- XLogWalRcvFlush(false);
-
- XLogFileName(xlogfname, recvFileTLI, recvSegNo, wal_segment_size);
-
- /*
- * XLOG segment files will be re-read by recovery in startup
- * process soon, so we don't advise the OS to release cache
- * pages associated with the file like XLogFileClose() does.
- */
- if (close(recvFile) != 0)
- ereport(PANIC,
- (errcode_for_file_access(),
- errmsg("could not close log segment %s: %m",
- xlogfname)));
-
- /*
- * Create .done file forcibly to prevent the streamed segment
- * from being archived later.
- */
- if (XLogArchiveMode != ARCHIVE_MODE_ALWAYS)
- XLogArchiveForceDone(xlogfname);
- else
- XLogArchiveNotify(xlogfname);
- }
- recvFile = -1;
-
/* Create/use new log file */
XLByteToSeg(recptr, recvSegNo, wal_segment_size);
use_existent = true;
@@ -985,6 +951,47 @@ XLogWalRcvWrite(char *buf, Size nbytes, XLogRecPtr recptr)
buf += byteswritten;
LogstreamResult.Write = recptr;
+
+ /*
+ * Close the current WAL segment if it is completed then let the file
+ * be archived if needed.
+ */
+ if (!XLByteInSeg(recptr, recvSegNo, wal_segment_size))
+ {
+ char xlogfname[MAXFNAMELEN];
+
+ Assert (recvFile >= 0);
+
+ /*
+ * fsync() and close current file before we switch to next one. We
+ * would otherwise have to reopen this file to fsync it later
+ */
+ XLogWalRcvFlush(false);
+
+ XLogFileName(xlogfname, recvFileTLI, recvSegNo, wal_segment_size);
+
+ /*
+ * XLOG segment files will be re-read by recovery in startup
+ * process soon, so we don't advise the OS to release cache
+ * pages associated with the file like XLogFileClose() does.
+ */
+ if (close(recvFile) != 0)
+ ereport(PANIC,
+ (errcode_for_file_access(),
+ errmsg("could not close log segment %s: %m",
+ xlogfname)));
+
+ /*
+ * Create .done file forcibly to prevent the streamed segment
+ * from being archived later.
+ */
+ if (XLogArchiveMode != ARCHIVE_MODE_ALWAYS)
+ XLogArchiveForceDone(xlogfname);
+ else
+ XLogArchiveNotify(xlogfname);
+
+ recvFile = -1;
+ }
}
/* Update shared-memory status */
--
2.18.4
----Next_Part(Mon_Jun_29_16_41_11_2020_855)----
^ permalink raw reply [nested|flat] 10+ messages in thread
* Exclusion constraints on partitioned tables
@ 2022-12-15 23:33 Paul Jungwirth <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Paul Jungwirth @ 2022-12-15 23:33 UTC (permalink / raw)
To: pgsql-hackers
Hello Hackers,
I'm trying to get things going again on my temporal tables work, and
here is a small patch to move that forward.
It lets you create exclusion constraints on partitioned tables, similar
to today's rules for b-tree primary keys & unique constraints:
just as we permit a PK on a partitioned table when the PK's columns are
a superset of the partition keys, so we could also allow an exclusion
constraint when its columns are a superset of the partition keys.
This patch also requires the matching constraint columns to use equality
comparisons (`(foo WITH =)`), so it is really equivalent to the existing
b-tree rule. Perhaps that is more conservative than necessary, but we
can't permit an arbitrary operator, since some might require testing
rows that fall into other partitions. For example `(foo WITH <>)` would
obviously cause problems.
The exclusion constraint may still include other columns beyond the
partition keys, and those may use equality operators or something else.
This patch is required to support temporal partitioned tables, because
temporal tables use exclusion constraints as their primary key.
Essentially they are `(id WITH =, valid_at with &&)`. Since the primary
key is not a b-tree, partitioning them would be forbidden prior to this
patch. But now you could partition that table on `id`, and we could
still correctly validate the temporal PK without requiring rows from
other partitions.
This patch may be helpful beyond just temporal tables (or for DIY
temporal tables), so it seems worth submitting it separately.
Yours,
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] v1-0001-Allow-some-exclusion-constraints-on-partitions.patch (20.8K, ../../[email protected]/2-v1-0001-Allow-some-exclusion-constraints-on-partitions.patch)
download | inline diff:
From 7daadf9e822509186c9e32794d0e29effdc90edc Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Wed, 23 Nov 2022 14:55:43 -0800
Subject: [PATCH v1] Allow some exclusion constraints on partitions
Previously we only allowed UNIQUE B-tree constraints on partitions
(and only if the constraint included all the partition keys). But we
could allow exclusion constraints with the same restriction. We also
require that those columns be compared for equality, not something like
&&.
---
doc/src/sgml/ddl.sgml | 12 ++--
src/backend/commands/indexcmds.c | 36 +++++++++--
src/backend/parser/parse_utilcmd.c | 6 --
src/test/regress/expected/alter_table.out | 31 +++++++--
src/test/regress/expected/create_table.out | 16 +++--
src/test/regress/expected/indexing.out | 73 ++++++++++++++++++----
src/test/regress/sql/alter_table.sql | 29 +++++++--
src/test/regress/sql/create_table.sql | 13 +++-
src/test/regress/sql/indexing.sql | 57 +++++++++++++++--
9 files changed, 221 insertions(+), 52 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..59be911471 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4206,11 +4206,13 @@ ALTER INDEX measurement_city_id_logdate_key
<listitem>
<para>
- There is no way to create an exclusion constraint spanning the
- whole partitioned table. It is only possible to put such a
- constraint on each leaf partition individually. Again, this
- limitation stems from not being able to enforce cross-partition
- restrictions.
+ Similarly an exclusion constraint must include all the
+ partition key columns. Furthermore the constraint must compare those
+ columns for equality (not e.g. <literal>&&</literal>).
+ Again, this limitation stems from not being able to enforce
+ cross-partition restrictions. The constraint may include additional
+ columns that aren't part of the partition key, and it may compare
+ those with any operators you like.
</para>
</listitem>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 7dc1aca8fe..28840544b5 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -709,11 +709,6 @@ DefineIndex(Oid relationId,
(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),
- errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
- RelationGetRelationName(rel))));
}
/*
@@ -926,7 +921,7 @@ DefineIndex(Oid relationId,
* We could lift this limitation if we had global indexes, but those have
* their own problems, so this is a useful feature combination.
*/
- if (partitioned && (stmt->unique || stmt->primary))
+ if (partitioned && (stmt->unique || stmt->primary || stmt->excludeOpNames != NIL))
{
PartitionKey key = RelationGetPartitionKey(rel);
const char *constraint_type;
@@ -983,6 +978,8 @@ DefineIndex(Oid relationId,
*/
if (accessMethodId == BTREE_AM_OID)
eq_strategy = BTEqualStrategyNumber;
+ else if (accessMethodId == GIST_AM_OID)
+ eq_strategy = RTEqualStrategyNumber;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1020,11 +1017,38 @@ DefineIndex(Oid relationId,
idx_opcintype,
idx_opcintype,
eq_strategy);
+
+ /* For exclusion constraints, fail on columns that don't compare for equality. */
+ if (stmt->excludeOpNames != NIL && indexInfo->ii_ExclusionStrats[j] != BTEqualStrategyNumber && indexInfo->ii_ExclusionStrats[j] != RTEqualStrategyNumber)
+ {
+ Form_pg_attribute att;
+
+ att = TupleDescAttr(RelationGetDescr(rel),
+ key->partattrs[i] - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot match partition key to index on column \"%s\" using non-equal operator \"%s\".",
+ NameStr(att->attname), get_opname(indexInfo->ii_ExclusionOps[j]))));
+ }
+
if (ptkey_eqop == idx_eqop)
{
found = true;
break;
}
+ else if (eq_strategy == RTEqualStrategyNumber)
+ {
+ /* For exclusion constraints, check BTEqualStrategy too. */
+ idx_eqop = get_opfamily_member(idx_opfamily,
+ idx_opcintype,
+ idx_opcintype,
+ BTEqualStrategyNumber);
+ if (ptkey_eqop == idx_eqop)
+ {
+ found = true;
+ break;
+ }
+ }
}
}
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index f743cd548c..27124720fa 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -904,12 +904,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("exclusion constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("exclusion constraints are not supported on partitioned tables"),
- parser_errposition(cxt->pstate,
- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 600e603bdf..57c3a674c8 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3797,16 +3797,35 @@ Referenced by:
TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- supported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+-- unsupported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "partitioned" lacks column "b" which is part of the partition key.
+DROP TABLE partitioned;
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH -|-);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+DROP TABLE partitioned;
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
- ^
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 5eace915a7..02fbd8b433 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -153,14 +153,18 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
--- unsupported constraint type for partitioned tables
+-- exclusion constraint type for partitioned tables
CREATE TABLE partitioned (
- a int,
- EXCLUDE USING gist (a WITH &&)
+ a int4range,
+ EXCLUDE USING gist (a WITH =)
+) PARTITION BY RANGE (a);
+DROP TABLE partitioned;
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ EXCLUDE USING gist (a WITH -|-)
) PARTITION BY RANGE (a);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 3: EXCLUDE USING gist (a WITH &&)
- ^
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
-- prevent using prohibited expressions in the key
CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 1bdd430f06..6f8b15c315 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -986,11 +986,32 @@ DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "a" which is par
-- OK if you use them in some other order
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- not other types of index-based constraints
-create table idxpart (a int, exclude (a with = )) partition by range (a);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: create table idxpart (a int, exclude (a with = )) partition ...
- ^
+-- OK to add an exclusion constraint if partitioning by its equal column
+create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
+-- Not OK with just -|-
+create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
+drop table idxpart;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
ERROR: unsupported PRIMARY KEY constraint with partition key definition
@@ -1047,12 +1068,42 @@ Indexes:
Number of partitions: 0
drop table idxpart;
--- Exclusion constraints cannot be added
-create table idxpart (a int, b int) partition by range (a);
-alter table idxpart add exclude (a with =);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: alter table idxpart add exclude (a with =);
- ^
+-- Exclusion constraints can be added if a partitioning by their equal column
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
+drop table idxpart;
+-- Not OK with just -|-
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with -|-);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+drop table idxpart;
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with &&);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (b with =, c with &&);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
+drop table idxpart;
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index f58b2f75d5..6ea923d101 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2315,14 +2315,35 @@ ALTER TABLE ataddindex
\d ataddindex
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- supported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH -|-);
+DROP TABLE partitioned;
+
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
ALTER TABLE partitioned DROP COLUMN b;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 93ccf77d4a..35ba4f4f2b 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -106,10 +106,17 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
--- unsupported constraint type for partitioned tables
+-- exclusion constraint type for partitioned tables
CREATE TABLE partitioned (
- a int,
- EXCLUDE USING gist (a WITH &&)
+ a int4range,
+ EXCLUDE USING gist (a WITH =)
+) PARTITION BY RANGE (a);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ EXCLUDE USING gist (a WITH -|-)
) PARTITION BY RANGE (a);
-- prevent using prohibited expressions in the key
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 429120e710..198a368a64 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -483,8 +483,27 @@ create table idxpart (a int, b int primary key) partition by range (b, a);
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- not other types of index-based constraints
-create table idxpart (a int, exclude (a with = )) partition by range (a);
+-- OK to add an exclusion constraint if partitioning by its equal column
+create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
+-- Not OK with just -|-
+create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
+drop table idxpart;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
@@ -506,9 +525,37 @@ alter table idxpart add unique (b, a); -- this works
\d idxpart
drop table idxpart;
--- Exclusion constraints cannot be added
-create table idxpart (a int, b int) partition by range (a);
-alter table idxpart add exclude (a with =);
+-- Exclusion constraints can be added if a partitioning by their equal column
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- Not OK with just -|-
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with -|-);
+drop table idxpart;
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with &&);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (b with =, c with &&);
+drop table idxpart;
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
--
2.25.1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Exclusion constraints on partitioned tables
@ 2022-12-16 00:12 Tom Lane <[email protected]>
parent: Paul Jungwirth <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2022-12-16 00:12 UTC (permalink / raw)
To: Paul Jungwirth <[email protected]>; +Cc: pgsql-hackers
Paul Jungwirth <[email protected]> writes:
> It lets you create exclusion constraints on partitioned tables, similar
> to today's rules for b-tree primary keys & unique constraints:
> just as we permit a PK on a partitioned table when the PK's columns are
> a superset of the partition keys, so we could also allow an exclusion
> constraint when its columns are a superset of the partition keys.
OK. AFAICS that works in principle.
> This patch also requires the matching constraint columns to use equality
> comparisons (`(foo WITH =)`), so it is really equivalent to the existing
> b-tree rule.
That's not quite good enough: you'd better enforce that it's the same
equality operator (and same collation, if relevant) as is being used
in the partition key. Remember that we don't have a requirement that
a datatype have only one equality operator; and these days I think
collation can affect equality, too.
Another problem is that while we can safely assume that we know what
BTEqualStrategyNumber means in btree, we can NOT assume that we know
what gist opclass strategy numbers mean: each opclass is free to
define those as it sees fit. The part of your patch that is looking
at RTEqualStrategyNumber seems dangerously broken to me.
It might work better to consider the operator itself and ask if
it's equality in the same btree opfamily that's used by the
partition key. (Hm, do we use btree opfamilies for all types of
partitioning?)
Anyway, I think something can be made of this, but you need to be less
fuzzy about matching the equality semantics of the partition key.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Exclusion constraints on partitioned tables
@ 2022-12-16 05:11 Paul Jungwirth <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Paul Jungwirth @ 2022-12-16 05:11 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On 12/15/22 16:12, Tom Lane wrote:
>> This patch also requires the matching constraint columns to use equality
>> comparisons (`(foo WITH =)`), so it is really equivalent to the existing
>> b-tree rule.
>
> That's not quite good enough: you'd better enforce that it's the same
> equality operator (and same collation, if relevant) as is being used
> in the partition key.
> [snip]
> It might work better to consider the operator itself and ask if
> it's equality in the same btree opfamily that's used by the
> partition key.
Thank you for taking a look! Here is a comparison on just the operator
itself.
I included a collation check too, but I'm not sure it's necessary.
Exclusion constraints don't have a collation per se; it comes from the
index, and we choose it just a little above in this function. (I'm not
even sure how to elicit that new error message in a test case.)
I'm not sure what to do about matching the opfamily. In practice an
exclusion constraint will typically use gist, but the partition key will
always use btree/hash. You're saying that the equals operator can be
inconsistent between those access methods? That is surprising to me, but
I admit op classes/families are still sinking in. (Even prior to this
patch, isn't the code for hash-based partitions looking up ptkey_eqop
via the hash opfamily, and then comparing it to idx_eqop looked up via
the btree opfamily?)
If partitions can only support btree-based exclusion constraints, you
still wouldn't be able to partition a temporal table, because those
constraints would always be gist. So I guess what I really want is to
support gist index constraints on partitioned tables.
Regards,
--
Paul ~{:-)
[email protected]
Attachments:
[text/x-patch] v2-0001-Allow-some-exclusion-constraints-on-partitions.patch (21.0K, ../../[email protected]/2-v2-0001-Allow-some-exclusion-constraints-on-partitions.patch)
download | inline diff:
From df25cb63cacd992fc7453fc432488c6046e59479 Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <[email protected]>
Date: Wed, 23 Nov 2022 14:55:43 -0800
Subject: [PATCH v2] Allow some exclusion constraints on partitions
Previously we only allowed UNIQUE B-tree constraints on partitions
(and only if the constraint included all the partition keys). But we
could allow exclusion constraints with the same restriction. We also
require that those columns be compared for equality, not something like
&&.
---
doc/src/sgml/ddl.sgml | 12 ++--
src/backend/commands/indexcmds.c | 42 +++++++++++--
src/backend/parser/parse_utilcmd.c | 6 --
src/test/regress/expected/alter_table.out | 31 +++++++--
src/test/regress/expected/create_table.out | 16 +++--
src/test/regress/expected/indexing.out | 73 ++++++++++++++++++----
src/test/regress/sql/alter_table.sql | 29 +++++++--
src/test/regress/sql/create_table.sql | 13 +++-
src/test/regress/sql/indexing.sql | 57 +++++++++++++++--
9 files changed, 227 insertions(+), 52 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 6e92bbddd2..59be911471 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -4206,11 +4206,13 @@ ALTER INDEX measurement_city_id_logdate_key
<listitem>
<para>
- There is no way to create an exclusion constraint spanning the
- whole partitioned table. It is only possible to put such a
- constraint on each leaf partition individually. Again, this
- limitation stems from not being able to enforce cross-partition
- restrictions.
+ Similarly an exclusion constraint must include all the
+ partition key columns. Furthermore the constraint must compare those
+ columns for equality (not e.g. <literal>&&</literal>).
+ Again, this limitation stems from not being able to enforce
+ cross-partition restrictions. The constraint may include additional
+ columns that aren't part of the partition key, and it may compare
+ those with any operators you like.
</para>
</listitem>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 7dc1aca8fe..a9ecb7df19 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -709,11 +709,6 @@ DefineIndex(Oid relationId,
(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),
- errmsg("cannot create exclusion constraints on partitioned table \"%s\"",
- RelationGetRelationName(rel))));
}
/*
@@ -926,7 +921,7 @@ DefineIndex(Oid relationId,
* We could lift this limitation if we had global indexes, but those have
* their own problems, so this is a useful feature combination.
*/
- if (partitioned && (stmt->unique || stmt->primary))
+ if (partitioned && (stmt->unique || stmt->primary || stmt->excludeOpNames != NIL))
{
PartitionKey key = RelationGetPartitionKey(rel);
const char *constraint_type;
@@ -983,6 +978,8 @@ DefineIndex(Oid relationId,
*/
if (accessMethodId == BTREE_AM_OID)
eq_strategy = BTEqualStrategyNumber;
+ else if (accessMethodId == GIST_AM_OID)
+ eq_strategy = RTEqualStrategyNumber;
else
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
@@ -1020,8 +1017,41 @@ DefineIndex(Oid relationId,
idx_opcintype,
idx_opcintype,
eq_strategy);
+
if (ptkey_eqop == idx_eqop)
{
+ /* For exclusion constraints, fail on columns that don't compare for equality. */
+ if (stmt->excludeOpNames != NIL)
+ {
+ if (idx_eqop != indexInfo->ii_ExclusionOps[j])
+ {
+ Form_pg_attribute att;
+
+ att = TupleDescAttr(RelationGetDescr(rel),
+ key->partattrs[i] - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot match partition key to index on column \"%s\" using non-equal operator \"%s\".",
+ NameStr(att->attname), get_opname(indexInfo->ii_ExclusionOps[j]))));
+ }
+
+ /*
+ * Require a matching collation too.
+ * This should have been set correctly above, but it doesn't hurt to check.
+ */
+ if (key->partcollation[i] != collationObjectId[j])
+ {
+ Form_pg_attribute att;
+
+ att = TupleDescAttr(RelationGetDescr(rel),
+ key->partattrs[i] - 1);
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot match partition key to index on column \"%s\" using collation \"%s\".",
+ NameStr(att->attname), get_collation_name(collationObjectId[j]))));
+ }
+ }
+
found = true;
break;
}
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index f743cd548c..27124720fa 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -904,12 +904,6 @@ transformTableConstraint(CreateStmtContext *cxt, Constraint *constraint)
errmsg("exclusion constraints are not supported on foreign tables"),
parser_errposition(cxt->pstate,
constraint->location)));
- if (cxt->ispartitioned)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("exclusion constraints are not supported on partitioned tables"),
- parser_errposition(cxt->pstate,
- constraint->location)));
cxt->ixconstraints = lappend(cxt->ixconstraints, constraint);
break;
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 600e603bdf..57c3a674c8 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -3797,16 +3797,35 @@ Referenced by:
TABLE "ataddindex" CONSTRAINT "ataddindex_ref_id_fkey" FOREIGN KEY (ref_id) REFERENCES ataddindex(id)
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- supported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+-- unsupported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "partitioned" lacks column "b" which is part of the partition key.
+DROP TABLE partitioned;
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH -|-);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+DROP TABLE partitioned;
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
- ^
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ERROR: cannot drop column "a" because it is part of the partition key of relation "partitioned"
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 5eace915a7..02fbd8b433 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -153,14 +153,18 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
ERROR: cannot use "list" partition strategy with more than one column
--- unsupported constraint type for partitioned tables
+-- exclusion constraint type for partitioned tables
CREATE TABLE partitioned (
- a int,
- EXCLUDE USING gist (a WITH &&)
+ a int4range,
+ EXCLUDE USING gist (a WITH =)
+) PARTITION BY RANGE (a);
+DROP TABLE partitioned;
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ EXCLUDE USING gist (a WITH -|-)
) PARTITION BY RANGE (a);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 3: EXCLUDE USING gist (a WITH &&)
- ^
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
-- prevent using prohibited expressions in the key
CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE;
CREATE TABLE partitioned (
diff --git a/src/test/regress/expected/indexing.out b/src/test/regress/expected/indexing.out
index 1bdd430f06..6f8b15c315 100644
--- a/src/test/regress/expected/indexing.out
+++ b/src/test/regress/expected/indexing.out
@@ -986,11 +986,32 @@ DETAIL: PRIMARY KEY constraint on table "idxpart" lacks column "a" which is par
-- OK if you use them in some other order
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- not other types of index-based constraints
-create table idxpart (a int, exclude (a with = )) partition by range (a);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: create table idxpart (a int, exclude (a with = )) partition ...
- ^
+-- OK to add an exclusion constraint if partitioning by its equal column
+create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
+-- Not OK with just -|-
+create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
+drop table idxpart;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
ERROR: unsupported PRIMARY KEY constraint with partition key definition
@@ -1047,12 +1068,42 @@ Indexes:
Number of partitions: 0
drop table idxpart;
--- Exclusion constraints cannot be added
-create table idxpart (a int, b int) partition by range (a);
-alter table idxpart add exclude (a with =);
-ERROR: exclusion constraints are not supported on partitioned tables
-LINE 1: alter table idxpart add exclude (a with =);
- ^
+-- Exclusion constraints can be added if a partitioning by their equal column
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "b" which is part of the partition key.
+drop table idxpart;
+-- Not OK with just -|-
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with -|-);
+ERROR: cannot match partition key to index on column "a" using non-equal operator "-|-".
+drop table idxpart;
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with &&);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (b with =, c with &&);
+ERROR: unique constraint on partitioned table must include all partitioning columns
+DETAIL: EXCLUDE constraint on table "idxpart" lacks column "a" which is part of the partition key.
+drop table idxpart;
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
create table idxpart (a int, b int, primary key (a, b)) partition by range (a, b);
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index f58b2f75d5..6ea923d101 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -2315,14 +2315,35 @@ ALTER TABLE ataddindex
\d ataddindex
DROP TABLE ataddindex;
--- unsupported constraint types for partitioned tables
+-- supported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint parts for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH =);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ b int4range
+) PARTITION BY RANGE (a, b);
+ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH -|-);
+DROP TABLE partitioned;
+
+-- cannot drop column that is part of the partition key
CREATE TABLE partitioned (
a int,
b int
) PARTITION BY RANGE (a, (a+b+1));
-ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&);
-
--- cannot drop column that is part of the partition key
ALTER TABLE partitioned DROP COLUMN a;
ALTER TABLE partitioned ALTER COLUMN a TYPE char(5);
ALTER TABLE partitioned DROP COLUMN b;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index 93ccf77d4a..35ba4f4f2b 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -106,10 +106,17 @@ CREATE TABLE partitioned (
a2 int
) PARTITION BY LIST (a1, a2); -- fail
--- unsupported constraint type for partitioned tables
+-- exclusion constraint type for partitioned tables
CREATE TABLE partitioned (
- a int,
- EXCLUDE USING gist (a WITH &&)
+ a int4range,
+ EXCLUDE USING gist (a WITH =)
+) PARTITION BY RANGE (a);
+DROP TABLE partitioned;
+
+-- unsupported exclusion constraint operator for partitioned tables
+CREATE TABLE partitioned (
+ a int4range,
+ EXCLUDE USING gist (a WITH -|-)
) PARTITION BY RANGE (a);
-- prevent using prohibited expressions in the key
diff --git a/src/test/regress/sql/indexing.sql b/src/test/regress/sql/indexing.sql
index 429120e710..198a368a64 100644
--- a/src/test/regress/sql/indexing.sql
+++ b/src/test/regress/sql/indexing.sql
@@ -483,8 +483,27 @@ create table idxpart (a int, b int primary key) partition by range (b, a);
create table idxpart (a int, b int, c text, primary key (a, b, c)) partition by range (b, c, a);
drop table idxpart;
--- not other types of index-based constraints
-create table idxpart (a int, exclude (a with = )) partition by range (a);
+-- OK to add an exclusion constraint if partitioning by its equal column
+create table idxpart (a int4range, exclude USING GIST (a with = )) partition by range (a);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a, b);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with =)) partition by range (a);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with = )) partition by range (a, b);
+-- Not OK with just -|-
+create table idxpart (a int4range, exclude USING GIST (a with -|- )) partition by range (a);
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range, exclude USING GIST (a with =, b with &&)) partition by range (a);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (b with =, c with &&)) partition by range (a);
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range, exclude USING GIST (a with =, b with =, c with &&)) partition by range (a, b);
+drop table idxpart;
-- no expressions in partition key for PK/UNIQUE
create table idxpart (a int primary key, b int) partition by range ((b + a));
@@ -506,9 +525,37 @@ alter table idxpart add unique (b, a); -- this works
\d idxpart
drop table idxpart;
--- Exclusion constraints cannot be added
-create table idxpart (a int, b int) partition by range (a);
-alter table idxpart add exclude (a with =);
+-- Exclusion constraints can be added if a partitioning by their equal column
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- OK more than one equal column
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- OK with more than one equal column: constraint is a proper superset of partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with =);
+drop table idxpart;
+-- Not OK more than one equal column: partition keys are a proper superset of constraint
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =);
+drop table idxpart;
+-- Not OK with just -|-
+create table idxpart (a int4range, b int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with -|-);
+drop table idxpart;
+-- OK with equals and &&, and equals is the partition key
+create table idxpart (a int4range, b int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (a with =, b with &&);
+drop table idxpart;
+-- Not OK with equals and &&, and equals is not the partition key
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a);
+alter table idxpart add exclude USING GIST (b with =, c with &&);
+drop table idxpart;
+-- OK more than one equal column and a && column
+create table idxpart (a int4range, b int4range, c int4range) partition by range (a, b);
+alter table idxpart add exclude USING GIST (a with =, b with =, c with &&);
drop table idxpart;
-- When (sub)partitions are created, they also contain the constraint
--
2.25.1
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Adding REPACK [concurrently]
@ 2026-02-15 16:03 Mihail Nikalayeu <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Mihail Nikalayeu @ 2026-02-15 16:03 UTC (permalink / raw)
To: Alvaro Herrera <[email protected]>; +Cc: Antonin Houska <[email protected]>; Pg Hackers <[email protected]>; Robert Treat <[email protected]>
Hello!
Some feedback for v33.
> else if (pg_strcasecmp(cmd, "REPACK") == 0)
> cmdtype = PROGRESS_COMMAND_REPACK;
src/backend/utils/adt/pgstatfuncs.c:290
I think we need to add "CLUSTER" here too to avoid regression.
-----------
> ConditionVariablePrepareToSleep(&shared->cv);
> for (;;)
> {
> bool initialized;
>
> SpinLockAcquire(&shared->mutex);
> initialized = shared->initialized;
> SpinLockRelease(&shared->mutex);
src/backend/commands/cluster.c:3663
I think we should check GetBackgroundWorkerPid for worker status, to
not wait forever in case of some issue with the worker.
-----------
> /* Error queue. */
> shm_mq *error_mq;
src/backend/commands/cluster.c:210.
Not used anywhere.
-----------
> finish_heap_swap(old_table_oid, new_table_oid,
> is_system_catalog,
> false, /* swap_toast_by_content */
> false, true, false,
> frozenXid, cutoffMulti,
> relpersistence);
src/backend/commands/cluster.c
I think we should add comments for other boolean parameters.
-----------
> elog(ERROR, "Incomplete insert info.");
> elog(ERROR, "Incomplete update info.");
src/backend/replication/pgoutput_repack/pgoutput_repack.c:118,132
Should be non-capitalized?
-----------
> # Copyright (c) 2022-2024, PostgreSQL Global Development Group
src/backend/replication/pgoutput_repack/meson.build
2022-2026
-----------
> int newxcnt = 0;
src/backend/replication/logical/snapbuild.c:53
uint32 is better here.
Best regards,
Mikhail.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Adding REPACK [concurrently]
@ 2026-02-16 07:47 Antonin Houska <[email protected]>
parent: Mihail Nikalayeu <[email protected]>
0 siblings, 1 reply; 10+ messages in thread
From: Antonin Houska @ 2026-02-16 07:47 UTC (permalink / raw)
To: Mihail Nikalayeu <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]>; Robert Treat <[email protected]>
Mihail Nikalayeu <[email protected]> wrote:
> Some feedback for v33.
Thanks again for your review!
> > else if (pg_strcasecmp(cmd, "REPACK") == 0)
> > cmdtype = PROGRESS_COMMAND_REPACK;
> src/backend/utils/adt/pgstatfuncs.c:290
>
> I think we need to add "CLUSTER" here too to avoid regression.
What kind of regression? There is no pg_stat_get_progress_info('CLUSTER') call
in system_views.sql.
> -----------
>
> > ConditionVariablePrepareToSleep(&shared->cv);
> > for (;;)
> > {
> > bool initialized;
> >
> > SpinLockAcquire(&shared->mutex);
> > initialized = shared->initialized;
> > SpinLockRelease(&shared->mutex);
> src/backend/commands/cluster.c:3663
>
> I think we should check GetBackgroundWorkerPid for worker status, to
> not wait forever in case of some issue with the worker.
ConditionVariableSleep() calls CHECK_FOR_INTERRUPTS(). That should process
error messages from the worker.
> -----------
>
> > /* Error queue. */
> > shm_mq *error_mq;
> src/backend/commands/cluster.c:210.
>
> Not used anywhere.
ok
> -----------
>
> > finish_heap_swap(old_table_oid, new_table_oid,
> > is_system_catalog,
> > false, /* swap_toast_by_content */
> > false, true, false,
> > frozenXid, cutoffMulti,
> > relpersistence);
> src/backend/commands/cluster.c
>
> I think we should add comments for other boolean parameters.
Perhaps, it wouldn't hurt.
> -----------
>
> > elog(ERROR, "Incomplete insert info.");
> > elog(ERROR, "Incomplete update info.");
> src/backend/replication/pgoutput_repack/pgoutput_repack.c:118,132
>
> Should be non-capitalized?
ok
> -----------
>
> > # Copyright (c) 2022-2024, PostgreSQL Global Development Group
> src/backend/replication/pgoutput_repack/meson.build
>
> 2022-2026
ok
> -----------
>
> > int newxcnt = 0;
> src/backend/replication/logical/snapbuild.c:53
>
> uint32 is better here.
This was already discussed:
https://www.postgresql.org/message-id/137668.1768235610%40localhost
--
Antonin Houska
Web: https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Adding REPACK [concurrently]
@ 2026-02-16 11:16 Mihail Nikalayeu <[email protected]>
parent: Antonin Houska <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: Mihail Nikalayeu @ 2026-02-16 11:16 UTC (permalink / raw)
To: Antonin Houska <[email protected]>; +Cc: Alvaro Herrera <[email protected]>; Pg Hackers <[email protected]>; Robert Treat <[email protected]>
Hello!
> What kind of regression? There is no pg_stat_get_progress_info('CLUSTER') call
> in system_views.sql.
I am about a direct call to that function (some monitoring tools may use it).
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2026-02-16 11:16 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-06-15 21:08 pg_waldump command line arguments Robert Haas <[email protected]>
2017-06-16 08:55 ` Ashutosh Bapat <[email protected]>
2017-06-16 17:58 ` Andres Freund <[email protected]>
2020-06-29 07:12 [PATCH] Let complete segment archived immediately on standy Kyotaro Horiguchi <[email protected]>
2022-12-15 23:33 Exclusion constraints on partitioned tables Paul Jungwirth <[email protected]>
2022-12-16 00:12 ` Re: Exclusion constraints on partitioned tables Tom Lane <[email protected]>
2022-12-16 05:11 ` Re: Exclusion constraints on partitioned tables Paul Jungwirth <[email protected]>
2026-02-15 16:03 Re: Adding REPACK [concurrently] Mihail Nikalayeu <[email protected]>
2026-02-16 07:47 ` Re: Adding REPACK [concurrently] Antonin Houska <[email protected]>
2026-02-16 11:16 ` Re: Adding REPACK [concurrently] Mihail Nikalayeu <[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