A recursive CTE seems the way to go.
Assuming your path has no cycles. Else it needs more work to detect cycles. The array solution below can readily be adapted.
Test setup
Building on this simplified layout:
CREATE TABLE t1 (t1_id int, objid text);
INSERT INTO t1 VALUES
(1,'aaa')
,(2,'bbb')
,(3,'ccc')
,(4,'ddd')
,(5,'eee')
,(6,'fff')
,(7,'ggg')
,(8,'hhh');
CREATE TABLE t2 (t2_id int, t1_id int, objid text);
INSERT INTO t2 VALUES
(1,3,'aaa')
,(2,4,'aaa')
,(3,7,'hhh')
,(4,8,'ccc');
Two different solutions:
Solution with string as path
WITH RECURSIVE cte AS (
SELECT t.t1_id AS start_id
, t2.t2_id::text || '(t2)' || COALESCE(' ->' || t1.t1_id || '(t1)', '') AS path
, t1.t1_id
FROM t1 t
LEFT JOIN t2 USING (t1_id)
LEFT JOIN t1 ON t1.objid = t2.objid
UNION ALL
SELECT c.start_id
, c.path || ' ->' || t2.t2_id || '(t2)' || COALESCE(' ->' || t1.t1_id || '(t1)', '')
, t1.t1_id
FROM cte c
JOIN t2 USING (t1_id)
LEFT JOIN t1 USING (objid)
)
SELECT DISTINCT ON (start_id)
start_id, path
FROM cte
ORDER BY start_id, path DESC;
Result:
start_id path
1
2
3 1(t2) ->1(t1)
4 2(t2) ->1(t1)
5
6
7 3(t2) ->8(t1) ->4(t2) ->3(t1) ->1(t2) ->1(t1)
8 4(t2) ->3(t1) ->1(t2) ->1(t1)
Table names are redundant, obviously. I added them for good looks.
Solution with inverted array
Rightmost element is first t2_id
, keep alternating from right to left.
WITH RECURSIVE cte AS (
SELECT t.t1_id AS start_id, ARRAY[t1.t1_id, t2.t2_id] AS path
FROM t1 t
LEFT JOIN t2 USING (t1_id)
LEFT JOIN t1 ON t1.objid = t2.objid
UNION ALL
SELECT c.start_id, t1.t1_id || (t2.t2_id || path)
FROM cte c
JOIN t2 ON t2.t1_id = path[1]
LEFT JOIN t1 USING (objid)
)
SELECT DISTINCT ON (start_id)
start_id, array_remove(path, NULL) AS path
FROM cte
ORDER BY start_id, array_length(path, 1) DESC;
Result:
start_id path
1 {}
2 {}
3 {1,1}
4 {1,2}
5 {}
6 {}
7 {1,1,3,4,8,3}
8 {1,1,3,4}
array_remove()
requires Postgres 9.3+.
Inverted the array to need one less columns. By putting the last element first, I can reference path[1]
in the next step. Not sure if that's cheaper, would need a test ...
Shorter code, but probably slower. I suspect array handling is more expensive. Easier to adapt if we need to observe cycles.
SQL Fiddle.
Major points
We are alternating between two tables.
To make this recursive, one step needs to cover two hops (from t1 -> t2
and back t2 -> t1
).
The initial SELECT
uses 2x LEFT JOIN
to include all rows like in your example result.
The recursive part JOIN
to stop the loop when no match is found. The hop back uses LEFT JOIN
again.
Best Answer
You could wrap them in an
IF
clause that tests a custom configuration variable. This will work but it's a bit verbose and each statement in PL/PgSQL has a cost.I suspect you'll be better off lowering the log level in the
RAISE
messages so you log on a level likeDEBUG
that isn't captured bylog_min_messages
by default. That way you can just turn it on by changingclient_min_messages
orlog_min_messages
.See the expanded form of the
RAISE
statement, eg:See the documentation for the
RAISE
statement.Alternately, you can leave your messages at
RAISE NOTICE
and increase the log level, but that may hide other information you'll want to see in the logs. Better to log with a lower priority initially.So long as the notices aren't doing lots of expensive string formatting or being called huge numbers of times I suspect you won't have too much trouble with their performance impact while logging to file/client is suppressed by log levels.