I just had the experience that running a query to a PostgreSQL database using IJulia and LibPQ brought my computer to a halt and I had to push the power button to shut it off.
This happened in 1.0. The memory usage was quickly pushed up to a level exceeding my laptop’s capacity (16G ram). I tried again and killed the process when the memory usage passed 3.8Gb. The same code worked without a problem in 0.6-4 and memory usage never even get up to 1Gb and finishing within less than a second.
How do I debug it?
The code:
using Queryverse, VegaDatasets, VegaLite
using LibPQ
import LibPQ.libpq_c: PQputCopyData, PQputCopyEnd
using DataFrames, CSV, DataFramesMeta, Query
function read_ini()
h = homedir()
f = ".pg.ini"
l = readlines(open("$h/$f"))[2:end]
conn_string = join(l," ")
end
function dbcon()
c = replace(join([read_ini(),"dbname = wos"]," "),"5432"=>"63334")
conn = LibPQ.Connection(c);
end
function runselectquery(query)
conn = dbcon()
df= fetch!(DataFrame, execute(conn, query))
close(conn)
df
end
jaara = 2005
jaarb = 2017
query = """with
w as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null
group by 1),
d as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "DHET" is not null
group by 1),
i as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "IBSS" is not null
group by 1),
s as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null
group by 1),
n as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "NWList" is not null
group by 1),
c as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scielo" is not null
group by 1),
ws as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scopus" is not null
group by 1),
wd as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "DHET" is not null
group by 1),
wc as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scielo" is not null
group by 1),
wn as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "NWList" is not null
group by 1),
wi as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "IBSS" is not null
group by 1),
cd as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scielo" is not null and "DHET" is not null
group by 1) ,
cn as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scielo" is not null and "NWList" is not null
group by 1),
ci as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scielo" is not null and "IBSS" is not null
group by 1),
di as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "DHET" is not null and "IBSS" is not null
group by 1),
ni as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "NWList" is not null and "IBSS" is not null
group by 1),
nd as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "NWList" is not null and "DHET" is not null
group by 1),
sd as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null and "DHET" is not null
group by 1),
sc as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null and "Scielo" is not null
group by 1),
sn as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null and "NWList" is not null
group by 1),
si as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null and "IBSS" is not null
group by 1),
scn as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null
and "Scielo" is not null and "NWList" is not null
group by 1),
wsc as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scopus" is not null
and "Scielo" is not null
group by 1),
wsd as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scopus" is not null
and "DHET" is not null
group by 1),
wid as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "IBSS" is not null
and "DHET" is not null
group by 1),
sid as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Scopus" is not null and "IBSS" is not null
and "DHET" is not null
group by 1),
wscn as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scopus" is not null
and "Scielo" is not null and "NWList" is not null
group by 1),
wscni as (select "Publication Year"::integer pubyear, count(distinct "Ltitle") titles
from public."sak_Master_Table_2017_Submissions"
where "Web of Science" is not null and "Scopus" is not null
and "Scielo" is not null and "NWList" is not null
and "IBSS" is not null
group by 1)
select w.pubyear, w.titles wos, s.titles scopus, i.titles ibss, c.titles scielo, n.titles norwegian, d.titles dhet,
ws.titles WoS_Scopus, wd.titles WoS_DHET, wn.titles Wos_NO, wc.titles Wos_Scielo, wi.titles WoS_IBSS,
si.titles Scopus_IBSS, sc.titles Scopus_Scielo, sn.titles Scopus_NO, sd.titles Scopus_DHET,
ci.titles Scielo_IBSS, cn.titles Scielo_NO, cd.titles Scielo_DHET, di.titles DHET_IBSS,
ni.titles NO_IBSS, nd.titles NO_DHET,
wsc.titles WoS_Scopus_Scielo, wscn.titles WoS_Scopus_Scielo_NO,
scn.titles Scopus_Scielo_NO, wscni.titles Wos_Scopus_Scielo_NO_IBSS,
wsd.titles WoS_Scopus_DHET, wid.titles as WoS_IBSS_DHET,
sid.titles Scopus_IBSS_DHET
from
w
left join s on s.pubyear = w.pubyear
left join i on i.pubyear = w.pubyear
left join c on c.pubyear = w.pubyear
left join n on n.pubyear = w.pubyear
left join d on d.pubyear = w.pubyear
left join wi on wi.pubyear = w.pubyear
left join wc on wc.pubyear = w.pubyear
left join wn on wn.pubyear = w.pubyear
left join wd on wd.pubyear = w.pubyear
left join ws on ws.pubyear = w.pubyear
left join si on si.pubyear = w.pubyear
left join sc on sc.pubyear = w.pubyear
left join sn on sn.pubyear = w.pubyear
left join sd on sd.pubyear = w.pubyear
left join ci on ci.pubyear = w.pubyear
left join cn on cn.pubyear = w.pubyear
left join cd on cd.pubyear = w.pubyear
left join ni on ni.pubyear = w.pubyear
left join nd on nd.pubyear = w.pubyear
left join di on di.pubyear = w.pubyear
left join wsc on wsc.pubyear = w.pubyear
left join wscn on wscn.pubyear = w.pubyear
left join wscni on wscni.pubyear = w.pubyear
left join scn on scn.pubyear = w.pubyear
left join wsd on wsd.pubyear = w.pubyear
left join wid on wid.pubyear = w.pubyear
left join sid on sid.pubyear = w.pubyear
where w.pubyear between 2016 and 2017
"""
df = runselectquery(query);