Обсуждение: PL/Pgsql or PL/Python?
Hello, I was wondering whether it is worth for me to learn PL/Pgsql, given the support of Python in Postgresql. The latter (Python) is the only programming language I've learned voluntarily in my whole life, i.e. without being obliged to do so, you know. ;-) So, what things can be done in PL/Pgsql which can't be done in PL/Python (and vice-versa)? And, how do both compare in terms of runtime efficiency (speed/memory)? Apart from typical "ERP-like" work, the applications I am planning to use Postgresql for involve storage and (numeric) processing of quite significant amounts of (binary) measurement data. Specifically, "hacking" long streams of binary data fed into a database in batch mode into individual OPC-conform triplets, among others. So I thought the numeric module and iterator feature in Python would be quite useful... TIA, Best regards Wolfgang Keller
This is a very vague question. I'd suggest it's worth learning pl/pgsql simply because it's designed to solve a set of problems common in database systems. The design makes it reasonably efficient for implementing solutions in this area. The problem you're (just barely) describing below is something I'd probably try to address with pure sql stored procedures. Remember, the database already has numeric support capabilities. If you stick to pure sql procedures, not only do you remain portable, but you may be able to gain some wins in the planner. I also suggest that if you're attempting to iterate in Python, then you might not be understanding how an integrated procedural language is intended to be used. Drew Wolfgang Keller wrote: > Hello, > > I was wondering whether it is worth for me to learn PL/Pgsql, given the > support of Python in Postgresql. The latter (Python) is the only > programming language I've learned voluntarily in my whole life, i.e. > without being obliged to do so, you know. ;-) > > So, what things can be done in PL/Pgsql which can't be done in PL/Python > (and vice-versa)? And, how do both compare in terms of runtime efficiency > (speed/memory)? > > Apart from typical "ERP-like" work, the applications I am planning to use > Postgresql for involve storage and (numeric) processing of quite > significant amounts of (binary) measurement data. Specifically, "hacking" > long streams of binary data fed into a database in batch mode into > individual OPC-conform triplets, among others. So I thought the numeric > module and iterator feature in Python would be quite useful... > > TIA, > > Best regards > > Wolfgang Keller > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Wolfgang Keller wrote: | I was wondering whether it is worth for me to learn PL/Pgsql, given the | support of Python in Postgresql. The latter (Python) is the only | programming language I've learned voluntarily in my whole life, i.e. | without being obliged to do so, you know. ;-) | | So, what things can be done in PL/Pgsql which can't be done in PL/Python | (and vice-versa)? And, how do both compare in terms of runtime efficiency | (speed/memory)? | | Apart from typical "ERP-like" work, the applications I am planning to use | Postgresql for involve storage and (numeric) processing of quite | significant amounts of (binary) measurement data. Specifically, "hacking" | long streams of binary data fed into a database in batch mode into | individual OPC-conform triplets, among others. So I thought the numeric | module and iterator feature in Python would be quite useful... There is one limitation in particular to to using PL/Python instead of PL/pgSQL: ~ - If a user has access to create PL/Python stored procedures, ~ they have full access to the host the PostgreSQL is running on ~ as the user it is running as. ~ - The API for executing database queries or calling other server side ~ procedures is fairly primative. However, there are also advantages to using PL/Python (or PL/Perl for that matter): ~ - You are less likely to be the only person around who can maintain ~ the code. ~ - You can share the same code on the server side and on the client ~ side (so the same Python code can be used to enforce your column ~ constraints as is used in your form validation, for example). ~ - You can test your code outside of PostgreSQL using the standard ~ Python test harnesses. ~ - You have access to the Python standard library and any installed ~ libraries, so you can avoid reinventing a lot of wheels. I don't think anyone could tell you which would end up being faster or use less memory - I expect it would depend on whether the savings using numeric or other libraries outweighs the overheads involved in translating data between Python and PostgreSQL formats. - -- Stuart Bishop <stuart.bishop@canonical.com> http://www.canonical.com/ Canonical Ltd. http://www.ubuntulinux.com/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQFCKFlHAfqZj7rGN0oRAtTAAJ0R6x1/k2S/kzOzeWgwb42Yq98HvgCfYprE qUWC+h19qGn6gK3SsgbmpM8= =xzjX -----END PGP SIGNATURE-----