Обсуждение: Error: "Out of memory while reading tuples." in pushing table from SAS to PostgreSQL on Mac

On 12/13/2014 08:03 PM, wetter wetterana wrote:
> Hi,
>
> I'm passing rows from SAS to PostgreSQL (I assign a libname and use a
> PROC APPEND).  This works fine with smaller tables (~below 1 million
> rows).  However, as tables get larger I receive the following error
> messages:

This will need some more explaining for those of us that do not use SAS
on a regular basis.

Or to put it another way, what exactly are you doing?

>
>
> "ERROR: CLI describe error: Out of memory while reading tuples.; No
> query has been executed with that handle"
> and
> "GLOBAL SYSDBMSG POSTGRES: Out of memory while reading tuples.; No query
> has been executed with that handle
> GLOBAL SYSDBRC HY000"

So where exactly are the above messages coming from, they do not look
like Postgres messages?

>
> I've tried to change memory settings on the PostgreSQL server, but can't
> solve the problem.  As far as I could understand—I'm new to PostgreSQL
> ;)—it seems that PostgreSQL want to somehow read information on the
> whole table before processing it and this behavior could eventually be
> switched off, but I might be wrong here.

I thought the information was going from SAS to Postgres, so I am not
sure why Postgres would need to read the table?

>
> FYI:
> - I run SAS 9.4 on a windows machine.
> - I run PostgreSQL server on a MAC: PostgreSQL 9.3.5 on
> x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1, 64-bit
>    I've Pgadmin 1.18.1 installed.
>
> Any help would be much appreciated!!
>
> THANKS!
>
>
> PS: For several reasons, I cannot use the bulkload feature in SAS for
> this job.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/13/2014 10:03 PM, wetter wetterana wrote:
> Hi,
>
> I'm passing rows from SAS to PostgreSQL (I assign a libname and use a PROC APPEND).  This works fine with smaller
tables(~below 1 million rows).  However, as tables get larger I receive the following error messages: 
>
>
> "ERROR: CLI describe error: Out of memory while reading tuples.; No query has been executed with that handle"
> and
> "GLOBAL SYSDBMSG POSTGRES: Out of memory while reading tuples.; No query has been executed with that handle
> GLOBAL SYSDBRC HY000"
>
> I've tried to change memory settings on the PostgreSQL server, but can't solve the problem.  As far as I could
understand—I'mnew to PostgreSQL ;)—it seems that PostgreSQL want to somehow read information on the whole table before
processingit and this behavior could eventually be switched off, but I might be wrong here. 
>
> FYI:
> - I run SAS 9.4 on a windows machine.
> - I run PostgreSQL server on a MAC: PostgreSQL 9.3.5 on x86_64-apple-darwin12.5.0, compiled by Apple LLVM version
5.1,64-bit 
>    I've Pgadmin 1.18.1 installed.
>
> Any help would be much appreciated!!
>
> THANKS!
>
>
> PS: For several reasons, I cannot use the bulkload feature in SAS for this job.
>


That error is coming from SAS, not PG.  SAS must have pretty bad documentation because when I google "GLOBAL SYSDBRC
HY000"there is pretty much nothing.  (Also, I've never used, or even heard of SAS.  At first I thought you meant serial
attachedscsi) 

Does SAS support a cursor of some kind?

-Andy

On 12/14/2014 07:44 AM, Andy Colson wrote:
> On 12/13/2014 10:03 PM, wetter wetterana wrote:
>> Hi,
>>
>> I'm passing rows from SAS to PostgreSQL (I assign a libname and use a
>> PROC APPEND).  This works fine with smaller tables (~below 1 million
>> rows).  However, as tables get larger I receive the following error
>> messages:
>>
>>
>> "ERROR: CLI describe error: Out of memory while reading tuples.; No
>> query has been executed with that handle"
>> and
>> "GLOBAL SYSDBMSG POSTGRES: Out of memory while reading tuples.; No
>> query has been executed with that handle
>> GLOBAL SYSDBRC HY000"
>>
>> I've tried to change memory settings on the PostgreSQL server, but
>> can't solve the problem.  As far as I could understand—I'm new to
>> PostgreSQL ;)—it seems that PostgreSQL want to somehow read
>> information on the whole table before processing it and this behavior
>> could eventually be switched off, but I might be wrong here.
>>
>> FYI:
>> - I run SAS 9.4 on a windows machine.
>> - I run PostgreSQL server on a MAC: PostgreSQL 9.3.5 on
>> x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1, 64-bit
>>    I've Pgadmin 1.18.1 installed.
>>
>> Any help would be much appreciated!!
>>
>> THANKS!
>>
>>
>> PS: For several reasons, I cannot use the bulkload feature in SAS for
>> this job.
>>
>
>
> That error is coming from SAS, not PG.  SAS must have pretty bad
> documentation because when I google "GLOBAL SYSDBRC HY000" there is
> pretty much nothing.  (Also, I've never used, or even heard of SAS.  At
> first I thought you meant serial attached scsi)

SAS is statistical software:

http://www.sas.com/en_us/home.html

Last time I used it mainframes roamed the Earth:)

>
> Does SAS support a cursor of some kind?
>
> -Andy
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 12/14/2014 09:51 AM, Adrian Klaver wrote:
> On 12/14/2014 07:44 AM, Andy Colson wrote:
>> On 12/13/2014 10:03 PM, wetter wetterana wrote:
>>> Hi,
>>>
>>> I'm passing rows from SAS to PostgreSQL (I assign a libname and use a
>>> PROC APPEND).  This works fine with smaller tables (~below 1 million
>>> rows).  However, as tables get larger I receive the following error
>>> messages:
>>>
>>>
>>> "ERROR: CLI describe error: Out of memory while reading tuples.; No
>>> query has been executed with that handle"
>>> and
>>> "GLOBAL SYSDBMSG POSTGRES: Out of memory while reading tuples.; No
>>> query has been executed with that handle
>>> GLOBAL SYSDBRC HY000"
>>>
>>> I've tried to change memory settings on the PostgreSQL server, but
>>> can't solve the problem.  As far as I could understand—I'm new to
>>> PostgreSQL ;)—it seems that PostgreSQL want to somehow read
>>> information on the whole table before processing it and this behavior
>>> could eventually be switched off, but I might be wrong here.
>>>
>>> FYI:
>>> - I run SAS 9.4 on a windows machine.
>>> - I run PostgreSQL server on a MAC: PostgreSQL 9.3.5 on
>>> x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1, 64-bit
>>>    I've Pgadmin 1.18.1 installed.
>>>
>>> Any help would be much appreciated!!
>>>
>>> THANKS!
>>>
>>>
>>> PS: For several reasons, I cannot use the bulkload feature in SAS for
>>> this job.
>>>
>>
>>
>> That error is coming from SAS, not PG.  SAS must have pretty bad
>> documentation because when I google "GLOBAL SYSDBRC HY000" there is
>> pretty much nothing.  (Also, I've never used, or even heard of SAS.  At
>> first I thought you meant serial attached scsi)
>
> SAS is statistical software:
>
> http://www.sas.com/en_us/home.html
>
> Last time I used it mainframes roamed the Earth:)
>
>>
>> Does SAS support a cursor of some kind?
>>
>> -Andy
>>
>>
>
>

Ah!  That would explain it.

Welp, this search was more helpful: "Out of memory while reading tuples"

http://stackoverflow.com/questions/22532149/vba-and-postgresql-connection

It says: include UseDeclareFetch=1 in connect string, which sounds like its part of odbc.  How does SAS connect to PG?

-Andy

>
> Ah!  That would explain it.
>
> Welp, this search was more helpful: "Out of memory while reading tuples"
>
> http://stackoverflow.com/questions/22532149/vba-and-postgresql-connection
>
> It says: include UseDeclareFetch=1 in connect string, which sounds like its part of odbc.  How does SAS connect to
PG?
>
> -Andy
>

Here is another reference that describes the same problem:


https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4498d4f2-9646-4921-b56d-79e22c14a215/import-data-from-postgresql-into-sql-server-2005

The answer (very bottom) is to set the "use declare/fetch" option in psqlODBC driver options.


-Andy

On 12/14/2014 08:14 AM, wetter wetterana wrote:
> Apologies,
>
> I am using SAS, a statistical package/database management system.  SAS
> has the feature of connecting to a PostgreSQL server.  It does so by
> assigning what is called a libname (a 'library' connection telling SAS
> that a particular folder is a data storage). In this assignment, you
> specify the server address, databasename, password.  I.e. SAS connects
> to the server and allows you to push data to/read data/manipulate data
> in the PSQL database.

So are you using the SAS Postgres interface or are you going through
ODBC? The fact that you say you cannot use bulkload would seem to
indicate ODBC.

If via ODBC, see Andys comments.

>
> Here is the code in SAS:
>
> # connect to the server and assign it the name 'psqlibrary'
> libname psqlibrary postgres server='xxx.xx.xxx.x' port=5432
>     user=username password='xxxxxxx' database=dbname;
>
> # then append data to the table 'table' in the psql database
> proc append base = psqlibrary.table
> data = datatoappend;
> run;
>
> SAS reports back error messages from the server if it receives such
> errors.  In my case, I repeatedly get the error described above.  This
> error is also reported in my terminal app if I log into the database at
> the same time to monitor things there. I.e. as far as I can tell, the
> error "Out of memory while reading tuples.; No query has been executed
> with that handle" stems from PSQL itself. SAS prefixes the error with
> some specific indicators such as "GLOBAL SYSDBMSG POSTGRES"  or "ERROR:
> CLI describe error".
>
> Adrian, you are right, PSQL does receive the data. Also not sure what
> you mean by cursor,

Actually that was Andy.
For information on cursors see here:

http://www.postgresql.org/docs/9.3/interactive/sql-declare.html

  I really new to PSQL... sorry!
>
> Help much appreciated!
>
>
>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

wetter wetterana <wetterana@gmail.com> writes:
> Help much appreciated!

The out-of-memory situation is definitely happening on the client side,
not the server side.  A problem happening in the server would not result
in a message spelled quite that way, and it would not use an HY000 error
code either.

A plausible guess is that you're trying to retrieve more rows in a single
query than your client-side environment can hold in memory.  You could
break up the query, or convert to a cursor with which you fetch a few rows
at a time.

Not knowing anything much about your client-side software stack, it's
hard to be more specific than that.

            regards, tom lane

On 15/12/14 04:44, Andy Colson wrote:
> On 12/13/2014 10:03 PM, wetter wetterana wrote:
>> Hi,
>>
>> I'm passing rows from SAS to PostgreSQL (I assign a libname and use a
>> PROC APPEND).  This works fine with smaller tables (~below 1 million
>> rows).  However, as tables get larger I receive the following error
>> messages:
>>
>>
>> "ERROR: CLI describe error: Out of memory while reading tuples.; No
>> query has been executed with that handle"
>> and
>> "GLOBAL SYSDBMSG POSTGRES: Out of memory while reading tuples.; No
>> query has been executed with that handle
>> GLOBAL SYSDBRC HY000"
>>
>> I've tried to change memory settings on the PostgreSQL server, but
>> can't solve the problem.  As far as I could understand—I'm new to
>> PostgreSQL ;)—it seems that PostgreSQL want to somehow read
>> information on the whole table before processing it and this behavior
>> could eventually be switched off, but I might be wrong here.
>>
>> FYI:
>> - I run SAS 9.4 on a windows machine.
>> - I run PostgreSQL server on a MAC: PostgreSQL 9.3.5 on
>> x86_64-apple-darwin12.5.0, compiled by Apple LLVM version 5.1, 64-bit
>>    I've Pgadmin 1.18.1 installed.
>>
>> Any help would be much appreciated!!
>>
>> THANKS!
>>
>>
>> PS: For several reasons, I cannot use the bulkload feature in SAS for
>> this job.
>>
>
>
> That error is coming from SAS, not PG.  SAS must have pretty bad
> documentation because when I google "GLOBAL SYSDBRC HY000" there is
> pretty much nothing.  (Also, I've never used, or even heard of SAS.
> At first I thought you meant serial attached scsi)
>
> Does SAS support a cursor of some kind?
>
> -Andy
>
>
SAS: Statistical Analysis System (this is the original name I remember,
I think they have renamed it)

SAS: Special Air Service (elite British fighting force, deliberately
misnamed to confuse the Germans in World War II)

Funny, just now I thought of the second definition first, but obviously
the first applies.

I actually used SAS many years ago, long before I had written any SQL -
let alone directly used any database!


Cheers,
Gavin