PostgreSQL 10.6外键有效性验证及数据防扩散方案问询

Hey there, let's break down how to solve your PostgreSQL 10.6 foreign key validation problem without manually writing 144 checks. Your core needs are: automatically detect invalid foreign key records, log them (not delete), and stop the issue from spreading—here's a step-by-step solution:

1. Auto-Generate Foreign Key Validation Queries #

Instead of writing checks for each table manually, we can query PostgreSQL's system catalogs to dynamically build validation statements for all foreign keys. This works by pulling metadata from pg_constraint, pg_class, and pg_attribute to identify every foreign key relationship.

Option 1: Generate Individual Check Queries #

Run this SQL to get a list of ready-to-run queries (one per foreign key) that will find invalid records:

SELECT format(
  $$
  SELECT
    '%I' AS foreign_table,
    '%I' AS foreign_column,
    %I AS invalid_value,
    '%I' AS referenced_table,
    '%I' AS referenced_column
  FROM %I
  WHERE %I NOT IN (SELECT %I FROM %I)
  $$,
  conrelid::regclass,
  attname,
  attname,
  confrelid::regclass,
  confattname,
  conrelid::regclass,
  attname,
  confattname,
  confrelid::regclass
) AS check_query
FROM pg_constraint
JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY(conkey)
JOIN pg_attribute confatt ON confrelid = confatt.attrelid AND confatt.attnum = ANY(confkey)
WHERE contype = 'f';

Each output row targets a specific foreign key (like dependencies.to_task referencing tasks.id) and returns any values that don't exist in the referenced table.

Option 2: Generate a Single Unified Check Query #

If you want to pull all invalid records in one go, use this to create a combined UNION ALL query:

SELECT string_agg(
  format(
    $$
    SELECT
      '%I' AS foreign_table,
      '%I' AS foreign_column,
      %I::text AS invalid_value,
      '%I' AS referenced_table,
      '%I' AS referenced_column
    FROM %I
    WHERE %I NOT IN (SELECT %I FROM %I)
    $$,
    conrelid::regclass,
    attname,
    attname,
    confrelid::regclass,
    confattname,
    conrelid::regclass,
    attname,
    confattname,
    confrelid::regclass
  ),
  ' UNION ALL '
) AS full_check_query
FROM pg_constraint
JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY(conkey)
JOIN pg_attribute confatt ON confrelid = confatt.attrelid AND confatt.attnum = ANY(confkey)
WHERE contype = 'f';

Copy the output full_check_query and run it—you'll get a single result set with all invalid foreign key records across your entire database.

2. Log Invalid Records (Don't Delete) #

To track issues over time and notify users, create a dedicated log table to store invalid records:

CREATE TABLE IF NOT EXISTS invalid_foreign_keys_log (
  log_id SERIAL PRIMARY KEY,
  check_timestamp TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  foreign_table TEXT NOT NULL,
  foreign_column TEXT NOT NULL,
  invalid_value TEXT NOT NULL,
  referenced_table TEXT NOT NULL,
  referenced_column TEXT NOT NULL
);

Automate Logging with a PL/pgSQL Function #

Wrap the check logic in a function so you can run it on a schedule:

CREATE OR REPLACE FUNCTION check_all_foreign_keys()
RETURNS VOID AS $$
DECLARE
  check_sql TEXT;
BEGIN
  SELECT string_agg(
    format(
      $$
      SELECT
        '%I' AS foreign_table,
        '%I' AS foreign_column,
        %I::text AS invalid_value,
        '%I' AS referenced_table,
        '%I' AS referenced_column
      FROM %I
      WHERE %I NOT IN (SELECT %I FROM %I)
      $$,
      conrelid::regclass,
      attname,
      attname,
      confrelid::regclass,
      confattname,
      conrelid::regclass,
      attname,
      confattname,
      confrelid::regclass
    ),
    ' UNION ALL '
  ) INTO check_sql
  FROM pg_constraint
  JOIN pg_attribute ON conrelid = attrelid AND attnum = ANY(conkey)
  JOIN pg_attribute confatt ON confrelid = confatt.attrelid AND confatt.attnum = ANY(confkey)
  WHERE contype = 'f';

  IF check_sql IS NOT NULL THEN
    EXECUTE 'INSERT INTO invalid_foreign_keys_log (foreign_table, foreign_column, invalid_value, referenced_table, referenced_column) ' || check_sql;
  END IF;
END;
$$ LANGUAGE plpgsql;

Run SELECT check_all_foreign_keys(); to populate the log with current invalid records. You can query this log later to notify users or trace when issues started.

3. Schedule Regular Checks to Stop Spread #

To prevent invalid data from being propagated to other databases:

• On Linux: Use crontab to run the function on a schedule. For example, add this line to run every 4 hours:

  0 */4 * * * psql -U your_username -d your_dbname -c "SELECT check_all_foreign_keys();" >> /var/log/pg_fk_checks.log 2>&1
  

• On Windows: Use Task Scheduler to run a batch file with the psql command above.

Before restoring backups to a database with foreign key constraints, run the check function first. Clean up or flag invalid records before restoring—this will prevent the target database from becoming corrupted.

Quick Note on Your Original Scenario #

It's unusual for a valid foreign key constraint (not marked NOT VALID) to have violating data—PostgreSQL normally blocks this unless constraints were bypassed (e.g., a misconfigured deferred transaction or direct catalog edits). Your existing checks (explain analyze, tablespaces, partition/row security flags) didn't turn up issues, so focusing on stopping spread first makes sense. Once you have logging in place, you can use the log data to trace back when invalid records were introduced.

内容的提问来源于stack exchange，提问作者ChrisMM