pg_background extension help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_background extension help

Stephen Anderson
Hi postgres gurus,
I started my postgres journey Friday so I'm on day three of working postgres out.  Hence I've posted this in pgsql-novice on the assumption I'm missing something obvious.. 


Versions / OS

Whilst I don't think the version is of much use for this query, for completeness sake I'm using:
- EnterpriseDB Standard version of postgres
- psql starts up and tells me psql.bin (9.5.16.22)
- The docker container I've built the server on started life as centos:centos7



Background

I'm porting over an Oracle application to postgres and by and large its been relatively seamless.  One of the challenges I've hit is replicating the functions/procedures that utilise Oracle's autonomous transaction functionality.  I've found Vibhor's pg_background extension (https://github.com/vibhorkum/pg_background) and successfully created it in my instance and this seems pretty damn close to what I need.  Unfortunately I've hit a bit of a snag around getting parameter values back.  So to the point of all this :-)



Query

I have simple procedure that accepts three parameters - the first being an IN parameter, and the remaining two being OUT parameters.. Eg.
select * from testing('x')
  pv_result  | pv_result2  
-------------+-------------
 out value 1 | another out

I want to call this using pg_background and get the value of the two out parameters.  Does anyone have any idea what I should use in the as section of the following query:

select * from pg_background_result(pg_background_launch('exec testing(''x'')')) as (???) 

if I run something like:

vacuum verbose <my table>
I get  22 odd lines of guff and then a line with VACUUM on it.

If I run that in pg_background

SELECT * FROM pg_background_result(pg_background_launch('vacuum verbose <my table>')) as (result TEXT);

I get  22 odd lines of guff and then

-[ RECORD 1 ]--
result | VACUUM

So I guess that seems to be functioning as expected.  Sadly if I try to call a function in the pg_background_launch, or I try calling my procedure in it as above it simply tells me:
ERROR: remote query result rowtype does not match the specified FROM clause rowtype

I thought perhaps matching my out parameters might work eg. (pv_result1 text, pv_result2 text) and even tried (result text, pv_result1 text, pv_result2 text) but no luck.


That set me on a path of trying to work out what the remote query result rowtype is..  


\df+ pg_background_result just tells me the result data type is "SETOF record"..


I tried the functions here: https://stackoverflow.com/questions/34883062/how-to-read-a-function-return-table-definition-from-pg-catalog-or-information-sc which work fine on my dummy procedure:


select proname, function_return_type_names(p)

from pg_proc p

where proname = 'testing';

-[ RECORD 1 ]--------------+------------------

proname                    | testing

function_return_type_names | {varchar,varchar}


but the function_return_type_names function returns nothing for pg_background_result (I assume because function_return_type_names is looking for OUT parameters, and not processing the return type of the pg_background_result function itself.  ).. 

Seeing that I saw prorettype in pg_proc but that just joins to "record" in pg_type so got me nowhere.. 


All in all, I'm a bit lost at this point.. 


Any suggestions most welcome

Steve.

Reply | Threaded
Open this post in threaded view
|

Re: pg_background extension help

Tom Lane-2
Stephen Anderson <[hidden email]> writes:
> I'm porting over an Oracle application to postgres and by and large its
> been relatively seamless.  One of the challenges I've hit is replicating
> the functions/procedures that utilise Oracle's autonomous transaction
> functionality.  I've found Vibhor's pg_background extension (
> https://github.com/vibhorkum/pg_background) and successfully created it in
> my instance and this seems pretty damn close to what I need.  Unfortunately
> I've hit a bit of a snag around getting parameter values back.  So to the
> point of all this :-)

I know diddly-squat about pg_background, but judging from the symptoms
you mention, it's not very smart about functions declared to return
RECORD, which is what's happening under the hood if you use multiple OUT
parameters.  You might have better luck if you create a named composite
type and declare the function to return that (which'll require some
internal notational changes in the function, but nothing too awful).

Or perhaps not.  In any case, it'd be an idea to take this up with the
extension's author; maybe it can be improved in future releases.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

Re: pg_background extension help

Stephen Anderson
Thanks Tom, I've tried contacting Vibhor but had no luck.  With further digging it seems that not much at all comes back from the pg_background_result function.  I've written a wrapper function around the call that saves the output parameter values into a local table so they can be retrieved by the calling routine.  it's ugly but it works :-)

Steve.

On Wed, 6 Mar 2019 at 02:00, Tom Lane <[hidden email]> wrote:
Stephen Anderson <[hidden email]> writes:
> I'm porting over an Oracle application to postgres and by and large its
> been relatively seamless.  One of the challenges I've hit is replicating
> the functions/procedures that utilise Oracle's autonomous transaction
> functionality.  I've found Vibhor's pg_background extension (
> https://github.com/vibhorkum/pg_background) and successfully created it in
> my instance and this seems pretty damn close to what I need.  Unfortunately
> I've hit a bit of a snag around getting parameter values back.  So to the
> point of all this :-)

I know diddly-squat about pg_background, but judging from the symptoms
you mention, it's not very smart about functions declared to return
RECORD, which is what's happening under the hood if you use multiple OUT
parameters.  You might have better luck if you create a named composite
type and declare the function to return that (which'll require some
internal notational changes in the function, but nothing too awful).

Or perhaps not.  In any case, it'd be an idea to take this up with the
extension's author; maybe it can be improved in future releases.

                        regards, tom lane