BLOBS : how to remove them totally

Nilabhra Banerjee wrote:

Hi,

I am still not sure whether the BLOBS are actually
stored in the database or they have the pointer to the
database for that file in the filesystem. If I remove
the files (sources) for BLOBS from the directories
with the BLOB still hold the data ?

Also one more very intriguing part is that if BLOBS
are not deleted if we delete them from tables how to
remove them ?

Here you can find an excellent description, how BLOBs in PostgreSQL can
be handled:

http://www.varlena.com/varlena/GeneralBits/44.php

Regards
N Banerjee

Bernd

Bernd Helmle <mailings@oopsware.de> writes:

Here you can find an excellent description, how BLOBs in PostgreSQL can
be handled:
http://www.varlena.com/varlena/GeneralBits/44.php

That's a good discussion, but it left out at least one useful bit of
info about managing large objects: there's a contrib utility
(contrib/vacuumlo) that can find and remove large objects that are not
referenced anywhere in the database. This is a good way to clean up
if you've been using large objects without any of the automatic
management techniques suggested in the GeneralBits article.

regards, tom lane

Thanks a lot for the clue... Now I am comfortably
handling the Lrge Objects thru SQL...

But unfortunately I could not extract this data to
frontend thru java... I tried in two ways but got the
same error...after getting the data in Blob or Large
Object.

Error in connection == FastPath call returned ERROR:
invalid large-object descriptor: 0

1) Process One
Blob myBlob = null;
Then for resultset rs
myBlob=rs.getBlob(1);

The error is returned in any statement which processes
the Blob object like,
long myLength = myBlob.length();

2) Process Two
FIRST the largeobject manager
LargeObjectManager lobj =
((org.postgresql.PGConnection)conn).getLargeObjectAPI();
THEN in the while rs.next() loop
LargeObject obj = lobj.open(oid,
LargeObjectManager.READ);
AND THEN
InputStream input = new
BufferedInputStream(largeobj.getInputStream());

THe Error is returned in any statement that processes
the input like writing in a ouputstream
int b = -1;
while ((b = input.read()) != -1)
outputStream.write(b);

I AM PUZZLED... WHERE IS THE WRONG ? THE CODE IS NOT
COMPLAINING WHEN I GET THE VALUE FROM THE RESULT IN A
OBJECT. BUT IT IS GIVING ERROR WHEN I AM TRYING TO
READ THE OBJECT.

Regards
Nilabhra Banerjee

--- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bernd Helmle
<mailings@oopsware.de> writes:

Here you can find an excellent description, how

BLOBs in PostgreSQL can

be handled:
http://www.varlena.com/varlena/GeneralBits/44.php

That's a good discussion, but it left out at least
one useful bit of
info about managing large objects: there's a contrib
utility
(contrib/vacuumlo) that can find and remove large
objects that are not
referenced anywhere in the database. This is a good
way to clean up
if you've been using large objects without any of
the automatic
management techniques suggested in the GeneralBits
article.

regards, tom lane

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to
majordomo@postgresql.org so that your
message can get through to the mailing list

cleanly

________________________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

On Sat, 20 Dec 2003, [iso-8859-1] Nilabhra Banerjee wrote:

But unfortunately I could not extract this data to
frontend thru java... I tried in two ways but got the
same error...after getting the data in Blob or Large
Object.

Error in connection == FastPath call returned ERROR:
invalid large-object descriptor: 0

This is usually a symptom of not being in a transaction. Large objects
need to be done inside a transaction. Try adding
connection.setAutoCommit(false) somewhere in your code.

Kris Jurka

tgl@sss.pgh.pa.us says...

That's a good discussion, but it left out at least one useful bit of
info about managing large objects: there's a contrib utility
(contrib/vacuumlo) that can find and remove large objects that are not
referenced anywhere in the database.

What is the URL for the contributed stuff?

TIA.

Paul...

regards, tom lane

--

plinehan x__AT__x yahoo x__DOT__x com

C++ Builder 5 SP1, Interbase 6.0.1.6 IBX 5.04 W2K Pro

Please do not top-post.

Thanx for ur suggestion... But I face a new problem
now...

connection.setAutoCommit works well with postgresql
7.3 .. but with postgresql 7.4 I am getting the
error...
Error in connection == ERROR: SET AUTOCOMMIT TO OFF
is no longer supported

I have tried pg73jdbc1.jar and pg73jdbc3.jar .. both
gave the same error....

But in psql (7.4) the command \set AUTOCOMMIT off is
working. Strangely \set AUTOCOMMIT off is actually
changing the value of AUTOCOMMIT internal variable...
If I type \set autocommit off .. there will create
another variable 'autocommit' and set it to 'off'...
But this wont change the autocommit mode to off.. (The
documentation doesnot tell us of any such caps/small
behaviour!)

Regards
N Banerjee

 --- Kris Jurka <books@ejurka.com> wrote: > 

On Sat, 20 Dec 2003, [iso-8859-1] Nilabhra Banerjee
wrote:

But unfortunately I could not extract this data to
frontend thru java... I tried in two ways but got

the

same error...after getting the data in Blob or

Large

Object.

Error in connection == FastPath call returned

ERROR:

invalid large-object descriptor: 0

This is usually a symptom of not being in a
transaction. Large objects
need to be done inside a transaction. Try adding
connection.setAutoCommit(false) somewhere in your
code.

Kris Jurka

________________________________________________________________________
Yahoo! Messenger - Communicate instantly..."Ping"
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html

On Mon, 22 Dec 2003, [iso-8859-1] Nilabhra Banerjee wrote:

connection.setAutoCommit works well with postgresql
7.3 .. but with postgresql 7.4 I am getting the
error...
Error in connection == ERROR: SET AUTOCOMMIT TO OFF
is no longer supported

I have tried pg73jdbc1.jar and pg73jdbc3.jar .. both
gave the same error....

To access a 7.4 database you need a 7.4 jdbc driver. Try downloading one
from http://jdbc.postgresql.org/download.html

Kris Jurka