Thread: PL/Python
Hi,
I'm toying with the idea of making the Pyrseas utilities a true
PostgreSQL extension. Currently, the utilities (dbtoyaml and yamltodb)
rely on a library of Python functions: over 16 modules and 4000+ lines
of code. The programs would remain as Python command line front-ends,
but there would be a PL/Python function, i.e., to_yaml() or to_map(),
that would return a YAML / JSON map, like this:
{'schema public': {'type greeting': {'attributes': [{'how': 'text'},
{'who': 'text'}]}, 'function pymax(a integer, b integer)': {'language':
'plpythonu', 'source': 'return a if a > b else b', 'returns':
'integer'}, 'description': 'standard public schema'}, 'language
plpythonu': {}}
(Looks nicer this way, in YAML):
language plpythonu: {}
schema public:
description: standard public schema
function pymax(a integer, b integer):
language: plpythonu
returns: integer
source: return a if a > b else b
type greeting:
attributes:
- how: text
- who: text
Although there are no discussions or examples in the documentation, I've
determined that PL/Python supports Python new style classes like class
Test(object), and import of standard modules.
Now, in order for to_yaml/to_map to do its work, it needs to import 15
modules, e.g.,
from pyrseas.dbobject.language import LanguageDict
I'd be interested to know if anyone has tried something similar (in
terms of scope) and if so, how they tackled it.
The other function, diff_yaml() or diff_map(), is a bit trickier because
it requires reading in a YAML spec file, like the one above, and then
comparing it to the internal version of to_yaml/to_map, in order to
output SQL DDL statements. The command line tool could read the spec
file locally and send it off as one big text argument. Again, I'm
interested in similar experiences if any.
Recently in -hackers there was a discussion about splitting pg_dump.c
and some suggested breaking it up IIUC by catalog object type (much like
Pyrseas does) and providing, for example, access to functions that
output SQL DDL for a given object. A side effect of turning Pyrseas
into an extension could be --with some extra work-- to provide such a
set of functions. I'd like to know if there is any interest in this
capability.
Joe
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
body p { margin-bottom: 0cm; margin-top: 0pt; }
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#ffffff"
text="#000000">
On 09/30/2011 05:10 AM, Joe Abbate wrote:
<blockquote cite="mid:4E852515.5010801@freedomcircle.com"
type="cite">
Although there are no discussions or examples in the documentation, I've
determined that PL/Python supports Python new style classes like class
Test(object), and import of standard modules.
Now, in order for to_yaml/to_map to do its work, it needs to import 15
modules, e.g.,
from pyrseas.dbobject.language import LanguageDict
I'd be interested to know if anyone has tried something similar (in
terms of scope) and if so, how they tackled it.
The other function, diff_yaml() or diff_map(), is a bit trickier because
it requires reading in a YAML spec file, like the one above, and then
comparing it to the internal version of to_yaml/to_map, in order to
output SQL DDL statements. The command line tool could read the spec
file locally and send it off as one big text argument. Again, I'm
interested in similar experiences if any.
If I understand plpython correctly, it uses the python installed on
the machine. In other words, plpythonu doesn't support the new style
classes, it depends on what version of python is installed.
In the same way, if you have libraries installed on your machine,
plpythonu has access to them as well. So if someone installed
pyrseas, he would be able to use all the functions from within his
function.
IMO, there is no need for an extension here, all you need to do is
have an sql file containing your 2 plpythonu functions that can be
run into the database.
Sim
Hi Sim, On 10/02/2011 08:02 AM, Sim Zacks wrote: > If I understand plpython correctly, it uses the python installed on the > machine. In other words, plpythonu doesn't support the new style > classes, it depends on what version of python is installed. Well, Python has had new style classes since 2.2 (December 2001). PG 8.2 release notes says it supports Python 2.5 and the 9.0 notes show support was added for Python 3. Unless someone is running Python 2.1 or earlier, it seems new style classes are available. > In the same way, if you have libraries installed on your machine, > plpythonu has access to them as well. So if someone installed pyrseas, > he would be able to use all the functions from within his function. Yes, that seems to be the case, assuming the path to the library is visible in (or added to) PYTHONPATH. > IMO, there is no need for an extension here, all you need to do is have > an sql file containing your 2 plpythonu functions that can be run into > the database. Maybe I'm misunderstanding something, but isn't such a sql file an extension or is 95% of the way there? Pyrseas is already distributed via PGXN, but like some other PGXN "extensions" (dbi-link?), it doesn't actually create functions in the database. Its two utilities run entirely as external programs. If the Pyrseas functions were added via an .sql file to a database, EXTENSION or not, they would be available for use by non-Pyrseas programs, e.g., pgAdmin could call diff_map() to compare database objects, Perl scripts or even a plain psql script could call to_yaml(). And these would not depend on psycopg2, which currently *is* a Pyrseas dependency (it would still be necessary for access to the command line utilities). Joe
> Maybe I'm misunderstanding something, but isn't such a sql file an > extension or is 95% of the way there? Pyrseas is already distributed > via PGXN, but like some other PGXN "extensions" (dbi-link?), it doesn't > actually create functions in the database. Its two utilities run > entirely as external programs. If the Pyrseas functions were added via > an .sql file to a database, EXTENSION or not, they would be available > for use by non-Pyrseas programs, e.g., pgAdmin could call diff_map() to > compare database objects, Perl scripts or even a plain psql script could > call to_yaml(). And these would not depend on psycopg2, which currently > *is* a Pyrseas dependency (it would still be necessary for access to the > command line utilities). > > Joe I don't know if there is an official definition, but an extension is generally a compiled program that is accessed by the SQL. It must be compiled with the postgresql headers and have the magic number defined. The compiled file must then be put into the lib directory with the other postgresql extensions. A user defined function does extend postgresql, but it doesn't have any of the complications.
On 10/03/2011 04:14 AM, Sim Zacks wrote: > I don't know if there is an official definition, but an extension is > generally a compiled program that is accessed by the SQL. It must be > compiled with the postgresql headers and have the magic number defined. > The compiled file must then be put into the lib directory with the other > postgresql extensions. > > A user defined function does extend postgresql, but it doesn't have any > of the complications. As of 9.1 (and I *was* thinking in terms of 9.1 when I wrote "EXTENSION"), there is a definition in: http://www.postgresql.org/docs/9.1/static/sql-createextension.html http://www.postgresql.org/docs/9.1/static/extend-extensions.html You'll notice that in the latter there is an example of a SQL-only extension. That's why I thinking of Pyrseas as some plpython functions as a true EXTENSION. Joe