| blob | 384e751663abc3e680611e9243a01352abcd820e |
1 -- directory paths and dlsuffix are passed to us in environment variables
2 \getenv libdir PG_LIBDIR
3 \getenv dlsuffix PG_DLSUFFIX
4 \set regresslib :libdir '/regress' :dlsuffix
5 -- Function to assist with verifying EXPLAIN which includes costs. A series
6 -- of bool flags allows control over which portions are masked out
7 CREATE FUNCTION explain_mask_costs(query text, do_analyze bool,
8 hide_costs bool, hide_row_est bool, hide_width bool) RETURNS setof text
9 LANGUAGE plpgsql AS
10 $$
11 DECLARE
12 ln text;
13 analyze_str text;
14 BEGIN
15 IF do_analyze = true THEN
16 analyze_str := 'on';
17 ELSE
18 analyze_str := 'off';
19 END IF;
21 -- avoid jit related output by disabling it
22 SET LOCAL jit = 0;
24 FOR ln IN
25 EXECUTE format('explain (analyze %s, costs on, summary off, timing off, buffers off) %s',
26 analyze_str, query)
27 LOOP
28 IF hide_costs = true THEN
29 ln := regexp_replace(ln, 'cost=\d+\.\d\d\.\.\d+\.\d\d', 'cost=N..N');
30 END IF;
32 IF hide_row_est = true THEN
33 -- don't use 'g' so that we leave the actual rows intact
34 ln := regexp_replace(ln, 'rows=\d+', 'rows=N');
35 END IF;
37 IF hide_width = true THEN
38 ln := regexp_replace(ln, 'width=\d+', 'width=N');
39 END IF;
41 RETURN NEXT ln;
42 END LOOP;
43 END;
44 $$;
45 --
46 -- num_nulls()
47 --
48 SELECT num_nonnulls(NULL);
49 num_nonnulls
50 --------------
51 0
52 (1 row)
54 SELECT num_nonnulls('1');
55 num_nonnulls
56 --------------
57 1
58 (1 row)
60 SELECT num_nonnulls(NULL::text);
61 num_nonnulls
62 --------------
63 0
64 (1 row)
66 SELECT num_nonnulls(NULL::text, NULL::int);
67 num_nonnulls
68 --------------
69 0
70 (1 row)
72 SELECT num_nonnulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
73 num_nonnulls
74 --------------
75 4
76 (1 row)
78 SELECT num_nonnulls(VARIADIC '{1,2,NULL,3}'::int[]);
79 num_nonnulls
80 --------------
81 3
82 (1 row)
84 SELECT num_nonnulls(VARIADIC '{"1","2","3","4"}'::text[]);
85 num_nonnulls
86 --------------
87 4
88 (1 row)
90 SELECT num_nonnulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
91 num_nonnulls
92 --------------
93 99
94 (1 row)
96 SELECT num_nulls(NULL);
97 num_nulls
98 -----------
99 1
100 (1 row)
102 SELECT num_nulls('1');
103 num_nulls
104 -----------
105 0
106 (1 row)
108 SELECT num_nulls(NULL::text);
109 num_nulls
110 -----------
111 1
112 (1 row)
114 SELECT num_nulls(NULL::text, NULL::int);
115 num_nulls
116 -----------
117 2
118 (1 row)
120 SELECT num_nulls(1, 2, NULL::text, NULL::point, '', int8 '9', 1.0 / NULL);
121 num_nulls
122 -----------
123 3
124 (1 row)
126 SELECT num_nulls(VARIADIC '{1,2,NULL,3}'::int[]);
127 num_nulls
128 -----------
129 1
130 (1 row)
132 SELECT num_nulls(VARIADIC '{"1","2","3","4"}'::text[]);
133 num_nulls
134 -----------
135 0
136 (1 row)
138 SELECT num_nulls(VARIADIC ARRAY(SELECT CASE WHEN i <> 40 THEN i END FROM generate_series(1, 100) i));
139 num_nulls
140 -----------
141 1
142 (1 row)
144 -- special cases
145 SELECT num_nonnulls(VARIADIC NULL::text[]);
146 num_nonnulls
147 --------------
149 (1 row)
151 SELECT num_nonnulls(VARIADIC '{}'::int[]);
152 num_nonnulls
153 --------------
154 0
155 (1 row)
157 SELECT num_nulls(VARIADIC NULL::text[]);
158 num_nulls
159 -----------
161 (1 row)
163 SELECT num_nulls(VARIADIC '{}'::int[]);
164 num_nulls
165 -----------
166 0
167 (1 row)
169 -- should fail, one or more arguments is required
170 SELECT num_nonnulls();
171 ERROR: function num_nonnulls() does not exist
172 LINE 1: SELECT num_nonnulls();
173 ^
174 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
175 SELECT num_nulls();
176 ERROR: function num_nulls() does not exist
177 LINE 1: SELECT num_nulls();
178 ^
179 HINT: No function matches the given name and argument types. You might need to add explicit type casts.
180 --
181 -- canonicalize_path()
182 --
183 CREATE FUNCTION test_canonicalize_path(text)
184 RETURNS text
185 AS :'regresslib'
186 LANGUAGE C STRICT IMMUTABLE;
187 SELECT test_canonicalize_path('/');
188 test_canonicalize_path
189 ------------------------
190 /
191 (1 row)
193 SELECT test_canonicalize_path('/./abc/def/');
194 test_canonicalize_path
195 ------------------------
196 /abc/def
197 (1 row)
199 SELECT test_canonicalize_path('/./../abc/def');
200 test_canonicalize_path
201 ------------------------
202 /abc/def
203 (1 row)
205 SELECT test_canonicalize_path('/./../../abc/def/');
206 test_canonicalize_path
207 ------------------------
208 /abc/def
209 (1 row)
211 SELECT test_canonicalize_path('/abc/.././def/ghi');
212 test_canonicalize_path
213 ------------------------
214 /def/ghi
215 (1 row)
217 SELECT test_canonicalize_path('/abc/./../def/ghi//');
218 test_canonicalize_path
219 ------------------------
220 /def/ghi
221 (1 row)
223 SELECT test_canonicalize_path('/abc/def/../..');
224 test_canonicalize_path
225 ------------------------
226 /
227 (1 row)
229 SELECT test_canonicalize_path('/abc/def/../../..');
230 test_canonicalize_path
231 ------------------------
232 /
233 (1 row)
235 SELECT test_canonicalize_path('/abc/def/../../../../ghi/jkl');
236 test_canonicalize_path
237 ------------------------
238 /ghi/jkl
239 (1 row)
241 SELECT test_canonicalize_path('.');
242 test_canonicalize_path
243 ------------------------
244 .
245 (1 row)
247 SELECT test_canonicalize_path('./');
248 test_canonicalize_path
249 ------------------------
250 .
251 (1 row)
253 SELECT test_canonicalize_path('./abc/..');
254 test_canonicalize_path
255 ------------------------
256 .
257 (1 row)
259 SELECT test_canonicalize_path('abc/../');
260 test_canonicalize_path
261 ------------------------
262 .
263 (1 row)
265 SELECT test_canonicalize_path('abc/../def');
266 test_canonicalize_path
267 ------------------------
268 def
269 (1 row)
271 SELECT test_canonicalize_path('..');
272 test_canonicalize_path
273 ------------------------
274 ..
275 (1 row)
277 SELECT test_canonicalize_path('../abc/def');
278 test_canonicalize_path
279 ------------------------
280 ../abc/def
281 (1 row)
283 SELECT test_canonicalize_path('../abc/..');
284 test_canonicalize_path
285 ------------------------
286 ..
287 (1 row)
289 SELECT test_canonicalize_path('../abc/../def');
290 test_canonicalize_path
291 ------------------------
292 ../def
293 (1 row)
295 SELECT test_canonicalize_path('../abc/../../def/ghi');
296 test_canonicalize_path
297 ------------------------
298 ../../def/ghi
299 (1 row)
301 SELECT test_canonicalize_path('./abc/./def/.');
302 test_canonicalize_path
303 ------------------------
304 abc/def
305 (1 row)
307 SELECT test_canonicalize_path('./abc/././def/.');
308 test_canonicalize_path
309 ------------------------
310 abc/def
311 (1 row)
313 SELECT test_canonicalize_path('./abc/./def/.././ghi/../../../jkl/mno');
314 test_canonicalize_path
315 ------------------------
316 ../jkl/mno
317 (1 row)
319 --
320 -- pg_log_backend_memory_contexts()
321 --
322 -- Memory contexts are logged and they are not returned to the function.
323 -- Furthermore, their contents can vary depending on the timing. However,
324 -- we can at least verify that the code doesn't fail, and that the
325 -- permissions are set properly.
326 --
327 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
328 pg_log_backend_memory_contexts
329 --------------------------------
330 t
331 (1 row)
333 SELECT pg_log_backend_memory_contexts(pid) FROM pg_stat_activity
334 WHERE backend_type = 'checkpointer';
335 pg_log_backend_memory_contexts
336 --------------------------------
337 t
338 (1 row)
340 CREATE ROLE regress_log_memory;
341 SELECT has_function_privilege('regress_log_memory',
342 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- no
343 has_function_privilege
344 ------------------------
345 f
346 (1 row)
348 GRANT EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
349 TO regress_log_memory;
350 SELECT has_function_privilege('regress_log_memory',
351 'pg_log_backend_memory_contexts(integer)', 'EXECUTE'); -- yes
352 has_function_privilege
353 ------------------------
354 t
355 (1 row)
357 SET ROLE regress_log_memory;
358 SELECT pg_log_backend_memory_contexts(pg_backend_pid());
359 pg_log_backend_memory_contexts
360 --------------------------------
361 t
362 (1 row)
364 RESET ROLE;
365 REVOKE EXECUTE ON FUNCTION pg_log_backend_memory_contexts(integer)
366 FROM regress_log_memory;
367 DROP ROLE regress_log_memory;
368 --
369 -- Test some built-in SRFs
370 --
371 -- The outputs of these are variable, so we can't just print their results
372 -- directly, but we can at least verify that the code doesn't fail.
373 --
374 select setting as segsize
375 from pg_settings where name = 'wal_segment_size'
376 \gset
377 select count(*) > 0 as ok from pg_ls_waldir();
378 ok
379 ----
380 t
381 (1 row)
383 -- Test ProjectSet as well as FunctionScan
384 select count(*) > 0 as ok from (select pg_ls_waldir()) ss;
385 ok
386 ----
387 t
388 (1 row)
390 -- Test not-run-to-completion cases.
391 select * from pg_ls_waldir() limit 0;
392 name | size | modification
393 ------+------+--------------
394 (0 rows)
396 select count(*) > 0 as ok from (select * from pg_ls_waldir() limit 1) ss;
397 ok
398 ----
399 t
400 (1 row)
402 select (w).size = :segsize as ok
403 from (select pg_ls_waldir() w) ss where length((w).name) = 24 limit 1;
404 ok
405 ----
406 t
407 (1 row)
409 select count(*) >= 0 as ok from pg_ls_archive_statusdir();
410 ok
411 ----
412 t
413 (1 row)
415 select count(*) >= 0 as ok from pg_ls_summariesdir();
416 ok
417 ----
418 t
419 (1 row)
421 -- pg_read_file()
422 select length(pg_read_file('postmaster.pid')) > 20;
423 ?column?
424 ----------
425 t
426 (1 row)
428 select length(pg_read_file('postmaster.pid', 1, 20));
429 length
430 --------
431 20
432 (1 row)
434 -- Test missing_ok
435 select pg_read_file('does not exist'); -- error
436 ERROR: could not open file "does not exist" for reading: No such file or directory
437 select pg_read_file('does not exist', true) IS NULL; -- ok
438 ?column?
439 ----------
440 t
441 (1 row)
443 -- Test invalid argument
444 select pg_read_file('does not exist', 0, -1); -- error
445 ERROR: requested length cannot be negative
446 select pg_read_file('does not exist', 0, -1, true); -- error
447 ERROR: requested length cannot be negative
448 -- pg_read_binary_file()
449 select length(pg_read_binary_file('postmaster.pid')) > 20;
450 ?column?
451 ----------
452 t
453 (1 row)
455 select length(pg_read_binary_file('postmaster.pid', 1, 20));
456 length
457 --------
458 20
459 (1 row)
461 -- Test missing_ok
462 select pg_read_binary_file('does not exist'); -- error
463 ERROR: could not open file "does not exist" for reading: No such file or directory
464 select pg_read_binary_file('does not exist', true) IS NULL; -- ok
465 ?column?
466 ----------
467 t
468 (1 row)
470 -- Test invalid argument
471 select pg_read_binary_file('does not exist', 0, -1); -- error
472 ERROR: requested length cannot be negative
473 select pg_read_binary_file('does not exist', 0, -1, true); -- error
474 ERROR: requested length cannot be negative
475 -- pg_stat_file()
476 select size > 20, isdir from pg_stat_file('postmaster.pid');
477 ?column? | isdir
478 ----------+-------
479 t | f
480 (1 row)
482 -- pg_ls_dir()
483 select * from (select pg_ls_dir('.') a) a where a = 'base' limit 1;
484 a
485 ------
486 base
487 (1 row)
489 -- Test missing_ok (second argument)
490 select pg_ls_dir('does not exist', false, false); -- error
491 ERROR: could not open directory "does not exist": No such file or directory
492 select pg_ls_dir('does not exist', true, false); -- ok
493 pg_ls_dir
494 -----------
495 (0 rows)
497 -- Test include_dot_dirs (third argument)
498 select count(*) = 1 as dot_found
499 from pg_ls_dir('.', false, true) as ls where ls = '.';
500 dot_found
501 -----------
502 t
503 (1 row)
505 select count(*) = 1 as dot_found
506 from pg_ls_dir('.', false, false) as ls where ls = '.';
507 dot_found
508 -----------
509 f
510 (1 row)
512 -- pg_timezone_names()
513 select * from (select (pg_timezone_names()).name) ptn where name='UTC' limit 1;
514 name
515 ------
516 UTC
517 (1 row)
519 -- pg_tablespace_databases()
520 select count(*) > 0 from
521 (select pg_tablespace_databases(oid) as pts from pg_tablespace
522 where spcname = 'pg_default') pts
523 join pg_database db on pts.pts = db.oid;
524 ?column?
525 ----------
526 t
527 (1 row)
529 --
530 -- Test replication slot directory functions
531 --
532 CREATE ROLE regress_slot_dir_funcs;
533 -- Not available by default.
534 SELECT has_function_privilege('regress_slot_dir_funcs',
535 'pg_ls_logicalsnapdir()', 'EXECUTE');
536 has_function_privilege
537 ------------------------
538 f
539 (1 row)
541 SELECT has_function_privilege('regress_slot_dir_funcs',
542 'pg_ls_logicalmapdir()', 'EXECUTE');
543 has_function_privilege
544 ------------------------
545 f
546 (1 row)
548 SELECT has_function_privilege('regress_slot_dir_funcs',
549 'pg_ls_replslotdir(text)', 'EXECUTE');
550 has_function_privilege
551 ------------------------
552 f
553 (1 row)
555 GRANT pg_monitor TO regress_slot_dir_funcs;
556 -- Role is now part of pg_monitor, so these are available.
557 SELECT has_function_privilege('regress_slot_dir_funcs',
558 'pg_ls_logicalsnapdir()', 'EXECUTE');
559 has_function_privilege
560 ------------------------
561 t
562 (1 row)
564 SELECT has_function_privilege('regress_slot_dir_funcs',
565 'pg_ls_logicalmapdir()', 'EXECUTE');
566 has_function_privilege
567 ------------------------
568 t
569 (1 row)
571 SELECT has_function_privilege('regress_slot_dir_funcs',
572 'pg_ls_replslotdir(text)', 'EXECUTE');
573 has_function_privilege
574 ------------------------
575 t
576 (1 row)
578 DROP ROLE regress_slot_dir_funcs;
579 --
580 -- Test adding a support function to a subject function
581 --
582 CREATE FUNCTION my_int_eq(int, int) RETURNS bool
583 LANGUAGE internal STRICT IMMUTABLE PARALLEL SAFE
584 AS $$int4eq$$;
585 -- By default, planner does not think that's selective
586 EXPLAIN (COSTS OFF)
587 SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1
588 WHERE my_int_eq(a.unique2, 42);
589 QUERY PLAN
590 ----------------------------------------------
591 Hash Join
592 Hash Cond: (b.unique1 = a.unique1)
593 -> Seq Scan on tenk1 b
594 -> Hash
595 -> Seq Scan on tenk1 a
596 Filter: my_int_eq(unique2, 42)
597 (6 rows)
599 -- With support function that knows it's int4eq, we get a different plan
600 CREATE FUNCTION test_support_func(internal)
601 RETURNS internal
602 AS :'regresslib', 'test_support_func'
603 LANGUAGE C STRICT;
604 ALTER FUNCTION my_int_eq(int, int) SUPPORT test_support_func;
605 EXPLAIN (COSTS OFF)
606 SELECT * FROM tenk1 a JOIN tenk1 b ON a.unique1 = b.unique1
607 WHERE my_int_eq(a.unique2, 42);
608 QUERY PLAN
609 -------------------------------------------------
610 Nested Loop
611 -> Seq Scan on tenk1 a
612 Filter: my_int_eq(unique2, 42)
613 -> Index Scan using tenk1_unique1 on tenk1 b
614 Index Cond: (unique1 = a.unique1)
615 (5 rows)
617 -- Also test non-default rowcount estimate
618 CREATE FUNCTION my_gen_series(int, int) RETURNS SETOF integer
619 LANGUAGE internal STRICT IMMUTABLE PARALLEL SAFE
620 AS $$generate_series_int4$$
621 SUPPORT test_support_func;
622 EXPLAIN (COSTS OFF)
623 SELECT * FROM tenk1 a JOIN my_gen_series(1,1000) g ON a.unique1 = g;
624 QUERY PLAN
625 ----------------------------------------
626 Hash Join
627 Hash Cond: (g.g = a.unique1)
628 -> Function Scan on my_gen_series g
629 -> Hash
630 -> Seq Scan on tenk1 a
631 (5 rows)
633 EXPLAIN (COSTS OFF)
634 SELECT * FROM tenk1 a JOIN my_gen_series(1,10) g ON a.unique1 = g;
635 QUERY PLAN
636 -------------------------------------------------
637 Nested Loop
638 -> Function Scan on my_gen_series g
639 -> Index Scan using tenk1_unique1 on tenk1 a
640 Index Cond: (unique1 = g.g)
641 (4 rows)
643 --
644 -- Test the SupportRequestRows support function for generate_series_timestamp()
645 --
646 -- Ensure the row estimate matches the actual rows
647 SELECT explain_mask_costs($$
648 SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day') g(s);$$,
649 true, true, false, true);
650 explain_mask_costs
651 ------------------------------------------------------------------------------------------
652 Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30 loops=1)
653 (1 row)
655 -- As above but with generate_series_timestamp
656 SELECT explain_mask_costs($$
657 SELECT * FROM generate_series(TIMESTAMP '2024-02-01', TIMESTAMP '2024-03-01', INTERVAL '1 day') g(s);$$,
658 true, true, false, true);
659 explain_mask_costs
660 ------------------------------------------------------------------------------------------
661 Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30 loops=1)
662 (1 row)
664 -- As above but with generate_series_timestamptz_at_zone()
665 SELECT explain_mask_costs($$
666 SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '1 day', 'UTC') g(s);$$,
667 true, true, false, true);
668 explain_mask_costs
669 ------------------------------------------------------------------------------------------
670 Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30 loops=1)
671 (1 row)
673 -- Ensure the estimated and actual row counts match when the range isn't
674 -- evenly divisible by the step
675 SELECT explain_mask_costs($$
676 SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '7 day') g(s);$$,
677 true, true, false, true);
678 explain_mask_costs
679 ----------------------------------------------------------------------------------------
680 Function Scan on generate_series g (cost=N..N rows=5 width=N) (actual rows=5 loops=1)
681 (1 row)
683 -- Ensure the estimates match when step is decreasing
684 SELECT explain_mask_costs($$
685 SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '-1 day') g(s);$$,
686 true, true, false, true);
687 explain_mask_costs
688 ------------------------------------------------------------------------------------------
689 Function Scan on generate_series g (cost=N..N rows=30 width=N) (actual rows=30 loops=1)
690 (1 row)
692 -- Ensure an empty range estimates 1 row
693 SELECT explain_mask_costs($$
694 SELECT * FROM generate_series(TIMESTAMPTZ '2024-03-01', TIMESTAMPTZ '2024-02-01', INTERVAL '1 day') g(s);$$,
695 true, true, false, true);
696 explain_mask_costs
697 ----------------------------------------------------------------------------------------
698 Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0 loops=1)
699 (1 row)
701 -- Ensure we get the default row estimate for infinity values
702 SELECT explain_mask_costs($$
703 SELECT * FROM generate_series(TIMESTAMPTZ '-infinity', TIMESTAMPTZ 'infinity', INTERVAL '1 day') g(s);$$,
704 false, true, false, true);
705 explain_mask_costs
706 -------------------------------------------------------------------
707 Function Scan on generate_series g (cost=N..N rows=1000 width=N)
708 (1 row)
710 -- Ensure the row estimate behaves correctly when step size is zero.
711 -- We expect generate_series_timestamp() to throw the error rather than in
712 -- the support function.
713 SELECT * FROM generate_series(TIMESTAMPTZ '2024-02-01', TIMESTAMPTZ '2024-03-01', INTERVAL '0 day') g(s);
714 ERROR: step size cannot equal zero
715 --
716 -- Test the SupportRequestRows support function for generate_series_numeric()
717 --
718 -- Ensure the row estimate matches the actual rows
719 SELECT explain_mask_costs($$
720 SELECT * FROM generate_series(1.0, 25.0) g(s);$$,
721 true, true, false, true);
722 explain_mask_costs
723 ------------------------------------------------------------------------------------------
724 Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25 loops=1)
725 (1 row)
727 -- As above but with non-default step
728 SELECT explain_mask_costs($$
729 SELECT * FROM generate_series(1.0, 25.0, 2.0) g(s);$$,
730 true, true, false, true);
731 explain_mask_costs
732 ------------------------------------------------------------------------------------------
733 Function Scan on generate_series g (cost=N..N rows=13 width=N) (actual rows=13 loops=1)
734 (1 row)
736 -- Ensure the estimates match when step is decreasing
737 SELECT explain_mask_costs($$
738 SELECT * FROM generate_series(25.0, 1.0, -1.0) g(s);$$,
739 true, true, false, true);
740 explain_mask_costs
741 ------------------------------------------------------------------------------------------
742 Function Scan on generate_series g (cost=N..N rows=25 width=N) (actual rows=25 loops=1)
743 (1 row)
745 -- Ensure an empty range estimates 1 row
746 SELECT explain_mask_costs($$
747 SELECT * FROM generate_series(25.0, 1.0, 1.0) g(s);$$,
748 true, true, false, true);
749 explain_mask_costs
750 ----------------------------------------------------------------------------------------
751 Function Scan on generate_series g (cost=N..N rows=1 width=N) (actual rows=0 loops=1)
752 (1 row)
754 -- Ensure we get the default row estimate for error cases (infinity/NaN values
755 -- and zero step size)
756 SELECT explain_mask_costs($$
757 SELECT * FROM generate_series('-infinity'::NUMERIC, 'infinity'::NUMERIC, 1.0) g(s);$$,
758 false, true, false, true);
759 explain_mask_costs
760 -------------------------------------------------------------------
761 Function Scan on generate_series g (cost=N..N rows=1000 width=N)
762 (1 row)
764 SELECT explain_mask_costs($$
765 SELECT * FROM generate_series(1.0, 25.0, 'NaN'::NUMERIC) g(s);$$,
766 false, true, false, true);
767 explain_mask_costs
768 -------------------------------------------------------------------
769 Function Scan on generate_series g (cost=N..N rows=1000 width=N)
770 (1 row)
772 SELECT explain_mask_costs($$
773 SELECT * FROM generate_series(25.0, 2.0, 0.0) g(s);$$,
774 false, true, false, true);
775 explain_mask_costs
776 -------------------------------------------------------------------
777 Function Scan on generate_series g (cost=N..N rows=1000 width=N)
778 (1 row)
780 -- Test functions for control data
781 SELECT count(*) > 0 AS ok FROM pg_control_checkpoint();
782 ok
783 ----
784 t
785 (1 row)
787 SELECT count(*) > 0 AS ok FROM pg_control_init();
788 ok
789 ----
790 t
791 (1 row)
793 SELECT count(*) > 0 AS ok FROM pg_control_recovery();
794 ok
795 ----
796 t
797 (1 row)
799 SELECT count(*) > 0 AS ok FROM pg_control_system();
800 ok
801 ----
802 t
803 (1 row)
805 -- pg_split_walfile_name, pg_walfile_name & pg_walfile_name_offset
806 SELECT * FROM pg_split_walfile_name(NULL);
807 segment_number | timeline_id
808 ----------------+-------------
809 |
810 (1 row)
812 SELECT * FROM pg_split_walfile_name('invalid');
813 ERROR: invalid WAL file name "invalid"
814 SELECT segment_number > 0 AS ok_segment_number, timeline_id
815 FROM pg_split_walfile_name('000000010000000100000000');
816 ok_segment_number | timeline_id
817 -------------------+-------------
818 t | 1
819 (1 row)
821 SELECT segment_number > 0 AS ok_segment_number, timeline_id
822 FROM pg_split_walfile_name('ffffffFF00000001000000af');
823 ok_segment_number | timeline_id
824 -------------------+-------------
825 t | 4294967295
826 (1 row)
828 SELECT setting::int8 AS segment_size
829 FROM pg_settings
830 WHERE name = 'wal_segment_size'
831 \gset
832 SELECT segment_number, file_offset
833 FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size),
834 pg_split_walfile_name(file_name);
835 segment_number | file_offset
836 ----------------+-------------
837 1 | 0
838 (1 row)
840 SELECT segment_number, file_offset
841 FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size + 1),
842 pg_split_walfile_name(file_name);
843 segment_number | file_offset
844 ----------------+-------------
845 1 | 1
846 (1 row)
848 SELECT segment_number, file_offset = :segment_size - 1
849 FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1),
850 pg_split_walfile_name(file_name);
851 segment_number | ?column?
852 ----------------+----------
853 0 | t
854 (1 row)
856 -- pg_current_logfile
857 CREATE ROLE regress_current_logfile;
858 -- not available by default
859 SELECT has_function_privilege('regress_current_logfile',
860 'pg_current_logfile()', 'EXECUTE');
861 has_function_privilege
862 ------------------------
863 f
864 (1 row)
866 GRANT pg_monitor TO regress_current_logfile;
867 -- role has privileges of pg_monitor and can execute the function
868 SELECT has_function_privilege('regress_current_logfile',
869 'pg_current_logfile()', 'EXECUTE');
870 has_function_privilege
871 ------------------------
872 t
873 (1 row)
875 DROP ROLE regress_current_logfile;
876 -- pg_column_toast_chunk_id
877 CREATE TABLE test_chunk_id (a TEXT, b TEXT STORAGE EXTERNAL);
878 INSERT INTO test_chunk_id VALUES ('x', repeat('x', 8192));
879 SELECT t.relname AS toastrel FROM pg_class c
880 LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
881 WHERE c.relname = 'test_chunk_id'
882 \gset
883 SELECT pg_column_toast_chunk_id(a) IS NULL,
884 pg_column_toast_chunk_id(b) IN (SELECT chunk_id FROM pg_toast.:toastrel)
885 FROM test_chunk_id;
886 ?column? | ?column?
887 ----------+----------
888 t | t
889 (1 row)
891 DROP TABLE test_chunk_id;
892 DROP FUNCTION explain_mask_costs(text, bool, bool, bool, bool);
893 -- test stratnum support functions
894 SELECT gist_stratnum_identity(3::smallint);
895 gist_stratnum_identity
896 ------------------------
897 3
898 (1 row)
900 SELECT gist_stratnum_identity(18::smallint);
901 gist_stratnum_identity
902 ------------------------
903 18
904 (1 row)