By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,716 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

BLOBS : how to remove them totally

P: n/a
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 ?

Regards
N Banerjee

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

P: n/a
Bernd Helmle <ma******@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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #3

P: n/a
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).getLargeObject API();
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 <tg*@sss.pgh.pa.us> wrote: > Bernd Helmle
<ma******@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
ma*******@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #4

P: n/a


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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5

P: n/a

tg*@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.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #6

P: n/a
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 <bo***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #7

P: n/a


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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.