Skip to content

Commit f4095b4

Browse files
jconwayjconway
committed
Implement dblink_get_notify().
Adds the ability to retrieve async notifications using dblink, via the addition of the function dblink_get_notify(). Original patch by Marcus Kempe, suggestions by Tom Lane and Alvaro Herrera, patch review and adjustments by Joe Conway.
1 parent 16f3cf8 commit f4095b4

File tree

7 files changed

+253
-5
lines changed

7 files changed

+253
-5
lines changed

‎contrib/dblink/dblink.c

Lines changed: 84 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Darko Prenosil <Darko.Prenosil@finteh.hr>
99
* Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1010
*
11-
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.82 2009/06/11 14:48:50 momjian Exp $
11+
* $PostgreSQL: pgsql/contrib/dblink/dblink.c,v 1.83 2009/08/05 16:11:07 joe Exp $
1212
* Copyright (c) 2001-2009, PostgreSQL Global Development Group
1313
* ALL RIGHTS RESERVED;
1414
*
@@ -1635,6 +1635,89 @@ dblink_current_query(PG_FUNCTION_ARGS)
16351635
PG_RETURN_DATUM(current_query(fcinfo));
16361636
}
16371637

1638+
/*
1639+
* Retrieve async notifications for a connection.
1640+
*
1641+
* Returns an setof record of notifications, or an empty set if none recieved.
1642+
* Can optionally take a named connection as parameter, but uses the unnamed connection per default.
1643+
*
1644+
*/
1645+
#define DBLINK_NOTIFY_COLS 3
1646+
1647+
PG_FUNCTION_INFO_V1(dblink_get_notify);
1648+
Datum
1649+
dblink_get_notify(PG_FUNCTION_ARGS)
1650+
{
1651+
PGconn *conn = NULL;
1652+
remoteConn *rconn = NULL;
1653+
PGnotify *notify;
1654+
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
1655+
TupleDesc tupdesc;
1656+
Tuplestorestate *tupstore;
1657+
MemoryContext per_query_ctx;
1658+
MemoryContext oldcontext;
1659+
1660+
DBLINK_INIT;
1661+
if (PG_NARGS() == 1)
1662+
DBLINK_GET_NAMED_CONN;
1663+
else
1664+
conn = pconn->conn;
1665+
1666+
/* create the tuplestore */
1667+
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
1668+
oldcontext = MemoryContextSwitchTo(per_query_ctx);
1669+
1670+
tupdesc = CreateTemplateTupleDesc(DBLINK_NOTIFY_COLS, false);
1671+
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "notify_name",
1672+
TEXTOID, -1, 0);
1673+
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "be_pid",
1674+
INT4OID, -1, 0);
1675+
TupleDescInitEntry(tupdesc, (AttrNumber) 3, "extra",
1676+
TEXTOID, -1, 0);
1677+
1678+
tupstore = tuplestore_begin_heap(true, false, work_mem);
1679+
rsinfo->returnMode = SFRM_Materialize;
1680+
rsinfo->setResult = tupstore;
1681+
rsinfo->setDesc = tupdesc;
1682+
1683+
MemoryContextSwitchTo(oldcontext);
1684+
1685+
PQconsumeInput(conn);
1686+
while ((notify = PQnotifies(conn)) != NULL)
1687+
{
1688+
Datum values[DBLINK_NOTIFY_COLS];
1689+
bool nulls[DBLINK_NOTIFY_COLS];
1690+
1691+
memset(values, 0, sizeof(values));
1692+
memset(nulls, 0, sizeof(nulls));
1693+
1694+
if (notify->relname != NULL)
1695+
values[0] = CStringGetTextDatum(notify->relname);
1696+
else
1697+
nulls[0] = true;
1698+
1699+
values[1] = Int32GetDatum(notify->be_pid);
1700+
1701+
if (notify->extra != NULL)
1702+
values[2] = CStringGetTextDatum(notify->extra);
1703+
else
1704+
nulls[2] = true;
1705+
1706+
/* switch to appropriate context while storing the tuple */
1707+
MemoryContextSwitchTo(per_query_ctx);
1708+
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
1709+
MemoryContextSwitchTo(oldcontext);
1710+
1711+
PQfreemem(notify);
1712+
PQconsumeInput(conn);
1713+
}
1714+
1715+
/* clean up and return the tuplestore */
1716+
tuplestore_donestoring(tupstore);
1717+
1718+
return (Datum) 0;
1719+
}
1720+
16381721
/*************************************************************
16391722
* internal functions
16401723
*/

‎contrib/dblink/dblink.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Darko Prenosil <Darko.Prenosil@finteh.hr>
99
* Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
1010
*
11-
* $PostgreSQL: pgsql/contrib/dblink/dblink.h,v 1.22 2009/06/09 17:41:02 tgl Exp $
11+
* $PostgreSQL: pgsql/contrib/dblink/dblink.h,v 1.23 2009/08/05 16:11:07 joe Exp $
1212
* Copyright (c) 2001-2009, PostgreSQL Global Development Group
1313
* ALL RIGHTS RESERVED;
1414
*
@@ -57,5 +57,6 @@ extern Datum dblink_build_sql_insert(PG_FUNCTION_ARGS);
5757
extern Datum dblink_build_sql_delete(PG_FUNCTION_ARGS);
5858
extern Datum dblink_build_sql_update(PG_FUNCTION_ARGS);
5959
extern Datum dblink_current_query(PG_FUNCTION_ARGS);
60+
extern Datum dblink_get_notify(PG_FUNCTION_ARGS);
6061

6162
#endif /* DBLINK_H */

‎contrib/dblink/dblink.sql.in

Lines changed: 20 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/contrib/dblink/dblink.sql.in,v 1.18 2009/06/09 17:41:02 tgl Exp $ */
1+
/* $PostgreSQL: pgsql/contrib/dblink/dblink.sql.in,v 1.19 2009/08/05 16:11:07 joe Exp $ */
22

33
-- Adjust this setting to control where the objects get created.
44
SET search_path = public;
@@ -202,3 +202,22 @@ CREATE OR REPLACE FUNCTION dblink_error_message(text)
202202
RETURNS text
203203
AS 'MODULE_PATHNAME', 'dblink_error_message'
204204
LANGUAGE C STRICT;
205+
206+
CREATE OR REPLACE FUNCTION dblink_get_notify(
207+
OUT notify_name TEXT,
208+
OUT be_pid INT4,
209+
OUT extra TEXT
210+
)
211+
RETURNS setof record
212+
AS 'MODULE_PATHNAME', 'dblink_get_notify'
213+
LANGUAGE C STRICT;
214+
215+
CREATE OR REPLACE FUNCTION dblink_get_notify(
216+
conname TEXT,
217+
OUT notify_name TEXT,
218+
OUT be_pid INT4,
219+
OUT extra TEXT
220+
)
221+
RETURNS setof record
222+
AS 'MODULE_PATHNAME', 'dblink_get_notify'
223+
LANGUAGE C STRICT;

‎contrib/dblink/expected/dblink.out

Lines changed: 51 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -827,3 +827,54 @@ DROP USER dblink_regression_test;
827827
DROP USER MAPPING FOR public SERVER fdtest;
828828
DROP SERVER fdtest;
829829
DROP FOREIGN DATA WRAPPER postgresql;
830+
-- test asynchronous notifications
831+
SELECT dblink_connect('dbname=contrib_regression');
832+
dblink_connect
833+
----------------
834+
OK
835+
(1 row)
836+
837+
--should return listen
838+
SELECT dblink_exec('LISTEN regression');
839+
dblink_exec
840+
-------------
841+
LISTEN
842+
(1 row)
843+
844+
--should return listen
845+
SELECT dblink_exec('LISTEN foobar');
846+
dblink_exec
847+
-------------
848+
LISTEN
849+
(1 row)
850+
851+
SELECT dblink_exec('NOTIFY regression');
852+
dblink_exec
853+
-------------
854+
NOTIFY
855+
(1 row)
856+
857+
SELECT dblink_exec('NOTIFY foobar');
858+
dblink_exec
859+
-------------
860+
NOTIFY
861+
(1 row)
862+
863+
SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
864+
notify_name | is_self_notify | extra
865+
-------------+----------------+-------
866+
regression | t |
867+
foobar | t |
868+
(2 rows)
869+
870+
SELECT * from dblink_get_notify();
871+
notify_name | be_pid | extra
872+
-------------+--------+-------
873+
(0 rows)
874+
875+
SELECT dblink_disconnect();
876+
dblink_disconnect
877+
-------------------
878+
OK
879+
(1 row)
880+

‎contrib/dblink/sql/dblink.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -389,3 +389,20 @@ DROP USER dblink_regression_test;
389389
DROP USER MAPPING FOR public SERVER fdtest;
390390
DROP SERVER fdtest;
391391
DROP FOREIGN DATA WRAPPER postgresql;
392+
393+
-- test asynchronous notifications
394+
SELECT dblink_connect('dbname=contrib_regression');
395+
396+
--should return listen
397+
SELECT dblink_exec('LISTEN regression');
398+
--should return listen
399+
SELECT dblink_exec('LISTEN foobar');
400+
401+
SELECT dblink_exec('NOTIFY regression');
402+
SELECT dblink_exec('NOTIFY foobar');
403+
404+
SELECT notify_name, be_pid = (select t.be_pid from dblink('select pg_backend_pid()') as t(be_pid int)) AS is_self_notify, extra from dblink_get_notify();
405+
406+
SELECT * from dblink_get_notify();
407+
408+
SELECT dblink_disconnect();

‎contrib/dblink/uninstall_dblink.sql

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
/* $PostgreSQL: pgsql/contrib/dblink/uninstall_dblink.sql,v 1.7 2008/04/05 02:26:14 momjian Exp $ */
1+
/* $PostgreSQL: pgsql/contrib/dblink/uninstall_dblink.sql,v 1.8 2009/08/05 16:11:07 joe Exp $ */
22

33
-- Adjust this setting to control where the objects get dropped.
44
SET search_path = public;
@@ -76,3 +76,7 @@ DROP FUNCTION dblink_get_result(text, boolean);
7676
DROP FUNCTION dblink_is_busy(text);
7777

7878
DROP FUNCTION dblink_send_query(text, text);
79+
80+
DROP FUNCTION dblink_get_notify();
81+
82+
DROP FUNCTION dblink_get_notify(text);

‎doc/src/sgml/dblink.sgml

Lines changed: 74 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.8 2009/06/18 14:34:36 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/dblink.sgml,v 1.9 2009/08/05 16:11:07 joe Exp $ -->
22

33
<sect1 id="dblink">
44
<title>dblink</title>
@@ -1260,6 +1260,79 @@ SELECT *
12601260
</refsect1>
12611261
</refentry>
12621262

1263+
<refentry id="CONTRIB-DBLINK-GET-NOTIFY">
1264+
<refnamediv>
1265+
<refname>dblink_get_notify</refname>
1266+
<refpurpose>retrieve async notifications on a connection</refpurpose>
1267+
</refnamediv>
1268+
1269+
<refsynopsisdiv>
1270+
<synopsis>
1271+
dblink_get_notify() returns setof (notify_name text, be_pid int, extra text)
1272+
dblink_get_notify(text connname) returns setof (notify_name text, be_pid int, extra text)
1273+
</synopsis>
1274+
</refsynopsisdiv>
1275+
1276+
<refsect1>
1277+
<title>Description</title>
1278+
1279+
<para>
1280+
<function>dblink_get_notify</> retrieves notifications on either
1281+
the unnamed connection, or on a named connection if specified.
1282+
To receive notifications via dblink, <function>LISTEN</> must
1283+
first be issued, using <function>dblink_exec</>.
1284+
For details see <xref linkend="sql-listen"> and <xref linkend="sql-notify">.
1285+
</para>
1286+
1287+
</refsect1>
1288+
1289+
<refsect1>
1290+
<title>Arguments</title>
1291+
1292+
<variablelist>
1293+
<varlistentry>
1294+
<term><parameter>conname</parameter></term>
1295+
<listitem>
1296+
<para>
1297+
The name of a named connection to get notifications on.
1298+
</para>
1299+
</listitem>
1300+
</varlistentry>
1301+
</variablelist>
1302+
</refsect1>
1303+
1304+
<refsect1>
1305+
<title>Return Value</title>
1306+
<para>Returns setof (notify_name text, be_pid int, extra text), or an empty set if none.</para>
1307+
</refsect1>
1308+
1309+
<refsect1>
1310+
<title>Example</title>
1311+
1312+
<programlisting>
1313+
test=# SELECT dblink_exec('LISTEN virtual');
1314+
dblink_exec
1315+
-------------
1316+
LISTEN
1317+
(1 row)
1318+
1319+
test=# SELECT * FROM dblink_get_notify();
1320+
notify_name | be_pid | extra
1321+
-------------+--------+-------
1322+
(0 rows)
1323+
1324+
test=# NOTIFY virtual;
1325+
NOTIFY
1326+
1327+
SELECT * FROM dblink_get_notify();
1328+
notify_name | be_pid | extra
1329+
-------------+--------+-------
1330+
virtual | 1229 |
1331+
(1 row)
1332+
</programlisting>
1333+
</refsect1>
1334+
</refentry>
1335+
12631336
<refentry id="CONTRIB-DBLINK-GET-RESULT">
12641337
<refmeta>
12651338
<refentrytitle>dblink_get_result</refentrytitle>

0 commit comments

Comments
 (0)