RE: BUG #16474: Foreign key issue

Hi, Tom,

I expected a question on data structures. Well, the whole thing consists of
2683 tables and other stuff. I don't believe you need them all. But let me
know if it would be useful to send more structures for debug purposes.

That's the parent table.

CREATE TABLE ppdm.r_source
(
    source character varying(40) COLLATE pg_catalog."default" NOT NULL,
    abbreviation character varying(12) COLLATE pg_catalog."default",
    active_ind character varying(1) COLLATE pg_catalog."default",
    effective_date timestamp(0) without time zone,
    expiry_date timestamp(0) without time zone,
    long_name character varying(255) COLLATE pg_catalog."default",
    ppdm_guid character varying(38) COLLATE pg_catalog."default" NOT NULL,
    remark character varying(2000) COLLATE pg_catalog."default",
    row_source character varying(40) COLLATE pg_catalog."default",
    short_name character varying(30) COLLATE pg_catalog."default",
    row_changed_by character varying(30) COLLATE pg_catalog."default",
    row_changed_date timestamp(0) without time zone,
    row_created_by character varying(30) COLLATE pg_catalog."default",
    row_created_date timestamp(0) without time zone,
    row_effective_date timestamp(0) without time zone,
    row_expiry_date timestamp(0) without time zone,
    row_quality character varying(40) COLLATE pg_catalog."default",
    CONSTRAINT r_s_pk PRIMARY KEY (source),
    CONSTRAINT r_s_guid UNIQUE (ppdm_guid),
    CONSTRAINT r_s_r_prq_fk FOREIGN KEY (row_quality)
        REFERENCES ppdm.r_ppdm_row_quality (row_quality_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT r_s_r_s_fk FOREIGN KEY (row_source)
        REFERENCES ppdm.r_source (source) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT r_s_ck CHECK (active_ind::text = ANY (ARRAY['Y'::character
varying, 'N'::character varying]::text[]))
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

That's a child  table:

CREATE TABLE ppdm.ppdm_rule_remark
(
    rule_id character varying(40) COLLATE pg_catalog."default" NOT NULL,
    remark_type character varying(40) COLLATE pg_catalog."default" NOT NULL,
    remark_seq_no integer NOT NULL,
    active_ind character varying(1) COLLATE pg_catalog."default",
    effective_date timestamp(0) without time zone,
    expiry_date timestamp(0) without time zone,
    ppdm_guid character varying(38) COLLATE pg_catalog."default" NOT NULL,
    remark character varying(2000) COLLATE pg_catalog."default",
    remark_by_ba_id character varying(40) COLLATE pg_catalog."default",
    remark_date timestamp(0) without time zone,
    source character varying(40) COLLATE pg_catalog."default",
    row_changed_by character varying(30) COLLATE pg_catalog."default",
    row_changed_date timestamp(0) without time zone,
    row_created_by character varying(30) COLLATE pg_catalog."default",
    row_created_date timestamp(0) without time zone,
    row_effective_date timestamp(0) without time zone,
    row_expiry_date timestamp(0) without time zone,
    row_quality character varying(40) COLLATE pg_catalog."default",
    CONSTRAINT prr_pk PRIMARY KEY (rule_id, remark_type, remark_seq_no),
    CONSTRAINT prr_guid UNIQUE (ppdm_guid),
    CONSTRAINT prr_ba_fk FOREIGN KEY (remark_by_ba_id)
        REFERENCES ppdm.business_associate (business_associate_id) MATCH
SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT prr_pr_fk FOREIGN KEY (rule_id)
        REFERENCES ppdm.ppdm_rule (rule_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT prr_r_prq_fk FOREIGN KEY (row_quality)
        REFERENCES ppdm.r_ppdm_row_quality (row_quality_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT prr_r_rt2_fk FOREIGN KEY (remark_type)
        REFERENCES ppdm.r_remark_type (remark_type) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT prr_r_s_fk FOREIGN KEY (source)
        REFERENCES ppdm.r_source (source) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT prr_ck CHECK (active_ind::text = ANY (ARRAY['Y'::character
varying, 'N'::character varying]::text[]))
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

There's a record in r_source with a value 'PPDM39RNEX' on it.

In a sample script I specifically get this value from r_source. I can
utilize a string literal or whatever way to define the value.

My select works. My insert fails saying that there is no such value in
r_source. That's a foreign key checking issue.

The roughly same query fails in psql and pgAdmin as well.

In a mean time I will run the same exercise on version 11.8. 

For me an issue like that is a failure of the 3-months long development
exercise and a good reason for a database backend replacement.  


Best regards, Dmitry Ignatyev


-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us> 
Sent: Monday, June 1, 2020 8:05 PM
To: d.a.ignatyev@yandex.ru
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #16474: Foreign key issue

PG Bug reporting form <noreply@postgresql.org> writes:
> A few lines of SQL to clarify the situation:

This isn't clarifying much.  If I guess at your table definitions, it works
for me:

regression=# create table r_source (source text primary key); CREATE TABLE
regression=# create table ppdm_rule_remark(source text references r_source);
CREATE TABLE regression=# insert into r_source values ('PPDM39RNEX'); INSERT
0 1 regression=# select source from r_source where source = 'PPDM39RNEX'
\gset regression=# \echo :source PPDM39RNEX regression=# INSERT INTO
ppdm_rule_remark("source") values (:'source'); INSERT 0 1

Please provide a *self contained* example if you want us to investigate more
closely.

            regards, tom lane