#1
  1. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19

    Ever used rcsrc from pg_relcheck to validate data in the app?


    Yes, data integrity needs to be in the database, however, I still need to do the same checks in the app if I'm to return meaningful error messages to users.

    That said, I thought of using Postgres' internal constraint check storage to get the info I need to create dynamic integrity checks (i.e. query the rcsrc field of the pg_relcheck internal table to get the constraint check created for the table.) Has anyone tried something like this or know of or can think of any pros/cons?
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Sorry to leave this post hanging out there, but I haven't really done any checking with pg_relcheck.rcsrc.

    I don't see why there could be anything wrong with doing this, and it could make a lot of sense for certain types of applications. Have you read this tutorial?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Thanks, rycamor.

    No, I hadn't read that, tho must of it I more or less knew about (except hacking the referential integrity portion). Don't think I'll be hacking the system tables anytime soon but it does give me some further ideas.
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You know, I first dismissed the idea of using the constraint name as an error message due to the limitation of 31 characters not being enough to convey a meaningful error message. After some thought, I COULD parse the constraint name out of the error message and use these as indices in an array of error messages. The problem here is multiple constraint violations.

    1) The error returns only one violation, even if the query would generate multiple violations
    2) If the transaction requires insert/update in multiple tables, it would stop on the first query to have a violation, thereby failing to report any violations that would have occured in subsequent queries in the transaction.

    Interesting idea, but I don't see a way around these problems...
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Other databases allow you to create and bind error message to certain constraints, error codes, etc.. From looking through the docs I couldn't find anything on that, so you'd probably have to do it yourself in some way. Encapsulate all your queries into functions (or stored procedures or whatever they are called) -- which you should do anyway -- which involve an error handler that looks up your own error table and spits out the 'pretty' error msg.
  10. #6
  11. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Matt,

    That is probably the best solution. I've decided to write trigger procedures for each table. Due to wanting all the checks to be performed so all error messages can be returned, each check appends the error message to one text variable. After all checks are completed the error message is checked for length >0 and if true, an exception raised. In my HTML forms I like to highlight elements that had an error, so my format for the error text is:

    \nColumnName\tError message

    This way I can seperate each error by \n and the column from the message by \t. The \n is at the beginning so I can easily drop the "ERROR:" string that's automatically prepended.

    I was a bit worried about updates. If I checked "fieldName" IS NULL and returned an error, what happens with fields not included in the update? But it worked NOT as I expected... if "fieldName" was not included in the update, 'if "fieldName" IS NULL' did not return true... This means I do not have to write contingencies for update vs insert or seperate procedures and triggers for updates and inserts

    Sorry if this is old hat for you, but I hope this will help others.

    Do you have any other suggestions?
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    So it works well? Good to hear that!

    If it was me I would probably try and make it as dynamic as possible; in most DBMS' system tables info is stored on constraints that is fetchable in your SQL statements (as are column info). So you could say (pseudocode):

    PHP Code:
    CREATE PROCEDURE error_checker
    AS

    /*
    We have our SQL statement in a system table
    or some other method of obtaining:
    -- table name <- assuming single-table insert/update
    -- columns in UPDATE/INSERT statement
    -- corresponding data values for columns 

    Does not handle *multi-row* INSERT or UPDATE 
    (assumed you will enter those statements yourself and probably not 
    from a web application)
    */ 
    SELECT scol.colname,
           
    scon.constraint_type
      FROM sysconstraints  scon
     INNER JOIN syscolumns scol ON  scol
    .colname scon.colname
                                
    AND scol.colname  IN SELECT colname
                                                        FROM SQL_Statement 
    )
                                AND 
    scol.tablename = ( SELECT table_name
                                                        FROM SQL_Statement 
    )
       
    /*
    Loop through each col in the SQL statement

    UPDATE/INSERTS should be the same; cannot INSERT INTO *nor* UPDATE to 
    NOT NULL col when dataval IS NULL
    Same for PK/UNIQUE.
    */
    foreach row IN result_set AS col_name => type {
        
        switch( 
    type ) {
            case 
    'PRIMARY_KEY':
              
    // more logic needed if multi-column PK
              
    ...
            break;
            
            case 
    'UNIQUE':
              
    // make sure NOT NULL and NOT EXISTS
              
    if( is_nullSELECT coldata
                             FROM SQL_Statement
                            WHERE colname 
    col_name )
                  OR
                    
    EXISTS SELECT *
                               
    FROM SELECT table_name
                                        FROM SQL_Statement 
    )
                              
    WHERE col_name = ( SELECT coldata
                                                   FROM SQL_Statement
                                                  WHERE colname 
    col_name )
                           )
                )
                     
    err_str err_str "\nViolated UNIQUE constraint!";
             break;
        } 
    // end switch
    // end foreach

    if( not is_nullerr_str ) ) {
      
    rollback;
      print 
    "You fool!  You broke it!" err_str;
    } else
      
    commit;
    -- 
    end procedure 
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Something like that you could write-once, use anywhere and perhaps give back to the PGSQL community to enhance and use; I suspect other are in somewhat the same boat as well and it may spur the PGSQL developers to include proper error handling via creation of error messages and binds.

    Then you'd have:
    Code:
    CREATE PROCEDURE insert_into_my_table
    AS
        INSERT INTO bob ( ... ) VALUES ( ... )
        
        CALL error_checker
    Or you could bind the error_checker procedure as a global "ON INSERT AND UPDATE CALL error_checker" sort of thing. Can you do that in pgsql?
  16. #9
  17. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    in most DBMS' system tables info is stored on constraints that is fetchable in your SQL statements
    Yes, this was the original plan, however... After my previous post, I did some looking for examples and came across an interesting comment. Basically the point was that when writing your own insert/update constraints, it may be preferrable to not use table (or row) check constraints. The reason being that you have a duplication of effort and waste of resources. Perhaps leaving the original constraints in and removing them after much testing to be sure the manual constraints work as expected.

    I would imagine I would leave NULL constraints alone, AND FK constraints as well since several triggers are needed there.

    Can you do that in pgsql?
    Not globally, AFAIK, but adding a trigger to each table is not difficult.

IMN logo majestic logo threadwatch logo seochat tools logo