Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back. On update, it generates a list of columns in the table being updated and inserts rows in the auditing table recording any changes (by table, column, data before, data after, etc). The same trigger function is used across multiple tables.

I'm toying with pl/perl as it seems to be much faster for the task at hand, but I seem to have run into a problem on differentiating between NULL and empty string ('') value in the database.

In my mind if a column goes from NULL to an empty string (or vice versa), that's a change that I need to record. However, using the available new/old column references ($_TD->{new/old}{$columnName}), I can't seem to differentiate between columns that are actually null and those that contain the empty string. Columns that I know are null are being caught by both the empty and the undef checks, as are columns that I know are empty.

    if($_TD->{new}{$column} eq '') {
        elog(NOTICE, "New value in column $column is empty");
    }
    if($_TD->{old}{$column} eq '') {
        elog(NOTICE, "Old value in column $column is empty");
    }
    if($_TD->{new}{$column} eq undef) {
        elog(NOTICE, "New value in column $column is not defined");
    }
    if($_TD->{old}{$column} eq undef) {
        elog(NOTICE, "Old value in column $column is not defined");
    }

I suspect that I'm doing something silly here, but maybe I'm trying to do something that I simply can't do. Any advice?

Edit - Using Postgres 8.4.4 for what it's worth

Edit - After looking at filiprem's post below (and lots more testing), I ended up with this, which seems to be working:

    my %newrow = %{$_TD->{new}};
    my %oldrow = %{$_TD->{old}};

    my $valChanged;
    while (($column,$value) = each %newrow) {

        $valChanged = 0;

        if($newrow{$column} ne $oldrow{$column}) {
            $valChanged = 1;
            elog(NOTICE, "Values in column $column differ.  New: $_TD->{new}{$column}, Old: $_TD->{old}{$column}");
        }   
        elsif(!defined($newrow{$column}) && defined($oldrow{$column})) {
            elog(NOTICE, "New row contains nulled out field");
            $valChanged = 1;
        }
        elsif(defined($newrow{$column}) && !defined($oldrow{$column})) {
            elog(NOTICE, "New row contains newly populated field");
            $valChanged = 1;
        }

        if($valChanged) {
            ### Update audit table
        }
    }

It catches the difference between empty strings and NULLs and logs them all appropriately in the audit table.

share|improve this question
    
I solve this by adding a not empty constraint xenoterracide.com/2010/01/… –  xenoterracide Nov 2 '11 at 0:03
    
+1 for not empty constraint - it's almos always a good idea. –  filiprem Nov 2 '11 at 5:27
add comment

1 Answer

up vote 3 down vote accepted

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}
share|improve this answer
    
This is not a bug because SQL null means 'NO VALUE' and an empty string IS a value. –  xenoterracide Nov 2 '11 at 0:05
    
    
I had my Null vs empty string run-in a few years back, that part I've got; I'm actually trying to distinguish between the two situations here. My failure seems to be on the Perl side of things. After toying more, using the actual defined function and not comparing to the undef value seems to help. I also started extracting the rows from the trigger data (my %newrow = %{$_TD->{new}};) before looking at the columns. I'll provide more info when I know it works. –  Jafoy Nov 2 '11 at 1:45
    
@xenoterracide, I think it's a bug in PL/Perl, I'm not discussing SQL NULL semantics here (good article, BTW). If it's not a bug then please say - How can PL/Perl distinguish NULL::text arg from ''::text arg, in a better way than I've shown? –  filiprem Nov 2 '11 at 5:25
    
Led me to the proper result. –  Jafoy Nov 2 '11 at 16:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.