Thread: 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.

> 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

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