Skip to content

Instantly share code, notes, and snippets.

@seanlinsley
Last active June 10, 2016 18:40
Show Gist options
  • Save seanlinsley/c3c9d0c704c21c5c67e0 to your computer and use it in GitHub Desktop.
Save seanlinsley/c3c9d0c704c21c5c67e0 to your computer and use it in GitHub Desktop.
# This asks the user if they want a read-only or read-write PSQL session. PSQL
# provides `\prompt`, but that doesn't provide a way to conditionally prompt
# based on whether it was being run interactively, or from inside another script.
#
# Commands that call PSQL non-interactively but load the .psqlrc file:
# - bash-completion
# - pg_restore
#
# Oddly, passing a string via `psql -c` doesn't load .psqlrc, so Postgres must
# have some internal mechanism to skip the config file, but hasn't provided it
# as a public API. Made worse is the fact that `pg_restore` isn't using that
# mechanism, so the behavior is inconsistent across commands. πŸ”₯πŸ”₯πŸ”₯
#
# Save this file to ~/.psqlprompt and add the below to ~/.psqlrc:
#
# \set read_only `bash ~/.psqlprompt`
# set default_transaction_read_only = :read_only;
#
function psql_prompt() {
local process_id=$PPID
local parent_state="$(echo $(ps -o state= $(ps -o ppid= $process_id)))"
# This uses the state attribute from `ps` to determine whether the parent
# process still has control over the terminal. From the `ps` documentation:
#
# S Marks a process that is sleeping for less than about 20 seconds.
# + The process is in the foreground process group of its control terminal.
#
# At the time of writing, all known scripts that call PSQL have the status of
# S+, and an interactive PSQL prompt's parent (bash) has the status of S.
#
if [[ ! $parent_state =~ '+' ]]; then
while true; do
read -p 'should this session be read-only? ' answer
case $answer in
[Yy]*) echo true; break;;
[Nn]*) echo false; break;;
esac
done
else
echo false;
fi;
}
psql_prompt
\set read_only `bash ~/.psqlprompt`
set default_transaction_read_only = :read_only;
@seanlinsley
Copy link
Author

Note that the names of the two files are wrong. They're only named like they are because Gists don't provide a way to set the language other than their file extension.

  • psqlprompt.sh -> .psqlprompt
  • psqlrc.sql -> .psqlrc

@seanlinsley
Copy link
Author

seanlinsley commented Jun 10, 2016

Note: pg_restore is fixed in Postgres 9.5.3 πŸŽ‰

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment