From: Nikolay Shaplov <dhyan@nataraj.su>
To: pgsql-hackers@lists.postgresql.org
Cc: Nikita Glukhov <n.gluhov@postgrespro.ru>,
 PostgreSQL mailing lists <pgsql-hackers@postgresql.org>,
 Oleg Bartunov <obartunov@gmail.com>
Subject: Re: [PATCH] Opclass parameters
Date: Wed, 28 Feb 2018 17:46:06 +0300
Message-ID: <15077864.qMPJiTemUy@x200m>
User-Agent: KMail/4.14.1 (Linux/3.16.0-4-amd64; KDE/4.14.2; x86_64; ; )
In-Reply-To: <d22c3a18-31c7-1879-fc11-4c1ce2f5e5af@postgrespro.ru>
References: <d22c3a18-31c7-1879-fc11-4c1ce2f5e5af@postgrespro.ru>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart3355716.fql3LSuDt9";
 micalg="pgp-sha256"; protocol="application/pgp-signature"
Precedence: bulk


--nextPart3355716.fql3LSuDt9
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset="utf-8"

=D0=92 =D0=BF=D0=B8=D1=81=D1=8C=D0=BC=D0=B5 =D0=BE=D1=82 28 =D1=84=D0=B5=
=D0=B2=D1=80=D0=B0=D0=BB=D1=8F 2018 00:46:36 =D0=BF=D0=BE=D0=BB=D1=8C=D0=
=B7=D0=BE=D0=B2=D0=B0=D1=82=D0=B5=D0=BB=D1=8C Nikita Glukhov =D0=BD=D0=B0=
=D0=BF=D0=B8=D1=81=D0=B0=D0=BB:

> I would like to present patch set implementing opclass parameters.
>=20
> This feature was recently presented at pgconf.ru:
> http://www.sai.msu.su/~megera/postgres/talks/opclass_pgconf.ru-2018.p=
df
>=20
> A analogous work was already done by Nikolay Shaplov two years ago:
> https://www.postgresql.org/message-id/5213596.TqFRiqmCTe%40nataraj-am=
d64
> But this patches are not based on it, although they are very similar.=

Hi!

You know, I am still working on this issue.

When I started to work on it, I found out that option code is not flexi=
ble at=20
all, and you can' reuse it for options that are not relOptions.

I gave your patch just a short glance for now, but as far as I can you =
start=20
deviding options into global and local one. I am afraid it will create =
grater=20
mess than it is now.

What I am doing right now, I am creating a new reloption internal API, =
that=20
will allow to create any option in any place using the very same code.

I think it should be done first, and then use it for opclass parameters=
 and=20
any kind of options you like.

The big patch is here https://commitfest.postgresql.org/14/992/ (I am a=
fraid=20
it will not apply to current master as it is, It is quite old. But you =
can get=20
the idea)

The smaller parts of the patch that in current commitfest are

https://commitfest.postgresql.org/17/1536/
https://commitfest.postgresql.org/17/1489/

You can help reviewing them. Then the whole thing will go faster. The s=
econd=20
patch is quite trivial. The first will also need attention of someone w=
ho is=20
really good in understanding postgres internals.

=2D----------

Concerning the patch that you've provided. I've just have a short look.=
 But I=20
already have some question.

1. I've seen you've added a new attribute into pg_index. Why??!!
As far as I can get, if have index built on several columns (A1, A2, A3=
) you=20
can set, own opclass for each column. And set individual options for ea=
ch=20
opclass if we are speaking about options. So I would expect to have the=
se=20
options not in pg_index, but in pg_attribute. And we already have one t=
here:=20
attoptions.I just do not get how you have come to per-index options. Ma=
y be I=20
should read code more attentively...


2. Your patch does not provide any example of your new tool usage. In m=
y=20
prototype patch I've shown the implementation of opclass options for in=
tarray.=20
May be you should do the same. (Use my example it will be more easy the=
n do it=20
from=20scratch). It will give more understanding of how this improvement =
can be=20
used.

3.=20

=2D-- a/src/test/regress/expected/btree_index.out
+++ b/src/test/regress/expected/btree_index.out
@@ -150,3 +150,8 @@ vacuum btree_tall_tbl;
 -- need to insert some rows to cause the fast root page to split.
 insert into btree_tall_tbl (id, t)
   select g, repeat('x', 100) from generate_series(1, 500) g;
+-- Test unsupported btree opclass parameters
+create index on btree_tall_tbl (id int4_ops(foo=3D1));
+ERROR:  access method "btree" does not support opclass options=20
+create index on btree_tall_tbl (id default(foo=3D1));
+ERROR:  access method "btree" does not support opclass options=20

Are you sure we really need these in postgres???

=2D--------------------------------

So my proposal is the following:
let's commit=20

https://commitfest.postgresql.org/17/1536/
https://commitfest.postgresql.org/17/1489/

I will provide a final patch for option engine refactoring in commit fe=
st, and=20
you will write you implementation of opclass options on top of it. We c=
an to=20
it simultaneously. Or I will write opclass options myself... I do not c=
are who=20
will do oplcass options as long it is done using refactored options eng=
ine. If=20
you do it, I can review it.

>=20
>=20
> Opclass parameters can give user ability to:
>   * Define the values of the constants that are hardcoded now in the
> opclasses depending on the indexed data.
>   * Specify what to index for non-atomic data types (arrays, json[b],=

> tsvector). Partial index can only filter whole rows.
>   * Specify what indexing algorithm to use depending on the indexed d=
ata.
>=20
>=20
> Description of patches:
>=20
> 1. Infrastructure for opclass parameters.
>=20
> SQL grammar is changed only for CREATE INDEX statement: parenthesized=

> parameters in reloptions format are added after column's opclass name=
.=20
> Default opclass can be specified with DEFAULT keyword:
>=20
> CREATE INDEX idx ON tab USING am (
>     {expr {opclass | DEFAULT} ({name=3Dvalue} [,...])} [,...]
> );
>=20
> Example for contrib/intarray:
>=20
> CREATE INDEX ON arrays USING gist (
>    arr gist__intbig_ops (siglen =3D 32),
>    arr DEFAULT (numranges =3D 100)
> );
>=20
> \d arrays
>                  Table "public.arrays"
>   Column |   Type    | Collation | Nullable | Default
> --------+-----------+-----------+----------+---------
>   arr    | integer[] |           |          |
> Indexes:
>      "arrays_arr_arr1_idx" gist (arr gist__intbig_ops (siglen=3D'32')=
, arr
> gist__int_ops (numranges=3D'100'))
>=20
>=20
> I decided to store parameters in text[] column pg_index.indoptions ne=
ar to
> existing columns like indkey, indcollation, indclass, indoption. I-th=

> element of indoptions[] is a text array of parameters of i-th index c=
olumn
> serialized into a string.  Each parameter is stored as 'name=3Dvalue'=
 text
> string like ordinal reloptions.  There is another way to store opclas=
s
> parameters: store them in the existing column pg_attribute.attoptions=
 (as
> it was done by Nikolay Shaplov) and there will be no need to serializ=
e
> reloptions to a text array element.
>=20
> Example query showing how parameters are stored:
>=20
> SELECT ARRAY(
>      SELECT (pg_identify_object('pg_opclass'::regclass, opcid, 0)).na=
me
>      FROM unnest(indclass::int[]) opcid
>    ) indclass, indoptions
> FROM pg_index
> WHERE indoptions IS NOT NULL;
>=20
>               indclass             |             indoptions
> ----------------------------------+----------------------------------=
=2D-
>   {gist__intbig_ops,gist__int_ops} | {"{siglen=3D32}","{numranges=3D1=
00}"}
>   {jsonb_path_ops}                 | {"{projection=3D$.tags[*].term}"=
}
> (2 rows)
>=20
>=20
> Each access method supporting opclass parameters specifies amopclasso=
ptions
> routine for transformation of text[] parameters datum into a binary b=
ytea
> structure which will be cached in RelationData and IndexOptInfo struc=
tures:
>=20
> typedef bytea *(*amopclassoptions_function) (
>     Relation index, AttrNumber colnum, Datum indoptions, bool validat=
e
> );
>=20
>=20
> If access method wants simply to delegate parameters processing to on=
e of
> column opclass's support functions, then it can use
> index_opclass_options_generic() subroutine in its amopclassoptions
> implementation:
>=20
> bytea *index_opclass_options_generic(
>     Relation relation, AttrNumber attnum, uint16 procnum,
>     Datum indoptions, bool validate
> );
>=20
> This support functions must have the following signature:
> internal (options internal, validate bool).
> Opclass parameters are passed as a text[] reloptions datum, returned =
pointer
> to a bytea structure with parsed parameter values.
>=20
> Opclass can use new functions parseLocalRelOptions(),
> parseAndFillLocalRelOptions() for reloptions parsing.  This functions=
 differ
> from the standard parseRelOptions() in that a local array of reloptio=
ns
> descriptions is passed here, not a global relopt_kind.  But it seems =
that
> reloptions processing still needs deeper refactoring like the one alr=
eady
> done by Nikolay Shaplov
> (https://www.postgresql.org/message-id/flat/2146419.veIEZdk4E4%40x200=
m#2146
> 419.veIEZdk4E4@x200m).
>=20
>=20
> 2. Opclass parameters support in GiST indices.
>=20
> Parametrized GiST opclass specifies optional 10th (GIST_OPCLASSOPT_PR=
OC)
> support function with the following signature:
> internal (options internal, validate bool)
>=20
> Returned parsed bytea pointer with parameters will be passed to all s=
upport
> functions in the last argument.
>=20
> 3. Opclass parameters support in GIN indices.
>=20
> Everything is the same as for GiST, except for the optional support
> function number which is 7 (GIN_OPCLASSOPTIONS_PROC) here.
>=20
> 4. Opclass parameters for GiST tsvector_ops
> 5. Opclass parameters for contrib/intarray
> 6. Opclass parameters for contrib/ltree
> 7. Opclass parameters for contrib/pg_trgm
> 8. Opclass parameters for contrib/hstore
>=20
> This 5 patches for GiST opclasses are very similar: added optional 's=
iglen'
> parameter for specifying signature length.  Default signature length =
is left
> equal to the hardcoded value that was here before. Also added 'numran=
ges'
> parameter for gist__int_ops.
>=20
>=20
>=20
> We also have two more complex unfinished patches for GIN opclasses wh=
ich
> should be posted in separate threads:
>=20
>   * tsvector_ops: added parameter 'weights' for specification of inde=
xed
>     lexeme's weight groups.  This parameter can reduce index size and=
 its
>     build/update time and can also eliminate recheck.  By default, al=
l
> weights are indexed within the same group.
>=20
>   * jsonb_ops: added jsonpath parameter 'projection' for specificatio=
n of
>     indexed paths in jsonb (this patch depends on SQL/JSON jsonpath p=
atch).
>     Analogically to tsvector_ops, this parameter can reduce index siz=
e and
> its build/update time, but can not eliminate recheck.

=2D-=20
Do code for fun.
--nextPart3355716.fql3LSuDt9
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part.
Content-Transfer-Encoding: 7Bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAABCAAGBQJalsCuAAoJEKc13HowK+H4yq4H/0P/mkKEVPtEwCR/LE0tgP8s
/+LI/KFtheMW+yPWW/Otew2eY14XZq2XVguHoudfmfCtA7sEeO5MANGYWgrhNxNN
u/xlFukPPBNahgUFGSN/fUMGQBcrY7vt6Wg1P7LYdjYJUBx7jTD0BJIbzXosKZeg
GqPCcL2dJE8dEUsjxARcF69+nQlJXsXTdADD6LUaFmBMuMlJMBmNMTdBLO52Scee
NuVCTzzWSsFkg6UGRvkPrEHcwAB1rTko7IInX+sLPVuNly4zDUkI4FX5BQB15PAs
nBGQfcv+g7o+mnbAwdcBLZJe5wlLI6o4FDkMk/zzKXpW4C6jZJpaGeP8DE9MtIo=
=beSg
-----END PGP SIGNATURE-----

--nextPart3355716.fql3LSuDt9--