On Fri, Feb 17, 2017 at 1:47 AM, Amit Langote <langote_amit...@lab.ntt.co.jp> wrote: > On 2017/02/17 14:50, Peter Geoghegan wrote: >> On Thu, Feb 16, 2017 at 9:27 PM, Amit Langote >> <langote_amit...@lab.ntt.co.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
