Re: [ADMIN] Log, via triggers.

>
> 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(at)debis(dot)com (Jan Wieck) #