Re: Partitioning and ORM tools

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
>> <chris(dot)travers(at)gmail(dot)com <mailto:chris(dot)travers(at)gmail(dot)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(at)consistentstate(dot)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(at)postgresql(dot)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)
bfehrle(at)comscore(dot)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