Re: Enforce Symmetric Matrix

SELECT l.id, u.id, func(l.id, u.id)
FROM ids l CROSS JOIN ids u
WHERE l.id < u.id

Depending on whether you always update a known pair, or instead invalidate
all rows where either id is a given value, you can use various means to
manage the resultant materialized view. Triggers or interface functions
mainly.

Without calling the value function you would also know, at any given time,
whether a given pair is present. The usefulness of this depends on how
real-time you need the updates to be; which is a trade-off with performance
during changes.

Adding a simple limit on the two ids sub-queries, and doing the incremental
add in a loop, you can appropriately scale the updates to limit memory usage
during the bulk load phase. Likely ongoing updates will not have the same
requirement since you only have N updates instead of N^2/2; but can be done
all the same.

SELECT LID, UID, FUNC(lid, uid) FROM
SELECT CASE WHEN c1 < c2 THEN c1 ELSE c2 END AS LID , CASE WHEN c1 < c2 THEN
c2 ELSE c1 END AS UID FROM
SELECT * FROM -> WHERE c1 <> c2
SELECT :newval AS c1, ids.id AS c2 FROM ids

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Enforce-Symmetric-Matrix-tp5803064p5803126.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.