473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

BLOBS : how to remove them totally

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
7 3989
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
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
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


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

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
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


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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Kirby Urner | last post by:
I've been testing the Cookbook example 8.6 (2002 edition) re using cPickle to insert and retrieve BLOBs from mySQL, using Python's MySQLdb module. When I try to cPickle.loads(blob), I get an...
0
by: Ole Hansen | last post by:
Hi, I have a working application inserting rows to a table using the array interface. Now I want to insert BLOBs as well but OCIStmtExecute crashes when executed with BLOBs. For BLOB types I...
0
by: Ole Hansen | last post by:
Hi, Is it at all possible to insert BLOBs using the Array Interface? Today I have an application using the array interface. It works fine but so far I haven't been using BLOBs. I insert...
1
by: picaza | last post by:
hi, does anyone have a perl example of loading blobs from either a bin file or an input stream? much thanks, peter
7
by: Howard Lowndes | last post by:
My situation is that I am interacting PHP 4.1.2 to PostgreSQL 7.2.2 I have no difficulty inserting and managing BLOBs into the Large Object system table, and I have a user table called images...
0
by: Bing | last post by:
Hi there, I am using the DB2 universal JDBC driver type 4 to insert BLOBs into a DB2 database. The Method I used for supplying the BLOB data value is setBinaryStream(). Everything works fine as...
1
by: Kevin | last post by:
Hello - Quick question - does anyone know of a good way to archive a blob? Some background - Our application (Java Web Application) uses a DB2 table with a blob column of length 75000 to store...
2
by: Jerry LeVan | last post by:
Hi, I am just getting into large objects and bytea "stuff". I created a small db called pictures and loaded some large objects and then tried to do a restore. Here is how I got the dump. ...
5
by: bhodgins | last post by:
Hi, I am new on here, and had a newbie question that I am stumped with. I am not new to access, but am new to VB. I am trying to export BLOBs from a field called photo to external jpeg files. I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.