Subscription with Filter receives Deleted Rows Outside of Provided Filter

[acupofjose]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

When subscribed to postgres_changes and specifying a row level filter, websocket inspector shows that rows outside of the provided filter are being sent to the client.

To Reproduce

Given the following table:

create table
  public.test_realtime (
    id bigint generated by default as identity not null,
    data text null,
    created_at timestamp with time zone null default now(),
    constraint test_realtime_pkey primary key (id)
  ) tablespace pg_default;

INSERT into test_realtime (data) VALUES ('Test 1');
INSERT into test_realtime (data) VALUES ('Test 2');
INSERT into test_realtime (data) VALUES ('Test 3');
INSERT into test_realtime (data) VALUES ('Test 4');
INSERT into test_realtime (data) VALUES ('Test 5');
INSERT into test_realtime (data) VALUES ('Test 6');
INSERT into test_realtime (data) VALUES ('Test 7');
INSERT into test_realtime (data) VALUES ('Test 8');
INSERT into test_realtime (data) VALUES ('Test 9');

And the following code:

const channel = supabase.channel("test_realtime")
channel.on("postgres_changes", { event: "DELETE", schema: 'public', table: 'test_realtime', filter: 'id=eq.6' }, (payload: any) => {
  console.log(payload)
});
await channel.subscribe((status) => console.log(status))

In Supabase Inspector:

• [x] Delete Row with ID of 5
• [x] Delete Row with ID of 6

Console Behavior:

SUBSCRIBED
{
    "schema": "public",
    "table": "test_realtime",
    "commit_timestamp": "2023-04-25T12:21:54.479Z",
    "eventType": "DELETE",
    "new": {},
    "old": {
        "id": 5
    },
    "errors": null
}
{
    "schema": "public",
    "table": "test_realtime",
    "commit_timestamp": "2023-04-25T12:22:05.160Z",
    "eventType": "DELETE",
    "new": {},
    "old": {
        "id": 6
    },
    "errors": null
}

Websocket Behavior:

{"topic":"realtime:test_realtime","event":"phx_join","payload":{"config":{"broadcast":{"ack":false,"self":false},"presence":{"key":""},"postgres_changes":[{"event":"delete","schema":"public","table":"test_realtime","filter":"id=eq.6"}]},"access_token":"-----------"},"ref":"1","join_ref":"1"}
{"event":"phx_reply","payload":{"response":{"postgres_changes":[{"id":115269023,"event":"delete","filter":"id=eq.6","schema":"public","table":"test_realtime"}]},"status":"ok"},"ref":"1","topic":"realtime:test_realtime"}
{"topic":"realtime:test_realtime","event":"access_token","payload":{"access_token":"-------"},"ref":"2","join_ref":"1"}
{"event":"system","payload":{"channel":"test_realtime","extension":"postgres_changes","message":"Subscribed to PostgreSQL","status":"ok"},"ref":null,"topic":"realtime:test_realtime"}
{"event":"system","payload":{"channel":"test_realtime","extension":"postgres_changes","message":"Subscribed to PostgreSQL","status":"ok"},"ref":null,"topic":"realtime:test_realtime"}
{"event":"postgres_changes","payload":{"data":{"columns":[{"name":"id","type":"int8"},{"name":"data","type":"text"},{"name":"created_at","type":"timestamptz"}],"commit_timestamp":"2023-04-25T12:21:54.479Z","errors":null,"old_record":{"id":5},"schema":"public","table":"test_realtime","type":"DELETE"},"ids":[103716393]},"ref":null,"topic":"realtime:test_realtime"}
{"event":"postgres_changes","payload":{"data":{"columns":[{"name":"id","type":"int8"},{"name":"data","type":"text"},{"name":"created_at","type":"timestamptz"}],"commit_timestamp":"2023-04-25T12:22:05.160Z","errors":null,"old_record":{"id":6},"schema":"public","table":"test_realtime","type":"DELETE"},"ids":[103716393]},"ref":null,"topic":"realtime:test_realtime"}

Expected behavior

Neither websocket nor console should produce logs for rows outside of the filter.

System information

• OS: Windows
• Browser (if applies): Chrome
• Version of supabase-js: 2.21.0
• Version of Node.js: 18.14.0

Additional context

In reference to: supabase-community/realtime-csharp#27

[w3b6x9]

@acupofjose thanks for reporting! we're aware of this issue and we're tracking it here: https://github.com/supabase/walrus/issues/53

[pierroo]

almost a year later, no news / update about this?

If this is such a hard to fix issue, I would like to say it should be MANDATORY to mention that in the doc for the delete event subscriber

I just realized that issue the hard way with a live product with a huge nasty impact :/

[filipecabaco]

Sorry for the lack of updates. Unfortunately this is a limitation of the underlying approach we're using https://github.com/supabase/walrus/issues/53

We are changing some elements of our implementation and this will be a concern we will take into consideration.