diff options
Diffstat (limited to '')
| -rw-r--r-- | 2024/talks/pgmacs.md | 164 |
1 files changed, 164 insertions, 0 deletions
diff --git a/2024/talks/pgmacs.md b/2024/talks/pgmacs.md new file mode 100644 index 00000000..8d901d80 --- /dev/null +++ b/2024/talks/pgmacs.md @@ -0,0 +1,164 @@ +[[!meta title="PGmacs: browsing and editing PostgreSQL databases from Emacs"]] +[[!meta copyright="Copyright © 2024 Eric Marsden"]] +[[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]] + +<!-- Initially generated with emacsconf-publish-talk-page and then left alone for manual editing --> +<!-- You can manually edit this file to update the abstract, add links, etc. ---> + + +# PGmacs: browsing and editing PostgreSQL databases from Emacs +Eric Marsden (he/him) - <mailto:eric.marsden@risk-engineering.org>, [@emarsden@mastodon.social](https://mastodon.social/@emarsden) + +[[!inline pages="internal(2024/info/pgmacs-before)" raw="yes"]] + +PGmacs provides an Emacs-based browsing and +editing interface for the PostgreSQL DBMS. It +allows you to browse the contents of database +tables, in paginated mode for large tables, to +edit column values in the minibuffer or using a +widget-based interface, to delete, copy and insert +new rows, and to save the contents of a table in +CSV or JSON format. You can run shell commands on +column values. You can also rename tables and +columns, add SQL comments, and generate SchemaSpy +diagrams. + +PGmacs works both in the terminal and in GUI mode. +It uses the pg-el library, which implements the +PostgreSQL wire protocol, to connect to PostgreSQL +over the network or over a Unix socket. + +The talk will provide an overview of the functionality of PGmacs, then show how it can be easily +extended to display images stored as database BLOBs inlined in a row-list buffer. + +<https://github.com/emarsden/pgmacs> + +About the speaker: + +Eric Marsden developed his first Emacs packages +pg-el, coffee.el (an implementation of the +essential Hyper Text Coffee Pot Control Protocol) +and ipp.el as a procrastination exercise to avoid +working on his PhD. He continues to use Emacs Lisp +in his spare time. + + +# Discussion + +## Questions and answers + +- Q: This is brilliant, thank you! Do you know if PGmacs works with + TRAMP? I often use TRAMP multi-hop to access databases - both + remotely when accessing via a 'bastion server' and locally when + using OCI containers + - A: There is no TRAMP support, I'm afraid. PGmacs is directly + connecting to the database server over the network. You can set + up ssh tunnels, but that would be done separately from Tramp. + Thanks :) + - you can tunnel arbitrary data over ssh + - ssh subsystems are the thing to look at, I + think\... I might look at it + - (sftp works that way: basically you can put + arbitrary progams at the remote end\... like, oh, say, + pgsql. transparently to the ssher.) + - Possibly PGmacs could setup the tunnel itself, + using TRAMP? + - With docker.el, kubel, etc, it's often possible to for example + select a container/pod/whatever that is hosted on a machine + you've connected to via TRAMP (such as + /podman:\<image\>:/path/), and trigger a terminal/eshell as well + as port-forwards and other similar things. It'd be nice to be + able to use this tool in a similar way since it would open up + the ability to use it with complex connection configurations. + Doing SSH tunnels manually is ofc totally fine in practice :) +- Q: \<Donovan\>Great work! I'm impressed. How did you come up with + this brilliant idea? + - A: First got the idea by looking at sqlite-mode +- Q: Is sqlite-mode also capable of all of this functionality (table + relations, etc)? If not, will it be possible to abstract out this + functionality from pgmacs somehow? + - A: I'm not veyr familiar with sqlite-mode but it looks more + basic. There are differences between the sql dialects so it will + be difficult to abstract it out. +- Q: Would it be possible to move it into Emacs tree? Are the + maintainers interested in it? + - A: Currently its at a very early stage and is being updated + regularly. I also have some philosophical reasons to not do it + regarding copyright transfer to the FSF. +- Q: Almost missed this one, so glad I didn't, but this may have been + answered already: what do you use for the in-buffer tables? vtable? + - A: vtable but "forked" (some changes/improvements, may + consider "merging" back with vtable core work) +- Q: + - A: + +## Notes + +- I have got 270 tables, and it stays there forever "loading" + tables, and nothing appears. +- Thanks for checking it out! You may have an authentication failure, + and PGmacs is not very good at showing that in the connect phase. + Switch to the \*Messages\* buffer and see whether there's an error + message there. + - This is happening with me as well. I noticed this error message + in Postgres Logs. It works if I open the postgres database. + - db-1 \| 2024-12-08 18:58:41.524 UTC \[48\] STATEMENT: + CREATE EXTENSION IF NOT EXISTS vector + - db-1 \| 2024-12-08 18:59:13.831 UTC \[50\] ERROR: could + not open extension control file + "/usr/share/postgresql/14/extension/vector.control": No + such file or directory + - In \*Messages\* buffer, this is what I see + - pg-bind: Wrong type argument: stringp, 97 +- OK, thanks for this feedback. It looks like your Postgres vector + extension is not well set up, but that really shouldn't be causing + a complete connection failure. You can comment out the line + (pg-vector-setup con) in function pgmacs-open in pgmacs.el and see + whether that helps + - - Let me try that and report back. + - I got this error: pg-do-startup: Process postgres not + running: failed with code 111 + - OK, that means that the network connection failed. There is + probably more error information in the backtrace + - Ah sorry, had stopped the pg. This time I got the same error + - pg-bind: Wrong type argument: stringp, 97 + - But it didn't try to load vector extensions (from pg logs). + - With toggle-debug-on-error, this is the backtrace + - [https://gist.github.com/ankitrgadiya/d9ae038489e4f680e3037e2e61584312](https://gist.github.com/ankitrgadiya/d9ae038489e4f680e3037e2e61584312) + - OK, thanks for that backtrace. I don't immediately see what + is wrong here. There is something strange about that + shared_urls table, it seems. Are you using pg-el from the + github repo? + - I created the github issue to continue the discussion: + [https://github.com/emarsden/pgmacs/issues/9](https://github.com/emarsden/pgmacs/issues/9) +- - - BTW I'm using the postgres docker image. + +- This is amazing! Image inline :o !! + +- I'm impressed by the inline images. Just sprinkle a little elisp + and voila. + +- A PostGIS point field -\> osm.el integration would be very cool +- Interesting idea, will look into that. +- Super slick Emacs/PG hacking and presentation! + Eat your heart out, MS Access +- another package I never knew I needed +- Very cool, I'm currently using PG on a small project and + it's always a pain to leave emacs to do checks in the database. +- I just wish it had existed when I was doing massive db + work in the 90s/2000s +- Just installed it, to prevent me from forgetting about + it. +- I just wish it had existed when I was doing massive db work in the 90s/2000s + - Better 20+ years late than never 😆 +- there is a grantback if you assign copyright to the FSF: you get all the rights you gave away right back again +- Thanks Eric, looks fantastic, and quite eager to look into those vtable enhancements myself (thanks for answering) +- YouTube comment: Very impressive, thanks! +- YouTube comment: Finally a perspective on a potential real excel killer. +- YouTube comment: How is the duckdb more coming along? + +[[!inline pages="internal(2024/info/pgmacs-after)" raw="yes"]] + +[[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]] + + |