• (cs)

    Selligent?

    They advised us to restrict bulk uploads to 1000 records per second for this reason.

  • (cs)

    Oh the HORROR........

  • (cs)
    "Well," Rick sighed, "the user was right."
    And with that, the universe collapsed inward and promptly ceased to exist.
  • dkallen (unregistered)

    The obvious solution is to catch the error, loop until SYSDATE changes, then retry. The loop should go very fast on the new hardware.

  • Owen Two (unregistered)
    ...populated by a trigger
    Should have been populated by a sequence.
  • Dave (unregistered) in reply to dkallen
    dkallen:
    The obvious solution is to catch the error, loop until SYSDATE changes, then retry. The loop should go very fast on the new hardware.
    ^ This.
  • Catty (unregistered) in reply to Owen Two
    Should have been populated by a sequence.

    Since they are using an oracle database using a trigger is probably right. Oracle databases are a real WTF you use triggers to support auto-numbered columns.

  • (cs)

    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

  • Robb (unregistered)

    It takes a real DB Admin to synchronize sleep calls.

  • (cs) in reply to boog
    "Well," Rick sighed, "the user was right."

    Immediately after which, Rick apologized profusely to the user for doubting him.

    Ha, as if.

  • (cs) in reply to Owen Two
    Owen Two:
    ...populated by a trigger
    Should have been populated by a sequence.
    Do you know that it isn't? Triggers can pull the nextval from a sequence, can't they?
  • Robb (unregistered)
    "Well," Rick sighed, "the user was right."

    You know, I think I'll just Rick-Roll myself. It'll just be easier.

  • Ollie Jones (unregistered)

    This is normal. All production dbms instances look like buried utility lines in a city.

    In enterprise software, we need 1-800-dig-safe to come and spray orange paint on stuff before we change it.

    Seriously.

  • (cs)

    N_TRXN_ID: "snif Nobody notices me. snif Nobody cares."

  • Jack (unregistered)
    some orders had the same item on two lines meant that the transaction exploded
    Yeah, but why would anyone wanna do that? Stupid users. See, it was their fault after all, not the upgrade.
  • XXXXX (unregistered)

    http://www.laputan.org/mud/mud.html

    Clearly Rick was adhering to Keep it Working & Shearing Layers. But perhaps he should have looke dfor a way to sweep it under the rug...

  • (cs)

    I just wanted to mention, for the benefit of anyone fortunate enough not to have worked with Oracle databases, that this: "For the same reason, he didn't want to change the timestamp to an actual timestamp datatype, which supported fractional seconds." is not nonsensical. A date field in Oracle has date plus hours/minutes/seconds, a timestamp field has the same information but at higher precision. But for some unfathomable reason, most PL/SQL queries that are handed a date when they were expecting a timestamp, or vice-versa, will choke and die. Unlike when dealing with numeric fields of different precisions, or datetime fields of different precisions in any other DBMS that I know of, Oracle refuses to cope with this and just spits errors at you.

    If you're smart, you write explicit CAST statements into your PL/SQL to handle column definition changes of this kind. If you're even smarter, you use a different DBMS. :)

  • (cs) in reply to Robb
    Robb:
    It takes a real baby to synchronize sleep calls.
    FTFY
  • OneMist8k (unregistered)

    "...morbidly-obese sweat-pant wearer in a buffet line..."

    Stop. I'm getting horny.

  • airdrik (unregistered) in reply to Alistair Wall
    Alistair Wall:
    Selligent?

    They advised us to restrict bulk uploads to 300 records per second for this reason.

    ftfy (another wtf is SQL Server datetime which has a granularity of 1/300 seconds)

  • LANMind (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    Good Job. You got the exact point of the story.

  • (cs)

    Pretty standard practice in under-documented code systems that you have no idea what the hell is going on.

    It took an act of CEO to get our DB team to set up foreign key constraints and not do implicit key constratints

  • Anon (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    Yes, well done captain obvious.

  • Ton (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    Woosh...

  • Ace (unregistered) in reply to Catty

    The problem arises when "developers" refuse to use triggers and instead set the sequence number in the service layer (OpenJPA seems to prefer this by default, or at least so it seems). Did anyone say "data integrity"? Aka "it's not the software's fault; I think we need a more powerful server..or two". </rant>

  • Sweatpants Wearing Nagesh (unregistered)
    The Article:
    But dangling from the end of the key like an angry Klingon hung "D_TIMESTAMP".

    In India, Software Engineers have Mega-Maids for taking care of this.

  • Dan (unregistered) in reply to Sweatpants Wearing Nagesh
    Sweatpants Wearing Nagesh:
    The Article:
    But dangling from the end of the key like an angry Klingon hung "D_TIMESTAMP".

    In India, Software Engineers have Mega-Maids for taking care of this.

    "What happened?" "It's Mega-Maid, sir. She's gone from suck to blow!"

  • (cs) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    It was probably added with that intent, by a later maintainer that dodn't go through the last mile and changed the primary key because, well, it ain't broke... But he kept the column there, so that no future update would make such column impossible to implement.

    Yep, I've been on those foot.

  • (cs) in reply to Ace
    Ace:
    The problem arises when "developers" refuse to use triggers and instead set the sequence number in the service layer (OpenJPA seems to prefer this by default, or at least so it seems). Did anyone say "data integrity"? Aka "it's not the software's fault; I think we need a more powerful server..or two". </rant>

    Who talked about a sequence at the application layer? You know, real DBMS (even MySQL, but not Oracle) have sequences you can use, directly at the data layer, keeping data integrity.

  • boog (unregistered) in reply to boog
    boog:
    "Well," Rick sighed, "the user was right."
    And with that, the universe collapsed inward and promptly ceased to exist.
    Who cares?
  • (cs) in reply to boog
    boog:
    boog:
    "Well," Rick sighed, "the user was right."
    And with that, the universe collapsed inward and promptly ceased to exist.
    Who is left to care?

    FTFY

  • (cs) in reply to boog
    boog (unregistered):
    boog:
    "Well," Rick sighed, "the user was right."
    And with that, the universe collapsed inward and promptly ceased to exist.
    Who cares?
    Nobody.
  • BobbyTables (unregistered)

    Always nice to hear a happy ending.

  • John (unregistered) in reply to boog

    Actually, the upgrades were removed the next day....

  • Gunslinger (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    Very good young padawan. That's why this story is here.

  • Les (unregistered)

    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

  • (cs) in reply to Les
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Wha?

  • (cs) in reply to Les
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Having a unique index on the column might still be of considerable benefit, especially for all the queries making use of the column.

    TFA:
    ...undocumented and unreferenced in any process he could find...
    Okay, never mind.
  • Omnomnonymous (unregistered) in reply to Les
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Not sure if troll?

  • (cs) in reply to Omnomnonymous
    Omnomnonymous:
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Not sure if troll?

    Actually, I agree with him. Your primary key should be a unique identifier for your entity. Which means it should convey some meaning about the identity of the entity. In general, slapping an auto-numbered PK on a table is a hack. A well defined entity should define its own primary key.

  • rfoxmich (unregistered) in reply to Catty

    Yes well with Oracle it'd going to be a trigger that pulls data from a sequence so you're both right which leads me to the captcha : saluto

    Catty:
    Should have been populated by a sequence.

    Since they are using an oracle database using a trigger is probably right. Oracle databases are a real WTF you use triggers to support auto-numbered columns.

  • rfoxmich (unregistered) in reply to Remy Porter

    Unless, of course you are the architect of CakePHP.

    Remy Porter:
    Omnomnonymous:
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Not sure if troll?

    Actually, I agree with him. Your primary key should be a unique identifier for your entity. Which means it should convey some meaning about the identity of the entity. In general, slapping an auto-numbered PK on a table is a hack. A well defined entity should define its own primary key.

  • (cs) in reply to @Deprecated
    @Deprecated:
    "Well," Rick sighed, "the user was right."

    Immediately after which, Rick apologized profusely to the user for doubting him.

    Ha, as if.

    Well, in one important sense, the user was not right in what he probably assumed: It wasn't that the upgrade was the only cause of the problem, or was done incorrectly. It was merely that a valid configuration change in the hardware tickled a bug in the application.

    If you switch from ordinary water to filtered water in an industrial system, and it causes the system to fail because mineral deposits were sealing cracks in the water lines that weren't supposed to be there in the first place, I wouldn't say the water switch "caused" the failure. Rather, the failure was already there in the system and the previous state of affairs was masking it.

  • (cs) in reply to Remy Porter
    Remy Porter:
    Omnomnonymous:
    Les:
    If N_TRXN_ID is auto-populated by a trigger, it has no business being in a primary key.

    Think about it. N_TRXN_ID will always be unique, even if your code starts inserting the same row over and over. You may as well not have a primary key; the end result is the same, and you'll save a little bit of overhead.

    Not sure if troll?

    Actually, I agree with him. Your primary key should be a unique identifier for your entity. Which means it should convey some meaning about the identity of the entity. In general, slapping an auto-numbered PK on a table is a hack. A well defined entity should define its own primary key.

    You're referring to natural keys, which unfortunately I have used and been burned by them in the past. Also because natural keys are occasionally maintained by users (think something like ItemNo), and you can guess what kind of rat-hole this could go down...

    Surrogate keys, IMO, are the safest route, although I do agree that they are meaningless to the entity. They also usually allow increased performance when performing joins because you're comparing a single unique value vs multiple values which can be int, datetime, varchar, etc.

    If you need uniqueness, just add a unique constraint on them, therefore you have them indexed, unique plus the gains from not having to manage the PK through triggers manually and you know (locally) you won't have ID collisions.

  • (cs) in reply to Ace
    Ace:
    The problem arises when "developers" refuse to use triggers and instead set the sequence number in the service layer (OpenJPA seems to prefer this by default, or at least so it seems). Did anyone say "data integrity"? Aka "it's not the software's fault; I think we need a more powerful server..or two". </rant>
    Ugh, a "DBA". How about just start transaction, get next value of sequence, insert new record using sequence number, then insert child records using sequence number, then close transaction.

    Using a trigger makes it very hard to find the foreign key to set in the child records.

  • Lorens (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?

    I suspect the original programmer did not understand the goal of a primary key. TRXID must have been added by somebody else, probably someone who left in disgust before he could implement it or document it.

    I have worked with at least two supposedly competent persons who did not understand the concept of a primary key.

    The first one (third-year student) set the key to be the client name plus the client's address. Many of our clients were multi-located. When I asked what he thought would happen when a client moved, he proudly replied that he'd thought of that: the code that did the address change went through all the related tables and updated the key.

    The second one (fiftyish with supposedly +10 years experience) came asking if we could add an index to a database because otherwise her code wouldn't work. We didn't understand how lack of an index could cause anything except slowness. After long confused explanations it appeared that she wanted us to change the primary key. We told her she'd need to ask the manager responsible for the databases. She said "I know, I did that, but she told me it was the wrong way to solve the problem". So she came to see if somebody else could make the change . . .

    Both were gone inside the month.

  • (cs) in reply to Lorens
    Lorens:
    her she her she'd She she she

    Ahh, there's your problem...

  • oh, please (unregistered) in reply to CodeRage
    CodeRage:
    Ace:
    The problem arises when "developers" refuse to use triggers and instead set the sequence number in the service layer (OpenJPA seems to prefer this by default, or at least so it seems). Did anyone say "data integrity"? Aka "it's not the software's fault; I think we need a more powerful server..or two". </rant>
    Ugh, a "DBA". How about just start transaction, get next value of sequence, insert new record using sequence number, then insert child records using sequence number, then close transaction.

    Using a trigger makes it very hard to find the foreign key to set in the child records.

    Perhaps you should try "Insert into y returning x", why don't you?

  • anonimes (unregistered) in reply to BentFranklin
    BentFranklin:
    Unique, auto-numbered, never null... Shouldn't TRXID have been the primary key all along?
    [image]
  • (cs) in reply to C-Octothorpe
    C-Octothorpe:
    Lorens:
    her she her she'd She she she

    Ahh, there's your problem...

    Naughty ... I shouldn't, but: +1 PML

Leave a comment on “The Key to a Good Schema”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article