On Oct 6, 2017, at 9:31 PM, Andres Freund <
andres@anarazel.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;