Обсуждение: Altering multiple column types

Поиск
Список
Период
Сортировка

Altering multiple column types

От
Bharanee Rathna
Дата:
Hi,

I'm encountering an issue altering multiple column types in a single ALTER TABLE

psql (12beta2, server 11.4)
Type "help" for help.

test=# create table users(id serial primary key, name varchar(255), age int, email varchar(255));
CREATE TABLE
test=# create index users_name_idx on users(name);
CREATE INDEX
test=# create index users_email_idx on users(email);
CREATE INDEX
test=# alter table users alter column name type text, alter column email type text;
psql: ERROR:  relation "users_name_idx" already exists
test=# alter table users alter column name type text;
ALTER TABLE
test=# alter table users alter column email type text;
ALTER TABLE

Is there a limitation I'm missing in the docs ?

https://www.postgresql.org/docs/11/sql-altertable.html

Thanks

Re: Altering multiple column types

От
Luca Ferrari
Дата:
On Fri, Aug 2, 2019 at 7:42 AM Bharanee Rathna <deepfryed@gmail.com> wrote:
>
> Hi,
>
> I'm encountering an issue altering multiple column types in a single ALTER TABLE
>
> psql (12beta2, server 11.4)

it is working on my 11.4 with psql 11.4. Could it be a problem of psql
version 12? Can you try again with a psql "stable"?

testdb=> create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=> alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=> drop table users;
DROP TABLE
testdb=> create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=> create index users_email_idx on users(email);
CREATE INDEX
testdb=> alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=> \d users
                            Table "public.users"
 Column |  Type   | Collation | Nullable |              Default
--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('users_id_seq'::regclass)
 name   | text    |           |          |
 age    | integer |           |          |
 email  | text    |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_idx" btree (email)

testdb=> select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
8.3.0-6ubuntu1~18.10.1) 8.3.0, 64-bit
(1 row)



Re: Altering multiple column types

От
Luca Ferrari
Дата:
On Fri, Aug 2, 2019 at 9:39 AM Bharanee Rathna <deepfryed@gmail.com> wrote:
>
> Hi Luca,
>
> I've tried it with a different client and Postgres 10.9, no luck
>
> psql (10.3, server 10.9)

I've fired up a 12beta2 and it works, either with psql 12 or psql 11.4 on linux.
What if you run the statements within another client (pgadmin, a java
client or something else)?

% psql -U postgres testdb
psql (12beta2)
Type "help" for help.

testdb=# create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=# create index users_email_idx on users(email);
CREATE INDEX
testdb=# alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=# SELECT version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 12beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
8.3.0-6ubuntu1~18.10.1) 8.3.0, 64-bit
(1 row)




% ~/git/misc/PostgreSQL/pgenv/pgsql-11.4/bin/psql -U postgres testdb

psql (11.4, server 12beta2)
WARNING: psql major version 11, server major version 12.
         Some psql features might not work.
Type "help" for help.

testdb=# drop table users;
DROP TABLE
testdb=# create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=# create index users_email_idx on users(email);
CREATE INDEX
testdb=# alter table users alter column name type text, alter column
email type text;
ALTER TABLE



Re: Altering multiple column types

От
Bharanee Rathna
Дата:
Hi Luca,

testing this using docker images. I can replicate it with 10.9-alpine

bash-5.0# psql -h127.0.0.1 -Upostgres test
psql (10.9)
Type "help" for help.

test=# \d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default              
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(255) |           |          |
 age    | integer                |           |          |
 email  | character varying(255) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_idx" btree (email)
    "users_name_idx" btree (name)

test=# alter table users alter column name type text, alter column email type text;
ERROR:  relation "users_name_idx" already exists

test=# select version();
                                        version                                        
---------------------------------------------------------------------------------------
 PostgreSQL 10.9 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
(1 row)


and 11.4

psql (11.4)
Type "help" for help.

test=# \d users
                                    Table "public.users"
 Column |          Type          | Collation | Nullable |              Default              
--------+------------------------+-----------+----------+-----------------------------------
 id     | integer                |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(255) |           |          |
 age    | integer                |           |          |
 email  | character varying(255) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_idx" btree (email)
    "users_name_idx" btree (name)

test=# alter table users alter column name type text, alter column email type text;
ERROR:  relation "users_name_idx" already exists

test=# select version();
                                        version                                        
---------------------------------------------------------------------------------------
 PostgreSQL 11.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0) 8.3.0, 64-bit
(1 row)

Not sure what's going on at my end ...

On Fri, 2 Aug 2019 at 17:44, Luca Ferrari <fluca1978@gmail.com> wrote:
On Fri, Aug 2, 2019 at 9:39 AM Bharanee Rathna <deepfryed@gmail.com> wrote:
>
> Hi Luca,
>
> I've tried it with a different client and Postgres 10.9, no luck
>
> psql (10.3, server 10.9)

I've fired up a 12beta2 and it works, either with psql 12 or psql 11.4 on linux.
What if you run the statements within another client (pgadmin, a java
client or something else)?

% psql -U postgres testdb
psql (12beta2)
Type "help" for help.

testdb=# create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=# create index users_email_idx on users(email);
CREATE INDEX
testdb=# alter table users alter column name type text, alter column
email type text;
ALTER TABLE
testdb=# SELECT version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 12beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
8.3.0-6ubuntu1~18.10.1) 8.3.0, 64-bit
(1 row)




% ~/git/misc/PostgreSQL/pgenv/pgsql-11.4/bin/psql -U postgres testdb

psql (11.4, server 12beta2)
WARNING: psql major version 11, server major version 12.
         Some psql features might not work.
Type "help" for help.

testdb=# drop table users;
DROP TABLE
testdb=# create table users(id serial primary key, name varchar(255),
age int, email varchar(255));
CREATE TABLE
testdb=# create index users_email_idx on users(email);
CREATE INDEX
testdb=# alter table users alter column name type text, alter column
email type text;
ALTER TABLE

Re: Altering multiple column types

От
Luca Ferrari
Дата:
On Fri, Aug 2, 2019 at 10:06 AM Bharanee Rathna <deepfryed@gmail.com> wrote:
>
> Hi Luca,
>
> testing this using docker images. I can replicate it with 10.9-alpine
>
> bash-5.0# psql -h127.0.0.1 -Upostgres test
> psql (10.9)
> Type "help" for help.
>
> test=# \d users
>                                     Table "public.users"
>  Column |          Type          | Collation | Nullable |              Default
> --------+------------------------+-----------+----------+-----------------------------------
>  id     | integer                |           | not null | nextval('users_id_seq'::regclass)
>  name   | character varying(255) |           |          |
>  age    | integer                |           |          |
>  email  | character varying(255) |           |          |
> Indexes:
>     "users_pkey" PRIMARY KEY, btree (id)
>     "users_email_idx" btree (email)
>     "users_name_idx" btree (name)

My fault!
I missed one index, so it is working with one index a two column alter
table, but not with two indexes:

testdb=# alter table users alter column name type text, alter column
email type text;
alter table users alter column name type text, alter column email type text;
psql: ERROR:  relation "users_name_idx" already exists


Therefore I think it is a strange behavior, I cannot explain.
I confirm the problem shows up in 11.4 and 12beta2, so I guess there's
must be a reason I don't understand.

Luca



Re: Altering multiple column types

От
Bharanee Rathna
Дата:
Thanks for confirming, so I know I'm not going crazy :)

FWIW, it works on 10.3 to 10.8 (I think) and looks like a regression.

psql (10.3 (Debian 10.3-2))
Type "help" for help.

test=# create table users(id serial primary key, name varchar(255), email varchar(255), age int);
CREATE TABLE
Time: 26.650 ms
test=# create index users_name_idx on users(name); create index users_email_idx on users(email);
CREATE INDEX
Time: 15.660 ms
CREATE INDEX
Time: 12.065 ms
test=# alter table users alter column name type text, alter column email type text;
ALTER TABLE
Time: 7.213 ms
test=# select version();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 (Debian 10.3-2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.3.0-11) 7.3.0, 64-bit
(1 row)

Time: 2.683 ms



Re: Altering multiple column types

От
Tom Lane
Дата:
Bharanee Rathna <deepfryed@gmail.com> writes:
> I'm encountering an issue altering multiple column types in a single ALTER
> TABLE

Yeah, this is a regression in the May batch of minor releases :-(.
It's fixed for next week's releases.

https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=f946a4091

            regards, tom lane



Re: Altering multiple column types

От
Bharanee Rathna
Дата:
Thanks Tom!