Thread: FK Constraint on index not PK
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
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
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.
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 [mailto:pgsql-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
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 [mailto:pgsql-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/
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
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
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
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
=?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
Alvaro Herrera <alvherre@commandprompt.com> writes:
> 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)
That's certainly the safest way, but if this is a live production
database then the time spent holding exclusive locks while
re-establishing the FK constraint may be a problem. If that's not
tolerable then the solution is manually mucking with the system
catalogs as I suggested in an earlier reply.
regards, tom lane
Tom Lane a écrit : > =?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 > Hi Tom, Thank You very much for this answer. I'll try that tomorrow morning. regards, SAS