Re: Best practice when reindexing in production

On Wed, May 29, 2013 at 10:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>wrote:

>
>
> You could do something like this (which considers you use simple names for
> your indexes, where simple ~ [a-z_][a-z0-9_]*):
>
> SELECT
> regexp_replace(i.indexdef, '^CREATE( UNIQUE)? INDEX (.*) ON (.*)',
> 'CREATE\1 INDEX CONCURRENTLY tmp_\2 ON \3;') || E'\n'
> || E'BEGIN;\n'
> || 'DROP INDEX ' || i.indexname || E';\n'
> || 'ALTER INDEX tmp_' || i.indexname || ' RENAME TO ' || i.indexname ||
> E';\n'
> || E'COMMIT;\n'
> FROM pg_indexes i
> WHERE schemaname !~ '^(pg_|information_schema$)';
>
> Although this one is *really simple* and *error phrone*, because it does
> not consider at least two things: index that are constraints and index that
> has FK depending on it. For the first case, you only need to change the
> constraint to use the index and the DROP command. As for the second case,
> you would need to remove the FKs, drop the old one and recreate the FK
> (inside a transaction, of course), but this could be really slow, a reindex
> for this case would be simpler and perhaps faster.
>
> =================
>
> I must be missing something here.
> But, how is that FK depends on the index?
> I understand FK lookup works much faster with the index supporting FK than
> without it, but you could have FK without index (on the "child" table).
> So, what gives?
>
>
AFAIK, when you create a FK, PostgreSQL associate it with an UNIQUE INDEX
on the target table. It creates an entry on pg_depends (I don't know if
somewhere else), and when you try to drop the index, even if there is an
identical one that PGs could use, it will throw an error.

You can easily check this:

postgres=# CREATE TABLE parent(id int);
CREATE TABLE
postgres=# CREATE UNIQUE INDEX parent_idx1 ON parent (id);
CREATE INDEX
postgres=# CREATE TABLE child(idparent int REFERENCES parent (id));
CREATE TABLE
postgres=# CREATE UNIQUE INDEX parent_idx2 ON parent (id);
CREATE INDEX
postgres=# DROP INDEX parent_idx1;
ERROR: cannot drop index parent_idx1 because other objects depend on it
DETAIL: constraint child_idparent_fkey on table child depends on index
parent_idx1
HINT: Use DROP ... CASCADE to drop the dependent objects too.

BTW, I do think PostgreSQL could verify if there is another candidate to
this FK. Is it in TODO list? Should it be?

Regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres