What's it all about, Alfie?

This morning I decided to see about the status on one of my favorite Leiningen plugins - Ultra, the formatter, highlighter for the REPL. It makes things so much easier to see and read at a glance, and follow along quickly with the output of a function, or the values in some input. It's really what syntax highlighting did to text editors. Very nice.

Anyway... I found that Ultra 0.6.0 was up on Clojars, and so I was able to move to Leiningen 2.9.0 in Homebrew with:

  $ brew switch leiningen 2.9.0

and then I changed my ~/.lein/profiles.clj to be:

  {
   :user {:plugins [[lein-exec "0.3.7"]
                    [venantius/ultra "0.6.0"]]
          :ultra {:color-scheme {:delimiter [:bold :yellow]
                                 :tag [:bold :yellow]
                                 :nil [:cyan]
                                 :boolean [:bold :cyan]
                                 :number [:bold :green]
                                 :string [:bold :red]
                                 :character [:cyan]
                                 :keyword [:yellow]
                                 :symbol [:bold :magenta]
                                 :function-symbol [:bold :blue]
                                 :class-delimiter [:blue]
                                 :class-name [:green]
                                 :exception [:bold :red]}}
         }
  }

and then I was ready to go.

One downside of moving to Leiningen 2.9.0 is that nREPL 0.6.0 formats the output a little differently, and it places an additional newline on the end of the output, so that it now appears to have a "double spaced" output:

where the top REPL was using the old nREPL in Leiningen 2.8.3, and the bottom one was using Leiningen 2.9.0 with nREPL 0.6.0. Now the developers may come to terms with this, and clean this up, or at least make it optional, but for now, I'm willing to live with the extra line as just "part of life"... as I really wanted to get to Ultra 0.6.0 and Leiningen 2.9.0 - due to it's significant structural changes under the hood.

Progress isn't always without a few bumps in the road...

My friend, that asked for the Timeless project, pinged me yesterday to say that he really would like to have a "clear" button on the page so that it cleared out all the data in the table. I said I'd look at it today - as soon as I got back up to speed with my new laptop. More on that later. 🙂

What I needed to do was to add a function to wipe out the HandsOnTable, and that wasn't too bad... simply get the row count, and clear out the timestamp data:

  /*
   * Function to clear out the timestamps from the table, and then refresh
   * the table to make sure that it's all cleared out and ready to go.
   */
  function wipeTable(tid) {
    var rows = $(tid).handsontable('countRows');
    for (var i = 0; i < rows; i++) {
      $(tid).handsontable('setDataAtCell', i, 0, '');
      $(tid).handsontable('setDataAtCell', i, 1, '');
    }
  }

Then I just needed to update the HandsOnTable formatter for the computed difference so that if the timestamp cell was empty, we put null in the computed column, and then that would work for both the clearing and the totally empty rows:

  /*
   * The Timeline table needs to have a computed 'diff' for the current line
   * to the previous line. These are all msec times, so it's just a difference
   * of two numbers. Shouldn't be hard. :)
   */
  var timeDiff = function(instance, td, row, col, prop, value) {
    if (instance.getDataAtCell(row, 0)) {
      var a = instance.getDataAtCell(row, 1);
      var b = instance.getDataAtCell((row - 1), 1);
      value = ($.isNumeric(a) ? a : 0) - ($.isNumeric(b) ? b : 0);
    } else {
      value = null;
    }
    Handsontable.NumericRenderer.apply(this, arguments);
  }

And then I needed to place a button in the header of the panel with the table:

  <div class="panel-heading">
    <h3 class="panel-title"><span id="timelineTitle">Timeline Evolution Worksheet</span>
      <form id="wipe_table" class="navbar-form navbar-right pullright"
            role="search" style="margin-top: -8px; display: visible;">
        <button id="wipeTable" class="btn btn-default">
          <span class="glyphicon glyphicon-trash"></span>
        </button>
      </form>
    </h3>
  </div>

Finally, I needed to disable the default function of the form, and wipe the table with:

  // set up the form components to work as we need them
  $("#wipe_table").submit( function(e) {
    e.preventDefault();
    wipeTable('#timelineTable');
  });

Now we have the "trash can" icon in the panel header, and it works like a charm!

HandsOnTable really is a nice tool. I can see doing a lot of cool things with it.

This morning, I realized it was about time to really shop around and see if Heroku or AWS was the right choice for my projects. Now I've heard, and mentioned, that it's likely that the best solution is Heroku, and after digging into all the capabilities and costs, it really is far easier and cheaper to use Heroku and just let it be done. The support for Clojure is first class, and the tools have gotten a lot better, and the pricing has come down, and that was all it took.

The first thing to do was to sign up at Heroku for an account, and then set up that account with the right SSH key, and 2FA with Authy on my iPhone, and then put in the credit card info to be a Validated user - and get more free hours a month for the "Free" dynes. All this was very nice, and really smooth. I liked that the 2FA with Authy is as easy as it is, because it pulls in the account information and icon, and it just plain works.

The next thing was to walk through the example Clojure project to see how it's done. What really matters is that it works, and then I can pick out things from that project - which also uses Jetty, and pull them into my project to get it going as well. As expected, that went very well, and the documentation is really first rate. Just what I've heard I could expect.

Finally, I followed their instructions to scale down the dynos to 0 on the example project, and started to work on converting my project from the old deployment model I had to using Heroku. First off, we needed to pull in the Procfile and app.json from the example project, and then update them to match what we needed for this project. The Procfile was updated to be:

  web: java $JVM_OPTS -cp target/timeless-standalone.jar timeless.main web

because the old init.d scripts I'm used to writing had this same basic line in them. We have to fix up the Uberjar file name, but we'll do that in a minute, but this will launch the Jetty server on the right port, and have everything cooking just fine.

The app.json was updated to be:

{
  "name": "Timeless: Sequencing timestamps",
  "description": "A service/app that helps sequence timestamps from log files.",
  "image": "heroku/clojure"
}

and here, it's pretty obvious what to update - just the name and description.

At this point, we can create the application at Heroku:

  $ heroku create

We need to make a few changes to the code so that it would work with the Heroku infrastructure. First, we had to update the project.clj to add these entries:

  :min-lein-version "2.7.1"
  :plugins [[environ/environ.lein "0.3.1"]]
  :hooks [environ.leiningen.hooks]
  :dependencies [[org.clojure/clojure "1.10.0"]
                 ;; ...other dependencies...
 
                 ;; Heroku environment tools
                 [environ "1.0.0"]]
  :uberjar-name "timeless-standalone.jar"
  :profiles {:uberjar {:main timeless.main, :aot :all}}

Then, in main.clj, we needed to pull the port for Jetty from Heroku's environment variables, and to do this, we updated the code to read:

(defn handle-args
  "Function to parse the arguments to the main entry point of this project and
  do what it's asking. By the time we return, it's all done and over."
  [args app]
  (let [[params [action]] (cli args
             ["-p" "--port"     "Listen on this port" :default 8080 :parse-fn #(Integer. %)]
             ["-s" "--ssl-port" "Listen on this port" :default 8443 :parse-fn #(Integer. %)]
             ["-v" "--verbose" :flag true])
        quiet? (:quiet params)
        fixed? (:fixed params)
        port (Integer. (or (env :port) (:port params) 5000))]
    (cond
      (= "web" action)
		      (try
		        (jt/run-jetty app {:port port})
		        ; finally, close down the async jobs
		        (finally
		          ))
      :else
        (do
          (info "Welcome to Timeless!")
          (println "Welcome to Timeless")))))

the key being to check the :port value with the env function, and then put that in the or before the default from the CLI args. It makes sense, they will want to set the port on the process so that they can stack a lot of them on a single box.

After this, it's as simple as deploying the app with:

  $ git push heroku master
  $ heroku ps:scale web=1

and it should all be running just fine.

When I added the CNAME records to my DNS for this domain, I get a certificate problem because we're moving from bobbeaty.com to herokuapp.com - so I still have to figure that out to make things really smooth, but this is a huge step in the right direction.

UPDATE: the key is not to add it as a CNAME - but a simply web permanent redirection. If we do that, then we can add it as: bobbeaty.com/timeless and we're all good!

This morning I saw a tweet from @SublimeHQ saying that Sublime Text 3.2 was out, and the list of fixes and features was really quite nice. The git integration is very nice - you can now see the branch you're on in the status bar, and when you click that, it will bring up Sublime Merge. It's not bad... not sure how much I'll use that, but it's nice that they have it integrated, and can show the branch on the editor window.

They also added a lot of nice Mac features:

• Mac: Added Mojave support
• Mac: Add full support for macOS native tabs
• Mac: Ensure context menus are shown without scrolling
• Mac: Error message dialogs can now be closed with the escape key
• Mac: Improved window placement
• Mac: Improved resize performance

I wasn't really disappointed with the performance, but to see the support for native tabs and Mojave - it's just really nice.

They also updated the syntax highlighting for Clojure, D, Go, and Lua - and while I'm not a big fan of the others, the changes they made to Clojure really nice. I also noticed that they have done something with the rendering in the Clojure files - could all be part of the syntax highlighting, but I'll have to see what it turns out to be. Looks like they "thinned out" the strokes on the comments, but we'll see. In any case, it's a lot nicer now for most of the coding I do.

It's an exciting upgrade, and it makes my day a little brighter.

A couple of days ago, I found out about the licensing rules for JDK 11 from Oracle, and wanted to install OpenJDK 11 with Homebrew. Sadly, Homebrew doesn't really like the idea of installing older versions of packages, so I'm going to live with JDK 1.8, and OpenJDK 11. But that's not what this is about. 🙂 This is about Clojure, and Leiningen's REPL, and the fact that in that post I found a known problem with the current version of Ultra, and Leiningen versions greater than 2.7.1

Well... a little digging, and I find that the core component of Ultra is another project, and that same author has a similar REPL formatter and colorizer - called whidbey. Now it turns out that whidbey also has issues with Leiningen 2.9.0 - but at least it works with 2.8.3, so I can upgrade to 2.8.3, and then let them figure out the issue with nREPL 0.6.0 - as it's already an issue on whidbey.

So until then, I have both in my ~/.lein/profiles.clj, and I can flip between the two as needed:

  {
   :user {:plugins [[lein-exec "0.3.7"]
  ;                  [venantius/ultra "0.5.4"]
                    [mvxcvi/whidbey "2.0.0"]
                    ]
          :middleware [whidbey.plugin/repl-pprint]
          :whidbey {:color-scheme {:delimiter [:bold :yellow]
                                   :tag [:bold :yellow]
                                   :nil [:cyan]
                                   :boolean [:bold :cyan]
                                   :number [:bold :green]
                                   :string [:bold :red]
                                   :character [:cyan]
                                   :keyword [:yellow]
                                   :symbol [:bold :magenta]
                                   :function-symbol [:bold :blue]
                                   :class-delimiter [:blue]
                                   :class-name [:green]
                                   :exception [:bold :red]}}
          :ultra {:color-scheme ;; :solarized_dark
                   {:delimiter [:bold :yellow]
                    :tag [:bold :yellow]
                    :nil [:cyan]
                    :boolean [:bold :cyan]
                    :number [:bold :green]
                    :string [:bold :red]
                    :character [:cyan]
                    :keyword [:yellow]
                    :symbol [:bold :magenta]
                    :function-symbol [:bold :blue]
                    :class-delimiter [:blue]
                    :class-name [:green]
                    :exception [:bold :red]}}
          }
  }

At this point, I can switch to 2.8.3 with:

  $ brew switch leiningen 2.8.3
  Cleaning /usr/local/Cellar/leiningen/2.7.1
  Cleaning /usr/local/Cellar/leiningen/2.9.0
  Cleaning /usr/local/Cellar/leiningen/2.8.3
  3 links created for /usr/local/Cellar/leiningen/2.8.3

and I'm good to go with Leiningen 2.8.3, and as soon as whidbey is updated, I'll be able to move up to 2.9.0. Really glad I found this project.

[3/6] UPDATE: it looks like ultra is getting an update that will allow it to work with Leiningen 2.9.0 - which is great! I just need to wait for ultra 0.6.0 to hit Clojars, and I can update and use it. That would be very nice!

Well... today has been a very eventful day. I had noticed that Oracle dropped support for JDK 10, and mentioned it to some friends in Slack. I didn't know the details, so I decided to dig in and see if I could see anything on the net about it. I did. Lots. Changed my day.

Turns out, Oracle is changing it's licensing for JDK 11 - it's free for development and testing, but if you use it in production, it requires a license. A paid license. Yikes!

OpenJDK is also built by Oracle, and IBM, and RedHat - and it is Open Source - free for any use-case. So that's where people appear to be going. I can see that, but I've stayed away from OpenJDK because it's just the official JDK from Oracle. But now it seems Oracle is looking to cash in on the JDK, and this is just a lovely predicament.

But there is hope - kinda. Homebrew is supporting OpenJDK as a cask so that it can be versioned, and supported within the Homebrew ecosystem. That's good news. To install the latest, you simply need to:

  $ brew cask install java

and it will install it into:

  $ brew cask info java
  java: 11.0.2,9
  https://jdk.java.net/
  /usr/local/Caskroom/java/11.0.2,9 (64B)
  From: https://github.com/Homebrew/homebrew-cask/blob/master/Casks/java.rb
  ==> Name
  OpenJDK Java Development Kit
  ==> Artifacts
  jdk-11.0.2.jdk -> /Library/Java/JavaVirtualMachines/openjdk-11.0.2.jdk
  (Generic Artifact)

and the tools I built for switching JDK versions work just fine:

  $ setjdk 1.8; echo $JAVA_HOME
  /Library/Java/JavaVirtualMachines/jdk1.8.0_181.jdk/Contents/Home
  $ setjdk 11; echo $JAVA_HOME
  /Library/Java/JavaVirtualMachines/openjdk-11.0.2.jdk/Contents/Home

That's the good news. What isn't really good is that there is an issue with the Java Collections where the toArray method added an additional arity and that breaks Clojure - badly. So it really doesn't help to have the OpenJDK 11 on the box - as Clojure can't make use of it.

However, that's not all... it appears that when I upgraded Leiningen, I broke one of the really nice plugins that I like to use with it - Ultra. The error is nasty as well:

  $ lein clean
  $ lein deps
  $ lein repl
  [WARNING] No nREPL middleware descriptor in metadata of
    #'clojure.tools.nrepl.middleware.render-values/render-values,
    see nrepl.middleware/set-descriptor!
  [WARNING] No nREPL middleware descriptor in metadata of
    #'clojure.tools.nrepl.middleware.render-values/render-values,
    see nrepl.middleware/set-descriptor!
  nREPL server started on port 54967 on host 127.0.0.1 - nrepl://127.0.0.1:54967
  ERROR: Unhandled REPL handler exception processing message
    {:id ad3092dd-8491-4d60-a1a9-5092d4d090a9, :op clone}
    java.lang.IllegalArgumentException: No implementation of method:
    :send of protocol: #'nrepl.transport/Transport

and if I removed Ultra from my ~/.lein/profiles.clj then everything worked fine again. But this was with Leiningen 2.9.0 - and that was an issue. In fact, it was an issue that's already been reported to Ultra. I added my notes, as the suggestion didn't work for me.

So now I'm left with downgrading Leiningen to get Clojure working again. This is not as easy as I'd hoped, but it's what needs to be done. First, get into where the Homebrew formulae are stored, :

  $ cd $(brew --prefix)/Homebrew/Library/Taps/homebrew/homebrew-core/Formula
  $ git log --follow leiningen.rb
  commit 2be19f3787d811247095e704765fc33a8815d639
  Author: BrewTestBot <homebrew-test-bot@lists.sfconservancy.org>
  Date:   Tue Feb 12 13:07:29 2019 +0000
 
      leiningen: update 2.9.0 bottle.
 
  commit 9ca7899818eb02cc8252d47cc32b5a42554a7655
  Author: Rahul De <rahul080327@gmail.com>
  Date:   Tue Feb 12 10:43:59 2019 +0100
 
      leiningen 2.9.0
 
      Closes #36927.
 
      Signed-off-by: Chongyu Zhu <i@lembacon.com>
 
  commit fd1c0ba8f162a3c41debef6143dca8ba19227f90
  Author: BrewTestBot <homebrew-test-bot@lists.sfconservancy.org>
  Date:   Sat Dec 15 10:45:41 2018 +0000
 
      leiningen: update 2.8.3 bottle.
 
  commit 40553be0b01710db9e16cff978f593b83ecdfe3b
  Author: Rahul De <rahul080327@gmail.com>
  Date:   Sat Dec 15 11:27:08 2018 +0100
 
      Leiningen 2.8.3
 
      Closes #35151.

at this point we heed the git SHA for the version we want to move to. In the case of Leiningen, the SHA for 2.8.3 is fd1c0ba8f162a3c41debef6143dca8ba19227f90 and the SHA for 2.7.1 is cdddd1094b26d0092e0030051dac97a286bb3fc4. I started with 2.8.3, and that didn't work, so I had to go back to 2.7.1. This is a big difference.

Then make a branch for that version, and reinstall Leininden from the Ruby file:

  $ git checkout -b leiningen-2.8.3 fd1c0ba8
  $ brew reinstall ./leiningen.rb
  $ brew switch leiningen 2.8.3
  $ git checkout master

for 2.8.3 and, for 2.7.1:

  $ git checkout -b leiningen-2.7.1 cdddd109
  $ brew reinstall ./leiningen.rb
  $ brew switch leiningen 2.7.1
  $ git checkout master

At this point, we can see that both versions are installed:

  $ brew info leiningen
  leiningen: stable 2.7.1 (bottled), HEAD
  Build tool for Clojure
  https://github.com/technomancy/leiningen
  /usr/local/Cellar/leiningen/2.7.1 (9 files, 14.7MB) *
    Poured from bottle on 2019-02-18 at 13:29:34
  /usr/local/Cellar/leiningen/2.8.3 (9 files, 13MB)
    Poured from bottle on 2019-02-18 at 13:22:14

Now it's possible to run Leiningen and Ultra - again. It has to be JDK 8, and Leiningen 2.7.1... but until Ultra is fixed for the change in Leiningen, this is as far as I can go.

What was even more annoying was that my new work laptop has a very recent version of Homebrew on it, and the git repo has been trimmed, and so there is no git SHAs for the previous versions. However, with tar and copying the tarball over, I was able to get the 2.7.1 and 2.8.3 versions there, and things are now working OK.

Wow! What a mess to get back to where I thought I started the day!

It's time to make sure that my laptop has the latest version of PostgreSQL, as I was reading that they were making big strides in the windowing functions in postgreSQL 11. And with Homebrew 2.0 out, it made sense to just write it all down again, just so that I have it for reference.

The process isn't bad... dump all the databases into one file, stop the server, update Homebrew, update postgres. This gets us to the point that we are ready to rebuild the new database:

  $ pg_dumpall > dump.sql
  $ brew services stop postgresql
  $ brew update
  $ brew upgrade postgres

Now we need to move out the old database data, create a new structure, and restart the service:

  $ cd /usr/local/var
  $ mv postgres postgres.old
  $ initdb -D /usr/local/var/postgres
  $ brew services start postgresql

You then need to go back to the directory of the first command - the one where you dumped the databases, and reload them all:

  $ psql -d postgres -f dump.sql

and at this point, everything should be back and running:

  $ psql --version
  psql (PostgreSQL) 11.1
  $ psql -l
                                      List of databases
      Name     | Owner | Encoding  |    Collate     |     Ctype      |  Access privileges
  -------------+-------+-----------+----------------+----------------+---------------------
   health      | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII |
   inventory   | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII |
   northhollow | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII |
   postgres    | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII |
   template0   | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | =c/drbob           +
               |       |           |                |                | drbob=CTc/drbob
   template1   | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | drbob=CTc/drbob    +
               |       |           |                |                | =c/drbob           +
               |       |           |                |                | _postgres=CTc/drbob+
               |       |           |                |                | postgres=CTc/drbob
   test        | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII |
  (7 rows)

At this point you can remove the old data:

  $ rm -rf /usr/local/var/postgres.old
  $ rm dump.sql

and everything is updated. You can then use any of the normal tools, including the Apache/PHP/Postgres that Apple makes available, and Postico - a great GUI client.

This morning I saw a tweet that announced that the Cognitect AWS Client library 0.8.243 was released. This was news to me, because I've been using Amazonica for years now, and it works, but has plenty of little things that always made me write a wrapper around all the things I really needed to do.

But hey... Cognitect are the guys that own Clojure - so if there's a "preferred way" to integrate with AWS, these guys would be the ones that would do it in the best way possible. I haven't looked at the docs much, but I wanted to put all this in a post so I could come back to it the next time I needed to talk to AWS from Clojure, and give this a spin.

Quite interesting...

UPDATE: I've taken a look at it, and I find it very interesting. Rather than use the AWS Java SDK, and just wrap the Java objects in Clojure code - which isn't bad but it doesn't really make it discoverable, and you end up wondering if there isn't a simpler way to get the data. With the Cognitect scheme, you send data, and get data, and it's up to you to then read what you need. Much nicer.

I saw this on Twitter this morning:

Adam McCrea - @adamlogic: I see a lot of confusion around @heroku's free dynos, so I wrote up a guide. Let me know if you find this helpful, and please share!

https://railsautoscale.com/heroku-free-dynos

and I have to say, it's packed with a lot of really useful information about the free, hobby, and paid dynes and database plans. What I really liked was that the alternatives, and work-arounds that he presented makes it clear there are some decent alternatives to the Heroku plans, but that Heroku is still in a good place.

I still want to put some Clojure service up on Heroku someday... I just need to know what the app is, and maybe it's tied into a macOS or iOS app as well. Who knows... but I really like to see someone look into the good, and the not-so-great parts of a service provider - just to keep them honest.

A friend asked me today for the trick I've used in the past to make sure that the data in a database is immutable and versioned. This is nice because it matches what Clojure acts like, internally, and it makes it easy to see who's doing what, when. Assuming we start with a table - for the sake of argument, let's say it's customers, and looks something like:

  CREATE TABLE IF NOT EXISTS customer (
    id             uuid NOT NULL,
    version        INTEGER NOT NULL,
    as_of          TIMESTAMP WITH TIME zone NOT NULL,
    last_name      VARCHAR,
    first_name     VARCHAR,
    PRIMARY KEY (id, version, as_of)
  );

where the first three fields are really the key ones for any table to work with this scheme. You need to have a unique key, and in this case, the easiest I've found is a UUID, and then you need a version and a timestamp when that change was made. What's left in the table is really not important here, but it's something.

You then need to make an Audit table that has the same table structure, but has the string _audit added to the name:

  CREATE TABLE IF NOT EXISTS customer_audit LIKE customer including ALL;

What we then need to create is the following trigger that intercepts the INSERT and UPDATE commands on the customers table, and places the historical data into the Audit table, and the most recent version of the customer data is always kept in the customer table.

The trigger looks like this:

  --
  -- create the trigger on INSERT and UPDATE to update the version if it's
  -- not provided, and to maintain all versions in the audit table, but have
  -- the current version in the non-audit table. Importantly, NOTHING is
  -- deleted.
  --
  CREATE OR REPLACE FUNCTION audit_customer()
  RETURNS TRIGGER AS $body$
  DECLARE
    ver INTEGER;
  BEGIN
    -- get the advisory lock on this id
    perform pg_advisory_xact_lock(('x' || translate(LEFT(NEW.id::text, 18),
                                  '-', ''))::bit(64)::BIGINT);
 
    -- get the max of the existing version for the data now
    SELECT MAX(version) INTO ver
      FROM customer_audit
     WHERE id = NEW.id;
    -- and bump it up one and use that
    IF ver IS NULL THEN
      NEW.version := 1;
    ELSE
      NEW.version := ver + 1;
    END IF;
 
    -- if an update, then we need to insert the new
    IF tg_op = 'UPDATE' THEN
      -- now let's insert the old row into the audit table
      INSERT INTO customer_audit
        VALUES (NEW.*);
    elsif tg_op = 'INSERT' THEN
      -- now let's insert the new row into the audit table
      INSERT INTO customer_audit
        VALUES (NEW.*);
      -- and delete the old one in the customer table
      DELETE FROM customer
        WHERE id = NEW.id
          AND version <= ver;
    END IF;
 
    -- finally, return the row to be inserted to customer
    RETURN NEW;
  END
  $body$ LANGUAGE plpgsql;
 
  CREATE TRIGGER set_version BEFORE INSERT OR UPDATE ON customer
    FOR each ROW EXECUTE PROCEDURE audit_customer();

At this point, we can INSERT or UPDATE on customers and the previous version of that customer will be mmoved to the Audit table, and the the most recent version will be held in the customers table.

I have found this very useful, and I've put it in a gist for easy access.

The point of:

    -- get the advisory lock on this id
    perform pg_advisory_xact_lock(('x' || translate(LEFT(NEW.id::text, 18),
                                  '-', ''))::bit(64)::BIGINT);

is to get an exclusive lock on the data for a given id. This is necessary to make sure that updates from multiple services get serialized on the same data. This scheme can't ensure that there are merged changes - only that there is a sequence of changes to the table, and each one is entirely correct for the time it was entered.

So... what happens if you have a string as the primary key, and not a UUID? Well, use can use the MD5 checksum of the string as the lock indicator:

    -- get the advisory lock on a general string
    perform pg_advisory_xact_lock(('x' || md5(NEW.wiggle::VARCHAR))::bit(64)::BIGINT);

where the field wiggle is a varchar, and here, we are just computing the MD5, and using that as the basis of the lock. Yes, there could be some hash collisions, but that's likely not a huge performance problem, and it's conservative in that we'll over-lock, and not under-lock.

UPDATE: a friend asked about using an int as the primary key, and in that case, the line would be:

    -- get the advisory lock on a general string
    perform pg_advisory_xact_lock(NEW.id::BIGINT);

where the column id is an int. Again, we just need to get it to the bigint for the advisory lock call. After that, Postgres does the rest.