pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
5+ messages / 4 participants
[nested] [flat]
* BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
@ 2019-03-21 08:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2019-03-21 08:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: thomas@palan.at
The following bug has been logged on the website:
Bug reference: 15710
Logged by: Tom Palan
Email address: thomas@palan.at
PostgreSQL version: 9.6.10
Operating system: Debian stretch 64bit
Description:
If I want to add a column to a table which and the column may or may not
already exist, the specified column constraint like UNIQUE is added every
time the command is issued, regardless if the column gets added or not.
Example:
CREATE TABLE test (id SERIAL PRIMARY KEY);
ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;
Result:
\d test
Table "public.test"
Column | Type | Modifiers
------------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
new_column | integer |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
"test_new_column_key" UNIQUE CONSTRAINT, btree (new_column)
After issuing the command:
ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;
again, the table structure is like:
\d test
Table "public.test"
Column | Type | Modifiers
------------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
new_column | integer |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
"test_new_column_key" UNIQUE CONSTRAINT, btree (new_column)
"test_new_column_key1" UNIQUE CONSTRAINT, btree (new_column)
Result: a new UNIQUE constraint was added
Expected result: as the column new_column already exists, creating the
column and the specified constraint should not be done
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
@ 2019-03-21 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2019-03-21 14:18 UTC (permalink / raw)
To: thomas@palan.at; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> If I want to add a column to a table which and the column may or may not
> already exist, the specified column constraint like UNIQUE is added every
> time the command is issued, regardless if the column gets added or not.
Yeah, this is the same thing previously reported at
https://www.postgresql.org/message-id/flat/152509815280.19803.16118194452213577808%40wrigleys.postgr...
We haven't got further than sketching a design for a fix :-(
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways
@ 2019-03-22 08:36 Christoph Berg <myon@debian.org>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 5+ messages in thread
From: Christoph Berg @ 2019-03-22 08:36 UTC (permalink / raw)
To: thomas@palan.at; pgsql-bugs@lists.postgresql.org
Re: PG Bug reporting form 2019-03-21 <15710-f52bad04543be4cb@postgresql.org>
> CREATE TABLE test (id SERIAL PRIMARY KEY);
> ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer UNIQUE;
As a workaround, you can name the constraint:
ALTER TABLE test ADD COLUMN IF NOT EXISTS new_column integer CONSTRAINT id_unique UNIQUE;
Then it won't get added multiple times. (It will still be executed
once, though.)
Christoph
^ permalink raw reply [nested|flat] 5+ messages in thread
* [PATCH] Fix incorrect calculation regarding max_slot_wal_keep_size
@ 2023-04-20 02:51 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kyotaro Horiguchi @ 2023-04-20 02:51 UTC (permalink / raw)
The calculation in KeepLogSeg related to max_slot_wal_keep_size can
result in unexpected behavior when replication advances quickly, and
the checkpointer is delayed just before removing WAL files.
---
src/backend/access/transam/xlog.c | 7 +++++--
1 file changed, 5 insertions(+), 2 deletions(-)
inline patch 1: src/backend/access/transam/xlog.c (+5/-2 lines) download inline-1.patch
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 63481d826f..7bdd4d86b1 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -7463,8 +7463,11 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo)
{
XLByteToSeg(keep, segno, wal_segment_size);
- /* Cap by max_slot_wal_keep_size ... */
- if (max_slot_wal_keep_size_mb >= 0)
+ /*
+ * Cap by max_slot_wal_keep_size. However this is not required if the
+ * slots alredy won't protect any segments.
+ */
+ if (max_slot_wal_keep_size_mb >= 0 && segno < currSegNo)
{
uint64 slot_keep_segs;
--
2.31.1
* [PATCH v2] Fix incorrect calculation regarding max_slot_wal_keep_size
@ 2023-04-20 02:51 Kyotaro Horiguchi <horikyota.ntt@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kyotaro Horiguchi @ 2023-04-20 02:51 UTC (permalink / raw)
The calculation in KeepLogSeg related to max_slot_wal_keep_size can
result in unexpected behavior when replication advances quickly, and
the checkpointer is delayed just before removing WAL files.
---
src/backend/access/transam/xlog.c | 8 ++++++++
1 file changed, 8 insertions(+)
inline patch 1: src/backend/access/transam/xlog.c (+8/-0 lines) download inline-1.patch
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 63481d826f..bda859c299 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -7463,6 +7463,14 @@ KeepLogSeg(XLogRecPtr recptr, XLogSegNo *logSegNo)
{
XLByteToSeg(keep, segno, wal_segment_size);
+ /*
+ * Slot minimum LSN could be greater than recptr. In such cases, no
+ * segments are protected by slots but we still need to keep segno in a
+ * reasonable range for subsequent calculations to avoid underflow.
+ */
+ if (segno > currSegNo)
+ segno = currSegNo;
+
/* Cap by max_slot_wal_keep_size ... */
if (max_slot_wal_keep_size_mb >= 0)
{
--
2.31.1
end of thread, other threads:[~2023-04-20 02:51 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-21 08:41 BUG #15710: ADD COLUMN IF NOT EXISTS adds constraint anyways PG Bug reporting form <noreply@postgresql.org>
2019-03-21 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-03-22 08:36 ` Christoph Berg <myon@debian.org>
2023-04-20 02:51 [PATCH] Fix incorrect calculation regarding max_slot_wal_keep_size Kyotaro Horiguchi <horikyota.ntt@gmail.com>
2023-04-20 02:51 [PATCH v2] Fix incorrect calculation regarding max_slot_wal_keep_size Kyotaro Horiguchi <horikyota.ntt@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox