Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jfqEX-0003PK-33 for pgsql-bugs@arkaria.postgresql.org; Mon, 01 Jun 2020 19:36:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jfqEV-0006r4-11 for pgsql-bugs@arkaria.postgresql.org; Mon, 01 Jun 2020 19:36:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jfqEU-0006n8-Jn for pgsql-bugs@lists.postgresql.org; Mon, 01 Jun 2020 19:36:26 +0000 Received: from forward101p.mail.yandex.net ([2a02:6b8:0:1472:2741:0:8b7:101]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jfqEL-0007Ay-Us for pgsql-bugs@lists.postgresql.org; Mon, 01 Jun 2020 19:36:25 +0000 Received: from mxback1j.mail.yandex.net (mxback1j.mail.yandex.net [IPv6:2a02:6b8:0:1619::10a]) by forward101p.mail.yandex.net (Yandex) with ESMTP id 24BFF26458A3; Mon, 1 Jun 2020 22:36:13 +0300 (MSK) Received: from myt5-ca5ec8faf378.qloud-c.yandex.net (myt5-ca5ec8faf378.qloud-c.yandex.net [2a02:6b8:c12:2514:0:640:ca5e:c8fa]) by mxback1j.mail.yandex.net (mxback/Yandex) with ESMTP id hzi78tRLEZ-aCa4jQqO; Mon, 01 Jun 2020 22:36:13 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1591040173; bh=cdjVIdZSjzaE6YPSQ1GjiRtpGSBDGfOzPkRGuazy/HI=; h=Subject:In-Reply-To:To:From:Cc:Date:References:Message-ID; b=oy7GlM3yXo0fkjXniQUrdSlZPpNRStdZGXeAXJSHIrcL3saW18rA75HehKOQPC2+A BAZFt2exAEUO9f+sIzIWQ5GdvTTUxrqu0Q3nuaWvHgkrKwI4+B69H4Fv0MD9tDQZbu AspkGBcJQizmRL90BBUng6DMNN+sJ973uluA9aFc= Authentication-Results: mxback1j.mail.yandex.net; dkim=pass header.i=@yandex.ru Received: by myt5-ca5ec8faf378.qloud-c.yandex.net (smtp/Yandex) with ESMTPSA id EYRT1PHXQ6-aC2iAqtA; Mon, 01 Jun 2020 22:36:12 +0300 (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client certificate not present) From: To: "'Tom Lane'" Cc: References: <16474-03a567470736cd26@postgresql.org> <387001.1591031124@sss.pgh.pa.us> In-Reply-To: <387001.1591031124@sss.pgh.pa.us> Subject: RE: BUG #16474: Foreign key issue Date: Mon, 1 Jun 2020 22:36:11 +0300 Message-ID: <000a01d6384b$e81a7900$b84f6b00$@yandex.ru> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQHv30UZtDwalPgvt0oa8zpyPD8dEgI6+MzAqH7565A= Content-Language: ru List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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 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 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