From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: misha_nagel@mail.ru
Subject: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
Date: Fri, 14 May 2021 07:30:08 +0000
Message-ID: <17009-b364ef2e97142a54@postgresql.org> (raw)
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);
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, misha_nagel@mail.ru
Subject: Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
In-Reply-To: <17009-b364ef2e97142a54@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox