From d7efbe8ca44be265d35afa7242de430046ece8b5 Mon Sep 17 00:00:00 2001 From: Petr Jelinek Date: Sun, 19 Apr 2015 00:29:07 +0200 Subject: [PATCH] Make sure node_name is not null and unique. --- extsql/bdr--0.10.0.0--0.10.0.1.sql | 38 ++++++++++++++++++++++++++++++ 1 file changed, 38 insertions(+) diff --git a/extsql/bdr--0.10.0.0--0.10.0.1.sql b/extsql/bdr--0.10.0.0--0.10.0.1.sql index 96f8f91956..2588014367 100644 --- a/extsql/bdr--0.10.0.0--0.10.0.1.sql +++ b/extsql/bdr--0.10.0.0--0.10.0.1.sql @@ -19,6 +19,44 @@ BEGIN END IF; END; $$; + +DO $$ +BEGIN + IF NOT EXISTS ( + SELECT 1 + FROM pg_class c + JOIN pg_namespace n ON n.oid = c.relnamespace + WHERE c.relname = 'bdr_nodes_node_name' + AND n.nspname = 'bdr' + ) THEN + -- make sure node names are unique, renaming as few nodes as possible + WITH nodes_to_rename AS ( + SELECT node_sysid, node_timeline, node_dboid FROM ( + SELECT node_sysid, node_timeline, node_dboid, + ROW_NUMBER() OVER(PARTITION BY node_name ORDER BY node_sysid, node_timeline, node_dboid) rownum + FROM bdr.bdr_nodes + WHERE node_name IS NOT NULL + ) dups + WHERE + dups.rownum > 1 + UNION + SELECT node_sysid, node_timeline, node_dboid + FROM bdr.bdr_nodes + WHERE node_name IS NULL + ) + UPDATE bdr.bdr_nodes SET node_name = r.node_sysid || '_' || r.node_timeline || '_' || r.node_dboid + FROM nodes_to_rename r + WHERE bdr_nodes.node_sysid = r.node_sysid + AND bdr_nodes.node_timeline = r.node_timeline + AND bdr_nodes.node_dboid = r.node_dboid; + + -- add constrains ensuring node_names are unique and not null + ALTER TABLE bdr.bdr_nodes ALTER COLUMN node_name SET NOT NULL; + CREATE UNIQUE INDEX bdr_nodes_node_name + ON bdr.bdr_nodes_node_name(node_name); + END IF; +END;$$; + RESET bdr.permit_unsafe_ddl_commands; RESET bdr.skip_ddl_replication; RESET search_path; -- 2.39.5