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