Damaged Postgresql Cluster

หลังจากวันก่อนที่อยู่ดีๆ FC Disk บน SAN ก็ถูก mark ว่า removed แบบไม่ทันตั้งตัวเล่นเอา postgresql database พังไปเลย ถ้าไล่ดู log ก็ฟ้องประมาณนี้

ERROR:  invalid page header in block 5869177 of relation base/17291/17420

ใน Log มี error แบบนี้เยอะมาก เลยไม่ได้เข้าไปวิเคราะห์ว่าเกิดปัญหาตรงส่วนไหนบ้าง sequence หรือ index หรือ table พังกันแน่

ก็เลยคิดว่าเอา data ออกมาไว้ในที่ปลอดภัยก่อนดีกว่า โชคไม่ร้ายเกินไปนักที่สามารถ dump ข้อมูลออกมาได้อย่างไม่มีปัญหา ก็เลยคิดว่าไหนๆ ก็เอาข้อมูลออกมาได้แล้วก็สร้าง postgresql cluster ใหม่แล้ว restore data ใหม่เลยดีกว่า

ปรากฎว่าตอน restore มันก็ฟ้องว่าไม่สามารถสร้าง unique index (primary key) ได้ 7 ตารางไล่เข้าไปดูก็พบว่าปัญหาเกิดจากช่วงที่มันบอกว่า HDD  ถูก removed นั่นแหล่ะ sequence ส่งตัวเลขซ้ำออกมาทำให้ตารางมี key ที่ซ้ำกัน

ถึงตอนนี้ก็ยังไม่หายมึน…

Left outer join and Where clause Part 2

มาดูประเด็นของเมื่อวาน

SELECT  vce.emp_id , vce.emp_name, da.date, da.stamp_inout, dl.leave_type, dl.leave_hours 
FROM   ((v_current_employee vce  INNER JOIN d_attendance da ON (vce.emp_id = da.emp_id))
       LEFT JOIN d_leave  dl  ON (vce.emp_id = dl.emp_id and da.date = dl.date))
WHERE  (da.date between '2014-01-25' and '2014-02-08')
       AND (dl.date between '2014-01-25' and '2014-02-08')

ประเด็นที่ Mr.X เขาอธิบายมีดังต่อไปนี้

– เขาใช้ d_attendance  left join กับ d_leave ตาม key ที่ระบุ เพื่อจะได้ดูว่าวันนั้นถ้าคนคนนั้นไม่มีข้อมูล stamp_inout (ค่าว่าง) ก็จะได้ดูว่าคนนั้นขอลางานไว้หรือเปล่า ที่ทำ left join นั้นเพราะว่า ตารางข้อมูลทางฝั่งซ้ายคือ d_attendance นั้นมีข้อมูลแน่ๆ ไม่ว่าคนนั้นจะบันทึกการเข้าทำงานหรือไม่ แต่ฝั่งขวาคือ d_leave จะมีข้อมูลก็ต่อเมื่อมีการบันทึกวันลาไว้ก่อนแล้ว

– เขาใช้เงื่อนไข da_date ระหว่าง ‘2014-01-25’ และ ‘2014-02-08’ เพื่อให้ได้เวลาการทำงานตามช่วงเวลาที่กำหนด

– เขาใช้เงื่อนไข dl_date ระหว่าง ‘2014-01-25’ และ ‘2014-02-08’ เพื่อให้ได้เวลาของข้อมูลการลางานตามช่วงเวลาที่กำหนด

อธิบายมาแบบนี้ก็น่าจะถูกแล้วใช่ไหมครับ 🙂  แล้วทำไมคำตอบมันไม่ถูกอ่ะ?

นี่คืออีกตัวอย่างนึงที่ programmer ไม่ค่อยคำนึงถึงค่า Null value ครับ (ย้อนกลับไปอ่านอันนี้น่าจะเห็นภาพ https://mairai.wordpress.com/2014/01/22/is-distinct-from-postgresql/)

หลังจากการทำ left join แล้ว ข้อมูล d_attendance มันก็จะจับคู่กับ ข้อมูล d_leave ตาม emp_id และ date ถ้าไม่มีข้อมูลใน d_leave ที่ match กันได้ ผลลัพธ์ทางฝั่ง d_leave มันก็จะเป็น null ทั้ง record

ดังนั้น หลังจาที่ join เสร็จแล้วก็มาตัดเงื่อไขออกตาม WHERE clause

เงื่อนไข   da.date between ‘2014-01-25’ and ‘2014-02-08’ มันก็ filter ได้แบบไม่มีปัญหา เพราะทุก record มันมีค่ามาให้เปรียบเทียบอยู่แล้ว

แต่  dl.date between ‘2014-01-25’ and ‘2014-02-08’  นี่สิ …. บาง record ก็เป็น null บาง record ก็มีค่า เห็นแล้วก็ร้องอ๋อแล้วใช่ไหมครับ มันก็ Filter เอาเฉพาะวันที่ที่มีค่าและมีค่าอยู่ระหว่าง  ‘2014-01-25’ and ‘2014-02-08’  ไง แล้วมันก็ไม่เอาอันที่มีค่า null ด้วย ซึ่งนั่นเป็นสาเหตุให้ข้อมูลผลลัพธ์มันผิดปกติ

แล้วที่ถูกต้องทำยังไง  ต้องเพิ่มเงื่อนไข   OR  (dl.date is null) เข้าไปเหรอ?

ใช่ครับ …  แต่ถ้าลองพิจารณาดีๆ นะ

( dl.date between ‘2014-01-25’ and ‘2014-02-08) OR (dl.date is null) ก็คือข้อมูลที่เป็นไปได้ทุกตัวของ d_leave อยู่แล้ว (ลองนึกภาพแผนภาพเวนน์ เงื่อนไขนี้กำลังจะบอกว่าเอาอันที่มีค่าทั้งหมดหรือไม่มีค่า ซึ่งก็คือมันเอาทุกตัวนั่นแหล่ะ)

ดังนั้น เงื่อนไข นี้จะใส่หรือไม่ใส่ค่ามันเท่ากัน  ดังนั้นถ้าตัดเงื่อนไขเหลือแค่เนี้ยก็น่าจะถูกแล้ว จริงมะ

SELECT  vce.emp_id , vce.emp_name, da.date, da.stamp_inout, dl.leave_type, dl.leave_hours 
FROM   ((v_current_employee vce  INNER JOIN d_attendance da ON (vce.emp_id = da.emp_id))
       LEFT JOIN d_leave  dl  ON (vce.emp_id = dl.emp_id and da.date = dl.date))
WHERE  (da.date between '2014-01-25' and '2014-02-08')

Left outer join and where clause

มีคนส่ง Query มาให้ดู ซึ่งประกอบไปด้วย 3 ตารางง่ายๆ คือ

v_current_employee  – มีข้อมูพนักงานปัจจุบัน
     v_current_employee (emp_id, emp_name)

d_attendance – มีข้อมูลตารางการทำงานและข้อมูลการบันทึกเวลาเข้า/ออกบริษัท จะมีครบทุกคนทุกวัน ดังนั้นก็จะมีทั้งหมด 365 records/คน/ปี  เสมอ
     d_attendance (emp_id, date, stamp_inout)

d_leave – มีข้อมูลการขอลางาน ซึ่งข้อมูลจะถูกเก็บในตารางนี้ก็ต่อเมื่อมีการบันทึกรายการลางานเท่านั้น
d_leave (emp_id, date, leave_type, leave_hours)

ความสัมพันธ์จะเป็นดังนี้ครับ

v_current_employee  (1) <———->(1..365) d_attendance

v_current_employee (1) <———->(0..n) d_leave

เรื่องมันมีอยู่ว่า …. เขาเขียน query ว่าต้องการข้อมูล attendance ของพนักงานทุกคนระหว่างวันที่ 25/Jan/2014 – 08/Feb/2014

SELECT  vce.emp_id , vce.emp_name, da.date, da.stamp_inout, dl.leave_type, dl.leave_hours 
FROM   ((v_current_employee vce  INNER JOIN d_attendance da ON (vce.emp_id = da.emp_id))
       LEFT JOIN d_leave  dl  ON (vce.emp_id = dl.emp_id and da.date = dl.date))
WHERE  (da.date between '2014-01-25' and '2014-02-08')
       AND (dl.date between '2014-01-25' and '2014-02-08')

เขาสงสัยว่าทำไมสั่งรัน Query นี้แล้วข้อมูลมันน้อยเกิน เพราะถ้ามีพนักงาน 10000 คน มันควรจะมีข้อมูลประมาณ  10000 * 15 = 150000  รายการที่มันจะต้องถูก return กลับมา แต่ข้อมูลที่เขาได้จาก Query อันนี้มันมีน้อยกว่านั้นมากจนผิดปกติเลย

เห็น Query แล้วพอจะวิเคราะห์ได้ไหมครับว่าเพราะอะไร?  🙂

“IS DISTINCT FROM” ท่าประหลาดใน postgresql

สมมติว่าผมมีข้อมูลหน้าตาอยู่ประมาณนี้นะครับ

pstatus คือสถานะการจ่ายเงินค่าเทอม ซึ่งเป็นไปได้ทั้ง

NULL
1 – ชำระเรียบร้อย
2 – รอตรวจสอบ
3 – ยังไม่ชำระ

คำถามคือ ถ้าผมต้องการข้อมูลคนที่ สถานะ ไม่ใช่ “ชำระเรียบร้อย” ทั้งหมด จะเขียน query ว่าเยี่ยงใด

ขนมใช่ไหมครับ

SELECT *  FROM student WHERE  pstatus <> 1;

…… ซึ่งคำตอบนี้ไม่ถูกครับ … เพราะ Query ด้านบนนี้มันให้ผลลัพธ์แบบนี้

เห็นไหมครับ … MR.C  , MR.E และ MR.G ไปไหนหว่า?

การทำงานกับข้อมูลที่มันสามารถเป็น Null ได้ก็ต้องปวดหัวแบบนี้แหล่ะ

ถ้าจะให้ถูกต้องเขียนว่า

SELECT *  FROM student WHERE  (pstatus <> 1) OR (pstatus is null);

ซึ่ง Postgresql เขาก็มีท่ายากมาให้เราใช้กันสำหรับกรณีแบบนี้ … สามารถเขียนได้เป็น

SELECT *  FROM student WHERE  (pstatus is distinct from 1);

 

สั่ง SQL ในการ create table กับ insert data อยู่ที่นี่นะครับ
http://pastebin.com/tb92BqiQ

รายละเอียดการใช้ “IS DISTINCT FROM” ดูได้จากที่นี่เลยขอรับ
http://www.postgresql.org/docs/current/interactive/functions-comparison.html

Create database with template

วันนี้ขอ blog เรื่องเบๆ ของ postgresql หน่อย เผื่อว่าบางคนมาแอบดูแล้วจะได้เอาไปใช้ 555

ถ้าเราต้องการสร้าง database ก้อนใหม่ที่เหมือน database ก้อนเดิม จะทำไปเพื่อทดสอบอะไรสักอย่างเราสามารถใช้ Database เก่า เป็น template เพื่อสร้าง database อันใหม่ได้ใน postgresql

เช่น ถ้าเรามี database ชื่อ example1 เราต้องการสร้าง example2 ที่เหมือนกับ example1 เลย เราสามารถสั่ง

CREATE DATABASE eample2 WITH TEMPLATE example1;

ง่ายๆ อย่างนี้แหล่ะ ไม่ต้อง dump / restore ให้ยุ่งยาก

วันนี้ขี้เกียจ ขอบล็อกสั้นๆ แค่นี้แหล่ะ 555

Serial Types (Auto Increment) ใน postgresql

ออกจากโหมดนักพนันหนุ่มกลับเข้าสู่โหมดโปรแกรมเมอร์อีกที

มีคนถามว่าอยากจะให้ column ชื่อ emp_id  มัน เป็น AUTO_INCREMENT เหมือนๆ กับใน MySql อย่างไร

ถ้าให้ตอบแบบง่ายๆ ก็คือให้ระบุ Type เป็น serial

CREATE TABLE employee_profile (
    emp_id SERIAL,
    emp_name varchar(50)
);

แค่นี้ก็เรียบร้อยครับ …

แต่ให้รู้สักนิดไว้เถอะว่า จริงๆ แล้ว ไอ้เจ้า SERIAL มันไม่ใช่ type จริงๆ ใน postgresql นะครับ
พอคุณสั่งไปอย่างนั้น postgresql มันจะไปสร้าง sequence ให้เราอัตโนมัติ แล้วไประบุ default value ของ emp_id ให้เป็น nextval ของ sequence

pg_dump table แบบมี constraint

วันนี้เปิดดู Stackoverflow มีคำถามนึงน่าสนใจ เกี่ยวกับ Postgresql

Postgresql : Function ที่ไม่ค่อยได้ใช้

ใน postgresql มี Function มาให้ใช้เยอะมากแต่แทบไม่ได้ใช้มันเลย แถมมันมีมาตั้งแต่เวอร์ชัน 7.x โน่น

วันนี้ผมยกตัวอย่าง function  cash_words ตามชื่อเลยครับ

อย่างเช่น

select cash_words(‘53,918,000,000’::money)

ก็ได้ผลลัพธ์ว่า

“Fifty three billion nine hundred and eighteen million  dollars and zero cents”

…. แหมเสียดายที่มันไม่มีเป็นภาษาไทยอ …. จะได้ช่วยไม่ให้คนอ่านผิดว่า ห้าหมื่นสามแสนเก้าร้อยสิบแปดล้านบาท

PostgreSQL: การเรียงลำดับตัวอักษร

(ขุดของเก่าอีกแล้ว) ทีมผมเคยยกปัญหานึงที่เจอในระบบ ERP ของผม … มันเรียงลำดับไม่ถูก  อ้อ … ก่อนจะเล่าต่อ รำลึกถึงความหลังกันหน่อยไหม

ตอนเราเรียนกันตอนเด็กๆ เราเรียนกันว่าเวลาที่มันจะเปรียบเทียบ String นั้น มันจะเปรียบเทียบทีละตัว

‘ABE’ กับ ‘ACD’

มันก็จะเปรียบเทียบตัวอักษรทีละตัว โดยเปรียบเทียบตัวแรกก่อน คือ A กับ A … อ๊ะเท่ากันก็ไปเปรียบเทียบตัวที่ 2
จากนั้นก็เปรียบเทียบ B กับ C อ้าเจอแล้วว่า B < C ดังนั้นเรก็จะสรุปว่า ‘ABE’ < ‘ACD’

Postgresql Quiz เกิดอะไรขึ้นกับ 3 คำสั่งนี้ครับ

Back to basic กันอีกวัน ….คำถามครับ ….

ทำไม 2 คำสั่งแรกให้ผลเป็น True แต่คำสั่งที่ 3 ให้ผลเป็น False ครับ
mis=# select ‘.’ < ‘0’;
?column?
———-
t
(1 row)

mis=# select ‘1.’ < ’10’;
?column?
———-
t
(1 row)

mis=# select ‘1.1’ < ‘101’;
?column?
———-
f
(1 row)