pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17009: create temporary table with like option using same name as persistent table does not create indexes
4+ messages / 3 participants
[nested] [flat]
* BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
@ 2021-05-14 07:30 PG Bug reporting form <noreply@postgresql.org>
2021-05-14 09:46 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2021-05-14 07:30 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: misha_nagel@mail.ru
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);
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
2021-05-14 07:30 BUG #17009: create temporary table with like option using same name as persistent table does not create indexes PG Bug reporting form <noreply@postgresql.org>
@ 2021-05-14 09:46 ` David Rowley <dgrowleyml@gmail.com>
2021-05-14 10:55 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes Нагель Михаил <Misha_Nagel@mail.ru>
0 siblings, 1 reply; 4+ messages in thread
From: David Rowley @ 2021-05-14 09:46 UTC (permalink / raw)
To: misha_nagel@mail.ru, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
2021-05-14 07:30 BUG #17009: create temporary table with like option using same name as persistent table does not create indexes PG Bug reporting form <noreply@postgresql.org>
2021-05-14 09:46 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes David Rowley <dgrowleyml@gmail.com>
@ 2021-05-14 10:55 ` Нагель Михаил <Misha_Nagel@mail.ru>
2021-05-14 11:02 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Нагель Михаил @ 2021-05-14 10:55 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
> 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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
2021-05-14 07:30 BUG #17009: create temporary table with like option using same name as persistent table does not create indexes PG Bug reporting form <noreply@postgresql.org>
2021-05-14 09:46 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes David Rowley <dgrowleyml@gmail.com>
2021-05-14 10:55 ` Re: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes Нагель Михаил <Misha_Nagel@mail.ru>
@ 2021-05-14 11:02 ` David Rowley <dgrowleyml@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: David Rowley @ 2021-05-14 11:02 UTC (permalink / raw)
To: Нагель Михаил <Misha_Nagel@mail.ru>; +Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-05-14 11:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-05-14 07:30 BUG #17009: create temporary table with like option using same name as persistent table does not create indexes PG Bug reporting form <noreply@postgresql.org>
2021-05-14 09:46 ` David Rowley <dgrowleyml@gmail.com>
2021-05-14 10:55 ` Нагель Михаил <Misha_Nagel@mail.ru>
2021-05-14 11:02 ` David Rowley <dgrowleyml@gmail.com>
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