gin_hexedit

#!/bin/bash
#
# gin_hexedit:  Don't set any offsets in wxHexEditor cache.  Instead, output
# a summary of interesting offsets for the GIN index to stdout for user.
#
# Unlike relation_hexedit, this script does not set any offsets in the
# wxHexEditor cache/registry.

usage() {
  cat <<EOM
  Usage:
  $(basename "$0") relname

EOM
  exit 0
}

[ -z "$1" ] && { usage; }

source ./hexedit.cfg

relname=$1

# Print useful byte offsets for user to stdout:
if ! psql --no-psqlrc -c "CREATE EXTENSION IF NOT EXISTS pageinspect"
then
  echo "creating pageinspect extension failed"
  exit 1
fi

echo "Contiguous flags in GIN index summary:"
if ! psql --no-psqlrc -P pager=off -c "WITH recursive index_details AS (
  SELECT '$relname'::text AS idx
),
size_in_pages_index AS (
  SELECT
    (pg_relation_size(idx::regclass) / (2^13))::int4 AS size_pages
  FROM
    index_details
),
page_stats AS (
  SELECT
    index_details.*,
    stats.*,
    i AS series
  FROM
    index_details,
    size_in_pages_index,
    LATERAL (SELECT i FROM generate_series(0, size_pages - 1) i) AS series,
    LATERAL (SELECT * FROM gin_page_opaque_info(get_raw_page(idx, i))) AS stats
  ORDER BY
    i
),
recursive_rangewise_blocks(flags, series, first) AS (
  SELECT
    p.flags,
    0,
    0
  FROM
    page_stats p
  WHERE
    series = 0
  UNION
  SELECT
    i.flags,
    i.series,
    CASE WHEN i.flags = o.flags THEN o.first ELSE i.series END
  FROM
    page_stats AS i,
    recursive_rangewise_blocks AS o
  WHERE
    i.series = o.series + 1
)
SELECT
  flags,
  first AS first_block_in_series,
  lead(series, 1, size_pages) OVER() - 1 AS last_block_in_series,
  first * (2^13) AS offset_bytes_first
FROM
  recursive_rangewise_blocks,
  size_in_pages_index
WHERE
  series = first
ORDER BY
  first;"
then
  echo "displaying contiguous range of blocks failed"
  exit 1
fi

export OFFSETS=""
echo "Note: no offsets are set in cache when using $(basename "$0") convenience script"
./__open_relation "$relname"