Thread: BUG #8681: column 'n_tup_del' of pg_stat_user_tables doesn't change in case of truncate

The following bug has been logged on the website:

Bug reference:      8681
Logged by:          Anit chakkarwar
Email address:      anitchakkarwar@gmail.com
PostgreSQL version: 9.3.1
Operating system:   Linux
Description:

I came across one issue while truncating table in Postgres9.3, please find
below steps:


First Scenario:


1. Create table and insert some rows.
2. Now delete two rows.
3. If I delete rows 'n_tup_del' column in pg_stat_user_tables is set 2.
4. Do vacuum now i.e vacuum table <tablename>
5. 'n_tup_del' column in pg_stat_user_tables is still showing  2 records.


Second Scenario:


1. Create table and insert some rows.
2. Now truncate table.
3. n_tup_del column in pg_stat_user_tables is 0.


If I do truncate then n_tup_del value is 0 but all the rows are deleted from
table.


Is this something expected behaviour ?

anitchakkarwar@gmail.com writes:
> If I do truncate then n_tup_del value is 0 but all the rows are deleted from
> table.
> Is this something expected behaviour ?

Yes.  We're certainly not going to make TRUNCATE update that count, since
that would require making a pass over the table to count the to-be-deleted
tuples, and the whole point of TRUNCATE is to not make a pass over the
table.

            regards, tom lane

Hi Tom,

Thanks, I have one query please refer below scenario.

1. Create table.
2. Insert 100 rows.
3. Delete 2 rows.
4. Truncate table.
5. Do vacuum
6. Insert 20 rows again. (Table count is 20 now)

Now n_tup_del = 2, n_tup_ins=120, n_live_tup=20 in pg_stat_user_tables, but
how can I figure out what has happened to 98 rows?


Regards,
Anit Chakkarwar


On Fri, Dec 13, 2013 at 11:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> anitchakkarwar@gmail.com writes:
> > If I do truncate then n_tup_del value is 0 but all the rows are deleted
> from
> > table.
> > Is this something expected behaviour ?
>
> Yes.  We're certainly not going to make TRUNCATE update that count, since
> that would require making a pass over the table to count the to-be-deleted
> tuples, and the whole point of TRUNCATE is to not make a pass over the
> table.
>
>                         regards, tom lane
>

Anit,

* Anit Chakkarwar (anitchakkarwar@gmail.com) wrote:
> Now n_tup_del = 2, n_tup_ins=120, n_live_tup=20 in pg_stat_user_tables, but
> how can I figure out what has happened to 98 rows?

This is information for statistics- if you need an accurate count,
you'll need to use a trigger and track that information explicitly..
There can be other ways that n_tup_del can end up being inexact.

    Thanks,

        Stephen

It's still an interesting question.

Why doesn't truncate reset table statistic to 0? The table state prior to a
truncate shouldn't influence vacuum or necessity after the truncate.

Not that vacuuming a recently truncated table would be expensive, but
Analyze timing is a concern.

regards,

Rod



On Mon, Dec 16, 2013 at 10:50 AM, Stephen Frost <sfrost@snowman.net> wrote:

> Anit,
>
> * Anit Chakkarwar (anitchakkarwar@gmail.com) wrote:
> > Now n_tup_del = 2, n_tup_ins=120, n_live_tup=20 in pg_stat_user_tables,
> but
> > how can I figure out what has happened to 98 rows?
>
> This is information for statistics- if you need an accurate count,
> you'll need to use a trigger and track that information explicitly..
> There can be other ways that n_tup_del can end up being inexact.
>
>         Thanks,
>
>                 Stephen
>

Rod Taylor <rod.taylor@gmail.com> writes:
> Why doesn't truncate reset table statistic to 0?

Well, it does reset the counts that correspond to current table size.
This one doesn't though.  n_tup_del is a count of historical operations.

            regards, tom lane