Skip to content

Commit d724c31

Browse files
tglsfdctglsfdc
committed
The attached adds GRANTs to PUBLIC for dblink functions, removes the
non-standard regression test, and adds standard installcheck regression test support. The test creates a second database (regression_slave) and drops it again, in order to avoid the cheesy-ness of connecting back to the same database ;-) Joe Conway
1 parent 1b69b12 commit d724c31

File tree

5 files changed

+198
-85
lines changed

5 files changed

+198
-85
lines changed

‎contrib/dblink/Makefile

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
# $Header: /cvsroot/pgsql/contrib/dblink/Makefile,v 1.6 2002/09/03 04:01:05 tgl Exp $
1+
# $Header: /cvsroot/pgsql/contrib/dblink/Makefile,v 1.7 2002/09/14 20:28:54 tgl Exp $
22

33
subdir = contrib/dblink
44
top_builddir = ../..
@@ -11,7 +11,6 @@ SHLIB_LINK = $(libpq)
1111

1212
DATA_built = dblink.sql
1313
DOCS = README.dblink
14-
15-
EXTRA_CLEAN = dblink.test.out dblink.test.diff
14+
REGRESS = dblink
1615

1716
include $(top_srcdir)/contrib/contrib-global.mk

‎contrib/dblink/dblink.sql.in

Lines changed: 22 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -29,10 +29,12 @@ CREATE OR REPLACE FUNCTION dblink_close (text) RETURNS text
2929
AS 'MODULE_PATHNAME','dblink_close' LANGUAGE 'c'
3030
WITH (isstrict);
3131

32-
-- Note: if this is a first time install of dblink, the following DROP
33-
-- FUNCTION line is expected to fail.
34-
-- Comment out the following 4 lines if the DEPRECATED functions are used.
35-
DROP FUNCTION dblink (text,text);
32+
-- Note: if this is not a first time install of dblink, uncomment the
33+
-- following DROP which prepares the database for the new, non-deprecated
34+
-- version.
35+
--DROP FUNCTION dblink (text,text);
36+
37+
-- Comment out the following 3 lines if the DEPRECATED functions are used.
3638
CREATE OR REPLACE FUNCTION dblink (text,text) RETURNS setof record
3739
AS 'MODULE_PATHNAME','dblink_record' LANGUAGE 'c'
3840
WITH (isstrict);
@@ -69,3 +71,19 @@ CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int2, _tex
6971

7072
CREATE OR REPLACE FUNCTION dblink_current_query () RETURNS text
7173
AS 'MODULE_PATHNAME','dblink_current_query' LANGUAGE 'c';
74+
75+
GRANT EXECUTE ON FUNCTION dblink_connect (text) TO PUBLIC;
76+
GRANT EXECUTE ON FUNCTION dblink_disconnect () TO PUBLIC;
77+
GRANT EXECUTE ON FUNCTION dblink_open (text,text) TO PUBLIC;
78+
GRANT EXECUTE ON FUNCTION dblink_fetch (text,int) TO PUBLIC;
79+
GRANT EXECUTE ON FUNCTION dblink_close (text) TO PUBLIC;
80+
GRANT EXECUTE ON FUNCTION dblink (text,text) TO PUBLIC;
81+
GRANT EXECUTE ON FUNCTION dblink (text) TO PUBLIC;
82+
GRANT EXECUTE ON FUNCTION dblink_exec (text,text) TO PUBLIC;
83+
GRANT EXECUTE ON FUNCTION dblink_exec (text) TO PUBLIC;
84+
GRANT EXECUTE ON FUNCTION dblink_get_pkey (text) TO PUBLIC;
85+
GRANT EXECUTE ON FUNCTION dblink_build_sql_insert (text, int2vector, int2, _text, _text) TO PUBLIC;
86+
GRANT EXECUTE ON FUNCTION dblink_build_sql_delete (text, int2vector, int2, _text) TO PUBLIC;
87+
GRANT EXECUTE ON FUNCTION dblink_build_sql_update (text, int2vector, int2, _text, _text) TO PUBLIC;
88+
GRANT EXECUTE ON FUNCTION dblink_current_query () TO PUBLIC;
89+

‎contrib/dblink/dblink_check.sh

Lines changed: 0 additions & 15 deletions
This file was deleted.

‎contrib/dblink/dblink.test.expected.out renamed to ‎contrib/dblink/expected/dblink.out

Lines changed: 96 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,11 @@
1-
\connect dblink_test_slave
1+
--
2+
-- First, create a slave database and define the functions.
3+
-- Turn off echoing so that expected file does not depend on
4+
-- contents of dblink.sql.
5+
--
6+
CREATE DATABASE regression_slave;
7+
\connect regression_slave
8+
\set ECHO none
29
create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
310
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
411
insert into foo values(0,'a','{"a0","b0","c0"}');
@@ -11,9 +18,54 @@ insert into foo values(6,'g','{"a6","b6","c6"}');
1118
insert into foo values(7,'h','{"a7","b7","c7"}');
1219
insert into foo values(8,'i','{"a8","b8","c8"}');
1320
insert into foo values(9,'j','{"a9","b9","c9"}');
14-
\connect dblink_test_master
21+
-- misc utilities
22+
-- show the currently executing query
23+
select 'hello' as hello, dblink_current_query() as query;
24+
hello | query
25+
-------+-----------------------------------------------------------
26+
hello | select 'hello' as hello, dblink_current_query() as query;
27+
(1 row)
28+
29+
-- list the primary key fields
30+
select * from dblink_get_pkey('foo');
31+
position | colname
32+
----------+---------
33+
1 | f1
34+
2 | f2
35+
(2 rows)
36+
37+
-- build an insert statement based on a local tuple,
38+
-- replacing the primary key values with new ones
39+
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
40+
dblink_build_sql_insert
41+
-----------------------------------------------------------
42+
INSERT INTO foo(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}')
43+
(1 row)
44+
45+
-- build an update statement based on a local tuple,
46+
-- replacing the primary key values with new ones
47+
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
48+
dblink_build_sql_update
49+
----------------------------------------------------------------------------------------
50+
UPDATE foo SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz'
51+
(1 row)
52+
53+
-- build a delete statement based on a local tuple,
54+
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
55+
dblink_build_sql_delete
56+
---------------------------------------------
57+
DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
58+
(1 row)
59+
60+
--
61+
-- Connect back to the regression database and define the functions.
62+
-- Turn off echoing so that expected file does not depend on
63+
-- contents of dblink.sql.
64+
--
65+
\connect regression
66+
\set ECHO none
1567
-- regular old dblink
16-
select * from dblink('dbname=dblink_test_slave','select * from foo') as t(a int, b text, c text[]) where t.a > 7;
68+
select * from dblink('dbname=regression_slave','select * from foo') as t(a int, b text, c text[]) where t.a > 7;
1769
a | b | c
1870
---+---+------------
1971
8 | i | {a8,b8,c8}
@@ -24,7 +76,7 @@ select * from dblink('dbname=dblink_test_slave','select * from foo') as t(a int,
2476
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
2577
ERROR: dblink: no connection available
2678
-- create a persistent connection
27-
select dblink_connect('dbname=dblink_test_slave');
79+
select dblink_connect('dbname=regression_slave');
2880
dblink_connect
2981
----------------
3082
OK
@@ -94,14 +146,14 @@ select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.
94146
ERROR: dblink: no connection available
95147
-- put more data into our slave table, first using arbitrary connection syntax
96148
-- but truncate the actual return value so we can use diff to check for success
97-
select substr(dblink_exec('dbname=dblink_test_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
149+
select substr(dblink_exec('dbname=regression_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
98150
substr
99151
--------
100152
INSERT
101153
(1 row)
102154

103155
-- create a persistent connection
104-
select dblink_connect('dbname=dblink_test_slave');
156+
select dblink_connect('dbname=regression_slave');
105157
dblink_connect
106158
----------------
107159
OK
@@ -160,43 +212,48 @@ select * from dblink('select * from foo') as t(a int, b text, c text[]) where a
160212
---+---+---
161213
(0 rows)
162214

163-
-- misc utilities
164-
\connect dblink_test_slave
165-
-- show the currently executing query
166-
select 'hello' as hello, dblink_current_query() as query;
167-
hello | query
168-
-------+-----------------------------------------------------------
169-
hello | select 'hello' as hello, dblink_current_query() as query;
215+
-- close the persistent connection
216+
select dblink_disconnect();
217+
dblink_disconnect
218+
-------------------
219+
OK
170220
(1 row)
171221

172-
-- list the primary key fields
173-
select * from dblink_get_pkey('foo');
174-
position | colname
175-
----------+---------
176-
1 | f1
177-
2 | f2
178-
(2 rows)
222+
-- now wait for the connection to the slave to be cleared before
223+
-- we try to drop the database
224+
CREATE FUNCTION wait() RETURNS TEXT AS '
225+
DECLARE
226+
rec record;
227+
cntr int;
228+
BEGIN
229+
cntr = 0;
179230

180-
-- build an insert statement based on a local tuple,
181-
-- replacing the primary key values with new ones
182-
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
183-
dblink_build_sql_insert
184-
-----------------------------------------------------------
185-
INSERT INTO foo(f1,f2,f3) VALUES('99','xyz','{a0,b0,c0}')
186-
(1 row)
231+
select into rec d.datname
232+
from pg_database d,
233+
(select pg_stat_get_backend_dbid(pg_stat_get_backend_idset()) AS dbid) b
234+
where d.oid = b.dbid and d.datname = ''regression_slave'';
187235

188-
-- build an update statement based on a local tuple,
189-
-- replacing the primary key values with new ones
190-
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
191-
dblink_build_sql_update
192-
----------------------------------------------------------------------------------------
193-
UPDATE foo SET f1 = '99', f2 = 'xyz', f3 = '{a0,b0,c0}' WHERE f1 = '99' AND f2 = 'xyz'
194-
(1 row)
236+
WHILE FOUND LOOP
237+
cntr = cntr + 1;
195238

196-
-- build a delete statement based on a local tuple,
197-
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
198-
dblink_build_sql_delete
199-
---------------------------------------------
200-
DELETE FROM foo WHERE f1 = '0' AND f2 = 'a'
239+
select into rec d.datname
240+
from pg_database d,
241+
(select pg_stat_get_backend_dbid(pg_stat_get_backend_idset()) AS dbid) b
242+
where d.oid = b.dbid and d.datname = ''regression_slave'';
243+
244+
-- safety valve
245+
if cntr > 1000 THEN
246+
EXIT;
247+
end if;
248+
END LOOP;
249+
RETURN ''OK'';
250+
END;
251+
' LANGUAGE 'plpgsql';
252+
SELECT wait();
253+
wait
254+
------
255+
OK
201256
(1 row)
202257

258+
-- OK, safe to drop the slave
259+
DROP DATABASE regression_slave;

‎contrib/dblink/dblink.test.sql renamed to ‎contrib/dblink/sql/dblink.sql

Lines changed: 78 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,13 @@
1-
\connect dblink_test_slave
1+
--
2+
-- First, create a slave database and define the functions.
3+
-- Turn off echoing so that expected file does not depend on
4+
-- contents of dblink.sql.
5+
--
6+
CREATE DATABASE regression_slave;
7+
\connect regression_slave
8+
\set ECHO none
9+
\i dblink.sql
10+
\set ECHO all
211

312
create table foo(f1 int, f2 text, f3 text[], primary key (f1,f2));
413
insert into foo values(0,'a','{"a0","b0","c0"}');
@@ -12,16 +21,43 @@ insert into foo values(7,'h','{"a7","b7","c7"}');
1221
insert into foo values(8,'i','{"a8","b8","c8"}');
1322
insert into foo values(9,'j','{"a9","b9","c9"}');
1423

15-
\connect dblink_test_master
24+
-- misc utilities
25+
26+
-- show the currently executing query
27+
select 'hello' as hello, dblink_current_query() as query;
28+
29+
-- list the primary key fields
30+
select * from dblink_get_pkey('foo');
31+
32+
-- build an insert statement based on a local tuple,
33+
-- replacing the primary key values with new ones
34+
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
35+
36+
-- build an update statement based on a local tuple,
37+
-- replacing the primary key values with new ones
38+
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
39+
40+
-- build a delete statement based on a local tuple,
41+
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
42+
43+
--
44+
-- Connect back to the regression database and define the functions.
45+
-- Turn off echoing so that expected file does not depend on
46+
-- contents of dblink.sql.
47+
--
48+
\connect regression
49+
\set ECHO none
50+
\i dblink.sql
51+
\set ECHO all
1652

1753
-- regular old dblink
18-
select * from dblink('dbname=dblink_test_slave','select * from foo') as t(a int, b text, c text[]) where t.a > 7;
54+
select * from dblink('dbname=regression_slave','select * from foo') as t(a int, b text, c text[]) where t.a > 7;
1955

2056
-- should generate "no connection available" error
2157
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
2258

2359
-- create a persistent connection
24-
select dblink_connect('dbname=dblink_test_slave');
60+
select dblink_connect('dbname=regression_slave');
2561

2662
-- use the persistent connection
2763
select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.a > 7;
@@ -50,10 +86,10 @@ select * from dblink('select * from foo') as t(a int, b text, c text[]) where t.
5086

5187
-- put more data into our slave table, first using arbitrary connection syntax
5288
-- but truncate the actual return value so we can use diff to check for success
53-
select substr(dblink_exec('dbname=dblink_test_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
89+
select substr(dblink_exec('dbname=regression_slave','insert into foo values(10,''k'',''{"a10","b10","c10"}'')'),1,6);
5490

5591
-- create a persistent connection
56-
select dblink_connect('dbname=dblink_test_slave');
92+
select dblink_connect('dbname=regression_slave');
5793

5894
-- put more data into our slave table, using persistent connection syntax
5995
-- but truncate the actual return value so we can use diff to check for success
@@ -74,22 +110,40 @@ select dblink_exec('delete from foo where f1 = 11');
74110
-- let's see it
75111
select * from dblink('select * from foo') as t(a int, b text, c text[]) where a = 11;
76112

77-
-- misc utilities
78-
\connect dblink_test_slave
79-
80-
-- show the currently executing query
81-
select 'hello' as hello, dblink_current_query() as query;
82-
83-
-- list the primary key fields
84-
select * from dblink_get_pkey('foo');
85-
86-
-- build an insert statement based on a local tuple,
87-
-- replacing the primary key values with new ones
88-
select dblink_build_sql_insert('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
89-
90-
-- build an update statement based on a local tuple,
91-
-- replacing the primary key values with new ones
92-
select dblink_build_sql_update('foo','1 2',2,'{"0", "a"}','{"99", "xyz"}');
113+
-- close the persistent connection
114+
select dblink_disconnect();
93115

94-
-- build a delete statement based on a local tuple,
95-
select dblink_build_sql_delete('foo','1 2',2,'{"0", "a"}');
116+
-- now wait for the connection to the slave to be cleared before
117+
-- we try to drop the database
118+
CREATE FUNCTION wait() RETURNS TEXT AS '
119+
DECLARE
120+
rec record;
121+
cntr int;
122+
BEGIN
123+
cntr = 0;
124+
125+
select into rec d.datname
126+
from pg_database d,
127+
(select pg_stat_get_backend_dbid(pg_stat_get_backend_idset()) AS dbid) b
128+
where d.oid = b.dbid and d.datname = ''regression_slave'';
129+
130+
WHILE FOUND LOOP
131+
cntr = cntr + 1;
132+
133+
select into rec d.datname
134+
from pg_database d,
135+
(select pg_stat_get_backend_dbid(pg_stat_get_backend_idset()) AS dbid) b
136+
where d.oid = b.dbid and d.datname = ''regression_slave'';
137+
138+
-- safety valve
139+
if cntr > 1000 THEN
140+
EXIT;
141+
end if;
142+
END LOOP;
143+
RETURN ''OK'';
144+
END;
145+
' LANGUAGE 'plpgsql';
146+
SELECT wait();
147+
148+
-- OK, safe to drop the slave
149+
DROP DATABASE regression_slave;

0 commit comments

Comments
 (0)