two questions about JAMES and PostgreSQL

[Lou Ceci <ce...@glyphic.com>]

Hello, JAMESians.

We are storing messages in a PostgreSQL database.  To do this, we
define the message_body column of the inbox table as an oid (object
identifier).  When the message comes in, a large object ID is stored
in the message_body of inbox and the text of the message itself is
stored in the data column of the pg_largeobject system table.

This produces the following two problems.

1. When the data goes into the database, carriage returns are
	octal-encoded as \015\012 (CR, LF).  We've put in a
	System.out.println at that point and it shows us that
	the octets are interpreted as single characters, and we
	see carriage returns in the output window as expected.  We
	can query the database (using octet_length(data)) and it, too,
	sees the octets as single characters.  However, when we
	retrieve the message data, the octets come back as four
	ASCII characters each.  Because they are not interpreted
	as carriage returns, the headers are not parsed.

	Can anyone explain why they are octets going in, octets
	in the database, but characters coming out?

2. When a message gets read from the inbox, it gets deleted.  That
	works just fine, but the data is left behind in pg_largeobject.
	To delete the data from pg_largeobject, I created the following
	PostgreSQL rule:

	CREATE RULE data_delete AS ON DELETE TO inbox
		DO DELETE FROM pg_largeobject WHERE loid = OLD.message_body;

	This rule works fine in the database when I stuff the two tables
	by hand and then delete from inbox.  It does not work in JAMES.
	Instead, JAMES complains that "relation inbox already exists."
	Here is the output from the mailstore log:

Fri Nov 09 17:17:21 PST 2001 [DEBUG  ] (mailstore):
Reading SQL resources from file:
C:\Development\Tagmail\dev1\james\apps\james\conf\sqlResources.xml, section
org.apache.james.mailrepository.JDBCMailRepository.

Fri Nov 09 17:17:21 PST 2001 [WARN   ] (mailstore): Exception while
creating repository:ERROR:  Relation 'inbox' already exists

java.sql.SQLException: ERROR:  Relation 'inbox' already exists

	at org.postgresql.Connection.ExecSQL(Connection.java:533)
	at org.postgresql.jdbc2.Statement.execute(Statement.java:294)
	at org.postgresql.jdbc2.PreparedStatement.execute(PreparedStatement.java:610)
	at 
org.apache.james.mailrepository.JDBCMailRepository.initialize(JDBCMailRepository.java:255)
	at org.apache.james.core.AvalonMailStore.select(AvalonMailStore.java:210)
	at java.lang.reflect.Method.invoke(Native Method)


	Does anyone know why the existence of a rule on the inbox table
	would cause JAMES to behave this way?  I can even go so far as
	to start up JAMES, insert a message, THEN create the rule, and
	then, when trying to retrieve the message, getting the above
	error.

I would appreciate any information anyone has on either of these issues.

Thanks,

--Lou







--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>