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