Subscribing to Views (in addition to Tables)

[yekta]

Feature request

I'm not sure if this exists already but I couldn't get it to work. It would be nice if we could subscribe to views in addition to tables. I'm not familiar enough with the system to say if this is easy, hard or possible.

The problem

I want to give public access to certain columns in a table (instead of rows). I learned that views is a solution, but I don't seem to be able to use views with realtime.

Solution I'd like

Given messages_public is a view based on messages, the usage might look like this:

channel.on(
    'postgres_changes',
    {
        event: '*',
        schema: 'public',
        table: 'messages_public'
    },
    (payload) => console.log('payload', payload);
);

Or this

channel.on(
    'postgres_changes',
    {
        event: '*',
        schema: 'public',
        view: 'messages_public'
    },
    (payload) => console.log('payload', payload);
);

Describe alternatives you've considered

Currently I'm just manually polling the view instead of a realtime subscription, which isn't ideal.

[GaryAustin1]

Realtime uses Postgres replication to get updates. Replication does not work on views.
I can't comment on ability of something to be done by Supabase in the future.

There are few threads in Github discussions on workarounds. Search here : https://github.com/supabase/supabase/discussions?discussions_q=realtime+on+views

Also there are discussions in the Supabase Discord.

[baderdean]

Why supabase doesn't implement an alternative realtime mechanism using something different from replication. It could be notification (LISTEN/NOTIFY) with a statement trigger or something like that, isn't it?

[GaryAustin1]

This link has a history on realtime and why they did not use Listen/notify. https://supabase.com/blog/supabase-realtime-multiplayer-general-availability

[baderdean]

I'm not saying to switch to LISTEN/NOTIFY globally but to explore this solution among others, like logical decoding, for the specific usecase of materialized views at least. Even a temporary table created "on the fly" with some limits on its size for instance is better than nothing.

It's better than no support of any change data capture / realtime pattern at all for materialized views IMHO.