Thread: Select works only when connected from login postgres
I'm trying to get a new build of 9.6.1 working on Debian
stable and I'm seeing some odd behavior where things work
correctly if I run psql when logged in as user 'postgres',
but if I'm logged-in as user 'doom' (my usual login), I don't
seem to have any select privileges. Even this fails
silently:
select 'world' as hello;
But if run logged in as 'postgres', all is well:
sudo su - postgres
/usr/local/pgsql/bin/psql --dbname=doom --username=doom
doom=# select 'world' as hello;
select 'world' as hello;
hello
-------
world
Note that I'm talking about the unix logins, in both cases
the postgresql username/role is 'doom' (which has Superuser
privileges and is the owner of the 'doom' database).
I've got my pg_hba.conf setup to use "trust" for all users:
# TYPE DATABASE USER ADDRESS METHOD
local all all trust
host all all 127.0.0.1/32 trust
Looking at how the program files are installed, I see they're all
owned by 'root' with group 'staff':
ls -la /usr/local/pgsql/bin/psql
-rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql
On speculation, I added doom to the staff group and reloaded
pg, but that didn't help. I see that the data files are all
owned by postgres, but I don't think that's unusual (or else
I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
privs to the data files):
drwx------ 1 postgres postgres 42 Nov 26 16:14 base
I'm running out of ideas for things to check. Any suggestions?
Some more info, for completeness sake:
\du
List of roles
Role name | Attributes
| Member of
-----------+------------------------------------------------------------+-----------
doom | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
\l
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
doom | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
...
On 12/03/2016 12:08 PM, Joseph Brenner wrote:
> I'm trying to get a new build of 9.6.1 working on Debian
> stable and I'm seeing some odd behavior where things work
> correctly if I run psql when logged in as user 'postgres',
> but if I'm logged-in as user 'doom' (my usual login), I don't
> seem to have any select privileges. Even this fails
> silently:
>
> select 'world' as hello;
>
> But if run logged in as 'postgres', all is well:
>
> sudo su - postgres
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom
> doom=# select 'world' as hello;
> select 'world' as hello;
> hello
> -------
> world
>
> Note that I'm talking about the unix logins, in both cases
> the postgresql username/role is 'doom' (which has Superuser
> privileges and is the owner of the 'doom' database).
>
> I've got my pg_hba.conf setup to use "trust" for all users:
>
> # TYPE DATABASE USER ADDRESS METHOD
> local all all trust
> host all all 127.0.0.1/32 trust
>
> Looking at how the program files are installed, I see they're all
> owned by 'root' with group 'staff':
>
> ls -la /usr/local/pgsql/bin/psql
> -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql
>
> On speculation, I added doom to the staff group and reloaded
> pg, but that didn't help. I see that the data files are all
> owned by postgres, but I don't think that's unusual (or else
> I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
> privs to the data files):
>
> drwx------ 1 postgres postgres 42 Nov 26 16:14 base
>
> I'm running out of ideas for things to check. Any suggestions?
>
>
> Some more info, for completeness sake:
>
> \du
> List of roles
> Role name | Attributes
> | Member of
> -----------+------------------------------------------------------------+-----------
> doom | Superuser, Create role, Create DB | {}
> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
>
> \l
> List of databases
> Name | Owner | Encoding | Collate | Ctype |
> Access privileges
> -----------+----------+----------+-------------+-------------+-----------------------
> doom | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> ...
>
>
Did you receive the previous suggestions?
--
Adrian Klaver
adrian.klaver@aklaver.com
Yes, and sorry about the re-post. I thought my original message was
hung-up in moderation, so I was doing an unsub/resub fandango to get
email addresses to match.
On Sat, Dec 3, 2016 at 12:13 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
> On 12/03/2016 12:08 PM, Joseph Brenner wrote:
>>
>> I'm trying to get a new build of 9.6.1 working on Debian
>> stable and I'm seeing some odd behavior where things work
>> correctly if I run psql when logged in as user 'postgres',
>> but if I'm logged-in as user 'doom' (my usual login), I don't
>> seem to have any select privileges. Even this fails
>> silently:
>>
>> select 'world' as hello;
>>
>> But if run logged in as 'postgres', all is well:
>>
>> sudo su - postgres
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom
>> doom=# select 'world' as hello;
>> select 'world' as hello;
>> hello
>> -------
>> world
>>
>> Note that I'm talking about the unix logins, in both cases
>> the postgresql username/role is 'doom' (which has Superuser
>> privileges and is the owner of the 'doom' database).
>>
>> I've got my pg_hba.conf setup to use "trust" for all users:
>>
>> # TYPE DATABASE USER ADDRESS METHOD
>> local all all trust
>> host all all 127.0.0.1/32 trust
>>
>> Looking at how the program files are installed, I see they're all
>> owned by 'root' with group 'staff':
>>
>> ls -la /usr/local/pgsql/bin/psql
>> -rwxr-xr-x 1 root staff 516824 Nov 26 23:20 /usr/local/pgsql/bin/psql
>>
>> On speculation, I added doom to the staff group and reloaded
>> pg, but that didn't help. I see that the data files are all
>> owned by postgres, but I don't think that's unusual (or else
>> I'd try adding 'doom' to the 'postgres' group, and adding g+rwx
>> privs to the data files):
>>
>> drwx------ 1 postgres postgres 42 Nov 26 16:14 base
>>
>> I'm running out of ideas for things to check. Any suggestions?
>>
>>
>> Some more info, for completeness sake:
>>
>> \du
>> List of roles
>> Role name | Attributes
>> | Member of
>>
>> -----------+------------------------------------------------------------+-----------
>> doom | Superuser, Create role, Create DB |
>> {}
>> postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
>> {}
>>
>> \l
>> List of databases
>> Name | Owner | Encoding | Collate | Ctype |
>> Access privileges
>>
>> -----------+----------+----------+-------------+-------------+-----------------------
>> doom | doom | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
>> ...
>>
>>
>
> Did you receive the previous suggestions?
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
Joseph Brenner <doomvox@gmail.com> writes:
> I'm trying to get a new build of 9.6.1 working on Debian
> stable and I'm seeing some odd behavior where things work
> correctly if I run psql when logged in as user 'postgres',
> but if I'm logged-in as user 'doom' (my usual login), I don't
> seem to have any select privileges. Even this fails
> silently:
> select 'world' as hello;
Um, define "fails silently"? Do you get a command prompt from
psql? What does the interaction look like *exactly*? If psql
just returns to the shell command prompt, maybe it's giving a
nonzero exit code? (try "echo $?" afterwards)
[ and later... ]
> The only thing unusual about the steps that I followed was I built
> with port 5433 (rather than 5432) as the default,
This is not as simple as it might look; the default port is actually
wired into libpq.so, not psql itself. And on most brands of Linuxen,
it's not that easy to get a program to link to a non-default copy of
a shared library if there's a copy in /usr/lib. However, if you were
connecting to the wrong port number, I'd still not expect that it
just dies without saying anything.
Hmm ... a different take on that is that maybe psql is crashing
because it's linking to an ABI-incompatible libpq. You should try
"ldd" on the psql executable and see if it's resolving the libpq
dependency to the copy you intended.
regards, tom lane
Our story thus far: I've now got three different pg installations, with three
servers running simultaneously:
ps ax | egrep postgres | egrep '\-D'
748 ? S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D
/var/lib/postgresql/9.4/main -c
config_file=/etc/postgresql/9.4/main/postgresql.co
23019 pts/1 S 0:01 /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data
27352 ? S 0:00 /usr/lib/postgresql/9.6/bin/postgres -D
/var/lib/postgresql/9.6/main -c
config_file=/etc/postgresql/9.6/main/postgresql.co
The 9.4 version presumably is using the standard default port 5432.
The 9.6 /usr/local version was compiled to use port 5433.
The other 9.6 version I just installed from apt.postgresql.org,
which according to the installation messages used port 5434
(automatically grabbing the next unused port, I gather: pretty
slick).
This is what I mean by "failing silently", I get no output from
the select, no error message inside of psql, nothing in the error
logs, *but* psql doesn't terminate:
doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
psql (9.6.1)
Type "help" for help.
doom=# select 'hello' as world;
doom=#
Nothing else gives me any output either: \l, \du, etc.
> > The only thing unusual about the steps that I followed was I built
> > with port 5433 (rather than 5432) as the default,
> This is not as simple as it might look; the default port is actually
> wired into libpq.so, not psql itself. And on most brands of Linuxen,
> it's not that easy to get a program to link to a non-default copy of
> a shared library if there's a copy in /usr/lib. However, if you were
> connecting to the wrong port number, I'd still not expect that it
> just dies without saying anything.
Well, I've been presuming that the INSTALL file knows what
it's talking about in describing configure options:
--with-pgport=NUMBER
Set "NUMBER" as the default port number for server and
clients. The default is 5432. The port can always be
changed later on, but if you specify it here then both
server and clients will have the same default compiled in,
which can be very convenient.
> ... maybe psql is crashing
> because it's linking to an ABI-incompatible libpq. You should try
> "ldd" on the psql executable and see if it's resolving the libpq
> dependency to the copy you intended.
Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
/usr/local/pgsql/lib/libpq.so.5
ldd /usr/local/pgsql/bin/psql
linux-vdso.so.1 (0x00007fff033e2000)
libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x00007f2c34e8f000)
libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
(0x00007f2c34c45000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f2c34944000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f2c34599000)
libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
(0x00007f2c34338000)
libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
(0x00007f2c33f3c000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
(0x00007f2c33d1f000)
libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007f2c33af5000)
/lib64/ld-linux-x86-64.so.2 (0x00007f2c350bc000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f2c338f1000)
This seems a bit peculiar though, the binary packages are both
configured to use the same, unversioned libpq?
ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007fe9db2ea000)
ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007fa7337ec000)
On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joseph Brenner <doomvox@gmail.com> writes:
>> I'm trying to get a new build of 9.6.1 working on Debian
>> stable and I'm seeing some odd behavior where things work
>> correctly if I run psql when logged in as user 'postgres',
>> but if I'm logged-in as user 'doom' (my usual login), I don't
>> seem to have any select privileges. Even this fails
>> silently:
>
>> select 'world' as hello;
>
> Um, define "fails silently"? Do you get a command prompt from
> psql? What does the interaction look like *exactly*? If psql
> just returns to the shell command prompt, maybe it's giving a
> nonzero exit code? (try "echo $?" afterwards)
>
> [ and later... ]
>
>> The only thing unusual about the steps that I followed was I built
>> with port 5433 (rather than 5432) as the default,
>
> This is not as simple as it might look; the default port is actually
> wired into libpq.so, not psql itself. And on most brands of Linuxen,
> it's not that easy to get a program to link to a non-default copy of
> a shared library if there's a copy in /usr/lib. However, if you were
> connecting to the wrong port number, I'd still not expect that it
> just dies without saying anything.
>
> Hmm ... a different take on that is that maybe psql is crashing
> because it's linking to an ABI-incompatible libpq. You should try
> "ldd" on the psql executable and see if it's resolving the libpq
> dependency to the copy you intended.
>
> regards, tom lane
On Saturday, December 3, 2016, Joseph Brenner <doomvox@gmail.com> wrote:
doom=# select 'hello' as world;
doom=#
Nothing else gives me any output either: \l, \du, etc.
For kicks, how about \echo or \! Something that doesn't need a server to work.
David J,
>For kicks, how about \echo or \! Something that doesn't need a server to work. Sure: those do work. doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom psql (9.6.1) Type "help" for help. doom=# select 'hello' as world; doom=# \echo 'yo' yo doom=# \! ls -lad p* drwxr-xr-x 1 doom doom 12 Nov 16 12:29 perl5 On Sat, Dec 3, 2016 at 7:48 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Saturday, December 3, 2016, Joseph Brenner <doomvox@gmail.com> wrote: >> >> >> doom=# select 'hello' as world; >> doom=# >> >> Nothing else gives me any output either: \l, \du, etc. >> > > For kicks, how about \echo or \! Something that doesn't need a server to > work. > > David J,
On 12/03/2016 07:38 PM, Joseph Brenner wrote: > Our story thus far: I've now got three different pg installations, with three > servers running simultaneously: > > ps ax | egrep postgres | egrep '\-D' > 748 ? S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D > /var/lib/postgresql/9.4/main -c > config_file=/etc/postgresql/9.4/main/postgresql.co > 23019 pts/1 S 0:01 /usr/local/pgsql/bin/postgres -D > /usr/local/pgsql/data > 27352 ? S 0:00 /usr/lib/postgresql/9.6/bin/postgres -D > /var/lib/postgresql/9.6/main -c > config_file=/etc/postgresql/9.6/main/postgresql.co > > The 9.4 version presumably is using the standard default port 5432. So is the 9.4 instance the production/live database? > The 9.6 /usr/local version was compiled to use port 5433. > The other 9.6 version I just installed from apt.postgresql.org, > which according to the installation messages used port 5434 > (automatically grabbing the next unused port, I gather: pretty > slick). > > This is what I mean by "failing silently", I get no output from > the select, no error message inside of psql, nothing in the error > logs, *but* psql doesn't terminate: > > doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom > psql (9.6.1) > Type "help" for help. > > doom=# select 'hello' as world; > doom=# So what happens when you specify the port in your psql connection, eg: /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433 /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434 > > Nothing else gives me any output either: \l, \du, etc. > >>> The only thing unusual about the steps that I followed was I built >>> with port 5433 (rather than 5432) as the default, > >> This is not as simple as it might look; the default port is actually >> wired into libpq.so, not psql itself. And on most brands of Linuxen, >> it's not that easy to get a program to link to a non-default copy of >> a shared library if there's a copy in /usr/lib. However, if you were >> connecting to the wrong port number, I'd still not expect that it >> just dies without saying anything. > > Well, I've been presuming that the INSTALL file knows what > it's talking about in describing configure options: > > --with-pgport=NUMBER > Set "NUMBER" as the default port number for server and > clients. The default is 5432. The port can always be > changed later on, but if you specify it here then both > server and clients will have the same default compiled in, > which can be very convenient. Generally it is just easier/safer to just change the port in postgresql.conf. That is what the Debian packaging does when it sets up multiple Postgres instances. > >> ... maybe psql is crashing >> because it's linking to an ABI-incompatible libpq. You should try >> "ldd" on the psql executable and see if it's resolving the libpq >> dependency to the copy you intended. > > Ok... for /usr/local/pgsql/bin/psql this looks right, correct? > /usr/local/pgsql/lib/libpq.so.5 > > ldd /usr/local/pgsql/bin/psql > linux-vdso.so.1 (0x00007fff033e2000) > libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x00007f2c34e8f000) > libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6 > (0x00007f2c34c45000) > libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f2c34944000) > libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f2c34599000) > libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0 > (0x00007f2c34338000) > libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0 > (0x00007f2c33f3c000) > libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 > (0x00007f2c33d1f000) > libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5 (0x00007f2c33af5000) > /lib64/ld-linux-x86-64.so.2 (0x00007f2c350bc000) > libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f2c338f1000) > > This seems a bit peculiar though, the binary packages are both > configured to use the same, unversioned libpq? > > ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq > libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007fe9db2ea000) > > ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq > libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5 (0x00007fa7337ec000) > > On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Joseph Brenner <doomvox@gmail.com> writes: >>> I'm trying to get a new build of 9.6.1 working on Debian >>> stable and I'm seeing some odd behavior where things work >>> correctly if I run psql when logged in as user 'postgres', >>> but if I'm logged-in as user 'doom' (my usual login), I don't >>> seem to have any select privileges. Even this fails >>> silently: >> >>> select 'world' as hello; >> >> Um, define "fails silently"? Do you get a command prompt from >> psql? What does the interaction look like *exactly*? If psql >> just returns to the shell command prompt, maybe it's giving a >> nonzero exit code? (try "echo $?" afterwards) >> >> [ and later... ] >> >>> The only thing unusual about the steps that I followed was I built >>> with port 5433 (rather than 5432) as the default, >> >> This is not as simple as it might look; the default port is actually >> wired into libpq.so, not psql itself. And on most brands of Linuxen, >> it's not that easy to get a program to link to a non-default copy of >> a shared library if there's a copy in /usr/lib. However, if you were >> connecting to the wrong port number, I'd still not expect that it >> just dies without saying anything. >> >> Hmm ... a different take on that is that maybe psql is crashing >> because it's linking to an ABI-incompatible libpq. You should try >> "ldd" on the psql executable and see if it's resolving the libpq >> dependency to the copy you intended. >> >> regards, tom lane > > -- Adrian Klaver adrian.klaver@aklaver.com
> So is the 9.4 instance the production/live database?
Essentially, but it's not heavily used: this is me messing around on a dev box.
> So what happens when you specify the port in your psql connection, eg:
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
other two complain like so:
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
On Sat, Dec 3, 2016 at 9:11 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
> On 12/03/2016 07:38 PM, Joseph Brenner wrote:
>>
>> Our story thus far: I've now got three different pg installations, with
>> three
>> servers running simultaneously:
>>
>> ps ax | egrep postgres | egrep '\-D'
>> 748 ? S 0:04 /usr/lib/postgresql/9.4/bin/postgres -D
>> /var/lib/postgresql/9.4/main -c
>> config_file=/etc/postgresql/9.4/main/postgresql.co
>> 23019 pts/1 S 0:01 /usr/local/pgsql/bin/postgres -D
>> /usr/local/pgsql/data
>> 27352 ? S 0:00 /usr/lib/postgresql/9.6/bin/postgres -D
>> /var/lib/postgresql/9.6/main -c
>> config_file=/etc/postgresql/9.6/main/postgresql.co
>>
>> The 9.4 version presumably is using the standard default port 5432.
>
>
> So is the 9.4 instance the production/live database?
>
>> The 9.6 /usr/local version was compiled to use port 5433.
>> The other 9.6 version I just installed from apt.postgresql.org,
>> which according to the installation messages used port 5434
>> (automatically grabbing the next unused port, I gather: pretty
>> slick).
>>
>> This is what I mean by "failing silently", I get no output from
>> the select, no error message inside of psql, nothing in the error
>> logs, *but* psql doesn't terminate:
>>
>> doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
>> psql (9.6.1)
>> Type "help" for help.
>>
>> doom=# select 'hello' as world;
>> doom=#
>
>
> So what happens when you specify the port in your psql connection, eg:
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>
> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>
>>
>> Nothing else gives me any output either: \l, \du, etc.
>>
>>>> The only thing unusual about the steps that I followed was I built
>>>> with port 5433 (rather than 5432) as the default,
>>
>>
>>> This is not as simple as it might look; the default port is actually
>>> wired into libpq.so, not psql itself. And on most brands of Linuxen,
>>> it's not that easy to get a program to link to a non-default copy of
>>> a shared library if there's a copy in /usr/lib. However, if you were
>>> connecting to the wrong port number, I'd still not expect that it
>>> just dies without saying anything.
>>
>>
>> Well, I've been presuming that the INSTALL file knows what
>> it's talking about in describing configure options:
>>
>> --with-pgport=NUMBER
>> Set "NUMBER" as the default port number for server and
>> clients. The default is 5432. The port can always be
>> changed later on, but if you specify it here then both
>> server and clients will have the same default compiled in,
>> which can be very convenient.
>
>
> Generally it is just easier/safer to just change the port in
> postgresql.conf. That is what the Debian packaging does when it sets up
> multiple Postgres instances.
>
>
>>
>>> ... maybe psql is crashing
>>> because it's linking to an ABI-incompatible libpq. You should try
>>> "ldd" on the psql executable and see if it's resolving the libpq
>>> dependency to the copy you intended.
>>
>>
>> Ok... for /usr/local/pgsql/bin/psql this looks right, correct?
>> /usr/local/pgsql/lib/libpq.so.5
>>
>> ldd /usr/local/pgsql/bin/psql
>> linux-vdso.so.1 (0x00007fff033e2000)
>> libpq.so.5 => /usr/local/pgsql/lib/libpq.so.5 (0x00007f2c34e8f000)
>> libreadline.so.6 => /lib/x86_64-linux-gnu/libreadline.so.6
>> (0x00007f2c34c45000)
>> libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f2c34944000)
>> libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f2c34599000)
>> libssl.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
>> (0x00007f2c34338000)
>> libcrypto.so.1.0.0 => /usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
>> (0x00007f2c33f3c000)
>> libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0
>> (0x00007f2c33d1f000)
>> libtinfo.so.5 => /lib/x86_64-linux-gnu/libtinfo.so.5
>> (0x00007f2c33af5000)
>> /lib64/ld-linux-x86-64.so.2 (0x00007f2c350bc000)
>> libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f2c338f1000)
>>
>> This seems a bit peculiar though, the binary packages are both
>> configured to use the same, unversioned libpq?
>>
>> ldd /usr/lib/postgresql/9.4/bin/psql | egrep libpq
>> libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5
>> (0x00007fe9db2ea000)
>>
>> ldd /usr/lib/postgresql/9.6/bin/psql | egrep libpq
>> libpq.so.5 => /usr/lib/x86_64-linux-gnu/libpq.so.5
>> (0x00007fa7337ec000)
>>
>> On Sat, Dec 3, 2016 at 4:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>
>>> Joseph Brenner <doomvox@gmail.com> writes:
>>>>
>>>> I'm trying to get a new build of 9.6.1 working on Debian
>>>> stable and I'm seeing some odd behavior where things work
>>>> correctly if I run psql when logged in as user 'postgres',
>>>> but if I'm logged-in as user 'doom' (my usual login), I don't
>>>> seem to have any select privileges. Even this fails
>>>> silently:
>>>
>>>
>>>> select 'world' as hello;
>>>
>>>
>>> Um, define "fails silently"? Do you get a command prompt from
>>> psql? What does the interaction look like *exactly*? If psql
>>> just returns to the shell command prompt, maybe it's giving a
>>> nonzero exit code? (try "echo $?" afterwards)
>>>
>>> [ and later... ]
>>>
>>>> The only thing unusual about the steps that I followed was I built
>>>> with port 5433 (rather than 5432) as the default,
>>>
>>>
>>> This is not as simple as it might look; the default port is actually
>>> wired into libpq.so, not psql itself. And on most brands of Linuxen,
>>> it's not that easy to get a program to link to a non-default copy of
>>> a shared library if there's a copy in /usr/lib. However, if you were
>>> connecting to the wrong port number, I'd still not expect that it
>>> just dies without saying anything.
>>>
>>> Hmm ... a different take on that is that maybe psql is crashing
>>> because it's linking to an ABI-incompatible libpq. You should try
>>> "ldd" on the psql executable and see if it's resolving the libpq
>>> dependency to the copy you intended.
>>>
>>> regards, tom lane
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
On 12/03/2016 09:38 PM, Joseph Brenner wrote: >> So is the 9.4 instance the production/live database? > > Essentially, but it's not heavily used: this is me messing around on a dev box. > >> So what happens when you specify the port in your psql connection, eg: >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433 >> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434 > > With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the > other two complain like so: > > psql: could not connect to server: No such file or directory > Is the server running locally and accepting > connections on Unix domain socket "/tmp/.s.PGSQL.5434"? > Alright how about?: /usr/bin/psql --dbname=doom --username=doom -p 5434 My suspicion is that when you did the source build you got some cross contamination of libraries. If it where me I would get rid of the instance that you built from source, assuming that there is nothing important on it. -- Adrian Klaver adrian.klaver@aklaver.com
Yeah, I get the sense I need to simplify the situation, if only to eliminate some distractions from the story. This morning I was thinking I want to play around with pg_hba.conf settings some more, but after that I'll do some pg_dumps and uninstalls and see how things behave. Oh, trying: /usr/bin/psql --dbname=doom --username=doom -p 543x As login 'doom' the connection happens only for port 5432, as login 'postgres' the connection fails differently for port 5432: psql: FATAL: Peer authentication failed for user "doom" (Like I said, I need to play with pg_hba.conf a bit.) But that actually surprises me, I would've expected it'd be on port 5434, because that's the most recent binary install, not the original 9.4 version: /usr/bin/psql -V psql (PostgreSQL) 9.6.1 /etc/postgresql/9.6/main/postgresql.conf port = 5434 # (change requires restart) /etc/postgresql/9.4/main/postgresql.conf port = 5432 # (change requires restart) On Sun, Dec 4, 2016 at 7:06 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 12/03/2016 09:38 PM, Joseph Brenner wrote: >>> >>> So is the 9.4 instance the production/live database? >> >> >> Essentially, but it's not heavily used: this is me messing around on a dev >> box. >> >>> So what happens when you specify the port in your psql connection, eg: >>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432 >>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433 >>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434 >> >> >> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the >> other two complain like so: >> >> psql: could not connect to server: No such file or directory >> Is the server running locally and accepting >> connections on Unix domain socket "/tmp/.s.PGSQL.5434"? >> > > Alright how about?: > > /usr/bin/psql --dbname=doom --username=doom -p 5434 > > My suspicion is that when you did the source build you got some cross > contamination of libraries. > > If it where me I would get rid of the instance that you built from source, > assuming that there is nothing important on it. > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> My suspicion is that when you did the source build you got some cross
> contamination of libraries.
That's the best theory I can come up with either, although libpq's
APIs haven't really changed in any non-backwards-compatible fashion
in years. I could imagine a newer psql flat-out crashing because it
tries to call some libpq function that doesn't exist in an older
libpq, but that's not what we're seeing here. It's really weird.
A couple of tests that might help narrow things down:
1. In the server configuration, enable log_connections,
log_disconnections, and log_statement = 'all'. Does anything show up in
the log when you connect with the broken psql and issue commands?
2. If you issue commands that span multiple lines --- unclosed left
parens, multiline string literals, missing semicolons --- does the
psql prompt change to match?
regards, tom lane
Joseph Brenner <doomvox@gmail.com> writes:
>> So what happens when you specify the port in your psql connection, eg:
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
> other two complain like so:
> psql: could not connect to server: No such file or directory
> Is the server running locally and accepting
> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
What this probably indicates is that the other two installations are
configured to put their socket files someplace else than /tmp, perhaps
/var/run/postgresql. Connecting to them and issuing "show
unix_socket_directories" would tell the tale.
You can persuade a psql to connect to a socket in a nondefault directory
by giving the directory name as host, eg
psql --host=/var/run/postgresql -p 5434
It would be interesting to try all nine combinations of the psql's
supplied by your various installations and the servers, just to confirm
which ones behave normally and which don't. Of course, the other two
would have to be told --host=/tmp to talk to the handbuilt server.
regards, tom lane
Okay: I think I'm closing in on the trouble. I didn't used to
have a ~/.psqlrc file, but recently I experimented with
creating one. When I have a .psqlrc file containing the magic
incantaion
\pset pager off
*Then* everything works. All three of my extant postgresql
installations work correctly whether connected to with unix
login doom or postgres.
Our story thus far:
I've got three postgresql installations running on a Debian
stable machine:
o version 9.4, a binary package from Debian stable
(using port 5432),
o a build of 9.6.1 from scratch (using port 5433),
o an installation of 9.6.1 from a binary pgdb
package, (using port 5434).
I've been seeing some odd behavior where a psql connection will
work fine if connected to as *unix login* 'postgres', but not
always if with unix login 'doom', it which case even the
simplest selects can fail silently, without any messages in the
log or on the screen to explain why.
Tom Lane suggested I might try connecting all three of my psql
clients to all three of the servers (by juggling the port and host
options).
Since I was going to conduct at least 9 experiments (with two
logins each), I decided to script it, but before that I made an
effort to clean things up and make sure all three installations
were exactly parallel setups: all needed a user 'doom' with
superuser privileges, all needed a 'doom' database which was
owned by 'doom', all have a pg_hba.conf with auth-method trust,
and so on. I also added additional logging settings (as
suggested by Tom) to all three the postgresql.conf files.
There were some other small things I changed, such as making
all the log files "chmod a+r" so the script would be able to
read them and echo newly added messages...
And I created a ~/.psqlrc file, though I didn't expect it
to have any effect on my new trial runs using the --command
feature, e.g.:
/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"
When I got the script cleaned up and working, I found that all
9 connections worked, for both logins: something I'd done has
fixed the problem (or alternately, the problem has "gone away
on it's own").
For example, now when connecting to my local build (without
bothering to specifying port & host):
doom@tango:~$ /usr/local/pgsql/bin/psql --dbname=doom --username=doom
This works now (as does \du, \l, etc):
select 'hello' as world;
world
-------
hello
I just went around temporarily undoing things I did while
straigtening up, and I find there's one thing I can do that
consistently breaks things: removing my new ~/.psqlrc file.
In fact, it appears that I need to have a file that exists and
contains this line:
\pset pager off
I thought it might be just the fact that it was non-empty, and
tried a few other settings without any luck. If I have that
line in my ~/.psqlrc, then this probe returns the expected result:
/usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
--host=/var/run/postgresql --command="SELECT 'hello' AS world;"
If I delete that line, then the select fails silently again.
Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed, e.g.
Pager usage is off.
Or in the logs:
2016-12-05 16:17:04 PST [18517-3] doom@doom LOG: statement: set
client_encoding to 'unicode'
Because I also had this line:
\encoding unicode
On Sun, Dec 4, 2016 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joseph Brenner <doomvox@gmail.com> writes:
>>> So what happens when you specify the port in your psql connection, eg:
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5432
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5433
>>> /usr/local/pgsql/bin/psql --dbname=doom --username=doom -p 5434
>
>> With /usr/local/pgsql/bin/psql, only "-p 5433" connects, the
>> other two complain like so:
>
>> psql: could not connect to server: No such file or directory
>> Is the server running locally and accepting
>> connections on Unix domain socket "/tmp/.s.PGSQL.5434"?
>
> What this probably indicates is that the other two installations are
> configured to put their socket files someplace else than /tmp, perhaps
> /var/run/postgresql. Connecting to them and issuing "show
> unix_socket_directories" would tell the tale.
>
> You can persuade a psql to connect to a socket in a nondefault directory
> by giving the directory name as host, eg
>
> psql --host=/var/run/postgresql -p 5434
>
> It would be interesting to try all nine combinations of the psql's
> supplied by your various installations and the servers, just to confirm
> which ones behave normally and which don't. Of course, the other two
> would have to be told --host=/tmp to talk to the handbuilt server.
>
> regards, tom lane
Another oddity I noticed is that I expected that the .psqlrc
file would not be read at all when using the --command feature,
but instead I would see messaging that indicated the commands
in there were being executed
New behavior in 9.6 - you shouldn't be seeing this in your 9.4 test setup unless you are using the 9.6 psql to connect to the 9.4 server (its a client-specific behavior).
David J.
On 12/05/2016 05:13 PM, Joseph Brenner wrote: > I just went around temporarily undoing things I did while > straigtening up, and I find there's one thing I can do that > consistently breaks things: removing my new ~/.psqlrc file. > In fact, it appears that I need to have a file that exists and > contains this line: > > \pset pager off So what does: env | grep PAGER show? -- Adrian Klaver adrian.klaver@aklaver.com
Joseph Brenner <doomvox@gmail.com> writes:
> Okay: I think I'm closing in on the trouble. I didn't used to
> have a ~/.psqlrc file, but recently I experimented with
> creating one. When I have a .psqlrc file containing the magic
> incantaion
> \pset pager off
> *Then* everything works.
Ah! So, most likely, there is something wrong with the local installation
of "more", or whatever the environment variable PAGER is set to. If you
say "more somefile", does it behave reasonably? Check "echo $PAGER"
as well.
regards, tom lane
> So what does: > > env | grep PAGER > > show? Nothing. I have no PAGER settting (I don't normally use one). On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: > On 12/05/2016 05:13 PM, Joseph Brenner wrote: > > >> I just went around temporarily undoing things I did while >> straigtening up, and I find there's one thing I can do that >> consistently breaks things: removing my new ~/.psqlrc file. >> In fact, it appears that I need to have a file that exists and >> contains this line: >> >> \pset pager off > > > So what does: > > env | grep PAGER > > show? > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com
Wait, that's not quite right. The user 'postgres' has no PAGER envar, but user 'doom' has an empty value: PAGER= On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner <doomvox@gmail.com> wrote: >> So what does: >> >> env | grep PAGER >> >> show? > > Nothing. I have no PAGER settting (I don't normally use one). > > > On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 12/05/2016 05:13 PM, Joseph Brenner wrote: >> >> >>> I just went around temporarily undoing things I did while >>> straigtening up, and I find there's one thing I can do that >>> consistently breaks things: removing my new ~/.psqlrc file. >>> In fact, it appears that I need to have a file that exists and >>> contains this line: >>> >>> \pset pager off >> >> >> So what does: >> >> env | grep PAGER >> >> show? >> >> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com
And I guess I did that intentionally, my .bashrc has # I use emacs shells, I got a "pager" already: export PAGER='' On Mon, Dec 5, 2016 at 8:52 PM, Joseph Brenner <doomvox@gmail.com> wrote: > Wait, that's not quite right. The user 'postgres' has no PAGER envar, > but user 'doom' has an empty value: > > PAGER= > > > On Mon, Dec 5, 2016 at 8:50 PM, Joseph Brenner <doomvox@gmail.com> wrote: >>> So what does: >>> >>> env | grep PAGER >>> >>> show? >> >> Nothing. I have no PAGER settting (I don't normally use one). >> >> >> On Mon, Dec 5, 2016 at 5:33 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote: >>> On 12/05/2016 05:13 PM, Joseph Brenner wrote: >>> >>> >>>> I just went around temporarily undoing things I did while >>>> straigtening up, and I find there's one thing I can do that >>>> consistently breaks things: removing my new ~/.psqlrc file. >>>> In fact, it appears that I need to have a file that exists and >>>> contains this line: >>>> >>>> \pset pager off >>> >>> >>> So what does: >>> >>> env | grep PAGER >>> >>> show? >>> >>> >>> >>> >>> -- >>> Adrian Klaver >>> adrian.klaver@aklaver.com
And I guess I did that intentionally, my .bashrc has
# I use emacs shells, I got a "pager" already:
export PAGER=''
PAGER= psql --pset=pager=always -c 'select 1;'
<nothing on the screen>
Remove PAGER= and I'm good.
I guess that psql could be a bit more helpful by reporting something to stderr if the value of PAGER is not an executable (platform dependent...)
I find it a bit odd that all of your queries were using the pager...did I miss where you reported that setting?
David J.
Well yeah, trying to run a PAGER that's not there might throw an error. Or you know, nothing in PAGER might imply "pager off". > I find it a bit odd that all of your queries were using the pager...did I miss where you reported that setting? I didn't report it because I wasn't looking in that direction. A PAGER set to blank for login 'doom' and no PAGER setting for login 'postgres' explains much of what I was seeing, I think: selects run as 'doom' tended to be blank (unless I had a pset no pager somewhere), selects run as 'postgres' always worked. On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doomvox@gmail.com> wrote: >> >> And I guess I did that intentionally, my .bashrc has >> >> # I use emacs shells, I got a "pager" already: >> export PAGER='' >> > > PAGER= psql --pset=pager=always -c 'select 1;' > <nothing on the screen> > > Remove PAGER= and I'm good. > > I guess that psql could be a bit more helpful by reporting something to > stderr if the value of PAGER is not an executable (platform dependent...) > > I find it a bit odd that all of your queries were using the pager...did I > miss where you reported that setting? > > David J. >
Looking back on the order of events, I think it went like this: Back in around May, I was annoyed at pager behavior and wanted to get rid of them-- I tried a blank PAGER setting in my .bashrc (and forgot about it). I also noticed the psql option "\pset pager off". For not particular reason, it didn't occur to me to just put that in a .psqlrc file, and instead I had it in a command-line alias: alias psql="psql --pset='pager=off'" Then more recently I started experimenting with a new build of 9.6.1: The behavior at that stage looked like a difference between 9.4 (run with a bare "psql") and 9.6 (run using a path to the new build). Then later, as I was experimenting with multiple postgres installations, I got more careful about using paths to make sure I knew which one I was running. That cut the alias out of the picture, and the .bashrc setting took over: The behavior at that stage looked like a difference between user login 'doom' and 'postgres'. I kept chasing after obscure file permissions settings or postgres internal permissions (maybe there was something about the new row level security features?), and so on. The folks here kept assuming it had to be some kind of version skew cross-talk between the different installations. Something I'd thought of vaguely (and wish I'd tried) was just creating a new user and trying to reproduce the behavior in a clean account set-up (there are always options to suppress rc files for testing, but suppressing .bashrc isn't that simple). The next thing on the list for me was to remove everything except the new 9.6.1 pgdb binary install: by itself that wouldn't have gotten any closer to isolating the problem, but it would've eliminated some distractions. Oddly enough, if I'd been reading up-to-date pg docs, I might not have tried the .psqlrc setup that pointed the finger at the pager situation: that was just something I was doing on the side, and I didn't think it'd have any effect on a --command invocation, so I didn't worry about it. So yeah, some better messaging when PAGER is mangled wouldn't hurt, if that's possible. Falling back to "pager off" would make sense to me. On Mon, Dec 5, 2016 at 9:28 PM, Joseph Brenner <doomvox@gmail.com> wrote: > Well yeah, trying to run a PAGER that's not there might throw an error. > Or you know, nothing in PAGER might imply "pager off". > >> I find it a bit odd that all of your queries were using the pager...did I miss where you reported that setting? > > I didn't report it because I wasn't looking in that direction. A > PAGER set to blank for login 'doom' and no PAGER setting for login > 'postgres' explains much of what I was seeing, I think: selects run as > 'doom' tended to be blank (unless I had a pset no pager somewhere), > selects run as 'postgres' always worked. > > > > > > > On Mon, Dec 5, 2016 at 9:03 PM, David G. Johnston > <david.g.johnston@gmail.com> wrote: >> On Mon, Dec 5, 2016 at 9:53 PM, Joseph Brenner <doomvox@gmail.com> wrote: >>> >>> And I guess I did that intentionally, my .bashrc has >>> >>> # I use emacs shells, I got a "pager" already: >>> export PAGER='' >>> >> >> PAGER= psql --pset=pager=always -c 'select 1;' >> <nothing on the screen> >> >> Remove PAGER= and I'm good. >> >> I guess that psql could be a bit more helpful by reporting something to >> stderr if the value of PAGER is not an executable (platform dependent...) >> >> I find it a bit odd that all of your queries were using the pager...did I >> miss where you reported that setting? >> >> David J. >>
Joseph Brenner <doomvox@gmail.com> writes:
> Looking back on the order of events, I think it went like this:
> [ careful postmortem ]
Thanks for following up!
> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if
> that's possible. Falling back to "pager off" would make sense to me.
Agreed. One thing that would be very simple is to treat an empty PAGER
value the same as "unset". Detecting whether a nonempty value is behaving
sanely seems a great deal harder; depending on what pager you're using
and how you stop it, nonzero exit codes from the called process might
be normal. I think it might be practical to issue a warning if we get
an exit code of 126 or 127, though. We have a comment in archive-command
invocation:
* Per the Single Unix Spec, shells report exit status > 128 when a called
* command died on a signal. Also, 126 and 127 are used to report
* problems such as an unfindable command; treat those as fatal errors
* too.
The relevant text in POSIX is
If a command is not found, the exit status shall be 127. If the
command name is found, but it is not an executable utility, the
exit status shall be 126. Applications that invoke utilities
without using the shell should use these exit status values to
report similar errors.
I don't believe we want to complain about exit on a signal, because
SIGTERM or SIGINT is a typical exit in some pager setups. But these
two codes strongly suggest something broken about your PAGER value.
So I propose
(1) ignore PAGER if it's an empty string
(2) if pclose returns exit code 126 or 127, report that the PAGER
command didn't work. I'm not sure how complex that is, because IIRC
the pclose is at some remove from the popen call, but if it's not
unreasonably hairy we should do it.
regards, tom lane
> Agreed. One thing that would be very simple is to treat an empty PAGER > value the same as "unset". Sounds excellent. > Detecting whether a nonempty value is behaving > sanely seems a great deal harder ... I was thinking a check for existence and executability, but I guess that's covered already... if you use a random string as PAGER you get a sh error: export PAGER="nadatech" /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT 'hello' AS world;" sh: 1: nadatech: not found So the empty PAGER value case is the only one that doesn't seem covered already. (I'm talented about finding these things...) On Tue, Dec 6, 2016 at 9:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joseph Brenner <doomvox@gmail.com> writes: >> Looking back on the order of events, I think it went like this: >> [ careful postmortem ] > > Thanks for following up! > >> So yeah, some better messaging when PAGER is mangled wouldn't hurt, if >> that's possible. Falling back to "pager off" would make sense to me. > > Agreed. One thing that would be very simple is to treat an empty PAGER > value the same as "unset". Detecting whether a nonempty value is behaving > sanely seems a great deal harder; depending on what pager you're using > and how you stop it, nonzero exit codes from the called process might > be normal. I think it might be practical to issue a warning if we get > an exit code of 126 or 127, though. We have a comment in archive-command > invocation: > > * Per the Single Unix Spec, shells report exit status > 128 when a called > * command died on a signal. Also, 126 and 127 are used to report > * problems such as an unfindable command; treat those as fatal errors > * too. > > The relevant text in POSIX is > > If a command is not found, the exit status shall be 127. If the > command name is found, but it is not an executable utility, the > exit status shall be 126. Applications that invoke utilities > without using the shell should use these exit status values to > report similar errors. > > I don't believe we want to complain about exit on a signal, because > SIGTERM or SIGINT is a typical exit in some pager setups. But these > two codes strongly suggest something broken about your PAGER value. > > So I propose > (1) ignore PAGER if it's an empty string > (2) if pclose returns exit code 126 or 127, report that the PAGER > command didn't work. I'm not sure how complex that is, because IIRC > the pclose is at some remove from the popen call, but if it's not > unreasonably hairy we should do it. > > regards, tom lane
Joseph Brenner <doomvox@gmail.com> writes:
>> Agreed. One thing that would be very simple is to treat an empty PAGER
>> value the same as "unset".
> Sounds excellent.
Actually, after thinking about it a bit longer, should PAGER-set-but-
empty be treated as equivalent to "pager off", rather than as a request
to use the default pager? I could see arguments either way for that.
>> Detecting whether a nonempty value is behaving
>> sanely seems a great deal harder ...
> I was thinking a check for existence and executability, but I guess
> that's covered already... if you use a random string as PAGER you get
> a sh error:
> export PAGER="nadatech"
> /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom
> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT
> 'hello' AS world;"
> sh: 1: nadatech: not found
Hm, so you do; so my thought that this needs explicit code on our end
seems wrong. [ experiments... ] It seems like the specific case of
PAGER being empty or all-white-space causes the shell to think that
it's executing an empty line and just do nothing (in particular, not
print any error). pclose then returns EPIPE, at least on my platform,
which we could report but it doesn't seem like a very useful report.
Any other case seems to provoke a shell complaint that's probably
sufficient for diagnosis.
So what I'm thinking now is that if PAGER is empty or all white space
then we should not try to use it as a shell command; we can either
treat the case as "pager off" or as "use default pager". Everything
else we can leave to the invoked shell to complain about.
Comments?
regards, tom lane
So what I'm thinking now is that if PAGER is empty or all white space
then we should not try to use it as a shell command; we can either
treat the case as "pager off" or as "use default pager". Everything
else we can leave to the invoked shell to complain about.
My pick would be to leave \pset pager to control the on/off nature of the pager and treat both an unset and an empty string PAGER identically and solely for the purpose of choosing which pager to use should one be required.
David J.
Well, my take would be that if you've taken the trouble to set an empty string as the PAGER that means something, and it probably means you don't want any pager to be used. But then, I would say that. On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joseph Brenner <doomvox@gmail.com> writes: >>> Agreed. One thing that would be very simple is to treat an empty PAGER >>> value the same as "unset". > >> Sounds excellent. > > Actually, after thinking about it a bit longer, should PAGER-set-but- > empty be treated as equivalent to "pager off", rather than as a request > to use the default pager? I could see arguments either way for that. > >>> Detecting whether a nonempty value is behaving >>> sanely seems a great deal harder ... > >> I was thinking a check for existence and executability, but I guess >> that's covered already... if you use a random string as PAGER you get >> a sh error: > >> export PAGER="nadatech" >> /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom >> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT >> 'hello' AS world;" > >> sh: 1: nadatech: not found > > Hm, so you do; so my thought that this needs explicit code on our end > seems wrong. [ experiments... ] It seems like the specific case of > PAGER being empty or all-white-space causes the shell to think that > it's executing an empty line and just do nothing (in particular, not > print any error). pclose then returns EPIPE, at least on my platform, > which we could report but it doesn't seem like a very useful report. > Any other case seems to provoke a shell complaint that's probably > sufficient for diagnosis. > > So what I'm thinking now is that if PAGER is empty or all white space > then we should not try to use it as a shell command; we can either > treat the case as "pager off" or as "use default pager". Everything > else we can leave to the invoked shell to complain about. > > Comments? > > regards, tom lane
But on the other hand, if you've got a blank PAGER envar and a "\pset pager something", the pset should win (just as it does now). On Tue, Dec 6, 2016 at 1:53 PM, Joseph Brenner <doomvox@gmail.com> wrote: > Well, my take would be that if you've taken the trouble to set an > empty string as the PAGER that means something, and it probably means > you don't want any pager to be used. > > But then, I would say that. > > > On Tue, Dec 6, 2016 at 12:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Joseph Brenner <doomvox@gmail.com> writes: >>>> Agreed. One thing that would be very simple is to treat an empty PAGER >>>> value the same as "unset". >> >>> Sounds excellent. >> >> Actually, after thinking about it a bit longer, should PAGER-set-but- >> empty be treated as equivalent to "pager off", rather than as a request >> to use the default pager? I could see arguments either way for that. >> >>>> Detecting whether a nonempty value is behaving >>>> sanely seems a great deal harder ... >> >>> I was thinking a check for existence and executability, but I guess >>> that's covered already... if you use a random string as PAGER you get >>> a sh error: >> >>> export PAGER="nadatech" >>> /usr/lib/postgresql/9.6/bin/psql --no-psqlrc --dbname=doom >>> --username=doom -p 5434 --host=/var/run/postgresql --command="SELECT >>> 'hello' AS world;" >> >>> sh: 1: nadatech: not found >> >> Hm, so you do; so my thought that this needs explicit code on our end >> seems wrong. [ experiments... ] It seems like the specific case of >> PAGER being empty or all-white-space causes the shell to think that >> it's executing an empty line and just do nothing (in particular, not >> print any error). pclose then returns EPIPE, at least on my platform, >> which we could report but it doesn't seem like a very useful report. >> Any other case seems to provoke a shell complaint that's probably >> sufficient for diagnosis. >> >> So what I'm thinking now is that if PAGER is empty or all white space >> then we should not try to use it as a shell command; we can either >> treat the case as "pager off" or as "use default pager". Everything >> else we can leave to the invoked shell to complain about. >> >> Comments? >> >> regards, tom lane
Joseph Brenner <doomvox@gmail.com> writes:
> Well, my take would be that if you've taken the trouble to set an
> empty string as the PAGER that means something, and it probably means
> you don't want any pager to be used.
Yeah, on reflection that argument seems pretty persuasive. So I propose
the attached patch.
BTW, I realized while testing this that there's still one gap in our
understanding of what went wrong for you: cases like "SELECT 'hello'"
should not have tried to use the pager, because that would've produced
less than a screenful of data. But that's irrelevant here, because it
can easily be shown that psql doesn't behave nicely if PAGER is set to
empty when it does try to use the pager.
regards, tom lane
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 261652a..9915731 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*************** $endif
*** 3801,3808 ****
If the query results do not fit on the screen, they are piped
through this command. Typical values are
<literal>more</literal> or <literal>less</literal>. The default
! is platform-dependent. The use of the pager can be disabled by
! using the <command>\pset</command> command.
</para>
</listitem>
</varlistentry>
--- 3801,3809 ----
If the query results do not fit on the screen, they are piped
through this command. Typical values are
<literal>more</literal> or <literal>less</literal>. The default
! is platform-dependent. Use of the pager can be disabled by setting
! <envar>PAGER</envar> to empty, or by using pager-related options of
! the <command>\pset</command> command.
</para>
</listitem>
</varlistentry>
diff --git a/src/fe_utils/print.c b/src/fe_utils/print.c
index 1ec74f1..5c5d285 100644
*** a/src/fe_utils/print.c
--- b/src/fe_utils/print.c
*************** PageOutput(int lines, const printTableOp
*** 2874,2879 ****
--- 2874,2885 ----
pagerprog = getenv("PAGER");
if (!pagerprog)
pagerprog = DEFAULT_PAGER;
+ else
+ {
+ /* if PAGER is empty or all-white-space, don't use pager */
+ if (strspn(pagerprog, " \t\r\n") == strlen(pagerprog))
+ return stdout;
+ }
disable_sigpipe_trap();
pagerpipe = popen(pagerprog, "w");
if (pagerpipe)
diff --git a/src/interfaces/libpq/fe-print.c b/src/interfaces/libpq/fe-print.c
index c33dc42..e596a51 100644
*** a/src/interfaces/libpq/fe-print.c
--- b/src/interfaces/libpq/fe-print.c
*************** PQprint(FILE *fout, const PGresult *res,
*** 166,173 ****
screen_size.ws_col = 80;
#endif
pagerenv = getenv("PAGER");
if (pagerenv != NULL &&
! pagerenv[0] != '\0' &&
!po->html3 &&
((po->expanded &&
nTups * (nFields + 1) >= screen_size.ws_row) ||
--- 166,174 ----
screen_size.ws_col = 80;
#endif
pagerenv = getenv("PAGER");
+ /* if PAGER is unset, empty or all-white-space, don't use pager */
if (pagerenv != NULL &&
! strspn(pagerenv, " \t\r\n") != strlen(pagerenv) &&
!po->html3 &&
((po->expanded &&
nTups * (nFields + 1) >= screen_size.ws_row) ||
Tom Lane wrote:
> BTW, I realized while testing this that there's still one gap in our
> understanding of what went wrong for you: cases like "SELECT 'hello'"
> should not have tried to use the pager, because that would've produced
> less than a screenful of data
At some point emacs was mentioned as the terminal:
>> And I guess I did that intentionally, my .bashrc has
>>
>> # I use emacs shells, I got a "pager" already:
>> export PAGER=''
The M-x shell mode of emacs has a so-called "dumb" terminal
emulation (that's the value of $TERM) where the notion of a "page"
doesn't quite apply.
For instance, when using emacs 24.3 with my default pager on an
Ubuntu desktop, this is what I get:
test=> select 1;
WARNING: terminal is not fully functional
- (press RETURN)
?column?
----------
1
(1 row)
I suspect that psql is unable to determine the screen size
of the "dumb" terminal, and that it's the fault of the terminal
rather than psql.
The warning is displayed by "less" AFAICS.
There are other psql features like tab-completion that don't work
in this mode because emacs interpret keystrokes first for
itself, in effect mixing emacs functionalities with these of the
application run in the terminal. It's awesome sometimes
and irritating at other times depending on what you expect :)
OTOH it has also a M-x term command/mode that provides a
more sophisticated screen emulation into which paging seems
to work exactly like in a normal terminal and the emacs key bindings
are turned off.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite
Yes, I have a tendency to use emacs sub-shells (and occasionally M-x sql-postgres)-- I thought I'd reproduced the behavior in an xterm, but I was just trying again and I don't see it. It does seem that the dumbness of my dumb terminal is a factor. If I understand the way this works, it could be an even more baffling behavior if I were using an xterm: with a blank PAGER your output would disappear only if the select exceeded a certain number of lines... On Wed, Dec 7, 2016 at 2:31 AM, Daniel Verite <daniel@manitou-mail.org> wrote: > Tom Lane wrote: > >> BTW, I realized while testing this that there's still one gap in our >> understanding of what went wrong for you: cases like "SELECT 'hello'" >> should not have tried to use the pager, because that would've produced >> less than a screenful of data > > At some point emacs was mentioned as the terminal: > >>> And I guess I did that intentionally, my .bashrc has >>> >>> # I use emacs shells, I got a "pager" already: >>> export PAGER='' > > The M-x shell mode of emacs has a so-called "dumb" terminal > emulation (that's the value of $TERM) where the notion of a "page" > doesn't quite apply. > > For instance, when using emacs 24.3 with my default pager on an > Ubuntu desktop, this is what I get: > > test=> select 1; > WARNING: terminal is not fully functional > - (press RETURN) > ?column? > ---------- > 1 > (1 row) > > I suspect that psql is unable to determine the screen size > of the "dumb" terminal, and that it's the fault of the terminal > rather than psql. > The warning is displayed by "less" AFAICS. > > There are other psql features like tab-completion that don't work > in this mode because emacs interpret keystrokes first for > itself, in effect mixing emacs functionalities with these of the > application run in the terminal. It's awesome sometimes > and irritating at other times depending on what you expect :) > > OTOH it has also a M-x term command/mode that provides a > more sophisticated screen emulation into which paging seems > to work exactly like in a normal terminal and the emacs key bindings > are turned off. > > > Best regards, > -- > Daniel Vérité > PostgreSQL-powered mailer: http://www.manitou-mail.org > Twitter: @DanielVerite
Joseph Brenner <doomvox@gmail.com> writes:
> I thought I'd reproduced the behavior in an xterm, but I was just
> trying again and I don't see it. It does seem that the dumbness of my
> dumb terminal is a factor.
Evidently.
> If I understand the way this works, it could be an even more baffling
> behavior if I were using an xterm: with a blank PAGER your output
> would disappear only if the select exceeded a certain number of
> lines...
Yeah, that was exactly the behavior I was seeing before fixing it
(the fix is pushed btw).
regards, tom lane