Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Sat, Aug 27, 2011 at 11:43 PM, Andrej  wrote:
> On 19 August 2011 04:16, Merlin Moncure  wrote:
>>> It's been around for a long time already:
>>>
>>>  http://asmith.id.au/mod_libpq.html
>> mod_libpq looks like it hasn't been updated in quite a while (apache
>> 1.3 only) -- I think a node.js http server is superior in just about
>> every way for this case.  I 100% agree with the comments on the page
>> though.
>
> Tad late to chime in, but mod_libpq2.c is available from the good
> man, too.
> http://asmith.id.au/source/mod_libpq2.c
>
> Compiles & installs fine on Slackware64 13.1:
> sudo /usr/sbin/apxs -i -a -c  -I /usr/include/postgresql/ -I
> /usr/include/httpd -lpq -o mod_libpq.so -n MOD_LIBPQ mod_libpq2.c
> using postgresql 9.0.4 & apache 2.2.19

Sure -- but these days if I were trying to run a setup like this
through a classic web server, I think a small fastcgi wrapper is a
better way to go.  It's simpler, gives you a cleaner approach to
hooking in your on stuff like a connection pool, and is portable
across web servers.  ISTM lighttpd and nginx are lighter weight and
designed for this type of serving.  In apache, you have mod_fastcgi
which is probably a better abstraction to use than mod_libpq. You also
have a much wider audience for your code if you chose to advance your
wrapper as a library.

node.js is even thinner.  instead of running your requests through
fastcgi, node.js is a simple protocol server that runs the web request
in the server thread itself, including your libpq bindings, connection
pool, etc.  This is why as long as you are not doing to much work in
the javascript itself, it could very well prove to be the most
scalable solution with the lowest latency response times without
resorting to a hand rolled http server in C.  node.js is single
threaded and 100% asynchronous which fits very nice with libpq which
is at heart a single threaded asynchronous library.   Client side
connection pooling is trivial in single threaded engines such that a
middle of the road programmer could rig up an ad hoc implementation
quickly and effectively, very complex otherwise.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Dimitri Fontaine]

[edited]

Merlin Moncure  writes:
  http://asmith.id.au/mod_libpq.html
>> http://asmith.id.au/source/mod_libpq2.c
>
> node.js is even thinner.
>
> node.js is single threaded and 100% asynchronous which fits very nice
> with libpq which is at heart a single threaded asynchronous library.

Sure.  Elnode shares this design, and yaws more seriously so.  I
wouldn't pick mod_libpq myself.

  http://nic.ferrier.me.uk/blog/2010_10/elnode
  http://yaws.hyber.org/

Just saying that the thin web server layer that directly hands the
request to the database has been existing in PostgreSQL land for a long
time already, no need to resort to other proprietary architectures here.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Andrej]

On 19 August 2011 04:16, Merlin Moncure  wrote:
>> It's been around for a long time already:
>>
>>  http://asmith.id.au/mod_libpq.html
> mod_libpq looks like it hasn't been updated in quite a while (apache
> 1.3 only) -- I think a node.js http server is superior in just about
> every way for this case.  I 100% agree with the comments on the page
> though.

Tad late to chime in, but mod_libpq2.c is available from the good
man, too.
http://asmith.id.au/source/mod_libpq2.c

Compiles & installs fine on Slackware64 13.1:
sudo /usr/sbin/apxs -i -a -c  -I /usr/include/postgresql/ -I
/usr/include/httpd -lpq -o mod_libpq.so -n MOD_LIBPQ mod_libpq2.c
using postgresql 9.0.4 & apache 2.2.19


Cheers,
Andrej

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Dmitriy Igrishin]

2011/8/18 s...@bestmx.ru 

> Dmitriy Igrishin пишет:
>
>>
>>
>> 2011/8/18 s...@bestmx.ru  > s...@bestmx.ru>>
>>
>>Dmitriy Igrishin пишет:
>>
>>
>>
>>2011/8/18 s...@bestmx.ru 
>>> > >
>>>>
>>
>>
>>   Merlin Moncure пишет:
>>
>>   On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
>>mailto:dimi...@2ndquadrant.fr**>
>>>
>>>>  wrote:
>>
>>
>>   c k>> >
>>>
>> >>
>>  writes:
>>
>>
>>   Many users are using it and found it stable and
>>   scalable. Important is that
>>   web server is external to the database and a
>>mod_pgsql
>>   like mod_plsql is
>>   used to connect web server to database. Each
>>page is
>>   considered as a stored
>>   procedure in the oracle database. I am not
>>thinking of
>>   implementing as it is
>>
>>   It's been around for a long time already:
>>
>>
>> http://asmith.id.au/mod_libpq.**html
>>
>>   mod_libpq looks like it hasn't been updated in quite a
>>while
>>   (apache
>>   1.3 only) -- I think a node.js http server is superior
>>in just
>>   about
>>   every way for this case.  I 100% agree with the comments on
>>   the page
>>   though.
>>
>>   merlin
>>
>>   i still recommend nginx
>>
>>I recommend Wt:
>>http://www.webtoolkit.eu/
>>:-)
>>
>>it looks like feces
>>"and uses well-tested patterns of desktop GUI development"
>>
>> Oh oh. So unprofessional comment!
>> Well, have a nice coding a Web 2.0 application with nginx + PostgreSQL :-)
>>
>> --
>> // Dmitriy.
>>
>>
>>
> who said "web 2.0" ?
> i've never used religious idioms in a technical talk.
>
I see. You're using only "nginx" :-)



-- 
// Dmitriy.

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Thu, Aug 18, 2011 at 4:32 AM, Sim Zacks  wrote:

> There are many differences.
> 1) If I have a database function and I copy my database to another server,
> the function still works.
> If I have an external daemon application, I not only have to copy my
> database, I also have to copy the daemon application. Then I have to build
> an init script and make sure it runs at startup. My LISTEN/NOTIFY daemon is
> a c application, so when I move my database to a server on a different
> platform, I have to recompile it.


Ok, so you have made a decision to favor performance well ahead of
flexibility.  I guess the question is what the performance cost
writing it in python actually is and what the flexibility cost of
writing it in C actually is.  Presumably you have already answered
this for yourself, but this strikes me as coming out of that tradeoff
rather than being inherent in the idea.

>
> 2) there is absolutely no reason you can't build redundancy into this
> system.
>
> Its not a question of whether I can or cannot build redundancy, it is a
> question of whether I have to build an entire system in order to call a
> database function from another database function. The only reason this is
> complicated is because it needs to be in its own session. That simple issue
> shouldn't force me to build: a) a daemon application, b) include redundancy
> to ensure that it is running, c) not be included in my database
> backup/restore.

Emailing IMHO isn't a database function.

> Remember, I don't want to build a _system_, I basically want an asynchronous
> trigger. On specific event call a database function in its own transaction
> space and allow the existing transaction to end.
>
> 3)  The overhead really shouldn't be bad, and if your parts are
> well-modularized, and carefully designed overhead really should be
> minimal.
>
> Any overhead that is not necessary should not be added in. It is the minor
> level of frustration that something didn't work when I migrated servers
> until the "Oh Yeah" kicked in. Then looking through all my notes to find the
> compilation instructions for my daemon because we moved from a 32 bit server
> to a 64 bit. Then trying to figure out the syntax for the init script,
> because we moved from Gentoo to Debian and it is slightly different. It
> isn't a lot of overhead but it is completely unneccessary in our situation.
> I will agree that this is entirely necessary if your application actually
> uses an external system and the database communicates through Listen/Notify.
> You have 2 systems to deal with in any case, but for me the only external
> component is having the daemon listen so it can call another function in the
> database. IOW, I don't generally deal with anything else on the server.

In general I would be opposed to allowing functions to exist outside
of transactional control.  While it is true you save some conceptual
complexity in moving everything into the database, allowing stored
proc functions to commit/start transactions would add a tremendous
amount conceptual complexity in the database itself.  At the moment I
don't think this is generally worth it.  The beauty of the current
approach is that the transactional control works in very well-defined
ways.  This significantly saves testing and QA effort.I would be
concerned that a capability like this would be sufficiently disruptive
to the assumptions of testing, that the costs would always be far
higher than the benefits.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[s...@bestmx.ru]

Dmitriy Igrishin пишет:



2011/8/18 s...@bestmx.ru  >


Dmitriy Igrishin пишет:



2011/8/18 s...@bestmx.ru 
> mailto:s...@bestmx.ru> >>


   Merlin Moncure пишет:

   On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
mailto:dimi...@2ndquadrant.fr>
>>  wrote:


   c kmailto:shreeseva.learn...@gmail.com>
>>  writes:


   Many users are using it and found it stable and
   scalable. Important is that
   web server is external to the database and a
mod_pgsql
   like mod_plsql is
   used to connect web server to database. Each
page is
   considered as a stored
   procedure in the oracle database. I am not
thinking of
   implementing as it is

   It's been around for a long time already:

http://asmith.id.au/mod_libpq.html

   mod_libpq looks like it hasn't been updated in quite a
while
   (apache
   1.3 only) -- I think a node.js http server is superior
in just
   about
   every way for this case.  I 100% agree with the comments on
   the page
   though.

   merlin

   i still recommend nginx

I recommend Wt:
http://www.webtoolkit.eu/
:-)

it looks like feces
"and uses well-tested patterns of desktop GUI development"

Oh oh. So unprofessional comment!
Well, have a nice coding a Web 2.0 application with nginx + PostgreSQL :-)

--
// Dmitriy.




who said "web 2.0" ?
i've never used religious idioms in a technical talk.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Dmitriy Igrishin]

2011/8/18 s...@bestmx.ru 

> Dmitriy Igrishin пишет:
>
>>
>>
>> 2011/8/18 s...@bestmx.ru  > s...@bestmx.ru>>
>>
>>
>>Merlin Moncure пишет:
>>
>>On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
>>mailto:dimi...@2ndquadrant.fr**>>  wrote:
>>
>>
>>c k>
>> >
>>  writes:
>>
>>
>>Many users are using it and found it stable and
>>scalable. Important is that
>>web server is external to the database and a mod_pgsql
>>like mod_plsql is
>>used to connect web server to database. Each page is
>>considered as a stored
>>procedure in the oracle database. I am not thinking of
>>implementing as it is
>>
>>It's been around for a long time already:
>>
>>
>> http://asmith.id.au/mod_libpq.**html
>>
>>mod_libpq looks like it hasn't been updated in quite a while
>>(apache
>>1.3 only) -- I think a node.js http server is superior in just
>>about
>>every way for this case.  I 100% agree with the comments on
>>the page
>>though.
>>
>>merlin
>>
>>i still recommend nginx
>>
>> I recommend Wt:
>> http://www.webtoolkit.eu/
>> :-)
>>
>>  it looks like feces
> "and uses well-tested patterns of desktop GUI development"
>
Oh oh. So unprofessional comment!
Well, have a nice coding a Web 2.0 application with nginx + PostgreSQL :-)

-- 
// Dmitriy.

Re: [GENERAL] Using Postgresql as application server

[s...@bestmx.ru]

Dmitriy Igrishin пишет:



2011/8/18 s...@bestmx.ru  >


Merlin Moncure пишет:

On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
mailto:dimi...@2ndquadrant.fr>>  wrote:

c kmailto:shreeseva.learn...@gmail.com>>  writes:

Many users are using it and found it stable and
scalable. Important is that
web server is external to the database and a mod_pgsql
like mod_plsql is
used to connect web server to database. Each page is
considered as a stored
procedure in the oracle database. I am not thinking of
implementing as it is

It's been around for a long time already:

http://asmith.id.au/mod_libpq.html

mod_libpq looks like it hasn't been updated in quite a while
(apache
1.3 only) -- I think a node.js http server is superior in just
about
every way for this case.  I 100% agree with the comments on
the page
though.

merlin

i still recommend nginx

I recommend Wt:
http://www.webtoolkit.eu/
:-)


it looks like feces
"and uses well-tested patterns of desktop GUI development"


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Dmitriy Igrishin]

2011/8/18 s...@bestmx.ru 

> Merlin Moncure пишет:
>
>  On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
>>   wrote:
>>
>>> c k  writes:
>>>
 Many users are using it and found it stable and scalable. Important is
 that
 web server is external to the database and a mod_pgsql like mod_plsql is
 used to connect web server to database. Each page is considered as a
 stored
 procedure in the oracle database. I am not thinking of implementing as
 it is

>>> It's been around for a long time already:
>>>
>>>  http://asmith.id.au/mod_libpq.**html
>>>
>> mod_libpq looks like it hasn't been updated in quite a while (apache
>> 1.3 only) -- I think a node.js http server is superior in just about
>> every way for this case.  I 100% agree with the comments on the page
>> though.
>>
>> merlin
>>
> i still recommend nginx
>
> I recommend Wt:
http://www.webtoolkit.eu/
:-)


-- 
// Dmitriy.

Re: [GENERAL] Using Postgresql as application server

[s...@bestmx.ru]

Merlin Moncure пишет:

On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
  wrote:

c k  writes:

Many users are using it and found it stable and scalable. Important is that
web server is external to the database and a mod_pgsql like mod_plsql is
used to connect web server to database. Each page is considered as a stored
procedure in the oracle database. I am not thinking of implementing as it is

It's been around for a long time already:

  http://asmith.id.au/mod_libpq.html

mod_libpq looks like it hasn't been updated in quite a while (apache
1.3 only) -- I think a node.js http server is superior in just about
every way for this case.  I 100% agree with the comments on the page
though.

merlin

i still recommend nginx


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Thu, Aug 18, 2011 at 5:48 AM, Dimitri Fontaine
 wrote:
> c k  writes:
>> Many users are using it and found it stable and scalable. Important is that
>> web server is external to the database and a mod_pgsql like mod_plsql is
>> used to connect web server to database. Each page is considered as a stored
>> procedure in the oracle database. I am not thinking of implementing as it is
>
> It's been around for a long time already:
>
>  http://asmith.id.au/mod_libpq.html

mod_libpq looks like it hasn't been updated in quite a while (apache
1.3 only) -- I think a node.js http server is superior in just about
every way for this case.  I 100% agree with the comments on the page
though.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Thu, Aug 18, 2011 at 3:40 AM, Dimitri Fontaine
 wrote:
> Chris Travers  writes:
>> I want an email to go out to the ordering manager when the quantity I
>> have of an item drops below the re-order point.  I also want this
>> email NOT to go out if the transaction rolls back.  (Wait, the order
>> of 5 widgets I just processed rolled back because it isn't to a
>> valid customer!  We normally only sell 5 per year anyway.  No need
>> for the email.)
>
> Just use PGQ and be done with it.  You have transactional and
> asynchronous behavior.  Typically, a trigger would produce events in the
> queue, and a separate daemon will consume the queue and send emails.
>
That actually looks quite helpful.  Thanks.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

  
  
On 08/18/2011 07:57 AM, Chris Travers wrote:

  On Wed, Aug 17, 2011 at 9:38 PM, Sim Zacks  wrote:


  
The point was not whether I have a bug in an external application, the point
is that I need an external application which creates more overhead and
another point of failure in the application stack.


  
  1)  Not sure how an external python script is different from a
PL/Python sproc except that the former exists external to transaction
control.


There are many differences. 
1) If I have a database function and I copy my database to another
server, the function still works. 
If I have an external daemon application, I not only have to copy my
database, I also have to copy the daemon application. Then I have to
build an init script and make sure it runs at startup. My
LISTEN/NOTIFY daemon is a c application, so when I move my database
to a server on a different platform, I have to recompile it.  

  2) there is absolutely no reason you can't build redundancy into this system.


Its not a question of whether I can or cannot build redundancy, it
is a question of whether I have to build an entire system in order
to call a database function from another database function. The only
reason this is complicated is because it needs to be in its own
session. That simple issue shouldn't force me to build: a) a daemon
application, b) include redundancy to ensure that it is running, c)
not be included in my database backup/restore.
Remember, I don't want to build a _system_, I basically want an
asynchronous trigger. On specific event call a database function in
its own transaction space and allow the existing transaction to end.


  3)  The overhead really shouldn't be bad, and if your parts are
well-modularized, and carefully designed overhead really should be
minimal.


Any overhead that is not necessary should not be added in. It is the
minor level of frustration that something didn't work when I
migrated servers until the "Oh Yeah" kicked in. Then looking through
all my notes to find the compilation instructions for my daemon
because we moved from a 32 bit server to a 64 bit. Then trying to
figure out the syntax for the init script, because we moved from
Gentoo to Debian and it is slightly different. It isn't a lot of
overhead but it is completely unneccessary in our situation. 
I will agree that this is entirely necessary if your application
actually uses an external system and the database communicates
through Listen/Notify. You have 2 systems to deal with in any case,
but for me the only external component is having the daemon listen
so it can call another function in the database. IOW, I don't
generally deal with anything else on the server.


  Best Wishes,
Chris Travers


Sim

  

Re: [GENERAL] Using Postgresql as application server

[Dimitri Fontaine]

c k  writes:
> Many users are using it and found it stable and scalable. Important is that
> web server is external to the database and a mod_pgsql like mod_plsql is
> used to connect web server to database. Each page is considered as a stored
> procedure in the oracle database. I am not thinking of implementing as it is

It's been around for a long time already:

  http://asmith.id.au/mod_libpq.html

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Dimitri Fontaine]

Chris Travers  writes:
> I want an email to go out to the ordering manager when the quantity I
> have of an item drops below the re-order point.  I also want this
> email NOT to go out if the transaction rolls back.  (Wait, the order
> of 5 widgets I just processed rolled back because it isn't to a
> valid customer!  We normally only sell 5 per year anyway.  No need
> for the email.)

Just use PGQ and be done with it.  You have transactional and
asynchronous behavior.  Typically, a trigger would produce events in the
queue, and a separate daemon will consume the queue and send emails.

Regards,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Wed, Aug 17, 2011 at 9:38 PM, Sim Zacks  wrote:

> The point was not whether I have a bug in an external application, the point
> is that I need an external application which creates more overhead and
> another point of failure in the application stack.
>
1)  Not sure how an external python script is different from a
PL/Python sproc except that the former exists external to transaction
control.
2) there is absolutely no reason you can't build redundancy into this system.
3)  The overhead really shouldn't be bad, and if your parts are
well-modularized, and carefully designed overhead really should be
minimal.

I don;t see what you gain from using cron that you don't gain from
using a persistent process and notify... you could even have a cron
script to check if it is running and start if not from time to time.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

  
  
On 08/17/2011 05:34 PM, Scott Ribe wrote:

  On Aug 17, 2011, at 1:05 AM, Sim Zacks wrote:


  
One problem we have with LISTEN/NOTIFY (and I haven't found the cause for this yet) is every once in a while my daemon stops listening. It may be after a month of use or longer, and may be caused by the database being restarted or something similar. When the daemon stops listening, it doesn't give any errors or indication that it isn't working anymore.

  
  
So your daemon has a bug. When the database is restarted, connections will be closed, and the daemon should certainly notice that. Of course the cause may be something else, but either way I doubt it's a problem with NOTIFY/LISTEN.


The point was not whether I have a bug in an external
  application, the point is that I need an external application
  which creates more overhead and another point of failure in the
  application stack. 

  

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

  
  
On 08/17/2011 06:13 PM, Chris Travers wrote:

  On Tue, Aug 16, 2011 at 11:53 PM, Sim Zacks  wrote:


  
We are doing this same sort of thing now. If the transaction goes through,
the email record gets written to a table. We have a cron job that calls a
database function that processes all emails that have not been processed
yet. If the transaction gets rolled back, the email record does not get
written to the table and the email does not get sent.
In your scenario, if you send the NOTIFY message and then you roll back the
transaction, the helper application will still send the email. IOW, doing
this outside of the database can more easily break your transactional
integrity.


  
  Notify hits on commit, right?

Best Wishes,
Chris Travers


My bad. I just tested this. Notify doesn't get send until after
  commit.

  

Re: [GENERAL] Using Postgresql as application server

[Craig Ringer]

On 18/08/2011 12:35 AM, John R Pierce wrote:

On 08/17/11 7:40 AM, Merlin Moncure wrote:

GRANT/REVOKE only constrain read/write privileges to a database.


at a table level, and even distinguishing between INSERT (writing new 
data) and UPDATING (updating existing data).


Column level, actually :-) and they can control read access as well as 
write access.


Further control for writes can be applied using triggers that RAISE 
EXCEPTION when  they don't like something.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[John R Pierce]

On 08/17/11 7:40 AM, Merlin Moncure wrote:

GRANT/REVOKE only constrain read/write privileges to a database.


at a table level, and even distinguishing between INSERT (writing new 
data) and UPDATING (updating existing data).  you can get even finer 
granularity, using functions with SECURITY_DEFINER based permissions.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Wed, Aug 17, 2011 at 7:40 AM, Merlin Moncure  wrote:

>
> GRANT/REVOKE only constrain read/write privileges to a database.
> Application level security is typically much finer grained than that.
> Also, I using SQL roles for actual user roles is not typically done
> for various reasons.  Generally, SQL roles are used to define 'what'
> is logging in, no necessarily 'who'.  If you allow and SQL through
> from the application then table level security becomes very
> important...otherwise not so much.

You can use roles to affect things on a fairly granular level, if you
combine a relation interface with a functional one.

And the fact most people use SQL roles this way is due to enforcing
security in the middleware.  The disadvantage is that the database has
to trust the middleware and the other clients connecting to it.  To
some extent this is unavoidable, but in general, reducing the level of
trust between the components reduces the security exposure.  Obviously
this has some issues in terms of how far it can scale.

One of the things we decided to do with LedgerSMB was to make every
application user a database user and then enforce security on the back
end.
>
>> What I am saying is that the further back you enforce the security the
>> more you can guarantee consistent enforcement across applications.
>> Connection pooling makes this much harder because you can't enforce it
>> within the db using the normal methods and end up having to implement
>> it all over.  Instead you have to implement security before the data
>> hits the database.  That's a big difference and it has HUGE
>> ramifications for security exposure vs utility of an application.
>
> That is totally incorrect.  pgbouncer maintains separate pools for
> each role and only intermingles queries for like roles.  Any
> intelligent connection pooler would do the same. You lose access to
> database session features but database level security features are
> still enforced.  Whether you connection pool or not really doesn't
> play into this from my point of view.

Right, but then you still can't enforce *user* permissions on the
database because there isn't a point in having a connection pool if
each user gets one as a db user, is there?

>
> Recall that in our hypothetical 'database as middleware' database, the
> main tables and there data are not actually in the database -- the
> only tables available to query would be session state, etc.  Most
> operations would funnel through back to the main database through
> procedures and application level security would be checked there.
> Now, if you want your system to be simple, tight, and fast, you could
> combine those two databases but would have to figure out how to manage
> security to a libpq speaking application.   Like I said, in my case I
> did this with a whitelist, but it's not the only way.

I guess I am approaching it differently as looking at logical tiers
getting incorporated into the RDBMS, which becomes the centerpiece of
and entrance point to the application server environment.  That's why
I am talking about the database taking over traditional middleware
functions rather than having a separate database..
>
>>> well, not exactly.  it is a concession to security.  allowing

> If you expose (as I did) your middleware api as a 100% sql function
> interface, then yes ad hoc sql is not allowed.   If you wrap your
> middleware with a http server than ad hoc sql would not be allowed.  I
> doubt the day will come where the browser will be sending ad hoc SQL
> queries directly to a database.

One of my LedgerSMB customers decided they wanted to be able to
distribute SQL scripts to bookkeepers and have them run them via
pgAdmin.  So from the browser?  No.  From other software clients?
Quite possibly.  What we were able to do was assign the specifically
needed functionality to the pgAdmin users and thus ensure that
security and data integrity were not compromised by this approach.
Now, the users in this case require a lot of read access, with a few
very specific write permissions.   The security footprint here is very
low.

We couldn't have accommodated that request safely, however, if our
permissions system wasn't geared around the db enforcing permissions
per user.
>
> The reason to use a database backend to handle middleware functions is
> based on the strength of the SQL language, supported by the various PL
> extensions you may want to include, to manage various everyday
> programming tasks.  The security discussion is a bit of a sideshow
> because it is generally a tiny fraction of the coding that typically
> happens at this level.  An individual's personal appraisal of this
> idea will largely depend on certain personal factors that will vary
> from developer to developer.  An unbiased analysis would probably
> conclude that it is an interesting, but unproven approach with a lot
> of potential.

It's a tiny piece of the code, but it's a critical one, and when
something goes wrong

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Tue, Aug 16, 2011 at 11:53 PM, Sim Zacks  wrote:

> We are doing this same sort of thing now. If the transaction goes through,
> the email record gets written to a table. We have a cron job that calls a
> database function that processes all emails that have not been processed
> yet. If the transaction gets rolled back, the email record does not get
> written to the table and the email does not get sent.
> In your scenario, if you send the NOTIFY message and then you roll back the
> transaction, the helper application will still send the email. IOW, doing
> this outside of the database can more easily break your transactional
> integrity.
>
Notify hits on commit, right?

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Tue, Aug 16, 2011 at 6:14 PM, Chris Travers  wrote:
> On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure  wrote:
>
>>
>> /shrug.  pretty much every project I've ever worked on application
>> security has been ad hoc, database driven, not very complicated, and
>> not a performance bottleneck.  By the way, I think the opposite of
>> you: security information relating to application roles and actions
>> *should* be stored in the database (it is, after all, data) even if it
>> is enforced by a classic middleware.  What happens when some other
>> application, written by another team, connects to the database?
>
> Not understanding my perspective.  Ideally you'd use the RDBMS's
> functionality directly to enforce security via GRANT and REVOKE
> statements.  Whether it is stored in the database or not is for the
> RDBMS to decide.

GRANT/REVOKE only constrain read/write privileges to a database.
Application level security is typically much finer grained than that.
Also, I using SQL roles for actual user roles is not typically done
for various reasons.  Generally, SQL roles are used to define 'what'
is logging in, no necessarily 'who'.  If you allow and SQL through
from the application then table level security becomes very
important...otherwise not so much.

> What I am saying is that the further back you enforce the security the
> more you can guarantee consistent enforcement across applications.
> Connection pooling makes this much harder because you can't enforce it
> within the db using the normal methods and end up having to implement
> it all over.  Instead you have to implement security before the data
> hits the database.  That's a big difference and it has HUGE
> ramifications for security exposure vs utility of an application.

That is totally incorrect.  pgbouncer maintains separate pools for
each role and only intermingles queries for like roles.  Any
intelligent connection pooler would do the same. You lose access to
database session features but database level security features are
still enforced.  Whether you connection pool or not really doesn't
play into this from my point of view.

Recall that in our hypothetical 'database as middleware' database, the
main tables and there data are not actually in the database -- the
only tables available to query would be session state, etc.  Most
operations would funnel through back to the main database through
procedures and application level security would be checked there.
Now, if you want your system to be simple, tight, and fast, you could
combine those two databases but would have to figure out how to manage
security to a libpq speaking application.   Like I said, in my case I
did this with a whitelist, but it's not the only way.

>> well, not exactly.  it is a concession to security.  allowing
>> untrusted entities to send ad hoc sql to a database is obviously not
>> going to fly so it must be dealt with appropriately.  note pgbouncer
>> (or node.js etc) is not defining or handling session auth, just
>> playing a small role enforcement.  an auth'd application service
>> requests are essentially protocol noise and I see no problem letting
>> the protocol handler bounce them out.  also, whatever you happen to
>> wrap your 'middleware' database is still part of the middleware.
>
> Well, what you are actually doing here is enforcing security on a
> level of abstraction away from the database.  This means that you
> can't allow ad hoc queries because you can't guarantee safety.  I
> don't know what you get by doing this instead of providing
> interface-level security in the part of your middleware.  In fact
> that's essentially what you have to do, is it not?

If you expose (as I did) your middleware api as a 100% sql function
interface, then yes ad hoc sql is not allowed.   If you wrap your
middleware with a http server than ad hoc sql would not be allowed.  I
doubt the day will come where the browser will be sending ad hoc SQL
queries directly to a database.

The reason to use a database backend to handle middleware functions is
based on the strength of the SQL language, supported by the various PL
extensions you may want to include, to manage various everyday
programming tasks.  The security discussion is a bit of a sideshow
because it is generally a tiny fraction of the coding that typically
happens at this level.  An individual's personal appraisal of this
idea will largely depend on certain personal factors that will vary
from developer to developer.  An unbiased analysis would probably
conclude that it is an interesting, but unproven approach with a lot
of potential.

>> also I think most people would not go the libpq route even though
>> listen/notify allows you to create beautifully interactive systems --
>> mainly due to weirdness of the whole thing and the amount of work you
>> have to do to get it safe.
>
> Why?  Each listener has to have its own connection, right?  Otherwise
> there is nowhere to send the notifications to.  That connection has t

Re: [GENERAL] Using Postgresql as application server

[Scott Ribe]

On Aug 17, 2011, at 1:05 AM, Sim Zacks wrote:

> One problem we have with LISTEN/NOTIFY (and I haven't found the cause for 
> this yet) is every once in a while my daemon stops listening. It may be after 
> a month of use or longer, and may be caused by the database being restarted 
> or something similar. When the daemon stops listening, it doesn't give any 
> errors or indication that it isn't working anymore.

So your daemon has a bug. When the database is restarted, connections will be 
closed, and the daemon should certainly notice that. Of course the cause may be 
something else, but either way I doubt it's a problem with NOTIFY/LISTEN.

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Scott Ribe]

On Aug 17, 2011, at 12:53 AM, Sim Zacks wrote:

> In your scenario, if you send the NOTIFY message and then you roll back the 
> transaction, the helper application will still send the email.

How? NOTIFY doesn't get delivered until the transaction commits.

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

On 08/16/2011 07:04 AM, Darren Duncan wrote:

Chris Travers wrote:

On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan
 wrote:

I believe we basically have all the foundation already, with maybe
procedures executable outside transactions being the last major part.


Why is this desirable? Why is it more desirable than actually using
the listen/notify infrastructure that exists already?


Maybe listen/notify is sufficient by itself. I withdraw my "procedures
executable outside transactions" comment for now, and just bring it up
later if I can think of a specific use case that other mechanisms don't
satisfy. -- Darren Duncan

LISTEN/NOTIFY is good if you want to call an outside application, 
however, if you want to call another database function, then it is just 
plain wrong. I need a daemon that will listen to for notify calls so 
that it can open a new database session and call the function. With an 
asynchronous function, my function would be able to spawn a new session 
and run in its own transaction while the calling function would be able 
to complete and go away.
See http://archives.postgresql.org/pgsql-hackers/2011-04/msg01503.php 
for my proposal on this issue.


One problem we have with LISTEN/NOTIFY (and I haven't found the cause 
for this yet) is every once in a while my daemon stops listening. It may 
be after a month of use or longer, and may be caused by the database 
being restarted or something similar. When the daemon stops listening, 
it doesn't give any errors or indication that it isn't working anymore. 
It is only after a user complains that something hasn't been updated in 
a day or so that we manually restart it. It doesn't happen very often, 
but it does happen on occasion.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

On 08/15/2011 11:50 PM, Chris Travers wrote:

On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan  wrote:


I believe that it is ideal for Postgres to be computationally complete in
that one *could* use it to implement a complete application.  That isn't to
say one should do this as a matter of course, good to use appropriate tools
for a job, but that it should at least be possible if one wanted to. --


I think the limit is actually transactional control.

So, suppose I manage inventory

I want an email to go out to the ordering manager when the quantity I
have of an item drops below the re-order point.  I also want this
email NOT to go out if the transaction rolls back.  (Wait, the order
of 5 widgets I just processed rolled back because it isn't to a
valid customer!  We normally only sell 5 per year anyway.  No need
for the email.)

1)  I don't see how this is possible directly from within PostgreSQL
2)  Given the obvious ways around this, I don't see why this is
desirable to add to PostgreSQL.

Best Wishes,
Chris Travers

We are doing this same sort of thing now. If the transaction goes 
through, the email record gets written to a table. We have a cron job 
that calls a database function that processes all emails that have not 
been processed yet. If the transaction gets rolled back, the email 
record does not get written to the table and the email does not get sent.
In your scenario, if you send the NOTIFY message and then you roll back 
the transaction, the helper application will still send the email. IOW, 
doing this outside of the database can more easily break your 
transactional integrity.


Sim

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Sim Zacks]

On 08/16/2011 03:06 AM, Craig Ringer wrote:

On 15/08/2011 10:36 PM, Merlin Moncure wrote:

On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
 wrote:

No, PG has never, and will never, act as an application-server.

Why in the world not?


The biggest reason is safety. Beyond that, the lack of autonomous
transactions, stored procedures, in-DB timers, and support for any
protocol other than the native Pg database query protocol mean it's also
rather impractical.

I guess theoretically one could embed a JVM / Python instance / whatever
in the postmaster and have it spawn new backends for incoming
connections with other protocols. But ... why? Why add all that
complexity and - more importantly - contaiminate PostgreSQL's address
space with more code that can fail when you don't have to? PLs and user
C procedures are already risk enough as far as I'm concerned.



As I've mentioned in other posts, we are using postgresql as a hybrid 
application server and have found it to be very practical and easy to 
code. For functions that need to run on a regular basis, we use a cron 
job, though from what I understand pgAgent should be able to handle that 
just as well.  Instead of using Listen/Notify to tell an application to 
send e-mails, we have an email function written in plpython that sends 
the email directly.
The reason to do this is because it gives you "write once" code which 
makes your application pretty much client-agnostic.


PG already spawns a new backend for each connection. So your specialized 
code has little chance of hosing the whole database.


We connect to other databases when we need to, mostly mysql using the 
MySQLdb python module, to move data to and from our website.


Scalability would be increased with multi-master replication, which 
bucardo currently does (I haven't tested it yet).


One issue you have is using database style hardware for application 
server needs, which may be more expensive.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[c k]

In this war no one is looking at APEX from oracle. Oracle have implemented
the whole webserver to DBMS gateway and web development and a web based
business solutions in the database it self. They are using pl/sql for this.
Many users are using it and found it stable and scalable. Important is that
web server is external to the database and a mod_pgsql like mod_plsql is
used to connect web server to database. Each page is considered as a stored
procedure in the oracle database. I am not thinking of implementing as it is
in postgresql but we can use plpgsql and pl/python to do some tasks that are
required not only for web server but for daily tasks also such as sending
emails, checking for data files from remote location, using FTP or other
protocols and connecting to other systems.
Web server must be external but most of the application functionality can be
implemented using database stored procs and functions.

Chaitanya Kulkarni

On Wed, Aug 17, 2011 at 4:44 AM, Chris Travers wrote:

> On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure 
> wrote:
>
> >
> > /shrug.  pretty much every project I've ever worked on application
> > security has been ad hoc, database driven, not very complicated, and
> > not a performance bottleneck.  By the way, I think the opposite of
> > you: security information relating to application roles and actions
> > *should* be stored in the database (it is, after all, data) even if it
> > is enforced by a classic middleware.  What happens when some other
> > application, written by another team, connects to the database?
>
> Not understanding my perspective.  Ideally you'd use the RDBMS's
> functionality directly to enforce security via GRANT and REVOKE
> statements.  Whether it is stored in the database or not is for the
> RDBMS to decide.
>
> What I am saying is that the further back you enforce the security the
> more you can guarantee consistent enforcement across applications.
> Connection pooling makes this much harder because you can't enforce it
> within the db using the normal methods and end up having to implement
> it all over.  Instead you have to implement security before the data
> hits the database.  That's a big difference and it has HUGE
> ramifications for security exposure vs utility of an application.
>
> > Saying 'that shouldn't be allowed to happen' is simply not the reality
> > in many enterprise environments.  I don't see what's so complicated
> > about storing who a person is and what they are allowed to do, and
> > checking the permission just before that 'what' is about to get done.
>
> No, I think the opposite.  If the RDBMS enforces security then any
> client can connect and you don't have to worry about this problem at
> all.  However if you push this into the RDBMS and use native
> facilities, then you can't use connection pooling.
> >
>
> >
> > well, not exactly.  it is a concession to security.  allowing
> > untrusted entities to send ad hoc sql to a database is obviously not
> > going to fly so it must be dealt with appropriately.  note pgbouncer
> > (or node.js etc) is not defining or handling session auth, just
> > playing a small role enforcement.  an auth'd application service
> > requests are essentially protocol noise and I see no problem letting
> > the protocol handler bounce them out.  also, whatever you happen to
> > wrap your 'middleware' database is still part of the middleware.
>
> Well, what you are actually doing here is enforcing security on a
> level of abstraction away from the database.  This means that you
> can't allow ad hoc queries because you can't guarantee safety.  I
> don't know what you get by doing this instead of providing
> interface-level security in the part of your middleware.  In fact
> that's essentially what you have to do, is it not?
> >
> > also I think most people would not go the libpq route even though
> > listen/notify allows you to create beautifully interactive systems --
> > mainly due to weirdness of the whole thing and the amount of work you
> > have to do to get it safe.
>
> Why?  Each listener has to have its own connection, right?  Otherwise
> there is nowhere to send the notifications to.  That connection has to
> be attached to a db role.  The DB role has to have permission to some
> portions of the database but not others, right?  I don't see why that
> is hard to make safe.
>
> >
> > sure. that is a distinction I guess you could make.  but the real
> > point is all the zillions of LOC that just stupidly bounce data around
> > -- kill em with fire, i say :-D.
> >
> We can agree with that part.
>
>  Best Wishes,
> Chris Travers
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Tue, Aug 16, 2011 at 3:51 PM, Merlin Moncure  wrote:

>
> /shrug.  pretty much every project I've ever worked on application
> security has been ad hoc, database driven, not very complicated, and
> not a performance bottleneck.  By the way, I think the opposite of
> you: security information relating to application roles and actions
> *should* be stored in the database (it is, after all, data) even if it
> is enforced by a classic middleware.  What happens when some other
> application, written by another team, connects to the database?

Not understanding my perspective.  Ideally you'd use the RDBMS's
functionality directly to enforce security via GRANT and REVOKE
statements.  Whether it is stored in the database or not is for the
RDBMS to decide.

What I am saying is that the further back you enforce the security the
more you can guarantee consistent enforcement across applications.
Connection pooling makes this much harder because you can't enforce it
within the db using the normal methods and end up having to implement
it all over.  Instead you have to implement security before the data
hits the database.  That's a big difference and it has HUGE
ramifications for security exposure vs utility of an application.

> Saying 'that shouldn't be allowed to happen' is simply not the reality
> in many enterprise environments.  I don't see what's so complicated
> about storing who a person is and what they are allowed to do, and
> checking the permission just before that 'what' is about to get done.

No, I think the opposite.  If the RDBMS enforces security then any
client can connect and you don't have to worry about this problem at
all.  However if you push this into the RDBMS and use native
facilities, then you can't use connection pooling.
>

>
> well, not exactly.  it is a concession to security.  allowing
> untrusted entities to send ad hoc sql to a database is obviously not
> going to fly so it must be dealt with appropriately.  note pgbouncer
> (or node.js etc) is not defining or handling session auth, just
> playing a small role enforcement.  an auth'd application service
> requests are essentially protocol noise and I see no problem letting
> the protocol handler bounce them out.  also, whatever you happen to
> wrap your 'middleware' database is still part of the middleware.

Well, what you are actually doing here is enforcing security on a
level of abstraction away from the database.  This means that you
can't allow ad hoc queries because you can't guarantee safety.  I
don't know what you get by doing this instead of providing
interface-level security in the part of your middleware.  In fact
that's essentially what you have to do, is it not?
>
> also I think most people would not go the libpq route even though
> listen/notify allows you to create beautifully interactive systems --
> mainly due to weirdness of the whole thing and the amount of work you
> have to do to get it safe.

Why?  Each listener has to have its own connection, right?  Otherwise
there is nowhere to send the notifications to.  That connection has to
be attached to a db role.  The DB role has to have permission to some
portions of the database but not others, right?  I don't see why that
is hard to make safe.

>
> sure. that is a distinction I guess you could make.  but the real
> point is all the zillions of LOC that just stupidly bounce data around
> -- kill em with fire, i say :-D.
>
We can agree with that part.

 Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Tue, Aug 16, 2011 at 4:04 PM, Chris Travers  wrote:
> On Tue, Aug 16, 2011 at 12:31 PM, Merlin Moncure  wrote:
>

>>> Who enforces security and how?
>>
>> *) http wrapper (example node.js): check security in the wrapper.
>> presumably your application server would be keeping sessions state
>> independently of database session and would do verification on every
>> call.
>
> But here then you hare having to re-implement the whole security
> system yourself.  Does this give you a net benefit in complexity and
> performance over a standard middleware solution?  But moreover for
> this to work you have to include security information in meaningful
> tuples handed to the database, right?  I don't like this.  It sounds
> like a lot of complexity where a middleware solution for managing
> security would be far better.

/shrug.  pretty much every project I've ever worked on application
security has been ad hoc, database driven, not very complicated, and
not a performance bottleneck.  By the way, I think the opposite of
you: security information relating to application roles and actions
*should* be stored in the database (it is, after all, data) even if it
is enforced by a classic middleware.  What happens when some other
application, written by another team, connects to the database?
Saying 'that shouldn't be allowed to happen' is simply not the reality
in many enterprise environments.  I don't see what's so complicated
about storing who a person is and what they are allowed to do, and
checking the permission just before that 'what' is about to get done.

>> *) stock pgbouncer: there is essentially no strong way of checking
>> security. what we ended up doing was modifying pgbouncer to keep track
>> of the client auth and building a query whitelist.  very simple and
>> effective.  we also added in support for listen/notify.  imagine
>> interacting directly with remote agents inside the psql console and
>> being able to join client provided data to other tables in the
>> database :-).  ad hoc sql obviously can't be allowed from an untrusted
>> source.
>
> Ok, so here you are extending the connection pooler itself to provide
> traditional middleware functions rather than implementing them in the
> database itself, right?  So this important app server function is not
> implemented in the database.

well, not exactly.  it is a concession to security.  allowing
untrusted entities to send ad hoc sql to a database is obviously not
going to fly so it must be dealt with appropriately.  note pgbouncer
(or node.js etc) is not defining or handling session auth, just
playing a small role enforcement.  an auth'd application service
requests are essentially protocol noise and I see no problem letting
the protocol handler bounce them out.  also, whatever you happen to
wrap your 'middleware' database is still part of the middleware.

also I think most people would not go the libpq route even though
listen/notify allows you to create beautifully interactive systems --
mainly due to weirdness of the whole thing and the amount of work you
have to do to get it safe.

> The thing is that for small to midsize businesses, I think Pg makes a
> great centerpiece for an app server environment.  In the areas I am
> used to working in, even most larger businesses would be unlikely to
> need connection pooling.  It seems that if you get to the point where
> you need to use connection pooling you have to do what you are doing
> and implement your security no further back than the connection
> pooler. This makes the connection pooler, not PostgreSQL, the entry
> point for the application server environment.  It doesn't mean that Pg
> isn't taking on some of the load that middleware normally does at that
> point but it is no longer capable of *being* the middleware.

sure. that is a distinction I guess you could make.  but the real
point is all the zillions of LOC that just stupidly bounce data around
-- kill em with fire, i say :-D.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Tue, Aug 16, 2011 at 12:31 PM, Merlin Moncure  wrote:

>>>
>> Who enforces security and how?
>
> *) http wrapper (example node.js): check security in the wrapper.
> presumably your application server would be keeping sessions state
> independently of database session and would do verification on every
> call.

But here then you hare having to re-implement the whole security
system yourself.  Does this give you a net benefit in complexity and
performance over a standard middleware solution?  But moreover for
this to work you have to include security information in meaningful
tuples handed to the database, right?  I don't like this.  It sounds
like a lot of complexity where a middleware solution for managing
security would be far better.

> *) stock pgbouncer: there is essentially no strong way of checking
> security. what we ended up doing was modifying pgbouncer to keep track
> of the client auth and building a query whitelist.  very simple and
> effective.  we also added in support for listen/notify.  imagine
> interacting directly with remote agents inside the psql console and
> being able to join client provided data to other tables in the
> database :-).  ad hoc sql obviously can't be allowed from an untrusted
> source.

Ok, so here you are extending the connection pooler itself to provide
traditional middleware functions rather than implementing them in the
database itself, right?  So this important app server function is not
implemented in the database.

The thing is that for small to midsize businesses, I think Pg makes a
great centerpiece for an app server environment.  In the areas I am
used to working in, even most larger businesses would be unlikely to
need connection pooling.  It seems that if you get to the point where
you need to use connection pooling you have to do what you are doing
and implement your security no further back than the connection
pooler. This makes the connection pooler, not PostgreSQL, the entry
point for the application server environment.  It doesn't mean that Pg
isn't taking on some of the load that middleware normally does at that
point but it is no longer capable of *being* the middleware.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [] Using Postgresql as application server

[Steve Atkins]

On Aug 16, 2011, at 11:42 AM, John R Pierce wrote:

> 
> this whole discussion reminds me of the old adage...
> 
>if your only tool is a hammer, every problem looks like a nail.

I'm amazed nobody has mentioned http://www.sqlonrails.org/ yet.

Cheers,
  Steve


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Tue, Aug 16, 2011 at 1:47 PM, Chris Travers  wrote:
> On Tue, Aug 16, 2011 at 11:08 AM, Merlin Moncure  wrote:
>
>> why not?  if you are serving http, just put thin connection pooler in
>> your http server (node.js would be great for that).  if you are
>> serving libpq directly, you can pool with pgbouncer.
>>
> Who enforces security and how?

*) http wrapper (example node.js): check security in the wrapper.
presumably your application server would be keeping sessions state
independently of database session and would do verification on every
call.
*) stock pgbouncer: there is essentially no strong way of checking
security. what we ended up doing was modifying pgbouncer to keep track
of the client auth and building a query whitelist.  very simple and
effective.  we also added in support for listen/notify.  imagine
interacting directly with remote agents inside the psql console and
being able to join client provided data to other tables in the
database :-).  ad hoc sql obviously can't be allowed from an untrusted
source.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Tue, Aug 16, 2011 at 11:08 AM, Merlin Moncure  wrote:

> why not?  if you are serving http, just put thin connection pooler in
> your http server (node.js would be great for that).  if you are
> serving libpq directly, you can pool with pgbouncer.
>
Who enforces security and how?

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] [] Using Postgresql as application server

[John R Pierce]

this whole discussion reminds me of the old adage...

if your only tool is a hammer, every problem looks like a nail.



--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Tue, Aug 16, 2011 at 12:52 AM, Chris Travers  wrote:
> On Mon, Aug 15, 2011 at 10:05 PM, Scott Marlowe  
> wrote:
>
>>
>> Yep.  Also, it's REAL easy to stick a caching layer like memcached
>> into the middle tier app layer, but nearly impossible to do so in
>> pgsql.  For large systems, this would make pg as an app server a nogo.
>>  But for small to medium sized systems that don't need caching it
>> could work out.
>>
> The other big scalability limitation for Pg as an app server is you
> really can't do connection pooling.

why not?  if you are serving http, just put thin connection pooler in
your http server (node.js would be great for that).  if you are
serving libpq directly, you can pool with pgbouncer.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 10:05 PM, Scott Marlowe  wrote:

>
> Yep.  Also, it's REAL easy to stick a caching layer like memcached
> into the middle tier app layer, but nearly impossible to do so in
> pgsql.  For large systems, this would make pg as an app server a nogo.
>  But for small to medium sized systems that don't need caching it
> could work out.
>
The other big scalability limitation for Pg as an app server is you
really can't do connection pooling.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Scott Marlowe]

On Mon, Aug 15, 2011 at 7:23 PM, Merlin Moncure  wrote:
> There are downsides too -- you lose access to the excellent middleware
> tools out there, and you are 'stuck' on postgres and need to come up
> with hard to find and expensive postgres talent.   You need to be
> prepared to blaze a path, etc etc.

Yep.  Also, it's REAL easy to stick a caching layer like memcached
into the middle tier app layer, but nearly impossible to do so in
pgsql.  For large systems, this would make pg as an app server a nogo.
 But for small to medium sized systems that don't need caching it
could work out.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Darren Duncan]

Chris Travers wrote:

On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan  wrote:

I believe we basically have all the foundation already, with maybe
procedures executable outside transactions being the last major part.


Why is this desirable?  Why is it more desirable than actually using
the listen/notify infrastructure that exists already?


Maybe listen/notify is sufficient by itself.  I withdraw my "procedures 
executable outside transactions" comment for now, and just bring it up later if 
I can think of a specific use case that other mechanisms don't satisfy. -- 
Darren Duncan


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 5:14 PM, David Johnston  wrote:

>> This whole line is getting somewhat off-topic; we're not talking about a 
>> "computationally complete" application but simply one that can handle HTTP 
>> requests and dispatch calls to user-defined methods.  This seems like a 
>> small-enough requirement and seems to already have a solution (though I 
>> haven't looked at the provided links); whether you call it PostgreSQL or not 
>> is a matter of semantics (as is much of this thead).<<

Which PostgreSQL *can* do, but probably shouldn't.

>> Please restate your request in terms of benefits as opposed to checklist of 
>> cool features that barely work but, because they are present, can be added 
>> to the marketing materials.<<

The whole thing is:  Pg as an app server occupies a *different* role
than a traditional app server.  Not better or worse, just different.
Think of it as a database program which can also operate as a message
queue once operations complete successfully and committed.  That's a
pretty powerful thing with listen/notify.  And you can process data in
additional PL's as well, accessing methods from other languages so you
don't have to reinvent the wheel.

It is my view that PostgreSQL makes an *excellent* application server
as long as you use it intelligently.   That means treating the
database as the *central* application and then using it to manage not
only main data storage, but also message queues to other helper
processes which can, for example, send email, print documents, or
whatever.

This is the direction LedgerSMB is moving:  using Pg to the max but
doing so carefully and intelligently.

It is not a traditional application server, but it can easily replace
a traditional application server along with helper programs in
whatever languages one wants to write them in.  The power and
flexibility is really quite amazing.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Mon, Aug 15, 2011 at 7:06 PM, Craig Ringer  wrote:
> On 15/08/2011 10:36 PM, Merlin Moncure wrote:
>>
>> On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
>>   wrote:
>>>
>>> No, PG has never, and will never, act as an application-server.
>>
>> Why in the world not?
>
> The biggest reason is safety. Beyond that, the lack of autonomous
> transactions, stored procedures, in-DB timers, and support for any protocol
> other than the native Pg database query protocol mean it's also rather
> impractical.

well, autonomous transactions can be kludged.  beyond that though, I
agree on the sproc point but not the protocol.  it's pretty trivial to
frontend the database with a http understanding server which converts
that to libpq representation (node.js gets the nod from that point of
view).

> I guess theoretically one could embed a JVM / Python instance / whatever in
> the postmaster and have it spawn new backends for incoming connections with
> other protocols. But ... why? Why add all that complexity and - more
> importantly - contaiminate PostgreSQL's address space with more code that
> can fail when you don't have to? PLs and user C procedures are already risk
> enough as far as I'm concerned.

I don't see what's so difficult about putting thin protocol server in
front of the database.  In terms of security, good coding practices in
languages like plpgsql are well understood and there is no reason to
believe they are any more or less secure than the middleware as long
as good coding practices are followed..

> I *like* the DB being standalone. It's safer from bad code scribbling
> garbage across the heap and corrupting shared memory.

> What do you gain by trying to make Pg play appserver too?

Simple.  You eliminate the layer that traditionally sucks down most of
the coding effort and presents most of the bugs.  SQL errors and
(especially) transaction state are first class, so that you can just
let the sql engine clean everything up for you when you have an error.
 If your application is small enough so that you don't have to break
application serving into a separate machine, you can eliminate moving
data through the protocol and all the application managed caches that
plague traditional stacks.  You get to code in a more declarative
functional environment that has (in my humble experience) absolutely
proven itself in terms of defect rates.  You get to reduce your LOC by
50-80%.

There are downsides too -- you lose access to the excellent middleware
tools out there, and you are 'stuck' on postgres and need to come up
with hard to find and expensive postgres talent.   You need to be
prepared to blaze a path, etc etc.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Darren Duncan
Sent: Monday, August 15, 2011 6:48 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using Postgresql as application server

David Johnston wrote:
>>> I believe that it is ideal for Postgres to be computationally 
>>> complete in that one *could* use it to implement a complete 
>>> application.  That isn't to say one should do this as a matter of 
>>> course, good to use appropriate tools for a >>job, but that it 
>>> should at least be possible if one wanted to. -- Darren Duncan
> 
> So who wants to fund the effort to create the necessary infrastructure 
> to display a programmer-defined user interface screen (think of the "Forms"
> module in Microsoft Access)?  Or are you expecting the end-user to 
> open up PgAdmin and type "SELECT hello_world();".  I would argue that 
> because PostgreSQL is able to talk with many languages that can create these 
> "Forms"
> (or even - through extensions - a web-browser) that such functionality 
> is NOT DESIREABLE and thus PostgreSQL would not ideally be 
> "computationally complete" by that definition.

I mean computationally complete in that Postgres is an application-level 
virtual machine within which it is technically possible to write an emulator 
for any given computationally complete language using just stock Postgres and 
stock PLs, such that say you can just feed a self-contained script to psql and 
that this script is an application capable of doing anything.  I'm not saying 
that it has to perform well but just be possible.

I certainly don't expect any interface-related higher level libraries from this 
effort, but the foundation should be there so users can create their own just 
by writing an installable Postgres extension consisting of PL procedures etc 
that don't need a C compiler.

I believe we basically have all the foundation already, with maybe procedures 
executable outside transactions being the last major part.

-- Darren Duncan



This whole line is getting somewhat off-topic; we're not talking about a 
"computationally complete" application but simply one that can handle HTTP 
requests and dispatch calls to user-defined methods.  This seems like a 
small-enough requirement and seems to already have a solution (though I haven't 
looked at the provided links); whether you call it PostgreSQL or not is a 
matter of semantics (as is much of this thead).

That said...

Please restate your request in terms of benefits as opposed to checklist of 
cool features that barely work but, because they are present, can be added to 
the marketing materials.

I rather cater to a user that is capable and willing to choose multiple, 
separate, best-of-breed applications as opposed to those that want "Microsoft 
Access" on steroids.  Given limited resources even software obeys the axiom 
"Jack of All Trades; Master of None".

If PostgreSQL was actually getting money for each user then maybe a focus on 
adding additional "user experience" features would be warranted; as it is the 
effort should be in making the Free Core as good as possible and encourage and 
assist a community to build tools (core augments and developer).  This is 
opposed to the "do everything ourselves" mentality that you seem to prescribe.  
While this community exists now my personal impression is that more efforts 
could be taken to make it more prominent and accessible to users.  Think of it 
as giving new users the course outline and "required reading" documents and 
then saying that they can spend the time to teach themselves the fundamentals 
or direct them to capable teachers who can help guide them.  Likely these 
teachers would be willing to provide the outline and other materials for the 
opportunity to gain new students.  PostgreSQL could then serve as an unbiased 
moderator; attempting to make sure that there is a variety of philosophies 
represented as well as to review the accuracy/tone of the materials that they 
certify.

David J.




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Craig Ringer]

On 15/08/2011 10:36 PM, Merlin Moncure wrote:

On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
  wrote:

No, PG has never, and will never, act as an application-server.

Why in the world not?


The biggest reason is safety. Beyond that, the lack of autonomous 
transactions, stored procedures, in-DB timers, and support for any 
protocol other than the native Pg database query protocol mean it's also 
rather impractical.


I guess theoretically one could embed a JVM / Python instance / whatever 
in the postmaster and have it spawn new backends for incoming 
connections with other protocols. But ... why? Why add all that 
complexity and - more importantly - contaiminate PostgreSQL's address 
space with more code that can fail when you don't have to? PLs and user 
C procedures are already risk enough as far as I'm concerned.


I *like* the DB being standalone. It's safer from bad code scribbling 
garbage across the heap and corrupting shared memory.


What do you gain by trying to make Pg play appserver too?

Now, I have nothing against keeping business logic in the DB. This makes 
a lot of sense to me, though it'd be better if it were easier to version 
DB changes. What I don't get is having the client access front-end in 
the DB too ... that just seems like asking for trouble and security 
problems.



Postgresql has certain features, in particular being able to implement
functions in any language, that make it uniquely well suited among its
peers to act as a application server platform.
Actually, I'd say Oracle's really solid Java support, in-db timers, and 
autonomous transactions makes it rather better suited.


--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 3:47 PM, Darren Duncan  wrote:


>
> I believe we basically have all the foundation already, with maybe
> procedures executable outside transactions being the last major part.
>
Why is this desirable?  Why is it more desirable than actually using
the listen/notify infrastructure that exists already?

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Darren Duncan]

David Johnston wrote:

I believe that it is ideal for Postgres to be computationally complete in
that one *could* use it to implement a complete application.  That isn't
to say one should do this as a matter of course, good to use appropriate
tools for a >>job, but that it should at least be possible if one wanted
to. -- Darren Duncan


So who wants to fund the effort to create the necessary infrastructure to
display a programmer-defined user interface screen (think of the "Forms"
module in Microsoft Access)?  Or are you expecting the end-user to open up
PgAdmin and type "SELECT hello_world();".  I would argue that because
PostgreSQL is able to talk with many languages that can create these "Forms"
(or even - through extensions - a web-browser) that such functionality is NOT
DESIREABLE and thus PostgreSQL would not ideally be "computationally
complete" by that definition.


I mean computationally complete in that Postgres is an application-level virtual 
machine within which it is technically possible to write an emulator for any 
given computationally complete language using just stock Postgres and stock PLs, 
such that say you can just feed a self-contained script to psql and that this 
script is an application capable of doing anything.  I'm not saying that it has 
to perform well but just be possible.


I certainly don't expect any interface-related higher level libraries from this 
effort, but the foundation should be there so users can create their own just by 
writing an installable Postgres extension consisting of PL procedures etc that 
don't need a C compiler.


I believe we basically have all the foundation already, with maybe procedures 
executable outside transactions being the last major part.


-- Darren Duncan


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[David Johnston]

-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Merlin Moncure
Sent: Monday, August 15, 2011 6:10 PM
To: David Johnston
Cc: Darren Duncan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using Postgresql as application server

On Mon, Aug 15, 2011 at 4:54 PM, David Johnston  wrote:
>>>
>>>I believe that it is ideal for Postgres to be computationally 
>>>complete in that one *could* use it to implement a complete 
>>>application.  That isn't to say one should do this as a matter of 
>>>course, good to use appropriate tools for a >>job, but that it should 
>>>at least be possible if one wanted to. -- Darren Duncan
>
> So who wants to fund the effort to create the necessary infrastructure to
display a programmer-defined user interface screen (think of the "Forms"
module in Microsoft Access)?  Or are you expecting the end-user to open up
PgAdmin and type "SELECT hello_world();".  I would argue that because
PostgreSQL is able to talk with many languages that can create these "Forms"
(or even - through extensions - a web-browser) that such functionality is
NOT DESIREABLE and thus PostgreSQL would not ideally be "computationally
complete" by that definition.

I've been thinking for a while about doing this: describe the interface in
tables along with some rendering code which directly accepts the request and
spits out some html.  I'd be gunning for something with the speed and ease
of development of delphi, which fwict has never been reproduced.  So you'd
have a 'form' table which you could then bind to a real table via an adapter
or some automatic scaffolding which renders the entry based on the structure
of the table.  Sitting in front of the database would probably be an ultra
thin web server like node.js which just grabs the request and hands it to
the database through a simple connection pool.

merlin

--

A more powerful (and complex) solution would be to integrate some form of
"Templating" solution  (e.g., Velocity) and require that the interface
definitions be templates that generate valid HTML upon processing (since
your solution already requires an external web browser anyway).  Hstore and
Cursors (with a named composite type) could be used to feed data into the
template.

David J.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Mon, Aug 15, 2011 at 4:54 PM, David Johnston  wrote:
>>>
>>>I believe that it is ideal for Postgres to be computationally complete in 
>>>that one *could* use it to implement a complete application.  That isn't to 
>>>say one should do this as a matter of course, good to use appropriate tools 
>>>for a >>job, but that it should at least be possible if one wanted to. -- 
>>>Darren Duncan
>
> So who wants to fund the effort to create the necessary infrastructure to 
> display a programmer-defined user interface screen (think of the "Forms" 
> module in Microsoft Access)?  Or are you expecting the end-user to open up 
> PgAdmin and type "SELECT hello_world();".  I would argue that because 
> PostgreSQL is able to talk with many languages that can create these "Forms" 
> (or even - through extensions - a web-browser) that such functionality is NOT 
> DESIREABLE and thus PostgreSQL would not ideally be "computationally 
> complete" by that definition.

I've been thinking for a while about doing this: describe the
interface in tables along with some rendering code which directly
accepts the request and spits out some html.  I'd be gunning for
something with the speed and ease of development of delphi, which
fwict has never been reproduced.  So you'd have a 'form' table which
you could then bind to a real table via an adapter or some automatic
scaffolding which renders the entry based on the structure of the
table.  Sitting in front of the database would probably be an ultra
thin web server like node.js which just grabs the request and hands it
to the database through a simple connection pool.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[David Johnston]

>>
>>I believe that it is ideal for Postgres to be computationally complete in 
>>that one *could* use it to implement a complete application.  That isn't to 
>>say one should do this as a matter of course, good to use appropriate tools 
>>for a >>job, but that it should at least be possible if one wanted to. -- 
>>Darren Duncan

So who wants to fund the effort to create the necessary infrastructure to 
display a programmer-defined user interface screen (think of the "Forms" module 
in Microsoft Access)?  Or are you expecting the end-user to open up PgAdmin and 
type "SELECT hello_world();".  I would argue that because PostgreSQL is able to 
talk with many languages that can create these "Forms" (or even - through 
extensions - a web-browser) that such functionality is NOT DESIREABLE and thus 
PostgreSQL would not ideally be "computationally complete" by that definition.

David J.









-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 2:05 PM, Darren Duncan  wrote:

> 1.  Try using multiple processes.  You can have a separate process, in a
> distinct transactional context, for sending the emails, and it only does so
> under certain conditions, such as if it sees that a committed change has put
> the processed change over 50K.  The process performing the order that might
> get rolled back wouldn't send the email itself, though it might explicitly
> notify the other process, if the other can't tell that something happened by
> itself. Its not like everything has to be a single process.

This is the "obvious workaround" and not directly from within PostgreSQL.

The helper process cannot be a Pg function because it can't commit
it's own deletion from the queue.
>
> 2.  The computational completeness I'm speaking of what I see as an ideal
> here.  Some of that computational completeness may currently be missing but
> could be added later.  Or it may already exist.

But given the ability to do all this through helper processes and
LISTEN/NOTIFY, is that even really needed?  If so, why?  Just because
PostgreSQL may not be computationally complete in this regard, that
doesn't mean that it can't be part of a computationally complete
system, right?

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Darren Duncan]

Chris Travers wrote:

On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan  wrote:

I believe that it is ideal for Postgres to be computationally complete in
that one *could* use it to implement a complete application.  That isn't to
say one should do this as a matter of course, good to use appropriate tools
for a job, but that it should at least be possible if one wanted to. --


I think the limit is actually transactional control.

So, suppose I manage inventory

I want an email to go out to the ordering manager when the quantity I
have of an item drops below the re-order point.  I also want this
email NOT to go out if the transaction rolls back.  (Wait, the order
of 5 widgets I just processed rolled back because it isn't to a
valid customer!  We normally only sell 5 per year anyway.  No need
for the email.)

1)  I don't see how this is possible directly from within PostgreSQL
2)  Given the obvious ways around this, I don't see why this is
desirable to add to PostgreSQL.


I have 2 answers to this:

1.  Try using multiple processes.  You can have a separate process, in a 
distinct transactional context, for sending the emails, and it only does so 
under certain conditions, such as if it sees that a committed change has put the 
processed change over 50K.  The process performing the order that might get 
rolled back wouldn't send the email itself, though it might explicitly notify 
the other process, if the other can't tell that something happened by itself. 
Its not like everything has to be a single process.


2.  The computational completeness I'm speaking of what I see as an ideal here. 
 Some of that computational completeness may currently be missing but could be 
added later.  Or it may already exist.


-- Darren Duncan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Mon, Aug 15, 2011 at 3:50 PM, Chris Travers  wrote:
> On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan  
> wrote:
>
>> I believe that it is ideal for Postgres to be computationally complete in
>> that one *could* use it to implement a complete application.  That isn't to
>> say one should do this as a matter of course, good to use appropriate tools
>> for a job, but that it should at least be possible if one wanted to. --
>
> I think the limit is actually transactional control.

100% agree.  until we get stored procedures with manual transaction
control you have to involve an external agent.  for web serving
backend though this isn't a big deal because you have to involve the
browser anyways and your request liftetime is generally short enough
to keep in a database transaction.

> So, suppose I manage inventory
>
> I want an email to go out to the ordering manager when the quantity I
> have of an item drops below the re-order point.  I also want this
> email NOT to go out if the transaction rolls back.  (Wait, the order
> of 5 widgets I just processed rolled back because it isn't to a
> valid customer!  We normally only sell 5 per year anyway.  No need
> for the email.)
>
> 1)  I don't see how this is possible directly from within PostgreSQL
> 2)  Given the obvious ways around this, I don't see why this is
> desirable to add to PostgreSQL.

With a stored procedure, you could just listen for notifications
(which are not delivered if the transaction rolls back) indefinitely
and/or monitor a queue table.  In lieu of that, just cron it up.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 1:44 PM, Darren Duncan  wrote:

> I believe that it is ideal for Postgres to be computationally complete in
> that one *could* use it to implement a complete application.  That isn't to
> say one should do this as a matter of course, good to use appropriate tools
> for a job, but that it should at least be possible if one wanted to. --

I think the limit is actually transactional control.

So, suppose I manage inventory

I want an email to go out to the ordering manager when the quantity I
have of an item drops below the re-order point.  I also want this
email NOT to go out if the transaction rolls back.  (Wait, the order
of 5 widgets I just processed rolled back because it isn't to a
valid customer!  We normally only sell 5 per year anyway.  No need
for the email.)

1)  I don't see how this is possible directly from within PostgreSQL
2)  Given the obvious ways around this, I don't see why this is
desirable to add to PostgreSQL.

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Darren Duncan]

c k wrote:

Dear Postgres users,
from last few months I am reading and searching for can postgresql used 
as application server? As postgresql supports many languages like 
pl/perl, pl/python etc, supports dblink like functions to connect to 
other postgresql servers and now features are in development to use 
external data. Postgresql works well on many operating systems and has a 
stable and good quality code. As many users are using plpython or plperl 
to work on many types of data and to implement logic that can be useful 
in web application management.

So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web 
application in other servers.
4. data and logic/processing can be separated. One postgresql can be 
used as application server and another as database.

5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni


I believe that it is ideal for Postgres to be computationally complete in that 
one *could* use it to implement a complete application.  That isn't to say one 
should do this as a matter of course, good to use appropriate tools for a job, 
but that it should at least be possible if one wanted to. -- Darren Duncan




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Mon, Aug 15, 2011 at 9:50 AM, Andreas Joseph Krogh
 wrote:
> På mandag 15. august 2011 kl 16:36:23 skrev du:
>> On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
>>  wrote:
>> > No, PG has never, and will never, act as an application-server.
>>
>> Why in the world not?  Now, it may or may not be a good idea but there
>> is no technical constraint that prevents postgresql from being used in
>> this fashion. I think it's a fine idea.
>>
>> Postgresql has certain features, in particular being able to implement
>> functions in any language, that make it uniquely well suited among its
>> peers to act as a application server platform.  Having a
>> quasi-functional front end to your middeware procedural code is very
>> powerful as is having direct access to a local data store for caching
>> and spooling purposes.  So powerful that I would argue that if
>> properly supported by tools it would be superior to many of the more
>> classic stacks in use today.  I could go down the list of reasons why
>> that's the case -- tight coupling with data, performance, emphasis on
>> transactional coding, etc.   There are downsides too, but those could
>> be mitigated with some thought and work.
>>
>> Postgres is not just a database -- it's a language hosting platform if
>> you want to use it as such.  Now, you can continue to do things as
>> you've always done (database 'here', code 'here', web server 'here'),
>> but why discourage people from trying out different things?
>>
>> merlin
>
> Sorry if I stepped on any toes here. But seriously - by my definition of 
> app-server PG is not suited at all. I strongly would discourage anyone from 
> using any RDBMS as an app-server. *IMO* it makes development, testing, 
> separation-of-consern, debugging and re-deployment a nightmare.
>
> PG has a lot of nice DB-features and I use it every day, but as an app-server 
> - no way.

Nobody's toes are getting stepped on.  Constructive debate gets people
talking and puts the issues on the table.  Your complaints are pretty
non-specific (development is a 'nightmare'...why?).  This topic
reminds me a lot of the 'is putting logic in functions a good idea?'
debate which tends to bring out strong opinions which are light on
substance.  As I've said many times this debate gets really
interesting in the specific case of PostgreSQL because the database is
so extensible with good abstractions in the right places.

An application server from my point of view is just a server that sits
between the UI (typically a browser these days) and the database.
This server manages various temporary structures like application
state and data spools coming from the 'persistence later' (the
database).  Due to heavy emphasis on the importance of putting most
code into this layer from companies like IBM and Microsoft, we've seen
an explosion of tools and libraries that can be hooked in here so
that, if you choose to do so, you can drive the entire application
stack such that the database and the browser are just appendages of
this system.  This path is rich, well traveled, and boring.  But are
we things really being done in the right way?  I obviously don't think
so...in fact I think the typical corporate IT programming dept is a
total disaster.

Many programmers, especially those who really get SQL and the
relational model tend to resist programming in this fashion because
they feel that it's over-complex, verbose (especially in java-land),
and brittle.  SQL provides a lot of strong guarantees that are
relatively easy for even a junior programmer to take advantage of like
serializability of action and automatic rollback to a known state due
to an unhandled error.  Programming around the lack of these features
could charitably be described as creating a strong justification for
the excellent debugging tools you are mentioning.

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Leif Biberg Kristensen]

On Monday 15. August 2011 16.36.23 Merlin Moncure wrote:
> Postgres is not just a database -- it's a language hosting platform if
> you want to use it as such.  Now, you can continue to do things as
> you've always done (database 'here', code 'here', web server 'here'),
> but why discourage people from trying out different things?

Somebody's probably going to do it -- for no other reason why than because you 
can.

Sometimes I'll write functions like

CREATE OR REPLACE FUNCTION dpp(INTEGER) RETURNS SETOF TEXT AS $$
SELECT '' || ss_link_expand(source_text) || ''
FROM sources
WHERE parent_id=$1
ORDER BY sort_order
$$ LANGUAGE SQL STABLE;

for dumping thext that I'll copy and paste right into a static Web page. It's 
a lot easier to do this in psql than a lot of other methods that I can think 
of.

BTW, the mentioned ss_link_expand() function will generate hyperlinks on the 
fly from a compact format stored in the database. The concept is explained 
here: 

I'm working with Postgres and PHP in tandem, and frequently write functions in 
sql or pl/pgsql that will output text directly in HTML format, mostly because 
I've found text transformation (particularly regexp_replace) in Postgres to be 
far superior to the equivalent methods of doing it in PHP.

Leif

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

On Mon, Aug 15, 2011 at 7:50 AM, Andreas Joseph Krogh
 wrote:

> Sorry if I stepped on any toes here. But seriously - by my definition of 
> app-server PG is not suited at all. I strongly would discourage anyone from 
> using any RDBMS as an app-server. *IMO* it makes development, testing, 
> separation-of-consern, debugging and re-deployment a nightmare.
>
> PG has a lot of nice DB-features and I use it every day, but as an app-server 
> - no way.
>
The key issue here IMO is what you really want in an app server, which
is why my answer is "kinda."  PostgreSQL can do, and do well, a strong
subset of what an app-server does.  It is not a reasonable replacement
for a standard middleware solution with all the bells and whistles,
however.

This being said, you could build an app server around PostgreSQL where
Pg is the entry point of the app server without a whole lot of
additional problems.  Want to send an email?  Write the data to a
queue table and NOTIFY..  Let another process pick it up and
handle it.  And the approach you have to take is different from a
traditional app server approach, largely because of the separation of
concerns issues you flag.

In general, as with any multi-tiered application (and several tiers
can exist within PostgreSQL quite well), separation of concerns is a
critical aspect of design.  I'd suggest that PostgreSQL can do at
least half of what folks usually expect from middleware by itself and
do it well, without other processes listening for notifications.  For
example, it can accept business logic calls, take the data presented,
manipulate (and store/retrieve), and send the updated data back to the
client, all while enforcing security and data integrity constraints.
Practical experience suggests that this is a solid majority of what a
middleware program is actually doing.  And it can do things better
because you don't really have to deal with all the ORM stuff that
usually ends up in that environment.

If you want to do stuff beyond that, it is really best to either do it
before hitting the database (through the client or some lightweight
middleware wrapper), or after (via helper programs and NOTIFY/LISTEN
commands).

I think using PostgreSQL as a business logic entry point is not
necessarily a bad idea, provided the application is well designed.
The idea of using it to connect to other databases however, is
generally best avoided (though there are some cases where it is
impossible to do otherwise).

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Andreas Joseph Krogh]

På mandag 15. august 2011 kl 16:36:23 skrev du:
> On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
>  wrote:
> > No, PG has never, and will never, act as an application-server.
> 
> Why in the world not?  Now, it may or may not be a good idea but there
> is no technical constraint that prevents postgresql from being used in
> this fashion. I think it's a fine idea.
> 
> Postgresql has certain features, in particular being able to implement
> functions in any language, that make it uniquely well suited among its
> peers to act as a application server platform.  Having a
> quasi-functional front end to your middeware procedural code is very
> powerful as is having direct access to a local data store for caching
> and spooling purposes.  So powerful that I would argue that if
> properly supported by tools it would be superior to many of the more
> classic stacks in use today.  I could go down the list of reasons why
> that's the case -- tight coupling with data, performance, emphasis on
> transactional coding, etc.   There are downsides too, but those could
> be mitigated with some thought and work.
> 
> Postgres is not just a database -- it's a language hosting platform if
> you want to use it as such.  Now, you can continue to do things as
> you've always done (database 'here', code 'here', web server 'here'),
> but why discourage people from trying out different things?
> 
> merlin

Sorry if I stepped on any toes here. But seriously - by my definition of 
app-server PG is not suited at all. I strongly would discourage anyone from 
using any RDBMS as an app-server. *IMO* it makes development, testing, 
separation-of-consern, debugging and re-deployment a nightmare.

PG has a lot of nice DB-features and I use it every day, but as an app-server - 
no way.

--
Andreas Joseph Krogh 
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
Org.nr: NO 981 479 076  | |
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Merlin Moncure]

On Sat, Aug 13, 2011 at 2:30 PM, Andreas Joseph Krogh
 wrote:
> No, PG has never, and will never, act as an application-server.

Why in the world not?  Now, it may or may not be a good idea but there
is no technical constraint that prevents postgresql from being used in
this fashion. I think it's a fine idea.

Postgresql has certain features, in particular being able to implement
functions in any language, that make it uniquely well suited among its
peers to act as a application server platform.  Having a
quasi-functional front end to your middeware procedural code is very
powerful as is having direct access to a local data store for caching
and spooling purposes.  So powerful that I would argue that if
properly supported by tools it would be superior to many of the more
classic stacks in use today.  I could go down the list of reasons why
that's the case -- tight coupling with data, performance, emphasis on
transactional coding, etc.   There are downsides too, but those could
be mitigated with some thought and work.

Postgres is not just a database -- it's a language hosting platform if
you want to use it as such.  Now, you can continue to do things as
you've always done (database 'here', code 'here', web server 'here'),
but why discourage people from trying out different things?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Gavin Flower]

On 14/08/11 05:12, David Johnston wrote:


On Aug 13, 2011, at 11:57, c k  wrote:


Dear Postgres users,
from last few months I am reading and searching for can postgresql used as 
application server? As postgresql supports many languages like pl/perl, 
pl/python etc, supports dblink like functions to connect to other postgresql 
servers and now features are in development to use external data. Postgresql 
works well on many operating systems and has a stable and good quality code. As 
many users are using plpython or plperl to work on many types of data and to 
implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web application 
in other servers.
4. data and logic/processing can be separated. One postgresql can be used as 
application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web 
browser and outputs in HTML using only PostgreSQL.  IF you can actually do that simple 
task you will then be in a better position to decide if such an architecture is worth 
expanding upon.

The better question to ask is why wouldn't you want to use something like 
Tomcat or Apache+Programming Language?

David J.




Hi,

I see from other posts that you are using Python (A lovely language, but 
one that does not scale well for my own purposes -- unfortunately) so 
this is most likely not relevant to your current situation.


However, for those people who would consider Java, then consider using 
JBoss 7, it is very fast and will support very sophisticated web front 
ends while allowing very powerful logic written in Java, and can easily 
be configured to use PostgreSQL.


The combination of JBoss http://www.jboss.org/overview.html and JEE 
(Java Enterprise Edition), is well suited to large projects that can 
benefit from multi-core processors and massive amounts of RAM. My own 
humble development machine has a mere 4 cores and 8GB, but production 
machines can easily use a terrabyte of memory and many more cores. 
Eclipse is an IDE that is well suited for developing applications in 
Java, and can be configured to develop applications for JBoss.



Cheers,
Gavin

I have written a script to install JBoss 7 (a lot easier than for JBoss 
4 and earlier!) and convert it to use PostgreSQL 9.1 beta3. This I am 
using to revise my JEE knowledge and get to grips with version 6 of JEE.

Re: [GENERAL] Using Postgresql as application server

[Greg Williamson]

>
>Dear Postgres users,



How about sending these to just one mailing list -- when you cross post 
everybody gets two copies of each response.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Chris Travers]

Hi CK:

First, it depends on what you mean by an application server.  There
are people who in fact do very similar things with PostgreSQL,
essentially having it take on roles traditionally served by
middleware.

On Sat, Aug 13, 2011 at 8:57 AM, c k  wrote:
> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used as
> application server?

Kinda.

> As postgresql supports many languages like pl/perl,
> pl/python etc, supports dblink like functions to connect to other postgresql
> servers and now features are in development to use external data. Postgresql
> works well on many operating systems and has a stable and good quality code.
> As many users are using plpython or plperl to work on many types of data and
> to implement logic that can be useful in web application management.
> So i am thinking if I can use postgresql as web application server.
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.

> 2. can connect to other postgresql servers or databases

Best used sparingly.

> 3. it is easy to manage stored procs or triggers than managing web
> application in other servers.

I agree, but

> 4. data and logic/processing can be separated. One postgresql can be used as
> application server and another as database.

Bad idea.  You don't really gain anything except complexity and
headache by trying to separate like this.

> 5. stable, mature and open codebase.

Probably better than give some general feedback I will share how we do
this with LedgerSMB.  Some of our deployments using this approach are
decent-sized.

1:  Thus far all our stored procs are all in SQL and PLPGSQL.  We do
not currently use PL/Perl or PL/Python, or any other stored procedure
language.

2:  We pay close attention to semantics in order to ensure, to the
extent possible, that catalog data allows for discovery of stored
procedure interfaces.  We then do a lot of query building in the
"client" app (a web app) to discover these interfaces and call them
properly.  We do not include SQL code in most perl modules.  Instead
virtually all calls go through a generic discovery interface.

3:  All logic required to store, retrieve, or present (to the
application) the data goes through these stored procedures.

4:  The web app is a fairly thin Perl glue that binds HTML templates
written in Template Toolkit to these stored procedures.  It's rare to
find Perl functions more than about 5-10 lines long and when that
happens most of the logic is usually taking data and putting it into a
tabular structure for a report template.

This could be done with a desktop app as well.  The primary business
logic and security is thus reusable between applications, making
PostgreSQL essentially a middleware server.

Observations from my experience:  Design is critical and difficult.
There aren't a lot of people doing this sort of thing and so there is
a LOT of trial and error.  Also, it is quite possible to do a heck of
a lot in this area in SQL and PLPGSQL.  Focus on writing unified,
maintainable queries and semantically meaningful interfaces (argument
names, function names, etc).

Best Wishes,
Chris Travers

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Using Postgresql as application server

[Andreas Joseph Krogh]

On 08/13/2011 05:57 PM, c k wrote:
> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used
as application server? As postgresql supports many languages like
pl/perl, pl/python etc, supports dblink like functions to connect to
other postgresql servers and now features are in development to use
external data. Postgresql works well on many operating systems and has a
stable and good quality code. As many users are using plpython or plperl
to work on many types of data and to implement logic that can be useful
in web application management.
> So i am thinking if I can use postgresql as web application server.
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.
> 2. can connect to other postgresql servers or databases
> 3. it is easy to manage stored procs or triggers than managing web
application in other servers.
> 4. data and logic/processing can be separated. One postgresql can be
used as application server and another as database.
> 5. stable, mature and open codebase.
>
> I request to users and developers give your suggestions and opinions.
> Waiting for your replies.
>
> Thanks and regards,
>
> Chaitanya Kulkarni

No, PG has never, and will never, act as an application-server. Are you
really sure you need a full-blown application-server?

If you're familiar with Scala (a language which runs on the JVM) I
really recommend Lift (www.liftweb.net) with PostgreSQL. Definitely the
best. Although Lift has a history of having a steep learning-curve I
always favour maintainability, type-safety and robustness over RAD and
time-to-market. Having said that I honestly think a skilled Lift team is
at least 2x more productive than with any other web-based framework for
building modern UI.

I'm about to post a Lift+JPA+Spring+PostgreSQL example soon which
demonstrates how to use Lift with Spring+JPA+XA(2 phase commit, aka.
distributed transactions). Follow the mailing-list and you'll see it
there soon (I hope): http://groups.google.com/group/liftweb

-- 
Andreas Joseph Krogh 
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Rosenholmveien 25   | know how to do a thing and to watch |
1414 Trollåsen  | somebody else doing it wrong, without   |
NORWAY  | comment.|
Org.nr: NO 981 479 076  | |
| |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909  56 963 | |
+-+

Re: [GENERAL] Using Postgresql as application server

[David Johnston]

On Aug 13, 2011, at 11:57, c k  wrote:

> Dear Postgres users,
> from last few months I am reading and searching for can postgresql used as 
> application server? As postgresql supports many languages like pl/perl, 
> pl/python etc, supports dblink like functions to connect to other postgresql 
> servers and now features are in development to use external data. Postgresql 
> works well on many operating systems and has a stable and good quality code. 
> As many users are using plpython or plperl to work on many types of data and 
> to implement logic that can be useful in web application management. 
> So i am thinking if I can use postgresql as web application server. 
> Few points that supports this opinion:
> 1. multiple languages support for stored procs and triggers.
> 2. can connect to other postgresql servers or databases
> 3. it is easy to manage stored procs or triggers than managing web 
> application in other servers.
> 4. data and logic/processing can be separated. One postgresql can be used as 
> application server and another as database. 
> 5. stable, mature and open codebase.
> 
> I request to users and developers give your suggestions and opinions.
> Waiting for your replies.
> 
> Thanks and regards,
> 
> Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web 
browser and outputs in HTML using only PostgreSQL.  IF you can actually do that 
simple task you will then be in a better position to decide if such an 
architecture is worth expanding upon.

The better question to ask is why wouldn't you want to use something like 
Tomcat or Apache+Programming Language?

David J.





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Using Postgresql as application server

[c k]

Dear Postgres users,
from last few months I am reading and searching for can postgresql used as
application server? As postgresql supports many languages like pl/perl,
pl/python etc, supports dblink like functions to connect to other postgresql
servers and now features are in development to use external data. Postgresql
works well on many operating systems and has a stable and good quality code.
As many users are using plpython or plperl to work on many types of data and
to implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web
application in other servers.
4. data and logic/processing can be separated. One postgresql can be used as
application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni