[BUGS] exp(x) vs :
SELECT : 1; -- returns e Works, but gives a NOTICE that the : operator is depreciated and that exp(x) should be used instead. SELECT exp(1); Gets ERROR: exp(INT4) does not exist. SELECT exp(1.0); Works fine of course. Just seems strange that a depreciated operator actually works smoother. -- Robert
[BUGS] Factorial operator gets parser error in psql.
SELECT 3 !; ERROR: parser error at or near "" SELECT 3 ! ; Works ok. Parser error occurs if the ending ; is on the same line. -- Robert
Re: [BUGS] Re: [HACKERS] Re: [SQL] MAX() of 0 records.
On Fri, 07 Jul 2000, Tom Lane wrote: > Chris Bitmead <[EMAIL PROTECTED]> writes: > UPDATE foo > SET bar = (SELECT min(f1) FROM othertab > WHERE othertab.keycol = foo.keycol) > WHERE condition-determining-which-foo-rows-to-update > if you wanted to use an aggregate. This is pretty ugly, especially so If you use min(x) or max(x) frequently, isn't it best to make a trigger that intercepts x on insert and update, then check it and store it somewhere rather than scanning for it everytime? (not that this fixes any db problem thats being discussed here) -- Robert
[BUGS] pg_dump of functions containing \' fail to restore (if not corrected by hand)
This is a minor problem, but maybe easily fixed? ...
If you create a database and load in the following sql, dump it with pg_dump,
then try to restore it (psql -e db < dump), it will get a parser error loading
the function when it encounters the "\'" in the regsub functions. I've had many
troubles with the handling of a literal "\" in text! They get stripped out all
the time since they are seen as escapes. I've had to use regsub() to replace
every \ with \\ a couple times below because of the way strings do that.
-- Load the TCL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION pltcl_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so'
LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl'
HANDLER pltcl_call_handler
LANCOMPILER 'PL/tcl';
-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
--
-- Large Text storage
--
-- putlgtext - generic function to store text into the
-- specified text storage table.
-- The table specified in $1 should have the following
-- fields:
-- id, text_seq, text_block
--
-- $1 is the name of the table into which $3 is stored
-- $2 is the id of the text and references id in another table
-- $3 is the text to store, which is broken into chunks.
-- returns 0 on success
-- nonzero otherwise
CREATE FUNCTION putlgtext (TEXT, INTEGER, TEXT) RETURNS INTEGER AS '
set i_table $1
set i_id $2
set i_t {}
# pg_dump to psql doesnt like the quotes
regsub -all {([\\''])} $3 {\\1} i_t
set i_seq 0
while { $i_t != {} } {
set i_offset 0
set tblock [string range $i_t 0 [expr 7000 + $i_offset]]
# Do not split string at a backslash
while { [string range $tblock end end] == "" && $i_offset < 1001 }
{
set i_offset [expr $i_offset + 1]
set tblock [string range $i_t 0 [expr 7000 + $i_offset]]
}
set i_t [string range $i_t [expr 7000 + [expr $i_offset + 1]] end]
spi_exec "INSERT INTO $i_table (id, text_seq, text_block) VALUES (
$i_id , $i_seq , ''$tblock'' )"
incr i_seq
}
return 0
' LANGUAGE 'pltcl';
-- getlgtext - like putlgtext, this is a generic
-- function that does the opposite of putlgtext
-- $1 is the table from which to get TEXT
-- $2 is the id of the text to get
-- returns the text concatenated from one or more rows
CREATE FUNCTION getlgtext(TEXT, INTEGER) RETURNS TEXT AS '
set o_text {}
spi_exec -array q_row "SELECT text_block FROM $1 WHERE id = $2 ORDER BY
text_seq" {
append o_text $q_row(text_block)
}
return $o_text
' LANGUAGE 'pltcl';
-- largetext exists just to hold an id and a dummy 'lgtext' attribute.
-- This table's trigger function provides for inserting and updating
-- into largetext_block. The text input to lgtext actually gets
-- broken into chunks and stored in largetext_block.
-- Deletes to this table will chain to largetext_block automatically
-- by referential integrity on the id attribute.
-- Selects have to be done using the getlgtext function.
CREATE TABLE largetext (
id INTEGER PRIMARY KEY,
lgtext TEXT -- dummy field
);
COMMENT ON TABLE largetext IS 'Holds large text';
-- This table must have the field names as they are.
-- These attribute names are expected by put/getlgtext.
CREATE TABLE largetext_block (
id INTEGER NOT NULL
REFERENCES largetext
ON DELETE CASCADE,
text_seqINTEGER NOT NULL,
text_block TEXT,
PRIMARY KEY (id, text_seq)
);
COMMENT ON TABLE largetext_block IS 'Holds blocks of text for table largetext';
CREATE SEQUENCE largetext_seq;
-- SELECT:
-- SELECT id AS the_id FROM largetext;
-- SELECT getlgtext('largetext_block', id) FROM largetext WHERE id = the_id;
-- INSERT:
-- INSERT INTO largetext (lgtext) values ('...');
-- DELETE:
-- DELETE FROM largetext WHERE id = someid;
-- deletes from largetext and by referential
-- integrity, from largetext_text all associated block rows.
CREATE
[BUGS] INITIALLY DEFERRED / UPDATE in transaction bug
POSTGRESQL BUG REPORT TEMPLATE Your name : Robert B. Easter Your email address : [EMAIL PROTECTED] System Configuration - Architecture (example: Intel Pentium) : Intel Celeron Operating System (example: Linux 2.0.26 ELF) : Linux 2.2.13 Slackware PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2 Compiler used (example: gcc 2.8.0) : egcs-2.91.66 Please enter a FULL description of your problem: Referential integrity problem. When using an INITIALLY DEFERRED foreign key within a transaction, I give it a value that is not in the referenced table. Then I UPDATE it so that it has a value in the referenced table. Then I COMMIT. I still get an RI error on COMMIT. Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: -- CREATE TABLE t1 ( t2_id INTEGER NOT NULL ); CREATE TABLE t2 ( id SERIAL PRIMARY KEY ); ALTER TABLE t1 ADD CONSTRAINT t1_t2_id_fk FOREIGN KEY (t2_id) REFERENCES t2 INITIALLY DEFERRED; BEGIN; INSERT INTO t1 VALUES (0); INSERT INTO t2 VALUES (1); SELECT t2_id FROM t1; SELECT id FROM t2; UPDATE t1 SET t2_id = 1 WHERE t2_id = 0; SELECT t2_id FROM t1; SELECT id FROM t2; COMMIT; -- error -- Or CREATE TABLE t3 ( id SERIAL PRIMARY KEY ); CREATE TABLE t4 ( t3_id INTEGER REFERENCES t3 INITIALLY DEFERRED ); BEGIN; INSERT INTO t4 VALUES (0); INSERT INTO t3 VALUES (1); SELECT t3_id FROM t4; SELECT id FROM t3; UPDATE t4 SET t3_id = 1 WHERE t3_id = 0; SELECT t3_id FROM t4; SELECT id FROM t3; COMMIT; -- again, error If you know how this problem might be fixed, list the solution below: - -- Robert
[BUGS] dump of functions does not handle backslashes correctly
If you create a function from psql like: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; CREATE FUNCTION "atestfun" ( ) RETURNS text AS 'DECLARE mtt TEXT; BEGIN mtt := ''This is a test.''; RETURN mtt; END;' LANGUAGE 'plpgsql'; Then use pg_dump on it, you get: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS '/usr/local/pgsql/lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; CREATE FUNCTION "atestfun" ( ) RETURNS text AS 'DECLARE mtt TEXT; BEGIN mtt := ''This is a \\ test.''; RETURN mtt; END;' LANGUAGE 'plpgsql'; In the function, the variable "mtt" loses half of the '\' characters. When you reload this dump, the embedded '\' is lost. When you do a SELECT atestfun(); The ouput should be: atestfun ---------- This is a \ test. (1 row) But instead, it returns: atestfun -- This is a test. (1 row) Is this a pg_dump bug or is there there some way to do this right? -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
Re: [BUGS] Can't use NULL in IN conditional?
On Monday 11 December 2000 10:51, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > -- This works
> > SELECT type_id, code FROM product_types WHERE code = '0A' OR code = NULL;
> > -- This doesn't
> > SELECT type_id, code FROM product_types WHERE code IN ('OA', NULL);
>
> "code = NULL" is not legal SQL --- or at least, the standard's
> interpretation of it is not what you appear to expect. According to the
> spec the result must always be NULL, which is effectively FALSE in this
> context.
>
> Since certain Microsoft products misinterpret "var = NULL" as "var IS
> NULL", we've inserted a hack into our parser to convert a comparison
> against a literal NULL to an IS NULL clause. However, that only works for
> the specific cases of "var = NULL" and "var <> NULL", not for any other
> contexts where a null might be compared against something else.
>
> Personally I regard this hack as a bad idea, and would prefer to take it
> out. I'd certainly resist extending it to the IN operator...
>
> regards, tom lane
What you are saying agrees with things I've read elsewhere, and a little
definition/note that I wrote on my "Databasing" Terms page:
three-valued logic:
a logic system that employs TRUE, FALSE, and UNKNOWN. NULL values introduce
UNKNOWN into boolean operations. A truth table must be used to lookup the
proper value (TRUE or FALSE) of UNKNOWN under specific operations. In SQL
implementations that use three-valued logic, you must consult the
documentation for its truth table. Some newer implementations of SQL
eliminate UNKNOWN, and may generally behave as follows: all boolean tests
involving NULL return FALSE except the explicit test IS NULL, e.g., if NULL
is a possibility, it has to be tested for explicity using IS NULL or IS NOT
NULL. (any additions/corrections to this definition/note will be happily
considered)
I think Bruce Momjian's book says this too:
http://www.postgresql.org/docs/aw_pgsql_book/node45.html
(that book is really useful!)
--
Robert B. Easter [EMAIL PROTECTED] -
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/
Re: [BUGS] Can't use NULL in IN conditional?
On Monday 11 December 2000 12:34, Tom Lane wrote: > Three-valued logic is perfectly straightforward if you keep in mind the > interpretation of NULL/UNKNOWN: "I don't know if this is true or false". > Thus: > > NOT unknown => unknown > > false AND unknown => false (it can't possibly be true) > true AND unknown => unknown > unknown AND unknown => unknown > > false OR unknown => unknown > true OR unknown => true (it's true no matter what the unknown is) > unknown OR unknown => unknown > > For ordinary operators such as "=", the result is generally NULL if any > input is NULL, although there are some specific cases where you can > deduce a correct result knowing only some of the inputs. In particular, > NULL = NULL does not yield TRUE, it yields UNKNOWN. This is correct > when you consider that NULL is not a specific value, but a placeholder > for an unknown value. (Reference: SQL99 section 8.2 general rule 1a.) > > IS NULL and IS NOT NULL are not ordinary operators in this sense, since > they can deliver a non-null result for NULL input. > > Also, SQL specifies that a WHERE clause that evaluates to "unknown" is > taken as false, ie, the row is not selected. > > Bottom line is that in a spec-conformant implementation, > WHERE code = '0A' OR code = NULL > will act the same as if you'd just written "WHERE code = '0A'"; the > second clause always yields unknown and so can never cause the WHERE to > be taken as true. > > > Some newer implementations of SQL > > eliminate UNKNOWN, and may generally behave as follows: all boolean tests > > involving NULL return FALSE except the explicit test IS NULL, e.g., if > > NULL is a possibility, it has to be tested for explicity using IS NULL or > > IS NOT NULL. > > They may *appear* to return FALSE if you aren't looking too closely, > since WHERE treats top-level results of FALSE and UNKNOWN the same. > If they really don't make the distinction then they are broken. > AFAICT, neither SQL92 nor SQL99 regard NULL support as optional. > > regards, tom lane Thanks for the clarification and SQL reference. I spent some time on this today and updated a file of mine at http://www.comptechnews.com/~reaster/dbdesign.html#three-valued-logic to take into consideration these things. If you do take a look at it and find an error, I will fix it. This dbdesign.html file is a file linked to from http://postgresql.readysetnet.com/docs/faq-english.html so I'm hoping to keep it correct and useful. Thanks :) -- Robert B. Easter [EMAIL PROTECTED] - - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - -- http://www.comptechnews.com/~reaster/
Re: [BUGS] Backend dies when overloading + operator for bool
On Tuesday 19 December 2000 22:11, Jeff Davis wrote: > > create function bool_to_int(bool) returns int as 'select 1 as result > where $1 union select 0 as result where not $1;' language 'sql'; This is an alternative way to make the bool_to_int, but it doesn't solve your problem unless the union was causing a bug: CREATE FUNCTION bool_to_int(BOOLEAN) RETURNS INTEGER AS ' SELECT CASE WHEN $1 THEN 1 ELSE 0 END AS result; ' LANGUAGE 'SQL';
[BUGS] boolean bugs
CREATE TABLE nulltest (nullfield);
INSERT INTO nulltest VALUES (null);
pgcvs=# select (nullfield = 'willbenull') is null from nulltest;
?column?
--
t
(1 row)
pgcvs=# select (nullfield = 'willbenull') is true from nulltest;
?column?
--
(1 row)
pgcvs=# select (nullfield = 'willbenull') is false from nulltest;
?column?
--
(1 row)
The IS operator is supposed to return only TRUE or FALSE, never NULL. See
ISO/IEC 9075-2:1999 6.30
pgcvs=# select (nullfield = 'willbenull') is (false is false) from nulltest;
ERROR: parser: parse error at or near "("
The IS operator has a problem if right side is in parenthesis.
pgcvs=# select (nullfield = 'willbenull') and (false is false) from nulltest;
?column?
--
(1 row)
AND and OR are ok with the paren, why not IS?
The UNKNOWN literal is not understood. It should be synonymous with NULL:
pgcvs=# select TRUE IS UNKNOWN;
ERROR: parser: parse error at or near "unknown"
pgcvs=#
This should be the same as:
pgcvs=# select TRUE IS NULL;
?column?
--
f
(1 row)
See ISO/IEC 9075-2:1999 5.3
pgcvs=# select true is null;
?column?
--
f
(1 row)
pgcvs=# select null is true;
?column?
--
(1 row)
This is strange. Just reversing the order changes the result.
I'm using the cvs version. I think 7.0.3 has all this (wrong?) behavior too.
--
Robert B. Easter [EMAIL PROTECTED] -
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
-- http://www.comptechnews.com/~reaster/
Re: [BUGS] boolean bugs
On Saturday 06 January 2001 17:56, Tom Lane wrote:
> "Robert B. Easter" <[EMAIL PROTECTED]> writes:
> > The IS operator is supposed to return only TRUE or FALSE, never NULL.
> > See ISO/IEC 9075-2:1999 6.30
>
> Yeah, we do not implement IS TRUE, IS FALSE, etc per spec. IS [NOT] NULL
> is the only one of the group that works per-spec; the others all
> erroneously produce NULL for null input, and IS UNKNOWN isn't there at all.
>
> I've had that on my to-do list for awhile, but it's pretty low priority.
>
> > pgcvs=# select (nullfield = 'willbenull') is (false is false) from
> > nulltest; ERROR: parser: parse error at or near "("
> >
> > The IS operator has a problem if right side is in parenthesis.
>
> I'd be interested to know how you derive that expression from the spec.
> By my reading of the grammar, IS is supposed to be followed by one or
> two literal keywords, not an expression.
>
> regards, tom lane
::=
[ IS [ NOT ] ]
::=
TRUE
| FALSE
| UNKNOWN
You're right about the spec. I guess there is some good reason not to allow
IS to take expressions on the right like AND and OR, even though I feel like
IS is just like AND and OR as an op that takes two boolean args and returns a
boolean (true/false only).
--
Robert B. Easter [EMAIL PROTECTED] -
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
-- http://www.comptechnews.com/~reaster/
Re: [BUGS] BUG in postgres mathematic
This problem is not specific to Postgres. If you play around with a little C
program like:
#include
int main(int argc, char * argv[])
{
float f = 27.81;
int i = 5;
int l = 100;
int ii = i*f*l;
long ll = l*f*i;
float ff = i*f*l;
printf("%i\n", ii);
printf("%li\n", ll);
printf("%.5f\n", ff);
printf("%i\n", (int) ff);
}
It prints:
13904
13904
13905.0
13905
There is probably a good explanation for this. gcc 2.95 and egcs 2.91.66 do
this. Maybe a rounding problem.
On Thursday 25 January 2001 05:34, Max Vaschenko wrote:
> Postgres-7.0.3-2
> RedHat-6.2
>
> SELECT int8(5*27.81*100);
> 13904
>
> SELECT int4(5*27.81*100);
> 13905
>
> SELECT int8(27.81*100*5);
> 13905
--
Robert B. Easter [EMAIL PROTECTED] -
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
-- http://www.comptechnews.com/~reaster/
Re: [BUGS] BUG in postgres mathematic
On Thursday 25 January 2001 22:52, Tom Lane wrote: > "Robert B. Easter" <[EMAIL PROTECTED]> writes: > > This problem is not specific to Postgres. > > The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety > floating-point roundoff error. However, I think Max has a fair > complaint here: it seems float-to-int8 conversion is truncating, not > rounding like the other conversions to integer do. > > regression=# select 4.7::float8::int4; > ?column? > -- > 5 > (1 row) > > regression=# select 4.7::float8::int8; > ?column? > -- > 4 > (1 row) > > Seems to me this is a bug we should fix. > > regards, tom lane Yeah, I agree. It isn't right that it truncates and that is something C does appearently. The fix is to pass the float through a rounding something like (long)(f + 0.5) or else C just truncates it off. This must already be happening for the int4 conversion or C would do the same thing to it. I didn't look at the Postgres sources yet, but it is probably one of those very easy things to fix. :) -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
Re: [BUGS] BUG in postgres mathematic
Notice how the INT4 rounding is banker's rounding (round to the nearest even number). That is what we would want the INT8 to do as well, not just a simple round like I mentioned before. Again, the INT8 shows truncation. I've been looking around the source code, but I can't see where all this happens. reaster=# SELECT 1.5::FLOAT::INT4; ?column? -- 2 (1 row) reaster=# SELECT 2.5::FLOAT::INT4; ?column? -- 2 (1 row) reaster=# SELECT 1.5::FLOAT::INT8; ?column? -- 1 (1 row) reaster=# SELECT 2.5::FLOAT::INT8; ?column? -- 2 (1 row) On Thursday 25 January 2001 22:52, Tom Lane wrote: > "Robert B. Easter" <[EMAIL PROTECTED]> writes: > > This problem is not specific to Postgres. > > The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety > floating-point roundoff error. However, I think Max has a fair > complaint here: it seems float-to-int8 conversion is truncating, not > rounding like the other conversions to integer do. > > regression=# select 4.7::float8::int4; > ?column? > -- > 5 > (1 row) > > regression=# select 4.7::float8::int8; > ?column? > -- > 4 > (1 row) > > Seems to me this is a bug we should fix. > > regards, tom lane -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
Re: [BUGS] BUG in postgres mathematic
On Friday 26 January 2001 18:07, Tom Lane wrote: > Curiously, this change exposed what I take to be a platform dependency > in the int8 regress test. It was computing > int8(float8(4567890123456789::int8)) and expecting to get back exactly > 4567890123456789. However, that value is 53 bits long and so there is > no margin for error in a standard IEEE float8 value. I find that at > least on HP hardware, rint() treats the value as inexact and rounds to > nearest even: > > regression=# select round(4567890123456788::float8) - > 4567890123456780::float8; ?column? > -- > 8 > (1 row) > > regression=# select round(4567890123456789::float8) - > 4567890123456780::float8; ?column? > -- > 8 > (1 row) > > regression=# select round(4567890123456790::float8) - > 4567890123456780::float8; ?column? > -- >10 > (1 row) > > regression=# > > Whether this is a bug in rint or spec-compliant behavior is unclear, but > I'll bet HP's hardware is not the only platform that behaves this way. > Since I'm not eager to try to develop a new set of platform-specific > int8 expected files at this late hour, I just diked out that test > instead... Here is what I get on Linux (PIII): reaster=# select round(4567890123456788::float8) - 4567890123456780::float8; ?column? -- 8 (1 row) reaster=# select round(4567890123456789::float8) - 4567890123456780::float8; ?column? -- 9 (1 row) reaster=# select round(4567890123456790::float8) - 4567890123456780::float8; ?column? -- 10 (1 row) I'm not sure what the problem is either. The PIII has an 80-bit FPU but not sure that matters. When there is no exponent, maybe only 52 bits are really in the mantissa. If you try rounding numbers <= 4503599627370495 (2^52 - 1), maybe you'll get expected results. The hidden bit is 0. Could be that round or rint (whatever it is) always makes the hidden bit 1 when I think it should only be 1 when the exponent is nonzero. I'm no float expert! :) Feel free to correct me. -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
Re: [BUGS] [PATCHES] JDBC patch (attempt#2) for util.Serialize and jdbc2.PreparedStatement
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
