Thread: How to generate a WAL record spanning multiple WAL files?
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. Thoughts? Regards, Bharath Rupireddy.
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
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(..);
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] implementing functions to generate huge WAL records. I used the "Custom WAL Resource Managers" feature [2] 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.