fill_seg.sql

application/sql

Download

CREATE OR REPLACE FUNCTION fill_segment_upto_remaining_approx(approx_trailing_space int, approx_iterations int default 10)
RETURNS VOID
LANGUAGE plpgsql AS $$
  DECLARE
    starting_lsn pg_lsn = pg_current_wal_insert_lsn();
    ending_lsn pg_lsn = pg_current_wal_insert_lsn();
    segment_size int = (SELECT setting::int AS wal_segsize FROM pg_settings WHERE name = 'wal_segment_size');
    fill_to_offset int;
    segment_offset int;
    emit_size int;
    iterations int = 0;
  BEGIN

    fill_to_offset = segment_size - trailing_space;
    segment_offset = (pg_walfile_name_offset(starting_lsn)).file_offset;

    WHILE segment_offset < fill_to_offset LOOP
      emit_size = (segment_size - trailing_space) / numrecords;
      emit_size = emit_size + emit_size * (random() - 0.5);

      IF segment_offset + emit_size >= segment_size THEN
        emit_size = (fill_to_offset  - segment_offset) / 2;
      END IF;

      PERFORM pg_logical_emit_message(true, 'test 026', repeat('          ', emit_size / 10));
      segment_offset = (pg_walfile_name_offset(pg_current_wal_insert_lsn())).file_offset;
      iterations = iterations + 1;
    END LOOP;

    ending_lsn = pg_current_wal_insert_lsn();
    RAISE NOTICE 'filled segment % up to off % in % iterations, % bytes / % pct left, now segment %',
      pg_walfile_name(starting_lsn),
      segment_offset, iterations, segment_size - segment_offset, 1 - segment_offset::float8 / segment_size,
      pg_walfile_name(ending_lsn);
    ASSERT pg_walfile_name(starting_lsn) = pg_walfile_name(ending_lsn);
    ASSERT pg_walfile_name(starting_lsn) <> pg_walfile_name(ending_lsn + trailing_space);
  END;
$$;

SELECT pg_switch_wal();
SELECT fill_segment_upto_remaining_approx(octet_length(repeat('xyzxz', 123456)) / 2, 100);