title: postgresql: trigger that tests for dblink connection and establishes if not present
body: <p>I'm trying to set up a trigger in postgresql 9.6 that will use dblink to insert a row into another database when a row is inserted into its own table.  Since there can be a large volume of these inserts, I don't want to connect and disconnect to the database for every insertion, so I would prefer to have a persistent connection that gets used by every insert.  But I think I would then have to test if the connection is available since a number of things could cause the connection to drop.</p>
  
  <p>My pseudo-code is as follows:</p>
  
  <pre><code>-- 1. test if named dblink connection already exists
  -- 2.   if it does not, create a named dblink connection
  -- 3. insert data via dblink
  </code></pre>
  
  <p>What I have so far is like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION db_link_trigger()
     RETURNS trigger AS
  $BODY$
  BEGIN
     -- 1. test if named dblink connection already exists
     IF (SELECT COALESCE('dblinktest' = ANY (dblink_get_connections()), false)) = false THEN 
        -- 2. if it does not, create a named dblink connection
        RAISE NOTICE 'dblink connection not established.  Connecting now';
        PERFORM dblink_connect('dblinktest', 'hostaddr=192.168.1.30 port=5433 dbname=otherdb user=myuser password=mypassword');
     ELSE
        RAISE NOTICE 'dblink connection already established';
     END IF;
  
     -- 3. insert data via dblink
     PERFORM dblink_exec('dblinktest', 'insert into mytable(data) values(''' || NEW.data || ''');');
  
     RETURN NEW;
  END;
  $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
  </code></pre>
  
  <p>My main concerns are how to handle errors and how to handle near-simultaneous invocation of the trigger.  Consider if two INSERTS come in at the same time, and there is no previous dblink named 'dblinktest'.  When the first sees that the connection does not exist, it proceeds to set it up.  Then the second could see that the link is also not established and try to connect itself, but it will fail since the first will connect before it, and it will raise an error as:</p>
  
  <pre><code>ERROR:  duplicate connection name
  </code></pre>
  
  <p>How can I handle an error like that?  Does postgresql have something like this python-inspired pseudo-code?</p>
  
  <pre><code>try:
    if dblink connection is not established:
      establish dblink connection
  except 'ERROR:  duplicate connection name':
    pass # do nothing
  finally:
    insert row into other db via dblink connection
  </code></pre>