Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A PostgreSQL Extension that allows you to access any other datasource

multicorn.org

Post reply on HN
Print view View original

11–16 of 16 posts

Re: A PostgreSQL Extension that allows you to access any other datasource

#11

Post by danmaz74 »

rdunklau wrote: post #4
danmaz74 wrote: post #3

Earlier quoted context omitted.

This looks interesting, thanks for sharing. But what kind of performance is it possible to get with complex queries on these external sources? Does the PG server need to load all the data in memory from the source to do filters, joins and sorts?

Thank you for your interest! The postgresql plan is parsed, and passed as a list of "quals", objects representing simple filters. As an implementer, you don't HAVE to enforce those, since postgresql will recheck them for you anyway, but they can be quite handy. For example, you can look at the imap foreign data wrapper ( https://github.com/Kozea/Multicorn/blob/master/python/multic... ) to see how the conditions from…

Thank you for the explanation. I don't have an immediate need for this, but it's an interesting approach. Good luck!

Re: A PostgreSQL Extension that allows you to access any other datasource

#12

Post by muyuu »

rdunklau wrote: post #10
muyuu wrote: post #9

I have well over 10K messages in gmail. Will this work? I was thinking in downloading the whole thing via POP3, creating a Unix mailbox and indexing that.

It depends on what your use case is. If you have well targeted queries (eg, with filters on "From" or "Subject" headers, and only query the message headers (not the payload) you can have a pretty good performance. What is your use case, exactly ?

Most of my filtering would be based on the contents of the subject header. I have a site that sends me copies of particular transactions, and these few thousand messages are the ones I'd like to query. Ideally I'd like to have them downloaded for statistical processing.

Re: A PostgreSQL Extension that allows you to access any other datasource

#13

Post by leopard »

I try to install this extension on Ubuntu 11.10. First of all error in instalation -

gcc -g -O2 -fPIC -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -lpythonpython2: -fpic -L/usr/lib -Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed -Wl,--as-needed -lpythonpython2: -shared -o src/multicorn.so src/multicorn.o /usr/bin/ld: cannot find -lpythonpython2: /usr/bin/ld: cannot find -lpythonpython2: collect2: ld returned 1 exit status make: * [src/multicorn.so] Error 1 rm src/multicorn.o ERROR: command returned 2

This is because Ubuntu have - python and python2.7 bin files. I fixed this by creating symlink on python.

After instalation another problem:

$ psql psql (9.1.1) Type "help" for help.

leo=# CREATE EXTENSION multicorn; ERROR: could not load library "/usr/lib/postgresql/9.1/lib/multicorn.so": /usr/lib/postgresql/9.1/lib/multicorn.so: undefined symbol: _Py_NoneStruct

All tested on specialy created for this system: $ python -V Python 2.7.2+ $ psql -V psql (PostgreSQL) 9.1.1 contains support for command-line editing

On this my tests end.

Re: A PostgreSQL Extension that allows you to access any other datasource

#14

Post by Jach »

I was at OSCON Data this year that held a talk about the new foreign data connectors piece, it's great that Postgres has them now. Want twitter data? Make an interface to translate twitter data to rows, it's that easy! LucidDB has had foreign data connectors for a while but you have to use Java at least for part of it. I like the generator-style of yielding list-rows shown here: https://github.com/Kozea/Multicorn/blob/master/python/multic...

Re: A PostgreSQL Extension that allows you to access any other datasource

#15

Post by rdunklau »

leopard wrote: post #13

I try to install this extension on Ubuntu 11.10. First of all error in instalation - gcc -g -O2 -fPIC -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -lpythonpython2: -fpic -L/usr/lib -Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed -Wl,--as-needed -lpythonpython2: -shared -o src/multicorn.so src/multicorn.o /usr/b…

Thank you for this report. I'll try to make it work on Ubuntu, expect the documentation to be updated in the next few days.

PS: if you're the 'leopard' who requested a redmine account, it should be activated now, feel free to report it there.

Re: A PostgreSQL Extension that allows you to access any other datasource

#16

Post by leopard »

rdunklau wrote: post #15
leopard wrote: post #13

I try to install this extension on Ubuntu 11.10. First of all error in instalation - gcc -g -O2 -fPIC -fPIC -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -g -lpythonpython2: -fpic -L/usr/lib -Wl,-Bsymbolic-functions -Wl,--as-needed -Wl,--as-needed -Wl,--as-needed -lpythonpython2: -shared -o src/multicorn.so src/multicorn.o /usr/b…

Thank you for this report. I'll try to make it work on Ubuntu, expect the documentation to be updated in the next few days. PS: if you're the 'leopard' who requested a redmine account, it should be activated now, feel free to report it there.

Ok, I already added this to tracker. I am very interesting in this extension, its amazing.
Post reply on HN