Re: pg_logical_slot_peek_changes crashes postgres when called from inside pl/pgsql

On 2017-10-06 21:40:06 -0700, Ben Chobot wrote:
> Oh, for sure, and understood. When we actually pull data from the
> slot, we'll be doing it via the streaming interface.

Ah, good ;)

> This function is reduced from what it was originally intended to be, which was an infrequent check to an alerting system to make sure nobody had stopped consuming data from their logical replication slot. FWIW, what we ended up with was this SQL function, which would have been a little easier to follow in pl/pgsql, but works just fine in this form:
>
> CREATE OR REPLACE FUNCTION logical_replication_slot_lsn_delta(slot text) RETURNS numeric AS
> $$
> select pg_current_xlog_location()-
> case when active then
> (select flush_location from pg_stat_replication where pid=active_pid)
> else
> (select location from pg_logical_slot_peek_changes($1,null,1) union
> select pg_current_xlog_location() order by location limit 1)
> end
> from pg_replication_slots where slot_name=$1;
> $$ language sql security definer;

Why don't you just look at pg_replication_slots.confirmed_flush_lsn?

Greetings,

Andres Freund