Message-ID: <50ADED9B.7000502@vmware.com>
Date: Thu, 22 Nov 2012 11:17:15 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.10) Gecko/20121027 Icedove/10.0.10
MIME-Version: 1.0
To: eildert.groeneveld@fli.bund.de
CC: Albe Laurenz <laurenz.albe@wien.gv.at>, pgsql-performance@postgresql.org
Subject: Re: fast read of binary data
References: <1352717148.3684.18.camel@eno.tzv.local>
	<D960CB61B694CF459DCFB4B0128514C208AF0471@exadv11.host.magwien.gv.at>
	<1353570844.30293.8.camel@eno.tzv.local>
In-Reply-To: <1353570844.30293.8.camel@eno.tzv.local>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 22.11.2012 09:54, Eildert Groeneveld wrote:
> ok, I did have a look at the libpq librar, and you are right, there is a
> way to obtain binary data from the backend through the  PQexecParams
>
>      res = PQexecParams(conn,
>                         "DECLARE myportal CURSOR FOR select genotype_bits
> from v_genotype_data",
>                         0,       /* zero param */
>                         NULL,    /* let the backend deduce param type */
>                         paramValues,
>                         NULL,    /* don't need param lengths since text*/
>                         NULL,    /* default to all text params */
>                         1);      /* ask for binary results */
>
> genotype_bits is defined as bit varying in the backend. When writing the
> results:
>      for (i = 0; i<  PQntuples(res); i++)
>      {
>          for (j = 0; j<  nFields; j++)
>        fwrite(PQgetvalue(res, i, j),100000,1,f);
>      }
>
> it is clear that the results are NOT in binary format:
> eg(eno,snp): od -b junk |head
> 0000000 061 060 061 060 061 060 061 060 061 060 061 060 061 060 061 060

You need to ask for binary results when you execute the FETCH 
statements. Asking for binary results on the DECLARE CURSOR statement 
has no effect, as DECLARE CURSOR doesn't return any results; it's the 
FETCH that follows that returns the result set.

- Heikki