รีเซ็ต sequence ของตาราง Postgres หลังการ DELETE
เมื่อลบ record ล่าสุดออกจากตาราง คุณสามารถรีเซ็ตตัวนับ sequence ของตารางนั้นได้ ถ้าความต่อเนื่องของฟิลด์ auto-increment เป็นเรื่องสำคัญสำหรับคุณ ลองดูการทำงานกับตารางต่อไปนี้ users:
select max(id) from users;
+-----+
| max |
+-----+
| 896 |
+-----+
1 rows in set (0.03 sec)
จากนั้นมีการ delete แถวล่าสุดบางส่วน:
delete from users where created_at > timestamp 'yesterday';
96 rows in set (0.15 sec)
ค่า auto-increment ถัดไปของ id จะเป็น 897 ในการ insert ครั้งถัดไป ใช้คำสั่งต่อไปนี้เพื่อลดค่า sequence ของตาราง users ลง 96
select setval('users_id_seq',(select max(id) from users));
+--------+
| setval |
+--------+
| 800 |
+--------+
1 rows in set (0.04 sec)
ทำไมต้องรีเซ็ต sequence?
การรีเซ็ต sequence ใน Postgres จำเป็นเมื่อคุณต้องการกำหนดค่าเริ่มต้นใหม่ให้กับ sequence เพื่อให้สร้างลำดับตัวเลขที่ต่อเนื่องชุดใหม่ได้ ซึ่งมักจำเป็นเมื่อคุณต้อง merge ข้อมูลจากหลายฐานข้อมูล เปลี่ยนโครงสร้างของฐานข้อมูล หรือกู้คืนจากข้อมูลที่เสียหาย การรีเซ็ต sequence ช่วยให้มั่นใจได้ว่าคอลัมน์ primary key ของคุณจะยังคงสร้างตัวเลขที่ไม่ซ้ำกันและต่อเนื่องกันต่อไป
ทำความเข้าใจ sequence ของ primary key
sequence ของ primary key คือ object ในฐานข้อมูลที่ใช้สร้างค่าตัวเลขที่ไม่ซ้ำกันสำหรับคอลัมน์ primary key ในตาราง มันเป็นส่วนประกอบสำคัญของระบบจัดการฐานข้อมูลเชิงสัมพันธ์ เพราะช่วยให้มั่นใจว่าแต่ละแถวในตารางมีตัวระบุที่ไม่ซ้ำกัน โดยปกติ sequence ของ primary key จะถูกสร้างขึ้นพร้อมกับตอนสร้างตาราง และถูกใช้สร้างค่าให้กับคอลัมน์ primary key การเข้าใจว่า sequence ของ primary key ทำงานอย่างไรเป็นสิ่งจำเป็นต่อการจัดการฐานข้อมูลอย่างมีประสิทธิภาพ
วิธีรีเซ็ต sequence
การรีเซ็ต sequence ใน Postgres ทำได้ด้วยคำสั่ง ALTER SEQUENCE คำสั่งนี้ให้คุณเปลี่ยนนิยามของตัวสร้าง sequence ได้ ทั้งค่าเริ่มต้น ค่า increment และค่าสูงสุด ในการรีเซ็ต sequence คุณต้องระบุชื่อของ sequence ค่าเริ่มต้นใหม่ และค่า increment ตัวอย่างเช่น ถ้าจะรีเซ็ต sequence ที่ชื่อ “my_sequence” ให้เริ่มจาก 100 โดยเพิ่มทีละ 1 ให้ใช้คำสั่งนี้: ALTER SEQUENCE my_sequence RESTART WITH 100 INCREMENT BY 1; อีกทางเลือกหนึ่งคือใช้ฟังก์ชัน SETVAL เพื่อรีเซ็ต sequence ไปยังค่าที่ต้องการ ฟังก์ชันนี้จะตั้งค่าปัจจุบันของ sequence เป็นค่าที่ระบุ และสามารถใช้รีเซ็ต sequence ไปยังค่าเริ่มต้นใหม่ได้ ตัวอย่างเช่น: SELECT setval('my_sequence', 100);
ข้อควรระวังและข้อควรพิจารณา
ก่อนรีเซ็ต sequence ควรระมัดระวังเพื่อหลีกเลี่ยงข้อมูลเสียหายหรือความไม่สอดคล้องกันของข้อมูล นี่คือสิ่งที่ควรคำนึงถึง:
- ตรวจสอบให้แน่ใจว่าคุณมี backup ของฐานข้อมูลก่อนแก้ไขอะไรก็ตามกับ sequence
- ตรวจสอบว่าคุณมีสิทธิ์เพียงพอในการแก้ไข sequence
- ระมัดระวังเมื่อรีเซ็ต sequence เพราะอาจกระทบต่อความถูกต้องสมบูรณ์ของข้อมูล
- พิจารณาผลกระทบของการรีเซ็ต sequence ต่อแอปพลิเคชันและผู้ใช้ของคุณ
วิธีอื่น ๆ
นอกจากการใช้คำสั่ง ALTER SEQUENCE หรือฟังก์ชัน SETVAL แล้ว ยังมีวิธีอื่นในการรีเซ็ต sequence ใน Postgres วิธีหนึ่งคือใช้ฟังก์ชัน pg_get_serial_sequence เพื่อดึงชื่อของ sequence ที่ผูกกับคอลัมน์นั้น แล้วใช้คำสั่ง ALTER SEQUENCE เพื่อรีเซ็ต sequence ตัวอย่างเช่น: SELECT pg_get_serial_sequence('my_table', 'id'); ALTER SEQUENCE my_table_id_seq RESTART WITH 100; อีกวิธีหนึ่งคือใช้ฟังก์ชัน SQL เพื่อย้าย sequence ของ primary key ไปยังค่าเริ่มต้นใหม่ ซึ่งมีประโยชน์อย่างยิ่งเมื่อคุณต้องการให้ค่าของ sequence ต่อเนื่องและไม่ซ้ำกันข้ามหลายตารางหรือหลายฐานข้อมูล