Re: parallel append vs. simple UNION ALL - Mailing list pgsql-hackers
| From | Rajkumar Raghuwanshi |
|---|---|
| Subject | Re: parallel append vs. simple UNION ALL |
| Date | |
| Msg-id | CAKcux6k_4dHMduH6AVCkB_ZJ-wPGUfietZTOP4zb+ei1R9MFsg@mail.gmail.com Whole thread Raw |
| In response to | Re: parallel append vs. simple UNION ALL (Robert Haas <robertmhaas@gmail.com>) |
| List | pgsql-hackers |
On Sat, Feb 24, 2018 at 2:55 AM, Robert Haas <robertmhaas@gmail.com> wrote:
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
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
pgsql-hackers by date: