ON CONFLICT DO SELECT (take 3)
От
Viktor Holmberg
Тема
ON CONFLICT DO SELECT (take 3)
Дата
Msg-id
d631b406-13b7-433e-8c0b-c6040c4b4663@Spark
Список
Дерево обсуждения
ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) "v@viktorh.net" <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) "v@viktorh.net" <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) "v@viktorh.net" <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) jian he <jian.universality@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Marko Tiikkaja <marko@joh.to>
Re: ON CONFLICT DO SELECT (take 3) Andreas Karlsson <andreas@proxel.se>
Re: ON CONFLICT DO SELECT (take 3) Dean Rasheed <dean.a.rasheed@gmail.com>
Re: ON CONFLICT DO SELECT (take 3) Marko Tiikkaja <marko@joh.to>
Re: ON CONFLICT DO SELECT (take 3) Viktor Holmberg <v@viktorh.net>
Re: ON CONFLICT DO SELECT (take 3) Andreas Karlsson <andreas@proxel.se>
Hi!
This patch implements ON CONFLICT DO SELECT.
This feature would be very handy in bunch of cases, for example idempotent APIs.
I’ve worked around the lack of this by using three statements, like: SELECT -> INSERT if not found -> SELECT again for concurrency safety. (And having to do that dance is driving me nuts)
Apart from the convenience, it’ll also have a performance boost in cases with high latency.
As evidence of that fact that this is needed, and workarounds are complicated, see this stack overflow question: https://stackoverflow.com/questions/16123944/write-a-postgres-get-or-create-sql-query or this entire podcast episode (!) https://www.youtube.com/watch?v=59CainMBjtQ
This patch is 85% the work of Andreas Karlsson and the reviewers (Dean Rasheed, Joel Jacobson, Kirill Reshke) in this thread: https://www.postgresql.org/message-id/flat/2b5db2e6-8ece-44d0-9890-f256fdca9f7e%40proxel.se, which unfortunately seems to have stalled.
I’ve fixed up all the issues mentioned in that thread (at least I think so), plus some minor extra stuff:
This patch implements ON CONFLICT DO SELECT.
This feature would be very handy in bunch of cases, for example idempotent APIs.
I’ve worked around the lack of this by using three statements, like: SELECT -> INSERT if not found -> SELECT again for concurrency safety. (And having to do that dance is driving me nuts)
Apart from the convenience, it’ll also have a performance boost in cases with high latency.
As evidence of that fact that this is needed, and workarounds are complicated, see this stack overflow question: https://stackoverflow.com/questions/16123944/write-a-postgres-get-or-create-sql-query or this entire podcast episode (!) https://www.youtube.com/watch?v=59CainMBjtQ
This patch is 85% the work of Andreas Karlsson and the reviewers (Dean Rasheed, Joel Jacobson, Kirill Reshke) in this thread: https://www.postgresql.org/message-id/flat/2b5db2e6-8ece-44d0-9890-f256fdca9f7e%40proxel.se, which unfortunately seems to have stalled.
I’ve fixed up all the issues mentioned in that thread (at least I think so), plus some minor extra stuff:
- Made it work with partitioned tables
- Added isolation test
- Added tests for row-level security
- Added tests for partitioning
- Docs updated
- Comment misspellings fixed
- Renamed struct OnConflictSetState -> OnConflictActionState
I’ve kept the patches proposed there separate, in case any of the people involved back then would like to pick it up again.
Grateful in advance to anyone who can help reviewing!
Grateful in advance to anyone who can help reviewing!
/Viktor
В списке pgsql-hackers по дате отправления
От: Hayato Kuroda (Fujitsu)
Дата: