On Wed, 28 Aug 2024 at 16:21, Sushrut Shivaswamy <sushrut.shivasw...@gmail.com> wrote: > > Hi, > > I'm trying to create an Index Access Method Roting. > Building the index requires iterating over all rows and calculating, > which is then used during index construction. > > The methods in the IndexAmRoutine seem to deal with insertion / index build > one row at a time. > Is there any workaround you can suggest that would allow me to calculate the > median of a column, > store it someplace and then use it during Inserts to the index?
I'm not sure what to say. Index insertions through indam->aminsert happen as users insert new values into the table, so I don't see how a once-calculated median would remain correct across an index's lifespan: every time I insert a new value (or delete a tuple) the median will change. Furthermore, indexes will not know about deletions and updates until significantly after the deleting or updating transaction got committed, so transactionally consistent aggregates are likely impossible to keep consistent while staying inside the index AM API. However, if you only need this median (or other aggregate) at index build time, you should probably look at various indexes' indam->ambuild functions, as that function's purpose is to build a new index from an existing table's dataset, usually by scanning the table with table_index_build_scan. As for storing such data more permanently: Practically all included indexes currently have a metapage at block 0 of the main data fork, which contains metadata and bookkeeping info about the index's structure, and you're free to do the same for your index. I hope that helps? Kind regards, Matthias van de Meent Neon (https://neon.tech)
