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