Re: Large Objects...ODBC/VB6/ADO...& PostgreSQL

Sanjay Arora <sk(at)pobox(dot)com> writes:
> I am using postgreSQL v. 7.0.2 on RH Linux 6.2 on the server with VB6
> Application accessing the DB through postgrSQL ODBC driver v. 6.50.

> I am porting an application that used a lot of memo fields in the jet
> database engine, well over 8k limit of PG. I now need to store these using
> the large object data type of PG.

AFAIK it's pretty painful to use large objects through ODBC (but I don't
know much about ODBC, maybe there is a way?).

The record length limit will be effectively gone in 7.1, so it's a shame
to see you going to great lengths to work around it now. How far away
are you from being able to fit --- ie, what's the most data you need to
put in a row/field at the moment? Two stopgap measures you could take
are (a) rebuild Postgres with BLCKSZ 32K instead of 8K, and (b) declare
your large fields as lztext, not text or varchar, to get compression
applied to their contents. Depending on what your data looks like,
lztext might be good for a 2X or so savings on average, which would get
you up to ~64K per row. Perhaps that will be good enough to hold the
fort until 7.1 is out.

BTW, if you are going to go to the trouble of rebuilding Postgres from
source, I'd definitely recommend fetching the 7.0.3 release first.
There are some small but critical bug fixes over 7.0.2...

> Another query is that since lo operations need to be in
> transactions....does this have to be a seperate transaction dealing with
> the lo operation or can the lo operation be executed in a transaction that
> is updating other tables as well.

It just has to be a transaction; you can do whatever you want therein.
One thing to be wary of is that lo_unlink is not rollback-able in 7.0.*;
if you do an unlink, better be sure your transaction commits, rather
than failing later :-(

> Another problem I am facing is that opening a recordset using Microsoft
> data shape OLE DB with following and using the open method leads to error
> (-2147418113(8000ffff) Catastrophic Failure):

Sorry, don't know a thing about OLE or ADO. Anyone?

regards, tom lane