pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16758: create temporary table with the same name loses defaults, indexes
5+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2020-12-01 f7f83a55bf dffc82a5b9 f00c440027 5f9b05ada1 28bb8c4966 fb500296df d98e27dc6e Ensure that expandTableLikeClause() re-examines the same table.
* BUG #16758: create temporary table with the same name loses defaults, indexes
@ 2020-12-01 14:09 PG Bug reporting form <noreply@postgresql.org>
2020-12-01 14:18 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Marc Boeren <m.boeren@guidance.nl>
2020-12-01 15:58 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: PG Bug reporting form @ 2020-12-01 14:09 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: marc@guidance.nl
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16758: create temporary table with the same name loses defaults, indexes
2020-12-01 14:09 BUG #16758: create temporary table with the same name loses defaults, indexes PG Bug reporting form <noreply@postgresql.org>
@ 2020-12-01 14:18 ` Marc Boeren <m.boeren@guidance.nl>
1 sibling, 0 replies; 5+ messages in thread
From: Marc Boeren @ 2020-12-01 14:18 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16758: create temporary table with the same name loses defaults, indexes
2020-12-01 14:09 BUG #16758: create temporary table with the same name loses defaults, indexes PG Bug reporting form <noreply@postgresql.org>
@ 2020-12-01 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-01 17:07 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-12-01 15:58 UTC (permalink / raw)
To: marc@guidance.nl; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16758: create temporary table with the same name loses defaults, indexes
2020-12-01 14:09 BUG #16758: create temporary table with the same name loses defaults, indexes PG Bug reporting form <noreply@postgresql.org>
2020-12-01 15:58 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-12-01 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-17 07:19 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Michael Paquier <michael@paquier.xyz>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-12-01 17:07 UTC (permalink / raw)
To: Marc Boeren <m.boeren@guidance.nl>; +Cc: pgsql-bugs@lists.postgresql.org
[ 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #16758: create temporary table with the same name loses defaults, indexes
2020-12-01 14:09 BUG #16758: create temporary table with the same name loses defaults, indexes PG Bug reporting form <noreply@postgresql.org>
2020-12-01 15:58 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Tom Lane <tgl@sss.pgh.pa.us>
2020-12-01 17:07 ` Re: BUG #16758: create temporary table with the same name loses defaults, indexes Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-12-17 07:19 ` Michael Paquier <michael@paquier.xyz>
0 siblings, 0 replies; 5+ messages in thread
From: Michael Paquier @ 2020-12-17 07:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Marc Boeren <m.boeren@guidance.nl>; pgsql-bugs@lists.postgresql.org
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
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-12-17 07:19 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-01 14:09 BUG #16758: create temporary table with the same name loses defaults, indexes PG Bug reporting form <noreply@postgresql.org>
2020-12-01 14:18 ` Marc Boeren <m.boeren@guidance.nl>
2020-12-01 15:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-01 17:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-12-17 07:19 ` Michael Paquier <michael@paquier.xyz>
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