Thread: drop/add fk in transaction
Hi all, 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 ? I'm using 7.3.3 TIA -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
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.
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. I think it would be ok for that case to lock everything :) while this transaction is running. Do you have a commandline by hand ? Usually I would just do begin; ... end; TFYH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de
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.
Hello All I am trying to test the following function, But I dont know how to test these functions. Please help me how to test these functions, and also give some examples.... 1. pg_get_viewdef 2. pg_get_ruledef 3. pg_get_indexdef 4. pg_get_userbyid 5. obj_description thanks in advance --mathan --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.491 / Virus Database: 290 - Release Date: 6/18/2003
Am Don, 2003-08-14 um 04.23 schrieb Stephan Szabo: > > 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 already tried that with some less important databases and it took about 2 seconds. The DBs aren't that big and the machine is quite 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. I'll try that. TFYH -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : http://www.e-trolley.de