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 NULLfield
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.
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);
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_v2type 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_v1to 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.