UPSERT on partition - Mailing list pgsql-hackers

Hi,

INSERT ON CONFLICT DO UPDATE doesn't seem to work on the current partitioning
mechanism. For example, in the following SQL commands, the last UPSERT command
would fail with an error. The error message is
   ERROR:  duplicate key value violates unique constraint "hoge_20150601_pkey"   DETAIL:  Key (col1)=(2015-06-01
10:30:00)already exists.   CONTEXT:  SQL statement "INSERT INTO hoge_20150601 VALUES (($1).*)"       PL/pgSQL function
hoge_insert_trigger()line 6 at EXECUTE statement
 

------------------------------------------------------------------------------------
CREATE TABLE hoge (col1 TIMESTAMP PRIMARY KEY, col2 INT);

CREATE OR REPLACE FUNCTION hoge_insert_trigger () RETURNS trigger AS
$$               DECLARE                               part TEXT;               BEGIN
part:= 'hoge_' || to_char(new.col1,
 
'YYYYMMDD');                               EXECUTE 'INSERT INTO ' || part || '
VALUES (($1).*)' USING new;                               RETURN NULL;               END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_hoge_trigger            BEFORE INSERT ON hoge            FOR EACH ROW EXECUTE PROCEDURE
hoge_insert_trigger();

CREATE TABLE hoge_20150601 (   LIKE hoge INCLUDING INDEXES                               INCLUDING DEFAULTS
                 INCLUDING CONSTRAINTS,   CHECK ('2015-06-01 00:00:00' <= col1 AND col1 < '2015-06-02 00:00:00')
 
)
INHERITS (hoge);

CREATE TABLE hoge_20150602 (   LIKE hoge INCLUDING INDEXES                               INCLUDING DEFAULTS
                 INCLUDING CONSTRAINTS,   CHECK ('2015-06-02 00:00:00' <= col1 AND col1 < '2015-06-03 00:00:00')
 
)
INHERITS (hoge);

INSERT INTO hoge VALUES ('2015-06-01 10:30:00', 1234)   ON CONFLICT (col1) DO UPDATE SET col2 = EXCLUDED.col2;

INSERT INTO hoge VALUES ('2015-06-01 10:30:00', 1234)   ON CONFLICT (col1) DO UPDATE SET col2 = EXCLUDED.col2;
------------------------------------------------------------------------------------

How should we treat this problem for 9.5? If we want to fix this problem
completely, probably we would need to make constraint_exclusion work with
even UPSERT. Which sounds difficult to do at least for 9.5. Any other idea?
Or we should just treat it as a limitation of UPSERT and add that document?

Thought?

Regards,

-- 
Fujii Masao