BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
The following bug has been logged on the website:
Bug reference: 17009
Logged by: Mikhail Nagel
Email address: misha_nagel@mail.ru
PostgreSQL version: 12.5
Operating system: Debian x64
Description:
test:
create table t_tmp (a int);
create index on t_tmp (a);
create temporary table t_tmp (like t_tmp including all);
select *
from pg_catalog.pg_indexes
where tablename like 't_tmp%';
drop table t_tmp;
drop table t_tmp;
12.5 output:
ce2pg2=> create table t_tmp (a int);
CREATE TABLE
ce2pg2=> create index on t_tmp (a);
CREATE INDEX
ce2pg2=> create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
ce2pg2=> select *
ce2pg2-> from pg_catalog.pg_indexes
ce2pg2-> where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-------------+------------+---------------------------------------------------------
test2 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
(1 строка)
index on temporary table "t_tmp" not created
12.3 and 13.3 output:
postgres=# create table t_tmp (a int);
CREATE TABLE
postgres=# create index on t_tmp (a);
CREATE INDEX
postgres=# create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
postgres=# select *
postgres-# from pg_catalog.pg_indexes
postgres-# where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+--------------+------------+---------------------------------------------------------------
public | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
pg_temp_4 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON pg_temp_4.t_tmp USING btree (a)
(2 строки)
as expected index on temporary table "t_tmp" was created (second row)
workaround
1. use different table name
create temporary table t_other_name (like t_tmp including all);
2. use schema in like option
create temporary table t_tmp (like public.t_tmp including all);
On Fri, 14 May 2021 at 21:08, PG Bug reporting form
<noreply@postgresql.org> wrote:
12.5 output:
ce2pg2=> create table t_tmp (a int);
CREATE TABLE
ce2pg2=> create index on t_tmp (a);
CREATE INDEX
ce2pg2=> create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
ce2pg2=> select *
ce2pg2-> from pg_catalog.pg_indexes
ce2pg2-> where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace |
indexdef
------------+-----------+-------------+------------+---------------------------------------------------------
test2 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
If you upgrade to 12.6 or 12.7, does it work?
https://www.postgresql.org/docs/12/release-12-6.html mentions:
Prevent misprocessing of ambiguous CREATE TABLE LIKE clauses (Tom Lane)
A LIKE clause is re-examined after initial creation of the new table, to handle importation of indexes and such. It was possible for this re-examination to find a different table of the same name, causing unexpected behavior; one example is where the new table is a temporary table of the same name as the LIKE target."
This sounds like it might fix the issue.
David
If you upgrade to 12.6 or 12.7, does it work?
Good afternoon, David.
After updating to 12.7, the bug really disappeared:
postgres=# select version();
version
------------------------------------------------------------
PostgreSQL 12.7, compiled by Visual C++ build 1914, 64-bit
(1 строка)
postgres=# create table t_tmp (a int);
CREATE TABLE
postgres=# create index on t_tmp (a);
CREATE INDEX
postgres=#
postgres=# create temporary table t_tmp (like t_tmp including all);
CREATE TABLE
postgres=#
postgres=# select *
postgres-# from pg_catalog.pg_indexes
postgres-# where tablename like 't_tmp%';
schemaname | tablename | indexname | tablespace
| indexdef
------------+-----------+-------------+------------+-------------------------------------------------------------
public | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON public.t_tmp USING btree (a)
pg_temp_3 | t_tmp | t_tmp_a_idx | | CREATE INDEX
t_tmp_a_idx ON pg_temp_3.t_tmp USING btree (a)
(2 строки)
Do I understand correctly that the solution in my case is to upgrade to
at least 12.6?
On Fri, 14 May 2021 at 22:55, Нагель Михаил <Misha_Nagel@mail.ru> wrote:
Do I understand correctly that the solution in my case is to upgrade to
at least 12.6?
Going from 12.5 to 12.6 is a minor version upgrade. You should always
aim to be on the latest minor version of the given release you are
using, which in your case is 12. In minor version releases only bugs
are fixed.
Before PostgreSQL 10 came out, we used to have 3 parts to the version
number. The final one has always been the minor version number. Before
that, we had two portions to the major version number.
Since 12.6 fixed the bug you reported then it seems an upgrade is the
correct way to obtain the fix.
David