What sort of relational integrity do you mean?

Anyway, they say that they're not implementing triggers until version 5.something. We're on 3.23.something.

Even autoincrement is a trigger, admittedly the only one available in MYSQL.

Nominally, sure. And we're already using those. MySQL docs don't claim they have triggers, and I was using their definition.

However, what Postgres offers is trigger functionality that we can define.

What does that have to do inherently with relational integrity?

Sure - and like I said it will be slower, but offer better data integrity - which I gather is a good trade-off because data integrity is what we are jumping through hoops to provide.

t on edit User defined triggerss offer data integrity, because among other things they ensure code is appled consistently.

In others words you don't have to worry about - if I do this new function am I calling all my standard calculations and incrementing and stuff. It is right there in the database; if you alter the database you trigger the code that does everything it should.

It may very well be faster, since it'll probably need to do less SQL to achieve the same effect -- it's being done very clumsily right now.

It may - it depends; is this standard casade, restrict delete type functionality? Because yes, most full-fledged databases can do that pretty quickly (although MS_SQL sometimes can be amazingly slow in implemntation). Or are you going to be doing a lot of custom code? Because true custom codes tends to give worse performance.

I'm curious. Since we have multiple "threads'/"discussions" in a single table, I presume we are using our code, not the autoincrement to create post numbers. And the occassional skipped thread numbers is one of the minor problems that arises from this, but there are major ones as well? Or are our major problems elsewhere?

the occassional skipped thread numbers is one of the minor problems that arises from this

Skipped and doubles. It's very kludgy code there, with a lot of "check the table again!" in it. But if we can push some of the work closer to the DB engine, that'd not be so necessary.

Also, there's a lot of deleting that's just not allowed, because of the headache of maintaining that integrity on the front end. With a better DB, then we can beef things up.

There is a standard method for doing this kind of thing. MySqL's default table format does not allow record locking, but supports several format that do. So if you have record locking here is a quick english language description of the standard method for maintaining your own incrementations: I'm curious where in these steps we are running into problems

1) Maintain a separate table with one record for each "table". It should have only two fields the table key( d a numeric field that contains the current table number), and one that contains the last post number (set to zero when a new empty table is created). OK you can have extra fields for "table" name and description if you wish.

2) When a new record is added to the a "table" retrieve the record for that table, lock it and increment it by one.

3) Then add the new post record to the table. with the appropriate number.

4) Only then unlock the record with the last post number

Make sure you use pessmistic record locking for that table. If neccesary change record locking to pessimistic in code before adding a new post, and then change in back to automatic or optimsitc afterwords.

If you some consideration prevents using record locking then you can use table locking instead. Because the table or record is locked very briefly, if users have a 1 second timeout on obtaining a record, they should almost never encounter a time out before obtaining a lock.

This only needs to be used in adding, obviously not in editing or deleting.

I'm sure we are doing this or a minor variation. Do we know where we are hitting the problem?

We use MyISAM tables, so no row locking there. And I'm nervous about a performance hit with table locking for every post.

Completely unrelatedly, I was bopping through our referer logs (yes, I have work to do, what of it?) and noticed that more than one site that linked to us then had posts saying we're hard to navigate.

I'm not proposing we re-vamp the whole thing so we all get confused, but I was wondering -- do you figure it's an artifact of just being used to different boards and never the twain shall meet? TWOP, Whedonesque, Fametracker, etc, confuse the living hell out of me.