pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWG: Extension using the current connection handle
4+ messages / 2 participants
[nested] [flat]
* WG: Extension using the current connection handle
@ 2017-12-13 12:59 Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
2017-12-13 14:46 ` Re: WG: Extension using the current connection handle Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Brandl, Wolfgang @ 2017-12-13 12:59 UTC (permalink / raw)
To: pgsql-admin
Hello
I have written a extension in postgres to call a Oracle Stored procedure.
We have a Postgres table holding the usernames and password related to the Oracle SID.
In my extension I wanted to select the username and password out of this table.
Do I have to make a new Postgres connection or can I somehow use the current connection handel from the session Where the extension will be called?
Regards
Wolfgang
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WG: Extension using the current connection handle
2017-12-13 12:59 WG: Extension using the current connection handle Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
@ 2017-12-13 14:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2017-12-13 14:46 UTC (permalink / raw)
To: Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>; pgsql-admin
Wolfgang Brandl wrote:
> I have written a extension in postgres to call a Oracle Stored procedure.
> We have a Postgres table holding the usernames and password related to the Oracle SID.
>
> In my extension I wanted to select the username and password out of this table.
> Do I have to make a new Postgres connection or can I somehow use the current connection handel from the session Where the extension will be called?
You would use the SPI interface to query database tables from
a C function:
https://www.postgresql.org/docs/current/static/spi.html
If you don't feel like reinventing the wheel, there are tricks how
to do that with oracle_fdw. You could create an Oracle table with
a BEFORE INSERT trigger that executes the procedure.
See https://stackoverflow.com/a/46360383/6464308
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WG: Extension using the current connection handle
@ 2017-12-19 12:07 Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
2017-12-19 12:23 ` Re: WG: Extension using the current connection handle Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 4+ messages in thread
From: Brandl, Wolfgang @ 2017-12-19 12:07 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-admin
Hello
Thanks for the Tipp with the SPI.
We are not using oracle_fdw, becaue we got somehow problems with the connection. Therefore some of the transactions get lost and all following didn't get a connection.
We did not analyse this.
Regards Wolfgang
-----Ursprüngliche Nachricht-----
Von: Laurenz Albe [mailto:laurenz.albe@cybertec.at]
Gesendet: Mittwoch, 13. Dezember 2017 15:47
An: Brandl, Wolfgang; pgsql-admin@postgresql.org
Betreff: Re: WG: Extension using the current connection handle
Wolfgang Brandl wrote:
> I have written a extension in postgres to call a Oracle Stored procedure.
> We have a Postgres table holding the usernames and password related to the Oracle SID.
>
> In my extension I wanted to select the username and password out of this table.
> Do I have to make a new Postgres connection or can I somehow use the current connection handel from the session Where the extension will be called?
You would use the SPI interface to query database tables from a C function:
https://www.postgresql.org/docs/current/static/spi.html
If you don't feel like reinventing the wheel, there are tricks how to do that with oracle_fdw. You could create an Oracle table with a BEFORE INSERT trigger that executes the procedure.
See https://stackoverflow.com/a/46360383/6464308
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: WG: Extension using the current connection handle
2017-12-19 12:07 Re: WG: Extension using the current connection handle Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
@ 2017-12-19 12:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2017-12-19 12:23 UTC (permalink / raw)
To: Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>; pgsql-admin
Wolfgang Brandl wrote:
> Thanks for the Tipp with the SPI.
> We are not using oracle_fdw, becaue we got somehow problems with the connection.
> Therefore some of the transactions get lost and all following didn't get a connection.
> We did not analyse this.
Normally, oracle_fdw re-establishes lost connections, so you
get at most one error, and the next statement works again.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-12-19 12:23 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-12-13 12:59 WG: Extension using the current connection handle Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
2017-12-13 14:46 ` Laurenz Albe <laurenz.albe@cybertec.at>
2017-12-19 12:07 Re: WG: Extension using the current connection handle Brandl, Wolfgang <wolfgang.brandl@brz.gv.at>
2017-12-19 12:23 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox