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

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