Re: FW: [HACKERS] Allow replacement of bloated primary key indexes without foreign key rebuilds
> From: Gurjeet Singh [mailto:singh.gurj...@gmail.com]
> Sent: Friday, July 13, 2012 4:24 AM
>>On Tue, Jul 10, 2012 at 2:40 AM, Amit Kapila
wrote:
>>> Having to drop foreign key constraints before this command, and recreate
them afterwards makes this command useless to most database setups. I feel
sorry
>>> that no one brought this up when we were implementing the feature;
maybe we could've done something about it right then.
>>Will it impact user such that it will block its operation or something
similar or it is a usability issue?
> Yes, it will have to take an exclusive lock on the index, and possibly the
table too, but the operation should be quick to be even noticeable in low
load
> conditions.
Which index you are referring here, is it primary key table index?
According to what I have debugged, the locks are taken on foreign key table,
constraint object and dependent triggers.
> However, if the x-lock is waiting for some other long running query to
finish, then lock queuing logic in Postgres will make new queries to wait
for this x-lock to > be taken and released before any new query can begin
processing. This is my recollection of the logic from an old conversation,
others can weigh in to confirm.
>>> Syntax options:
>>> ALTER TABLE tbl REPLACE [CONSTRAINT constr] {PRIMARY KEY | UNIQUE} USING
INDEX new_index;
>>> ALTER INDEX ind REPLACE WITH new_index;
>>After this new syntax there will be 2 ways for users to do the replacement
of index, won't it confuse users for which syntax to use?
> Yes, I forgot to mention this in the original post. This feature will be a
superset of the feature we introduced in ALTER TABLE. I don't see a way
around that,
> except for slowly deprecating the older feature.
After new implementation, there will be no need to perform any operation for
table with foreign key and hence reduce the lock time for same as well.
However after implementation of Reindex Concurrently, this feature will also
needs to be deprecated which might not happen soon but still I feel it
should be considered whether providing new syntax and implementation is
really required by users.
With Regards,
Amit Kapila.
Re: FW: [HACKERS] Allow replacement of bloated primary key indexes without foreign key rebuilds
On Tue, Jul 10, 2012 at 2:40 AM, Amit Kapila wrote:
> > Today I learnt [1,2,3] that the feature ALTER TABLE .. ADD CONSTRAINT
> ... USING INDEX we added back in the day is not so useful in the field. **
> **
>
> > Having to drop foreign key constraints before this command, and
> recreate them afterwards makes this command useless to most database
> setups. I feel sorry
>
> > that no one brought this up when we were implementing the feature;
> maybe we could've done something about it right then.
>
> ** **
>
> Will it impact user such that it will block its operation or something
> similar or it is a usability issue?
>
Yes, it will have to take an exclusive lock on the index, and possibly the
table too, but the operation should be quick to be even noticeable in low
load conditions.
However, if the x-lock is waiting for some other long running query to
finish, then lock queuing logic in Postgres will make new queries to wait
for this x-lock to be taken and released before any new query can begin
processing. This is my recollection of the logic from an old conversation,
others can weigh in to confirm.
>
>
>
>
> > All we need to do is allow swapping of pg_class.relfilenode of two
> indexes. This will let the dependency entries stand as they are and allow
> us to drop the
>
> > bloated primary key index structure without having to rebuild the
> foreign key constraints.
>
> ** **
>
> I have noticed is that currently Oid and pg_class.relfilenode are same for
> user created tables and indexes. But after your implementation that will
> not remain same, I am not sure whether it can impact any other path of
> code.
>
They start off as same, but some operations, like REINDEX, changes the
relfilenode; that's the purpose of relfilenode: to map the oid to a
filename on disk.
>
>
>
>
> >As for the syntactical sugar, this can be added to either ALTER TABLE or
> to ALTER INDEX. Although under no normal circumstances one would need to
> use >ALTER INDEX to swap two indexes' relfilenode (because one can easily
> create a duplicate index and drop/rename-in-place the old one), I think it
> would make > more sense here since it is just an operation on two indexes
> and has nothing to do with the constraints, apart from the fact that we
> want to use this feature to
>
> > meddle with the constraints.
>
> > Syntax options:
>
> > ALTER TABLE tbl REPLACE [CONSTRAINT constr] {PRIMARY KEY | UNIQUE}
> USING INDEX new_index;
>
> > ALTER INDEX ind REPLACE WITH new_index;
>
> After this new syntax there will be 2 ways for users to do the replacement
> of index, won’t it confuse users for which syntax to use?
>
Yes, I forgot to mention this in the original post. This feature will be a
superset of the feature we introduced in ALTER TABLE. I don't see a way
around that, except for slowly deprecating the older feature.
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
FW: [HACKERS] Allow replacement of bloated primary key indexes without foreign key rebuilds
Sorry by mistake for below mail, I have not kept hackers in loop.
From: Amit Kapila [mailto:amit.kap...@huawei.com]
Sent: Tuesday, July 10, 2012 12:07 PM
To: 'Gurjeet Singh'
Subject: RE: [HACKERS] Allow replacement of bloated primary key indexes
without foreign key rebuilds
From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Gurjeet Singh
Sent: Saturday, July 07, 2012 9:23 AM
> Today I learnt [1,2,3] that the feature ALTER TABLE .. ADD CONSTRAINT ...
USING INDEX we added back in the day is not so useful in the field.
> Having to drop foreign key constraints before this command, and recreate
them afterwards makes this command useless to most database setups. I feel
sorry
> that no one brought this up when we were implementing the feature; maybe
we could've done something about it right then.
Will it impact user such that it will block its operation or something
similar or it is a usability issue?
> All we need to do is allow swapping of pg_class.relfilenode of two
indexes. This will let the dependency entries stand as they are and allow us
to drop the
> bloated primary key index structure without having to rebuild the foreign
key constraints.
I have noticed is that currently Oid and pg_class.relfilenode are same for
user created tables and indexes. But after your implementation that will not
remain same, I am not sure whether it can impact any other path of code.
>As for the syntactical sugar, this can be added to either ALTER TABLE or to
ALTER INDEX. Although under no normal circumstances one would need to use
>ALTER INDEX to swap two indexes' relfilenode (because one can easily create
a duplicate index and drop/rename-in-place the old one), I think it would
make > more sense here since it is just an operation on two indexes and has
nothing to do with the constraints, apart from the fact that we want to use
this feature to
> meddle with the constraints.
> Syntax options:
> ALTER TABLE tbl REPLACE [CONSTRAINT constr] {PRIMARY KEY | UNIQUE} USING
INDEX new_index;
> ALTER INDEX ind REPLACE WITH new_index;
After this new syntax there will be 2 ways for users to do the replacement
of index, won't it confuse users for which syntax to use?
