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

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461