The following bug has been logged on the website:
Bug reference: 16768
Logged by: Andrew H
Email address: theqacollective@gmail.com
PostgreSQL version: 12.5
Operating system: Linux Mint 20 (Ulyana)
Description:
Hello,
I have had code running for 3 months or so which uses the INSERT ... ON
CONFLICT ... UPDATE feature. In the last week or so, a user has noticed
that data is no longer being updated as expected. At first, I suspected an
error in my code, or perhaps in the ORM I am using to access PostgreSQL
(PeeWee: http://docs.peewee-orm.com/en/latest/). However, after creating a
new database, new schema and test table and running INSERT SQL on it, the
same problem persists. That is, if the database needs to run an INSERT -
that succeeds to insert a row. However, if the row already exists, the
UPDATE doesn't happen - and shows no sign of failing. The query returns
success. There are no errors in postgresql-12-main.log. It is a very
silent failure, unless I'm missing something. I suspect it may have been
the upgrade to PostgreSQL 12.5, but I've not yet had the time or guts to
downgrade to test this.
I am running: "PostgreSQL 12.5 (Ubuntu 12.5-0ubuntu0.20.04.1) on
x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0,
64-bit"
According to dpkg logs, these were installed 1/12/2020:
dpkg.log.1:2020-12-01 18:18:07 upgrade postgresql-client-12:amd64
12.4-0ubuntu0.20.04.1 12.5-0ubuntu0.20.04.1
dpkg.log.1:2020-12-01 18:18:07 upgrade postgresql-12:amd64
12.4-0ubuntu0.20.04.1 12.5-0ubuntu0.20.04.1
I also have timescaledb-1.7.4 extension installed, but I have tested this
bug on an entirely new database without that extension enabled.
I have checked /var/log/postgresql/postgresql-12-main.log and this doesn't
contain anything suspicious or anything that directly relates to the SQL
below.
The SQL below recreates my issue. That is, the LAST insert statement DOES
NOT update the accountinfo_test table value for 'balance' to 200.0 ... it
remains at the previous value of 100.0.
CREATE DATABASE test
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_AU.UTF-8'
LC_CTYPE = 'en_AU.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;
----
CREATE SCHEMA test_schema
AUTHORIZATION postgres;
----
--DROP TABLE test_schema.accountinfo_test;
CREATE TABLE test_schema.accountinfo_test
(
login integer NOT NULL,
balance real NOT NULL,
CONSTRAINT accountinfo_test_pkey PRIMARY KEY (login)
)
TABLESPACE pg_default;
ALTER TABLE test_schema.accountinfo_test
OWNER to auto_trader;
GRANT ALL ON TABLE test_schema.accountinfo_test TO auto_trader;
GRANT SELECT ON TABLE test_schema.accountinfo_test TO viewer;
----
INSERT INTO "test_schema"."accountinfo_test" ("login", "balance") VALUES
(2000, 100.0) ON CONFLICT ("login") DO UPDATE SET "login" =
"accountinfo_test"."login", "balance" = "accountinfo_test"."balance"
RETURNING "accountinfo_test"."login"
----
INSERT INTO "test_schema"."accountinfo_test" ("login", "balance") VALUES
(2000, 200.0) ON CONFLICT ("login") DO UPDATE SET "login" =
"accountinfo_test"."login", "balance" = "accountinfo_test"."balance"
RETURNING "accountinfo_test"."login"
