Postgresql po co sa CONSTRAINT?

0

Witam,

CREATE TABLE equipment (
   id serial PRIMARY KEY,
   name VARCHAR (50) NOT NULL,
   equip_id VARCHAR (16) NOT NULL,
   removed TIMESTAMP NULL DEFAULT NULL
);

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id 
ON equipment (equip_id);

Moze mi ktos dokladnie wytlumaczyc co robi kod ponize? Po co mi Alter talble, jak index juz dziala bez tego? Druga sprawa co to jest te CONCURRENTLY?

ALTER TABLE equipment 
ADD CONSTRAINT unique_equip_id 
UNIQUE USING INDEX equipment_equip_id;

I insert:

INSERT INTO equipment(name,equip_id) VALUES('Test1', 1) ON CONFLICT (name,equip_id) WHERE removed IS NULL DO
UPDATE SET name = EXCLUDED.name RETURNING id;
0

Po to żeby index nazywał się equipment_equip_id a nie był losowy wygenerowany przez bazę.
Parametry opisuje dokumentacja:
https://www.postgresql.org/docs/11/sql-createindex.html
Co jest dziwnego w tym insercie? Działa jak merge/upsert.

0

No cze
CONCURRENTLY
When this option is used, PostgreSQL will build the index without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it's done. There are several caveats to be aware of when using this option — see Building Indexes Concurrently.

0

A mi sie wydaje, ze nie ma sensu tworzenia indexu w momencie, gdy chcemy trzymac unikatowe wartosci. Wystarczy utworzy CONSTRAINT, czyli:

ALTER TABLE equipment
ADD CONSTRAINT equipment_unique_constraint UNIQUE(name, equip_id);

Noo, po co mi index? Ja nie chce tworzyc kopii columny, czy column. Ja chce tylko utworzyc cos jak check, warunek, ktory sprawdzi mi czy istnieje juz taka grupa wartosci w tabeli czy nie. Ja tak to rozumiem.

edit
Albo lepiej. Mozemy dodać index i constrain. Więc constrain sprawdzi czy wartosc istnieje na indexie, a nie na oryginalnej kolumnie.

edit
Druga sprawa jest taka, ze przy UNIQUE CONSTRAIN automatycznie tworzy INDEX. Jednakze, moj zapis z pierwszego postu ma sens jedynie jak chcemy utworzyc partial index, czyli np:

CREATE UNIQUE INDEX CONCURRENTLY equipment_equip_id 
ON equipment (equip_id) 
WHERE removed IS NULL;

ALTER TABLE equipment 
ADD CONSTRAINT unique_equip_id 
UNIQUE USING INDEX equipment_equip_id;

Czyli tworzymy dostosowany index do constrain, bo nie chcemy uzywac domyslnego indexu w naszym constain, ale chcemy uzyc naszego wyjatkowego indexu :)

Fajnie by było, jak by ktos potwierdzil, czy jestem chociaz blisko prawdy :) ?

0

A nie jednak nie mozna uzyc CONSTRAINT z partial INDEX. Taki blad wyskakuje:

[42809] ERROR: "eg_guestlist_ticket_classes_name_event_instance_id" is a partial index Detail: Cannot create a primary key or unique constraint using such an index. Position: 45

A jak utworze sam partial Index to wywala mi blad:

[42P10] ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Sprobowalem jeszcze z EXCLUDE CONSTRAINT. Tylko, ze tutaj problem jest taki, ze nie mozna uzyc ON CONFLICT z EXCLUDE with returning Id nie zadziala.

Takeze nie mam pojecia po co jest taki zapis jaki podalem w pierwszym poscie. I jak uzyc ON CONFLICT z RETURNING.

0

Chyba powinieneś zacząć pytanie od opisu jaki efekt chcesz osiągnąć a później dopiero rozwiązanie, które próbowałeś zastosować.
Te trzy kolumny dodaj do jednego unikalnego indeksu funkcyjnego z zamianą null na jakąś stałą wartość. Przynajmniej tak się to robi gdy nie ma partial index. Przy ON CONFLICT możesz spróbować użyć DO NOTHING.

0

@ralf: Czy masz na mysli cos takiego:


CREATE OR REPLACE FUNCTION test_function(firstname, surname, pk TEXT)
RETURNS equipment AS $$

    SELECT firstname, surname WHERE removed IS NULL;

$$
LANGUAGE SQL IMMUTABLE;


CREATE UNIQUE INDEX unique_match ON equipment(
  test_function(firstname, surname, removed)
);

0

https://www.postgresql.org/docs/current/indexes-expressional.html
Czyli bardziej coś takiego:
CREATE UNIQUE INDEX idx1 ON equipment (firstname, surname, COALESCE(removed, 'inna wartosc niz w removed'))

0

@ralf Niebardzo rozumiem tego Indexu. W removed kolumnie trzymam date usunięcia rekordu z typem TIMESTAMP, Co mialo by być w drugiej czesci COALESCE?

id | firstname | surname | removed
---------------- | -------------------
1| Adam | Romanowski | 2019-01-01 0000
2| Adam | Romanowski | 2019-01-01 0000
3| Adam | Romanowski | null
4| Tomek| Kowalski | 2019-01-01 0000
5| Tomek| Kowalski | null

Zapytanie insert ma tak wygladac?


INSERT INTO equipment (firstname, surname) VALUES ('test 7', 'test 7')
ON CONFLICT(firstname, surname, COALESCE(removed, '2019-01-01 00:00:00')) 
DO UPDATE SET surname = EXCLUDED.surname RETURNING id ;

Tak, dziala. Ale troche nie rozumiem jak ten index funkcjonuje.

0

Ok, znalazlem jeszcze inne rozwiazanie. Moze komus sie przyda, bo temat wcale nie taki prosty.


CREATE UNIQUE INDEX equipment_firstname_surname_idx
    ON equipment (firstname, surname) WHERE removed IS NULL;

INSERT INTO equipment (firstname, surname) VALUES ('test 1', 'test 1')
ON CONFLICT(firstname, surname) WHERE removed IS NULL
    DO UPDATE SET surname = EXCLUDED.surname RETURNING id ;

Jest jeszcze cos takiego jak CONCURRENTLY :) Troche jest rozkminy.

1

Pewnie byłoby prościej gdybyś napisał dokładnie o co Ci chodzi, a nie rzucał surowymi kwerendami na lewo i prawo.

poniatowski napisał(a):

W removed kolumnie trzymam date usunięcia rekordu z typem TIMESTAMP

Jaką informacje w takim razie przechowuje rekord z removed = NULL?

0

@tajny_agent: Fakt, troche zakrecilem watek :) Kolumna removed przechowuje date zaktualizowania albo usuniecia unikatowego rekordu. Podalem przyklad powyzej. Removed = null przechowuje nowy rekord albo zaktualizowany rekord. Rekordy z data, sa usuniete, z systemu. Nie biore ich pod uwage. To jest tylko backup dla mnie.

1

Dziwne rozwiązania wskazują na słaby model bazy. Ta twoja tabelka będzie rosła w nieskończoność? Dobry wzorzec to przenoszenie usuniętych danych do tabel historycznych jeżeli już musisz je przechowywać. Wspomniałeś też backup - to zupełnie inna historia i mechanizmy. Polecam zapoznać się z tematami retencja danych albo Data Life Cycle.

1 użytkowników online, w tym zalogowanych: 0, gości: 1