Skip site navigation (1) Skip section navigation (2)

possible new feature: asynchronous sql or something like oracles dbms_job.submit

From: Vorarlberger <vorarlberger(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: possible new feature: asynchronous sql or something like oracles dbms_job.submit
Date: 2011-08-03 16:20:11
Message-ID: CALtUeZbLJx=5V6revfTV6txi7kM4dw=_4=jsrad3eb=GY9C18A@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
hi

i switched back from oracle xe for many reasons and because the lack of
pl/java in oralce xe. and posgesql is the best open source db i know! sadly
what i realy miss is the possibility of asynchronous sql. which means the
need to perform an sql in a completely different new session. something i
could do in oracle with dbms_job.submit.

there are may reasons for the need of this feature and not because of the
scheduler. just one actual need: assume you have a table and every time
something gets inserted you want to notify another pq-application. lets say
this table is part of your crm application. but every new inserted customer
is an interesting part of your accounting application. instead of time
scheduling or polling you could use asynchronous sql. simply add a trigger
on your customer table and execute the add_customer_to_accounting pgsql
interface in its own session. if it fails it is not the fault of the crm
application and should not raise a exception and rollback. and of course
triggering an interface for a foreign application should not be in the
native transaction. also your crm application do not want to wait on every
insert for a interface to complete.

before someone is going to say this is a q. no its not realy a q - because
you do not need a consumer daemon. there is absolutely no need for an
additional daemon. you simply trigger a procedure and send it to the
background like you would do in a unix shell. you can also use dbms_jobs for
parallel execution or to recalculate material views.

so what i would like to ask you if you can feel comfortable with this idea
and possible implement this into postgres 10.x?

thanks and cheers
chris

Responses

pgsql-hackers by date

Next:From: Florian PflugDate: 2011-08-03 16:27:28
Subject: Re: WIP fix proposal for bug #6123
Previous:From: Tom LaneDate: 2011-08-03 16:19:44
Subject: Re: Transient plans versus the SPI API

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group