It's slow at 20 seconds, but I'm pleased I finally found a good enough
way to use tables for day 8. Afterall, the reason I tried AoC in
postgres is because I really like table logic. By swapping out two
temp tables and doing insert only, I can avoid the update MVCC bloat
that wrecked my previous attempt. It was very educational watching the
loop speed degrade to a crawl after a thousand update runs, even
though the table never got bigger than 1000 rows.
I copied the input into d8(t text).
create temp table w1(c int, b text);
create temp table w2(c int, b text);
do $$
declare r record;
begin
--loop through the connections in closest order
for r in (with j as (
select row_number() over () r, t,
split_part(t, ',', 1)::int8 x,
split_part(t, ',', 2)::int8 y,
split_part(t, ',', 3)::int8 z
from d8)
select row_number() over(
order by (j.x-j1.x)^2 + (j.y-j1.y)^2 + (j.z-j1.z)^2) i,
j.t b1, j1.t b2, j.x * j1.x s from j, j j1 where j1.r > j.r) loop
--add the two boxes from the current connection
insert into w1 values (r.i, r.b1), (r.i, r.b2);
--connect all the boxes in the circuits of these two boxes
insert into w1 select r.i, w3.b
from w1 join w2 on w1.b = w2.b join w2 w3 on w2.c = w3.c;
--keep all the existing boxes with their current circuits
insert into w1 select * from w2;
truncate w2;
--get the latest circuit per box
insert into w2 select distinct on (b) * from w1 order by b, c desc;
truncate w1;
--the circuit is complete when all the boxes are in the current circuit
if (select count(*) from w2 where c = r.i) = 1000 then
raise notice '%', r.s;
exit;
end if;
end loop;
end
$$;
