Обсуждение: Getting a error on creating a partition table index 12.2.
HI ALL,
While creating the index on the partition table i am getting error on it saying
ERROR: cannot specify default tablespace for partitioned relations SQL state: 0A00.
Query is
CREATE INDEX t_e20so1_doi_c_doid_idx
ON public.t_e20so1_doi USING btree
(i_doid ASC NULLS LAST)
TABLESPACE pg_default;
ON public.t_e20so1_doi USING btree
(i_doid ASC NULLS LAST)
TABLESPACE pg_default;
Note:- but the same query is executed in the 12.1 version.its working fine.
On 3/5/20 10:04 AM, nikhil raj wrote: > HI ALL, > > While creating the index on the partition table i am getting error on > it saying > > *ERROR: cannot specify default tablespace for partitioned relations SQL > state: 0A00.* > > Query is > > *CREATE INDEX t_e20so1_doi_c_doid_idx > ON public.t_e20so1_doi USING btree > (i_doid ASC NULLS LAST) > TABLESPACE pg_default;* > * > * > * > * > Note:- but the same query is executed in the 12.1 version.its working fine. Well this ERROR appeared here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/commands/indexcmds.c;h=87259588d0ab0b8e742e30596afa7ae25caadb18 Thu, 25 Apr 2019 06:20:23 -0800 (10:20 -0400) That would encompass 12.1 also. Are you doing anything else to public.t_e20so1_doi prior to the above? -- Adrian Klaver adrian.klaver@aklaver.com
Hi Adrian,
On that table nothing was happening just created the table and later o was creating the index and i was getting this error.
Please can you tell me is this the draw back of that in 12.2 version.
On Fri, 6 Mar 2020, 12:04 am Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 3/5/20 10:04 AM, nikhil raj wrote:
> HI ALL,
>
> While creating the index on the partition table i am getting error on
> it saying
>
> *ERROR: cannot specify default tablespace for partitioned relations SQL
> state: 0A00.*
>
> Query is
>
> *CREATE INDEX t_e20so1_doi_c_doid_idx
> ON public.t_e20so1_doi USING btree
> (i_doid ASC NULLS LAST)
> TABLESPACE pg_default;*
> *
> *
> *
> *
> Note:- but the same query is executed in the 12.1 version.its working fine.
Well this ERROR appeared here:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/commands/indexcmds.c;h=87259588d0ab0b8e742e30596afa7ae25caadb18
Thu, 25 Apr 2019 06:20:23 -0800 (10:20 -0400)
That would encompass 12.1 also.
Are you doing anything else to public.t_e20so1_doi prior to the above?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/5/20 10:48 AM, nikhil raj wrote: > Hi Adrian, > > On that table nothing was happening just created the table and later o > was creating the index and i was getting this error. > > Please can you tell me is this the draw back of that in 12.2 version. > Unfortunately I don't have answer for you on that. Someone with more knowledge of the internals will have to comment. For now the solution would seem to be not to specify the TABLESPACE if you want to use the default. -- Adrian Klaver adrian.klaver@aklaver.com
Ok, thanks for the clarification.
On Fri, Mar 6, 2020 at 12:31 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/5/20 10:48 AM, nikhil raj wrote:
> Hi Adrian,
>
> On that table nothing was happening just created the table and later o
> was creating the index and i was getting this error.
>
> Please can you tell me is this the draw back of that in 12.2 version.
>
Unfortunately I don't have answer for you on that. Someone with more
knowledge of the internals will have to comment.
For now the solution would seem to be not to specify the TABLESPACE if
you want to use the default.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2020-Mar-05, Adrian Klaver wrote: > On 3/5/20 10:04 AM, nikhil raj wrote: > > *CREATE INDEX t_e20so1_doi_c_doid_idx > > ON public.t_e20so1_doi USING btree > > (i_doid ASC NULLS LAST) > > TABLESPACE pg_default;* > > *ERROR: cannot specify default tablespace for partitioned relations SQL > > state: 0A00.* > > Note:- but the same query is executed in the 12.1 version.its working fine. > > Well this ERROR appeared here: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/commands/indexcmds.c;h=87259588d0ab0b8e742e30596afa7ae25caadb18 The reason for the error is the expectation that creating an index on a partitioned table with a tablespace specification will cause the children indexes (ie. the indexes on the partitions) to use the same tablespace. This does not work properly for the default tablespace, so I made that particular condition an error. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 3/5/20 11:31 AM, Alvaro Herrera wrote: > On 2020-Mar-05, Adrian Klaver wrote: > >> On 3/5/20 10:04 AM, nikhil raj wrote: > >>> *CREATE INDEX t_e20so1_doi_c_doid_idx >>> ON public.t_e20so1_doi USING btree >>> (i_doid ASC NULLS LAST) >>> TABLESPACE pg_default;* > >>> *ERROR: cannot specify default tablespace for partitioned relations SQL >>> state: 0A00.* > >>> Note:- but the same query is executed in the 12.1 version.its working fine. >> >> Well this ERROR appeared here: >> >> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/commands/indexcmds.c;h=87259588d0ab0b8e742e30596afa7ae25caadb18 > > The reason for the error is the expectation that creating an index on a > partitioned table with a tablespace specification will cause the > children indexes (ie. the indexes on the partitions) to use the same > tablespace. > > This does not work properly for the default tablespace, so I made that > particular condition an error. > The OP was wondering why it worked in 12.1 and not 12.2? I could not see any obvious reason for that, so: 1) There is a not obvious reason 2) It did not work in 12.1 either. -- Adrian Klaver adrian.klaver@aklaver.com
On 2020-Mar-05, Adrian Klaver wrote: > The OP was wondering why it worked in 12.1 and not 12.2? > > I could not see any obvious reason for that, so: > > 1) There is a not obvious reason > > 2) It did not work in 12.1 either. (2) is correct. 55469 12.1 9913=# show server_version; server_version ──────────────── 12.1 (1 fila) 55469 12.1 9913=# create index on p (a) tablespace pg_default; ERROR: cannot specify default tablespace for partitioned relations -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services