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

21–30 of 132 posts

Re: PostgreSQL 15

#21

Post by andy_ppp »

praveenweb wrote: post #5

MERGE feature is interesting. But specifically on the revoking CREATE permissions for the public (or default) schema, this is a step in the right direction. Some of the defaults in Postgres can be more secure. For example, the first time I use a POSTGRES_PASSWORD to configure a password, changing this password involves more steps than just changing the values of the ENV, because it doesn't take the changed value ther…

Looking at the Alpine docker file(s) for Postgres you might be able to use the one for the release candidate and set en environment variable of PGVERSION=15.0 which should use https://ftp.postgresql.org/pub/source/v15.0/ here.

You would need to figure out what the package name is on Debian (if it even exists yet?) it's currently set to ENV PG_VERSION 15~rc2-1.pgdg110+1

YMMV.

Re: PostgreSQL 15

#23

Post by cogman10 »

systems wrote: post #8
chrisjc wrote: post #6

Earlier quoted context omitted.

I'm actually surprised to hear that MERGE is only now available on Postgres. I'm now interesting in hearing about other standard (what I have come to expect as standard) SQL that's not or only now available on Postgres?

Well MS SQL Merge statement is not very good, and I personally avoid it, and most places I worked in recommend to avoid it, except in the simplest scenarios From the docs "At scale, MERGE may introduce complicated concurrency issues or require advanced troubleshooting. As such, plan to thoroughly test any MERGE statement before deploying to production." I dont know if its better in PQSQL , but they took their time, s…

Agreed. The amount of deadlocks merged causes in MS SQL is pretty insane.

We tried to use it for "upsert" type capabilities and even that would cause weird deadlocks.

Postgres already has the `Insert foo on conflict do update` type syntax which I think is generally better.

Re: PostgreSQL 15

#24

Post by cogman10 »

trollied wrote: post #18
chrisjc wrote: post #6

Earlier quoted context omitted.

I'm actually surprised to hear that MERGE is only now available on Postgres. I'm now interesting in hearing about other standard (what I have come to expect as standard) SQL that's not or only now available on Postgres?

It kind of did support it before. You could do an INSERT … ON CONFLICT ( keys here ) DO UPDATE update query here

Merge has a few more capabilities beyond that, but I think that's where you'd want to use it the most.

Re: PostgreSQL 15

#25

Post by morley »

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.)

Re: PostgreSQL 15

#26

Post by ianbutler »

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 don't think I've worked in an environment where we've upgraded for every release. New projects may start on that newer version, but generally speaking for older projects there's a cost calculation done for the newer features versus the lift required to do the upgrade and ensure it doesn't introduce any regressions.

Someone already mentioned the view permissions shift here which looks at caller permissions versus the view creator permissions, that will be compelling in a lot of cases so like this is something that I'd probably raise internally for my team for a few of the apps we maintain and then have a back and forth with the principals, if we like it then go through our current list of work with a PM and our manager and see if it makes sense to do now with the current pipeline of work etc.

Re: PostgreSQL 15

#27

Post by cogman10 »

chrstr wrote: post #11

> Queries using SELECT DISTINCT can now be executed in parallel. This sounds quite interesting, but I would assume it does not always work? I didn't see this mentioned in the linked documentation, does someone know when/how the parallel distinct works?

Couldn't tell you the when, but I can tell you the how is likely how you'd expect.

Generally speaking, to do distinct you need a dictionary to look up previously seen values. To do it in parallel you need to make that dictionary thread safe.

For Java, such a thread safe dictionary is made by segmenting the table and synchronizing on the segments. So you'd hash your values, figure out which segment that targets, lock that segment, and then read/update that segment to contain the new value.

I'd assume that postgres is doing a fairly similar trick, The only additional synchronization would be on a linked list of found values. In that case, you could either lock the list and update as new values come in, you could sort those values after the fact, or you could employ a lock free algorithm to add nodes to the list (see lock free queue implementations).

Re: PostgreSQL 15

#28

Post by [deleted] »

CWuestefeld wrote: post #7
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

Hmmm. The doc kinda suggests that this might be more efficient than doing it with separate commands: "First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first cla…

[deleted]

Re: PostgreSQL 15

#29

Post by wahnfrieden »

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.)

.1+ releases might be best with pg for quality assurance

Re: PostgreSQL 15

#30

Post by dewey »

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.)

This probably depends on your database, but with Postgres we usually don't stay on the old version for too long as the updates are usually very smooth and painless.
Post reply on HN