Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfe7fd4e

Browse files
committed
Add regression tests for REPLICA IDENTITY with dropped indexes
REPLICA IDENTITY USING INDEX behaves the same way as NOTHING if theassociated index is dropped, even if there is a primary key that couldbe used as a fallback for the changes generated. There have never beenany tests to cover such scenarios, so this commit closes the gap.Author: Michael PaquierReviewed-by: Masahiko Sawada, Rahila Syed, Euler TaveiraDiscussion:https://postgr.es/m/20200522035028.GO2355@paquier.xyz
1 parent7e45363 commitfe7fd4e

File tree

2 files changed

+101
-1
lines changed

2 files changed

+101
-1
lines changed

‎contrib/test_decoding/expected/ddl.out

Lines changed: 70 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -565,6 +565,35 @@ UPDATE table_with_unique_not_null SET data = 3 WHERE data = 2;
565565
UPDATE table_with_unique_not_null SET id = -id;
566566
UPDATE table_with_unique_not_null SET id = -id;
567567
DELETE FROM table_with_unique_not_null WHERE data = 3;
568+
-- check tables with dropped indexes used in REPLICA IDENTITY
569+
-- table with primary key
570+
CREATE TABLE table_dropped_index_with_pk (a int PRIMARY KEY, b int, c int);
571+
CREATE UNIQUE INDEX table_dropped_index_with_pk_idx
572+
ON table_dropped_index_with_pk(a);
573+
ALTER TABLE table_dropped_index_with_pk REPLICA IDENTITY
574+
USING INDEX table_dropped_index_with_pk_idx;
575+
DROP INDEX table_dropped_index_with_pk_idx;
576+
INSERT INTO table_dropped_index_with_pk VALUES (1,1,1), (2,2,2), (3,3,3);
577+
UPDATE table_dropped_index_with_pk SET a = 4 WHERE a = 1;
578+
UPDATE table_dropped_index_with_pk SET b = 5 WHERE a = 2;
579+
UPDATE table_dropped_index_with_pk SET b = 6, c = 7 WHERE a = 3;
580+
DELETE FROM table_dropped_index_with_pk WHERE b = 1;
581+
DELETE FROM table_dropped_index_with_pk WHERE a = 3;
582+
DROP TABLE table_dropped_index_with_pk;
583+
-- table without primary key
584+
CREATE TABLE table_dropped_index_no_pk (a int NOT NULL, b int, c int);
585+
CREATE UNIQUE INDEX table_dropped_index_no_pk_idx
586+
ON table_dropped_index_no_pk(a);
587+
ALTER TABLE table_dropped_index_no_pk REPLICA IDENTITY
588+
USING INDEX table_dropped_index_no_pk_idx;
589+
DROP INDEX table_dropped_index_no_pk_idx;
590+
INSERT INTO table_dropped_index_no_pk VALUES (1,1,1), (2,2,2), (3,3,3);
591+
UPDATE table_dropped_index_no_pk SET a = 4 WHERE a = 1;
592+
UPDATE table_dropped_index_no_pk SET b = 5 WHERE a = 2;
593+
UPDATE table_dropped_index_no_pk SET b = 6, c = 7 WHERE a = 3;
594+
DELETE FROM table_dropped_index_no_pk WHERE b = 1;
595+
DELETE FROM table_dropped_index_no_pk WHERE a = 3;
596+
DROP TABLE table_dropped_index_no_pk;
568597
-- check toast support
569598
BEGIN;
570599
CREATE SEQUENCE toasttable_rand_seq START 79 INCREMENT 1499; -- portable "random"
@@ -682,6 +711,46 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
682711
table public.table_with_unique_not_null: DELETE: id[integer]:4
683712
COMMIT
684713
BEGIN
714+
table public.table_dropped_index_with_pk: INSERT: a[integer]:1 b[integer]:1 c[integer]:1
715+
table public.table_dropped_index_with_pk: INSERT: a[integer]:2 b[integer]:2 c[integer]:2
716+
table public.table_dropped_index_with_pk: INSERT: a[integer]:3 b[integer]:3 c[integer]:3
717+
COMMIT
718+
BEGIN
719+
table public.table_dropped_index_with_pk: UPDATE: a[integer]:4 b[integer]:1 c[integer]:1
720+
COMMIT
721+
BEGIN
722+
table public.table_dropped_index_with_pk: UPDATE: a[integer]:2 b[integer]:5 c[integer]:2
723+
COMMIT
724+
BEGIN
725+
table public.table_dropped_index_with_pk: UPDATE: a[integer]:3 b[integer]:6 c[integer]:7
726+
COMMIT
727+
BEGIN
728+
table public.table_dropped_index_with_pk: DELETE: (no-tuple-data)
729+
COMMIT
730+
BEGIN
731+
table public.table_dropped_index_with_pk: DELETE: (no-tuple-data)
732+
COMMIT
733+
BEGIN
734+
table public.table_dropped_index_no_pk: INSERT: a[integer]:1 b[integer]:1 c[integer]:1
735+
table public.table_dropped_index_no_pk: INSERT: a[integer]:2 b[integer]:2 c[integer]:2
736+
table public.table_dropped_index_no_pk: INSERT: a[integer]:3 b[integer]:3 c[integer]:3
737+
COMMIT
738+
BEGIN
739+
table public.table_dropped_index_no_pk: UPDATE: a[integer]:4 b[integer]:1 c[integer]:1
740+
COMMIT
741+
BEGIN
742+
table public.table_dropped_index_no_pk: UPDATE: a[integer]:2 b[integer]:5 c[integer]:2
743+
COMMIT
744+
BEGIN
745+
table public.table_dropped_index_no_pk: UPDATE: a[integer]:3 b[integer]:6 c[integer]:7
746+
COMMIT
747+
BEGIN
748+
table public.table_dropped_index_no_pk: DELETE: (no-tuple-data)
749+
COMMIT
750+
BEGIN
751+
table public.table_dropped_index_no_pk: DELETE: (no-tuple-data)
752+
COMMIT
753+
BEGIN
685754
table public.toasttable: INSERT: id[integer]:1 toasted_col1[text]:'12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000' rand1[double precision]:79 toasted_col2[text]:null rand2[double precision]:1578
686755
COMMIT
687756
BEGIN
@@ -690,7 +759,7 @@ SELECT data FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'inc
690759
BEGIN
691760
table public.toasttable: UPDATE: id[integer]:1 toasted_col1[text]:'12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562156315641565156615671568156915701571157215731574157515761577157815791580158115821583158415851586158715881589159015911592159315941595159615971598159916001601160216031604160516061607160816091610161116121613161416151616161716181619162016211622162316241625162616271628162916301631163216331634163516361637163816391640164116421643164416451646164716481649165016511652165316541655165616571658165916601661166216631664166516661667166816691670167116721673167416751676167716781679168016811682168316841685168616871688168916901691169216931694169516961697169816991700170117021703170417051706170717081709171017111712171317141715171617171718171917201721172217231724172517261727172817291730173117321733173417351736173717381739174017411742174317441745174617471748174917501751175217531754175517561757175817591760176117621763176417651766176717681769177017711772177317741775177617771778177917801781178217831784178517861787178817891790179117921793179417951796179717981799180018011802180318041805180618071808180918101811181218131814181518161817181818191820182118221823182418251826182718281829183018311832183318341835183618371838183918401841184218431844184518461847184818491850185118521853185418551856185718581859186018611862186318641865186618671868186918701871187218731874187518761877187818791880188118821883188418851886188718881889189018911892189318941895189618971898189919001901190219031904190519061907190819091910191119121913191419151916191719181919192019211922192319241925192619271928192919301931193219331934193519361937193819391940194119421943194419451946194719481949195019511952195319541955195619571958195919601961196219631964196519661967196819691970197119721973197419751976197719781979198019811982198319841985198619871988198919901991199219931994199519961997199819992000' rand1[double precision]:79 toasted_col2[text]:null rand2[double precision]:1578
692761
COMMIT
693-
(103 rows)
762+
(143 rows)
694763

695764
INSERT INTO toasttable(toasted_col1) SELECT string_agg(g.i::text, '') FROM generate_series(1, 2000) g(i);
696765
-- update of second column, first column unchanged

‎contrib/test_decoding/sql/ddl.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -345,6 +345,37 @@ UPDATE table_with_unique_not_null SET id = -id;
345345
UPDATE table_with_unique_not_nullSET id=-id;
346346
DELETEFROM table_with_unique_not_nullWHERE data=3;
347347

348+
-- check tables with dropped indexes used in REPLICA IDENTITY
349+
-- table with primary key
350+
CREATETABLEtable_dropped_index_with_pk (aintPRIMARY KEY, bint, cint);
351+
CREATEUNIQUE INDEXtable_dropped_index_with_pk_idx
352+
ON table_dropped_index_with_pk(a);
353+
ALTERTABLE table_dropped_index_with_pk REPLICA IDENTITY
354+
USING INDEX table_dropped_index_with_pk_idx;
355+
DROPINDEX table_dropped_index_with_pk_idx;
356+
INSERT INTO table_dropped_index_with_pkVALUES (1,1,1), (2,2,2), (3,3,3);
357+
UPDATE table_dropped_index_with_pkSET a=4WHERE a=1;
358+
UPDATE table_dropped_index_with_pkSET b=5WHERE a=2;
359+
UPDATE table_dropped_index_with_pkSET b=6, c=7WHERE a=3;
360+
DELETEFROM table_dropped_index_with_pkWHERE b=1;
361+
DELETEFROM table_dropped_index_with_pkWHERE a=3;
362+
DROPTABLE table_dropped_index_with_pk;
363+
364+
-- table without primary key
365+
CREATETABLEtable_dropped_index_no_pk (aintNOT NULL, bint, cint);
366+
CREATEUNIQUE INDEXtable_dropped_index_no_pk_idx
367+
ON table_dropped_index_no_pk(a);
368+
ALTERTABLE table_dropped_index_no_pk REPLICA IDENTITY
369+
USING INDEX table_dropped_index_no_pk_idx;
370+
DROPINDEX table_dropped_index_no_pk_idx;
371+
INSERT INTO table_dropped_index_no_pkVALUES (1,1,1), (2,2,2), (3,3,3);
372+
UPDATE table_dropped_index_no_pkSET a=4WHERE a=1;
373+
UPDATE table_dropped_index_no_pkSET b=5WHERE a=2;
374+
UPDATE table_dropped_index_no_pkSET b=6, c=7WHERE a=3;
375+
DELETEFROM table_dropped_index_no_pkWHERE b=1;
376+
DELETEFROM table_dropped_index_no_pkWHERE a=3;
377+
DROPTABLE table_dropped_index_no_pk;
378+
348379
-- check toast support
349380
BEGIN;
350381
CREATESEQUENCEtoasttable_rand_seq START79 INCREMENT1499;-- portable "random"

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp