Обсуждение: BUG #14027: n_tup_ins increments regardless of insertion success

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

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

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

> 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=3D# create table t (name text unique);
> > postgres=3D# select n_tup_ins from pg_stat_user_tables where relname=3D=
't';
> >  n_tup_ins
> > -----------
> >          0
> > postgres=3D# insert into t (name) values ('a');
> > INSERT 0 1
> > postgres=3D# select n_tup_ins from pg_stat_user_tables where relname=3D=
't';
> >  n_tup_ins
> > -----------
> >          1
> > postgres=3D# insert into t (name) values ('b');
> > INSERT 0 1
> > postgres=3D# select n_tup_ins from pg_stat_user_tables where relname=3D=
't';
> >  n_tup_ins
> > -----------
> >          2
> > postgres=3D# insert into t (name) values ('a');
> > =D0=9E=D0=A8=D0=98=D0=91=D0=9A=D0=90:  =D0=BF=D0=BE=D0=B2=D1=82=D0=BE=
=D1=80=D1=8F=D1=8E=D1=89=D0=B5=D0=B5=D1=81=D1=8F =D0=B7=D0=BD=D0=B0=D1=87=
=D0=B5=D0=BD=D0=B8=D0=B5 =D0=BA=D0=BB=D1=8E=D1=87=D0=B0 =D0=BD=D0=B0=D1=80=
=D1=83=D1=88=D0=B0=D0=B5=D1=82 =D0=BE=D0=B3=D1=80=D0=B0=D0=BD=D0=B8=D1=87=
=D0=B5=D0=BD=D0=B8=D0=B5 =D1=83=D0=BD=D0=B8=D0=BA=D0=B0=D0=BB=D1=8C=D0=BD=
=D0=BE=D1=81=D1=82=D0=B8
> > "t_name_key"
> > =D0=9F=D0=9E=D0=94=D0=A0=D0=9E=D0=91=D0=9D=D0=9E=D0=A1=D0=A2=D0=98:  =
=D0=9A=D0=BB=D1=8E=D1=87 "(name)=3D(a)" =D1=83=D0=B6=D0=B5 =D1=81=D1=83=D1=
=89=D0=B5=D1=81=D1=82=D0=B2=D1=83=D0=B5=D1=82.
> > postgres=3D# select n_tup_ins from pg_stat_user_tables where relname=3D=
'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.
>

=E2=80=8BTo help explain why - consider that PostgreSQL is basically optimi=
stic 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"=E2=80=8B

=E2=80=8Bjust 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.

2016-03-19 4:08 GMT+03:00 David G. Johnston <david.g.johnston@gmail.com>:

> 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, thanks for the explanation. Now that's clear to me.