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

Commit9f77ad2

Browse files
committed
Provide plpgsql tests for cases involving record field changes.
We suppressed one of these test cases in commitfeb1cc5 becauseit was failing to produce the expected results on CLOBBER_CACHE_ALWAYSbuildfarm members. But now we need another test with similar behavior,so let's set up a test file that is expected to vary between regular andCLOBBER_CACHE_ALWAYS cases, and provide variant expected files.Someday we should fix plpgsql's failure for change-of-field-type, andthen the discrepancy will go away and we can fold these tests backinto plpgsql_record.sql. But today is not that day.Discussion:https://postgr.es/m/87wotkfju1.fsf@news-spur.riddles.org.uk
1 parent662d12a commit9f77ad2

File tree

6 files changed

+194
-26
lines changed

6 files changed

+194
-26
lines changed

‎src/pl/plpgsql/src/Makefile

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -27,7 +27,7 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql
2727
REGRESS_OPTS = --dbname=$(PL_TESTDB)
2828

2929
REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record\
30-
plpgsql_transaction plpgsql_varprops
30+
plpgsql_cacheplpgsql_transaction plpgsql_varprops
3131

3232
all: all-lib
3333

Lines changed: 67 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,67 @@
1+
--
2+
-- Cache-behavior-dependent test cases
3+
--
4+
-- These tests logically belong in plpgsql_record.sql, and perhaps someday
5+
-- can be merged back into it. For now, however, their results are different
6+
-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two
7+
-- expected-output files to cover both cases. To minimize the maintenance
8+
-- effort resulting from that, this file should contain only tests that
9+
-- do have different results under CLOBBER_CACHE_ALWAYS.
10+
--
11+
-- check behavior with changes of a named rowtype
12+
create table c_mutable(f1 int, f2 text);
13+
create function c_sillyaddone(int) returns int language plpgsql as
14+
$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$;
15+
select c_sillyaddone(42);
16+
c_sillyaddone
17+
---------------
18+
43
19+
(1 row)
20+
21+
alter table c_mutable drop column f1;
22+
alter table c_mutable add column f1 float8;
23+
-- currently, this fails due to cached plan for "r.f1 + 1" expression
24+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
25+
select c_sillyaddone(42);
26+
ERROR: type of parameter 4 (double precision) does not match that when preparing the plan (integer)
27+
CONTEXT: PL/pgSQL function c_sillyaddone(integer) line 1 at RETURN
28+
-- but it's OK if we force plan rebuilding
29+
discard plans;
30+
select c_sillyaddone(42);
31+
c_sillyaddone
32+
---------------
33+
43
34+
(1 row)
35+
36+
-- check behavior with changes in a record rowtype
37+
create function show_result_type(text) returns text language plpgsql as
38+
$$
39+
declare
40+
r record;
41+
t text;
42+
begin
43+
execute $1 into r;
44+
select pg_typeof(r.a) into t;
45+
return format('type %s value %s', t, r.a::text);
46+
end;
47+
$$;
48+
select show_result_type('select 1 as a');
49+
show_result_type
50+
----------------------
51+
type integer value 1
52+
(1 row)
53+
54+
-- currently this fails due to cached plan for pg_typeof expression
55+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
56+
select show_result_type('select 2.0 as a');
57+
ERROR: type of parameter 5 (numeric) does not match that when preparing the plan (integer)
58+
CONTEXT: SQL statement "select pg_typeof(r.a)"
59+
PL/pgSQL function show_result_type(text) line 7 at SQL statement
60+
-- but it's OK if we force plan rebuilding
61+
discard plans;
62+
select show_result_type('select 2.0 as a');
63+
show_result_type
64+
------------------------
65+
type numeric value 2.0
66+
(1 row)
67+
Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,72 @@
1+
--
2+
-- Cache-behavior-dependent test cases
3+
--
4+
-- These tests logically belong in plpgsql_record.sql, and perhaps someday
5+
-- can be merged back into it. For now, however, their results are different
6+
-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two
7+
-- expected-output files to cover both cases. To minimize the maintenance
8+
-- effort resulting from that, this file should contain only tests that
9+
-- do have different results under CLOBBER_CACHE_ALWAYS.
10+
--
11+
-- check behavior with changes of a named rowtype
12+
create table c_mutable(f1 int, f2 text);
13+
create function c_sillyaddone(int) returns int language plpgsql as
14+
$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$;
15+
select c_sillyaddone(42);
16+
c_sillyaddone
17+
---------------
18+
43
19+
(1 row)
20+
21+
alter table c_mutable drop column f1;
22+
alter table c_mutable add column f1 float8;
23+
-- currently, this fails due to cached plan for "r.f1 + 1" expression
24+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
25+
select c_sillyaddone(42);
26+
c_sillyaddone
27+
---------------
28+
43
29+
(1 row)
30+
31+
-- but it's OK if we force plan rebuilding
32+
discard plans;
33+
select c_sillyaddone(42);
34+
c_sillyaddone
35+
---------------
36+
43
37+
(1 row)
38+
39+
-- check behavior with changes in a record rowtype
40+
create function show_result_type(text) returns text language plpgsql as
41+
$$
42+
declare
43+
r record;
44+
t text;
45+
begin
46+
execute $1 into r;
47+
select pg_typeof(r.a) into t;
48+
return format('type %s value %s', t, r.a::text);
49+
end;
50+
$$;
51+
select show_result_type('select 1 as a');
52+
show_result_type
53+
----------------------
54+
type integer value 1
55+
(1 row)
56+
57+
-- currently this fails due to cached plan for pg_typeof expression
58+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
59+
select show_result_type('select 2.0 as a');
60+
show_result_type
61+
------------------------
62+
type numeric value 2.0
63+
(1 row)
64+
65+
-- but it's OK if we force plan rebuilding
66+
discard plans;
67+
select show_result_type('select 2.0 as a');
68+
show_result_type
69+
------------------------
70+
type numeric value 2.0
71+
(1 row)
72+

‎src/pl/plpgsql/src/expected/plpgsql_record.out

Lines changed: 2 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -421,20 +421,8 @@ select sillyaddone(42);
421421
43
422422
(1 row)
423423

424-
alter table mutable drop column f1;
425-
alter table mutable add column f1 float8;
426-
-- currently, this fails due to cached plan for "r.f1 + 1" expression
427-
-- (but we can't actually show that, because a CLOBBER_CACHE_ALWAYS build
428-
-- will succeed)
429-
-- select sillyaddone(42);
430-
-- but it's OK if we force plan rebuilding
431-
discard plans;
432-
select sillyaddone(42);
433-
sillyaddone
434-
-------------
435-
43
436-
(1 row)
437-
424+
-- test for change of type of column f1 should be here someday;
425+
-- for now see plpgsql_cache test
438426
alter table mutable drop column f1;
439427
select sillyaddone(42); -- fail
440428
ERROR: record "r" has no field "f1"
Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,50 @@
1+
--
2+
-- Cache-behavior-dependent test cases
3+
--
4+
-- These tests logically belong in plpgsql_record.sql, and perhaps someday
5+
-- can be merged back into it. For now, however, their results are different
6+
-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two
7+
-- expected-output files to cover both cases. To minimize the maintenance
8+
-- effort resulting from that, this file should contain only tests that
9+
-- do have different results under CLOBBER_CACHE_ALWAYS.
10+
--
11+
12+
-- check behavior with changes of a named rowtype
13+
createtablec_mutable(f1int, f2text);
14+
15+
createfunctionc_sillyaddone(int) returnsint language plpgsqlas
16+
$$ declare r c_mutable;beginr.f1 := $1; returnr.f1+1; end $$;
17+
select c_sillyaddone(42);
18+
19+
altertable c_mutable drop column f1;
20+
altertable c_mutable add column f1 float8;
21+
22+
-- currently, this fails due to cached plan for "r.f1 + 1" expression
23+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
24+
select c_sillyaddone(42);
25+
26+
-- but it's OK if we force plan rebuilding
27+
discard plans;
28+
select c_sillyaddone(42);
29+
30+
-- check behavior with changes in a record rowtype
31+
createfunctionshow_result_type(text) returnstext language plpgsqlas
32+
$$
33+
declare
34+
r record;
35+
ttext;
36+
begin
37+
execute $1 into r;
38+
select pg_typeof(r.a) into t;
39+
return format('type %s value %s', t,r.a::text);
40+
end;
41+
$$;
42+
43+
select show_result_type('select 1 as a');
44+
-- currently this fails due to cached plan for pg_typeof expression
45+
-- (but a CLOBBER_CACHE_ALWAYS build will succeed)
46+
select show_result_type('select 2.0 as a');
47+
48+
-- but it's OK if we force plan rebuilding
49+
discard plans;
50+
select show_result_type('select 2.0 as a');

‎src/pl/plpgsql/src/sql/plpgsql_record.sql

Lines changed: 2 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -270,17 +270,8 @@ create function sillyaddone(int) returns int language plpgsql as
270270
$$ declare r mutable;beginr.f1 := $1; returnr.f1+1; end $$;
271271
select sillyaddone(42);
272272

273-
altertable mutable drop column f1;
274-
altertable mutable add column f1 float8;
275-
276-
-- currently, this fails due to cached plan for "r.f1 + 1" expression
277-
-- (but we can't actually show that, because a CLOBBER_CACHE_ALWAYS build
278-
-- will succeed)
279-
-- select sillyaddone(42);
280-
281-
-- but it's OK if we force plan rebuilding
282-
discard plans;
283-
select sillyaddone(42);
273+
-- test for change of type of column f1 should be here someday;
274+
-- for now see plpgsql_cache test
284275

285276
altertable mutable drop column f1;
286277
select sillyaddone(42);-- fail

0 commit comments

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