PG logical replication disconnects - view updates are frozen

[Ten0]

Since updating to Postgres 16, some updates freeze (replication slots in pg_replication_slots are marked as active:false, wal_status:lost).

I was wondering whether this was a PG bug (they made lots of changes to logical replication in PG 16) or a Materialize bug, so I wanted to search in the issue tracker, but apparently all issues are gone now. (Also, the "bug reports" discussions are disabled.)

1. Does this particular bug ring a bell, and what would you expect its status to be?
2. How can we access the list of issues to track whether such an issue has already happened and been fixed? (CONTRIBUTING.md mentions issues but link is broken, and Bug reports discussions categories is empty (instead of having been migrated from existing issues).

Thanks,

[morsapaes]

Thanks for reporting this, @Ten0 — and sorry for the confusion! We're mid-migration and there are some loose ends to tie still while issues aren't publicly accessible. cc @chaas

We're testing against multiple versions of PostgreSQL in CI (including 16.4), but haven't seen the specific error you mentioned before. What minor version are you seeing this in? We'll take a look internally, thanks again for reporting this!

[Ten0]

So the logs I'm getting are a terminating connection due to administrator command, then 3 seconds later materialize retries connecting and can no longer get changes from replication slot, DETAIL: This slot has been invalidated because it exceeded the maximum reserved size.
This started happening when we migrated from pg 15 to pg 16, but since then we have increased our max_slot_wal_keep_size from 512M to 10G and the same error keeps happening, disconnecting after a while running, so it looks like it's not just PG 16 consuming slightly more...
Do you have an idea what would cause this, or if perhaps someone else has encountered the same issue?

(I think the drops are when we re-create everything replication-slot/materialize-related to make it refresh)

[morsapaes]

What version of Materialize are you running?

[Ten0]

v0.26.6 (5ad960e) 😁

[morsapaes]

Ah. Sorry — we no longer support versions under v0.27 as of October 2022 (as stated in the LTS documentation). The PostgreSQL source has been refactored for the new architecture and hardened based on feedback from users running it in production against different PostgreSQL versions (and services), which explains why we haven't seen this issue with newer Materialize versions. If you'd like to keep using the binary version, I'm afraid you'll need to stick to PostgreSQL < v16.

[Ten0]

I'm trying to experiment on whether upgrading would solve this issue.
Trying to connect to a newer version works fine with psql. I'm trying to connect via datagrip following this guide, which used to work in 0.26, but it is now unable to connect (maybe it takes the connection NOTICE message for an error):

Since issues are gone, where can I report this?
Thanks,

EDIT: I can confirm that is indeed the issue: Specifying options: --welcome_message=off in DataGrip allows it to connect:

So maybe only the guide needs a fix, although it would be more practical if upon recognizing DataGrip it would also turn of the welcome message...

=> Is there any way to turn the message off globally, by e.g. adding an env var or a config file in the docker?
I found MZ_SYSTEM_PARAMETER_DEFAULT=welcome_message=off but that doesn't change anything.

[morsapaes]

Ah, that's an interesting finding. I'll tweak the documentation to add the welcome_message tip! You can set the default value of welcome_message to off for the connecting role by running the following command (which will persist across sessions):

ALTER ROLE materialize SET welcome_message = off;

There's an internal tracking issue that proposes turning this off by default at the system level, or allowing to customise the value depending on the application_name, as you suggested. We explicitly turn this off in our dbt adapter, too — so it's definitely open for debate! I added this thread to the issue for consideration.