On Wednesday, November 8, 2017 at 3:24:14 PM UTC-8, [email protected] wrote: > > Over 8 years ago the `indexes` method in the Postgres adapter was updated > to "only include valid non-partial indexes of simple column references": > > > https://github.com/jeremyevans/sequel/commit/7679098eb64dea234143386e2be7031dd4d27091 > > (This line of code has moved through various changes and is now present > here: > https://github.com/jeremyevans/sequel/blob/c133f066b2b663d09549c18d967f0d7ebfff3bb9/lib/sequel/adapters/shared/postgres.rb#L434 > ) > > > > To accomplish this we only select indexes which have the `indcheckxmin` > column set to false when the Postgres version is above 8.3. > > The `indcheckxmin` column was introduced in Postgres 8.3 as part of the > HOT (Heap-Only Tuples) performance improvement. Basically because of how > HOT works a newly created index won't be be usable for certain transactions: > > If [`indcheckxmin` is] true, queries must not use the index until the xmin >> of this pg_index row is below their TransactionXmin event horizon, because >> the table may contain broken HOT chains with incompatible rows that they >> can see > > https://www.postgresql.org/docs/9.6/static/catalog-pg-index.html > > Explanation of why `indcheckxmin` is needed is explained here: > https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT > > I don't fully understand Postgres transaction numbers, but I'm almost > positive that even if `indcheckxmin` is true then eventually all queries > will be able to use the index. > > > > Because `indcheckxmin` *may* mean an index cannot be used, the Sequel > implementation is technically consistent with the explanation of the commit > message. However, I searched through the Postgres source and it seems that > `indcheckxmin` is only ever set to `false` when an index is rebuilt. Since > this is never guaranteed to happen, Sequel may never return some > non-partial simple column reference indexes. > > > > I don't know what the justification was for only returning valid indexes 8 > years ago, but it seems easier to return all indexes and include `is_valid` > / `is_ready` / `is_live` in the returned hash. (There are fields in > pg_index for each of these.) I think most users would expect the `indexes` > method to match the output of `\d table_name` when using psql, which shows > indexes where `indcheckxmin` is true. (If you start psql with the '-E' > option it will show what queries it executes when you run things like \d > table_name, and that way you can see the query that Postgres runs to fetch > all the indexes for a particular table.) >
Unfortunately, this cannot be made the default behavior, as there is quite a bit of existing code (some internal to Sequel) that relies on partial/functional indexes not being returned by Database#indexes. Also, other shared database adapters exclude partial/functional indexes, so for consistency the same should be true of the shared postgres adapter. Potentially more indexes than the default could be supported via a separate option to Database#indexes as an adapter-specific extension, but that would have to be done with care and in a way that makes sense for Sequel and would be simple to maintain. If you want the same results as psql, you can always just run the same queries that psql runs. Database#indexes is designed to be an abstraction with consistent behavior across multiple database types, and is limited in how database-specific it can be. Specifically for indexcheckxmin, it may be possible to drop that filter. One would assume that code calling Database#indexes would not be inside a transaction created before the index was created and also cared that the index was not returned. This is assuming that xmin is always set to a current/past value and not a future value, which should hopefully be a safe assumption. Hopefully someone with more knowledge of the PostgreSQL internals than I can confirm that. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
