I've been conducting a systematic performance analysis of our primary PostgreSQL 16 instance since the last Ideogram platform update (version 2.4.7), and I have identified a reproducible regression concerning calculated fields defined within the data model layer. Specifically, computed columns that rely on subqueries or window functions are now either returning null values or throwing permission errors, whereas they functioned correctly under version 2.4.5. This appears to be independent of the underlying database engine, as my test suite shows identical failures on both our production Aurora PostgreSQL cluster and a local PostgreSQL instance.
The failure pattern is not uniform. I have categorized the breaking changes into three distinct types based on my diagnostic queries:
1. **Subquery-Dependent Calculated Fields:** Fields that perform a correlated subquery to aggregate data from a related table.
```sql
-- Example of a now-failing calculated field definition
ALTER TABLE orders ADD COLUMN total_item_count integer GENERATED ALWAYS AS (
(SELECT COUNT(*) FROM order_items WHERE order_items.order_id = orders.id)
) STORED;
```
Result: This now consistently returns NULL for all rows, and the query plan shows the subquery is being optimized out entirely.
2. **Window Function-Based Fields:** Computed columns utilizing `ROW_NUMBER()`, `SUM() OVER (PARTITION BY ...)`, etc.
```sql
ALTER TABLE user_sessions ADD COLUMN session_sequence_num integer GENERATED ALWAYS AS (
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at)
) STORED;
```
Result: Error: `[42883] ERROR: window functions are not allowed in generated columns`
3. **Permission Errors on Materialized Views:** Several calculated fields are materialized asynchronously into summary tables (materialized views). The Ideogram refresh job now fails with:
```
ERROR: permission denied for schema ideogram_internal
```
This suggests a change in the security context or ownership chain of the internal schema used for materialization.
My immediate workaround has been to replace the stored generated columns with traditional views for the subquery and window function cases, but this negates the performance benefits of pre-computation and increases load on the OLTP database.
I am seeking to confirm if this is a known issue and to understand the technical rationale behind the change. Was this an intentional restriction placed on the expression evaluator for generated columns, perhaps for stability or performance reasons in the new update? Furthermore, what is the recommended migration path? Should we be moving all complex calculated logic into application-side triggers, or is there a configuration flag to revert to the previous, more permissive behavior?
For the team's investigation, here is the environment detail from my benchmark setup:
- Ideogram Version: 2.4.7 (Docker image `ideogram/ideogram-core:2.4.7`)
- Primary Database: PostgreSQL 16.2 on AWS Aurora
- Calculated Field Count: 147
- Breaking Field Count: 23 (all of types 1 and 2 above)
Interesting. I've seen similar issues before where a platform update changes how it handles generated column security contexts. The permission errors especially point to the session user or the definer role being set differently when those subqueries execute.
Have you checked the query logs to see the exact SQL statement the platform is generating and the user it's connecting as? Sometimes the generated column's calculation runs with the privileges of the table owner, but a change in the connection pool or session variable settings can switch that to a less privileged user.
grep is my friend.
That's a good call about the session user. I ran into something like that last year with a different tool, but it was after a driver update, not a platform update. In that case, the connection pool started using a new default user for certain background jobs, and all the calculated fields on our dashboards broke because that user only had read access.
Your point makes me wonder if Ideogram 2.4.7 changed how it manages database sessions for these background calculations. Maybe it's now using a separate, lower-privileged connection pool for performance, and those calculated fields weren't designed with that in mind. The permission errors popping up now would make total sense.
Happy reviewing!