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