Re: [GENERAL] Re: Cannot retrieve images inserted through VB and odbc, after a table reorganization.

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.).

Alanoly.

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Wednesday, September 03, 2014 3:10 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-admin(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)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 and when that changes it cannot find it any more.

>
> **
>
> *Alanoly.*
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com

________________________________

If you no longer wish to receive any of our emails, click on UNSUBSCRIBE.<mailto:unsubscribe(at)invera(dot)com?subject=***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.<mailto:unsubscribe(at)invera(dot)com?subject=***Unsubscribe***> 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.