pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Duncan Sands <duncan.sands@deepbluecap.com>
To: pgsql-bugs@lists.postgresql.org
Subject: Unlimited memory consumption with long-lived connection
Date: Tue, 21 Feb 2023 13:57:09 +0100
Message-ID: <a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com> (raw )
If I execute the attached python script against a postgresql 15.2 (Ubuntu
15.2-1.pgdg22.10+1) server, with the default configuration (eg shared_buffers =
128M), then the server memory usage goes up and up, apparently endlessly. After
about 10 minutes (on my laptop) pg_top shows RES memory usage for the back-end
handling the connection as greater than 1 gigabyte, which seems far too high
given the server configuration. The script just performs the same SELECT
endlessly in a loop. The memory is released when the script is killed.
Platform: Ubuntu 22.10; Linux version 5.19.0-31-generic; x86-64.
PS: The testcase was reduced from a script that kept a connection open for a
long time in order to LISTEN, and would execute a query using the same
connection every time there was a notification on the channel. It consumed ever
more memory to the point of crashing the postgresql server. Changing the script
to perform the query using a new short-lived connection was an effective workaround.
Attachments:
mem.py (text/x-python, 33.9K) download
import psycopg2
CONNECT = {'user': 'duncan', 'dbname': 'duncan'}
with psycopg2.connect(**CONNECT) as conn:
with conn.cursor() as curs:
curs.execute("""CREATE TEMP TABLE rd(rel_id int, depends_id int);
INSERT INTO rd VALUES
(18148,17853),
(244428946,235943182),
(12702512,17250),
(286554898,286477957),
(237187718,237187698),
(149643401,148477834),
(79524917,57024841),
(242214487,236582291),
(234802283,1014296),
(237520389,237483575),
(239117342,51731863),
(237187698,60920396),
(167968823,167740068),
(17753,1213024),
(151606,149381),
(225066148,244428854),
(236600409,236600228),
(12201,12196),
(244428802,235487401),
(237822703,286984509),
(285665272,286976086),
(1173597,17288),
(235487814,120819438),
(286477952,70701465),
(94045,190097157),
(277474404,277473781),
(13909955,75315),
(235881172,235876901),
(238263027,167744231),
(13442,13437),
(238094596,238086061),
(242209055,242166704),
(237531210,17288),
(237857268,235506991),
(1906909,1906901),
(287544995,233568530),
(236836878,236836869),
(255271519,255267800),
(237795778,239105958),
(236706073,17250),
(232105555,232105525),
(1176233,1176229),
(235976044,226552579),
(235528445,239227739),
(1121066,244428808),
(1359381,1359392),
(261844877,260251652),
(239062840,51731906),
(237797300,237797001),
(255271519,236407515),
(1236694,17250),
(235953791,235952352),
(288296645,55918),
(13433,13428),
(236713908,236613686),
(238287087,238287062),
(138521904,149643401),
(237129009,235234426),
(235764835,234488164),
(158673485,158058371),
(235206654,235202977),
(1905425,1152899),
(236713913,236713343),
(242209055,242175265),
(235502401,235502377),
(236265629,236265092),
(25130148,17951),
(239221214,236676034),
(17676,17667),
(238282057,239105958),
(232314758,232311521),
(1906909,2094042),
(1061439,1061424),
(235520766,235506991),
(281773795,281773760),
(76198109,169987185),
(244428756,236613686),
(231168043,167989115),
(235535625,235534960),
(277376722,277375234),
(16647885,17453),
(275877752,244428820),
(30628319,30628167),
(194755341,244428854),
(275839090,275839021),
(286973513,56646881),
(17416,17410),
(1236694,55918),
(237129009,85377880),
(239028018,239027950),
(243410665,502087),
(17695,17692),
(237665476,286985374),
(236582098,236581953),
(238094596,238090671),
(236712833,225069513),
(235943134,239072108),
(236717677,55918),
(237392373,234161080),
(1916445,1916434),
(237796974,235506991),
(239221214,1239173),
(190097157,244428794),
(256669930,244428862),
(170507820,8293202),
(225066148,167123684),
(239080988,239227739),
(235655138,286984509),
(17676,236620433),
(13524,13515),
(238146755,60920396),
(236676913,236676770),
(138521904,8293167),
(275663570,59928475),
(237809560,236609045),
(255270979,235746870),
(148643426,148633613),
(236613838,237531572),
(236705621,225069513),
(25130143,25130148),
(234266369,234266285),
(13498,13490),
(237590512,16023877),
(237809560,237809555),
(236152107,236152013),
(239221214,1213024),
(866320,17288),
(235913821,235668180),
(268521944,234161080),
(255271351,255270979),
(244428750,236605702),
(236837433,236836903),
(237801739,17921),
(275663570,238288771),
(286973513,235234426),
(236398332,236397208),
(285655565,286976086),
(166493175,51731686),
(263266867,263205897),
(249728879,249728800),
(255271804,255268198),
(243413645,268275983),
(18522,17746),
(170620111,170507391),
(236676746,225069513),
(256669930,244428922),
(18148,17844),
(239519820,236675043),
(235506741,72683086),
(12179,12170),
(276603140,236888835),
(234849449,231168043),
(1906909,1905425),
(238094596,234150183),
(170620696,170620111),
(12157,12147),
(236675043,268275983),
(236620433,236613686),
(13224,13219),
(1373182,60583970),
(167892839,17676),
(237181693,239123354),
(235977722,235977481),
(237182063,237182042),
(256669930,244428808),
(237797001,237796974),
(237398114,234161080),
(236827680,236823805),
(17331,17324),
(235977481,235976184),
(102109544,102107907),
(236594338,57024841),
(235232765,235206654),
(243410665,238094596),
(285491845,101140712),
(1174872,1174855),
(243410665,75315),
(236620433,17921),
(237520260,237484239),
(236722150,236721752),
(256669930,244428848),
(12152,12142),
(242886331,242886190),
(1213874,75315),
(236702554,236398332),
(135006770,55918),
(239070096,239062840),
(1173597,1173568),
(236680394,236679801),
(236557747,236557689),
(287549448,287549051),
(275663570,244428794),
(235502241,235501751),
(866320,17453),
(17489,17485),
(188485894,72683086),
(235232765,234265061),
(255271437,255271432),
(236827675,239227739),
(276603140,17951),
(235953498,239072108),
(1702683,239221313),
(286562757,286976086),
(244428934,237522443),
(287705551,287705181),
(283438187,235236941),
(225219107,225069513),
(17753,17750),
(277473781,17951),
(285652665,286976086),
(236721752,236721515),
(234875752,234849421),
(244428862,235535747),
(60583970,244428778),
(239082084,239082023),
(236706078,236706073),
(235498178,239072108),
(1015170,243413645),
(237182042,237181693),
(277379726,277379716),
(236722214,236722150),
(239221214,1726468),
(283991155,1239173),
(194755341,1110878),
(237142233,237129591),
(236717961,236717677),
(268294099,60444),
(285652665,286985374),
(170507820,148633613),
(256669930,244428854),
(239098148,55918),
(237581279,239123354),
(255873398,255872864),
(236366340,235679489),
(13476,13471),
(235207151,232259597),
(235508111,268275983),
(285491840,281671599),
(236717677,236609011),
(275877752,244428768),
(286559240,235679489),
(236613738,236687746),
(235502241,235502237),
(237521175,239123354),
(1072005,721960),
(135006770,72683086),
(236406415,236406410),
(236358362,286554135),
(238282921,238281578),
(12262,12257),
(225066148,244428808),
(236679801,225512569),
(275663570,244428778),
(238000681,236613848),
(170507820,8293175),
(242214963,242214487),
(17426,17420),
(237857412,17453),
(235506741,75416),
(236687622,270704973),
(148524482,149643401),
(1072117,1072005),
(256669930,244428744),
(237129009,286976086),
(235198224,234691861),
(239227739,239227722),
(234849781,234848921),
(235468535,235474533),
(237128603,237129009),
(94801208,13909955),
(1015211,618803),
(17406043,237801739),
(1173503,1239173),
(237857412,243412470),
(235976184,235976044),
(235667794,235667409),
(286984996,72695171),
(239070446,239070096),
(2870430,2869901),
(256669930,244428732),
(256669930,244428836),
(17489,17481),
(194755341,18491),
(236605658,236605528),
(244428878,236613838),
(236680394,236680384),
(236594426,236593679),
(237581583,237665395),
(17406043,237597073),
(13909955,72683086),
(232311521,232311513),
(255271432,237522369),
(238287087,238286387),
(237581583,236609053),
(236675043,1174960),
(275877752,244428910),
(237795778,239072108),
(234265061,232105555),
(18500,17707),
(237483575,236398332),
(236397208,60920396),
(236594418,236557768),
(235679489,235655138),
(236403896,235679489),
(1148937,236620433),
(237796974,16647885),
(138521904,8293182),
(287704178,233573961),
(1173503,236673497),
(276603140,72683086),
(188485894,187553247),
(236406415,236403896),
(2869901,17453),
(1121066,243413645),
(244428866,236613801),
(235499893,235496462),
(235499893,17921),
(79681709,236613838),
(237129009,286985374),
(170547794,170507820),
(235206654,17862),
(238282985,238282921),
(129934031,129929801),
(268311607,268295148),
(194755341,167123684),
(236713913,236609003),
(239064283,239063074),
(30628319,181174468),
(1173503,1173597),
(236557747,237128603),
(237182042,237182037),
(244428764,236613706),
(188485923,187588156),
(277474834,277474404),
(17707,17700),
(236594422,236594418),
(170507820,5811013),
(236152013,236151861),
(244428814,236557768),
(285667005,286976086),
(236265092,268275983),
(268523013,237398114),
(234849781,234848357),
(275875341,275875104),
(275564450,275551517),
(188485894,75416),
(244428830,237187718),
(244428910,236379229),
(188485923,188485894),
(244428768,236407515),
(2094232,2094219),
(235913821,235764835),
(275875341,286550663),
(236594422,236581906),
(237857412,237857268),
(237476679,237476655),
(239082023,239080988),
(1726468,164180203),
(286554135,286477957),
(235527826,235520766),
(237857268,236613816),
(174412599,242885228),
(18497,79681709),
(12166,12147),
(234875752,234849257),
(235506991,235506826),
(237520413,237518912),
(283991053,166493180),
(283991155,18148),
(236600228,236600064),
(2013710,1014296),
(261844877,236594864),
(4061343,234850470),
(287715833,287531236),
(237521282,237520260),
(1174960,1174872),
(1906879,1906873),
(244428858,236613789),
(1121066,94801208),
(236151861,236147017),
(237520413,237520965),
(237142706,237142233),
(239117346,226555831),
(13507,13502),
(277376722,273600348),
(232317511,232314758),
(236613779,236722691),
(268293842,268294099),
(255271994,268275983),
(237665395,286984509),
(235534960,235534956),
(239123354,225069494),
(236406410,239105958),
(151606,150692),
(237591307,237590512),
(235745997,235745988),
(239098148,239227739),
(1702564,17740),
(235506741,235506390),
(236706004,239123354),
(239517999,235943138),
(238282921,238282057),
(235506741,285665272),
(236407515,236407464),
(287705621,287705551),
(286563806,235679489),
(237796974,75416),
(236147017,244878190),
(281773760,277339451),
(1916743,1916448),
(237477053,237476679),
(261844877,242227716),
(1371032,236620433),
(94801208,13910299),
(244428884,236130070),
(242887759,174412599),
(287549051,232333785),
(239105958,70701465),
(239123354,60591631),
(18365,17416),
(286550663,235679489),
(242227716,242214963),
(236618999,237597073),
(243410665,235234426),
(239106949,239106826),
(1173159,1173155),
(277473781,273600348),
(168651717,165479291),
(239123354,60920396),
(148808053,148806544),
(236581906,236557768),
(238135662,236397208),
(244428874,236613827),
(236836869,235506991),
(190097157,244428902),
(237393376,234161080),
(238287062,239123354),
(236150622,236151289),
(94801208,72683086),
(275877752,244428760),
(255872864,1023473),
(138521904,5811005),
(236836869,102402584),
(721960,100091),
(236557747,236557742),
(236714162,236713913),
(1248701,268588210),
(255270979,268275983),
(244428798,236613769),
(261844877,17951),
(236613801,237797300),
(286550663,2730166),
(138521904,8293202),
(275841029,275839090),
(244428892,235955277),
(244428942,237522277),
(236594338,236557768),
(239063074,239227739),
(229314190,229314102),
(17676,164180203),
(244428870,236613816),
(1906553,1906549),
(170507820,149643401),
(287547476,233573961),
(13471,13228),
(1702557,1702426),
(237129591,237129009),
(235528497,235527826),
(235745997,931054),
(237809560,75416),
(235881172,72683086),
(1213024,1212987),
(242888791,2338473),
(285664776,286985374),
(1702683,239222116),
(236618999,164172738),
(236129813,236129601),
(236581953,236581906),
(235206654,235198224),
(236557742,239107314),
(236358362,236356969),
(235915867,235913821),
(18372,17426),
(235534956,166493180),
(236801780,236801636),
(231056942,231055460),
(281774126,281773795),
(236581906,57024841),
(237484239,236398332),
(255271994,236407515),
(1248885,1248877),
(85609505,85406765),
(237857412,75315),
(1174855,17453),
(237581583,237581554),
(168657268,168651717),
(281773760,236688204),
(287703387,232333785),
(235487819,235487814),
(1183284,237837884),
(235913821,72683086),
(236594864,236594515),
(225219107,225512569),
(238135662,235679489),
(2870451,2870430),
(235498182,235497120),
(236692843,236398332),
(287705665,287705621),
(237531572,239519188),
(287705551,287703387),
(237520413,237484594),
(234802283,619793),
(275663570,244428798),
(235534960,235534912),
(275877752,244428878),
(236706004,65117833),
(287761367,287517323),
(235234426,283438187),
(283438187,60444),
(286975855,286973513),
(235487819,235468535),
(249729118,249728879),
(238286978,225512569),
(236376571,236367251),
(234848921,1015260),
(12702512,55918),
(275516305,256814980),
(236675043,1176233),
(237845900,235876901),
(17406043,16023853),
(285667925,286985374),
(236680797,236680394),
(1173503,17453),
(1172721,1172716),
(229314102,229311597),
(244428772,236613716),
(239117351,239117346),
(234882200,234875752),
(261904504,258817964),
(275551517,256814980),
(244428760,236613696),
(13327,13308),
(263266137,263205706),
(239106949,56958312),
(276603140,245329),
(150340423,181174468),
(243410665,85609505),
(129934031,129930371),
(236827675,235746870),
(244428950,236613887),
(256669930,249729118),
(255271804,17921),
(239519188,237531210),
(149643401,148483857),
(236378158,236376571),
(237857268,1183258),
(242892360,242885228),
(263205063,275564450),
(236147017,234266369),
(244428922,236152107),
(148808053,236613769),
(235745997,286562757),
(275551517,258817964),
(239227722,101140712),
(234849781,255271351),
(256669930,244428896),
(237822703,286985374),
(2094210,1152882),
(235668180,235667794),
(287549051,244428892),
(236129601,235915867),
(236676746,225512569),
(237520260,237521175),
(287761367,287759562),
(237520965,239123354),
(231056942,167989115),
(235502377,235502241),
(281775396,167885678),
(225066148,72683086),
(237581554,17670),
(194755341,72683086),
(235976044,235496462),
(236721752,235746870),
(283991053,165665113),
(236713638,239123354),
(287549448,287554068),
(236376546,164172738),
(244428794,236613759),
(231166463,231161309),
(288296645,288300835),
(236605702,236605658),
(256669930,244428814),
(255271804,237477484),
(277376722,277380423),
(235811266,286976086),
(239072108,72695171),
(235506826,235506741),
(17676,17670),
(286554135,235679489),
(261844877,268275983),
(235496462,235498474),
(271500952,235976109),
(255271994,243413645),
(236801636,236801610),
(236359115,236358816),
(236680178,239123354),
(17446,17440),
(275549438,275516305),
(1176229,2870451),
(255271432,268275983),
(13910299,244428824),
(1239173,60444),
(234849781,255271437),
(235876720,235876715),
(2013718,2013710),
(268294099,164180203),
(238086061,286984509),
(167885678,281774126),
(236713908,17250),
(236823805,75315),
(235876715,66783578),
(235745997,70711863),
(285661608,56646881),
(17489,17476),
(244428926,237522151),
(275663570,244428790),
(30628319,30628231),
(231167904,231161343),
(244428808,235496462),
(1239173,17256),
(283991155,17489),
(255271804,268275983),
(235943138,235942912),
(236379229,236378158),
(236827675,236359115),
(235528445,239072108),
(249728595,249727090),
(277473781,255873398),
(284002459,283982320),
(255270979,243413645),
(1173503,17288),
(2869901,1175062),
(255271804,235746870),
(255271809,255271804),
(238000681,268275983),
(263311635,263207871),
(239519646,236618999),
(286976086,286975855),
(236581953,236557328),
(237809860,237809560),
(236827675,75315),
(236836903,236609079),
(281773760,283991053),
(234265061,234260940),
(234260940,232104609),
(236672701,237983646),
(2870451,1175062),
(256669930,244428870),
(1174931,1176271),
(235529214,235528497),
(239519720,236608952),
(55799731,30628175),
(256669930,72683086),
(237522443,237521632),
(236676034,17921),
(30628319,30628175),
(268588210,167889934),
(277473781,72683086),
(236675068,239519820),
(1173155,236676034),
(237857412,1173597),
(229314102,229311038),
(235943182,239517999),
(235667409,235665454),
(237797001,286559240),
(285667005,286985374),
(237857268,72683086),
(239027950,57024708),
(225512569,60920396),
(239104106,239104101),
(236594515,236594426),
(287553200,287551496),
(237520260,237518812),
(237520990,239123354),
(236680394,236608977),
(1905425,236620433),
(285664402,286976086),
(286984509,235234426),
(237857518,237857412),
(234849421,231166463),
(235667297,239227739),
(100091,100002),
(226555831,226555827),
(275877752,238281241),
(244428916,236613877),
(13909955,13906253),
(17436,17430),
(236557763,236557747),
(234847894,1015211),
(129934031,128941184),
(18386,17446),
(1726468,17468),
(236713638,65117833),
(268522172,237393376),
(243413645,17921),
(235913821,235496462),
(13433,13308),
(268295148,268293842),
(234802283,1014415),
(243412470,72683086),
(261904504,236594864),
(255271994,237522151),
(231168043,231161316),
(3394336,1702683),
(17700,17695),
(1015211,235508111),
(236712833,225512569),
(1174931,1173503),
(255271432,17921),
(287704178,233568530),
(17753,236676034),
(13511,13502),
(236129601,236129330),
(60583970,244428794),
(261904504,242227716),
(239222116,239222106),
(236680384,236620433),
(239106949,56970932),
(237809555,239072108),
(17406043,16023613),
(244428784,236613738),
(239519646,236608944),
(236376546,17670),
(235499893,268275983),
(236827675,235955277),
(237665476,235679489),
(237797001,236609037),
(1906549,1905432),
(255019936,255014217),
(148808053,235496462),
(237857412,17288),
(287703387,244428892),
(17510,268295148),
(238288423,238287087),
(239106826,239071481),
(225228400,2094315),
(285661608,286976086),
(229225139,229225588),
(1905432,1152920),
(17695,236620433),
(236407464,236406415),
(1172716,17978),
(256669930,244428750),
(282697958,263311635),
(237182042,236609119),
(235953791,235953498),
(287547476,287545040),
(286984996,286984509),
(18152,17862),
(249728800,249728595),
(243410665,85406242),
(239070096,239064283),
(255271804,243413645),
(234847297,1015170),
(234849781,255271999),
(235534960,285664776),
(237796974,236613816),
(234848357,231056942),
(225092796,225093156),
(237857412,1239173),
(236845241,239070446),
(1702683,1702557),
(225242880,225069494),
(256669930,244428802),
(148523935,148524482),
(231056942,75315),
(244428778,236613727),
(167889934,167892839),
(239107314,239106949),
(256669930,244428764),
(188485894,235508111),
(1359392,5922673),
(135006770,55940),
(237581279,239105958),
(234802292,234802283),
(242209055,275663570),
(255872864,244428854),
(1172716,236676034),
(239063074,101511026),
(236706078,55918),
(287761367,287551496),
(255271524,255271519),
(76324167,17331),
(235487401,235487819),
(236676770,236676746),
(268312843,268293842),
(231166463,72683086),
(239072108,166493180),
(1015216,618784),
(1174855,235499893),
(135006770,134996609),
(275663570,244428756),
(277474834,277376883),
(18482,1121066),
(268523460,237392373),
(238281241,238282985),
(236706078,236608995),
(1015170,618743),
(263205063,261904974),
(148808053,268275983),
(239519820,236608960),
(13909940,72683086),
(242886331,242885228),
(148808053,17921),
(235483413,235468535),
(236721752,235668180),
(1174855,1174931),
(286984509,502087),
(235520766,102108272),
(244428854,235506991),
(283991053,235483413),
(1173503,243412470),
(236845241,239107314),
(190097157,275877752),
(243410665,286984509),
(866320,236673497),
(261904504,268275983),
(286985374,286984996),
(236397208,225069494),
(236673497,17921),
(235529283,235529214),
(13910299,243412470),
(236827675,243412470),
(237597073,17921),
(237521632,237520413),
(232311513,232259597),
(235502241,285655565),
(236613848,236837433),
(235655138,70701465),
(1906879,1904957),
(236676034,268275983),
(237809555,239104106),
(236618999,17670),
(236823805,286985374),
(237837884,236613816),
(1015216,243413645),
(237581554,236613696),
(237801739,236613801),
(12702512,12693223),
(244428744,236600409),
(244428738,236594864),
(238135082,70701465),
(275862403,275862363),
(235876715,239070446),
(286973513,284932330),
(866320,17670),
(235943138,285661608),
(236613749,236706257),
(13909940,13905815),
(231056942,243413645),
(234875752,234849449),
(271509744,271509236),
(288296645,75315),
(60583970,244428790),
(237182037,12693223),
(239098794,239098148),
(236713913,236713908),
(225242884,225242880),
(235745988,239105958),
(13538,13528),
(2338473,242886331),
(237822703,286973513),
(236827680,236827675),
(236406415,736483),
(236613816,237809860),
(237809560,237822703),
(194756255,194755341),
(235473225,235472289),
(236676034,236613706),
(235913821,235506991),
(234849781,255271809),
(234849781,234847894),
(236130070,236129813),
(239117346,239117342),
(236827675,72683086),
(226555827,51731863),
(1172716,1239173),
(271509711,271500952),
(268275911,72683086),
(236687746,236687622),
(256669930,244428892),
(1248666,1248701),
(285655565,286985374),
(1239173,68746),
(239222106,1213024),
(270704973,270703396),
(236722214,236609019),
(239104017,51731760),
(236706257,236706078),
(237476679,237470808),
(236224839,268275983),
(17406043,243413645),
(235498182,285667925),
(1906886,1906879),
(275839021,263206110),
(235483413,268275983),
(237142233,236609103),
(190097157,243412470),
(239117346,239098921),
(263205897,263205063),
(275551517,275549438),
(225066148,1023473),
(18148,17849),
(236613877,237142706),
(237796974,243412470),
(286975855,72695171),
(138521904,5811013),
(238263027,167740068),
(286562757,235679489),
(235483413,17921),
(235534960,235534721),
(12218,12209),
(17331,17317),
(55799731,181174468),
(1121066,244428870),
(236713913,55918),
(238093596,234161080),
(1121066,244428750),
(1702557,17440),
(234849257,231167904),
(244428820,236359115),
(225242880,225069513),
(237581583,237581279),
(866320,247242),
(170620656,170507399),
(286477952,286973513),
(283991155,236676034),
(12214,12209),
(236705621,225512569),
(271509236,235976184),
(235881172,244428906),
(244428888,236613848),
(277473781,245329),
(238288771,238288423),
(235955277,235954455),
(2013710,94415763),
(234849781,234848329),
(238146755,286984509),
(255271994,255268268),
(236613759,236714162),
(236613727,236680797),
(263311635,17951),
(285503601,285491845),
(236717034,236398332),
(239222106,236676034),
(236605528,236594338),
(866320,245329),
(275663570,244428926),
(236403896,236398332),
(13442,13308),
(237531210,245329),
(236613686,236619268),
(236225281,236224839),
(238005501,237983646),
(170507820,8293167),
(283991155,17453),
(235976880,235976538),
(235811266,66783578),
(285661608,286985374),
(234266285,232259597),
(256669930,168700),
(148808053,148631345),
(255271432,255264107),
(239106949,239104106),
(236376571,236366340),
(25130148,1061439),
(235954455,235953791),
(286562757,286985374),
(244428930,237522369),
(275663570,244428942),
(236706078,236705621),
(232259597,235232765),
(1906913,1906909),
(237581822,237581583),
(236594426,236594422),
(263205706,261844877),
(275848868,275841029),
(268294099,17256),
(255271994,235746870),
(148808053,236407515),
(275877541,275875806),
(12188,12183),
(234849781,234847297),
(1213024,17288),
(60583970,240073783),
(170620941,170620656),
(244428732,242227716),
(235667409,286554898),
(235508111,235506991),
(236376442,239105958),
(236688204,268275983),
(2094042,2094232),
(225066148,249729118),
(243410665,238148223),
(239519188,236609061),
(236265092,17921),
(13471,13360),
(242214487,236605702),
(236150622,236261246),
(240073783,237377888),
(255271999,255271994),
(236613887,237182063),
(237581554,236407515),
(285503601,285491840),
(255271804,236407515),
(236713913,236712833),
(236706073,55918),
(275877752,244428916),
(229225588,229199449),
(17695,17689),
(94801208,13909940),
(275663570,244428950),
(277380423,277376252),
(1905425,1152882),
(236713908,55918),
(235858841,75315),
(135006770,75315),
(18152,17866),
(249726912,244878190),
(235976184,243412470),
(235534960,235533557),
(236845241,239105958),
(236600064,236599592),
(12175,12170),
(239104101,239082084),
(235527826,102109544),
(268522029,268521944),
(241948177,165479291),
(2017754,234802292),
(236675043,1176271),
(236672701,235668180),
(244428836,235502401),
(236557768,236557763),
(239080988,101506970),
(236680394,236692843),
(236836903,236836878),
(277379716,277474834),
(235745997,72683086),
(1015170,236407515),
(236706078,236706004),
(275875740,275875341),
(238135082,286985374),
(1213874,17288),
(101511026,51731686),
(275663570,244428874),
(1371032,12693223),
(1175062,1174960),
(236717034,55918),
(242214487,236600409),
(1904957,1152908),
(275862363,263207871),
(1916434,1906553),
(235667297,239105958),
(167744231,167722934),
(256669930,244428884),
(190097157,94801208),
(284002459,181174468),
(287759562,287759270),
(13486,13481),
(286973513,502087),
(256669930,244428902),
(235506717,239072108),
(13341,13308),
(239106949,85377880),
(33296700,33295073),
(283438187,235251627),
(239098921,239098794),
(17676,17453),
(236594418,57024841),
(239027950,57024841),
(277473781,235496462),
(236613789,236801780),
(235498182,235498178),
(236613827,237581822),
(285664776,286976086),
(236613696,236672705),
(275663570,244428930),
(148633613,148641530),
(239105958,239104106),
(287547476,233568530),
(235667409,235682573),
(236717677,236717034),
(235202977,234692134),
(94801208,962314),
(235746870,235746644),
(237809555,239070446),
(287761367,287705665),
(235534912,236261246),
(236706073,236613686),
(1906549,1906541),
(173450099,12126),
(236676770,236608968),
(234266285,234265061),
(237665395,235679489),
(236702554,55918),
(284932330,284922752),
(236800164,236398332),
(231167904,235508111),
(236680384,12693223),
(235913821,237845900),
(239104101,239104017),
(235236941,234161080),
(12702512,236620433),
(239098148,65117833),
(866320,75416),
(285665272,286985374),
(236613706,236675068),
(238146755,225069494),
(1183284,1183258),
(243412470,237845900),
(287531236,287530291),
(235876720,235875643),
(255271994,17921),
(236722691,236722214),
(243412470,244428906),
(287715833,287705665),
(244878190,235955277),
(237397765,234161080),
(235876715,239072108),
(237476655,236398332),
(235473179,239072108),
(236376571,236376442),
(255872864,229198997),
(261904504,255873398),
(237531210,236613696),
(234802283,94415763),
(13528,13502),
(237520031,237518821),
(237521175,51731693),
(244428896,235468535),
(148808053,235746870),
(275663570,244428938),
(239071481,239070446),
(277473781,277462444),
(129934031,127874565),
(236397208,60591631),
(18379,17436),
(237837884,268275983),
(268275911,235858841),
(236261246,236130070),
(148641530,148808053),
(13471,13317),
(236713908,12693223),
(236706078,236702554),
(238000681,235529283),
(288296645,241689464),
(236376546,236673497),
(194755341,244428808),
(243410665,72695171),
(170507820,5811005),
(237531210,17453),
(285667925,286976086),
(2094315,2094042),
(268522934,237397765),
(236605528,236603290),
(255270979,237522443),
(277473781,268275983),
(1148937,12693223),
(241496501,17406043),
(236557742,239072108),
(286984509,234150183),
(237837884,17921),
(236827680,236609087),
(244428790,236613749),
(12014,12005),
(235506741,235506717),
(150340349,150340423),
(229225588,229198997),
(236224839,17921),
(236581906,245329),
(236406410,239123354),
(261904974,261904504),
(1726468,17453),
(235508111,17921),
(237520990,51731693),
(79681709,275877752),
(275877752,244428824),
(238000681,17921),
(236358362,236358338),
(236679801,225069513),
(236713343,236398332),
(235534956,239072108),
(17746,17740),
(1173503,1183284),
(102409239,72683086),
(286559240,286985374),
(263207871,263266137),
(236613769,236717961),
(100091,17331),
(255872864,243413645),
(255271432,243413645),
(236376571,236376546),
(235976044,235746870),
(286550663,286477957),
(25130143,236620433),
(94801208,94800640),
(235528497,235505647),
(236836878,102409239),
(236151861,236150622),
(13471,13303),
(263207871,263266867),
(239221313,239221214),
(149643401,148475185),
(237983421,1072117),
(236823805,286976086),
(2094232,2094210),
(235474533,235473225),
(236619268,239519646),
(239105958,56970932),
(102109544,72683086),
(102409239,102402027),
(239072108,56646881),
(235876901,235876720),
(281773760,283991155),
(12161,12142),
(256669930,244428842),
(236673497,236613696),
(1015260,618719),
(287549051,287547476),
(237129009,56646881),
(237129009,235746870),
(1015170,235746870),
(275877752,70711863),
(231056942,55918),
(236151861,236151708),
(170507820,8293182),
(235943138,235943134),
(256669930,75315),
(167989115,55940),
(236618999,236673497),
(235913821,235746870),
(236675043,2870430),
(263207871,277379726),
(241946032,241948177),
(234848329,1015216),
(286973513,75416),
(287530291,287517323),
(1183284,235508111),
(235528497,285667005),
(225066148,18491),
(235913821,235234426),
(1359392,148633613),
(229314102,229311280),
(242214487,245329),
(138521904,8293175),
(13533,13528),
(237522277,237521282),
(236600064,236594418),
(286554898,235679489),
(170547765,170507820),
(255872864,229311597),
(239106949,166493180),
(236672705,239519720),
(275877752,275877541),
(4061343,234882200),
(255271432,235746870),
(239519720,236672701),
(244428824,235668180),
(60583970,244428830),
(276603140,268275983),
(286562757,286477957),
(60583970,244428950),
(1183284,72683086),
(190097157,240073783),
(236261246,235234426),
(275877752,244428866),
(255270979,255263872),
(12205,12196),
(1702564,1702459),
(255271519,268275983),
(287550409,287549448),
(255271519,235746870),
(25130148,24955602),
(1176229,17453),
(235976109,235976044),
(167740068,167722934),
(236801610,239123354),
(235528497,235528445),
(276603140,255873398),
(236706073,12693223),
(235976538,235976109),
(236801636,236800164),
(55799731,55799699),
(168657268,165479291),
(275663570,244428830),
(237665395,236398332),
(237796974,236613696),
(13494,13490),
(1176271,1176267),
(236261246,72683086),
(239117346,225242884),
(13404,13308),
(243413645,243412470),
(231168043,75315),
(237182037,236613686),
(275841029,263207871),
(235535747,235535625),
(18152,17857),
(237520389,237520990),
(263311635,275848868),
(255271432,236407515),
(244878190,232333785),
(275663570,244428858),
(236594422,236594338),
(281773795,72683086),
(236224839,236130070),
(235473225,285652665),
(236801636,236609028),
(1121066,244428814),
(286477957,286477952),
(285664402,286985374),
(17406043,275877752),
(268275983,268275911),
(255270979,236407515),
(235953791,285664402),
(244428848,235529283),
(129934031,127871324),
(268313330,268311607),
(237531210,247242),
(244428842,236613779),
(239123354,239117351),
(1248877,18500),
(235876720,235811266),
(244428906,235746870),
(256669930,244428888),
(239106949,235234426),
(234849781,255271524),
(237484594,236398332),
(235534721,236721752),
(237531210,17670),
(1015211,243413645),
(286559240,286976086),
(225512569,225069494),
(1121066,244428854),
(1173597,75416),
(242209055,256669930),
(284922752,285503601),
(236613857,236827874),
(271509236,271499985),
(13909955,55918),
(235207151,235206654),
(236721752,72683086),
(244428902,236613857),
(235976044,243412470),
(231168043,72683086),
(148808053,72683086),
(235811266,286985374),
(237520389,237518830),
(237521567,237520389),
(234265061,17862),
(237797001,237795778),
(256669930,243413645),
(234850470,234849781),
(17514,17510),
(244428938,237477484),
(271500952,271499985),
(287551496,287550409),
(12192,12183),
(239106949,239028018),
(237531210,75416),
(1176267,1173503),
(13471,13278),
(236613716,236676913),
(242209055,72683086),
(236582291,236582098),
(236151289,235207151),
(236261246,235764835),
(288300835,242209055),
(288296645,72683086),
(242209055,275877752),
(237796974,236613706),
(237520031,237482850),
(237857412,236613696),
(13520,13515),
(235502237,239072108),
(235858841,168700),
(237522369,237521567),
(237522151,237521232),
(236679801,17906),
(237590512,16023613),
(2013710,1014415),
(166493180,166493175),
(255271519,17921),
(275663570,244428934),
(239027950,57022962),
(256669930,244428738),
(236721752,235496462),
(255271519,243413645),
(256669930,244428726),
(239072108,239071481),
(225065803,225066148),
(85609505,85406242),
(1213024,75416),
(149643401,5922673),
(237142233,236845241),
(236713913,236713638),
(237129009,239028018),
(13341,13336),
(94800640,94798801),
(235473225,235473179),
(286559240,286477957),
(13404,13399),
(275663570,244428772),
(231168043,55918),
(237822703,286976086),
(235746644,235745997),
(236687622,236608986),
(287715833,287551496),
(276603140,235496462),
(1702683,1702564),
(1916448,1906886),
(236147017,235535747),
(236397208,286984509),
(263311635,250430816),
(235811266,268275983),
(169987185,244428862),
(286984509,225242884),
(261904504,17951),
(236265092,236152107),
(94800640,244428854),
(138521904,148633613),
(1173503,75315),
(94415763,4061343),
(287545040,287544995),
(17600,721960),
(236680394,236680178),
(167374449,167374535),
(236358816,236358362),
(256669930,55918),
(277376883,277376722),
(1173155,18213),
(237521232,237520031),
(277376252,277375234),
(236679801,225069557),
(236688204,17921),
(277473781,236888835),
(244428726,236582291),
(275841029,263206110),
(236827874,236827680),
(2020874,2017754),
(237477484,237477053),
(287544995,287517323),
(249728595,249726912),
(2013710,619793),
(235682573,235667297),
(239222106,17750),
(256669930,244428946),
(255270979,17921),
(237983646,237983421),
(1175062,1174855),
(236688204,236613738),
(235498474,235498182),
(13327,13268),
(276603140,273600348),
(237520965,51731693),
(225093156,1906913),
(237482850,236398332),
(237597073,236613827),
(238286978,225069513),
(255271519,237522277),
(270704973,72683086),
(167374535,5922673),
(236358338,239105958),
(238287087,238286978),
(236721515,178181039),
(235745988,239072108),
(225059786,238000681),
(287703387,287704178),
(1248877,275877752)
""")
while True:
curs.execute("""
WITH RECURSIVE target_depths(rel_id, depth) AS (
SELECT d.depends_id AS rel_id,
1 AS depth
FROM rd d
UNION
SELECT bar.rel_id,
CASE
WHEN bar.inherit THEN bar.depth
ELSE bar.depth + 1
END AS depth
FROM ( SELECT rd.depends_id AS rel_id,
ad_1.depth,
NOT (EXISTS ( SELECT
FROM rd a
WHERE a.rel_id = ad_1.rel_id)) AS inherit
FROM target_depths ad_1
JOIN rd USING (rel_id)
WHERE ad_1.depth < 100) bar
)
SELECT ad.rel_id
FROM target_depths ad
""")
curs.fetchall()
view thread (7+ messages) latest in thread
Message-ID: <a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com>
Permalink: ../a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com/
Also on: postgresql.org/message-id/a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: duncan.sands@deepbluecap.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Unlimited memory consumption with long-lived connection
In-Reply-To: <a53cacb0-8835-57d6-31e4-4c5ef196de1a@deepbluecap.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox