Обсуждение: BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
BUG #17009: create temporary table with like option using same name as persistent table does not create indexes
От
PG Bug reporting form
Дата:
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 waspossible for this re-examination to find a different table of the same name, causing unexpected behavior; one exampleis 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