Execution crashes for PostgreSQL with SQL syntax error
Opened this issue · 4 comments
Syntactically wrong SQL is fired against PostgreSQL after few hours of execution.
here is the extract from the log
...
[ 20060s ] thds: 4 tps: 11.00 qps: 360.60 (r/w/o: 165.80/163.90/30.90) lat (ms,99%): 90.78 err/s 0.00 reconn/s: 0.00
[ 20070s ] thds: 4 tps: 8.50 qps: 282.90 (r/w/o: 128.80/128.10/26.00) lat (ms,99%): 48.34 err/s 0.00 reconn/s: 0.00
[ 20080s ] thds: 4 tps: 10.80 qps: 316.10 (r/w/o: 146.20/135.70/34.20) lat (ms,99%): 42.61 err/s 0.00 reconn/s: 0.00
[ 20090s ] thds: 4 tps: 11.60 qps: 342.50 (r/w/o: 157.20/149.50/35.80) lat (ms,99%): 87.56 err/s 0.00 reconn/s: 0.00
[ 20100s ] thds: 4 tps: 9.10 qps: 220.40 (r/w/o: 100.80/96.00/23.60) lat (ms,99%): 34.95 err/s 0.00 reconn/s: 0.00
[ 20110s ] thds: 4 tps: 9.30 qps: 260.40 (r/w/o: 118.90/117.50/24.00) lat (ms,99%): 39.65 err/s 0.10 reconn/s: 0.00
FATAL: PQexec() failed: 7 syntax error at or near "LIMIT"
FATAL: failed query was: DELETE FROM history1 where h_w_id=2 AND h_d_id=2 LIMIT 10
FATAL: `thread_run' function failed: /usr/share/sysbench/tpcc_run.lua:785: SQL error, errno = 0, state = '42601': syntax error at or near "LIMIT"
...
A DELETE statement should no have LIMIT clause.
@jobinau that DELETE is used only if you use enable_purge , which should not be used in the normal benchmark.
Thank you @vadimtk for clarifying.
We have been using the sysbench for creating a sustained load.
@jobinau Anyway, I am surprised you see it only after a few hours and not immediately.
Can you suggest a way how to rewrite the DELETE for PostgreSQL ?
@vadimtk , Currently what we have is a syntax error because of "LIMIT"
Is it meaningful to delete all the rows which are satisfying the WHERE condition?
Exact same behavior of limited row delete can be achieved by DELETE with sub select :
DELETE FROM history1 WHERE ctid IN (SELECT ctid FROM history1 where h_w_id=2 AND h_d_id=2 LIMIT 10);