6 ms·
PostgreSQL Lock Conflicts
- orf 3y agoI’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?
- riku_iki 3y agohow do I install it? There is no docs about that..
- codetrotter 3y agoThis 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.
- riku_iki 3y agosite says: "this tool shows ...", so I assumed there is a tool I need to install.
- sleepy_keita 3y agoIt looks like the site itself is a tool, to figure out what kind of lock you want to use / debug.
- mrAssHat 3y agoThat'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.
- taberiand 3y agoThe 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.
- simonw 3y agoWhat a fabulously detailed and niche piece of documentation this is. Anyone know of other examples of documentation projects like this one?
- riku_iki 3y agoI think this is just copied from original PG docs: https://www.postgresql.org/docs/7.2/locking-tables.html https://www.postgresql.org/docs/7.2/locking-tables.html
- dharmab 3y agoThat's a _very_ outdated document. Current version is https://www.postgresql.org/docs/current/explicit-locking.html https://www.postgresql.org/docs/current/explicit-locking.htm...
- riku_iki 3y agoyeah, my link somehow was first in google search..
- unilynx 3y agoIt'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
- ahachete 3y agoOne 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/ https://postgresqlco.nf/
- pdhborges 3y agoIs 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.
- grodes 3y agooriginal https://postgres-locks.husseinnasser.com/ https://postgres-locks.husseinnasser.com/
- kul_ 3y agoThis 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.
- Sparkyte 3y agoVery nice! I wish I had this when I was working with Postgress.