From: Steven Flatt <steven.flatt@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time
Date: Tue, 19 Dec 2006 10:43:13 -0500
Message-ID: <357fa7590612190743lc9201fey6519737114881080@mail.gmail.com> (raw)
In-Reply-To: <17718.1166515736@sss.pgh.pa.us>
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: steven.flatt@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Insertion to temp table deteriorating over time
In-Reply-To: <357fa7590612190743lc9201fey6519737114881080@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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