BUG #15871: Regression in 11.4 altering type on column with an index

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15871: Regression in 11.4 altering type on column with an index

apt.postgresql.org Repository Update
The following bug has been logged on the website:

Bug reference:      15871
Logged by:          Tom Dunstan
Email address:      [hidden email]
PostgreSQL version: 11.4
Operating system:   Linux x86_64 (Alpine 3.9)
Description:        

This worked up to 11.3:

test=# CREATE TABLE foo (ts1 TIMESTAMP NOT NULL, ts2 TIMESTAMP NOT NULL);
CREATE TABLE
test=# CREATE INDEX foo_ts1_idx ON foo (ts1);
CREATE INDEX
test=# CREATE INDEX foo_ts2_idx ON foo (ts2);
CREATE INDEX
test=# ALTER TABLE FOO ALTER ts1 TYPE TIMESTAMP WITH TIME ZONE, ALTER ts2
TYPE TIMESTAMP WITH TIME ZONE;
ERROR:  42P07: relation "foo_ts1_idx" already exists
LOCATION:  index_create, index.c:877

Necessary condition seems to be:
 - Multiple columns altered in the same statement
 - Both altered columns have an index

Altering in separate statements, or not having one of the indexes doesn't
trigger it.

May be related to this item mentioned in the 11.4 release notes: "Fix
failure of ALTER TABLE ... ALTER COLUMN TYPE when the table has a partial
exclusion constraint (Tom Lane)"

Reply | Threaded
Open this post in threaded view
|

BUG #15871: Regression in 11.4 altering type on column with an index

David G Johnston
On Monday, June 24, 2019, PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15871
Logged by:          Tom Dunstan
Email address:      [hidden email]
PostgreSQL version: 11.4
Operating system:   Linux x86_64 (Alpine 3.9)
Description:       

test=# ALTER TABLE FOO ALTER ts1 TYPE TIMESTAMP WITH TIME ZONE, ALTER ts2
TYPE TIMESTAMP WITH TIME ZONE;
ERROR:  42P07: relation "foo_ts1_idx" already exists
LOCATION:  index_create, index.c:877

Necessary condition seems to be:
 - Multiple columns altered in the same statement
 - Both altered columns have an index

Seems to be the same behavior as BUG #1586 whose fix just got applied today and will appear in the next point release.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15871: Regression in 11.4 altering type on column with an index

Tom Dunstan-2
On Tue, 25 Jun 2019 at 14:33, David G. Johnston <[hidden email]> wrote:

Seems to be the same behavior as BUG #1586 whose fix just got applied today and will appear in the next point release.

Ah, I missed that one when eyeballing the archives, I guess I didn't go back far enough. Yes, absolutely looks the same, thanks.

Cheers

Tom