Heroku Connect fails to sync parent/child records that reference the same object, or Hierarchical relationship. For example, parent account and child account. This sql triggers auto-resubmit the failed records so that they succeed the second time. In addition a sync_status table is created to track the failures. This can be used for auditing and…

heroku_connect_retryfails.sql

---
---  Table to store sync_status results.
---  mostly a counter for how sync failures
---
CREATE TABLE IF NOT EXISTS "public"."sync_status" (
    id serial,
    "createddate" timestamp with Time Zone NOT NULL  DEFAULT now(),
    "lastmodifieddate" timestamp with Time Zone NOT NULL DEFAULT now(),
    "count_fail" integer DEFAULT 1,
    "recordid" integer not null,
    "relation" text not null
);

CREATE UNIQUE INDEX "idx_sync_status" ON "public"."sync_status" USING btree( "recordid", "relation" );

CREATE OR REPLACE FUNCTION sync_status_lastmodified() RETURNS trigger AS
$BODY$
BEGIN
  NEW.lastmodifieddate = now();
  RETURN NEW;
END;
$BODY$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS sync_status_lastmod ON sync_status;
CREATE TRIGGER sync_status_lastmod
BEFORE INSERT OR UPDATE ON sync_status
FOR EACH ROW
  EXECUTE PROCEDURE sync_status_lastmodified();

---
---  Function to retrieve the failure count for a particular row in sync_status
---  this is used by sync_status_increment
---
CREATE OR REPLACE FUNCTION sync_status_get_count(int, text) RETURNS integer AS $sync_status_get_count$
DECLARE
  thecount int;
BEGIN
  SELECT count_fail INTO thecount FROM sync_status WHERE recordid = $1 AND relation = $2;
  RETURN thecount;
END;
$sync_status_get_count$ LANGUAGE plpgsql;

--
-- sync_status_increment()  trigger function
-- Call this function in a BEFORE UPDATE on whatever object is being synced with Heroku Connect
--
--  Note that this function will setup a FAILED record for auto-retry up to 5 times.
--

CREATE OR REPLACE FUNCTION sync_status_increment() RETURNS trigger AS $sync_status_increment$
BEGIN
  -- Check that the record already exists in sync_status
  UPDATE sync_status SET count_fail = count_fail + 1
  WHERE recordid = NEW.id AND relation = TG_TABLE_NAME;

  IF not found THEN
    -- If not found then insert
    INSERT INTO sync_status ( recordid, relation ) VALUES ( NEW.id, TG_TABLE_NAME );
  END IF;

  IF sync_status_get_count( NEW.id, TG_TABLE_NAME ) < 5 THEN
    NEW._hc_lastop = 'PENDING';
    NEW._hc_err = '';

    -- stolen from hc_account_logger()
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO public._trigger_log (state,table_name,action,record_id,values)
        VALUES ( 'NEW', TG_TABLE_NAME ,'update',NEW.id,
          (
            hstore(NEW.*) -
            ( CASE when OLD.sfid is null THEN ''::hstore ELSE hstore(OLD.*) END) -
            ARRAY['_hc_lastop','_hc_err']
          ) || hstore( 'sfid', (hstore(NEW.*)||hstore(OLD.*))->'sfid')
        );
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO public._trigger_log (state,table_name,action,record_id,values)
        VALUES ('NEW', TG_TABLE_NAME,'insert',NEW.id,
          hstore(NEW.*) - ARRAY['_hc_lastop','_hc_err']
        );
    END IF;
  END IF;

  RETURN NEW;
END;
$sync_status_increment$ LANGUAGE plpgsql;


--
-- setup account for auto-retries
-- Any object that is synced with Heroku Connect can be setup just like this one
-- 

DROP TRIGGER IF EXISTS sync_status_account ON account;
CREATE TRIGGER sync_status_account
BEFORE UPDATE ON account
FOR EACH ROW
  WHEN (OLD._hc_lastop IS DISTINCT FROM NEW._hc_lastop AND NEW._hc_lastop = 'FAILED')
    EXECUTE PROCEDURE sync_status_increment();