Symmetric DS schema versioning

Question

I have a server and few clients with databases sinchronized by Simmetric-DS. Now the database version is 1.0 for client and server. So the column node.schema_version is 1.0 for clients and server.

I can upgrade the server database manually to rev.2.0. The client databases will self-upgrade to rev.2.0 using another application.

I want to use node.schema_version to avoid synchronization between the server and the nodes with version different from 2.0.

I used a subselect router with this router_expression:

'(SELECT check_version(c.schema_version))'

.. where the function check_version is true if client server version are equal, otherwise raises an exception to stop the synchronization:

CREATE OR REPLACE FUNCTION check_version(v_ver_check text)
  RETURNS boolean AS
$BODY$
DECLARE
    v_ver_cur text;
    v_success boolean;
BEGIN
    v_success:=false;

    v_ver_cur:='';
    SELECT n.schema_version 
    FROM sym_node n 
      INNER JOIN sym_node_identity ni on n.node_id=ni.node_id
    INTO v_ver_cur;


    IF v_ver_cur=v_ver_check 
    THEN 
      v_success:=true;
    ELSE 
      RAISE EXCEPTION 'SERVER-DB-VERSION<>CLIENT-DB-VERSION';
    END IF;

    RETURN v_success;   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 1;

In case of version mismatch:

During upload data from client to server:
This approach works fine because the exception blocks the synchronization process on the client ONLY.
During download data from server to client: The exception blocks the synchronization process ON THE SERVER so the synchronization is blocked versus ALL clients.

How I can block the download process only versus the node with version=1.0?

score 0 · Answer 1 · answered Apr 15 '15 at 18:23

Not sure I understand the question. In either case, you can simplify the plpgsql function:

CREATE OR REPLACE FUNCTION check_version(v_ver_check text)
  RETURNS boolean AS
$BODY$
BEGIN
   IF EXISTS ( 
      SELECT 1
      FROM   sym_node n 
      JOIN   sym_node_identity ni USING (node_id)
      WHERE  n.schema_version = v_ver_check
      ) THEN
      RETURN true;
   ELSE 
      RAISE EXCEPTION 'SERVER-DB-VERSION <> CLIENT-DB-VERSION';
   END IF;
END
$func$ LANGUAGE plpgsql STRICT;

There is a bit of guesswork here. Necessary information about involved tables is not in the question.

I wonder why there is no WHERE condition on sym_node_identity? Do sym_node and sym_node_identity only have one row matching on node_id?

score 0 · Answer 2 · answered Apr 21 '15 at 12:19

Sym_node_identity will only have one row representing the node id connected to the given database so there is no where clause needed to join to single row in sym_node. Sym_node will represent the nodes that current node communicates with.

However I think you can get rid of the check_version function all together and use the following query in your router expression directly. The subselect router expression appends this to the where clause to determine which lists of nodes it should route data to. In this case you get the version for the node your own (sym_node_identity) and get all other node_id's in sym_node that have a matching version. Note, this will only allow you synch matching versions only, you could do a >= instead of an = in the inner most query if you wanted 1's to sync with 2's but not the other way around.

c.node_id in (
    select node_id 
    from sym_node sn1
    join (
        select schema_version 
        from sym_node sn2
        join sym_node_identity si on si.node_id = sn2.node_id
    ) sv on sv.schema_version = sn1.schema_version
)

Symmetric DS schema versioning

2 Answers2