src/bin/pgbench/t/001_pgbench_with_server.pl

   1
   2 # Copyright (c) 2021-2024, PostgreSQL Global Development Group
   3
   4 use strict;
   5 use warnings FATAL => 'all';
   6
   7 use PostgreSQL::Test::Cluster;
   8 use PostgreSQL::Test::Utils;
   9 use Test::More;
  10
  11 # Check the initial state of the data generated.  Tables for tellers and
  12 # branches use NULL for their filler attribute.  The table accounts uses
  13 # a non-NULL filler.  The history table should have no data.
  14 sub check_data_state
  15 {
  16         local $Test::Builder::Level = $Test::Builder::Level + 1;
  17         my $node = shift;
  18         my $type = shift;
  19
  20         my $sql_result = $node->safe_psql('postgres',
  21                 'SELECT count(*) AS null_count FROM pgbench_accounts WHERE filler IS NULL LIMIT 10;'
  22         );
  23         is($sql_result, '0',
  24                 "$type: filler column of pgbench_accounts has no NULL data");
  25         $sql_result = $node->safe_psql('postgres',
  26                 'SELECT count(*) AS null_count FROM pgbench_branches WHERE filler IS NULL;'
  27         );
  28         is($sql_result, '1',
  29                 "$type: filler column of pgbench_branches has only NULL data");
  30         $sql_result = $node->safe_psql('postgres',
  31                 'SELECT count(*) AS null_count FROM pgbench_tellers WHERE filler IS NULL;'
  32         );
  33         is($sql_result, '10',
  34                 "$type: filler column of pgbench_tellers has only NULL data");
  35         $sql_result = $node->safe_psql('postgres',
  36                 'SELECT count(*) AS data_count FROM pgbench_history;');
  37         is($sql_result, '0', "$type: pgbench_history has no data");
  38 }
  39
  40 # start a pgbench specific server
  41 my $node = PostgreSQL::Test::Cluster->new('main');
  42 # Set to untranslated messages, to be able to compare program output with
  43 # expected strings.
  44 $node->init(extra => [ '--locale', 'C' ]);
  45 $node->start;
  46
  47 # tablespace for testing, because partitioned tables cannot use pg_default
  48 # explicitly and we want to test that table creation with tablespace works
  49 # for partitioned tables.
  50 my $ts = $node->basedir . '/regress_pgbench_tap_1_ts_dir';
  51 mkdir $ts or die "cannot create directory $ts";
  52
  53 # the next commands will issue a syntax error if the path contains a "'"
  54 $node->safe_psql('postgres',
  55         "CREATE TABLESPACE regress_pgbench_tap_1_ts LOCATION '$ts';");
  56
  57 # Test concurrent OID generation via pg_enum_oid_index.  This indirectly
  58 # exercises LWLock and spinlock concurrency.
  59 my $labels = join ',', map { "'l$_'" } 1 .. 1000;
  60 $node->pgbench(
  61         '--no-vacuum --client=5 --protocol=prepared --transactions=25',
  62         0,
  63         [qr{processed: 125/125}],
  64         [qr{^$}],
  65         'concurrent OID generation',
  66         {
  67                 '001_pgbench_concurrent_insert' =>
  68                   "CREATE TYPE pg_temp.e AS ENUM ($labels); DROP TYPE pg_temp.e;"
  69         });
  70
  71 # Trigger various connection errors
  72 $node->pgbench(
  73         'no-such-database',
  74         1,
  75         [qr{^$}],
  76         [
  77                 qr{connection to server .* failed},
  78                 qr{FATAL:  database "no-such-database" does not exist}
  79         ],
  80         'no such database');
  81
  82 $node->pgbench(
  83         '-S -t 1', 1, [],
  84         [qr{Perhaps you need to do initialization}],
  85         'run without init');
  86
  87 # Initialize pgbench tables scale 1
  88 $node->pgbench(
  89         '-i', 0,
  90         [qr{^$}],
  91         [
  92                 qr{creating tables},
  93                 qr{vacuuming},
  94                 qr{creating primary keys},
  95                 qr{done in \d+\.\d\d s }
  96         ],
  97         'pgbench scale 1 initialization',);
  98
  99 # Check data state, after client-side data generation.
 100 check_data_state($node, 'client-side');
 101
 102 # Again, with all possible options
 103 $node->pgbench(
 104         '--initialize --init-steps=dtpvg --scale=1 --unlogged-tables --fillfactor=98 --foreign-keys --quiet --tablespace=regress_pgbench_tap_1_ts --index-tablespace=regress_pgbench_tap_1_ts --partitions=2 --partition-method=hash',
 105         0,
 106         [qr{^$}i],
 107         [
 108                 qr{dropping old tables},
 109                 qr{creating tables},
 110                 qr{creating 2 partitions},
 111                 qr{vacuuming},
 112                 qr{creating primary keys},
 113                 qr{creating foreign keys},
 114                 qr{(?!vacuuming)},    # no vacuum
 115                 qr{done in \d+\.\d\d s }
 116         ],
 117         'pgbench scale 1 initialization');
 118
 119 # Test interaction of --init-steps with legacy step-selection options
 120 $node->pgbench(
 121         '--initialize --init-steps=dtpvGvv --no-vacuum --foreign-keys --unlogged-tables --partitions=3',
 122         0,
 123         [qr{^$}],
 124         [
 125                 qr{dropping old tables},
 126                 qr{creating tables},
 127                 qr{creating 3 partitions},
 128                 qr{creating primary keys},
 129                 qr{generating data \(server-side\)},
 130                 qr{creating foreign keys},
 131                 qr{(?!vacuuming)},    # no vacuum
 132                 qr{done in \d+\.\d\d s }
 133         ],
 134         'pgbench --init-steps');
 135
 136 # Check data state, after server-side data generation.
 137 check_data_state($node, 'server-side');
 138
 139 # Run all builtin scripts, for a few transactions each
 140 $node->pgbench(
 141         '--transactions=5 -Dfoo=bla --client=2 --protocol=simple --builtin=t'
 142           . ' --connect -n -v -n',
 143         0,
 144         [
 145                 qr{builtin: TPC-B},
 146                 qr{clients: 2\b},
 147                 qr{processed: 10/10},
 148                 qr{mode: simple},
 149                 qr{maximum number of tries: 1}
 150         ],
 151         [qr{^$}],
 152         'pgbench tpcb-like');
 153
 154 $node->pgbench(
 155         '--transactions=20 --client=5 -M extended --builtin=si -C --no-vacuum -s 1',
 156         0,
 157         [
 158                 qr{builtin: simple update},
 159                 qr{clients: 5\b},
 160                 qr{threads: 1\b},
 161                 qr{processed: 100/100},
 162                 qr{mode: extended}
 163         ],
 164         [qr{scale option ignored}],
 165         'pgbench simple update');
 166
 167 $node->pgbench(
 168         '-t 100 -c 7 -M prepared -b se --debug',
 169         0,
 170         [
 171                 qr{builtin: select only},
 172                 qr{clients: 7\b},
 173                 qr{threads: 1\b},
 174                 qr{processed: 700/700},
 175                 qr{mode: prepared}
 176         ],
 177         [
 178                 qr{vacuum}, qr{client 0}, qr{client 1}, qr{sending},
 179                 qr{receiving}, qr{executing}
 180         ],
 181         'pgbench select only');
 182
 183 # check if threads are supported
 184 my $nthreads = 2;
 185
 186 {
 187         my ($stderr);
 188         run_log([ 'pgbench', '-j', '2', '--bad-option' ], '2>', \$stderr);
 189         $nthreads = 1 if $stderr =~ m/threads are not supported on this platform/;
 190 }
 191
 192 # run custom scripts
 193 $node->pgbench(
 194         "-t 100 -c 1 -j $nthreads -M prepared -n",
 195         0,
 196         [
 197                 qr{type: multiple scripts},
 198                 qr{mode: prepared},
 199                 qr{script 1: .*/001_pgbench_custom_script_1},
 200                 qr{weight: 2},
 201                 qr{script 2: .*/001_pgbench_custom_script_2},
 202                 qr{weight: 1},
 203                 qr{processed: 100/100}
 204         ],
 205         [qr{^$}],
 206         'pgbench custom scripts',
 207         {
 208                 '001_pgbench_custom_script_1@1' => q{-- select only
 209 \set aid random(1, :scale * 100000)
 210 SELECT abalance::INTEGER AS balance
 211   FROM pgbench_accounts
 212   WHERE aid=:aid;
 213 },
 214                 '001_pgbench_custom_script_2@2' => q{-- special variables
 215 BEGIN;
 216 \set foo 1
 217 -- cast are needed for typing under -M prepared
 218 SELECT :foo::INT + :scale::INT * :client_id::INT AS bla;
 219 COMMIT;
 220 }
 221         });
 222
 223 $node->pgbench(
 224         '-n -t 10 -c 1 -M simple',
 225         0,
 226         [
 227                 qr{type: .*/001_pgbench_custom_script_3},
 228                 qr{processed: 10/10},
 229                 qr{mode: simple}
 230         ],
 231         [qr{^$}],
 232         'pgbench custom script',
 233         {
 234                 '001_pgbench_custom_script_3' => q{-- select only variant
 235 \set aid random(1, :scale * 100000)
 236 BEGIN;
 237 SELECT abalance::INTEGER AS balance
 238   FROM pgbench_accounts
 239   WHERE aid=:aid;
 240 COMMIT;
 241 }
 242         });
 243
 244 $node->pgbench(
 245         '-n -t 10 -c 2 -M extended',
 246         0,
 247         [
 248                 qr{type: .*/001_pgbench_custom_script_4},
 249                 qr{processed: 20/20},
 250                 qr{mode: extended}
 251         ],
 252         [qr{^$}],
 253         'pgbench custom script',
 254         {
 255                 '001_pgbench_custom_script_4' => q{-- select only variant
 256 \set aid random(1, :scale * 100000)
 257 BEGIN;
 258 SELECT abalance::INTEGER AS balance
 259   FROM pgbench_accounts
 260   WHERE aid=:aid;
 261 COMMIT;
 262 }
 263         });
 264
 265 # Verify server logging of query parameters.
 266 # (This doesn't really belong here, but pgbench is a convenient way
 267 # to issue commands using extended query mode with parameters.)
 268
 269 # 1. Logging neither with errors nor with statements
 270 $node->append_conf('postgresql.conf',
 271                 "log_min_duration_statement = 0\n"
 272           . "log_parameter_max_length = 0\n"
 273           . "log_parameter_max_length_on_error = 0");
 274 $node->reload;
 275 $node->pgbench(
 276         '-n -t1 -c1 -M prepared',
 277         2,
 278         [],
 279         [
 280                 qr{ERROR:  invalid input syntax for type json},
 281                 qr{(?!unnamed portal with parameters)}
 282         ],
 283         'server parameter logging',
 284         {
 285                 '001_param_1' => q[select '{ invalid ' as value \gset
 286 select $$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$$ as long \gset
 287 select column1::jsonb from (values (:value), (:long)) as q;
 288 ]
 289         });
 290 my $log = PostgreSQL::Test::Utils::slurp_file($node->logfile);
 291 unlike(
 292         $log,
 293         qr[DETAIL:  Parameters: \$1 = '\{ invalid ',],
 294         "no parameters logged");
 295 $log = undef;
 296
 297 # 2. Logging truncated parameters on error, full with statements
 298 $node->append_conf('postgresql.conf',
 299                 "log_parameter_max_length = -1\n"
 300           . "log_parameter_max_length_on_error = 64");
 301 $node->reload;
 302 $node->pgbench(
 303         '-n -t1 -c1 -M prepared',
 304         2,
 305         [],
 306         [
 307                 qr{ERROR:  division by zero},
 308                 qr{CONTEXT:  unnamed portal with parameters: \$1 = '1', \$2 = NULL}
 309         ],
 310         'server parameter logging',
 311         {
 312                 '001_param_2' => q{select '1' as one \gset
 313 SELECT 1 / (random() / 2)::int, :one::int, :two::int;
 314 }
 315         });
 316 $node->pgbench(
 317         '-n -t1 -c1 -M prepared',
 318         2,
 319         [],
 320         [
 321                 qr{ERROR:  invalid input syntax for type json},
 322                 qr[CONTEXT:  JSON data, line 1: \{ invalid\.\.\.[\r\n]+unnamed portal with parameters: \$1 = '\{ invalid ', \$2 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que \.\.\.']m
 323         ],
 324         'server parameter logging',
 325         {
 326                 '001_param_3' => q[select '{ invalid ' as value \gset
 327 select $$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$$ as long \gset
 328 select column1::jsonb from (values (:value), (:long)) as q;
 329 ]
 330         });
 331 $log = PostgreSQL::Test::Utils::slurp_file($node->logfile);
 332 like(
 333         $log,
 334         qr[DETAIL:  Parameters: \$1 = '\{ invalid ', \$2 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia\?'''],
 335         "parameter report does not truncate");
 336 $log = undef;
 337
 338 # 3. Logging full parameters on error, truncated with statements
 339 $node->append_conf('postgresql.conf',
 340                 "log_min_duration_statement = -1\n"
 341           . "log_parameter_max_length = 7\n"
 342           . "log_parameter_max_length_on_error = -1");
 343 $node->reload;
 344 $node->pgbench(
 345         '-n -t1 -c1 -M prepared',
 346         2,
 347         [],
 348         [
 349                 qr{ERROR:  division by zero},
 350                 qr{CONTEXT:  unnamed portal with parameters: \$1 = '1', \$2 = NULL}
 351         ],
 352         'server parameter logging',
 353         {
 354                 '001_param_4' => q{select '1' as one \gset
 355 SELECT 1 / (random() / 2)::int, :one::int, :two::int;
 356 }
 357         });
 358
 359 $node->append_conf('postgresql.conf', "log_min_duration_statement = 0");
 360 $node->reload;
 361 $node->pgbench(
 362         '-n -t1 -c1 -M prepared',
 363         2,
 364         [],
 365         [
 366                 qr{ERROR:  invalid input syntax for type json},
 367                 qr[CONTEXT:  JSON data, line 1: \{ invalid\.\.\.[\r\n]+unnamed portal with parameters: \$1 = '\{ invalid ', \$2 = '''Valame Dios!'' dijo Sancho; ''no le dije yo a vuestra merced que mirase bien lo que hacia\?']m
 368         ],
 369         'server parameter logging',
 370         {
 371                 '001_param_5' => q[select '{ invalid ' as value \gset
 372 select $$'Valame Dios!' dijo Sancho; 'no le dije yo a vuestra merced que mirase bien lo que hacia?'$$ as long \gset
 373 select column1::jsonb from (values (:value), (:long)) as q;
 374 ]
 375         });
 376 $log = PostgreSQL::Test::Utils::slurp_file($node->logfile);
 377 like(
 378         $log,
 379         qr[DETAIL:  Parameters: \$1 = '\{ inval\.\.\.', \$2 = '''Valame\.\.\.'],
 380         "parameter report truncates");
 381 $log = undef;
 382
 383 # Check that bad parameters are reported during typinput phase of BIND
 384 $node->pgbench(
 385         '-n -t1 -c1 -M prepared',
 386         2,
 387         [],
 388         [
 389                 qr{ERROR:  invalid input syntax for type smallint: "1a"},
 390                 qr{CONTEXT:  unnamed portal parameter \$2 = '1a'}
 391         ],
 392         'server parameter logging',
 393         {
 394                 '001_param_6' => q{select 42 as value1, '1a' as value2 \gset
 395 select :value1::smallint, :value2::smallint;
 396 }
 397         });
 398
 399 # Restore default logging config
 400 $node->append_conf('postgresql.conf',
 401                 "log_min_duration_statement = -1\n"
 402           . "log_parameter_max_length_on_error = 0\n"
 403           . "log_parameter_max_length = -1");
 404 $node->reload;
 405
 406 # test expressions
 407 $node->pgbench(
 408         '--random-seed=5432 -t 1 -Dfoo=-10.1 -Dbla=false -Di=+3 -Dn=null -Dt=t -Df=of -Dd=1.0',
 409         0,
 410         [ qr{type: .*/001_pgbench_expressions}, qr{processed: 1/1} ],
 411         [
 412                 qr{setting random seed to 5432\b},
 413
 414                 # After explicit seeding, the four random checks (1-3,20) are
 415                 # deterministic; but see also magic values in checks 111,113.
 416                 qr{command=1.: int 17\b},      # uniform random
 417                 qr{command=2.: int 104\b},     # exponential random
 418                 qr{command=3.: int 1498\b},    # gaussian random
 419                 qr{command=4.: int 4\b},
 420                 qr{command=5.: int 5\b},
 421                 qr{command=6.: int 6\b},
 422                 qr{command=7.: int 7\b},
 423                 qr{command=8.: int 8\b},
 424                 qr{command=9.: int 9\b},
 425                 qr{command=10.: int 10\b},
 426                 qr{command=11.: int 11\b},
 427                 qr{command=12.: int 12\b},
 428                 qr{command=15.: double 15\b},
 429                 qr{command=16.: double 16\b},
 430                 qr{command=17.: double 17\b},
 431                 qr{command=20.: int 3\b},    # zipfian random
 432                 qr{command=21.: double -27\b},
 433                 qr{command=22.: double 1024\b},
 434                 qr{command=23.: double 1\b},
 435                 qr{command=24.: double 1\b},
 436                 qr{command=25.: double -0.125\b},
 437                 qr{command=26.: double -0.125\b},
 438                 qr{command=27.: double -0.00032\b},
 439                 qr{command=28.: double 8.50705917302346e\+0?37\b},
 440                 qr{command=29.: double 1e\+0?30\b},
 441                 qr{command=30.: boolean false\b},
 442                 qr{command=31.: boolean true\b},
 443                 qr{command=32.: int 32\b},
 444                 qr{command=33.: int 33\b},
 445                 qr{command=34.: double 34\b},
 446                 qr{command=35.: int 35\b},
 447                 qr{command=36.: int 36\b},
 448                 qr{command=37.: double 37\b},
 449                 qr{command=38.: int 38\b},
 450                 qr{command=39.: int 39\b},
 451                 qr{command=40.: boolean true\b},
 452                 qr{command=41.: null\b},
 453                 qr{command=42.: null\b},
 454                 qr{command=43.: boolean true\b},
 455                 qr{command=44.: boolean true\b},
 456                 qr{command=45.: boolean true\b},
 457                 qr{command=46.: int 46\b},
 458                 qr{command=47.: boolean true\b},
 459                 qr{command=48.: boolean true\b},
 460                 qr{command=49.: int -5817877081768721676\b},
 461                 qr{command=50.: boolean true\b},
 462                 qr{command=51.: int -7793829335365542153\b},
 463                 qr{command=52.: int -?\d+\b},
 464                 qr{command=53.: boolean true\b},
 465                 qr{command=65.: int 65\b},
 466                 qr{command=74.: int 74\b},
 467                 qr{command=83.: int 83\b},
 468                 qr{command=86.: int 86\b},
 469                 qr{command=93.: int 93\b},
 470                 qr{command=95.: int 0\b},
 471                 qr{command=96.: int 1\b},                       # :scale
 472                 qr{command=97.: int 0\b},                       # :client_id
 473                 qr{command=98.: int 5432\b},                    # :random_seed
 474                 qr{command=99.: int -9223372036854775808\b},    # min int
 475                 qr{command=100.: int 9223372036854775807\b},    # max int
 476                         # pseudorandom permutation tests
 477                 qr{command=101.: boolean true\b},
 478                 qr{command=102.: boolean true\b},
 479                 qr{command=103.: boolean true\b},
 480                 qr{command=104.: boolean true\b},
 481                 qr{command=105.: boolean true\b},
 482                 qr{command=109.: boolean true\b},
 483                 qr{command=110.: boolean true\b},
 484                 qr{command=111.: boolean true\b},
 485                 qr{command=113.: boolean true\b},
 486         ],
 487         'pgbench expressions',
 488         {
 489                 '001_pgbench_expressions' => q{-- integer functions
 490 \set i1 debug(random(10, 19))
 491 \set i2 debug(random_exponential(100, 199, 10.0))
 492 \set i3 debug(random_gaussian(1000, 1999, 10.0))
 493 \set i4 debug(abs(-4))
 494 \set i5 debug(greatest(5, 4, 3, 2))
 495 \set i6 debug(11 + least(-5, -4, -3, -2))
 496 \set i7 debug(int(7.3))
 497 -- integer arithmetic and bit-wise operators
 498 \set i8 debug(17 / (4|1) + ( 4 + (7 >> 2)))
 499 \set i9 debug(- (3 * 4 - (-(~ 1) + -(~ 0))) / -1 + 3 % -1)
 500 \set ia debug(10 + (0 + 0 * 0 - 0 / 1))
 501 \set ib debug(:ia + :scale)
 502 \set ic debug(64 % (((2 + 1 * 2 + (1 # 2) | 4 * (2 & 11)) - (1 << 2)) + 2))
 503 -- double functions and operators
 504 \set d1 debug(sqrt(+1.5 * 2.0) * abs(-0.8E1))
 505 \set d2 debug(double(1 + 1) * (-75.0 / :foo))
 506 \set pi debug(pi() * 4.9)
 507 \set d4 debug(greatest(4, 2, -1.17) * 4.0 * Ln(Exp(1.0)))
 508 \set d5 debug(least(-5.18, .0E0, 1.0/0) * -3.3)
 509 -- reset variables
 510 \set i1 0
 511 \set d1 false
 512 -- yet another integer function
 513 \set id debug(random_zipfian(1, 9, 1.3))
 514 --- pow and power
 515 \set poweri debug(pow(-3,3))
 516 \set powerd debug(pow(2.0,10))
 517 \set poweriz debug(pow(0,0))
 518 \set powerdz debug(pow(0.0,0.0))
 519 \set powernegi debug(pow(-2,-3))
 520 \set powernegd debug(pow(-2.0,-3.0))
 521 \set powernegd2 debug(power(-5.0,-5.0))
 522 \set powerov debug(pow(9223372036854775807, 2))
 523 \set powerov2 debug(pow(10,30))
 524 -- comparisons and logical operations
 525 \set c0 debug(1.0 = 0.0 and 1.0 != 0.0)
 526 \set c1 debug(0 = 1 Or 1.0 = 1)
 527 \set c4 debug(case when 0 < 1 then 32 else 0 end)
 528 \set c5 debug(case when true then 33 else 0 end)
 529 \set c6 debug(case when false THEN -1 when 1 = 1 then 13 + 19 + 2.0 end )
 530 \set c7 debug(case when (1 > 0) and (1 >= 0) and (0 < 1) and (0 <= 1) and (0 != 1) and (0 = 0) and (0 <> 1) then 35 else 0 end)
 531 \set c8 debug(CASE \
 532                 WHEN (1.0 > 0.0) AND (1.0 >= 0.0) AND (0.0 < 1.0) AND (0.0 <= 1.0) AND \
 533                      (0.0 != 1.0) AND (0.0 = 0.0) AND (0.0 <> 1.0) AND (0.0 = 0.0) \
 534                   THEN 36 \
 535                   ELSE 0 \
 536               END)
 537 \set c9 debug(CASE WHEN NOT FALSE THEN 3 * 12.3333334 END)
 538 \set ca debug(case when false then 0 when 1-1 <> 0 then 1 else 38 end)
 539 \set cb debug(10 + mod(13 * 7 + 12, 13) - mod(-19 * 11 - 17, 19))
 540 \set cc debug(NOT (0 > 1) AND (1 <= 1) AND NOT (0 >= 1) AND (0 < 1) AND \
 541     NOT (false and true) AND (false OR TRUE) AND (NOT :f) AND (NOT FALSE) AND \
 542     NOT (NOT TRUE))
 543 -- NULL value and associated operators
 544 \set n0 debug(NULL + NULL * exp(NULL))
 545 \set n1 debug(:n0)
 546 \set n2 debug(NOT (:n0 IS NOT NULL OR :d1 IS NULL))
 547 \set n3 debug(:n0 IS NULL AND :d1 IS NOT NULL AND :d1 NOTNULL)
 548 \set n4 debug(:n0 ISNULL AND NOT :n0 IS TRUE AND :n0 IS NOT FALSE)
 549 \set n5 debug(CASE WHEN :n IS NULL THEN 46 ELSE NULL END)
 550 -- use a variables of all types
 551 \set n6 debug(:n IS NULL AND NOT :f AND :t)
 552 -- conditional truth
 553 \set cs debug(CASE WHEN 1 THEN TRUE END AND CASE WHEN 1.0 THEN TRUE END AND CASE WHEN :n THEN NULL ELSE TRUE END)
 554 -- hash functions
 555 \set h0 debug(hash(10, 5432))
 556 \set h1 debug(:h0 = hash_murmur2(10, 5432))
 557 \set h3 debug(hash_fnv1a(10, 5432))
 558 \set h4 debug(hash(10))
 559 \set h5 debug(hash(10) = hash(10, :default_seed))
 560 -- lazy evaluation
 561 \set zy 0
 562 \set yz debug(case when :zy = 0 then -1 else (1 / :zy) end)
 563 \set yz debug(case when :zy = 0 or (1 / :zy) < 0 then -1 else (1 / :zy) end)
 564 \set yz debug(case when :zy > 0 and (1 / :zy) < 0 then (1 / :zy) else 1 end)
 565 -- substitute variables of all possible types
 566 \set v0 NULL
 567 \set v1 TRUE
 568 \set v2 5432
 569 \set v3 -54.21E-2
 570 SELECT :v0, :v1, :v2, :v3;
 571 -- if tests
 572 \set nope 0
 573 \if 1 > 0
 574 \set id debug(65)
 575 \elif 0
 576 \set nope 1
 577 \else
 578 \set nope 1
 579 \endif
 580 \if 1 < 0
 581 \set nope 1
 582 \elif 1 > 0
 583 \set ie debug(74)
 584 \else
 585 \set nope 1
 586 \endif
 587 \if 1 < 0
 588 \set nope 1
 589 \elif 1 < 0
 590 \set nope 1
 591 \else
 592 \set if debug(83)
 593 \endif
 594 \if 1 = 1
 595 \set ig debug(86)
 596 \elif 0
 597 \set nope 1
 598 \endif
 599 \if 1 = 0
 600 \set nope 1
 601 \elif 1 <> 0
 602 \set ih debug(93)
 603 \endif
 604 -- must be zero if false branches where skipped
 605 \set nope debug(:nope)
 606 -- check automatic variables
 607 \set sc debug(:scale)
 608 \set ci debug(:client_id)
 609 \set rs debug(:random_seed)
 610 -- minint constant parsing
 611 \set min debug(-9223372036854775808)
 612 \set max debug(-(:min + 1))
 613 -- parametric pseudorandom permutation function
 614 \set t debug(permute(0, 2) + permute(1, 2) = 1)
 615 \set t debug(permute(0, 3) + permute(1, 3) + permute(2, 3) = 3)
 616 \set t debug(permute(0, 4) + permute(1, 4) + permute(2, 4) + permute(3, 4) = 6)
 617 \set t debug(permute(0, 5) + permute(1, 5) + permute(2, 5) + permute(3, 5) + permute(4, 5) = 10)
 618 \set t debug(permute(0, 16) + permute(1, 16) + permute(2, 16) + permute(3, 16) + \
 619              permute(4, 16) + permute(5, 16) + permute(6, 16) + permute(7, 16) + \
 620              permute(8, 16) + permute(9, 16) + permute(10, 16) + permute(11, 16) + \
 621              permute(12, 16) + permute(13, 16) + permute(14, 16) + permute(15, 16) = 120)
 622 -- random sanity checks
 623 \set size random(2, 1000)
 624 \set v random(0, :size - 1)
 625 \set p permute(:v, :size)
 626 \set t debug(0 <= :p and :p < :size and :p = permute(:v + :size, :size) and :p <> permute(:v + 1, :size))
 627 -- actual values
 628 \set t debug(permute(:v, 1) = 0)
 629 \set t debug(permute(0, 2, 5431) = 0 and permute(1, 2, 5431) = 1 and \
 630              permute(0, 2, 5433) = 1 and permute(1, 2, 5433) = 0)
 631 -- check permute's portability across architectures
 632 \set size debug(:max - 10)
 633 \set t debug(permute(:size-1, :size, 5432) = 520382784483822430 and \
 634              permute(:size-2, :size, 5432) = 1143715004660802862 and \
 635              permute(:size-3, :size, 5432) = 447293596416496998 and \
 636              permute(:size-4, :size, 5432) = 916527772266572956 and \
 637              permute(:size-5, :size, 5432) = 2763809008686028849 and \
 638              permute(:size-6, :size, 5432) = 8648551549198294572 and \
 639              permute(:size-7, :size, 5432) = 4542876852200565125)
 640 }
 641         });
 642
 643 # random determinism when seeded
 644 $node->safe_psql('postgres',
 645         'CREATE UNLOGGED TABLE seeded_random(seed INT8 NOT NULL, rand TEXT NOT NULL, val INTEGER NOT NULL);'
 646 );
 647
 648 # same value to check for determinism
 649 my $seed = int(rand(1000000000));
 650 for my $i (1, 2)
 651 {
 652         $node->pgbench(
 653                 "--random-seed=$seed -t 1",
 654                 0,
 655                 [qr{processed: 1/1}],
 656                 [qr{setting random seed to $seed\b}],
 657                 "random seeded with $seed",
 658                 {
 659                         "001_pgbench_random_seed_$i" => q{-- test random functions
 660 \set ur random(1000, 1999)
 661 \set er random_exponential(2000, 2999, 2.0)
 662 \set gr random_gaussian(3000, 3999, 3.0)
 663 \set zr random_zipfian(4000, 4999, 1.5)
 664 INSERT INTO seeded_random(seed, rand, val) VALUES
 665   (:random_seed, 'uniform', :ur),
 666   (:random_seed, 'exponential', :er),
 667   (:random_seed, 'gaussian', :gr),
 668   (:random_seed, 'zipfian', :zr);
 669 }
 670                 });
 671 }
 672
 673 # check that all runs generated the same 4 values
 674 my ($ret, $out, $err) = $node->psql('postgres',
 675         'SELECT seed, rand, val, COUNT(*) FROM seeded_random GROUP BY seed, rand, val'
 676 );
 677
 678 ok($ret == 0, "psql seeded_random count ok");
 679 ok($err eq '', "psql seeded_random count stderr is empty");
 680 ok($out =~ /\b$seed\|uniform\|1\d\d\d\|2/,
 681         "psql seeded_random count uniform");
 682 ok( $out =~ /\b$seed\|exponential\|2\d\d\d\|2/,
 683         "psql seeded_random count exponential");
 684 ok( $out =~ /\b$seed\|gaussian\|3\d\d\d\|2/,
 685         "psql seeded_random count gaussian");
 686 ok($out =~ /\b$seed\|zipfian\|4\d\d\d\|2/,
 687         "psql seeded_random count zipfian");
 688
 689 $node->safe_psql('postgres', 'DROP TABLE seeded_random;');
 690
 691 # backslash commands
 692 $node->pgbench(
 693         '-t 1', 0,
 694         [
 695                 qr{type: .*/001_pgbench_backslash_commands},
 696                 qr{processed: 1/1},
 697                 qr{shell-echo-output}
 698         ],
 699         [qr{command=8.: int 1\b}],
 700         'pgbench backslash commands',
 701         {
 702                 '001_pgbench_backslash_commands' => q{-- run set
 703 \set zero 0
 704 \set one 1.0
 705 -- sleep
 706 \sleep :one ms
 707 \sleep 100 us
 708 \sleep 0 s
 709 \sleep :zero
 710 -- setshell and continuation
 711 \setshell another_one\
 712   echo \
 713     :one
 714 \set n debug(:another_one)
 715 -- shell
 716 \shell echo shell-echo-output
 717 }
 718         });
 719
 720 # working \gset
 721 $node->pgbench(
 722         '-t 1', 0,
 723         [ qr{type: .*/001_pgbench_gset}, qr{processed: 1/1} ],
 724         [
 725                 qr{command=3.: int 0\b},
 726                 qr{command=5.: int 1\b},
 727                 qr{command=6.: int 2\b},
 728                 qr{command=8.: int 3\b},
 729                 qr{command=10.: int 4\b},
 730                 qr{command=12.: int 5\b}
 731         ],
 732         'pgbench gset command',
 733         {
 734                 '001_pgbench_gset' => q{-- test gset
 735 -- no columns
 736 SELECT \gset
 737 -- one value
 738 SELECT 0 AS i0 \gset
 739 \set i debug(:i0)
 740 -- two values
 741 SELECT 1 AS i1, 2 AS i2 \gset
 742 \set i debug(:i1)
 743 \set i debug(:i2)
 744 -- with prefix
 745 SELECT 3 AS i3 \gset x_
 746 \set i debug(:x_i3)
 747 -- overwrite existing variable
 748 SELECT 0 AS i4, 4 AS i4 \gset
 749 \set i debug(:i4)
 750 -- work on the last SQL command under \;
 751 \; \; SELECT 0 AS i5 \; SELECT 5 AS i5 \; \; \gset
 752 \set i debug(:i5)
 753 }
 754         });
 755 # \gset cannot accept more than one row, causing command to fail.
 756 $node->pgbench(
 757         '-t 1', 2,
 758         [ qr{type: .*/001_pgbench_gset_two_rows}, qr{processed: 0/1} ],
 759         [qr{expected one row, got 2\b}],
 760         'pgbench gset command with two rows',
 761         {
 762                 '001_pgbench_gset_two_rows' => q{
 763 SELECT 5432 AS fail UNION SELECT 5433 ORDER BY 1 \gset
 764 }
 765         });
 766
 767 # working \aset
 768 # Valid cases.
 769 $node->pgbench(
 770         '-t 1', 0,
 771         [ qr{type: .*/001_pgbench_aset}, qr{processed: 1/1} ],
 772         [ qr{command=3.: int 8\b}, qr{command=4.: int 7\b} ],
 773         'pgbench aset command',
 774         {
 775                 '001_pgbench_aset' => q{
 776 -- test aset, which applies to a combined query
 777 \; SELECT 6 AS i6 \; SELECT 7 AS i7 \; \aset
 778 -- unless it returns more than one row, last is kept
 779 SELECT 8 AS i6 UNION SELECT 9 ORDER BY 1 DESC \aset
 780 \set i debug(:i6)
 781 \set i debug(:i7)
 782 }
 783         });
 784 # Empty result set with \aset, causing command to fail.
 785 $node->pgbench(
 786         '-t 1', 2,
 787         [ qr{type: .*/001_pgbench_aset_empty}, qr{processed: 0/1} ],
 788         [
 789                 qr{undefined variable \"i8\"},
 790                 qr{evaluation of meta-command failed\b}
 791         ],
 792         'pgbench aset command with empty result',
 793         {
 794                 '001_pgbench_aset_empty' => q{
 795 -- empty result
 796 \; SELECT 5432 AS i8 WHERE FALSE \; \aset
 797 \set i debug(:i8)
 798 }
 799         });
 800
 801 # Working \startpipeline
 802 $node->pgbench(
 803         '-t 1 -n -M extended',
 804         0,
 805         [ qr{type: .*/001_pgbench_pipeline}, qr{actually processed: 1/1} ],
 806         [],
 807         'working \startpipeline',
 808         {
 809                 '001_pgbench_pipeline' => q{
 810 -- test startpipeline
 811 \startpipeline
 812 } . "select 1;\n" x 10 . q{
 813 \endpipeline
 814 }
 815         });
 816
 817 # Working \startpipeline in prepared query mode
 818 $node->pgbench(
 819         '-t 1 -n -M prepared',
 820         0,
 821         [ qr{type: .*/001_pgbench_pipeline_prep}, qr{actually processed: 1/1} ],
 822         [],
 823         'working \startpipeline',
 824         {
 825                 '001_pgbench_pipeline_prep' => q{
 826 -- test startpipeline
 827 \startpipeline
 828 \endpipeline
 829 \startpipeline
 830 } . "select 1;\n" x 10 . q{
 831 \endpipeline
 832 }
 833         });
 834
 835 # Try \startpipeline twice
 836 $node->pgbench(
 837         '-t 1 -n -M extended',
 838         2,
 839         [],
 840         [qr{already in pipeline mode}],
 841         'error: call \startpipeline twice',
 842         {
 843                 '001_pgbench_pipeline_2' => q{
 844 -- startpipeline twice
 845 \startpipeline
 846 \startpipeline
 847 }
 848         });
 849
 850 # Try to end a pipeline that hasn't started
 851 $node->pgbench(
 852         '-t 1 -n -M extended',
 853         2,
 854         [],
 855         [qr{not in pipeline mode}],
 856         'error: \endpipeline with no start',
 857         {
 858                 '001_pgbench_pipeline_3' => q{
 859 -- pipeline not started
 860 \endpipeline
 861 }
 862         });
 863
 864 # Try \gset in pipeline mode
 865 $node->pgbench(
 866         '-t 1 -n -M extended',
 867         2,
 868         [],
 869         [qr{gset is not allowed in pipeline mode}],
 870         'error: \gset not allowed in pipeline mode',
 871         {
 872                 '001_pgbench_pipeline_4' => q{
 873 \startpipeline
 874 select 1 \gset f
 875 \endpipeline
 876 }
 877         });
 878
 879 # Try \startpipeline without \endpipeline in a single transaction
 880 $node->pgbench(
 881         '-t 1 -n -M extended',
 882         2,
 883         [],
 884         [qr{end of script reached with pipeline open}],
 885         'error: call \startpipeline without \endpipeline in a single transaction',
 886         {
 887                 '001_pgbench_pipeline_5' => q{
 888 -- startpipeline only with single transaction
 889 \startpipeline
 890 }
 891         });
 892
 893 # Try \startpipeline without \endpipeline
 894 $node->pgbench(
 895         '-t 2 -n -M extended',
 896         2,
 897         [],
 898         [qr{end of script reached with pipeline open}],
 899         'error: call \startpipeline without \endpipeline',
 900         {
 901                 '001_pgbench_pipeline_6' => q{
 902 -- startpipeline only
 903 \startpipeline
 904 }
 905         });
 906
 907 # Working \startpipeline in prepared query mode with serializable
 908 $node->pgbench(
 909         '-c4 -t 10 -n -M prepared',
 910         0,
 911         [
 912                 qr{type: .*/001_pgbench_pipeline_serializable},
 913                 qr{actually processed: (\d+)/\1}
 914         ],
 915         [],
 916         'working \startpipeline with serializable',
 917         {
 918                 '001_pgbench_pipeline_serializable' => q{
 919 -- test startpipeline with serializable
 920 \startpipeline
 921 BEGIN ISOLATION LEVEL SERIALIZABLE;
 922 } . "select 1;\n" x 10 . q{
 923 END;
 924 \endpipeline
 925 }
 926         });
 927
 928 # trigger many expression errors
 929 my @errors = (
 930
 931         # [ test name, expected status, expected stderr, script ]
 932         # SQL
 933         [
 934                 'sql syntax error',
 935                 2,
 936                 [
 937                         qr{ERROR:  syntax error},
 938                         qr{prepared statement .* does not exist}
 939                 ],
 940                 q{-- SQL syntax error
 941     SELECT 1 + ;
 942 }
 943         ],
 944         [
 945                 'sql too many args', 1,
 946                 [qr{statement has too many arguments.*\b255\b}],
 947                 q{-- MAX_ARGS=256 for prepared
 948 \set i 0
 949 SELECT LEAST(} . join(', ', (':i') x 256) . q{)}
 950         ],
 951
 952         # SHELL
 953         [
 954                 'shell bad command', 2,
 955                 [qr{\(shell\) .* meta-command failed}], q{\shell no-such-command}
 956         ],
 957         [
 958                 'shell undefined variable', 2,
 959                 [qr{undefined variable ":nosuchvariable"}],
 960                 q{-- undefined variable in shell
 961 \shell echo ::foo :nosuchvariable
 962 }
 963         ],
 964         [ 'shell missing command', 1, [qr{missing command }], q{\shell} ],
 965         [
 966                 'shell too many args', 1, [qr{too many arguments in command "shell"}],
 967                 q{-- 256 arguments to \shell
 968 \shell echo } . join(' ', ('arg') x 255)
 969         ],
 970
 971         # SET
 972         [
 973                 'set syntax error', 1,
 974                 [qr{syntax error in command "set"}], q{\set i 1 +}
 975         ],
 976         [
 977                 'set no such function', 1,
 978                 [qr{unexpected function name}], q{\set i noSuchFunction()}
 979         ],
 980         [
 981                 'set invalid variable name', 2,
 982                 [qr{invalid variable name}], q{\set . 1}
 983         ],
 984         [ 'set division by zero', 2, [qr{division by zero}], q{\set i 1/0} ],
 985         [
 986                 'set undefined variable',
 987                 2,
 988                 [qr{undefined variable "nosuchvariable"}],
 989                 q{\set i :nosuchvariable}
 990         ],
 991         [ 'set unexpected char', 1, [qr{unexpected character .;.}], q{\set i ;} ],
 992         [
 993                 'set too many args',
 994                 2,
 995                 [qr{too many function arguments}],
 996                 q{\set i least(0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16)}
 997         ],
 998         [
 999                 'set empty random range', 2,
1000                 [qr{empty range given to random}], q{\set i random(5,3)}
1001         ],
1002         [
1003                 'set random range too large', 2,
1004                 [qr{random range is too large}], q{\set i random(:minint, :maxint)}
1005         ],
1006         [
1007                 'set gaussian param too small',
1008                 2,
1009                 [qr{gaussian param.* at least 2}],
1010                 q{\set i random_gaussian(0, 10, 1.0)}
1011         ],
1012         [
1013                 'set exponential param greater 0',
1014                 2,
1015                 [qr{exponential parameter must be greater }],
1016                 q{\set i random_exponential(0, 10, 0.0)}
1017         ],
1018         [
1019                 'set zipfian param to 1',
1020                 2,
1021                 [qr{zipfian parameter must be in range \[1\.001, 1000\]}],
1022                 q{\set i random_zipfian(0, 10, 1)}
1023         ],
1024         [
1025                 'set zipfian param too large',
1026                 2,
1027                 [qr{zipfian parameter must be in range \[1\.001, 1000\]}],
1028                 q{\set i random_zipfian(0, 10, 1000000)}
1029         ],
1030         [
1031                 'set non numeric value', 2,
1032                 [qr{malformed variable "foo" value: "bla"}], q{\set i :foo + 1}
1033         ],
1034         [ 'set no expression', 1, [qr{syntax error}], q{\set i} ],
1035         [ 'set missing argument', 1, [qr{missing argument}i], q{\set} ],
1036         [
1037                 'set not a bool', 2,
1038                 [qr{cannot coerce double to boolean}], q{\set b NOT 0.0}
1039         ],
1040         [
1041                 'set not an int', 2,
1042                 [qr{cannot coerce boolean to int}], q{\set i TRUE + 2}
1043         ],
1044         [
1045                 'set not a double', 2,
1046                 [qr{cannot coerce boolean to double}], q{\set d ln(TRUE)}
1047         ],
1048         [
1049                 'set case error',
1050                 1,
1051                 [qr{syntax error in command "set"}],
1052                 q{\set i CASE TRUE THEN 1 ELSE 0 END}
1053         ],
1054         [
1055                 'set random error', 2,
1056                 [qr{cannot coerce boolean to int}], q{\set b random(FALSE, TRUE)}
1057         ],
1058         [
1059                 'set number of args mismatch', 1,
1060                 [qr{unexpected number of arguments}], q{\set d ln(1.0, 2.0))}
1061         ],
1062         [
1063                 'set at least one arg', 1,
1064                 [qr{at least one argument expected}], q{\set i greatest())}
1065         ],
1066
1067         # SET: ARITHMETIC OVERFLOW DETECTION
1068         [
1069                 'set double to int overflow', 2,
1070                 [qr{double to int overflow for 100}], q{\set i int(1E32)}
1071         ],
1072         [
1073                 'set bigint add overflow', 2,
1074                 [qr{int add out}], q{\set i (1<<62) + (1<<62)}
1075         ],
1076         [
1077                 'set bigint sub overflow',
1078                 2, [qr{int sub out}], q{\set i 0 - (1<<62) - (1<<62) - (1<<62)}
1079         ],
1080         [
1081                 'set bigint mul overflow', 2,
1082                 [qr{int mul out}], q{\set i 2 * (1<<62)}
1083         ],
1084         [
1085                 'set bigint div out of range', 2,
1086                 [qr{bigint div out of range}], q{\set i :minint / -1}
1087         ],
1088
1089         # SETSHELL
1090         [
1091                 'setshell not an int', 2,
1092                 [qr{command must return an integer}], q{\setshell i echo -n one}
1093         ],
1094         [ 'setshell missing arg', 1, [qr{missing argument }], q{\setshell var} ],
1095         [
1096                 'setshell no such command', 2,
1097                 [qr{could not read result }], q{\setshell var no-such-command}
1098         ],
1099
1100         # SLEEP
1101         [
1102                 'sleep undefined variable', 2,
1103                 [qr{sleep: undefined variable}], q{\sleep :nosuchvariable}
1104         ],
1105         [
1106                 'sleep too many args', 1,
1107                 [qr{too many arguments}], q{\sleep too many args}
1108         ],
1109         [
1110                 'sleep missing arg', 1,
1111                 [ qr{missing argument}, qr{\\sleep} ], q{\sleep}
1112         ],
1113         [
1114                 'sleep unknown unit', 1,
1115                 [qr{unrecognized time unit}], q{\sleep 1 week}
1116         ],
1117
1118         # MISC
1119         [
1120                 'misc invalid backslash command', 1,
1121                 [qr{invalid command .* "nosuchcommand"}], q{\nosuchcommand}
1122         ],
1123         [ 'misc empty script', 1, [qr{empty command list for script}], q{} ],
1124         [
1125                 'bad boolean', 2,
1126                 [qr{malformed variable.*trueXXX}], q{\set b :badtrue or true}
1127         ],
1128         [
1129                 'invalid permute size',
1130                 2,
1131                 [qr{permute size parameter must be greater than zero}],
1132                 q{\set i permute(0, 0)}
1133         ],
1134
1135         # GSET
1136         [
1137                 'gset no row', 2,
1138                 [qr{expected one row, got 0\b}], q{SELECT WHERE FALSE \gset}
1139         ],
1140         [ 'gset alone', 1, [qr{gset must follow an SQL command}], q{\gset} ],
1141         [
1142                 'gset no SQL', 1,
1143                 [qr{gset must follow an SQL command}], q{\set i +1
1144 \gset}
1145         ],
1146         [
1147                 'gset too many arguments', 1,
1148                 [qr{too many arguments}], q{SELECT 1 \gset a b}
1149         ],
1150         [
1151                 'gset after gset', 1,
1152                 [qr{gset must follow an SQL command}], q{SELECT 1 AS i \gset
1153 \gset}
1154         ],
1155         [
1156                 'gset non SELECT',
1157                 2,
1158                 [qr{expected one row, got 0}],
1159                 q{DROP TABLE IF EXISTS no_such_table \gset}
1160         ],
1161         [
1162                 'gset bad default name', 2,
1163                 [qr{error storing into variable \?column\?}], q{SELECT 1 \gset}
1164         ],
1165         [
1166                 'gset bad name',
1167                 2,
1168                 [qr{error storing into variable bad name!}],
1169                 q{SELECT 1 AS "bad name!" \gset}
1170         ],);
1171
1172 for my $e (@errors)
1173 {
1174         my ($name, $status, $re, $script, $no_prepare) = @$e;
1175         $status != 0 or die "invalid expected status for test \"$name\"";
1176         my $n = '001_pgbench_error_' . $name;
1177         $n =~ s/ /_/g;
1178         $node->pgbench(
1179                 '-n -t 1 -Dfoo=bla -Dnull=null -Dtrue=true -Done=1 -Dzero=0.0 -Dbadtrue=trueXXX'
1180                   . ' -Dmaxint=9223372036854775807 -Dminint=-9223372036854775808'
1181                   . ($no_prepare ? '' : ' -M prepared'),
1182                 $status,
1183                 [ $status == 1 ? qr{^$} : qr{processed: 0/1} ],
1184                 $re,
1185                 'pgbench script error: ' . $name,
1186                 { $n => $script });
1187 }
1188
1189 # throttling
1190 $node->pgbench(
1191         '-t 100 -S --rate=100000 --latency-limit=1000000 -c 2 -n -r',
1192         0,
1193         [ qr{processed: 200/200}, qr{builtin: select only} ],
1194         [qr{^$}],
1195         'pgbench throttling');
1196
1197 $node->pgbench(
1198
1199         # given the expected rate and the 2 ms tx duration, at most one is executed
1200         '-t 10 --rate=100000 --latency-limit=1 -n -r',
1201         0,
1202         [
1203                 qr{processed: [01]/10},
1204                 qr{type: .*/001_pgbench_sleep},
1205                 qr{above the 1.0 ms latency limit: [01]/}
1206         ],
1207         [qr{^$}],
1208         'pgbench late throttling',
1209         { '001_pgbench_sleep' => q{\sleep 2ms} });
1210
1211 # return a list of files from directory $dir matching regexpr $re
1212 # this works around glob portability and escaping issues
1213 sub list_files
1214 {
1215         my ($dir, $re) = @_;
1216         opendir my $dh, $dir or die "cannot opendir $dir: $!";
1217         my @files = grep /$re/, readdir $dh;
1218         closedir $dh or die "cannot closedir $dir: $!";
1219         return map { $dir . '/' . $_ } @files;
1220 }
1221
1222 # Check log contents and clean them up:
1223 #   $dir: directory holding logs
1224 #   $prefix: file prefix for per-thread logs
1225 #   $nb: number of expected files
1226 #   $min/$max: minimum and maximum number of lines in log files
1227 #   $re: regular expression each log line has to match
1228 sub check_pgbench_logs
1229 {
1230         local $Test::Builder::Level = $Test::Builder::Level + 1;
1231
1232         my ($dir, $prefix, $nb, $min, $max, $re) = @_;
1233
1234         # $prefix is simple enough, thus does not need escaping
1235         my @logs = list_files($dir, qr{^$prefix\..*$});
1236         ok(@logs == $nb, "number of log files");
1237         ok(grep(/\/$prefix\.\d+(\.\d+)?$/, @logs) == $nb, "file name format");
1238
1239         my $log_number = 0;
1240         for my $log (sort @logs)
1241         {
1242                 # Check the contents of each log file.
1243                 my $contents_raw = slurp_file($log);
1244
1245                 my @contents = split(/\n/, $contents_raw);
1246                 my $clen = @contents;
1247                 ok( $min <= $clen && $clen <= $max,
1248                         "transaction count for $log ($clen)");
1249                 my $clen_match = grep(/$re/, @contents);
1250                 ok($clen_match == $clen, "transaction format for $prefix");
1251
1252                 # Show more information if some logs don't match
1253                 # to help with debugging.
1254                 if ($clen_match != $clen)
1255                 {
1256                         foreach my $log (@contents)
1257                         {
1258                                 print "# Log entry not matching: $log\n"
1259                                   unless $log =~ /$re/;
1260                         }
1261                 }
1262         }
1263         return;
1264 }
1265
1266 my $bdir = $node->basedir;
1267
1268 # Run with sampling rate, 2 clients with 50 transactions each.
1269 $node->pgbench(
1270         "-n -S -t 50 -c 2 --log --sampling-rate=0.5", 0,
1271         [ qr{select only}, qr{processed: 100/100} ], [qr{^$}],
1272         'pgbench logs', undef,
1273         "--log-prefix=$bdir/001_pgbench_log_2");
1274 # The IDs of the clients (1st field) in the logs should be either 0 or 1.
1275 check_pgbench_logs($bdir, '001_pgbench_log_2', 1, 8, 92,
1276         qr{^[01] \d{1,2} \d+ \d \d+ \d+$});
1277
1278 # Run with different read-only option pattern, 1 client with 10 transactions.
1279 $node->pgbench(
1280         "-n -b select-only -t 10 -l", 0,
1281         [ qr{select only}, qr{processed: 10/10} ], [qr{^$}],
1282         'pgbench logs contents', undef,
1283         "--log-prefix=$bdir/001_pgbench_log_3");
1284 # The ID of a single client (1st field) should match 0.
1285 check_pgbench_logs($bdir, '001_pgbench_log_3', 1, 10, 10,
1286         qr{^0 \d{1,2} \d+ \d \d+ \d+$});
1287
1288 # abortion of the client if the script contains an incomplete transaction block
1289 $node->pgbench(
1290         '--no-vacuum',
1291         2,
1292         [qr{processed: 1/10}],
1293         [
1294                 qr{client 0 aborted: end of script reached without completing the last transaction}
1295         ],
1296         'incomplete transaction block',
1297         { '001_pgbench_incomplete_transaction_block' => q{BEGIN;SELECT 1;} });
1298
1299 # Test the concurrent update in the table row and deadlocks.
1300
1301 $node->safe_psql('postgres',
1302                 'CREATE UNLOGGED TABLE first_client_table (value integer); '
1303           . 'CREATE UNLOGGED TABLE xy (x integer, y integer); '
1304           . 'INSERT INTO xy VALUES (1, 2);');
1305
1306 # Serialization error and retry
1307
1308 local $ENV{PGOPTIONS} = "-c default_transaction_isolation=repeatable\\ read";
1309
1310 # Check that we have a serialization error and the same random value of the
1311 # delta variable in the next try
1312 my $err_pattern =
1313         "(client (0|1) sending UPDATE xy SET y = y \\+ -?\\d+\\b).*"
1314   . "client \\2 got an error in command 3 \\(SQL\\) of script 0; "
1315   . "ERROR:  could not serialize access due to concurrent update\\b.*"
1316   . "\\1";
1317
1318 $node->pgbench(
1319         "-n -c 2 -t 1 -d --verbose-errors --max-tries 2",
1320         0,
1321         [
1322                 qr{processed: 2/2\b},
1323                 qr{number of transactions retried: 1\b},
1324                 qr{total number of retries: 1\b}
1325         ],
1326         [qr/$err_pattern/s],
1327         'concurrent update with retrying',
1328         {
1329                 '001_pgbench_serialization' => q{
1330 -- What's happening:
1331 -- The first client starts the transaction with the isolation level Repeatable
1332 -- Read:
1333 --
1334 -- BEGIN;
1335 -- UPDATE xy SET y = ... WHERE x = 1;
1336 --
1337 -- The second client starts a similar transaction with the same isolation level:
1338 --
1339 -- BEGIN;
1340 -- UPDATE xy SET y = ... WHERE x = 1;
1341 -- <waiting for the first client>
1342 --
1343 -- The first client commits its transaction, and the second client gets a
1344 -- serialization error.
1345
1346 \set delta random(-5000, 5000)
1347
1348 -- The second client will stop here
1349 SELECT pg_advisory_lock(0);
1350
1351 -- Start transaction with concurrent update
1352 BEGIN;
1353 UPDATE xy SET y = y + :delta WHERE x = 1 AND pg_advisory_lock(1) IS NOT NULL;
1354
1355 -- Wait for the second client
1356 DO $$
1357 DECLARE
1358   exists boolean;
1359   waiters integer;
1360 BEGIN
1361   -- The second client always comes in second, and the number of rows in the
1362   -- table first_client_table reflect this. Here the first client inserts a row,
1363   -- so the second client will see a non-empty table when repeating the
1364   -- transaction after the serialization error.
1365   SELECT EXISTS (SELECT * FROM first_client_table) INTO STRICT exists;
1366   IF NOT exists THEN
1367         -- Let the second client begin
1368         PERFORM pg_advisory_unlock(0);
1369         -- And wait until the second client tries to get the same lock
1370         LOOP
1371           SELECT COUNT(*) INTO STRICT waiters FROM pg_locks WHERE
1372           locktype = 'advisory' AND objsubid = 1 AND
1373           ((classid::bigint << 32) | objid::bigint = 1::bigint) AND NOT granted;
1374           IF waiters = 1 THEN
1375                 INSERT INTO first_client_table VALUES (1);
1376
1377                 -- Exit loop
1378                 EXIT;
1379           END IF;
1380         END LOOP;
1381   END IF;
1382 END$$;
1383
1384 COMMIT;
1385 SELECT pg_advisory_unlock_all();
1386 }
1387         });
1388
1389 # Clean up
1390
1391 $node->safe_psql('postgres', 'DELETE FROM first_client_table;');
1392
1393 local $ENV{PGOPTIONS} = "-c default_transaction_isolation=read\\ committed";
1394
1395 # Deadlock error and retry
1396
1397 # Check that we have a deadlock error
1398 $err_pattern =
1399         "client (0|1) got an error in command (3|5) \\(SQL\\) of script 0; "
1400   . "ERROR:  deadlock detected\\b";
1401
1402 $node->pgbench(
1403         "-n -c 2 -t 1 --max-tries 2 --verbose-errors",
1404         0,
1405         [
1406                 qr{processed: 2/2\b},
1407                 qr{number of transactions retried: 1\b},
1408                 qr{total number of retries: 1\b}
1409         ],
1410         [qr{$err_pattern}],
1411         'deadlock with retrying',
1412         {
1413                 '001_pgbench_deadlock' => q{
1414 -- What's happening:
1415 -- The first client gets the lock 2.
1416 -- The second client gets the lock 3 and tries to get the lock 2.
1417 -- The first client tries to get the lock 3 and one of them gets a deadlock
1418 -- error.
1419 --
1420 -- A client that does not get a deadlock error must hold a lock at the
1421 -- transaction start. Thus in the end it releases all of its locks before the
1422 -- client with the deadlock error starts a retry (we do not want any errors
1423 -- again).
1424
1425 -- Since the client with the deadlock error has not released the blocking locks,
1426 -- let's do this here.
1427 SELECT pg_advisory_unlock_all();
1428
1429 -- The second client and the client with the deadlock error stop here
1430 SELECT pg_advisory_lock(0);
1431 SELECT pg_advisory_lock(1);
1432
1433 -- The second client and the client with the deadlock error always come after
1434 -- the first and the number of rows in the table first_client_table reflects
1435 -- this. Here the first client inserts a row, so in the future the table is
1436 -- always non-empty.
1437 DO $$
1438 DECLARE
1439   exists boolean;
1440 BEGIN
1441   SELECT EXISTS (SELECT * FROM first_client_table) INTO STRICT exists;
1442   IF exists THEN
1443         -- We are the second client or the client with the deadlock error
1444
1445         -- The first client will take care by itself of this lock (see below)
1446         PERFORM pg_advisory_unlock(0);
1447
1448         PERFORM pg_advisory_lock(3);
1449
1450         -- The second client can get a deadlock here
1451         PERFORM pg_advisory_lock(2);
1452   ELSE
1453         -- We are the first client
1454
1455         -- This code should not be used in a new transaction after an error
1456         INSERT INTO first_client_table VALUES (1);
1457
1458         PERFORM pg_advisory_lock(2);
1459   END IF;
1460 END$$;
1461
1462 DO $$
1463 DECLARE
1464   num_rows integer;
1465   waiters integer;
1466 BEGIN
1467   -- Check if we are the first client
1468   SELECT COUNT(*) FROM first_client_table INTO STRICT num_rows;
1469   IF num_rows = 1 THEN
1470         -- This code should not be used in a new transaction after an error
1471         INSERT INTO first_client_table VALUES (2);
1472
1473         -- Let the second client begin
1474         PERFORM pg_advisory_unlock(0);
1475         PERFORM pg_advisory_unlock(1);
1476
1477         -- Make sure the second client is ready for deadlock
1478         LOOP
1479           SELECT COUNT(*) INTO STRICT waiters FROM pg_locks WHERE
1480           locktype = 'advisory' AND
1481           objsubid = 1 AND
1482           ((classid::bigint << 32) | objid::bigint = 2::bigint) AND
1483           NOT granted;
1484
1485           IF waiters = 1 THEN
1486             -- Exit loop
1487                 EXIT;
1488           END IF;
1489         END LOOP;
1490
1491         PERFORM pg_advisory_lock(0);
1492     -- And the second client took care by itself of the lock 1
1493   END IF;
1494 END$$;
1495
1496 -- The first client can get a deadlock here
1497 SELECT pg_advisory_lock(3);
1498
1499 SELECT pg_advisory_unlock_all();
1500 }
1501         });
1502
1503 # Clean up
1504 $node->safe_psql('postgres', 'DROP TABLE first_client_table, xy;');
1505
1506 # Test --exit-on-abort
1507 $node->safe_psql('postgres',
1508         'CREATE TABLE counter(i int); '.
1509         'INSERT INTO counter VALUES (0);'
1510 );
1511
1512 $node->pgbench(
1513         '-t 10 -c 2 -j 2 --exit-on-abort',
1514         2,
1515         [],
1516         [
1517                 qr{division by zero},
1518                 qr{Run was aborted due to an error in thread}
1519         ],
1520         'test --exit-on-abort',
1521         {
1522                 '001_exit_on_abort' => q{
1523 update counter set i = i+1 returning i \gset
1524 \if :i = 5
1525 \set y 1/0
1526 \endif
1527 }
1528         });
1529
1530 # Clean up
1531 $node->safe_psql('postgres', 'DROP TABLE counter;');
1532
1533 # done
1534 $node->safe_psql('postgres', 'DROP TABLESPACE regress_pgbench_tap_1_ts');
1535 $node->stop;
1536 done_testing();