Monday, May 12, 2008

[PERFORM] Partitioning: INSERT 0 0 but want INSERT 0 1

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: partitions; Type: SCHEMA; Schema: -; Owner: cdr
--

CREATE SCHEMA partitions;


ALTER SCHEMA partitions OWNER TO cdr;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: cdr
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO cdr;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: ttt; Type: TABLE; Schema: public; Owner: cdr; Tablespace:
--

CREATE TABLE ttt (
id integer NOT NULL,
a timestamp without time zone,
b character varying
);


ALTER TABLE public.ttt OWNER TO cdr;

--
-- Name: ttt_id_seq; Type: SEQUENCE; Schema: public; Owner: cdr
--

CREATE SEQUENCE ttt_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;


ALTER TABLE public.ttt_id_seq OWNER TO cdr;

--
-- Name: ttt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cdr
--

ALTER SEQUENCE ttt_id_seq OWNED BY ttt.id;


--
-- Name: ttt_id_seq; Type: SEQUENCE SET; Schema: public; Owner: cdr
--

SELECT pg_catalog.setval('ttt_id_seq', 17, true);


--
-- Name: id; Type: DEFAULT; Schema: public; Owner: cdr
--

ALTER TABLE ttt ALTER COLUMN id SET DEFAULT nextval('ttt_id_seq'::regclass);


SET search_path = partitions, pg_catalog;

--
-- Name: ttt_050508; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050508 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050508_a_check CHECK (((a >= '2008-05-05'::date) AND (a < '2008-05-06'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_050508 OWNER TO cdr;

--
-- Name: ttt_050608; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050608 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050608_a_check CHECK (((a >= '2008-05-06'::date) AND (a < '2008-05-07'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_050608 OWNER TO cdr;

--
-- Name: ttt_050708; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050708 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050708_a_check CHECK (((a >= '2008-05-07'::date) AND (a < '2008-05-08'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_050708 OWNER TO cdr;

--
-- Name: ttt_050808; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050808 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050808_a_check CHECK (((a >= '2008-05-08'::date) AND (a < '2008-05-09'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_050808 OWNER TO cdr;

--
-- Name: ttt_050908; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050908 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050908_a_check CHECK (((a >= '2008-05-09'::date) AND (a < '2008-05-10'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_050908 OWNER TO cdr;

--
-- Name: ttt_051008; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051008 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051008_a_check CHECK (((a >= '2008-05-10'::date) AND (a < '2008-05-11'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_051008 OWNER TO cdr;

--
-- Name: ttt_051108; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051108 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051108_a_check CHECK (((a >= '2008-05-11'::date) AND (a < '2008-05-12'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_051108 OWNER TO cdr;

--
-- Name: ttt_051208; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051208 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051208_a_check CHECK (((a >= '2008-05-12'::date) AND (a < '2008-05-13'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_051208 OWNER TO cdr;

--
-- Name: ttt_051308; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051308 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051308_a_check CHECK (((a >= '2008-05-13'::date) AND (a < '2008-05-14'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_051308 OWNER TO cdr;

--
-- Name: ttt_051408; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051408 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051408_a_check CHECK (((a >= '2008-05-14'::date) AND (a < '2008-05-15'::date)))
)
INHERITS (public.ttt);


ALTER TABLE partitions.ttt_051408 OWNER TO cdr;

SET search_path = public, pg_catalog;

--
-- Name: ttt_insert_trigger(); Type: FUNCTION; Schema: public; Owner: cdr
--

CREATE FUNCTION ttt_insert_trigger() RETURNS trigger
AS $$
BEGIN
IF (NEW.a >= DATE '05-05-08' AND NEW.a < DATE '05-06-08') THEN
INSERT INTO partitions.ttt_050508 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-06-08' AND NEW.a < DATE '05-07-08') THEN
INSERT INTO partitions.ttt_050608 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-07-08' AND NEW.a < DATE '05-08-08') THEN
INSERT INTO partitions.ttt_050708 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-08-08' AND NEW.a < DATE '05-09-08') THEN
INSERT INTO partitions.ttt_050808 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-09-08' AND NEW.a < DATE '05-10-08') THEN
INSERT INTO partitions.ttt_050908 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-10-08' AND NEW.a < DATE '05-11-08') THEN
INSERT INTO partitions.ttt_051008 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-11-08' AND NEW.a < DATE '05-12-08') THEN
INSERT INTO partitions.ttt_051108 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-12-08' AND NEW.a < DATE '05-13-08') THEN
INSERT INTO partitions.ttt_051208 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-13-08' AND NEW.a < DATE '05-14-08') THEN
INSERT INTO partitions.ttt_051308 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-14-08' AND NEW.a < DATE '05-15-08') THEN
INSERT INTO partitions.ttt_051408 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'ttt.a out of range: 2008-05-05 <= % < 2008-05-14', NEW.a;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;


ALTER FUNCTION public.ttt_insert_trigger() OWNER TO cdr;

SET search_path = partitions, pg_catalog;

--
-- Data for Name: ttt_050508; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050508 (id, a, b) FROM stdin;
1 2008-05-05 00:00:00 test11212
2 2008-05-05 00:00:00 test11212
3 2008-05-05 00:00:00 test11212
4 2008-05-05 00:00:00 test11212
5 2008-05-05 00:00:00 test11212
6 2008-05-05 00:00:00 test11212
7 2008-05-05 00:00:00 test11212
8 2008-05-05 00:00:00 test11212
9 2008-05-05 00:00:00 test11212
10 2008-05-05 00:00:00 test11212
11 2008-05-05 00:00:00 test11212
12 2008-05-05 00:00:00 test11212
13 2008-05-05 00:00:00 test11212
14 2008-05-05 00:00:00 test11212
15 2008-05-05 00:00:00 test11212
16 2008-05-05 00:00:00 test11212
17 2008-05-05 00:00:00 test11212
\.


--
-- Data for Name: ttt_050608; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050608 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_050708; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050708 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_050808; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050808 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_050908; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050908 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_051008; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051008 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_051108; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051108 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_051208; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051208 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_051308; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051308 (id, a, b) FROM stdin;
\.


--
-- Data for Name: ttt_051408; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051408 (id, a, b) FROM stdin;
\.


SET search_path = public, pg_catalog;

--
-- Data for Name: ttt; Type: TABLE DATA; Schema: public; Owner: cdr
--

COPY ttt (id, a, b) FROM stdin;
\.


--
-- Name: ttt_pkey; Type: CONSTRAINT; Schema: public; Owner: cdr; Tablespace:
--

ALTER TABLE ONLY ttt
ADD CONSTRAINT ttt_pkey PRIMARY KEY (id);


SET search_path = partitions, pg_catalog;

--
-- Name: ttt_050508_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050508_a ON ttt_050508 USING btree (a);


--
-- Name: ttt_050608_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050608_a ON ttt_050608 USING btree (a);


--
-- Name: ttt_050708_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050708_a ON ttt_050708 USING btree (a);


--
-- Name: ttt_050808_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050808_a ON ttt_050808 USING btree (a);


--
-- Name: ttt_050908_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050908_a ON ttt_050908 USING btree (a);


--
-- Name: ttt_051008_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051008_a ON ttt_051008 USING btree (a);


--
-- Name: ttt_051108_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051108_a ON ttt_051108 USING btree (a);


--
-- Name: ttt_051208_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051208_a ON ttt_051208 USING btree (a);


--
-- Name: ttt_051308_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051308_a ON ttt_051308 USING btree (a);


--
-- Name: ttt_051408_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051408_a ON ttt_051408 USING btree (a);


SET search_path = public, pg_catalog;

--
-- Name: insert_ttt_trigger; Type: TRIGGER; Schema: public; Owner: cdr
--

CREATE TRIGGER insert_ttt_trigger
BEFORE INSERT ON ttt
FOR EACH ROW
EXECUTE PROCEDURE ttt_insert_trigger();


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--


I figure this subject belongs on the performance mailing list because it is about partitioning, which is a performance issue.

I'm working on partitioning some of the tables used by an application that uses OpenJPA.  It turns out that OpenJPA is sensitive to the numbers returned when you do an insert.  So I put together a test and attached it.  My postgres version is 8.3.1 compiled from source.

My problem is that this:
test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212');
INSERT 0 0
Time: 21.646 ms
needs to show:
INSERT 0 1

or OpenJPA will not accept it.  The insert works, but OpenJPA does not believe it and aborts the current transaction.

Is it possible to have partitioning and have insert show the right number of rows inserted?

Thanks,

--Nik
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)