summaryrefslogblamecommitdiffstats
path: root/2024/talks/pgmacs.md
blob: ed9c2d20cc677e5ec498aaae15ba1771a66eea30 (plain) (tree)
1
2
3
4
5
6
7
8
9








                                                                                                    
                                                                                                                                  






















                                                                                                

                                    









                                                  












































































































                                                                                                                                                                                     





                                                              
[[!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 :)
        -   \<NullNix\> you can tunnel arbitrary data over ssh
        -   \<NullNix\> ssh subsystems are the thing to look at, I
            think\... I might look at it
        -   \<NullNix\> (sftp works that way: basically you can put
            arbitrary progams at the remote end\... like, oh, say,
            pgsql. transparently to the ssher.)
        -   \<jreicher\> 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){rel="noreferrer noopener"}
        -   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){rel="noreferrer noopener"}
-   -   -   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.
-   \<sctb\> emarsden: Super slick Emacs/PG hacking and presentation!
    Eat your heart out, MS Access
-   \<NullNix\> another package I never knew I needed
-   \<alzai\> 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.
-   \<NullNix\> I just wish it had existed when I was doing massive db
    work in the 90s/2000s
-   \<gs-101\> Just installed it, to prevent me from forgetting about
    it.

\


[[!inline pages="internal(2024/info/pgmacs-after)" raw="yes"]]

[[!inline pages="internal(2024/info/pgmacs-nav)" raw="yes"]]