Version

1.30.0

What happened?

When sqlc generate replays a PostgreSQL schema containing DROP TABLE ... CASCADE, it appears to leave dependent views in its internal schema catalog.

In real Postgres, dropping a table with CASCADE drops views that depend on that table. In sqlc, the dependent view still appears to exist, so a later CREATE VIEW with the same name fails.

Minimal example (Example on play.sqlc.dev):

CREATE TABLE reference_rates (
    id BIGSERIAL PRIMARY KEY,
    data_ts TIMESTAMPTZ NOT NULL
);

CREATE VIEW vw_reference_rates AS
SELECT *
FROM reference_rates;

CREATE TABLE reference_rates_new (
    id BIGSERIAL PRIMARY KEY,
    data_ts TIMESTAMPTZ NOT NULL
);

DROP TABLE reference_rates CASCADE;

ALTER TABLE reference_rates_new RENAME TO reference_rates;

CREATE VIEW vw_reference_rates AS
SELECT *
FROM reference_rates;

I expected sqlc generate to succeed because this SQL is valid in Postgres. The DROP TABLE reference_rates CASCADE statement should remove vw_reference_rates, allowing the later CREATE VIEW vw_reference_rates to succeed.

Instead, sqlc fails with relation "vw_reference_rates" already exists.

This reproduces on v1.31.1. I also reproduced it on v1.30.0, and on v1.31.1 with SQLCEXPERIMENT=analyzerv2.

Relevant log output

# package db
schema.sql:1:1: relation "vw_reference_rates" already exists

Database schema

CREATE TABLE reference_rates (
    id BIGSERIAL PRIMARY KEY,
    data_ts TIMESTAMPTZ NOT NULL
);

CREATE VIEW vw_reference_rates AS
SELECT *
FROM reference_rates;

CREATE TABLE reference_rates_new (
    id BIGSERIAL PRIMARY KEY,
    data_ts TIMESTAMPTZ NOT NULL
);

DROP TABLE reference_rates CASCADE;

ALTER TABLE reference_rates_new RENAME TO reference_rates;

CREATE VIEW vw_reference_rates AS
SELECT *
FROM reference_rates;

SQL queries

-- name: ListReferenceRates :many
SELECT id, data_ts
FROM vw_reference_rates
ORDER BY id;

Configuration

{
  "version": "2",
  "sql": [
    {
      "engine": "postgresql",
      "schema": "schema.sql",
      "queries": "query.sql",
      "gen": {
        "go": {
          "package": "db",
          "out": "db"
        }
      }
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/77fea362bc83d66c4b8439685849e22d2a7e472774e3a6a386074477ecbf080e

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go