Thread: possible new feature: asynchronous sql or something like oracles dbms_job.submit
possible new feature: asynchronous sql or something like oracles dbms_job.submit
From
Vorarlberger
Date:
hi<br /><br />i switched back from oracle xe for many reasons and because the lack of pl/java in oralce xe. and posgesqlis the best open source db i know! sadly what i realy miss is the possibility of asynchronous sql. which means theneed to perform an sql in a completely different new session. something i could do in oracle with dbms_job.submit. <br/><br />there are may reasons for the need of this feature and not because of the scheduler. just one actual need: assumeyou have a table and every time something gets inserted you want to notify another pq-application. lets say this tableis 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 tableand execute the add_customer_to_accounting pgsql interface in its own session. if it fails it is not the fault of thecrm application and should not raise a exception and rollback. and of course triggering an interface for a foreign applicationshould not be in the native transaction. also your crm application do not want to wait on every insert for a interfaceto complete. <br /><br />before someone is going to say this is a q. no its not realy a q - because you do not needa consumer daemon. there is absolutely no need for an additional daemon. you simply trigger a procedure and send it tothe background like you would do in a unix shell. you can also use dbms_jobs for parallel execution or to recalculate materialviews.<br /><br />so what i would like to ask you if you can feel comfortable with this idea and possible implementthis into postgres 10.x?<br /><br />thanks and cheers<br />chris<br /><br />
Re: possible new feature: asynchronous sql or something like oracles dbms_job.submit
From
Joe Conway
Date:
On 08/03/2011 09:20 AM, Vorarlberger wrote: > 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. Would this work for you? http://www.postgresql.org/docs/9.0/interactive/contrib-dblink-send-query.html Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support