Skip to content

Commit 9f77ad2

Browse files
tglsfdctglsfdc
committed
Provide plpgsql tests for cases involving record field changes.
We suppressed one of these test cases in commit feb1cc5 because it was failing to produce the expected results on CLOBBER_CACHE_ALWAYS buildfarm 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 and CLOBBER_CACHE_ALWAYS cases, and provide variant expected files. Someday we should fix plpgsql's failure for change-of-field-type, and then the discrepancy will go away and we can fold these tests back into plpgsql_record.sql. But today is not that day. Discussion: https://postgr.es/m/87wotkfju1.fsf@news-spur.riddles.org.uk
1 parent 662d12a commit 9f77ad2

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_cache plpgsql_transaction plpgsql_varprops
3131

3232
all: all-lib
3333

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

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+

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

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"

‎src/pl/plpgsql/src/sql/plpgsql_cache.sql

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+
create table c_mutable(f1 int, f2 text);
14+
15+
create function c_sillyaddone(int) returns int language plpgsql as
16+
$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$;
17+
select c_sillyaddone(42);
18+
19+
alter table c_mutable drop column f1;
20+
alter table 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+
create function show_result_type(text) returns text language plpgsql as
32+
$$
33+
declare
34+
r record;
35+
t text;
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; begin r.f1 := $1; return r.f1 + 1; end $$;
271271
select sillyaddone(42);
272272

273-
alter table mutable drop column f1;
274-
alter table 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
alter table mutable drop column f1;
286277
select sillyaddone(42); -- fail

0 commit comments

Comments
 (0)