αααααΆααααααΎαααΆααααα»αααααΆααααααα»ααα·αααααααα (αααααααααΆ
αα·α
αα
ααΆαααααααΆααααααααααα ααΆααααααΆααααΆαααα
ααΌα
ααΆα "αα
βααΈβααα
ααα»αααααα
ααααααααα·ααΆααα "α’αααΈαα½α" αααα
αΆααααααΎαααΆαααααααΆααΆααααααααΆαα α αΎαααααΆααααααααΌαααααααααααΎααΆαααΆαα½αααΌαααααΆααα·αααααα 24x7 αααααααΆααααΆα
αααΎαααΎαα‘αΎααααααΉαααααααΆαααΈαα·αααααα’αααα
ααΎααααΈαααααααΆαααΆαα½ααα½ααααα
αααα»α PostgreSQL (α αΎααα·αααααΉααααα
αααα»αααΆααα»ααααααα) α’αααα’αΆα
ααααΎααΆααααααΎαααααα·αααααΆααα½αα
ααα½ααααααΉαα’αα»ααααΆαα±ααα’αααααααΎαααΆαα’αααΈαααΆαααΏαααΆααα»α αα·αααΆαααΆαααααΎααααΆααααααΆααα·α
α
1. αααααααααααααΌαααΉααααααΌα?
ααΆααααΌα α αΌαααΎααααααα α α·αααααΆααΎααΎαα’αΆα αααα»ααα·αααααααααααΎαα αα "ααααΎαααΆα" αα ααααααααΆα
α‘.α‘. ααΆααΆαααααααα’αΆαααα (ααΆααΆαααααααα’αΆαααα)
ααΆαααααΆααααααααΆααααΆααΆαααααααα’αΆαααα PostgreSQL ααΊααΌα ααααΆααΉαααΆααΆαααααααααα ααΌα ααααααααΏαααααα αΌαα α·ααα "α’αααΈαααΆααα’ααααααΌαααΆααααααΆαα»ααααα»αααΆαα αα αΆαααα»ααααα α αΎαααΆα’αΆα αααα ααααΆα". ααα»ααααααααΆαααΆααα»αααααΆααααΆααααα½αα ααα½ααααααα
"namespace" ααααΆαααααα½αααααα’ααααααααΆααααΆααααααΆααααΈαα½αααα ααΆααααΌαααααΆααα·αααααα
ααααα·αααΎααΆααααααΆααααΈαααααΆααΆαααααΆαααααα»αααααααα½α CREATE TABLE x
αααααΆααααααααΆααααΆααααΉαααα½αααΆα ααα α»ααα·αα―αα ααααα»ααΌαααααΆααα·ααααααα
ααα»ααααααααα·αααΎααΆααααΈαααααΆααΆαααααα·ααααα· CREATE TEMPORARY TABLE x
αααααΆααααααΆααααΈαααΉαααααΎααΆααΆααααααΆ α αΎααααααααααΆααΉαααα½αααΆα α
αααΆααα
ααααααααα’αααα αα»α α αΎαβααΉαβαα·αβααΆαβα’αααΈβααΌα
ααααΆβαααΆαβαα½αααβααα
"ααααααΆααααα½αα―α" αα αααααααΆα α
αα
ααααααααΆααααααΆααααααΌαααΆααα·α ααΆααΆαααααααα’αΆααααααΆααα’ααααααΌαααΆααα»ααααααααααααααααα· ααΌα
ααααααααα DROP TABLE x
ααααΆαα
ααα»α
α’αααΈαααα
ααΈ...
ααααα·αααΎα’ααααααα»αααααΎααΆαααΆαααα pgbouncer αα αααα»αααααααααα·ααααα·ααΆααααααΆααααααΌαααααΆααα·ααααααααααααΏααΆααΆααααααΆααααααα ααααααα α αΎααα αααα»ααααααΆααΆαααααααα’αΆααααααααα ααααΆαα
ααΌα
αααα ααΆαααααΆααΆααααααΎαααΆααααααα ααΈααΆααααααΆααααααααα
pgbouncer ααΉααααααΆαα±ααααΆαααα α»αα ααα»αααααααα’αΆα
ααααΌαααΆααααααΆααααααααΎ CREATE TEMPORARY TABLE IF NOT EXISTS x
.
ααΆααΆααααααΎαααΆαα ααΆααΆααΆααααααΎαααΆααα»αααααΎααΌα
ααα ααΈααααααααααΆααααα’αααα’αΆα
"ααααΆαα" ααααΎααα·ααααααααααα
αααααΈ "ααα
αΆαααα»α" αα
ααΈαααα αααα½ααααα·α ααΆααΆααΆααααααΎαααΆααααα»αααΆαα’αΆαααααα
ααααΆα α αΎαααΎαααΆαα
ααααααααΎαααΆααΆαααΆα’αΆα
ααααααααΆαα ON COMMIT DROP
- αααααΊαα
ααααααααααα·ααααα·ααΆαααΆααααα
αα ααΆααΆαααΉαααααΌαααΆααα»ααααααααααααααααα·α
αα·αα αααα
αααααΆααα½αααΆααΆαααααα·αααα·ααααααΆααααααΆααααΆααααΆαα ααΆααΆαααααααα’αΆαααααα·αααααΌαααΆαα ααααααα ααα»αααα ααααα»αααααΆαααααααΌαααΆααααααΆααααΆααααααααΆαα·ααααααααΈααα αα αααα»α heap + WAL ααΌα αααα INSERT/UPDATE/DELETE αα αααα»αααΆααΏαααΆααααΆαααααΆααα
ααα»αααααααααΆαααΆααΆαααααααα’αΆαααααα ααααΆααΆααΆα "ααααΎαααααααααΆ" ααΆαα·αα’αΆα αααααΎααα ααΎααΆαα ααααααΆαααα αααΆαα αα ααΆαααααααΆαααααααα ααΎαααααΈααΆαααααααααααΌαααααΆααααΌαααΆαααααααααααΆαα’ααααααααααΆααΌααααααα
α‘.α’. ααΆααΆαααααα·αααΆαα α»αααααα
ααα»ααααα’αααΈαααα’ααααα½αααααΎ ααΆα§ααΆα ααα ααααα·αααΎα’αααααΆαααααΎαααΆα ETL αααααα»αααααΆααα½αα ααα½α ααααα·αα’αΆα ααααΌαααΆαα’αα»αααααα αααα»αααααα·ααααα·ααΆααα½α ααα»ααααα’ααααα ααααΆα pgbouncer αα αααα»αααααααααα·ααααα·ααΆα? ..
α¬ααα αΌααα·ααααααααΆαααα ααα αα·αααΆαααααα·ααααααΌααααααααααΆαααα ααΎααΆααααααΆαααααα½αααα ααΈααΌαααααΆααα·αααααα (α’αΆαααααΎαααΆααα½ααααα»ααα½ααααΈααΈααΌ)?..
α¬ααααα·ααααα·ααΆααααααααα»αααααΎαααΆα α’ααααΆα αα αααα»αααΆααααααΆαααααααααααΆ? ..
ααΆααααααΎααααα½αααααα ααΈααα - αααααΎαααΆααΆαααααα·ααααααΆααααααα’αΆαααα. Pun ααΆαα αααααΊα
- ααΆααααααΎαααΆααΆα "αααααααα»α" αααααΆααααααα αααααααΆα’αα·ααααΆ ααΎααααΈαα»αα±αααααααααααΆαα½αααααΆααααΆαα
- ααΆααααααααα ααααααα½αααΆααααα·ααααααααΈαααααααΆααααα
- ααααΆααααααΌαα ααΆαααααααα ααααααααα»αααΆαααααΆαααααααΉα
- αααα»αα α αΆαααα·αααααααα½α ααΆαααα αααα»αααΆααΆαααααα
- ααΆααα»αααΆααΆα "αααααααα»α"
α αΎαα₯α‘αΌαααα - αα»ααα
αααα»ααα½αα ααΆααα·ααα
ααΆααααααααΆααα’αααα
αααα»α PostgreSQL ααΎαα‘αΎαααΈααα - COMMIT
' nutty αα·α ROLLBACK
'ααααα·ααααα·ααΆαααααΆαα
ααα»ααααααΎααα·αααααΌαααΆαααααα! ααΎαααΆαααααΎαααΆαααΆααααΌα αααβααΈβααΆβααΆβααα½αβααΆαβααααααβααΆααβαααα»α α¬βαα·αβααΆαβαααααα α. ααΆαα·αααΆααααα αΆααΆααΎααααα·ααααα·ααΆαααααα·ααααααααΉαααΆαααα»ααααΆαααααα - ααΎααα·αα αΆααα’αΆααααααααΉα "ααααααααΎαααΆαααΈααΆαααααααΆα" ααΆαα·ααααα ααααααααΆαα·αα αααΆααααΆααΆαα α―ααΆα
ααΎααααΈααααΎααΌα
αααα’αααα’αα·αααααα PostgreSQL αααα‘αααααα·ααα
αααα»ααααα 9.1 ααΆαααααΆαααΏαααΌα
ααΆ
ααΆαα½αααΉαααΆαα ααα’α»ααααα αΆαααα ααΆααΆαααααΌαααΆααααααΎαααΆ unlogged α αα·ααααααβαααβαααααβαα βααΆααΆαβαααβαα·αβααΆαβα αΌαβαα·αβααΆαβα αΌαβαα βαααα»αβααΆαβαααααβαα»αβαα»αβαα (ααΌαβααΎαβααααΌαβααΈ 29) αααβααααΎβα±ααβααΆααΆαβααΌα βααΆ ααααΎααΆαααΏαααΆαααααααΆα. αααααΆαααΆαααΆ, αα½ααααα·αααΆαααΆααααΆααα ααΉαααΆααααΆααα; αααα»αααααΈαααΆαααΈααααααΆααα α¬ααΆααα·αααΆαααααΆαα ααΆααΆαααααα·αααΆαα αΌα ααΆααααααααααααααααααααααα·. ααΎαααΈααα ααααΉαααΆαααααΆααΆαααααα·αααΆαα αΌα αα·αααΆαα αααα ααΎααααΈαααααΎααααΆαααααΎα αα·αα·ααααααΆαα½ααααααΆααααααΎααα ααΎααΆααΆαααααα·αααΆαα αΌαααααΌαααΆααα»αα αααααααααααααααααα·α
αα·ααΆαα±ααααααΈ ααΆααΉαααΏαααΆααα»αααα»ααααααααα·αααΎαααΆαααΈαααααΌαααααΆααα·αααααα "ααααΆαα" ααΆααΉααα·αααα’ααα ααα»ααααααΎααΆααΎαα‘αΎαααΉαααΆααααα»ααααΆ α αΎαααΎααααΎαααΆα ETL ααααα’αααααΉαααΈααααααααΆα±ααααΆαααααΉαααααΌα "ααΈαααααΆα" αααααΆααααΈ "ααααΎα±ααα‘αΎααα·α" ααΌαααααΆααα·αααααααααα¬αα?..
ααααα·αααΎαα·αααΆααα α αΎαααααΈααΆαααΎααΊααααααααΉαααααα’ααα ααΌαααααΎ UNLOGGED
ααα»αααααα·αααα αα»αααΎααα»αααααααααααα
ααΎααΆααΆααα·α, αα·αααααααααααΆααΈαααα‘αΆαααααααΆααα’αααα
α‘.α£. ON COMMIT { Delete ROWS | αααααΆαα}
ααΆαααααΆαααΆαααα’αα»ααααΆαα±ααα’ααααααααΆααα₯αα·ααΆαααααααααααααααααα·αα αααααααα·ααααα·ααΆαααααΌαααΆααααα αααα ααααααααΎαααΆααΆαα
αα
ααΎ ON COMMIT DROP
αααα»αααΆααααααααΆαααΎαα½α
α αΎα ααΆαααααΎα DROP TABLE
ααα»ααααααΆαα½α ON COMMIT DELETE ROWS
ααααΆαααΆαααΆαααααα½αα±ααα
αΆααα’αΆαααααα - ααΆααααΌαααΆααααααΎααα
ααΈααα TRUNCATE TABLE
.
αααααΆαα αααααΆαα ααΆααααααααααΆααααΌααααααΆααααΆααααααΆαα»αααΆααα·αααααΆααααΆααααΆααΆαααααααα’αΆααααααΊααΌα ααααΆααααα·ααα ααΉαααΆααΆαααααααΆ ααΌα αααα ααΆααααααΎαααΆαααα αΆα αα·αααΆααα»αααΆααΆαααααααα’αΆααααααΆαα±ααααΆα "ααΆαα αΎα" αααααααααααααΆααΆααααααααα pg_class, pg_attribute, pg_attrdef, pg_depend,β¦
α₯α‘αΌααααααααΆα’αααααΆαααααααααααΆαααα ααΎααΆαααααΆαααααααααΆαααα ααΌαααααΆααα·αααααα αααααΎαααααα·ααααα·ααΆαααααΈαααααΆαααα·ααΆααΈ αααααΎα ααααα ααααΎαααΆα αα·ααα»αααΆααΆαααααααα’αΆαααα... ααΆααΉαααΆαααααΆαααΎααα αααα»αααΆααΆααααααααα α αΎα ααΆααΉααααααΆαα±ααααΆαα αααααΆαααααααααααααΆααααααα·ααααα·ααΆαααΈαα½ααα
ααΆααΌαα
αα»αααααΎαααααα! αααα»αααααΈαααααΆααΆαααααα·αααααΆαααΆα CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS
ααααΆα
ααααΈααααααααα·ααααα·ααΆα - αααααΆαααααααααΆαα
αΆααααααΎαααααααα·ααααα·ααΆαααααΈααΈαα½ααααΆααΆαααΆααα½α
α αΎα ααΉαααΆα (αααααΆαα»αααΆαα α
ααΌαααααα CREATE
) ααα»αααα ααΉαααα, α’ααα»αβα
αααα TRUNCATE
(ααΎαααααΆααααααΆαα»αααΆαα α
ααααααΆααααα) αα
ααααααα
ααααααα·ααααα·ααΆααα»αα
α‘.α€. ααΌα ααΆ... αα½αααΆαα...
αααα»αααΆααααααΆαααα ααΎαααααΌαααΆ ααααΈααααΎααααΆααααααααΆαα½ααααααΆααααΆααΆαααααααα’αΆααααααΊααααααααααααααααΆαααΆαα αΌα α αΎαα’αααα’αα·ααααααΏαα αααααα»αααΆαα αααα-αα·αααααΆαααααααΈααΆαααααΆααΆαααααα αα αααα»αααα ααααΈαααααΆαααααααα’αΆααααααααααΆαα...
ααα»ααααααΆααααα·αααΊααΆααααΆααααΆαααΈααααααααααΆα! αααβα αΎαβααΆβααΌαβα ααα»βααα αααααΎαααΆααΆαααααΈ "ααα’ααααΎααααΌ" ααΆα’αΆα ααΆααααααΆααααα
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);
α αΆααααΆααααΈαααααααα α’αααα’αΆα αααααΎααα·ααααααααΆα αααΎααα αααα»αααΆααΆαααα ααΆααααααααααΆααααααΆααΉααα·αααΏαααα ααα»ααααααΆααααααααααΆαααααααααααΈα αααααααα αΆααα - αα·αα·αααα! α αΎαααΆα ααΆααΆαααααααα’αΆααααααα’αΆα ααΆααα·αα·αααααααααα.
α
αΆααααΆααααΈ ααΆααΉαααΆαα αα·αα·αααααααααααΌαααΆαααααααααΆααΆαα½αααΉααααααααααααααΆααΆαααααα
α’αααα’αΆα
ααααααααΆαααΆαααα LIKE target_table INCLUDING INDEXES
.
ααααα·αααΎα’αααααααΌαααΆααααααα DEFAULT
-values ββ(α§ααΆα ααα ααΎααααΈβαααααβαααααβαααααΉαβα
αααα) α’αααβα’αΆα
βααααΎ LIKE target_table INCLUDING DEFAULTS
. α¬ααΆαααα - LIKE target_table INCLUDING ALL
- α
ααααααααΆαααΎα αα·αα·αααα α§ααααα...
ααα»αααααα
ααΈαααα’αααααααΌααααααΆααααα·αααΎα’αααααΆααααααΎα ααΆααΆαααΆαα
αΌαααααΆααααΆαα½αααΉααα·αα·αααα αααααΆαααααα·ααααααααΉαα
αααΆααααααΌαααΎααααΈαααα»αααΆαααααα·αααΎα’ααααααααα’αααΈαααααααααΆαααΆαα»ααα·α α αΎαααααΆααααααααα‘αΎαααααααααα - ααΎαααΈαααααααααΆααααΎαααααΆα§ααΆα ααα
ααΆααΌαα
2. ααααααααα?
αααα»αααααΆαααααα·ααΆαααΆ - ααααΎααΆα
- ααα αΌααααα½αα±αα "αααα
αα" INSERT
,
3. ααΎααααΌαααααΎαααΆααααααααααΆ?
ααΌα ααααβααΌαβα±ααβααΆαβααααΆαβααααβααΎαβααΎαβαα βααΌα βαααβ:
- α’αααβααΆαβααΆααΆαβαααβααΆαβαα·ααααααβα’αα·αα·ααβαααβααΆαβαααααΆβαα»αβαααα»αβααΌαααααΆαβαα·ααααααβααααβα’αααα αααααααααΆ 1M
- αααααΆαααααα α’αα·αα·ααααααΎα’αααααααΈα "ααΌαααΆα" αααααα
- ααΆααααα·ααααα α’αααααΉαααΆααΈααααα½ααα ααααα½αα αααααααααΆαα·αααΎαααΈ 10K ααααΌαααΆαααααΆααααααΌα
α§ααΆα ααααα»ααΆαααααααΆαααΆαααααααααΊ
α£.α‘. αααα½ααααααααΆαααΆαααααΎααααΆααααααααααα
αααααΆααααΆαααΆαααα α αΌααα·ααΆαααΆα’ααααα·αα αΆαααΆα ααααα ααα·ααααααα‘αΎααα·ααα - ααααΆααααααΆαααααΆααΆααα ααΆαααααααααα ααααΆα αααααΊα
- ααα αα α’αααΈααααααααΆαααααααααΆα
- ααααΎα±ααααΆαααααα α’αααΈααααααααΆααααααΆααα½α α αΎα αα·αα αΆαααΆα αααααΌαααααΎαα αα α»ααααααααΆα
- αααα αΌα α’αααΈααααααααΆαααααα·αααΆααααΎαα‘αΎα
α ααα»α’αααΈααΆαααΆααααα·ααααα·ααΆααα½αααααααΌαααΆαααααΎαα
αααα»αααααΆααααα? ααααααααααΆαααααααααα αααΆααΆαααΉαααΎαα‘αΎααα·α
αα½α
αααα»α (
αα»αα ααααΈ dst
αα αα·αααΆααα’αααα’αΆα ααα½αααΆααααααααΆααααααααα·ααααα·ααΆαααΈαα
- ααα
αα (
DELETE
) α’αααΈααααααααΆα - αααα αΌα ααΆααα’ααααΈααΌαααΆαααααΈα
ααα»αααααααααΉαααΉαααα ααΌαα’ααα»αααα MVCC ααα αααααΆααΆαααΉαααΎαα‘αΎααααααα! ααΆαααα½αααΆαααΌαααΆα +1M αααααααααααΆαα αααα»αααΆααΆααααααΆαααααΆαα’αΆααααα 10K ααΊα α½αα ααα»ααα...
ααΆααα αα dst
α’αααα’αα·αααααααααααΆααααα·αααααα αααΎαααΉαααΆαααααααααΆααααΌαα’αΆα αααα’αΆαααΆαααααααα
- α
αααΆαα (
TRUNCATE
) ααΆααΆαααΆααααΌα - αααα αΌα ααΆααα’ααααΈααΌαααΆαααααΈα
αα·ααΈααΆαααααααΆαααααα·αααααΆα
αααααΆααααααΆα
- ααΎααααα»αα αΆααααααΎα ααααα·ααααα·ααΆαααααααααα
TRUNCATE
ααααα α αΌαααααΎααααΆα ααα»α- ααΆααααααααΆαα- ααΎαααααΎαα·ααΆα
αΆααΉαααΆααΌαααΆααααα αΎα α αΎαα’ααααααααααααα
αααααα αα·αα’αΆα
ααΌααααΈαα
SELECT
ααΆαβα’αααΈβαα·αβααα’...
ααΆααΆαααααΆααααααΌαβ¦ ααααΌααααααβ¦ / αααααΆααααΆααΆαβ¦
αααααΎααα½αααΊααααΌααααααα’αααΈααααααααΆααα αααα»αααΆααΆαααααΈααΆα ααααα‘αααα½α α αΎααααααΆααααααααΆααααααααΌααααααααΆαααα½αααααααα αΆααα ααΏαααΌα ααΆα αα½αα ααα½αα
- αα αααααα α αΌαααααΎααααΆα ααα»ααααααΈααΆαααααααΆαα·α ααΆααα½αα±ααααααααααΆαααααααα
- αααααΆααααα½α/αααα·αα·ααΆααα’αααααααΆααααΆααΆααααααααΌαααΆααααααα‘αΎααα·α
ααααΌαααΆαααααΎαααΆα ANALYZE - αααααααααΆααα’ααααααΌαααΆαααΌα (FK) αα αα»
ααΆααααα WIP ααΈ Simon Riggs αααααααΎα±ααααααΎ ALTER
- ααααα·ααααα·ααΆαααΎααααΈαααα½ααα½ααΆααΆααα
ααααα·αα―αααΆαααααα·αααααααα·αα· αα·α FK ααα»αααααα·αααΆααααααΌαααΌααα»αααα
αα»α ααααΎαα αα α»ααααααααΆα αααα αΌα
ααΌα αααα ααΎααααααααΆαααΎαααααΎααα·ααααααααΆααααααααα·ααααα·ααΆαααΈα ααααΎαααααΈ... ααΎααααΎααΌα ααααα ααΎαααΆαααααα·αααααΆααααα»α?
-- Π²ΡΠ΅ Π΄Π΅Π»Π°Π΅ΠΌ Π² ΡΠ°ΠΌΠΊΠ°Ρ
ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΈ, ΡΡΠΎΠ±Ρ Π½ΠΈΠΊΡΠΎ Π½Π΅ Π²ΠΈΠ΄Π΅Π» "ΠΏΡΠΎΠΌΠ΅ΠΆΡΡΠΎΡΠ½ΡΡ
" ΡΠΎΡΡΠΎΡΠ½ΠΈΠΉ
BEGIN;
-- ΡΠΎΠ·Π΄Π°Π΅ΠΌ Π²ΡΠ΅ΠΌΠ΅Π½Π½ΡΡ ΡΠ°Π±Π»ΠΈΡΡ Ρ ΠΈΠΌΠΏΠΎΡΡΠΈΡΡΠ΅ΠΌΡΠΌΠΈ Π΄Π°Π½Π½ΡΠΌΠΈ
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- ΠΏΠΎ ΠΎΠ±ΡΠ°Π·Ρ ΠΈ ΠΏΠΎΠ΄ΠΎΠ±ΠΈΡ, Π²ΠΌΠ΅ΡΡΠ΅ Ρ ΠΈΠ½Π΄Π΅ΠΊΡΠ°ΠΌΠΈ
) ON COMMIT DROP; -- Π·Π° ΡΠ°ΠΌΠΊΠ°ΠΌΠΈ ΡΡΠ°Π½Π·Π°ΠΊΡΠΈΠΈ ΠΎΠ½Π° Π½Π°ΠΌ Π½Π΅ Π½ΡΠΆΠ½Π°
-- Π±ΡΡΡΡΠΎ-Π±ΡΡΡΡΠΎ Π²Π»ΠΈΠ²Π°Π΅ΠΌ Π½ΠΎΠ²ΡΠΉ ΠΎΠ±ΡΠ°Π· ΡΠ΅ΡΠ΅Π· COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- ΡΠ΄Π°Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΡΡΡΠΈΠ΅
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- ΠΏΠΎΠ»Ρ ΠΏΠ΅ΡΠ²ΠΈΡΠ½ΠΎΠ³ΠΎ ΠΊΠ»ΡΡΠ°
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "Π°Π½ΡΠΈΠ΄ΠΆΠΎΠΉΠ½"
-- ΠΎΠ±Π½ΠΎΠ²Π»ΡΠ΅ΠΌ ΠΎΡΡΠ°Π²ΡΠΈΠ΅ΡΡ
UPDATE
dst D
SET
(f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
tmp T
WHERE
(D.pk1, D.pk2) = (T.pk1, T.pk2) AND
(D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- Π½Π΅Π·Π°ΡΠ΅ΠΌ ΠΎΠ±Π½ΠΎΠ²Π»ΡΡΡ ΡΠΎΠ²ΠΏΠ°Π΄Π°ΡΡΠΈΠ΅
-- Π²ΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΡΡΡΠΈΠ΅
INSERT INTO
dst
SELECT
T.*
FROM
tmp T
LEFT JOIN
dst D
USING(pk1, pk2)
WHERE
D IS NOT DISTINCT FROM NULL;
COMMIT;
α£.α’. ααΆαα αΌααααααααααΎαααΆα
αα αααα»α KLADR ααΌα ααααΆ αααααααααΆαααααΆαααααΆααααααΌαααΆααα’ααααααΌαααααααΎαααΆααααααααααααΆααααααΆαααααΎαααΆαααααα - ααααΎα±ααααΆαααααααααααααΆ ααΆααααααααΉααααααΆαααααα·α αα·αααΆααααααααα ααΆαα ααΆαααααααααααααααΌαααΆαα ααα»ααααααΎα’αααααΉααααΆαααΌα ααααα - α’αααΈααααα·αααΆααΆαααααΆααααααΌαααααα·αααααΎα±αααααα»αααααΆαααΌαααααΎααααΆαααααααΆαα§ααααααα·ααααα·ααααααΆααΆααααααα?
ααααα·αααΎααΆαααααααΎαααΆαααααα’αααααΆααα·αααα·ααααααα αααααααΎααααΆααααα αααα’αααα’αΆα ααααΎαααααΉααααααΉααααααΌαααΆαααααΆααααααΌαααΆααα’αααααααΆαααα½αααΎαα
-- ΡΠ΅Π»Π΅Π²ΡΠ΅ ΡΠ°Π±Π»ΠΈΡΡ
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- ΡΠ°Π±Π»ΠΈΡΡ Ρ ΠΈΡΡΠΎΡΠΈΠ΅ΠΉ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ
CREATE TABLE kladr$log(
ro kladr, -- ΡΡΡ Π»Π΅ΠΆΠ°Ρ ΡΠ΅Π»ΡΠ΅ ΠΎΠ±ΡΠ°Π·Ρ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΡΡΠ°ΡΠΎΠΉ/Π½ΠΎΠ²ΠΎΠΉ
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- ΠΎΠ±ΡΠ°Ρ ΡΡΠ½ΠΊΡΠΈΡ Π»ΠΎΠ³ΠΈΡΠΎΠ²Π°Π½ΠΈΡ ΠΈΠ·ΠΌΠ΅Π½Π΅Π½ΠΈΠΉ
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- ΠΏΡΠΎΠ²Π΅ΡΡΠ΅ΠΌ Π½Π΅ΠΎΠ±Ρ
ΠΎΠ΄ΠΈΠΌΠΎΡΡΡ Π»ΠΎΠ³Π³ΠΈΡΠΎΠ²Π°Π½ΠΈΡ ΠΏΡΠΈ ΠΎΠ±Π½ΠΎΠ²Π»Π΅Π½ΠΈΠΈ Π·Π°ΠΏΠΈΡΠΈ
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- ΡΠΎΠ·Π΄Π°Π΅ΠΌ Π·Π°ΠΏΠΈΡΡ Π»ΠΎΠ³Π°
stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
CASE TG_OP
WHEN 'INSERT' THEN
EXECUTE stmt || 'NULL,$1)' USING NEW;
WHEN 'UPDATE' THEN
EXECUTE stmt || '$1,$2)' USING OLD, NEW;
WHEN 'DELETE' THEN
EXECUTE stmt || '$1,NULL)' USING OLD;
END CASE;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
α₯α‘αΌααααααΎαα’αΆα
α’αα»αααααααααΉααα»ααααα
αΆααααααΎαααΆαααααΎααααΆααααα (α¬ααΎααα½αααΆααΆαααα ALTER TABLE ... ENABLE TRIGGER ...
):
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
CREATE TRIGGER log
AFTER INSERT OR UPDATE OR DELETE
ON kladr_house
FOR EACH ROW
EXECUTE PROCEDURE diff$log();
α αΎααααααΆααααααΎαααΆαααααΆαααααΆααααααΌαααΆααα’αααααααΎαααααΌαααΆαααΈααΆααΆααααααα ααα»ααααααααααααΆαα α αΎαααααΎαααΆαααΆααΆααααα§ααααααααααααΆαααααααα
α£.α£. αααα»αβααΆαα αΌαβαααα»αβαααβααΆαβααααΆαα
ααΆαααΎααΎαααΆααα·α αΆαααΆααααΈαα αααααααα ααΆαααααααααα·ααααααααααααα αα·ααα·ααα ααΊααΌα ααααΆα ααα»ααααα α»ααααΆαααΆααΎααΆαα’αΆααα‘αΌαααΈααααααααααΆααααα ααΆααααααααα»αααΈαα ααΆαααααααααααα»ααααα»αααΌαααααΆααα·ααααααααααααΎα?
α αΌαααααΆα§ααΆα ααα ααΆααααα»αα’αα·αα·αα αα·αααααΈαααααα½ααα αααααΎα "α αααΎααα αα½α" αα»ααΆαα
CREATE TABLE client(
client_id
serial
PRIMARY KEY
, inn
varchar
UNIQUE
, name
varchar
);
CREATE TABLE invoice(
invoice_id
serial
PRIMARY KEY
, client_id
integer
REFERENCES client(client_id)
, number
varchar
, dt
date
, sum
numeric(32,2)
);
ααα»ααααααΆαααΆαααααΈαααααααΆααααα ααααααΎααααα»αααααααααΆ βall in oneβα
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);
ααΆααααααα αα·ααααααααααα’αα·αα·ααα’αΆα ααααΌαααΆαα αααααα αααα»αααααααα α αΎααααααααααΆααααΆααααΊ "ααααΈ"α
0123456789;ΠΠ°ΡΡ;A-01;2020-03-16;1000.00
9876543210;ΠΠ΅ΡΡ;A-02;2020-03-16;666.00
0123456789;ΠΠ°ΡΡ;B-03;2020-03-16;9999.00
αααααΆααααααΌ ααΎαααΉααααα
αΌααα·ααααααααΆαααααααααααΎααααΆαααΆαααα ααα»ααααααααΌαα
αΆαααΆ - COPY
ααΆααααααΆαααααα·αααααΆα!
INSERT INTO invoice_import
VALUES
('0123456789', 'ΠΠ°ΡΡ', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'ΠΠ΅ΡΡ', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'ΠΠ°ΡΡ', 'B-03', '2020-03-16', 9999.00);
ααΆααααΌα ααΌαααΌααααααΆαα "ααΆαααΆαα" ααΆααααα ααα "ααΆααα·α" ααααααΎααααα αααα»αααααΈααααααΎα αα·ααααααααααααα α’αα·αα·ααα
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- ΠΌΠΎΠΆΠ½ΠΎ ΠΏΡΠΎΡΡΠΎ SELECT DISTINCT, Π΅ΡΠ»ΠΈ Π΄Π°Π½Π½ΡΠ΅ Π·Π°Π²Π΅Π΄ΠΎΠΌΠΎ Π½Π΅ΠΏΡΠΎΡΠΈΠ²ΠΎΡΠ΅ΡΠΈΠ²Ρ
client_inn inn
, client_name "name"
FROM
invoice_import;
ααΎααααΈααααΆααααααΈααΆαα½αααααααααΆααα’αα·αα·ααααΆαααααΉαααααΌα ααΎαααααΌαααααααα α¬αααααΎαα’ααααααααΆαααΆαααααααΆαα»ααα·αα αααααααααααΆααα ααααααα½αααΆα
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;
α αΌαααααΎαα·ααΈααΆαααααααααΎααααΆαααααααΆααΆααααααΆααα·αααααΆααΆαααΎααΆαα½αααΉαααΆαααααααααΌα αα½α - ααΎαααΉααα·αααααΎαα αα α»ααααααααΆα α¬αα»αα’αααΈααΆααα’αααα αααα»αααΆααΆαααααα αα ααΈαααααααΎαααΆαα αΌαα’αα·αα·αα "αααααααααααααααα»ααααα"α
-- ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ Π² ΡΠ°Π±Π»ΠΈΡΠ΅ ΠΈΠΌΠΏΠΎΡΡΠ° ID ΡΠΆΠ΅ ΡΡΡΠ΅ΡΡΠ²ΡΡΡΠΈΡ
Π·Π°ΠΏΠΈΡΠ΅ΠΉ
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- unique key
-- Π²ΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΎΡΡΡΡΡΡΠ²ΠΎΠ²Π°Π²ΡΠΈΠ΅ Π·Π°ΠΏΠΈΡΠΈ ΠΈ ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ ΠΈΡ
ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELECT
inn
, name
FROM
client_import
WHERE
client_id IS NULL -- Π΅ΡΠ»ΠΈ ID Π½Π΅ ΠΏΡΠΎΡΡΠ°Π²ΠΈΠ»ΡΡ
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- unique key
-- ΠΏΡΠΎΡΡΠ°Π²Π»ΡΠ΅ΠΌ ID ΠΊΠ»ΠΈΠ΅Π½ΡΠΎΠ² Ρ Π·Π°ΠΏΠΈΡΠ΅ΠΉ ΡΡΠ΅ΡΠΎΠ²
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- ΠΏΡΠΈΠΊΠ»Π°Π΄Π½ΠΎΠΉ ΠΊΠ»ΡΡ
ααΆααα·αα’αααΈααααααααΆαααΊαα
αααα»α invoice_import
α₯α‘αΌαβααΎαβααΆαβαααααβααααααβααααΆαααααα client_id
αααααΎαααΉααααα
αΌααα·ααααααααα
ααααα: www.habr.com