Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf4095b4

Browse files
committed
Implement dblink_get_notify().
Adds the ability to retrieve async notifications using dblink,via the addition of the function dblink_get_notify(). Original patchby Marcus Kempe, suggestions by Tom Lane and Alvaro Herrera, patchreview and adjustments by Joe Conway.
1 parent16f3cf8 commitf4095b4

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+
#defineDBLINK_NOTIFY_COLS3
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+
TupleDesctupdesc;
1656+
Tuplestorestate*tupstore;
1657+
MemoryContextper_query_ctx;
1658+
MemoryContextoldcontext;
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+
Datumvalues[DBLINK_NOTIFY_COLS];
1689+
boolnulls[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
externDatumdblink_build_sql_delete(PG_FUNCTION_ARGS);
5858
externDatumdblink_build_sql_update(PG_FUNCTION_ARGS);
5959
externDatumdblink_current_query(PG_FUNCTION_ARGS);
60+
externDatumdblink_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
DROPUSER 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= (selectt.be_pidfrom dblink('select pg_backend_pid()')as t(be_pidint))AS is_self_notify, extrafrom 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/0502:26:14 momjian Exp $*/
1+
/* $PostgreSQL: pgsql/contrib/dblink/uninstall_dblink.sql,v 1.8 2009/08/0516: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
DROPFUNCTION dblink_is_busy(text);
7777

7878
DROPFUNCTION dblink_send_query(text,text);
79+
80+
DROPFUNCTION dblink_get_notify();
81+
82+
DROPFUNCTION 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)
[8]ページ先頭
©2009-2025 Movatter.jp