Обсуждение: 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? -- NeKo@(kva.hu|Kornel.szif.hu) the servant of Crash
> > 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) #
> > 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) #