What I learned this week⌗

Software⌗

Splitting network into two subnets to make life miserable for wifi stealers

Redshift max recursion rows is defaults to 50,000,000 SHOW max_recursion_rows;. Seems like this is actual recursion count not rows… Testing out with the following code:

WITH RECURSIVE digits(d) AS (
    SELECT 1 AS d
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 5
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 0
),
    integers(i) AS (
    SELECT d FROM digits
    /* */
    UNION ALL
    /* */
    SELECT i + 1 AS next_i
    FROM integers
    WHERE i < 50000800
)
SELECT min(i), max(i), COUNT(*) FROM integers;

We get: min max count 0 50,000,800 500,007,965

And a small snippet of the results reveals an interesting pattern:

I wanted to test, how the initial set of numbers propagates and how new numbers are added in each iteration. I reduced the size of digits and added a static source column in integers to track over time.

-- Smaller exmaple multi-row set
WITH RECURSIVE digits(d) AS (
    SELECT 1 AS d
    UNION ALL SELECT 2
    UNION ALL SELECT 3
),
integers(i, source_d) AS (
    -- Base case: Add source_d to track where each number came from
    SELECT d, d FROM digits
    UNION ALL
    -- Recursive case
    SELECT i + 1, source_d
    FROM integers
    WHERE i < 10
)
SELECT
    i,
    COUNT(*) OVER (PARTITION BY i) as row_count, -- How many rows we have for each value of i
    source_d, -- Where each number originated from
    ROW_NUMBER() OVER (PARTITION BY i ORDER BY source_d) as row_num -- The ordering within each i value
FROM integers
ORDER BY i, source_d;

I noticed:

The row count grows until it equals the number of initial digits (3 in this case)

So I guess there is an initial growth phase: i=1: 1 row (from digit 1) i=2: 2 rows (from digits 1,2) i=3: 3 rows (from digits 1,2,3)

And then this stabilizes: i=4: 3 rows (maintains all 3 chains) So my mental model is that each individual initial digit is maintaining a chain of values: Starting Digits: 1, 2, 3

Chain 1 (starting from 1): 1 -> 2 -> 3 -> 4 -> 5 -> 6 -> 7 -> … ^ First chain always present

Chain 2 (starting from 2): 2 -> 3 -> 4 -> 5 -> 6 -> 7 -> … ^ Second chain joins at i=2

Chain 3 (starting from 3): 3 -> 4 -> 5 -> 6 -> 7 -> … ^ Third chain joins at i=3 So for instance at i = 6: i=6: source_d=1, row_num=1 (from Chain 1) source_d=2, row_num=2 (from Chain 2) source_d=3, row_num=3 (from Chain 3)

Each row at i=6 came from a different original chain, but they've all reached the value 6 through different paths of recursion.

Business/Finance⌗

Interesting Links⌗

Viagra laced contraband honey is pretty wild

Math/Stats⌗

Travel⌗

Other⌗

Work on what’s most important not what’s most urgent.