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

101–110 of 132 posts

Re: PostgreSQL 15

#101

Post by nicoburns »

gen220 wrote: post #85
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…

You can already do SELECT followed by UPDATE/INSERT in a single query in postgres using CTEs...

Re: PostgreSQL 15

#102

Post by [deleted] »

tiffanyh wrote: post #87
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).

[deleted]

Re: PostgreSQL 15

#103

Post by tremon »

tiffanyh wrote: post #87
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).

You're mixing up two orthogonal concepts. In the absence of row-level security, allowing a user read access to a view only (which may only have a filtered or condensed view of the underlying tables) is the more secure solution. If such a view would only work if the user also had access to the raw underlying tables, that would be a major source of data leaks.

In the presence of row-level security, the same concept allows you to create a system where (for example) the user can still access aggregate data for parts of the table where the individual rows are hidden to them. A simple example: a manager has full access to the salary records of his direct reports, but there's also a view that shows the wage balance per-team. If that view were to have invoke-as-user permissions, the manager would still only be able to view his own teams' wage balance. By making that view execute as security_definer instead, the admin can once again exercise full control over who gets access to which data.

So no, I don't think it should not be the default behaviour, and no, it does not completely defeat the purpose of row-level security.

Re: PostgreSQL 15

#104

Post by whartung »

tcgv wrote: post #95
thewataccount wrote: post #90

Earlier quoted context omitted.

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

One main challenge is to upgrade a live large database smoothly, without downtime. I believe minor version upgrades are easily enough since they maintain disk format compatibility and you can simply spin up a new replica server under the new postgres version and then when it's synched with the main database you can make the switch and kill the old version. I'm not so sure about migrating to a new major version (ex: 9…

Thing is, in this specific context, as I understand it, this is much easier to do post 9.x.

Notably, Postgres has more formal support for logical replication. Logical replication is distinct from the normal replication in that it forwards actual SQL to the replica rather than binary write ahead logs.

The SQL is portable across versions more than WALs are. So with logical replication you can copy a live db to a new version. Then you just need some (ideally) brief down time to swap out the servers.

There are solutions for 9.x, but it got much better later on.

Re: PostgreSQL 15

#105

Post by anarazel »

systemvoltage wrote: post #93

Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Looks like that's going to take 2-3 days. Sources are available but that's not something most people want to delve in with make files and dependencies. Something like postgres is huge. Right now, if you go to downloads and expect postgresql…

> Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version.

Normally that's the case - we "wrap" the release on Monday so that packagers have time till Thursday to get packages ready. Looks like something didn't quite work out this time. Looking into what went wrong.

Part of it is that a list of supported versions on the windows, macos download pages weren't updated, despite the 15 being available. But unfortunately the Debian / Ubuntu packages are indeed not yet ready.

> May be also docker repositories

postgresql.org doesn't currently provide docker containers to my knowledge.

Re: PostgreSQL 15

#106

Post by systemvoltage »

anarazel wrote: post #105
systemvoltage wrote: post #93

Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Looks like that's going to take 2-3 days. Sources are available but that's not something most people want to delve in with make files and dependencies. Something like postgres is huge. Right now, if you go to downloads and expect postgresql…

> Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Normally that's the case - we "wrap" the release on Monday so that packagers have time till Thursday to get packages ready. Looks like something didn't quite work out this time. Looking into what went wrong. Part of it is that a list of s…

No worries, thanks for looking into it.

Re: PostgreSQL 15

#107

Post by [deleted] »

systemvoltage wrote: post #93

Just some feedback for releases of any software: I think apt sources and repositories should be ready to go on launch and PR-release so people can immediately use the new version. Looks like that's going to take 2-3 days. Sources are available but that's not something most people want to delve in with make files and dependencies. Something like postgres is huge. Right now, if you go to downloads and expect postgresql…

[deleted]

Re: PostgreSQL 15

#108

Post by [deleted] »

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…

[deleted]

Re: PostgreSQL 15

#109

Post by singingfish »

mullsork wrote: post #3

SQL MERGE looks great! I hope I remember it when the time comes, instead of writing 3 separate queries. edit: Postgres docs on MERGE: https://www.postgresql.org/docs/15/sql-merge.html

I'm currently neck deep in a decent sized oracle to postgres project, and MERGE INTO saved me many many hours

Re: PostgreSQL 15

#110

Post by solidr53 »

CodeIsTheEnd wrote: post #88

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), ); CREAT…

Thanks for your work on that, very useful.
Post reply on HN