Thread: BUG #16474: Foreign key issue

The following bug has been logged on the website:

Bug reference:      16474
Logged by:          Dmitry Ignatyev
Email address:      d.a.ignatyev@yandex.ru
PostgreSQL version: 12.2
Operating system:   Windows 10
Description:

Hey, guys,

A few lines of SQL to clarify the situation:

        /* Here we make sure there is a value in parent table */

    select source from shelf.r_source where source = 'PPDM39RNEX' \gset

       /* And here we try to use it in an insert */

    INSERT INTO ppdm.ppdm_rule_remark
        (rule_id, 
        remark_type, 
        remark_seq_no, 
        remark_date, 
        "source")
    VALUES
        (:'ppdm_rule_id',
        :'remark_type',
        nextval('shelf.id_sequence'),
        NOW(),
        :'source');

This kind of code produces an SQLSTATE 23503

START TRANSACTION
psql:rule_run.sql:43: ОШИБКА:  INSERT или UPDATE в таблице
"ppdm_rule_remark" нарушает ограничение внешнего ключа "prr_r_s_fk"
ПОДРОБНОСТИ:  Ключ (source)=(PPDM39RNEX) отсутствует в таблице "r_source".
ROLLBACK

This is a foreign key violation message in Russian reporting an absence of
an existing value PPDM39RNEX.

Any quick workaround?

Best regards, Dmitry

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

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

On 2020-Jun-01, PG Bug reporting form wrote:

>     select source from shelf.r_source where source = 'PPDM39RNEX' \gset

This one in the initial post says shelf.r_source, the other that you
pasted complete tables says ppdm.r_source.  Are you querying the correct
table?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services