Jim C. Nasby wrote:
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.
Thanks for the help, now ill just have to think what will be better at
my case.
Shana Tova Everyone! (Happy new year in hebrew :))
Ben-Nes Yonatan
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
