Re: [GENERAL] Partitioning and ORM tools

[Brian Fehrle]

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:



On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers
> 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
 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@postgresql.org)
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.





Brian FehrleDatabase Administrator II | comScore, Inc. (NASDAQ:SCOR)
bfeh...@comscore.com
|  | CO

Rentrak and comScore are now one, creating the new model for a dynamic 
cross-platform world. To learn more, visit: www.comscore.com
--
-- 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 

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

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



On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers 
> 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
>
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@postgresql.org
)
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.

Re: [GENERAL] Partitioning and ORM tools

[Chris Travers]

On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers 
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 
> 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@postgresql.org)
>> 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

Re: [GENERAL] Partitioning and ORM tools

[Chris Travers]

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  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?
>
> Thanks in advance
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> 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

Re: [GENERAL] Partitioning and ORM tools

[John R Pierce]

On 3/22/2016 2:20 PM, CS DBA wrote:
I would think the ORM (as yet undefined) would want to think in terms 
of the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking 
for non-zero inserts?





That was our first suggestion, they don;t want to make any app changes


so they want someone else to make major architectural changes. great.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Partitioning and ORM tools

[Manuel Kniep]

> So the ORM is parsing the INSERT return value, correct?
> 
> Would something like this(borrowing from docs example) freak it out?:
> 
> CREATE OR REPLACE FUNCTION measurement_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE
>_ct int;
> BEGIN
>INSERT INTO measurement_y2016m03 VALUES (NEW.*);
>SELECT INTO  _ct count(NEW.*);
>RAISE NOTICE 'INSERT 0 %', _ct;
>RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
> 
> test=# insert into measurement values(1, '03/21/2016', 50, 87);
> NOTICE:  INSERT 0 1
> INSERT 0 0
> 
> 


we had a similar problem using ruby and ActiveRecord and solved it with

 RETURN NEW;

at the end of  the insert trigger 

which would result in inserting the row into the master table as well
that is then deleted right away in an AFTER INSERT trigger

CREATE OR REPLACE FUNCTION delete_master_trigger()
 DECLARE
 r master%rowtype;
 BEGIN  
 DELETE FROM ONLY master WHERE id = NEW.id returning * into r;
 RETURN r;  
 END;
$$
LANGUAGE plpgsql;  

Returning the inserted row here also solves the problem that ORM often need 
auto increment values back.


regards

Manuel Kniep

Re: [GENERAL] Partitioning and ORM tools

[Manuel Kniep]

> So the ORM is parsing the INSERT return value, correct?
> 
> Would something like this(borrowing from docs example) freak it out?:
> 
> CREATE OR REPLACE FUNCTION measurement_insert_trigger()
> RETURNS TRIGGER AS $$
> DECLARE
>_ct int;
> BEGIN
>INSERT INTO measurement_y2016m03 VALUES (NEW.*);
>SELECT INTO  _ct count(NEW.*);
>RAISE NOTICE 'INSERT 0 %', _ct;
>RETURN NULL;
> END;
> $$
> LANGUAGE plpgsql;
> 
> test=# insert into measurement values(1, '03/21/2016', 50, 87);
> NOTICE:  INSERT 0 1
> INSERT 0 0
> 
> 


we had a similar problem using ruby and ActiveRecord and solved it with

 RETURN NEW;

at the end of  the insert trigger 

which would result in inserting the row into the master table as well
that is then deleted right away in an AFTER INSERT trigger

CREATE OR REPLACE FUNCTION delete_master_trigger()
 DECLARE
 r master%rowtype;
 BEGIN  
 DELETE FROM ONLY master WHERE id = NEW.id returning * into r;
 RETURN r;  
 END;
$$
LANGUAGE plpgsql;  

Returning the inserted row here also solves the problem that ORM often need 
auto increment values back.


regards

Manuel Kniep

Re: [GENERAL] Partitioning and ORM tools

[Adrian Klaver]

On 03/22/2016 02:20 PM, CS DBA wrote:



On 03/22/2016 03:18 PM, Rob Sargent wrote:



On 03/22/2016 03:00 PM, Joshua D. Drake wrote:

On 03/22/2016 01:50 PM, CS DBA wrote:


Understood, was just wondering if there is a way to cause the child
table insert results to be returned to the ORM/Application instead of
the master/base table insert


Insert into the child table directly based on the partition rules.

JD



I would think the ORM (as yet undefined) would want to think in terms
of the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking
for non-zero inserts?




That was our first suggestion, they don;t want to make any app changes


So the ORM is parsing the INSERT return value, correct?

Would something like this(borrowing from docs example) freak it out?:

CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
_ct int;
BEGIN
INSERT INTO measurement_y2016m03 VALUES (NEW.*);
SELECT INTO  _ct count(NEW.*);
RAISE NOTICE 'INSERT 0 %', _ct;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

test=# insert into measurement values(1, '03/21/2016', 50, 87);
NOTICE:  INSERT 0 1
INSERT 0 0











--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Partitioning and ORM tools

[Melvin Davidson]

On Tue, Mar 22, 2016 at 5:20 PM, CS DBA  wrote:

>
>
> On 03/22/2016 03:18 PM, Rob Sargent wrote:
>
>>
>>
>> On 03/22/2016 03:00 PM, Joshua D. Drake wrote:
>>
>>> On 03/22/2016 01:50 PM, CS DBA wrote:
>>>
>>> Understood, was just wondering if there is a way to cause the child
 table insert results to be returned to the ORM/Application instead of
 the master/base table insert

>>>
>>> Insert into the child table directly based on the partition rules.
>>>
>>> JD
>>>
>>>
>>> I would think the ORM (as yet undefined) would want to think in terms of
>> the parent table and not know about the physical schema details.
>> Can  the client not be written to check only for errors vs checking for
>> non-zero inserts?
>>
>>
>>
>> That was our first suggestion, they don;t want to make any app changes
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

I highly suspect this is a problem with trigger function design, constraint
conflict or a bad insert statement, but since no details have been
provided, it cannot be resolved.
IOW, all they said was "It don't work", but they have not provided proof.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

On 03/22/2016 03:18 PM, Rob Sargent wrote:



On 03/22/2016 03:00 PM, Joshua D. Drake wrote:

On 03/22/2016 01:50 PM, CS DBA wrote:


Understood, was just wondering if there is a way to cause the child
table insert results to be returned to the ORM/Application instead of
the master/base table insert


Insert into the child table directly based on the partition rules.

JD


I would think the ORM (as yet undefined) would want to think in terms 
of the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking 
for non-zero inserts?





That was our first suggestion, they don;t want to make any app changes




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

Re: [GENERAL] Partitioning and ORM tools

[Rob Sargent]

On 03/22/2016 03:00 PM, Joshua D. Drake wrote:

On 03/22/2016 01:50 PM, CS DBA wrote:


Understood, was just wondering if there is a way to cause the child
table insert results to be returned to the ORM/Application instead of
the master/base table insert


Insert into the child table directly based on the partition rules.

JD


I would think the ORM (as yet undefined) would want to think in terms of 
the parent table and not know about the physical schema details.
Can  the client not be written to check only for errors vs checking for 
non-zero inserts?




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

Re: [GENERAL] Partitioning and ORM tools

[Joshua D. Drake]

On 03/22/2016 01:50 PM, CS DBA wrote:


Understood, was just wondering if there is a way to cause the child
table insert results to be returned to the ORM/Application instead of
the master/base table insert


Insert into the child table directly based on the partition rules.

JD


--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

On 03/22/2016 02:43 PM, Joshua D. Drake wrote:

On 03/22/2016 01:35 PM, CS DBA wrote:



On 03/22/2016 02:23 PM, Joshua D. Drake wrote:

On 03/22/2016 11:40 AM, CS DBA 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?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That
would do it.



Yes.. that's exactly the issue.


Because the parent table doesn't actually receive the insert, it is 
returning correctly (if obnoxiously considering the circumstances). It 
is known, expected behaviour.


Sincerely,

JD









Understood, was just wondering if there is a way to cause the child 
table insert results to be returned to the ORM/Application instead of 
the master/base table insert





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

Re: [GENERAL] Partitioning and ORM tools

[Joshua D. Drake]

On 03/22/2016 01:35 PM, CS DBA wrote:



On 03/22/2016 02:23 PM, Joshua D. Drake wrote:

On 03/22/2016 11:40 AM, CS DBA 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?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That
would do it.



Yes.. that's exactly the issue.


Because the parent table doesn't actually receive the insert, it is 
returning correctly (if obnoxiously considering the circumstances). It 
is known, expected behaviour.


Sincerely,

JD









--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

On 03/22/2016 02:23 PM, Joshua D. Drake wrote:

On 03/22/2016 11:40 AM, CS DBA 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?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That 
would do it.




Yes.. that's exactly the issue.




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

Re: [GENERAL] Partitioning and ORM tools

[Melvin Davidson]

On Tue, Mar 22, 2016 at 4:23 PM, Joshua D. Drake 
wrote:

> On 03/22/2016 11:40 AM, CS DBA 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?
>>
>> Thanks in advance
>>
>
> Are you inserting via trigger from an insert into a parent table? That
> would do it.
>
>
> --
> Command Prompt, Inc.  http://the.postgres.company/
>  +1-503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


>it's the fact that the number of rows inserted is returned as 0 - due to
>the fact that the rows are not in fact inserted in the parent table when
>configured as per the example in the docs.

Yes, you have declared a problem, but without providing actual detailed
information (as previously requested), there is no way we can duplicate
your problem to debug it and provide a solution.
To the best of my knowledge, telepathy and crystal ball options are not yet
available and are not planned for future versions of PostgreSQL/
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Partitioning and ORM tools

[Joshua D. Drake]

On 03/22/2016 11:40 AM, CS DBA 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?

Thanks in advance


Are you inserting via trigger from an insert into a parent table? That 
would do it.



--
Command Prompt, Inc.  http://the.postgres.company/
 +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.


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

Re: [GENERAL] Partitioning and ORM tools

[CS DBA]

On 03/22/2016 01:10 PM, Rob Sargent wrote:



On 03/22/2016 12:55 PM, Melvin Davidson wrote:
Your problem seems strange as it has never been previously reported 
for anyone else that has _successfully_ set up partioning.
Perhaps is you provide just a little bit more detail we might be able 
to help you.

Useful and needed information would be:
1. Version of PostgreSQL
2. Operating System
3. Table structure for partitioned table
4. Trigger function and trigger used for insert
5. The actual insert statement.

On Tue, Mar 22, 2016 at 2:40 PM, CS DBA > 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?

Thanks in advance




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




And which ORM are you using.

I take it the problem is that the ORM is saying "zero rows inserted" 
and that's um, er, upsetting the client ( which might decide to retry 
and then generates an error for non-unique key or some such noise)


rjs



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


I think they are using Ruby, turns out the application is checking this 
and throwing an error (and rolling back) when it detects no rows inserted

Re: [GENERAL] Partitioning and ORM tools

[Kris Deugau]

Melvin Davidson wrote:
> Your problem seems strange as it has never been previously reported for
> anyone else that has _successfully_ set up partioning.

At least as of when I asked a very similar question
(http://www.postgresql.org/message-id/flat/547f7e88.7080...@vianet.ca#547f7e88.7080...@vianet.ca),
the answer amounted to "deal with it", "don't use partitioning",
"copy-and-delete rather than redirecting the INSERT" (causes undesirable
VACUUM side effects) or "insert directly in the child table".

The problem is not data going astray, or not getting inserted at all,
it's the fact that the number of rows inserted is returned as 0 - due to
the fact that the rows are not in fact inserted in the parent table when
configured as per the example in the docs.

-kgd


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

Re: [GENERAL] Partitioning and ORM tools

[Rob Sargent]

On 03/22/2016 12:55 PM, Melvin Davidson wrote:
Your problem seems strange as it has never been previously reported 
for anyone else that has _successfully_ set up partioning.
Perhaps is you provide just a little bit more detail we might be able 
to help you.

Useful and needed information would be:
1. Version of PostgreSQL
2. Operating System
3. Table structure for partitioned table
4. Trigger function and trigger used for insert
5. The actual insert statement.

On Tue, Mar 22, 2016 at 2:40 PM, CS DBA > 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?

Thanks in advance




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org

)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




And which ORM are you using.

I take it the problem is that the ORM is saying "zero rows inserted" and 
that's um, er, upsetting the client ( which might decide to retry and 
then generates an error for non-unique key or some such noise)


rjs



--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Partitioning and ORM tools

[Melvin Davidson]

Your problem seems strange as it has never been previously reported for
anyone else that has _successfully_ set up partioning.
Perhaps is you provide just a little bit more detail we might be able to
help you.
Useful and needed information would be:
1. Version of PostgreSQL
2. Operating System
3. Table structure for partitioned table
4. Trigger function and trigger used for insert
5. The actual insert statement.

On Tue, Mar 22, 2016 at 2:40 PM, CS DBA  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?
>
> Thanks in advance
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.