Thread: Data integrity and sanity check

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-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.

> 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

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
>

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
restoreof 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