Thread: race conditions in my sequences

I'd like to explain what I'm trying to do.

I have three tables, primary key, bug_id.  Bug_id is a foreign key in the
other two tables, with a cascade on update and delete.

What I've done is created three sequences for each table for the bug_id.
Sometimes the table with the primary key gets its sequence out of sync
from the other two tables and I start getting referential integrity
violation problems and then all inserts  start getting dropped.

I think this problem may be due to a date field I have in the table with
the primary key.  I'm taking things like:

Mon Aug 13 10:40:36 PDT 2001 and placing it into a timestamp field.  The
reason I say this is only because my logs show:

postgres[29169] [1] ERROR:  Bad timestamp external representation
'day/time/etc'

and then immediately following that is:

postgres[29169]: [2] ERROR:  <unnamed> referential integrity violation -
key referenced from env_info not found in summary

Thing is, majority of the time the date inserts fine.

Any suggestions would be more then appreciate as always.  Is there a
better way to do what I'm trying to do?  Should I be converting my date to
something else before insert?

thanks
-jeremy
--
salad.

> Any suggestions would be more then appreciate as always.  Is there a
> better way to do what I'm trying to do?

I would recommend using only one sequence for the master table. Then just
reference that sequence value for the two foreign keys. For example:

BEGIN;
INSERT INTO Bugs (bug_date) VALUES (CURRENT_DATE);
INSERT INTO BugA (bug_id,bug_desc) VALUES (currval('bugs_bug_id_seq'),'This
is a bug');
INSERT INTO BugB (bug_id,bug_fix) VALUES (currval('bugs_bug_id'),'Reinstall
Windows... again');
COMMIT;

This way, you don't have to worry about sequences getting out of sync
because they all refer to the same value.

As for the timestamp problem... I don't know how you are trying to INSERT
those values (interface, SQL, etc).

Greg

Jeremy Hansen writes:

> I have three tables, primary key, bug_id.  Bug_id is a foreign key in the
> other two tables, with a cascade on update and delete.
>
> What I've done is created three sequences for each table for the bug_id.
> Sometimes the table with the primary key gets its sequence out of sync
> >from the other two tables and I start getting referential integrity
> violation problems and then all inserts  start getting dropped.

What you want is one sequence that generates the primary key, and the
foreign key columns you declare as integer and fill them in manually by
reading the generated primary key for the tuple you want to reference.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter