From c606f10ff832fc54258bb8a259a8e0daa46468ee Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 26 Jul 2018 18:18:37 -0400 Subject: [PATCH] Provide plpgsql tests for cases involving record field changes. We suppressed one of these test cases in commit feb1cc559 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 --- src/pl/plpgsql/src/Makefile | 2 +- src/pl/plpgsql/src/expected/plpgsql_cache.out | 67 +++++++++++++++++ .../plpgsql/src/expected/plpgsql_cache_1.out | 72 +++++++++++++++++++ .../plpgsql/src/expected/plpgsql_record.out | 16 +---- src/pl/plpgsql/src/sql/plpgsql_cache.sql | 50 +++++++++++++ src/pl/plpgsql/src/sql/plpgsql_record.sql | 13 +--- 6 files changed, 194 insertions(+), 26 deletions(-) create mode 100644 src/pl/plpgsql/src/expected/plpgsql_cache.out create mode 100644 src/pl/plpgsql/src/expected/plpgsql_cache_1.out create mode 100644 src/pl/plpgsql/src/sql/plpgsql_cache.sql diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index dd17092fd5..25a5a9d448 100644 --- a/src/pl/plpgsql/src/Makefile +++ b/src/pl/plpgsql/src/Makefile @@ -27,7 +27,7 @@ DATA = plpgsql.control plpgsql--1.0.sql plpgsql--unpackaged--1.0.sql REGRESS_OPTS = --dbname=$(PL_TESTDB) REGRESS = plpgsql_call plpgsql_control plpgsql_domain plpgsql_record \ - plpgsql_transaction plpgsql_varprops + plpgsql_cache plpgsql_transaction plpgsql_varprops all: all-lib diff --git a/src/pl/plpgsql/src/expected/plpgsql_cache.out b/src/pl/plpgsql/src/expected/plpgsql_cache.out new file mode 100644 index 0000000000..c2cf013605 --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_cache.out @@ -0,0 +1,67 @@ +-- +-- Cache-behavior-dependent test cases +-- +-- These tests logically belong in plpgsql_record.sql, and perhaps someday +-- can be merged back into it. For now, however, their results are different +-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two +-- expected-output files to cover both cases. To minimize the maintenance +-- effort resulting from that, this file should contain only tests that +-- do have different results under CLOBBER_CACHE_ALWAYS. +-- +-- check behavior with changes of a named rowtype +create table c_mutable(f1 int, f2 text); +create function c_sillyaddone(int) returns int language plpgsql as +$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$; +select c_sillyaddone(42); + c_sillyaddone +--------------- + 43 +(1 row) + +alter table c_mutable drop column f1; +alter table c_mutable add column f1 float8; +-- currently, this fails due to cached plan for "r.f1 + 1" expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select c_sillyaddone(42); +ERROR: type of parameter 4 (double precision) does not match that when preparing the plan (integer) +CONTEXT: PL/pgSQL function c_sillyaddone(integer) line 1 at RETURN +-- but it's OK if we force plan rebuilding +discard plans; +select c_sillyaddone(42); + c_sillyaddone +--------------- + 43 +(1 row) + +-- check behavior with changes in a record rowtype +create function show_result_type(text) returns text language plpgsql as +$$ + declare + r record; + t text; + begin + execute $1 into r; + select pg_typeof(r.a) into t; + return format('type %s value %s', t, r.a::text); + end; +$$; +select show_result_type('select 1 as a'); + show_result_type +---------------------- + type integer value 1 +(1 row) + +-- currently this fails due to cached plan for pg_typeof expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select show_result_type('select 2.0 as a'); +ERROR: type of parameter 5 (numeric) does not match that when preparing the plan (integer) +CONTEXT: SQL statement "select pg_typeof(r.a)" +PL/pgSQL function show_result_type(text) line 7 at SQL statement +-- but it's OK if we force plan rebuilding +discard plans; +select show_result_type('select 2.0 as a'); + show_result_type +------------------------ + type numeric value 2.0 +(1 row) + diff --git a/src/pl/plpgsql/src/expected/plpgsql_cache_1.out b/src/pl/plpgsql/src/expected/plpgsql_cache_1.out new file mode 100644 index 0000000000..0ac2c64a15 --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_cache_1.out @@ -0,0 +1,72 @@ +-- +-- Cache-behavior-dependent test cases +-- +-- These tests logically belong in plpgsql_record.sql, and perhaps someday +-- can be merged back into it. For now, however, their results are different +-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two +-- expected-output files to cover both cases. To minimize the maintenance +-- effort resulting from that, this file should contain only tests that +-- do have different results under CLOBBER_CACHE_ALWAYS. +-- +-- check behavior with changes of a named rowtype +create table c_mutable(f1 int, f2 text); +create function c_sillyaddone(int) returns int language plpgsql as +$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$; +select c_sillyaddone(42); + c_sillyaddone +--------------- + 43 +(1 row) + +alter table c_mutable drop column f1; +alter table c_mutable add column f1 float8; +-- currently, this fails due to cached plan for "r.f1 + 1" expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select c_sillyaddone(42); + c_sillyaddone +--------------- + 43 +(1 row) + +-- but it's OK if we force plan rebuilding +discard plans; +select c_sillyaddone(42); + c_sillyaddone +--------------- + 43 +(1 row) + +-- check behavior with changes in a record rowtype +create function show_result_type(text) returns text language plpgsql as +$$ + declare + r record; + t text; + begin + execute $1 into r; + select pg_typeof(r.a) into t; + return format('type %s value %s', t, r.a::text); + end; +$$; +select show_result_type('select 1 as a'); + show_result_type +---------------------- + type integer value 1 +(1 row) + +-- currently this fails due to cached plan for pg_typeof expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select show_result_type('select 2.0 as a'); + show_result_type +------------------------ + type numeric value 2.0 +(1 row) + +-- but it's OK if we force plan rebuilding +discard plans; +select show_result_type('select 2.0 as a'); + show_result_type +------------------------ + type numeric value 2.0 +(1 row) + diff --git a/src/pl/plpgsql/src/expected/plpgsql_record.out b/src/pl/plpgsql/src/expected/plpgsql_record.out index 6ea88b3de0..cc36231aef 100644 --- a/src/pl/plpgsql/src/expected/plpgsql_record.out +++ b/src/pl/plpgsql/src/expected/plpgsql_record.out @@ -421,20 +421,8 @@ select sillyaddone(42); 43 (1 row) -alter table mutable drop column f1; -alter table mutable add column f1 float8; --- currently, this fails due to cached plan for "r.f1 + 1" expression --- (but we can't actually show that, because a CLOBBER_CACHE_ALWAYS build --- will succeed) --- select sillyaddone(42); --- but it's OK if we force plan rebuilding -discard plans; -select sillyaddone(42); - sillyaddone -------------- - 43 -(1 row) - +-- test for change of type of column f1 should be here someday; +-- for now see plpgsql_cache test alter table mutable drop column f1; select sillyaddone(42); -- fail ERROR: record "r" has no field "f1" diff --git a/src/pl/plpgsql/src/sql/plpgsql_cache.sql b/src/pl/plpgsql/src/sql/plpgsql_cache.sql new file mode 100644 index 0000000000..f3b64d9209 --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_cache.sql @@ -0,0 +1,50 @@ +-- +-- Cache-behavior-dependent test cases +-- +-- These tests logically belong in plpgsql_record.sql, and perhaps someday +-- can be merged back into it. For now, however, their results are different +-- between regular and CLOBBER_CACHE_ALWAYS builds, so we must have two +-- expected-output files to cover both cases. To minimize the maintenance +-- effort resulting from that, this file should contain only tests that +-- do have different results under CLOBBER_CACHE_ALWAYS. +-- + +-- check behavior with changes of a named rowtype +create table c_mutable(f1 int, f2 text); + +create function c_sillyaddone(int) returns int language plpgsql as +$$ declare r c_mutable; begin r.f1 := $1; return r.f1 + 1; end $$; +select c_sillyaddone(42); + +alter table c_mutable drop column f1; +alter table c_mutable add column f1 float8; + +-- currently, this fails due to cached plan for "r.f1 + 1" expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select c_sillyaddone(42); + +-- but it's OK if we force plan rebuilding +discard plans; +select c_sillyaddone(42); + +-- check behavior with changes in a record rowtype +create function show_result_type(text) returns text language plpgsql as +$$ + declare + r record; + t text; + begin + execute $1 into r; + select pg_typeof(r.a) into t; + return format('type %s value %s', t, r.a::text); + end; +$$; + +select show_result_type('select 1 as a'); +-- currently this fails due to cached plan for pg_typeof expression +-- (but a CLOBBER_CACHE_ALWAYS build will succeed) +select show_result_type('select 2.0 as a'); + +-- but it's OK if we force plan rebuilding +discard plans; +select show_result_type('select 2.0 as a'); diff --git a/src/pl/plpgsql/src/sql/plpgsql_record.sql b/src/pl/plpgsql/src/sql/plpgsql_record.sql index aba6887994..88333d45e1 100644 --- a/src/pl/plpgsql/src/sql/plpgsql_record.sql +++ b/src/pl/plpgsql/src/sql/plpgsql_record.sql @@ -270,17 +270,8 @@ create function sillyaddone(int) returns int language plpgsql as $$ declare r mutable; begin r.f1 := $1; return r.f1 + 1; end $$; select sillyaddone(42); -alter table mutable drop column f1; -alter table mutable add column f1 float8; - --- currently, this fails due to cached plan for "r.f1 + 1" expression --- (but we can't actually show that, because a CLOBBER_CACHE_ALWAYS build --- will succeed) --- select sillyaddone(42); - --- but it's OK if we force plan rebuilding -discard plans; -select sillyaddone(42); +-- test for change of type of column f1 should be here someday; +-- for now see plpgsql_cache test alter table mutable drop column f1; select sillyaddone(42); -- fail -- 2.40.0