How to generate a WAL record spanning multiple WAL files?

On 2022-Apr-05, Bharath Rupireddy wrote:

Hi,

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

It's easier to use pg_logical_emit_message().

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
Tom: There seems to be something broken here.
Teodor: I'm in sackcloth and ashes... Fixed.
http://archives.postgresql.org/message-id/482D1632.8010507@sigaev.ru

On Tue, 5 Apr 2022 at 15:13, Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

The function pg_logical_emit_message (callable with REPLICATION
permissions from SQL) allows you to emit records of arbitrary length <
2GB - 2B (for now), which should be enough.

Other than that, you could try to generate 16MB of subtransaction IDs;
the commit record would contain all subxids and thus be at least 16MB
in size.

-Matthias

Hi,

On Tue, Apr 5, 2022 at 9:46 PM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

On 2022-Apr-05, Bharath Rupireddy wrote:

Hi,

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

It's easier to use pg_logical_emit_message().

Not sure I understand the question correctly here. What if I use the below
code
where the len might be very large? like 64MB.

XLogBeginInsert();
XLogRegisterData((char *)&xl_append, sizeof(xl_cstore_append));
XLogRegisterData((char *)data, len);

XLogInsert(..);

--
Best Regards
Andy Fan

On Tue, Apr 5, 2022 at 10:10 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

It's easier to use pg_logical_emit_message().

Not sure I understand the question correctly here. What if I use the below code
where the len might be very large? like 64MB.

XLogBeginInsert();
XLogRegisterData((char *)&xl_append, sizeof(xl_cstore_append));
XLogRegisterData((char *)data, len);

XLogInsert(..);

Well, that's how to do it from C. And pg_logical_emit_message() is how
to do it from SQL.

--
Robert Haas
EDB: http://www.enterprisedb.com

On Wed, Apr 6, 2022 at 12:41 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Apr 5, 2022 at 10:10 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

It's easier to use pg_logical_emit_message().

Not sure I understand the question correctly here. What if I use the

below code

where the len might be very large? like 64MB.

XLogBeginInsert();
XLogRegisterData((char *)&xl_append, sizeof(xl_cstore_append));
XLogRegisterData((char *)data, len);

XLogInsert(..);

Well, that's how to do it from C. And pg_logical_emit_message() is how
to do it from SQL.

OK, Thanks for your confirmation!

--
Best Regards
Andy Fan

On Wed, Apr 6, 2022 at 6:56 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:

On Wed, Apr 6, 2022 at 12:41 AM Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Apr 5, 2022 at 10:10 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:

I wanted to have a WAL record spanning multiple WAL files of size, say
16MB. I'm wondering if the Full Page Images (FPIs) of a TOAST table
would help here. Please let me know if there's any way to generate
such large WAL records.

It's easier to use pg_logical_emit_message().

Not sure I understand the question correctly here. What if I use the below code
where the len might be very large? like 64MB.

XLogBeginInsert();
XLogRegisterData((char *)&xl_append, sizeof(xl_cstore_append));
XLogRegisterData((char *)data, len);

XLogInsert(..);

Well, that's how to do it from C. And pg_logical_emit_message() is how
to do it from SQL.

OK, Thanks for your confirmation!

Thanks all for your responses. Yes, using pg_logical_emit_message() is
easy, but it might come in the way of logical decoding as those
messages get decoded.

PS: I wrote a small extension (just for fun) called pg_synthesize_wal
[1]: https://github.com/BRupireddy/pg_synthesize_wal
"Custom WAL Resource Managers" feature [2]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5c279a6d350205cc98f91fb8e1d3e4442a6b25d1 that got committed to PG15.

[1]: https://github.com/BRupireddy/pg_synthesize_wal
[2]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5c279a6d350205cc98f91fb8e1d3e4442a6b25d1

Regards,
Bharath Rupireddy.