INSERT on MSAccess linked ODBC table : out of memory

Hi all !

I have a MSAccess 2007 frontend with linked tables on a PostgreSQL 8.3.7
backend.
The tables are linked via ODBC with the PsqlODBC 8.03.0400 driver.
I use the driver's default options.

I am trying to run a query that inserts ~350.000 rows from a local
Access table to a linked PostgreSQL table. Both tables have the same
structure, so the statement is basically something like :
INSERT INTO mylinkedtable (field1, field2, etc...) SELECT field1,
field2, etc... FROM myaccesstable;
The table on the postgresql server is quite simple, no trigger or rule,
only a primary key and 3 indexes (btree).

This query runs for quite a long time (10 minutes) and fails before the
end.
Access only shows me an "ODBC failure" message.
On the postgresql server (backend), I have an "ERROR: out of memory
DETAIL: Failed on request of size 560".

On the postgresql server (running on Windows 2003), I have 1GB of RAM
and ~3GB of pagefile.
The process running the query eats up all available RAM before swapping
and dies when its memory usage totals ~2.5GB.

My server settings are :
shared_buffers = 64MB
sort_buffers = 8MB
work_mem = 8MB
maintenance_work_mem = 128MB

work_mem and maintenance_work_mem were higher (32MB and 512MB
respectively), so I tried to lower them but to no avail.

What should I change for this query to work ? Why does postgresql use so
much memory (more than 2GB !!) when all my ressource settings are set so
low ?
How can I make sure that this won't happen again ?

Thanks a lot for your help !


Regards
--
Arnaud

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: INSERT on MSAccess linked ODBC table : out of memory

postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
> Hi all !
>
> I have a MSAccess 2007 frontend with linked tables on a PostgreSQL 8.3.7
> backend.
> The tables are linked via ODBC with the PsqlODBC 8.03.0400 driver.
> I use the driver's default options.
>
> I am trying to run a query that inserts ~350.000 rows from a local
> Access table to a linked PostgreSQL table. Both tables have the same
> structure, so the statement is basically something like :
> INSERT INTO mylinkedtable (field1, field2, etc...) SELECT field1,
> field2, etc... FROM myaccesstable;
> The table on the postgresql server is quite simple, no trigger or rule,
> only a primary key and 3 indexes (btree).
>
> This query runs for quite a long time (10 minutes) and fails before the
> end.
> Access only shows me an "ODBC failure" message.
> On the postgresql server (backend), I have an "ERROR: out of memory
> DETAIL: Failed on request of size 560".

Please set the *Level of rollback on errors* Datasource option to
*Transaction* if you are setting the option to *Statement*.
You also have to relink the link table after changing the Datasource
setting.

regards,
Hiroshi Inoue

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: INSERT on MSAccess linked ODBC table : out of memory

Hiroshi Inoue a =E9crit :
> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
>> Hi all !
>>=20
>> I have a MSAccess 2007 frontend with linked tables on a PostgreSQL 8.3=
..7=20
>> backend.
>> The tables are linked via ODBC with the PsqlODBC 8.03.0400 driver.
>> I use the driver's default options.
>>=20
>> I am trying to run a query that inserts ~350.000 rows from a local=20
>> Access table to a linked PostgreSQL table. Both tables have the same=20
>> structure, so the statement is basically something like :
>> INSERT INTO mylinkedtable (field1, field2, etc...) SELECT field1,=20
>> field2, etc... FROM myaccesstable;
>> The table on the postgresql server is quite simple, no trigger or rule=
,=20
>> only a primary key and 3 indexes (btree).
>>=20
>> This query runs for quite a long time (10 minutes) and fails before th=
e=20
>> end.
>> Access only shows me an "ODBC failure" message.
>> On the postgresql server (backend), I have an "ERROR: out of memory=20
>> DETAIL: Failed on request of size 560".
>=20
> Please set the *Level of rollback on errors* Datasource option to
> *Transaction* if you are setting the option to *Statement*.
> You also have to relink the link table after changing the Datasource
> setting.

Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is=20
for "statement" ?
I could not find this in the documentation, but this is a guess from an=20
automatically generated file-DSN.

Thanks a lot for your answer Hiroshi !

Regards
Arnaud

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: INSERT on MSAccess linked ODBC table : out of memory

postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
> Hiroshi Inoue a =E9crit :
>> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
>>> Hi all !
>>>
>>> I have a MSAccess 2007 frontend with linked tables on a PostgreSQL=20
>>> 8.3.7 backend.
>>> The tables are linked via ODBC with the PsqlODBC 8.03.0400 driver.
>>> I use the driver's default options.
>>>
>>> I am trying to run a query that inserts ~350.000 rows from a local=20
>>> Access table to a linked PostgreSQL table. Both tables have the same=20
>>> structure, so the statement is basically something like :
>>> INSERT INTO mylinkedtable (field1, field2, etc...) SELECT field1,=20
>>> field2, etc... FROM myaccesstable;
>>> The table on the postgresql server is quite simple, no trigger or=20
>>> rule, only a primary key and 3 indexes (btree).
>>>
>>> This query runs for quite a long time (10 minutes) and fails before=20
>>> the end.
>>> Access only shows me an "ODBC failure" message.
>>> On the postgresql server (backend), I have an "ERROR: out of memory=20
>>> DETAIL: Failed on request of size 560".
>>
>> Please set the *Level of rollback on errors* Datasource option to
>> *Transaction* if you are setting the option to *Statement*.
>> You also have to relink the link table after changing the Datasource
>> setting.
>=20
> Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is=20
> for "statement" ?

Yes.
This problem seems closely related to the topic
http://archives.postgresql.org/pgsql-general/2009-04/msg0072 8.php
..

regards,
Hiroshi Inoue

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: INSERT on MSAccess linked ODBC table : out of memory

Hiroshi Inoue a =E9crit :
> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
>> Hiroshi Inoue a =E9crit :
>>> postgresqlodbc.domain.thewild_codata@spamgourmet.com wrote:
>>>> I am trying to run a query that inserts ~350.000 rows from a local=20
>>>> Access table to a linked PostgreSQL table.
>>>> This query runs for quite a long time (10 minutes) and fails before=20
>>>> the end.
>>>> Access only shows me an "ODBC failure" message.
>>>> On the postgresql server (backend), I have an "ERROR: out of memory=20
>>>> DETAIL: Failed on request of size 560".
>>>
>>> Please set the *Level of rollback on errors* Datasource option to
>>> *Transaction* if you are setting the option to *Statement*.
>>> You also have to relink the link table after changing the Datasource
>>> setting.
>>=20
>> Is this done by setting "protocol" to 7.4-1 ? And a value of 7.4-2 is=20
>> for "statement" ?
>=20
> This problem seems closely related to the topic
> http://archives.postgresql.org/pgsql-general/2009-04/msg0072 8.php

Yes, that's exactly what I am seeing here.
Strange that this is not considered as a bug, but my understanding of=20
postgresql's internals is very limited.
Anyway, protocol=3D7.4-1 fixed this !

Thanks for your help !

Regards
--
Arnaud

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc