Support Questions
Find answers, ask questions, and share your expertise

Executescript can't find postgres driver in jar specified in 'Modules Directory'


I'm on NiFi 1.11.4 trying to write a simple groovy script which can output the results of a postgres SQL query to an attribute (something not currently supported by ExecuteSql).  I've got a postgres Driver jar (confirmed working outside of NiFi) but when I specify it in the "Modules Directory" option of ExecuteScript, I get this:

classNotFoundException: org.postgresql.Driver


Here is my ExecuteScript setup:



And here is the script body:




import groovy.sql.Sql

def url = 'jdbc:postgresql://mydatabaseUrl:5432/mydb'
def user = 'myuser'
def password = 'mypassword'
def driver = 'org.postgresql.Driver'
//def sql = Sql.newInstance(url, user, password, driver)

Sql.withInstance(url, user, password, driver) { sql ->
    def first = sql.firstRow('SELECT * from myschema.mytablelimit 1;')
    log.error(first) // easy way to print directly to the NiFi GUI





I'm very sure that the postgresql-42.2.5.jar I'm using is good.  I've tested it using the exact same script but invoking it with this and got a response, all worked fine.


groovy -cp /data/apps/nifi/conf/postgresql-42.2.5.jar postgrestest.groovy


All evidence seems to indicate that the 'Modules Directory' isn't properly adding the jar to my classpath.  I tried to work around this by loading the jar directly in the script using

this.getClass().classLoader.rootLoader.addURL(new File("file.jar").toURL())
but it looks like "this" means something different in ExecuteScript, which makes sense but is a rabbit hole I'm not trying to go down today.  I also tried this blog post to reuse an existing Controller Service, but only got timeouts (Controller Service is tested and working on other ExecuteSql processors).

I've also tested ExecuteScript Modules Directory with other jars, and those seem to work fine.  This might be a Postgres specific problem