Ok, I tried to be clever and I wrote code to avoid inserting duplicate data.
The calling function has a try-catch to recover from this, but I am curious
as to why it failed:
INSERT INTO
mdx_lib.acache_mdx_logic_address_validation
(
address,
postal_code,
address_id
)
SELECT
'306 station 22 1 2 st' AS address,
'29482' AS postal_code,
100165016 AS address_id
WHERE
NOT EXISTS
( SELECT
1
FROM
mdx_lib.acache_mdx_logic_address_validation
WHERE
address = '306 station 22 1 2 st'
AND postal_code = '29482'
)
Exec status=PGRES_FATAL_ERROR error=ERROR: duplicate key value violates
unique constraint "uq_acache_mdx_logic_address_validation_idx"
DETAIL: Key (address, postal_code)=(306 station 22 1 2 st, 29482) already
exists.
The client insists that this process is the only one running, so if he's
right no other process could be inserting a row with the same data between
the SELECT . NOT EXISTS and the actual INSERT operation.
This particular code works as expected right now (SELECT returns 0 rows,
therefore no rows INSERTed).
Should this have worked?
Carlo
