What’s New in Postgresql 18 – a Developer’s Perspective

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?

[1] https://www.postgresql.org/message-id/flat/bbe7d1cb-0435-4ee...

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.

Some more discussion: https://news.ycombinator.com/item?id=45372283

During the past 15 years I have been using Postgres quite a bit. I have also almost exclusively have been accessing databases using an ORM. Love them or hate them, they do provide value for a lot of common cases and speed up development and debugability, or at least they did for me and I know they remain very popular.

This all meant that as databases like Postgres keep adding cool new features they mostly go unused because an ORM just doesn’t let you pierce that layer of abstraction except dropping to pure SQL which is typically seen as a code smell and an annoyance to everyone involved.

So on the one hand I love that Postgres is getting amazing new features, not to mention all the cool extensions. On the other hand I and I suspect many others are essentially locked out of them and since most ORMa try to serve multiple databases they typically only include the most common denominator features. As I get more experienced I both see why RDBMS is the right choice most times AND see the appeal of an object store instead of a row store.

One obvious thing I still can’t believe pg doesn’t have is the ability to define triggers at the database or schema level. I must have written code to mass generate DROP/CREATE TRIGGER probably 5 times (yes I know you can reuse the trigger procedure itself). And then you need to remember to re-run whenever tables are added/removed.

For me: computed columns in change stream records. Super nice.

We use them to pull out some fields out of JSON blobs, and until PG18 they were not available for logical replication log consumers.