pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feed12.1 Partitioned Table Creation Bug
5+ messages / 4 participants
[nested] [flat]
* 12.1 Partitioned Table Creation Bug
@ 2020-01-22 12:26 Chris Drawater <Chris.Drawater@viavisolutions.com>
2020-01-22 19:47 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Drawater @ 2020-01-22 12:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
A pretty simple case attached : attempting to create a partitioned table via psql fails -->
Psql can be either be a remote Windows PG 11.0 client or a Linux PG 12.1 client ( same node as PG server) - makes no difference.
---
[postgres@ssd1 pg_scripts]$ cat /etc/system-release
CentOS Linux release 7.7.1908 (Core)
geo=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
geo=> create table junk ( a int, b int) PARTITION BY RANGE (a);
ERROR: cannot specify default tablespace for partitioned relations
Or
geo=> create table junk (a int,b int) partition by list(a);
ERROR: cannot specify default tablespace for partitioned relations
I tried cut/paste and typing in the command from scratch to preclude control character issues.
-----------------------------------------------------------
Note , the non-partitioned table DDL below works fine -->
geo=> create table junk ( a int, b int);
CREATE TABLE
All my application partitioned table DDL works OK on PG 11.1 on CentOS Linux release 7.3.1611 (Core) but none on 12.1...
I'd be surprised if something so fundamental is a bug but just in case, I thought I'd submit it....
Chris Drawater
Database Architect
Viavi Solutions
Astor House, Newbury Business Park, London Road
Newbury, Berkshire RG14 2PZ UK
+44 (0)1635 223000 (office)
www.viavisolutions.com<http://www.viavisolutions.com/;
Follow us on:
Viavi Perspectives<http://blog.viavisolutions.com/; │ LinkedIn<https://www.linkedin.com/company/viavi-solutions?trk=nav_account_sub_nav_company_admin; │ Twitter<http://www.twitter.com/viavisolutions; │ YouTube<https://www.youtube.com/channel/UCCCGcTKD1teKf8VyaEKm-MA; │Facebook<https://www.facebook.com/viavisolutions;
[cid:image001.jpg@01D1FC8E.7D083320]
Viavi Solutions UK Ltd. is registered in England & Wales with company number 00887400. Its registered office is Astor House, Newbury Business Park, London Road, Newbury, Berkshire, RG14 2PZ, United Kingdom. Information contained in this email is intended for the use of the addressee only, is confidential and may be legally privileged. Any further dissemination, distribution, copying or use of this communication without prior permission of the sender is strictly prohibited.
* Please consider the environment before printing this email.
Attachments:
Picture (Device Independent Bitmap) 1.jpg (image/jpeg, 6.2K) download
view image
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 12.1 Partitioned Table Creation Bug
2020-01-22 12:26 12.1 Partitioned Table Creation Bug Chris Drawater <Chris.Drawater@viavisolutions.com>
@ 2020-01-22 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-22 21:39 ` Re: 12.1 Partitioned Table Creation Bug David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-01-22 19:47 UTC (permalink / raw)
To: Chris Drawater <Chris.Drawater@viavisolutions.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Chris Drawater <Chris.Drawater@viavisolutions.com> writes:
> geo=> create table junk ( a int, b int) PARTITION BY RANGE (a);
> ERROR: cannot specify default tablespace for partitioned relations
I'm guessing you've got default_tablespace set to something nonempty.
I don't know why the decision was made to throw an error rather than
silently ignoring the setting ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 12.1 Partitioned Table Creation Bug
2020-01-22 12:26 12.1 Partitioned Table Creation Bug Chris Drawater <Chris.Drawater@viavisolutions.com>
2020-01-22 19:47 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-22 21:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-01-22 23:17 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2020-01-22 21:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris Drawater <Chris.Drawater@viavisolutions.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Wed, Jan 22, 2020 at 12:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Drawater <Chris.Drawater@viavisolutions.com> writes:
> > geo=> create table junk ( a int, b int) PARTITION BY RANGE (a);
> > ERROR: cannot specify default tablespace for partitioned relations
>
> I'm guessing you've got default_tablespace set to something nonempty.
>
> I don't know why the decision was made to throw an error rather than
> silently ignoring the setting ...
>
Ignoring the setting seems to be what the documentation says we do:
https://www.postgresql.org/docs/12/sql-createtable.html
"For partitioned tables, since no storage is required for the table itself,
the tablespace specified overrides default_tablespace as the default
tablespace to use for any newly created partitions when no other tablespace
is explicitly specified."
So this seems like a regression in v12.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 12.1 Partitioned Table Creation Bug
2020-01-22 12:26 12.1 Partitioned Table Creation Bug Chris Drawater <Chris.Drawater@viavisolutions.com>
2020-01-22 19:47 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
2020-01-22 21:39 ` Re: 12.1 Partitioned Table Creation Bug David G. Johnston <david.g.johnston@gmail.com>
@ 2020-01-22 23:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-23 15:48 ` Re: 12.1 Partitioned Table Creation Bug Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-01-22 23:17 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Chris Drawater <Chris.Drawater@viavisolutions.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Jan 22, 2020 at 12:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Chris Drawater <Chris.Drawater@viavisolutions.com> writes:
>>> geo=> create table junk ( a int, b int) PARTITION BY RANGE (a);
>>> ERROR: cannot specify default tablespace for partitioned relations
>> I'm guessing you've got default_tablespace set to something nonempty.
>> I don't know why the decision was made to throw an error rather than
>> silently ignoring the setting ...
> Ignoring the setting seems to be what the documentation says we do:
> https://www.postgresql.org/docs/12/sql-createtable.html
> "For partitioned tables, since no storage is required for the table itself,
> the tablespace specified overrides default_tablespace as the default
> tablespace to use for any newly created partitions when no other tablespace
> is explicitly specified."
> So this seems like a regression in v12.
Poking into it, I can reproduce Chris' failure in the specific case
where default_tablespace is explicitly set to "pg_default" rather
than being left empty.
You're right that this is new behavior in v12; it seems to have been
introduced by commit 87259588d, which quoth
* Setting a partitioned rel's tablespace to the database default is
confusing; if it worked, it would direct the partitions to that
tablespace regardless of default_tablespace. But in reality it does
not work, and making it work is a larger project. Therefore, throw
an error when this condition is detected, to alert the unwary.
I wouldn't say that this behavior is "alerting the unwary"; it's just
confusing them --- and the case that this is less confusing than what
happened before seems pretty thin.
Maybe a better error message would improve matters. But I'm inclined
to say that reverting this aspect of the patch would be a better idea.
I find it particularly bletcherous that it made GetDefaultTablespace()
know whether it's operating to select a tablespace for a partitioned
relation or some other case --- that is just awful design, and it doesn't
make me feel that the whole idea was especially well thought out.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: 12.1 Partitioned Table Creation Bug
2020-01-22 12:26 12.1 Partitioned Table Creation Bug Chris Drawater <Chris.Drawater@viavisolutions.com>
2020-01-22 19:47 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
2020-01-22 21:39 ` Re: 12.1 Partitioned Table Creation Bug David G. Johnston <david.g.johnston@gmail.com>
2020-01-22 23:17 ` Re: 12.1 Partitioned Table Creation Bug Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-01-23 15:48 ` Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2020-01-23 15:48 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; Chris Drawater <Chris.Drawater@viavisolutions.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On 2020-Jan-22, Tom Lane wrote:
> Poking into it, I can reproduce Chris' failure in the specific case
> where default_tablespace is explicitly set to "pg_default" rather
> than being left empty.
>
> You're right that this is new behavior in v12; it seems to have been
> introduced by commit 87259588d, which quoth
>
> * Setting a partitioned rel's tablespace to the database default is
> confusing; if it worked, it would direct the partitions to that
> tablespace regardless of default_tablespace. But in reality it does
> not work, and making it work is a larger project. Therefore, throw
> an error when this condition is detected, to alert the unwary.
>
> I wouldn't say that this behavior is "alerting the unwary"; it's just
> confusing them --- and the case that this is less confusing than what
> happened before seems pretty thin.
>
> Maybe a better error message would improve matters. But I'm inclined
> to say that reverting this aspect of the patch would be a better idea.
> I find it particularly bletcherous that it made GetDefaultTablespace()
> know whether it's operating to select a tablespace for a partitioned
> relation or some other case --- that is just awful design, and it doesn't
> make me feel that the whole idea was especially well thought out.
A partitioned relation behaves differently from other relations in the
storage regard, so I don't find particularly surprising that
GetDefaultTablespace behaves differently for them.
I think randomly, carelessly reverting parts of the patch would be a
terrible idea, because it was difficult to arrive at the current
behavior; all other behaviors had different problematic corner cases.
I invite people to read the discussion to led to the current design.
As the quoted commit message indicates, it *is* possible to make this
configuration/command combination work, but it's not as trivial as
reverting a few lines of a commit. Again, I suggest to reread the old
thread for some insight. In particular,
https://postgr.es/m/20190423222633.GA8364@alvherre.pgsql
The fact remains that partitioned tables were left to work poorly in
many cases, and we made great efforts to improve them. Some casualties
were left along the way, mostly in the area of backwards compatibility.
I didn't shed tears for those, and nobody should either: in pg10 we got
partitioning, which was a huge effort and I applaud that, but it was
just the beginning and we knew full well that it had wrinkles that we'd
have to iron out later on. That is what we were doing in the discussion
that led to commit 87259588d.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-01-23 15:48 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-22 12:26 12.1 Partitioned Table Creation Bug Chris Drawater <Chris.Drawater@viavisolutions.com>
2020-01-22 19:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-22 21:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-01-22 23:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-01-23 15:48 ` Alvaro Herrera <alvherre@2ndquadrant.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