Request for a "force interactive mode" flag (-I) for psql - Mailing list pgsql-hackers

Back in 2003 Bruce Momjian proposed adding a flag (-I) to psql to force
it into "interactive" mode.  (See
http://archives.postgresql.org/pgsql-hackers/2003-11/msg00013.php for
the thread.)  The proposal was rejected because there was no proven need
for it at that time. I'd like to raise this proposal again, since I
think in our situation, this is the only fix for our problem.

Our environment is as follows:
* A large number of PostgreSQL 7.4 servers running on a variety of SuSE
Linux (9.0 - 9.3) servers
* A growing number of PostgreSQL 8.0.3 servers running on Windows Server
2003 servers. (We are running the native Win32 version of PostgreSQL,
not the Cygwin version.)

The servers are all located in remote offices.  Maintenance is done
remotely via SSH to a "local" bash command prompt where we use the
command line tools: psql, pg_dump, etc.  On Linux we use the native
sshd; on Windows we use Cygwin to get bash, sshd, cron, etc.  On Linux
this works fine; on Windows, however, psql thinks it is not in an
interactive console so we get no prompts, no line editing, no history,
very little cursor control, etc. (I see that the missing autocomplete
feature is a readline issue, but that's a topic for another posting.)
This makes remote maintenance on the Windows servers much more difficult
than it otherwise could be.

The issue appears to be due to isatty() returning false in the Windows
Cygwin environment.  From other research around the web, it appears that
if the app is "Cygwin-aware" is knows to override this check (or allow
manual override) or try to do further testing, but in this case, since
we are using the native Win32 version of PostgreSQL, psql doesn't do any
additional testing.

Also, even in the local console on Windows, running rxvt -- our
preferred terminal in Windows (since it lets us make our Windows command
line act just like our Linux command line <grin>) --  causes psql to
think that there is no terminal. (Our first encounter of this "no
terminal" problem was in trying to run psql via a local bash shell via
rxvt, and originally we thought that psql was hanging. Given our
reliance on psql for remote maintenance, this would have prevented our
rolling out a Windows version of PostgreSQL. It was only after too much
time looking at it with some low-level tools that we stumbled across the
fact that psql was simply silently waiting at a command prompt rather
than being hung. However, from other posts in these lists [e.g.:
http://archives.postgresql.org/pgsql-patches/2004-07/msg00369.php ] it
appears that other people also thought psql was hanging when it was run
from a terminal program, so I suppose I shouldn't feel too bad...)

Bruce's proposal and suggested code simply added a new "-I" flag to
force psql into "interactive mode" (by simply setting "pset.notty = 0").
From everything I can find (including reading through the Cygwin code,
ssh and sshd man pages and code, psql code, testing various modes of
running the sshd service in Windows, changing Windows profile account
permissions, etc.), adding this flag seems to be the only viable option
(other than writing a replacement for psql for our Windows servers,
something I'd prefer to not do). While I can obviously add this patch
myself and build and maintain a custom version of psql for our own use,
since there now is a true native version of PostgreSQL for Windows, I
think we will see a growing list of people supporting mixed environments
just like ours, and bumping into this exact same problem.

Thoughts?

- Bill

Bill Bartlett
meridianEMR, Inc.
http://www.meridianemr.com