Re: Re: Postgres and Oracle differences and questions

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