Repro: Client executes Bind on a server which has prep stmt of another client

main.go

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgproto3"
)

func main() {
	host := os.Getenv("PGHOST")
	if host == "" {
		host = "localhost"
	}
	port := os.Getenv("PGPORT")
	if port == "" {
		port = "5433"
	}

	connStr := fmt.Sprintf("postgres://yugabyte:yugabyte@%s:%s/yugabyte", host, port)
	fmt.Printf("Connecting to: %s\n\n", connStr)

	ctx := context.Background()

	// Connect client 1
	fmt.Println("Step 1: Connecting client 1...")
	conn1, err := pgconn.Connect(ctx, connStr)
	if err != nil {
		log.Fatalf("Failed to connect client 1: %v", err)
	}
	defer conn1.Close(ctx)
	fmt.Println("  Client 1 connected")

	// Connect client 2
	fmt.Println("Step 2: Connecting client 2...")
	conn2, err := pgconn.Connect(ctx, connStr)
	if err != nil {
		log.Fatalf("Failed to connect client 2: %v", err)
	}
	defer conn2.Close(ctx)
	fmt.Println("  Client 2 connected")

	sql1 := "SELECT 1 + 100"
	sql2 := "SELECT 'hello world'"

	// ============================================================
	// Client 1: First execution - Parse + Bind + Execute (NO Describe)
	// ============================================================
	fmt.Println("\nStep 3: Client 1 - First execution (Parse + Bind + Execute, no Describe)...")
	fmt.Printf("  SQL: %s\n", sql1)

	fe1 := conn1.Frontend()

	// Send Parse (create unnamed prepared statement)
	fe1.Send(&pgproto3.Parse{
		Name:  "",   // unnamed prepared statement
		Query: sql1,
	})
	// Send Bind (create unnamed portal from unnamed statement)
	fe1.Send(&pgproto3.Bind{
		PreparedStatement:    "", // unnamed prepared statement
		DestinationPortal:    "", // unnamed portal
		ResultFormatCodes:    []int16{0}, // text format
	})
	// Send Execute
	fe1.Send(&pgproto3.Execute{
		Portal: "", // unnamed portal
	})
	// Send Sync
	fe1.Send(&pgproto3.Sync{})
	// Flush to send all messages
	err = fe1.Flush()
	if err != nil {
		log.Fatalf("Client 1 flush failed: %v", err)
	}

	// Read responses
	result1, err := readQueryResult(conn1, ctx)
	if err != nil {
		log.Fatalf("Client 1 query failed: %v", err)
	}
	fmt.Printf("  Result: %s\n", result1)

	// ============================================================
	// Client 2: Parse DIFFERENT query + Bind + Execute (NO Describe)
	// ============================================================
	fmt.Println("\nStep 4: Client 2 - Execute DIFFERENT query (Parse + Bind + Execute, no Describe)...")
	fmt.Printf("  SQL: %s\n", sql2)

	fe2 := conn2.Frontend()

	fe2.Send(&pgproto3.Parse{
		Name:  "",
		Query: sql2,
	})
	fe2.Send(&pgproto3.Bind{
		PreparedStatement:    "",
		DestinationPortal:    "",
		ResultFormatCodes:    []int16{0},
	})
	fe2.Send(&pgproto3.Execute{
		Portal: "",
	})
	fe2.Send(&pgproto3.Sync{})
	err = fe2.Flush()
	if err != nil {
		log.Fatalf("Client 2 flush failed: %v", err)
	}

	result2, err := readQueryResult(conn2, ctx)
	if err != nil {
		log.Fatalf("Client 2 query failed: %v", err)
	}
	fmt.Printf("  Result: %s\n", result2)

	// ============================================================
	// Client 1: Second execution - Bind + Execute ONLY (no Parse!)
	// ============================================================
	fmt.Println("\nStep 5: Client 1 - Second execution (Bind + Execute ONLY, no Parse)...")
	fmt.Printf("  SQL: %s (expecting to reuse prepared statement)\n", sql1)

	// NO Parse! Just Bind + Execute, reusing the unnamed prepared statement
	fe1.Send(&pgproto3.Bind{
		PreparedStatement:    "",
		DestinationPortal:    "",
		ResultFormatCodes:    []int16{0},
	})
	fe1.Send(&pgproto3.Execute{
		Portal: "",
	})
	fe1.Send(&pgproto3.Sync{})
	err = fe1.Flush()
	if err != nil {
		log.Fatalf("Client 1 second flush failed: %v", err)
	}

	result3, err := readBindExecuteResult(conn1, ctx)
	if err != nil {
		log.Fatalf("Client 1 second query failed (BUG?): %v", err)
	}
	fmt.Printf("  Result: %s\n", result3)

	fmt.Println("\nAll tests passed!")
}

// readQueryResult reads responses for Parse + Bind + Execute + Sync
func readQueryResult(conn *pgconn.PgConn, ctx context.Context) (string, error) {
	var result string
	for {
		msg, err := conn.ReceiveMessage(ctx)
		if err != nil {
			return "", fmt.Errorf("receive message: %w", err)
		}
		switch m := msg.(type) {
		case *pgproto3.ParseComplete:
			// Parse done
		case *pgproto3.BindComplete:
			// Bind done
		case *pgproto3.RowDescription:
			// Describes the columns
		case *pgproto3.DataRow:
			if len(m.Values) > 0 {
				result = string(m.Values[0])
			}
		case *pgproto3.CommandComplete:
			// Command done
		case *pgproto3.ReadyForQuery:
			return result, nil
		case *pgproto3.ErrorResponse:
			return "", fmt.Errorf("error: %s (SQLSTATE %s)", m.Message, m.Code)
		}
	}
}

// readBindExecuteResult reads responses for Bind + Execute + Sync (no Parse)
func readBindExecuteResult(conn *pgconn.PgConn, ctx context.Context) (string, error) {
	var result string
	for {
		msg, err := conn.ReceiveMessage(ctx)
		if err != nil {
			return "", fmt.Errorf("receive message: %w", err)
		}
		switch m := msg.(type) {
		case *pgproto3.BindComplete:
			// Bind done
		case *pgproto3.RowDescription:
			// Describes the columns
		case *pgproto3.DataRow:
			if len(m.Values) > 0 {
				result = string(m.Values[0])
			}
		case *pgproto3.CommandComplete:
			// Command done
		case *pgproto3.ReadyForQuery:
			return result, nil
		case *pgproto3.ErrorResponse:
			return "", fmt.Errorf("error: %s (SQLSTATE %s)", m.Message, m.Code)
		}
	}
}