Automatic reload of schema cache on cache miss - add event trigger to tutorials

[wolfgangwalther]

Even though we give our best to provide different ways to reload the schema and document it in a lot of places, it still is troubling for a lot of users, as we can tell from our issue tracker and github discussions - this topic comes up again and again.

What if we did the following:
When an endpoint (table/view or function) is requested and we can't find it in the schema cache, instead of returning an error immediately we will first run a very small query on the pg catalogs to see whether this endpoint does now exist. If it does not exist, we return the same error we do now - but probably without the schema cache hint.

However, if the table/view/function exists, we run a full schema cache reload - because we know the cache is stale by now - and then run the original request.

This will make that one request slow, because it needs to wait for the schema cache reload - but all other requests should be just as fast. It might make requests a tiny bit slower that run on an endpoint that doesn't exist - but those shouldn't be the norm anyway.

Not sure if we can detect everything that can make the schema cache stale, especially with function overloading, but for the simple cases this could provide a much better experience for beginners / first-time users.

[steve-chavez]

Hm, it doesn't look like the right design, seems it could fail in many ways(perf and usage).

much better experience for beginners / first-time users.

If this is the end goal, I think we could provide an option on the CLI to create the event trigger on the user database. Something like:

postgrest --quickstart
.. not recommended for production
.. create event triggers (Y/n)
.. create roles (Y/n)
.. ....

The quickstart could even create a database for quick exploration, I've used a similar feature on cockroachdb before and that cockroach demo command is so nice for exploration.

[wolfgangwalther]

much better experience for beginners / first-time users.

If this is the end goal,

Yes, this is the main motivation here.

I think we could provide an option on the CLI to create the event trigger on the user database. Something like:

postgrest --quickstart
.. not recommended for production
.. create event triggers (Y/n)
.. create roles (Y/n)
.. ....

The quickstart could even create a database for quick exploration,

Hm, we'd need superuser privileges to create the event trigger. I don't think we should encourage to use a superuser account to connect postgrest to pg, not even initially.

[steve-chavez]

I don't think we should encourage to use a superuser account to connect postgrest to pg, not even initially.

Yeah, I don't like that as well.

The thing is, I know for a fact(seen it on many production instances) that the event trigger works flawlessly, users don't even notice the schema cache(unless an unrelated failure leaks it in the error message). Seems wrong to have another solution when we have one that works so well.

much better experience for beginners / first-time users
Yes, this is the main motivation here.

I think there are other things besides the schema cache that newcomers need for a much better experience. Wouldn't it be better to have an additional tool for this? I'm thinking in a Nix-based tool like ihp(might also answer #453 via NixOps). Likely lots of work but feels like the right direction.

[wolfgangwalther]

The thing is, I know for a fact(seen it on many production instances) that the event trigger works flawlessly, users don't even notice the schema cache(unless an unrelated failure leaks it in the error message). Seems wrong to have another solution when we have one that works so well.

Absolutely, I fully agree with that. Maybe a first step could be to make it the verrrry first thing in all our tutorials to install the event trigger. We can say something like "this event trigger allows postgrest to react to changes of the database schema". They will do it - and then forget about it. Once they have more experience with postgrest and got their first queries working, they know how to set up a schema, how to make requests etc. - when they move to a production instance where they can not use an event trigger, because they're missing superuser, they can still learn about other ways to notify postgrest of updates.

The whole schema cache thing is really mostly a problem for beginners, because nobody (including the user and us) knows whether they created the database objects in the right place, are making the right query - and whether they reloaded the schema cache. If we can take that thing out for the first few steps, everything else should be a lot smoother.

---

I think there are other things besides the schema cache that newcomers need for a much better experience. Wouldn't it be better to have an additional tool for this? I'm thinking in a Nix-based tool like ihp(might also answer PostgREST/postgrest-docs#453 via NixOps). Likely lots of work but feels like the right direction.

I don't know ihp. I'm doing Vue.js in the frontend, so I guess we're looking at something like vue-cli here? So a CLI tool to quickly bootstrap a full working setup of PostgREST, PostgreSQL etc. for local development.

[steve-chavez]

Maybe a first step could be to make it the verrrry first thing in all our tutorials to install the event trigger.

Oh, we should have been doing that since NOTIFY was introduced. Fully agree!

So a CLI tool to quickly bootstrap a full working setup of PostgREST, PostgreSQL etc. for local development.

Yes, something like that. NixOps could take it to production(IIRC it had a way to deploy RDS instances) as well, I think.

[steve-chavez]

Moving this one to postgrest-docs since we agreed that we just need to solve it with better tutorials.