Postgrest: Partitioned table causes 500 internal server error

[yeedle]

I have a partitioned table (using the approach described in the docs, with an empty "master" table, tables inheriting from the master table, and a trigger to redirect INSERTs to the appropriate child table). I have no problem inserting into child tables directly, but when inserting into the master table, I get a 500 response, with a body that says:

{
    "details": "Row number 0",
    "message": "Row error: unexpected null"
}

My guess is that the INSERT operation fails and that is what causes this error, but I'm not sure. Is this expected behavior? Should I rather use the pre-request hook to redirect INSERTs into the parent to child tables?

[ruslantalpa]

@Yeedle postgrest does nothing more than generate queries and execute them.
turn on query loging on your database and observe the postgrest generated query that fails and paste it here if you can't figure out the problem

[begriffs]

Also if you're not familiar with how to get verbose postgresql logs, we have a section in the docs to help - https://postgrest.com/en/v4.1/admin.html#debugging

[yeedle]

Here are the results from postgresql.log.

The database name is yeedle. The authenticator role I created is auth. The following is the DDL I used to set up the schema and tables:

create schema api;

create api.table master_table (
    id serial primary key,
    info text not null,
    category text not null
);

create table api.child1 (
    constraint enforce_category check(class = 'child1')
) inherits (api.master_table);

create table api.child2 (
    constraint enforce_category check(class = 'child2')
) inherits (api.master_table);

I then created a function and a trigger as follows:

create or replace function master_table_insert_trigger() 
returns trigger as $$
begin
    case new.category
        when 'child1' then insert into api.child1 values (new.*);
        when 'child2' then insert into api.child2 values (new.*);
    end case;
    return null;
end; $$ language plpgsql;

create trigger master_table_insert_trigger 
     before insert on api.master_table 
         for each row execute procedure master_table_insert_trigger();

I should add that adding to the master table via psql works perfectly fine with no problems. Using a POST request with the following body:

{
  "info": "postgrest is great",
  "category": "child1"
}

produces an error.

2017-09-03 13:04:53.135 EDT [30282] auth@yeedle LOG:  statement: set local role 'anon';set local "request.jwt.claim.role" = 'anon';set local "request.header.host" = 'localhost:3000';set local "request.header.connection" = 'keep-alive';set local "request.header.content-length" = '58';set local "request.header.postman-token" = '89c683f4-9415-b7f6-8347-d6dd3fdab7cf';set local "request.header.cache-control" = 'no-cache';set local "request.header.origin" = 'chrome-extension://fhbjgbiflinjbdggehcddcbncdddomop';set local "request.header.user-agent" = 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36';set local "request.header.content-type" = 'application/json';set local "request.header.accept" = '*/*';set local "request.header.accept-encoding" = 'gzip, deflate, br';set local "request.header.accept-language" = 'en-US,en;q=0.8,he;q=0.6';
2017-09-03 13:04:53.136 EDT [30282] auth@yeedle LOG:  duration: 1.112 ms
2017-09-03 13:04:53.138 EDT [30282] auth@yeedle LOG:  duration: 1.080 ms  parse 1: 
          WITH pg_source AS (INSERT INTO "api"."master_table" ("category", "info")SELECT "category", "info" FROM json_populate_recordset(null:: "api"."master_table" , $1) RETURNING  "api"."master_table".*)
          SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, ( WITH s AS (SELECT row_to_json(ss) as r from pg_source as ss  limit 1) SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null')) FROM s, json_each_text(s.r) AS json_data WHERE json_data.key IN ('id')), ''
          FROM (SELECT 1 FROM pg_source) _postgrest_t 
2017-09-03 13:04:53.138 EDT [30282] auth@yeedle LOG:  duration: 0.399 ms  bind 1: 
          WITH pg_source AS (INSERT INTO "api"."master_table" ("category", "info")SELECT "category", "info" FROM json_populate_recordset(null:: "api"."master_table" , $1) RETURNING  "api"."master_table".*)
          SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, ( WITH s AS (SELECT row_to_json(ss) as r from pg_source as ss  limit 1) SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null')) FROM s, json_each_text(s.r) AS json_data WHERE json_data.key IN ('id')), ''
          FROM (SELECT 1 FROM pg_source) _postgrest_t 
2017-09-03 13:04:53.138 EDT [30282] auth@yeedle DETAIL:  parameters: $1 = '[{"category":"child1","info":"postgrest is great"}]'
2017-09-03 13:04:53.138 EDT [30282] auth@yeedle LOG:  execute 1: 
          WITH pg_source AS (INSERT INTO "api"."master_table" ("category", "info")SELECT "category", "info" FROM json_populate_recordset(null:: "api"."master_table" , $1) RETURNING  "api"."master_table".*)
          SELECT '' AS total_result_set, pg_catalog.count(_postgrest_t) AS page_total, ( WITH s AS (SELECT row_to_json(ss) as r from pg_source as ss  limit 1) SELECT array_agg(json_data.key || '=' || coalesce('eq.' || json_data.value, 'is.null')) FROM s, json_each_text(s.r) AS json_data WHERE json_data.key IN ('id')), ''
          FROM (SELECT 1 FROM pg_source) _postgrest_t 
2017-09-03 13:04:53.138 EDT [30282] auth@yeedle DETAIL:  parameters: $1 = '[{"category":"child1","info":"postgrest is great"}]'
2017-09-03 13:04:53.140 EDT [30282] auth@yeedle LOG:  duration: 2.098 ms
2017-09-03 13:04:53.140 EDT [30282] auth@yeedle LOG:  duration: 0.015 ms  parse 2: ABORT
2017-09-03 13:04:53.141 EDT [30282] auth@yeedle LOG:  duration: 0.014 ms  bind 2: ABORT
2017-09-03 13:04:53.141 EDT [30282] auth@yeedle LOG:  execute 2: ABORT
2017-09-03 13:04:53.141 EDT [30282] auth@yeedle LOG:  duration: 0.055 ms

[ruslantalpa]

i think i know what's the problem, your trigger needs to do
return new or something like that

try to do a manual query like
insert .... into master returning * ...
and i bet the result will be a null instead of the row you just inserted.

you need to fix your trigger so that inserting with returning works the same way when you insert in a simple table

(pls confirm that's the problem then close)

[yeedle]

You are right about that. changing return null to return new fixes the issue, but I'm not sure it's a complete solution, since the return value of the function gets inserted into the master table. The idea of partitioning is to keep the master table empty and only insert the value in the appropriate child table, which is why the documentation suggest returning null in the first place.

After looking around a bit, I found that this is a known issue with trigger-based partitioning and that there are some ways to get around it (e.g. here https://stackoverflow.com/a/22933270/4240010).

[ruslantalpa]

try sending the header
Prefer: return=minimal
https://postgrest.com/en/v4.1/api.html#insertions-updates