While writing a migration which made use of a temporary table, I observed a limitation/quirk of sqlc. The exact migration involves a lot of irrelevant details, so I will share a contrived example which reproduces the problem:
BEGIN;
CREATE TEMPORARY TABLE foobar (
id INT
) ON COMMIT DROP;
INSERT INTO foobar (id) VALUES (1);
SELECT * FROM foobar;
COMMIT;
If you run this interactively against Postgres (version 16.2 for what it's worth) this transaction does what you would expect - it creates a temporary table, populates a row, and then displays the contents of the new row when the SELECT query is run. Once the transaction is committed, the table disappears and can no longer be queried.
If I place the same code into a sqlc migration, sqlc will generate a struct for the temporary table even though it no longer exists once the migration runs:
type Foobar struct {
ID sql.NullInt32 `json:"id"`
}
If I add an explicit DROP TABLE IF EXISTS... before the COMMIT, then the struct will no longer be generated. This was an acceptable workaround in my case, but it seems like sqlc should be able to understand the ON COMMIT DROP clause and not generate code for the temp table.
I'm considering taking a look at the sqlc code and see if I can change sqlc's behaviour in this scenario, but I wanted to make sure that it isn't a known issue that either will be fixed in a future release, or if it's an behaviour which can not/should not be changed.
Version
1.27.0
What happened?
While writing a migration which made use of a temporary table, I observed a limitation/quirk of sqlc. The exact migration involves a lot of irrelevant details, so I will share a contrived example which reproduces the problem:
If you run this interactively against Postgres (version 16.2 for what it's worth) this transaction does what you would expect - it creates a temporary table, populates a row, and then displays the contents of the new row when the
SELECTquery is run. Once the transaction is committed, the table disappears and can no longer be queried.If I place the same code into a sqlc migration, sqlc will generate a struct for the temporary table even though it no longer exists once the migration runs:
If I add an explicit
DROP TABLE IF EXISTS...before theCOMMIT, then the struct will no longer be generated. This was an acceptable workaround in my case, but it seems like sqlc should be able to understand theON COMMIT DROPclause and not generate code for the temp table.I'm considering taking a look at the sqlc code and see if I can change sqlc's behaviour in this scenario, but I wanted to make sure that it isn't a known issue that either will be fixed in a future release, or if it's an behaviour which can not/should not be changed.
Relevant log output
No response
Database schema
SQL queries
Configuration
No response
Playground URL
No response
What operating system are you using?
macOS
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go