Re: Partitioning vs ON CONFLICT

On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote
<Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> On 2017/02/17 14:50, Peter Geoghegan wrote:
>> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote
>> <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> wrote:
>>> Attached patch fixes that. Thom, your example query should not error out
>>> with the patch. As discussed here, DO UPDATE cannot be supported at the
>>> moment.
>>
>> Maybe you should just let infer_arbiter_indexes() fail, rather than
>> enforcing this directly. IIRC, that's what happens with
>> inheritance-based partitioning.
>
> That would be another way. The error message emitted by
> infer_arbiter_indexes() would be:
>
> ERROR: there is no unique or exclusion constraint matching the ON
> CONFLICT specification
>
> It does read better than what proposed patch makes
> transformOnConflictClause() emit:
>
> ERROR: ON CONFLICT ON UPDATE clause is not supported with partitioned tables
>
> I updated the patch. Now it's reduced to simply removing the check in
> transformInsertStmt() that prevented using *any* ON CONFLICT on
> partitioned tables at all.
>
>
> I don't however see why the error would *necessarily* occur in the case of
> inheritance partitioning. I mean if inserts into the root table in an
> inheritance hierarchy, it's still possible to ON CONFLICT DO UPDATE using
> the unique index only on that table for inference, although that's what a
> user would intend to do.
>
> create table foo (a int, b int, unique (a));
> create table foo_part (like foo including indexes) inherits (foo);
> insert into foo values (1, 2);
>
> -- the following still works
>
> insert into foo values (1, 2)
> on conflict (a) do update set b = excluded.b where excluded.a = 1;
> insert into foo values (1, 2)
> on conflict (a) do update set b = excluded.b where excluded.a = 1;
>
> As the documentation about inheritance partitioning notes, that may not be
> the behavior expected for partitioned tables:
>
> <para>
> <command>INSERT</command> statements with <literal>ON CONFLICT</>
> clauses are unlikely to work as expected, as the <literal>ON CONFLICT</>
> action is only taken in case of unique violations on the specified
> target relation, not its child relations.
> </para>
>
> With partitioned tables, since it's not possible to create index
> constraints on them, ON CONFLICT DO UPDATE simply won't work. So the
> patch also updates the note in the document about partitioned tables and
> ON CONFLICT.

This patch no longer applies.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company