> How is this strange PK being assigned? What I see there is a
> char(20) that is populated variously as simple digits or (in
> the case of the problem record(s), as a mish-mash of numbers
> and blanks.

The PK is assigned using two 10 digit blocks, the first as a global
identifier and the second from a generator.

> On the client side "how it happened" is probably that two
> different client apps posted that key and, at the time of
> posting , they *were*
> different. For example, app A posted a key as '327 11020'
> (no trailing
> blanks) while app B posted '327 11020 ' (some trailing
> blanks). So,
> as the two values stood at the time the records were created,
> they were different, i.e. the difference between the supplied
> trailing blanks and the trailing blanks that would be added
> to make the length up to 20 was recognised.
>
> Then, you back up. Gbak will pack those values with all
> trailing blanks stripped. Then, when it tries to
> reconstitute the values on restore, it pads them both out to
> 20 with vanilla-flavoured blanks and boom! you've got duplication.

The database supplied has not been backed up (I tried so that I could reduce
the size, but to no luck with the restoration). I can't find any trailing
blanks on the keys, somehow there is a key in there violating the PK
constraint!

I also tried inserting a row with the key padded out with ' ' (spaces) i.e.
simular to the example you gave above, this failed with a PK constraint
violation - this was done in IBSQL, does this trim the values?

> I see several problems here. One is the discrepancy between
> the original and the constituted values. In principle, this
> wants looking at as a possible bug. We should be able to
> protect data from bad design.

Sorry, but I don't see how bad design of a PK should be able to violate the
PK constraints in the database. Even using a char(20) as the PK, if the
values are different then why does FB choke when doing any operation on that
key?

> Another is the choice of a char type to store data of
> arbitrary size and format. Char types should be restricted
> only to store data of known size and format. Even worse is
> the choice to use that arbitrary data as a key. It's no way
> atomic. It's enough of a problem defining non-simple keys
> that have no integrity rules to protect them. It's a
> disaster waiting to happen when you expose these keys to
> users and/or depend on client code to validate the content.
>
> An inquiry will no doubt reveal that someone at your customer
> has been dabbling around in the database with a DB admin
> tool. There's nothing quite so effective for finding your
> design flaws.

I still have no idea how someone (expecially one of our customers ;)) would
be able to duplicate this scenario, even in a DB admin tool.

Are you able to reproduce this? If so, I would love to know how.

> ./heLen
>

Cheers,

Ryan.

> Now,
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources
> item on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>