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