Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLawQ-0006B0-Ig for pgsql-general@arkaria.postgresql.org; Sat, 28 Jan 2023 02:27:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pLavQ-0007Ys-Lo for pgsql-general@arkaria.postgresql.org; Sat, 28 Jan 2023 02:26:40 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pLavQ-0007Yj-3v for pgsql-general@lists.postgresql.org; Sat, 28 Jan 2023 02:26:40 +0000 Received: from mail-oo1-xc31.google.com ([2607:f8b0:4864:20::c31]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pLavJ-0004o7-DO for pgsql-general@lists.postgresql.org; Sat, 28 Jan 2023 02:26:39 +0000 Received: by mail-oo1-xc31.google.com with SMTP id t14-20020a4ad0ae000000b004f51f61de9fso805678oor.13 for ; Fri, 27 Jan 2023 18:26:33 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=gN0BQ3ohDMH1FFJrmdt7xjJBO0UbOD/BAUPX6gxOTAE=; b=dswPZkATM9Xn6Kr4/w5u3hjg7rTK53Zm91248hOLz5GkjYkXAzFOEoVAUr3hzOindx TfXb4dobrJaxDc8oQisDHBCfQP4En0rXEBDnUbNVB77QdPfiOoRvwwZYiSryaC+dT+6w N1v2SYIGBxTlxUYnm15MUdl/JnP/NtyiAjQj0uqEz5DiMSTdZdLwzXXc5rueIXfdfGyn A89DMME4dfGrOuArCbR3+Tkjv1P1CvdJo4w8YzpqTpzaZDnxsfXDJ0RtKOVE71GUOBd0 u84gbHa97MQ2B2Tcpzje9IxQi8eBloKz5V5ifIEEpVQIrZgKmn8Z3GjGykUjgwEEg8ng woaQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=gN0BQ3ohDMH1FFJrmdt7xjJBO0UbOD/BAUPX6gxOTAE=; b=2zZ7kiBxovVaIORGKIkrmDtO3Q+RMM5zu+4w6gfbvWUQxSqmv/h5n6BSA9U1iVepga jGfulcBDSpS3zg6vUwXqdTyiM3jM3GDSO2qVMlyeFbbttoKyTpZMD9UXnq9BW4quFkhm KakpUMD+QvsU3+wHKuXeCv950cOaasS8LWpWC2bOLz2DO3PwUkDDoaeDO2AMGbld+0Qf 5HhHfmbcriu6l4Eq2AOxIjtmnQ8KsBqh2w/vjQgcGPsGwpXYD6/MwGxG7lfC5qOH5l+C p/TvlvykRGej1oPSIxuRBaZKzyqBmBum4DB1gZgJ9rUJ/eV92zlS/+U1wNxE2B2FugUk OTrg== X-Gm-Message-State: AFqh2kpNFYgg5eIS88t5qpBSg4l4XmOGGi475QqiMjY7S3Ax0bGdynK/ f2y6Vzx0kX+VWW0aSq7t7QGA2g5TBFM= X-Google-Smtp-Source: AMrXdXuD2ZgBGDuh25l0wtIhl8K6Z6038tisbHVDb+Epj4wt86DW3+nI4FMGuKRiWqRdFaeBjnmatg== X-Received: by 2002:a4a:e6d9:0:b0:50c:ed7a:ec9f with SMTP id v25-20020a4ae6d9000000b0050ced7aec9fmr9075750oot.9.1674872792324; Fri, 27 Jan 2023 18:26:32 -0800 (PST) Received: from [192.168.1.66] (108-250-206-84.lightspeed.nworla.sbcglobal.net. [108.250.206.84]) by smtp.googlemail.com with ESMTPSA id w12-20020a4aa98c000000b004a0ad937ccdsm2374469oom.1.2023.01.27.18.26.31 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 27 Jan 2023 18:26:31 -0800 (PST) Content-Type: multipart/alternative; boundary="------------wFRgx2WburtVwdahrBWj0TTn" Message-ID: <8a43877f-a282-6d9a-7092-244635b10ad5@gmail.com> Date: Fri, 27 Jan 2023 20:26:31 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Indexes mysteriously change to ON ONLY Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <3567881.1674852814@sss.pgh.pa.us> <3570422.1674854184@sss.pgh.pa.us> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------wFRgx2WburtVwdahrBWj0TTn Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit I cheat by using sed to remove "ONLY ON " from the CREATE statements. On 1/27/23 15:30, Rumpi Gravenstein wrote: > Tom/Christophe  I now understand.  Thanks for the clear explanation. > > On Fri, Jan 27, 2023 at 4:16 PM Tom Lane wrote: > > Rumpi Gravenstein writes: > > We are using the pg_indexes view (indexdef) to retrieve the index > > definition. > > Ah. > > > Are you saying that as a normal part of building an index, there are > short > > periods of time where the pg_indexes view will show the index with > ON ONLY > > specified? > > No, there's no "short periods", this is what it shows.  That's partly > because the output is designed for pg_dump to use.  But there's > a reasonably good argument for it anyway, which is that if you just > say "create index" then that's effectively a macro for building the > whole partitioned index set.  That pg_indexes entry is only about the > top-level "virtual" index, and there are other entries for the leaf > indexes.  For example, > > regression=# create table foo (f1 int primary key) partition by list (f1); > CREATE TABLE > regression=# create table foo_1 partition of foo for values in (1); > CREATE TABLE > regression=# create table foo_2 partition of foo for values in (2); > CREATE TABLE > regression=# select tablename,indexname,indexdef from pg_indexes where > indexname like 'foo%'; >  tablename | indexname  |  indexdef > -----------+------------+------------------------------------------------------------------ >  foo       | foo_pkey   | CREATE UNIQUE INDEX foo_pkey ON ONLY > public.foo USING btree (f1) >  foo_1     | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON > public.foo_1 USING btree (f1) >  foo_2     | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON > public.foo_2 USING btree (f1) > (3 rows) > > If you wanted to reconstruct this from individual parts, as pg_dump does, > you'd issue those commands and then connect them together with ATTACH > PARTITION commands. > >                         regards, tom lane > > > > -- > Rumpi Gravenstein -- Born in Arizona, moved to Babylonia. --------------wFRgx2WburtVwdahrBWj0TTn Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit I cheat by using sed to remove "ONLY ON " from the CREATE statements.

On 1/27/23 15:30, Rumpi Gravenstein wrote:
Tom/Christophe  I now understand.  Thanks for the clear explanation.

On Fri, Jan 27, 2023 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Rumpi Gravenstein <rgravens@gmail.com> writes:
> We are using the pg_indexes view (indexdef) to retrieve the index
> definition.

Ah.

> Are you saying that as a normal part of building an index, there are short
> periods of time where the pg_indexes view will show the index with ON ONLY
> specified?

No, there's no "short periods", this is what it shows.  That's partly
because the output is designed for pg_dump to use.  But there's
a reasonably good argument for it anyway, which is that if you just
say "create index" then that's effectively a macro for building the
whole partitioned index set.  That pg_indexes entry is only about the
top-level "virtual" index, and there are other entries for the leaf
indexes.  For example,

regression=# create table foo (f1 int primary key) partition by list (f1);
CREATE TABLE
regression=# create table foo_1 partition of foo for values in (1);
CREATE TABLE
regression=# create table foo_2 partition of foo for values in (2);
CREATE TABLE
regression=# select tablename,indexname,indexdef from pg_indexes where indexname like 'foo%';
 tablename | indexname  |                             indexdef                             
-----------+------------+------------------------------------------------------------------
 foo       | foo_pkey   | CREATE UNIQUE INDEX foo_pkey ON ONLY public.foo USING btree (f1)
 foo_1     | foo_1_pkey | CREATE UNIQUE INDEX foo_1_pkey ON public.foo_1 USING btree (f1)
 foo_2     | foo_2_pkey | CREATE UNIQUE INDEX foo_2_pkey ON public.foo_2 USING btree (f1)
(3 rows)

If you wanted to reconstruct this from individual parts, as pg_dump does,
you'd issue those commands and then connect them together with ATTACH
PARTITION commands.

                        regards, tom lane


--
Rumpi Gravenstein

--
Born in Arizona, moved to Babylonia.
--------------wFRgx2WburtVwdahrBWj0TTn--