Re: Insertion to temp table deteriorating over time - Mailing list pgsql-performance

From Steven Flatt
Subject Re: Insertion to temp table deteriorating over time
Date
Msg-id 357fa7590612190743lc9201fey6519737114881080@mail.gmail.com
Whole thread Raw
In response to Re: Insertion to temp table deteriorating over time  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Insertion to temp table deteriorating over time
List pgsql-performance
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
 

pgsql-performance by date:

Previous
From: "Jeremy Haile"
Date:
Subject: Inner join vs where-clause subquery
Next
From: Richard Huxton
Date:
Subject: Re: Inner join vs where-clause subquery