Re: BUG #14027: n_tup_ins increments regardless of insertion success

On Fri, Mar 18, 2016 at 3:08 PM, Vik Fearing <vik(at)2ndquadrant(dot)fr> wrote:

> On 03/16/2016 11:59 PM, matvejchikov(at)gmail(dot)com wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 14027
> > Logged by: Ilya Matveychikov
> > Email address: matvejchikov(at)gmail(dot)com
> > PostgreSQL version: 9.5.1
> > Operating system: Linux
> > Description:
> >
> > postgres=# create table t (name text unique);
> > 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');
> > ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности
> > "t_name_key"
> > ПОДРОБНОСТИ: Ключ "(name)=(a)" уже существует.
> > postgres=# select n_tup_ins from pg_stat_user_tables where relname='t';
> > n_tup_ins
> > -----------
> > 3
>
> This is not a bug, there are three rows inserted into the table, you
> just can't see the third.
>
> Try this:
>
> create extension pageinspect;
> select * from heap_page_items(get_raw_page('t', 0));
>
> and you will see the three rows.
>

​To help explain why - consider that PostgreSQL is basically optimistic in
its behavior. It writes out data expecting that the various constraints
are going to succeed and that the transaction as a whole will be
committed. If at any point the written data is deemed to be invalid it is
marked as have been (for practical purposes) "deleted"​

​just as if you had done an SQL DELETE on a valid record. Its just that in
this instance the data in question was never visible outside of its
transaction. It is, however, physically present and thus eligible for
vacuum and contributes to the statistics of the database.

David J.