Message-ID: <5407679B.2030906@aklaver.com>
Date: Wed, 03 Sep 2014 12:10:19 -0700
From: Adrian Klaver <adrian.klaver@aklaver.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:24.0) Gecko/20100101 Thunderbird/24.6.0
MIME-Version: 1.0
To: Alanoly Andrews <alanolya@invera.com>,
 'Craig James' <cjames@emolecules.com>
CC: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>,
 "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Re: Cannot retrieve images inserted through
 VB and odbc, after a table reorganization.
References: <6909b219ecae4d41b431efdaf6c953a3@Exch2.invera.com>
	<CAFwQ8rd0Abfm=0j4Oq5D0hcRPBn=D7A-HJzSPJmDk=EgDMA9zQ@mail.gmail.com>
 <a45db4656040438eaf2034a8f5870b8d@Exch2.invera.com>
In-Reply-To: <a45db4656040438eaf2034a8f5870b8d@Exch2.invera.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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


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