Thread

1. Data integrity and sanity check

   Ferruccio Zamuner <nonsolosoft@diff.org> — 2002-03-30T15:08:09Z

   Hi,
   
   someone asks me about an utility to check any PostgreSQL database
   data to be sure that:
   1) there is not any page corrupted 
      (by a memory fault or a damaged disk)
   2) re-check any constraint inserted into the database
   
   I really don't know if PostgreSQL itself has any crc check on
   its pages. Please, there is anyone able to confirm such function?
   
   I've understood that PostgreSQL trust the operating system for
   doing its work, but I don't know if there is any operating system
   able to give warranty the memory sanity before allocation, during 
   the memory use. 
   
   According to me, if the database is well-designed it's not 
   possible to find constraint violation on data already inserted
   and accepted from the SQL engine. 
   Am I in fault for this sentence?
   
    
   Thank you in advance for any reply.
   
   
   Best regards,              \fer
   
   

2. Re: Data integrity and sanity check

   Rod Taylor <rbt@zort.ca> — 2002-03-30T15:34:31Z

   > 2) re-check any constraint inserted into the database
   
   There should not be any if it was accepted, however if it's a new
   constraint it doesn't get applied to data that already exists.  A dump
   and restore will ignore these as well (with good reason).
   
   I suppose the easiest way to find if data violates current constraints
   (rather than the constraints applied during initial insertion) is to:
   
   update table set column = column;
   
   That should re-process any constraints.
   
   
   Primary keys, or other index style constraints (UNIQUE for example)
   are always guarenteed.  The only way that new constraints are added is
   via alter table commands.
   
   BTW.  There are good reasons sometimes for having data that violates
   current constraints.  The top of a tree may have a static record with
   a null parent.  The NOT NULL constraint added after this entry (via
   alter table add constraint) should not affect the static record, so
   unless you know your data quite well this type of tool wouldn't be
   particularly useful anyway.
   
   Normally I use triggers which are programmed to account for that, but
   there are a few cases where the check constraint speed (rather than
   the trigger) is useful and the assumption the initial record will
   never be touched is good enough.
   
   
   

3. Re: Data integrity and sanity check

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2002-03-31T05:31:36Z

   > BTW.  There are good reasons sometimes for having data that violates
   > current constraints.  The top of a tree may have a static record with
   > a null parent.  The NOT NULL constraint added after this entry (via
   > alter table add constraint) should not affect the static record, so
   > unless you know your data quite well this type of tool wouldn't be
   > particularly useful anyway.
   
   As far as I am aware, there is no alter table add constraint syntax for
   NOT NULLs atm.  I've submitted a patch that allows alter table/alter
   column set/drop not null though.
   
   Chris
   
   
   
   

4. Re: Data integrity and sanity check

   Rod Taylor <rbt@zort.ca> — 2002-03-31T13:16:11Z

   There was -- kinda
   
   alter table tab add constraint check (value not null);
   --
   Rod Taylor
   
   Your eyes are weary from staring at the CRT. You feel sleepy. Notice
   how restful it is to watch the cursor blink. Close your eyes. The
   opinions stated above are yours. You cannot imagine why you ever felt
   otherwise.
   
   ----- Original Message -----
   From: "Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
   To: "Rod Taylor" <rbt@zort.ca>
   Cc: "Ferruccio Zamuner" <nonsolosoft@diff.org>;
   <pgsql-hackers@postgresql.org>
   Sent: Sunday, March 31, 2002 12:31 AM
   Subject: Re: [HACKERS] Data integrity and sanity check
   
   
   > > BTW.  There are good reasons sometimes for having data that
   violates
   > > current constraints.  The top of a tree may have a static record
   with
   > > a null parent.  The NOT NULL constraint added after this entry
   (via
   > > alter table add constraint) should not affect the static record,
   so
   > > unless you know your data quite well this type of tool wouldn't be
   > > particularly useful anyway.
   >
   > As far as I am aware, there is no alter table add constraint syntax
   for
   > NOT NULLs atm.  I've submitted a patch that allows alter table/alter
   > column set/drop not null though.
   >
   > Chris
   >
   >
   >
   > ---------------------------(end of
   broadcast)---------------------------
   > TIP 3: if posting/reading through Usenet, please send an appropriate
   > subscribe-nomail command to majordomo@postgresql.org so that your
   > message can get through to the mailing list cleanly
   >
   
   
   

5. Re: Data integrity and sanity check

   Jan Wieck <janwieck@yahoo.com> — 2002-04-01T16:14:10Z

   Rod Taylor wrote:
   > > 2) re-check any constraint inserted into the database
   >
   > There should not be any if it was accepted, however if it's a new
   > constraint it doesn't get applied to data that already exists.  A dump
   > and restore will ignore these as well (with good reason).
   
       Please don't make up any answers. If you don't know for sure,
       look at the code in question or just don't answer.
   
       PostgreSQL does check all existing data when adding a foreign
       key  contraint.  It  skips  the check during the restore of a
       dump though.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #================================================== JanWieck@Yahoo.com #
   
   
   
   _________________________________________________________
   Do You Yahoo!?
   Get your free @yahoo.com address at http://mail.yahoo.com