Thread: Procedural Languages

There are least 10 Procedural Languages available for PostGreSQL. The one that comes with the installation is PL/pgSQL.

Which ones do you use and why?

Thanks,

John Townsend

On Thu, May 31, 2012 at 9:36 AM, John Townsend
<jtownsend@advancedformulas.com> wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?

pl/pgsql is unique in that it has 'first class queries' -- sql is
intermixed freely with procedural code and it uses the same type
system and error handling mechanisms  (although the syntax is
different).   this directly translates into direct, impactful coding
as long as you are not trying to do things that are awkward for the
language like heavy computation or string processing.

the other languages have various pros and cons but at the end are
typically going to be judged by your familiarity with the particular
language.  I have a special fondness for pl/sh for example.

merlin

 Torsdag 31. mai 2012 17.07.19 skrev Merlin Moncure :
> pl/pgsql is unique in that it has 'first class queries' -- sql is
> intermixed freely with procedural code and it uses the same type
> system and error handling mechanisms  (although the syntax is
> different).   this directly translates into direct, impactful coding
> as long as you are not trying to do things that are awkward for the
> language like heavy computation or string processing.

I'm using plpgsql for string processing all the time, mostly with regexes, and
don't find it particularly awkward. That may of course be an example of the "If
all you've got is a hammer, all problems look like nails" syndrome. But I've
never felt a need for installing another pl language.

regards, Leif

PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily to launch shell scripts from triggers, for example to update an external website when a row in a table has been inserted, deleted or updated.
--
Mike Nolan

On 5/31/12 8:36 AM, John Townsend wrote:
>   There are least 10 Procedural Languages
> <http://en.wikipedia.org/wiki/PL/pgSQL> available for PostGreSQL. The
> one that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?


I've often wondered how these "external" languages perform, figuring
that using a native language would perform better.

If I'm executing say a PL/Perl procedure, once I've executed it the first
time, can I take it the interpreter is now resident withing the PG footprint?
Is it analogous to say Apache and mod_perl?  The module/library has been loaded
and is now ready?  Or is it more along the lines of the one single PL/perl
function is now cached, and any other function executed afterward will need to
be brought in but the cached procedure is now "in ram"?  Or.. is the function
code just passed off to the system in a new process?  I.e.. how do these hooks work?

Thanks,

-ds

Michael Nolan wrote:
> PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily
> to launch shell scripts from triggers, for example to update an external
> website when a row in a table has been inserted, deleted or updated.

There is also another way to do what you describe that might be more secure.

Rather than having the DBMS launch shell scripts directly, instead use
LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an
ordinary client script listening for them, and the client script launches the
shell scripts when it gets a message.

This way, you need a persistent client script, but you don't need to invoke the
shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it
was for.

-- Darren Duncan

On Thu, 2012-05-31 at 11:52 -0600, David Salisbury wrote:
> I've often wondered how these "external" languages perform, figuring
> that using a native language would perform better.

One language isn't more "native" than another, really. SQL is a bit more
native in the sense that it might be inlined, and C is more native in
the sense that it is native code.

But PL/pgSQL just happens to be a good language when you are doing
mostly SQL with some procedural aspects, it doesn't really have an
inherent performance advantage over external PLs. There may be some
implementation quality differences, however.

> If I'm executing say a PL/Perl procedure, once I've executed it the first
> time, can I take it the interpreter is now resident withing the PG footprint?

Yes.

Regards,
    Jeff Davis

Michael Nolan wrote:
> On Thu, May 31, 2012 at 2:23 PM, Darren Duncan <darren@darrenduncan.net> wrote:
>     Michael Nolan wrote:
>
>         PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu
>         primarily to launch shell scripts from triggers, for example to
>         update an external website when a row in a table has been
>         inserted, deleted or updated.
>
>     There is also another way to do what you describe that might be more
>     secure.
>
>     Rather than having the DBMS launch shell scripts directly, instead
>     use LISTEN/NOTIFY messaging, where the trigger posts a message, and
>     you have an ordinary client script listening for them, and the
>     client script launches the shell scripts when it gets a message.
>
>     This way, you need a persistent client script, but you don't need to
>     invoke the shell in the DBMS ... or use the untrusted version of
>     PL/Perl if that's all it was for.
>
> Anybody have examples of a persistent client script?

Not a whole one.  But you can make a simple daemon in Perl, either by hand or
using some CPAN module.

The Postgres-related part though, see
http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_notifies for how the client script
talks to Postgres for this message passing.

Hopefully that'll answer the more interesting part for you.

-- Darren Duncan

On 1 June 2012 02:36, John Townsend <jtownsend@advancedformulas.com> wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.

The count looks closer to 18
http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
but I doubt some get much use (PL/LOLCODE anyone?).

PL/R opens some good functionality not offered by any other language,
and is particularly useful for statistics, GIS and other science
applications. R itself is often a wrapper to dusty FORTRAN subroutines
published in statistics journals decades ago.

-Mike

On 1 June 2012 02:36, John Townsend <jtownsend@advancedformulas.com> wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.

The count looks closer to 18
http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
but I doubt some get much use (PL/LOLCODE anyone?).

PL/R opens some good functionality not offered by any other language,
and is particularly useful for statistics, GIS and other science
applications. R itself is often a wrapper to dusty FORTRAN subroutines
published in statistics journals decades ago.

-Mike

On 5/31/2012 3:13 PM, Mike Toews wrote:

On 1 June 2012 02:36, John Townsend <jtownsend@advancedformulas.com> wrote:

There are least 10 Procedural Languages available for PostGreSQL. The one
that comes with the installation is PL/pgSQL.

The count looks closer to 18
http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
but I doubt some get much use (PL/LOLCODE anyone?).

PL/R opens some good functionality not offered by any other language,
and is particularly useful for statistics, GIS and other science
applications. R itself is often a wrapper to dusty FORTRAN subroutines
published in statistics journals decades ago.

-Mike

Thanks.

Fortran was the first computer language for me. (I guess that reveals my age :-) )

PL/pgSQL is easy to learn for me since it is pascal like. It appears this is the one to use, if you want to write maintainable code for others. (Next choice might be Perl*).

PL/pgSQL was the choice by Postbooks. Almost 1300 functions (counting triggers) are used for the open source addition. It's a good example of how to do build a nice PG app.

But I don't like the environment, namely using Qt and C++. Of course, if you have this environment already setup, and you are a good C++ programmer, then it will not be easy.

*Perl was used in SMB Ledger.

John

On Thu, May 31, 2012 at 2:13 PM, Mike Toews <mwtoews@gmail.com> wrote:
> On 1 June 2012 02:36, John Townsend <jtownsend@advancedformulas.com> wrote:
>> There are least 10 Procedural Languages available for PostGreSQL. The one
>> that comes with the installation is PL/pgSQL.
>
> The count looks closer to 18
> http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
> but I doubt some get much use (PL/LOLCODE anyone?).

And don't be surprised if you find one not listed there.

For instance, my entire production system runs entirely on pl/bf

https://github.com/mikejs/pl-bf

It's really the only logical choice for critical and complex financial
analysis work.

On Thu, 2012-05-31 at 11:23 -0700, Darren Duncan wrote:
> Michael Nolan wrote:
> > PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily
> > to launch shell scripts from triggers, for example to update an external
> > website when a row in a table has been inserted, deleted or updated.
>
> There is also another way to do what you describe that might be more secure.
>
> Rather than having the DBMS launch shell scripts directly, instead use
> LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an
> ordinary client script listening for them, and the client script launches the
> shell scripts when it gets a message.
>
> This way, you need a persistent client script, but you don't need to invoke the
> shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it
> was for.

An additional advantage is that if you issue NOTIFY with exactly the
same message many times in one transaction, the LISTENer only gets the
message once.

In other words, a big update won't case a million rebuilds of the static
pages.

Regards,
    Jeff Davis

On Fri, Jun 01, 2012 at 08:13:28AM +1200, Mike Toews wrote:
> On 1 June 2012 02:36, John Townsend <jtownsend@advancedformulas.com> wrote:
> > There are least 10 Procedural Languages available for PostGreSQL. The one
> > that comes with the installation is PL/pgSQL.
>
> The count looks closer to 18
> http://en.wikipedia.org/wiki/PostgreSQL#Procedural_languages
> but I doubt some get much use (PL/LOLCODE anyone?).

I use PL/LOLCODE... in its test suite, which runs on the rare occasion I make
changes to the language.

For whatever it's worth, PL/LOLCODE wasn't written as a language people would
use in production, but rather as a teaching tool. It taught me rather nicely,
too. :)

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

On Thu, May 31, 2012 at 7:36 AM, John Townsend
<jtownsend@advancedformulas.com> wrote:
> There are least 10 Procedural Languages available for PostGreSQL. The one
> that comes with the installation is PL/pgSQL.
>
> Which ones do you use and why?

Virtually all the time I use PL/PGSQL.  The reason is that I think
that the primary purpose of a stored procedure language is to
encapsulate database functionality inside the database.  Secondarily I
write functions in plain old SQL.  With SQL becoming more
full-featured (CTE's etc) the use cases I have for PL/PGSQL are
actually shrinking.  The major reasons I use PL/PGSQL as opposed to
SQL are actually shrinking.  The major reasons I use it today are:

1)  Exception handling and triggers
2)  There are a few cases where logic is sufficiently complex that the
procedural extensions are really helpful.
3)  Backwards-compatibility with older PostgreSQL versions (won't use
writeable CTE's for a while)
4)  named input arguments, so if there are more than a few arguments,
I will use PL/PGSQL just because I think it leads to more readable
code.

My view is that PL/PGSQL rocks.  Code written in PL/PGSQL is clear,
readable, and database-friendly.  While there may be niches for other
languages but for db stuff, it is my workhorse.

BTW, I second the point about listen/notify.  I have some sample code
there I can share.  It's not perfect by any means and would probably
cause annoyances if used as is in production but you can find it at
https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/branches/1.3/utils/notify_short/

Best Wishes,
Chris Travers

Minor correction

On Thu, May 31, 2012 at 1:57 PM, John Townsend
<jtownsend@advancedformulas.com> wrote:

> Fortran was the first computer language for me. (I guess that reveals my age
> :-) )

Fortran was my second computer language, but I hated it.
>
> PL/pgSQL is easy to learn for me since it is pascal like. It appears this is
> the one to use, if you want to write maintainable code for others. (Next
> choice might be Perl*).
>
> PL/pgSQL was the choice by Postbooks. Almost 1300 functions (counting
> triggers) are used for the open source addition. It's a good example of how
> to do build a nice PG app.
>
> But I don't like the environment, namely using Qt and C++. Of course, if you
> have this environment already setup, and you are a good C++ programmer, then
> it will not be easy.
>
> *Perl was used in SMB Ledger.

Minor correction:

LedgerSMB uses Perl in the middle layer around the database.  All our
stored procedures are in SQL or PL/PGSQL depending on complexity of
operations and inputs.

Best Wishes,
Chris Travers

On 5/31/2012 10:52 PM, Chris Travers wrote:

Minor correction

On Thu, May 31, 2012 at 1:57 PM, John Townsend
<jtownsend@advancedformulas.com> wrote:

Fortran was the first computer language for me. (I guess that reveals my age
:-) )

Fortran was my second computer language, but I hated it.

PL/pgSQL is easy to learn for me since it is pascal like. It appears this is
the one to use, if you want to write maintainable code for others. (Next
choice might be Perl*).

PL/pgSQL was the choice by Postbooks. Almost 1300 functions (counting
triggers) are used for the open source addition. It's a good example of how
to do build a nice PG app.

But I don't like the environment, namely using Qt and C++. Of course, if you
have this environment already setup, and you are a good C++ programmer, then
it will not be easy.

*Perl was used in SMB Ledger.

Minor correction:

LedgerSMB uses Perl in the middle layer around the database.  All our
stored procedures are in SQL or PL/PGSQL depending on complexity of
operations and inputs.

Best Wishes,
Chris Travers

Thanks for the correction. I realized later this might be the case after the post.

I had previously downloaded SMB for an examination, but did not restore the database. Therein I would have seen the stored procedures written in pgSQL.

I hope all continues to go well in your SMB project.

jt

On Thu, May 31, 2012 at 2:22 PM, Jeff Davis <pgsql@j-davis.com> wrote:
> On Thu, 2012-05-31 at 11:23 -0700, Darren Duncan wrote:
>> Michael Nolan wrote:
>> > PL/pgSQL and PL/perlu are the only ones I use.  I use PL/perlu primarily
>> > to launch shell scripts from triggers, for example to update an external
>> > website when a row in a table has been inserted, deleted or updated.
>>
>> There is also another way to do what you describe that might be more secure.
>>
>> Rather than having the DBMS launch shell scripts directly, instead use
>> LISTEN/NOTIFY messaging, where the trigger posts a message, and you have an
>> ordinary client script listening for them, and the client script launches the
>> shell scripts when it gets a message.
>>
>> This way, you need a persistent client script, but you don't need to invoke the
>> shell in the DBMS ... or use the untrusted version of PL/Perl if that's all it
>> was for.
>
> An additional advantage is that if you issue NOTIFY with exactly the
> same message many times in one transaction, the LISTENer only gets the
> message once.
>
The big one though is that the notifications are only sent on commit.
This being said while this is a very important advantage it is a
limitation and I can see corner cases (logging custom info even when
transactions rollback) that might be better served by untrusted
language stored procs.

Best Wishes,
Chris Travers

Am 31.05.2012 22:57, schrieb Scott Marlowe:
> And don't be surprised if you find one not listed there.
>
> For instance, my entire production system runs entirely on pl/bf
>
> https://github.com/mikejs/pl-bf
>
> It's really the only logical choice for critical and complex financial
> analysis work.
>
yeah, wonderful maintainability :D


/me should use this for next module ...

On 05/31/2012 10:36 PM, John Townsend wrote:

There are least 10 Procedural Languages available for PostGreSQL. The one that comes with the installation is PL/pgSQL.

Which ones do you use and why?

PL/PgSQL, and I avoid using anything else if at all possible. PL/PgSQL is the only language that's guaranteed to be available w/o installing additional runtimes, so it's nice and safe from the perspective of moving backups around, future-proofing, etc. It's not the fastest thing around for intensive computation, but it's very efficient when interacting heavily with the database.

I'd love to use PL/Java for some things, but PostgreSQL's multiprocess model doesn't play all that well with Java's multi-threading oriented design. The JVM startup overhead is a bit high and you can't share things between backends without expensive inter-process communication or other hacks. Because Pg doesn't re-use backends, there's a huge amount of JVM startup and shutdown cost.

I don't use PL/PythonU because there's no security model in Python, so only the "untrusted" version is available. It also requires an external runtime, and kind of sucks to install under Windows.

The new JavaScript PL looks exciting and I suspect it'll gain a *lot* of traction in future. Most JavaScript runtimes don't allow file I/O or other nasty things unless you explicitly enable it, they're fast to start, many support JIT compilation, etc etc. Given how much less modern JavaScript sucks, I expect to see tons more PL/JavaScript once Pg 9.2 starts seeing adoption.

--
Craig Ringer

Will someone please point me to a URL re PG's Java backend cost.. been
googling but no joy so far..

I'd like find out how often the JVM starts up eg based on queries or
sessions or connections or what... didn't know it was potentially
woeful. In particular re "Because Pg doesn't re-use backends, there's
a huge amount of JVM startup and shutdown cost."

TIA
Zenaan

Found key info here:
http://wiki.tada.se/index.php?title=The_choice_of_JNI
Rgds
Zenaan


On 6/8/12, Zenaan Harkness <zen@freedbms.net> wrote:
> Will someone please point me to a URL re PG's Java backend cost.. been
> googling but no joy so far..
>
> I'd like find out how often the JVM starts up eg based on queries or
> sessions or connections or what... didn't know it was potentially
> woeful. In particular re "Because Pg doesn't re-use backends, there's
> a huge amount of JVM startup and shutdown cost."
>
> TIA
> Zenaan
>

On 06/08/2012 08:40 AM, Zenaan Harkness wrote:

Will someone please point me to a URL re PG's Java backend cost.. been
googling but no joy so far..

Benchmark it. It depends on your hardware, your workload, your JVM version, your JVM vendor, your Java configuration, whether you have pljava in your  shared_preload_libraries, etc.

I'd like find out how often the JVM starts up eg based on queries or
sessions or connections or what... didn't know it was potentially
woeful. In particular re "Because Pg doesn't re-use backends, there's
a huge amount of JVM startup and shutdown cost."

"Huge" is relative; I'm just saying that it's a big cost relative to PL/PgSQL. It may well be overstating the case.

Benchmark and see. In general, PL/Java should be cheaper if you have longer-lived backends that do lots of work, and more expensive if you have lots of short lived backends that run just one or two simple PL/Java procedures then terminate.

Most PL/Java users are likely to be operating with connection pools where connections are heavily re-used. The overhead of PL/Java may be quite low in this case. Again, benchmark and see, but importantly benchmark something comparable to your workload. There's no point running a benchmark that's nothing like your workload and drawing conclusions about your workload from it.

--
Craig Ringer

On 2012-06-08, Zenaan Harkness <zen@freedbms.net> wrote:

> I'd like find out how often the JVM starts up eg based on queries or
> sessions or connections or what... didn't know it was potentially
> woeful. In particular re "Because Pg doesn't re-use backends, there's
> a huge amount of JVM startup and shutdown cost."

A "backend" is what is at the other end of the socket which mediates
the  "connection" - there's a 1:1 mapping.

--
⚂⚃ 100% natural