Thread: JDBC patch (attempt#2) for util.Serialize and jdbc2.PreparedStatement

(attempt #2) Don't use serializepatch.tgz that I sent before, use the file
attached now to this email instead.

The attached file: SerializePatch2.tgz, contains a patch for
org.postgresql.util.Serialize and org.postgresql.jdbc2.PreparedStatement that
fixes the ability to "serialize" a simple java class into a postgres table.

The current cvs seems completely broken in this support, so the patch puts it
into working condition, granted that there are many limitations with
serializing java classes into Postgres.

A little test program is included with the patches.  The comments in the
Serialize class tries to explain how all this works.

Note:
The code to do serialize appears to have been in the driver since Postgres
6.4, according to some comments in the source.  My code is not adding any
totally new ability to the driver, rather just fixing what is there so that
it actually is usable.  I do not think that it should affect any existing
functions of the driver that people regularly depend on.

The code is activated if you use jdbc2.PreparedStatement and try to setObject
some java class type that is unrecognized, like not String or not some other
primitive type.  This will cause a sequence of function calls that results in
an instance of Serialize being instantiated for the class type passed.  The
Serialize constructor will query pg_class to see if it can find an existing
table that matches the name of the java class. If found, it will continue and
try to use the table to store the object, otherwise an SQL exception is
thrown and no harm is done.  Serialize.create() has to be used to setup the
table for a java class before anything can really happen with this code other
than an SQLException (unless by some freak chance a table exists that it
thinks it can use).

I saw a difference in Serialize.java between 7.1.3 and 7.2devel that I didn't
notice before, so I had to redo my changes from the 7.2devel version (why I
had to resend this patch now).  I was missing the fixString stuff, which is
nice and is imporant to ensure the inserts will not fail due to embedded
single quote or unescaped backslashes. I changed that fixString function in
Serialize just a little since there is no need to muddle with escaping
newlines: only escaping single quote and literal backslashes is needed.
Postgres appears to insert newlines within strings without trouble.

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

> (attempt #2) Don't use serializepatch.tgz that I sent before, use the file
> attached now to this email instead.
>
> The attached file: SerializePatch2.tgz, contains a patch for
> org.postgresql.util.Serialize and org.postgresql.jdbc2.PreparedStatement that
> fixes the ability to "serialize" a simple java class into a postgres table.
>
> The current cvs seems completely broken in this support, so the patch puts it
> into working condition, granted that there are many limitations with
> serializing java classes into Postgres.
>
> A little test program is included with the patches. ?The comments in the
> Serialize class tries to explain how all this works.
>
> Note:
> The code to do serialize appears to have been in the driver since Postgres
> 6.4, according to some comments in the source.  My code is not adding any
> totally new ability to the driver, rather just fixing what is there so that
> it actually is usable.  I do not think that it should affect any existing
> functions of the driver that people regularly depend on.
>
> The code is activated if you use jdbc2.PreparedStatement and try to setObject
> some java class type that is unrecognized, like not String or not some other
> primitive type.  This will cause a sequence of function calls that results in
> an instance of Serialize being instantiated for the class type passed.  The
> Serialize constructor will query pg_class to see if it can find an existing
> table that matches the name of the java class. If found, it will continue and
> try to use the table to store the object, otherwise an SQL exception is
> thrown and no harm is done.  Serialize.create() has to be used to setup the
> table for a java class before anything can really happen with this code other
> than an SQLException (unless by some freak chance a table exists that it
> thinks it can use).
>
> I saw a difference in Serialize.java between 7.1.3 and 7.2devel that I didn't
> notice before, so I had to redo my changes from the 7.2devel version (why I
> had to resend this patch now).  I was missing the fixString stuff, which is
> nice and is imporant to ensure the inserts will not fail due to embedded
> single quote or unescaped backslashes. I changed that fixString function in
> Serialize just a little since there is no need to muddle with escaping
> newlines: only escaping single quote and literal backslashes is needed.
> Postgres appears to insert newlines within strings without trouble.
>

[ Attachment, skipping... ]

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Both diffs applied.  Test files not installed.  Thanks.

> (attempt #2) Don't use serializepatch.tgz that I sent before, use the file
> attached now to this email instead.
>
> The attached file: SerializePatch2.tgz, contains a patch for
> org.postgresql.util.Serialize and org.postgresql.jdbc2.PreparedStatement that
> fixes the ability to "serialize" a simple java class into a postgres table.
>
> The current cvs seems completely broken in this support, so the patch puts it
> into working condition, granted that there are many limitations with
> serializing java classes into Postgres.
>
> A little test program is included with the patches. ?The comments in the
> Serialize class tries to explain how all this works.
>
> Note:
> The code to do serialize appears to have been in the driver since Postgres
> 6.4, according to some comments in the source.  My code is not adding any
> totally new ability to the driver, rather just fixing what is there so that
> it actually is usable.  I do not think that it should affect any existing
> functions of the driver that people regularly depend on.
>
> The code is activated if you use jdbc2.PreparedStatement and try to setObject
> some java class type that is unrecognized, like not String or not some other
> primitive type.  This will cause a sequence of function calls that results in
> an instance of Serialize being instantiated for the class type passed.  The
> Serialize constructor will query pg_class to see if it can find an existing
> table that matches the name of the java class. If found, it will continue and
> try to use the table to store the object, otherwise an SQL exception is
> thrown and no harm is done.  Serialize.create() has to be used to setup the
> table for a java class before anything can really happen with this code other
> than an SQLException (unless by some freak chance a table exists that it
> thinks it can use).
>
> I saw a difference in Serialize.java between 7.1.3 and 7.2devel that I didn't
> notice before, so I had to redo my changes from the 7.2devel version (why I
> had to resend this patch now).  I was missing the fixString stuff, which is
> nice and is imporant to ensure the inserts will not fail due to embedded
> single quote or unescaped backslashes. I changed that fixString function in
> Serialize just a little since there is no need to muddle with escaping
> newlines: only escaping single quote and literal backslashes is needed.
> Postgres appears to insert newlines within strings without trouble.
>

[ Attachment, skipping... ]

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

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Robert,


Thanks for the patch.  While I didn't review it too closely, what I saw
seemed fine.

However, I have concerns about the feature as a whole, which really has
nothing to do with your patch.

I don't like the approach taken here for serialization.  It seems to me
that this is a half baked attempt to reimplement native java
serialization.  Why not just use java serialization to do what it was
intended for?  We could then store the results of the serialization in a
bytea column, or a LargeObject, or even in a separate table as is done
here.  However, I am unsure of the desireabilty of creating these
additional tables.  The bigest reason I don't like the additional tables
is that the serialized objects don't ever get deleted.

To the extent that this is documented, I think the feature should be
marked as experimental with a caution that it may be changed in major
non-backwardly compatible ways in the future.

thanks,
--Barry


Robert B. Easter wrote:
> (attempt #2) Don't use serializepatch.tgz that I sent before, use the file
> attached now to this email instead.
>
> The attached file: SerializePatch2.tgz, contains a patch for
> org.postgresql.util.Serialize and org.postgresql.jdbc2.PreparedStatement that
> fixes the ability to "serialize" a simple java class into a postgres table.
>
> The current cvs seems completely broken in this support, so the patch puts it
> into working condition, granted that there are many limitations with
> serializing java classes into Postgres.
>
> A little test program is included with the patches.  The comments in the
> Serialize class tries to explain how all this works.
>
> Note:
> The code to do serialize appears to have been in the driver since Postgres
> 6.4, according to some comments in the source.  My code is not adding any
> totally new ability to the driver, rather just fixing what is there so that
> it actually is usable.  I do not think that it should affect any existing
> functions of the driver that people regularly depend on.
>
> The code is activated if you use jdbc2.PreparedStatement and try to setObject
> some java class type that is unrecognized, like not String or not some other
> primitive type.  This will cause a sequence of function calls that results in
> an instance of Serialize being instantiated for the class type passed.  The
> Serialize constructor will query pg_class to see if it can find an existing
> table that matches the name of the java class. If found, it will continue and
> try to use the table to store the object, otherwise an SQL exception is
> thrown and no harm is done.  Serialize.create() has to be used to setup the
> table for a java class before anything can really happen with this code other
> than an SQLException (unless by some freak chance a table exists that it
> thinks it can use).
>
> I saw a difference in Serialize.java between 7.1.3 and 7.2devel that I didn't
> notice before, so I had to redo my changes from the 7.2devel version (why I
> had to resend this patch now).  I was missing the fixString stuff, which is
> nice and is imporant to ensure the inserts will not fail due to embedded
> single quote or unescaped backslashes. I changed that fixString function in
> Serialize just a little since there is no need to muddle with escaping
> newlines: only escaping single quote and literal backslashes is needed.
> Postgres appears to insert newlines within strings without trouble.
>
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(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
>
> SerializePatch2.tgz
>
> Content-Type:
>
> application/x-gzip
> Content-Encoding:
>
> base64
>
>
> ------------------------------------------------------------------------
> Part 1.3
>
> Content-Type:
>
> text/plain
> Content-Encoding:
>
> binary
>
>

A CREATE TYPE/DROP TYPE bug is reported below. DROP TYPE allows a type to be
dropped even when it is used as a column in an existing table.

On Wednesday 05 September 2001 15:11, Barry Lind wrote:
> serialization.  Why not just use java serialization to do what it was
> intended for?  We could then store the results of the serialization in a
> bytea column, or a LargeObject, or even in a separate table as is done
> here.  However, I am unsure of the desireabilty of creating these
> additional tables.  The bigest reason I don't like the additional tables
> is that the serialized objects don't ever get deleted.
>

Using the serializable interface and writeObject routines could work well and
the problem of deleting would be gone. I was thinking that CREATE TYPE could
be used in Seralize.create() to make the specialized java class types in the
database, reusing the textin and textout functions (or other appropriate
functions):

CREATE TYPE myjavatype ( input = textin, output = textout, internallength =
variable );

This user-defined type would be used in tables that hold the serialized
(maybe base64 encoded) binary string. These types would be unrecognized and
default to being (de)serialized in (get)setObject like now.

It might be possible to create some basic operators for these types with
CREATE OPERATOR to allow =, > etc, and even the ability to index on these
custom types. Some special functions might be required for this though, but
gets complicated when you try to cast between types. In the end, it looks
like each type has to have its own low-level C functions to handle casting
and operators unless I'm overlooking something.

Changing the code to work like this has the advantage that the object is
simpler to serialize and the deletion problem is fixed.  However, these
pg_type entries for the java types stored would clutter the system tables. At
some time, when classes are not stored anymore, someone would want to DROP
TYPE. Hmm, I just noticed something odd:

CREATE TYPE footype (input=textin,output=textout,internallength=variable);
CREATE
CREATE TABLE holdsfoo (f footype);
CREATE
\d holdsfoo
        Table "holdsfoo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 f         | footype |
DROP TYPE footype;
DROP
\d holdsfoo
      Table "holdsfoo"
 Attribute | Type | Modifier
-----------+------+----------
 f         | ???  |

WOOPS! ??? bad type! Scary to try inserting something here.
(Postgres 7.1.3 and cvs do this)

However, for a table-type:

CREATE TABLE footype (t TEXT);
CREATE
CREATE TABLE holdsfoo (f footype);
CREATE
DROP TYPE footype;
ERROR:  RemoveType: type '_footype' does not exist
DROP TABLE footype;
ERROR:  DeleteTypeTuple: att of type footype exists in relation 37836

This appears to be more robust.

The advantage of the way it works right now is that the tables that are
produced can be accessed by other non-Java programs. The problem above could
be a consideration too.

> To the extent that this is documented, I think the feature should be
> marked as experimental with a caution that it may be changed in major
> non-backwardly compatible ways in the future.

Whoever uses it should definitely use it with caution. In the javadoc for
Serialize I expressed this. Marking it as experimental would be ok.

I'd like to find time to read official JDBC/J2EE etc. standards documents
sometime and see if there is a standard extension defined for this type of
stuff (anyone know?). If so, this whole thing should be trashed and
rewritten.  My patch, again, was just a hack to make the existing code/design
work and at least does provide some functionality now.

Robert B. Easter