How to perform GET requests against a view that requires mutation?

[dalthon]

Environment

• PostgreSQL version: postgres:15.2-alpine
• PostgREST version: postgrest/postgrest:v11.0.0
• Operating system: Ubuntu

Description of issue

TLDR: Is there a way to perform a GET request against a view with write transaction instead of read?

I have a view that aims to lazy evaluate some heavy computation only when needed and using a persisted (cached) data when previously available.

Below I just wrote a simplified version of my actual problem having a function lazily_heavy_procedure_and_cache that returns a setof computed_data and my view lazily_computed_data that returns rows of the exactly same type.

CREATE OR REPLACE FUNCTION lazily_heavy_procedure_and_cache(
  _user_id  varchar,
  _min_date date,
  _max_date date
)
RETURNS SETOF data.computed_data
LANGUAGE sql AS $$
  WITH previously_cached AS (
    SELECT cached.*
    FROM data.computed_data AS cached
    WHERE user_id = _user_id
  ),

  recently_cached AS (
    INSERT INTO data.computed_data (
        SELECT
          raw.user_id      AS user_id,
          created_at::date AS accrual_date,
          SUM(raw.amount)  AS total_amount
        FROM data.raw_data AS raw
        WHERE
          created_at::date > (SELECT MAX(c.accrual_date) FROM previously_cached AS c) AND
          created_at::date < _max_date
      )
    RETURNING *
  )

  SELECT * FROM previously_cached
  UNION
  SELECT * FROM recently_cached
$$;

CREATE OR REPLACE VIEW api.lazily_computed_data AS
  SELECT balance.*
  FROM (
    SELECT
      user_id,
      MIN(created_at::date  ) AS min_created_at,
      MAX(created_at::date-1) AS max_created_at
    FROM data.raw_data
    GROUP BY 1
  ) AS raw
  LEFT JOIN LATERAL (
    SELECT cached.*
    FROM lazily_heavy_procedure_and_cache(raw.user_id, min_created_at, max_created_at) AS balance
  ) AS cached ON TRUE;

(Obs.: I haven't run this, but only my version, I am not 100% if previous code works, but you will get the idea...)

The issue is that I can't perform GET requests against this view, receiving this error:

status: 405 (Error: Method Not Allowed)
content-type: application/json; charset=utf-8
date: Wed,19 Apr 2023 07:12:43 GMT
server: postgrest/11.0.0
transfer-encoding: chunked

{
  "code": "25006",
  "details": null,
  "hint": null,
  "message": "cannot execute INSERT in a read-only transaction"
}

It seems that because it is a GET request, it runs inside a read transaction and PostgREST can't recognize that I use a function that is neither immutable or stable, so it can perform mutation and this is actually expected.

There is a way to force this transaction to behave differently allowing mutation?

I believe that this mutation don't hurts HTTP semantics, because even though internally I am performing a mutation, it is still a lazy mutation that otherwise would be performed after a trigger or something else.

In the client perspective, it is only an implementation detail that this is lazy evaluated, this should be transparent to him and is done only due to performance, so I guess other people may have similar issues depending on this use or do you really think that all GET performs no mutation, even if is a cache of data already present being computed?

[steve-chavez]

The issue is that I can't perform GET requests against this view, receiving this error:

Why not just call the procedure directly with POST? Instead of a view, you can also have a wrapper function.

---

It seems that because it is a GET request, it runs inside a read transaction

Yes, documented here: https://postgrest.org/en/stable/api.html#immutable-and-stable-functions. Maybe it needs to be more visible.

We want to preserve GET semantics by enforcing read-only transactions. I don't think we'll lift this restriction, otherwise we wouldn't be conforming to HTTP standards.

https://www.w3.org/Protocols/rfc2616/rfc2616-sec9.html

the convention has been established that the GET and HEAD methods SHOULD NOT have the significance of taking an action other than retrieval. These methods ought to be considered "safe".

[dalthon]

Calling the procedure as a RPC call would not be the same because for that I would need to implement all possible filters on top of it.

The thing I love most on PostgREST is exactly that possibility that provides a very robust, consistent and standartized REST interface for relations, so I would love to not lose that.

If there was a way to mark views based on procedures with mutation on a write transaction (or at least have a conf that would make queries against views to be always in write transactions) would fit that use case.

I understand that having a read transaction where only reads happen is way better, but finding out that with instrospection alone would miss cases like that.

[steve-chavez]

Calling the procedure as a RPC call would not be the same because for that I would need to implement all possible filters on top of it.

All filters are available for RPC, see https://postgrest.org/en/stable/api.html#function-filters. You can even use embedding on them https://postgrest.org/en/stable/api.html#embedding-on-stored-procedures

[wolfgangwalther]

I was in a similar situation with an RPC for a very complex query that I was calling via GET at first. I then added some temporary tables inside the function because that improved performance. Because of the read only transaction, I had to change my client-side interface to use POST - which actually involved quite a bit of work.

I think there are valid use-cases such as these, where a GET / HEAD request would be better called in a writable transaction.

That being said.. I'm still not sure whether we should allow do it. I fully agree with this:

We want to preserve GET semantics by enforcing read-only transactions.

While a config option or a marker on functions/views could help.. it's also dangerous because it would allow people to break those GET semantics. And that's something that we don't want to allow...

[steve-chavez]

I think there are valid use-cases such as these, where a GET / HEAD request would be better called in a writable transaction.

It would be simple to come up with an interface to do this. For example, by setting default_transaction_read_only in a similar way to https://postgrest.org/en/stable/references/transactions.html#isolation-level.

But then once we do this we wouldn't be able to reliably offer read replica support (#2429), since any GET can potentially mutate the db. I think in most cases the caching done for GET is to increase perf, read replicas would also serve the same purpose (maybe with a simpler setup).

[wolfgangwalther]

For example, by setting default_transaction_read_only in a similar way to https://postgrest.org/en/stable/references/transactions.html#isolation-level.

👍

[steve-chavez]

I guess the read replica logic could be accommodated too. Simply these GETs that use default_transaction_read_only=false won't be routed to the replicas. It's a static config so we know in advance.