pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [HACKERS] ODBC and palloc ...
5+ messages / 3 participants
[nested] [flat]
* Re: [HACKERS] ODBC and palloc ...
@ 1998-07-23 17:30 Byron Nikolaidis <byronn@insightdist.com>
parent: Davide Libenzi <dlibenzi@maticad.it>
0 siblings, 1 reply; 5+ messages in thread
From: Byron Nikolaidis @ 1998-07-23 17:30 UTC (permalink / raw)
To: Davide Libenzi <dlibenzi@maticad.it>; +Cc: pgsql-interfaces; David Hartwig <daveh@insightdist.com>
Davide Libenzi wrote:
>
> After a lot of changes I've compiled,linked and tested (regression) my
> PostgreSQL installation no HPUX 9.*.
>
> I've also built and installed the ODBC driver and I get Ms Access error
> which the PostgresSQL server log in "palloc failure : memory exausted".
>
> Is this a server bug or ODBC driver bug ?
>
I am assuming you have a fairly new odbc driver (6.30.0248 is the
latest) and not the old postodbc. BTW, on our website
(www.insightdist.com/psqlodbc) we have the DLL and a full install EXE
for win32 so you wouldn't have to build it yourself from the source code
if you didn't want to.
The palloc failure usually occurs because Access uses the multiple OR
query (select ... where a=1 OR a=2 OR a=3...) to access the recordset.
The backend does not handle this very well and it is already well known
on the TODO list.
There are several possibilities to get past this:
1. Use a non-updateable table (by setting the driver readonly option, or
by not specifying any unique identifiers).
2. For a query, use a snapshot recordset in the query properties.
3. Show the OID column in the drivers advanced datasource options and
use that alone to index on. You should create an index on it too. This
is still slow, but at least shouldn't crash.
Other possibilities:
In house, Dave made a patch to postgres which rewrites the multiple OR
query into a UNION query, which works great and its fast! We may make
this patch available evntually on our website.
Byron
^ permalink raw reply [nested|flat] 5+ messages in thread
* ODBC and palloc ...
@ 1998-07-23 18:00 Davide Libenzi <dlibenzi@maticad.it>
0 siblings, 1 reply; 5+ messages in thread
From: dlibenzi@maticad.it @ 1998-07-23 18:00 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
After a lot of changes I've compiled,linked and tested (regression) my
PostgreSQL installation no HPUX 9.*.
I've also built and installed the ODBC driver and I get Ms Access error
which the PostgresSQL server log in "palloc failure : memory exausted".
Is this a server bug or ODBC driver bug ?
----
Davide Libenzi at :
Maticad s.r.l.
Via Della Giustizia n.9 Fano (PS) 61032 Italy
Tel.: +39-721-808308 (ra) Fax: +39-721-808309
Email: <davidel@maticad.it>
WWW: <http://www.maticad.it;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [HACKERS] ODBC and palloc ...
@ 1998-07-23 18:50 Byron Nikolaidis <byronn@insightdist.com>
parent: Davide Libenzi <dlibenzi@maticad.it>
0 siblings, 0 replies; 5+ messages in thread
From: Byron Nikolaidis @ 1998-07-23 18:50 UTC (permalink / raw)
To: Davide Libenzi <dlibenzi@maticad.it>; +Cc: pgsql-hackers@postgreSQL.org, pgsql-interfaces@postgreSQL.org, David Hartwig <daveh@insightdist.com>
Davide Libenzi wrote:
>
> I think this is not my case.
> See attachment log for details.
>
From looking at the log that is *exactly* your case. I pulled the
offending query out and cleaned it up a bit.
You have a two-part key (padre & figlio) and you can see the multiple
OR's between them. The MS Jet db engine typically uses a rowset size of
10 (so you see 10 keys below) and a keyset size of a couple of hundred
or so. In other words, it first read in 200 keys (the "keyset") and
then uses these keys to access a "rowset" of size 10 out of the entire
"resultset" (how ever many records you have total). This is called a
Mixed (Keyset/Dynamic) cursor or a "Dynaset". Like I said in my last
email, if you change the datasource to be read-only, then re-link your
table in Access, it will not use this style of retrieval and you should
get some results. OR, you can try the other options I mentioned.
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
OR "padre" = 'KKKL' AND "figlio" = 'LLLA'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
OR "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
The only problem with this style of retrieving records is that the
Postgres backend can not handle it. It results in exponential memory
usage as it tries to optimize it. You could type in the above query by
hand to the monitor and see the same result.
Then for fun try rewriting the query to use UNIONS instead of OR's and
you will see how fast it is (assuming you have an index). See below.
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'PPPA' AND "figlio" = 'AAA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'KKKL' AND "figlio" = 'LLLA'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
UNION
SELECT "padre","figlio","qta" FROM "distinta"
WHERE "padre" = 'AAAAA' AND "figlio" = 'ASDWDWD'
....
Byron
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [HACKERS] ODBC and palloc ...
@ 1998-07-23 19:09 Davide Libenzi <dlibenzi@maticad.it>
0 siblings, 1 reply; 5+ messages in thread
From: dlibenzi@maticad.it @ 1998-07-23 19:09 UTC (permalink / raw)
To: Byron Nikolaidis <byronn@insightdist.com>; Davide Libenzi <dlibenzi@maticad.it>; +Cc: pgsql-hackers@postgreSQL.org; pgsql-interfaces; David Hartwig <daveh@insightdist.com>
I think this is not my case.
See attachment log for details.
Hi
----
Davide Libenzi at :
Maticad s.r.l.
Via Della Giustizia n.9 Fano (PS) 61032 Italy
Tel.: +39-721-808308 (ra) Fax: +39-721-808309
Email: <davidel@maticad.it>
WWW: <http://www.maticad.it;
-----Original Message-----
From: Byron Nikolaidis <byronn@insightdist.com>
To: Davide Libenzi <dlibenzi@maticad.it>
Cc: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>;
pgsql-interfaces@postgreSQL.org <pgsql-interfaces@postgreSQL.org>; David
Hartwig <daveh@insightdist.com>
Date: Thursday, July 23, 1998 6:59 PM
Subject: Re: [HACKERS] ODBC and palloc ...
>Davide Libenzi wrote:
>>
>> After a lot of changes I've compiled,linked and tested (regression) my
>> PostgreSQL installation no HPUX 9.*.
>>
>> I've also built and installed the ODBC driver and I get Ms Access error
>> which the PostgresSQL server log in "palloc failure : memory exausted".
>>
>> Is this a server bug or ODBC driver bug ?
>>
>
>I am assuming you have a fairly new odbc driver (6.30.0248 is the
>latest) and not the old postodbc. BTW, on our website
>(www.insightdist.com/psqlodbc) we have the DLL and a full install EXE
>for win32 so you wouldn't have to build it yourself from the source code
>if you didn't want to.
>
>The palloc failure usually occurs because Access uses the multiple OR
>query (select ... where a=1 OR a=2 OR a=3...) to access the recordset.
>The backend does not handle this very well and it is already well known
>on the TODO list.
>
>There are several possibilities to get past this:
>1. Use a non-updateable table (by setting the driver readonly option, or
>by not specifying any unique identifiers).
>2. For a query, use a snapshot recordset in the query properties.
>3. Show the OID column in the drivers advanced datasource options and
>use that alone to index on. You should create an index on it too. This
>is still slow, but at least shouldn't crash.
>
>Other possibilities:
>
>In house, Dave made a patch to postgres which rewrites the multiple OR
>query into a UNION query, which works great and its fast! We may make
>this patch available evntually on our website.
>
>Byron
>
Attachments:
psqlodbc.log (text/plain, 9.1K) download
conn=155525648, SQLDriverConnect( in)='DSN=AAAAA;', fDriverCompletion=1
DSN info: DSN='AAAAA',server='salvia',port='5432',dbase='prova',user='postgres',passwd=''
readonly='0',protocol='',showoid='0',fakeoidindex='0',showsystable='0'
conn_settings=''
translation_dll='',translation_option=''
conn=155525648, SQLDriverConnect(out)='DSN=AAAAA;DATABASE=prova;SERVER=salvia;PORT=5432;UID=postgres;PWD=;READONLY=0;PROTOCOL=;FAKEOIDINDEX=0;SHOWOIDCOLUMN=0;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS='
Global Options: fetch=100, socket=4096, unknown_sizes=2, max_varchar_size=254, max_longvarchar_size=4094
disable_optimizer=1, unique_index=0, use_declarefetch=1
text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1
extra_systable_prefixes='dd_;', conn_settings=''
conn=155525648, query=' '
conn=155525648, query='BEGIN'
conn=155525648, query='set DateStyle to 'ISO'; set geqo to 'OFF''
Command response: 'SET VARIABLE'
conn=155525648, query='declare SQL_CUR160038976 cursor for select oid from pg_type where typname='lo''
conn=155525648, query='fetch 100 in SQL_CUR160038976'
[ fetched 0 rows ]
conn=155525648, query='close SQL_CUR160038976'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160038976 cursor for SELECT Config, nValue FROM MSysConf'
ERROR from backend during send_query: 'ERROR: msysconf: Table does not exist.'
conn=155525648, query='ABORT'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=155525648, stmt=160038976, result=0
manual_result=0, prepare=0, internal=0
bindings=0, bindings_allocated=0
parameters=0, parameters_allocated=0
statement_type=0, statement='SELECT Config, nValue FROM MSysConf'
stmt_with_params='declare SQL_CUR160038976 cursor for SELECT Config, nValue FROM MSysConf'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR160038976'
----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=10, errmsg='ERROR: msysconf: Table does not exist.'
------------------------------------------------------------
henv=155518528, conn=155525648, status=1, num_stmts=16
sock=155518544, stmts=155518592, lobj_type=-999
---------------- Socket Info -------------------------------
socket=340, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=155531944, buffer_out=155536048
buffer_filled_in=2, buffer_filled_out=0, buffer_read_in=2
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160104664 cursor for select relname, usename, relhasrules from pg_class, pg_user where relkind = 'r' and relname !~ '^xinv[0-9]+' and int4out(usesysid) = int4out(relowner) order by relname'
conn=155525648, query='fetch 100 in SQL_CUR160104664'
[ fetched 28 rows ]
conn=155525648, query='close SQL_CUR160104664'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160173568 cursor for select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c, pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname like 'distinta' order by attnum'
conn=155525648, query='fetch 100 in SQL_CUR160173568'
[ fetched 3 rows ]
conn=155525648, query='close SQL_CUR160173568'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160239776 cursor for select u.usename, c.relname, a.attname, a.atttypid,t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull from pg_user u, pg_class c, pg_attribute a, pg_type t where int4out(u.usesysid) = int4out(c.relowner) and c.oid= a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname like 'distinta' order by attnum'
conn=155525648, query='fetch 100 in SQL_CUR160239776'
[ fetched 3 rows ]
conn=155525648, query='close SQL_CUR160239776'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160174088 cursor for select c.relname, i.indkey, i.indisunique, i.indisclustered from pg_index i, pg_class c, pg_class d where c.oid = i.indexrelid and d.relname = 'distinta' and d.oid = i.indrelid'
conn=155525648, query='fetch 100 in SQL_CUR160174088'
[ fetched 1 rows ]
conn=155525648, query='close SQL_CUR160174088'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160038976 cursor for SELECT "distinta"."padre","distinta"."figlio" FROM "distinta" '
conn=155525648, query='fetch 100 in SQL_CUR160038976'
[ fetched 3 rows ]
conn=155525648, query='close SQL_CUR160038976'
conn=155525648, query='END'
conn=155525648, query='BEGIN'
conn=155525648, query='declare SQL_CUR160038976 cursor for SELECT "padre","figlio","qta" FROM "distinta" WHERE "padre" = 'PPPA ' AND "figlio" = 'AAA ' OR "padre" = 'KKKL ' AND "figlio" = 'LLLA ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ''
ERROR from backend during send_query: 'FATAL 1: palloc failure: memory exhausted'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while executing the query'
------------------------------------------------------------
hdbc=155525648, stmt=160038976, result=0
manual_result=0, prepare=1, internal=0
bindings=0, bindings_allocated=0
parameters=160169992, parameters_allocated=20
statement_type=0, statement='SELECT "padre","figlio","qta" FROM "distinta" WHERE "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ? OR "padre" = ? AND "figlio" = ?'
stmt_with_params='declare SQL_CUR160038976 cursor for SELECT "padre","figlio","qta" FROM "distinta" WHERE "padre" = 'PPPA ' AND "figlio" = 'AAA ' OR "padre" = 'KKKL ' AND "figlio" = 'LLLA ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ' OR "padre" = 'AAAAA ' AND "figlio" = 'ASDWDWD ''
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=-1, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1
cursor_name='SQL_CUR160038976'
----------------QResult Info -------------------------------
CONN ERROR: func=SC_execute, desc='', errnum=8, errmsg='FATAL 1: palloc failure: memory exhausted'
------------------------------------------------------------
henv=155518528, conn=155525648, status=1, num_stmts=16
sock=155518544, stmts=155518592, lobj_type=-999
---------------- Socket Info -------------------------------
socket=340, reverse=0, errornumber=0, errormsg='(null)'
buffer_in=155531944, buffer_out=155536048
buffer_filled_in=45, buffer_filled_out=0, buffer_read_in=45
conn=155525648, SQLDisconnect
* Re: [HACKERS] ODBC and palloc ...
@ 1998-07-23 19:45 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 5+ messages in thread
From: Bruce Momjian @ 1998-07-23 19:45 UTC (permalink / raw)
To: byronn@insightdist.com; +Cc: dlibenzi@maticad.it; pgsql-hackers@postgreSQL.org; pgsql-interfaces; daveh@insightdist.com
> Davide Libenzi wrote:
> >
> > After a lot of changes I've compiled,linked and tested (regression) my
> > PostgreSQL installation no HPUX 9.*.
> >
> > I've also built and installed the ODBC driver and I get Ms Access error
> > which the PostgresSQL server log in "palloc failure : memory exausted".
> >
> > Is this a server bug or ODBC driver bug ?
> >
>
> I am assuming you have a fairly new odbc driver (6.30.0248 is the
> latest) and not the old postodbc. BTW, on our website
> (www.insightdist.com/psqlodbc) we have the DLL and a full install EXE
> for win32 so you wouldn't have to build it yourself from the source code
> if you didn't want to.
>
> The palloc failure usually occurs because Access uses the multiple OR
> query (select ... where a=1 OR a=2 OR a=3...) to access the recordset.
> The backend does not handle this very well and it is already well known
> on the TODO list.
>
> There are several possibilities to get past this:
> 1. Use a non-updateable table (by setting the driver readonly option, or
> by not specifying any unique identifiers).
> 2. For a query, use a snapshot recordset in the query properties.
> 3. Show the OID column in the drivers advanced datasource options and
> use that alone to index on. You should create an index on it too. This
> is still slow, but at least shouldn't crash.
>
> Other possibilities:
>
> In house, Dave made a patch to postgres which rewrites the multiple OR
> query into a UNION query, which works great and its fast! We may make
> this patch available evntually on our website.
I am thinking about this right now, and will post an analysis within the
next day.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1998-07-23 19:45 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-07-23 18:00 ODBC and palloc ... Davide Libenzi <dlibenzi@maticad.it>
1998-07-23 17:30 ` Byron Nikolaidis <byronn@insightdist.com>
1998-07-23 19:45 ` Bruce Momjian <maillist@candle.pha.pa.us>
1998-07-23 19:09 Re: [HACKERS] ODBC and palloc ... Davide Libenzi <dlibenzi@maticad.it>
1998-07-23 18:50 ` Byron Nikolaidis <byronn@insightdist.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox