Thread: to find table stats for last update time.

Hi All,

Is there any way to find out the most recent update time for a table in postgres like the mysql equivalent :

 
mysql> SHOW TABLE STATUS LIKE 'XXXX';
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name               | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| XXXX | MyISAM |       9 | Compressed |    0 |              0 |          60 |      4294967295 |         1024 |         0 |           NULL | 2010-01-01 23:50:01 | 2010-01-05 00:59:17 | 2010-01-05 00:59:17 | latin1_swedish_ci |        0 |                |         |
+--------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)


In Mysql the Update_time field tells the most recent time at which the table XXXX was updated.

I need to figure out the way to check whether the table is active insertions/updates in last 15 mins?

Any way out??


Thanks and Regards,

Shabala Deshpande.

In response to Shabala Deshpande :
> Hi All,
>
> Is there any way to find out the most recent update time for a table in
> postgres like the mysql equivalent :


No.


>
> I need to figure out the way to check whether the table is active insertions/
> updates in last 15 mins?
>
> Any way out??

Write a TRIGGER for INSERT and UPDATE and store the timestamp in an extra table.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Thanks Andreas.

I was trying to figure out if i could use any system tables info... but seems i have to take the tiggers way out...

Thanks and Regards,

Shabala Deshpande.

On Tue, Jan 5, 2010 at 9:37 AM, Shabala Deshpande <de.shabala@gmail.com> wrote:
> I was trying to figure out if i could use any system tables info... but
> seems i have to take the tiggers way out...

Keep in mind that this creates a point of contention and will
serialize all updates against the table. If this is a busy table with
many updates per second it could reduce performance.

--
greg

I got this help and it worked for me.

1. Find the file node name from the pg_class for your table
select relfilenode from pg_class where relname = 'tablename'

2. Locate the relfilenode file in your system. The last updated system time
of that file node is your last update time of your table.

I hope it works for you.

SIVA JAN KAN


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/to-find-table-stats-for-last-update-time-tp2142054p4575271.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.