Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1j8mut-0002iF-Qc for pgsql-interfaces@arkaria.postgresql.org; Mon, 02 Mar 2020 15:23:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1j8mus-0002Eh-G0 for pgsql-interfaces@arkaria.postgresql.org; Mon, 02 Mar 2020 15:23:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1j8mus-0002Ea-5R for pgsql-interfaces@lists.postgresql.org; Mon, 02 Mar 2020 15:23:34 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j8mup-0006Rv-JC for pgsql-interfaces@lists.postgresql.org; Mon, 02 Mar 2020 15:23:33 +0000 Received: by mail-oi1-x22b.google.com with SMTP id j80so8634887oih.7 for ; Mon, 02 Mar 2020 07:23:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=pLmCaT3adnlIEIojGM+lBpWY18Jf2nDpncRhEbOh9Xc=; b=GGj/wcpcIpE2M8uhqA8EsYw+rEt8oVrl9ZfWdeJADtg4CEkQkx1c3s/CHf6TPY19Dr PAi7eWvaE7FZN0/CZ82NV3JqAQfRfId9zoD+P4JJNSe1gKPKzpuJLqPl5mLhDsx8Mqkw j8BlDiAICD103oyC+Pw8IkZEuY+CEY5GHW2YKgEHtVxy5P8woiMEq7uL1rHuEExNxj+U f+5rd8l7WeEiGOSBuDJ+7Wi7onW9EThlpnxu4RZW26mPanDVzfamxxt8v3G9hDUlNIUu y4IBlLmh1R/ZbaqNAhaCquTnLQISGPk3XE5ZaYW47v5D5jCydtVic3c+7Yp4OGPrN+Hm 0RKw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=pLmCaT3adnlIEIojGM+lBpWY18Jf2nDpncRhEbOh9Xc=; b=UGJlT1CZWYm12aDCCXb7fBMNopdoTVhy66kHqN3ejY7HQrpTR8dH7I8f5aMPpw0fpk mD8k3DEEsPUWxXRs4J3CN6AmKn+oMoK4eJMWVbSLCU0QN8HIJsXItfOE/5dGRhwlBUUz 8xrKW5hdpy7rYRBUYQSeHe/nefuyJLR0xLd/N+1ReObBPdqjGE734ddiyxyKXlXQwgaF B48wT60Qgusu7rG24DzrsALD0HhJlf8qmelba5eaXAcUwqkEjzaahDqqFpnHMYygLtvI xdd1CyFEN+y9wkt9GYv0EhfNF2hVZ+OxGiZ3LulLPwA0ao9Lz5yum3nX6A07OW++lrGz Tfkw== X-Gm-Message-State: ANhLgQ2mfLwfIMvd7QF4k1P7JiqgCFp9cUVwCGl8qpSl/bvykXkmaWNz yzb1Hr5MiIdhRD7myli1H6OksTcRBoRqStdRF1ff8KWf X-Google-Smtp-Source: ADFU+vvtpcJ5XnOlAw0OFcxW283/hRviowyp+xqCT4/yg8IVM5fuyxZ07hclMYXmtzt0ZHRchSlUq1ZUkS91/3hrGoY= X-Received: by 2002:aca:f305:: with SMTP id r5mr16498oih.174.1583162610773; Mon, 02 Mar 2020 07:23:30 -0800 (PST) MIME-Version: 1.0 References: <000e01d5f0a0$9aeec2f0$d0cc48d0$@zpmt.de> In-Reply-To: From: "Jonah H. Harris" Date: Mon, 2 Mar 2020 10:23:19 -0500 Message-ID: Subject: Re: MS-ACCESS 2010, ODBC 12.01, Out of Memory To: Pavlo Golub Cc: Stefan Wolf , pgsql-interfaces@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000000973e0059fe0c5e7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000000973e0059fe0c5e7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Mar 2, 2020 at 9:43 AM Pavlo Golub wrote: > > ------ Original Message ------ > From: "Stefan Wolf" > To: pgsql-interfaces@lists.postgresql.org > Sent: 2020-03-02 15:41:05 > Subject: MS-ACCESS 2010, ODBC 12.01, Out of Memory > > Win10-Client: MS-ACCESS 2010 (32-Bit), ODBC-Driver: 12.01 > > Linux-Server (1): PostgreSQL 12.2 > > Linux-Server (2): PostgreSQL 9.6 > > > > > > We are currently migrating from PG 9.6. to 12.x. > > > > We=E2=80=99ve created System-DSNs (PostgreSQL Unicode) with [Defaults] fo= r both > Server. > > > > In MS-ACCESS our biggest Server-Tables (>14.000.000 Rows) can be opened > (on both Servers). > > But it takes a lot of time and uses a lot of memory - more than 450MB on > the client and some GB on the server. > > > This is for sure MsAcess problem, and not a bug of PostgreSQL. > Is it? > > The indexes are recognized an filtering is fast. > > > > Trying to open more than one =E2=80=9Cbig table=E2=80=9D will result in M= S-ACCESS =E2=80=9COut of > memory=E2=80=9D. > > > Sounds like msAccess fetches all rows and put them into memory which is > weird and unwise. Not a bug of PostgreSQL > Sounds like the ODBC driver configuration isn't using UseDeclareFetch=3D1 and, as such, is defaulting to Postgres' crappy default buffer-all-results-on-the-client-at-once protocol - a problem that would exist in any client software not overriding that behavior. --=20 Jonah H. Harris --0000000000000973e0059fe0c5e7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Mar 2, 2020 at 9:43 AM Pavlo Golu= b <pavlo.go= lub@cybertec.at> wrote:

------ Original Message ------
From: "Stefan Wolf" <sw@zpmt.de>
Sent: 2020-03-02 15:41:05
Subject: MS-ACCESS 2010, ODBC 12.01, Out of Memory

Win10-Client: =C2=A0MS-ACCESS 2010 (32-Bit),=C2= =A0 ODBC-Driver: =C2=A012.01

Linux-Server (1):=C2=A0 PostgreSQL 12.2

Linux-Server (2):=C2=A0 PostgreSQL =C2=A09.6

=C2=A0

=C2=A0

We are cur= rently migrating from PG 9.6. t= o 12.x.

=C2=A0=

We=E2=80=99ve created System-DSNs (Postgr= eSQL Unicode) with [Defaults] for both Server.

=C2=A0

In= MS-ACCESS our biggest Server-Tables (>14.000.000 Rows) can be opened (o= n both Servers).

But it tak= es a lot of time and uses a lot of memory - more than 450MB on the client and some GB on the server.


This is for sure = MsAcess problem, and not a bug of PostgreSQL.

Is it?
=C2=A0


The indexes are recognized an filtering i= s fast.

=C2=A0=

Trying to open more than one =E2=80=9Cbig= table=E2=80=9D will result in MS-ACCESS =E2=80=9COut of memory=E2=80=9D.

=

So= unds like msAccess fetches all rows and put them into memory which is weird= and unwise. Not a bug of PostgreSQL

Sounds like the ODBC driver configuration isn't using= =C2=A0UseDeclareFetch=3D1 and, as such, is defaulting to Postgres' crap= py default buffer-all-results-on-the-client-at-once protocol - a problem th= at would exist in any client software not overriding that behavior.

--
Jonah H. H= arris

--0000000000000973e0059fe0c5e7--