Description

Phase 1 row counting aborted on statement timeout (SQLSTATE 57014), preventing views 25-108 from processing and blocking Phase 2/3 execution. Added exception handler in DO block to catch query_canceled, log timeout, and continue. Additionally improved error handling to properly capture and track complete error details for all row count failures, fixed materialized view handling for unanalyzed objects, preserved user interrupt capability, corrected progress tracking counts to 108 views, ensured complete error diagnostics storage, updated all documentation to reflect accurate view counts with search-based instructions that avoid line number drift, clarified temporary table lifecycle, and documented all output files for comprehensive user guidance.

Code Review Fixes Applied:

• Fixed object_type consistency: Changed from view_record.object_type (values 'VIEW'/'MATERIALIZED VIEW') to lowercase 'view' to match summary query filters
• Fixed cia_tmp_rowcount exception handling: Changed to re-raise all exceptions (both query_canceled and OTHERS) for proper error propagation
• Fixed error detail capture: Removed sentinel value pattern to allow full error messages via SQLERRM in tracking table
• Fixed timeout message: Changed from hard-coded "120s" to dynamic current_setting('statement_timeout')
• Fixed README line references: Changed all hard-coded line numbers to search-based instructions to avoid line number drift (e.g., "search for SET statement_timeout", "search for the view exclusion list")
• Fixed README timeout examples: Updated hard-coded "120s" examples to "{timeout_value}" placeholder
• Fixed materialized view handling: Use GREATEST(COALESCE(reltuples, 0), 0) to normalize negative reltuples to 0 for unanalyzed matviews
• Fixed user interrupt support: Distinguish statement timeout from user cancel (Ctrl+C) via SQLERRM check, re-raise user cancels to preserve interactive abort capability
• Fixed progress tracking: Added view_riksdagen_coalition_alignment_matrix exclusion to total_views query to match loop exclusions (108 views, not 109)
• Fixed timeout diagnostics: Changed timeout tracking to store actual SQLERRM instead of fixed string for complete error context
• Fixed documentation consistency: Updated all README examples from "24/109" and "25-109" to "24/108" and "25-108" to match corrected view count
• Fixed exclusion notice: Updated Phase 1 NOTICE to mention both excluded views (view_riksdagen_coalition_alignment_matrix, view_riksdagen_intelligence_dashboard)
• Fixed temp table references: Changed troubleshooting guidance from referencing temp table cia_extraction_tracking to persistent files (extraction_summary_report.csv, extract-sample-data.log)
• Clarified query_canceled comment: Updated to reflect exception covers both statement timeout and user cancel scenarios
• Documented temp table lifecycle: Added clarification that cia_extraction_tracking is temporary (available only during active psql session) and that data is persisted in CSV/log files
• Added output files documentation: Added extraction_summary_report.csv and extract-sample-data.log to documented output artifacts for user discovery

Technical Change:

-- Phase 1 DO block (lines 1257-1327)
FOR view_record IN ... LOOP
    BEGIN
        row_count := cia_tmp_rowcount(schema, view);
        INSERT INTO cia_view_row_counts VALUES (..., row_count);
        
        IF row_count > 0 THEN
            RAISE NOTICE '✓ Contains % rows', row_count;
        ELSE
            RAISE NOTICE '⚠️  EMPTY (0 rows)';
        END IF;
    EXCEPTION 
        WHEN query_canceled THEN
            -- Check if statement timeout or user cancel
            IF SQLERRM LIKE '%statement timeout%' OR 
               SQLERRM LIKE '%canceling statement due to statement timeout%' THEN
                -- Statement timeout: mark as -1, track with SQLERRM, continue loop
            ELSE
                -- User cancel (Ctrl+C): re-raise to stop script
                RAISE;
            END IF;
        WHEN OTHERS THEN
            -- Mark as error (-2), capture full SQLERRM, continue loop
    END;
END LOOP;

-- cia_tmp_rowcount function re-raises ALL exceptions for proper error capture
EXCEPTION 
    WHEN query_canceled THEN
        RAISE;  -- Re-raise query_canceled (timeout or user cancel) so caller can handle it
    WHEN OTHERS THEN
        RAISE;  -- Re-raise errors so caller can capture SQLERRM

-- Materialized view handling
SELECT GREATEST(COALESCE(c.reltuples, 0), 0)::BIGINT INTO row_count
-- Normalizes negative reltuples (-1 for unanalyzed) to 0

-- total_views query excludes both special views
WHERE viewname != 'view_riksdagen_coalition_alignment_matrix'
  AND viewname != 'view_riksdagen_intelligence_dashboard'

-- Phase 1 exclusion notice mentions both views
RAISE NOTICE 'Excluding: view_riksdagen_coalition_alignment_matrix, view_riksdagen_intelligence_dashboard';

Phase 2 Filtering:

WHERE row_count >= 0  -- Skip timeout (-1) and error (-2) views

Status Codes:

• > 0: Success, extract data
• 0: Empty, header-only CSV
• -1: Timeout, skip extraction
• -2: Error, skip extraction

Type of Change

Primary Changes

• 🐛 Bug Fix
• 🔄 Enhancement

Technical Changes

• 🏗️ Infrastructure
  • Database Changes
  • Performance Optimization
• 📝 Documentation
  • Technical Documentation

Impact Analysis

Political Analysis Impact

• Impact on data quality: Positive - complete error diagnostics captured with full PostgreSQL error messages including timeout context; unanalyzed materialized views now properly extracted instead of skipped; accurate progress tracking with correct view counts; complete documentation clarity on temp table lifecycle and output files
• Impact on analysis accuracy: Positive - now extracts all successful views instead of aborting at first timeout, properly skips errored views, correctly handles unanalyzed materialized views, provides accurate progress indicators (108 views), and offers clear guidance on where to find diagnostic information
• Impact on transparency features: Positive - timeout and error tracking in cia_extraction_tracking table with consistent object_type='view', full SQLERRM for both timeouts and errors, accurate view counts, complete documentation alignment with search-based instructions, clear temp table lifecycle documentation, and comprehensive output file documentation for user guidance

Technical Impact

• Performance impact: Neutral - same per-view timeout (120s), but script completes instead of aborting
• Security implications: None
• Dependency changes: None

Testing

• Political data validation completed
• Performance testing completed

Test 1 (Simulated Timeout): 3 views with 5s timeout, middle view times out

• Result: All 3 views attempted, timeout logged with dynamic "after 5s" message, script completed
• Verified: object_type='view' in cia_extraction_tracking, timeout properly caught

Test 2 (Production): 108 views with 120s timeout

• Result: All 108 views processed including view_election_cycle_network_analysis (position 24)

Test 3 (Exception Handling): Verified cia_tmp_rowcount re-raises query_canceled

• Result: Timeout exception properly propagates to caller, classified as row_count=-1

Test 4 (Error Handling): 3 views with nonexistent view causing error

• Result: All 3 views attempted, error exception propagated, script continued
• Verified: Full error message captured: relation "public.nonexistent_view_xyz_123" does not exist

Test 5 (Error Detail Capture): Verified complete error messages in tracking table

• Result: Full PostgreSQL error details stored in cia_extraction_tracking.error_message
• Verified: Error diagnostics include complete context (relation names, specific error types)

Test 6 (Materialized View Handling): Verified unanalyzed matviews with reltuples=-1

• Result: GREATEST(COALESCE(reltuples, 0), 0) normalizes -1 to 0
• Verified: Unanalyzed materialized views extracted with 0 rows instead of being skipped

Test 7 (User Interrupt Support): Verified SQLERRM distinguishes timeout from user cancel

• Result: Statement timeout message contains "canceling statement due to statement timeout"
• Verified: Timeout handled gracefully with continuation; user cancel would re-raise and stop script

Test 8 (Progress Count Accuracy): Verified total_views matches loop iterations

• Result: total_views query = 108, actual loop count = 108
• Verified: Progress indicator shows correct denominator [24/108] instead of incorrect [24/109]

Test 9 (Timeout SQLERRM Storage): Verified timeout tracking stores complete error message

• Result: SQLERRM "canceling statement due to statement timeout" stored in cia_extraction_tracking
• Verified: Complete timeout diagnostics available for troubleshooting

Test 10 (Documentation Consistency): Verified README examples match script output

• Result: All examples updated to show "24/108" and "25-108"
• Verified: Documentation accurately reflects 108 view count throughout with search-based instructions

Test 11 (Output Files): Verified extraction_summary_report.csv and extract-sample-data.log documentation

• Result: Both files documented in Output Files section
• Verified: Users can discover and verify all output artifacts; troubleshooting guidance is consistent

Documentation

• README updated
• Package/module documentation updated

Updated: README-EXTRACT-SAMPLE-DATA.md

• Added "Graceful Timeout Handling" section with temp table lifecycle clarification
• Updated troubleshooting guide with timeout behavior and persistent file references
• Fixed all line number references to use search-based instructions instead
• Updated timeout message examples to use dynamic placeholder
• Documented status codes and continuation logic
• Updated all progress examples from "24/109" to "24/108" for accuracy
• Added Output Files section documenting extraction_summary_report.csv and extract-sample-data.log
• Clarified that cia_extraction_tracking is temporary (available only during active psql session)
• Documented that timeout/error data is persisted in CSV and log files for later analysis

Checklist

• Code follows project coding standards
• Comments are clear and helpful
• Documentation is updated
• Tests are passing
• Security compliance is maintained
• Performance impact is acceptable
• Breaking changes are documented
• Changes are backward compatible

Additional Notes

Exception Handling Pattern:

• Exception handler must be inside DO block loop (not in cia_tmp_rowcount function) because timeout exception propagates from EXECUTE statement and terminates function context
• cia_tmp_rowcount re-raises ALL exceptions (both query_canceled and OTHERS) to allow caller's handler to properly classify errors and capture full error details
• Comment clarified to reflect that query_canceled covers both statement timeout and user-initiated cancel (Ctrl+C)
• Exception propagation preferred over sentinel return values for complete error diagnostics
• Block-level exception handling allows per-view isolation

Tracking Consistency:

• cia_extraction_tracking.object_type must use lowercase 'view' (not 'VIEW'/'MATERIALIZED VIEW') to match summary query filters at end of script
• Ensures timeout/error counts appear correctly in final reports

Error Detail Capture:

• All exceptions re-raised from cia_tmp_rowcount to preserve complete error context
• Phase 1 WHEN OTHERS handler captures full SQLERRM with detailed PostgreSQL error messages
• Phase 1 timeout handler now stores actual SQLERRM instead of fixed string for complete timeout context
• Error messages include complete diagnostics: relation names, permission issues, specific failure reasons, timeout details
• Enables better troubleshooting via extraction_summary_report.csv with full error context for all failure types

Materialized View Handling:

• pg_class.reltuples can be -1 for newly created or unanalyzed materialized views
• Use GREATEST(COALESCE(reltuples, 0), 0) to normalize negative values to 0
• Prevents unanalyzed materialized views from being skipped by Phase 2 filter (row_count >= 0)
• Unanalyzed matviews treated as empty (0 rows) and included in extraction with header-only CSV

User Interrupt Support:

• query_canceled exception catches both statement timeout AND user-initiated cancels (Ctrl+C)
• SQLERRM check distinguishes timeout (contains "statement timeout") from user cancel
• Statement timeouts handled gracefully with continuation to next view
• User cancels re-raised to stop script immediately, preserving interactive abort capability

Progress Tracking Accuracy:

• total_views query now excludes both view_riksdagen_coalition_alignment_matrix and view_riksdagen_intelligence_dashboard to match loop exclusions
• Progress indicator shows correct denominator: 108 views total
• Phase 1 NOTICE lists both excluded views for user clarity
• All documentation examples updated to reflect 108 view count

Documentation Stability:

• All hard-coded line number references replaced with search-based instructions
• Examples: "search for SET statement_timeout", "search for the view exclusion list"
• Prevents documentation drift as script evolves
• Dynamic timeout value used in message examples via "{timeout_value}" placeholder

Temporary Table Lifecycle:

• cia_extraction_tracking is created as a TEMP TABLE that exists only during the psql session
• Table is automatically dropped when the session ends (e.g., after psql -f extract-sample-data.sql exits)
• Users running via shell script cannot query the temp table after script completion
• Documentation clarifies temp table is available only during active psql session
• Timeout and error data is persisted for later analysis in extraction_summary_report.csv and extract-sample-data.log

Output Files Documentation:

• Added extraction_summary_report.csv to documented output artifacts
• Added extract-sample-data.log to documented output artifacts
• Users can now discover all output files in Output Files section
• Troubleshooting guidance is consistent with documented output files
• Clear path for users to find diagnostic information after script completion

Dynamic Timeout Display:

• Timeout warning uses current_setting('statement_timeout') to display actual configured value
• Message remains accurate if users modify statement_timeout setting
• Documentation updated to reflect dynamic behavior

Complex views (e.g., view_election_cycle_network_analysis with CTEs, window functions, cross joins) may timeout during query planning even with 0 rows due to planner overhead. Graceful handling ensures maximum data extraction without manual exclusion list maintenance.

Security Considerations

• No sensitive data exposed
• Security best practices followed
• Compliance requirements met

Release Notes

Fixed: extract-sample-data.sql now continues processing all views after timeout or error instead of aborting. Timeouts logged in cia_extraction_tracking table with status code -1, errors with status code -2, both with consistent object_type='view'. Phase 2/3 automatically skip timed-out and errored views. Timeout messages display actual configured timeout value dynamically. Complete PostgreSQL error messages captured in tracking table for better diagnostics (e.g., "relation does not exist", "permission denied"). Unanalyzed materialized views (reltuples=-1) now properly extracted as empty views instead of being skipped. User interrupt (Ctrl+C) capability preserved - statement timeouts continue gracefully while user cancels stop the script immediately. Progress tracking corrected to show accurate 108 view count. Documentation updated with search-based instructions to avoid line number drift, temp table lifecycle clarification, and complete output files documentation (extraction_summary_report.csv, extract-sample-data.log) for comprehensive user guidance.

Custom agent used: intelligence-operative
Expert in political science, intelligence analysis, OSINT, behavioral analysis, and Swedish politics with focus on exposing high risk national entities

💬 We'd love your input! Share your thoughts on Copilot coding agent in our 2 minute survey.