autonomous commit?

#11

Lee wrote:

Quote:

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

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.

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.

Quote:

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

#12

[CUT]

Quote:

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...ronous_Queries
http://search.cpan.org/~turnstep/DBD...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

#13

Lee wrote:

Quote:

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.

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.

#14

On Fri, 18 Dec 2009 15:29:37 -0500, Lee wrote:

Quote:

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!

--
http://mgogala.byethost5.com

#15

On 2009-12-18, Lee <Lee (AT) JamToday (DOT) com> wrote:

Quote:

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.

Quote:

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.

#16

Jasen Betts wrote:
<SNIP>

Quote:

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

Quote:

snip> I want the stored procedure to submit the report (and
its parameters) to be run asap in background.

Quote:

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.

Quote:

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.

#17

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

Quote:

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/s...ons-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-

#18

On 2009-12-19, Lee <Lee (AT) JamToday (DOT) com> wrote:

Quote:

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.

Quote:

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

#19

On 2009-12-19, Matthew Woodcraft <mattheww (AT) chiark (DOT) greenend.org.uk> wrote:

Quote:

Thomas Kellerer <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:
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/s...ons-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.

COPY ( SELECT NULL ) TO 'file' WITH NULL AS 'arbitrary text' ;

#20

Jasen Betts wrote in reply to Lee:

Quote:

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.

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