Thread

1. Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T05:03:12Z

   Hi,
   
   I would like to be able to harden the conditions
   of generating IDENTITY columns so the
   events below run in this order:
   
   - assign values for regular columns (with or without DEFAULT)
   - NOT NULL checks on regular columns
   - CHECK constraints whose expression contains only regular columns
   
   - assign values for GENERATED columns
   - NOT NULL checks on GENERATED columns
   - CHECK constraints whose expression may contain regular
     or GENERATED columns
   
   - UNIQUE index checks that has only regular columns
   - UNIQUE index checks that may have regular or GENERATED columns
   
   - assign values for IDENTITY column
   - NOT NULL on IDENTITY
   - CHECK constraints on IDENTITY
   - UNIQUE index checks that can reference IDENTITY column
   
   At this point the heap tuple and the index tuple can be inserted
   without further checks.
   
   Currently tuple->t_self is required by ExecInsertIndexTuples()
   and I don't see any way to make IDENTITY work the way it's
   intended but to mix heap_insert()/heap_update() and
   ExecInsertIndexTuples() together and use the result in
   ExecInsert() and ExecUpdate().
   
   Would it be acceptable?
   
   Best regards,
   Zoltán Böszörményi
   
   
   

2. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Tom Lane <tgl@sss.pgh.pa.us> — 2007-03-01T06:06:04Z

   Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
   > Would it be acceptable?
   
   No, because you can't create index entries when you haven't yet got the
   TID for the heap tuple.  What do you propose doing, insert a dummy index
   entry and then go back to fill it in later?  Aside from approximately
   doubling the work involved, this is fundamentally broken because no
   other backend could know what to do upon encountering the dummy index
   entry --- there's no way for it to check if the entry references a live
   tuple or not.  Not to mention that a crash here will leave a permanently
   dummy index entry that there's no way to vacuum.
   
   The other rearrangements you suggest are not any more acceptable;
   we are not going to restructure the entire handling of defaults and
   check constraints around a single badly-designed SQL2003 feature.
   
   			regards, tom lane
   
   

3. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T07:04:53Z

   Hi,
   
   Tom Lane írta:
   > Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
   >   
   >> Would it be acceptable?
   >>     
   >
   > No, because you can't create index entries when you haven't yet got the
   > TID for the heap tuple.  What do you propose doing, insert a dummy index
   > entry and then go back to fill it in later?  Aside from approximately
   >   
   
   No, I was thinking about breaking up e.g. heap_insert()
   to be able to mix with ExecInsertIndexTuples() so I get a
   pinned buffer and have the heap_tuple's t_self set first
   then doing the uniqueness checks step by step.
   BTW, can I use modify_tuple() after doing
   RelationGetBufferForTuple() and RelationPutHeapTuple(),
   right?
   
   > doubling the work involved, this is fundamentally broken because no
   >   
   
   Well, the work wouldn't be doubled as all the unique indexes
   have to be checked anyway with the current way, too, to have
   the tuple accepted into the database.
   
   > other backend could know what to do upon encountering the dummy index
   > entry --- there's no way for it to check if the entry references a live
   > tuple or not.  Not to mention that a crash here will leave a permanently
   > dummy index entry that there's no way to vacuum.
   >
   > The other rearrangements you suggest are not any more acceptable;
   > we are not going to restructure the entire handling of defaults and
   > check constraints around a single badly-designed SQL2003 feature.
   >   
   
   My IDENTITY/GENERATED patch broke up the
   checks currently this way (CHECK constraints are prohibited
   for special case columns):
   
   - normal columns are assigned values (maybe using DEFAULT)
   - check NOT NULLs and CHECKs for normal columns
   
   ( Up to this point this works the same way as before if you don't
      use neither IDENTITY nor GENERATED. )
   
   - assign GENERATED with ther values
   - check NOT NULLs for GENERATED
   - assign IDENTITY with value
   - check NOT NULL for IDENTITY
   
   and
   
   - check UNIQUE for everything
   
   Identity would be special so it doesn't inflate the sequence
   if avoidable. Currently the only way if UNIQUE fails
   for any index which is still very much makes it unusable.
   
   What I would like to achieve is for IDENTITY to skip
   a sequence value and fail to be INSERTed if the IDENTITY
   column's uniqe check is failed. Which pretty much means
   that there is already a record with that IDENTITY value
   regardless of the UNIQUE index is defined for only the IDENTITY
   column or the IDENTITY column is part of a multi-column
   UNIQUE index.
   
   If I could broke up the order of events the way I described
   in my first mail, I could re-enable having CHECK constraints
   for both IDENTITY and GENERATED columns.
   
   The point with GENERATED is you have to have
   all other columns assigned with values BEFORE
   being able to compute a GENERATED column
   that reference other columns in its expression so
   you _have to_ break up the current order of computing
   DEFAULTs. I know a computed column could be done
   either in the application or with SELECTs but compare
   the amount of work: if you do it in the SELECT you have to
   compute the expressions every time the SELECT is run
   making it slower. Doing it on UPDATE or INSERT
   makes it LESS work in a fewer INSERT/UPDATE +
   heavy SELECT workload. Of course, for a heavy UPDATE
   workload it makes it more work but only if you actually
   use GENERATED columns. It means exatly the same
   amount of work if you use IDENTITY as with SERIAL,
   it's just made in different order.
   
   The GENERATED column is an easy of use feature
   with possibly having less work, whereas the IDENTITY
   column is mandatory for some applications (e.g. accounting
   and billing is stricter in some countries) where you simply
   cannot skip a value in the sequence, the strict monotonity is
   not enough.
   
   Best regards,
   Zoltán Böszörményi
   
   
   

4. Postgres Replication

   Mageshwaran <j_mageshwaran@sifycorp.com> — 2007-03-01T08:00:33Z

   Hi,
   
   Some body help me regarding postgres replication, Give me some ideas .
   
   Thanks in advance
   
   Regards
   J Mageshwaran
   
   
   ********** DISCLAIMER **********
   Information contained and transmitted by this E-MAIL is proprietary to 
   Sify Limited and is intended for use only by the individual or entity to 
   which it is addressed, and may contain information that is privileged, 
   confidential or exempt from disclosure under applicable law. If this is a 
   forwarded message, the content of this E-MAIL may not have been sent with 
   the authority of the Company. If you are not the intended recipient, an 
   agent of the intended recipient or a  person responsible for delivering the 
   information to the named recipient,  you are notified that any use, 
   distribution, transmission, printing, copying or dissemination of this 
   information in any way or in any manner is strictly prohibited. If you have 
   received this communication in error, please delete this mail & notify us 
   immediately at admin@sifycorp.com
   
   
   Watch the latest updates on Mumbai, with video coverage of news, events,
   Bollywood, live darshan from Siddhivinayak temple and more, only on
   www.mumbailive.in
   
   Watch the hottest videos from Bollywood, Fashion, News and more only on
   www.sifymax.com
   
   
   

5. Re: Postgres Replication

   Andrew Dunstan <andrew@dunslane.net> — 2007-03-01T09:21:58Z

   Mageshwaran wrote:
   > Hi,
   >
   > Some body help me regarding postgres replication, Give me some ideas .
   >
   > Thanks in advance
   >
   >
   
   Here are some ideas:
   
   lose the idiotic, pointless and inaccurate email addendum, especially 
   the ads for bollywood etc.
   
   do some research yourself, especially by reading the Postgres 
   documentation - google is also your friend
   
   ask questions in the correct forum, which for this type of question this 
   is not.
   
   do not create mail on a new subject by replying to an old email and 
   changing the subject line
   
   
   cheers
   
   andrew
   
   
   

6. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Florian Pflug <fgp@phlo.org> — 2007-03-01T10:26:23Z

   Zoltan Boszormenyi wrote:
   > The GENERATED column is an easy of use feature
   > with possibly having less work, whereas the IDENTITY
   > column is mandatory for some applications (e.g. accounting
   > and billing is stricter in some countries) where you simply
   > cannot skip a value in the sequence, the strict monotonity is
   > not enough.
   
   But just postponing nextval() until after the uniqueness checks
   only decreases the *probability* of non-monotonic values, and
   *does not* preven them. Consindert two transactions
   
   A: begin ;
   B: Begin ;
   A: insert ... -- IDENTITY generates value 1
   B: insert .. -- IDENTITY generates value 2
   A: rollback ;
   B: commit ;
   
   Now there is a record with IDENTITY 2, but not with 1. The *only*
   way to fix this is to *not* use a sequence, but rather do
   lock table t in exclusive mode ;
   select max(identity)+1 from t ;
   to generate the identity - but of course this prevents any concurrent
   inserts, which will make this unuseable for any larger database.
   
   Note that this is not a deficency of postgres sequences - there is no
   way to guarantee stricly monotonic values while allowing concurrent
   selects at the same time. (Other than lazyly assigning the values, but
   this needs to be done by the application)
   
   I agree that I'd be nice to generate the identity columns as late as
   possible to prevents needless gaps, but not if price is a for more
   intrusive patch, or much higher complexity.
   
   greetings, Florian Pflug
   
   
   

7. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T10:48:02Z

   Florian G. Pflug írta:
   > Zoltan Boszormenyi wrote:
   >> The GENERATED column is an easy of use feature
   >> with possibly having less work, whereas the IDENTITY
   >> column is mandatory for some applications (e.g. accounting
   >> and billing is stricter in some countries) where you simply
   >> cannot skip a value in the sequence, the strict monotonity is
   >> not enough.
   >
   > But just postponing nextval() until after the uniqueness checks
   > only decreases the *probability* of non-monotonic values, and
   > *does not* preven them. Consindert two transactions
   >
   > A: begin ;
   > B: Begin ;
   > A: insert ... -- IDENTITY generates value 1
   > B: insert .. -- IDENTITY generates value 2
   > A: rollback ;
   > B: commit ;
   
   I can understand that. But your example is faulty,
   you can't have transaction inside a transaction.
   Checkpoints are another story. 8-)
   
   You can have some application tricks to
   have continous sequence today with regular
   serials but only if don't have a unique index
   that doesn't use the serial column. Inserting
   a record to that table outside the transaction,
   making note of the serial value.
   
   If subsequent processing fails (because of unique,
   check constraint, etc) you have to go back to the main
   table and modify the record, indicating that the record
   isn't representing valid data. But you must keep it with
   the serial value it was assigned. I have seen systems
   requiring this. My point is that with the identity
   column, you will be able to define unique index
   on the table that exludes the identity column.
   
   > Now there is a record with IDENTITY 2, but not with 1. The *only*
   > way to fix this is to *not* use a sequence, but rather do
   > lock table t in exclusive mode ;
   > select max(identity)+1 from t ;
   > to generate the identity - but of course this prevents any concurrent
   > inserts, which will make this unuseable for any larger database.
   >
   > Note that this is not a deficency of postgres sequences - there is no
   > way to guarantee stricly monotonic values while allowing concurrent
   > selects at the same time. (Other than lazyly assigning the values, but
   > this needs to be done by the application)
   
   Agreed.
   
   > I agree that I'd be nice to generate the identity columns as late as
   > possible to prevents needless gaps, but not if price is a for more
   > intrusive patch, or much higher complexity.
   
   Intrusive, hm? The catalog have to indicate that the column
   is IDENTITY, otherwise you cannot know it.
   
   The cost I am thinking now is an extra heap_update()
   after heap_insert() without generating the identity value
   and inserting index tuples to indexes that doesn't
   contain the identity column.
   
   Best regards,
   Zoltán Böszörményi
   
   
   

8. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T10:51:39Z

   Zoltan Boszormenyi írta:
   > Florian G. Pflug írta:
   >> Zoltan Boszormenyi wrote:
   >>> The GENERATED column is an easy of use feature
   >>> with possibly having less work, whereas the IDENTITY
   >>> column is mandatory for some applications (e.g. accounting
   >>> and billing is stricter in some countries) where you simply
   >>> cannot skip a value in the sequence, the strict monotonity is
   >>> not enough.
   >>
   >> But just postponing nextval() until after the uniqueness checks
   >> only decreases the *probability* of non-monotonic values, and
   >> *does not* preven them. Consindert two transactions
   >>
   >> A: begin ;
   >> B: Begin ;
   >> A: insert ... -- IDENTITY generates value 1
   >> B: insert .. -- IDENTITY generates value 2
   >> A: rollback ;
   >> B: commit ;
   >
   > I can understand that. But your example is faulty,
   > you can't have transaction inside a transaction.
   > Checkpoints are another story. 8-)
   >
   > You can have some application tricks to
   > have continous sequence today with regular
   > serials but only if don't have a unique index
   > that doesn't use the serial column. Inserting
   > a record to that table outside the transaction,
   > making note of the serial value.
   >
   > If subsequent processing fails (because of unique,
   > check constraint, etc) you have to go back to the main
   > table and modify the record, indicating that the record
   > isn't representing valid data. But you must keep it with
   > the serial value it was assigned. I have seen systems
   > requiring this. My point is that with the identity
   > column, you will be able to define unique index
   > on the table that exludes the identity column.
   >
   >> Now there is a record with IDENTITY 2, but not with 1. The *only*
   >> way to fix this is to *not* use a sequence, but rather do
   >> lock table t in exclusive mode ;
   >> select max(identity)+1 from t ;
   >> to generate the identity - but of course this prevents any concurrent
   >> inserts, which will make this unuseable for any larger database.
   >>
   >> Note that this is not a deficency of postgres sequences - there is no
   >> way to guarantee stricly monotonic values while allowing concurrent
   >> selects at the same time. (Other than lazyly assigning the values, but
   >> this needs to be done by the application)
   >
   > Agreed.
   >
   >> I agree that I'd be nice to generate the identity columns as late as
   >> possible to prevents needless gaps, but not if price is a for more
   >> intrusive patch, or much higher complexity.
   >
   > Intrusive, hm? The catalog have to indicate that the column
   > is IDENTITY, otherwise you cannot know it.
   >
   > The cost I am thinking now is an extra heap_update()
   > after heap_insert() without generating the identity value
   > and inserting index tuples to indexes that doesn't
   > contain the identity column.
   
   And as far as I tested the current state, there is no cost
   if you don't use GENERATED or IDENTITY.
   The extra heap_update() would be performed only
   if you have an IDENTITY colum.
   
   > Best regards,
   > Zoltán Böszörményi
   >
   >
   
   
   

9. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

   Florian Pflug <fgp@phlo.org> — 2007-03-01T11:13:04Z

   Zoltan Boszormenyi wrote:
   > Florian G. Pflug írta:
   >> Zoltan Boszormenyi wrote:
   >>> The GENERATED column is an easy of use feature
   >>> with possibly having less work, whereas the IDENTITY
   >>> column is mandatory for some applications (e.g. accounting
   >>> and billing is stricter in some countries) where you simply
   >>> cannot skip a value in the sequence, the strict monotonity is
   >>> not enough.
   >>
   >> But just postponing nextval() until after the uniqueness checks
   >> only decreases the *probability* of non-monotonic values, and
   >> *does not* preven them. Consindert two transactions
   >>
   >> A: begin ;
   >> B: Begin ;
   >> A: insert ... -- IDENTITY generates value 1
   >> B: insert .. -- IDENTITY generates value 2
   >> A: rollback ;
   >> B: commit ;
   > 
   > I can understand that. But your example is faulty,
   > you can't have transaction inside a transaction.
   > Checkpoints are another story. 8-)
   
   A: and B: are meant to denote *two* *different*
   transactions running concurrently.
   
   > You can have some application tricks to
   > have continous sequence today with regular
   > serials but only if don't have a unique index
   > that doesn't use the serial column. Inserting
   > a record to that table outside the transaction,
   > making note of the serial value.
   > 
   > If subsequent processing fails (because of unique,
   > check constraint, etc) you have to go back to the main
   > table and modify the record, indicating that the record
   > isn't representing valid data. But you must keep it with
   > the serial value it was assigned. I have seen systems
   > requiring this. My point is that with the identity
   > column, you will be able to define unique index
   > on the table that exludes the identity column.
   
   Yes, of course you can prevent gaps by just filling them
   with garbage/invalid records of whatever. But I don't see
   why this is usefull - either you want, say, your invoice
   number to be continuous because it's required by law - or
   you don't. But if the law required your invoice numbers to be
   continous, surely just filling the gaps with fake invoices
   it just as illegal as having gaps in the first place.
   
   >> I agree that I'd be nice to generate the identity columns as late as
   >> possible to prevents needless gaps, but not if price is a for more
   >> intrusive patch, or much higher complexity.
   > 
   > Intrusive, hm? The catalog have to indicate that the column
   > is IDENTITY, otherwise you cannot know it.
   > 
   > The cost I am thinking now is an extra heap_update()
   > after heap_insert() without generating the identity value
   > and inserting index tuples to indexes that doesn't
   > contain the identity column.
   
   I'll have to admit that I haven't actually looked at your patch -
   so sorry if I missunderstood things. I got the impression that
   tom's main complaint was that you are shuffling too much existing code
   around in your patch, and I figured that this is partly because you
   try to generate the IDENTITY value as late as possible. Since doing
   this won't prevent gaps, but just reduces the probability of creating
   them, I thought that a way around tom's concerns might be to drop
   that requirement.
   
   I will shut up now, at least until I have read the patch ;-)
   
   greetings, Florian Pflug
   
   
   
   

10. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T12:06:20Z

    Florian G. Pflug írta:
    > Yes, of course you can prevent gaps by just filling them
    > with garbage/invalid records of whatever. But I don't see
    > why this is usefull - either you want, say, your invoice
    > number to be continuous because it's required by law - or
    > you don't. But if the law required your invoice numbers to be
    > continous, surely just filling the gaps with fake invoices
    > it just as illegal as having gaps in the first place.
    
    Not fake invoice, "stornoed" for whatever reason.
    But you have to keep the record to show you didn't delete anything.
    
    Best regards,
    Zoltán Böszörményi
    
    
    

11. Re: Postgres Replication

    Joshua D. Drake <jd@commandprompt.com> — 2007-03-01T13:33:51Z

    Andrew Dunstan wrote:
    > Mageshwaran wrote:
    >> Hi,
    >>
    >> Some body help me regarding postgres replication, Give me some ideas .
    >>
    >> Thanks in advance
    >>
    >>
    > 
    > Here are some ideas:
    
    Tsk, Andrew.
    
    Replication in PostgreSQL is usually served via Point in Time recovery,
    Slony (http://www.slony.info) or Mammoth Replication
    (http://www.commandprompt.com/).
    
    Joshua D. Drake
    
    
    > 
    > lose the idiotic, pointless and inaccurate email addendum, especially
    > the ads for bollywood etc.
    > 
    > do some research yourself, especially by reading the Postgres
    > documentation - google is also your friend
    > 
    > ask questions in the correct forum, which for this type of question this
    > is not.
    > 
    > do not create mail on a new subject by replying to an old email and
    > changing the subject line
    > 
    > 
    > cheers
    > 
    > andrew
    > 
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 3: Have you checked our extensive FAQ?
    > 
    >               http://www.postgresql.org/docs/faq
    > 
    
    
    -- 
    
          === The PostgreSQL Company: Command Prompt, Inc. ===
    Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
                 http://www.commandprompt.com/
    
    Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
    PostgreSQL Replication: http://www.commandprompt.com/products/
    
    
    

12. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T13:50:30Z

    Zoltan Boszormenyi írta:
    >> The cost I am thinking now is an extra heap_update()
    >> after heap_insert() without generating the identity value
    >> and inserting index tuples to indexes that doesn't
    >> contain the identity column.
    >
    > And as far as I tested the current state, there is no cost
    > if you don't use GENERATED or IDENTITY.
    > The extra heap_update() would be performed only
    > if you have an IDENTITY colum.
    
    The modification I imagined is actually working:
    - skip indexes using the identity columns
    - do a simple_heap_update() after all other columns are
      assigned and index tuples are inserted
    - do ExecInsertIndexTuples() on indexes referencing
      the IDENTITY column
    
    However, I get warning messages like:
    
    WARNING:  detected write past chunk end in ExecutorState 0xaaff68
    
    How can I prevent them?
    
    Best regards,
    Zoltán Böszörményi
    
    
    

13. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Tom Lane <tgl@sss.pgh.pa.us> — 2007-03-01T16:00:55Z

    Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
    > However, I get warning messages like:
    > WARNING:  detected write past chunk end in ExecutorState 0xaaff68
    > How can I prevent them?
    
    Find the memory-clobbering bug in your patch.
    
    			regards, tom lane
    
    

14. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Zoltan Boszormenyi <zboszor@dunaweb.hu> — 2007-03-01T16:58:52Z

    Tom Lane írta:
    > Zoltan Boszormenyi <zboszor@dunaweb.hu> writes:
    >   
    >> However, I get warning messages like:
    >> WARNING:  detected write past chunk end in ExecutorState 0xaaff68
    >> How can I prevent them?
    >>     
    >
    > Find the memory-clobbering bug in your patch.
    >
    > 			regards, tom lane
    >   
    
    Thanks, I found it.
    
    Best regards,
    Zoltán Böszörményi
    
    
    

15. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Bruno Wolff III <bruno@wolff.to> — 2007-03-02T22:38:31Z

    On Thu, Mar 01, 2007 at 11:26:23 +0100,
      "Florian G. Pflug" <fgp@phlo.org> wrote:
    > 
    > But just postponing nextval() until after the uniqueness checks
    > only decreases the *probability* of non-monotonic values, and
    > *does not* preven them. Consindert two transactions
    > 
    > A: begin ;
    > B: Begin ;
    > A: insert ... -- IDENTITY generates value 1
    > B: insert .. -- IDENTITY generates value 2
    > A: rollback ;
    > B: commit ;
    > 
    > Now there is a record with IDENTITY 2, but not with 1. The *only*
    > way to fix this is to *not* use a sequence, but rather do
    > lock table t in exclusive mode ;
    > select max(identity)+1 from t ;
    > to generate the identity - but of course this prevents any concurrent
    > inserts, which will make this unuseable for any larger database.
    
    While this demonstrates that you can get holes in the sequence, it doesn't
    show an example that is not monotonic.
    
    > Note that this is not a deficency of postgres sequences - there is no
    > way to guarantee stricly monotonic values while allowing concurrent
    > selects at the same time. (Other than lazyly assigning the values, but
    > this needs to be done by the application)
    
    With in a single session and barring wrap-around you will get monotonicly
    increasing values. You are correct that there is no such guaranty between
    separate sessions that overlap in time.
    
    

16. Re: Is there a way to run heap_insert() AFTER ExecInsertIndexTuples() ?

    Florian Pflug <fgp@phlo.org> — 2007-03-03T00:45:21Z

    Bruno Wolff III wrote:
    > On Thu, Mar 01, 2007 at 11:26:23 +0100,
    >   "Florian G. Pflug" <fgp@phlo.org> wrote:
    >> But just postponing nextval() until after the uniqueness checks
    >> only decreases the *probability* of non-monotonic values, and
    >> *does not* preven them. Consindert two transactions
    >>
    >> A: begin ;
    >> B: Begin ;
    >> A: insert ... -- IDENTITY generates value 1
    >> B: insert .. -- IDENTITY generates value 2
    >> A: rollback ;
    >> B: commit ;
    >>
    >> Now there is a record with IDENTITY 2, but not with 1. The *only*
    >> way to fix this is to *not* use a sequence, but rather do
    >> lock table t in exclusive mode ;
    >> select max(identity)+1 from t ;
    >> to generate the identity - but of course this prevents any concurrent
    >> inserts, which will make this unuseable for any larger database.
    > 
    > While this demonstrates that you can get holes in the sequence, it doesn't
    > show an example that is not monotonic.
    Sorry, my formulation was sloppy. What I meant is that you can't
    guarantee gaplessness.
    
    greetings, Florian Pflug