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

On 03/16/2016 11:59 PM, matvejchikov@gmail.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      14027
> Logged by:          Ilya Matveychikov
> Email address:      matvejchikov@gmail.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.
--
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support