Re: Partitioning and ORM tools - Mailing list pgsql-general

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