Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL 15

postgresql.org

Post reply on HN
Print view View original

81–90 of 132 posts

Re: PostgreSQL 15

#81

Post by Uninen »

jeff-davis wrote: post #67
morley wrote: post #25

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

The version support is five years: https://www.postgresql.org/support/versioning/ As long as you stay well ahead of that, you are fine. However, I'd strongly recommend testing your applications with new versions as they come out, or in beta (even better). You may learn about new features, find bugs in your application, find bugs in your migration processes, etc. If you bring any problems to the community right away,…

According to https://www.postgresql.org/support/versioning/ the "Final Release" (whatever that means) for v14 is November 2026, and for v15 is November 2024. I wonder if it's a typo or some LTS thing.

Re: PostgreSQL 15

#83

Post by praveenweb »

Interestingly, there are no breaking changes that were required to be addressed by Hasura GraphQL Engine to support Postgres 15. Hasura is fully compatible with this release, with the potential of adding the MERGE command via the GraphQL API soon.

Excited about the incremental performance improvements and making more secure defaults by revoking CREATE permission for public schema for non-superusers.

Re: PostgreSQL 15

#84

Post by NegativeLatency »

brunooliv wrote: post #58

Could someone give some examples on their own domains where the MERGE command is a huge QOL improvement over what's currently available? I see a lot of people being so very happy in the comments, and, well, I've tried to think long and hard about how to apply it to my current domain but was a bit at a loss... Maybe some practical examples can help?

Works well for bulk operations where you're loading data in on a lower frequency.

Re: PostgreSQL 15

#85

Post by gen220 »

brunooliv wrote: post #58

Could someone give some examples on their own domains where the MERGE command is a huge QOL improvement over what's currently available? I see a lot of people being so very happy in the comments, and, well, I've tried to think long and hard about how to apply it to my current domain but was a bit at a loss... Maybe some practical examples can help?

The operation it's replacing is something like "SELECT, followed by UPDATE/INSERT".

Implicit in that sequence is transmitting the selected rows over the network, and buffering the rows in-memory on the client side.

With MERGE, you eliminate the network stress, and push the burden of managing the rows in-memory onto the postgres server.

That's quite nice if you have beefy operations and want to keep the services/jobs running those operations lean.

Re: PostgreSQL 15

#87

Post by tiffanyh »

hellcow wrote: post #19

> PostgreSQL 15 lets users create views that query data using the permissions of the caller, not the view creator. This option, called security_invoker, adds an additional layer of protection to ensure that view callers have the correct permissions for working with the underlying data. Thank you, kind friends. This is a huge QOL improvement when using row-level security with views and is the top reason I'll be upgrad…

This should have always been the default behavior. Is it the default now?

Shocked it never was since it is a major source of data leaks (which completely defeats the purpose of using row-level security).

Re: PostgreSQL 15

#88

Post by CodeIsTheEnd »

This release includes a feature I added [1] to support partial foreign key updates in referential integrity triggers!

This is useful for schemas that use a denormalized tenant id across multiple tables, as might be common in a multi-tenant application:

    CREATE TABLE tenants (id serial PRIMARY KEY);
    CREATE TABLE users (
      tenant_id int REFERENCES tenants ON DELETE CASCADE,
      id serial,
      PRIMARY KEY (tenant_id, id),
    );
    CREATE TABLE posts (
        tenant_id int REFERENCES tenants ON DELETE CASCADE,
        id serial,
        author_id int,
        PRIMARY KEY (tenant_id, id),
        FOREIGN KEY (tenant_id, author_id)
          REFERENCES users ON DELETE SET NULL
    );
This schema has a problem. When you delete a user, it will try to set both the tenant_id and author_id columns on the posts table to NULL:

    INSERT INTO tenants VALUES (1);
    INSERT INTO users VALUES (1, 101);
    INSERT INTO posts VALUES (1, 201, 101);
    DELETE FROM users WHERE id = 101;
    ERROR:  null value in column "tenant_id" violates not-null constraint
    DETAIL:  Failing row contains (null, 201, null).
When we delete a user, we really only want to clear the author_id column in the posts table, and we want to leave the tenant_id column untouched. The feature I added is a small syntax extension to support doing exactly this. You can provide an explicit column list to the ON DELETE SET NULL / ON DELETE SET DEFAULT actions:

    CREATE TABLE posts (
        tenant_id int REFERENCES tenants ON DELETE CASCADE,
        id serial,
        author_id int,
        PRIMARY KEY (tenant_id, id),
        FOREIGN KEY (tenant_id, author_id)
          -- Clear only author_id, not tenant_id
          REFERENCES users ON DELETE SET NULL (author_id)
          --                                  ^^^^^^^^^^^
    );
I initially encountered this problem while converting a database to use composite primary keys in preparation for migrating to Citus [2], and it required adding custom triggers for every single foreign key we created. Now it can be handled entirely by Postgres!

[1]: https://www.postgresql.org/message-id/flat/CACqFVBZQyMYJV%3D...

[2]: https://www.citusdata.com/

Re: PostgreSQL 15

#89

Post by thewataccount »

morley wrote: post #25

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

I manage two separate projects at my work, I would never upgrade to a .0 update, but we do every update it either when there's features we want, over every ~2~3 years for the general improvements.

We use django and some other stuff that has very decent support for postgres, and in my experience postgres updates rarely have breaking changes that require much if any changes for us. This also keeps us from being "trapped" on an old version.

I reiterate from all my systems management - do not update to a .0 release of anything unless there is a critical security update and you're forced to.

EDIT: Oh directly for the question - eh depends. Every 2 to 3 max, and usually after it's tested by others more.

And new projects generally use whatever is newest unless it's a .0 release.

Re: PostgreSQL 15

#90

Post by thewataccount »

clepto wrote: post #59
morley wrote: post #25

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

As someone who maintains Postgres 9.2, 9.4, and 9.6 databases. No, they do not. (It is not my will that these databases aren’t on newer versions, I would very much like them to be)

Just curious, is the issue a time thing, or is it on a system that can't easily upgrade it - or is it a breaking changes thing that would require refactors?

Basically I'm just curious what broke for you

Post reply on HN