No data shown in web UI?

[laurentS]

Looking at the web UI, it looks at though all my data has disappeared from my db. But I can still see it through psql.

I dropped and recreated the tables, then uploaded about 400k rows total to 3 tables (using sqlalchemy), and saw all of this appear on the web UI. The data uploaded completed at 23:48:47. A bit later, I started seeing a lot of "Error" messages in the UI, mostly on /editor/tables but I think on other pages as well. This started sometime between approx 00:00 and 02:00.
The only reference I noticed was this started after the daily backup time which seems to have happened just after midnight (00:00:49 UTC) (my data upload happened before the backup).

At this point, the UI shows the list of tables, and the table schema, but no data. Under /database/tables I see the list of tables, what seems like the correct size in MB, but a row count of 0 on all tables.

Again, through psql, I'm able to see the data, so it is there, supabase just seems to only know some bits of it, like the schema and size on disk.
It seems to me as though there is a bug in supabase, seeing that I could initially see my data, and I suspect the backup time was probably when it started.
Alternately, is there some sql instructions that need to be issued to postgresql when creating a table via a separate client? (I don't think I did anything like that before, and I could see my data)

[laurentS]

As a follow up, I just had a confusing sequence of events just now:

• 16:46 UTC, /editor/tables said something like "congratulations, this is a fresh new project, start by creating some tables". No tables listed in the UI
• a couple minutes later, everything was back, tables, schema, data (in the UI)
• when I tried to open another table to look at it in the UI, data disappeared again and I got a red "Error: Database client error. Retrying the connection." notification.
• In parallel (no exact timing available, sorry), I was running this from psql (my table has 220k rows, not a monster):

postgres=# select * from mytable where "NAME" like 'ABCD%';
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

It looks like some part of the server keeps crashing and restarting?

[laurentS]

This might actually relate to #623

[inian]

@laurentS can you pass me the project ref for this? I will take a look.

[laurentS]

Sure. This is the project xapaihoqnlqhiypqwbaf. After my messages above, I noticed that my own code wasn't getting any updates from subscriptions. So I followed some of the tips in #428 but it didn't help. All I saw was that the pg_publication_tables was empty. supabase_realtime replication existed, but its puballtables was false. So I dropped it and recreated it with FOR ALL TABLES. But no luck. I don't know enough postgresql to be able to figure it out, so appreciate your help.

I suspect it has to do with the fact that I dropped and recreated the tables via pg_dump / psql, and something must be missing in the setup that you normally do for subscriptions, but I don't know what that is.

[laurentS]

This seems to be fixed now, I'm going to guess through supabase/realtime#106
Marking this as solved. Thanks for figuring it out!

[inian]

Yup, we rolled out that fix to everyone yesterday. Glad it is working again now!