Advanced
Mailing List Archive

Mailing List Archive

  1. Home >
  2. DBMail>
  3. dev
cvs: postgresql problems
dbmail at tmueller
Dec 1, 2003, 2:37 AM
Post #1 of 9 (1948 views)
Permalink
Hi,

I've checked out dbmail on 30 Nov and found some problems with
postgresql.

When I try to insert a message:
----------------------------------------------------------------------
Dec 1 01:56:55 debian dbmail/imap4d[15868]: dbpgsql.c,db_query:
executing query [.INSERT INTO physmessage (messagesize, rfcsize,
internal_date) VALUES ('0', '0', '2003-12-01 01:56:55')]
Dec 1 01:56:55 debian dbmail/imap4d[15868]: dbpgsql.c,db_query:
executing query [SELECT currval('physmessages_id_seq')]
Dec 1 01:56:55 debian postgres[15873]: [3] ERROR: Relation
"physmessages_id_seq" does not exist
Dec 1 01:56:55 debian dbmail/imap4d[15868]: dbpgsql.c, db_query: Error
executing query [SELECT currval('physmessages_id_seq')] : [ERROR:
Relation "physmessages_id_seq" does not exist ]
Dec 1 01:56:55 debian dbmail/imap4d[15868]: dbpgsql.c,db_query:
executing query [.INSERT INTO messages (mailbox_idnr
, physmessage_id, unique_id, status,seen_flag) VALUES ('1', '0', '',
'001', '1')]
Dec 1 01:56:55 debian postgres[15873]: [4] ERROR: $1 referential
integrity violation - key referenced from message
s not found in physmessage
Dec 1 01:56:55 debian dbmail/imap4d[15868]: dbpgsql.c, db_query: Error
executing query [.INSERT INTO messages (mailb
ox_idnr, physmessage_id, unique_id, status,seen_flag) VALUES ('1', '0',
'', '001', '1')] : [.ERROR: $1 referential integrity violation - key
referenced from messages not found in physmessage ]
Dec 1 01:56:55 debian dbmail/imap4d[15868]: db.c,db_imap_append_msg:
could not create message
Dec 1 01:56:55 debian dbmail/imap4d[15868]: ic_append(): error
appending msg
Dec 1 01:56:55 debian dbmail/imap4d[15868]: IMAPClientHandler():
Finished command append
----------------------------------------------------------------------

dbmail tries to access physmessages_id_seq, but there is only a sequence
physmessage_id_seq.

The maintenance task fails:
----------------------------------------------------------------------
# su dbmail -c "/usr/local/sbin/dbmail-maintenance -cfpd"
*** dbmail-maintenance ***
Opening connection to database... Opening connection to
authentication... Ok. Connected
Deleting messages with DELETE status... Ok. [0] messages deleted.
Setting DELETE status for deleted messages... Ok. [0] messages set for
deletion.
Re-calculating used quota for all users... Failed. An error occured.
Please check log.

Dec 1 02:02:14 debian postgres[15990]: [3] ERROR: Attribute
usr.curmail_size must be GROUPed or used in an aggrega
te function
Dec 1 02:02:14 debian dbmail/maintenance[15989]: dbpgsql.c, db_query:
Error executing query [.SELECT usr.user_idnr,
sum(pm.messagesize), usr.curmail_size FROM users usr, mailboxes mbx,
messages msg, physmessage pm WHERE pm.id = msg.physmessage_id AND
msg.mailbox_idnr = mbx.mailbox_idnr AND mbx.owner_idnr = usr.user_idnr
AND msg.status < '2' AND usr.user_idnr <> '0' GROUP BY usr.user_idnr
HAVING sum(pm.messagesize) <> usr.curmail_size] : [.ERROR: Attribute
usr.curmail_size must be GROUPed or used in an aggregate function ]
Dec 1 02:02:14 debian dbmail/maintenance[15989]:
db.c,db_calculate_quotum_all: error finding used quota
----------------------------------------------------------------------

The man page of dbmail-config is still in cvs, the file should probably
be removed.

The first problem led to a inconsistent database (the insert to
physmessage succeeded) - this could be easily solved using transactions.
I found a thread about using transaction on the dbmail list - are there
plans to use transactions for 2.0 ?

Thanks!


--
MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)
Re: cvs: postgresql problems [ In reply to ]
ilja at ic-s
Dec 1, 2003, 3:39 AM
Post #2 of 9 (1922 views)
Permalink
Hi,

On Dec 1, 2003, at 10:37 AM, Thomas Mueller wrote:

> Hi,
>
> I've checked out dbmail on 30 Nov and found some problems with
> postgresql.
>
> When I try to insert a message:
> <snip log>
> dbmail tries to access physmessages_id_seq, but there is only a
> sequence
> physmessage_id_seq.
Stupid mistake (which isn't found when using MySQL..,
because it does not use the argument to the db_insert_result() function.

> The maintenance task fails:
> ----------------------------------------------------------------------
> # su dbmail -c "/usr/local/sbin/dbmail-maintenance -cfpd"
> *** dbmail-maintenance ***
<snip log>

Again, this was a MySQL-ism. In SQL-92, one needs to have
all columns in the "HAVING" clause to be in the "GROUP BY"
clause, but MySQL does NOT demand this. So it slipped through.

From today on, my main testing platform will use PostgreSQL,
because it is much stricter than MySQL.

>
> The man page of dbmail-config is still in cvs, the file should probably
> be removed.
Fixed
>
> The first problem led to a inconsistent database (the insert to
> physmessage succeeded) - this could be easily solved using
> transactions.
> I found a thread about using transaction on the dbmail list - are there
> plans to use transactions for 2.0 ?
We'd like to use transactions, and make better use of foreign keys. It
would be easiest to ditch MySQL ISAM tables completely, so that the we
can use those features, and let
the database handle the integrity of the tables. The less db-handling
code in DBMail, the better.

I guess we need some discussions about this in this list and here at
IC&S.

Anyway, thanks for pointing out these bugs. They're fixed in CVS and
will be in the next snapshot.

Cheers,
Ilja

--
IC&S
Stadhouderslaan 57
3583 JD Utrecht
telnr. 030-6355730
faxnr. 030-6355731

PGP-key:
http://www.ic-s.nl/keys/ilja.txt
Re: cvs: postgresql problems [ In reply to ]
xndbmail at xerus
Dec 1, 2003, 10:49 AM
Post #3 of 9 (1913 views)
Permalink
On Mon, Dec 01, 2003 at 11:39:19AM +0100, Ilja Booij wrote:
> >The first problem led to a inconsistent database (the insert to
> >physmessage succeeded) - this could be easily solved using
> >transactions.
> >I found a thread about using transaction on the dbmail list - are there
> >plans to use transactions for 2.0 ?
> We'd like to use transactions, and make better use of foreign keys. It
> would be easiest to ditch MySQL ISAM tables completely, so that the we
> can use those features, and let
> the database handle the integrity of the tables. The less db-handling
> code in DBMail, the better.
>
> I guess we need some discussions about this in this list and here at
> IC&S.

here's one vote for ditching myisam support and adding transactions.

xn
Re: cvs: postgresql problems [ In reply to ]
mason at suse
Dec 1, 2003, 11:13 AM
Post #4 of 9 (1915 views)
Permalink
On Mon, 2003-12-01 at 05:39, Ilja Booij wrote:

> We'd like to use transactions, and make better use of foreign keys. It
> would be easiest to ditch MySQL ISAM tables completely, so that the we
> can use those features, and let
> the database handle the integrity of the tables. The less db-handling
> code in DBMail, the better.
>
> I guess we need some discussions about this in this list and here at
> IC&S.

Transactions will make things faster overall, and could really reduce
code complexity. I would suggest dropping the ISAM support, people that
really need it can stay at 1.2

-chris
Re: cvs: postgresql problems [ In reply to ]
aaron at serendipity
Dec 1, 2003, 11:18 AM
Post #5 of 9 (1909 views)
Permalink
Dropping MyISAM seems like the right thing to do here. DBMail 2.0 could
reasonably require MySQL 4.0 or 4.1 as its minimum, along with whatever recent
version of PostgreSQL supports needed features, too (yeah yeah, flame away
that PostgreSQL supports everything we need and always has).

Aaron


""Christian G. Warden"" <xndbmail@xerus.org> said:

> On Mon, Dec 01, 2003 at 11:39:19AM +0100, Ilja Booij wrote:
> > >The first problem led to a inconsistent database (the insert to
> > >physmessage succeeded) - this could be easily solved using
> > >transactions.
> > >I found a thread about using transaction on the dbmail list - are there
> > >plans to use transactions for 2.0 ?
> > We'd like to use transactions, and make better use of foreign keys. It
> > would be easiest to ditch MySQL ISAM tables completely, so that the we
> > can use those features, and let
> > the database handle the integrity of the tables. The less db-handling
> > code in DBMail, the better.
> >
> > I guess we need some discussions about this in this list and here at
> > IC&S.
>
> here's one vote for ditching myisam support and adding transactions.
>
> xn
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>



--
Re: cvs: postgresql problems [ In reply to ]
matthew at zeut
Dec 1, 2003, 11:19 AM
Post #6 of 9 (1909 views)
Permalink
On Mon, 2003-12-01 at 13:13, Chris Mason wrote:
> On Mon, 2003-12-01 at 05:39, Ilja Booij wrote:
>
> > We'd like to use transactions, and make better use of foreign keys. It
> > would be easiest to ditch MySQL ISAM tables completely, so that the we
> > can use those features, and let
> > the database handle the integrity of the tables. The less db-handling
> > code in DBMail, the better.
> >
> > I guess we need some discussions about this in this list and here at
> > IC&S.
>
> Transactions will make things faster overall, and could really reduce
> code complexity. I would suggest dropping the ISAM support, people that
> really need it can stay at 1.2

I couldn't agree more. Use the database as a database, not as just some
type of fancy flat file. Use it to guarantee data integrity and reduce
code complexity.
Re: cvs: postgresql problems [ In reply to ]
wbh at conducive
Dec 1, 2003, 11:47 AM
Post #7 of 9 (1913 views)
Permalink
Aaron Stone wrote:

> Dropping MyISAM seems like the right thing to do here. DBMail 2.0 could
> reasonably require MySQL 4.0 or 4.1 as its minimum, along with whatever recent
> version of PostgreSQL supports needed features, too (yeah yeah, flame away
> that PostgreSQL supports everything we need and always has).
>
> Aaron
>
*SNIP*

Now, *that* would be a cheap shot.<G>

W/E 23 May, (MySQL & SAP deal)

http://www.mysql.com/press/release_2003_16.html

MySQL will be moving toward..... lessee ...... where DB2 and Ingres
were, ...hmmm.. 15-20 years ago?

Maybe less.

DB2 hadn't even a CLI, (had to access it via COBOL), groked only
BCDIC/EBCDIC ....and Ingres (PostgreSQL) didn't grok SQL then or for
quite a long time thereafter...

Progress is that we have two open-source choices that work well.

BTW .... ever wonder how suitable the ZODB would be as a mailstore <G>

Bill Hacker
Re: cvs: postgresql problems [ In reply to ]
dbmail at tmueller
Dec 6, 2003, 8:48 AM
Post #8 of 9 (1913 views)
Permalink
Hi Ilja,

> They're fixed in CVS and will be in the next snapshot.

I found 2 more problems. In my logs I see quite often:

Dec 6 17:27:01 server postgres[3043]: [5] ERROR: Attribute
blk.messageblk must be GROUPed or used in an aggregate function
Dec 6 17:27:20 server postgres[14828]: [6] ERROR: No such attribute
shmbx_acc.is_subscribed


imapcommands.c ends the logging lines with \r\n this leads to output
like this one:

Dec 6 17:26:03 server dbmail/imap4d[4346]: IMAPD [PID 4346]: user (id
1, name test) login accepted @ 2003-12-06 17:26:03^M


--
MfG Thomas Mueller - http://www.tmueller.com for pgp key (95702B3B)
Re: cvs: postgresql problems [ In reply to ]
ilja at ic-s
Dec 11, 2003, 9:59 AM
Post #9 of 9 (1919 views)
Permalink
Hi,
On Dec 6, 2003, at 4:48 PM, Thomas Mueller wrote:

> Hi Ilja,
>
>> They're fixed in CVS and will be in the next snapshot.
>
> I found 2 more problems. In my logs I see quite often:
>
> Dec 6 17:27:01 server postgres[3043]: [5] ERROR: Attribute
> blk.messageblk must be GROUPed or used in an aggregate function
> Dec 6 17:27:20 server postgres[14828]: [6] ERROR: No such attribute
> shmbx_acc.is_subscribed
Both fixed
>
>
> imapcommands.c ends the logging lines with \r\n this leads to output
> like this one:
>
> Dec 6 17:26:03 server dbmail/imap4d[4346]: IMAPD [PID 4346]: user (id
> 1, name test) login accepted @ 2003-12-06 17:26:03^M
And also fixed.

Thanks for the reports,
Ilja
--
IC&S
Stadhouderslaan 57
3583 JD Utrecht
telnr. 030-6355730
faxnr. 030-6355731

PGP-key:
http://www.ic-s.nl/keys/ilja.txt

©2024 GT.net. A Gossamer Threads company.
5th Floor, 455 Granville St., Vancouver, BC V6C 1T1, Canada | Legal