Last active
June 10, 2016 18:40
-
-
Save seanlinsley/c3c9d0c704c21c5c67e0 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\set read_only `bash ~/.psqlprompt` | |
set default_transaction_read_only = :read_only; |
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
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