Language options for GIN index support functions

[Phillip Diffley]

Hello,

The support functions and operator methods needed to extend a GIN index are
documented in C syntax
. Do these
functions need to be implemented in C, or is there an interface for other
languages like PL/pgSQL?

Thanks!
Phillip

What changes to a table cannot be detected by a trigger?

[Phillip Diffley]

Triggers can be set for insert, update, delete, and truncate operations.
Alter table operations, like adding or dropping a column from a table, do
not cause an update trigger to fire but can be captured by an event trigger.

Are there any situations where the data in a table can be changed and the
change cannot be detected by a trigger or event trigger?

Thanks,
Phillip

How to generate random bigint

[Phillip Diffley]

Postgres's random() function generates a random double. That can be
converted to a random int for smaller integers, but a double can't
represent all of the values in a bigint. Is there a recommended way to
generate a random bigint in Postgres?

Thanks,
Phillip

Re: How to generate random bigint

[Phillip Diffley]

Thank you for all the suggestions! I ended up using pgcrypto's
pg_random_bytes() to build the random int. I haven't fully tested the
function yet, but it looks like this works.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION gen_random_int() RETURNS INT8 AS $$
DECLARE
bytes bytea;
BEGIN
bytes := gen_random_bytes(8);
RETURN
(get_byte(bytes,0)::int8 << 8*0) |
(get_byte(bytes,1)::int8 << 8*1) |
(get_byte(bytes,2)::int8 << 8*2) |
(get_byte(bytes,3)::int8 << 8*3) |
(get_byte(bytes,4)::int8 << 8*4) |
(get_byte(bytes,5)::int8 << 8*5) |
(get_byte(bytes,6)::int8 << 8*6) |
(get_byte(bytes,7)::int8 << 8*7);
END;
$$ LANGUAGE plpgsql;


On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer  wrote:
>
> On 2023-12-21 00:06:39 -0600, Phillip Diffley wrote:
> > Postgres's random() function generates a random double. That can be 
> > converted
> > to a random int for smaller integers, but a double can't represent all of 
> > the
> > values in a bigint. Is there a recommended way to generate a random bigint 
> > in
> > Postgres?
>
> Call random() twice and add the results?
>
> Like this:
>
> select (random() * 2147483648)::int8 * 4294967296
>+ (random() * 4294967296)::int8;
>
> (This assumes that random() actually returns at least 32 random bits.
> If that's not the case you'll need more calls to random())
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"

Replication slot WAL reservation

[Phillip Diffley]

I am trying to understand how logical replication slots work, specifically
in regard to how WAL is reserved and freed by a replication slot.

My current understanding of the WAL (set to wal_level logical) is that:
1. Every DML operation (insert, update, delete, truncate) will have a row
in the WAL and that row will have an LSN assigned to it.
2. The LSNs are assigned when the operation happens.
3. Operations within a transaction are written to the WAL in a contiguous
block when the transaction commits.
4. Each transaction block in the WAL has a commit timestamp associated with
it, and these timestamps are monotonically increasing (I'm only inferring
this, so please correct me if this is not always the case).

A result of an operation being assigned an LSN when the operation occurs
but not being recorded to the WAL until it's transaction commits is that
LSNs in the WAL are not necessarily in order.

But when processing data from a replication slot, we confirm rows that have
been processed and can be deleted from the WAL based on the LSN (eg. with
pg_replication_slot_advance). How does postgres identify what parts of the
WAL can be freed?

Create replication slot options

[Phillip Diffley]

I am testing out some streaming logical replication commands and am having
trouble specifying options when calling CREATE_REPLICATION_SLOT

.

I connect to the database with
psql "dbname=replication_test_db replication=database"

Then I am able to successfully run
CREATE_REPLICATION_SLOT test_slot LOGICAL pgoutput;

But if I try to specify the snapshot option with
CREATE_REPLICATION_SLOT test_slot_2 LOGICAL pgoutput SNAPSHOT 'nothing';
the command fails with "ERROR:  syntax error"

I have tried several combinations of brackets commas and equals signs but
have not been able to get the command with additional options to work. What
is the right syntax to use here?

Thanks,
Phillip

Re: Replication slot WAL reservation

[Phillip Diffley]

Oh I see! I was conflating the data I see coming out of a replication slot
with the internal organization of the WAL. I think the more specific
question I am trying to answer is, as a consumer of a replication slot, how
do I reason about what replication records will be made unavailable when I
confirm an LSN? Here I am worried about situations where the replication
connection is interrupted or the program processing the records crashes,
and we need to replay records that may have been previously sent but were
not fully processed.

For example, are the records sent by a replication slot always sent in the
same order such that if I advance the confirmed_flush_lsn of a slot to the
LSN of record "A", I will know that any records that had been streamed
after record "A" will be replayable?


On Tue, Mar 25, 2025 at 12:14 PM Christophe Pettus  wrote:

> Missed this question!
>
> > On Mar 25, 2025, at 09:56, Phillip Diffley 
> wrote:
> > But when processing data from a replication slot, we confirm rows that
> have been processed and can be deleted from the WAL based on the LSN (eg.
> with pg_replication_slot_advance). How does postgres identify what parts of
> the WAL can be freed?
>
> Basically, if no part of the system "needs" a particular LSN position, the
> segments that include that LSN position and earlier can be free.
>
> The various things that can "need" a particular LSN point are:
>
> 1. Replication slots, if the other side has not confirmed that it has
> received it (under whatever synchronous commit rules that slot is operating
> under).
> 2. The wal_keep_size setting.
> 3. The max_wal_size setting.
> 4. The archive_command, if a WAL segment hasn't been successfully archived
> yet.
>
> One thing to remember is that the WAL does *not* contain contiguous blocks
> of operations for a single transaction.  The operations are written to the
> WAL by every session as they do operations, so the WAL is a jumble of
> different transactions.  One of the jobs of the logical replication
> framework is to sort that out so it can present only the operations that
> belong to committed transactions to the output plugin.  (This is why
> there's an internal structure called the "reorder buffer": it reorders WAL
> operations into transaction blocks.)

Re: Replication slot WAL reservation

[Phillip Diffley]

> You're looking for Standby Status Update

Awesome! I completely missed that.

> Transactions are always presented to the output plugin in commit order

Ok great. I think that is what I needed to know.

Just to confirm, it sounds like the order messages are sent from the output
plugin is what matters here. When you update confirmed_flush_lsn to LSN
"A", any messages that were sent by the output plugin after the message
with LSN "A" will be replayable. Any messages sent by the output plugin
before the message with LSN "A" will most likely not be replayed, since
their data is freed for deletion. Is that correct?

On Tue, Mar 25, 2025 at 11:32 PM Christophe Pettus  wrote:

>
>
> > On Mar 25, 2025, at 20:56, Phillip Diffley 
> wrote:
> >
> > Is there a message type that is used to confirm what logs have been
> successfully consumed?
>
> You're looking for Standby Status Update:
>
>
> https://www.postgresql.org/docs/current/protocol-replication.html#PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE
>
> The logical replication protocol is a superset of the binary replication
> protocol, so many of the same messages are used.
>
> > The part I am uncertain about is what "after" means here, since LSNs are
> not presented in order, and the order of data streamed over the replication
> slot does not match the order of the data in the WAL.
>
> I think there's a misunderstanding here (possibly my fault).  Transactions
> are always presented to the output plugin in commit order, and LSNs can be
> reliably used to determine the time ordering of commits.  LSNs are exactly
> what is used to determine how far into the WAL the replication slot has
> gotten.

Re: Replication slot WAL reservation

[Phillip Diffley]

> You shouldn't need to manually advance the replication slot.
> The client is also expected to send back regular messages letting the
publisher / primary know that it has successfully consumed up to a
particular point

I was thinking of these as the same thing, but it sounds like they are
different. At the moment, the only method I know for letting the
publisher/primary know what has been successfully consumed is
pg_replication_slot_advance. I looked at the message formats
<https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS>
and logical replication message formats
<https://www.postgresql.org/docs/current/protocol-logicalrep-message-formats.html>
pages, but I did not see a message type for updating confirmed_flush_lsn or
otherwise letting the publisher/primary know what logs have been
successfully consumed. There is the flush
<https://www.postgresql.org/docs/current/protocol-message-formats.html#PROTOCOL-MESSAGE-FORMATS-FLUSH>
message, but it looks like it only passes a 4 byte int instead of the 8
bytes required for an LSN. Is there a message type that is used to confirm
what logs have been successfully consumed?

> You know that any WAL generated after `confirmed_flush_lsn` is available
for replay.

The part I am uncertain about is what "after" means here, since LSNs are
not presented in order, and the order of data streamed over the replication
slot does not match the order of the data in the WAL.

I initially (and incorrectly) thought the confirmation order was based on
LSN. So if you confirmed an LSN "x" then all logs with LSN less than "x"
could be released by the publisher/primary. That can't work though since
LSNs are not presented in order by the replication slot. Is there a
monotonically increasing identifier that can be used to identify which logs
come "after" another? Or do you just keep track of the order the
replication slot delivers logs in and not confirm a log until it and all
the logs received before it are processed to the point of being crash-proof?

On Tue, Mar 25, 2025 at 4:32 PM Christophe Pettus  wrote:

>
>
> > On Mar 25, 2025, at 13:58, Phillip Diffley 
> wrote:
> >
> > Oh I see! I was conflating the data I see coming out of a replication
> slot with the internal organization of the WAL. I think the more specific
> question I am trying to answer is, as a consumer of a replication slot, how
> do I reason about what replication records will be made unavailable when I
> confirm an LSN? Here I am worried about situations where the replication
> connection is interrupted or the program processing the records crashes,
> and we need to replay records that may have been previously sent but were
> not fully processed.
>
> It's up to the consuming client to keep track of where it is in the WAL
> (using an LSN).  When the client connects, it specifies what LSN to start
> streaming at.  If that LSN is no longer available, the publisher / primary
> returns an error.
>
> The client shouldn't confirm the flush of an LSN unless it is crash-proof
> to that point, since any WAL before that should be assumed to be
> unavailable.
>
> > For example, are the records sent by a replication slot always sent in
> the same order such that if I advance the confirmed_flush_lsn of a slot to
> the LSN of record "A", I will know that any records that had been streamed
> after record "A" will be replayable?
>
> You know that any WAL generated after `confirmed_flush_lsn` is available
> for replay.  That's the oldest LSN that the client can specify on
> connection (although it can specify a later one, if it exists).  You
> shouldn't need to manually advance the replication slot.  Instead, the
> client specifies where it wants to start when it connects.  The client is
> also expected to send back regular messages letting the publisher / primary
> know that it has successfully consumed up to a particular point in the WAL,
> so the publisher / primary knows it can release that WAL information.