11–20 of 29 posts
It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article: postgres=# SELECT (pg_identify_object(s.classid,s.objid,s.objsubid)).*, pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl FROM pg_catalog.pg_…
Some more discussion: https://news.ycombinator.com/item?id=45372283
PostgreSQL 18 Released https://news.ycombinator.com/item?id=45372283 - 3 days ago, 21 comments
I always find it hard to think of a good reason for a (computed) virtual column Why would you ever force your db to multiply a value by 12 to another column, or parse a json path, if it’s not for filtering? Move that effort to your clients so you’re not needlessly consuming db resources.
I always find it hard to think of a good reason for a (computed) virtual column Why would you ever force your db to multiply a value by 12 to another column, or parse a json path, if it’s not for filtering? Move that effort to your clients so you’re not needlessly consuming db resources.
Having computed (stored or virtual) columns would've been awesome.
The use case isn't really "multiply a value by 12", but more like "we have a single boolean is_active column, and want to migrate to a more extensive status model" or "migrate from an is_active column to a (begin, end) timestamp tuple" or so.
With a virtual column, you can present a read-only, compatible column to the legacy application, while the other applications can use the more detailed, new columns, without having to keep the legacy column in sync.
I always find it hard to think of a good reason for a (computed) virtual column Why would you ever force your db to multiply a value by 12 to another column, or parse a json path, if it’s not for filtering? Move that effort to your clients so you’re not needlessly consuming db resources.
I've worked in an environment where 3 applications accessed the same database, one of the applications wasn't really maintained. Having computed (stored or virtual) columns would've been awesome. The use case isn't really "multiply a value by 12", but more like "we have a single boolean is_active column, and want to migrate to a more extensive status model" or "migrate from an is_active column to a (begin, end) times…
I always find it hard to think of a good reason for a (computed) virtual column Why would you ever force your db to multiply a value by 12 to another column, or parse a json path, if it’s not for filtering? Move that effort to your clients so you’re not needlessly consuming db resources.
Earlier quoted context omitted.
I'm not a fan of stored procedures but this is lightweight enough that I like how it simplifies by removing responsibility from the code. I imagine the computed column could be indexed or materialized if needed.
> I imagine the computed column could be indexed or materialized if needed. The article mentions that "you cannot create indexes on VIRTUAL generated columns".
I always find it hard to think of a good reason for a (computed) virtual column Why would you ever force your db to multiply a value by 12 to another column, or parse a json path, if it’s not for filtering? Move that effort to your clients so you’re not needlessly consuming db resources.
I've worked in an environment where 3 applications accessed the same database, one of the applications wasn't really maintained. Having computed (stored or virtual) columns would've been awesome. The use case isn't really "multiply a value by 12", but more like "we have a single boolean is_active column, and want to migrate to a more extensive status model" or "migrate from an is_active column to a (begin, end) times…
Earlier quoted context omitted.
I've worked in an environment where 3 applications accessed the same database, one of the applications wasn't really maintained. Having computed (stored or virtual) columns would've been awesome. The use case isn't really "multiply a value by 12", but more like "we have a single boolean is_active column, and want to migrate to a more extensive status model" or "migrate from an is_active column to a (begin, end) times…
I think it /can/ also be to 'multiply a number by 12'. For example lets say I'm a supermarket, who gets their order data out of a 20 year old IT system and as a result I've got a orders table which has a id, user_id and data column with an array of 'sku, price and qty'. If I regularly want to query/sort/filter based on total order value the easiest and most performant solution absolutely is to use a computed column.
In case you only want to filter without returning values, you could also index directly on the expression without needing to add a stored generated column with an index on it
It made me happy to see the pg_get_acl() function that I was involved in adding, is appreciated by users. I think there is still much improvement in the space of querying privileges. I think most users would probably struggle to come up with the query from the article: postgres=# SELECT (pg_identify_object(s.classid,s.objid,s.objsubid)).*, pg_catalog.pg_get_acl(s.classid,s.objid,s.objsubid) AS acl FROM pg_catalog.pg_…