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

Commitd724c31

Browse files
committed
The attached adds GRANTs to PUBLIC for dblink functions, removes the
non-standard regression test, and adds standard installcheck regression testsupport.The test creates a second database (regression_slave) and drops it again, inorder to avoid the cheesy-ness of connecting back to the same database ;-)Joe Conway
1 parent1b69b12 commitd724c31

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.outrenamed 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.sqlrenamed 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+
CREATEDATABASEregression_slave;
7+
\connect regression_slave
8+
\set ECHO none
9+
\idblink.sql
10+
\set ECHO all
211

312
createtablefoo(f1int, f2text, f3text[],primary key (f1,f2));
413
insert into foovalues(0,'a','{"a0","b0","c0"}');
@@ -12,16 +21,43 @@ insert into foo values(7,'h','{"a7","b7","c7"}');
1221
insert into foovalues(8,'i','{"a8","b8","c8"}');
1322
insert into foovalues(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+
\idblink.sql
51+
\set ECHO all
1652

1753
-- regular old dblink
18-
select*from dblink('dbname=dblink_test_slave','select * from foo')as t(aint, btext, ctext[])wheret.a>7;
54+
select*from dblink('dbname=regression_slave','select * from foo')as t(aint, btext, ctext[])wheret.a>7;
1955

2056
-- should generate "no connection available" error
2157
select*from dblink('select * from foo')as t(aint, btext, ctext[])wheret.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(aint, btext, ctext[])wheret.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(aint, btext, ctext[])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+
CREATEFUNCTIONwait() RETURNSTEXTAS'
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+
DROPDATABASE regression_slave;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp