title: Getting newest not handled, updated rows using txid
body: <p>I have a table in my PostgreSQL (actually its mulitple tables, but for the sake of simplicity lets assume its just one) and multiple clients that periodically need to query the table to find changed items. These are updated or inserted items (deleted items are handled by first marking them for deletion and then actually deleting them after a grace period).</p>
  <p>Now the obvious solution would be to just keep a “modified” timestamp column for each row, remember it for each each client and then simply fetch the changed ones</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM the_table WHERE modified &gt; saved_modified_timestamp;
  </code></pre>
  <p>The modified column would then be kept up to date using triggers like:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION update_timestamp()
      RETURNS trigger
      LANGUAGE ‘plpgsql’
  AS $$
      BEGIN
          NEW.modified = NOW();
          RETURN NEW;
      END;
  $$;
  
  CREATE TRIGGER update_timestamp_update
      BEFORE UPDATE ON the_table
      FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
  
  CREATE TRIGGER update_timestamp_insert
      BEFORE INSERT ON the_table
      FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
  </code></pre>
  <p>The obvious problem here is that <code>NOW()</code> is the time the transation started. So it might happen that a transaction is not yet commited while fetching the updated rows and when its commited, the timestamp is lower than the saved_modified_timestamp, so the update is never registered.</p>
  <p>I think I found a solution that would work and I wanted to see if you can find any flaws with this approach.</p>
  <p>The basic idea is to use xmin (or rather <code>txid_current()</code>) instead of the timestamp and then when fetching the changes, wrap them in an explicit transaction with <code>REPEATABLE READ</code> and read <code>txid_snapshot()</code> (or rather the three values it contains <code>txid_snapshot_xmin()</code>, <code>txid_snapshot_xmax()</code>, <code>txid_snapshot_xip()</code>) from the transaction.</p>
  <p>If I read the postgres documentation correctly, then all changes made transactions that are &lt; <code>txid_snapshot_xmax()</code> and not in <code>txid_snapshot_xip()</code> should be returned in that fetch transaction.  This information should then be all that is required to get all the update rows when fetching again. The select would then look like this, with <code>xmin_version</code> replacing the <code>modified</code> column:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM the_table WHERE
     xmin_version &gt;= last_fetch_txid_snapshot_xmax OR xmin_version IN last_fetch_txid_snapshot_xip;
  </code></pre>
  <p>The triggers would then be simply like this:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE FUNCTION update_xmin_version()
      RETURNS trigger
      LANGUAGE ‘plpgsql’
  AS $$
      BEGIN
          NEW.xmin_version = txid_current();
          RETURN NEW;
      END;
  $$;
  
  CREATE TRIGGER update_timestamp_update
      BEFORE UPDATE ON the_table
      FOR EACH ROW EXECUTE PROCEDURE update_xmin_version();
  
  CREATE TRIGGER update_timestamp_update_insert
      BEFORE INSERT ON the_table
      FOR EACH ROW EXECUTE PROCEDURE update_xmin_version();
  </code></pre>
  <p>Would this work? Or am I missing something?</p>