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快取
- Last-Modified Header in Asp.net Core MVCASTHeaderASP.NETMVC
- 使用 ETag 和 Last-Modified 報頭減輕伺服器壓力(轉)AST伺服器
- Error: cannot fetch last explain plan from PLAN_TABLEErrorASTAI
- 關於DBA_OBJECTS的LAST_DDL_TIMEObjectAST
- 12c新特性:Last Successful login timeAST
- 【Data Pump】理解expdp中的ESTIMATE和ESTIMATE_ONLY引數
- time wait bucket table overflowAI
- 瀏覽器快取詳解:expires,cache-control,last-modified,etag詳細說明瀏覽器快取AST
- [Clickhouse] Clickhouse 報SQLException : Read timed outSQLException
- 【TABLE】11g中只讀表(Read-only Table)技術特性
- SAP MM Table to read PO Header text and item textHeader
- MySQL報錯Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.MySqlPluginError
- java.net.SocketTimeoutException: Read timed out異常解決方法JavaException
- Project Management - 2) Estimate Your WorkProject
- nginx expires if_modifiedNginx
- 與瀏覽器快取相關的expires,cache-control,last-modified,Etag等頭部資訊瀏覽器快取AST
- 說說瀏覽器端快取的那點事兒-撲朔迷離的 etag 與 last-modified瀏覽器快取AST
- MySQL錯誤修復:Table xx is marked as crashed and last (automatic?) repair failedMySqlASTAI
- 聊聊pg jdbc的queryTimeout及next方法JDBC
- PG14中的idle_session_timeoutSession
- jdbc訪問KingbaseES資料庫SocketTimeoutException Read timed outJDBC資料庫Exception
- Feign,HTTP連線超時問題SocketTimeoutException: Read timed outHTTPException
- LeetCode-Read N Characters Given Read4 II - Call multiple timesLeetCode
- LLM-kimi-HTTP read timeout常見原因HTTP
- pip安裝selenium報錯:Read timed out
- plsql_index by_table of_count_next_prior_last,first語法小測SQLIndexAST
- Broker reports ORA-16858: last communication time from redo source could not beAST
- java.sql.SQLException:IO 錯誤:Socket read timed out !JavaSQLException
- Vetur報錯:The Vue Language Server server crashed 5 times in the last 3 minutes.VueServerAST
- jQuery last()jQueryAST
- Last WinnerAST
- Last danceAST
- 【PG】PG基礎操作
- Could not execute Write_rows event on table time_task.tt_log
- java.sql.SQLRecoverableException: IO Error: Socket read timed out 排查歷程JavaSQLExceptionError
- 【問題處理】ORA-00376 file xx cannot be read at this time
- Loop Subdivision與Modified Butterfly SubdivisionOOP