BUG #14645: Can't use psql from within program used by "copy ... from program" script?
The following bug has been logged on the website:
Bug reference: 14645
Logged by: John Norman
Email address: john@7fff.com
PostgreSQL version: 9.4.11
Operating system: OS/X
Description:
Here's a little .sql script in a directory bug/ in my home.
drop database if exists bug;
create database bug;
\c bug
create table bug (id int);
copy bug(id) from program '$HOME/bug/data' csv header;
select * from bug;
It calls a script called data. I run it with psql -X -f
$HOME/bug/setup.sql
This works for the "data" script:
#/bin/bash
echo "id"
echo "1"
echo "2"
This doesn't:
#/bin/bash
echo "id"
echo "1"
psql -X -t -q -c "select 2" | xargs
I can't figure out if psql is disallowed from the program referenced by the
copy program; or if somehow standard out is getting nuked.
The xargs is to remove the spaces are the "2" generated by the psql
command.
My use case was that I wanted part of the script referenced via copy program
to get something out of the database. Didn't work.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
john@7fff.com writes:
copy bug(id) from program '$HOME/bug/data' csv header;
It calls a script called data. I run it with psql -X -f
$HOME/bug/setup.sql
This works for the "data" script:
#/bin/bash
echo "id"
echo "1"
echo "2"
This doesn't:
#/bin/bash
echo "id"
echo "1"
psql -X -t -q -c "select 2" | xargs
I can't figure out if psql is disallowed from the program referenced by the
copy program; or if somehow standard out is getting nuked.
I think you're getting messed up by the fact that the program will
be run by the server, ie, under the postgres user's login, not your
own login. Try capturing psql's stderr to see if it's printing a
cant-login type of message.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thanks, Tom.
I'm running this all locally on my laptop.
I checkout stdout and stderr: Nothing.
John
On Sat, May 6, 2017 at 12:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
john@7fff.com writes:
copy bug(id) from program '$HOME/bug/data' csv header;
It calls a script called data. I run it with psql -X -f
$HOME/bug/setup.sqlThis works for the "data" script:
#/bin/bash
echo "id"
echo "1"
echo "2"This doesn't:
#/bin/bash
echo "id"
echo "1"
psql -X -t -q -c "select 2" | xargsI can't figure out if psql is disallowed from the program referenced by
the
copy program; or if somehow standard out is getting nuked.
I think you're getting messed up by the fact that the program will
be run by the server, ie, under the postgres user's login, not your
own login. Try capturing psql's stderr to see if it's printing a
cant-login type of message.regards, tom lane
(I'm really stumped. I almost got out dtrace but figured it was so weird I
shouldn't get into that.)
On Sat, May 6, 2017 at 12:59 PM, John Norman <john@7fff.com> wrote:
Show quoted text
Thanks, Tom.
I'm running this all locally on my laptop.
I checkout stdout and stderr: Nothing.
John
On Sat, May 6, 2017 at 12:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
john@7fff.com writes:
copy bug(id) from program '$HOME/bug/data' csv header;
It calls a script called data. I run it with psql -X -f
$HOME/bug/setup.sqlThis works for the "data" script:
#/bin/bash
echo "id"
echo "1"
echo "2"This doesn't:
#/bin/bash
echo "id"
echo "1"
psql -X -t -q -c "select 2" | xargsI can't figure out if psql is disallowed from the program referenced by
the
copy program; or if somehow standard out is getting nuked.
I think you're getting messed up by the fact that the program will
be run by the server, ie, under the postgres user's login, not your
own login. Try capturing psql's stderr to see if it's printing a
cant-login type of message.regards, tom lane
John Norman <john@7fff.com> writes:
I'm running this all locally on my laptop.
I checkout stdout and stderr: Nothing.
Hmph ... how are you starting the server exactly?
I now notice that you're running on OS X ... what version of that?
Do you have SIP enabled? SIP has been known to break stuff like
this; in particular I'm thinking of its habit of silently suppressing
key environment variables like DYLD_LIBRARY_PATH at inopportune times.
If your setup is such that your psql needs that set to start correctly,
that could be a place to look for an explanation.
A related thought is that maybe PATH in the server is different from
what you have interactively, so that it's not finding psql, or finding
the wrong copy. Specifying a full path to psql in the invoked script
would help narrow that down.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Tom,
PATH issue!
I changed the inner script to source my $HOME/.bash_profile -- all good.
THANKS! I apologize for not checking the obvious.
And also in the category of the obvious: I double-checked the PG log, and
now see line 5: psql: command not found
My bad. Sorry to use up your time.
On Sat, May 6, 2017 at 1:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
John Norman <john@7fff.com> writes:
I'm running this all locally on my laptop.
I checkout stdout and stderr: Nothing.Hmph ... how are you starting the server exactly?
I now notice that you're running on OS X ... what version of that?
Do you have SIP enabled? SIP has been known to break stuff like
this; in particular I'm thinking of its habit of silently suppressing
key environment variables like DYLD_LIBRARY_PATH at inopportune times.
If your setup is such that your psql needs that set to start correctly,
that could be a place to look for an explanation.A related thought is that maybe PATH in the server is different from
what you have interactively, so that it's not finding psql, or finding
the wrong copy. Specifying a full path to psql in the invoked script
would help narrow that down.regards, tom lane
John Norman <john@7fff.com> writes:
And also in the category of the obvious: I double-checked the PG log, and
now see line 5: psql: command not found
Yeah, I was about to mention that --- given a properly set up server,
any stderr bleats from the invoked program would be getting captured
in the postmaster log.
My bad. Sorry to use up your time.
NP
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs