איך כדאי למספר עמודה משנית בpostgresql?

nigun

יש לי טבלה שבו אני רוצה למספר את השורה על פי אחד העמודות
בא נאמר למשל שאני רוצה למספר את השורות של עמודת הuser.
אני יכול להוציא את המספר הכי גבוה לפני כל insert
SELECT MAX(sub_id) FROM my_tabel where user =$1
ואז להכניס אותו בבקשה חדשה
INSERT into my_tabel ( user ,sub_id ) VALUES ($1,$2)
כש$2 זה המשתנה שקיבלתי מהSELECT.

הבעיה היא שיכול להיות שני בקשות בו זמנית ואז יש לי שתי שורות עם אותו sub_id.

חשבתי להשתמש עם SEQUENCE
ואז הבקשה נראית ככה
"CREATE SEQUENCE IF NOT EXISTS sequence"+user+" OWNED BY my_tabel.sub_id ;"
ואת הinsert אני משנה ל:
INSERT into my_tabel ( user ,sub_id ) VALUES ($1,nextval('sequence"+user+"')

וככה לכאורה אין לי חשש מsub_id כפול
וללא צורך בנעילה של כל הטבלה בכל בקשה
כשברוב המקרים לא יהיו שתי בקשות לאותו user וזה באמת נעילה מיותרת.

זה אולי קצת מסוכן אם מישהו מכניס sql-injection לשדה של user, אבל נראה לי שאפשר להגן על זה עם בדיקה ידנית של המשתנה.

זה תקין לעבוד ככה? יש משהו יותר חכם?

OdedDvir

@nigun אמר באיך כדאי למספר עמודה משנית בpostgresql?:

יש לי טבלה שבו אני רוצה למספר את השורה על פי אחד העמודות
בא נאמר למשל שאני רוצה למספר את השורות של עמודת הuser.

אפשר בבקשה דוגמא איך אמורה להיראות התוצאה הסופית?

nigun

@odeddvir
של הטבלה?
איך היא נראית אחרי שכל הנתונים בפנים?

+--------+---------------+
| user   | sub_id   |
|--------+---------------|
| a     | 1             |
| a     | 2             |
| c     | 1             |
| b    | 1             |
| c     | 2             |
| c    | 3             |
+--------+---------------+

OdedDvir

@nigun כן. אם הבנתי נכון, אתה רוצה מספר רץ של רשומות עם מנייה נפרדת עבור כל user_id?
לענ"ד תשתמש בטרנזקציה כדי למנוע כפילויות.
עיין בשאלה דומה בסטאק: Increment counter based on column match (postgres)

nigun

פוסט זה נמחק!

dovid

https://stackoverflow.com/questions/6821871/postgresql-sequence-based-on-another-column

האפשרות של window function נראית לי טובה יותר מהsequence
אבל בלי שום קשר לאיך תעשה אתה אמור להיות יכול להשתמש בפרמטרים ולא לחשוב בכלל על "האם יש בעיה של הזרקה" - המחשבה הזאת היא פרצת אבטחה בפני עצמה.
אין לי מושג מה הקליינט שלך (מנחש שGO) הוא חייב לתמוך בפרמטרים ובטוקניזציה בטוחה.

nigun

@dovid אמר באיך כדאי למספר עמודה משנית בpostgresql?:

אתה אמור להיות יכול להשתמש בפרמטרים ולא לחשוב בכלל על "האם יש בעיה של הזרקה"

מה הכוונה פרמטרים?
$1 במקום שרשור של הסטרינג?

OdedDvir

@nigun כשהקליינט שלך מבצע קריאה לשאילתא בבסיס נתונים, הקריאה אמורה לתמוך בהעברת פרמטרים לשאילתא, ולבצע בדיקה של הפרמטרים כדי למנוע SQL-Injection, בצד הקליינט. אף פעם לא עושים Execute של משפט SQL שמורכב משרשור ישיר של קלט הלקוח.

nigun

@odeddvir
ניסיתי לעשות
"CREATE SEQUENCE IF NOT EXISTS sequence$1 OWNED BY my_tabel.sub_id ;"
ולא עבד
זה לא אמור להיות פרמטר

OdedDvir

@nigun אם השתמשת ב window_function כפי שהציע @dovid אתה בכלל לא צריך Sequence, וזה הרבה יותר פשוט. ראית שם את התשובה השניה? שיניתי אותה למקרה שלך:

INSERT INTO YourTable
    (user, sub_id, DATA)
    SELECT user, ROW_NUMBER() OVER(PARTITION BY user ORDER BY DATA), DATA
        FROM YourSource

yossiz

זו אותה שאלה שאני שאלתי בעבר?
https://tchumim.com/post/111656

OdedDvir

@yossiz אמר באיך כדאי למספר עמודה משנית בpostgresql?:

זו אותה שאלה שאני שאלתי בעבר?

כך זה נראה. אמנם פונקציית חלון לא הייתה קיימת בגרסאות 9.5 ומטה, כך שאיני יודע אם בזמן השאלה היה קיים הפתרון הנ"ל.

yossiz

@odeddvir אני עובר על הפוסטים שם, וא) אני מבין שהיום אני מבין פחות ב-DB-ים ממה שהבנתי אז... ב) אני רואה שיצא לי שגם אם עושים את הכל במשפט SQL אחת זה לא פותר את בעיית ההתנגשויות, כך שאני לא בטוח שפונקציית חלון יעזור פה.

nigun

@odeddvir
ראיתי שם את התשובה השניה והתחלתי לשחק עם השאילתות לראות איך זה עובד.
סתם נסחפתי לנושא של הפרמטרים.
את הרעיון של sequence קיבלתי מהמדריך הזה
שמסביר איך הסוג עמודה SERIAL עובד

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (
    id integer NOT NULL DEFAULT nextval('table_name_id_seq')
);

ALTER SEQUENCE table_name_id_seq
OWNED BY table_name.id;

חשבתי שהסיבה שבחרו בדרך הזאת זה כנראה בגלל יעילות
אז למה שאני לא יעשה ככה גם.

עכשיו אני מנסה להבין מה ROW_NUMBER() עושה מאחורי הקלעים ונראה האם זה יעיל או סתם נועל את כל הטבלה ללא צורך?
ובמה זה שונה משאילתת MAX?

dovid

אני פתאום חושב שwindow function לא עוזר כלום למקביליות,
בעוד הsequence עשוי עבור זה.

yossiz

@dovid אמר באיך כדאי למספר עמודה משנית בpostgresql?:

אני פתאום חושב

זה מה שטענתי למעלה...

מנצפך

ליתר בטחון תעשה כלל uniqe על 2 העמודות הללו ביחד.
ואז לא יהיה מצב של כפילות. מקסימום אתה מריץ כמה פעמים את השאילתה (לדעתי זה הפתרון הכי קל, להריץ בלולאה עם כמה ניסיונות- attempts).

(אגב, אני לא מצליח להיזכר בדיוק, אבל יש מנגנון דומה בעוד מקומות של התנגשות, אולי של התנגשות בגישה לרשומת ב DB או סתם מערכת קבצים, ואז כל תהליך מנסה להמתין זמן רנדומלי, וכו, לא זוכר את הסוף)

yossiz

@מנצפך אמר באיך כדאי למספר עמודה משנית בpostgresql?:

(אגב, אני לא מצליח להיזכר בדיוק, אבל יש מנגנון דומה בעוד מקומות של התנגשות, אולי של התנגשות בגישה לרשומת ב DB או סתם מערכת קבצים, ואז כל תהליך מנסה להמתין זמן רנדומלי, וכו, לא זוכר את הסוף)

אולי https://en.wikipedia.org/wiki/Exponential_backoff?