Re: [ADMIN] Log, via triggers. - Mailing list pgsql-admin

>
> Hello
>
> I tried create a small logging utility for postgresql (6.4, i use it on
> redhat-linux-5.2)
> I meand with triggers 'n SPI all database change can be hooked.
> But this have small problem: in an aborted transaction the triggers run on
> changes, but NOT run with the undos.
> 4xmpl.:
>  --
> my=> Begin;
> my=> insert into test values ( 'now',0,'Text' );
> NOTICE: trigf(): Ok, Insert event sent to log server.
> INSERT [5234]
> my=> -- Succesfully logged. BUT:
> my-> rollback;
> ROLLBACK
> my=> -- Not logged ;(
>  --
> Can postgres call trigger beetween rollbacks too? (Nawp: all changes must be
> succesfully undoned, this can not depending on triggers)
> ... can anybody give another solution for logging?

    From  the 'sent to log server' I assume you want that logging
    to go outside the database.

    Sorry - but that cannot work in Postgres. It has to  do  with
    the  way  COMMIT  and  ROLLBACK  work (what I'm simplifying a
    little below).

    Up to now, Postgres does not  modify  or  overwrite  existing
    data in the tables (except for some special cases). There are
    two special fields in the tuple header, xmin and xmax  (along
    with  cmin  and  cmax  for  finer  granularity in the current
    transaction). Let's call the current transaction ID "CTID".

    On INSERT, a new tuple with xmin  =  CTID  is  added  to  the
    table.  On UPDATE, the header of the old tuple is modified to
    xmax = CTID and a new tuple with xmin =  CTID  is  added.  On
    DELETE  only  the  xmax  =  CTID  on  the  existing  tuple is
    performed.

    At COMMIT/ROLLBACK time,  only  the  CTID  is  remembered  as
    committed/aborted  in  the  pg_log file. Nothing else. Any of
    the tuples (old and new ones) are in  the  tables.  Only  the
    xmin  and  xmax  fields  along  with  the  information  if  a
    transaction committed tells, if a tuple should be skipped  in
    a heap scan or not.

    That  all  has  the advantage, that Postgres does not need to
    remember  anything  about   the   changes   done   during   a
    transaction,  because  it  does not need to UNDO anything. It
    just remembers if the changes are committed or not. There  is
    no need for ROLLBACK SEGMENTS like in Oracle.

    The  VACUUM  cleaner  finally  throws away those tuples where
    xmin isn't committed or where xmax is.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #