Re: FK Constraint on index not PK

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/