BUG #14609: ON CONSTRAINT (aka UPSERT) code fails when excluded.<columname> used in calculation.
The following bug has been logged on the website:
Bug reference: 14609
Logged by: Paul Coyne
Email address: paul.coyne@diridium.com
PostgreSQL version: 9.5.6
Operating system: Ubuntu 14.04 (and later)
Description:
Table:
CREATE TABLE public.test
(
id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
patient_id bigint,
site_code character varying,
mrn character varying,
sendingfacility character varying(50),
CONSTRAINT test_pkey PRIMARY KEY (id),
CONSTRAINT test_site_code_mrn_key UNIQUE (site_code, mrn)
)
WITH (
OIDS=FALSE
);
CREATE INDEX index_test_on_patient_id
ON public.test
USING btree
(patient_id);
CREATE UNIQUE INDEX index_test_on_site_code_and_mrn
ON public.test
USING btree
(site_code COLLATE pg_catalog."default", mrn COLLATE
pg_catalog."default");
SP:
CREATE OR REPLACE FUNCTION public.sp_test_upsert_with_coalesce(
p_sendingfacility character varying DEFAULT NULL::character varying,
p_site_code character varying DEFAULT NULL::character varying,
p_mrn character varying DEFAULT NULL::character varying)
RETURNS bigint AS
$BODY$
/* BEGIN */
INSERT INTO test(
sendingfacility,
site_code,
mrn
)
VALUES (
NULLIF(p_sendingfacility,''),
p_site_code,
p_mrn
)
ON CONFLICT ON CONSTRAINT test_site_code_mrn_key
DO UPDATE
SET sendingfacility =
NULLIF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code
RETURNING id;
/* END; */
$BODY$
LANGUAGE sql VOLATILE
COST 100;
SQL to Run:
select sp_test_upsert_with_coalesce('ABC','1','DEF')
select * from test -- returns 1,,1,DEF,ABC
select sp_test_upsert_with_coalesce('DEF','1','DEF')
select * from test -- returns 1,,1,DEF,DEF
select sp_test_upsert_with_coalesce('','1','DEF')
select * from test -- returns 1,,1,DEF,, -- ERROR: blank parameter
should not be written to DB based on SP
-- if
'excluded.sendingfacility' is changed to test.sendingfacility, the code
works
Can excluded.variablename be used in a calculation? It appears to have the
value NULL in the formula.
The actual code applies to healthcare HL7 messages where by most fields
follow this logic:
If I sent you a value for variable A, store it.
If I send you an empty string for variable A, keep what I sent you before
If I send you "", clear out the variable A
If I send you a new value for for variable A, store it.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Fri, Mar 31, 2017 at 5:10 PM, <paul.coyne@diridium.com> wrote:
Can excluded.variablename be used in a calculation? It appears to have the
value NULL in the formula.
I'm on a flight, and haven't looked at this properly, but I have a
hard time following the expression in the UPDATE part of your INSERT
within the plpgsql function. What is this supposed to do?
IF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code
It's also weird that "WHERE excluded.mrn = p_mrn" is there, since
you're proposing that same value (function argument) for insertion in
the first place (same with site_code).
Are you sure that this isn't just an "IS NULL vs. =" issue?
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
This does not work (all other code the same for the SP but omitted for brevity):
SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
This does work as intended.
SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),test.sendingfacility),'""')
It is as if I can’t reference “excluded,sendingfacility” in the calculation.
The is irrespective of the WHERE clause, but I can remove it as you mention.
On 3/31/17, 4:54 PM, "Peter Geoghegan" <pg@bowt.ie> wrote:
On Fri, Mar 31, 2017 at 5:10 PM, <paul.coyne@diridium.com> wrote:
Can excluded.variablename be used in a calculation? It appears to have the
value NULL in the formula.
I'm on a flight, and haven't looked at this properly, but I have a
hard time following the expression in the UPDATE part of your INSERT
within the plpgsql function. What is this supposed to do?
IF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code
It's also weird that "WHERE excluded.mrn = p_mrn" is there, since
you're proposing that same value (function argument) for insertion in
the first place (same with site_code).
Are you sure that this isn't just an "IS NULL vs. =" issue?
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Typo in last post:
Before: It is as if I can’t reference “excluded,sendingfacility” in the calculation.
After: It is as if I can’t reference “excluded.sendingfacility” in the calculation.
I didn't answer part of your question: "... a hard time following the expression in the UPDATE part of your INSERT within the plpgsql function. What is this supposed to do?"
Part one (the calculation):
Essentially:
IF value ='""', set the column to NULL. Those are two double quotes wrapped in single quotes.
If value = '', persist the column value that is already in the row. That is an empty string, i.e. quote quote.
If value = 'ASDF', set the column value to 'ASDF'
This is a concept used in HL7 whereby messages are sent over and over for a patient and the receiver is expected to "upsert" data. HL7 has no concept of NULL so double quotes are used to say "wipe out what you have".
This inline code demonstrates the behavior of the calculations, just modify p_sendingfacilty and execute.
DO $$
DECLARE
sendingfacility varchar:= 'Duke';
p_sendingfacility varchar:='ASDF';
result varchar:= NULLIF(COALESCE(NULLIF(p_sendingfacility,''),sendingfacility),'""');
BEGIN
RAISE NOTICE '%',result;
END
$$;
Part two (why the WHERE clause).
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code
Likely my unfamiliarly with the fact the INSERT... ON CONFLICT ON CONSTRAINT already knows the row to which apply the update.
-----Original Message-----
From: Paul Coyne
Sent: Friday, March 31, 2017 5:47 PM
To: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL mailing lists <pgsql-bugs@postgresql.org>
Subject: Re: [BUGS] BUG #14609: ON CONSTRAINT (aka UPSERT) code fails when excluded.<columname> used in calculation.
This does not work (all other code the same for the SP but omitted for brevity):
SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
This does work as intended.
SET sendingfacility = NULLIF(COALESCE(NULLIF(p_sendingfacility,''),test.sendingfacility),'""')
It is as if I can’t reference “excluded,sendingfacility” in the calculation.
The is irrespective of the WHERE clause, but I can remove it as you mention.
On 3/31/17, 4:54 PM, "Peter Geoghegan" <pg@bowt.ie> wrote:
On Fri, Mar 31, 2017 at 5:10 PM, <paul.coyne@diridium.com> wrote:
Can excluded.variablename be used in a calculation? It appears to have the
value NULL in the formula.
I'm on a flight, and haven't looked at this properly, but I have a
hard time following the expression in the UPDATE part of your INSERT
within the plpgsql function. What is this supposed to do?
IF(COALESCE(NULLIF(p_sendingfacility,''),excluded.sendingfacility),'""')
WHERE excluded.mrn = p_mrn and excluded.site_code = p_site_code
It's also weird that "WHERE excluded.mrn = p_mrn" is there, since
you're proposing that same value (function argument) for insertion in
the first place (same with site_code).
Are you sure that this isn't just an "IS NULL vs. =" issue?
--
Peter Geoghegan
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs