Schema reloading via notice not working(Windows)

[ajsmith-savills]

Environment

• PostgreSQL version: V12.10
• PostgREST version: 10.0.0
• Operating system: Windows

Description of issue

When I make schema changes and execute a notice to pgrst I expect the schema to reload an the new function to work. However it remains not working until I stop and restart PostgREST.

For any new function added to my api schema and after a notice is raised I still get the below error

"code": "PGRST202",
"details": null,
"hint": "If a new function was created in the database with this name and parameters, try reloading the schema cache.",
"message": "Could not find the api.test_funct2(srid, x1, x2, y1, y2) function or the api.test_funct2 function with a single unnamed json or jsonb parameter in the schema cache"

[ajsmith-savills]

I should also add I copied the automatic schema refresh functions from the documentation and these also did not work to resolve the issue.

[wolfgangwalther]

When you do the schema reload via notify - can you see the "schema reloaded" message on the postgrest cli output?

[ajsmith-savills]

No, I do not get that notification. I also do not see any updates to the CLI when I successfully request data from the API

[wolfgangwalther]

What does postgrest --dump-config tell you? (make sure to remove any secrets, if in there)

[ajsmith-savills]

Please see below

C:\Program Files\PostgREST>postgrest --dump-config postgrest.conf
db-anon-role = "api_anon"
db-channel = "pgrst"
db-channel-enabled = true
db-extra-search-path = "public"
db-max-rows = 50
db-plan-enabled = false
db-pool = 10
db-pool-timeout = 3600
db-pre-request = ""
db-prepared-statements = true
db-root-spec = ""
db-schemas = "api"
db-config = true
db-tx-end = "commit"
db-uri = "postgres://api_authenticator:************@*********:5432/gis"
db-use-legacy-gucs = true
jwt-aud = ""
jwt-role-claim-key = ".\"role\""
jwt-secret = "****************"
jwt-secret-is-base64 = false
log-level = "error"
openapi-mode = "follow-privileges"
openapi-security-active = true
openapi-server-proxy-uri = "http://***********/api"
raw-media-types = ""
server-host = "!4"
server-port = 3000
server-unix-socket = ""
server-unix-socket-mode = "660"
admin-server-port = ""

[ajsmith-savills]

Does anyone have any ideas?

[wolfgangwalther]

How exactly do you NOTIFY the reload? Which command do you execute?

[ajsmith-savills]

I have tried NOTIFY pgrst, 'reload schema' as described in the documentation, as well as building the watch functions specified there, all without success.

[ajsmith-savills]

Does anyone have any further ideas on this issue? Also should I raise the lack of logging as a separate issue?

[steve-chavez]

The problem is that it's hard for us maintainers to reproduce since we don't use Windows.

Pinging @majkinetor which has helped us before with packaging for Windows.

[majkinetor]

@steve-chavez, I am on vacation, but I could look into it in April. I am not using currently postgrest but I would like to help it spread.

It would be good to assign me this ticket so not to forget.

[benoit-penelle]

I have the same problem (also on Windows). Is there any progress on this issue ?

[diogob]

@benoit-penelle it seems the issue is in hasql-notifications. I'm investigating and tracking on diogob/hasql-notifications#17

[laurenceisla]

The issue was fixed in #2909.
@ajsmith-savills, @benoit-penelle

[bepenelle]

It worked correctly with releases 12.0.2 and 12.0.3. Since the release 12.2.0 it doesn't work anymore.

[steve-chavez]

@bepenelle Is there any error message?

[bepenelle]

@bepenelle Is there any error message?

No error message is displayed. Also, no info message is displayed indicating that the schema has been reloaded.

Here is my config :

db-uri = "postgres://authenticator:***************@127.0.0.1:5432/prbd-tuto"
db-schemas = "public"
db-anon-role = "anon"
server-port = 3000
jwt-secret = "**************************************************"
listen = true
db-channel = "pgrst"
db-channel-enabled = true
log-level = "info"

When I execute notify pgrst, 'reload schema' with the release 12.0.2 or 12.0.3, it works as expected: the console logs the following message :

25/Jan/2025:09:31:03 +0100: Attempting to connect to the database...
25/Jan/2025:09:31:03 +0100: Connection successful
25/Jan/2025:09:31:03 +0100: Config reloaded
25/Jan/2025:09:31:03 +0100: Schema cache loaded

Since release 12.2.0, no message appears when I run the notify command and the schema is not reloaded in cache. Here is the message I receive when I request http://localhost:3000/rpc/test just after having added a new function test and executed the notify :

{
    "code": "PGRST202",
    "details": "Searched for the function public.test without parameters, but no matches were found in the schema cache.",
    "hint": null,
    "message": "Could not find the function public.test without parameters in the schema cache"
}

I've tested versions 12.2.0, 12.2.3, 12.2.4 and 12.2.5 on Windows 11 and the problem is the same.

[wolfgangwalther]

@diogob in diogob/hasql-notifications@243f01b you added a conditional for windows, which fixed this problem.

But in diogob/hasql-notifications@769ca47 it seems this conditional was lost and the old behavior restored.

Was this by accident?

[diogob]

@diogob in diogob/hasql-notifications@243f01b you added a conditional for windows, which fixed this problem.

But in diogob/hasql-notifications@769ca47 it seems this conditional was lost and the old behavior restored.

Was this by accident?

It does look odd, I'm checking it now.

[diogob]

@wolfgangwalther my mistake. That conditional was removed by accident. I have reintroduced it and published a version 0.2.3.2. I'm still waiting the hackage update to test the PostgREST build. In case it breaks some dependency I'll publish a fix for the 0.2.2.0.

[wolfgangwalther]

Cool, thanks for the quick fix.

I'm pretty sure we can't move to 0.2.3.2, yet, because it will conflict with hasql dependencies. I tried in https://github.com/PostgREST/postgrest/pull/3866/files#diff-2b83012b070c2bc79ae40e1e4bc9a229f65e046225e8276281b96f37ee80a3ea.

So we probably need a 0.2.2.1.

[diogob]

@wolfgangwalther just published 0.2.2.1 that will probably compile seamlessly.

[wolfgangwalther]

just published 0.2.2.1 that will probably compile seamlessly.

This seems to have odd bounds for hasql-pool.

0.2.2.0 had hasql-pool (>=0.4 && <1.1), but 0.2.2.1 has hasql-pool (>=0.5 && <0.6).

[diogob]

just published 0.2.2.1 that will probably compile seamlessly.

This seems to have odd bounds for hasql-pool.

0.2.2.0 had hasql-pool (>=0.4 && <1.1), but 0.2.2.1 has hasql-pool (>=0.5 && <0.6).

unfortunately the old bounds are producing a compilation error.
I have investigated and found the issue to be some problem in hasql-pool 0.4.3.0.

You will find a new version 0.2.2.2 with bounds closer to the original hasql-pool (>=0.4.3.1 && <1.1)