postgresqlcommon-lispsbcl

How do I get a notification upon table change with Postmodern in Common Lisp?

thanks in advance for the help!

I would like to set up a Lisp program that does something when my Postgres database table is updated. I'm using the amazing Postmodern library, which has been absolutely terrific.

As discussed in this question (client side notification on table change in Common Lisp with Postmodern package) I don't want to poll the database (as the OP complains of the answer), but instead want the database to tell my program when to do something.

The person who answered points to the Postmodern function "wait-for-notifications": https://github.com/marijnh/Postmodern/blob/22aab0ec25d5f6affd390e690827a7515aeafd4f/cl-postgres/public.lisp#L358-L370

I am a little concerned about this function because 1. it is internal to Postmodern and therefore (let me know if I'm wrong here) not for public consumption and 2. it takes "database-connection" as an argument, which I can't track down.

So:

  1. Can anyone point me in the right direction for understanding and working with "database-connection" properly?
  2. Tell me that I'm barking up the wrong tree here and, if so, point me in the right direction for learning how to get my Lisp program to do something when my table changes?

Thank you so much!

Solution
    1. it is internal to Postmodern and therefore (let me know if I'm wrong here) not for public consumption

    There are different layers, wait-for-notification is an exported symbol from cl-postgres package, and defined in a file named public.lisp, so this should be ok to use it directly. Postmodern is at a higher level of abstraction but depending on your needs you are allowed to call directly cl-postgres functions.

    1. it takes "database-connection" as an argument, which I can't track down.

    A connection is what you get when you call open-database. Helper macros like with-connection bind the special *database* variable. Usually this is used invisibly by query and other commands, which look at the current binding for *database*. However if you want to understand a bit more how it is used, the documentation at https://marijnhaverbeke.nl/postmodern/cl-postgres.html seems quite good.

    Remark

    If you are going to wait for changes in your tables, chances are that you will want to use a dedicated thread. The documentation says that:

    If your application is threaded, each thread should use its own connection. Connections are stateful and attempts to use the same connection for multiple threads will likely have problems.