What’s New in PostgreSQL 18 – a Developer’s Perspective
1–10 of 29 posts
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#2It 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_shdepend AS s
JOIN pg_catalog.pg_database AS d
ON d.datname = current_database() AND
d.oid = s.dbid
JOIN pg_catalog.pg_authid AS a
ON a.oid = s.refobjid AND
s.refclassid = 'pg_authid'::regclass
WHERE s.deptype = 'a';
-[ RECORD 1 ]-----------------------------------------
type | table
schema | public
name | testtab
identity | public.testtab
acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
Do people here need pg_ownerships and/or pg_privileges?
[1] https://www.postgresql.org/message-id/flat/bbe7d1cb-0435-4ee...
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#3I 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.
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#4I 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'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.
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#5I 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.
Think about schema migrations, and the need to be compatible with the new and old versions of the schema.
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#6I 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.
[deleted]
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#7Some more discussion: https://news.ycombinator.com/item?id=45372283
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#8I 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'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".
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#9Earlier 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".
Oh thanks I missed the distinction between a virtual and regular generated column.
Re: What’s New in PostgreSQL 18 – a Developer’s Perspective
#10I 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.
Much faster to do it in the database than in the client, especially in a normal situation where the postgres server is on a different machine that's possibly not even in the same building as the app server or where it allows you to use existing indexes.
I agree that they're often a symptom of bad schema design or data normalisation though. Sometimes that can't be helped however.