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.kla...@aklaver.com]
Sent: Wednesday, September 03, 2014 3:10 PM
To: Alanoly Andrews; 'Craig James'
Cc: pgsql-ad...@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 and when that changes it cannot find it 
any more.

>
> **
>
> *Alanoly.*
>



--
Adrian Klaver
adrian.kla...@aklaver.com

________________________________

If you no longer wish to receive any of our emails, click on 
UNSUBSCRIBE.<mailto:unsubscr...@invera.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:unsubscr...@invera.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.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to