Thread: [BUGS] BUG #14645: Can't use psql from within program used by "copy ... fromprogram" 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

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

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