On 12/19/06, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Thanks again for all your help.
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
