PostgreSQL Server Crashes

[TempKmkim]

Thanks for your nice work!

Could you help me solve the following issue for running JOB queries? The PostgreSQL server crashes whenever I switch to use QuerySplit and run a query.

The steps I did:

1. Downloaded PostgreSQL 12.3 and replaced the files with yours (SPJ version).
2. Built a docker image out of this.
3. Imported the IMDB database and built all PK, FK indexes inside the docker container.
4. Ran "switch to ..." and a JOB query inside the PostgreSQL client. Here, running a query after "switch to Postgres" or without it works fine, but after "switch to relationshipcenter" or "switch to entitycenter" the query crashes the PostgreSQL server.

Are you also considering releasing a docker image that contains all necessary codes and binaries to run QuerySplit?

[zzjjyyy]

I will try to fix it. Although I never considered building a docker image, I will try to build one and release it as soon as possible.

[zzjjyyy]

Can you show the query that you run, so I can repeat it on my computer?

[zzjjyyy]

Interestingly, the code on my computer is Ok. Can you give me more detailed Debug info ? So sorry for the inconvenience.

[TempKmkim]

Thanks! I first ran the JOB query 9c and 15c mentioned in the paper.

9c: SELECT MIN(an.name) AS alternative_name, MIN(chn.name) AS voiced_character_name, MIN(n.name) AS voicing_actress, MIN(t.title) AS american_movie FROM aka_name AS an, char_name AS chn, cast_info AS ci, company_name AS cn, movie_companies AS mc, name AS n, role_type AS rt, title AS t WHERE ci.note in ('(voice)', '(voice: Japanese version)', '(voice) (uncredited)', '(voice: English version)') AND cn.country_code ='[us]' AND n.gender ='f' and n.name like '%An%' AND rt.role ='actress' AND ci.movie_id = t.id AND t.id = mc.movie_id AND ci.movie_id = mc.movie_id AND mc.company_id = cn.id AND ci.role_id = rt.id AND n.id = ci.person_id AND chn.id = ci.person_role_id AND an.person_id = n.id AND an.person_id = ci.person_id;

15c: SELECT MIN(mi.info) AS release_date, MIN(t.title) AS modern_american_internet_movie FROM aka_title AS at, company_name AS cn, company_type AS ct, info_type AS it1, keyword AS k, movie_companies AS mc, movie_info AS mi, movie_keyword AS mk, title AS t WHERE cn.country_code = '[us]' AND it1.info = 'release dates' AND mi.note like '%internet%' AND mi.info is not NULL and (mi.info like 'USA:% 199%' or mi.info like 'USA:% 200%') AND t.production_year > 1990 AND t.id = at.movie_id AND t.id = mi.movie_id AND t.id = mk.movie_id AND t.id = mc.movie_id AND mk.movie_id = mi.movie_id AND mk.movie_id = mc.movie_id AND mk.movie_id = at.movie_id AND mi.movie_id = mc.movie_id AND mi.movie_id = at.movie_id AND mc.movie_id = at.movie_id AND k.id = mk.keyword_id AND it1.id = mi.info_type_id AND cn.id = mc.company_id AND ct.id = mc.company_type_id;

[TempKmkim]

Here's the log messages (just saying that a seg fault has occurred) I got after running 9c.

[23] DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0 [23] LOG: statement: SELECT MIN(an.name) AS alternative_name, MIN(chn.name) AS voiced_character_name, MIN(n.name) AS voicing_actress, MIN(t.title) AS american_movie FROM aka_name AS an, char_name AS chn, cast_info AS ci, company_name AS cn, movie_companies AS mc, name AS n, role_type AS rt, title AS t WHERE ci.note in ('(voice)', '(voice: Japanese version)', '(voice) (uncredited)', '(voice: English version)') AND cn.country_code ='[us]' AND n.gender ='f' and n.name like '%An%' AND rt.role ='actress' AND ci.movie_id = t.id AND t.id = mc.movie_id AND ci.movie_id = mc.movie_id AND mc.company_id = cn.id AND ci.role_id = rt.id AND n.id = ci.person_id AND chn.id = ci.person_role_id AND an.person_id = n.id AND an.person_id = ci.person_id; [1] DEBUG: reaping dead processes [1] DEBUG: server process (PID 23) was terminated by signal 11: Segmentation fault [1] DETAIL: Failed process was running: SELECT MIN(an.name) AS alternative_name, MIN(chn.name) AS voiced_character_name, MIN(n.name) AS voicing_actress, MIN(t.title) AS american_movie FROM aka_name AS an, char_name AS chn, cast_info AS ci, company_name AS cn, movie_companies AS mc, name AS n, role_type AS rt, title AS t WHERE ci.note in ('(voice)', '(voice: Japanese version)', '(voice) (uncredited)', '(voice: English version)') AND cn.country_code ='[us]' AND n.gender ='f' and n.name like '%An%' AND rt.role ='actress' AND ci.movie_id = t.id AND t.id = mc.movie_id AND ci.movie_id = mc.movie_id AND mc.company_id = cn.id AND ci.role_id = rt.id AND n.id = ci.person_id AND chn.id = ci.person_role_id AND an.person_id = n.id AND an.person_id = ci.person_id; [1] LOG: server process (PID 23) was terminated by signal 11: Segmentation fault [1] DETAIL: Failed process was running: SELECT MIN(an.name) AS alternative_name, MIN(chn.name) AS voiced_character_name, MIN(n.name) AS voicing_actress, MIN(t.title) AS american_movie FROM aka_name AS an, char_name AS chn, cast_info AS ci, company_name AS cn, movie_companies AS mc, name AS n, role_type AS rt, title AS t WHERE ci.note in ('(voice)', '(voice: Japanese version)', '(voice) (uncredited)', '(voice: English version)') AND cn.country_code ='[us]' AND n.gender ='f' and n.name like '%An%' AND rt.role ='actress' AND ci.movie_id = t.id AND t.id = mc.movie_id AND ci.movie_id = mc.movie_id AND mc.company_id = cn.id AND ci.role_id = rt.id AND n.id = ci.person_id AND chn.id = ci.person_role_id AND an.person_id = n.id AND an.person_id = ci.person_id;

[zzjjyyy]

The queries are executed well on my Windows computer, so I guess the only reason is different compilers. I found a docker Linux image without data that I built before and it was tested that can work. Using the command docker pull zhaojy20/query_split:v1. The total uncompressed image should be about 600-700MB.

[TempKmkim]

Thank you for the image! Now the Postgres server does not crash, but running the query 9c after "switch to relationshipcenter" or "switch to entitycenter" does not output any result, where running the query after "switch to Postgres" or "switch to minsubquery" outputs one tuple.

[zzjjyyy]

Oh, it's possible, I never check the output result for the image. It was used about 2 years ago. Do other queries work correctly? If others work well, it could be a new bug that I never find. I guess it is due to the cycle between the table an, n, and ci. You can try to remove the Fk constraint between relation an and n. Because in my database, it seems no this Fk constraint. But I'm not sure whether it will work. The problem is that on my Windows 10 computer with VS Studio, my code works well and outputs correctly, so it is difficult for me to debug your problem. I'm so sorry for the inconvenience.