Thread: Problem creating stored procedure

Try
CREATE FUNCTION .....



On Tuesday 27 December 2005 09:41, Ted Byers wrote:
> I am puzzled.  Can ayone explain why I get an error from Postgres on this
> simple stored procedure?
>
> The following is from the pgAdmin III History window:
> -- Executing query:
> CREATE PROCEDURE addEntity (
>  fn IN VARCHAR,
>  ln IN VARCHAR,
>  ivar IN VARCHAR,
>  hi IN VARCHAR,
>  pw IN VARCHAR,
>  ea IN VARCHAR,
>  ad IN VARCHAR,
>  c IN VARCHAR,
>  p IN VARCHAR,
>  co IN VARCHAR,
>  pc IN VARCHAR
>  )
> AS
> DECLARE
>  varID INTEGER
> BEGIN
>  SELECT int varID uid from uids where email_address=ea;
>  IF varID IS NOT NULL THEN
>   INSERT INTO addy (uid,address,city,province,country,postal_code)
>    VALUES (varID,ad,c,p,co,pc)
>  ELSE
>   INSERT INTO uids(family_name,first_name,initials,hid,pword,email_address)
>    VALUES (ln,fn,ivar,hi,pw,ea)
>   INSERT INTO addys(...) VALUES (currval('seq'),ad,c,p,co,pc)
>  END IF;
> END
> LANGUAGE 'sql' VOLATILE;
>
> ERROR:  syntax error at or near "PROCEDURE" at character 8
>
> Judging from the examples in the manual (around page 600), my procedure
> ought to be fine, but clearly Postgres doesn't like it.
>
>
> Thanks,
>
> Ted
>
>
> R.E. (Ted) Byers, Ph.D., Ed.D.
> R & D Decision Support Software
> http://www.randddecisionsupportsolutions.com/

--
    UC

--
Open Source Solutions 4U, LLC    1618 Kelly St
Phone:  +1 707 568 3056        Santa Rosa, CA 95401
Cell:   +1 650 302 2405        United States
Fax:    +1 707 568 6416

On 12/27/05, Ted Byers <r.ted.byers@rogers.com> wrote:
> I am puzzled.  Can ayone explain why I get an error from Postgres on this
> simple stored procedure?
>
> The following is from the pgAdmin III History window:
> -- Executing query:
> CREATE PROCEDURE addEntity (

one reason could be that PROCEDURE's doesn't exist in postgres... you
have to create a FUNCTION...

>  fn IN VARCHAR,
>  ln IN VARCHAR,
>  ivar IN VARCHAR,
>  hi IN VARCHAR,
>  pw IN VARCHAR,
>  ea IN VARCHAR,
>  ad IN VARCHAR,
>  c IN VARCHAR,
>  p IN VARCHAR,
>  co IN VARCHAR,
>  pc IN VARCHAR

i think it's [IN|OUT|INOUT] var_name datatype... note the order...

>  )
> AS

needs a $$ sign to begin function

> DECLARE
>  varID INTEGER

needs a semicolon

> BEGIN
>  SELECT int varID uid from uids where email_address=ea;

select into... note the missing "o"

>  IF varID IS NOT NULL THEN
>   INSERT INTO addy
> (uid,address,city,province,country,postal_code)
>    VALUES (varID,ad,c,p,co,pc)
>  ELSE
>   INSERT INTO
> uids(family_name,first_name,initials,hid,pword,email_address)
>    VALUES (ln,fn,ivar,hi,pw,ea)
>   INSERT INTO addys(...) VALUES (currval('seq'),ad,c,p,co,pc)
>  END IF;
> END

needs a semicolon

needs a $$ sign to end function

> LANGUAGE 'sql' VOLATILE;
>

it is not sql language but plpgsql

> ERROR:  syntax error at or near "PROCEDURE" at character 8
>
> Judging from the examples in the manual (around page 600), my procedure
> ought to be fine, but clearly Postgres doesn't like it.
>

maybe are you looking at the examples in how to convert oracle
procedures tu postgres functions? read carefully...

>
> Thanks,
>
> Ted
>
> R.E. (Ted) Byers, Ph.D., Ed.D.
> R & D Decision Support Software
> http://www.randddecisionsupportsolutions.com/


--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Tue, Dec 27, 2005 at 12:41:44PM -0500, Ted Byers wrote:
> I am puzzled.  Can ayone explain why I get an error from Postgres
> on this simple stored procedure?

There are several mistakes in the code you posted:

* PostgreSQL doesn't have a CREATE PROCEDURE command.  Use CREATE
  FUNCTION.

* You didn't declare a return type or any OUT or INOUT parameters.

* You didn't quote the function body.

* Several statements are missing terminating semicolons.

* You wrote "SELECT int" instead of "SELECT INTO".

* You wrote "INSERT INTO addys(...)" instead of providing a column
  list.  If this is the actual code then it's a syntax error, and
  if it's not the actual code then we need to see what you're
  really doing.

* You wrote plpgsql code but declared the function to be sql.

> Judging from the examples in the manual (around page 600), my
> procedure ought to be fine, but clearly Postgres doesn't like it.

What section of the manual are you looking at, and for what version
of PostgreSQL (many of us use the online documentation so page
numbers don't mean anything)?  Are you mixing Oracle syntax with
PL/pgSQL syntax?

--
Michael Fuhr

Hi there,

How can i send mail form postgresql.
which language i can use,for that some thing i have to install for postgres.

Kindly suggest.

thanx & regards

aftab

Aftab Alam wrote:
> Hi there,
>
> How can i send mail form postgresql.
> which language i can use,for that some thing i have to install for postgres.
>
> Kindly suggest.
>
> thanx & regards
>
> aftab


Check out my site at http://www.amsoftwaredesign.com

and click on the forums link, I have some examples on how to send a
email from a function using plperl, also how to connect to a simple
socket server and send messages, plus lots of other function examples,
some my own and some from the original function cookbook.


Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql

>>>>> "Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:

Tony> Check out my site at http://www.amsoftwaredesign.com

Tony> and click on the forums link, I have some examples on how to send a
Tony> email from a function using plperl, also how to connect to a simple
Tony> socket server and send messages, plus lots of other function examples,
Tony> some my own and some from the original function cookbook.

I fear for the future.

Did someone forget what a "database" is for?

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

On Tue, 2005-12-27 at 20:58 -0800, Randal L. Schwartz wrote:
> >>>>> "Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:
>
> Tony> Check out my site at http://www.amsoftwaredesign.com
>
> Tony> and click on the forums link, I have some examples on how to send a
> Tony> email from a function using plperl, also how to connect to a simple
> Tony> socket server and send messages, plus lots of other function examples,
> Tony> some my own and some from the original function cookbook.
>
> I fear for the future.
>
> Did someone forget what a "database" is for?

Hah! A few months ago, I got all excited because I made PostgreSQL
connect to an instance of DRb (distributed ruby) and make an IRC bot
talk on freenode when a trigger was called... it was a nice proof of
concept... but in the end I moved that process into the application
layer.

http://rubyurl.com/t3S (installing pl/ruby-untrusted)

http://rubyurl.com/Dz2 (using plruby and rubygems)

In any event... using Ruby from within a PostgreSQL function is a
snap... just hasn't proved itself very useful outside of allowing some
older PHP applications to use some rubygems in the database.

Robby


--
/**************************************************************
* Robby Russell, Founder & Executive Director                 *
* PLANET ARGON, LLC | www.planetargon.com                     *
* Ruby on Rails Development, Consulting, and Hosting          *
* Portland, Oregon  | p: 503.351.4730 | f: 815.642.4068       *
* blog: www.robbyonrails.com | book: www.programmingrails.com *
***************************************************************/

Randal L. Schwartz wrote:

> I fear for the future.
>
> Did someone forget what a "database" is for?
>

You have nothing to fear but fear itself....
(FDR http://historymatters.gmu.edu/d/5057/)

Let us see why this might be handy(sending a email or connecting to a
socket server).

The email function would be handy to send a notifiction of a certain
event, like a trigger.  hmm.. a 60 day notifcation trigger fires on a
financial application, some one needs to be notified.

A real world example of the socket server would be for a notification
system for Pro FTP (which has pg integeration).   A file comes in, Pro
FTP writes it to the Postgresql based log table, a trigger fires that
the log has been updated for a new file upload, the PL-perl function
then connects to the pop up notification server and sends a unicast
message to the users connected to the popup notification server.
hmm..pretty handy, you just extended Pro FTP with out having to hack the
Pro FTP source.

This is actually in use in a large corporate setting.  while this might
not be a good idea for something out on the internet because of email
latencies etc, it does work extremely well in a setting where that is
not a issue.

A database can be used for much more than simply storing data....

Tony Caduto schrieb:
> Randal L. Schwartz wrote:
>
>> I fear for the future.
>>
>> Did someone forget what a "database" is for?
>>
>
> You have nothing to fear but fear itself....
> (FDR http://historymatters.gmu.edu/d/5057/)
>
> Let us see why this might be handy(sending a email or connecting to a
> socket server).
>
> The email function would be handy to send a notifiction of a certain
> event, like a trigger.  hmm.. a 60 day notifcation trigger fires on a
> financial application, some one needs to be notified.
>
> A real world example of the socket server would be for a notification
> system for Pro FTP (which has pg integeration).   A file comes in, Pro
> FTP writes it to the Postgresql based log table, a trigger fires that
> the log has been updated for a new file upload, the PL-perl function
> then connects to the pop up notification server and sends a unicast
> message to the users connected to the popup notification server.
> hmm..pretty handy, you just extended Pro FTP with out having to hack the
> Pro FTP source.
>
> This is actually in use in a large corporate setting.  while this might
> not be a good idea for something out on the internet because of email
> latencies etc, it does work extremely well in a setting where that is
> not a issue.
>
And you are sure you can handle a few hundred thousands emails in your
incoming due to a non context aware process which is sending mail?
There is no such thing like a 60 day trigger either ;)
In short its usual better to connect with an application to the
database and handle all that asynchronously - with all the error
handling and sanetizing. You can send mail from server but you
really should not :-)
(Another hint regarding mail: if you send mail to an event and
then the transaction rolls back - how to you hold back the email?
And if sending/connection to the mta fails, do you want your
transaction roll back?)

> On Tue, 2005-12-27 at 20:58 -0800, Randal L. Schwartz wrote:
>> >>>>> "Tony" == Tony Caduto <tony.caduto@amsoftwaredesign.com> writes:
>>
>> Tony> Check out my site at http://www.amsoftwaredesign.com
>>
>> Tony> and click on the forums link, I have some examples on how to send a
>> Tony> email from a function using plperl, also how to connect to a simple
>> Tony> socket server and send messages, plus lots of other function examples,
>> Tony> some my own and some from the original function cookbook.
>>
>> I fear for the future.
>>
>> Did someone forget what a "database" is for?
>
> Hah! A few months ago, I got all excited because I made PostgreSQL
> connect to an instance of DRb (distributed ruby) and make an IRC bot
> talk on freenode when a trigger was called... it was a nice proof of
> concept... but in the end I moved that process into the application
> layer.
>
> http://rubyurl.com/t3S (installing pl/ruby-untrusted)
>
> http://rubyurl.com/Dz2 (using plruby and rubygems)
>
> In any event... using Ruby from within a PostgreSQL function is a
> snap... just hasn't proved itself very useful outside of allowing some
> older PHP applications to use some rubygems in the database.

I'd be perfectly happy having a trigger (or some such thing) that
automagically records, in a table somewhere, messages that I want to
have propagated.

That nicely fits with "what a database is for."

Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
would then grab messages from the table and submit them to [whatever
is the communications layer].

That is a clean sort of design for that sort of thing.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://linuxfinances.info/info/lisp.html
Rules of the Evil Overlord #192.  "If I appoint someone as my consort,
I will  not subsequently inform  her that she  is being replaced  by a
younger, more attractive woman.  <http://www.eviloverlord.com/>

Tino,
I am talking about corporate apps, there would never be thousands of
emails in the email servers inbound queue from just a few database
applications. (anyway a thousand emails in a Postfix queue is nothing)

Sometimes it is best to send the email right from the client app, but in
many situations sending a  email from the client application may be
blocked by corporate firewall restrictions etc, so sending the email
from the server via a trigger or function is the best bet.

The email should be the last thing that is called, and in postgresql 8.1
you can use error handling in your function if you so desire to avoid
sending a bogus email if there is a problem.

And you can code a trigger that fires when there is a difference of 60
days between dates(I have apps that do so...,and the trigger sends a email)

You have your opinion and I have mine, but mine is backed by real
applications running in a corporate environment, and the situations you
describe NEVER occur, so if some one want's to send a email from
Postgresql I don't think they should be turned back by FUD.


> And you are sure you can handle a few hundred thousands emails in your
> incoming due to a non context aware process which is sending mail?
> There is no such thing like a 60 day trigger either ;)
> In short its usual better to connect with an application to the
> database and handle all that asynchronously - with all the error
> handling and sanetizing. You can send mail from server but you
> really should not :-)
> (Another hint regarding mail: if you send mail to an event and
> then the transaction rolls back - how to you hold back the email?
> And if sending/connection to the mta fails, do you want your
> transaction roll back?)

> And you can code a trigger that fires when there is a difference of 60
> days between dates(I have apps that do so...,and the trigger sends a email)

I guess I should expand on this more, the trigger is fired via a
insert/update into a table called logons, when a user logs on to the
system a record is inserted(if it does not exist) or updated.  Once
fired the trigger function then compares dates on the accounts the user
is reviewing, if there is a 60 day difference a email is sent to
managers letting them know that the user has accounts that have not been
reviewed in the last 60 days.

The trigger is of course fired by a insert/delete or update, but the
logic inside determines if the trigger should actually do something or
just be aborted.

Tony

Tony Caduto schrieb:
> Tino,
> I am talking about corporate apps, there would never be thousands of
> emails in the email servers inbound queue from just a few database
> applications. (anyway a thousand emails in a Postfix queue is nothing)
>
> Sometimes it is best to send the email right from the client app, but in
> many situations sending a  email from the client application may be
> blocked by corporate firewall restrictions etc, so sending the email
> from the server via a trigger or function is the best bet.

Been there. Done that. My email was just about to make you
aware of the problems. You seem "Oh, sending mail from database
is a good idea, lets do that" but actually its not that easy
(but easy done with one of the pl/*u - languages)

How do you stop your server sending mail in case a transaction goes
wild and is sending mails for every updated row in a some megs table?

> The email should be the last thing that is called, and in postgresql 8.1
> you can use error handling in your function if you so desire to avoid
> sending a bogus email if there is a problem.

I'd be surprised how you can avoid sending mail in a transaction but
get it back if the transaction rolls back. Maybe with a queue? But
then - why not just do all the processing outside the database?

> And you can code a trigger that fires when there is a difference of 60
> days between dates(I have apps that do so...,and the trigger sends a email)
>
> You have your opinion and I have mine, but mine is backed by real
> applications running in a corporate environment, and the situations you
> describe NEVER occur, so if some one want's to send a email from
> Postgresql I don't think they should be turned back by FUD.

*hollow lough* No, we are all playing with toy applications ;) Get real
man, get real :)

I wrote "you can do that" but "its not recommended".

Hint: client applications can run on servers too :-)
So much for "firewall rules" and all the like.

I'd recommend a small easy controllable script which receives
the message signals thru one connection to the server and
LISTEN/NOTIFY and then checks consistency (when did I last
send similar mail etc...) and handles all the mail sending.
This frees up the server early (NOTIFY is rather cheap)
And leaves you with a simple "kill" in  case something
goes wrong with your script, w/o disturbing your database
application.

This idea has grown in the practice of a large corp.
application in case you wonder...

Nice days
Tino

Tino Wildenhain wrote:

> How do you stop your server sending mail in case a transaction goes
> wild and is sending mails for every updated row in a some megs table?
>

It would not be smart to send a email via a trigger that updates or
inserts 1000s of rows would it?  All the times I have used PL Perl to
send a email that has NEVER been the case.

I agree that for a super busy server that is running a website or
something I probably would not do it that way(most would send the email
via PHP or whatever).  BUT for a GUI client server application(not web
based) it's more than acceptable especially when the emails are being
sent internally to a internal mail server where the connection/send time
is low.  It also does not have to be called from a trigger you could
just call it from another function.

It's not that difficult to catch and handle exceptions in a 8.1 or 8.0
plpgsql function, see next text from the docs:
***********************************************************************************
36.7.5. Trapping Errors

By default, any error occurring in a PL/pgSQL function aborts execution
of the function, and indeed of the surrounding transaction as well. You
can trap errors and recover from them by using a BEGIN block with an
EXCEPTION clause. The syntax is an extension of the normal syntax for a
BEGIN block:

[ <<label>> ]
[ DECLARE
     declarations ]
BEGIN
     statements
     -- Call PL Perl sendmail function here
EXCEPTION
     WHEN condition [ OR condition ... ] THEN
         handler_statements
     [ WHEN condition [ OR condition ... ] THEN
           handler_statements
       ... ]
END;

If no error occurs, this form of block simply executes all the
statements, and then control passes to the next statement after END. But
if an error occurs within the statements, further processing of the
statements is abandoned, and control passes to the EXCEPTION list. The
list is searched for the first condition matching the error that
occurred. If a match is found, the corresponding handler_statements are
executed, and then control passes to the next statement after END. If no
match is found, the error propagates out as though the EXCEPTION clause
were not there at all: the error can be caught by an enclosing block
with EXCEPTION, or if there is none it aborts processing of the function.
*********************************************************************************************

My point is that you can send a email from a PL perl or C function and
it will work just fine.  if you use error handling in your function, the
call to the sendmail function will be skipped, it's not that difficult.


Later,

Tony

On Dec 28, 2005, at 8:28 AM, Christopher Browne wrote:

> Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
> would then grab messages from the table and submit them to [whatever
> is the communications layer].
>
> That is a clean sort of design for that sort of thing.

This is precisely how we build things.  Throw in some good locking
mechanisms and you can scale this to incredible levels.

Tony Caduto schrieb:
> Tino Wildenhain wrote:
>
>> How do you stop your server sending mail in case a transaction goes
>> wild and is sending mails for every updated row in a some megs table?
>>
>
> It would not be smart to send a email via a trigger that updates or
> inserts 1000s of rows would it?  All the times I have used PL Perl to
> send a email that has NEVER been the case.
>
> I agree that for a super busy server that is running a website or
> something I probably would not do it that way(most would send the email
> via PHP or whatever).  BUT for a GUI client server application(not web
> based) it's more than acceptable especially when the emails are being
> sent internally to a internal mail server where the connection/send time
> is low.  It also does not have to be called from a trigger you could
> just call it from another function.
>
> It's not that difficult to catch and handle exceptions in a 8.1 or 8.0

We do not talk about exceptions here. I'm talking about transactions.
And you never know who will be aborting a transaction after your
call to the function. No need for referral to the fine manuals :-)

++Tino

----- Original Message -----
From: "Vivek Khera" <vivek@khera.org>
To: "PG-General General" <pgsql-general@postgresql.org>
Sent: Wednesday, December 28, 2005 11:48 AM
Subject: Re: [GENERAL] sending mail from Postgres


>
> On Dec 28, 2005, at 8:28 AM, Christopher Browne wrote:
>
>> Some separate process (perhaps polling, perhaps using NOTIFY/LISTEN)
>> would then grab messages from the table and submit them to [whatever
>> is the communications layer].
>>
>> That is a clean sort of design for that sort of thing.
>
> This is precisely how we build things.  Throw in some good locking
> mechanisms and you can scale this to incredible levels.
>
Here is a general question relating to this.  The problem involves due
diligence related to environmental protection.  Consider a distributed
application to support this.  You have a facility with an environmental
monitoring program.  Samples are taken from soil, water and air and sent
off-site for analysis.  Each sample, when analyzed, results in a report sent
to the facility's management for their records.  However, if the
concentration of some contaminant in a sample is above some threshold, a
second report, containing the complete analysis results for the sample, is
generated and sent to one or more people, both inside and outside the
organisation (e.g. engineers within the organization responsible for fixing
problems with the facility and engineers at relevant regulatory agencies).
One objective is to automate as much of the data management as possible and
to ensure that if a problem arises everyone who needs to know about it is
notified.  The process has to be auditable, so that information about when
each step in the process starts is stored in the database, as well as
information about when messages are acknowledged (again automated - so when
an engineer opens a message about a problem, an acknowledgement is sent to
the database without his intervention).

I suppose email might work as a means of sending messages, but I was
thinking of Sun's JMS instead, working with triggers.  I could then create
my own thin client to display the reports, perhaps sorting them according to
user specified criteria.  I can see how to do it within the web tier, or
within the client tier (within the labs doing the analyses).  The thing is,
of the designs I have considered, the one involving triggers with JMS on the
main supporting website (with a database back end) is the simplest in terms
of deployment, since all interested parties could interact with the
application through the internet using a very thin client (perhaps even with
an applet within a web page) and I would not need to worry about deploying
software to all relevant people/sites.

If you faced this problem, what would you do, and why?

Cheers,

Ted
R.E. (Ted) Byers, Ph.D., Ed.D.
R & D Decision Support Software
http://www.randddecisionsupportsolutions.com/

>>>>> "Ted" == Ted Byers <r.ted.byers@rogers.com> writes:

Ted> Here is a general question relating to this.  The problem involves due
Ted> diligence related to environmental protection.  Consider a distributed
Ted> application to support this.  You have a facility with an
Ted> environmental monitoring program.  Samples are taken from soil, water
Ted> and air and sent off-site for analysis.  Each sample, when analyzed,
Ted> results in a report sent to the facility's management for their
Ted> records.  However, if the concentration of some contaminant in a
Ted> sample is above some threshold, a second report, containing the
Ted> complete analysis results for the sample, is generated and sent to one
Ted> or more people, both inside and outside the organisation
Ted> (e.g. engineers within the organization responsible for fixing
Ted> problems with the facility and engineers at relevant regulatory
Ted> agencies). One objective is to automate as much of the data management
Ted> as possible and to ensure that if a problem arises everyone who needs
Ted> to know about it is notified.  The process has to be auditable, so
Ted> that information about when each step in the process starts is stored
Ted> in the database, as well as information about when messages are
Ted> acknowledged (again automated - so when an engineer opens a message
Ted> about a problem, an acknowledgement is sent to the database without
Ted> his intervention).

Ted> I suppose email might work as a means of sending messages, but I was
Ted> thinking of Sun's JMS instead, working with triggers.  I could then
Ted> create my own thin client to display the reports, perhaps sorting them
Ted> according to user specified criteria.  I can see how to do it within
Ted> the web tier, or within the client tier (within the labs doing the
Ted> analyses).  The thing is, of the designs I have considered, the one
Ted> involving triggers with JMS on the main supporting website (with a
Ted> database back end) is the simplest in terms of deployment, since all
Ted> interested parties could interact with the application through the
Ted> internet using a very thin client (perhaps even with an applet within
Ted> a web page) and I would not need to worry about deploying software to
Ted> all relevant people/sites.

Ted> If you faced this problem, what would you do, and why?

As already proposed, I'd have a trigger noticing the exceptional
condition post a record to an audit log table (and use NOTIFY).  Then
I'd have a notification process manage watching that table, and send
the notices as needed.

That's the right level for this.  Database triggers should be about
adding rows, deleting rows, and modifying values in rows to manage
integrity.  It's the middleware that's responsible for repackaging
that or acting on changed data.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

Tino Wildenhain wrote:

>
> We do not talk about exceptions here. I'm talking about transactions.
> And you never know who will be aborting a transaction after your
> call to the function. No need for referral to the fine manuals :-)
>

Common Tino, you let users abort transactions? Who else is going to be
aborting transactions besides you the programmer?
I would never allow that as they would mess everything up.

I only abort transactions in my end user apps when the server throws a
error, I then evaluate that error and take appropriate action.
That's why having exceptions in your function would work. You are
inviting trouble letting users control when a transaction ends or starts
for that matter, they have a tendency to leave them in a non commited
state for long periods of time.

Transactions need to be short and sweet.

Sure a very long latency email might slow up a transaction for a second
or two, but like I said for a internal use corporate app, you will never
even see one second to send a email.

It all depends on your app what will work best.

There is nothing wrong with sending a email directly from a trigger or
function.  If you really need to scale there are better ways, but for 99
  percent of apps the way I suggested will work fine and dandy.

I myself, for a high volume email notifictation system would probably
use some form of a queue, but that would be severe overkill for the
majority of apps, if it's not broke don't fix it.

I guess we will have to agree to disagree :-)


Later,

Tony

Tony Caduto schrieb:
> Tino Wildenhain wrote:
>
>>
>> We do not talk about exceptions here. I'm talking about transactions.
>> And you never know who will be aborting a transaction after your
>> call to the function. No need for referral to the fine manuals :-)
>>
>
> Common Tino, you let users abort transactions? Who else is going to be
> aborting transactions besides you the programmer?
> I would never allow that as they would mess everything up.

Actually not. There are several reasons why a transaction can
abort - do you cover all your queries in functions?
This would be a little bit unusual, to say at least.
Every query can fail. Its useless to do error
catching in your app if the server actually rolled back
due to an error and yet sent the mail :-)

Just curious, what framework do you use for your frontend
applications?

Have a nice day
Tino

Yep, we try to keep all the sql in functions/procedures as it makes
updates a lot easier and keeps the business logic seperate from the
client application.
We use native compiled front ends (Delphi) for windows and Lazarus for
everything else.  When you keep everything in a function/proc it makes
it a piece of cake to tweak the business logic or update it from a dev box.

I know a lot of coders who use PHP etc put the sql right in the scripts
and that is fine since you can update it without a recompile.

If you catch the errors in you functions and recover from them you won't
need to roll anything back, you would just do a commit.

Later,

Tony


> Actually not. There are several reasons why a transaction can
> abort - do you cover all your queries in functions?
> This would be a little bit unusual, to say at least.
> Every query can fail. Its useless to do error
> catching in your app if the server actually rolled back
> due to an error and yet sent the mail :-)
>
> Just curious, what framework do you use for your frontend
> applications?
>
> Have a nice day
> Tino
>