|
4 | 4 | use Config; |
5 | 5 | use PostgresNode; |
6 | 6 | use TestLib; |
7 | | -use Test::Moretests=>22; |
| 7 | +use Test::Moretests=>27; |
8 | 8 |
|
9 | 9 | my$node = get_new_node('aqotest'); |
10 | 10 | $node->init; |
|
298 | 298 | is($new_stat_count ==$stat_count -$pgb_stat_count, 1, |
299 | 299 | 'Total number of samples in aqo_query_stat'); |
300 | 300 |
|
| 301 | +# ############################################################################## |
| 302 | +# |
| 303 | +# AQO works after moving to another schema |
| 304 | +# |
| 305 | +# ############################################################################## |
| 306 | + |
| 307 | +# Move the extension to not-in-search-path schema |
| 308 | +# use LEARN mode to guarantee that AQO will be triggered on each query. |
| 309 | +$node->safe_psql('postgres',"CREATE SCHEMA test; ALTER EXTENSION aqo SET SCHEMA test"); |
| 310 | +$node->safe_psql('postgres',"SELECT * FROM test.aqo_reset()");# Clear data |
| 311 | + |
| 312 | +$res =$node->safe_psql('postgres',"SELECT count(*) FROM test.aqo_queries"); |
| 313 | +is($res, 1,'The extension data was reset'); |
| 314 | + |
| 315 | +$node->command_ok(['pgbench','-i','-s','1' ],'init pgbench tables'); |
| 316 | +$node->safe_psql('postgres'," |
| 317 | +ALTER SYSTEM SET aqo.mode = 'learn'; |
| 318 | +ALTER SYSTEM SET log_statement = 'ddl'; |
| 319 | +SELECT pg_reload_conf(); |
| 320 | +"); |
| 321 | +$node->restart(); |
| 322 | + |
| 323 | +$node->command_ok(['pgbench','-t',"25",'-c',"$CLIENTS",'-j',"$THREADS" ], |
| 324 | +'pgbench should work with moved AQO.'); |
| 325 | + |
| 326 | +# DEBUG |
| 327 | +$res =$node->safe_psql('postgres'," |
| 328 | +SELECT executions_with_aqo, query_text |
| 329 | +FROM test.aqo_query_stat a, test.aqo_query_texts b |
| 330 | +WHERE a.queryid = b.queryid |
| 331 | +"); |
| 332 | +note("executions:\n$res\n"); |
| 333 | + |
| 334 | +$res =$node->safe_psql('postgres', |
| 335 | +"SELECT sum(executions_with_aqo) FROM test.aqo_query_stat"); |
| 336 | + |
| 337 | +# 25 trans * 10 clients * 4 query classes = 1000 + unique SELECT to pgbench_branches |
| 338 | +is($res, 1001,'Each query should be logged in LEARN mode'); |
| 339 | +$res =$node->safe_psql('postgres', |
| 340 | +"SELECT sum(executions_without_aqo) FROM test.aqo_query_stat"); |
| 341 | +is($res, 0,'AQO has learned on the queries - 2'); |
| 342 | + |
| 343 | +# Try to call UI functions. Break the test on an error |
| 344 | +$res =$node->safe_psql('postgres'," |
| 345 | +SELECT * FROM test.aqo_cardinality_error(true); |
| 346 | +SELECT * FROM test.aqo_execution_time(true); |
| 347 | +SELECT * FROM |
| 348 | +(SELECT queryid FROM test.aqo_queries WHERE queryid<>0 LIMIT 1) q, |
| 349 | +LATERAL test.aqo_drop_class(queryid); |
| 350 | +SELECT * FROM test.aqo_cleanup(); |
| 351 | +"); |
| 352 | +note("OUTPUT:\n$res\n"); |
| 353 | + |
301 | 354 | $node->safe_psql('postgres',"DROP EXTENSION aqo"); |
302 | 355 |
|
303 | 356 | # ############################################################################## |
|
333 | 386 | $node->safe_psql('postgres'," |
334 | 387 | CREATE EXTENSION aqo; |
335 | 388 | ALTER SYSTEM SET aqo.mode = 'intelligent'; |
336 | | -ALTER SYSTEM SET log_statement = 'ddl'; |
| 389 | +ALTER SYSTEM SET log_statement = 'none'; |
337 | 390 | SELECT pg_reload_conf(); |
338 | 391 | "); |
339 | 392 | $node->restart(); |
|
