Message-ID: <3A8155DF.80007@tm.ee>
Date: Wed, 07 Feb 2001 16:04:15 +0200
From: Hannu Krosing <hannu@tm.ee>
User-Agent: Mozilla/5.0 (X11; U; Linux 2.2.17-21mdk i686; en-US;
	0.7) Gecko/20010112
MIME-Version: 1.0
To: Jan Wieck <janwieck@Yahoo.com>
Cc: "D. Johnson" <dspectra@home.com>, pgsql-interfaces@postgresql.org
Subject: Re: Re: Postgres and Oracle differences and questions
References: <200102071006.FAA02488@jupiter.greatbridge.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Jan Wieck wrote:

> D. Johnson wrote:
> 
>> I have recently been working on several projects that use MSAccess as a
>> front end to both a Postgres and Oracle DB. Unfortunately, I have
>> noticed some differences and limitations in Oracle that seem to make
>> Postgres look like a better choice for the application. My problem is
>> with the block sizing differences in PG and Oracle. It seems that when
>> mapping memo types from Access to Postgres you could create a text type
>> in Postgres that emulates the Access memo type, and you could define a
>> table with any number of these types of fields. In Oracle you can only
>> have one memo field mapped to a table with a max size equal I believe to
>> the block size, and if you use the max block size then you cannot define
>> any other fields in the same table.
>> 
>> I am curious how Postgres handles text types, is a var char or does it
>> allocate the full 8K for the text type. In Oracle, the size of the table
>> definition has to be within the block boundary, is the same restriction
>> true in Postgres.
> 
> 
>     The  size  limitations (8K by default, 32K max) are gone with
>     7.1. Well, you shouldn't  really  use  100+  MB  sized  rows,
>     because the resulting INSERT already needs to travel from the
>     frontend, through the parser down into the executor.  And  on
>     SELECT  the  client  needs  to buffer all the data at once in
>     memory.

Well, you could roll your own client that streams parts of a query from 
file. Or you can INSERT the results of a user-defined function that 
reads from a file/socket/URL or just makes up the field contents ;)

>     But if you really need  to  do  it,  swap  space  is
>     cheap...

What are the performance implications - is it 1.01,  10 or 1000 times 
slower than accessing the same file from fs for files in the range of 
typical Office documents (0.2-20M) ?

Will updating one non-toasted field in a tuple copy the toasted one
as well, or is only the reference copied ?
------------------
Hannu