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

On Oct 6, 2017, at 9:31 PM, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2017-10-04 13:59:20 -0700, Ben Chobot wrote:
>> postgres=# CREATE OR REPLACE FUNCTION logical_replication_slot_lsn_delta(slot text) RETURNS pg_lsn AS
>> postgres-# $$
>> postgres$# BEGIN
>> postgres$# return location from pg_logical_slot_peek_changes(slot,null,1) limit 1;
>> postgres$# END
>> postgres$# $$ language plpgsql;
>
> As the issue is fixed now, I just want to mention that looking at
> logical decoding output via the SQL interface, especially when doing it
> in very small increments as you're suggesting here, is way much more
> expensive than continually streaming changes via the replication
> protocol. In a lot of cases it'll be orders of magnitude more expensive.
> So if you can change your usecase to use that, you'll benefit. It also
> avoids having to change between peek/get, because you can just send back
> messages specifying up to where you've processed changes safely.

Oh, for sure, and understood. When we actually pull data from the slot, we'll be doing it via the streaming interface. 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;