Skip site navigation (1) Skip section navigation (2)

Re: Problem with PgTcl auditing function on trigger

From: Brett Schwarz <brett_schwarz(at)yahoo(dot)com>
To: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Problem with PgTcl auditing function on trigger
Date: 2008-01-04 22:07:15
Message-ID: 342200.2969.qm@web38905.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-hackers
> 
> 
> ----- Original Message ----
> From: Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk>
> To: pgsql-hackers(at)postgresql(dot)org
> Sent: Friday, January 4, 2008 5:23:18 AM
> Subject: [HACKERS] Problem with PgTcl auditing function on trigger
> 
> Hi people,
> 
> I've tried posting on the general list about this, but I never get
> a
> reply, so I'm trying here.
> 
> I have a function that is run each time an INSERT, DELETE or UPDATE
> happens on a row and log into an audit table.
> 
> It is based on the info here:
> 
> http://www.alberton.info/postgresql_table_audit.html
> 
> We have a table Customers.CREDIT with a primary key "NUMBER",
> "TRANSNO", "RECNUM".
> 
> I have a trigger as follows:
> 
> CREATE TRIGGER "tg_audit_credit"
> AFTER INSERT OR DELETE OR UPDATE ON "Customers"."CREDIT"
> FOR EACH ROW
> EXECUTE PROCEDURE "log_to_audit_table" ();
> 
> This uses the attached tcl function which basically runs this for a
> delete
> 
> spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld,
> pk_name, pk_value, mod_type, old_val, new_val)
>   VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname',
> '$modified_field',
> '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
> 
> The function works fine for this SQL statement
> 
> delete from "CREDIT" where "TRANSNO" < 11148188 AND "TRANSNO" >
> 11148180;
> 
> However if I try this one I get a syntax error.
> 
> delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" >
> 2484905;
> 

Is the Tcl function the same for RECNUM and TRANSNO? I noticed you have
set pk_name "RECNUM" ...

Not sure if you are changing this when you run the different DELETEs.

> 
> The error is below. Do I need to escape my strings? And if so how
> do
> I do this?
> 
> Thanks
> Glyn
> 
> 
> SEE=# delete from "CREDIT" where "RECNUM" < 2484907 AND "RECNUM" >
> 2484905;
> ERROR:  syntax error at or near "S"
> CONTEXT:  syntax error at or near "S"
>     while executing
> "spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld,
> pk_name, pk_value, mod_type, old_val, new_val)
> VALUES (CURRENT_TIMESTAMP, '$tguser', '$t..."
>     ("foreach" body line 5)
>     invoked from within
> "foreach field $TG_relatts {
>     if {! [string equal -nocase [lindex [array get OLD $field] 0]
> $pk_name]} {
>       set modified_field [lindex [array get..."
>     ("DELETE" arm line 11)
>     invoked from within
> "switch $TG_op {
> INSERT {
> 
>   #get PK value
>   foreach field $TG_relatts {
>     if {[string equal -nocase [lindex [array get NEW $field] 0]
> $pk_name]} {..."
>     (procedure "__PLTcl_proc_5667381_trigger_16644" line 23)
>     invoked from within
> "__PLTcl_proc_5667381_trigger_16644 tg_audit_credit 16644 CREDIT
> Customers {{} RECNUM TRANSNO NUMBER EXMON EXYEAR OLDTICK COACHES
> VALUE POSTAGE DEPOSIT..."
> 

I'm not sure where the error is coming from, off hand. The only thing
I can think of now is that you may need to [quote] the values or
use spi_execp instead. Perhaps there is a ' in there somewhere causing problems.


As a side note, just some tips (i realize that you got this from the link above):

You have several instances of constructs such as:

    if {! [string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {

but this really is just $field...so you don't need all of that. You can just do:

    if {! [string equal -nocase $field $pk_name]} {


Similiarily, you have this construct
    set pk_value [lindex [array get NEW $field] 1]

But you can use this instead:
    set pk_value $NEW($field)
    

And then, this whole block:

  foreach field $TG_relatts {
    if {[string equal -nocase [lindex [array get NEW $field] 0] $pk_name]} {
      set pk_value [lindex [array get NEW $field] 1]
      break;
    }
  }

Not sure the purpose here, but you should be able to just do:

    if {[info exists NEW($pk_name)]} {
       set pk_value $NEW($pk_name)
    } else {
       # something went wrong here...need this if there's a chance $pk_name might not be there
    }

Note also that TG_relatts has an empty element as the first element of the list, so this
     if {! [string equal -nocase [lindex [array get OLD $field] 0] $pk_name]} {

may be giving you trouble, since it won't catch the empty element.

so, you could write that particular loop construct as such:
  foreach field [lrange $TG_relatts 1 end] {
    if {! [string equal -nocase $field $pk_name]} {
      set modified_field $field
      set previous_value $OLD($field)
      spi_exec -array C "INSERT INTO audit_table(ts, usr, tbl, fld, pk_name, pk_value, mod_type, old_val, new_val)
    VALUES (CURRENT_TIMESTAMP, '$tguser', '$tgname', '$modified_field', '$pk_name', '$pk_value', '$TG_op', '$previous_value', NULL)"
    }
  }
    

You may to throw some [elog]'s in there, to see what's going on as well.

HTH,
    --brett



      ____________________________________________________________________________________
Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  http://tools.search.yahoo.com/newsearch/category.php?category=shopping

pgsql-hackers by date

Next:From: Kevin GrittnerDate: 2008-01-04 22:16:53
Subject: Re: OUTER JOIN performance regression remains in8.3beta4
Previous:From: Simon RiggsDate: 2008-01-04 22:01:31
Subject: Re: Dynamic Partitioning using Segment Visibility Maps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group