Re: parallel append vs. simple UNION ALL

On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> 0004 causes generate_union_path() to consider both the traditional
> method and also Gather -> Parallel Append -> [partial path for each
> subquery]. This is still a bit rough around the edges and there's a
> lot more that could be done here, but I'm posting what I have for now
> in the (perhaps vain) hope of getting some feedback. With this, you
> can use Parallel Append for the UNION ALL step of a query like SELECT
> .. UNION ALL .. SELECT ... EXCEPT SELECT ...
>

Hi,

With all 0001,0002,0003 and 0004 patch applied on head, I am getting a
strange crash, while trying to change table name
in a query by using "TAB" key.

Same test case working fine with only 0001 applied and also on PG-head.

below are steps to reproduce.

--run below sqls

SET parallel_setup_cost=0;
SET parallel_tuple_cost=0;
SET min_parallel_table_scan_size=0;
CREATE TABLE tbl_union_t1 (c1 INTEGER PRIMARY KEY,c2 INTEGER,c3 CHAR(10));
INSERT INTO tbl_union_t1 SELECT i, i % 125, to_char(i % 4, 'FM0000') FROM
generate_series(0, 499,2) i;
CREATE TABLE tbl_union_t2 (c1 INTEGER PRIMARY KEY,c2 INTEGER,c3 CHAR(10));
INSERT INTO tbl_union_t2 SELECT i, i % 125, to_char(i % 4, 'FM0000') FROM
generate_series(0, 499,3) i;
ANALYSE tbl_union_t1;
ANALYSE tbl_union_t2;

EXPLAIN SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM tbl_union_t1 EXCEPT
SELECT c1,c2 FROM tbl_union_t2 WHERE c1 % 25 =0 )UA;

--now try modifying tbl_union_t1 in the above query
--remove "_union_t1" and press TAB key, It crashed for me.

EXPLAIN SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM tbl*<PRESS TAB KEY
HERE>*EXCEPT SELECT c1,c2 FROM tbl_union_t2 WHERE c1 % 25 =0 )UA;

postgres=# EXPLAIN SELECT AVG(c1),SUM(c2) FROM (SELECT c1,c2 FROM
tblWARNING: terminating connection because of crash of another server
process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.

--logfile says something like this
2018-03-01 18:37:36.456 IST [50071] LOG: database system is ready to
accept connections
2018-03-01 18:38:38.668 IST [50071] LOG: background worker "parallel
worker" (PID 51703) was terminated by signal 11: Segmentation fault
2018-03-01 18:38:38.668 IST [50071] DETAIL: Failed process was running:
SELECT pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c WHERE
c.relkind IN ('r', 'S', 'v', 'm', 'f', 'p') AND
substring(pg_catalog.quote_ident(c.relname),1,3)='tbl' AND
pg_catalog.pg_table_is_visible(c.oid) AND c.relnamespace <> (SELECT oid
FROM pg_catalog.pg_namespace WHERE nspname = 'pg_catalog')
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' FROM
pg_catalog.pg_namespace n WHERE substring(pg_catalog.quote_ident(n.nspname)
|| '.',1,3)='tbl' AND (SELECT pg_catalog.count(*) FROM
pg_catalog.pg_namespace WHERE substring(pg_catalog.quote_ident(nspname) ||
'.',1,3) =
substring('tbl',1,pg_catalog.length(pg_catalog.quote_ident(nspname))+1)) > 1
UNION
SELECT pg_catalog.quote_ident(n.nspname) || '.' ||
pg_catalog.quote_ident(c.relname) FROM pg_catalog.pg_class c,
pg_catalog.pg_namespace n WHERE c.relnamespace = n.oid AND c.relkind IN
('r', 'S', 'v', 'm', 'f', 'p') AND
substring(pg_catalog.quote_ident(n.nspname) || '.' ||
pg_catalog.quote_ident(c.relname),1,3)='tbl' AND
substring(pg_catalog.quote_id
2018-03-01 18:38:38.668 IST [50071] LOG: terminating any other active
server processes
2018-03-01 18:38:38.668 IST [50082] WARNING: terminating connection
because of crash of another server process
2018-03-01 18:38:38.668 IST [50082] DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2018-03-01 18:38:38.668 IST [50082] HINT: In a moment you should be able
to reconnect to the database and repeat your command.
2018-03-01 18:38:38.670 IST [50076] WARNING: terminating connection
because of crash of another server process
2018-03-01 18:38:38.670 IST [50076] DETAIL: The postmaster has commanded
this server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2018-03-01 18:38:38.670 IST [50076] HINT: In a moment you should be able
to reconnect to the database and repeat your command.
2018-03-01 18:38:38.675 IST [50071] LOG: all server processes terminated;
reinitializing
2018-03-01 18:38:38.702 IST [51712] LOG: database system was interrupted;
last known up at 2018-03-01 18:37:36 IST
2018-03-01 18:38:38.723 IST [51712] LOG: database system was not properly
shut down; automatic recovery in progress
2018-03-01 18:38:38.724 IST [51712] LOG: redo starts at 0/1639510
2018-03-01 18:38:38.726 IST [51712] LOG: invalid record length at
0/1669488: wanted 24, got 0
2018-03-01 18:38:38.726 IST [51712] LOG: redo done at 0/1669420
2018-03-01 18:38:38.726 IST [51712] LOG: last completed transaction was at
log time 2018-03-01 18:38:36.53573+05:30
2018-03-01 18:38:38.744 IST [50071] LOG: database system is ready to
accept connections

Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation