Thread: 12.1 Partitioned Table Creation Bug
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
www.viavisolutions.com
Follow us on:
![](cid:image001.jpg@01D1FC8E.7D083320)
![](/media/maillist_attaches/pgsql-bugs/2020/01/22/BY5PR18MB3170E372542F34694E630B12F10C0@BY5PR18MB3170.namprd18.prod.outlook.com/Picture %28Devic.jpg)
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.
P Please consider the environment before printing this email.
Attachment
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
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:
"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.
"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
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