🥇PgGraph - segédprogram a táblafüggőségek archiválására és keresésére a PostgreSQL-ben

Ma egy Pythonban írt segédprogramot szeretnék bemutatni a Habr olvasóknak a PostgreSQL DBMS táblafüggőségeinek kezelésére.

A segédprogram API-ja egyszerű, és három módszerből áll:

archív_tábla - sorok rekurzív archiválása/törlése meghatározott elsődleges kulcsokkal
get_table_references — függőségek keresése egy táblához (megjeleníti a megadott táblákat és azokat, amelyekre hivatkozik)
get_rows_references - olyan sorok keresése más táblákban, amelyek a kívánt tábla meghatározott soraira hivatkoznak

őstörténet

A nevem Oleg Borzov, fejlesztő vagyok a Domklik jelzáloghitelezési menedzsereinek CRM csapatában.

CRM rendszerünk fő adatbázisa volumenét tekintve az egyik legnagyobb a cégnél. Ez egyben az egyik legrégebbi is: a projekt indulásakor jelent meg, amikor még nagyok voltak a fák, a Domklik startup volt, és a divatos Python aszinkron keretrendszer mikroszolgáltatása helyett hatalmas monolit volt a PHP-ben.

A PHP-ről a Pythonra való átállás nagyon hosszú volt, és mindkét rendszer egyidejű támogatását igényelte, ami befolyásolta az adatbázis kialakítását.

Ennek eredményeként van egy adatbázisunk, amely nagyszámú, szorosan összekapcsolt és hatalmas táblát tartalmaz, számos indexel a különböző típusú lekérdezésekhez. Mindez negatívan befolyásolja az adatbázis teljesítményét: a nagy táblák és a közöttük lévő kapcsolatok sokasága miatt folyamatosan növekszik a lekérdezések bonyolultsága, ami különösen a legtöbbet terhelt táblák esetében kritikus.

Az adatbázis terhelésének csökkentése érdekében úgy döntöttünk, hogy írunk egy szkriptet, amely a legterjedelmesebb és legterheltebb táblákból a régi rekordokat átviszi az archivált táblákba (pl. task в task_archive).

Ezt a feladatot nehezíti a táblák közötti kapcsolatok nagy száma: egyszerűen mozgassa át a sorokat task в task_archive nem elég, előtte meg kell tennie ugyanezt rekurzív módon minden hivatkozással task táblázatok.

Egy példával mutatom be demo adatbázis a postgrespro.ru webhelyről:

Tegyük fel, hogy rekordokat kell törölnünk egy táblából Flights. A Postgres nem engedi meg, hogy ezt csak úgy tegyük: először törölnünk kell a rekordokat az összes hivatkozási táblából, és így tovább rekurzív módon egészen a senki által nem hivatkozott táblákig.

Példánkban itt: Flights utal Ticket_flightsés rajta - Boarding_passes.

Ezért a következő sorrendben kell törölnie:

Megkapjuk a sorok elsődleges kulcsainak (PK) értékeit Ticket_flights, amelyek a törölni kívánt sorokra vonatkoznak Flights.
PK sorokat kapunk Boarding_passes, amelyek utalnak Ticket_flights.
A táblázat 2. lépéséből PK szerint töröljük a sorokat Boarding_passes.
Törölje a sorokat PK-val az 1. lépéstől kezdve Ticket_flights.
Sorok eltávolítása innen Flights.

Az eredmény egy PgGraph nevű segédprogram lett, amelyet úgy döntöttünk, hogy nyílt forráskódúvá teszünk.

Hogyan kell használni

A segédprogram két használati módot támogat:

Hívás parancssorból (pggraph …).
Használat Python kódban (osztály PgGraphApi).

Telepítés és konfigurálás

Először telepítenie kell a segédprogramot a Pypi tárolóból:

pip3 install pggraph

Ezután hozzon létre egy config.ini fájlt a helyi gépen az adatbázis konfigurációjával és az archiváló szkripttel:

[db]
host = localhost
port = 5432
user = postgres
password = postgres
dbname = postgres
schema = public ; Необязательный параметр, указано значение по умолчанию

[archive]  ; Данный раздел заполнять необязательно, ниже указаны значения по умолчанию
is_debug = false
chunk_size = 1000
max_depth = 20
to_archive = true
archive_suffix = 'archive'

Fuss a konzolról

Paraméterek

$ pggraph -h
usage: pggraph action [-h] --table TABLE [--ids IDS] [--config_path CONFIG_PATH]
positional arguments:
  action        required action: archive_table, get_table_references, get_rows_references

optional arguments:
  -h, --help                    show this help message and exit
  --table TABLE                 table name
  --ids IDS                     primary key ids, separated by comma, e.g. 1,2,3
  --config_path CONFIG_PATH     path to config.ini
  --log_path LOG_PATH           path to log dir
  --log_level LOG_LEVEL         log level (debug, info, error)

Pozíciós érvek:

action - szükséges intézkedés: archive_table, get_table_references vagy get_rows_references.

Megnevezett argumentumok:

--config_path — a konfigurációs fájl elérési útja;
--table — egy táblázat, amellyel egy műveletet kell végrehajtania;
--ids — az azonosítók listája vesszővel elválasztva, például, 1,2,3 (opcionális paraméter);
--log_path — a naplók mappájának elérési útja (opcionális paraméter, alapértelmezés szerint a saját mappa);
--log_level — naplózási szint (opcionális paraméter, alapértelmezett az INFO).

Parancspéldák

Táblázat archiválása

A segédprogram fő funkciója az adatok archiválása, azaz. sorok átvitele a főtáblából az archív táblába (például a táblából könyvek в könyvek_archívuma).

Az archiválás nélküli törlés is támogatott: ehhez be kell állítani a paramétert a config.ini-ben to_archive = false).

Kötelező paraméterek - config_path, tábla és azonosítók.

Az indítás után a rekordok rekurzív módon törlődnek ids az asztalban table és minden arra utaló táblázatban.

$ pggraph archive_table --config_path config.hw.local.ini --table flights --ids 1,2,3
2020-06-20 19:27:44 INFO: flights - START
2020-06-20 19:27:44 INFO: flights - start archive_recursive 3 rows (depth=0)
2020-06-20 19:27:44 INFO:       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 19:27:44 INFO:               START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               boarding_passes - start archive_recursive 3 rows (depth=2)
2020-06-20 19:27:44 INFO:                       START ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:                       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:               boarding_passes - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:               END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO:       ticket_flights - archive_by_ids 3 rows by ticket_no, flight_id
2020-06-20 19:27:44 INFO:       END ARCHIVE REFERRING TABLES
2020-06-20 19:27:44 INFO: flights - archive_by_ids 3 rows by id
2020-06-20 19:27:44 INFO: flights - END

Függőségek keresése egy megadott táblához

Funkció egy adott tábla függőségének megkeresésére table. Kötelező paraméterek - config_path и table.

Indítás után egy szótár jelenik meg a képernyőn, ahol:

in_refs — egy adott táblázatra hivatkozó táblák szótára, ahol a kulcs a tábla neve, az érték az idegen kulcs objektumok listája (pk_main - elsődleges kulcs a főtáblában, pk_ref - elsődleges kulcs a hivatkozási táblában, fk_ref — annak az oszlopnak a neve, amely a forrástábla idegen kulcsa);
out_refs — táblázatok szótára, amelyre ez hivatkozik.

$ pggraph get_table_references --config_path config.hw.local.ini --table flights
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='ticket_no, flight_id', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Hivatkozások keresése a megadott elsődleges kulccsal rendelkező karakterláncokra

Funkció, amellyel olyan sorokat kereshet más táblázatokban, amelyek idegen kulccsal hivatkoznak sorokra ids asztalok table. Kötelező paraméterek - config_path, table и ids.

Indítás után egy szótár jelenik meg a képernyőn a következő szerkezettel:

{
	pk_id_1: {
		reffering_table_name_1: {
			foreign_key_1: [
				{row_pk_1: value, row_pk_2: value},
				...
			], 
			...
		},
		...
	},
	pk_id_2: {...},
	...
}

Példahívás:

$ pggraph get_rows_references --config_path config.hw.local.ini --table flights --ids 1,2,3
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

Használat kódban

A konzolban való futtatás mellett a könyvtár Python kódban is használható. Az alábbiakban az iPython interaktív környezetben végrehajtott hívásokra mutatunk be példákat.

Táblázat archiválása

>>> from pg_graph.main import setup_logging
>>> setup_logging(log_level='DEBUG')
>>> from pg_graph.api import PgGraphApi
>>> api = PgGraphApi('config.hw.local.ini')
>>> api.archive_table('flights', [4,5])
2020-06-20 23:12:08 INFO: flights - START
2020-06-20 23:12:08 INFO: flights - start archive_recursive 2 rows (depth=0)
2020-06-20 23:12:08 INFO: 	START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 	ticket_flights - ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')
2020-06-20 23:12:08 DEBUG: 	SQL('SELECT flight_id, ticket_no FROM bookings.ticket_flights WHERE (flight_id) IN (%s, %s)')
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 30 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 30 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 30 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 30 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 30 rows
2020-06-20 23:12:08 INFO: 	ticket_flights - start archive_recursive 3 rows (depth=1)
2020-06-20 23:12:08 INFO: 		START ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 DEBUG: 		boarding_passes - ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 INFO: 		boarding_passes - archive_by_fk 3 rows by ForeignKey(pk_main='flight_id, ticket_no', pk_ref='flight_id, ticket_no', fk_ref='flight_id, ticket_no')
2020-06-20 23:12:08 DEBUG: 		SQL('CREATE TABLE IF NOT EXISTS bookings.boarding_passes_archive (LIKE bookings.boarding_passes)')
2020-06-20 23:12:08 DEBUG: 		DELETE FROM boarding_passes by FK flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 INFO: 		END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: 	ticket_flights - archive_by_ids 3 rows by flight_id, ticket_no
2020-06-20 23:12:08 DEBUG: 	SQL('CREATE TABLE IF NOT EXISTS bookings.ticket_flights_archive (LIKE bookings.ticket_flights)')
2020-06-20 23:12:08 DEBUG: 	DELETE FROM ticket_flights by flight_id, ticket_no - 3 rows
2020-06-20 23:12:08 DEBUG: 	INSERT INTO ticket_flights_archive - 3 rows
2020-06-20 23:12:08 INFO: 	END ARCHIVE REFERRING TABLES
2020-06-20 23:12:08 INFO: flights - archive_by_ids 2 rows by flight_id
2020-06-20 23:12:09 DEBUG: SQL('CREATE TABLE IF NOT EXISTS bookings.flights_archive (LIKE bookings.flights)')
2020-06-20 23:12:09 DEBUG: DELETE FROM flights by flight_id - 2 rows
2020-06-20 23:12:09 DEBUG: INSERT INTO flights_archive - 2 rows
2020-06-20 23:12:09 INFO: flights - END

Függőségek keresése egy megadott táblához

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> res = api.get_table_references('flights')
>>> pprint(res)
{'in_refs': {'ticket_flights': [ForeignKey(pk_main='flight_id', pk_ref='flight_id, ticket_no', fk_ref='flight_id')]},
 'out_refs': {'aircrafts': [ForeignKey(pk_main='aircraft_code', pk_ref='flight_id', fk_ref='aircraft_code')],
              'airports': [ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='arrival_airport'),
                           ForeignKey(pk_main='airport_code', pk_ref='flight_id', fk_ref='departure_airport')]}}

Hivatkozások keresése a megadott elsődleges kulccsal rendelkező karakterláncokra

>>> from pg_graph.api import PgGraphApi
>>> from pprint import pprint
>>> api = PgGraphApi('config.hw.local.ini')
>>> rows = api.get_rows_references('flights', [1,2,3])
>>> pprint(rows)
{1: {'ticket_flights': {'flight_id': [{'flight_id': 1,
                                       'ticket_no': '0005432816945'},
                                      {'flight_id': 1,
                                       'ticket_no': '0005432816941'}]}},
 2: {'ticket_flights': {'flight_id': [{'flight_id': 2,
                                       'ticket_no': '0005433101832'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005433101864'},
                                      {'flight_id': 2,
                                       'ticket_no': '0005432919715'}]}},
 3: {'ticket_flights': {'flight_id': [{'flight_id': 3,
                                       'ticket_no': '0005432817560'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817568'},
                                      {'flight_id': 3,
                                       'ticket_no': '0005432817559'}]}}}

A könyvtár forráskódja a címen érhető el GitHub MIT licenc alatt, valamint az adattárban PyPI.

Szívesen fogadok észrevételeket, javaslatokat.

Igyekszem a legjobb tudásom szerint válaszolni a kérdésekre itt és az adattárban.

Forrás: will.com

A PgGraph egy segédprogram a táblafüggőségek archiválására és megtalálására a PostgreSQL-ben

őstörténet

Hogyan kell használni

Telepítés és konfigurálás

Fuss a konzolról

Paraméterek

Parancspéldák

Táblázat archiválása

Függőségek keresése egy megadott táblához

Hivatkozások keresése a megadott elsődleges kulccsal rendelkező karakterláncokra

Használat kódban

Táblázat archiválása

Függőségek keresése egy megadott táblához

Hivatkozások keresése a megadott elsődleges kulccsal rendelkező karakterláncokra

Hozzászólás Megszünteti a választ