1

I would like to use YAML in some plpython code, but YAML is not included in the python3 extension for PostgreSQL.

My 'import yaml' gets an error that it cannot find yaml.

On my regular Python3 install I did 'pip3 install yaml' which worked fine.

How can I get yaml installed into PostgreSQL?

Thanks.

Some more info for clarification:

Here is the start of a function defined in PG:

-- Default audit trigger
create or replace function sys_audit() returns trigger language plpython3u AS $$
  from sys import path
  path.append('/usr/local/lib/ez-python-library/PostgreSQL/bin');

  from datetime import datetime
  from CommonRowFunctions import getPkValue, getRowValue, getRowChanges

  keyVal    = ''
  modData   = 'unknown'
...

The module 'CommonRowFunctions' tries to use YAML to configure logging. This module lives in my python library (external to PostgreSQL). This all works if I use a properties file for the log config, but using a dictionary is the preferred method and YAML makes that very easy.

  • PostgreSQL Version 10. – Crashmeister Mar 19 '18 at 19:28
1

This is a problem specific to EnterpriseDB. PostgreSQL does not ship Python, it merely ships a contrib module which your distribution is set to build against the system's Python. In the case of Windows, there is no Python for them to build against because there is no distribution. You can get any version of Python when you download "python" (more properly cpython). So instead, EnterpriseDB bundles a version of Python they provide as a distribution of PostgreSQL. Previously, that were bundling a statically linked Active State Python, now they're bundling cPython 3.3 (which they can dynamically link in). This is all from the docs here.

To configure your system to use EnterpriseDB Python though, you need to first follow a few steps. From the docs on configuration

After installing Language Pack, you must set the following variables: set PYTHONHOME=C:\edb\languagepack-9.6\x64\Python-3.3

Use the following commands to add Python, Perl and Tcl to your search path:

set PATH= C:\edb\LanguagePack-9.6\x64\Python-3.3\bin: C:\edb\LanguagePack-9.6\x64\Perl-5.20\bin: C:\edb\LanguagePack-9.6\x64\Tcl-8.5\bin:%PATH%

After setting the system-specific steps required to configure Language Pack on Windows, restart the Advanced Server database server; see Section 5 for detailed information about restarting the server.

There are similar instructions provided for Linux and OSX

After you configure that pip and easy_install should work fine.

  • I'm on OSX and use Python 3.6 as my main Python distrib. I would far rather change PostgreSQL config to use the system version of Python than support multiple versions. So getting rid of the EDB install would be a winner. I could also look at installing PostgreSQL from another distrib, one that would use PYTHONPATH. How are others doing this on OSX? – Crashmeister Jun 28 '18 at 14:17
  • Actually, after revisiting the PostgreSQL website, I think that EDB is probably the easiest and best way to install/maintain Postgres on a Mac. I will temp change the PATH var to run PIP when needed. – Crashmeister Jun 28 '18 at 14:27
  • @Crashmeister with OSX, I believe those users install brew, and then they use it for everything: if you install python and PostgreSQL with brew, everything just should work -- I think. – Evan Carroll Jun 28 '18 at 16:12
0

Make sure you're using plpythonu and not plpython. Then install yaml, or pyYaml and make sure it works. Then simply add the import statement.

  • I'm using plpython3u (version 3) to be exact. Where do you install yaml? I have already installed it into my python-3 setup and it works fine if I run it from terminal. Functions called from PostgreSQL get the error. – Crashmeister Mar 19 '18 at 19:26
0

I eventually did a manual install:

Copy folders 'yaml' and 'PyYAML-3.12-py3.6.egg-info'

from.: /Library/Frameworks/Python.framework/Versions/3.6/lib/python3.6/site-packages

to...: /Library/edb/languagepack-10/Python-3.4/lib/python3.4/site-packages

  • This sounds like an OSX error for plpythonu to find system-installed python libraries – Evan Carroll Jun 25 '18 at 16:30
  • Postgres uses its own install of Python. That's why it does not find the system-installed ones. The 'edb' folder is created when using the EnterpriseDB installer for Postgres. There may be a way to tell Postgres to use the System install, but I do not know how. – Crashmeister Jun 26 '18 at 18:29
  • Are you using EnterpriseDB or PostreSQL? – Evan Carroll Jun 26 '18 at 18:33
  • Also what operating system are you using? – Evan Carroll Jun 26 '18 at 18:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.