BUG #13633: ERROR: invalid memory alloc request size - Mailing list pgsql-bugs

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
--