1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
|
-- basic tests for the TID data type
SELECT
'(0,0)'::tid as tid00,
'(0,1)'::tid as tid01,
'(-1,0)'::tid as tidm10,
'(4294967295,65535)'::tid as tidmax;
tid00 | tid01 | tidm10 | tidmax
-------+-------+----------------+--------------------
(0,0) | (0,1) | (4294967295,0) | (4294967295,65535)
(1 row)
SELECT '(4294967296,1)'::tid; -- error
ERROR: invalid input syntax for type tid: "(4294967296,1)"
LINE 1: SELECT '(4294967296,1)'::tid;
^
SELECT '(1,65536)'::tid; -- error
ERROR: invalid input syntax for type tid: "(1,65536)"
LINE 1: SELECT '(1,65536)'::tid;
^
-- Also try it with non-error-throwing API
SELECT pg_input_is_valid('(0)', 'tid');
pg_input_is_valid
-------------------
f
(1 row)
SELECT * FROM pg_input_error_info('(0)', 'tid');
message | detail | hint | sql_error_code
------------------------------------------+--------+------+----------------
invalid input syntax for type tid: "(0)" | | | 22P02
(1 row)
SELECT pg_input_is_valid('(0,-1)', 'tid');
pg_input_is_valid
-------------------
f
(1 row)
SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
message | detail | hint | sql_error_code
---------------------------------------------+--------+------+----------------
invalid input syntax for type tid: "(0,-1)" | | | 22P02
(1 row)
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
-- min() and max() for TIDs
INSERT INTO tid_tab VALUES (1), (2);
SELECT min(ctid) FROM tid_tab;
min
-------
(0,1)
(1 row)
SELECT max(ctid) FROM tid_tab;
max
-------
(0,2)
(1 row)
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
-- Materialized view
CREATE MATERIALIZED VIEW tid_matview AS SELECT a FROM tid_tab;
SELECT currtid2('tid_matview'::text, '(0,1)'::tid); -- fails
ERROR: tid (0, 1) is not valid for relation "tid_matview"
INSERT INTO tid_tab VALUES (1);
REFRESH MATERIALIZED VIEW tid_matview;
SELECT currtid2('tid_matview'::text, '(0,1)'::tid); -- ok
currtid2
----------
(0,1)
(1 row)
DROP MATERIALIZED VIEW tid_matview;
TRUNCATE tid_tab;
-- Sequence
CREATE SEQUENCE tid_seq;
SELECT currtid2('tid_seq'::text, '(0,1)'::tid); -- ok
currtid2
----------
(0,1)
(1 row)
DROP SEQUENCE tid_seq;
-- Index, fails with incorrect relation type
CREATE INDEX tid_ind ON tid_tab(a);
SELECT currtid2('tid_ind'::text, '(0,1)'::tid); -- fails
ERROR: cannot open relation "tid_ind"
DETAIL: This operation is not supported for indexes.
DROP INDEX tid_ind;
-- Partitioned table, no storage
CREATE TABLE tid_part (a int) PARTITION BY RANGE (a);
SELECT currtid2('tid_part'::text, '(0,1)'::tid); -- fails
ERROR: cannot look at latest visible tid for relation "public.tid_part"
DROP TABLE tid_part;
-- Views
-- ctid not defined in the view
CREATE VIEW tid_view_no_ctid AS SELECT a FROM tid_tab;
SELECT currtid2('tid_view_no_ctid'::text, '(0,1)'::tid); -- fails
ERROR: currtid cannot handle views with no CTID
DROP VIEW tid_view_no_ctid;
-- ctid fetched directly from the source table.
CREATE VIEW tid_view_with_ctid AS SELECT ctid, a FROM tid_tab;
SELECT currtid2('tid_view_with_ctid'::text, '(0,1)'::tid); -- fails
ERROR: tid (0, 1) is not valid for relation "tid_tab"
INSERT INTO tid_tab VALUES (1);
SELECT currtid2('tid_view_with_ctid'::text, '(0,1)'::tid); -- ok
currtid2
----------
(0,1)
(1 row)
DROP VIEW tid_view_with_ctid;
TRUNCATE tid_tab;
-- ctid attribute with incorrect data type
CREATE VIEW tid_view_fake_ctid AS SELECT 1 AS ctid, 2 AS a;
SELECT currtid2('tid_view_fake_ctid'::text, '(0,1)'::tid); -- fails
ERROR: ctid isn't of type TID
DROP VIEW tid_view_fake_ctid;
DROP TABLE tid_tab CASCADE;
|
