Thread: A thread about SPs -- mentioning PostgreSQL

A thread about SPs -- mentioning PostgreSQL

From
Devrim GUNDUZ
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

There is a new thread at /. "Stored Procedures - Good or Bad?", and it
mentions about PostgreSQL among enterprise databases:

http://ask.slashdot.org/askslashdot/04/07/30/2324206.shtml?tid=198&tid=156&tid=4&tid=1&tid=8&tid=218

Regards,
- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
            http://www.tdmsoft.com
            http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFBCzqbtl86P3SPfQ4RAh6CAJwNMpN82gGIAV3QTbulUScxzOMOuQCeP0Hp
F08f8uEgNWFFmgipSrrXEaA=
=s4ky
-----END PGP SIGNATURE-----


Re: A thread about SPs -- mentioning PostgreSQL

From
Alvaro Herrera
Date:
On Sat, Jul 31, 2004 at 09:22:18AM +0300, Devrim GUNDUZ wrote:

> There is a new thread at /. "Stored Procedures - Good or Bad?", and it
> mentions about PostgreSQL among enterprise databases:
>
> http://ask.slashdot.org/askslashdot/04/07/30/2324206.shtml?tid=198&tid=156&tid=4&tid=1&tid=8&tid=218

Very interesting.  A gem:

    I used to work for an ecommerce (java/ejb) website that needed to ditch
    it's oracle licensing (we were running out of money) and had to switch
    everything over to mysql. Our site featured a custom built ecommerce
    site, forums and a few external services (feeds like weather etc).

    Because we designed everything using EJBs and a decent middle tier we
    were able to convert everything over in two days (two senior
    developers).

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Ni aun el genio muy grande llegaría muy lejos
si tuviera que sacarlo todo de su propio interior" (Goethe)


Re: A thread about SPs -- mentioning PostgreSQL

From
"Scott Marlowe"
Date:
On Sat, 2004-07-31 at 01:41, Alvaro Herrera wrote:
> On Sat, Jul 31, 2004 at 09:22:18AM +0300, Devrim GUNDUZ wrote:
>
> > There is a new thread at /. "Stored Procedures - Good or Bad?", and it
> > mentions about PostgreSQL among enterprise databases:
> >
> > http://ask.slashdot.org/askslashdot/04/07/30/2324206.shtml?tid=198&tid=156&tid=4&tid=1&tid=8&tid=218
>
> Very interesting.  A gem:
>
>     I used to work for an ecommerce (java/ejb) website that needed to ditch
>     it's oracle licensing (we were running out of money) and had to switch
>     everything over to mysql. Our site featured a custom built ecommerce
>     site, forums and a few external services (feeds like weather etc).
>
>     Because we designed everything using EJBs and a decent middle tier we
>     were able to convert everything over in two days (two senior
>     developers).

I think what this points up is that if you're going to have one big
process updating your data, and the others just need to read it, then
you can build your business rules into the application layer for
updates, and use views for all the output and it's quite portable.

OTOH, having a data store that's updated by different processes may need
to have more of the logic in the database for both performance and
correctness.

I feel the article was mistitled, it should have been "when you should
choose one or the other" or something like that.


Re: A thread about SPs -- mentioning PostgreSQL

From
"Fred Moyer"
Date:
> There is a new thread at /. "Stored Procedures - Good or Bad?", and it
> mentions about PostgreSQL among enterprise databases:
>
>http://ask.slashdot.org/askslashdot/04/07/30/2324206.shtml?tid=198&tid=156&tid=4&tid=1&tid=8&tid=218
>

So being both a programmer and dba, with a database like PostgreSQL which
has procedural languages in several different flavors, I am wondering what
else besides robust transactions PostgreSQL stored procedures provides?
(that in itself is enough for me)  Achieving transactions on the
application side has it's caveats, which is why I am keen on using
PostgreSQL's transactional API for data (read object) persistence.

I spend the bulk of my time right now coding mod_perl, so I ask you
pgsql-advocacy list, is pl/perl functionally equivalent to pl/pgsql?  If I
can move my persistence layer to PostgreSQL, with pl/perl taking care of
the under-the-persistence-layer-api-work, I would love to do so.  Perl is
great for manipulating data structures - PostgreSQL is great for storing
them.  But I need some solid arguments; I am easy to sell on the idea but
my colleagues are somewhat more discerning :)

- Fred

Re: A thread about SPs -- mentioning PostgreSQL

From
Josh Berkus
Date:
Fred,

> So being both a programmer and dba, with a database like PostgreSQL which
> has procedural languages in several different flavors, I am wondering what
> else besides robust transactions PostgreSQL stored procedures provides?
> (that in itself is enough for me)  Achieving transactions on the
> application side has it's caveats, which is why I am keen on using
> PostgreSQL's transactional API for data (read object) persistence.

Sure:

1) More efficient set operations: server-side stuff tends to do this better,
being closer to the database.

2) "Ironclad" business rules:  by attaching an Constraint, Data-push Function,
Trigger or Rule to a table you make it impossible to break that Business
Rule.  If the BR is in the mod_perl code, you're depending on your interface
developer's consistency, which can lead to mistakes.

3) "Virtual Tables":  You can use set-returning functions to return rowsets
that would be difficult to get efficiently out of a view, such as queries
containing "dummy rows" or complex orderings.

> I spend the bulk of my time right now coding mod_perl, so I ask you
> pgsql-advocacy list, is pl/perl functionally equivalent to pl/pgsql?

Once the new one is out of beta, yes.  Your Testing Will Help (see the
PL/perlNG project).


--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: A thread about SPs -- mentioning PostgreSQL

From
"Fred Moyer"
Date:
Josh,

>> pgsql-advocacy list, is pl/perl functionally equivalent to pl/pgsql?
>
> Once the new one is out of beta, yes.  Your Testing Will Help (see the
> PL/perlNG project).

Thanks for the tip!  Can you point me to the project homepage?  I can't
seem to find it on google, gforge, or gborg.

Thanks,

Fred

Re: A thread about SPs -- mentioning PostgreSQL

From
Alvaro Herrera Munoz
Date:
On Sun, Aug 01, 2004 at 10:29:28AM -0700, Fred Moyer wrote:
> Josh,
>
> >> pgsql-advocacy list, is pl/perl functionally equivalent to pl/pgsql?
> >
> > Once the new one is out of beta, yes.  Your Testing Will Help (see the
> > PL/perlNG project).
>
> Thanks for the tip!  Can you point me to the project homepage?  I can't
> seem to find it on google, gforge, or gborg.

It's integrated in the main source as of current CVS development tip,
which will be soon released as a first beta.  You can grab a binary
snapshot somewhere in the FTP site, I think.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"La rebeldía es la virtud original del hombre" (Arthur Schopenhauer)