pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16474: Foreign key issue
4+ messages / 4 participants
[nested] [flat]

* BUG #16474: Foreign key issue
@ 2020-06-01 16:17  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 2 replies; 4+ messages in thread

From: PG Bug reporting form @ 2020-06-01 16:17 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: d.a.ignatyev@yandex.ru

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16474: Foreign key issue
@ 2020-06-01 17:05  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2020-06-01 17:05 UTC (permalink / raw)
  To: d.a.ignatyev@yandex.ru; +Cc: pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: BUG #16474: Foreign key issue
@ 2020-06-01 19:36  d.a.ignatyev@yandex.ru
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 4+ messages in thread

From: d.a.ignatyev@yandex.ru @ 2020-06-01 19:36 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: BUG #16474: Foreign key issue
@ 2020-06-01 19:52  Alvaro Herrera <alvherre@2ndquadrant.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  1 sibling, 0 replies; 4+ messages in thread

From: Alvaro Herrera @ 2020-06-01 19:52 UTC (permalink / raw)
  To: d.a.ignatyev@yandex.ru; pgsql-bugs@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2020-06-01 19:52 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-01 16:17 BUG #16474: Foreign key issue PG Bug reporting form <noreply@postgresql.org>
2020-06-01 17:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-01 19:36   ` d.a.ignatyev@yandex.ru
2020-06-01 19:52 ` Alvaro Herrera <alvherre@2ndquadrant.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox