CREATE TABLE .. LIKE copies comments to an unrelated table
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t254020psql -h localhost -U postgresBuilt from patchset v4 (message #4), October 23, 2026 at 04:27 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t254020_4 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t254020_4 && git checkout t254020_4Patchset v4 (message #4) is on t254020_4
Hi
While working on another patch I found that CREATE TABLE ... LIKE
INCLUDING COMMENTS copies the comments to the wrong table when the
target is a temp table and a table with the same name exists in a schema
listed before pg_temp in search_path.
Example:
psql (18.3 (Debian 18.3-1.pgdg13+1))
Type "help" for help.
db=> CREATE TABLE src (a int);
CREATE TABLE
db=> COMMENT ON COLUMN src.a IS 'col comment';
COMMENT
db=> CREATE TABLE foo (a int); -- unrelated table
CREATE TABLE
db=> COMMENT ON COLUMN foo.a IS 'important comment';
COMMENT
db=> SET search_path = public, pg_temp;
SET
db=> CREATE TEMP TABLE foo (LIKE src INCLUDING COMMENTS);
CREATE TABLE
db=> SELECT n.nspname, relname, col_description(c.oid, 1) AS colcomment
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'foo';
nspname | relname | colcomment
------------+---------+-------------
public | foo | col comment
pg_temp_24 | foo |
(2 rows)
The unrelated public.foo got the column comment and the created
temporary table got none -- public.foo.a comment was overwritten. Is
this the expected behaviour?
Setting the schema to pg_temp in case of RELPERSISTENCE_TEMP in
transformCreateStmt seems to do the trick (but I didn't dive too deep in
the code just yet):
- if (stmt->relation->schemaname == NULL
- && stmt->relation->relpersistence != RELPERSISTENCE_TEMP)
- stmt->relation->schemaname =
get_namespace_name(namespaceid);
+ if (stmt->relation->schemaname == NULL)
+ {
+ if (stmt->relation->relpersistence == RELPERSISTENCE_TEMP)
+ stmt->relation->schemaname = pstrdup("pg_temp");
+ else
+ stmt->relation->schemaname =
get_namespace_name(namespaceid);
+ }With my changes in HEAD:
db=> CREATE TABLE src (a int);
CREATE TABLE
db=> COMMENT ON COLUMN src.a IS 'col comment';
COMMENT
db=> CREATE TABLE foo (a int); -- unrelated table
CREATE TABLE
db=> COMMENT ON COLUMN foo.a IS 'important comment';
COMMENT
db=> SET search_path = public, pg_temp;
SET
db=> CREATE TEMP TABLE foo (LIKE src INCLUDING COMMENTS);
CREATE TABLE
db=> SELECT n.nspname, relname, col_description(c.oid, 1) AS colcomment
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'foo';
nspname | relname | colcomment
------------+---------+-------------------
public | foo | important comment
pg_temp_92 | foo | col comment
(2 rows)
WDYT?
Best, Jim
On Thu, Oct 1, 2026 at 10:35 PM Jim Jones <jim.jones@uni-muenster.de> wrote:
Hi
While working on another patch I found that CREATE TABLE ... LIKE
INCLUDING COMMENTS copies the comments to the wrong table when the
target is a temp table and a table with the same name exists in a schema
listed before pg_temp in search_path.Example:
psql (18.3 (Debian 18.3-1.pgdg13+1))
Type "help" for help.db=> CREATE TABLE src (a int);
CREATE TABLE
db=> COMMENT ON COLUMN src.a IS 'col comment';
COMMENT
db=> CREATE TABLE foo (a int); -- unrelated table
CREATE TABLE
db=> COMMENT ON COLUMN foo.a IS 'important comment';
COMMENT
db=> SET search_path = public, pg_temp;
SET
db=> CREATE TEMP TABLE foo (LIKE src INCLUDING COMMENTS);
CREATE TABLE
db=> SELECT n.nspname, relname, col_description(c.oid, 1) AS colcomment
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'foo';
nspname | relname | colcomment
------------+---------+-------------
public | foo | col comment
pg_temp_24 | foo |
(2 rows)The unrelated public.foo got the column comment and the created
temporary table got none -- public.foo.a comment was overwritten. Is
this the expected behaviour?
I think this is a bug, and I can reproduce it on master.
Setting the schema to pg_temp in case of RELPERSISTENCE_TEMP in
transformCreateStmt seems to do the trick (but I didn't dive too deep in
the code just yet):- if (stmt->relation->schemaname == NULL - && stmt->relation->relpersistence != RELPERSISTENCE_TEMP) - stmt->relation->schemaname = get_namespace_name(namespaceid); + if (stmt->relation->schemaname == NULL) + { + if (stmt->relation->relpersistence == RELPERSISTENCE_TEMP) + stmt->relation->schemaname = pstrdup("pg_temp"); + else + stmt->relation->schemaname = get_namespace_name(namespaceid); + }
The preceding RangeVarGetAndCheckCreationNamespace has already
initialized the temporary namespace and returned its OID, so simplified it
to:
if (stmt->relation->schemaname == NULL)
stmt->relation->schemaname = get_namespace_name(namespaceid);
should work, I did a quick test to verify this.
With my changes in HEAD:
db=> CREATE TABLE src (a int);
CREATE TABLE
db=> COMMENT ON COLUMN src.a IS 'col comment';
COMMENT
db=> CREATE TABLE foo (a int); -- unrelated table
CREATE TABLE
db=> COMMENT ON COLUMN foo.a IS 'important comment';
COMMENT
db=> SET search_path = public, pg_temp;
SET
db=> CREATE TEMP TABLE foo (LIKE src INCLUDING COMMENTS);
CREATE TABLE
db=> SELECT n.nspname, relname, col_description(c.oid, 1) AS colcomment
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'foo';
nspname | relname | colcomment
------------+---------+-------------------
public | foo | important comment
pg_temp_92 | foo | col comment
(2 rows)WDYT?
Best, Jim
--
Regards
Junwang Zhao
Hi Junwang
On 01/10/2026 17:31, Junwang Zhao wrote:
The preceding RangeVarGetAndCheckCreationNamespace has already
initialized the temporary namespace and returned its OID, so simplified it
to:if (stmt->relation->schemaname == NULL)
stmt->relation->schemaname = get_namespace_name(namespaceid);should work, I did a quick test to verify this.
+1
Thanks! I will write some tests and prepare a patch.
Best, Jim
On 01/10/2026 18:02, Jim Jones wrote:
Thanks! I will write some tests and prepare a patch.
PFA this fix and some tests in create_table_like.sql.
Best, Jim
Hi,
While reviewing v1, I noticed a behavior change when a temporary schema
is renamed concurrently. Would it make sense to qualify temporary table
names with pg_temp rather than the actual schema name?
The following sequence reproduces it with two connections to the same
database:
-- A
CREATE TABLE public.src (a int);
CREATE TEMP TABLE sentinel (a int);
SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
-- B
BEGIN;
LOCK TABLE public.src IN ACCESS EXCLUSIVE MODE;
-- A: wait until this statement is blocked before continuing in B.
CREATE TEMP TABLE dst (LIKE public.src);
-- B: replace pg_temp_N with the name returned by A's SELECT.
ALTER SCHEMA pg_temp_N RENAME TO renamed_temp_for_review;
COMMIT;
With v1, A fails with "schema \"pg_temp_N\" does not exist", because the
schema name saved by transformCreateStmt() becomes stale while waiting
for the lock. The same sequence succeeds without the patch.
This requires a superuser, and permanent tables can already encounter
the same error when their destination schema is renamed concurrently.
Therefore, if the intention is to match the name-based behavior of permanent
tables, I think this is reasonable.
Or would it be preferable to use pg_temp for temporary tables, so that
the generated COMMENT commands are schema-qualified while preserving
the previous behavior (i.e., the behavior of current master) in this case?
If we choose pg_temp, the additional change on top of v1 would be:
Permanent tables would still be qualified with the actual schema name.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -229,7 +229,9 @@
* COMMENTS.
*/
if (stmt->relation->schemaname == NULL)
- stmt->relation->schemaname = get_namespace_name(namespaceid);
+ stmt->relation->schemaname =
+ (stmt->relation->relpersistence == RELPERSISTENCE_TEMP) ?
+ pstrdup("pg_temp") : get_namespace_name(namespaceid);/* Set up CreateStmtContext */
Regards,
Koshi Shibagaki
FUJITSU LIMITED
https://www.fujitsu.com/
Hi Koshi
On 13/10/2026 13:57, Koshi Shibagaki (Fujitsu) wrote:
This requires a superuser, and permanent tables can already encounter
the same error when their destination schema is renamed concurrently.
Therefore, if the intention is to match the name-based behavior of permanent
tables, I think this is reasonable.
I see your point there. However, since this requires a superuser, I
don't think it needs to be fixed -- a superuser can do a lot of nasty
things. Trying to anticipate a superuser renaming a temp schema
mid-session is IMHO not something we should worry about. Please let me
know if I'm missing something here.
Thanks for the review. Much appreciated!
Best, Jim
Hi Jim,
I see your point there. However, since this requires a superuser, I
don't think it needs to be fixed -- a superuser can do a lot of nasty
things. Trying to anticipate a superuser renaming a temp schema
mid-session is IMHO not something we should worry about. Please let me
know if I'm missing something here.
That makes sense. I agree that we don't need to handle
a superuser renaming a temporary schema mid-session.
Thanks for the clarification!
Best,
Koshi Shibagaki
FUJITSU LIMITED
https://www.fujitsu.com/