Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 75DDE9F9FE1 for ; Tue, 19 Dec 2006 11:43:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 28159-04 for ; Tue, 19 Dec 2006 11:43:15 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.235]) by postgresql.org (Postfix) with ESMTP id 38DB39F9357 for ; Tue, 19 Dec 2006 11:43:15 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so1750290wxd for ; Tue, 19 Dec 2006 07:43:13 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=ibGrWLpgCi3ckPVTXiyptNLiJ+bOCv3uTXiD7HB6dhMthArgysuF8JJBAB+iNjC5NbusNotRWbl2lRPSHgt2AtsPwlvtnB7u/IihlKAbNRSdgpe7zpnRuO/f7xJZdoCdQoJSHOgybUFB2FGUjpxiSDXZQNVjsV4dtxNniUBj8cE= Received: by 10.70.73.12 with SMTP id v12mr10045204wxa.1166542993555; Tue, 19 Dec 2006 07:43:13 -0800 (PST) Received: by 10.70.108.15 with HTTP; Tue, 19 Dec 2006 07:43:13 -0800 (PST) Message-ID: <357fa7590612190743lc9201fey6519737114881080@mail.gmail.com> Date: Tue, 19 Dec 2006 10:43:13 -0500 From: "Steven Flatt" To: "Tom Lane" Subject: Re: Insertion to temp table deteriorating over time Cc: pgsql-performance@postgresql.org In-Reply-To: <17718.1166515736@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6733_28540266.1166542993482" References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com> <12508.1166206006@sss.pgh.pa.us> <357fa7590612151026t4ae033f6w5367cac9d0b67064@mail.gmail.com> <13947.1166209788@sss.pgh.pa.us> <357fa7590612151121x5f95102fj21a96b1bdc7eb002@mail.gmail.com> <20792.1166211026@sss.pgh.pa.us> <357fa7590612180951q3da4c840l27caa82475444cfb@mail.gmail.com> <19298.1166464855@sss.pgh.pa.us> <357fa7590612181506r69a17e37x271623007f27c877@mail.gmail.com> <17718.1166515736@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/474 X-Sequence-Number: 22530 ------=_Part_6733_28540266.1166542993482 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 12/19/06, Tom Lane wrote: > > I still can't reproduce this. Using 7.4 branch tip, I did > > create temp table foo(f1 varchar); > create table nottemp(f1 varchar); > \timing > insert into foo select stringu1 from tenk1 limit 100; insert into nottemp > select * from foo; truncate foo; > insert into foo select stringu1 from tenk1 limit 100; insert into nottemp > select * from foo; truncate foo; > ... repeat several thousand times ... I can't reproduce the problem that way either (or when using a server-side PLpgSQL function to do similar). It looks like you have to go through an ODBC connection, with the looping done on the client side. Each individual insert to the temp table needs to be sent over the connection and this is what degrades over time. I can reproduce on 7.4.6 and 8.1.4. I have a small C program to do this which I can send you offline if you're interested. > Now the varchar columns that end up in the perm view come from the tbl > > table, but in tbl, they are defined as varchar(40). Somehow the 40 > limit is > > lost when constructing the view. > > Yeah, this is a known issue with UNIONs not preserving the length info > --- which is not entirely unreasonable: what will you do with varchar(40) > union varchar(50)? There's a hack in place as of 8.2 to keep the > length if all the union arms have the same length. I guess it comes down to what your philosophy is on this. You might just disallow unions when the data types do not match (varchar(40) != varchar(50)). But it might come down to what's best for your application. I tend to think that when the unioned types do match, the type should be preserved in the inheriting view (as done by the "hack" in 8.2). Thanks again for all your help. Steve ------=_Part_6733_28540266.1166542993482 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 12/19/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I still can't reproduce this.  Using 7.4 branch tip, I did

create temp table foo(f1 varchar);
create table nottemp(f1 varchar);
\timing
insert into foo select stringu1 from tenk1 limit 100; insert into nottemp select * from foo; truncate foo;
insert into foo select stringu1 from tenk1 limit 100; insert into nottemp select * from foo; truncate foo;
... repeat several thousand times ...
 
I can't reproduce the problem that way either (or when using a server-side PLpgSQL function to do similar).  It looks like you have to go through an ODBC connection, with the looping done on the client side.  Each individual insert to the temp table needs to be sent over the connection and this is what degrades over time.  I can reproduce on 7.4.6 and 8.1.4.  I have a small C program to do this which I can send you offline if you're interested.
 

> Now the varchar columns that end up in the perm view come from the tbl
> table, but in tbl, they are defined as varchar(40).  Somehow the 40 limit is
> lost when constructing the view.

Yeah, this is a known issue with UNIONs not preserving the length info
--- which is not entirely unreasonable: what will you do with varchar(40)
union varchar(50)?  There's a hack in place as of 8.2 to keep the
length if all the union arms have the same length.
 
I guess it comes down to what your philosophy is on this.  You might just disallow unions when the data types do not match (varchar(40) != varchar(50)).  But it might come down to what's best for your application.  I tend to think that when the unioned types do match, the type should be preserved in the inheriting view (as done by the "hack" in 8.2).

Thanks again for all your help.
Steve
 
------=_Part_6733_28540266.1166542993482--