pizzasql/pkg/pgserver/pg_catalog_test.go

package pgserver

import (
	"strconv"
	"strings"
	"testing"
)

func simpleQuery(sql string) *Message {
	return &Message{Type: MsgQuery, Data: append([]byte(sql), 0)}
}

// Catalog columns carry PostgreSQL's types over the wire (oid 26, name 19), so
// a driver decodes them without falling back to TEXT.
func TestPgCatalogWireTypes(t *testing.T) {
	c, client := newDataConnection(t)
	for _, sql := range []string{
		"CREATE TABLE wire (id INTEGER PRIMARY KEY, payload JSONB)",
		"CREATE INDEX idx_wire_payload ON wire (payload)",
	} {
		if msgs := runQuery(t, c, client, simpleQuery(sql)); errorCode(msgs[0]) != "" {
			t.Fatalf("%s: %s", sql, errorMessage(msgs[0]))
		}
	}

	msgs := runQuery(t, c, client, simpleQuery(`
		SELECT c.oid, c.relname, c.relkind
		FROM pg_catalog.pg_class c
		WHERE c.relname = 'wire'`))
	if msg := findError(msgs); msg != nil {
		t.Fatalf("catalog query failed: %s", errorMessage(msg))
	}
	rd := findRowDescription(t, msgs)
	if got := rowDescriptionOIDs(t, rd.Data); len(got) != 3 || got[0] != 26 || got[1] != 19 || got[2] != 25 {
		t.Fatalf("catalog OIDs = %v, want [26 19 25]", got)
	}
	rows := catalogRows(t, msgs)
	if len(rows) != 1 || rows[0][1] != "wire" || rows[0][2] != "r" {
		t.Fatalf("pg_class row = %v", rows)
	}
	if _, err := strconv.ParseUint(rows[0][0], 10, 32); err != nil {
		t.Fatalf("oid %q is not numeric: %v", rows[0][0], err)
	}
}

// A join across pg_class/pg_namespace/pg_attribute works over the simple-query
// protocol, which is how migration tools list columns.
func TestPgCatalogJoinOverWire(t *testing.T) {
	c, client := newDataConnection(t)
	for _, sql := range []string{
		"CREATE TABLE wire_join (id INTEGER PRIMARY KEY, label TEXT NOT NULL)",
	} {
		if msgs := runQuery(t, c, client, simpleQuery(sql)); errorCode(msgs[0]) != "" {
			t.Fatalf("%s: %s", sql, errorMessage(msgs[0]))
		}
	}
	msgs := runQuery(t, c, client, simpleQuery(`
		SELECT n.nspname, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)
		FROM pg_catalog.pg_class c
		JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
		WHERE c.relname = 'wire_join' AND a.attnum > 0
		ORDER BY a.attnum`))
	if msg := findError(msgs); msg != nil {
		t.Fatalf("catalog join failed: %s", errorMessage(msg))
	}
	rows := catalogRows(t, msgs)
	if len(rows) != 2 || rows[0][1] != "id" || rows[0][2] != "integer" || rows[1][1] != "label" {
		t.Fatalf("catalog join rows = %v", rows)
	}
}

// ORM connections issue SET during startup; the engine acknowledges it and
// SHOW reports the retained search path.
func TestSetAndShowSearchPathOverWire(t *testing.T) {
	c, client := newDataConnection(t)
	if msgs := runQuery(t, c, client, simpleQuery("SET search_path TO public")); errorCode(msgs[0]) != "" {
		t.Fatalf("SET search_path failed: %s", errorMessage(msgs[0]))
	}
	if msgs := runQuery(t, c, client, simpleQuery("SET TIME ZONE 'UTC'")); errorCode(msgs[0]) != "" {
		t.Fatalf("SET TIME ZONE failed: %s", errorMessage(msgs[0]))
	}
	msgs := runQuery(t, c, client, simpleQuery("SHOW search_path"))
	if msg := findError(msgs); msg != nil {
		t.Fatalf("SHOW search_path failed: %s", errorMessage(msg))
	}
	rows := catalogRows(t, msgs)
	if len(rows) != 1 || rows[0][0] != "public" {
		t.Fatalf("SHOW search_path = %v", rows)
	}
	msgs = runQuery(t, c, client, simpleQuery("SHOW client_encoding"))
	if rows := catalogRows(t, msgs); len(rows) != 1 || !strings.EqualFold(rows[0][0], "UTF8") {
		t.Fatalf("SHOW client_encoding = %v", rows)
	}
}