Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
On Tue, Jul 10, 2012 at 10:44 AM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@mit.edu writes: On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: All we need to do is allow swapping of pg_class.relfilenode of two indexes. Fwiw I don't like swapping relfilenodes on indexes the user created. REINDEX currently does this but it's a bit of a hack and only works because reindex carefully builds the new index with exactly the same definition as the old one. Yes. The swap-relfilenodes operation would have to carefully check that the index definitions were exactly equivalent, and there would be a constant risk for bugs of omission if that code weren't taught about any new index properties we invent. IMHO there must be many other places in this code-base where we run that risk. The way I am planning to do it was to compare all relevant fields of the FormData_pg_index. And I am assuming anybody changing the struct members will take care of relevant changes needed for this code too. We can add a runtime/compile-time assert to make sure that Natts_pg_index==17. That way, if a new column gets added, we will get alerted promptly. All of these things seem like ugly, hard-to-use kluges anyway (the make-sure-the-indexes-match business is just as much of a PITA for the DBA as it is for the system). What we really want is REINDEX CONCURRENTLY. +1, but I can't take on that task. -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
On Tue, Jul 10, 2012 at 11:11 AM, Greg Stark st...@mit.edu wrote: On Tue, Jul 10, 2012 at 3:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: The problem you describe is one of constraints and dependencies and not one of indexes. It seems what you really want is a way to alter foreign key dependencies to depend on a new index. Either an explicit command that lets you set the new dependency or what seems even better would be to have DROP INDEX check any dependent objects to see if there's another index that can satisfy them and change their dependency. Either of these have exactly the same issue, namely their correctness depends on determining if two indexes have identical properties. This doesn't sound right to me. In these cases all it would have to know about is the same set of properties that CREATE CONSTRAINT looks for to find a satisfactory index to depend on. I like the DROP index idea, but the silent side-effect may not make people happy. Can you give me a pointer to relevant code. -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
[HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: 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. Fwiw I don't like swapping relfilenodes on indexes the user created. REINDEX currently does this but it's a bit of a hack and only works because reindex carefully builds the new index with exactly the same definition as the old one. The problem you describe is one of constraints and dependencies and not one of indexes. It seems what you really want is a way to alter foreign key dependencies to depend on a new index. Either an explicit command that lets you set the new dependency or what seems even better would be to have DROP INDEX check any dependent objects to see if there's another index that can satisfy them and change their dependency. These might suffer from deadlock problems but hopefully they could be manageable since it's not a frequent operation and there aren't any other operations that rejigger dependencies. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
Greg Stark st...@mit.edu writes: On Sat, Jul 7, 2012 at 4:53 AM, Gurjeet Singh singh.gurj...@gmail.com wrote: All we need to do is allow swapping of pg_class.relfilenode of two indexes. Fwiw I don't like swapping relfilenodes on indexes the user created. REINDEX currently does this but it's a bit of a hack and only works because reindex carefully builds the new index with exactly the same definition as the old one. Yes. The swap-relfilenodes operation would have to carefully check that the index definitions were exactly equivalent, and there would be a constant risk for bugs of omission if that code weren't taught about any new index properties we invent. The problem you describe is one of constraints and dependencies and not one of indexes. It seems what you really want is a way to alter foreign key dependencies to depend on a new index. Either an explicit command that lets you set the new dependency or what seems even better would be to have DROP INDEX check any dependent objects to see if there's another index that can satisfy them and change their dependency. Either of these have exactly the same issue, namely their correctness depends on determining if two indexes have identical properties. All of these things seem like ugly, hard-to-use kluges anyway (the make-sure-the-indexes-match business is just as much of a PITA for the DBA as it is for the system). What we really want is REINDEX CONCURRENTLY. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
On Tue, Jul 10, 2012 at 3:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: The problem you describe is one of constraints and dependencies and not one of indexes. It seems what you really want is a way to alter foreign key dependencies to depend on a new index. Either an explicit command that lets you set the new dependency or what seems even better would be to have DROP INDEX check any dependent objects to see if there's another index that can satisfy them and change their dependency. Either of these have exactly the same issue, namely their correctness depends on determining if two indexes have identical properties. This doesn't sound right to me. In these cases all it would have to know about is the same set of properties that CREATE CONSTRAINT looks for to find a satisfactory index to depend on. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
Excerpts from Tom Lane's message of mar jul 10 10:44:03 -0400 2012: All of these things seem like ugly, hard-to-use kluges anyway (the make-sure-the-indexes-match business is just as much of a PITA for the DBA as it is for the system). What we really want is REINDEX CONCURRENTLY. http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.47.9961 ? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Allow replacement of bloated primary key indexes without foreign key rebuilds
Alvaro Herrera alvhe...@commandprompt.com writes: Excerpts from Tom Lane's message of mar jul 10 10:44:03 -0400 2012: What we really want is REINDEX CONCURRENTLY. http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.47.9961 ? Hm ... that paper looks like something we might want to incorporate into btree's VACUUM processing, but it's not very on-point if someone really wants to rebuild the index totally. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
