รีเซ็ต sequence ของตาราง Postgres หลังการ DELETE
หลังลบ record ล่าสุดออกจากตาราง ถ้าคุณอยากให้เลข auto-increment ต่อเนื่องกัน ก็รีเซ็ตตัวนับ sequence ของตารางนั้นได้ ลองดูตัวอย่างกับตาราง 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 นี้ทำงานอย่างไร ก็จะจัดการฐานข้อมูลได้ดีขึ้นมาก
วิธีรีเซ็ต 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 ต่อเนื่องและไม่ซ้ำกันในหลายตารางหรือหลายฐานข้อมูล