Re: How many insert + update should one transaction handle?

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