Postgresql po co sa CONSTRAINT?

Odpowiedz Nowy wątek
2019-07-17 20:18
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;
edytowany 4x, ostatnio: poniatowski, 2019-07-19 10:55

Pozostało 580 znaków

2019-07-17 21:01
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.

edytowany 1x, ostatnio: ralf, 2019-07-17 21:03

Pozostało 580 znaków

2019-07-17 21:07
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.

a co to ma do tematu? - fasadin 2019-07-18 10:18

Pozostało 580 znaków

2019-07-17 22:49
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 :) ?

edytowany 3x, ostatnio: poniatowski, 2019-07-17 23:14

Pozostało 580 znaków

2019-07-18 10:02
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.

edytowany 2x, ostatnio: poniatowski, 2019-07-18 11:30

Pozostało 580 znaków

2019-07-18 20:48
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.

edytowany 1x, ostatnio: ralf, 2019-07-18 20:48

Pozostało 580 znaków

2019-07-18 21:51
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)
);

Pozostało 580 znaków

2019-07-18 23:25

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

Pozostało 580 znaków

2019-07-18 23:35
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 00:00:00
2 Adam Romanowski 2019-01-01 00:00:00
3 Adam Romanowski null
4 Tomek Kowalski 2019-01-01 00:00:00
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.

edytowany 3x, ostatnio: poniatowski, 2019-07-18 23:54
Nie możesz tam dać wartości która może wystąpić w kolumnie removed bo wtedy nie dodasz nowego rekordu z tą wartością. Dla twojego przypadku byłaby to data np 1900-01-01 albo 2000-01-01 itd. Ważne żeby zamienić null bo każdy null jest unikalny. Removed ma typ date czy varchar? - ralf 2019-07-19 12:17

Pozostało 580 znaków

2019-07-19 10:10
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.

edytowany 1x, ostatnio: poniatowski, 2019-07-19 10:17
CONCURRENTLY - masz to wszystko napisane z dokumentacji którą wkleiłeś. To opcja, która nie blokuje tabelki przy tworzeniu indeksu, jeżeli robisz to na początku i nie ma danych to jest zbędna. Ma znaczenie jak system już działa produkcyjnie i musisz stworzyć nowy indeks. - ralf 2019-07-19 12:26
Wydaje mi się, że tam jest wiecej zależności CONCURRENTLY. - poniatowski 2019-07-19 22:44
Dobra opcja na produkcje. Ale dosc wrazliwa przy UNIQUE INDEX. Trzeba uwazac, bo moze utworzy invalid index i bedzie trzeba go usunac i utworzyc na nowo. Ale fajna opcja na standardowe indexy oraz czescione. - poniatowski 2019-07-19 23:21
Tak to prawda, wydaje ci się :) - ralf 2019-07-20 10:25

Pozostało 580 znaków

2019-07-19 10:33
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?

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

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