ป้ายกำกับ: Sql Query

ในฐานะนักพัฒนาระบบและผู้ดูแลฐานข้อมูล (DBA) การทราบว่า Object ใดใน Database เช่น Table, View, หรือ Stored Procedure ถูกสร้างหรือแก้ไขโครงสร้างล่าสุดเมื่อไหร่ เป็นขั้นตอนสำคัญอย่างยิ่งในการทำ Database Administration, การตรวจสอบการเปลี่ยนแปลง (Change Tracking), หรือการ Debugging เพื่อจำกัดขอบเขตของโค้ดที่ต้องตรวจสอบ

อย่างไรก็ตาม การตรวจสอบวันที่แก้ไข Object ในระบบฐานข้อมูลนั้นมีความซับซ้อน เพราะระบบต่าง ๆ มีวิธีการจัดเก็บ Metadata ที่แตกต่างกัน บทความนี้จะแนะนำวิธีการตรวจสอบการเปลี่ยนแปลง Object ในระบบ PostgreSQL ซึ่งเป็นระบบที่เน้นความยืดหยุ่นและมาตรฐานสูง

หลักการตรวจสอบ Metadata ของ Object ใน PostgreSQL

ใน PostgreSQL นั้น การติดตามวันที่เปลี่ยนแปลงโครงสร้าง (Schema Modification Date) ของ Object ทุกประเภทอย่างสมบูรณ์แบบนั้นทำได้ยากกว่าระบบที่ถูกออกแบบมาเพื่อการ Audit โดยเฉพาะ (เช่น SQL Server ที่มี System Views เฉพาะเจาะจง) อย่างไรก็ตาม เราสามารถใช้ System Catalog Views เพื่อดึงข้อมูลที่ใกล้เคียงที่สุดได้ โดยเฉพาะการตรวจสอบ Table และ View

สำหรับ Table และ View เราสามารถใช้ information_schema ร่วมกับ pg_catalog เพื่อดึงข้อมูลที่เกี่ยวข้องกับเวลาสร้างและเวลาแก้ไขได้:

SQL

SELECT 1, table_name, 'Table' AS object_type, last_altered_date FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'user_data';

คำอธิบายโค้ด:

• information_schema.tables: เป็นมุมมองมาตรฐานที่ให้ข้อมูลเกี่ยวกับตารางทั้งหมดใน Database
• last_altered_date: (สมมติว่ามีการสร้างคอลัมน์หรือใช้ Extension ที่บันทึกข้อมูลนี้) ในทางปฏิบัติจริง การตรวจสอบ Schema Change ต้องอาศัยการ Audit Log หรือการสร้าง Trigger

การตรวจสอบ Table และ View ด้วย Catalog Views

สำหรับการตรวจสอบ Table ที่ถูกสร้างขึ้นและมีการเปลี่ยนแปลงโครงสร้าง (DDL) ใน PostgreSQL นั้น ข้อมูลที่แม่นยำที่สุดมักจะมาจาก pg_class หรือการใช้ระบบ Audit Logging ภายนอก แต่หากต้องการดูข้อมูลพื้นฐานของ Object เราสามารถใช้ Query นี้เพื่อดึงข้อมูลของตารางและ View:

SQL

SELECT 1, c.relname, CASE WHEN c.relkind = 'r' THEN 'Table' WHEN c.relkind = 'v' THEN 'View' ELSE 'Other' END AS object_type, pg_last_vacuum(c.oid) AS last_vacuum_date FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'public' AND c.relkind IN ('r', 'v') ORDER BY last_vacuum_date DESC;

คำอธิบายคอลัมน์:

• relname: ชื่อของ Object (Table หรือ View)
• object_type: ประเภทของ Object
• last_vacuum_date: วันที่ล่าสุดที่ Object ถูก Vacuum (ซึ่งบ่งชี้ถึงกิจกรรมล่าสุดที่เกี่ยวข้องกับการจัดการข้อมูล)

ข้อจำกัดและแนวทางปฏิบัติที่ดีที่สุด (Best Practice)

ในฐานะ Senior Architect สิ่งสำคัญที่ต้องทราบคือ PostgreSQL ไม่ได้มีคอลัมน์ modify_date ที่ทำงานได้ง่ายเหมือนใน SQL Server สำหรับการติดตามการเปลี่ยนแปลงโครงสร้าง (Schema Change) ของ Object อย่างสมบูรณ์แบบ

แนวทางแก้ไขที่แนะนำ:

1. ใช้ Audit Triggers: สำหรับ Object ที่สำคัญมาก ควรสร้าง Trigger บน Table เพื่อบันทึกวันที่และผู้ใช้งานที่ทำการ UPDATE หรือ DELETE ข้อมูล
2. ใช้ Version Control: การจัดการ Schema Change ควรทำผ่านระบบ Version Control (เช่น Git) และใช้ Migration Tools (เช่น Flyway หรือ Liquibase) เพื่อบันทึกประวัติการเปลี่ยนแปลงอย่างเป็นทางการ

บทสรุป

การตรวจสอบวันที่แก้ไข Object ใน PostgreSQL ต้องอาศัยความเข้าใจใน System Catalog และการยอมรับข้อจำกัดของระบบ การใช้ information_schema และ pg_catalog เป็นจุดเริ่มต้นที่ดี แต่สำหรับความแม่นยำระดับ Production Grade การผสานรวมระบบ Audit Logging หรือการใช้ Migration Tools คือแนวทางที่นักพัฒนาระดับสูงควรนำไปใช้เพื่อรับประกันความสมบูรณ์ของข้อมูลและประวัติการเปลี่ยนแปลงของฐานข้อมูล