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_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}
What I wanted to really add, was two new system views, pg_ownerships and pg_privileges [1]. The pg_get_acl() was a dependency that we needed to get in place first. In the end, I withdrew the patch trying to add these views. If there is enough interest from users, I might consider picking up the task of trying to work out the remaining obstacles.
Do people here need pg_ownerships and/or pg_privileges?
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.
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:
What I wanted to really add, was two new system views, pg_ownerships and pg_privileges [1]. The pg_get_acl() was a dependency that we needed to get in place first. In the end, I withdrew the patch trying to add these views. If there is enough interest from users, I might consider picking up the task of trying to work out the remaining obstacles.Do people here need pg_ownerships and/or pg_privileges?
[1] https://www.postgresql.org/message-id/flat/bbe7d1cb-0435-4ee...
Yes!
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.
Think about schema migrations, and the need to be compatible with the new and old versions of the schema.
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.
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.
Some more discussion: https://news.ycombinator.com/item?id=45372283
Thanks! Macro-expanded:
PostgreSQL 18 Released https://news.ycombinator.com/item?id=45372283 - 3 days ago, 21 comments