v1.7.0: List columns, Postgres connects with psycopg 3

SLayer 1.7.0

This release adds list columns, and changes the driver a Postgres datasource connects with.

List columns

If your tables have array columns (the skills of a user, the tags on a ticket, the SKUs on an order), SLayer now ingests them as lists instead of text. Test a whole list with contains(skills, 'sql'), contains_any(...), contains_all(skills, ['go', 'sql']) and length(skills). Use skills.value as a dimension or filter to work with single elements, for example to count users per skill, without double counting anything else in the query. The new collect(x) aggregation builds a list per group. Lists work on Postgres, DuckDB, BigQuery, Snowflake, ClickHouse, Trino and Presto; other databases refuse them with an error. Re-ingest to turn array columns you saved as text into lists; the re-ingest lists every saved measure, derived column, model filter and saved query the change breaks. See List Columns.

Postgres connects with psycopg 3

A Postgres URL or config that names no driver now connects with psycopg (v3) instead of psycopg2 and asyncpg. The postgres extra installs it; if you installed a driver yourself, install psycopg or name your driver in the URL (postgresql+psycopg2://...). A URL that names a driver now always uses that driver, so its SSL and other settings are no longer lost. Connecting with psycopg2, asyncpg or aiomysql now warns that the extras stop installing them in 2.0.

Other changes

• Whole-number results are now typed INT instead of DOUBLE when SLayer probes a query's column types, for example for SQL-backed models and OSI imports.
• If you wrote your own SQL client for 1.6.0, it keeps working but warns: rename its get_column_types to get_result_column_types, which returns SLayer types and the database's type names.
• validate_models no longer deletes a column whose type changed while the column still exists: it reports a retype finding with the fix, and slayer validate-models --force-clean leaves the column and everything built on it in place.
• A model aggregation named like a built-in in another case (Sum) no longer replaces the built-in sum; only one named exactly sum does, as in 1.6.0.
• Postgres money and SQL Server MONEY / SMALLMONEY columns are exact numbers: their sums are no longer cast to floats.

Additional technical details

• get_result_column_types returns {column: ResultColumnType(type, db_type)}. The 1.5.0 category probes (get_column_types_sync, SlayerSQLClient.get_column_types, SlayerQueryEngine.get_column_types) still work and warn until 2.0.
• A client that serves only the 1.6.0 get_column_types (including a SqlClient subclass, or a DelegatingSqlClient subclass overriding it) is probed through that method until 2.0, its categories read as DOUBLE, TEXT, TIMESTAMP and BOOLEAN; DelegatingSqlClient.get_column_types is back, deprecated.
• The deprecated category probes answer what 1.6.0 answered for every type the database names (for example interval and MySQL YEAR are time, MySQL BIT is number); a column the database gives no type takes its SLayer type's category.
• The list_retype finding (new in this release) became the retype finding, which also covers scalar columns and names the live database type.
• IdempotentIngestResult.broken_dependents and the broken_dependent warning name each definition a saved list re-type breaks and the column that breaks it, or one whose check could not run.
• ReflectedColumn.type_name carries the database's name for every reflected column.
• A cast to INT now renders as BIGINT.
• On asyncpg a probed NUMERIC column has no scale and is typed DOUBLE.
• When SQL Server cannot describe a statement (a temporary table), a probed decimal column is named decimal(<precision>,<scale>) and keeps its exact type.
• The SQL client holds one async engine per event loop, and aclose() releases only the running loop's engine. On Windows' ProactorEventLoop, psycopg runs in sync mode in a worker thread.
• sqlite+aiosqlite:// URLs are refused with UnsupportedDriverError.
• A model's custom aggregation may now share a name with a library function and shadows it inside that model, with a warning. A stored time granularity named like a reserved library function is renamed on load, and every reference to it is rewritten. A query sent from outside SLayer that names the old granularity fails.
• A derived column with a declared exact db_type returns Decimal values; list cells render as JSON in markdown and CSV output; dialect hooks changed signature (custom dialects only).