Re: Partitioning and ORM tools

[Brian Fehrle <bfehrle@xxxxxxxxxxxx>]

Here is a working example of trigger based partitioning with a view and 'do instead' that works with ORM tools using the affected rows return (example attached).

The key things that make it work are:

1. RETURN NEW; (in the function after inserting into the partition)

2. INSTEAD OF INSERT (in the trigger)


example:
insert into data_log_view (date, thingy) values ('2015-01-02', 'test');
INSERT 0 1

On 3/24/16 8:28 AM, CS DBA wrote:

On 03/23/2016 02:48 AM, Chris Travers wrote:

Brian Fehrle

Database Administrator II

|

comScore, Inc.

(NASDAQ:SCOR)

bfehrle@xxxxxxxxxxxx

 |  | CO 

............................................................................................................................................................................................................................

Rentrak and comScore are now one, creating the new model for a dynamic cross-platform world. To learn more, visit: www.comscore.com

On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers <chris.travers@xxxxxxxxx> wrote:

Use a view with a DO INSTEAD trigger.   That will allow you to return the tuple properly.

On Tue, Mar 22, 2016 at 7:40 PM, CS DBA <cs_dba@xxxxxxxxxxxxxxxxxxx> wrote:

Hi All;

we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which causes the ORM tool to assume the insert inserted 0 rows.  Is there a standard / best practices work around for this?

Apologies for the top post above.

Just noting additionally that the view with DO INSTEAD approach was suggested to me by Matt Trout (major contributor to the DBIx::Class ORM in Perl.

I have used it.  It works well.  I think it is the best practice there.

Thanks in advance




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Best Wishes,

Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

http://www.efficito.com/learn_more

--

Best Wishes,

Chris Travers

Efficito:  Hosted Accounting and ERP.  Robust and Flexible.  No vendor lock-in.

http://www.efficito.com/learn_more

All;

Thanks for the great Ideas, I'll let you know where we end up.

--
-- PostgreSQL database dump
--

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;

--
-- Name: part; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA part;


ALTER SCHEMA part OWNER TO postgres;

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

--
-- Name: insert_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION insert_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    INSERT INTO part.data_log_2015 VALUES (NEW.*);
    RETURN NEW;
END;
$$;


ALTER FUNCTION public.insert_trigger() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: data_log; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE data_log (
    data_log_sid integer NOT NULL,
    date timestamp without time zone NOT NULL,
    thingy character varying
);


ALTER TABLE data_log OWNER TO postgres;

--
-- Name: data_log_data_log_sid_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE data_log_data_log_sid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE data_log_data_log_sid_seq OWNER TO postgres;

--
-- Name: data_log_data_log_sid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE data_log_data_log_sid_seq OWNED BY data_log.data_log_sid;


SET search_path = part, pg_catalog;

--
-- Name: data_log_2015; Type: TABLE; Schema: part; Owner: postgres; Tablespace: 
--

CREATE TABLE data_log_2015 (
    data_log_sid integer DEFAULT nextval('public.data_log_data_log_sid_seq'::regclass),
    date timestamp without time zone,
    thingy character varying
)
INHERITS (public.data_log);


ALTER TABLE data_log_2015 OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- Name: data_log_view; Type: VIEW; Schema: public; Owner: postgres
--

CREATE VIEW data_log_view AS
 SELECT data_log.data_log_sid,
    data_log.date,
    data_log.thingy
   FROM data_log;


ALTER TABLE data_log_view OWNER TO postgres;

--
-- Name: data_log_sid; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY data_log ALTER COLUMN data_log_sid SET DEFAULT nextval('data_log_data_log_sid_seq'::regclass);


--
-- Name: data_log_sid; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY data_log_view ALTER COLUMN data_log_sid SET DEFAULT nextval('data_log_data_log_sid_seq'::regclass);


SET search_path = part, pg_catalog;

--
-- Name: data_log_2015_pkey; Type: CONSTRAINT; Schema: part; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY data_log_2015
    ADD CONSTRAINT data_log_2015_pkey PRIMARY KEY (data_log_sid);


SET search_path = public, pg_catalog;

--
-- Name: data_log_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY data_log
    ADD CONSTRAINT data_log_pkey PRIMARY KEY (data_log_sid);


SET search_path = part, pg_catalog;

--
-- Name: data_log_2015_date_idx; Type: INDEX; Schema: part; Owner: postgres; Tablespace: 
--

CREATE INDEX data_log_2015_date_idx ON data_log_2015 USING btree (date);


SET search_path = public, pg_catalog;

--
-- Name: data_log_date_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE INDEX data_log_date_idx ON data_log USING btree (date);


--
-- Name: insert_trigger; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER insert_trigger INSTEAD OF INSERT ON data_log_view FOR EACH ROW EXECUTE PROCEDURE 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
--

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general