Python can open file, PL/Python can't

Question

Connected to mydb in PostgreSQL:

mydb=# CREATE FUNCTION file_test () RETURNS text AS $$
if open('mydir/myfile.xsl'): return 'success' 
$$ LANGUAGE plpythonu;
CREATE FUNCTION
mydb=# SELECT file_test();
ERROR:  IOError: [Errno 2] No such file or directory: 'mydir/myfile.xsl'
CONTEXT:  Traceback (most recent call last):
  PL/Python function "file_test", line 2, in <module>
    if open('mydir/myfile.xsl'): return 'success'
PL/Python function "file_test"

Over to Python:

>>> if open('mydir/myfile.xsl'): print 'success'
... 
success
>>>

An absolute path didn't seem to help PL/Python. What I'd like to do is use Postgres's query_to_xml() and run an XSLT transformation on the return. But to do that I'll need to read the xsl file...

That was my first thought. It didn't work. (Editing above accordingly.) — Franklin Einspruch, Sep 22 '16 at 16:18
Well, it's very likely either the path to the file isn't correct, or your PostgreSQL process doesn't have rights to read this file. — Gerrat, Sep 22 '16 at 16:27
You can also add ``plpy.notice('Full path: ' + os.path.abspath('mydir/myfile.xsl'))`` in order to see what file exactly is the server trying to access. — hruske, Sep 22 '16 at 16:33
Bingo. `NOTICE: /var/lib/postgresql/9.5/main/mydir/myfile.xsl` — Franklin Einspruch, Sep 22 '16 at 16:59

score 1 · Answer 1 · answered Sep 22 '16 at 17:08

At @hruske's suggestion I used plpy.notice(os.path.abspath('mydir/myfile.xsl')) to see how PL/Python was trying to resolve the path. It was /var/lib/postgresql/9.5/main/mydir/myfile.xsl, which was obviously not what I had in mind.

The absolute path worked after all. Copying the file to a location that was easier to type as an absolute path fixed the "problem."

For further reading I recommend Soko Morinaga's Novice to Master: An Ongoing Lesson in the Extent of My Own Stupidity.

Python can open file, PL/Python can't

1 Answers1