v1.0.0b55

Fixed

• Wrap portal_catalog._catalog.indexes[name] access (#137, PR #138). Plone and addon code commonly reaches into the catalog via the non-API-conform pattern cat._catalog.indexes[name] / .get(name) / .items(). Previously this returned raw ZCatalog index objects with empty BTrees, so queries silently returned no results. _CatalogCompat.indexes is now a property returning a transient view that wraps each index with PGIndex (same behavior as catalog.Indexes[name]). Custom PATH-type indexes and other special indexes (idx_key=None) continue to be returned raw.

  Based on prior prototyping by @thet on the thet/indexes-wrapper branch.

  Migration: GenericSetup profile bumped from v1 to v2. The upgrade step renames the persisted indexes attribute to _raw_indexes and sets __parent__ on the compat so aq_parent can reach the catalog tool. Run Plone Site Setup → Add-ons → plone.pgcatalog → Upgrade on existing sites, or let the next runAllImportSteps on the default profile pick it up.

  Likely-affected callers include plone.base.utils.check_id (reserved-name check), plone.restapi.search.query.Query.get_index, plone.app.discussion, plone.app.referenceablebehavior, plone.volto, collective.collectionfilter, and collective.exportimport — per-package verification is recommended after upgrade.

Full changelog: v1.0.0b54...v1.0.0b55

v1.0.0b54

Changed

• Stop duplicating path, path_parent, and path_depth between typed columns and idx JSONB (#132, PR #135). These three fields now live exclusively in their typed columns on object_state. Previously identical values were stored in both places, wasting ~10 % of JSONB storage and — more importantly — blocking the PG planner from collecting per-column selectivity statistics on path-subtree filters. Indexes (8) and extended statistics (3) on these fields have been migrated to reference the typed columns directly. Custom PATH-type indexes (e.g. tgpath) are unaffected and continue to store their data in idx.

  Migration: Schema changes are picked up automatically on startup via idempotent DROP … IF EXISTS + CREATE … IF NOT EXISTS. To strip the obsolete keys from existing JSONB on large catalogs:

  from plone.pgcatalog.migrations.strip_path_keys import run
  run(conn, batch_size=5000)

  Safe to run online, idempotent, batched, restores connection state on exit.

Full changelog: v1.0.0b53...v1.0.0b54

v1.0.0b53

Fixed

• Migration install handler silently dropped every DateRecurringIndex (DRI) — e.g. plone.app.event's / bda.aaf.site's general_start / general_end — when replacing a foreign portal_catalog with PlonePGCatalogTool. _snapshot_catalog correctly captured the stored attr_recurdef / attr_until attributes, but _build_extra had no DRI branch, so the restored extra namespace lacked the recurdef / until keys that DateRecurringIndex.__init__ reads. The constructor raised AttributeError, the outer try/except in _restore_from_snapshot swallowed it as a warning, and the index was never created — which meant extract_idx never indexed those fields, the IndexRegistry had no entry for them, and every site-wide Collection filtering on general_end returned zero results.

  Added the DRI translation in _build_extra, plus a roundtrip test that actually instantiates DateRecurringIndex with the built extra. Issue #126.

  Existing deployments that migrated on an affected build have to re-add the missing indexes manually (the upgrade can't recover them without the original catalog snapshot). See CHANGES.md for the recipe.

Added

• Slow-query suggestions now produce covering composite indexes for the common portal_type + effectiveRange + sort_on=effective pattern (issue #122). The suggestion engine splits the legacy _NON_IDX_FIELDS into purpose-specific constants, expands effectiveRange to its effective date contributor, and appends the query's sort_on field as a trailing btree composite column so the planner can skip the ORDER BY sort step.

See #128, #129.

v1.0.0b52

Fixed

• CatalogStateProcessor._enqueue_tika_jobs indexed result rows by integer position (row[0], row[1]), but the request-scoped connection pool uses a dict_row factory, so every content save that produced an unresolved blob ref raised KeyError: 0 during tpc_vote (e.g. uploading a Dexterity Image). Switched to column-name access.

  Existing tests didn't catch this because the integration tests opened their cursor with tuple_row and the unit tests mocked fetchall() with tuple rows — both diverged from production. Tests updated to use dict_row to match the real pool.

See #124 and follow-up #125.

v1.0.0b51

Added

• Multivariate PostgreSQL statistics for every default composite catalog index on object_state, plus extras for published-content filters (type + effective, type + expires). Without these, PG's per-column histograms treat JSONB-expression conditions as independent and underestimate joint selectivity, so the planner picks composite-index scans + heap filters over thousands of tuples instead of doing Bitmap-AND with available GIN indexes.

  On a published-Event navigation query observed in production: 911 ms → sub-100 ms.

  Stats kinds chosen per cardinality profile:

  • mcv + dependencies for low-cardinality pairs (type + state, parent + type, etc.)
  • dependencies only for path-pairs (CMS paths are essentially unique per row; mcv would be wasteful)

  On existing installations a one-shot ANALYZE object_state runs on the first write transaction after upgrade so the new statistics take effect immediately rather than waiting for autovacuum. Idempotent via pg_stats_ext skip check. Multi-pod safe.

  Issue #122 — first of three PRs (engine refactor + EXPLAIN-driven coverage to follow).

v1.0.0b50

Fixed

• release_request_connection now issues an explicit conn.rollback() before returning the connection to the pool. Otherwise an implicit transaction opened by a prior SELECT on the pool fallback path stays alive, holding a virtualxid that blocks CREATE INDEX CONCURRENTLY. Companion fix to bluedynamics/zodb-pgjsonb#58 (the storage-conn path). Closes #118.

• Suggested Indexes UI detects already-applied suggestions with mixed-case field names (e.g. Language) by matching index names case-insensitively — PostgreSQL folds unquoted identifiers to lowercase. Also strengthens expression normalization (whitespace around ->>, iterative paren collapse, WHERE-anchored extraction) so generated and PG-stored indexdef forms compare equal. apply_index is now idempotent when a valid index with the same name already exists — returns success no-op instead of propagating the DuplicateTable error. Closes #119.

• Tika enqueue: resolve Dexterity NamedBlobFile / NamedBlobImage wrapper OIDs via a second-hop lookup through object_state, so the queue receives jobs for modern Dexterity File/Image content. Previously _enqueue_tika_jobs() only looked up the OIDs it found in the content's state — which are the wrapper OIDs, not the inner ZODB.blob.Blob OIDs. The direct lookup returned zero rows and the enqueue silently skipped. Flat-state content (legacy/Archetypes-style, where the content state carries a direct ZODB.blob.Blob @ref) is unchanged. Closes #115.

• _handle_uuid now accepts list/tuple queries (uses = ANY(...)), matching _handle_field semantics. Previously a list query such as catalog.searchResults(UID=['f852...']) was stringified as str(['f852...']) → "['f852...']" and the JSONB ->> comparison never matched, so @@getVocabulary?name=plone.app.vocabularies.Catalog with a plone.app.querystring.operation.list.contains criterion on UID returned an empty vocabulary.

• catalog._catalog.getIndex(name) now returns a PGIndex wrapper with PG-backed _index and uniqueValues(), same as catalog.Indexes[name]. Previously it returned the raw ZCatalog index with empty BTrees, which broke:

  • plone.app.vocabularies.KeywordsVocabulary (empty Subject/Tags dropdowns).
  • Products.CMFPlone.browser.search.Search.types_list() (empty "Item type" filter in @@search).
  • plone.app.event.setuphandlers (DateIndex detection).
  • Other Plone code paths that bypass catalog.Indexes[name].

  Special indexes registered with idx_key=None (SearchableText, path, effectiveRange) are returned unwrapped so dedicated columns are used for them.

v1.0.0b49

Added

• Log all catalog queries for debugging via PGCATALOG_LOG_ALL_QUERIES=1 (#109). Runtime-toggleable, logs duration + SQL + params + keys at INFO level. Parameter repr truncated at 2000 chars to bound log size. See how-to/debug-queries for the production-safety note about PII in query params.

• Slow-query log format changed slightly: prefix is now Slow SQL catalog query (%.2f ms) instead of Slow catalog query (%.1f ms). Log-aggregation grep patterns may need an update.

Fixed

• clearFindAndRebuild works on fresh installs (#112). The rebuild now walks ISiteRoot breadth-first instead of relying on a PG path snapshot, so bootstrapping plone-pgcatalog on an existing ZODB correctly re-indexes all content. Memory-flat (path-string queue, no acquisition chains). Includes discussion items via IConversation adapter when plone.app.discussion is installed.

• Boolean query values are stringified to JSON notation 'true'/'false' so queries against JSONB ->> comparisons match (#110). Previously str(True) produced 'True' which never matched JSONB's lowercase form. Fix applied in query.py, pgindex.py, eeafacetednavigation.py, and backends.py.

• Index suggestion DDL now uses double parentheses around boolean casts: ((idx->>'field')::boolean) — single parens were a PG syntax error, breaking the ZMI "Apply" button for boolean suggestions (#106).

• Graceful fallback in _load_idx_batch() when the meta column is missing (#105). Prevents UndefinedColumn crash on first read after upgrade. Root cause fix in zodb-pgjsonb 1.10.4.

• ZMI error display: manage_apply_index / manage_drop_index now show errors in red (Bootstrap alert-danger) instead of green (#104).

v1.0.0b48

Fixed

• Fix startup warnings "security declaration for nonexistent method" for
  unsupported ZCatalog stubs (getAllBrains, searchAll, etc.).

Changed

• Extract @meta, object_provides, and allowedRolesAndUsers from
  idx JSONB into dedicated columns via generic ExtraIdxColumn mechanism.
  Reduces idx size by ~85% (from ~3.2 KB to ~400 B avg, below TOAST
  threshold). Run clear_and_rebuild after upgrading. (#98)
• object_provides queries now use a dedicated TEXT[] column with GIN
  index instead of JSONB containment.
• Removed _backfill_allowed_roles startup function (superseded by
  generic extraction mechanism).

v1.0.0b47

Fix ValueError on empty path query — lineage.index passes path='' which ZCatalog silently ignores.

v1.0.0b46

Fixed

• Query cache: use catalog-specific change counter instead of MAX(tid) (#94). The cache was invalidated on every ZODB write (~2500/hour from ScalesDict alone), making it nearly useless (~28% hit rate). Now uses pgcatalog_change_seq which only increments on actual catalog writes. Expected hit rate 90%+ on typical sites.