pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning: INSERT 0 0 but want INSERT 0 1
3+ messages / 2 participants
[nested] [flat]
* Partitioning: INSERT 0 0 but want INSERT 0 1
@ 2008-05-12 16:18 Nikolas Everett <nik9000@gmail.com>
2008-05-12 22:27 ` Re: Partitioning: INSERT 0 0 but want INSERT 0 1 Neil Peter Braggio <pbraggio@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Nikolas Everett @ 2008-05-12 16:18 UTC (permalink / raw)
To: pgsql-performance
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
Attachments:
testdump.sql (text/plain, 11.0K) download
--
-- 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
--
* Re: Partitioning: INSERT 0 0 but want INSERT 0 1
2008-05-12 16:18 Partitioning: INSERT 0 0 but want INSERT 0 1 Nikolas Everett <nik9000@gmail.com>
@ 2008-05-12 22:27 ` Neil Peter Braggio <pbraggio@gmail.com>
2008-05-13 03:46 ` Re: Partitioning: INSERT 0 0 but want INSERT 0 1 Nikolas Everett <nik9000@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Neil Peter Braggio @ 2008-05-12 22:27 UTC (permalink / raw)
To: pgsql-performance
I have the same problem in PG 8.2
To resolve this issue I had to create a new table with the same
structure than the partitioned table with a trigger for insert and
update. All the operations the application have to do are directed to
this new table.
When a new record is inserted in the new table the trigger insert a
new record with the same values into the partitioned table and then
delete all records from this new table. In updates operations the
trigger redirect the operation to the partitioned table too.
With this _not elegant_ solution our Java application is able to do its job.
If you find a better solution please let me know.
----
Neil Peter Braggio
pbraggio@gmail.com
On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett <nik9000@gmail.com> wrote:
> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioning: INSERT 0 0 but want INSERT 0 1
2008-05-12 16:18 Partitioning: INSERT 0 0 but want INSERT 0 1 Nikolas Everett <nik9000@gmail.com>
2008-05-12 22:27 ` Re: Partitioning: INSERT 0 0 but want INSERT 0 1 Neil Peter Braggio <pbraggio@gmail.com>
@ 2008-05-13 03:46 ` Nikolas Everett <nik9000@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Nikolas Everett @ 2008-05-13 03:46 UTC (permalink / raw)
To: Neil Peter Braggio <pbraggio@gmail.com>; +Cc: pgsql-performance
If I can't find an answer in the next day or so I'll crack open OpenJPA and
disable that check. Its a very simple, if ugly, hack.
--Nik
On 5/12/08, Neil Peter Braggio <pbraggio@gmail.com> wrote:
>
> I have the same problem in PG 8.2
>
> To resolve this issue I had to create a new table with the same
> structure than the partitioned table with a trigger for insert and
> update. All the operations the application have to do are directed to
> this new table.
>
> When a new record is inserted in the new table the trigger insert a
> new record with the same values into the partitioned table and then
> delete all records from this new table. In updates operations the
> trigger redirect the operation to the partitioned table too.
>
> With this _not elegant_ solution our Java application is able to do its
> job.
>
> If you find a better solution please let me know.
>
> ----
> Neil Peter Braggio
> pbraggio@gmail.com
>
>
> On Tue, May 13, 2008 at 11:48 AM, Nikolas Everett <nik9000@gmail.com>
> wrote:
> > 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
> >
> >
> > --
> > Sent via pgsql-performance mailing list (
> pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
> >
> >
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-05-13 03:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-12 16:18 Partitioning: INSERT 0 0 but want INSERT 0 1 Nikolas Everett <nik9000@gmail.com>
2008-05-12 22:27 ` Neil Peter Braggio <pbraggio@gmail.com>
2008-05-13 03:46 ` Nikolas Everett <nik9000@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox