PostgreSQL 18 RETURNING Enhancements: A Game Changer for Modern Applications
49 points by sjamaan
49 points by sjamaan
Why does everything have to be a 'game changer'.
Sure, it's a neat feature, but seriously? :-)
In the era of ChatGPT every feature is game changing :p.
But I could see it here, because it changes how you 'play the game'... Instead of doing WAL logical replication for CDC you could broadcast it within your application at the ORM level based on the RETURNING value.
The audit trail can be done with triggers in older versions.
This is exactly what we do, works great. Love it. It's not a "secure" audit necessarily since it's "online", so admins, etc can update/erase the audit logs. This is why we do 2 audits. The online one is just an audit_log table with the who, what and when. We also log all SQL to the syslog that gets shipped off to a diff machine for serious audits. there is pg_audit that does the second, more secure audit better, but we didn't know about it at the time.
The online audit log also has a SQL undo function with audit_log PK ID's, so it's easy to undo mistakes from people. Some of our UI's actually have this built-in now actually. Of course since it's a trigger, the undo's themselves are also in the audit_log, which is exactly what we want. Works amazingly well!
Does undo only undo the most recent single action? I can imagine scenarios where it is invalid to do anything else.
I agree that you have to be careful when feeding undo.
The SQL function takes a range of log_audit.pk ID #'s.
When it's in the UI, it might take some finagling to figure out the right range in context. We only expose this in the UI after we get tired of doing it by hand enough, and we go through the trouble of writing a helper to figure out the context. Only the most often undo requests get that automated and exposed into the UI.
Ah I see. If you have good constraints/state verification, then undoing a range of IDs can still be safe because we have transactional semantics, so we can rollback. Sounds like a nice compromise! Thanks for the explanation.
Right, audit_log undo isn't always safe, but it's generally safe enough, if you are careful. Thankfully you can undo your undo if you screw it up too badly :)
To alleviate these concerns:
What we do is have a _hourly and _daily database that are copies from production every hour and every day, respectively. These are restored from backups, so we also know our backups work.
This lets us play around with "live" data for serious debugging if needed, in the right context. Access to these have to be given the same access rights as production obviously(which we do, since they are PG prod restores and we use PG auth and PG RLS even out to users).
Our training DB is literally just _daily. User's love being able to mess around with their "live" data to test stuff out, to ensure they understand how stuff works, before doing something in production. Since real live users play with _daily all the time, we get quick feedback when our backups break :)
Anyone know if Postgres’s merge is a bug riddled mess like sql server’s or if they did it “right”?
I am a sample of 1, but so far so good. At $DAYJOB we are not doing anything crazy. We are mostly using it for OLAP inserts (we don't have big data, we have complicated data, so postgres FTW) and it works.
When I saw the title I was really hoping that it was a clean way to refer to the previous value. I'm very happy to see this, it will make a lot of hard-to-read queries much nicer in my application. I've also observed some cases where joining with xmax (the main workaround) was confusing the query planner and generating bad plans for some key queries so it will be nice to also remove the work around I added for the planner.