Shell脚本中执行SQL时PSQL_PREAMBLE设置的search_path未生效的技术咨询

Let's break down why your PSQL_PREAMBLE's search_path setting isn't working, and fix it step by step.

Possible Causes & Debug Steps #

1. Check if Sed is Altering Your SET Statement #

The sed regex replacements (${REGEX_DATETIME_DIFF} and ${REGEX_SCHEMA}) might be accidentally modifying or removing the SET search_path line. To verify this:

• Run this command to output the final SQL that gets sent to psql:

  { echo "${PSQL_PREAMBLE}; DROP TABLE IF EXISTS ventilation_durations; CREATE TABLE ventilation_durations AS "; cat durations/ventilation_durations.sql; } | sed -r -e "${REGEX_DATETIME_DIFF}" | sed -r -e "${REGEX_SCHEMA}" > debug_output.sql
  

• Open debug_output.sql and confirm the line SET search_path TO public,mimiciii; is present and unmodified. If it's missing or altered, adjust your regex patterns to exclude this line.

2. Verify No Conflicting search_path Settings in CONNSTR #

Your connection string ${CONNSTR} might include parameters that override the search_path (e.g., -c "search_path=public" or a database-level search_path setting). Check if CONNSTR has any such flags by echoing it:

echo ${CONNSTR}

If there's an explicit search_path setting here, remove it or update it to include mimiciii.

3. Ensure SQL Syntax is Valid #

A syntax error in the combined SQL could cause psql to stop executing before applying the search_path (or roll back the session). Run the debug SQL directly in psql to check for errors:

psql ${CONNSTR} -f debug_output.sql

Look for any error messages that might indicate why the search_path isn't being applied.

Fixed Command Options #

Once you've ruled out the above issues, use one of these more reliable approaches to set the search_path:

Option 1: Use psql's --set Flag (Recommended) #

This sets the search_path at the session level when psql starts, which is more robust than embedding a SET statement in your SQL:

psql ${CONNSTR} --set=search_path=public,mimiciii -f <(
  cat durations/ventilation_durations.sql | 
  sed -r -e "${REGEX_DATETIME_DIFF}" | 
  sed -r -e "${REGEX_SCHEMA}"
)

Option 2: Execute SET Separately Before Running the SQL #

Run the SET statement first with -c, then execute the processed SQL file:

psql ${CONNSTR} -c "${PSQL_PREAMBLE}" -f <(
  cat durations/ventilation_durations.sql | 
  sed -r -e "${REGEX_DATETIME_DIFF}" | 
  sed -r -e "${REGEX_SCHEMA}"
)

Option 3: Prepend the SET Statement to Your SQL File #

If you prefer keeping everything in a single SQL stream, ensure the SET statement is the first line and no regex is modifying it:

( echo "${PSQL_PREAMBLE};" && cat durations/ventilation_durations.sql ) | 
sed -r -e "${REGEX_DATETIME_DIFF}" | 
sed -r -e "${REGEX_SCHEMA}" | 
psql ${CONNSTR}

(Note: Using && instead of a space-separated echo ensures the SET is a separate statement, which helps psql parse it correctly.)

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