Re: PostGres 9.5 Upsert

2016-02-01 Thread Ben Liyanage 
Hey--that's pretty slick.  I'm not sure when we're going to take a shot at
this implementation, but I'll keep that in mind.

-Ben

On Mon, Feb 1, 2016 at 7:12 AM,  wrote:

> It should be possible to use a "RETURNING" clause to get the new row even
> in the instance of a get_or_create.
>
> I occasionally use an UPDATE ... RETURNING query with Manager.raw to
> update a table and get modified instances in one shot.
>
> On Tuesday, January 12, 2016 at 12:54:03 PM UTC-6, bliy...@rentlytics.com
> wrote:
>>
>> After thinking about it a bit, I think the only function that would
>> really benefit from this would be the update_or_create.  If you're doing
>> get_or_create you still need a second query to get the actual row.
>>
>> On Friday, January 8, 2016 at 4:13:26 PM UTC-8, bliy...@rentlytics.com
>> wrote:
>>>
>>> Hey Guys,
>>>
>>> Postgres 9.5 has added the functionality for UPSERT aka update or
>>> insert.  Any interest in aligning UPSERT on the db layer with the
>>> get_or_create or update_or_create functionality in django?  Sounds like my
>>> company would be interested in doing the work if the PR will get the
>>> traction.
>>>
>>> -Ben
>>>
>> --
> You received this message because you are subscribed to a topic in the
> Google Groups "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/django-developers/swBPqFi-Tdk/unsubscribe
> .
> To unsubscribe from this group and all its topics, send an email to
> django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/2b8db682-1b8d-4549-8d8c-f99f3d6b8f90%40googlegroups.com
> 
> .
>
> For more options, visit https://groups.google.com/d/optout.
>



-- 
*Ben Liyanage *|* Software Engineer *|* Rentlytics, Inc.*
Phone: (410) 336-2464 | Email: bliyan...@rentlytics.com
1132 Howard Street, San Francisco CA 94107
Visit our Website  | Watch our Video


-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CADgLBUM%2B7DOCHLYiOvRh-XXRRWfWVOQUZ%3DrNprQ8f48D_7fm9w%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-02-01 Thread john . parton 
It should be possible to use a "RETURNING" clause to get the new row even 
in the instance of a get_or_create.

I occasionally use an UPDATE ... RETURNING query with Manager.raw to update 
a table and get modified instances in one shot.

On Tuesday, January 12, 2016 at 12:54:03 PM UTC-6, bliy...@rentlytics.com 
wrote:
>
> After thinking about it a bit, I think the only function that would really 
> benefit from this would be the update_or_create.  If you're doing 
> get_or_create you still need a second query to get the actual row.
>
> On Friday, January 8, 2016 at 4:13:26 PM UTC-8, bliy...@rentlytics.com 
> wrote:
>>
>> Hey Guys,
>>
>> Postgres 9.5 has added the functionality for UPSERT aka update or 
>> insert.  Any interest in aligning UPSERT on the db layer with the 
>> get_or_create or update_or_create functionality in django?  Sounds like my 
>> company would be interested in doing the work if the PR will get the 
>> traction.
>>
>> -Ben
>>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/2b8db682-1b8d-4549-8d8c-f99f3d6b8f90%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-12 Thread bliyanage 
After thinking about it a bit, I think the only function that would really 
benefit from this would be the update_or_create.  If you're doing 
get_or_create you still need a second query to get the actual row.

On Friday, January 8, 2016 at 4:13:26 PM UTC-8, bliy...@rentlytics.com 
wrote:
>
> Hey Guys,
>
> Postgres 9.5 has added the functionality for UPSERT aka update or insert.  
> Any interest in aligning UPSERT on the db layer with the get_or_create or 
> update_or_create functionality in django?  Sounds like my company would be 
> interested in doing the work if the PR will get the traction.
>
> -Ben
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/858c3f77-a20c-413e-9e76-78435b589658%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-10 Thread Anssi Kääriäinen 
On Sun, Jan 10, 2016 at 5:22 PM, Sean Brant  wrote:

> I've always considered this a bug waiting to happen. I have seen many errors
> with the get operation failing because it returned more then one value.
> Usually you don't notice the error until you hit production. I always
> suggest the lookup use fields that have unique indexes.
>
> Changing that would be backwards incompatible so maybe it's a docs issue.

If there are no valid use cases for using get_or_create() without an
unique index, then we could consider doing a backwards incompatible
change here. I believe there are cases where you want to do a
get_or_create() without  unique index (though I can't come up with one
right now).

We could always check if there is a backing unique index and use
get_or_create only in those cases.

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CALMtK1EbwD7hJLFOjsiUeMxofov9zwRbX_Zr%2BZArqdy%2Bj_fT_g%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-10 Thread Anssi Kääriäinen 
On Sun, Jan 10, 2016 at 11:10 AM, Florian Apolloner
 wrote:
> On Sunday, January 10, 2016 at 9:09:37 AM UTC+1, Anssi Kääriäinen wrote:
>>
>> The save() operation matches the semantics of upsert exactly - maybe we
>> could use upsert there?
>
> Not sure .save() would be a good candidate for that. In the best case,
> .save() should execute one INSERT or UPDATE query without extra junk for
> "error" handling. Ie if you set a pk and want to update then pass
> force_update into it. I know that we already try UPDATE followed by INSERT
> in save() but I am wondering how often that is actually used by people.

Yes, it is likely that save() wouldn't benefit much from using upsert.
Upsert is likely a bit slower than plain update, and we end up doing
an update in almost all cases anyways. It could be an useful option
for some use cases, but it likely isn't a good default.

 - Anssi

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CALMtK1H4d%3DqEm--XrZuf310VXhGP_zt3q6pjQ0i-tV4quTYuBw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-10 Thread Sean Brant 


> On Jan 10, 2016, at 2:09 AM, Anssi Kääriäinen  wrote:
> 
> If I recall correctly, MySQL doesn't offer a way to specify on which index 
> you want to do the conflict resolution. This leads to problems - the upsert 
> might affect the wrong row if there are multiple unique indexes on the table.
> 
> PostgreSQL's version of upsert has a problem, too. It doesn't offer a direct 
> way to know if the result of the upsert was an insert or update, but Django 
> needs that knowledge, at least for save(). Maybe there is a way (the oid 
> return value seems promising).
> 
> For get_or_create and update_or_create the problem is that the user is free 
> to offer any condition to be used for matching, but PostgreSQL limits the 
> upsert matching to columns in unique index. So, we can use upsert only for 
> unique index cases.

I've always considered this a bug waiting to happen. I have seen many errors 
with the get operation failing because it returned more then one value. Usually 
you don't notice the error until you hit production. I always suggest the 
lookup use fields that have unique indexes.

Changing that would be backwards incompatible so maybe it's a docs issue.

> 
> The save() operation matches the semantics of upsert exactly - maybe we could 
> use upsert there?
> 
>  - Anssi
> 
>> On Sunday, January 10, 2016, Cristiano Coelho  
>> wrote:
>> I agree! Also, does this already happen for the MySQL backend? MySQL has the 
>> insert on conflict update, that could work the same way.
>> However, if I'm not wrong, the docs states that the above methods have a 
>> race condition (obvious since right now it does two operations), but if the 
>> code would actually use native database operations, the race conditions 
>> might be gone for those cases, so that should probably be documented as well.
>> 
>> El viernes, 8 de enero de 2016, 21:13:26 (UTC-3), bliy...@rentlytics.com 
>> escribió:
>>> 
>>> Hey Guys,
>>> 
>>> Postgres 9.5 has added the functionality for UPSERT aka update or insert.  
>>> Any interest in aligning UPSERT on the db layer with the get_or_create or 
>>> update_or_create functionality in django?  Sounds like my company would be 
>>> interested in doing the work if the PR will get the traction.
>>> 
>>> -Ben
>> 
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "Django developers (Contributions to Django itself)" group.
>> To unsubscribe from this group and stop receiving emails from it, send an 
>> email to django-developers+unsubscr...@googlegroups.com.
>> To post to this group, send email to django-developers@googlegroups.com.
>> Visit this group at https://groups.google.com/group/django-developers.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msgid/django-developers/ae38ba8e-3e79-47fb-92b9-dd305176c58e%40googlegroups.com.
>> For more options, visit https://groups.google.com/d/optout.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an 
> email to django-developers+unsubscr...@googlegroups.com.
> To post to this group, send email to django-developers@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit 
> https://groups.google.com/d/msgid/django-developers/CALMtK1Gub-uL3AdgFSxQkdUNNe_Q47%3D7O%3DZ4qsW3Exwjhkd4ZA%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/A53126E5-B9D8-4A5A-A56E-B58B77500E94%40gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-10 Thread Florian Apolloner 


On Sunday, January 10, 2016 at 9:09:37 AM UTC+1, Anssi Kääriäinen wrote:
>
> The save() operation matches the semantics of upsert exactly - maybe we 
> could use upsert there?
>

Not sure *.save()* would be a good candidate for that. In the best case, 
*.save()* should execute one INSERT or UPDATE query without extra junk for 
"error" handling. Ie if you set a pk and want to update then pass 
*force_update* into it. I know that we already try UPDATE followed by 
INSERT in *save() *but I am wondering how often that is actually used by 
people.

Cheers,
Florian

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/7edcc120-14b3-4c1b-9afa-8e06fe2156ee%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-10 Thread Anssi Kääriäinen 
If I recall correctly, MySQL doesn't offer a way to specify on which index
you want to do the conflict resolution. This leads to problems - the upsert
might affect the wrong row if there are multiple unique indexes on the
table.

PostgreSQL's version of upsert has a problem, too. It doesn't offer a
direct way to know if the result of the upsert was an insert or update, but
Django needs that knowledge, at least for save(). Maybe there is a way (the
oid return value seems promising).

For get_or_create and update_or_create the problem is that the user is free
to offer any condition to be used for matching, but PostgreSQL limits the
upsert matching to columns in unique index. So, we can use upsert only for
unique index cases.

The save() operation matches the semantics of upsert exactly - maybe we
could use upsert there?

 - Anssi

On Sunday, January 10, 2016, Cristiano Coelho 
wrote:

> I agree! Also, does this already happen for the MySQL backend? MySQL has
> the insert on conflict update, that could work the same way.
> However, if I'm not wrong, the docs states that the above methods have a
> race condition (obvious since right now it does two operations), but if the
> code would actually use native database operations, the race conditions
> might be gone for those cases, so that should probably be documented as
> well.
>
> El viernes, 8 de enero de 2016, 21:13:26 (UTC-3), bliy...@rentlytics.com
>  escribió:
>>
>> Hey Guys,
>>
>> Postgres 9.5 has added the functionality for UPSERT aka update or
>> insert.  Any interest in aligning UPSERT on the db layer with the
>> get_or_create or update_or_create functionality in django?  Sounds like my
>> company would be interested in doing the work if the PR will get the
>> traction.
>>
>> -Ben
>>
> --
> You received this message because you are subscribed to the Google Groups
> "Django developers (Contributions to Django itself)" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-developers+unsubscr...@googlegroups.com
> 
> .
> To post to this group, send email to django-developers@googlegroups.com
> .
> Visit this group at https://groups.google.com/group/django-developers.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/django-developers/ae38ba8e-3e79-47fb-92b9-dd305176c58e%40googlegroups.com
> 
> .
> For more options, visit https://groups.google.com/d/optout.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/CALMtK1Gub-uL3AdgFSxQkdUNNe_Q47%3D7O%3DZ4qsW3Exwjhkd4ZA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-09 Thread Cristiano Coelho 
I agree! Also, does this already happen for the MySQL backend? MySQL has 
the insert on conflict update, that could work the same way.
However, if I'm not wrong, the docs states that the above methods have a 
race condition (obvious since right now it does two operations), but if the 
code would actually use native database operations, the race conditions 
might be gone for those cases, so that should probably be documented as 
well.

El viernes, 8 de enero de 2016, 21:13:26 (UTC-3), bliy...@rentlytics.com 
escribió:
>
> Hey Guys,
>
> Postgres 9.5 has added the functionality for UPSERT aka update or insert.  
> Any interest in aligning UPSERT on the db layer with the get_or_create or 
> update_or_create functionality in django?  Sounds like my company would be 
> interested in doing the work if the PR will get the traction.
>
> -Ben
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/ae38ba8e-3e79-47fb-92b9-dd305176c58e%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Re: PostGres 9.5 Upsert

2016-01-09 Thread Carl Meyer 
On 01/08/2016 05:13 PM, bliyan...@rentlytics.com wrote:
> Postgres 9.5 has added the functionality for UPSERT aka update or
> insert.  Any interest in aligning UPSERT on the db layer with the
> get_or_create or update_or_create functionality in django?  Sounds like
> my company would be interested in doing the work if the PR will get the
> traction.

That'd be great! I can't see any reason why a good PR for that wouldn't
be accepted. There's no reason to be using the ugly algorithms in
`get_or_create` or `update_or_create` when native UPSERT is available at
the DB level.

Carl

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/569193B4.70508%40oddbird.net.
For more options, visit https://groups.google.com/d/optout.


signature.asc
Description: OpenPGP digital signature

Re: PostGres 9.5 Upsert

2016-01-08 Thread Florian Apolloner 
Absolutely!

On Saturday, January 9, 2016 at 1:13:26 AM UTC+1, bliy...@rentlytics.com 
wrote:
>
> Hey Guys,
>
> Postgres 9.5 has added the functionality for UPSERT aka update or insert.  
> Any interest in aligning UPSERT on the db layer with the get_or_create or 
> update_or_create functionality in django?  Sounds like my company would be 
> interested in doing the work if the PR will get the traction.
>
> -Ben
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/70a47291-92de-4db2-9a1e-68ccd884d41a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

PostGres 9.5 Upsert

2016-01-08 Thread bliyanage 
Hey Guys,

Postgres 9.5 has added the functionality for UPSERT aka update or insert.  
Any interest in aligning UPSERT on the db layer with the get_or_create or 
update_or_create functionality in django?  Sounds like my company would be 
interested in doing the work if the PR will get the traction.

-Ben

-- 
You received this message because you are subscribed to the Google Groups 
"Django developers  (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-developers/eaf86518-a1b0-465c-9bf0-de724ae50d7f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.