On Tue, Jul 21, 2020 at 11:30 AM Mohamed Wael Khobalatte <
mkhobala...@grubhub.com> wrote:
> Hi all,
>
> We are running 9.6, and we are planning to move some primary keys from int
> to bigint because we are approaching the type limit. We understand this
> requires some downtime, but we want to kno
On Wed, Jul 22, 2020 at 4:45 PM Ireneusz Pluta/wp.pl wrote:
> W dniu 2020-07-21 o 17:30, Mohamed Wael Khobalatte pisze:
> > we are planning to move some primary keys from int to bigint because we
> are approaching the type limit
> If that does not break your business logic, you might arrange to u
W dniu 2020-07-21 o 17:30, Mohamed Wael Khobalatte pisze:
we are planning to move some primary keys from int to bigint because we are
approaching the type limit
If that does not break your business logic, you might arrange to use the negative half of the ::int
value range. Ugly, but this might
> Believe it or not I pitched a similar idea internally. I'll explore it in
a test run.
By similar idea, I am referencing your suggestion of dropping the primary
key constraint.
>
>
> One presumes you may be planning to use pglogical or another similar
> solution to upgrade to a new Postgres version soon, and would have a
> convenient time then to change schema. I am curious, why not just stick
> with the single column unique index and forgo for the primary key
> constraint
On Wed, Jul 22, 2020 at 12:23 PM Mohamed Wael Khobalatte <
mkhobala...@grubhub.com> wrote:
> No worries. I suppose the answer to the original question, which is how to
> avoid a table scan when adding a primary key constraint to a newly
> backfilled column is "there is no way"? Downtime might be a
On Wed, Jul 22, 2020 at 11:13 AM Daniel Verite
wrote:
> Mohamed Wael Khobalatte wrote:
>
> > We lock the table as a precaution, with the understanding that we are
> > undergoing a "small" downtime to finish replacing the int id by the new
> > bigint
>
> Ah, sorry I overlooked that most ro
Mohamed Wael Khobalatte wrote:
> We lock the table as a precaution, with the understanding that we are
> undergoing a "small" downtime to finish replacing the int id by the new
> bigint
Ah, sorry I overlooked that most row updates are done
pre-transaction in a preliminary step:
/* in ba
On Wed, Jul 22, 2020 at 9:27 AM Daniel Verite
wrote:
> Mohamed Wael Khobalatte wrote:
>
> > > alter table change_seq alter COLUMN id set data
> > > type bigint;
>
> > This is significant downtime, since it locks exclusively, no? We want to
> > avoid that.
>
> Well, in the steps you mentio
Mohamed Wael Khobalatte wrote:
> > alter table change_seq alter COLUMN id set data
> > type bigint;
> This is significant downtime, since it locks exclusively, no? We want to
> avoid that.
Well, in the steps you mentioned upthread, the transaction starts by
doing LOCK TABLE some_table, s
> Yeah, I thought the int --> bigint would not do a table rewrite. Testing
> showed otherwise. Forget that idea.
Got it. Not sure what else we should consider. It seemed like the
constraint might be possible, but currently need a far bigger table to be
able to tell for sure, since we can't explain
On 7/21/20 2:18 PM, Mohamed Wael Khobalatte wrote:
> test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Column | Type | Collation | Nullable |
> > test_(aklaver)5432> alter table change_seq alter COLUMN id set data
type
> bigint;
> ALTER TABLE
> test_(aklaver)5432> \d change_seq
> Table "public.change_seq"
> Column | Type | Collation | Nullable |Default
> ++---+
Curious- what requires that the unique index be declared a primary key?
What advantage does that give you? Just ensuring it isn't null?
Side note- EOL for 9.6 is coming next year so just a plug for upgrading
when possible, perhaps utilizing pglogical to get to v11 or 12.
>
On 7/21/20 11:17 AM, Adrian Klaver wrote:
On 7/21/20 8:30 AM, Mohamed Wael Khobalatte wrote:
Hi all,
> test_(aklaver)5432> alter table change_seq alter COLUMN id set data type
bigint;
ALTER TABLE
test_(aklaver)5432> \d change_seq
Table "public.change_seq"
Colum
On 7/21/20 8:30 AM, Mohamed Wael Khobalatte wrote:
Hi all,
We are running 9.6, and we are planning to move some primary keys from
int to bigint because we are approaching the type limit. We understand
this requires some downtime, but we want to know if there are things we
can do to limit it.
Hi all,
We are running 9.6, and we are planning to move some primary keys from int
to bigint because we are approaching the type limit. We understand this
requires some downtime, but we want to know if there are things we can do
to limit it.
Here are our steps, with questions at the end.
ALTER T
17 matches
Mail list logo
