Tom Lane <t...@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <kevin.gritt...@wicourts.gov> writes:
>> If I'm reading this correctly, it would appear that nobody has
>> updated anything to a TOASTed value in a build against HEAD in
>> testing *anything* in the last two and a half months. And the
>> regression tests don't include a single UPDATE to a TOASTed value
>> anywhere. That seems like a significant code coverage
>> deficiency.
>
>> Attached is a patch to cure the latter of these. I'm submitting
>> this separately since it seems a good idea regardless of what
>> happens with the related SSI issue.
>
> Hmm, is there much regression coverage for TOAST inserts or
> deletes either?
There didn't appear to be. The attached provides minimal testing of
user-facing behavior of TOAST insert, update, and delete. It's
pretty basic, but a lot better than having no tests for this at all.
I can't help feeling that there should be a toast.sql test file
which reaches deeper, but I'm not exactly sure how far that would
go.
-Kevin
*** a/src/test/regress/expected/delete.out
--- b/src/test/regress/expected/delete.out
***************
*** 1,11 ****
CREATE TABLE delete_test (
id SERIAL PRIMARY KEY,
! a INT
);
NOTICE: CREATE TABLE will create implicit sequence "delete_test_id_seq" for
serial column "delete_test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"delete_test_pkey" for table "delete_test"
INSERT INTO delete_test (a) VALUES (10);
! INSERT INTO delete_test (a) VALUES (50);
INSERT INTO delete_test (a) VALUES (100);
-- allow an alias to be specified for DELETE's target table
DELETE FROM delete_test AS dt WHERE dt.a > 75;
--- 1,12 ----
CREATE TABLE delete_test (
id SERIAL PRIMARY KEY,
! a INT,
! b text
);
NOTICE: CREATE TABLE will create implicit sequence "delete_test_id_seq" for
serial column "delete_test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"delete_test_pkey" for table "delete_test"
INSERT INTO delete_test (a) VALUES (10);
! INSERT INTO delete_test (a, b) VALUES (50, repeat('x', 10000));
INSERT INTO delete_test (a) VALUES (100);
-- allow an alias to be specified for DELETE's target table
DELETE FROM delete_test AS dt WHERE dt.a > 75;
***************
*** 16,26 **** ERROR: invalid reference to FROM-clause entry for table
"delete_test"
LINE 1: DELETE FROM delete_test dt WHERE delete_test.a > 25;
^
HINT: Perhaps you meant to reference the table alias "dt".
! SELECT * FROM delete_test;
! id | a
! ----+----
! 1 | 10
! 2 | 50
(2 rows)
DROP TABLE delete_test;
--- 17,35 ----
LINE 1: DELETE FROM delete_test dt WHERE delete_test.a > 25;
^
HINT: Perhaps you meant to reference the table alias "dt".
! SELECT id, a, char_length(b) FROM delete_test;
! id | a | char_length
! ----+----+-------------
! 1 | 10 |
! 2 | 50 | 10000
(2 rows)
+ -- delete a row with a TOASTed value
+ DELETE FROM delete_test WHERE a > 25;
+ SELECT id, a, char_length(b) FROM delete_test;
+ id | a | char_length
+ ----+----+-------------
+ 1 | 10 |
+ (1 row)
+
DROP TABLE delete_test;
*** a/src/test/regress/expected/insert.out
--- b/src/test/regress/expected/insert.out
***************
*** 62,65 **** select * from inserttest;
--- 62,82 ----
2 | 3 | values are fun!
(7 rows)
+ --
+ -- TOASTed value test
+ --
+ insert into inserttest values(30, 50, repeat('x', 10000));
+ select col1, col2, char_length(col3) from inserttest;
+ col1 | col2 | char_length
+ ------+------+-------------
+ | 3 | 7
+ | 5 | 7
+ | 5 | 4
+ | 7 | 7
+ 10 | 20 | 2
+ -1 | 2 | 7
+ 2 | 3 | 15
+ 30 | 50 | 10000
+ (8 rows)
+
drop table inserttest;
*** a/src/test/regress/expected/update.out
--- b/src/test/regress/expected/update.out
***************
*** 87,90 **** ERROR: invalid reference to FROM-clause entry for table
"update_test"
--- 87,99 ----
LINE 1: UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a...
^
HINT: Perhaps you meant to reference the table alias "t".
+ -- Make sure that we can update to a TOASTed value.
+ UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
+ SELECT a, b, char_length(c) FROM update_test;
+ a | b | char_length
+ -----+----+-------------
+ 100 | 20 |
+ 11 | 41 | 10000
+ (2 rows)
+
DROP TABLE update_test;
*** a/src/test/regress/sql/delete.sql
--- b/src/test/regress/sql/delete.sql
***************
*** 1,10 ****
CREATE TABLE delete_test (
id SERIAL PRIMARY KEY,
! a INT
);
INSERT INTO delete_test (a) VALUES (10);
! INSERT INTO delete_test (a) VALUES (50);
INSERT INTO delete_test (a) VALUES (100);
-- allow an alias to be specified for DELETE's target table
--- 1,11 ----
CREATE TABLE delete_test (
id SERIAL PRIMARY KEY,
! a INT,
! b text
);
INSERT INTO delete_test (a) VALUES (10);
! INSERT INTO delete_test (a, b) VALUES (50, repeat('x', 10000));
INSERT INTO delete_test (a) VALUES (100);
-- allow an alias to be specified for DELETE's target table
***************
*** 14,19 **** DELETE FROM delete_test AS dt WHERE dt.a > 75;
-- to be referenced
DELETE FROM delete_test dt WHERE delete_test.a > 25;
! SELECT * FROM delete_test;
DROP TABLE delete_test;
--- 15,25 ----
-- to be referenced
DELETE FROM delete_test dt WHERE delete_test.a > 25;
! SELECT id, a, char_length(b) FROM delete_test;
!
! -- delete a row with a TOASTed value
! DELETE FROM delete_test WHERE a > 25;
!
! SELECT id, a, char_length(b) FROM delete_test;
DROP TABLE delete_test;
*** a/src/test/regress/sql/insert.sql
--- b/src/test/regress/sql/insert.sql
***************
*** 28,31 **** insert into inserttest values(10, 20, '40'), (-1, 2, DEFAULT),
--- 28,38 ----
select * from inserttest;
+ --
+ -- TOASTed value test
+ --
+ insert into inserttest values(30, 50, repeat('x', 10000));
+
+ select col1, col2, char_length(col3) from inserttest;
+
drop table inserttest;
*** a/src/test/regress/sql/update.sql
--- b/src/test/regress/sql/update.sql
***************
*** 54,57 **** UPDATE update_test SET (a,b) = (select a,b FROM update_test
where c = 'foo')
--- 54,61 ----
-- to the original table name
UPDATE update_test AS t SET b = update_test.b + 10 WHERE t.a = 10;
+ -- Make sure that we can update to a TOASTed value.
+ UPDATE update_test SET c = repeat('x', 10000) WHERE c = 'car';
+ SELECT a, b, char_length(c) FROM update_test;
+
DROP TABLE update_test;
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
