Обсуждение: Incorrect accounting (n_tup_ins) of non-inserted rows
Hello, Seems that accounting of insertions with `n_tup_ins` not correct in case of insertion errors cause by constraints checking (duplicate key value violates unique constraint): EXAMPLE: postgres=# create table t(name text unique); CREATE TABLE postgres=# SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='t'; n_tup_ins ----------- 0 postgres=# insert into t(name) values('a'); INSERT 0 1 postgres=# SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='t'; n_tup_ins ----------- 1 postgres=# insert into t(name) values('b'); INSERT 0 1 postgres=# SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='t'; n_tup_ins ----------- 2 postgres=# insert into t(name) values('a'); ERROR: duplicate key value violates unique constraint "t_name_key" DETAIL: Key (name)=(a) already exists. postgres=# SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='t'; n_tup_ins ----------- 3 name ------ a b CODE REFERENCE (src/backend/postmaster/pgstat.c) /* count attempted actions regardless of commit/abort */ tabstat->t_counts.t_tuples_inserted += trans->tuples_inserted; tabstat->t_counts.t_tuples_updated += trans->tuples_updated; tabstat->t_counts.t_tuples_deleted += trans->tuples_deleted; So, is this behavior normal or probably needs to be fixed? -- Ilya Matveychikov
Ilya Matveychikov <matvejchikov@gmail.com> writes: > Seems that accounting of insertions with `n_tup_ins` not correct in > case of insertion errors cause by constraints checking (duplicate key > value violates unique constraint): You already found one of the many code comments indicating that this is the intended behavior: > CODE REFERENCE (src/backend/postmaster/pgstat.c) > /* count attempted actions regardless of commit/abort */ > So, is this behavior normal or probably needs to be fixed? No, it's not a bug, and it's not going to be fixed. For many of the intended applications of those counts (e.g, determining whether autovacuum/autoanalyze is needed), this is the correct behavior and ignoring actions of failed transactions would be incorrect. The live/dead tuple counts do attempt to take transaction success into account; perhaps looking at those would be more helpful for your use-case? regards, tom lane
2016-03-18 16:25 GMT+03:00 Tom Lane <tgl@sss.pgh.pa.us>: > No, it's not a bug, and it's not going to be fixed. For many of the > intended applications of those counts (e.g, determining whether > autovacuum/autoanalyze is needed), this is the correct behavior > and ignoring actions of failed transactions would be incorrect. > Thank you for clarifying. > The live/dead tuple counts do attempt to take transaction success > into account; perhaps looking at those would be more helpful for > your use-case? I'll take a look on that counters, thanks.