PostgreSQL Lock Conflicts | Hacker News Reader

PostgreSQL Lock Conflicts

pglocks.org

152 pointsboiler_up80017 comments

Open articleView on HN

What a fabulously detailed and niche piece of documentation this is.

Anyone know of other examples of documentation projects like this one?

I think this is just copied from original PG docs: https://www.postgresql.org/docs/7.2/locking-tables.html

That's a _very_ outdated document. Current version is https://www.postgresql.org/docs/current/explicit-locking.htm...

yeah, my link somehow was first in google search..

It's a common issue with PG documentation, google often leads you to documentation for old versions (or it's an indication that these versions are actually still highly relevant)

Fortunately PG is very good at keeping the documentation structure the same between major versions and provides crosslinks to the proper documentation for all PG versions on (almost?) all pages

One example is "CONF" [1], a website that provides:

a) Detailed documentation about every postgresql.conf configuration parameter (in several languages), for Postgres version 9.1-current. Includes recommendations, comments and links to relevant threads in SO and PostgreSQL Hacker's mailing list relevant to every parameter.

b) A tool to manage postgresql.conf configuration, with tuning guides, the option to store your configs and download in multiple formats, and an open API behind it.

All website is 100% free for the Postgres Community. Disclosure: my company is behind this project.

Edit: minor edits.

[1]: https://postgresqlco.nf/

original https://postgres-locks.husseinnasser.com/

This is excellent, kudos to the author. I wonder if pg exposes enough control to develop a tool along with this information which help analyze locks and their effect on queries on a live server.

I’ve been working on a tool related to this for Database migrations.

Most current tooling is similar to this: parse the SQL, find specific statements and highlight “dangerous” locks. But that lacks all context and doesn’t account for changes or schema specific stuff related to rewrites/existing constraints/etc.

Why not… just get Postgres to tell you what locks or rewrites a given DDL statement will take? Why do we need to take the logic defined in the Postgres source code and spit it into a human readable form, for us to then take a given SQL statement, parse it in our head and cross reference it with this (or other) documentation? Am I missing something?

Is this resource up to date? ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT) shows a stronger lock than ALTER TABLE ADD FOREIGN KEY (PARENT) while the PG docs says that the locks should be the same.

Very nice! I wish I had this when I was working with Postgress.

how do I install it? There is no docs about that..

This appears to be a website that you use in order to learn about different kinds of locks in PostgreSQL. There is nothing to install AFAICT. You browse this site with your web browser.

site says: "this tool shows ...", so I assumed there is a tool I need to install.

It looks like the site itself is a tool, to figure out what kind of lock you want to use / debug.

That's weird to refer to a site as a tool. A 'service' - that I'd understand, but a tool? It looks like there is actually all this information is also available as a (probably CLI?) tool.

The most common approach is an H/C interface utilising an optical transmission medium to the ocular receptors (one is technically enough, two for fault tolerance), and sufficient memory and processing capacity behind those.

Reply on news.ycombinator.com