Advanced SQL — Series 2

Preview — 3 of 10 questions

PostgreSQL 14+ supports a CYCLE clause on recursive CTEs: What does CYCLE do here, compared to manually tracking a visited-nodes array?

javascript

WITH RECURSIVE search_graph AS (
  SELECT id, link FROM graph WHERE id = 1
  UNION ALL
  SELECT g.id, g.link FROM graph g, search_graph sg WHERE g.id = sg.link
) CYCLE id SET is_cycle USING path
SELECT * FROM search_graph;

AIt prevents the recursive CTE from ever exceeding 100 iterations, as a safety limit

BIt is a query hint that tells the planner to parallelize the recursion

CIt automatically detects when a row would revisit a value already seen earlier in the recursion, stops recursing on that branch, and exposes a boolean column marking the cycle — without you having to manually maintain a path array

DIt automatically deduplicates the final result set, removing any repeated rows

What is the purpose of pg_advisory_lock(key)?

javascript

SELECT pg_advisory_lock(12345);
-- ... critical section ...
SELECT pg_advisory_unlock(12345);

AIt acquires an application-defined lock, identified by an arbitrary integer key, with no connection to any table or row — useful for coordinating application-level critical sections across sessions

BIt locks a specific row in a table identified by key, equivalent to SELECT ... FOR UPDATE

CIt locks an entire table to prevent concurrent writes

DIt is a read-only lock that only blocks DDL statements

An id SERIAL column shows gaps after several inserts fail and roll back — e.g. rows exist at id = 1, 2, 5, 6, with 3 and 4 missing. Why?

javascript

BEGIN;
INSERT INTO orders DEFAULT VALUES; -- consumes id=3
INSERT INTO orders DEFAULT VALUES; -- consumes id=4
ROLLBACK; -- both inserts undone, but the sequence isn't

APostgreSQL reclaims and reuses skipped IDs automatically, so this indicates data corruption

BGaps only occur if VACUUM has not been run recently

CSERIAL columns re-use the highest rolled-back value on the next insert

DSERIAL sequences are not transactional — nextval() is consumed immediately when called, independent of whether the enclosing transaction later commits or rolls back

Answer all 10 questions (7 more), see explanations for every answer, and track your score.