On Tue, Sep 27, 2005 at 01:34:37PM +0200, Yonatan Ben-Nes wrote: > >Based on http://lnk.nu/developer.postgresql.org/44b.c, line 1478 on, > >there's not a lot that happens during the ALTER TABLE. Likewise DROP > >(line 517) doesn't do much either. So basically, anything trying to > >access the old table will block for a while waiting for the update to > >happen. > > > >But keep in mind that 'a while' will depend on what's happening on the > >system. Imagine... > > > >Start long transaction involving table > >Run code above; drop aquires lock on table > > > >Everything else against table will now block, waiting for the DROP to > >happen. > > Jim unless I didnt understand you I think that at my case I wont need to > make any long transaction which will handle the DROP & renaming of tables. > I will actually have 2 transactions now: > 1. which will handle the INSERT + UPDATE of the data into the temp_table > & at the end will move all of the new data (without the deleted tuples) > to the new_table and create its indexes. > 2. the second transaction will only handle the drop & renaming of the 2 > tables (new_table & table); > > The question is whats the expected time for the second transaction to > run? will it create problems to the constant availability of the site? > > S.Gnanavel I tried your idea but sadly it gives me the same block as I > would DROP the table and not RENAME it. Like I said, neither the ALTER or the DROP do much themselves. There is a slight advantage to Gnanavel's plan in that the ALTER won't wait for the filesystem to delete the files from the disk, but I *think* that DROP will. So it will be slightly faster. In either case, if a user is running a long transaction on the old table when you try and rename/drop it, that is going to completely swamp the effects of everything else. So, if you have some long-running queries that use that table, there is going to be a noticable delay to the system. If you don't have any queries like that, then this should work just fine. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
