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)---------------------------
Show quoted text
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 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