pizzasql/pkg/executor/pg_catalog.go

package executor

import (
	"fmt"
	"sort"
	"strconv"
	"strings"

	"github.com/danfragoso/pizzasql-next/pkg/parser"
	"github.com/danfragoso/pizzasql-next/pkg/pgerror"
	"github.com/danfragoso/pizzasql-next/pkg/storage"
)

// PostgreSQL catalog emulation.
//
// pg_catalog and information_schema are synthesized from the durable schema the
// connection's session observes (including DDL staged by an open transaction).
// Nothing is persisted and no virtual relation accepts writes. OIDs are
// deterministic: a 32-bit FNV-1a hash of the namespace-qualified name, lifted
// above PostgreSQL's FirstNormalObjectId and collision-probed in sorted order.
// The same allocator feeds pg_class, pg_attribute, pg_type, pg_index, and
// pg_constraint, so catalog joins agree.
//
// The emulated schemas are pg_catalog (OID 11), public (OID 2200), and
// information_schema (OID 13207). User relations always live in public.

const (
	pgCatalogSchemaName    = "pg_catalog"
	informationSchemaName  = "information_schema"
	publicSchemaName       = "public"
	pgCatalogNamespaceOID  = int64(11)
	publicNamespaceOID     = int64(2200)
	infoSchemaNamespaceOID = int64(13207)

	firstNormalObjectID = int64(16384)

	// Built-in OIDs the emulation refers to.
	pgDefaultTablespaceOID = int64(1663)
	pgGlobalTablespaceOID  = int64(1664)
	pgBtreeAMOID           = int64(403)
	pgHeapAMOID            = int64(2)
	catalogOwnerOID        = int64(10)
)

// catalogRelationDef declares a virtual relation's column list.
type catalogRelationDef struct {
	schema  string
	name    string
	columns []catalogColumnDef
}

type catalogColumnDef struct {
	name string
	typ  string
}

// pgCatalogRelationDefs lists the read-only pg_catalog relations.
var pgCatalogRelationDefs = []catalogRelationDef{
	{schema: pgCatalogSchemaName, name: "pg_namespace", columns: []catalogColumnDef{
		{"oid", "OID"}, {"nspname", "NAME"}, {"nspowner", "OID"}, {"nspacl", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_class", columns: []catalogColumnDef{
		{"oid", "OID"}, {"relname", "NAME"}, {"relnamespace", "OID"}, {"reltype", "OID"},
		{"reloftype", "OID"}, {"relowner", "OID"}, {"relam", "OID"}, {"relfilenode", "OID"},
		{"reltablespace", "OID"}, {"relpages", "INT4"}, {"reltuples", "FLOAT4"},
		{"relallvisible", "INT4"}, {"reltoastrelid", "OID"}, {"relhasindex", "BOOL"},
		{"relisshared", "BOOL"}, {"relpersistence", "CHAR"}, {"relkind", "CHAR"},
		{"relnatts", "INT2"}, {"relchecks", "INT2"}, {"relhasrules", "BOOL"},
		{"relhastriggers", "BOOL"}, {"relhassubclass", "BOOL"}, {"relrowsecurity", "BOOL"},
		{"relforcerowsecurity", "BOOL"}, {"relispopulated", "BOOL"}, {"relreplident", "CHAR"},
		{"relispartition", "BOOL"}, {"relrewrite", "OID"}, {"relfrozenxid", "OID"},
		{"relminmxid", "OID"}, {"relacl", "TEXT"}, {"reloptions", "TEXT"},
		{"relpartbound", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_attribute", columns: []catalogColumnDef{
		{"attrelid", "OID"}, {"attname", "NAME"}, {"atttypid", "OID"},
		{"attstattarget", "INT4"}, {"attlen", "INT2"}, {"attnum", "INT2"},
		{"attndims", "INT4"}, {"attcacheoff", "INT4"}, {"atttypmod", "INT4"},
		{"attbyval", "BOOL"}, {"attalign", "CHAR"}, {"attstorage", "CHAR"},
		{"attcompression", "CHAR"}, {"attnotnull", "BOOL"}, {"atthasdef", "BOOL"},
		{"atthasmissing", "BOOL"}, {"attidentity", "CHAR"}, {"attgenerated", "CHAR"},
		{"attisdropped", "BOOL"}, {"attislocal", "BOOL"}, {"attinhcount", "INT2"},
		{"attcollation", "OID"}, {"attacl", "TEXT"}, {"attoptions", "TEXT"},
		{"attfdwoptions", "TEXT"}, {"attmissingval", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_type", columns: []catalogColumnDef{
		{"oid", "OID"}, {"typname", "NAME"}, {"typnamespace", "OID"},
		{"typowner", "OID"}, {"typlen", "INT2"}, {"typbyval", "BOOL"},
		{"typtype", "CHAR"}, {"typcategory", "CHAR"}, {"typispreferred", "BOOL"},
		{"typisdefined", "BOOL"}, {"typdelim", "CHAR"}, {"typrelid", "OID"},
		{"typsubscript", "TEXT"}, {"typelem", "OID"}, {"typarray", "OID"},
		{"typinput", "TEXT"}, {"typoutput", "TEXT"}, {"typreceive", "TEXT"},
		{"typsend", "TEXT"}, {"typmodin", "TEXT"}, {"typmodout", "TEXT"},
		{"typanalyze", "TEXT"}, {"typalign", "CHAR"}, {"typstorage", "CHAR"},
		{"typnotnull", "BOOL"}, {"typbasetype", "OID"}, {"typtypmod", "INT4"},
		{"typndims", "INT4"}, {"typcollation", "OID"}, {"typdefaultbin", "TEXT"},
		{"typdefault", "TEXT"}, {"typacl", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_index", columns: []catalogColumnDef{
		{"indexrelid", "OID"}, {"indrelid", "OID"}, {"indnatts", "INT2"},
		{"indnkeyatts", "INT2"}, {"indisunique", "BOOL"}, {"indisprimary", "BOOL"},
		{"indisexclusion", "BOOL"}, {"indimmediate", "BOOL"}, {"indisclustered", "BOOL"},
		{"indisvalid", "BOOL"}, {"indcheckxmin", "BOOL"}, {"indisready", "BOOL"},
		{"indislive", "BOOL"}, {"indisreplident", "BOOL"}, {"indkey", "INT2[]"},
		{"indcollation", "TEXT"}, {"indclass", "TEXT"}, {"indoption", "INT2[]"},
		{"indexprs", "TEXT"}, {"indpred", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_constraint", columns: []catalogColumnDef{
		{"oid", "OID"}, {"conname", "NAME"}, {"connamespace", "OID"},
		{"contype", "CHAR"}, {"condeferrable", "BOOL"}, {"condeferred", "BOOL"},
		{"convalidated", "BOOL"}, {"conrelid", "OID"}, {"contypid", "OID"},
		{"conindid", "OID"}, {"conparentid", "OID"}, {"confrelid", "OID"},
		{"confupdtype", "CHAR"}, {"confdeltype", "CHAR"}, {"confmatchtype", "CHAR"},
		{"conislocal", "BOOL"}, {"coninhcount", "INT2"}, {"connoinherit", "BOOL"},
		{"conkey", "INT2[]"}, {"confkey", "INT2[]"}, {"conpfeqop", "TEXT"},
		{"conppeqop", "TEXT"}, {"conffeqop", "TEXT"}, {"conexclop", "TEXT"},
		{"conbin", "TEXT"}, {"consrc", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_attrdef", columns: []catalogColumnDef{
		{"oid", "OID"}, {"adrelid", "OID"}, {"adnum", "INT2"}, {"adbin", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_database", columns: []catalogColumnDef{
		{"oid", "OID"}, {"datname", "NAME"}, {"datdba", "OID"}, {"encoding", "INT4"},
		{"datlocprovider", "CHAR"}, {"datistemplate", "BOOL"}, {"datallowconn", "BOOL"},
		{"datconnlimit", "INT4"}, {"datlastsysoid", "OID"}, {"datfrozenxid", "OID"},
		{"datminmxid", "OID"}, {"dattablespace", "OID"}, {"datcollate", "TEXT"},
		{"datctype", "TEXT"}, {"datlocale", "TEXT"}, {"daticurules", "TEXT"},
		{"datcollversion", "TEXT"}, {"datacl", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_tablespace", columns: []catalogColumnDef{
		{"oid", "OID"}, {"spcname", "NAME"}, {"spcowner", "OID"}, {"spcacl", "TEXT"},
		{"spcoptions", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_proc", columns: []catalogColumnDef{
		{"oid", "OID"}, {"proname", "NAME"}, {"pronamespace", "OID"},
		{"proowner", "OID"}, {"prolang", "OID"}, {"procost", "FLOAT4"},
		{"prorows", "FLOAT4"}, {"provariadic", "OID"}, {"prosupport", "TEXT"},
		{"prokind", "CHAR"}, {"prosecdef", "BOOL"}, {"proleakproof", "BOOL"},
		{"proisstrict", "BOOL"}, {"proretset", "BOOL"}, {"provolatile", "CHAR"},
		{"proparallel", "CHAR"}, {"pronargs", "INT2"}, {"pronargdefaults", "INT2"},
		{"prorettype", "OID"}, {"proargtypes", "TEXT"}, {"proallargtypes", "TEXT"},
		{"proargmodes", "TEXT"}, {"proargnames", "TEXT"}, {"proargdefaults", "TEXT"},
		{"protrftypes", "TEXT"}, {"prosrc", "TEXT"}, {"probin", "TEXT"},
		{"prosqlbody", "TEXT"}, {"proconfig", "TEXT"}, {"proacl", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_am", columns: []catalogColumnDef{
		{"oid", "OID"}, {"amname", "NAME"}, {"amhandler", "OID"}, {"amtype", "CHAR"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_description", columns: []catalogColumnDef{
		{"objoid", "OID"}, {"classoid", "OID"}, {"objsubid", "INT4"}, {"description", "TEXT"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_tables", columns: []catalogColumnDef{
		{"schemaname", "NAME"}, {"tablename", "NAME"}, {"tableowner", "NAME"},
		{"tablespace", "NAME"}, {"hasindexes", "BOOL"}, {"hasrules", "BOOL"},
		{"hastriggers", "BOOL"}, {"rowsecurity", "BOOL"},
	}},
	{schema: pgCatalogSchemaName, name: "pg_indexes", columns: []catalogColumnDef{
		{"schemaname", "NAME"}, {"tablename", "NAME"}, {"indexname", "NAME"},
		{"tablespace", "NAME"}, {"indexdef", "TEXT"},
	}},
}

// informationSchemaRelationDefs lists the read-only information_schema views.
var informationSchemaRelationDefs = []catalogRelationDef{
	{schema: informationSchemaName, name: "tables", columns: []catalogColumnDef{
		{"table_catalog", "TEXT"}, {"table_schema", "TEXT"}, {"table_name", "TEXT"},
		{"table_type", "TEXT"}, {"self_referencing_column_name", "TEXT"},
		{"reference_generation", "TEXT"}, {"user_defined_type_catalog", "TEXT"},
		{"user_defined_type_schema", "TEXT"}, {"user_defined_type_name", "TEXT"},
		{"is_insertable_into", "TEXT"}, {"is_typed", "TEXT"}, {"commit_action", "TEXT"},
	}},
	{schema: informationSchemaName, name: "columns", columns: []catalogColumnDef{
		{"table_catalog", "TEXT"}, {"table_schema", "TEXT"}, {"table_name", "TEXT"},
		{"column_name", "TEXT"}, {"ordinal_position", "INT4"}, {"column_default", "TEXT"},
		{"is_nullable", "TEXT"}, {"data_type", "TEXT"}, {"character_maximum_length", "INT4"},
		{"character_octet_length", "INT4"}, {"numeric_precision", "INT4"},
		{"numeric_precision_radix", "INT4"}, {"numeric_scale", "INT4"},
		{"datetime_precision", "INT4"}, {"interval_type", "TEXT"},
		{"interval_precision", "INT4"}, {"character_set_catalog", "TEXT"},
		{"character_set_schema", "TEXT"}, {"character_set_name", "TEXT"},
		{"collation_catalog", "TEXT"}, {"collation_schema", "TEXT"},
		{"collation_name", "TEXT"}, {"domain_catalog", "TEXT"}, {"domain_schema", "TEXT"},
		{"domain_name", "TEXT"}, {"udt_catalog", "TEXT"}, {"udt_schema", "TEXT"},
		{"udt_name", "TEXT"}, {"scope_catalog", "TEXT"}, {"scope_schema", "TEXT"},
		{"scope_name", "TEXT"}, {"maximum_cardinality", "INT4"}, {"dtd_identifier", "TEXT"},
		{"is_self_referencing", "TEXT"}, {"is_identity", "TEXT"},
		{"identity_generation", "TEXT"}, {"identity_start", "TEXT"},
		{"identity_increment", "TEXT"}, {"identity_maximum", "TEXT"},
		{"identity_minimum", "TEXT"}, {"identity_cycle", "TEXT"},
		{"is_generated", "TEXT"}, {"generation_expression", "TEXT"},
		{"is_updatable", "TEXT"},
	}},
	{schema: informationSchemaName, name: "table_constraints", columns: []catalogColumnDef{
		{"constraint_catalog", "TEXT"}, {"constraint_schema", "TEXT"},
		{"constraint_name", "TEXT"}, {"table_catalog", "TEXT"}, {"table_schema", "TEXT"},
		{"table_name", "TEXT"}, {"constraint_type", "TEXT"}, {"is_deferrable", "TEXT"},
		{"initially_deferred", "TEXT"}, {"enforced", "TEXT"}, {"nulls_distinct", "TEXT"},
	}},
	{schema: informationSchemaName, name: "key_column_usage", columns: []catalogColumnDef{
		{"constraint_catalog", "TEXT"}, {"constraint_schema", "TEXT"},
		{"constraint_name", "TEXT"}, {"table_catalog", "TEXT"}, {"table_schema", "TEXT"},
		{"table_name", "TEXT"}, {"column_name", "TEXT"}, {"ordinal_position", "INT4"},
		{"position_in_unique_constraint", "INT4"},
	}},
	{schema: informationSchemaName, name: "referential_constraints", columns: []catalogColumnDef{
		{"constraint_catalog", "TEXT"}, {"constraint_schema", "TEXT"},
		{"constraint_name", "TEXT"}, {"unique_constraint_catalog", "TEXT"},
		{"unique_constraint_schema", "TEXT"}, {"unique_constraint_name", "TEXT"},
		{"match_option", "TEXT"}, {"update_rule", "TEXT"}, {"delete_rule", "TEXT"},
		{"is_deferrable", "TEXT"}, {"initially_deferred", "TEXT"}, {"enforced", "TEXT"},
	}},
	{schema: informationSchemaName, name: "constraint_column_usage", columns: []catalogColumnDef{
		{"table_catalog", "TEXT"}, {"table_schema", "TEXT"}, {"table_name", "TEXT"},
		{"constraint_catalog", "TEXT"}, {"constraint_schema", "TEXT"},
		{"constraint_name", "TEXT"}, {"column_name", "TEXT"},
	}},
	{schema: informationSchemaName, name: "schemata", columns: []catalogColumnDef{
		{"catalog_name", "TEXT"}, {"schema_name", "TEXT"}, {"schema_owner", "TEXT"},
		{"default_character_set_catalog", "TEXT"},
		{"default_character_set_schema", "TEXT"},
		{"default_character_set_name", "TEXT"}, {"sql_path", "TEXT"},
	}},
	{schema: informationSchemaName, name: "sequences", columns: []catalogColumnDef{
		{"sequence_catalog", "TEXT"}, {"sequence_schema", "TEXT"},
		{"sequence_name", "TEXT"}, {"data_type", "TEXT"},
		{"numeric_precision", "INT4"}, {"numeric_precision_radix", "INT4"},
		{"numeric_scale", "INT4"}, {"start_value", "TEXT"}, {"minimum_value", "TEXT"},
		{"maximum_value", "TEXT"}, {"increment", "TEXT"}, {"cycle_option", "TEXT"},
	}},
	{schema: informationSchemaName, name: "views", columns: []catalogColumnDef{
		{"table_catalog", "TEXT"}, {"table_schema", "TEXT"}, {"table_name", "TEXT"},
		{"view_definition", "TEXT"}, {"check_option", "TEXT"}, {"is_updatable", "TEXT"},
		{"is_insertable_into", "TEXT"}, {"is_trigger_updatable", "TEXT"},
		{"is_trigger_deletable", "TEXT"}, {"is_trigger_insertable_into", "TEXT"},
	}},
}

// catalogRelationSchema builds the storage schema that the analyzer and the
// materialized SELECT path use for a virtual relation.
func catalogRelationSchema(def catalogRelationDef) *storage.Schema {
	schema := &storage.Schema{Name: def.name, Columns: make([]storage.Column, len(def.columns))}
	for i, column := range def.columns {
		schema.Columns[i] = storage.Column{Name: column.name, Type: column.typ, Nullable: true}
	}
	return schema
}

var pgCatalogRelationIndex = buildCatalogRelationIndex()

type catalogRelationEntry struct {
	def    catalogRelationDef
	schema *storage.Schema
}

func buildCatalogRelationIndex() map[string]catalogRelationEntry {
	index := make(map[string]catalogRelationEntry, len(pgCatalogRelationDefs)+len(informationSchemaRelationDefs))
	for _, def := range append(append([]catalogRelationDef(nil), pgCatalogRelationDefs...), informationSchemaRelationDefs...) {
		index[def.schema+"."+strings.ToLower(def.name)] = catalogRelationEntry{def: def, schema: catalogRelationSchema(def)}
	}
	return index
}

// catalogRelationFor resolves a possibly schema-qualified table reference to a
// virtual catalog relation. Unqualified names resolve to pg_catalog only when
// no durable table shadows them, matching PostgreSQL's implicit search_path.
func (e *Executor) catalogRelationFor(ref parser.TableRef) (catalogRelationEntry, bool) {
	if ref.Subquery != nil || ref.Function != nil {
		return catalogRelationEntry{}, false
	}
	name := strings.ToLower(ref.Name)
	switch strings.ToLower(ref.Schema) {
	case pgCatalogSchemaName:
		entry, ok := pgCatalogRelationIndex[pgCatalogSchemaName+"."+name]
		return entry, ok
	case informationSchemaName:
		entry, ok := pgCatalogRelationIndex[informationSchemaName+"."+name]
		return entry, ok
	case "", publicSchemaName:
		if e.session.TableExists(ref.Name) {
			return catalogRelationEntry{}, false
		}
		entry, ok := pgCatalogRelationIndex[pgCatalogSchemaName+"."+name]
		return entry, ok
	default:
		return catalogRelationEntry{}, false
	}
}

// isCatalogRelationRef reports whether a FROM entry names a virtual relation.
func (e *Executor) isCatalogRelationRef(ref parser.TableRef) bool {
	_, ok := e.catalogRelationFor(ref)
	return ok
}

// fromReferencesCatalogRelation walks a FROM clause, including JOIN chains.
func (e *Executor) fromReferencesCatalogRelation(from []parser.TableRef) bool {
	for _, ref := range from {
		cur := ref
		for {
			if e.isCatalogRelationRef(cur) {
				return true
			}
			if cur.Join == nil || cur.Join.Table == nil {
				break
			}
			cur = *cur.Join.Table
		}
	}
	return false
}

// ---------------------------------------------------------------------------
// Deterministic OIDs

func fnv1a32(text string) uint32 {
	hash := uint32(2166136261)
	for i := 0; i < len(text); i++ {
		hash ^= uint32(text[i])
		hash *= 16777619
	}
	return hash
}

// allocateOID maps a kind and fully qualified name to a stable, positive OID.
// The first probe is the FNV-1a hash lifted into [firstNormalObjectID,
// MaxInt32]; collisions are resolved by linear probing in the caller's sorted
// order, so the assignment is deterministic for a given schema.
func (s *catalogSnapshot) allocateOID(kind, key string) int64 {
	owner := kind + ":" + key
	oid := int64(fnv1a32("pizzasql:" + owner))
	oid &= 0x7fffffff
	if oid < firstNormalObjectID {
		oid += firstNormalObjectID
	}
	for {
		if existing, used := s.oidOwners[oid]; !used || existing == owner {
			s.oidOwners[oid] = owner
			return oid
		}
		oid++
		if oid > 0x7fffffff {
			oid = firstNormalObjectID
		}
	}
}

// ---------------------------------------------------------------------------
// Built-in type table

type pgBuiltinType struct {
	oid      int64
	name     string // pg_type.typname
	sqlName  string // format_type / information_schema spelling
	length   int64
	byval    bool
	category string
	delim    string
	elem     int64
	array    int64
	align    string
	storage  string
}

func (t pgBuiltinType) isArray() bool { return t.category == "A" }

var pgBuiltinTypes = []pgBuiltinType{
	{oid: 16, name: "bool", sqlName: "boolean", length: 1, byval: true, category: "B", delim: ",", array: 1000, align: "c", storage: "p"},
	{oid: 17, name: "bytea", sqlName: "bytea", length: -1, category: "U", delim: ",", array: 1001, align: "i", storage: "x"},
	{oid: 18, name: "char", sqlName: "\"char\"", length: 1, byval: true, category: "S", delim: ",", array: 1002, align: "c", storage: "p"},
	{oid: 19, name: "name", sqlName: "name", length: 64, category: "S", delim: ",", array: 1003, align: "c", storage: "p"},
	{oid: 20, name: "int8", sqlName: "bigint", length: 8, byval: true, category: "N", delim: ",", array: 1016, align: "d", storage: "p"},
	{oid: 21, name: "int2", sqlName: "smallint", length: 2, byval: true, category: "N", delim: ",", array: 1005, align: "s", storage: "p"},
	{oid: 23, name: "int4", sqlName: "integer", length: 4, byval: true, category: "N", delim: ",", array: 1007, align: "i", storage: "p"},
	{oid: 25, name: "text", sqlName: "text", length: -1, category: "S", delim: ",", array: 1009, align: "i", storage: "x"},
	{oid: 26, name: "oid", sqlName: "oid", length: 4, byval: true, category: "N", delim: ",", array: 1028, align: "i", storage: "p"},
	{oid: 114, name: "json", sqlName: "json", length: -1, category: "U", delim: ",", array: 199, align: "i", storage: "x"},
	{oid: 700, name: "float4", sqlName: "real", length: 4, byval: true, category: "N", delim: ",", array: 1021, align: "i", storage: "p"},
	{oid: 701, name: "float8", sqlName: "double precision", length: 8, byval: true, category: "N", delim: ",", array: 1022, align: "d", storage: "p"},
	{oid: 1042, name: "bpchar", sqlName: "character", length: -1, category: "S", delim: ",", array: 1014, align: "i", storage: "x"},
	{oid: 1043, name: "varchar", sqlName: "character varying", length: -1, category: "S", delim: ",", array: 1015, align: "i", storage: "x"},
	{oid: 1082, name: "date", sqlName: "date", length: 4, byval: true, category: "D", delim: ",", array: 1182, align: "i", storage: "p"},
	{oid: 1083, name: "time", sqlName: "time without time zone", length: 8, byval: true, category: "D", delim: ",", array: 1183, align: "d", storage: "p"},
	{oid: 1184, name: "timestamptz", sqlName: "timestamp with time zone", length: 8, byval: true, category: "D", delim: ",", array: 1185, align: "d", storage: "p"},
	{oid: 1700, name: "numeric", sqlName: "numeric", length: -1, category: "N", delim: ",", array: 1231, align: "i", storage: "x"},
	{oid: 2205, name: "regclass", sqlName: "regclass", length: 4, byval: true, category: "N", delim: ",", array: 2210, align: "i", storage: "p"},
	{oid: 2206, name: "regtype", sqlName: "regtype", length: 4, byval: true, category: "N", delim: ",", array: 2211, align: "i", storage: "p"},
	{oid: 2950, name: "uuid", sqlName: "uuid", length: 16, category: "U", delim: ",", array: 2951, align: "c", storage: "p"},
	{oid: 3802, name: "jsonb", sqlName: "jsonb", length: -1, category: "U", delim: ",", array: 3807, align: "i", storage: "x"},

	// Array types for the supported element types.
	{oid: 199, name: "_json", sqlName: "json[]", length: -1, category: "A", delim: ",", elem: 114, align: "i", storage: "x"},
	{oid: 1000, name: "_bool", sqlName: "boolean[]", length: -1, category: "A", delim: ",", elem: 16, align: "i", storage: "x"},
	{oid: 1001, name: "_bytea", sqlName: "bytea[]", length: -1, category: "A", delim: ",", elem: 17, align: "i", storage: "x"},
	{oid: 1002, name: "_char", sqlName: "\"char\"[]", length: -1, category: "A", delim: ",", elem: 18, align: "i", storage: "x"},
	{oid: 1003, name: "_name", sqlName: "name[]", length: -1, category: "A", delim: ",", elem: 19, align: "i", storage: "x"},
	{oid: 1005, name: "_int2", sqlName: "smallint[]", length: -1, category: "A", delim: ",", elem: 21, align: "i", storage: "x"},
	{oid: 1007, name: "_int4", sqlName: "integer[]", length: -1, category: "A", delim: ",", elem: 23, align: "i", storage: "x"},
	{oid: 1009, name: "_text", sqlName: "text[]", length: -1, category: "A", delim: ",", elem: 25, align: "i", storage: "x"},
	{oid: 1014, name: "_bpchar", sqlName: "character[]", length: -1, category: "A", delim: ",", elem: 1042, align: "i", storage: "x"},
	{oid: 1015, name: "_varchar", sqlName: "character varying[]", length: -1, category: "A", delim: ",", elem: 1043, align: "i", storage: "x"},
	{oid: 1016, name: "_int8", sqlName: "bigint[]", length: -1, category: "A", delim: ",", elem: 20, align: "i", storage: "x"},
	{oid: 1021, name: "_float4", sqlName: "real[]", length: -1, category: "A", delim: ",", elem: 700, align: "i", storage: "x"},
	{oid: 1022, name: "_float8", sqlName: "double precision[]", length: -1, category: "A", delim: ",", elem: 701, align: "i", storage: "x"},
	{oid: 1028, name: "_oid", sqlName: "oid[]", length: -1, category: "A", delim: ",", elem: 26, align: "i", storage: "x"},
	{oid: 1182, name: "_date", sqlName: "date[]", length: -1, category: "A", delim: ",", elem: 1082, align: "i", storage: "x"},
	{oid: 1183, name: "_time", sqlName: "time without time zone[]", length: -1, category: "A", delim: ",", elem: 1083, align: "i", storage: "x"},
	{oid: 1185, name: "_timestamptz", sqlName: "timestamp with time zone[]", length: -1, category: "A", delim: ",", elem: 1184, align: "i", storage: "x"},
	{oid: 1231, name: "_numeric", sqlName: "numeric[]", length: -1, category: "A", delim: ",", elem: 1700, align: "i", storage: "x"},
	{oid: 2210, name: "_regclass", sqlName: "regclass[]", length: -1, category: "A", delim: ",", elem: 2205, align: "i", storage: "x"},
	{oid: 2211, name: "_regtype", sqlName: "regtype[]", length: -1, category: "A", delim: ",", elem: 2206, align: "i", storage: "x"},
	{oid: 2951, name: "_uuid", sqlName: "uuid[]", length: -1, category: "A", delim: ",", elem: 2950, align: "i", storage: "x"},
	{oid: 3807, name: "_jsonb", sqlName: "jsonb[]", length: -1, category: "A", delim: ",", elem: 3802, align: "i", storage: "x"},
}

var pgBuiltinTypeByOID = func() map[int64]pgBuiltinType {
	index := make(map[int64]pgBuiltinType, len(pgBuiltinTypes))
	for _, t := range pgBuiltinTypes {
		index[t.oid] = t
	}
	return index
}()

var pgBuiltinTypeByName = func() map[string]pgBuiltinType {
	index := make(map[string]pgBuiltinType, len(pgBuiltinTypes))
	for _, t := range pgBuiltinTypes {
		index[t.name] = t
	}
	return index
}()

// pgTypeForDeclaredName maps an engine column type ("INTEGER", "TEXT[]", ...)
// to its PostgreSQL type row. Unknown names report TEXT rather than claiming a
// type the engine does not have.
func pgTypeForDeclaredName(name string) pgBuiltinType {
	upper := strings.ToUpper(strings.TrimSpace(name))
	if idx := strings.IndexByte(upper, '('); idx >= 0 {
		upper = strings.TrimSpace(upper[:idx])
	}
	if strings.HasSuffix(upper, "[]") {
		element := strings.TrimSpace(strings.TrimSuffix(upper, "[]"))
		base := pgTypeForDeclaredName(element)
		if array, ok := pgBuiltinTypeByOID[base.array]; ok && base.array != 0 {
			return array
		}
		if fallback, ok := pgBuiltinTypeByOID[1009]; ok {
			return fallback
		}
	}
	switch upper {
	case "INTEGER", "INT", "INT4":
		return pgBuiltinTypeByOID[23]
	case "BIGINT", "INT8":
		return pgBuiltinTypeByOID[20]
	case "SMALLINT", "INT2", "TINYINT", "MEDIUMINT":
		return pgBuiltinTypeByOID[21]
	case "TEXT", "CLOB", "NCHAR", "NVARCHAR":
		return pgBuiltinTypeByOID[25]
	case "VARCHAR", "CHARACTER VARYING":
		return pgBuiltinTypeByOID[1043]
	case "CHAR", "CHARACTER":
		return pgBuiltinTypeByOID[1042]
	case "BOOLEAN", "BOOL":
		return pgBuiltinTypeByOID[16]
	case "REAL", "FLOAT", "FLOAT4":
		return pgBuiltinTypeByOID[700]
	case "DOUBLE", "DOUBLE PRECISION", "FLOAT8":
		return pgBuiltinTypeByOID[701]
	case "NUMERIC", "DECIMAL":
		return pgBuiltinTypeByOID[1700]
	case "JSONB":
		return pgBuiltinTypeByOID[3802]
	case "JSON":
		return pgBuiltinTypeByOID[114]
	case "BLOB", "BYTEA":
		return pgBuiltinTypeByOID[17]
	case "DATETIME", "TIMESTAMP", "TIMESTAMPTZ", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITHOUT TIME ZONE":
		return pgBuiltinTypeByOID[1184]
	case "DATE":
		return pgBuiltinTypeByOID[1082]
	case "TIME", "TIME WITHOUT TIME ZONE", "TIME WITH TIME ZONE":
		return pgBuiltinTypeByOID[1083]
	case "UUID":
		return pgBuiltinTypeByOID[2950]
	case "OID":
		return pgBuiltinTypeByOID[26]
	case "NAME":
		return pgBuiltinTypeByOID[19]
	case "REGCLASS":
		return pgBuiltinTypeByOID[2205]
	case "REGTYPE":
		return pgBuiltinTypeByOID[2206]
	default:
		return pgBuiltinTypeByOID[25]
	}
}

// ---------------------------------------------------------------------------
// Snapshot

type catalogRelation struct {
	schemaName string
	name       string
	oid        int64
	kind       byte // 'r' table, 'i' index, 'v' view / virtual relation
	columns    []storage.Column
	table      *storage.Schema
	index      *storage.Index
	// primaryIndex marks the synthetic index that backs a PRIMARY KEY. The
	// engine does not persist an index object for the key, but PostgreSQL tools
	// expect pg_index/pg_constraint to point at one.
	primaryIndex bool
}

type catalogConstraint struct {
	oid      int64
	name     string
	table    *catalogRelation
	contype  byte
	indexOID int64
	columns  []string // child columns (PK/UNIQUE/FK) or empty for CHECK
	fk       *storage.ForeignKeyConstraint
	check    *storage.CheckConstraint
}

type catalogSnapshot struct {
	database string

	relations     []*catalogRelation
	relationByKey map[string]*catalogRelation
	relationByOID map[int64]*catalogRelation
	tableByName   map[string]*catalogRelation
	tableIndexes  map[string][]*catalogRelation

	constraints     []*catalogConstraint
	constraintByOID map[int64]*catalogConstraint

	rows map[string][]storage.Row

	views []string

	// Preallocated OIDs keep every catalog relation deterministic regardless of
	// the order in which row sets are materialized.
	attrdefOIDs map[string]int64
	databaseOID int64
	procOIDs    map[string]int64

	oidOwners map[int64]string
}

// catalogSnapshotForQuery builds (once per statement) and returns the catalog
// snapshot the statement observes.
func (e *Executor) catalogSnapshotForQuery() (*catalogSnapshot, error) {
	if e.schema == nil || e.session == nil {
		return nil, pgerror.Unsupported("catalog introspection is not available in a stored expression")
	}
	if e.pgCatalog != nil {
		return e.pgCatalog, nil
	}
	snapshot, err := buildCatalogSnapshot(e)
	if err != nil {
		return nil, err
	}
	e.pgCatalog = snapshot
	return snapshot, nil
}

// visibleCatalogColumns drops the engine's hidden synthetic rowid column, which
// is not a user-visible PostgreSQL attribute.
func visibleCatalogColumns(schema *storage.Schema) []storage.Column {
	columns := make([]storage.Column, 0, len(schema.Columns))
	for _, column := range schema.Columns {
		if schema.UsesSyntheticRowID() && column.Name == "_rowid_" {
			continue
		}
		columns = append(columns, column)
	}
	return columns
}

func indexCatalogColumns(index *storage.Index) []storage.Column {
	columns := make([]storage.Column, len(index.Columns))
	for i, column := range index.Columns {
		name := column.Name
		if name == "" {
			name = fmt.Sprintf("expr%d", i+1)
		}
		columns[i] = storage.Column{Name: name, Type: "TEXT", Nullable: true}
	}
	return columns
}

func relationNamespaceOID(schemaName string) int64 {
	switch schemaName {
	case pgCatalogSchemaName:
		return pgCatalogNamespaceOID
	case informationSchemaName:
		return infoSchemaNamespaceOID
	default:
		return publicNamespaceOID
	}
}

// uniqueCatalogName returns base, or base with a numeric suffix, so a synthetic
// relation name never collides with a durable index name.
func uniqueCatalogName(base string, used map[string]bool) string {
	name := base
	for suffix := 2; used[strings.ToLower(name)]; suffix++ {
		name = fmt.Sprintf("%s%d", base, suffix)
	}
	used[strings.ToLower(name)] = true
	return name
}

func buildCatalogSnapshot(e *Executor) (*catalogSnapshot, error) {
	snapshot := &catalogSnapshot{
		database:        e.schema.GetDatabaseName(),
		relationByKey:   make(map[string]*catalogRelation),
		relationByOID:   make(map[int64]*catalogRelation),
		tableByName:     make(map[string]*catalogRelation),
		tableIndexes:    make(map[string][]*catalogRelation),
		constraintByOID: make(map[int64]*catalogConstraint),
		rows:            make(map[string][]storage.Row),
		views:           sortedViewNames(e.views),
		attrdefOIDs:     make(map[string]int64),
		procOIDs:        make(map[string]int64),
		oidOwners:       make(map[int64]string),
	}
	if snapshot.database == "" {
		snapshot.database = "pizzasql"
	}

	tables, err := e.session.ListTables()
	if err != nil {
		return nil, err
	}
	sort.Strings(tables)

	indexNames, err := e.session.ListIndexes()
	if err != nil {
		return nil, err
	}
	sort.Strings(indexNames)

	var descriptors []*catalogRelation
	for _, tableName := range tables {
		schema, err := e.session.GetSchema(tableName)
		if err != nil {
			return nil, fmt.Errorf("pg_catalog: resolve table %q: %w", tableName, err)
		}
		descriptors = append(descriptors, &catalogRelation{
			schemaName: publicSchemaName,
			name:       schema.Name,
			kind:       'r',
			columns:    visibleCatalogColumns(schema),
			table:      schema,
		})
	}

	usedNames := make(map[string]bool, len(indexNames))
	for _, indexName := range indexNames {
		index, err := e.session.GetIndex(indexName)
		if err != nil {
			return nil, fmt.Errorf("pg_catalog: resolve index %q: %w", indexName, err)
		}
		usedNames[strings.ToLower(index.Name)] = true
		descriptors = append(descriptors, &catalogRelation{
			schemaName: publicSchemaName,
			name:       index.Name,
			kind:       'i',
			columns:    indexCatalogColumns(index),
			index:      index,
		})
	}

	// One synthetic index per primary key so pg_index/pg_constraint have a
	// stable conindid. Composite keys keep their declared order.
	for _, tableName := range tables {
		schema, err := e.session.GetSchema(tableName)
		if err != nil {
			return nil, err
		}
		// A table without a declared PRIMARY KEY is addressed by the engine's
		// synthetic _rowid_ column, which is not a PostgreSQL attribute and has
		// no index.
		if schema.UsesSyntheticRowID() {
			continue
		}
		keyColumns := schema.PrimaryKeyList()
		if len(keyColumns) == 0 {
			continue
		}
		index := &storage.Index{Name: uniqueCatalogName(schema.Name+"_pkey", usedNames), Table: schema.Name, Unique: true}
		for _, column := range keyColumns {
			index.Columns = append(index.Columns, storage.IndexColumn{Name: column})
		}
		descriptors = append(descriptors, &catalogRelation{
			schemaName:   publicSchemaName,
			name:         index.Name,
			kind:         'i',
			columns:      indexCatalogColumns(index),
			index:        index,
			primaryIndex: true,
		})
	}

	for _, def := range pgCatalogRelationDefs {
		descriptors = append(descriptors, &catalogRelation{
			schemaName: def.schema,
			name:       def.name,
			kind:       'v',
			columns:    pgCatalogRelationIndex[def.schema+"."+def.name].schema.Columns,
		})
	}
	for _, def := range informationSchemaRelationDefs {
		descriptors = append(descriptors, &catalogRelation{
			schemaName: def.schema,
			name:       def.name,
			kind:       'v',
			columns:    pgCatalogRelationIndex[def.schema+"."+def.name].schema.Columns,
		})
	}

	sort.SliceStable(descriptors, func(i, j int) bool {
		if descriptors[i].schemaName != descriptors[j].schemaName {
			return descriptors[i].schemaName < descriptors[j].schemaName
		}
		if descriptors[i].name != descriptors[j].name {
			return descriptors[i].name < descriptors[j].name
		}
		return descriptors[i].kind < descriptors[j].kind
	})

	for _, relation := range descriptors {
		relation.oid = snapshot.allocateOID("relation", relation.schemaName+"."+relation.name)
		snapshot.relations = append(snapshot.relations, relation)
		snapshot.relationByKey[strings.ToLower(relation.schemaName+"."+relation.name)] = relation
		snapshot.relationByOID[relation.oid] = relation
		if relation.kind == 'r' {
			snapshot.tableByName[strings.ToLower(relation.name)] = relation
		}
		if relation.kind == 'i' {
			tableKey := strings.ToLower(relation.index.Table)
			snapshot.tableIndexes[tableKey] = append(snapshot.tableIndexes[tableKey], relation)
		}
	}

	// Constraints are allocated after relations so conindid can point at the
	// durable or synthetic index OID.
	for _, relation := range snapshot.relations {
		if relation.kind != 'r' {
			continue
		}
		usedConstraintNames := make(map[string]bool)
		addConstraint := func(constraint *catalogConstraint) {
			constraint.table = relation
			constraint.oid = snapshot.allocateOID("constraint", relation.name+"."+constraint.name)
			snapshot.constraints = append(snapshot.constraints, constraint)
			snapshot.constraintByOID[constraint.oid] = constraint
		}

		for _, indexRelation := range snapshot.tableIndexes[strings.ToLower(relation.name)] {
			if !indexRelation.primaryIndex {
				continue
			}
			usedConstraintNames[strings.ToLower(indexRelation.name)] = true
			addConstraint(&catalogConstraint{
				name:     indexRelation.name,
				contype:  'p',
				indexOID: indexRelation.oid,
				columns:  indexColumnNames(indexRelation.index),
			})
		}
		for _, indexRelation := range snapshot.tableIndexes[strings.ToLower(relation.name)] {
			if indexRelation.primaryIndex || !indexRelation.index.Unique {
				continue
			}
			if usedConstraintNames[strings.ToLower(indexRelation.name)] {
				continue
			}
			usedConstraintNames[strings.ToLower(indexRelation.name)] = true
			addConstraint(&catalogConstraint{
				name:     indexRelation.name,
				contype:  'u',
				indexOID: indexRelation.oid,
				columns:  indexColumnNames(indexRelation.index),
			})
		}
		for i := range relation.table.ForeignKeys {
			fk := &relation.table.ForeignKeys[i]
			name := fk.Name
			if name == "" {
				name = relation.name + "_" + strings.Join(fk.Columns, "_") + "_fkey"
			}
			name = uniqueCatalogName(name, usedConstraintNames)
			addConstraint(&catalogConstraint{name: name, contype: 'f', columns: fk.Columns, fk: fk})
		}
		for i := range relation.table.Checks {
			check := &relation.table.Checks[i]
			name := check.Name
			if name == "" {
				name = relation.name + "_check"
			}
			name = uniqueCatalogName(name, usedConstraintNames)
			addConstraint(&catalogConstraint{name: name, contype: 'c', check: check})
		}
	}

	// Default expressions, the database row, and function rows are allocated in
	// sorted/declared order so their OIDs do not depend on which relation a
	// query reads first.
	for _, relation := range snapshot.relations {
		if relation.kind != 'r' {
			continue
		}
		for _, column := range relation.columns {
			if column.DefaultLiteral() == nil {
				continue
			}
			snapshot.attrdefOIDs[attrdefKey(relation.name, column.Name)] = snapshot.allocateOID("attrdef", relation.name+"."+column.Name)
		}
	}
	snapshot.databaseOID = snapshot.allocateOID("database", snapshot.database)
	for _, function := range pgCatalogFunctions {
		snapshot.procOIDs[function.name] = snapshot.allocateOID("proc", function.name)
	}

	return snapshot, nil
}

// attrdefKey identifies a column default in the snapshot's OID table.
func attrdefKey(table, column string) string {
	return strings.ToLower(table) + "." + strings.ToLower(column)
}

// sortedViewNames returns the session's view names in deterministic order.
func sortedViewNames(views map[string]*parser.SelectStmt) []string {
	names := make([]string, 0, len(views))
	for name := range views {
		names = append(names, name)
	}
	sort.Strings(names)
	return names
}

func indexColumnNames(index *storage.Index) []string {
	names := make([]string, len(index.Columns))
	for i, column := range index.Columns {
		names[i] = column.Name
	}
	return names
}

// regclassOID resolves a relation name the way a regclass literal does:
// an explicit schema or the public search path, with pg_catalog implicit.
func (s *catalogSnapshot) regclassOID(name string) (int64, bool) {
	name = strings.TrimSpace(name)
	name = strings.Trim(name, `"`)
	if name == "" {
		return 0, false
	}
	if dot := strings.IndexByte(name, '.'); dot >= 0 {
		schemaName := strings.Trim(strings.TrimSpace(name[:dot]), `"`)
		tableName := strings.Trim(strings.TrimSpace(name[dot+1:]), `"`)
		relation, ok := s.relationByKey[strings.ToLower(schemaName+"."+tableName)]
		if ok {
			return relation.oid, true
		}
		return 0, false
	}
	if relation, ok := s.relationByKey[strings.ToLower(publicSchemaName+"."+name)]; ok {
		return relation.oid, true
	}
	if relation, ok := s.relationByKey[strings.ToLower(pgCatalogSchemaName+"."+name)]; ok {
		return relation.oid, true
	}
	return 0, false
}

// regclassName renders a relation OID the way regclass output does.
func (s *catalogSnapshot) regclassName(oid int64) (string, bool) {
	relation, ok := s.relationByOID[oid]
	if !ok {
		return "", false
	}
	return relation.name, true
}

// tableRelationFor resolves a table name (unqualified or schema-qualified) to
// its public relation row.
func (s *catalogSnapshot) tableRelationFor(name string) *catalogRelation {
	name = strings.TrimSpace(name)
	if dot := strings.IndexByte(name, '.'); dot >= 0 {
		name = name[dot+1:]
	}
	return s.tableByName[strings.ToLower(name)]
}

func catalogAttributeNumbers(schema *storage.Schema) map[string]int64 {
	numbers := make(map[string]int64, len(schema.Columns))
	position := int64(0)
	for _, column := range visibleCatalogColumns(schema) {
		position++
		numbers[strings.ToLower(column.Name)] = position
	}
	return numbers
}

func oidListText(values []int64) string {
	parts := make([]string, len(values))
	for i, value := range values {
		parts[i] = strconv.FormatInt(value, 10)
	}
	return "{" + strings.Join(parts, ",") + "}"
}

func defaultExpressionText(column storage.Column) interface{} {
	value := column.DefaultLiteral()
	if value == nil {
		return nil
	}
	return sqlLiteral(value)
}

// ---------------------------------------------------------------------------
// Row builders

func (s *catalogSnapshot) relationRows(key string) []storage.Row {
	key = strings.ToLower(key)
	if rows, ok := s.rows[key]; ok {
		return rows
	}
	var rows []storage.Row
	switch key {
	case "pg_catalog.pg_namespace":
		rows = s.pgNamespaceRows()
	case "pg_catalog.pg_class":
		rows = s.pgClassRows()
	case "pg_catalog.pg_attribute":
		rows = s.pgAttributeRows()
	case "pg_catalog.pg_type":
		rows = s.pgTypeRows()
	case "pg_catalog.pg_index":
		rows = s.pgIndexRows()
	case "pg_catalog.pg_constraint":
		rows = s.pgConstraintRows()
	case "pg_catalog.pg_attrdef":
		rows = s.pgAttrdefRows()
	case "pg_catalog.pg_database":
		rows = s.pgDatabaseRows()
	case "pg_catalog.pg_tablespace":
		rows = s.pgTablespaceRows()
	case "pg_catalog.pg_proc":
		rows = s.pgProcRows()
	case "pg_catalog.pg_am":
		rows = s.pgAmRows()
	case "pg_catalog.pg_description":
		rows = []storage.Row{}
	case "pg_catalog.pg_tables":
		rows = s.pgTablesRows()
	case "pg_catalog.pg_indexes":
		rows = s.pgIndexesRows()
	case "information_schema.tables":
		rows = s.informationSchemaTablesRows()
	case "information_schema.columns":
		rows = s.informationSchemaColumnsRows()
	case "information_schema.table_constraints":
		rows = s.informationSchemaTableConstraintsRows()
	case "information_schema.key_column_usage":
		rows = s.informationSchemaKeyColumnUsageRows()
	case "information_schema.referential_constraints":
		rows = s.informationSchemaReferentialConstraintsRows()
	case "information_schema.constraint_column_usage":
		rows = s.informationSchemaConstraintColumnUsageRows()
	case "information_schema.schemata":
		rows = s.informationSchemaSchemataRows()
	case "information_schema.sequences":
		rows = []storage.Row{}
	case "information_schema.views":
		rows = s.informationSchemaViewsRows()
	default:
		rows = []storage.Row{}
	}
	s.rows[key] = rows
	return rows
}

func (s *catalogSnapshot) pgNamespaceRows() []storage.Row {
	return []storage.Row{
		{"oid": pgCatalogNamespaceOID, "nspname": pgCatalogSchemaName, "nspowner": catalogOwnerOID, "nspacl": nil},
		{"oid": publicNamespaceOID, "nspname": publicSchemaName, "nspowner": catalogOwnerOID, "nspacl": nil},
		{"oid": infoSchemaNamespaceOID, "nspname": informationSchemaName, "nspowner": catalogOwnerOID, "nspacl": nil},
	}
}

func (s *catalogSnapshot) pgClassRows() []storage.Row {
	rows := make([]storage.Row, 0, len(s.relations))
	for _, relation := range s.relations {
		hasIndex := false
		checks := int64(0)
		relfilenode := int64(0)
		relam := int64(0)
		switch relation.kind {
		case 'r':
			hasIndex = len(s.tableIndexes[strings.ToLower(relation.name)]) > 0
			checks = int64(len(relation.table.Checks))
			relfilenode = relation.oid
		case 'i':
			relfilenode = relation.oid
			relam = pgBtreeAMOID
		}
		rows = append(rows, storage.Row{
			"oid":                 relation.oid,
			"relname":             relation.name,
			"relnamespace":        relationNamespaceOID(relation.schemaName),
			"reltype":             int64(0),
			"reloftype":           int64(0),
			"relowner":            catalogOwnerOID,
			"relam":               relam,
			"relfilenode":         relfilenode,
			"reltablespace":       int64(0),
			"relpages":            int64(0),
			"reltuples":           float64(0),
			"relallvisible":       int64(0),
			"reltoastrelid":       int64(0),
			"relhasindex":         hasIndex,
			"relisshared":         false,
			"relpersistence":      "p",
			"relkind":             string(relation.kind),
			"relnatts":            int64(len(relation.columns)),
			"relchecks":           checks,
			"relhasrules":         false,
			"relhastriggers":      false,
			"relhassubclass":      false,
			"relrowsecurity":      false,
			"relforcerowsecurity": false,
			"relispopulated":      true,
			"relreplident":        "d",
			"relispartition":      false,
			"relrewrite":          int64(0),
			"relfrozenxid":        int64(0),
			"relminmxid":          int64(0),
			"relacl":              nil,
			"reloptions":          nil,
			"relpartbound":        nil,
		})
	}
	return rows
}

func (s *catalogSnapshot) relationAttributeType(relation *catalogRelation, column storage.Column) pgBuiltinType {
	if relation.kind == 'i' && relation.index != nil {
		if table := s.tableRelationFor(relation.index.Table); table != nil {
			for _, tableColumn := range table.columns {
				if strings.EqualFold(tableColumn.Name, column.Name) {
					return pgTypeForDeclaredName(tableColumn.Type)
				}
			}
		}
	}
	return pgTypeForDeclaredName(column.Type)
}

func (s *catalogSnapshot) pgAttributeRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		for i, column := range relation.columns {
			builtin := s.relationAttributeType(relation, column)
			notNull := false
			hasDefault := false
			generated := ""
			if relation.kind == 'r' {
				notNull = !column.Nullable
				hasDefault = column.DefaultLiteral() != nil
				if column.GeneratedExpr != "" {
					generated = "s"
				}
			}
			arrayDimensions := int64(0)
			if builtin.isArray() {
				arrayDimensions = 1
			}
			rows = append(rows, storage.Row{
				"attrelid":       relation.oid,
				"attname":        column.Name,
				"atttypid":       builtin.oid,
				"attstattarget":  int64(-1),
				"attlen":         builtin.length,
				"attnum":         int64(i + 1),
				"attndims":       arrayDimensions,
				"attcacheoff":    int64(-1),
				"atttypmod":      int64(-1),
				"attbyval":       builtin.byval,
				"attalign":       builtin.align,
				"attstorage":     builtin.storage,
				"attcompression": "",
				"attnotnull":     notNull,
				"atthasdef":      hasDefault,
				"atthasmissing":  false,
				"attidentity":    "",
				"attgenerated":   generated,
				"attisdropped":   false,
				"attislocal":     true,
				"attinhcount":    int64(0),
				"attcollation":   int64(0),
				"attacl":         nil,
				"attoptions":     nil,
				"attfdwoptions":  nil,
				"attmissingval":  nil,
			})
		}
	}
	return rows
}

func (s *catalogSnapshot) pgTypeRows() []storage.Row {
	rows := make([]storage.Row, 0, len(pgBuiltinTypes))
	for _, t := range pgBuiltinTypes {
		rows = append(rows, storage.Row{
			"oid":            t.oid,
			"typname":        t.name,
			"typnamespace":   pgCatalogNamespaceOID,
			"typowner":       catalogOwnerOID,
			"typlen":         t.length,
			"typbyval":       t.byval,
			"typtype":        "b",
			"typcategory":    t.category,
			"typispreferred": false,
			"typisdefined":   true,
			"typdelim":       t.delim,
			"typrelid":       int64(0),
			"typsubscript":   nil,
			"typelem":        t.elem,
			"typarray":       t.array,
			"typinput":       "-",
			"typoutput":      "-",
			"typreceive":     "-",
			"typsend":        "-",
			"typmodin":       "-",
			"typmodout":      "-",
			"typanalyze":     "-",
			"typalign":       t.align,
			"typstorage":     t.storage,
			"typnotnull":     false,
			"typbasetype":    int64(0),
			"typtypmod":      int64(-1),
			"typndims":       int64(0),
			"typcollation":   int64(0),
			"typdefaultbin":  nil,
			"typdefault":     nil,
			"typacl":         nil,
		})
	}
	return rows
}

func (s *catalogSnapshot) indexAttributeNumbers(indexRelation *catalogRelation) []int64 {
	numbers := make([]int64, len(indexRelation.index.Columns))
	table := s.tableRelationFor(indexRelation.index.Table)
	if table == nil {
		return numbers
	}
	attnums := catalogAttributeNumbers(table.table)
	for i, column := range indexRelation.index.Columns {
		if column.Name == "" {
			continue
		}
		numbers[i] = attnums[strings.ToLower(column.Name)]
	}
	return numbers
}

func (s *catalogSnapshot) pgIndexRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind != 'i' || relation.index == nil {
			continue
		}
		table := s.tableRelationFor(relation.index.Table)
		tableOID := int64(0)
		if table != nil {
			tableOID = table.oid
		}
		attnums := s.indexAttributeNumbers(relation)
		options := make([]int64, len(attnums))
		var expressions []string
		hasExpression := false
		for _, column := range relation.index.Columns {
			if column.Expression != "" {
				expressions = append(expressions, column.Expression)
				hasExpression = true
			}
		}
		var indexprs interface{}
		if hasExpression {
			indexprs = strings.Join(expressions, ", ")
		}
		rows = append(rows, storage.Row{
			"indexrelid":     relation.oid,
			"indrelid":       tableOID,
			"indnatts":       int64(len(attnums)),
			"indnkeyatts":    int64(len(attnums)),
			"indisunique":    relation.index.Unique,
			"indisprimary":   relation.primaryIndex,
			"indisexclusion": false,
			"indimmediate":   true,
			"indisclustered": false,
			"indisvalid":     true,
			"indcheckxmin":   false,
			"indisready":     true,
			"indislive":      true,
			"indisreplident": false,
			"indkey":         oidListText(attnums),
			"indcollation":   oidListText(make([]int64, len(attnums))),
			"indclass":       oidListText(make([]int64, len(attnums))),
			"indoption":      oidListText(options),
			"indexprs":       indexprs,
			"indpred":        nil,
		})
	}
	return rows
}

func constraintActionCode(action string) string {
	switch strings.ToUpper(strings.TrimSpace(action)) {
	case "CASCADE":
		return "c"
	case "RESTRICT":
		return "r"
	case "SET NULL":
		return "n"
	case "SET DEFAULT":
		return "d"
	default:
		return "a"
	}
}

func (s *catalogSnapshot) pgConstraintRows() []storage.Row {
	rows := make([]storage.Row, 0, len(s.constraints))
	for _, constraint := range s.constraints {
		var conkey interface{}
		if len(constraint.columns) > 0 {
			attnums := catalogAttributeNumbers(constraint.table.table)
			key := make([]int64, 0, len(constraint.columns))
			for _, name := range constraint.columns {
				key = append(key, attnums[strings.ToLower(name)])
			}
			conkey = oidListText(key)
		}

		var confrelid, confupdtype, confdeltype, confmatchtype, confkey interface{}
		if constraint.fk != nil {
			if parent := s.tableRelationFor(constraint.fk.RefTable); parent != nil {
				confrelid = parent.oid
				parentAttnums := catalogAttributeNumbers(parent.table)
				key := make([]int64, 0, len(constraint.fk.RefColumns))
				for _, name := range constraint.fk.RefColumns {
					key = append(key, parentAttnums[strings.ToLower(name)])
				}
				confkey = oidListText(key)
			} else {
				confrelid = int64(0)
			}
			confupdtype = constraintActionCode(constraint.fk.OnUpdate)
			confdeltype = constraintActionCode(constraint.fk.OnDelete)
			confmatchtype = "s"
		}

		var conbin interface{}
		if constraint.check != nil {
			conbin = constraint.check.Expression
		}

		rows = append(rows, storage.Row{
			"oid":           constraint.oid,
			"conname":       constraint.name,
			"connamespace":  publicNamespaceOID,
			"contype":       string(constraint.contype),
			"condeferrable": false,
			"condeferred":   false,
			"convalidated":  true,
			"conrelid":      constraint.table.oid,
			"contypid":      int64(0),
			"conindid":      constraint.indexOID,
			"conparentid":   int64(0),
			"confrelid":     confrelid,
			"confupdtype":   confupdtype,
			"confdeltype":   confdeltype,
			"confmatchtype": confmatchtype,
			"conislocal":    true,
			"coninhcount":   int64(0),
			"connoinherit":  false,
			"conkey":        conkey,
			"confkey":       confkey,
			"conpfeqop":     nil,
			"conppeqop":     nil,
			"conffeqop":     nil,
			"conexclop":     nil,
			"conbin":        conbin,
			"consrc":        conbin,
		})
	}
	return rows
}

func (s *catalogSnapshot) pgAttrdefRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind != 'r' {
			continue
		}
		attnums := catalogAttributeNumbers(relation.table)
		for _, column := range relation.columns {
			text := defaultExpressionText(column)
			if text == nil {
				continue
			}
			adnum := attnums[strings.ToLower(column.Name)]
			rows = append(rows, storage.Row{
				"oid":     s.attrdefOIDs[attrdefKey(relation.name, column.Name)],
				"adrelid": relation.oid,
				"adnum":   adnum,
				"adbin":   text,
			})
		}
	}
	return rows
}

func (s *catalogSnapshot) pgDatabaseRows() []storage.Row {
	return []storage.Row{{
		"oid":            s.databaseOID,
		"datname":        s.database,
		"datdba":         catalogOwnerOID,
		"encoding":       int64(6), // UTF8
		"datlocprovider": "c",
		"datistemplate":  false,
		"datallowconn":   true,
		"datconnlimit":   int64(-1),
		"datlastsysoid":  int64(0),
		"datfrozenxid":   int64(0),
		"datminmxid":     int64(0),
		"dattablespace":  pgDefaultTablespaceOID,
		"datcollate":     "C",
		"datctype":       "C",
		"datlocale":      nil,
		"daticurules":    nil,
		"datcollversion": nil,
		"datacl":         nil,
	}}
}

func (s *catalogSnapshot) pgTablespaceRows() []storage.Row {
	return []storage.Row{
		{"oid": pgDefaultTablespaceOID, "spcname": "pg_default", "spcowner": catalogOwnerOID, "spcacl": nil, "spcoptions": nil},
		{"oid": pgGlobalTablespaceOID, "spcname": "pg_global", "spcowner": catalogOwnerOID, "spcacl": nil, "spcoptions": nil},
	}
}

// pgCatalogFunctions is the pg_proc surface the executor answers. It mirrors
// evalCatalogFunction; names not listed here never appear in pg_proc.
var pgCatalogFunctions = []struct {
	name    string
	nargs   int64
	rettype int64
}{
	{"current_database", 0, 19},
	{"current_schema", 0, 19},
	{"format_type", 2, 25},
	{"has_table_privilege", 3, 16},
	{"obj_description", 2, 25},
	{"col_description", 2, 25},
	{"pg_get_constraintdef", 2, 25},
	{"pg_get_expr", 3, 25},
	{"pg_get_indexdef", 3, 25},
	{"pg_get_serial_sequence", 2, 25},
	{"pg_get_userbyid", 1, 19},
	{"pg_table_is_visible", 1, 16},
}

func (s *catalogSnapshot) pgProcRows() []storage.Row {
	rows := make([]storage.Row, 0, len(pgCatalogFunctions))
	for _, function := range pgCatalogFunctions {
		rows = append(rows, storage.Row{
			"oid":             s.procOIDs[function.name],
			"proname":         function.name,
			"pronamespace":    pgCatalogNamespaceOID,
			"proowner":        catalogOwnerOID,
			"prolang":         int64(12), // internal
			"procost":         float64(1),
			"prorows":         float64(0),
			"provariadic":     int64(0),
			"prosupport":      nil,
			"prokind":         "f",
			"prosecdef":       false,
			"proleakproof":    false,
			"proisstrict":     true,
			"proretset":       false,
			"provolatile":     "s",
			"proparallel":     "s",
			"pronargs":        function.nargs,
			"pronargdefaults": int64(0),
			"prorettype":      function.rettype,
			"proargtypes":     "",
			"proallargtypes":  nil,
			"proargmodes":     nil,
			"proargnames":     nil,
			"proargdefaults":  nil,
			"protrftypes":     nil,
			"prosrc":          function.name,
			"probin":          nil,
			"prosqlbody":      nil,
			"proconfig":       nil,
			"proacl":          nil,
		})
	}
	return rows
}

func (s *catalogSnapshot) pgAmRows() []storage.Row {
	return []storage.Row{
		{"oid": pgHeapAMOID, "amname": "heap", "amhandler": int64(0), "amtype": "t"},
		{"oid": pgBtreeAMOID, "amname": "btree", "amhandler": int64(0), "amtype": "i"},
	}
}

func (s *catalogSnapshot) pgTablesRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind != 'r' {
			continue
		}
		hasIndex := len(s.tableIndexes[strings.ToLower(relation.name)]) > 0
		rows = append(rows, storage.Row{
			"schemaname":  relation.schemaName,
			"tablename":   relation.name,
			"tableowner":  "pizzasql",
			"tablespace":  nil,
			"hasindexes":  hasIndex,
			"hasrules":    false,
			"hastriggers": false,
			"rowsecurity": false,
		})
	}
	return rows
}

func (s *catalogSnapshot) pgIndexesRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind != 'i' || relation.index == nil {
			continue
		}
		rows = append(rows, storage.Row{
			"schemaname": relation.schemaName,
			"tablename":  relation.index.Table,
			"indexname":  relation.name,
			"tablespace": nil,
			"indexdef":   s.indexDefinition(relation.index),
		})
	}
	return rows
}

// ---------------------------------------------------------------------------
// information_schema row builders

func (s *catalogSnapshot) informationSchemaTablesRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind == 'i' {
			continue
		}
		tableType := "BASE TABLE"
		if relation.kind != 'r' {
			tableType = "VIEW"
		}
		insertable := "NO"
		if relation.kind == 'r' {
			insertable = "YES"
		}
		rows = append(rows, storage.Row{
			"table_catalog":                s.database,
			"table_schema":                 relation.schemaName,
			"table_name":                   relation.name,
			"table_type":                   tableType,
			"self_referencing_column_name": nil,
			"reference_generation":         nil,
			"user_defined_type_catalog":    nil,
			"user_defined_type_schema":     nil,
			"user_defined_type_name":       nil,
			"is_insertable_into":           insertable,
			"is_typed":                     "NO",
			"commit_action":                nil,
		})
	}
	for _, view := range s.views {
		rows = append(rows, storage.Row{
			"table_catalog":                s.database,
			"table_schema":                 publicSchemaName,
			"table_name":                   view,
			"table_type":                   "VIEW",
			"self_referencing_column_name": nil,
			"reference_generation":         nil,
			"user_defined_type_catalog":    nil,
			"user_defined_type_schema":     nil,
			"user_defined_type_name":       nil,
			"is_insertable_into":           "NO",
			"is_typed":                     "NO",
			"commit_action":                nil,
		})
	}
	return rows
}

func informationSchemaColumn(relation *catalogRelation, column storage.Column, position int) storage.Row {
	builtin := pgTypeForDeclaredName(column.Type)
	dataType := builtin.sqlName
	if builtin.isArray() {
		dataType = "ARRAY"
	}
	generated := "NEVER"
	generationExpression := interface{}(nil)
	if column.GeneratedExpr != "" {
		generated = "ALWAYS"
		generationExpression = column.GeneratedExpr
	}
	return storage.Row{
		"table_catalog":            "", // filled by the caller
		"table_schema":             relation.schemaName,
		"table_name":               relation.name,
		"column_name":              column.Name,
		"ordinal_position":         int64(position),
		"column_default":           defaultExpressionText(column),
		"is_nullable":              map[bool]string{true: "YES", false: "NO"}[column.Nullable],
		"data_type":                dataType,
		"character_maximum_length": nil,
		"character_octet_length":   nil,
		"numeric_precision":        nil,
		"numeric_precision_radix":  nil,
		"numeric_scale":            nil,
		"datetime_precision":       nil,
		"interval_type":            nil,
		"interval_precision":       nil,
		"character_set_catalog":    nil,
		"character_set_schema":     nil,
		"character_set_name":       nil,
		"collation_catalog":        nil,
		"collation_schema":         nil,
		"collation_name":           nil,
		"domain_catalog":           nil,
		"domain_schema":            nil,
		"domain_name":              nil,
		"udt_catalog":              "", // filled by the caller
		"udt_schema":               pgCatalogSchemaName,
		"udt_name":                 builtin.name,
		"scope_catalog":            nil,
		"scope_schema":             nil,
		"scope_name":               nil,
		"maximum_cardinality":      nil,
		"dtd_identifier":           strconv.Itoa(position),
		"is_self_referencing":      "NO",
		"is_identity":              "NO",
		"identity_generation":      nil,
		"identity_start":           nil,
		"identity_increment":       nil,
		"identity_maximum":         nil,
		"identity_minimum":         nil,
		"identity_cycle":           "NO",
		"is_generated":             generated,
		"generation_expression":    generationExpression,
		"is_updatable":             "YES",
	}
}

func (s *catalogSnapshot) informationSchemaColumnsRows() []storage.Row {
	var rows []storage.Row
	for _, relation := range s.relations {
		if relation.kind != 'r' {
			continue
		}
		for i, column := range relation.columns {
			row := informationSchemaColumn(relation, column, i+1)
			row["table_catalog"] = s.database
			row["udt_catalog"] = s.database
			rows = append(rows, row)
		}
	}
	return rows
}

func constraintTypeName(contype byte) string {
	switch contype {
	case 'p':
		return "PRIMARY KEY"
	case 'u':
		return "UNIQUE"
	case 'f':
		return "FOREIGN KEY"
	case 'c':
		return "CHECK"
	default:
		return "UNKNOWN"
	}
}

func (s *catalogSnapshot) informationSchemaTableConstraintsRows() []storage.Row {
	rows := make([]storage.Row, 0, len(s.constraints))
	for _, constraint := range s.constraints {
		rows = append(rows, storage.Row{
			"constraint_catalog": s.database,
			"constraint_schema":  publicSchemaName,
			"constraint_name":    constraint.name,
			"table_catalog":      s.database,
			"table_schema":       publicSchemaName,
			"table_name":         constraint.table.name,
			"constraint_type":    constraintTypeName(constraint.contype),
			"is_deferrable":      "NO",
			"initially_deferred": "NO",
			"enforced":           "YES",
			"nulls_distinct":     nil,
		})
	}
	return rows
}

func (s *catalogSnapshot) informationSchemaKeyColumnUsageRows() []storage.Row {
	var rows []storage.Row
	for _, constraint := range s.constraints {
		var columns []string
		switch constraint.contype {
		case 'p', 'u':
			columns = constraint.columns
		case 'f':
			columns = constraint.fk.Columns
		default:
			continue
		}
		for i, column := range columns {
			var position interface{}
			if constraint.contype == 'f' {
				position = int64(i + 1)
			}
			rows = append(rows, storage.Row{
				"constraint_catalog":            s.database,
				"constraint_schema":             publicSchemaName,
				"constraint_name":               constraint.name,
				"table_catalog":                 s.database,
				"table_schema":                  publicSchemaName,
				"table_name":                    constraint.table.name,
				"column_name":                   column,
				"ordinal_position":              int64(i + 1),
				"position_in_unique_constraint": position,
			})
		}
	}
	return rows
}

func (s *catalogSnapshot) uniqueConstraintFor(parent *catalogRelation, columns []string) string {
	for _, constraint := range s.constraints {
		if constraint.table != parent || (constraint.contype != 'p' && constraint.contype != 'u') {
			continue
		}
		keyColumns := constraint.columns
		if constraint.contype == 'u' && constraint.indexOID != 0 {
			for _, indexRelation := range s.relations {
				if indexRelation.oid == constraint.indexOID && indexRelation.index != nil {
					keyColumns = indexColumnNames(indexRelation.index)
					break
				}
			}
		}
		if len(keyColumns) != len(columns) {
			continue
		}
		matches := true
		for i := range columns {
			if !strings.EqualFold(keyColumns[i], columns[i]) {
				matches = false
				break
			}
		}
		if matches {
			return constraint.name
		}
	}
	return ""
}

func referentialRule(action string) string {
	if strings.TrimSpace(action) == "" {
		return "NO ACTION"
	}
	return strings.ToUpper(strings.TrimSpace(action))
}

func (s *catalogSnapshot) informationSchemaReferentialConstraintsRows() []storage.Row {
	var rows []storage.Row
	for _, constraint := range s.constraints {
		if constraint.contype != 'f' || constraint.fk == nil {
			continue
		}
		parent := s.tableRelationFor(constraint.fk.RefTable)
		var uniqueName interface{}
		if parent != nil {
			if name := s.uniqueConstraintFor(parent, constraint.fk.RefColumns); name != "" {
				uniqueName = name
			}
		}
		rows = append(rows, storage.Row{
			"constraint_catalog":        s.database,
			"constraint_schema":         publicSchemaName,
			"constraint_name":           constraint.name,
			"unique_constraint_catalog": s.database,
			"unique_constraint_schema":  publicSchemaName,
			"unique_constraint_name":    uniqueName,
			"match_option":              "NONE",
			"update_rule":               referentialRule(constraint.fk.OnUpdate),
			"delete_rule":               referentialRule(constraint.fk.OnDelete),
			"is_deferrable":             "NO",
			"initially_deferred":        "NO",
			"enforced":                  "YES",
		})
	}
	return rows
}

func (s *catalogSnapshot) informationSchemaConstraintColumnUsageRows() []storage.Row {
	var rows []storage.Row
	for _, constraint := range s.constraints {
		switch constraint.contype {
		case 'f':
			if constraint.fk == nil {
				continue
			}
			for _, column := range constraint.fk.RefColumns {
				rows = append(rows, storage.Row{
					"table_catalog":      s.database,
					"table_schema":       publicSchemaName,
					"table_name":         constraint.fk.RefTable,
					"constraint_catalog": s.database,
					"constraint_schema":  publicSchemaName,
					"constraint_name":    constraint.name,
					"column_name":        column,
				})
			}
		case 'c':
			if constraint.check == nil {
				continue
			}
			expr, err := parser.ParseExpr(constraint.check.Expression)
			if err != nil {
				continue
			}
			for _, column := range collectColumnRefs(expr) {
				rows = append(rows, storage.Row{
					"table_catalog":      s.database,
					"table_schema":       publicSchemaName,
					"table_name":         constraint.table.name,
					"constraint_catalog": s.database,
					"constraint_schema":  publicSchemaName,
					"constraint_name":    constraint.name,
					"column_name":        column,
				})
			}
		}
	}
	return rows
}

func (s *catalogSnapshot) informationSchemaSchemataRows() []storage.Row {
	return []storage.Row{
		{"catalog_name": s.database, "schema_name": pgCatalogSchemaName, "schema_owner": "pizzasql", "default_character_set_catalog": nil, "default_character_set_schema": nil, "default_character_set_name": nil, "sql_path": nil},
		{"catalog_name": s.database, "schema_name": publicSchemaName, "schema_owner": "pizzasql", "default_character_set_catalog": nil, "default_character_set_schema": nil, "default_character_set_name": nil, "sql_path": nil},
		{"catalog_name": s.database, "schema_name": informationSchemaName, "schema_owner": "pizzasql", "default_character_set_catalog": nil, "default_character_set_schema": nil, "default_character_set_name": nil, "sql_path": nil},
	}
}

// catalogSchemaByName resolves an unqualified pg_catalog relation name that is
// not shadowed by a durable table.
func (e *Executor) catalogSchemaByName(name string) (*storage.Schema, bool) {
	entry, ok := pgCatalogRelationIndex[pgCatalogSchemaName+"."+strings.ToLower(name)]
	if !ok || e.session.TableExists(name) {
		return nil, false
	}
	return entry.schema, true
}

func (s *catalogSnapshot) informationSchemaViewsRows() []storage.Row {
	// Views are session-local and the engine does not retain their SQL text, so
	// information_schema.views lists them with an unknown definition.
	var rows []storage.Row
	for _, view := range s.views {
		rows = append(rows, storage.Row{
			"table_catalog":              s.database,
			"table_schema":               publicSchemaName,
			"table_name":                 view,
			"view_definition":            nil,
			"check_option":               "NONE",
			"is_updatable":               "NO",
			"is_insertable_into":         "NO",
			"is_trigger_updatable":       "NO",
			"is_trigger_deletable":       "NO",
			"is_trigger_insertable_into": "NO",
		})
	}
	return rows
}

// ---------------------------------------------------------------------------
// Function/relation helpers used by the executor's expression evaluator

// indexDefinition renders CREATE INDEX statement text, used by pg_indexes and
// pg_get_indexdef.
func (s *catalogSnapshot) indexDefinition(index *storage.Index) string {
	var builder strings.Builder
	builder.WriteString("CREATE ")
	if index.Unique {
		builder.WriteString("UNIQUE ")
	}
	builder.WriteString("INDEX ")
	builder.WriteString(index.Name)
	builder.WriteString(" ON ")
	builder.WriteString(publicSchemaName)
	builder.WriteByte('.')
	builder.WriteString(index.Table)
	builder.WriteString(" USING btree (")
	for i, column := range index.Columns {
		if i > 0 {
			builder.WriteString(", ")
		}
		if column.Expression != "" {
			builder.WriteString("(")
			builder.WriteString(column.Expression)
			builder.WriteString(")")
			continue
		}
		builder.WriteString(column.Name)
		if column.Desc {
			builder.WriteString(" DESC")
		}
	}
	builder.WriteString(")")
	return builder.String()
}

func (s *catalogSnapshot) indexDefinitionForOID(oid int64) (string, bool) {
	relation, ok := s.relationByOID[oid]
	if !ok || relation.kind != 'i' || relation.index == nil {
		return "", false
	}
	return s.indexDefinition(relation.index), true
}

func (s *catalogSnapshot) constraintDefinition(constraint *catalogConstraint) string {
	switch constraint.contype {
	case 'p':
		return "PRIMARY KEY (" + strings.Join(constraint.columns, ", ") + ")"
	case 'u':
		return "UNIQUE (" + strings.Join(constraint.columns, ", ") + ")"
	case 'f':
		if constraint.fk == nil {
			return ""
		}
		clause := "FOREIGN KEY (" + strings.Join(constraint.fk.Columns, ", ") + ") REFERENCES " + constraint.fk.RefTable + "(" + strings.Join(constraint.fk.RefColumns, ", ") + ")"
		if constraint.fk.OnDelete != "" {
			clause += " ON DELETE " + strings.ToUpper(constraint.fk.OnDelete)
		}
		if constraint.fk.OnUpdate != "" {
			clause += " ON UPDATE " + strings.ToUpper(constraint.fk.OnUpdate)
		}
		return clause
	case 'c':
		if constraint.check == nil {
			return ""
		}
		return "CHECK ((" + constraint.check.Expression + "))"
	default:
		return ""
	}
}

func (s *catalogSnapshot) constraintDefinitionForOID(oid int64) (string, bool) {
	constraint, ok := s.constraintByOID[oid]
	if !ok {
		return "", false
	}
	return s.constraintDefinition(constraint), true
}

// formatType renders a type OID the way format_type() does. Precision modifiers
// are not persisted by the engine, so callers pass -1.
func (s *catalogSnapshot) formatType(oid int64, typmod int64) (string, bool) {
	builtin, ok := pgBuiltinTypeByOID[oid]
	if !ok {
		return "???", false
	}
	name := builtin.sqlName
	if typmod < 0 {
		return name, true
	}
	switch oid {
	case 1043, 1042: // varchar, bpchar
		return fmt.Sprintf("%s(%d)", name, typmod-4), true
	case 1700: // numeric
		modifier := typmod - 4
		precision := (modifier >> 16) & 0xffff
		scale := modifier & 0xffff
		return fmt.Sprintf("numeric(%d,%d)", precision, scale), true
	default:
		return name, true
	}
}

// typeOIDForName resolves a regtype literal.
func (s *catalogSnapshot) typeOIDForName(name string) (int64, bool) {
	name = strings.TrimSpace(name)
	name = strings.Trim(name, `"`)
	upper := strings.ToUpper(name)
	if strings.HasSuffix(upper, "[]") || strings.HasSuffix(upper, " ARRAY") {
		elementName := strings.TrimSuffix(strings.TrimSuffix(upper, " ARRAY"), "[]")
		element := pgTypeForDeclaredName(elementName)
		if array, ok := pgBuiltinTypeByOID[element.array]; ok && element.array != 0 {
			return array.oid, true
		}
		return 0, false
	}
	if !knownDeclaredTypeName(upper) {
		if builtin, ok := pgBuiltinTypeByName[strings.ToLower(name)]; ok {
			return builtin.oid, true
		}
		return 0, false
	}
	return pgTypeForDeclaredName(upper).oid, true
}

// knownDeclaredTypeName reports whether a type spelling maps to a built-in.
func knownDeclaredTypeName(upper string) bool {
	switch upper {
	case "INTEGER", "INT", "INT4", "BIGINT", "INT8", "SMALLINT", "INT2", "TINYINT", "MEDIUMINT",
		"TEXT", "CLOB", "NCHAR", "NVARCHAR", "VARCHAR", "CHARACTER VARYING", "CHAR", "CHARACTER",
		"BOOLEAN", "BOOL", "REAL", "FLOAT", "FLOAT4", "DOUBLE", "DOUBLE PRECISION", "FLOAT8",
		"NUMERIC", "DECIMAL", "JSONB", "JSON", "BLOB", "BYTEA", "DATETIME", "TIMESTAMP",
		"TIMESTAMPTZ", "TIMESTAMP WITH TIME ZONE", "TIMESTAMP WITHOUT TIME ZONE", "DATE",
		"TIME", "TIME WITHOUT TIME ZONE", "TIME WITH TIME ZONE", "UUID", "OID", "NAME",
		"REGCLASS", "REGTYPE":
		return true
	default:
		return false
	}
}

func (s *catalogSnapshot) typeNameForOID(oid int64) (string, bool) {
	builtin, ok := pgBuiltinTypeByOID[oid]
	if !ok {
		return "", false
	}
	return builtin.sqlName, true
}

func (s *catalogSnapshot) relationVisible(oid int64) bool {
	relation, ok := s.relationByOID[oid]
	if !ok {
		return false
	}
	switch relation.schemaName {
	case publicSchemaName, pgCatalogSchemaName, informationSchemaName:
		return true
	default:
		return false
	}
}

// numericArg converts a function argument that denotes an OID.
func numericArg(value interface{}) (int64, bool) {
	switch v := value.(type) {
	case RegOID:
		return v.oid, true
	case int64:
		return v, true
	case int:
		return int64(v), true
	case float64:
		return int64(v), true
	case string:
		parsed, err := strconv.ParseInt(strings.TrimSpace(v), 10, 64)
		if err != nil {
			return 0, false
		}
		return parsed, true
	default:
		return 0, false
	}
}

// RegOID is the value of a regclass or regtype cast. Numeric contexts use the
// OID; text output renders the resolved name, matching PostgreSQL's symbolic
// type rendering.
type RegOID struct {
	oid  int64
	name string
}

// OID returns the numeric OID the value carries.
func (r RegOID) OID() int64 { return r.oid }

// String renders the symbolic name.
func (r RegOID) String() string { return r.name }

// evalCatalogFunction answers the PostgreSQL catalog-introspection builtins.
// handled is false for every other function so evalFunctionCall keeps them.
func (e *Executor) evalCatalogFunction(name string, fn *parser.FunctionCall, args []interface{}) (interface{}, bool, error) {
	switch name {
	case "CURRENT_SCHEMA":
		return e.currentSchemaName(), true, nil
	case "CURRENT_DATABASE":
		if e.schema == nil {
			return "pizzasql", true, nil
		}
		database := e.schema.GetDatabaseName()
		if database == "" {
			database = "pizzasql"
		}
		return database, true, nil
	case "PG_GET_SERIAL_SEQUENCE", "OBJ_DESCRIPTION", "COL_DESCRIPTION":
		// The engine has no sequences and stores no catalog comments.
		return nil, true, nil
	case "PG_GET_USERBYID":
		return "pizzasql", true, nil
	case "CURRENT_SETTING":
		if len(args) == 0 || args[0] == nil {
			return nil, true, nil
		}
		value, err := e.settingValue(toString(args[0]))
		if err != nil {
			return nil, true, err
		}
		return value, true, nil
	case "PG_GET_EXPR":
		if len(args) == 0 || args[0] == nil {
			return nil, true, nil
		}
		if text, ok := args[0].(string); ok {
			return text, true, nil
		}
		return toString(args[0]), true, nil
	}

	switch name {
	case "FORMAT_TYPE", "PG_TABLE_IS_VISIBLE", "PG_GET_INDEXDEF", "PG_GET_CONSTRAINTDEF", "HAS_TABLE_PRIVILEGE":
	default:
		return nil, false, nil
	}

	snapshot, err := e.catalogSnapshotForQuery()
	if err != nil {
		return nil, true, err
	}

	switch name {
	case "FORMAT_TYPE":
		if len(args) == 0 {
			return nil, true, pgerror.Unsupported("format_type requires a type OID")
		}
		oid, ok := numericArg(args[0])
		if !ok {
			return nil, true, pgerror.InvalidText("format_type requires a numeric type OID")
		}
		typmod := int64(-1)
		if len(args) > 1 && args[1] != nil {
			if value, ok := numericArg(args[1]); ok {
				typmod = value
			}
		}
		rendered, _ := snapshot.formatType(oid, typmod)
		return rendered, true, nil
	case "PG_TABLE_IS_VISIBLE":
		if len(args) == 0 || args[0] == nil {
			return nil, true, nil
		}
		oid, ok := numericArg(args[0])
		if !ok {
			return false, true, nil
		}
		return snapshot.relationVisible(oid), true, nil
	case "PG_GET_INDEXDEF":
		if len(args) == 0 || args[0] == nil {
			return nil, true, nil
		}
		oid, ok := numericArg(args[0])
		if !ok {
			return nil, true, pgerror.InvalidText("pg_get_indexdef requires an index OID")
		}
		definition, found := snapshot.indexDefinitionForOID(oid)
		if !found {
			return nil, true, nil
		}
		return definition, true, nil
	case "PG_GET_CONSTRAINTDEF":
		if len(args) == 0 || args[0] == nil {
			return nil, true, nil
		}
		oid, ok := numericArg(args[0])
		if !ok {
			return nil, true, pgerror.InvalidText("pg_get_constraintdef requires a constraint OID")
		}
		definition, found := snapshot.constraintDefinitionForOID(oid)
		if !found {
			return nil, true, nil
		}
		return definition, true, nil
	case "HAS_TABLE_PRIVILEGE":
		tableArg := interface{}(nil)
		switch len(args) {
		case 2:
			tableArg = args[0]
		case 3:
			tableArg = args[1]
		default:
			return false, true, nil
		}
		if tableArg == nil {
			return nil, true, nil
		}
		if oid, ok := numericArg(tableArg); ok {
			return snapshot.relationVisible(oid), true, nil
		}
		_, ok := snapshot.regclassOID(toString(tableArg))
		return ok, true, nil
	default:
		return nil, false, nil
	}
}

// currentSchemaName reports the first schema in the session search path. The
// engine only materializes public, pg_catalog, and information_schema, so a
// search path naming anything else falls back to public.
func (e *Executor) currentSchemaName() string {
	if len(e.searchPath) > 0 && e.searchPath[0] != "" {
		return e.searchPath[0]
	}
	return publicSchemaName
}

// catalogCast handles regclass/regtype casts and text casts of their values.
func (e *Executor) catalogCast(expr *parser.CastExpr, value interface{}) (interface{}, bool, error) {
	typeName := strings.ToUpper(strings.TrimSpace(expr.Type.Name))
	switch typeName {
	case "REGCLASS", "REGTYPE":
		snapshot, err := e.catalogSnapshotForQuery()
		if err != nil {
			return nil, true, err
		}
		if reg, ok := value.(RegOID); ok {
			return reg, true, nil
		}
		if text, ok := value.(string); ok {
			if typeName == "REGTYPE" {
				oid, found := snapshot.typeOIDForName(text)
				if !found {
					return nil, true, fmt.Errorf("type %q does not exist", text)
				}
				name, _ := snapshot.typeNameForOID(oid)
				return RegOID{oid: oid, name: name}, true, nil
			}
			oid, found := snapshot.regclassOID(text)
			if !found {
				return nil, true, fmt.Errorf("relation %q does not exist", text)
			}
			name, _ := snapshot.regclassName(oid)
			return RegOID{oid: oid, name: name}, true, nil
		}
		oid, ok := numericArg(value)
		if !ok {
			return nil, true, pgerror.InvalidText("cannot cast %T to %s", value, typeName)
		}
		var name string
		if typeName == "REGTYPE" {
			name, _ = snapshot.typeNameForOID(oid)
		} else {
			name, _ = snapshot.regclassName(oid)
		}
		if name == "" {
			name = strconv.FormatInt(oid, 10)
		}
		return RegOID{oid: oid, name: name}, true, nil
	case "TEXT", "VARCHAR", "CHAR", "CHARACTER", "CLOB", "NCHAR", "NVARCHAR":
		if reg, ok := value.(RegOID); ok {
			return reg.name, true, nil
		}
	case "OID":
		if reg, ok := value.(RegOID); ok {
			return reg.oid, true, nil
		}
	}
	return nil, false, nil
}

// isCatalogWriteTarget reports a DML/DDL statement aimed at a virtual relation.
func (e *Executor) rejectCatalogWriteTarget(stmt parser.Statement) error {
	var refs []parser.TableRef
	switch s := stmt.(type) {
	case *parser.InsertStmt:
		if s.Table != nil {
			refs = append(refs, *s.Table)
		}
	case *parser.UpdateStmt:
		if s.Table != nil {
			refs = append(refs, *s.Table)
		}
	case *parser.DeleteStmt:
		if s.Table != nil {
			refs = append(refs, *s.Table)
		}
	case *parser.CreateTableStmt:
		if s.Table != nil {
			refs = append(refs, *s.Table)
		}
	case *parser.CreateIndexStmt:
		refs = append(refs, parser.TableRef{Name: s.Table})
	case *parser.AlterTableStmt:
		refs = append(refs, parser.TableRef{Name: s.Table})
	case *parser.CreateViewStmt:
		if s.View != nil {
			refs = append(refs, *s.View)
		}
	case *parser.DropTableStmt:
		for _, table := range s.Tables {
			if table != nil {
				refs = append(refs, *table)
			}
		}
	}
	for _, ref := range refs {
		if _, ok := e.catalogRelationFor(ref); !ok {
			continue
		}
		schemaName := strings.ToLower(ref.Schema)
		if schemaName == "" || schemaName == publicSchemaName {
			schemaName = pgCatalogSchemaName
		}
		return pgerror.Unsupported("%s.%s is a read-only virtual relation; catalog relations are synthesized from the durable schema and cannot be written", schemaName, strings.ToLower(ref.Name))
	}
	return nil
}

// ---------------------------------------------------------------------------
// SELECT over virtual catalog relations

// catalogSelectRelation is the column metadata of one FROM entry.
type catalogSelectRelation struct {
	qualifier string
	columns   []storage.Column
}

// catalogFlatRefs flattens a FROM clause, including JOIN chains, preserving the
// schema qualifier of every entry.
func catalogFlatRefs(from []parser.TableRef) []parser.TableRef {
	var refs []parser.TableRef
	for _, ref := range from {
		cur := ref
		for {
			flat := cur
			flat.Join = nil
			flat.Subquery = cur.Subquery
			flat.Function = cur.Function
			refs = append(refs, flat)
			if cur.Join == nil || cur.Join.Table == nil {
				break
			}
			cur = *cur.Join.Table
		}
	}
	return refs
}

func catalogRefQualifier(ref parser.TableRef) string {
	if ref.Alias != "" {
		return ref.Alias
	}
	return ref.Name
}

// catalogSelectMetadata resolves the column list of every FROM relation so
// SELECT * and table.* expand, and so projection types stay accurate.
func (e *Executor) catalogSelectMetadata(from []parser.TableRef) ([]catalogSelectRelation, error) {
	var relations []catalogSelectRelation
	for _, ref := range catalogFlatRefs(from) {
		if ref.Subquery != nil || ref.Function != nil {
			continue
		}
		var columns []storage.Column
		if entry, ok := e.catalogRelationFor(ref); ok {
			columns = entry.schema.Columns
		} else if cte, ok := e.cteTableFor(ref.Name); ok {
			columns = schemaFromColumns(cte.columns).Columns
		} else {
			schema, err := e.session.GetSchema(ref.Name)
			if err != nil {
				return nil, err
			}
			columns = visibleCatalogColumns(schema)
		}
		relations = append(relations, catalogSelectRelation{qualifier: catalogRefQualifier(ref), columns: columns})
	}
	return relations, nil
}

// materializeCatalogTableRef reads one FROM entry. Catalog relations come from
// the statement's virtual snapshot; everything else goes through the ordinary
// materializer so a catalog query can still join a real table or read a
// derived table.
func (e *Executor) materializeCatalogTableRef(ref parser.TableRef) ([]storage.Row, error) {
	if entry, ok := e.catalogRelationFor(ref); ok {
		snapshot, err := e.catalogSnapshotForQuery()
		if err != nil {
			return nil, err
		}
		rows := cloneRows(snapshot.relationRows(entry.def.schema + "." + entry.def.name))
		alias := catalogRefQualifier(ref)
		for i := range rows {
			rows[i] = e.addTableAlias(rows[i], alias)
		}
		return rows, nil
	}
	if cte, ok := e.cteTableFor(ref.Name); ok {
		rows := cloneRows(cte.rows)
		alias := catalogRefQualifier(ref)
		for i := range rows {
			rows[i] = e.addTableAlias(rows[i], alias)
		}
		return rows, nil
	}
	return e.materializeTableRef(ref)
}

// materializeCatalogFrom materializes the whole FROM list, applying each
// entry's JOIN chain and cross-joining comma-separated entries.
func (e *Executor) materializeCatalogFrom(from []parser.TableRef) ([]storage.Row, error) {
	rows, err := e.materializeCatalogTableRef(from[0])
	if err != nil {
		return nil, err
	}
	if from[0].Join != nil {
		rows, err = e.executeJoins(from[0], rows)
		if err != nil {
			return nil, err
		}
	}
	for _, ref := range from[1:] {
		right, err := e.materializeCatalogTableRef(ref)
		if err != nil {
			return nil, err
		}
		if ref.Join != nil {
			right, err = e.executeJoins(ref, right)
			if err != nil {
				return nil, err
			}
		}
		joined := make([]storage.Row, 0, len(rows)*len(right))
		for _, leftRow := range rows {
			for _, rightRow := range right {
				combined := make(storage.Row, len(leftRow)+len(rightRow))
				for key, value := range leftRow {
					combined[key] = value
				}
				for key, value := range rightRow {
					combined[key] = value
				}
				joined = append(joined, combined)
			}
		}
		rows = joined
	}
	return rows, nil
}

// expandCatalogTableStars rewrites table.* projections into explicit column
// references so the shared materialized projection can read qualified keys.
func expandCatalogTableStars(stmt *parser.SelectStmt, relations []catalogSelectRelation) (*parser.SelectStmt, error) {
	expanded := *stmt
	if len(stmt.Columns) == 0 {
		return &expanded, nil
	}
	columns := make([]parser.SelectColumn, 0, len(stmt.Columns))
	for _, projection := range stmt.Columns {
		if projection.TableStar == "" {
			columns = append(columns, projection)
			continue
		}
		var matched *catalogSelectRelation
		for i := range relations {
			if strings.EqualFold(relations[i].qualifier, projection.TableStar) {
				matched = &relations[i]
				break
			}
		}
		if matched == nil {
			return nil, fmt.Errorf("cannot expand %s.*: relation columns are not available", projection.TableStar)
		}
		for _, column := range matched.columns {
			columns = append(columns, parser.SelectColumn{
				Expr:  &parser.ColumnRef{Table: matched.qualifier, Column: column.Name},
				Alias: column.Name,
			})
		}
	}
	expanded.Columns = columns
	return &expanded, nil
}

func catalogJoinKeyParts(key string) (string, string) {
	if dot := strings.LastIndexByte(key, '.'); dot >= 0 {
		return key[:dot], key[dot+1:]
	}
	return "", key
}

// catalogProjectionTypes computes result types parallel to the projection. The
// resolver follows starKeys, so both qualified and unqualified references keep
// the declared catalog type (OID, NAME, BOOL, ...) instead of falling back to
// TEXT over the wire.
func catalogProjectionTypes(stmt *parser.SelectStmt, starKeys, types []string) []string {
	resolve := func(table, column string) (string, bool) {
		for i, key := range starKeys {
			qualifier, name := catalogJoinKeyParts(key)
			if !strings.EqualFold(name, column) {
				continue
			}
			if table != "" && !strings.EqualFold(table, qualifier) {
				continue
			}
			if i < len(types) && types[i] != "" {
				return types[i], true
			}
			return "", true
		}
		return "", false
	}
	result := make([]string, 0, len(stmt.Columns))
	for _, projection := range stmt.Columns {
		if projection.Star {
			result = append(result, types...)
			continue
		}
		result = append(result, inferExprType(projection.Expr, resolve))
	}
	return result
}

// executeSelectOnCatalogRelations answers a SELECT whose FROM clause reads at
// least one virtual relation. Rows are materialized from the session's schema
// view, so staged transactional DDL is visible to the issuing connection only.
func (e *Executor) executeSelectOnCatalogRelations(stmt *parser.SelectStmt) (*Result, error) {
	relations, err := e.catalogSelectMetadata(stmt.From)
	if err != nil {
		return nil, err
	}
	expanded, err := expandCatalogTableStars(stmt, relations)
	if err != nil {
		return nil, err
	}

	rows, err := e.materializeCatalogFrom(stmt.From)
	if err != nil {
		return nil, err
	}

	var columns, starKeys, types, qualifiers []string
	for _, relation := range relations {
		for _, column := range relation.columns {
			columns = append(columns, column.Name)
			starKeys = append(starKeys, relation.qualifier+"."+column.Name)
			types = append(types, column.Type)
			qualifiers = append(qualifiers, relation.qualifier)
		}
	}

	// The JOIN chain is already applied by materializeCatalogFrom; hand the
	// shared materialized path rows without a pending join.
	tail := *expanded
	tailFrom := make([]parser.TableRef, len(expanded.From))
	copy(tailFrom, expanded.From)
	tailFrom[0].Join = nil
	tail.From = tailFrom

	e.pushTypeScope(materializedTypeResolver(columns, types, qualifiers))
	result, err := e.executeSelectOnMaterializedRows(&tail, columns, starKeys, rows)
	e.popTypeScope()
	if err != nil {
		return nil, err
	}
	result.ColumnTypes = catalogProjectionTypes(&tail, starKeys, types)
	if tail.Distinct {
		result.Rows = e.applyDistinct(result.Rows)
	}
	return result, nil
}