accessing functions in the postgres public schema using sqlalchemy.func

[Brian Cherinka]

Hi,

What's the best way to access functions that live in schema='public' in the postgres databases?  Some postgresql extensions install functions in the public schema that I would like accessible via sqlachemy.func, however I get an error when attempting to call them.   Am I missing something when setting up my Base Classes?  Maybe regarding the `search_path`?  I'm defining my models and tables with a Declarative Base.  My default_schema_name is set to 'public'.   I've read through this page, http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path but it's not clear where I should be setting my search_path to ensure it includes the public schema.  

As an example, I have a test "add" function in the 'public' schema , which crashes presumably because it cannot find the function definition

session.query(func.add(2,4)).all()

ProgrammingError: (psycopg2.ProgrammingError) function add(integer, integer) does not exist
LINE 1: SELECT add(2, 4) AS add_1
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
 [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3': 4, 'add_2': 2}]

 and a test "newadd" function defined in an explicit schema called "functions".  This seems to automatically get reflected and mapped.  And works perfectly.  

session.query(func.newadd(2,4)).all()

[(6)]

One solution is to install the postgres extension into the functions schema, but this kind of breaks the usage within postgres itself.  I have to always explicity set search_path='functions' in order to use them.  So it's not ideal.  Ideally, I'd like sqlachemy.func to understand functions that live either in the "functions" or "public" schema.  Any ideas on how to fix this? 

Cheers, Brian

[Mike Bayer]

On Wed, Aug 1, 2018 at 2:18 PM, Brian Cherinka <havo...@gmail.com> wrote:
> Hi,
>
> What's the best way to access functions that live in schema='public' in the
> postgres databases? Some postgresql extensions install functions in the
> public schema that I would like accessible via sqlachemy.func, however I get
> an error when attempting to call them. Am I missing something when setting
> up my Base Classes? Maybe regarding the `search_path`? I'm defining my
> models and tables with a Declarative Base. My default_schema_name is set to
> 'public'. I've read through this page,
> http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path
> but it's not clear where I should be setting my search_path to ensure it
> includes the public schema.

the search_path should automatically include the "public" schema by default.

>
> As an example, I have a test "add" function in the 'public' schema , which
> crashes presumably because it cannot find the function definition
>
> session.query(func.add(2,4)).all()
>
> ProgrammingError: (psycopg2.ProgrammingError) function add(integer, integer)
> does not exist
> LINE 1: SELECT add(2, 4) AS add_1

> ^
> HINT: No function matches the given name and argument types. You might need
> to add explicit type casts.
> [SQL: 'SELECT add(%(add_2)s, %(add_3)s) AS add_1'] [parameters: {'add_3':
> 4, 'add_2': 2}]

if you log in via psql, can you run the "add()" function? you need
to get things working there first. check the search_path, etc.

>
>
> and a test "newadd" function defined in an explicit schema called
> "functions". This seems to automatically get reflected and mapped. And
> works perfectly.
>
> session.query(func.newadd(2,4)).all()

The terms "reflected" and "mapped" are both SQLAlchemy terms, and in
that regard, neither has anything to do with a SQL function. Your
result would indicate that Postgresql's search path for functions
includes this "functions" schema. I'm not aware if this is some
kind of default behavior in Postgresql or something but again, using
psql by itself to see what's happening is the first thing to work
with.

>
> [(6)]
>
> One solution is to install the postgres extension into the functions schema,
> but this kind of breaks the usage within postgres itself. I have to always
> explicity set search_path='functions' in order to use them.

If you are doing this, that would be why your "newadd" function in the
"functions" schema works...

> So it's not
> ideal. Ideally, I'd like sqlachemy.func to understand functions that live
> either in the "functions" or "public" schema. Any ideas on how to fix this?

this is all stuff you should ask on stackoverflow. I can't reproduce
your issue:

$ psql -U scott test
psql (10.4)
Type "help" for help.

test=# select current_schema();
current_schema
----------------
public
(1 row)

# create a function, goes into the "public" schema by default

test=# CREATE FUNCTION add(integer, integer) RETURNS integer
test-# AS 'select $1 + $2;'
test-# LANGUAGE SQL
test-# IMMUTABLE
test-# RETURNS NULL ON NULL INPUT;
CREATE FUNCTION

# works

test=# select add(1, 2);
add
-----
3
(1 row)

test=# show search_path;
search_path
-----------------
"$user", public
(1 row)

# now remove "public" from search path

test=# set search_path="%user";
SET

# function is gone
test=# select add(1, 2);
ERROR: function add(integer, integer) does not exist
LINE 1: select add(1, 2);

^
HINT: No function matches the given name and argument types. You
might need to add explicit type casts.

# put it back
test=# set search_path="%user",public;
SET


# works again

test=# select add(1, 2);
add
-----
3
(1 row)

test=#





>
> Cheers, Brian
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Brian Cherinka]

Yeah everything is working in psql just fine.   

select current_schema();
 current_schema
----------------
 public
(1 row)

Time: 1.226 ms
show search_path;

   search_path
-----------------
 "$user", public
(1 row)

select add(2,4);

 add

-----

   6

(1 row)

 

But you jogged my memory and think I found the problem.  I dug back through my code and found this buried in it

def clearSearchPathCallback(dbapi_con, connection_record):
    '''
    When creating relationships across schema, SQLAlchemy
    has problems when you explicitly declare the schema in
    ModelClasses and it is found in search_path.


    The solution is to set the search_path to "$user" for
    the life of any connection to the database. Since there
    is no (or shouldn't be!) schema with the same name
    as the user, this effectively makes it blank.


    This callback function is called for every database connection.


    For the full details of this issue, see:
    http://groups.google.com/group/sqlalchemy/browse_thread/thread/88b5cc5c12246220


    dbapi_con - type: psycopg2._psycopg.connection
    connection_record - type: sqlalchemy.pool._ConnectionRecord
    '''
    cursor = dbapi_con.cursor()
    cursor.execute('SET search_path TO "$user",functions')
    dbapi_con.commit()


listen(sqlalchemy.pool.Pool, 'connect', clearSearchPathCallback)

which shows my setting the search_path to exclude public.  Changing that line 'SET search_path TO "$user",functions, public' fixes everything SQLA.  It's been a while since I've looked at my code that sets up the DatabaseConnection.  I must have had an original reason to exclude public but it doesn't seem relevant anymore.  Thanks for your help.