A PostgreSQL Extension that allows you to access any other datasource
1–10 of 16 posts
Re: A PostgreSQL Extension that allows you to access any other datasource
#2Multicorn developer here, I'll be glad to answer any question.
Re: A PostgreSQL Extension that allows you to access any other datasource
#3Multicorn developer here, I'll be glad to answer any question.
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?
Re: A PostgreSQL Extension that allows you to access any other datasource
#4Multicorn developer here, I'll be glad to answer any question.
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 postgresql are converted to an IMAP filter, allowing for server side filtering.
The required columns are also provided, so if you don't need the email payload, the foreign data wrapper will not fetch it.
For joins, it will depend on the execution plan. There is still plenty of work on parsing the postgresql execution plan into something more useful, but the current set of "optimizations" is sufficient for our main use cases.
Re: A PostgreSQL Extension that allows you to access any other datasource
#5Multicorn developer here, I'll be glad to answer any question.
Crap, I'm working on something exactly like this :D Was about to upload it to Github just today, but still wanted to fix a few things.
Yours looks quite more finished, one thing that would be very useful is handling Python to Postgres type conversion, like PL/Python does. After that, the next step is caching I/O functions for the duration of the scan, which PL/Python also does.
Nice to see that the idea made sense for more than one person, hope Multicorn will rock!
Re: A PostgreSQL Extension that allows you to access any other datasource
#6Multicorn developer here, I'll be glad to answer any question.
Crap, I'm working on something exactly like this :D Was about to upload it to Github just today, but still wanted to fix a few things. Yours looks quite more finished, one thing that would be very useful is handling Python to Postgres type conversion, like PL/Python does. After that, the next step is caching I/O functions for the duration of the scan, which PL/Python also does. Nice to see that the idea made sense fo…
Sorry to beat you at it ;)
We currently have some very rudimentary python to postgres type conversion, but this area still need a lot of improvements.
You should release your code, I'm sure you have a wide range of ideas worth merging into Multicorn !
Re: A PostgreSQL Extension that allows you to access any other datasource
#7Earlier quoted context omitted.
Crap, I'm working on something exactly like this :D Was about to upload it to Github just today, but still wanted to fix a few things. Yours looks quite more finished, one thing that would be very useful is handling Python to Postgres type conversion, like PL/Python does. After that, the next step is caching I/O functions for the duration of the scan, which PL/Python also does. Nice to see that the idea made sense fo…
Sorry to beat you at it ;) We currently have some very rudimentary python to postgres type conversion, but this area still need a lot of improvements. You should release your code, I'm sure you have a wide range of ideas worth merging into Multicorn !
Actually, the ideal thing to happen would be for PL/Python to somehow become a shared library that provides the typecasting routines, but I'm afraid that's a very big task and difficult to get accepted upstream. It's sad that PL/Python already includes all the code you need to transform Python lists to Postgres arrays, bools to PG bools and so on and every project like this one has to duplicate all that logic.
BTW: in my approach I opted for passing the entire Qual node as a string (using nodeToString) and my plan was to write a Python parser for that format that takes a string and returns a Python object. Not sure how hard that will turn out.
Re: A PostgreSQL Extension that allows you to access any other datasource
#8Earlier quoted context omitted.
Sorry to beat you at it ;) We currently have some very rudimentary python to postgres type conversion, but this area still need a lot of improvements. You should release your code, I'm sure you have a wide range of ideas worth merging into Multicorn !
Actually, the ideal thing to happen would be for PL/Python to somehow become a shared library that provides the typecasting routines, but I'm afraid that's a very big task and difficult to get accepted upstream. It's sad that PL/Python already includes all the code you need to transform Python lists to Postgres arrays, bools to PG bools and so on and every project like this one has to duplicate all that logic. BTW: i…
I totally agree with you regarding PL/Python.
I may try to ask upstream what they think about it.
Re: A PostgreSQL Extension that allows you to access any other datasource
#9I 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.
Re: A PostgreSQL Extension that allows you to access any other datasource
#10I 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 ?