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.
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 |