pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedUnlimited memory consumption with long-lived connection
7+ messages / 4 participants
[nested] [flat]
* Unlimited memory consumption with long-lived connection
@ 2023-02-21 12:57 Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 1 reply; 7+ messages in thread
From: Duncan Sands @ 2023-02-21 12:57 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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()
* Re: Unlimited memory consumption with long-lived connection
@ 2023-02-21 14:32 Heikki Linnakangas <hlinnaka@iki.fi>
parent: Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2023-02-21 14:32 UTC (permalink / raw)
To: Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
On 21/02/2023 14:57, Duncan Sands wrote:
> 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.
I can reproduce this on my laptop. When I turn 'jit=off', or disable JIT
inlining with 'jit_inline_above_cost = -1', the leak stops, or at least
gets small enough that I don't readily see it with 'top' anymore. So it
seems to be related to LLVM inlining. I'm using LLVM and clang 14.
To track down the leak, I started postgres server with Valgrind, and let
the script run for 40 iterations with Valgrind. It did report some leaks
from LLVM inline functions (attached), but I'm not very familiar with
this code so I'm not sure which ones might be genuine leaks or how to
fix them.
- Heikki
Attachments:
valgrind-inline-leak.txt (text/plain, 125.8K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unlimited memory consumption with long-lived connection
@ 2023-02-22 14:53 Justin Pryzby <pryzby@telsasoft.com>
parent: Heikki Linnakangas <hlinnaka@iki.fi>
0 siblings, 2 replies; 7+ messages in thread
From: Justin Pryzby @ 2023-02-22 14:53 UTC (permalink / raw)
To: Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
On Tue, Feb 21, 2023 at 04:32:02PM +0200, Heikki Linnakangas wrote:
> On 21/02/2023 14:57, Duncan Sands wrote:
> > 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.
It sounds like the same as the issue here:
https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items
There are patches proposed here, which fixed the issue for me.
But I've always been suspicious that there may be a 2nd, undiagnosed
issue lurking behind this one...
https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unlimited memory consumption with long-lived connection
@ 2023-02-22 17:10 Duncan Sands <duncan.sands@deepbluecap.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 7+ messages in thread
From: Duncan Sands @ 2023-02-22 17:10 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; Heikki Linnakangas <hlinnaka@iki.fi>; +Cc: pgsql-bugs@lists.postgresql.org, Andres Freund <andres@anarazel.de>
Hi Justin,
>>> 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.
>
> It sounds like the same as the issue here:
> https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items
I agree that it is likely the same issue.
> There are patches proposed here, which fixed the issue for me.
> But I've always been suspicious that there may be a 2nd, undiagnosed
> issue lurking behind this one...
> https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de
Thanks. Since those patches are just a workaround, and I already worked around
it, I don't plan to give them a whirl. On the other hand, if Andres ever gets
round to implementing approach (1) from
https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7@alap3.anarazel.de
then I'd be happy to give it a whirl. I'm rusty now but I used to be quite
expert in LLVM, so I might even be able to do some patch review.
Best wishes, Duncan.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unlimited memory consumption with long-lived connection
@ 2023-02-22 18:28 Andres Freund <andres@anarazel.de>
parent: Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 2 replies; 7+ messages in thread
From: Andres Freund @ 2023-02-22 18:28 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org
Hi,
On 2023-02-22 08:53:41 -0600, Justin Pryzby wrote:
> On Tue, Feb 21, 2023 at 04:32:02PM +0200, Heikki Linnakangas wrote:
> > On 21/02/2023 14:57, Duncan Sands wrote:
> > > 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.
>
> It sounds like the same as the issue here:
> https://wiki.postgresql.org/wiki/PostgreSQL_13_Open_Items
> There are patches proposed here, which fixed the issue for me.
> But I've always been suspicious that there may be a 2nd, undiagnosed
> issue lurking behind this one...
Any more details?
> https://www.postgresql.org/message-id/20210417021602.7dilihkdc7oblrf7%40alap3.anarazel.de
A slightly edited / rebased version is at https://postgr.es/m/20221101055132.pjjsvlkeo4stbjkq%40awork3.anarazel.de
I'd feel a lot better applying the patch if there'd be a bit more review. If
none is forthcoming, I'm somewhat inclined to just apply it to HEAD and later
decide whether we'd want to backpatch.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unlimited memory consumption with long-lived connection
@ 2023-02-22 19:12 Justin Pryzby <pryzby@telsasoft.com>
parent: Andres Freund <andres@anarazel.de>
1 sibling, 0 replies; 7+ messages in thread
From: Justin Pryzby @ 2023-02-22 19:12 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org
On Wed, Feb 22, 2023 at 10:28:11AM -0800, Andres Freund wrote:
> On 2023-02-22 08:53:41 -0600, Justin Pryzby wrote:
> > There are patches proposed here, which fixed the issue for me.
> > But I've always been suspicious that there may be a 2nd, undiagnosed
> > issue lurking behind this one...
>
> Any more details?
No. My suspicion is because the process is killed by OOM roughly every
24h, and the leak didn't seem to be big enough to cause that.
Maybe the leak is nonlinear or not completely deterministic or something
sometimes exacerbates the original issue, or maybe there's only one
issue that's 90% but not 100% understood, or maybe there's a 2nd issue
that's latent once the fix is applied for the known issue.
There's also this report:
https://www.postgresql.org/message-id/1605804350439-0.post%40n3.nabble.com
..which offers no details or reponse, and could easily be a leak in
someting other than JIT, or postgres, or not a leak at all.
--
Justin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unlimited memory consumption with long-lived connection
@ 2023-04-12 17:52 Justin Pryzby <pryzby@telsasoft.com>
parent: Andres Freund <andres@anarazel.de>
1 sibling, 0 replies; 7+ messages in thread
From: Justin Pryzby @ 2023-04-12 17:52 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Heikki Linnakangas <hlinnaka@iki.fi>; Duncan Sands <duncan.sands@deepbluecap.com>; pgsql-bugs@lists.postgresql.org
On Wed, Feb 22, 2023 at 10:28:11AM -0800, Andres Freund wrote:
> I'd feel a lot better applying the patch if there'd be a bit more review. If
> none is forthcoming, I'm somewhat inclined to just apply it to HEAD and later
> decide whether we'd want to backpatch.
Previously I ran the patches in production. Not sure what else I could
do, but it seems important to do something.
--
Justin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-04-12 17:52 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-21 12:57 Unlimited memory consumption with long-lived connection Duncan Sands <duncan.sands@deepbluecap.com>
2023-02-21 14:32 ` Heikki Linnakangas <hlinnaka@iki.fi>
2023-02-22 14:53 ` Justin Pryzby <pryzby@telsasoft.com>
2023-02-22 17:10 ` Duncan Sands <duncan.sands@deepbluecap.com>
2023-02-22 18:28 ` Andres Freund <andres@anarazel.de>
2023-02-22 19:12 ` Justin Pryzby <pryzby@telsasoft.com>
2023-04-12 17:52 ` Justin Pryzby <pryzby@telsasoft.com>
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