Thread: passing parameters to CREATE INDEX
Just wanted to make clear to Hackers that the gates are now open to include other parameters for CREATE INDEX, as originally requested here: http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php The new WITH (param=value...) syntax could easily be extended to include a variety of other parameters for each different index AM. http://developer.postgresql.org/docs/postgres/sql-createindex.html Thanks to Itagaki Takahiro and various reviewers/committers. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs <simon@2ndquadrant.com> writes: > Just wanted to make clear to Hackers that the gates are now open to > include other parameters for CREATE INDEX, as originally requested here: > http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php Just to follow up on the discussion of that thread: what's been implemented is a way to store arbitrary name=value strings in an index's pg_class entry, and to make these available in a pre-parsed form through the index relcache entry. However you'd have to be cautious about using the values directly for any fundamental index structure decisions, because ALTER INDEX will just change them without giving you an opportunity to modify the index in response. So depending on what you are doing, you might need to store the "real" values in the index metapage, and set those values from the reloptions parameters only at ambuild() time. This would mean that ALTER INDEX + REINDEX would be the procedure needed to change the structure of an existing index. OTOH, if you can tolerate on-the-fly changes of a parameter, then using it directly from the rd_options struct would be reasonable. Also: as of CVS tip ginoptions() accepts FILLFACTOR but nothing is done with it. Can you do something useful with FILLFACTOR in GIN? regards, tom lane
>> http://archives.postgresql.org/pgsql-hackers/2005-09/msg00851.php > > Just to follow up on the discussion of that thread: what's been > implemented is a way to store arbitrary name=value strings in an index's > pg_class entry, and to make these available in a pre-parsed form through > the index relcache entry. However you'd have to be cautious about using > the values directly for any fundamental index structure decisions, > because ALTER INDEX will just change them without giving you an > opportunity to modify the index in response. So depending on what you > are doing, you might need to store the "real" values in the index > metapage, and set those values from the reloptions parameters only at > ambuild() time. This would mean that ALTER INDEX + REINDEX would be the I see. There is one more problem: pluggable parameters for index. For example, the parameter needed for tsearch2 (size of signature) isn't useful for others modules/opclasses. Another issue, GiST (and GIN too) doesn't have metapage at all for now, it's not a problem, but until now it wasn't needed. I think, we may can add to pg_opclass's definition method/parameter name and create some API (may be, index specific) to propagate parameter's to module's interface functions to index. > Also: as of CVS tip ginoptions() accepts FILLFACTOR but nothing is done > with it. Can you do something useful with FILLFACTOR in GIN? Now GIN is nested B-Tree: B-tree for entries (lexemes for tsearch2) and B-Tree for ItemPointers per entry if entry is popular enough. So fillfactor may be used as usual. Small advertising :) : http://www.sigaev.ru/gin/GinStructure.pdf -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes:
> can add to pg_opclass's definition method/parameter name and create some API
> (may be, index specific) to propagate parameter's to module's interface
> functions to index.
Huh? You can get them from the index's Relation structure. I don't
think there's anything missing in the API. About all you need is an
extended struct definition for rd_options, and to provide your own code
substituting for default_reloptions(). An index AM can do both of those
locally to itself.
regards, tom lane
Teodor Sigaev <teodor@sigaev.ru> wrote: > pluggable parameters for index. > I think, we may > can add to pg_opclass's definition method/parameter name and create some API > (may be, index specific) to propagate parameter's to module's interface > functions to index. How abount adding a new option hander to GiST/GIN support functions? Presently, amoptions() are defined as bytea *amoptions (ArrayType *reloptions, bool validate). If there is a support function like this form in operator classes, we can propagate options. Eventually, calling sequence will be DefineIndex() -> amoptions() -> tsvectoroptions(). We also need to change gist/ginoptions() in order to determine what operator class is used. This is a bit messy problem. Options are parsed before support functions are initialized, so that I don't know in what form we should pass the operator class to amoptions(). Regards, --- ITAGAKI Takahiro NTT Open Source Software Center