autonomous commit?
Lee
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
Mladen Gogala
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.
Laurenz Albe
>> 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.
>
> 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.
... 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
Lee
> Mladen Gogala wrote:
>
>>>Can I write a stored procedure which does an automonous commit?
>>
>>In Postgres, it is not possible to do an autonomous commit using "PRAGMA"
>>statement.
you would send the SQL to an
>>asynchronous process which would execute the transaction and return the
>>results.
>
> ... 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;
>
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?
Thomas Kellerer
> 1. Submitting a job to background, to be run in the future (at time
> specified by the call, OR immediately)
I think pgAgent is very similar to Oracle's dbms_job functionality.
http://www.pgadmin.org/docs/1.4/pgagent.html
> 2. Sending messages (data, arguments, pointer to data, whatever) to a
> different session via in internal queuing system.
I guess LISTEN/NOTIFY is be what you are looking for:
http://www.postgresql.org/docs/current/static/sql-listen.html
http://www.postgresql.org/docs/current/static/sql-notify.html
Thomas
Lee
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?
Lee
> Lee, 17.12.2009 16:38:
>
>> 1. Submitting a job to background, to be run in the future (at time
>> specified by the call, OR immediately)
>
>
> I think pgAgent is very similar to Oracle's dbms_job functionality.
> http://www.pgadmin.org/docs/1.4/pgagent.html
>
>
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.
Thomas Kellerer
> 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?
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/static/functions-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/Oracle_functionality_%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/current/static/plperl-trusted.html
PL/Python seems to be untrusted always:
http://www.postgresql.org/docs/current/static/plpython.html
And if you are adventorous you can even use shell scripting for your stored procedures:
http://pgfoundry.org/projects/plsh/
Thomas
Mladen Gogala
> 1. Submitting a job to background, to be run in the future (at time
> specified by the call, OR immediately)
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.
>
> 2. Sending messages (data, arguments, pointer to data, whatever) to a
> different session via in internal queuing system.
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).
Laurenz Albe
>>> 1. Submitting a job to background, to be run in the future (at time
>>> specified by the call, OR immediately)
>>
>> I think pgAgent is very similar to Oracle's dbms_job functionality.
>> http://www.pgadmin.org/docs/1.4/pgagent.html
>
> dynamically from inside a stored procedure.
If you are bent on doing that, you can
a) use pgAgent and write a function that adds the job to its
metadata tables (via dblink to the database "postgres"),
b) write a PL/PerlU function that adds things to crontab or similar.
>>> 2. Sending messages (data, arguments, pointer to data, whatever) to a
>>> different session via in internal queuing system.
>>
>>
>> I guess LISTEN/NOTIFY is be what you are looking for:
>
> Hmmmmm.....That's an interesting feature, but [...]
>
> So I cant simulate an autonomous commit that way.
You usually get better answers if you state right away what your
goal is...
Frequently, if you ask a question like "how can I mimic Oracle
feature XXX in PostgreSQL", you will get an answer like "why
would you want to do that?" because things might be done differently
in other database systems.
Autonomous commit: what's wrong with sending a COMMIT statement
via dblink? If I seem to misunderstand your request, maybe you can
help by elaborating a little more.
On a more general side, have you taken a look at EnterpriseDB?
They cater specifically for people who want a database that is as
much like Oracle as possible for a PostgreSQL-based DBMS, but cheaper.
Yours,
Laurenz Albe
Jasen Betts
> Thomas Kellerer wrote:
>> Lee, 17.12.2009 16:38:
>>
>>> 1. Submitting a job to background, to be run in the future (at time
>>> specified by the call, OR immediately)
>>
>>
>> I think pgAgent is very similar to Oracle's dbms_job functionality.
>> http://www.pgadmin.org/docs/1.4/pgagent.html
>>
>>
>>> 2. Sending messages (data, arguments, pointer to data, whatever) to a
>>> different session via in internal queuing system.
>>
>>
>> I guess LISTEN/NOTIFY is be what you are looking for:
>> http://www.postgresql.org/docs/current/static/sql-listen.html
>> http://www.postgresql.org/docs/current/static/sql-notify.html
>>
>>
>> Thomas
> Hmmmmm.....That's an interesting feature, but it doesnt seem to be able
> 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.
that's right suppose the sender was rolled back after the receiver
committed. attempting to undo that was AIUI considered too hard.
> So I cant simulate an autonomous commit that way.
what is your ultimate goal. perhaps you can get there by using
multiple connections to the database, checkpoints, and/or try-catch
> Can I write to a file (a plain old operating system file, that is) from
> postgres (Without a commit?)
only if you're a database superuser (or stored procedure by superuser with
SECURITY DEFINER).
> Can I read an operating system file from a postgres stored procedure?
the same conditions apply, I'm not sure that there's a simple way to
programatically access the file contents.
Coniglio Sgabbiato
> Very nice, but not what I'm looking for. Unless I'm misreading the
> 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.
[OT] Some hints, I made nothing similar yet, so I cannot help you about it:
http://en.wikipedia.org/wiki/Comet_%28programming%29
http://search.cpan.org/~turnstep/DBD-Pg-2.16.0/Pg.pm#Asynchronous_Queries
http://search.cpan.org/~turnstep/DBD-Pg-2.16.0/Pg.pm#pg_notifies
or:
http://en.wikipedia.org/wiki/Comet_%28programming%29
http://nodejs.org/
http://wiki.github.com/ry/node
http://github.com/creationix/postgres-js
Lee
I have been advised, and it sounds like a good call to me, that instead
of asking "How can I simulate this or that Oracle feature", It would be
better to ask "How can I acheive this or that goal using Postgres"
So here goes:
There are two things I'ld like to be able to do with a postgres database:
I). Assume there is a fixed repteoire of (parameterized) reports that a
web user might request.
I want a stored procedure to look at the name of the report and possibly
the value of its parameters so as to decide whether
a) The report can be generated in sub-second time, in which case it is
generated and displayed to the end user, OR
b) If not, then I want the stored procedure to submit the report (and
its parameters) to be run asap in background.
Part of the mechanism of "canned" report would be to place progress
indications ("requested","cooking","done") and possibly other
information (Such as how long it took to run or at least what the wall
clock time was when it started or finished (or both).
II). I'ld like to create a debugging/logging tool (one or more stored
procedures and supporting database objects) that can write messages
specified from inside a stored procedure under test and containing both
static text and the values of run time variables, which would be
inserted into a database table and made visible to other sessions,
regardless of whether the session from which the message originated did
a commit or not.
Can we do I, or II at all, and if so, what are some good ways (or at
least not egregiously bad) ways to fly?
I've been doing things like that in Oracle, and I'm perfectly open to
the idea that we do things differently in different technical
environments. I'm not insisting that we stuff postgres or any database
into an "oracle-like" model.
Sending "long running" routines to background, and logging or tracing
messages which persist without regard to database commit actions and
which are visible the "third party" sessions, seem to be general enough
requirements that there would be "ways" to get those things done in any
technology worth its salt.
What's the postgres way?
Thanks for your patience with all this.
Mladen Gogala
> I have been advised, and it sounds like a good call to me, that instead
> of asking "How can I simulate this or that Oracle feature", It would be
> better to ask "How can I acheive this or that goal using Postgres"
Here you go, young Grasshoppa!
Jasen Betts
> Lee wrote:
>> 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
>
> I have been advised, and it sounds like a good call to me, that instead
> of asking "How can I simulate this or that Oracle feature", It would be
> better to ask "How can I acheive this or that goal using Postgres"
>
>
> So here goes:
>
> There are two things I'ld like to be able to do with a postgres database:
>
> I). Assume there is a fixed repteoire of (parameterized) reports that a
> web user might request.
>
> I want a stored procedure to look at the name of the report and possibly
> the value of its parameters so as to decide whether
> a) The report can be generated in sub-second time, in which case it is
> generated and displayed to the end user, OR
> b) If not, then I want the stored procedure to submit the report (and
> its parameters) to be run asap in background.
run the report asynchronously
notices don't wait for commit, you can use "RAISE DEBUG" (or "RAISE
NOTICE" etc...) to pass messages out of a running stored procedure.
theses mmessages are visible to the caller (if correctly configured)
and may also be logged by the logging subsystem.
the caller needs to be running the procedure asynchronously to see the
messages before the result-set is returned.
> II). I'ld like to create a debugging/logging tool (one or more stored
> procedures and supporting database objects) that can write messages
> specified from inside a stored procedure under test and containing both
> static text and the values of run time variables, which would be
> inserted into a database table and made visible to other sessions,
> regardless of whether the session from which the message originated did
> a commit or not.
have a look at RAISE, it'll slow down your reports less than running
stuff through DBLINK to write into tables.
Lee
<SNIP>
>>
>>There are <snip> things I'ld like to be able to do with a postgres database:
>>I). Assume there is a fixed repteoire of (parameterized) reports that a
>>web user might request.
<SNIP>
>><snip> I want the stored procedure to submit the report (and
>>its parameters) to be run asap in background.
> run the report asynchronously
Yes indeed, I want to run the report asynchonously.
How can I submit the job (process? stored procedure?) to run
asynchronously from inside a running procedure? What is the command or
feature I need to invoke?
I'm sorry to be such a clueless newbie but if the shoe fits I'll have to
wear it.
> notices don't wait for commit, you can use "RAISE DEBUG" (or "RAISE
> NOTICE" etc...) to pass messages out of a running stored procedure.
> theses mmessages are visible to the caller (if correctly configured)
> and may also be logged by the logging subsystem.
>
> the caller needs to be running the procedure asynchronously to see the
> messages before the result-set is returned.
>
>
>>II). I'ld like to create a debugging/logging tool (one or more stored
>>procedures and supporting database objects) that can write messages
>>specified from inside a stored procedure under test and containing both
>>static text and the values of run time variables, which would be
>>inserted into a database table and made visible to other sessions,
>>regardless of whether the session from which the message originated did
>>a commit or not.
>
> have a look at RAISE, it'll slow down your reports less than running
> stuff through DBLINK to write into tables.
Thanks for the info on RAISE NOTICE etc. The manuals are pretty clear,
but it would have taken me forever to find (or realize that I had found)
what I'm looking for absent your help.
Matthew Woodcraft
>Lee wrote on 17.12.2009 22:59:
>> 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?
>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/static/functions-admin.html
>
> If you need to write to a file as well, you'll need to use something else.
COPY can write to a file 'out of the box', though it doesn't let you
write in an arbitrary format.
-M-
Jasen Betts
COPY ( SELECT NULL ) TO 'file' WITH NULL AS 'arbitrary text' ;
:)
Jasen Betts
> Jasen Betts wrote:
><SNIP>
>>>
>>>There are <snip> things I'ld like to be able to do with a postgres database:
>
>>>I). Assume there is a fixed repteoire of (parameterized) reports that a
>>>web user might request.
><SNIP>
>
>>><snip> I want the stored procedure to submit the report (and
>>>its parameters) to be run asap in background.
>
>
>> run the report asynchronously
>
> Yes indeed, I want to run the report asynchonously.
>
> How can I submit the job (process? stored procedure?) to run
> asynchronously from inside a running procedure? What is the command or
> feature I need to invoke?
Asynchronous Command Processing. details depend on the environment.
were it my problem I'd de diving in and writing a program in C
that uses select() to for a NOTIFY whilst idle and then when it gets the NOTIFY
pulls the report you want to run from a table and executes it
asynchrounously using PQsendQuery() ... (I'm not sure that you
actually need to do this bit asynchronously)
when it receives NOTICE callback you initialised with PQsetNoticeReceiver
it uses a second database connection to update the status information you
want updated.
> Thanks for the info on RAISE NOTICE etc. The manuals are pretty clear,
> but it would have taken me forever to find (or realize that I had found)
> what I'm looking for absent your help.
I don't think the notice processor needs to be reentrant, but I'm
fairly sure the base query won't wait for so you may need to be
mindful of the case when the report spits out status messages
faster than you can deal with them.
there's a SET command you need to run to turn on debug level messages.
SET client_min_messages to DEBUG;
watch out for race conditions
Laurenz Albe
I am not sure if Jasen's suggestions can help you much.
There is "asynchronous statement processing" in PostgreSQL, but that
only means that the client is not blocked while the server processes
the query. The client cannot use the database connection until
the response from the server is there, so you cannot do any other
database work unless you open a second connection (which is probably
not what you want to do).
Moreover, I think that the only APIs that allow you to do asynchronous
processing are the C API (libpq) and DBD::Pg (as Coniglio has stated).
My first idea for your question I) would be one or more "daemon"
processes that run on a server machine and look for entries in a
"queue" table that is filled by the Users requesting a report.
Synchronization could be done with a "status" field in the table,
like "new", "in progress" and "done". Finished reports could be
stored in a bytea column of that table. The requesting program can
check the table regularly and read and delete the row when the
report is done.
You will have to introduce some cleanup procedure for reports
whose requesting users decided to close the connection before the
report was done.
You could make the daemon process(es) update a "percent done" column
regularly.
In my experience, a report is something that does not change the
database. So I don't really see the need for "autonomous transactions"
unless you need isolation "read stability" for the reporting process.
If you need an autonomous transaction for the updates to the
queue table, let the daemon process have two connections to the
database: one for the report query, and one for the queue table
updates.
About your problem II): a NOTICE can do that, but the message will
only be sent to the client that started the function.
If you want other sessions (plural!) to read these messages,
you'll have to think of something else.
I can't think of anything simple and convincing for a full-blown
messenging system like that.
Maybe something with a table and a LISTEN/NOTIFY protocol could
be done, but you'd have to face problems like:
Which one is the next message for me?
Has everybody received that message and can I delete it?
How to identify messages from different sources?
Yours,
Laurenz Albe