Обсуждение: Partitioning and ORM tools

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

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.

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.7080901@vianet.ca#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

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

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.

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.

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.

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

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.

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?

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

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

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

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

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

Brian Fehrle    Database Administrator II  |  comScore, Inc.  (NASDAQ:SCOR)

bfehrle@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

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

All;

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