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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs