Issue with filter - Postgres CDC realtime

[MaximeLemolt]

I have a problem when I try to listen to UPDATE changes with Supabase realtime.

Realtime logs error message :

"UnableToSubscribeToPostgres: {:error,\n "Unable to subscribe to changes with given parameters. An exception happened so please check your connect parameters: [event: UPDATE, filter: id=eq.6100895b-7991-4f12-bed5-41708fa06176, schema: public, table: Player]. Exception: ERROR P0001 (raise_exception) invalid column for filter id"}"

My code :

const channel = realtime.channel(`player-updates`);
channel.on(
  "postgres_changes",
  {
    event: "UPDATE",
    schema: "public",
    table: "Player",
    filter: `id=eq.${player.id}`,
  },
  (payload) => {
    console.log(payload);
  }
);

channel.subscribe(async (status) => {
  if (status === "SUBSCRIBED") {
    console.log("Ready to receive updates");
  }
});

When I don't specify a filter, everything works fine, but as soon as I add a filter, it no longer works, regardless of the column.
The column name is correct, everything looks OK, but it doesn't work.
If anyone has encountered this and has a solution, please let me know.
Thanks in advance.

[GaryAustin1]

Don't put your string in quotes at all.

https://supabase.com/docs/guides/realtime/postgres-changes#equal-to-eq

[MaximeLemolt]

@GaryAustin1

Can you do a quick test with just a text value (not a uuid string) to see if it gets past the error? It should not care what the string looks like but....

II've already done this test and the error is still present, I've also tested all the columns in the table and got the same result

If that does not work I would try restarting the instance.
Also sometimes users with 2 instances are going to the wrong instance but tables are similar but not the same. So the UI is not matching the info the call is seeing.

I've already restarted the project on supabase several times. Maybe it caused some inconsistencies.

[MaximeLemolt]

@GaryAustin1
I'll try to create a new project on supabase to see if that solves the problem.
The problem occurred all at once, by which I mean that using the filters was working and when I resumed development of the application this morning it was no longer working.

I ran into a lot of problems with SQL connections, which led me to reset my migrations (I use Prisma for that). Maybe it finally broke everything on the supabase side.

[GaryAustin1]

I notice you seem to be using Prisma (based on your table image).
There are issues with realtime and Prisma, but they are usually caused by grants being wiped out by Prisma. The filter error though would not be what I would expect and you said it worked without the filter and that case I would think would also error.
Troubleshooting in this guide covers that: https://supabase.com/partners/integrations/prisma

[MaximeLemolt]

I solved the problem by creating a new supabase project. The filters work again without having modified the code.
I think the multiple project restarts caused problems.

I have the impression that the problem was perhaps linked to the RLS, as I notice the presence of a padlock icon next to the table name, which was no longer present on the old project despite the deactivation of the RLS.

Old project screenshot (with the error) :

New project screenshot (without the error) :

@GaryAustin1 thanks for your help 👍

[Rubenburdin]

For anyone else hitting this "invalid column for filter" error, few things to check before nuking the project:
Prisma specific: Prisma migrations can wipe grants on tables. Realtime needs specific permissions to read from the replication stream. Run this to restore them:
grant select on your_table to supabase_realtime_admin;

Also make sure your table is in the supabase_realtime publication:
alter publication supabase_realtime add table your_table;

RLS gotcha: Even with RLS "disabled" in the UI, sometimes the policies stick around in a weird state. The padlock icon difference you noticed is telling. You can verify with:
select relname, relrowsecurity from pg_class where relname = 'Player';

If relrowsecurity is true but you expect it off, run alter table "Player" disable row level security;
Filter column must be in replica identity: By default Postgres only includes primary key columns in the WAL for updates. If you're filtering on a non PK column, you need:
alter table "Player" replica identity full;

Otherwise Realtime can't see that column's value in the change event to apply your filter.
The "create new project" fix likely just reset all these to defaults. Worth checking the above if migrating isn't an option.