SQLite Trigger Affinity Change and Spock 5.0.7 Release for PostgreSQL Replication
Check SQLite trigger logic to cast NEW/OLD values explicitly and test Spock 5.0.7 replication in staging before production.
Update your SQLite trigger logic to explicitly cast NEW/OLD values and test Spock 5.0.7 replication in a staging environment to validate behavior before production rollout.
Summary
SQLite recently changed how trigger pseudo‑columns NEW and OLD inherit data type affinity, defaulting to NUMERIC instead of the table column’s declared type. This subtle shift can cause unexpected type conversions in trigger logic, especially when comparing TEXT values or performing numeric operations. Developers must now explicitly cast NEW/OLD values or adjust trigger code to maintain data integrity after the update.
In parallel, EnterpriseDB released Spock 5.0.7, a PostgreSQL extension that provides reliable logical multi‑master replication for active‑active setups. Spock supports fine‑grained table and operation replication, enabling high availability and geographic distribution without primary‑standby constraints. The 5.0.7 release includes performance improvements, bug fixes, and is distributed under the permissive PostgreSQL license, making it suitable for both small and large deployments. The extension allows developers to configure replication slots and replication policies directly within PostgreSQL, simplifying multi‑master architecture.
Key changes
- NEW/OLD columns in SQLite triggers now default to NUMERIC affinity, breaking implicit type inheritance.
- Trigger logic may produce unexpected type conversions; developers must explicitly cast values.
- Spock 5.0.7 introduces reliable logical multi‑master replication for PostgreSQL.
- Spock supports fine‑grained table and operation replication across active‑active clusters.
- Spock 5.0.7 includes performance enhancements and bug fixes for replication stability.
- Spock is distributed under the permissive PostgreSQL license, enabling open‑source use.
- The new replication model allows geographic distribution and high availability without primary‑standby.
- Spock’s logical replication can be configured via PostgreSQL extensions and replication slots.