pg_background extension help

From: Stephen Anderson <stephen(dot)anderson(at)appstech(dot)com(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: pg_background extension help
Date: 2019-03-05 05:45:15
Message-ID: CADn1m+6shOYPrMYdbhHbRa_Wb0xiTX942sQNxPpLZLFOAJqptA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2019-03-05 15:00:08 Re: pg_background extension help
Previous Message Tom Lane 2019-02-28 16:57:57 Re: compiling postgresql with uuid-ossp failure