SEO

Dear community members,

I'm having a quite strange behaviour while trying to drop some index.

We have some tables with two indexes on a primary key. The first one was
automatically created by the primary constraint. The second one was
manually created on the same column. Don't know why, but I would now
want to suppress it.

The first index is : foo_pkey
The second one : i_foo_pk
The constraint on table bar is fk_bar_foo references foo(id)

But, when trying to drop the second index I get the following message :

NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk

The database server is 7.4.5 .

Having dumped database and restored it on a 8.2 server, I could drop the
second index without any problem.

The fact is I could do that as I indded what to migrate all databases
from 7.4 to 8.2. But I would prefer not to recreate every index before
dropping the non necessary one. And duplicate indexes are surely
unnecessary...

I have read in some thread that these troubles are known and have been
corrected in versions > 7.4.5. But, droping them before migrating is an
option I'd prefer to use.

So I wonder if ther is a way to indicate my foreign key it has to use
the right primarry key constraint and not an arbitrary index on that
primary key.

(Almost 10 databases and >300 tables to migrate with something like 130
indexes badly created). So I'd alse prefer not to drop every fk
constraint before dropping index and recreating constraint...

Thanks by advance

Stéphane Schildknecht

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 2007-01-12 at 17:50 +0100, Stéphane Schildknecht wrote:
> Dear community members,
>
> I'm having a quite strange behaviour while trying to drop some index.
>
> We have some tables with two indexes on a primary key. The first one was
> automatically created by the primary constraint. The second one was
> manually created on the same column. Don't know why, but I would now
> want to suppress it.

Drop the second index. It is redundant.


Joshua D. Drake


> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate




---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote:
> Dear community members,
>
> I'm having a quite strange behaviour while trying to drop some index.
>
> We have some tables with two indexes on a primary key. The first one was
> automatically created by the primary constraint. The second one was
> manually created on the same column. Don't know why, but I would now
> want to suppress it.
>
> The first index is : foo_pkey
> The second one : i_foo_pk
> The constraint on table bar is fk_bar_foo references foo(id)
>
> But, when trying to drop the second index I get the following message :
>
> NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk
>
> The database server is 7.4.5 .
>
> Having dumped database and restored it on a 8.2 server, I could drop the
> second index without any problem.
>
> The fact is I could do that as I indded what to migrate all databases
> from 7.4 to 8.2. But I would prefer not to recreate every index before
> dropping the non necessary one. And duplicate indexes are surely
> unnecessary...
>
> I have read in some thread that these troubles are known and have been
> corrected in versions > 7.4.5. But, droping them before migrating is an
> option I'd prefer to use.

Simple. Backup your db just in case, then upgrade in place to the
latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in
place.

patch upgrades do NOT require dump / reload, so 7.4.5 -> 7.4.15 should
only require a brief period where you shut down the db while you install
the new version.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Read the release notes. 7.4.8 and 7.4.11 require special considerations. By all means upgrade, but it's not quite seamless.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailtogsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Friday, January 12, 2007 12:59 PM
To: Stéphane Schildknecht
Cc: pgsql general
Subject: Re: [GENERAL] FK Constraint on index not PK

On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote:
> Dear community members,
>
> I'm having a quite strange behaviour while trying to drop some index.
>
> We have some tables with two indexes on a primary key. The first one was
> automatically created by the primary constraint. The second one was
> manually created on the same column. Don't know why, but I would now
> want to suppress it.
>
> The first index is : foo_pkey
> The second one : i_foo_pk
> The constraint on table bar is fk_bar_foo references foo(id)
>
> But, when trying to drop the second index I get the following message :
>
> NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk
>
> The database server is 7.4.5 .
>
> Having dumped database and restored it on a 8.2 server, I could drop the
> second index without any problem.
>
> The fact is I could do that as I indded what to migrate all databases
> from 7.4 to 8.2. But I would prefer not to recreate every index before
> dropping the non necessary one. And duplicate indexes are surely
> unnecessary...
>
> I have read in some thread that these troubles are known and have been
> corrected in versions > 7.4.5. But, droping them before migrating is an
> option I'd prefer to use.

Simple. Backup your db just in case, then upgrade in place to the
latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in
place.

patch upgrades do NOT require dump / reload, so 7.4.5 -> 7.4.15 should
only require a brief period where you shut down the db while you install
the new version.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Being quite familiar with both of those issues from the past, I can't
imagine either one causing a problem with an update prior to dumping so
he can then upgrade to 8.2.

Seriously. Hungarian collation, plerl can no longer change locale and
corrupt indexes, and a minor security update.

And none of them need to be applied to do the pg_dump and then import to
8.2

Now, if he were gonna keep the 7.4 machine up and running, then I'd
definitely recommend he look into the points made in the release notes
for those versions. But all the OP seemed to be in search of was
dropping those extra indexes before dumping / migrating to 8.2.

On Fri, 2007-01-12 at 14:39, Brandon Aiken wrote:
> Read the release notes. 7.4.8 and 7.4.11 require special considerations. By all means upgrade, but it's not quite seamless.
>
> --
> Brandon Aiken
> CS/IT Systems Engineer
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailtogsql-general-owner@postgresql.org] On Behalf Of Scott Marlowe
> Sent: Friday, January 12, 2007 12:59 PM
> To: Stéphane Schildknecht
> Cc: pgsql general
> Subject: Re: [GENERAL] FK Constraint on index not PK
>
> On Fri, 2007-01-12 at 10:50, Stéphane Schildknecht wrote:
> > Dear community members,
> >
> > I'm having a quite strange behaviour while trying to drop some index.
> >
> > We have some tables with two indexes on a primary key. The first one was
> > automatically created by the primary constraint. The second one was
> > manually created on the same column. Don't know why, but I would now
> > want to suppress it.
> >
> > The first index is : foo_pkey
> > The second one : i_foo_pk
> > The constraint on table bar is fk_bar_foo references foo(id)
> >
> > But, when trying to drop the second index I get the following message :
> >
> > NOTICE: constraint fk_bar_foo on table t_foo depends on index i_foo_pk
> >
> > The database server is 7.4.5 .
> >
> > Having dumped database and restored it on a 8.2 server, I could drop the
> > second index without any problem.
> >
> > The fact is I could do that as I indded what to migrate all databases
> > from 7.4 to 8.2. But I would prefer not to recreate every index before
> > dropping the non necessary one. And duplicate indexes are surely
> > unnecessary...
> >
> > I have read in some thread that these troubles are known and have been
> > corrected in versions > 7.4.5. But, droping them before migrating is an
> > option I'd prefer to use.
>
> Simple. Backup your db just in case, then upgrade in place to the
> latest 7.4 (7.4.15 or so) then you should be able to drop the indexes in
> place.
>
> patch upgrades do NOT require dump / reload, so 7.4.5 -> 7.4.15 should
> only require a brief period where you shut down the db while you install
> the new version.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Scott Marlowe a écrit :
> Being quite familiar with both of those issues from the past, I can't
> imagine either one causing a problem with an update prior to dumping so
> he can then upgrade to 8.2.
>
> Seriously. Hungarian collation, plerl can no longer change locale and
> corrupt indexes, and a minor security update.
>
> And none of them need to be applied to do the pg_dump and then import to
> 8.2
>
> Now, if he were gonna keep the 7.4 machine up and running, then I'd
> definitely recommend he look into the points made in the release notes
> for those versions. But all the OP seemed to be in search of was
> dropping those extra indexes before dumping / migrating to 8.2.
>
>

My goal is to migrate to 8.2.1. definitely. But as you said it, I do not
want to recreate unwanted index when migrating. I want to drop them BEFORE.

But, I can't just do a "drop index" command. It fails.

That's why I asked for an advice to drop them or not recreate them. I
would really prefer not to parse the all dump (some GB).

Thx

SAS

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

Joshua D. Drake a écrit :
> On Fri, 2007-01-12 at 17:50 +0100, Stéphane Schildknecht wrote:
>
>> Dear community members,
>>
>> I'm having a quite strange behaviour while trying to drop some index.
>>
>> We have some tables with two indexes on a primary key. The first one was
>> automatically created by the primary constraint. The second one was
>> manually created on the same column. Don't know why, but I would now
>> want to suppress it.
>>
>
> Drop the second index. It is redundant.
>

I know it. But I can't.

SAS

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Stéphane Schildknecht wrote:
>
> My goal is to migrate to 8.2.1. definitely. But as you said it, I do not
> want to recreate unwanted index when migrating. I want to drop them BEFORE.
>
> But, I can't just do a "drop index" command. It fails.
>
> That's why I asked for an advice to drop them or not recreate them. I
> would really prefer not to parse the all dump (some GB).

Try the --list and --use-list options on pg_restore. IIRC indexes are
mentioned as items in that, so you should be able to comment out the one
you don't want.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Stéphane Schildknecht wrote:
> Joshua D. Drake a écrit :
> > On Fri, 2007-01-12 at 17:50 +0100, Stéphane Schildknecht wrote:
> >
> >> Dear community members,
> >>
> >> I'm having a quite strange behaviour while trying to drop some index.
> >>
> >> We have some tables with two indexes on a primary key. The first one was
> >> automatically created by the primary constraint. The second one was
> >> manually created on the same column. Don't know why, but I would now
> >> want to suppress it.
> >>
> >
> > Drop the second index. It is redundant.
>
> I know it. But I can't.

The problem is that the constraint was defined with a dependence on the
second index. I guess what you could do is drop the constraint, drop
the second index, and then recreate the constraint. Try it within a
transaction block, just in case it doesn't work (but I don't see why it
wouldn't)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

=?UTF-8?B?U3TDqXBoYW5lIFNjaGlsZGtuZWNodA==?= <stephane.schildknecht@postgresqlfr.org> writes:
> My goal is to migrate to 8.2.1. definitely. But as you said it, I do not
> want to recreate unwanted index when migrating. I want to drop them BEFORE.
> But, I can't just do a "drop index" command. It fails.

Right, because the FK constraints by chance seized on those indexes as
being the matching ones for them to depend on.

What you want to do is (1) update the relevant pg_depend entries to
reference the desired PK indexes instead of the undesired ones; then
(2) drop the undesired indexes.

I don't have a script to do (1) but it should be relatively
straightforward: in the rows with objid = OID of FK constraint
and refobjid = OID of unwanted index, update refobjid to be the
OID of the wanted index. (To be truly correct, make sure that
classid and refclassid are the right values; but the odds of a
false match are probably pretty low.)

Needless to say, test and debug your process for this in a scratch
database ... and when you do it on the production DB, start with
BEGIN so you can roll back if you realize you blew it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend