- Notifications
You must be signed in to change notification settings - Fork26
Closed
Assignees
Labels
Description
I apologize in advance for this messy bug report.
Executing the following SQL query, an instance of a TPC-DS template, occasionally causes a segmentation fault in PG10.3 whenpg_query_state is called. Here is the query:
select*from (selectcount(*) h8_30_to_9from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=8andtime_dim.t_minute>=30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s1, (selectcount(*) h9_to_9_30from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=9andtime_dim.t_minute<30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s2, (selectcount(*) h9_30_to_10from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=9andtime_dim.t_minute>=30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s3, (selectcount(*) h10_to_10_30from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=10andtime_dim.t_minute<30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s4, (selectcount(*) h10_30_to_11from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=10andtime_dim.t_minute>=30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s5, (selectcount(*) h11_to_11_30from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=11andtime_dim.t_minute<30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s6, (selectcount(*) h11_30_to_12from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=11andtime_dim.t_minute>=30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s7, (selectcount(*) h12_to_12_30from store_sales, household_demographics , time_dim, storewhere ss_sold_time_sk=time_dim.t_time_skand ss_hdemo_sk=household_demographics.hd_demo_skand ss_store_sk= s_store_skandtime_dim.t_hour=12andtime_dim.t_minute<30and ((household_demographics.hd_dep_count=0andhousehold_demographics.hd_vehicle_count<=0+2)or (household_demographics.hd_dep_count=2andhousehold_demographics.hd_vehicle_count<=2+2)or (household_demographics.hd_dep_count=4andhousehold_demographics.hd_vehicle_count<=4+2))andstore.s_store_name='ese') s8;
I apologize for the length... I am not sure what elements of the query causes the failure.
Here are the steps to reproduce:
- Download PostgresQL 10.3
- Download the PG10 branch
- Patch and build:
cd postgresql-10.3patch -p1< /home/postgres/pg_query_state/custom_signals.patchpatch -p1< /home/postgres/pg_query_state/runtime_explain.patch./configure --prefix=/home/postgres/local/make -j 2make installexport PATH=$PATH:/home/postgres/local/bincd /home/postgres/pg_query_statemake install USE_PGXS=1
- Enable the extension:
# wherever your postgresql.conf is...echo"shared_preload_libraries = 'pg_query_state'">> /media/data/pg/postgresql.conf
- Start the DB and load TPC-DS data (scale factor of 1GB will work)
- Start a PSQL session, get the backend PID. Use this session to execute the above query over and over again
- In another PSQL section, run
select * from pg_query_state($PID);while the query is running. After 1-5 tries, Postgres has a segfault.
Is there a way I can build PG to provide more useful info about what is going on? Are there log files I can provide?