Re: Preserving data after updates

Tom Lane wrote:

>The case I tested seems to work in 7.3 as well:
>
>CREATE TABLE person (last_name varchar(24),
> first_name varchar(24),
> CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
>(first_name IS NOT NULL))));
>
>CREATE TABLE person_change_history(
> action VARCHAR(6),
> update_date TIMESTAMP NOT NULL DEFAULT NOW(),
> update_user NAME NOT NULL DEFAULT CURRENT_USER
> ) INHERITS (person);
>
>pg_dump puts the CONSTRAINT only on person, as it should. I'm testing
>7.3.10 but I don't see any changes in the 7.3 CVS log that look related.
>Can you put together a reproducible test case?
>
>
I tried a simpler example than my original, as you have, and the problem
bahavior didn't manifest, but it still happens in my dev copy of my
production database. The immediately obvious difference between the
simpler example, like yours, and the actual case in which the problem
manifests is that the problem case to of the table constraints call a
user-defined function "check_pattern()" (which tests the column value
against a regular expression), i.e.

CREATE OR REPLACE FUNCTION public.check_pattern("varchar", "varchar")
RETURNS bool AS
'
DECLARE
l_value ALIAS FOR $1;
l_pattern ALIAS FOR $2;
l_row RECORD;
BEGIN
IF (l_value IS NOT NULL) AND (LENGTH(l_value) > 0) THEN
IF EXISTS(SELECT 1 FROM public.regular_expression WHERE
UPPER(description) = UPPER(l_pattern)) THEN
SELECT INTO l_row regular_expression, user_message FROM
public.regular_expression WHERE UPPER(description) = UPPER(l_pattern);
IF NOT (l_value ~ l_row.regular_expression) THEN
RAISE EXCEPTION \'Invalid %. %\', l_pattern, l_row.user_message;
END IF;
END IF;
END IF;
RETURN TRUE;
END;' LANGUAGE 'plpgsql' VOLATILE;

in the definition:

CREATE TABLE person
(
person_pk int4 NOT NULL DEFAULT nextval('"person_person_pk_seq"'::text),
last_name varchar(24),
first_name varchar(24),
middle_name varchar(24),
e_mail_address name,
social_security_no varchar(11),
CONSTRAINT person_pkey PRIMARY KEY (person_pk),
CONSTRAINT person_e_mail_address CHECK
(check_pattern((e_mail_address)::character varying, 'Internet E-Mail
Address'::character varying)),
CONSTRAINT person_name_check CHECK (((last_name IS NOT NULL) OR
(first_name IS NOT NULL))),
CONSTRAINT person_social_security_no CHECK
(check_pattern(social_security_no, 'Social Security Number'::character
varying))
)
WITHOUT OIDS;