Re: Preserving data after updates

Berend Tober <btober@xxxxxxxxxxxxxxxx> · Thu, 19 May 2005 17:14:44 -0400

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;

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org