Implement Runtime Query File Replacement

[ankitsejwal]

Background

Flow.bi requires the ability to show custom constraint definitions instead of actual PostgreSQL constraints to provide a curated database experience for our users. The constraints tab should display data from our intf_studio.pgweb_constraints table rather than querying PostgreSQL's system catalogs.

Initial Approach and Challenges

What We Tried First: SQL Conditional Logic

I initially attempted to solve this by implementing conditional SQL logic directly within the table_constraints.sql query using Common Table Expressions (CTEs). The approach involved:

WITH flowbi_constraints AS (
  SELECT CASE
    WHEN current_setting('pgweb.custom_constraints', true) = 'true'
      AND EXISTS (SELECT 1 FROM information_schema.tables
                  WHERE table_schema = 'intf_studio'
                  AND table_name = 'pgweb_constraints')
    THEN 'use_flowbi'
    ELSE 'use_standard'
  END AS mode
)
-- ... conditional UNION query based on mode

Why This Failed

This approach encountered a fundamental PostgreSQL limitation:

1. Parse-time validation: PostgreSQL validates table existence during query planning phase, not execution
2. UNION query constraints: Both sides of UNION operations are parsed regardless of WHERE conditions
3. Test environment failures: GitHub Actions tests failed with pq: relation "intf_studio.pgweb_constraints" does not exist

Even with comprehensive safety checks and conditional logic, PostgreSQL would still attempt to validate the intf_studio.pgweb_constraints table reference before any runtime conditions could be evaluated.

New Approach: Runtime File Replacement

Architecture Decision

After researching pgweb's query loading system, I discovered that pgweb uses Go's //go:embed directive to embed SQL files at compile time. The solution involved modifying this architecture to support runtime file replacement while maintaining backward compatibility.

Implementation Approach

1. Modified SQL Loading Architecture (pkg/statements/sql.go):

   • Converted TableConstraints from static embed to dynamic loading
   • Added loadTableConstraintsSQL() function that checks for external files first
   • Maintained graceful fallback to embedded SQL when external files are unavailable

2. Runtime Query Generation:

   SELECT
     conname AS name,
     definition
   FROM
     intf_studio.pgweb_constraints
   WHERE
     nspname = $1
     AND relname = $2
   ORDER BY
     contype DESC;

Benefits of This Solution

• Clean separation: Custom logic is completely separate from core pgweb functionality
• Test compatibility: All existing tests continue to pass without modification
• Runtime flexibility: Easy to enable/disable per deployment environment
• No performance impact: Standard mode operates with zero overhead
• Maintainable: Clear boundary between standard and custom behavior

Technical Details

How It Works

Custome Constraints Mode (PGWEB_CUSTOM_CONSTRAINTS=true):

1. Docker entrypoint generates /tmp/queries/table_constraints.sql with custom query
2. pgweb starts and init() function runs loadTableConstraintsSQL()
3. External file is found and loaded, replacing the embedded SQL
4. All constraint requests now query intf_studio.pgweb_constraints

Standard Mode (PGWEB_CUSTOM_CONSTRAINTS=false or unset):

1. No external file is generated
2. loadTableConstraintsSQL() falls back to embedded SQL
3. Standard PostgreSQL constraint queries work unchanged

Backward compatibility

This approach overcomes PostgreSQL's parse-time validation limitations while maintaining clean architecture and full backward compatibility. The solution provides Flow.bi with the custom constraints display functionality needed while ensuring that standard pgweb deployments continue to work exactly as before.

Integration Test

Up next.
Current status - Pending