Thread: BUG #13633: ERROR: invalid memory alloc request size
BUG #13633: ERROR: invalid memory alloc request size
From
andreas-postgresql@creative-memory.de
Date:
The following bug has been logged on the website: Bug reference: 13633 Logged by: Andreas Hauser Email address: andreas-postgresql@creative-memory.de PostgreSQL version: 9.5alpha2 Operating system: openSUSE 12.2 (x86_64) Description: 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 EXPLAIN: QUERY PLAN --------------------------------------------------------------------------------------------------------- HashAggregate (cost=49845576.86..50041694.10 rows=19611724 width=26) Group Key: g.markerid, g.allel1, g.allel2 -> Hash Join (cost=38473815.11..48892997.85 rows=95257901 width=26) Hash Cond: (m.markerid = g.markerid) -> Seq Scan on marker m (cost=0.00..29420.53 rows=777683 width=18) Filter: (version = '3'::smallint) -> Hash (cost=37266606.89..37266606.89 rows=96576658 width=26) -> Hash Join (cost=34061077.42..37266606.89 rows=96576658 width=26) Hash Cond: (ts.tierlidint = g.tierlidint) -> Seq Scan on tiereinsets ts (cost=0.00..3611.29 rows=993 width=4) Filter: (tiersetid = 'HD.09.15'::text) -> Hash (cost=24041909.00..24041909.00 rows=801533474 width=26) -> Seq Scan on genotyphd g (cost=0.00..24041909.00 rows=801533474 width=26) Filter: (allel1 <> allel2) installed from source with ./configure --prefix: # select version(); version ---------------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 9.5alpha2 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.7.1 20120723 [gcc-4_7-branch revision 189773], 64-bit $ locale LANG=de_DE.UTF-8 LC_CTYPE="de_DE.UTF-8" LC_NUMERIC="de_DE.UTF-8" LC_TIME="de_DE.UTF-8" LC_COLLATE=C LC_MONETARY="de_DE.UTF-8" LC_MESSAGES=C LC_PAPER="de_DE.UTF-8" LC_NAME="de_DE.UTF-8" LC_ADDRESS="de_DE.UTF-8" LC_TELEPHONE="de_DE.UTF-8" LC_MEASUREMENT="de_DE.UTF-8" LC_IDENTIFICATION="de_DE.UTF-8" LC_ALL= -- -- PostgreSQL database cluster dump -- SET default_transaction_read_only = off; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; -- -- Roles -- CREATE ROLE andy; ALTER ROLE andy WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION BYPASSRLS; -- -- Database creation -- CREATE DATABASE andy WITH TEMPLATE = template0 OWNER = andy; CREATE DATABASE "snpDB" WITH TEMPLATE = template0 OWNER = andy; REVOKE ALL ON DATABASE template1 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM andy; GRANT ALL ON DATABASE template1 TO andy; GRANT CONNECT ON DATABASE template1 TO PUBLIC; \connect andy SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- -- Dumped from database version 9.5alpha2 -- Dumped by pg_dump version 9.5alpha2 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- Name: public; Type: ACL; Schema: -; Owner: andy -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM andy; GRANT ALL ON SCHEMA public TO andy; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect postgres SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- -- Dumped from database version 9.5alpha2 -- Dumped by pg_dump version 9.5alpha2 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; -- -- Name: postgres; Type: COMMENT; Schema: -; Owner: andy -- COMMENT ON DATABASE postgres IS 'default administrative connection database'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- Name: public; Type: ACL; Schema: -; Owner: andy -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM andy; GRANT ALL ON SCHEMA public TO andy; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect "snpDB" SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- -- Dumped from database version 9.5alpha2 -- Dumped by pg_dump version 9.5alpha2 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: genotyphd; Type: TABLE; Schema: public; Owner: andy -- CREATE TABLE genotyphd ( tierlid text NOT NULL, markerid text NOT NULL, gst text NOT NULL, allel1 text, allel2 text, commentid smallint, tierlidint integer ); ALTER TABLE genotyphd OWNER TO andy; -- -- Name: marker; Type: TABLE; Schema: public; Owner: andy -- CREATE TABLE marker ( markerid text NOT NULL, version smallint NOT NULL, chr text, pos integer, a1 character(1), a2 character(1), topseq text, forallel1 character(1), forallel2 character(1), forseq text, aa text, comentar text ); ALTER TABLE marker OWNER TO andy; -- -- Name: tiereinsets; Type: TABLE; Schema: public; Owner: andy -- CREATE TABLE tiereinsets ( tierid text NOT NULL, tst text NOT NULL, tiersetid text NOT NULL, tierlid text NOT NULL, familie text NOT NULL, tierlidint integer ); ALTER TABLE tiereinsets OWNER TO andy; -- -- Name: tierlid; Type: TABLE; Schema: public; Owner: andy -- CREATE TABLE tierlid ( tierlidint integer NOT NULL, tierlid text ); ALTER TABLE tierlid OWNER TO andy; -- -- Name: tierlid_tierlidint_seq; Type: SEQUENCE; Schema: public; Owner: andy -- CREATE SEQUENCE tierlid_tierlidint_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE tierlid_tierlidint_seq OWNER TO andy; -- -- Name: tierlid_tierlidint_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: andy -- ALTER SEQUENCE tierlid_tierlidint_seq OWNED BY tierlid.tierlidint; -- -- Name: tierlidint; Type: DEFAULT; Schema: public; Owner: andy -- ALTER TABLE ONLY tierlid ALTER COLUMN tierlidint SET DEFAULT nextval('tierlid_tierlidint_seq'::regclass); -- -- Name: pk_genotyphd; Type: CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY genotyphd ADD CONSTRAINT pk_genotyphd PRIMARY KEY (tierlid, markerid, gst); -- -- Name: pk_markerseq_1; Type: CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY marker ADD CONSTRAINT pk_markerseq_1 PRIMARY KEY (markerid, version); -- -- Name: pk_tiereinsets; Type: CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY tiereinsets ADD CONSTRAINT pk_tiereinsets PRIMARY KEY (tierid, tst, tiersetid, tierlid, familie); -- -- Name: tierlid_pkey; Type: CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY tierlid ADD CONSTRAINT tierlid_pkey PRIMARY KEY (tierlidint); -- -- Name: tierlid_tierlid_key; Type: CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY tierlid ADD CONSTRAINT tierlid_tierlid_key UNIQUE (tierlid); -- -- Name: genotyphd_tierlidint_fkey; Type: FK CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY genotyphd ADD CONSTRAINT genotyphd_tierlidint_fkey FOREIGN KEY (tierlidint) REFERENCES tierlid(tierlidint) ON DELETE RESTRICT; -- -- Name: tiereinsets_tierlidint_fkey; Type: FK CONSTRAINT; Schema: public; Owner: andy -- ALTER TABLE ONLY tiereinsets ADD CONSTRAINT tiereinsets_tierlidint_fkey FOREIGN KEY (tierlidint) REFERENCES tierlid(tierlidint) ON DELETE RESTRICT; -- -- Name: public; Type: ACL; Schema: -; Owner: andy -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM andy; GRANT ALL ON SCHEMA public TO andy; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- \connect template1 SET default_transaction_read_only = off; -- -- PostgreSQL database dump -- -- Dumped from database version 9.5alpha2 -- Dumped by pg_dump version 9.5alpha2 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; -- -- Name: template1; Type: COMMENT; Schema: -; Owner: andy -- COMMENT ON DATABASE template1 IS 'default template for new databases'; -- -- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; -- -- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; -- -- Name: public; Type: ACL; Schema: -; Owner: andy -- REVOKE ALL ON SCHEMA public FROM PUBLIC; REVOKE ALL ON SCHEMA public FROM andy; GRANT ALL ON SCHEMA public TO andy; GRANT ALL ON SCHEMA public TO PUBLIC; -- -- PostgreSQL database dump complete -- -- -- PostgreSQL database cluster dump complete --
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
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
On 23 September 2015 at 04:48, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 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? > > This seems related to this http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp work_mem, I believe, must be higher than 1GB to trigger this. The patch attached to that thread has a bug in the following line + memset(hashtable->buckets, 0, sizeof(nbuckets * sizeof(HashJoinTuple))); It should be: + memset(hashtable->buckets, 0, nbuckets * sizeof(HashJoinTuple)); Regards David Rowley -- David Rowley http://www.2ndQuadrant.com/ <http://www.2ndquadrant.com/> PostgreSQL Development, 24x7 Support, Training & Services
David Rowley <david.rowley@2ndquadrant.com> writes: > On 23 September 2015 at 04:48, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> andreas-postgresql@creative-memory.de writes: >>> ERROR: invalid memory alloc request size 1073741824 >> FWIW, I could not reproduce this on the basis of the supplied information, > This seems related to this > http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp > work_mem, I believe, must be higher than 1GB to trigger this. Hmm ... yeah, that issue is a possible explanation if work_mem is high enough and it's trying to use a hash join. regards, tom lane
david.rowley wrote @ Wed, 23 Sep 2015 10:57:25 +1200: > > On 23 September 2015 at 04:48, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > 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? > > > > > This seems related to this > http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp > > work_mem, I believe, must be higher than 1GB to trigger this. > I can confirm that it works when work_mem is reduced to 1GB. Cheers and thanks Andreas
Andreas Hauser <andreas-postgresql@creative-memory.de> writes: > david.rowley wrote @ Wed, 23 Sep 2015 10:57:25 +1200: >> This seems related to this >> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp >> >> work_mem, I believe, must be higher than 1GB to trigger this. > I can confirm that it works when work_mem is reduced to 1GB. OK, that seems to confirm the diagnosis. Thanks. That patch thread seems to have gone off into the weeds, but I'll make sure something gets done about it before 9.5beta1. regards, tom lane
On 2015-09-23 09:24:52 -0400, Tom Lane wrote: > Andreas Hauser <andreas-postgresql@creative-memory.de> writes: > > david.rowley wrote @ Wed, 23 Sep 2015 10:57:25 +1200: > >> This seems related to this > >> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp > That patch thread seems to have gone off into the weeds, but I'll make > sure something gets done about it before 9.5beta1. Hm. I think this is still an open item? Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes: > On 2015-09-23 09:24:52 -0400, Tom Lane wrote: >> Andreas Hauser <andreas-postgresql@creative-memory.de> writes: >>> This seems related to this >>> http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F8010F6F3F@BPXM15GP.gisp.nec.co.jp >> That patch thread seems to have gone off into the weeds, but I'll make >> sure something gets done about it before 9.5beta1. > Hm. I think this is still an open item? Yeah, it is. I think somebody's just going to have to make an executive decision about how invasive to be. At this point I'd vote for "minimally". I will be stuck in release-note land for at least the next several hours, but will look at this tomorrow if no other committer gets to it. regards, tom lane