Postgrest: help: upsert not working

[swuecho]

take the code from another issues.

create table "UnitTest"(
  "idUnitTest" integer primary key,
  "nameUnitTest" text
);
insert into "UnitTest" values (1, 'unit test 1'), (2, 'unit test 2');

curl "localhost:3000/UnitTest" -H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates" \
-H "Prefer: return=representation" \
-d @- << EOF
[
  {
  "idUnitTest": 1,
  "nameUnitTest": "name of unittest 1"
  },
  {
  "idUnitTest": 2,
  "nameUnitTest": "name of unittest 2"
  }
]
EOF

{"hint":"Perhaps you meant to reference the column \"UnitTest.idUnitTest\" or the column \"*SELECT*.idUnitTest\".","details":null,"code":"42703","message":"column \"idunittest\" does not exist"}

_Originally posted by @steve-chavez in https://github.com/PostgREST/postgrest/issues/1113#issuecomment-389564543_

when run in my mac:

➜  app psql postgresql://admin@localhost:5432/admin
psql (11.0)
Type "help" for help.

admin=> create table "UnitTest"(
admin(>   "idUnitTest" integer primary key,
admin(>   "nameUnitTest" text
admin(> );
CREATE TABLE
admin=> insert into "UnitTest" values (1, 'unit test 1'), (2, 'unit test 2');
INSERT 0 2
admin=> \q
➜  app curl "localhost:3000/UnitTest" -H "Content-Type: application/json" \
-H "Prefer: resolution=merge-duplicates" \
-H "Prefer: return=representation" \
-d @- << EOF
[
  {
  "idUnitTest": 1,
  "nameUnitTest": "name of unittest 1"
  },
  {
  "idUnitTest": 2,
  "nameUnitTest": "name of unittest 2"
  }
]
EOF

{"hint":null,"details":"Key (\"idUnitTest\")=(1) already exists.","code":"23505","message":"duplicate key value violates unique constraint \"UnitTest_pkey\""}%

ENV: mac, pg 11, PostgREST 5.2.0 (1e732ac)

[steve-chavez]

@swuecho After creating the table you need to reload postgrest schema cache. Do a:

killall -SIGUSR1 postgrest     

See more details in http://postgrest.org/en/v5.2/admin.html#schema-reloading.

[swuecho]

Thanks! works after reloading. I also did a minor change to the docs to avoid lazy developer like me asking the question repeatedly.

I am still a bit confused. after create the table, without refresh schema. why insert works, but not upsert? The table is simple enough. I have a hard time to find the connection between upsert and the explanation of schema reloading.

[steve-chavez]

@swuecho The request above, gets roughtly translated to:

INSERT INTO  "test"."UnitTest" ("idUnitTest", "nameUnitTest") 
SELECT "idUnitTest", "nameUnitTest" FROM json_populate_recordset (null:: "test"."UnitTest", 
'
[  
  {  "idUnitTest": 1,  "nameUnitTest": "name of unittest 1"  },  
  {  "idUnitTest": 2,  "nameUnitTest": "name of unittest 2"  }
]
'
) _  
ON CONFLICT("idUnitTest") 
DO UPDATE SET "idUnitTest" = EXCLUDED."idUnitTest", "nameUnitTest" = EXCLUDED."nameUnitTest" RETURNING "test"."UnitTest".*;

The ON CONFLICT clause is what makes possible to do the UPSERT. For this we need the PK‐ON CONFLICT("idUnitTest")‐and to get that we have to query the schema cache.

Simple INSERTS don't need to query the schema cache, that's why they work without reloading PostgREST.

[steve-chavez]

Closing, since https://github.com/PostgREST/postgrest-docs/pull/206 got merged.

[mpasquini]

Hi.
using postgres:12 on debian-buster

CREATE TABLE up_text ( 
name text NOT NULL,
i int);
ALTER TABLE up_text ADD CONSTRAINT up_text_pkey  PRIMARY KEY (name);

1st POST:

POST http://myhost:3000/up_text HTTP/1.1
Authorization: Bearer ####
Content-Type: application/json
Prefer: resolution=merge-duplicates

[
{ "name": "alpha", "i": 1} ,
{ "name": "beta", "i": 1}
]

#

Response: HTTP/1.1 201 Created

2nd POST:

POST http://myhost:3000/up_text HTTP/1.1
Authorization: Bearer ####
Content-Type: application/json
Prefer: resolution=merge-duplicates

[
{ "name": "alpha", "i": 2} ,
{ "name": "beta", "i": 2}
]

#

Response: HTTP/1.1 409 Conflict
{
"hint": null,
"details": "Key (name)=(alpha) already exists.",
"code": "23505",
"message": "duplicate key value violates unique constraint \"up_text_pkey\""
}

>> insert into up_text(name, i) select 'alpha', 3
ON CONFLICT(name) DO UPDATE SET i = EXCLUDED.i
RETURNING up_text.*;
+-------+---+
| name  | i |
+-------+---+
| alpha | 3 |
+-------+---+
INSERT 0 1
>> insert into up_text(name, i) select 'beta', 3
ON CONFLICT(name) DO UPDATE SET i = EXCLUDED.i
RETURNING up_text.*;
+-------+---+
| name  | i |
+-------+---+
| beta | 3 |
+-------+---+
INSERT 0 1
>> table up_text;
+-------+---+
| name  | i |
+-------+---+
| beta  | 3 |
| alpha | 3 |
+-------+---+
SELECT 2

test also using as pk:

• int serial not null
• int not null

I cannot explain this.

[steve-chavez]

@mpasquini Did you reload postgrest after creating the table?

See http://postgrest.org/en/v6.0/admin.html#schema-reloading

[mpasquini]

I was convinced YES...,
sorry I've completely forgot to mention reloading.

1. is a docker, so.. but even while not rm/run :
2. used docker restart
   now:
3. sent: killall -s SIGUSR1 postgrest -> test tables works, production not

Tested again for POST/PATCH, with or without "?columns=name" ... no outcome

Unique difference is hash partition on primary key and presence of bytea datatype. like:
TABLE production (name text PK, data bytea NOT NULL) PARTITION BY HASH (name) ;
And a loop:
CREATE TABLE %s PARTITION OF production FOR VALUES WITH (MODULUS %s, REMAINDER %s);

Could partitioning affect postgRest's upsert ? -> this case I would like to open a specific issue.
( Also tested, sql ON CONFLICT and it works on partition tables. )

PostgREST 6.0.2 (713b214)
docker image: postgrest/postgrest

Thanks a lot.

[steve-chavez]

@mpasquini Could you see the sql query PostgREST generates on postgresql logs? More about that on http://postgrest.org/en/v6.0/admin.html#database-logs.

With that we could confirm if the ON CONFLICT clause is being added.

[steve-chavez]

Just tried a partitioned table upsert and it worked:

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date primary key,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

curl -H "Prefer: resolution=merge-duplicates" -H "Content-Type:application/json" \
-d '{"city_id": 1, "logdate": "2006-02-01", "peaktemp": 3, "unitsales": 5}' \
localhost:3000/measurement_y2006m02
## 201 Created

curl -H "Prefer: resolution=merge-duplicates" -H "Content-Type:application/json" \
 -d '{"city_id": 2, "logdate": "2006-02-01", "peaktemp": 4, "unitsales": 6}' \
localhost:3000/measurement_y2006m02
## 201 Created

Could you come up with a sample DDL of your partitioned table and share it here?

[steve-chavez]

Actually, I've tried targeting the partitioned table and it does not work.

curl -H "Prefer: resolution=merge-duplicates" -H "Content-Type:application/json" \
-d '{"city_id": 1, "logdate": "2006-02-01", "peaktemp": 3, "unitsales": 5}' \
localhost:3000/measurement
## 409 Conflict

@mpasquini You could open a more specific issue for this.

[steve-chavez]

Actually the prior snippet works with the on_conflict query parameter introduced on https://github.com/PostgREST/postgrest/pull/1432. Like:

curl -H "Prefer: resolution=merge-duplicates" -H "Content-Type:application/json" \
-d '{"city_id": 1, "logdate": "2006-02-01", "peaktemp": 3, "unitsales": 5}' \
localhost:3000/measurement?on_conflict=logdate
## 409 Conflict