-- Function: _my_txid_snapshot_xmin()
-- DROP FUNCTION _my_txid_snapshot_xmin();

CREATE OR REPLACE FUNCTION _my_txid_snapshot_xmin(p_writer	name DEFAULT 'sensor')
  RETURNS bigint AS
$BODY$
WITH in_process (txi) AS (SELECT txid_snapshot_xip(txid_current_snapshot()))
,not_writer_assync as -- ? move in dblink -- for no cash reed
	(SELECT 
		(backend_xid::text)::bigint 
			+ 4294967296::bigint *(txid_snapshot_xmin(txid_current_snapshot()) /4294967296::bigint)
			AS txi --
	FROM pg_stat_activity 
		WHERE usename <>p_writer AND backend_xid IS NOT NULL AND datname=current_database()
	)
	
SELECT min(txi) FROM 
	(SELECT txi FROM in_process
	EXCEPT
	SELECT txi from not_writer_assync) FOO

;
$BODY$
  LANGUAGE sql VOLATILE SECURITY DEFINER
  COST 100; 

CREATE OR REPLACE FUNCTION _tick()
  RETURNS bigint AS
$xxx$
WITH def (txmax, max_id) AS 
	(SELECT txid_snapshot_xmax(txid_current_snapshot()),_max_id())
	
,d AS (
	INSERT INTO tick
		(txmax, max_id)
	(SELECT txmax, max_id FROM def
	EXCEPT
	SELECT txmax, max_id FROM tick ORDER BY txmax desc limit 1)
	RETURNING txmax, max_id
	)
,u AS (UPDATE tick t SET max_id = def.max_id
	FROM def WHERE def.txmax=t.txmax
	RETURNING t.txmax, t.max_id
	)
,visible AS (
	SELECT max_id ,t.txmax FROM tick t
	WHERE 
		--t.txmax < txid_snapshot_xmin(txid_current_snapshot())
		(t.txmax < _my_txid_snapshot_xmin()
				OR _my_txid_snapshot_xmin() IS NULL)		
		AND txid_visible_in_snapshot(t.txmax, txid_current_snapshot())
	ORDER BY t.txmax DESC LIMIT 1
	)
,del AS (DELETE FROM  tick t 
	WHERE t.txmax <= (SELECT v.txmax FROM visible v)
	RETURNING txmax
	)
,calc AS (SELECT
		GREATEST(coalesce(
				(SELECT v.max_id FROM visible v),0)
				,(SELECT last_value FROM max_id_to_load /* + advisory 'max_id_to_load'::regclass*/)
				,(SELECT last_value FROM max_id_loaded)
			) AS calc_to
			,(SELECT last_value FROM max_id_to_load) AS current_to
		) 
SELECT case WHEN calc_to > current_to THEN
			setval('max_id_to_load'::regclass ,calc_to)
		ELSE calc_to
		END
FROM calc
;
$xxx$
	LANGUAGE sql VOLATILE
	COST 100;