Building and checking indexes online

locks_session.py

"""Online index operations on PostgreSQL 18.6: locks.sql on the accounts of setup.sql."""

from pg_session import Server

# The same output on every run: autovacuum off, no parallel workers or JIT, statistics from every row, UTC, and one
# process per index build, so that a failed unique build always reports the same duplicate.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'", "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    pg.psql("locks.sql", errors=1)

$ psql -X -q -f setup.sql

$ psql -X -a -f locks.sql
-- CREATE INDEX inside a transaction, so that pg_locks can show the lock it holds on the table.
BEGIN;
BEGIN
CREATE INDEX accounts_tier ON accounts (tier);
CREATE INDEX
SELECT relation::regclass AS locked, mode, granted FROM pg_locks
WHERE locktype = 'relation' AND relation = 'accounts'::regclass;
  locked  |   mode    | granted 
----------+-----------+---------
 accounts | ShareLock | t
(1 row)

ROLLBACK;
ROLLBACK
-- CONCURRENTLY cannot run inside a transaction block.
BEGIN;
BEGIN
CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier);
psql:locks.sql:9: ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block
ROLLBACK;
ROLLBACK

locks.sql

-- CREATE INDEX inside a transaction, so that pg_locks can show the lock it holds on the table.
BEGIN;
CREATE INDEX accounts_tier ON accounts (tier);
SELECT relation::regclass AS locked, mode, granted FROM pg_locks
WHERE locktype = 'relation' AND relation = 'accounts'::regclass;
ROLLBACK;
-- CONCURRENTLY cannot run inside a transaction block.
BEGIN;
CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier);
ROLLBACK;

setup.sql

-- The accounts of this lesson: 100,000 rows, and e-mail addresses of which ten are used twice.
CREATE TABLE accounts (id int PRIMARY KEY, email text NOT NULL, tier int NOT NULL, tags text[] NOT NULL);
INSERT INTO accounts
SELECT n, 'user' || (CASE WHEN n > 99990 THEN n - 99990 ELSE n END) || '@example.com', n % 5, ARRAY['t' || n % 9]
FROM generate_series(1, 100000) AS n;
VACUUM ANALYZE accounts;

pg_session.py

"""Throwaway PostgreSQL 18.6 servers for the examples of this lesson.

Every session creates a cluster of its own with initdb in a new folder inside the examples folder, starts it, runs
psql scripts and server programs on it, prints what they printed, and stops and deletes it again, also when something
fails. The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file.
It listens only on 127.0.0.1, an address no other computer can reach, on a port the operating system reports as
unused, with no Unix socket, and it asks for a password (scram-sha-256) made up for the run and never printed.
learn:verify's run sandbox cannot read the home folder, where mise keeps PostgreSQL, so these sessions are recorded
with LEARN_VERIFY_NETWORK=allow; they read only this folder and the toolchain and connect to nothing but 127.0.0.1.

The output is exactly what the programs printed, with four substitutions so that a record never shows the computer
it ran on and is the same on every run: the run's folder is shown as ".", the folder of PostgreSQL's programs is left
out of their paths, the operating-system user's name is shown as "learner", and the dots pg_ctl prints while it waits
(one about every tenth of a second) as three. The sessions never print the port. A session stops with status 1 when
a command fails that should have worked, or reports a different number of errors than the session expects, so a run
that went wrong is never recorded as if it had worked.
"""

import os
import pwd
import re
import secrets
import shlex
import shutil
import socket
import subprocess
import sys
import tempfile
import textwrap
import time
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"
OS_USER = pwd.getpwuid(os.geteuid()).pw_name


def free_port():
    """A TCP port on 127.0.0.1 that nothing listens on now."""
    with socket.socket() as probe:
        probe.bind(("127.0.0.1", 0))
        return probe.getsockname()[1]


class Server:
    """One throwaway cluster: initdb, settings, start; stopped and deleted when the with block ends."""

    def __init__(self, settings=None, initdb=(), name="data"):
        self.settings = dict(settings or {})
        self.initdb_args = list(initdb)
        self.name = name
        self.running = []

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg", dir=".")).resolve()
        self.port = free_port()
        self.password = secrets.token_urlsafe(24)
        (self.base / "pwfile").write_text(self.password + "\n")
        self.env = {**os.environ, "PGHOST": "127.0.0.1", "PGPORT": str(self.port), "PGUSER": "postgres",
                    "PGPASSWORD": self.password, "PGDATABASE": "postgres", "PGCONNECT_TIMEOUT": "10"}
        for key in ("PGSERVICE", "PGSERVICEFILE", "PGOPTIONS", "PSQLRC"):
            self.env.pop(key, None)
        for script in Path(".").glob("*.sql"):
            shutil.copy(script, self.base / script.name)
        for data in Path(".").glob("*.csv"):
            shutil.copy(data, self.base / data.name)
        return self

    def __exit__(self, *exc):
        for folder, _port in reversed(self.running):
            self._call(["pg_ctl", "-D", folder, "-m", "fast", "-w", "-t", "60", "stop"])
        self.running = []
        shutil.rmtree(self.base, ignore_errors=True)

    # ---- the cluster ---------------------------------------------------------------------------------------------
    def initdb(self, show=False):
        args = ["initdb", "-D", self.name, "-U", "postgres", "--auth=scram-sha-256", "--pwfile=pwfile",
                *self.initdb_args]
        if not show:
            args.append("--no-instructions")
        self._expect(args, show=show)
        with open(self.base / self.name / "postgresql.conf", "a") as conf:
            conf.write("\n# Added by the session: 127.0.0.1 only, no Unix socket.\n")
            conf.write(f"listen_addresses = '127.0.0.1'\nport = {self.port}\nunix_socket_directories = ''\n")
            for key, value in self.settings.items():
                conf.write(f"{key} = {value}\n")

    def start(self, show=False, folder=None, port=None):
        folder = folder or self.name
        self._expect(["pg_ctl", "-D", folder, "-l", f"{folder}.log", "-w", "-t", "60", "start"], show=show)
        self.running.append((folder, port or self.port))

    def up(self):
        """initdb and start, quietly."""
        self.initdb()
        self.start()
        return self

    def standby(self, folder="standby"):
        """A streaming standby of this server, made with pg_basebackup -R, started on a port of its own."""
        self._expect(["pg_basebackup", "-D", folder, "-R", "-X", "stream", "-c", "fast"])
        port = free_port()
        with open(self.base / folder / "postgresql.conf", "a") as conf:
            conf.write(f"port = {port}\n")
        self.start(folder=folder, port=port)
        for _ in range(300):
            if self.query("SELECT count(*) FROM pg_stat_replication WHERE state = 'streaming'") == "1":
                return port
            time.sleep(0.1)
        sys.exit("the standby did not start streaming")

    # ---- running things ------------------------------------------------------------------------------------------
    def _call(self, args, env=None):
        args = [str(a) for a in args]
        done = subprocess.run([str(BIN / args[0]), *args[1:]], stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
                              text=True, cwd=self.base, env=env or self.env)
        return done.returncode, self.clean(done.stdout)

    def _expect(self, args, show=False, status=0):
        code, out = self._call(args)
        if show:
            _show(args, out)
        if code != status:
            if not show:
                print(out)
            sys.exit(f"{args[0]} ended with status {code}, not {status}")
        return out

    def clean(self, text):
        """The run's folder as ".", the user as learner, pg_ctl's waiting dots as three (see the docstring)."""
        text = text.replace(str(BIN) + "/", "").replace(str(self.base) + "/", "").replace(str(self.base), ".")
        text = re.sub(r"(waiting for server to (?:start|shut down))\.+", r"\1...", text)
        return text.replace(f'"{OS_USER}"', '"learner"')

    def wait_for(self, sql, value):
        """Waits (unprinted) until a query returns the value, for example after a reload."""
        for _ in range(300):
            if self.query(sql) == value:
                return
            time.sleep(0.1)
        sys.exit(f"{sql} never returned {value}")

    def show_file(self, path):
        """Prints a text file of the cluster, as `cat` would."""
        print(f"$ cat {path}")
        print((self.base / path).read_text().rstrip("\n"))
        print()

    def query(self, sql, port=None):
        """One value from one query, unprinted (the session uses it to wait or to decide)."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        code, out = self._call(["psql", "-X", "-At", "-c", sql], env=env)
        if code != 0:
            sys.exit(f"the query failed: {out}")
        return out.strip()

    def silent(self, sql):
        """Runs a statement without printing it (used only to give a role the run's password)."""
        self.query(sql)

    def psql(self, script, errors=0, status=None, user=None, db=None, port=None, flags=("-a",), note=None):
        """Runs a psql script with -X (no ~/.psqlrc) and the given flags, and prints the command and its output.
        `errors` is how many ERROR lines the script must print; `status` the exit status it must end with."""
        env = {**self.env, "PGPORT": str(port or self.port)}
        if user:
            env["PGUSER"] = user
        if db:
            env["PGDATABASE"] = db
        args = ["psql", "-X", *flags, "-f", script]
        code, out = self._call(args, env=env)
        shown = ["psql", "-X", *flags, "-f", script]
        if user:
            shown[1:1] = ["-U", user]
        if db:
            shown[1:1] = ["-d", db]
        if note:
            print(f"-- {note}")
        _show(shown, out)
        found = sum(1 for line in out.splitlines() if "ERROR:" in line or ": error:" in line)
        expected_status = 0 if status is None else status
        if found != errors or code != expected_status:
            sys.exit(f"{script}: {found} errors and status {code}; the session expects {errors} and {expected_status}")

    def sessions(self, count):
        """Opens `count` idle psql connections (each one a client backend) until the with block ends."""
        return _Idle(self, count)

    def shell(self, *args, status=0):
        """Runs one of PostgreSQL's programs and prints the command and its output."""
        return self._expect(list(args), show=True, status=status)

    def listing(self, folder, prefix=""):
        """Prints the names in a folder of the cluster (sorted, folders with a slash), as `ls -p` would list them."""
        path = self.base / folder
        names = sorted(n + ("/" if (path / n).is_dir() else "") for n in os.listdir(path) if n.startswith(prefix))
        print(f"-- in {folder}" + (f", the names that start with {prefix}" if prefix else "") + ":")
        print(textwrap.fill("  ".join(names), width=78, initial_indent="   ", subsequent_indent="   ",
                            break_on_hyphens=False))
        print()
        return names


def _arg(a):
    """A command-line argument as a reader would type it: double quotes around text with single quotes in it."""
    a = str(a)
    if re.fullmatch(r"[\w@%+=:,./-]+", a):
        return a
    if not re.search(r"[\"$`\\!]", a):
        return f'"{a}"'
    return shlex.quote(a)


def _show(args, out):
    print("$ " + " ".join(_arg(a) for a in args))
    if out.strip():
        print(out.rstrip("\n"))
    print()


class _Idle:
    """Idle psql connections, closed again when the with block ends."""

    def __init__(self, server, count):
        self.server, self.count, self.procs = server, count, []

    def __enter__(self):
        before = int(self.server.query("SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"))
        for _ in range(self.count):
            self.procs.append(subprocess.Popen([str(BIN / "psql"), "-X", "-q"], stdin=subprocess.PIPE,
                                               stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL,
                                               env=self.server.env, cwd=self.server.base))
        sql = "SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'"
        for _ in range(300):
            # the query's own connection is one more client backend
            if int(self.server.query(sql)) == before + self.count:
                return self
            time.sleep(0.1)
        sys.exit("the idle connections did not open")

    def __exit__(self, *exc):
        for p in self.procs:
            p.stdin.close()
            p.wait(timeout=30)

progress_session.py

"""CREATE INDEX CONCURRENTLY waiting for a writer's open transaction, watched from a third session."""

import subprocess

from pg_session import BIN, Server

SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'", "max_parallel_maintenance_workers": 0}


def session(args, **kw):
    """A psql process of its own, as a second user at another terminal would run it."""
    return subprocess.Popen([str(BIN / "psql"), "-X", *args], env=pg.env, cwd=pg.base, text=True, **kw)


with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    # Session 1: a writer changes one account and leaves its transaction open.
    writer = session(["-q"], stdin=subprocess.PIPE, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL)
    writer.stdin.write("BEGIN;\nUPDATE accounts SET tier = tier WHERE id = 1;\n")
    writer.stdin.flush()
    pg.wait_for("SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction'", "1")
    print("-- session 1: BEGIN; UPDATE accounts SET tier = tier WHERE id = 1;   (its transaction stays open)")
    # Session 2: the concurrent build, which has to wait for that transaction to end.
    build = session(["-c", "CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier)"],
                    stdout=subprocess.PIPE, stderr=subprocess.STDOUT)
    pg.wait_for("SELECT phase FROM pg_stat_progress_create_index", "waiting for writers before build")
    print("-- session 2: CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier);   (running)")
    print()
    pg.psql("progress.sql", note="session 3, while session 2 waits")
    writer.stdin.write("COMMIT;\n")
    writer.stdin.close()
    writer.wait(timeout=60)
    out, _ = build.communicate(timeout=120)
    print("-- session 1: COMMIT;   then session 2 finished and printed:")
    print(pg.clean(out).rstrip("\n"))
    print()
    pg.psql("done.sql")

$ psql -X -q -f setup.sql

-- session 1: BEGIN; UPDATE accounts SET tier = tier WHERE id = 1;   (its transaction stays open)
-- session 2: CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier);   (running)

-- session 3, while session 2 waits
$ psql -X -a -f progress.sql
-- Asked while CREATE INDEX CONCURRENTLY runs in another session and one writer is still in its transaction.
SELECT phase, lockers_total, lockers_done FROM pg_stat_progress_create_index;
              phase               | lockers_total | lockers_done 
----------------------------------+---------------+--------------
 waiting for writers before build |             1 |            0
(1 row)

SELECT mode, granted FROM pg_locks WHERE locktype = 'relation' AND relation = 'accounts'::regclass ORDER BY mode;
           mode           | granted 
--------------------------+---------
 RowExclusiveLock         | t
 ShareUpdateExclusiveLock | t
(2 rows)

SELECT state, wait_event_type, wait_event, left(query, 60) AS query FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() ORDER BY state;
        state        | wait_event_type | wait_event |                           query                            
---------------------+-----------------+------------+------------------------------------------------------------
 active              | Lock            | virtualxid | CREATE INDEX CONCURRENTLY accounts_tier ON accounts (tier)
 idle in transaction | Client          | ClientRead | UPDATE accounts SET tier = tier WHERE id = 1;
(2 rows)

-- session 1: COMMIT;   then session 2 finished and printed:
CREATE INDEX

$ psql -X -a -f done.sql
-- After the writer committed: the concurrent build finished, and the index is valid.
SELECT indexrelid::regclass AS index, indisvalid, indisready FROM pg_index
WHERE indrelid = 'accounts'::regclass ORDER BY 1;
     index     | indisvalid | indisready 
---------------+------------+------------
 accounts_pkey | t          | t
 accounts_tier | t          | t
(2 rows)

progress.sql

-- Asked while CREATE INDEX CONCURRENTLY runs in another session and one writer is still in its transaction.
SELECT phase, lockers_total, lockers_done FROM pg_stat_progress_create_index;
SELECT mode, granted FROM pg_locks WHERE locktype = 'relation' AND relation = 'accounts'::regclass ORDER BY mode;
SELECT state, wait_event_type, wait_event, left(query, 60) AS query FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid() ORDER BY state;

done.sql

-- After the writer committed: the concurrent build finished, and the index is valid.
SELECT indexrelid::regclass AS index, indisvalid, indisready FROM pg_index
WHERE indrelid = 'accounts'::regclass ORDER BY 1;

invalid_session.py

"""Online index operations on PostgreSQL 18.6: invalid.sql on the accounts of setup.sql."""

from pg_session import Server

# The same output on every run: autovacuum off, no parallel workers or JIT, statistics from every row, UTC, and one
# process per index build, so that a failed unique build always reports the same duplicate.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'", "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    pg.psql("invalid.sql", errors=1)

$ psql -X -q -f setup.sql

$ psql -X -a -f invalid.sql
-- A unique index on the e-mail addresses, built concurrently: ten addresses are duplicated.
CREATE UNIQUE INDEX CONCURRENTLY accounts_email_key ON accounts (email);
psql:invalid.sql:2: ERROR:  could not create unique index "accounts_email_key"
DETAIL:  Key (email)=(user5@example.com) is duplicated.
SELECT indexrelid::regclass AS index, indisvalid, indisready, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'accounts'::regclass ORDER BY 1;
       index        | indisvalid | indisready |  size   
--------------------+------------+------------+---------
 accounts_pkey      | t          | t          | 2208 kB
 accounts_email_key | f          | f          | 0 bytes
(2 rows)

-- Remove the failed index without blocking the table, fix the data, and build it again.
DROP INDEX CONCURRENTLY accounts_email_key;
DROP INDEX
UPDATE accounts SET email = 'user' || id || '@example.com' WHERE id > 99990;
UPDATE 10
CREATE UNIQUE INDEX CONCURRENTLY accounts_email_key ON accounts (email);
CREATE INDEX
SELECT indexrelid::regclass AS index, indisvalid, indisready, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'accounts'::regclass ORDER BY 1;
       index        | indisvalid | indisready |  size   
--------------------+------------+------------+---------
 accounts_pkey      | t          | t          | 2208 kB
 accounts_email_key | t          | t          | 3992 kB
(2 rows)

invalid.sql

-- A unique index on the e-mail addresses, built concurrently: ten addresses are duplicated.
CREATE UNIQUE INDEX CONCURRENTLY accounts_email_key ON accounts (email);
SELECT indexrelid::regclass AS index, indisvalid, indisready, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'accounts'::regclass ORDER BY 1;
-- Remove the failed index without blocking the table, fix the data, and build it again.
DROP INDEX CONCURRENTLY accounts_email_key;
UPDATE accounts SET email = 'user' || id || '@example.com' WHERE id > 99990;
CREATE UNIQUE INDEX CONCURRENTLY accounts_email_key ON accounts (email);
SELECT indexrelid::regclass AS index, indisvalid, indisready, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_index WHERE indrelid = 'accounts'::regclass ORDER BY 1;

reindex_session.py

"""Online index operations on PostgreSQL 18.6: reindex.sql on the accounts of setup.sql."""

from pg_session import Server

# The same output on every run: autovacuum off, no parallel workers or JIT, statistics from every row, UTC, and one
# process per index build, so that a failed unique build always reports the same duplicate.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'", "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    pg.psql("reindex.sql", errors=0)

$ psql -X -q -f setup.sql

$ psql -X -a -f reindex.sql
-- An index after 90 % of its rows are deleted and vacuumed: the pages stay allocated.
CREATE INDEX accounts_email ON accounts (email);
CREATE INDEX
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS index_size;
 index_size 
------------
 3992 kB
(1 row)

DELETE FROM accounts WHERE id % 10 <> 0;
DELETE 90000
VACUUM accounts;
VACUUM
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS after_delete_and_vacuum;
 after_delete_and_vacuum 
-------------------------
 3992 kB
(1 row)

-- REINDEX CONCURRENTLY builds a new copy beside the old one and swaps them, while writes go on.
REINDEX INDEX CONCURRENTLY accounts_email;
REINDEX
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS after_reindex;
 after_reindex 
---------------
 416 kB
(1 row)

-- Index builds sort with maintenance_work_mem, not work_mem.
SHOW maintenance_work_mem;
 maintenance_work_mem 
----------------------
 64MB
(1 row)

reindex.sql

-- An index after 90 % of its rows are deleted and vacuumed: the pages stay allocated.
CREATE INDEX accounts_email ON accounts (email);
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS index_size;
DELETE FROM accounts WHERE id % 10 <> 0;
VACUUM accounts;
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS after_delete_and_vacuum;
-- REINDEX CONCURRENTLY builds a new copy beside the old one and swaps them, while writes go on.
REINDEX INDEX CONCURRENTLY accounts_email;
SELECT pg_size_pretty(pg_relation_size('accounts_email')) AS after_reindex;
-- Index builds sort with maintenance_work_mem, not work_mem.
SHOW maintenance_work_mem;

amcheck_session.py

"""Online index operations on PostgreSQL 18.6: amcheck.sql on the accounts of setup.sql."""

from pg_session import Server

# The same output on every run: autovacuum off, no parallel workers or JIT, statistics from every row, UTC, and one
# process per index build, so that a failed unique build always reports the same duplicate.
SETTINGS = {"autovacuum": "off", "max_parallel_workers_per_gather": 0, "jit": "off",
            "default_statistics_target": 1000, "timezone": "'UTC'", "max_parallel_maintenance_workers": 0}

with Server(settings=SETTINGS) as pg:
    pg.up()
    pg.psql("setup.sql", flags=("-q",))
    pg.psql("amcheck.sql", errors=1)

$ psql -X -q -f setup.sql

$ psql -X -a -f amcheck.sql
-- amcheck: structural checks of B-tree and GIN indexes and of the table itself.
CREATE EXTENSION amcheck;
CREATE EXTENSION
CREATE INDEX accounts_email ON accounts (email);
CREATE INDEX
CREATE INDEX accounts_tags ON accounts USING gin (tags);
CREATE INDEX
SELECT bt_index_check('accounts_email', heapallindexed => true) AS email_index;
 email_index 
-------------
 
(1 row)

SELECT bt_index_parent_check('accounts_pkey', heapallindexed => true) AS primary_key;
 primary_key 
-------------
 
(1 row)

SELECT gin_index_check('accounts_tags') AS tags_index;
 tags_index 
------------
 
(1 row)

SELECT count(*) AS heap_problems FROM verify_heapam('accounts');
 heap_problems 
---------------
             0
(1 row)

-- An index on a function's results, then the function is changed: the index no longer matches the table.
CREATE FUNCTION tier_label(int) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT 'tier ' || $1 $$;
CREATE FUNCTION
CREATE INDEX accounts_tier_label ON accounts (tier_label(tier));
CREATE INDEX
CREATE OR REPLACE FUNCTION tier_label(int) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT 'level ' || $1 $$;
CREATE FUNCTION
SELECT count(*) AS found_by_index FROM accounts WHERE tier_label(tier) = 'level 3';
 found_by_index 
----------------
              0
(1 row)

SELECT bt_index_check('accounts_tier_label', heapallindexed => true);
psql:amcheck.sql:14: ERROR:  heap tuple (0,1) from table "accounts" lacks matching index tuple within index "accounts_tier_label"
HINT:  Retrying verification using the function bt_index_parent_check() might provide a more specific error.
REINDEX INDEX accounts_tier_label;
REINDEX
SELECT bt_index_check('accounts_tier_label', heapallindexed => true) AS after_reindex;
 after_reindex 
---------------
 
(1 row)

SELECT count(*) AS found_by_index FROM accounts WHERE tier_label(tier) = 'level 3';
 found_by_index 
----------------
          20000
(1 row)

amcheck.sql

-- amcheck: structural checks of B-tree and GIN indexes and of the table itself.
CREATE EXTENSION amcheck;
CREATE INDEX accounts_email ON accounts (email);
CREATE INDEX accounts_tags ON accounts USING gin (tags);
SELECT bt_index_check('accounts_email', heapallindexed => true) AS email_index;
SELECT bt_index_parent_check('accounts_pkey', heapallindexed => true) AS primary_key;
SELECT gin_index_check('accounts_tags') AS tags_index;
SELECT count(*) AS heap_problems FROM verify_heapam('accounts');
-- An index on a function's results, then the function is changed: the index no longer matches the table.
CREATE FUNCTION tier_label(int) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT 'tier ' || $1 $$;
CREATE INDEX accounts_tier_label ON accounts (tier_label(tier));
CREATE OR REPLACE FUNCTION tier_label(int) RETURNS text IMMUTABLE LANGUAGE sql AS $$ SELECT 'level ' || $1 $$;
SELECT count(*) AS found_by_index FROM accounts WHERE tier_label(tier) = 'level 3';
SELECT bt_index_check('accounts_tier_label', heapallindexed => true);
REINDEX INDEX accounts_tier_label;
SELECT bt_index_check('accounts_tier_label', heapallindexed => true) AS after_reindex;
SELECT count(*) AS found_by_index FROM accounts WHERE tier_label(tier) = 'level 3';