I downloaded and installed the latest version of Multicorn on PostgreSQL 9.2. I get the following type of error message when trying to select from any Multicorn FDW:

ERROR: unrecognized node type: -72672464

Multicorn looks awesome. It would be nice to have some more documentation around it.

In particular : install documentation, more examples using the existing wrappers, some documentation on the base functions to implement to write/contribute a new wrapper (insert, delete, update, etc.)

Possibly using https://github.com/datastax/python-driver

I made a very slight modification to multicorn's included csvfdw.py example FDW. I define the following table, mostly containing integer values.

CREATE FOREIGN TABLE variants.coverage_4_cols (
    CHROM character varying,
    START_POS integer,
    END_POS integer,
    READ_COUNT integer,
    FILE character varying
) server multicorn_text ;

When I do a select statement, I can't search for READ_COUNT = 0 in my where clause. For some reason these 0 values in the underlying TSV file are being interpreted as null's or something by PostgreSQL. They show up as blank positions in the table when I query the whole thing.

Is there a way to escape or properly send a 0 value into a postgresql table using multicorn so that it gets in there as a 0 value rather than a null?

Thanks for your help!

Hi,

I downloaded the latest version from GIT (1.0.0), in the changelog it says that it supports 2.6:

"0.0.9
- Break backwards compatibility: the column definition to the foreign data
wrapper constructor is now a dictionary of column names to their types.
- Add python2.6 compatibility"

But it does not work at make install:

[root@ Multicorn]# make install
Python version is 2.6
./preflight-check.sh
/bin/mkdir -p '/usr/pgsql-9.3/lib'
/bin/mkdir -p '/usr/pgsql-9.3/share/extension'
/bin/mkdir -p '/usr/pgsql-9.3/share/extension'
/bin/mkdir -p '/usr/share/doc/pgsql/extension'
/usr/bin/install -c -m 755 multicorn.so '/usr/pgsql-9.3/lib/multicorn.so'
cp ./setup.py ./setup--1.0.0.py
sed -i -e "s/VERSION/1.0.0-dev/g" ./setup--1.0.0.py
/usr/bin/python ./setup--1.0.0.py install
Traceback (most recent call last):
File "./setup--1.0.0.py", line 12, in
include_dirs = [get_pg_config(d) for d in ("includedir", "pkgincludedir", "includedir-server")]
File "./setup--1.0.0.py", line 6, in get_pg_config
r = subprocess.check_output([pg_config, '--%s' % kind])
AttributeError: 'module' object has no attribute 'check_output'
make: *** [python_code] Error 1

It's because subprocess does not contain the check_output function:

import subprocess
dir(subprocess)
['CalledProcessError', 'MAXFD', 'PIPE', 'Popen', 'STDOUT', 'TimeoutExpired', '_PIPE_BUF', 'all', 'builtins', 'doc', 'file', 'name', 'package', '_active', '_cleanup', '_demo_posix', '_demo_windows', '_eintr_retry_call', '_has_poll', 'call', 'check_call', 'errno', 'fcntl', 'gc', 'list2cmdline', 'mswindows', 'os', 'pickle', 'select', 'signal', 'sys', 'time', 'traceback', 'types']

I'm trying to install it on Red Hat Enterprise Linux Server release 6.5 (Santiago)

It's not clear what license, if any, this software is using. Can you add a LICENSE file?

It should be noted that you need to have the following things installed prior to using/building this project:

1. pg_config needs to be in your path. for postgres 9.3 its in /usr/pgsql-9.3/bin
2. python 2.7 is required and if you have to build and install it you have to make it your default python
3. python-devel is required

The multicorn page says the qual value should be available, with the defined example below
"
Let's suppose we write the following query:

SELECT * from constanttable where test = 'test 2' and test2 like '%3%';

The method execute would be called with the following quals:

[Qual('test', '=', 'test 2'), Qual('test', '~~', '3')]

"

In my example requesting qual.value always returns 0.

• here is the SQL request im using

select lat from test where lat=45;

• Here is the import and the code thats causing the failure to return value

from multicorn import Qual
...
 for qual in quals:
            log_to_postgres(
                "qualField:"+ str(qual.field_name) 
                + " qualOperator:" + str(qual.operator) 
                + " qualValue:" +str(qual.value)
                , WARNING)

• here is the output with no mention of '45'

qualField: lat    qualOperator: =    qualValue: 0

Thanks,
Andy

I'm trying to build a FDW for ISAM files. I was hoping to receive the list of quals and use those to jump directly to the target records. But I just tried a join and it resulted in a table scan. This might be a dead end for me.

Here's an example:

    select r.patient_id, r.doc_id from rx r, doctor d
        where r.doc_id = d.doc_id and 
            r.pat_id = 6;

I got quals=[patient_id = 6.0] for the rx table. I can use that to jump to the matching record using an index on patient_id. But once the rx record is returned the doctor table is read with quals=[]. All I can do is read the entire table. That will take too long to be practical for my app.

This is not completely surprising. Postgres has no knowledge of the doctor table, the number of rows or which fields are indexed. Is there anything I can do to get the criteria passed in quals?

John

Edit: I guess one solution is avoid joins in SQL. Have the application perform nested queries and assemble the results.

i'm unable to create the extension:

dev_test=# create extension multicorn;
The connection to the server was lost. Attempting reset: Succeeded.

psql 9.2.2

The 1.0.0beta on PGXN uses Sed in a non-portable way.

sed "s/__VERSION__/1.0.0beta-dev/g" ./setup--1.0.0beta.py -i
...
sed: -i: No such file or directory
make: *** [python_code] Error 1
ERROR: command returned 2: make PG_CONFIG=/usr/local/bin/pg_config install

This bug was observed on Mac OS X.

I installed multicorn without any issues, and creating the extension works, but with all attempts to create a server, I get this error:
ERROR: Error in python: SystemError
DETAIL: null argument to internal routine

This happens no matter what class I attempt to use as the wrapper.

running python -c "import multicorn" works fine.
running sudo -u postgres python -c "import multicorn" works fine.

I just want to verify some details before I implement these methods. My underlying data is ISAM files with indexes.

get_rel_size():
Do I return the number of rows that I expect my execute() method to yield or the numbers of rows that Postgres will return after applying the full search criteria? In get_rel_size() I plan to look at the quals (if any), determine which indexes I can use based on the quals, calculate the number of rows I'll yield for each index and return the lowest number, the number associated with the “best” index. It's similar to the steps I'll follow in execute().

My assumption is that I should use an index whenever possible to help reduce the number of records I’ll yield and to minimize the number of reads I’ll perform to find these candidate records. I also assume there’s not much benefit in applying every qual to the records. Postgres can do that filtering about as efficiently as I can. And I assume in some cases the full search criteria is not represented in the quals so in those cases I have no choice but to let Postgres perform the final filtering.

Will execute() always receive the exact same quals as get_rel_size()? If so then I'll save my index selection from get_rel_size() for later use in execute(). In fact, I probably should cache my decisions since the app will be repeating a limited set of queries.

get_path_keys():
I plan to return one row count estimate for each index in my ISAM file. In the case of compound indexes I'll return one estimate for each combination of fields. So in the case of an index of three fields such as [a,b,c] I’ll return row counts for [a], [b], [a,b], [c], [a,c], [b,c] and [a,b,c]. I’m wondering if these estimates on the combinations will be misleading though. I think I can return accurate estimates but the numbers won’t reflect the amount of reads required to locate the records. For example, if only field “a” appears in the search criteria then I can use the index to go directly to the target records. But it will be more complicated for searches by “c” alone. The index will help me skip some records but in the worst case I will have to read all records to find matches on “c” alone. Is that a valid concern?

I created a branch (link) witch code that recognizes a new option on the table creation statement, "write_lock_mode". Please take a look at it. I can submit a pull request if this is a desirable feature.

The current behavior remains the default one; if the option is not specified, the code behaves similarly to how it works right now. If, otherwise, one passes a valid lock mode, the (local) foreign table is locked accordingly. This is useful for remotes that have no natural mechanism for concurrent writes (for example, writing to a file).

The code locks the table when it enters a modify block and keeps the lock until the transaction commits or rolls back.

$ git clone git://github.com/Kozea/Multicorn.git
Cloning into 'Multicorn'...
remote: Counting objects: 14232, done.
remote: Compressing objects: 100% (7257/7257), done.
remote: Total 14232 (delta 6690), reused 14147 (delta 6609)
Receiving objects: 100% (14232/14232), 5.50 MiB | 2.10 MiB/s, done.
Resolving deltas: 100% (6690/6690), done.
$ cd Multicorn
$ make
Python version is 2.7
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I. -I. -I/home/mudd/pkgs/postgresql-9.3.0.install/include/server -I/home/mudd/pkgs/postgresql-9.3.0.install/include/internal -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -c -o src/errors.o src/errors.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I. -I. -I/home/mudd/pkgs/postgresql-9.3.0.install/include/server -I/home/mudd/pkgs/postgresql-9.3.0.install/include/internal -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -c -o src/python.o src/python.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I. -I. -I/home/mudd/pkgs/postgresql-9.3.0.install/include/server -I/home/mudd/pkgs/postgresql-9.3.0.install/include/internal -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -c -o src/query.o src/query.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I. -I. -I/home/mudd/pkgs/postgresql-9.3.0.install/include/server -I/home/mudd/pkgs/postgresql-9.3.0.install/include/internal -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -I/home/mudd/pkgs/Python-2.7.5.install/include/python2.7 -c -o src/multicorn.o src/multicorn.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -shared -o multicorn.so src/errors.o src/python.o src/query.o src/multicorn.o -L/home/mudd/pkgs/postgresql-9.3.0.install/lib -Wl,--as-needed -Wl,-rpath,'/home/mudd/pkgs/postgresql-9.3.0.install/lib',--enable-new-dtags -lpthread -ldl -lutil -lm -lpython2.7
/usr/bin/ld: cannot find -lpython2.7
collect2: error: ld returned 1 exit status
make: *** [multicorn.so] Error 1
$

It would be cool if Multicorn supports writeable FDWs.

Is there a way to install multicorn on CentOS 6.5? Default version of python is 2.6 and I am aware that is no longer supported. So far I've taken these steps:

Installed python 2.7 alongside the default 2.6 version.
Compiled multicorn with changing all references/paths in makefile to python 2.7.
Python related files seem to end up in the right folder:

/usr/local/lib/python2.7/dist-packages/multicorn-1.0.2-py2.7-linux-x86_64.egg/multicorn

Creating extension statement works:

CREATE EXTENSION multicorn;

Creating server fails:

CREATE SERVER csv_srv foreign data wrapper multicorn options (wrapper 'multicorn.csvfdw.CsvFdw');
ERROR: Error in python: ImportError
DETAIL: No module named multicorn

I'm thinking this is because Postgres is referencing default python version 2.6? Is there any way to overcome this problem? My Postgres version is 9.3.3.

Running this simple use case leads to a segfault in the postgres process:

CREATE DATABASE test;
\c test
CREATE EXTENSION multicorn;
CREATE SERVER multicorn_fs FOREIGN DATA WRAPPER multicorn options ( wrapper 'multicorn.fsfdw.FilesystemFdw' );
CREATE FOREIGN TABLE test ( c1 varchar, c2 varchar, filename varchar, data varchar ) server multicorn_fs options (root_dir '/tmp', filename_column 'filename',  content_column 'data',  pattern '{c1}.dbfile');
-- This line is mandatory for generating the segfault
select * from test;
ALTER FOREIGN TABLE test OPTIONS (set pattern '{c2}.dbfile');
-- This line seqfaults python
select * from test;

Apr 29 17:52:11 zi kernel: postgres[19872]: segfault at ffffffffffffffff ip 00007fc54cfc4ba0 sp 00007fff0cdd1e30 error 5 in libpython3.4m.so.1.0[7fc54cf1e000+212000]                          
Apr 29 17:53:17 zi postgres[2507]: LOG:  processus serveur (PID 19872) a été arrêté par le signal 11 : Segmentation fault                                                                      
Apr 29 17:53:17 zi postgres[2507]: DÉTAIL:  Le processus qui a échoué exécutait : select * from test;

Tested with python 2.7 python 3.3 and python 3.4 with postgresql 9.3.4.

I created a csv fdw table following the example. I'm able to access the table and it works well. So I had another person connect to my db from a separate PC using pgadmin. They can't see the fdw table. Even thought they are using my user account.

If I create a regular table then that appears in pgAdmin. But not the fdw table from a remote PC.

Any suggestions?

I assume this has nothing to do with multicorn. I'm just posting this here so I can share my experience in case it helps others.

Seems there are some issues with linking Python in Xcode 5 (https://developer.apple.com/library/ios/technotes/tn2328/_index.html).

gcc -mmacosx-version-min=10.7 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv  -bundle -multiply_defined suppress -o multicorn.so src/errors.o src/python.o src/query.o src/multicorn.o -L/Applications/Postgres.app/Contents/Versions/9.3/lib -L/Applications/Postgres.app/Contents/Versions/9.3/lib  -L/Applications/Postgres.app/Contents/Versions/9.3/lib -Wl,-dead_strip_dylibs   -F/System/Library/Frameworks -framework Python  
ld: framework not found Python
clang: error: linker command failed with exit code 1 (use -v to see invocation)

Using PYTHON_OVERRIDE = python also errors:

gcc -mmacosx-version-min=10.7 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv  -bundle -multiply_defined suppress -o multicorn.so src/errors.o src/python.o src/query.o src/multicorn.o -L/Applications/Postgres.app/Contents/Versions/9.3/lib -L/Applications/Postgres.app/Contents/Versions/9.3/lib  -L/Applications/Postgres.app/Contents/Versions/9.3/lib -Wl,-dead_strip_dylibs   -ldl -framework CoreFoundation -lpython2.7  
ld: library not found for -lpython2.7

Determined that $(PY_LDFLAGS) is missing from :

SHLIB_LINK = $(PY_LIBSPEC) $(PY_LDFLAGS) $(PY_ADDITIONAL_LIBS) $(filter -lintl,$(LIBS)) 

Which results in lots of missing symbol errors:
[....]

"_untransformRelOptions", referenced from:
      _multicorn_GetUserMapping in python.o
      _multicorn_validator in multicorn.o
ld: symbol(s) not found for architecture x86_64

Determined that SHLIB_LINK is clobbering PGXS info. So changing SHLIB_LINK to:

SHLIB_LINK += $(PY_LIBSPEC) $(PY_LDFLAGS) $(PY_ADDITIONAL_LIBS) $(filter -lintl,$(LIBS)) 

This seems to resolve the issue. I am not sure how to patch this specifically for OSX yet. I wanted to get this into the project issues in case someone else has suggestions or stumbles onto the same problem.

If I create a foreign table, then drop a column, the function pythonSequenceToTuple still expects a sequence with the original number of columns.

Here's the test that's done at line 886 of src/python.c:

if (PySequence_Size(p_value) != state->attinmeta->tupdesc->natts)

The issue is, natts doesn't change after an attribute is dropped. Instead you have to look at the tupdesc->attrs[i] and check the attrisdropped field.

hi all im getting the following error when trying to use multicorn on Mac OSX, ive seen a few posts about this issue but i was hoping for some help. see below.

postgres=# create extension multicorn;
CREATE EXTENSION
postgres=# CREATE SERVER csv_srv foreign data wrapper multicorn options (
postgres(#     wrapper 'multicorn.csvfdw.CsvFdw'
postgres(# );
ERROR:  Error in python: ImportError
DETAIL:  No module named multicorn

Failed install using PyPI (and zip) on OSX 10.7, Python 2.7, Postgres 9.2

quasiben@my_machine:~$ pgxn install multicorn --testing
INFO: best version: multicorn 1.0.0-beta1
INFO: saving /var/folders/zt/011jk2fx1379w1cc_tnf5vdh0000gn/T/tmpEwpWoj/multicorn-1.0.0-beta1.zip
INFO: unpacking: /var/folders/zt/011jk2fx1379w1cc_tnf5vdh0000gn/T/tmpEwpWoj/multicorn-1.0.0-beta1.zip
INFO: building extension
cp sql/multicorn.sql sql/multicorn--1.0.0beta.sql
cc -I/usr/local/Cellar/ossp-uuid/1.6.2/include -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -lpython2.7 -I/usr/include/python2.7 -I/System/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I. -I/usr/local/Cellar/postgresql/9.2.2/include/server -I/usr/local/Cellar/postgresql/9.2.2/include/internal -I/usr/include/libxml2 -I. -I. -I/usr/local/Cellar/postgresql/9.2.2/include/server -I/usr/local/Cellar/postgresql/9.2.2/include/internal -I/usr/include/libxml2 -c -o src/multicorn.o src/multicorn.c
clang: warning: -lpython2.7: 'linker' input unused when '-c' is present
src/multicorn.c:545:1: warning: no previous prototype for function 'multicorn_init_typeoids' [-Wmissing-prototypes]
multicorn_init_typeoids(MulticornExecState * state)
^
src/multicorn.c:948:1: warning: no previous prototype for function 'multicorn_pyobject_to_datum' [-Wmissing-prototypes]
multicorn_pyobject_to_datum(PyObject *object, TupleDesc tupdesc, MulticornExecState * state, int attnum)
^
2 warnings generated.
cc -I/usr/local/Cellar/ossp-uuid/1.6.2/include -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -lpython2.7 -bundle -multiply_defined suppress -o multicorn.so src/multicorn.o -L/usr/local/Cellar/postgresql/9.2.2/lib -L/usr/local/Cellar/ossp-uuid/1.6.2/lib -Wl,-dead_strip_dylibs -lpython2.7 -lpython2.7 -bundle_loader /usr/local/Cellar/postgresql/9.2.2/bin/postgres
INFO: installing extension
/bin/sh /usr/local/Cellar/postgresql/9.2.2/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/9.2.2/lib'
/bin/sh /usr/local/Cellar/postgresql/9.2.2/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/9.2.2/share/postgresql/extension'
/bin/sh /usr/local/Cellar/postgresql/9.2.2/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/9.2.2/share/postgresql/extension'
/bin/sh /usr/local/Cellar/postgresql/9.2.2/lib/pgxs/src/makefiles/../../config/install-sh -c -d '/usr/local/Cellar/postgresql/9.2.2/share/doc/postgresql/extension'
cp ./setup.py ./setup--1.0.0beta.py
sed "s/VERSION/1.0.0beta-dev/g" ./setup--1.0.0beta.py -i
from setuptools import setup, find_packages, Extension

multicorn_utils_module = Extension('multicorn._utils',
include_dirs=['/usr/include/postgresql/', '/usr/include/postgresql/server', '/usr/include/postgresql/internal/'],
extra_compile_args = ['-shared'],
sources=['src/utils.c'])

setup(
name='multicorn',
version='1.0.0beta1',
author='Kozea',
license='Postgresql',
package_dir={'': 'python'},
packages=['multicorn', 'multicorn.fsfdw'],
ext_modules = [multicorn_utils_module]
)
sed: -i: No such file or directory
make: *** [python_code] Error 1
ERROR: command returned 2: make PG_CONFIG=/usr/local/bin/pg_config install

Hi!

I am not sure if it is something that I am doing wrong or missing some parts, but it has been now two days and I can't figure out what is wrong.

I am trying to create a foreign server:

CREATE SERVER test_mysql
foreign data wrapper multicorn
options (
wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw'
 );

And i keep getting this error output:

ERROR:  Error in python: ImportError
DETAIL:  No module named multicorn

I have installed multicorn as per the documentation and I am sure site-packages are in the path.

Just to test my assumptions, I've wrote this:

create function pytest()
returns text
as $$
import sys
import multicorn
result = multicorn.__file__
return result
$$ language 'plpythonu';

select pytest();

and it runs fine, with the following output:

---------------------------------------------------------------------------------------------------------------------------------------------------------
 /usr/local/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/multicorn-1.0.0_dev-py2.7-macosx-10.8-x86_64.egg/multicorn/__init__.pyc
(1 row)

Is there something I am missing?

EDIT:
i did ran "CREATE EXTENSION multicorn" and I did restart the postgres server.

EDIT2:
python version:

python 2.7.6 (default, Nov 12 2013, 10:37:13)
[GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.2.75)] on darwin

postgres version: 9.3.1

OS: OS X 10.8.5

python and postgres installed with brew.

pip freeze:

MySQL-python==1.2.4
SQLAlchemy==0.8.3
git-remote-helpers==0.1.0
multicorn==1.0.0-dev
pgxnclient==1.2.1
psycopg2==2.5.1
stevedore==0.12
virtualenv==1.10.1
virtualenv-clone==0.2.4
virtualenvwrapper==4.1.1
wsgiref==0.1.2

Error in usage IMAP wrapper:

# CREATE SERVER multicorn_imap FOREIGN DATA WRAPPER multicorn options (wrapper 'multicorn.imapfdw.ImapFdw');
ERROR:  Error in python: join
DETAIL:  No module named imapclient
# CREATE SERVER csv_srv foreign data wrapper multicorn options (
#     wrapper 'multicorn.csvfdw.CsvFdw'
# );
CREATE SERVER
# CREATE SERVER multicorn_imap FOREIGN DATA WRAPPER multicorn options (wrapper 'multicorn.imapfdw.ImapFdw');
ERROR:  Error in python: join
DETAIL:  No module named imapclient

ubuntu 12.04
psql 9.3.0
python 2.7.3
multicorn 1.0.0beta1

I'm getting an error trying to update from 1.0.0 to 1.0.2

alter extension multicorn update;

ERROR: extension "multicorn" has no update path from version "1.0.0" to version "1.0.2"

Since the code only pushes down quals that compare to T_Const, T_Param, and T_Var, it doesn't evaluate now() (a T_FuncExpr) and push down as a constant. I don't know enough about PostgreSQL development to know if this would be easy or hard. I'm happy to contribute this feature if you can give me a starting point.

It would be nice to be able to deploy multicorn on debian servers using debian packages.

Is it possible to build this with Jython?

The reason being: I'd like to use the SQL Alchemy FDW with a JDBC driver.
As far as I know: the only way to use JDBC w/ SQLAlchemy is with zxJDBC which is only supported by jython.

It looks like your build requires Python2.7, so the relevant Jython version would be 2.7b1

It looks like array generated via a query is passed incorrectly in Qual:

testdb=# select * from mytable where id = any (array(select mytable_id from acl where acl.member_id = 112));
NOTICE: PK COL : id
NOTICE: get_path_keys()
NOTICE: [id = ANY(140273338805376)]

whereas a constant array gets passed correctly

testdb=# select * from mytable where id = any (array[1,2,3,4,5]);
NOTICE: PK COL : id
NOTICE: get_path_keys()
NOTICE: [id = ANY([u'1', u'2', u'3', u'4', u'5'])]

I was was trying to compare a literal to a VARCHAR(31) field in my table. I wasn't getting a match so I tried switching data types. When I tried the Equal operator and CHAR(31) type I get a signal 11: Segmentation fault. This is not a serious problem for me, just something I noticed.

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst'" | psql rx30
 patkey | namefirst | namelast |         adrsadd1         | adrscity | adrsstate | adrszip 
--------+-----------+----------+--------------------------+----------+-----------+---------
  36505 | First     | Lst      | 123 Xxxxxxxxxxxxx AVENUE | Xxxxx    | NJ        | 12345
(1 row)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 = '123 Xxxxxxxxxxxxx AVE         '" | psql rx30
 patkey | namefirst | namelast | adrsadd1 | adrscity | adrsstate | adrszip 
--------+-----------+----------+----------+----------+-----------+---------
(0 rows)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 = '123 Xxxxxxxxxxxxx AVE         '::VARCHAR(31)" | psql rx30
 patkey | namefirst | namelast | adrsadd1 | adrscity | adrsstate | adrszip 
--------+-----------+----------+----------+----------+-----------+---------
(0 rows)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 = '123 Xxxxxxxxxxxxx AVE          '::VARCHAR(31)" | psql rx30
 patkey | namefirst | namelast | adrsadd1 | adrscity | adrsstate | adrszip 
--------+-----------+----------+----------+----------+-----------+---------
(0 rows)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 = '123 Xxxxxxxxxxxxx AVE'::VARCHAR(31)" | psql rx30
 patkey | namefirst | namelast | adrsadd1 | adrscity | adrsstate | adrszip 
--------+-----------+----------+----------+----------+-----------+---------
(0 rows)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 = '123 Xxxxxxxxxxxxx AVE'::CHAR(31)" | psql rx30
connection to server was lost
$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 like '123 Xxxxxxxxxxxxx AVE'::CHAR(31)" | psql rx30
 patkey | namefirst | namelast | adrsadd1 | adrscity | adrsstate | adrszip 
--------+-----------+----------+----------+----------+-----------+---------
(0 rows)

$ echo "select patkey, namefirst, namelast, adrsadd1, adrscity, adrsstate, adrszip from patient where namefirst = 'First' and namelast = 'Lst' and adrsadd1 like '123 Xxxxxxxxxxxxx AVE%'::CHAR(31)" | psql rx30
 patkey | namefirst | namelast |         adrsadd1         | adrscity | adrsstate | adrszip 
--------+-----------+----------+--------------------------+----------+-----------+---------
  36505 | First     | Lst      | 123 Xxxxxxxxxxxxx AVENUE | Xxxxx    | NJ        | 12345
(1 row)

$ 

Possibly due to an incorrect call to Py_DECREF in multicornExecForeignInsert (update and delete may also be affected), the refcount of None decreases by 6 references on each insert, eventually causing the above error.

Running log_to_postgres(sys.getrefcount(None)) indicates the decreasing refcount.

Any particular reason it's not on PGXN? I'd love to do just pgxn install multicorn.

Could the limit clause be exposed to Multicorn FDW implementations? This would be handy in cases where the foreign data source also has a limit-like functionality.

CREATE SERVER mssql foreign data wrapper multicorn options (
wrapper 'multicorn.alchemyfdw.SqlAlchemyFdw'
);

BŁĄD: Error in python: ImportError
DETAIL: No module named alchemyfdw
********** Błąd **********

BŁĄD: Error in python: ImportError
Stan SQL: XX000

Szczegóły: No module named alchemyfdw

Am I missing something?
how to get rid of this error?

so ive noticed a potential issue relating to "request columns" vs "foreign data table schema". i generate a very large foreign data table (more than 15 fields). rather than tying to return all the columns i want to be able to just return the requested columns, when i try and do that i get yelled at....if i return all the columns in the foreign data table it works file.

the request i want to perform,

select temp from "sampleFDT";

i.e my foreign data table (sampleFDT) might have the following fields

"time" timestamp,
"temp" real,
"density" real,
"salinity" real,
"pressure" real,
"conductivity" real,

now in my code i was to just loop through and return the request to reduce to overhead of getting the data and return a massive data block.

i.e looping though the requested columns in my FDW -> this causes issue....

data=[]
for field in req_columns: 
    addDataToReturnBlock(data)

#data is in correct row format 
return data

what i have to do instead is loop through all column and build the entire table
self.column = all table column fields

data=[]
for field in self.columns: 
    addDataToReturnBlock(data)

#data is in correct row format 
return data

now im wondering because of the way that FDW are used, if the desired method is even possible?

Cheers,

I switched to the dev version of Multicorn to get the fix for null detections. I'm able to create the server and tables but get the following when I try to query a table.

TRAP: FailedAssertion("!(((list) == ((List *) ((void *)0)) || ((((const Node*)((list)))->type) == T_OidList)))", File: "list.c", Line: 169)
LOG:  server process (PID 706) was terminated by signal 6: Aborted
DETAIL:  Failed process was running: select * from doctor;

I can query the tables with the beta version of Multicorn but there's the problem detecting nulls. Any advice? I'm using Postgres 9.3.1 and Python 2.7.6

John

Hi,
I try to install and run Multicorn on a Mac:
OS: 10.7.5
Pyhton: 2.7.1
make and make install work both fine. But as soon as I try to import the library in a python shell I get the foloowing error:

import multicorn.alchemyfdw.SqlAlchemyFdw
Traceback (most recent call last):
File "", line 1, in
ImportError: dlopen(./multicorn.so, 2): Symbol not found: _CacheMemoryContext
Referenced from: /Users/babak/Downloads/Multicorn/multicorn.so
Expected in: /System/Library/Frameworks/Python.framework/Versions/2.7/Resources/Python.app/Contents/MacOS/Python
in /Users/babak/Downloads/Multicorn/multicorn.so

Did I make something terribly wrong? I would appriciate any suggestions which lead to resolve this issue.

Thanks
B.

Using the lower-than-CQL-level pycassa driver? https://github.com/pycassa/pycassa

Hi again,

I am not sure if this is a multicorn issue or the python mysqldb issue, but when i try and run queries of type count (select count(*) from table_name) against a foreign mysql database, I always get back errors as:

ERROR:  Error in python: ProgrammingError
DETAIL:  (ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1") 'SELECT ' ()

does this ring any bells? anybody had similar errors?

Would it not be better if p_targets_set were a Python sequence/list?

The order of arguments passed to my execute method does not match that given in the table definition, which makes it impossible to filter columns in some circumstances (for example, in CSV-like wrappers).

Thanks for your help!

I'm running the latest Multicorn with Postgres 9.3.1 compiled without asserts. My 'smallint' and 'integer' type fields now correctly display zeroes in the output from SELECT statements instead of NULLs.

$ echo 'select deletedflag from doctor where deletedflag = 0 limit 1;' | psql rx30
 deletedflag
-------------
           0
(1 row)
$

But the zero in the WHERE clause is converted to NULL in the quals list.

quals=[deletedflag = None]

John

It looks like I'm getting corrupted quals when using JOINs. Here's my query below.

The quals passed to me for the Blob_Memo are [btype = 1, btype = 1, keykey = 50585.0]. That should be [btype = 1, keystoreid=102148, keykey = 50585.0]. It appears that the keystoreid qual was overlaid with a copy of 'btype = 1'.

I tried working around this by explicitly adding 'b.keystoreid=102148' to the WHERE clause. That worked, sort of. I still got two copies of 'btype = 1' but at least 'keystoreid=102148' appeared as a forth qual. But then I found calls where two of the quals had been duplicated/overlaid. So I've given up on that approach.

At this point we're trying to decide if we should rewrite our queries in order to eliminate JOINs.

SELECT DISTINCT
        r.RxNbr,
        g.DEAClass,
        e.Initials,
        p.NameLast,
        d.NameLast,
        b.Memo,
        l.Planname
    FROM
        Rx r
        INNER JOIN Patient p   ON r.PatNbrStoreID =        p.PatStoreID AND      r.PatNbrKey = p.PatKey
        INNER JOIN Doctor d    ON r.DocNbrStoreID =        d.DocStoreID AND      r.DocNbrKey = d.DocKey
        INNER JOIN Drug g      ON r.DispensedDrugStoreID = g.DrugNbrStoreID AND  r.DispensedDrugKey = g.DrugNbrKey
        INNER JOIN Employee e  ON r.RPHStoreID =           e.EmpNbrStoreID AND   r.RPHKey = e.EmpNbrKey
        INNER JOIN Blob_Memo b ON r.DirLinkStoreId =       b.keystoreid AND      r.DirLinkKey = b.keykey AND b.btype = 1
        INNER JOIN PLANS l     ON r.Plan1StoreID =         l.PlanCodeStoreID AND r.Plan1Key = PlanCodeKey
    WHERE
        r.StoreNbr = 102146 AND
        r.RxNbr = 885892 AND
        r.RfNbr = 1

CentOS 6.3. It comes with Python 2.6, so I have compiled and installed Python 2.7 from source, and modified Multicorn makefile to point to the newly compiled Python executable. Python is in /usr/local/bin as CentOS doesnt like stock Python install to be modified.

Creation of Multicorn FDW fails as it cant import dependent Python modules. For example sqlalchemyfdw fails with

ERROR: Error in python: ImportError
DETAIL: No module named sqlalchemy

I have installed SQLAlchemy, and Python CLI imports it fine. Why cant Multicorn find it?

If there's a Python exception in my fdw code then it is nicely logged in the Postgres log. But it looks like an exception from a nested query is not logged.

This example results in two calls to my fdw code. First for the Rx table and then for the Doctor table. The exception happened in the Doctor query but it wasn't logged so that created some confusion for me.

SELECT r.RxNbr, r.DocNbrKey
FROM
    Rx r, Doctor d
WHERE   
    r.DocNbrStoreID = d.DocStoreID AND r.DocNbrKey = d.DocKey and 
    r.StoreNbr = 102146 AND 
    r.RxNbr = 885892 AND 
    r.RfNbr = 0 

This is low priority. Bottom line is my fdw code failed.

Bit fields don't seem to be supported as foreign table fields due to the fact that they always have a size specified which seems to break the driver. Even if you define the table with a bit field and don't include the size it is still added as part of the table's definition afterwards. So a field defined as:

bitfield bit

will result in a foreign table with a definition of:

bitfield bit(1)

and this breaks the driver. If you convert the bit field to a Boolean then you will not be able to write to the foreign table, as the conversion from Boolean to bit is not understand and you get an incorrect value error, but you can get read access to the foreign data.

Tested on SQL Server 2008R2 with PostgreSQL 9.3.3

I tried using a MONEY type field in the WHERE clause but got this in the postgres log.

LOG:  server process (PID 9505) was terminated by signal 11: Segmentation fault
DETAIL:  Failed process was running: SELECT * from reclaim where potential = '12.34'::money;
LOG:  terminating any other active server processes

I verified that my FDW init() method was called and executed normally but there was no call to get_rel_size(), get_path_keys() or execute().

Can anyone test if this is a general problem?

John

I was wondering how more complex WHERE clauses would be handled. I see they're not. That's understandable.

What's the best way to suppress the WARNING and DETAIL messages for this specific situation? Is there a way to log the original SQL statement rather than the DETAIL message?

Can you tell me more about what is and is not supported?

Thanks,
John

$ echo 'select * from shipping WHERE residential = 1 or delivery = 1;' | psql rx30_db
WARNING:  unsupported expression for extractClauseFrom
DETAIL:  {BOOLEXPR :boolop or :args ({OPEXPR :opno 532 :opfuncid 158 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 19 :vartype 21 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 19 :location 29} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 43 :constvalue 4 [ 1 0 0 0 ]}) :location 41} {OPEXPR :opno 532 :opfuncid 158 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 6 :vartype 21 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 48} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 59 :constvalue 4 [ 1 0 0 0 ]}) :location 57}) :location -1}
WARNING:  unsupported expression for extractClauseFrom
DETAIL:  {BOOLEXPR :boolop or :args ({OPEXPR :opno 532 :opfuncid 158 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 19 :vartype 21 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 19 :location 29} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 43 :constvalue 4 [ 1 0 0 0 ]}) :location 41} {OPEXPR :opno 532 :opfuncid 158 :opresulttype 16 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 6 :vartype 21 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattno 6 :location 48} {CONST :consttype 23 :consttypmod -1 :constcollid 0 :constlen 4 :constbyval true :constisnull false :location 59 :constvalue 4 [ 1 0 0 0 ]}) :location 57}) :location -1}
 ctdel | reference | pat_storeid | pat_key | packaged | delivery | lastupdate | name | add1 | add2 | city | state | zip | phone | email_present | email | iteminfo | weight | residential | yes | no | spare
-------+-----------+-------------+---------+----------+----------+------------+------+------+------+------+-------+-----+-------+---------------+-------+----------+--------+-------------+-----+----+-------
(0 rows)

$ 

If I update a foreign table's options, the fdw instance associated with the table does not get updated, so the old options stick around until the server is restarted. Will add a test case to show this when I get a chance.