Re: A couple of questions

["Chris Travers" <..hidden..>]

first, this would be a better question for -devel.

On Thu, Oct 30, 2008 at 6:27 AM, Eric Lucas <..hidden..> wrote:
> I've done more research and I understand now that there are tables that
> use id directly from the sequence 'id'.
> It appears that trans_id is a foreign key for those id.
>
> Still trying to grok the transactions table.

THere is a bug in the way the transaction table works in 1.2.  Under
accepted workflows it doesnt cause a problem but will be fixed in 1.3.

As background:  One of the problems with the codebase we inherited
from SQL-Ledger was the fact that a few of the tables had ambiguous
foreign key relationships with any of a number of tables.  For
example, acc_trans.trans_id references any of ar(id), ap(id), gl(id),
and shipto.trans_id references any of ar(id), ap(id), customer(id),
vendor(id).

If the id sequence gets out of whack, bad things can happen.  So we
added the transactions table which is supposed to store the id and the
table.  However, we used a PostgreSQL "rule" to accomplish this which
is somewhat buggy and hence the id sequence gets incrimented an extra
time (i.e. we insert nextval('id') into ap, then we insert
nextval('id') into transactions).  There is a fix for this problem in
sql/fixes, but as long as you aren't reposting invoices, it still does
a reasonable job of enforcing uniqueness.

1.3 moves the writes to "transactions" to a trigger instead of a rule,
thus avoiding the problem.

In general, transactions is a table you can ignore.  It is just there
to help enforce uniqueness of id's, and in 1.3, it will help with
restoring the relational model while we refactor key portions of the
code and database structure.

Best Wishes,
Chris Travers