
> > Vadim, can you remind me what UNDO is used for?
> 4. Split pg_log into small files with ability to remove old ones (which
>    do not hold statuses for any running transactions).

They are already small (16Mb). Or do you mean even smaller ?
This imposes one huge risk, that is already a pain in other db's. You need
all logs of one transaction online. For a GigaByte transaction like a bulk
insert this can be very inconvenient. 
Imho there should be some limit where you can choose whether you want 
to continue without the feature (no savepoint) or are automatically aborted.

In any case, imho some thought should be put into this :-)

Another case where this is a problem is a client that starts a tx, does one little
insert or update on his private table, and then sits and waits for a day.

Both cases currently impose no problem whatsoever.

Andreas

Mon, 21 May 2001 16:44:42 +0200
Zeugswetter Andreas SB  <ZeugswetterA@wien.spardat.at>
AW: Plans for solving the VACUUM problem


Zeugswetter Andreas SB wrote:

>>> Vadim, can you remind me what UNDO is used for?
>> 
>> 4. Split pg_log into small files with ability to remove old ones (which
>>    do not hold statuses for any running transactions).
> 
> 
> They are already small (16Mb). Or do you mean even smaller ?
> This imposes one huge risk, that is already a pain in other db's. You need
> all logs of one transaction online. For a GigaByte transaction like a bulk
> insert this can be very inconvenient. 
> Imho there should be some limit where you can choose whether you want 
> to continue without the feature (no savepoint) or are automatically aborted.
> 
> In any case, imho some thought should be put into this :-)
> 
> Another case where this is a problem is a client that starts a tx, does one little
> insert or update on his private table, and then sits and waits for a day.
> 
> Both cases currently impose no problem whatsoever.

Correct me if I am wrong, but both cases do present a problem currently 
in 7.1.  The WAL log will not remove any WAL files for transactions that 
are still open (even after a checkpoint occurs).  Thus if you do a bulk 
insert of gigabyte size you will require a gigabyte sized WAL 
directory.  Also if you have a simple OLTP transaction that the user 
started and walked away from for his one week vacation, then no WAL log 
files can be deleted until that user returns from his vacation and ends 
his transaction.

--Barry

> 
> 
> Andreas
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 


Mon, 21 May 2001 13:19:08 -0700
Barry Lind <barry@xythos.com>
Re: AW: Plans for solving the VACUUM problem
Barry Lind wrote:
>
>
> Zeugswetter Andreas SB wrote:
>
> >>> Vadim, can you remind me what UNDO is used for?
> >>
> >> 4. Split pg_log into small files with ability to remove old ones (which
> >>    do not hold statuses for any running transactions).
> >
> >
> > They are already small (16Mb). Or do you mean even smaller ?
> > This imposes one huge risk, that is already a pain in other db's. You need
> > all logs of one transaction online. For a GigaByte transaction like a bulk
> > insert this can be very inconvenient.
> > Imho there should be some limit where you can choose whether you want
> > to continue without the feature (no savepoint) or are automatically aborted.
> >
> > In any case, imho some thought should be put into this :-)
> >
> > Another case where this is a problem is a client that starts a tx, does one little
> > insert or update on his private table, and then sits and waits for a day.
> >
> > Both cases currently impose no problem whatsoever.
>
> Correct me if I am wrong, but both cases do present a problem currently
> in 7.1.  The WAL log will not remove any WAL files for transactions that
> are still open (even after a checkpoint occurs).  Thus if you do a bulk
> insert of gigabyte size you will require a gigabyte sized WAL
> directory.  Also if you have a simple OLTP transaction that the user
> started and walked away from for his one week vacation, then no WAL log
> files can be deleted until that user returns from his vacation and ends
> his transaction.

    As  a  rule  of  thumb,  online  applications  that hold open
    transactions during user interaction  are  considered  to  be
    Broken  By  Design  (tm).   So I'd slap the programmer/design
    team with - let's use the server box since it doesn't contain
    anything useful.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Mon, 21 May 2001 16:41:33 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: AW: Plans for solving the VACUUM problem
At 04:41 PM 21-05-2001 -0400, Jan Wieck wrote:
>
>    As  a  rule  of  thumb,  online  applications  that hold open
>    transactions during user interaction  are  considered  to  be
>    Broken  By  Design  (tm).   So I'd slap the programmer/design
>    team with - let's use the server box since it doesn't contain
>    anything useful.
>

Many web applications use persistent database connections for performance
reasons.

I suppose it's unlikely for webapps to update a row and then sit and wait a
long time for a hit, so it shouldn't affect most of them.

However if long running transactions are to be aborted automatically, it
could possibly cause problems with some apps out there. 

Worse if long running transactions are _disconnected_ (not just aborted).

Regards,
Link.




Tue, 22 May 2001 09:38:54 +0800
Lincoln Yeoh <lyeoh@pop.jaring.my>
Re: AW: Plans for solving the VACUUM problem
> >    As  a  rule  of  thumb,  online  applications  that hold open
> >    transactions during user interaction  are  considered  to  be
> >    Broken  By  Design  (tm).   So I'd slap the programmer/design
> >    team with - let's use the server box since it doesn't contain
> >    anything useful.
>
> Many web applications use persistent database connections for performance
> reasons.

Persistent connection is not the same as an OPEN transaction BTW.

> I suppose it's unlikely for webapps to update a row and then sit and wait a
> long time for a hit, so it shouldn't affect most of them.
>
> However if long running transactions are to be aborted automatically, it
> could possibly cause problems with some apps out there.
>
> Worse if long running transactions are _disconnected_ (not just aborted).

-- 
Sincerely Yours,
Denis Perchine

----------------------------------
E-Mail: dyp@perchine.com
HomePage: http://www.perchine.com/dyp/
FidoNet: 2:5000/120.5
----------------------------------

Tue, 22 May 2001 14:25:59 +0700
Denis Perchine <dyp@perchine.com>
Re: Re: AW: Plans for solving the VACUUM problem
Lincoln Yeoh wrote:
> At 04:41 PM 21-05-2001 -0400, Jan Wieck wrote:
> >
> >    As  a  rule  of  thumb,  online  applications  that hold open
> >    transactions during user interaction  are  considered  to  be
> >    Broken  By  Design  (tm).   So I'd slap the programmer/design
> >    team with - let's use the server box since it doesn't contain
> >    anything useful.
> >
>
> Many web applications use persistent database connections for performance
> reasons.
>
> I suppose it's unlikely for webapps to update a row and then sit and wait a
> long time for a hit, so it shouldn't affect most of them.
>
> However if long running transactions are to be aborted automatically, it
> could possibly cause problems with some apps out there.
>
> Worse if long running transactions are _disconnected_ (not just aborted).

    All   true,   but   unrelated.  He  was  talking  about  open
    transactions holding locks while the user is off  to  recycle
    some  coffee  or so. A persistent database connection doesn't
    mean that you're holding a transaction while waiting for  the
    next hit.

    And  Postgres doesn't abort transaction or disconnect because
    of their runtime. Then again, it'd take care  for  half  done
    work aborted by the httpd because a connection loss inside of
    a transaction causes an implicit rollback.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Tue, 22 May 2001 09:01:56 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Re: AW: Plans for solving the VACUUM problem