Changeset: 400bfa57b05a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=400bfa57b05a
Added Files:
sql/test/mergetables/Tests/concurrent_merge_tables.SQL.py
sql/test/mergetables/Tests/concurrent_merge_tables.stable.err
sql/test/mergetables/Tests/concurrent_merge_tables.stable.out
Modified Files:
sql/test/mergetables/Tests/All
Branch: Jun2020-mmt
Log Message:
Added a test to concurrently create merge tables that share the same base table
diffs (134 lines):
diff --git a/sql/test/mergetables/Tests/All b/sql/test/mergetables/Tests/All
--- a/sql/test/mergetables/Tests/All
+++ b/sql/test/mergetables/Tests/All
@@ -34,3 +34,4 @@ sqlsmith-exists2
mergedb_drop
mergetable_rel_push_aggr_down
multiple-merge-tables
+concurrent_merge_tables
diff --git a/sql/test/mergetables/Tests/concurrent_merge_tables.SQL.py
b/sql/test/mergetables/Tests/concurrent_merge_tables.SQL.py
new file mode 100644
--- /dev/null
+++ b/sql/test/mergetables/Tests/concurrent_merge_tables.SQL.py
@@ -0,0 +1,87 @@
+###
+# Check that multiple processes can create and query a merge table with the
+# same base table without crashing or hanging mserver5.
+#
+# If everything went well, this test produces no output
+###
+
+import pymonetdb
+import os, sys
+import multiprocessing as mp
+import traceback
+import random
+import time
+
+# The job for worker process
+def exec_query(pid):
+ dbh = None
+ try:
+ # sleep for some random time less than 1 sec. to avoid immediate
+ # transaction conflicts on the `create merge table`
+ time.sleep(random.random())
+
+ dbh = pymonetdb.connect(database = os.environ['TSTDB'], port =
int(os.environ['MAPIPORT']), hostname = os.environ['MAPIHOST'], autocommit=True)
+ #dbh = pymonetdb.connect(database = 'demo', port=60000,
autocommit=True)
+ cur = dbh.cursor()
+ cur.execute('create merge table concurrent_mt.cncrnt_mrg_tbl{} (like
concurrent_mt.base_tbl);'.format(pid))
+ cur.execute('alter table concurrent_mt.cncrnt_mrg_tbl{} add table
base_tbl;'.format(pid))
+
+ rowcnt = cur.execute('select * from
concurrent_mt.cncrnt_mrg_tbl{};'.format(pid))
+ if rowcnt != 2:
+ raise ValueError('select * from concurrent_mt.cncrnt_mrg_tbl{}: 2
rows expected, got {}: {}'\
+ .format(pid, rowcnt, str(cur.fetchall())))
+
+ cur.execute('drop table concurrent_mt.cncrnt_mrg_tbl{};'.format(pid))
+ except pymonetdb.exceptions.Error as e:
+ if "transaction is aborted because of concurrency conflicts" not in
str(e):
+ print(e)
+ finally:
+ if dbh is not None:
+ dbh.close()
+
+if __name__ == '__main__':
+ mstdbh = None
+ try:
+ mstdbh = pymonetdb.connect(database = os.environ['TSTDB'], port =
int(os.environ['MAPIPORT']), hostname = os.environ['MAPIHOST'], autocommit=True)
+ #mstdbh = pymonetdb.connect(database = 'demo', port=60000,
autocommit=True)
+ mstcur = mstdbh.cursor()
+
+ try:
+ # This clean up is to make this test script usable standalone
+ mstcur.execute('drop schema concurrent_mt cascade;')
+ rowcnt = mstcur.execute('select name from sys.tables where name
like \'%_tbl%\';')
+ if rowcnt != 0:
+ raise ValueError('Leftover tables before:
{}'.format(str(mstcur.fetchall())))
+ except Exception:
+ pass
+ mstcur.execute('create schema concurrent_mt;')
+ # The base table to be shared by the merge tables.
+ mstcur.execute('create table concurrent_mt.base_tbl (i int);')
+ mstcur.execute('insert into concurrent_mt.base_tbl values (42), (24);')
+
+ ### Setup a list of processes that we want to run
+ # More processes can cause mserver5 to crash
+ jobs = [mp.Process(target=exec_query, args=(x,)) for x in range(0, 50)]
+ # Run processes
+ [p.start() for p in jobs]
+ # Wait for them to finish
+ [p.join() for p in jobs]
+
+ # Less processes can cause mserver5 to hang
+ jobs = [mp.Process(target=exec_query, args=(x,)) for x in range(100,
105)]
+ # Run processes
+ [p.start() for p in jobs]
+ # Wait for them to finish
+ [p.join() for p in jobs]
+
+ # Clean up
+ mstcur.execute('drop schema concurrent_mt cascade;')
+ rowcnt = mstcur.execute('select name from sys.tables where name like
\'%_tbl%\';')
+ if rowcnt != 0:
+ raise ValueError('Leftover tables after:
{}'.format(str(mstcur.fetchall())))
+ except pymonetdb.exceptions.Error as e:
+ print(e)
+ finally:
+ if mstdbh is not None:
+ mstdbh.close()
+
diff --git a/sql/test/mergetables/Tests/concurrent_merge_tables.stable.err
b/sql/test/mergetables/Tests/concurrent_merge_tables.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/mergetables/Tests/concurrent_merge_tables.stable.err
@@ -0,0 +1,12 @@
+stderr of test 'concurrent_merge_tables` in directory 'sql/test/mergetables`
itself:
+
+
+# 14:09:11 >
+# 14:09:11 > "/usr/bin/python3" "concurrent_merge_tables.SQL.py"
"concurrent_merge_tables"
+# 14:09:11 >
+
+
+# 14:09:13 >
+# 14:09:13 > "Done."
+# 14:09:13 >
+
diff --git a/sql/test/mergetables/Tests/concurrent_merge_tables.stable.out
b/sql/test/mergetables/Tests/concurrent_merge_tables.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/mergetables/Tests/concurrent_merge_tables.stable.out
@@ -0,0 +1,12 @@
+stdout of test 'concurrent_merge_tables` in directory 'sql/test/mergetables`
itself:
+
+
+# 14:09:11 >
+# 14:09:11 > "/usr/bin/python3" "concurrent_merge_tables.SQL.py"
"concurrent_merge_tables"
+# 14:09:11 >
+
+
+# 14:09:13 >
+# 14:09:13 > "Done."
+# 14:09:13 >
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
