Обсуждение: BUG #13633: ERROR: invalid memory alloc request size

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