tgl wrote @ Tue, 22 Sep 2015 12:48:40 -0400:
> andreas-postgresql@creative-memory.de writes:
> > SELECT G.MarkerID,G.Allel1,G.Allel2, count (G.TierLIDint) as nH
> > FROM GenotypHD as G, TiereInSets as TS, Marker as M
> > WHERE TS.TierSetID='HD.09.15' and TS.TierLIDint=G.TierLIDint and
> > M.Version='3' and M.MarkerID=G.MarkerID and G.Allel1<>G.Allel2
> > GROUP BY G.MarkerID,G.Allel1,G.Allel2;
>
> > ERROR: invalid memory alloc request size 1073741824
>
> FWIW, I could not reproduce this on the basis of the supplied information,
> ie just creating the tables and running this query does not do anything
> surprising. It looks like possibly a corrupt-data issue --- can you do
> "select * from" each table without problems?
>
> regards, tom lane
Yes and pg_dumpall works.
The dump is 32GB, the machine has 128GB, work_mem = 70G.
After filling the added serial, disk usage more than doubled
to 221GB. Vacuum did not change this.
I had similar problems with 9.4.4, but switched to 9.5. to be able
to fill the new serial field with a sub-select. The query using the
text id gave the same problem, but sometimes it worked (repeatedly
submitting it with pipe to pgsql would sometimes succeed and run
for about an hour).
Cheers
Andreas