Re: What needs to be done?

Ricardo,

There are actually a couple of reasons why the jdbc driver can't do this:

1) The client doesn't know that the column being deleted is a blob. All
it can know is that the data type of the column is oid. Oids can be
used for many reasons, one of which is blobs. The code can't assume
that just because a column is of type oid that it represents a blob.

2) The fact that the delete of the blob is separate from the delete of
the row is actually a useful feature. The postgres blob feature
essentially treats the blob as an independent object from the table row
that holds a pointer to it. Thus you can have multiple rows of data in
the same or even different tables point to the same blob. Because of
this feature, you can't assume that when any one row is deleted that the
corresponding blob should be deleted (that decision requires an
understanding of the application data model).

Postgres as of 7.1 has 'toast' which provides a different mechanism for
storing large objects. 'toast' doesn't have the 'multiple rows can
reference the same blob' feature, and therefore 'toast' does delete the
large object when the row is deleted. However 'toast' has other
deficiencies that prevent it from being used in the JDBC driver for
BLOBs. It is my hope that in the future with some additional
functionality on the server that the JDBC driver can have a reasonable
BLOB implementation that uses the new 'toast' functionality, and the
current blob implementation is deprecated.

thanks,
--Barry

Ricardo Maia wrote:

> Why can't the JDBC Driver deal with the delete of the Blob? From the user
> point of view the BLOB is an attribute of that row and should be
> inserted/deleted with the rest of the row.
>
> The fact that postgres uses another entity to store the blob is an
> implementation issue ...
>
> Regards,
>
> Ricardo
>
> On Thursday 02 August 2001 17:37, Barry Lind wrote:
>
>>Ricardo,
>>
>>There are many other issues with postgres blobs that will not allow you
>>to acheive your goal easily. You are going to need different
>>implementations per database type to deal with the differences between
>>blob implementations across different databases. The one big hurdle you
>>will have with postgres blobs is the fact that when you delete the row
>>containing the blob, it doesn't delete the blob. You have to issue a
>>separate delete blob request. This is very different than what happens
>>in Oracle for example. This can be automated by adding triggers to the
>>table to do this, but by now you are very far from having a single code
>>base (at least the code that creates the tables and triggers) that
>>supports all of the different databases.
>>
>>thanks,
>>--Barry
>>
>>Ricardo Maia wrote:
>>
>>>So how whould I map the BLOB java type in the corresponding SQL type?
>>>
>>>I want to create a table with a BLOB attribute, but I want that my code
>>>can run for PostgreSQL, Oracle and other BD that handles BLOBs.
>>>
>>>So first I had to map the BLOB in the corresponding BD SQL type and then
>>>create the table with an attribute of that SQL type.
>>>
>>>Ricardo Maia
>>>
>>>On Thursday 02 August 2001 03:16, Barry Lind wrote:
>>>
>>>>I actually think the response for 'oid' is correct. It reports the oid
>>>>as java type integer (which is the real datatype of the value stored).
>>>>A column of type oid can be used for may different things. It can be
>>>>used for blobs, but not all columns of type oid are used for blobs.
>>>>Another use of a column of type oid is to store foreign keys from one
>>>>table to another. Since all tables have a builtin column named 'oid' of
>>>>type oid, it is very convenient to use this value in foreign keys on
>>>>other tables. Assuming that oid = blob would break those applications.
>>>>
>>>>I hope everyone that uses postgresql and jdbc understands that BLOB
>>>>support is one area with many problems, some of which can be fixed in
>>>>the JDBC code, but others that will require better support in the
>>>>underlying database.
>>>>
>>>>thanks,
>>>>--Barry
>>>>
>>>>Ricardo Maia wrote:
>>>>
>>>>>For example when I call the method:
>>>>>
>>>>>DatabaseMetaData.getTypeInfo()
>>>>>
>>>>>I whould expect to see the SQL Type BLOB mapped as an oid.
>>>>>
>>>>>see attach
>>>>>
>>>>>Ricardo Maia
>>>>>
>>>>>On Wednesday 01 August 2001 23:29, Rene Pijlman wrote:
>>>>>
>>>>>>On Wed, 1 Aug 2001 22:49:40 +0100, Ricardo Maia wrote:
>>>>>>
>>>>>>>The problem is that, as the PostgreSQL JDBC driver doesn't
>>>>>>>follow JDBC Standard I had to write some specific code for
>>>>>>>use it with PostgreSQL DB.
>>>>>>>
>>>>>>So what exactly are the deviations from the standard that you
>>>>>>encountered?
>>>>>>
>>>>>>Regards,
>>>>>>René Pijlman
>>>>>>
>>>>>>---------------------------(end of
>>>>>>broadcast)--------------------------- TIP 1: subscribe and unsubscribe
>>>>>>commands go to majordomo(at)postgresql(dot)org
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>-
>>>>>>
>>>>>>package databasetest;
>>>>>>
>>>>>>import java.sql.*;
>>>>>>
>>>>>>public class GetTypesInfo {
>>>>>>
>>>>>>public static void main(String args[ ]) {
>>>>>>
>>>>>> String url = "jdbc:postgresql://127.0.0.1/test";
>>>>>>
>>>>>> Connection con;
>>>>>>
>>>>>> DatabaseMetaData dbmd;
>>>>>>
>>>>>> try {
>>>>>> Class.forName("org.postgresql.Driver");
>>>>>> } catch(java.lang.ClassNotFoundException e) {
>>>>>> System.err.print("ClassNotFoundException: ");
>>>>>> System.err.println(e.getMessage());
>>>>>> }
>>>>>>
>>>>>> try {
>>>>>> con = DriverManager.getConnection(url,"bobby", "tareco");
>>>>>>
>>>>>> dbmd = con.getMetaData();
>>>>>>
>>>>>> ResultSet rs = dbmd.getTypeInfo();
>>>>>>
>>>>>> while (rs.next()) {
>>>>>>
>>>>>> String typeName = rs.getString("TYPE_NAME");
>>>>>>
>>>>>> short dataType = rs.getShort("DATA_TYPE");
>>>>>>
>>>>>> String createParams = rs.getString("CREATE_PARAMS");
>>>>>>
>>>>>> int nullable = rs.getInt("NULLABLE");
>>>>>>
>>>>>> boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
>>>>>>
>>>>>> if(dataType != java.sql.Types.OTHER)
>>>>>> {
>>>>>> System.out.println("DBMS type " + typeName + ":");
>>>>>> System.out.println(" java.sql.Types: " +
>>>>>>typeName(dataType)); System.out.print(" parameters used to create:
>>>>>>");
>>>>>> System.out.println(createParams);
>>>>>> System.out.println(" nullable?: " + nullable);
>>>>>> System.out.print(" case sensitive?: ");
>>>>>> System.out.println(caseSensitive);
>>>>>> System.out.println("");
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> con.close();
>>>>>> } catch(SQLException ex) {
>>>>>> System.err.println("SQLException: " + ex.getMessage());
>>>>>> }
>>>>>>}
>>>>>>
>>>>>>
>>>>>>public static String typeName(int i)
>>>>>>{
>>>>>> switch(i){
>>>>>> case java.sql.Types.ARRAY: return "ARRAY";
>>>>>> case java.sql.Types.BIGINT: return "BIGINT";
>>>>>> case java.sql.Types.BINARY: return "BINARY";
>>>>>> case java.sql.Types.BIT: return "BIT";
>>>>>> case java.sql.Types.BLOB: return "BLOB";
>>>>>> case java.sql.Types.CHAR: return "CHAR";
>>>>>> case java.sql.Types.CLOB: return "CLOB";
>>>>>> case java.sql.Types.DATE: return "DATE";
>>>>>> case java.sql.Types.DECIMAL: return "DECIMAL";
>>>>>> case java.sql.Types.DISTINCT: return "DISTINCT";
>>>>>> case java.sql.Types.DOUBLE: return "DOUBLE";
>>>>>> case java.sql.Types.FLOAT: return "FLOAT";
>>>>>> case java.sql.Types.INTEGER: return "INTEGER";
>>>>>> case java.sql.Types.JAVA_OBJECT: return "JAVA_OBJECT";
>>>>>> case java.sql.Types.LONGVARBINARY: return "LONGVARBINARY";
>>>>>> case java.sql.Types.LONGVARCHAR: return "LONGVARCHAR";
>>>>>> case java.sql.Types.NULL: return "NULL";
>>>>>> case java.sql.Types.NUMERIC: return "NUMERIC";
>>>>>> case java.sql.Types.OTHER: return "OTHER";
>>>>>> case java.sql.Types.REAL: return "REAL";
>>>>>> case java.sql.Types.REF: return "REF";
>>>>>> case java.sql.Types.SMALLINT: return "SMALLINT";
>>>>>> case java.sql.Types.STRUCT: return "STRUCT";
>>>>>> case java.sql.Types.TIME: return "TIME";
>>>>>> case java.sql.Types.TIMESTAMP: return "TIMESTAMP";
>>>>>> case java.sql.Types.TINYINT: return "TINYINT";
>>>>>> case java.sql.Types.VARBINARY: return "VARBINARY";
>>>>>> case java.sql.Types.VARCHAR: return "VARCHAR";
>>>>>> default: return "";
>>>>>> }
>>>>>>}
>>>>>>}
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>-
>>>>>>
>>>>>>
>>>>>>---------------------------(end of
>>>>>>broadcast)--------------------------- TIP 5: Have you checked our
>>>>>>extensive FAQ?
>>>>>>
>>>>>>http://www.postgresql.org/users-lounge/docs/faq.html
>>>>>>
>>>>>>GetTypesInfo.java
>>>>>>
>>>>>>Content-Type:
>>>>>>
>>>>>>text/x-java
>>>>>>Content-Encoding:
>>>>>>
>>>>>>base64
>>>>>>
>>>>>>
>>>>>>-----------------------------------------------------------------------
>>>>>>- Part 1.3
>>>>>>
>>>>>>Content-Type:
>>>>>>
>>>>>>text/plain
>>>>>>Content-Encoding:
>>>>>>
>>>>>>binary
>>>>>>
>>>>---------------------------(end of broadcast)---------------------------
>>>>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 2: you can get off all lists at once with the unregister command
>>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>>>
>