Обсуждение: Cannot retrieve images inserted through VB and odbc, after a table reorganization.

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

On 09/03/2014 09:12 AM, Craig James wrote:
>
> On Wed, Sep 3, 2014 at 8:13 AM, Alanoly Andrews <alanolya@invera.com
> <mailto:alanolya@invera.com>> wrote:
>
>     *We have an issue with images (**TIFF files) that are loaded into a
>     postgres database table (where the image field is defined as type
>     **“**lo**”**)**. The code to load the images is written in VB and
>     the connection to the database is through postgres odbc**installed
>     on the PC. ****All images inserted through the VB program are
>     retrievable also through odbc/VB, as long as the table **rows **at
>     the backend **are**not re**organized in any way. If the table
>     dat**a**is unloaded/reloaded or clustered (on an index) or
>     rel**oaded through a pg_dump and restore, then **on trying to
>     retrieve the images, **the VB program gets the error :
>     **“**Multiple-step OLE DB operation generat**ed errors. Check each
>     OLE DB status value**……**etc**”**.*
>     *The data in the backend database is still readable through other
>     image retrieval progr**ams connecting directly to the server (not
>     through ODBC or VB).*
>
>
> Are you reloading into the very same database, or into a different one?
> Your question doesn't make it clear. If you're loading into a new
> database, then one thing that comes to mind is that the bytea_output
> parameter in postgresql.conf might be different on the second database.

The OP is using the lo type, so I suspect it is something to do with the
OIDs changing. I do not use lo so I cannot really be more helpful.

>
> Craig


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/03/2014 09:26 AM, Alanoly Andrews wrote:
> *Hi Craig,*
>
> **
>
> *I’m reloading into the very same database. *
>
> *If I insert a new row into the table (through VB/ODBC), I’m able to
> retrieve it (again from VB). But if after inserting the new row, I do a
> reorg of the table (eg. with a “cluster table”), I’m no longer able to
> retrieve that same row. In short, as long as the newly inserted rows are
> not “re-written” in some way, they can be retrieved through VB/ODBC. *

To follow up on my previous post with regards to OIDs. That may have
been a wrong fork. A little testing:

test=# CREATE TABLE image (
     name            text,
     raster          oid
);

test=# create index img_idx on image (raster);

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0359JPG'));

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0360.JPG'));

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image',
lo_import('/home/aklaver/Pictures/IMG_0361.JPG'));

test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
--------+-------------
  532144 |      532175

test=# cluster image using img_idx;
CLUSTER
test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
--------+-------------
  532144 |      532182

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use
--oids with pg_dump).

So to revise my previous statement, my guess is your VB/ODBC code is
using the relfilenode value to refer to the table and when that changes
it cannot find it any more.

>
> **
>
> *Alanoly.*
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/03/2014 09:26 AM, Alanoly Andrews wrote:
> *Hi Craig,*
>
> **
>
> *I’m reloading into the very same database. *
>
> *If I insert a new row into the table (through VB/ODBC), I’m able to
> retrieve it (again from VB). But if after inserting the new row, I do a
> reorg of the table (eg. with a “cluster table”), I’m no longer able to
> retrieve that same row. In short, as long as the newly inserted rows are
> not “re-written” in some way, they can be retrieved through VB/ODBC. *

This:

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use
--oids with pg_dump).

should be:

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(if you use
--oids with pg_dump).


>
> **
>
> *Alanoly.*
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that
thishappens because the table rows are being physically moved from one location to another. But such changes at the
backendshould be transparent to the end user. The VB code at the client side runs a simple sql like "select image from
image_tablewhere image_key=somevalue". There is no reference to postgres-specific internal variables like "oid" and
"relfilenode".I do not know the inner workings of the postgres odbc driver; but I would be surprised if it works at the
granularityof "oid" and "relfilenode" and that it would store the actual physical values of relfilenode (which would
keepchanging after every table reload, reorg etc.).
 

Alanoly.

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, September 03, 2014 3:10 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

On 09/03/2014 09:26 AM, Alanoly Andrews wrote:
> *Hi Craig,*
>
> **
>
> *I’m reloading into the very same database. *
>
> *If I insert a new row into the table (through VB/ODBC), I’m able to
> retrieve it (again from VB). But if after inserting the new row, I do
> a reorg of the table (eg. with a “cluster table”), I’m no longer able
> to retrieve that same row. In short, as long as the newly inserted
> rows are not “re-written” in some way, they can be retrieved through
> VB/ODBC. *

To follow up on my previous post with regards to OIDs. That may have been a wrong fork. A little testing:

test=# CREATE TABLE image (
     name            text,
     raster          oid
);

test=# create index img_idx on image (raster);

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0359JPG'));

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0360.JPG'));

test=# INSERT INTO image (name, raster)
     VALUES ('another beautiful image', lo_import('/home/aklaver/Pictures/IMG_0361.JPG'));

test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
--------+-------------
  532144 |      532175

test=# cluster image using img_idx;
CLUSTER
test=# select oid, relfilenode from pg_class where relname ='image';
   oid   | relfilenode
--------+-------------
  532144 |      532182

Note the OID for the table stays the same but the relfilenode changes.
This also happens with the other situations you describe(unless you use --oids with pg_dump).

So to revise my previous statement, my guess is your VB/ODBC code is using the relfilenode value to refer to the table
andwhen that changes it cannot find it any more.
 

>
> **
>
> *Alanoly.*
>



--
Adrian Klaver
adrian.klaver@aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on
UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>This e-mail may be privileged and/or confidential,
andthe sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the
informationit contains by other than an intended recipient is unauthorized. If you received this e-mail in error,
pleaseadvise me (by return e-mail or otherwise) immediately.
 

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur
DÉSABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>Ce courriel est confidentiel et protégé.
L'expéditeurne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce
messageou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est
interdite.Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un
autremoyen.
 

You are going to have to help us by providing server (and any other) logs
with complete error messages and VB/ODBC code, with corresponding schema,
that will reliably reproduce the problem.

Note that since you are on an ancient 9.1 release it is possible that, if
this is indeed a bug, this has already been fixed in one of the 10
subsequent releases.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817743.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 09/04/2014 05:56 AM, Alanoly Andrews wrote:
> Hi Adrian,
>
> Thanks for that explanation of how the "relfilenode" changes after a table reorganization. It is not surprising that
thishappens because the table rows are being physically moved from one location to another. But such changes at the
backendshould be transparent to the end user. The VB code at the client side runs a simple sql like "select image from
image_tablewhere image_key=somevalue". There is no reference to postgres-specific internal variables like "oid" and
"relfilenode".I do not know the inner workings of the postgres odbc driver; but I would be surprised if it works at the
granularityof "oid" and "relfilenode" and that it would store the actual physical values of relfilenode (which would
keepchanging after every table reload, reorg etc.). 

In addition to what David mentioned, some more detail on what you are
calling a 'large object'.  In Postgres there is not really a 'large
object' type(though I am guilty of saying there is), instead there is a
way of working with large objects outside of the bytea type. Now in the
ODBC FAQ there is a recipe for creating a 'lo' type:

http://psqlodbc.projects.pgfoundry.org/faq.html#4.4

Is this what you are using?

If not how are you working with the large objects?

Also given that you are working with the Postgres ODBC driver, you might
want to break the cross post rule and ask this question on the psql-odbc
list:

http://www.postgresql.org/list/pgsql-odbc/

In any case your ODBC settings would be helpful.

>
> Alanoly.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver-4 wrote
> On 09/04/2014 05:56 AM, Alanoly Andrews wrote:
>> Hi Adrian,
>>
>> Thanks for that explanation of how the "relfilenode" changes after a
>> table reorganization. It is not surprising that this happens because the
>> table rows are being physically moved from one location to another. But
>> such changes at the backend should be transparent to the end user. The VB
>> code at the client side runs a simple sql like "select image from
>> image_table where image_key=somevalue". There is no reference to
>> postgres-specific internal variables like "oid" and "relfilenode". I do
>> not know the inner workings of the postgres odbc driver; but I would be
>> surprised if it works at the granularity of "oid" and "relfilenode" and
>> that it would store the actual physical values of relfilenode (which
>> would keep changing after every table reload, reorg etc.).
>
> In addition to what David mentioned, some more detail on what you are
> calling a 'large object'.  In Postgres there is not really a 'large
> object' type(though I am guilty of saying there is), instead there is a
> way of working with large objects outside of the bytea type. Now in the
> ODBC FAQ there is a recipe for creating a 'lo' type:
>
> http://psqlodbc.projects.pgfoundry.org/faq.html#4.4
>
> Is this what you are using?
>
> If not how are you working with the large objects?
>
> Also given that you are working with the Postgres ODBC driver, you might
> want to break the cross post rule and ask this question on the psql-odbc
> list:
>
> http://www.postgresql.org/list/pgsql-odbc/
>
> In any case your ODBC settings would be helpful.

I suspect the OP is referring to the contrib module/extension:

http://www.postgresql.org/docs/9.1/interactive/lo.html

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Cannot-retrieve-images-inserted-through-VB-and-odbc-after-a-table-reorganization-tp5817580p5817761.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE. This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur DÉSABONNEMENT. Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un autre moyen.

On 09/04/2014 07:15 AM, David G Johnston wrote:
> Adrian Klaver-4 wrote
>> On 09/04/2014 05:56 AM, Alanoly Andrews wrote:
>>> Hi Adrian,
>>>
>>> Thanks for that explanation of how the "relfilenode" changes after a
>>> table reorganization. It is not surprising that this happens because the
>>> table rows are being physically moved from one location to another. But
>>> such changes at the backend should be transparent to the end user. The VB
>>> code at the client side runs a simple sql like "select image from
>>> image_table where image_key=somevalue". There is no reference to
>>> postgres-specific internal variables like "oid" and "relfilenode". I do
>>> not know the inner workings of the postgres odbc driver; but I would be
>>> surprised if it works at the granularity of "oid" and "relfilenode" and
>>> that it would store the actual physical values of relfilenode (which
>>> would keep changing after every table reload, reorg etc.).
>>
>> In addition to what David mentioned, some more detail on what you are
>> calling a 'large object'.  In Postgres there is not really a 'large
>> object' type(though I am guilty of saying there is), instead there is a
>> way of working with large objects outside of the bytea type. Now in the
>> ODBC FAQ there is a recipe for creating a 'lo' type:
>>
>> http://psqlodbc.projects.pgfoundry.org/faq.html#4.4
>>
>> Is this what you are using?
>>
>> If not how are you working with the large objects?
>>
>> Also given that you are working with the Postgres ODBC driver, you might
>> want to break the cross post rule and ask this question on the psql-odbc
>> list:
>>
>> http://www.postgresql.org/list/pgsql-odbc/
>>
>> In any case your ODBC settings would be helpful.
>
> I suspect the OP is referring to the contrib module/extension:
>
> http://www.postgresql.org/docs/9.1/interactive/lo.html

Aaah, did not know that existed, thanks.

>
> David J.
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 09/04/2014 08:03 AM, Alanoly Andrews wrote:
> Thanks, Adrian for the response.
>
> Yes, we are using the "large object" as per the specifications in the
> special "lo" module that we installed on the backend server. The table
> is created using the "lo" datatype for the image field and the table is
> being regularly used for insertions and retrievals using utilities other
> than ones using odbc. The odbc connection, too, works well on the table,
> as long as the table data is not reorganized after insertion(s).
>
> Here are three pages of our postgres odbc driver settings. Sorry, they
> are screen captures,  not plain text!

Hmm, to me at least nothing seems out of place. Though to be honest I am
not sure what 'Bytea as LO' does in this situation.

Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly
generic and covers a lot of different error sources.

So, is there any more specific error information in either the Postgres
or ODBC logs?

>
> Alanoly.


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

1. We are storing our large objects in a table column whose datatype is "lo" (which, I understand, ultimately is in
fact"bytea").
 
2. The error messge returned to the VB is, yes, very generic. And the backend postgres server does not have any
messagesrelevant to this issue.
 
3. We set the trace on in the odbc driver and got a large log file, from which I give below the part that I think is
significant.It refers to the "buffer size being too small". Where, in the odc settings, do we set the buffer size?
 


STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                        1 <SQL_C_CHAR>
                                PTR                 0x0018EECF [       0] ""
                                SQLLEN                     0
                                SQLLEN *            0x0018EEA4 (6)

                                DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec          ENTER SQLGetData
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                     0
                                SQLLEN *            0x0018EE54

STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                     0
                                SQLLEN *            0x0018EE54 (46165)

                                DIAG [01004] The buffer was too small for the GetData. (-2)

STELimageViewOn 834-fec          ENTER SQLGetData
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                 32768
                                SQLLEN *            0x003CE284

STELimageViewOn 834-fec          EXIT  SQLGetData  with return code -1 (SQL_ERROR)
                                HSTMT               0x002BFC70
                                UWORD                        2
                                SWORD                       -2 <SQL_C_BINARY>
                                PTR                 <unknown type>
                                SQLLEN                 32768
                                SQLLEN *            0x003CE284

                                DIAG [S1000] Error reading from large object. (1)

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, September 04, 2014 4:43 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-admin@postgresql.org; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Re: [ADMIN] Cannot retrieve images inserted through VB and odbc, after a table reorganization.

On 09/04/2014 08:03 AM, Alanoly Andrews wrote:
> Thanks, Adrian for the response.
>
> Yes, we are using the "large object" as per the specifications in the
> special "lo" module that we installed on the backend server. The table
> is created using the "lo" datatype for the image field and the table
> is being regularly used for insertions and retrievals using utilities
> other than ones using odbc. The odbc connection, too, works well on
> the table, as long as the table data is not reorganized after insertion(s).
>
> Here are three pages of our postgres odbc driver settings. Sorry, they
> are screen captures,  not plain text!

Hmm, to me at least nothing seems out of place. Though to be honest I am not sure what 'Bytea as LO' does in this
situation.

Are you actually trying to store any large objects in bytea columns?

I searched on the error message you originally posted, but is fairly generic and covers a lot of different error
sources.

So, is there any more specific error information in either the Postgres or ODBC logs?

>
> Alanoly.


--
Adrian Klaver
adrian.klaver@aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on
UNSUBSCRIBE.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>This e-mail may be privileged and/or confidential,
andthe sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the
informationit contains by other than an intended recipient is unauthorized. If you received this e-mail in error,
pleaseadvise me (by return e-mail or otherwise) immediately.
 

Si vous ne désirez plus recevoir de nos courriels, veuillez appuyer sur
DÉSABONNEMENT.<mailto:unsubscribe@invera.com?subject=***Unsubscribe***>Ce courriel est confidentiel et protégé.
L'expéditeurne renonce pas aux droits et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce
messageou des renseignements qu'il contient par une personne autre que le (les) destinataire(s) désigné(s) est
interdite.Si vous recevez ce courriel par erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un
autremoyen.
 

On 09/05/2014 08:33 AM, Alanoly Andrews wrote:
> Hi Adrian,
>
> 1. We are storing our large objects in a table column whose datatype is "lo" (which, I understand, ultimately is in
fact"bytea"). 

As way of testing where the issue is, you might explore  saving some
images in a test table directly to a bytea column. This is presumably
what the Bytea as LO option is for. Then run CLUSTER on the test table
and see what happens.

> 2. The error messge returned to the VB is, yes, very generic. And the backend postgres server does not have any
messagesrelevant to this issue. 
> 3. We set the trace on in the odbc driver and got a large log file, from which I give below the part that I think is
significant.It refers to the "buffer size being too small". Where, in the odc settings, do we set the buffer size? 
>

I would recommend posting the below(with some background information) on
the -odbc list where there are folks that might better understand it.

>
> STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
>                                  HSTMT               0x002BFC70
>                                  UWORD                        2
>                                  SWORD                        1 <SQL_C_CHAR>
>                                  PTR                 0x0018EECF [       0] ""
>                                  SQLLEN                     0
>                                  SQLLEN *            0x0018EEA4 (6)
>
>                                  DIAG [01004] The buffer was too small for the GetData. (-2)
>
> STELimageViewOn 834-fec          ENTER SQLGetData
>                                  HSTMT               0x002BFC70
>                                  UWORD                        2
>                                  SWORD                       -2 <SQL_C_BINARY>
>                                  PTR                 <unknown type>
>                                  SQLLEN                     0
>                                  SQLLEN *            0x0018EE54
>
> STELimageViewOn 834-fec          EXIT  SQLGetData  with return code 1 (SQL_SUCCESS_WITH_INFO)
>                                  HSTMT               0x002BFC70
>                                  UWORD                        2
>                                  SWORD                       -2 <SQL_C_BINARY>
>                                  PTR                 <unknown type>
>                                  SQLLEN                     0
>                                  SQLLEN *            0x0018EE54 (46165)
>
>                                  DIAG [01004] The buffer was too small for the GetData. (-2)
>
> STELimageViewOn 834-fec          ENTER SQLGetData
>                                  HSTMT               0x002BFC70
>                                  UWORD                        2
>                                  SWORD                       -2 <SQL_C_BINARY>
>                                  PTR                 <unknown type>
>                                  SQLLEN                 32768
>                                  SQLLEN *            0x003CE284
>
> STELimageViewOn 834-fec          EXIT  SQLGetData  with return code -1 (SQL_ERROR)
>                                  HSTMT               0x002BFC70
>                                  UWORD                        2
>                                  SWORD                       -2 <SQL_C_BINARY>
>                                  PTR                 <unknown type>
>                                  SQLLEN                 32768
>                                  SQLLEN *            0x003CE284
>
>                                  DIAG [S1000] Error reading from large object. (1)


--
Adrian Klaver
adrian.klaver@aklaver.com