PG: Estimate last modified or read time of a table
CREATE OR REPLACE FUNCTION table_file_access_info ( IN schemaname text, IN tablename text, OUT last_access timestamp with time zone, OUT last_change timestamp with time zone ) LANGUAGE plpgsql AS $func$ DECLARE tabledir text; filenode text; BEGIN SELECT regexp_replace( current_setting('data_directory') || '/' || pg_relation_filepath(c.oid), pg_relation_filenode(c.oid) || '$', ''), pg_relation_filenode(c.oid) INTO tabledir, filenode FROM pg_class c JOIN pg_namespace ns ON c.relnamespace = ns.oid AND c.relname = tablename AND ns.nspname = schemaname; RAISE NOTICE 'tabledir: % - filenode: %', tabledir, filenode; -- find latest access and modification times over all segments SELECT max((pg_stat_file(tabledir || filename)).access), max((pg_stat_file(tabledir || filename)).modification) INTO last_access, last_change FROM pg_ls_dir(tabledir) AS filename -- only use files matching <basefilename>[.segmentnumber] WHERE filename ~ ('^' || filenode || '([.]?[0-9]+)?$'); END; $func$; select * from table_file_access_info('public','a');
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/725820/viewspace-2934915/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Expires, Last-Modified, Etag快取機制AST快取
- 使用 ETag 和 Last-Modified 報頭減輕伺服器壓力(轉)AST伺服器
- Broker reports ORA-16858: last communication time from redo source could not beAST
- [20211013]19C 關於LAST SUCCESSFUL LOGIN TIME.txtAST
- 瀏覽器快取詳解:expires,cache-control,last-modified,etag詳細說明瀏覽器快取AST
- MySQL報錯Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.MySqlPluginError
- [20211115]12c以上版本Last Login Time 引發的故障.txtAST
- MySQL錯誤修復:Table xx is marked as crashed and last (automatic?) repair failedMySqlASTAI
- 【問題處理】ORA-00376 file xx cannot be read at this time
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- 與瀏覽器快取相關的expires,cache-control,last-modified,Etag等頭部資訊瀏覽器快取AST
- Could not execute Write_rows event on table time_task.tt_log
- Project Management - 2) Estimate Your WorkProject
- 7.121 LASTAST
- Last danceAST
- Loop Subdivision與Modified Butterfly SubdivisionOOP
- oracle ocp 19c考題,科目082考試題(21)-read-only tableOracle
- E:last-child與E:last-of-type區別AST
- 7.122 LAST_DAYAST
- Last digit of a huge numberASTGit
- direct path read/read temp等待事件事件
- PostgreSQL多值列的選擇性-Statistics,Cardinality,Selectivity,EstimateSQL
- Linux基礎命令—lastLinuxAST
- Linux基礎命令---lastLinuxAST
- CSS E:last-childCSSAST
- CSS E:last-of-typeCSSAST
- Traceback (most recent call last):AST
- [leetcode]length-of-last-wordLeetCodeAST
- 天國的《The Last Night》AST
- time time_t tm用法
- Read a story
- READ ME
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- pg升級(pg14-pg15)主從環境
- HTTP 200 OK和HTTP 304 Not modified的由來HTTP
- create table,show tables,describe table,DROP TABLE,ALTER TABLE ,怎麼使用?
- The last major melee archetype includes the sneakyAST
- 【Leetcode】1046. Last Stone WeightLeetCodeAST