Re: large numbers of inserts out of memory strategy - Mailing list pgsql-general

On 2017-11-29 08:32:02 -0600, Ted Toth wrote:
> Yes I did generate 1 large DO block:
>
> DO $$
> DECLARE thingid bigint; thingrec bigint; thingdataid bigint;
> BEGIN
> INSERT INTO thing
> (ltn,classification,machine,source,thgrec,flags,serial,type) VALUES
> ('T007336','THING',0,1025,7336,7,'XXX869977564',1) RETURNING id,thgrec
> INTO thingid,thingrec;
> INSERT INTO recnum_thing (recnum,thing_id) VALUES (thingrec,thingid);
> INSERT INTO thingstatus
>
(thing_id,nrpts,rmks_cs,force_type_id,ftn_cs,force_code,arr_cs,mask,toi_state,plot_id,signa_cs,lastchange,des_cs,rig_cs,ownship,correlation,maxrpts,rtn_cs,ctc_cs,group_mask,dep_cs)
> VALUES (thingid,121,'{0,0,0,0}',440,0,23,0,0,0,'{23,-1,3803,3805,-1,-1,0,6}',0,1509459164,0,0,0,0,1000,0,0,0,0);
> INSERT INTO thinger
> (thing_id,spe_key,cse_unc,lat_spd,cov,dtg,lng,spd,ave_spd,cse,tol,nrpts,lat,alpha,sigma,spd_unc,lng_spd)
> VALUES
(thingid,-1,0.0,-6.58197336634e-08,'{4.27624291532e-09,0.0,3.07802916488e-09,0.0,4.27624291532e-09,0.0,3.07802916488e-09,4.16110417234e-08,0.0,4.16110417234e-08}',1509459163,2.21596980095,0.000226273215958,1.0,0.0,0.10000000149,121,0.584555745125,10.0,4.23079740131e-08,0.0,-2.49999881907e-10);
> INSERT INTO thingdata
>
(thing_id,category,db_num,xref,org_type,trademark,shortname,fcode,platform,callsign,type,orig_xref,shipclass,home_base,uic,service,di,lngfixed,hull,precision,alert,flag,besufx,name,mmsi,catcode,ntds,imo,pn_num,chxref,threat,sconum,latfixed,db_type,pif,echelon,jtn,quantity,overwrite)
> VALUES
(thingid,'XXX','','','','','004403704','23','','','','','UNEQUATED','','','','',0.0,'','{0,0,0,0,0}','','KS','','UNKNOWN','004403704','','','','','','AFD','',0.0,3,'','',0,0,0)
> RETURNING id INTO thingdataid;
> INSERT INTO thingnum (thingdata_id,thgnum,state,dtg,cmd) VALUES
> (thingdataid,'013086',0,1502970401,'FOO');
>
> <repeated for each thing>
>
> END $$;
>
> Should I limit the number of 'thing' inserts within a DO block or
> wrapping each 'thing' insert in it's own DO block?

I would suggest getting rid of the do block entirely if that is
possible. Just create lots of insert statements. You can get the current
value of a sequence with currval('sequence_name').

Alternately or in addition, since you are using python, you might want
to insert directly into the database from python using psycopg2. For
separate insert statements that should have about the same performance.
(It is usually much faster to write to a csv file and load that with
copy than to insert each row, but you don't do that and it might be
difficult in your case).
       hp

--   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>