BUG #16758: create temporary table with the same name loses defaults, indexes
The following bug has been logged on the website:
Bug reference: 16758
Logged by: Marc Boeren
Email address: marc@guidance.nl
PostgreSQL version: 12.5
Operating system: macOS / docker-debian / CentOR
Description:
In 12.5 (and later), in a clean, empty database (in this case the default
12.5 docker image), when I execute the following lines:
create table xx (name text NOT NULL default '', PRIMARY KEY(name));
create temporary table xx
(like xx including DEFAULTS including CONSTRAINTS including
INDEXES);
select c.relname, n.nspname
from pg_namespace n
join pg_class c on n.oid=c.relnamespace
where (c.relname LIKE 'x%') order by n.nspname, c.relname;
I get this:
relname nspname
"xx" "pg_temp_8"
"xx" "public"
"xx_pkey" "public"
I expected the primary key to be copied too. Comparing the results from 12.4
(and earlier versions):
relname nspname
"xx" "pg_temp_8"
"xx_pkey" "pg_temp_8"
"xx" "public"
"xx_pkey" "public"
I couldn't find anything in the changelog for 12.5 that indicates it is
intentional. I checked against 13.1 too, same problem.
The actual table is a bit larger (about 100 fields, no relations) and loses
the primary key and all indexes and defaults since 12.5. The SQL above is
the shortest form that demonstrates the issue.
Thanks, Marc
As a followup, if I change the name to something different from the original, like 'xy', it works as expected.
Show quoted text
On 1 Dec 2020, at 15:09, PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16758
Logged by: Marc Boeren
Email address: marc@guidance.nl
PostgreSQL version: 12.5
Operating system: macOS / docker-debian / CentOR
Description:In 12.5 (and later), in a clean, empty database (in this case the default
12.5 docker image), when I execute the following lines:create table xx (name text NOT NULL default '', PRIMARY KEY(name));
create temporary table xx
(like xx including DEFAULTS including CONSTRAINTS including
INDEXES);select c.relname, n.nspname
from pg_namespace n
join pg_class c on n.oid=c.relnamespace
where (c.relname LIKE 'x%') order by n.nspname, c.relname;I get this:
relname nspname
"xx" "pg_temp_8"
"xx" "public"
"xx_pkey" "public"I expected the primary key to be copied too. Comparing the results from 12.4
(and earlier versions):relname nspname
"xx" "pg_temp_8"
"xx_pkey" "pg_temp_8"
"xx" "public"
"xx_pkey" "public"I couldn't find anything in the changelog for 12.5 that indicates it is
intentional. I checked against 13.1 too, same problem.
The actual table is a bit larger (about 100 fields, no relations) and loses
the primary key and all indexes and defaults since 12.5. The SQL above is
the shortest form that demonstrates the issue.Thanks, Marc
PG Bug reporting form <noreply@postgresql.org> writes:
In 12.5 (and later), in a clean, empty database (in this case the default
12.5 docker image), when I execute the following lines:
create table xx (name text NOT NULL default '', PRIMARY KEY(name));
create temporary table xx
(like xx including DEFAULTS including CONSTRAINTS including INDEXES);
Hm, interesting. Without having dug into the code, I bet what is
happening is that after creating pg_temp.xx, the LIKE code is looking
to see "what indexes exist on table xx?", to which the answer is "none"
because it finds pg_temp.xx. We need to nail down the schema in which
xx is sought for that step. As a workaround, you could nail down the
schema manually:
create temporary table xx (like public.xx including DEFAULTS including
CONSTRAINTS including INDEXES);
The ordering of these operations got rearranged recently to fix some
other bugs, so it doesn't surprise me if it used to work differently.
regards, tom lane
[ please keep the mailing list cc'd ]
Marc Boeren <m.boeren@guidance.nl> writes:
Without having dug into the code, I bet what is
happening is that after creating pg_temp.xx, the LIKE code is looking
to see "what indexes exist on table xx?", to which the answer is "none"
because it finds pg_temp.xx. We need to nail down the schema in which
xx is sought for that step. As a workaround, you could nail down the
schema manually:create temporary table xx (like public.xx including DEFAULTS including
CONSTRAINTS including INDEXES);
This workaround works both on the example code, and on my production code.
Thanks for confirming the diagnosis!
Not sure if it's a bug worth fixing, but perhaps a mention in the documentation?
No, it's definitely a bug IMO. The change in behavior was not
intentional.
regards, tom lane
Import Notes
Reply to msg id not found: B16D4C9A-2E12-4154-A628-CA239E0A3DA5@guidance.nl
On Tue, Dec 01, 2020 at 12:07:41PM -0500, Tom Lane wrote:
No, it's definitely a bug IMO. The change in behavior was not
intentional.
Please note that this bug has been fixed by 5f9b05ad, so there will be
a fix in 12.6.
--
Michael