Can I write a stored procedure which does an automonous commit?

I saw something on a website that claimed "you can't get there from
here" in postgresSql but then suggested calling an pl/perl routine to do
that.

Can anyone sort me out on that point?

I'm coming from Oracle where autonomous commits have been possible for
quite a while.

Thanks in advance

On Wed, 16 Dec 2009 23:03:06 -0500, Lee wrote:

In Postgres, it is not possible to do an autonomous commit using "PRAGMA"
statement. Postgres is not pragmatic, knows no pragmas. One execution
thread chan have one current transaction and that's it. Autonomous
transactions can be simulated by using more than one thread. Essentially,
you wouldn't do "autonomous transaction", you would send the SQL to an
asynchronous process which would execute the transaction and return the
results. This is most often used to make log entries in case of the
transaction failure. How would you do that, depends on your favorite
programming tool.



--
http://mgogala.byethost5.com

Mladen Gogala wrote:
.... or you use the "dblink" contrib to establish a loopback connection
inside your stored procedure, which then is its own session and
consequently its own transaction.

You can find the port, database and user name with
SELECT current_setting('port'), current_database(), current_user;

Yours,
Laurenz Albe

Laurenz Albe wrote:
you would send the SQL to an

Good to know there's at least a work-around.

While we're at it, there are two other Oracle features that may not be
present (maybe there's a work around?):

1. Submitting a job to background, to be run in the future (at time
specified by the call, OR immediately)

2. Sending messages (data, arguments, pointer to data, whatever) to a
different session via in internal queuing system.

I suppose both of the above can be simulated if we can "shell out" from
a stored procedure in postgres? Can we do that (like Oracle sql*plus
"host" command) ? A pl/perl or pl/python etc routine with "system" call
would probably do the trick, assuming its legal.

Am I smoking the funny stuff here or can we do all that?

Lee, 17.12.2009 16:38:
I think pgAgent is very similar to Oracle's dbms_job functionality.
http://www.pgadmin.org/docs/1.4/pgagent.html


I guess LISTEN/NOTIFY is be what you are looking for:
http://www.postgresql.org/docs/curre...ql-listen.html
http://www.postgresql.org/docs/curre...ql-notify.html


Thomas

Thomas Kellerer wrote:
to pass information as a parameter, unless I put the data into a table
(and commit) then have the signalled session read the table to find its
data.

It also seems that one must commit in the sender, or the signal wont be
delivered. So I cant send info to another session and have that other
session put the data into a table without first having issued a commit
in the original (sending) session.

So I cant simulate an autonomous commit that way.

Can I write to a file (a plain old operating system file, that is) from
postgres (Without a commit?)

Can I read an operating system file from a postgres stored procedure?

Thomas Kellerer wrote:
documentation, this is an external tool, not callable dynamically from
inside a stored procedure.

The idea would be for a stored procedure to set up a job and submit it
at run time based on local conditions.

I use that sort of thing (in oracle) to schedule longish reports from a
request taken from a web site user. If the report is going to take a
long time
(more than a second) we dont want the user waiting around for the
output, so we submit a backgrand job and let the user carry on with
something else while the report chugs away in the background. There's a
separate mechanism for allowing the user to check on whether anything is
"cooking" and which if any are ready to eat.

Lee wrote on 17.12.2009 22:59:
There are various solutions for this.

Right out of the box you can use the built-in function to read a file:
http://www.postgresql.org/docs/8.4/s...ons-admin.html


If you need to write to a file as well, you'll need to use something else.


Orafce implements a bunch Oracle utility functions
http://pgfoundry.org/forum/forum.php?forum_id=1579
http://www.postgres.cz/index.php/Ora...ality_%28en%29

I includes utl_file as far as I can tell

Then there is PL/Perl or PL/Pyhton:

If PL/Perl is installed as untrusted then I think you have full access to the file system.
http://www.postgresql.org/docs/curre...l-trusted.html

PL/Python seems to be untrusted always:
http://www.postgresql.org/docs/curre.../plpython.html

And if you are adventorous you can even use shell scripting for your stored procedures:
http://pgfoundry.org/projects/plsh/

Thomas

On Thu, 17 Dec 2009 10:38:49 -0500, Lee wrote:


Why would you want to do that? I find crontab easier to configure and
document. If you need a GUI interface, there are tools like UFRaw and
Spring Batch which can do everything that the database can do.

ActiveMQ messaging middleware by the Apache foundation supports
PostgreSQL as back-store. If you decide to use plperlu (note the "u" at
the end, it's an untrusted version which I love and trust), you can use
Net::Stomp module and send messages.

Perl can also do host commands easily, although I've never understood
what is that good for. If a database procedure needs to execute something
in the OS shell on the DB server, there is something wrong in the
application design.

The power of PostgreSQL lies in its ability to be easily extended. One of
the possibilities is to use Perl, an incredibly powerful language with
thousands of modules on CPAN. I can create a PostgreSQL function which
connects to Oracle RDBMS and returns a set of record. The net effect will
be the ability to select directly from Oracle, as if there was a DB link.

Postgres is not Oracle, so you cannot use the same application design or
the same logic in the database procedures. PostgreSQL cursors are nowhere
near as powerful as Oracle's. PostgreSQL has local temporary tables
instead. Those here who have worked with MS SQL can tell you how useful
those are. Oracle has RAC, STREAMS, DBMS_AQ, DBMS_SCHEDULER and many
other goodies which PostgreSQL simply does not have. Some of those things
can be easily replaced. Slony is, at least in my opinion, far superior to
STREAMS in master-slave situations, ActiveMQ is pretty decent replacement
for DBMS_AQ and cron and at commands can do most of the things that
DBMS_SCHEDULER can do. There is no RAC replacement. One database cannot
be opened from two machines simultaneously. You can have an ative-passive
cluster, be it by using Slony or some clustering solution in which the
database will be brought up automatically on the spare node, should it go
down on the primary node, but that's not RAC. You will have to carve your
database logically into pieces and then configure PgPool or GridSQL to
carve up your queries. That will give you the speed and scalability of
RAC but at the cost of human labor to support and maintain such database.
That can be done. Skype is running billing on PostgreSQL and they're doing
just fine, despite having tens of millions of records. Postgres is not
Oracle and you should not expect to have a seamless expdp/impdp migration
of the application software. You will have to work to do it, but at least
in my company, the reward is more then $120k saved on oracle installations
annually. Oracle EE with RAC & partitioning: $40,000 per CPU. Diagnostic
and tuning pack, $5,000 per CPU, each. The expression on the face of your
Oracle rep when you tell him that you moved half of your company
databases to Postgres - priceless. There are some things that money can't
buy for everything else, there is MasterCard(TM).



--
http://mgogala.byethost5.com

On 2009-12-17, Lee <leeh (AT) panix (DOT) com> wrote:
that's right suppose the sender was rolled back after the receiver
committed. attempting to undo that was AIUI considered too hard.

what is your ultimate goal. perhaps you can get there by using
multiple connections to the database, checkpoints, and/or try-catch

only if you're a database superuser (or stored procedure by superuser with
SECURITY DEFINER).

the same conditions apply, I'm not sure that there's a simple way to
programatically access the file contents.