A little confused about horizontal filtering and type casting

[benjamin-thomas]

Server: postgrest/7.0.1

Hello,

This is not really a bug, or at least I don't think so. Maybe more a request for more documentation as I'm a little puzzled by the behavior observed.

Following the tutorial, I see that this query:

curl 'localhost:4444/todos?done=eq.false'

Gets translated to this SQL: SELECT "api"."todos".* FROM "api"."todos" WHERE "api"."todos"."done" = 'false'::unknown

db_1         | 2020-11-16 11:09:41.501 UTC [1048] LOG:  execute <unnamed>: 
db_1         |        WITH
db_1         |        pgrst_source AS (SELECT "api"."todos".* FROM "api"."todos"  WHERE  "api"."todos"."done" = 'false'::unknown   )
db_1         |        
db_1         |        SELECT
db_1         |          null::bigint AS total_result_set,
db_1         |          pg_catalog.count(_postgrest_t) AS page_total,
db_1         |          array[]::text[] AS header,
db_1         |          coalesce(json_agg(_postgrest_t), '[]')::character varying AS body,
db_1         |          coalesce(nullif(current_setting('response.headers', true), ''), '[]') AS response_headers
db_1         |        FROM ( SELECT * FROM pgrst_source) _postgrest_t 
db_1         | 2020-11-16 11:09:41.501 UTC [1048] LOG:  execute 1: COMMIT

Initially, I was trying to ensure those queries would be valid:

• curl 'localhost:4444/todos?due_on=lt.2020-11-16'
• curl 'localhost:4444/todos?due_on=lt.now' <-- seems to work
• curl 'localhost:4444/todos?due_on=lt.now()' <-- seems to work too
• curl 'localhost:4444/todos?due_on=gt.today' <-- seems to work??

Etc. However the same ::unknown type casting seems to be applied to any search params, making me unsure about what to expect:

my_psql_prompt=# SELECT 'true'::unknown = true;
 ?column? 
----------
 t
(1 row)

my_psql_prompt=# SELECT now() > 'today'::unknown;
 ?column? 
----------
 t
(1 row)

I didn't find any documentation regarding this: http://postgrest.org/en/v7.0.0/api.html#horizontal-filtering-rows

Could you please clarify this behavior? Thanks.

[steve-chavez]

@benjamin-thomas Hey there,

unknown casting lets pg infer the type based on the context.

In the SELECT 'true'::unknown = true case, the right operand is a boolean so the left operand is converted to the same type. Then true = true is true.

The date inputs like now, today, yesterday are special in pg. They're documented here: https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-TABLE.

So you can do:

select 'tomorrow'::timestamp;
2020-11-17 00:00:00

select 'yesterday'::timestamp;
2020-11-15 00:00:00

curl 'localhost:4444/todos?due_on=lt.now()'

pg parsing can look crazy here, but in fact it ignores certain special chars:

select ')()()()()tomorrow()()()('::timestamp;
2020-11-17 00:00:00 

[benjamin-thomas]

Got you, thanks @steve-chavez.

So all incoming input strings are converted to their final type by pg itself. I wouldn't have guessed about its crazy parsing ;)

This can lead to surprising results though, at least theoretically:

pg_prompt => SELECT '1'::UNKNOWN = 1 AND '1'::UNKNOWN = '1' AS is_surprising;
 is_surprising 
---------------
 t
(1 row)

I gather pgrest uses prepared statements and queries are probably cached?

See change log entry: Prevent database memory cosumption by prepared statements caches - @ruslantalpa

I suppose we should not use those special keywords like now, today, tomorrow, etc. as suggested by the pg doc you pointed to?

Caution
While the input strings now, today, tomorrow, and yesterday are fine to use
in interactive SQL commands, they can have surprising behavior when the
command is saved to be executed later, for example in prepared statements...

Is that correct?

[steve-chavez]

I suppose we should not use those special keywords like now, today, tomorrow, etc. as suggested by the pg doc you pointed to?

@benjamin-thomas Not really, it should be fine to use them through pgrest.

The caution in the pg docs, refers to this case:

prepare nowstm as select 'now'::timestamptz;

execute nowstm;
2020-11-17 17:47:24.720227-05

execute nowstm;
2020-11-17 17:47:24.720227-05

-- will keep giving the same result

In our case, the now is passed as a parameter to the prepared statement we generate. Essentially:

prepare nowstminput as select $1::timestamptz;

execute nowstminput('now');
2020-11-17 17:57:56.983879-05

execute nowstminput('now');
2020-11-17 17:58:09.186483-05

-- value will keep changing

You should avoid using the special date inputs in a function/view definition though. For example:

create or replace view nowview as select 'now'::timestamptz;

That will give the same value for as long as the view lives.

[benjamin-thomas]

Of course, this makes perfect sense now, thanks!

Keep up the great work, I find pgrest very interesting :)

I'll try to contribute a doc update this week-end, so keeping this open for a bit

[steve-chavez]

Btw, the now special input also works for POST/PATCH:

curl -X PATCH -H "Prefer: return=representation" -H "Content-Type: application/json" "localhost:80/actor?actor_id=eq.1" -d @- << JSON
{"last_update": "now"}
JSON

[{"actor_id":1,"first_name":"Penelope","last_name":"Guiness","last_update":"2020-12-02T02:29:32.698689"}]

Wonder if we should document the special date inputs somewhere. This also looks similar to PostgREST/postgrest-docs#273 in that we take advantage of pg literal representations.

[benjamin-thomas]

@steve-chavez I haven't forgotten you. I was planning on proposing a doc update before closing.

I think this behavior should definitely be described, and I also think some kind of "best practices" should be mentioned (although slightly out of scope).

By that I mean that using those textual representation has some consequences such as dealing with time zone issues, etc.

In short, I concluded that they're ok to use, but only if used with TIMESTAMP WITHOUT TIME ZONE and deciding not to handle time zones, otherwise things get tricky and introducing bugs in the front end is easy and not immediately obvious.

In other words, now represents the server's now time, and probably not what the client wants if it's in a different time zone than the database.

[wolfgangwalther]

By that I mean that using those textual representation has some consequences such as dealing with time zone issues, etc.

In short, I concluded that they're ok to use, but only if used with TIMESTAMP WITHOUT TIME ZONE and deciding not to handle time zones, otherwise things get tricky and introducing bugs in the front end is easy and not immediately obvious.

In other words, now represents the server's now time, and probably not what the client wants if it's in a different time zone than the database.

I'd really like to see the longer explanation for this :).

My gut feeling is that the best way would be to always use timezone, otherwise you might get unexpected results. Do you have an example showing the problem when doing so?

[benjamin-thomas]

@wolfgangwalther ok I'll code a little and simple example project showing the different gotchas I'm hinting at.

I could be missing something so it'll be interesting to find that out ;)