Strategy for making Postgres support multiple versions of client code

Question

I'm about to embark on an overhaul of our code for pushing to Postgres, and want to solicit some feedback before going on.

The setup here is that we've got a lot of software deployed with a non-Postgres database where we periodically push rows into a central Postgres database. (RDS 11.4) The chances of us updating all deployed copies of the code simultaneously are zero. So, we're always going to have multiple active versions in the field...sometimes a lot of versions apart. That's okay in itself, but it does make it unmanageable to bake naive INSERT statements into the client code. Which I have done. It has only bitten us slightly, but it could eventually become enormously painful. Any and all of the following DDL changes on our central Postgres can break pushes from deployed software that's on an old version:

• Remove a field
• Rename a field
• Retype a field
• Add a NOT NULL DEFAULT NULL field

Stating the obvious, I've got to move hard-coded/fixed references out of client code and into something else. Most folks likely have some sort of stack with an ORM, etc between their collector/field application and Postgres. We do not. So, with much help from people here on SO, I've built the following solution, using "hsys" as an example table.

1. For each table version create a custom type, like hsys_v1.

   CREATE TYPE api.hsys_v1 AS (
       id uuid,
       name_ citext,
       marked_for_deletion boolean);
   

2. For each table and version, write an INSERT handling function that accepts an array of the custom type.

   CREATE OR REPLACE FUNCTION ascendco.insert_hsys_v1 (data_in api.hsys_v1[])
     RETURNS int
   AS $BODY$
   
   -- The CTE below is a roundabout way of returning an insertion count from a pure SQL function in Postgres.
   with inserted_rows as (
           INSERT INTO hsys (
               id,
               name_,
               marked_for_deletion)
   
           SELECT
               rows_in.id,
               rows_in.name_,
               rows_in.marked_for_deletion
   
           FROM unnest(data_in) as rows_in
   
           ON CONFLICT(id) DO UPDATE SET
               name_ = EXCLUDED.name_,
               marked_for_deletion = EXCLUDED.marked_for_deletion
   
           returning 1 as row_counter)
   
       select sum(row_counter)::integer from inserted_rows;
   
   $BODY$
   LANGUAGE sql;
   

   The idea here is that when I change the table, I'll be able to create the a hsys_v2 type and an insert_hsys_v2 (hsys_v2[]) function to match. Then old clients can continue pushing in the old format, so long as I rewrite insert_hsys_v1 to map/convert/coerce things to the new table format.

I wrote a GUI to grab my table definitions and put a code generator on top a few weeks ago. And then I stopped. I realize I'm wondering if I'm missing something I should consider, and am hoping for someone to point out a hole in this strategy. I'm not being lazy about reaching out to local programmers....there aren't any. (I'm in rural Australia.)

If there's nothing wrong with this strategy, I'll do the overhaul. As a bonus, the work so far made it easy to add in code builders for custom casts and views. Not sure if they'll be useful or not, but I'm generating them at the same time.

Answer 1

I cannot immediately see a problem with your idea, except that you have to change all code to use functions instead of data modifying SQL statements.

I can think of an alternative as follows: When you install your schema changes, also create a number of views that look just like the old tables and “do the right thing”:

• SELECTs on the view return the same data you would get in the old version.

• The views have triggers for INSERT, UPDATE and DELETE that perform the correct actions on the underlying tables.

Since the names of the views will collide with the table names, place the views in a different schema. You could pick a schema name based on the application version, and you can use search_path to assign different default schemas to the two application versions.

Once all clients are using the new version, you can drop the schema with the views.