How to test Pl/Python PostgreSQL procedures with Travis CI?

Question

I'm trying to set up CI for some PL/Python PostgreSQL procedures in Travis CI.

I've tried several ways:
1) With the legacy infrastructure I've tried to just assume, that PL/Python is already installed, but it had not succeed:

The command "psql -U postgres -c 'CREATE EXTENSION plpythonu;'" exited with 1.
0.01s$ psql -U postgres -d test -c 'CREATE LANGUAGE plpythonu;'
ERROR:  could not access file "$libdir/plpython2": No such file or directory  

2) Have tried to add sudo apt-get update && sudo apt-get -y install postgresql-plpython-9.4 commands in the beginning. And it was also failed, because this command initiated replacement of PostgresSQL 9.4, that comes already installed in the Travis environment.

Travis build.

3) Also tried to use container-based infrastructure with this lines in the config:

addons:
  postgresql: "9.4"
  apt:
    packages:
      - postgresql-plpython-9.4

No success too.

What is the good way to test PL/Python procedure in Travis CI?

Flatiron

Why do you think it's failing for option 2? Travis docs are clear that updating packages before the install is the right thing to do when installing dependencies... – Neptune

@PeterBrittain It conflicts somehow with existing PostgreSQL. See update, Ive attached the log of such build. – Flatiron

Looks like the upgrade is refusing to go through because your old version of Postgres is still running... Have you tried stopping Postgres before the attempted upgrade? – Neptune

Answer 1

I was able to get the python-tempo build working with the following .travis.yml:

sudo: required
language: python
before_install:
  - sudo apt-get -qq update
  - sudo /etc/init.d/postgresql stop
  - sudo apt-get install -y postgresql-9.4
  - sudo apt-get install -y postgresql-contrib-9.4 postgresql-plpython-9.4
  - sudo -u postgres createdb test
  - sudo -u postgres createlang plpython2u test
  - sudo pip install jinja2
script:
  - >
      sudo -u postgres psql -d test -c 'CREATE OR REPLACE FUNCTION py_test()
                                   RETURNS void LANGUAGE plpython2u AS $$
                                     import jinja2
                                   $$;'
  - sudo -u postgres psql -d test -c 'SELECT py_test();'

Your legacy configuration attempts had a variety of issues including not stopping the existing PostgreSQL 9.1 instance before installing 9.4 and not specifying the plpython language properly. I believe some commands were also not being run as the correct user. All of the issues are resolved by the above configuration. There may be ways in which this configuration can be improved, but I stopped once I got it working.

The container-based configuration won't work because postgresql-plpython-9.4 is not currently in the whitelist of pre-approved packages. However, postgresql-plpython-9.5 is, so if you want to migrate to a container-based configuration, you can either try following the package approval process for postgresql-plpython-9.4 or wait for the GA release of PostgreSQL 9.5 and try migrating then.

Sayers

If use container-based infrastructure, how would you stop PostgrteSQL before installing postgresql-plpython-9.4 or postgresql-plpython-9.5 packages? – Flatiron

@GillBates I didn't try doing your setup with a container-based configuration because I saw that postgresql-plpython-9.4 was not a pre-approved package. I would guess that if it was pre-approved and that you used the configuration from option 3, that Travis would be smart enough to know when to stop and start PostgreSQL. If it's not smart enough, I would consider that a bug with Travis, and I wouldn't know how to stop PostgreSQL in that scenario, because even the current Travis docs use sudo for stopping PostgreSQL: docs.travis-ci.com/user/database-setup/#PostgreSQL – Sayers

Answer 2

Converting my previous comments to an answer now that it has been confirmed...

As documented in the Travis docs, the right way to install this is to update your dependencies at the before_install stage (much like option 2 in your list).

The only problem appears to be that you did not stop Postgres before the upgrade.

Neptune

Answer 3

I don't know the details exactly, but, if you put the files in the right place, you can call it from the procedure itself

import fileName.className

or

import methodName from fileName.className

Edit: I looked it up, just put it in the same directory as the program you're running (cmd, idle, ect.) and call it, or put it in a folder and add the folder name to the code

eg.

import folder/fileName.ClassName

Splendor