def test_concurrent_access(config):
"""test when two backends compete with each other to extract state from third running backend"""
acon1, acon2, acon3 = common.n_async_connect(config, 3)
acurs1, acurs2, acurs3 = acon1.cursor(), acon2.cursor(), acon3.cursor()
query = 'select count(*) from foo join bar on foo.c1=bar.c1'
common.set_guc(acon3, 'max_parallel_workers_per_gather', 0)
acurs3.execute(query)
time.sleep(0.1)
acurs1.callproc('pg_query_state', (acon3.get_backend_pid(), ))
acurs2.callproc('pg_query_state', (acon3.get_backend_pid(), ))
common.wait(acon1)
common.wait(acon2)
common.wait(acon3)
qs1, qs2 = acurs1.fetchall(), acurs2.fetchall()
assert len(qs1) == len(qs2) == 1 \
and qs1[0][0] == qs2[0][0] == acon3.get_backend_pid() \
and qs1[0][1] == qs2[0][1] == 0 \
and qs1[0][2] == qs2[0][2] == query \
and len(qs1[0][3]) > 0 and len(qs2[0][3]) > 0 \
and qs1[0][4] == qs2[0][4] == None
common.n_close((acon1, acon2, acon3))
def test_buffers(config):
"""test buffer statistics"""
acon1, acon2 = common.n_async_connect(config, 2)
query = 'select count(*) from foo join bar on foo.c1=bar.c1 and unlock_if_eq_1(foo.c1)=bar.c1'
expected = r"""Aggregate \(Current loop: actual rows=0, loop number=1\)
-> Hash Join \(Current loop: actual rows=\d+, loop number=1\)
Hash Cond: \(foo.c1 = bar.c1\)
Join Filter: \(unlock_if_eq_1\(foo.c1\) = bar.c1\)
Buffers: shared hit=\d+, temp read=\d+ written=\d+
-> Seq Scan on foo \(Current loop: actual rows=1000000, loop number=1\)
Buffers: [^\n]*
-> Hash \(Current loop: actual rows=500000, loop number=1\)
Buckets: \d+ Batches: \d+ Memory Usage: \d+kB
Buffers: shared hit=\d+, temp written=\d+
-> Seq Scan on bar \(Current loop: actual rows=\d+, loop number=1\)
Buffers: .*"""
common.set_guc(acon1, 'pg_query_state.enable_buffers', 'on')
qs, notices = common.onetime_query_state_locks(config, acon1, acon2, query,
{'buffers': True})
assert len(qs) == 2
assert re.match(expected, qs[0][3])
assert len(notices) == 0
common.n_close((acon1, acon2))
def test_timing(config):
"""test timing statistics"""
acon1, acon2 = common.n_async_connect(config, 2)
query = 'select count(*) from foo join bar on foo.c1=bar.c1 and unlock_if_eq_1(foo.c1)=bar.c1'
expected = r"""Aggregate \(Current loop: running time=\d+.\d+ actual rows=0, loop number=1\)
-> Hash Join \(Current loop: actual time=\d+.\d+..\d+.\d+ rows=\d+, loop number=1\)
Hash Cond: \(foo.c1 = bar.c1\)
Join Filter: \(unlock_if_eq_1\(foo.c1\) = bar.c1\)
-> Seq Scan on foo \(Current loop: actual time=\d+.\d+..\d+.\d+ rows=1000000, loop number=1\)
-> Hash \(Current loop: actual time=\d+.\d+..\d+.\d+ rows=500000, loop number=1\)
Buckets: \d+ Batches: \d+ Memory Usage: \d+kB
-> Seq Scan on bar \(Current loop: actual time=\d+.\d+..\d+.\d+ rows=\d+, loop number=1\)"""
common.set_guc(acon1, 'pg_query_state.enable_timing', 'on')
qs, notices = common.onetime_query_state_locks(config, acon1, acon2, query,
{'timing': True})
assert len(qs) == 2
assert re.match(expected, qs[0][3])
assert len(notices) == 0
common.n_close((acon1, acon2))
def test_buffers(config):
"""test buffer statistics"""
acon, = common.n_async_connect(config)
query = 'select count(*) from foo join bar on foo.c1=bar.c1'
expected = r"""Aggregate \(Current loop: actual rows=0, loop number=1\)
-> Hash Join \(Current loop: actual rows=0, loop number=1\)
Hash Cond: \(foo.c1 = bar.c1\)
-> Seq Scan on foo \(Current loop: actual rows=1, loop number=1\)
Buffers: [^\n]*
-> Hash \(Current loop: actual rows=0, loop number=1\)
Buckets: \d+ Batches: \d+ Memory Usage: \d+kB
-> Seq Scan on bar \(Current loop: actual rows=\d+, loop number=1\)
Buffers: .*"""
common.set_guc(acon, 'pg_query_state.enable_buffers', 'on')
qs, notices = common.onetime_query_state(config, acon, query, {'buffers': True})
assert len(qs) == 1 and re.match(expected, qs[0][3])
assert len(notices) == 0
common.n_close((acon,))
def run_tpcds(config):
"""TPC-DS stress test"""
TPC_DS_EXCLUDE_LIST = [] # actual numbers of TPC-DS tests to exclude
TPC_DS_STATEMENT_TIMEOUT = 20000 # statement_timeout in ms
print('Preparing TPC-DS queries...')
queries = []
for query_file in sorted(
os.listdir(
'tmp_stress/tpcds-result-reproduction/query_qualification/')):
with open(
'tmp_stress/tpcds-result-reproduction/query_qualification/%s' %
query_file, 'r') as f:
queries.append(f.read())
acon, = common.n_async_connect(config)
pid = acon.get_backend_pid()
print('Starting TPC-DS queries...')
timeout_list = []
bar = progressbar.ProgressBar(max_value=len(queries))
for i, query in enumerate(queries):
bar.update(i + 1)
if i + 1 in TPC_DS_EXCLUDE_LIST:
continue
try:
# Set query timeout to TPC_DS_STATEMENT_TIMEOUT / 1000 seconds
common.set_guc(acon, 'statement_timeout', TPC_DS_STATEMENT_TIMEOUT)
# run query
acurs = acon.cursor()
acurs.execute(query)
# periodically run pg_query_state on running backend trying to get
# crash of PostgreSQL
MAX_FIRST_GETTING_QS_RETRIES = 10
PG_QS_DELAY, BEFORE_GETTING_QS_DELAY = 0.1, 0.1
BEFORE_GETTING_QS, GETTING_QS = range(2)
state, n_first_getting_qs_retries = BEFORE_GETTING_QS, 0
while True:
result, notices = common.pg_query_state(config, pid)
# run state machine to determine the first getting of query state
# and query finishing
if state == BEFORE_GETTING_QS:
if len(result
) > 0 or common.BACKEND_IS_ACTIVE_INFO in notices:
state = GETTING_QS
continue
n_first_getting_qs_retries += 1
if n_first_getting_qs_retries >= MAX_FIRST_GETTING_QS_RETRIES:
# pg_query_state callings don't return any result, more likely run
# query has completed
break
time.sleep(BEFORE_GETTING_QS_DELAY)
elif state == GETTING_QS:
if common.BACKEND_IS_IDLE_INFO in notices:
break
time.sleep(PG_QS_DELAY)
# wait for real query completion
common.wait(acon)
except psycopg2.extensions.QueryCanceledError:
timeout_list.append(i + 1)
common.n_close((acon, ))
if len(timeout_list) > 0:
print(
'\nThere were pg_query_state timeouts (%s s) on queries:' %
TPC_DS_STATEMENT_TIMEOUT, timeout_list)