Thread: BUG #16768: PostgreSQL 12.5 - INSERT ON CONFLICT succeeds on data INSERT but does nothing on UPDATE

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"

On Tue, Dec 8, 2020 at 5:02 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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.

I think that you need to UPDATE using the excluded.* pseudo table. For
example, "SET my_col = excluded.my_col". As things stand you're using
the target table itself. Technically the row is being updated, but
with the wrong values (the existing values rather than your intended
new successor values).

-- 
Peter Geoghegan

On Tue, Dec 8, 2020 at 6:25 PM QA Collective <theqacollective@gmail.com> wrote:
> Thanks for your reply Peter.  If this is the case, what would be the reason for this functionality having 'changed'
overthe past week or so?
 

I find it hard to believe that this is working as documented now, but
for some unknown reason used to work some other way (which is contrary
to documented behavior/broken, and yet something that you came to rely
on).

Don't you think it's more likely that you're simply mistaken in some way?

-- 
Peter Geoghegan