pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNon blocking type change for primary column
3+ messages / 3 participants
[nested] [flat]
* Non blocking type change for primary column
@ 2021-01-14 16:26 Torge Kummerow <tk@panaccess.com>
2021-01-15 09:42 ` Re: Non blocking type change for primary column Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 3+ messages in thread
From: Torge Kummerow @ 2021-01-14 16:26 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hi,
I wonder if there is a feature planned (or already available in 11+)
that allows one to change the column type of a table without making it
block until finished.
As seemingly many others, we used serial instead of bigserial in our
tables and some will at a not too distant future hit their limit.
I am currently evaluating our options. One, that seems possible would be
1. adding a new nullable column of type bigint.
2. copying the primary key into this field in small batches
3. Once done. rename the primary column and new column, to swap them
4. attach the sequence to the new column and deattach it from the old column
5. Make new column NON NULL (Not sure if this will block)
6. Remove primary key from old column, make new column to be the primary
key (I fear this will block to create the index, but I guess not nearly
as long as ALTER COLUMN type)
7. Delete old column.
This could be prevented, if this would be done in a non blocking way by
the database system itself, so I hope this is already implemented or at
least on the roadmap.
Any thoughts?
Greetings,
Torge.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Non blocking type change for primary column
2021-01-14 16:26 Non blocking type change for primary column Torge Kummerow <tk@panaccess.com>
@ 2021-01-15 09:42 ` Guillaume Lelarge <guillaume@lelarge.info>
2021-01-16 05:18 ` Re: Non blocking type change for primary column Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>
0 siblings, 1 reply; 3+ messages in thread
From: Guillaume Lelarge @ 2021-01-15 09:42 UTC (permalink / raw)
To: Torge Kummerow <tk@panaccess.com>; +Cc: pgsql-sql@lists.postgresql.org
Hi,
Le jeu. 14 janv. 2021 à 17:27, Torge Kummerow <tk@panaccess.com> a écrit :
> Hi,
>
> I wonder if there is a feature planned (or already available in 11+)
> that allows one to change the column type of a table without making it
> block until finished.
>
> As seemingly many others, we used serial instead of bigserial in our
> tables and some will at a not too distant future hit their limit.
>
> I am currently evaluating our options. One, that seems possible would be
>
> 1. adding a new nullable column of type bigint.
> 2. copying the primary key into this field in small batches
> 3. Once done. rename the primary column and new column, to swap them
> 4. attach the sequence to the new column and deattach it from the old
> column
> 5. Make new column NON NULL (Not sure if this will block)
> 6. Remove primary key from old column, make new column to be the primary
> key (I fear this will block to create the index, but I guess not nearly
> as long as ALTER COLUMN type)
> 7. Delete old column.
>
>
> This could be prevented, if this would be done in a non blocking way by
> the database system itself, so I hope this is already implemented or at
> least on the roadmap.
>
> Any thoughts?
>
>
Nope, the table needs to be re-written to change the 4-byte column into an
8-byte column. And I don't expect to see this anytime soon.
--
Guillaume.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Non blocking type change for primary column
2021-01-14 16:26 Non blocking type change for primary column Torge Kummerow <tk@panaccess.com>
2021-01-15 09:42 ` Re: Non blocking type change for primary column Guillaume Lelarge <guillaume@lelarge.info>
@ 2021-01-16 05:18 ` Mohamed Wael Khobalatte <mkhobalatte@grubhub.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mohamed Wael Khobalatte @ 2021-01-16 05:18 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Torge Kummerow <tk@panaccess.com>; pgsql-sql@lists.postgresql.org
>
> 1. adding a new nullable column of type bigint.
> 2. copying the primary key into this field in small batches
> 3. Once done. rename the primary column and new column, to swap them
> 4. attach the sequence to the new column and deattach it from the old
> column
> 5. Make new column NON NULL (Not sure if this will block)
> 6. Remove primary key from old column, make new column to be the primary
> key (I fear this will block to create the index, but I guess not nearly
> as long as ALTER COLUMN type)
> 7. Delete old column.
>
> This could be prevented, if this would be done in a non blocking way by
> the database system itself, so I hope this is already implemented or at
> least on the roadmap.
> Any thoughts?
Hye Torge,
I was recently involved with such a change. See this thread[1] for
discussion. In particular, it touches on point 5, where you ask what's
blocking. Hope it helps.
[1]
https://www.postgresql.org/message-id/flat/CABZeWdybDVTgCQFZf0jDtj0p4yLxNrYd8aw61iFxY-Xt%2BfqPkw%40m...
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-01-16 05:18 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-14 16:26 Non blocking type change for primary column Torge Kummerow <tk@panaccess.com>
2021-01-15 09:42 ` Guillaume Lelarge <guillaume@lelarge.info>
2021-01-16 05:18 ` Mohamed Wael Khobalatte <mkhobalatte@grubhub.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