Re: BUG #14344: string_agg(DISTINCT ..) crash - Mailing list pgsql-bugs

On 29 September 2016 at 18:41, David Rowley
<david.rowley@2ndquadrant.com> wrote:
> My guess would be a sorting bug...
>
> Let me check....
>
> Looks like:
>
> commit 0011c0091e886b874e485a46ff2c94222ffbf550

Just for the record I can only get this to crash on Windows too, but I
don't have much confidence that it's a windows only issue.

I compiled with TRACE_SORT on Windows and Linux to find out what the
sort does differently on each platform.

On windows:

LOG:  begin tuple sort: nkeys = 2, workMem = 4096, randomAccess = f
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  switching to external sort with 15 tapes: CPU 0.00s/0.03u sec
elapsed 0.02 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  performsort starting: CPU 0.00s/0.37u sec elapsed 0.37 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  finished writing run 1 to tape 0: CPU 0.00s/0.43u sec elapsed 0.43 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  finished writing final run 2 to tape 1: CPU 0.00s/0.48u sec
elapsed 0.48 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  grew memtuples 1.29x from 58253 (1366 KB) to 74896 (1756 KB) for
final merge
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  tape 0 initially used 878 KB of 1147 KB batch (0.765) and 37446
out of 37447 slots (1.000)
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  tape 1 initially used 309 KB of 1147 KB batch (0.269) and 13182
out of 37447 slots (0.352)
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  performsort done (except 2-way final merge): CPU 0.01s/0.48u sec
elapsed 0.49 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) from
string_Agg_crash_test;
LOG:  server process (PID 10848) was terminated by exception 0xC0000005

On Linux:

LOG:  begin tuple sort: nkeys = 2, workMem = 4096, randomAccess = f
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  switching to external sort with 15 tapes: CPU 0.00s/0.03u sec
elapsed 0.03 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  replacement selection will sort 58253 first run tuples
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  performsort starting: CPU 0.00s/0.16u sec elapsed 0.16 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  finished incrementally writing first run 1 to tape 0: CPU
0.00s/0.20u sec elapsed 0.20 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  starting quicksort of run 2: CPU 0.00s/0.20u sec elapsed 0.20 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  finished quicksort of run 2: CPU 0.00s/0.20u sec elapsed 0.20 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  finished writing run 2 to tape 1: CPU 0.00s/0.21u sec elapsed 0.21 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  grew memtuples 1.29x from 58253 (1366 KB) to 74896 (1756 KB) for
final merge
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  tape 0 initially used 878 KB of 1147 KB batch (0.765) and 37446
out of 37447 slots (1.000)
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  tape 1 initially used 309 KB of 1147 KB batch (0.269) and 13182
out of 37447 slots (0.352)
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  performsort done (except 2-way final merge): CPU 0.00s/0.21u sec
elapsed 0.21 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;
LOG:  external sort ended, 177 disk blocks used: CPU 0.00s/0.24u sec
elapsed 0.24 sec
STATEMENT:  SELECT string_agg(DISTINCT state, ',' ORDER BY state) FROM
string_agg_crash_test;

Both versions are 6ad8ac6

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services