
	Hello,

	I recently switched to 7.4 beta 1, one query that used to be
corectly executed uder 7.3.3 albeit slowly now abnormaly ends when the
backend can't get more memory when it ate about 480 MB of swap space.
I suspect that this behavior is the result of a 7.4 beta 1 bug but I
wanted to be sure it is one before posting a report on pgsql-bugs.

	That query operates on three tables:

==============================================================================
db=> \d movies
                              Table "public.movies"
   Column   |       Type        |                    Modifiers                    
------------+-------------------+-------------------------------------------------
 id         | bigint            | not null default nextval('movies_id_seq'::text)
 title      | character varying | not null
 orig_title | character varying | not null
 year       | smallint          | 
 year_end   | smallint          | 
Indexes:
    "movies_id_idx" unique, btree (id)
    "movies_title_idx" unique, btree (title)
    "movies_orig_title_idx" btree (orig_title)
    "movies_year_idx" btree ("year")
Check constraints:
    "movies_year" CHECK (("year" >= 1888) OR ("year" IS NULL))
    "$1" CHECK ((year_end >= 1888) OR (year_end IS NULL))

db=> \d raw_atitles
                            Table "public.raw_atitles"
   Column   |        Type         |                    Modifiers                     
------------+---------------------+--------------------------------------------------
 main_title | character varying   | not null
 aka_title  | character varying   | 
 charset    | character varying   | not null default 'ISO-8859-1'::character varying
 byte_title | character varying   | not null
 attribs    | character varying[] | 
Indexes:
    "rimdb_atitles_aka_title_idx" btree (aka_title)
    "rimdb_atitles_attribs_idx" btree (attribs array_ops)
    "rimdb_atitles_main_title_idx" btree (main_title)

db=> \d atitles
            Table "public.atitles"
   Column   |        Type         | Modifiers 
------------+---------------------+-----------
 title      | character varying   | not null
 movie_id   | bigint              | not null
 attribs    | character varying[] | 
 orig_title | character varying   | not null
Indexes:
    "truc" unique, btree (movie_id, orig_title, attribs array_ops)
    "atitles_movie_id_idx" btree (movie_id)
    "atitles_title_idx" btree (title)
Foreign-key constraints:
    "$1" FOREIGN KEY (movie_id) REFERENCES movies(id)

==============================================================================

	The operation is to update the "core" atitles table with the
contents of the "raw" raw_atitles table. The query is as follows:

==============================================================================
INSERT INTO atitles (movie_id, title, attribs, orig_title)
  SELECT mo.id, trans_title(rak.aka_title), rak.attribs, rak.aka_title
  FROM movies AS mo, raw_atitles AS rak 
  WHERE mo.orig_title=rak.main_title AND 
  NOT EXISTS
    (SELECT at2.movie_id from atitles AS at2
     WHERE at2.movie_id=mo.id AND
     at2.orig_title=rak.aka_title AND 
     at2.attribs=rak.attribs);
==============================================================================

	Table sizes are 362,921 rows for movies, 152,549 for atitles,
and 160,114 for raw_atitles.

	The query plan is:

==============================================================================
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Merge Join  (cost=106998.67..1039376.63 rows=80057 width=86)
   Merge Cond: ("outer"."?column3?" = "inner"."?column4?")
   Join Filter: (NOT (subplan))
   ->  Sort  (cost=66212.69..67119.99 rows=362921 width=38)
         Sort Key: (mo.orig_title)::text
         ->  Seq Scan on movies mo  (cost=0.00..8338.21 rows=362921 width=38)
   ->  Sort  (cost=40785.99..41186.27 rows=160114 width=107)
         Sort Key: (rak.main_title)::text
         ->  Seq Scan on raw_atitles rak  (cost=0.00..5145.14 rows=160114 width=107)
   SubPlan
     ->  Index Scan using truc on atitles at2  (cost=0.00..5.80 rows=1 width=8)
           Index Cond: ((movie_id = $0) AND ((orig_title)::text = ($1)::text) AND (attribs = $2))
(12 rows)

==============================================================================

	I suspect that the backend does not comply to the sort_mem
parameter (set to the default 1024).

	So my question is: does this really looks like a bug?

	Regards.

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

Thu, 14 Aug 2003 12:21:30 +0200
Bertrand Petit <pgsql@phoe.frmug.org>
7.4 beta 1 getting out of swap
Bertrand Petit <pgsql@phoe.frmug.org> writes:
> 	I recently switched to 7.4 beta 1, one query that used to be
> corectly executed uder 7.3.3 albeit slowly now abnormaly ends when the
> backend can't get more memory when it ate about 480 MB of swap space.

Please show us the memory context size info that the backend dumps to
stderr when it reports "out of memory".

			regards, tom lane

Thu, 14 Aug 2003 07:45:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 beta 1 getting out of swap 
On Thu, Aug 14, 2003 at 07:45:46AM -0400, Tom Lane wrote:
>
> Please show us the memory context size info that the backend dumps to
> stderr when it reports "out of memory".

	Here it is:

==============================================================================
TopMemoryContext: 32792 total in 4 blocks; 14600 free (9 chunks); 18192 used
TopTransactionContext: 8192 total in 1 blocks; 8136 free (0 chunks); 56 used
DeferredTriggerXact: 2088960 total in 8 blocks; 97384 free (6 chunks); 1991576 used
MessageContext: 57344 total in 3 blocks; 7552 free (2 chunks); 49792 used
PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
PortalHeapMemory: 1024 total in 1 blocks; 936 free (0 chunks); 88 used
ExecutorState: 4556232 total in 46 blocks; 3183264 free (8303 chunks); 1372968 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExecutorState: 528474112 total in 72 blocks; 760 free (1 chunks); 528473352 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
CacheMemoryContext: 516096 total in 6 blocks; 44208 free (2 chunks); 471888 used
raw_atitles_main_title_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
raw_atitles_attribs_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
raw_atitles_aka_title_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
movies_orig_title_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
movies_year_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
movies_title_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
movies_id_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_constraint_conrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
truc: 3144 total in 2 blocks; 1848 free (0 chunks); 1296 used
atitles_title_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
atitles_movie_id_idx: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_index_indrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_shadow_usesysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_proc_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_proc_proname_args_nsp_index: 3144 total in 2 blocks; 1784 free (0 chunks); 1360 used
pg_operator_oprname_l_r_n_index: 3144 total in 2 blocks; 1784 free (0 chunks); 1360 used
pg_namespace_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_language_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_language_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_group_sysid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_group_name_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_conversion_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_conversion_default_index: 3144 total in 2 blocks; 1784 free (0 chunks); 1360 used
pg_opclass_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_opclass_am_name_nsp_index: 3144 total in 2 blocks; 1848 free (0 chunks); 1296 used
pg_cast_source_target_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_amop_opr_opc_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_shadow_usename_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_class_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amproc_opc_procnum_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_operator_oid_index: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
pg_amop_opc_strategy_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 320 free (0 chunks); 704 used
MdSmgr: 8192 total in 1 blocks; 6120 free (0 chunks); 2072 used
DynaHash: 8192 total in 1 blocks; 7064 free (0 chunks); 1128 used
DynaHashTable: 8192 total in 1 blocks; 5080 free (0 chunks); 3112 used
DynaHashTable: 8192 total in 1 blocks; 2008 free (0 chunks); 6184 used
DynaHashTable: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used
DynaHashTable: 8192 total in 1 blocks; 3520 free (0 chunks); 4672 used
DynaHashTable: 24576 total in 2 blocks; 13240 free (4 chunks); 11336 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
DynaHashTable: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ErrorContext: 8192 total in 1 blocks; 8176 free (8 chunks); 16 used
==============================================================================

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

Thu, 14 Aug 2003 16:53:11 +0200
Bertrand Petit <pgsql@phoe.frmug.org>
Re: 7.4 beta 1 getting out of swap
Bertrand Petit <pgsql@phoe.frmug.org> writes:
> On Thu, Aug 14, 2003 at 07:45:46AM -0400, Tom Lane wrote:
>> Please show us the memory context size info that the backend dumps to
>> stderr when it reports "out of memory".

> 	Here it is:

> ExecutorState: 4556232 total in 46 blocks; 3183264 free (8303 chunks); 1372968 used

That seems a bit large, but I'm not sure if it's really to worry about.

> ExecutorState: 528474112 total in 72 blocks; 760 free (1 chunks); 528473352 used

Okay, you definitely must have a memory leak in query execution.

Could you provide enough info to let someone else reproduce it?  We
don't need your data, but a schema dump (pg_dump -s) would be nice
to avoid trying to guess what causes it.

			regards, tom lane

Thu, 14 Aug 2003 11:18:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 beta 1 getting out of swap 
Bertrand Petit <elrond@phoe.frmug.org> writes:
> 	And I just got another one, much simpler, that failed the same
> way with the same data set:
> UPDATE rimdb_atitles SET aka_title=convert(byte_title,charset,'UTF8');

[ where rimdb_atitles has an index on column "attribs varchar[]" ]

Uh-huh.  Actually, any large insert or update on that table will run out
of memory, I bet.  The problem appears to be due to the newly-added
support for indexing array columns --- array_cmp() leaks memory, which
is verboten for index support operators.

At first I thought this would be an easy fix --- just rewrite array_cmp
to not depend on deconstruct_array, as array_eq already does not.
I soon found that that only reduced the speed of leakage, however.
The real problem comes from the fact that array_eq and array_cmp expect
to be able to save information across calls using flinfo->fn_extra.
While this works to some extent, the btree routines generate a new
scankey --- with a nulled fn_extra --- for every index AM call.  btree
knows to delete the scankey when it's done, but it doesn't know anything
about deleting what fn_extra points to.  (Even if it did, there is
additional leakage inside equality_oper(), which would be very difficult
to clean up directly.)

Quite aside from the memory leak problem, it's annoying to think that
the array element information will be looked up again on every btree
operation.  That seems expensive.

I can think of a number of ways we might attack this, but none seem
especially attractive ---

1. Have the index AMs create and switch into a special memory context
for each call, rather than running in the main execution context.
I am not sure this is workable at all, since the AMs tend to think they
can create data structures that will live across calls (for example a
btree lookup stack).  It'd be the most general solution, if we could
make it work.

2. Modify the index AMs so that the comparison function FmgrInfo is
preserved across a whole query.  I think this requires changes to the
index AM API (index_insert for instance has no provision for sharing
data across multiple calls).  Messy, and would likely mean an initdb.
It would probably be the fastest answer though, since lookups wouldn't
need to be done more than once per query.

3. Set up a long-lived cache internal to the array functions that can
translate element type OID to the needed lookup data, and won't leak
memory across repeated calls.  This is not the fastest or most general
solution, but it seems the most localized and safest fix.

Has anyone got some other ideas?

			regards, tom lane

Thu, 14 Aug 2003 20:17:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 beta 1 getting out of swap 
> I can think of a number of ways we might attack this, but none seem
> especially attractive ---
> 
> 1. Have the index AMs create and switch into a special memory context
> for each call, rather than running in the main execution context.
> I am not sure this is workable at all, since the AMs tend to think they
> can create data structures that will live across calls (for example a
> btree lookup stack).  It'd be the most general solution, if we could
> make it work.
> 
> 2. Modify the index AMs so that the comparison function FmgrInfo is
> preserved across a whole query.  I think this requires changes to the
> index AM API (index_insert for instance has no provision for sharing
> data across multiple calls).  Messy, and would likely mean an initdb.
> It would probably be the fastest answer though, since lookups wouldn't
> need to be done more than once per query.

#2 seems most natural in that it formalizes something that is common for
lots of index methods.

We are only in beta1, so I think we can initdb.

> 3. Set up a long-lived cache internal to the array functions that can
> translate element type OID to the needed lookup data, and won't leak
> memory across repeated calls.  This is not the fastest or most general
> solution, but it seems the most localized and safest fix.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 14 Aug 2003 20:41:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: 7.4 beta 1 getting out of swap
Tom Lane wrote:
> Bertrand Petit <elrond@phoe.frmug.org> writes:
>>	And I just got another one, much simpler, that failed the same
>>way with the same data set:
>>UPDATE rimdb_atitles SET aka_title=convert(byte_title,charset,'UTF8');
> 
> [ where rimdb_atitles has an index on column "attribs varchar[]" ]
> 
> Uh-huh.  Actually, any large insert or update on that table will run out
> of memory, I bet.  The problem appears to be due to the newly-added
> support for indexing array columns --- array_cmp() leaks memory, which
> is verboten for index support operators.

Ugh.

> I can think of a number of ways we might attack this, but none seem
> especially attractive ---
> 
> 1. Have the index AMs create and switch into a special memory context
> for each call, rather than running in the main execution context.
> I am not sure this is workable at all, since the AMs tend to think they
> can create data structures that will live across calls (for example a
> btree lookup stack).  It'd be the most general solution, if we could
> make it work.

This seems like a risky change at this point.

> 2. Modify the index AMs so that the comparison function FmgrInfo is
> preserved across a whole query.  I think this requires changes to the
> index AM API (index_insert for instance has no provision for sharing
> data across multiple calls).  Messy, and would likely mean an initdb.
> It would probably be the fastest answer though, since lookups wouldn't
> need to be done more than once per query.

This seems like a fairly big change this late in the game too.

> 3. Set up a long-lived cache internal to the array functions that can
> translate element type OID to the needed lookup data, and won't leak
> memory across repeated calls.  This is not the fastest or most general
> solution, but it seems the most localized and safest fix.
> 

I think I like #3 the best, but maybe that's because it's the one I 
think I understand the best ;-)

It seems to me that #3 is the least risky, and even if it isn't the best 
possible performance, this is the initial implementation of indexes on 
arrays, so it isn't like we're taking away something. Maybe solution #2 
is better held as a performance enhancement for 7.5.

Do you want me to take a shot at this since I created the mess?

Joe


Thu, 14 Aug 2003 19:46:20 -0700
Joe Conway <mail@joeconway.com>
Re: 7.4 beta 1 getting out of swap
Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> 3. Set up a long-lived cache internal to the array functions that can
>> translate element type OID to the needed lookup data, and won't leak
>> memory across repeated calls.  This is not the fastest or most general
>> solution, but it seems the most localized and safest fix.

> It seems to me that #3 is the least risky, and even if it isn't the best 
> possible performance, this is the initial implementation of indexes on 
> arrays, so it isn't like we're taking away something. Maybe solution #2 
> is better held as a performance enhancement for 7.5.

I'm leaning that way too.  It occurs to me also that the same cache
could be used to eliminate repeated lookups in sorting setup --- which
would not be much of a win percentagewise, compared to the sort itself,
but still it seems worth doing.

> Do you want me to take a shot at this since I created the mess?

Actually I led you down the garden path on that, IIRC --- I was the one
who insisted these lookups needed to be cached.  I'll work on fixing it.

			regards, tom lane

Fri, 15 Aug 2003 07:09:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 beta 1 getting out of swap 
> Joe Conway <mail@joeconway.com> writes:
>> Tom Lane wrote:
> 3. Set up a long-lived cache internal to the array functions that can
> translate element type OID to the needed lookup data, and won't leak
> memory across repeated calls.  This is not the fastest or most general
> solution, but it seems the most localized and safest fix.

>> It seems to me that #3 is the least risky, and even if it isn't the best 
>> possible performance, this is the initial implementation of indexes on 
>> arrays, so it isn't like we're taking away something. Maybe solution #2 
>> is better held as a performance enhancement for 7.5.

> I'm leaning that way too.  It occurs to me also that the same cache
> could be used to eliminate repeated lookups in sorting setup --- which
> would not be much of a win percentagewise, compared to the sort itself,
> but still it seems worth doing.

I've committed fixes for this, and verified that inserts/updates on an
indexed array column don't leak memory anymore.

			regards, tom lane

Sun, 17 Aug 2003 18:25:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 7.4 beta 1 getting out of swap 