Re: drop/add fk in transaction

On 14 Aug 2003, Nabil Sayegh wrote:

> Am Don, 2003-08-14 um 02.36 schrieb Stephan Szabo:
> > On 14 Aug 2003, Nabil Sayegh wrote:
> >
> > > I want to convert a database from 7.2 RI to 7.3 RI
> > > I need to drop 165 RI_ConstraintTrigger and
> > > add 55 7.3-style foreign keys.
> > >
> > > Is it possible to do all that in a transaction ?
> >
> > It should be fine I think barring that you probably
> > don't want other transactions going on that might
> > touch those tables concurrently because you're going
> > to be grabbing exclusive locks on a bunch of tables
> > and you want to make sure you don't deadlock with
> > some other transaction.
>
> The action should take only 2 seconds or so.

Do you have data in the tables? Because if you're planning
to use ALTER TABLE ADD CONSTRAINT to add the new version
constraints it's going to check that the constraint is
satisified (and that can be not so fast).

> I think it would be ok for that case to lock everything :) while this
> transaction is running.
>
> Do you have a commandline by hand ?

I'd look at contrib/adddepend which I think does some portion
of this and will probably give you some ideas.