Partitioning and ORM tools

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 <cs_dba@consistentstate.com> 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.

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 <cs_dba@consistentstate.com
<mailto:cs_dba@consistentstate.com>> 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
<mailto: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 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
(/messages/by-id/547F7E88.7080901@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

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 <cs_dba@consistentstate.com
<mailto:cs_dba@consistentstate.com>> 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

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

On Tue, Mar 22, 2016 at 4:23 PM, Joshua D. Drake <jd@commandprompt.com>
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.

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

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

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

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

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

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

On Tue, Mar 22, 2016 at 5:20 PM, CS DBA <cs_dba@consistentstate.com> 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.

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.klaver@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

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

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

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

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@consistentstate.com> 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

On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers <chris.travers@gmail.com>
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@consistentstate.com>
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