devrimgunduz/pagila pagila-v4.1.0

4.1.0 - More SQL features

on GitHub

Version 4.1.0

• Add pgvector support and a film_embedding table with a vector(20) per film — a multi-hot encoding of its categories plus a few normalized numeric attributes, engineered from existing film/film_category data rather than a real text-embedding model — indexed with HNSW (vector_cosine_ops) and documented in the README with cosine-distance nearest-neighbor examples.

• Add pagila-sql-pgq-setup.sql, declaring a pagila_graph property graph over the existing actor/film/category vertex tables and film_actor/film_category edge tables (no new data), demonstrating PostgreSQL 19's SQL/PGQ (CREATE PROPERTY GRAPH/GRAPH_TABLE) with MATCH pattern examples in the README, verified against a postgres:19beta2 container, with a matching panel added to pagila-schema-diagram.png

• Add film.length_hours, a VIRTUAL generated column (round(length / 60.0, 2)) demonstrating PostgreSQL 18's new virtual generated columns, documented in the README alongside a note that (unlike STORED) virtual generated columns can't be indexed directly

• Add pagila-temporal-setup.sql, declaring film_price_history (WITHOUT OVERLAPS primary key on film_id/a daterange) and store_film_price_override (a temporal foreign key back to it), demonstrating PostgreSQL 19's SQL:2011 application-time temporal support — WITHOUT OVERLAPS keys, temporal foreign keys, and UPDATE/DELETE ... FOR PORTION OF row-splitting DML — with overlaps/containment predicate examples in the README, verified against a postgres:19beta2 container, with a matching panel added to pagila-schema-diagram.png

• Install pg_partman and pgvector in the Dockerfile from the PGDG apt repo instead of building both from source, now that the repo carries current packages for each