Обсуждение: Sequence and nextval problem

Hello everybody.
   I've got an vb aplication that uses an Access database. I'm trying 
to convert the database to postgres. The conversion was done ok, but 
i've got a little problem that i don't know how to solve. Let's see if 
anyone can help me.
   The conversion from access database to postgres worked fine. 
Everithing it's ok. But now, when i use my database i've found a problem 
with sequences. In the conversion, the "autonumeric" fields from access 
have been converted to sequences, everithing ok in a first view. The 
problem comes because the autonumeric fields in access always return the 
last value of the table +1, but postgres no. Postgres returns "lost" (i 
don't know how to call them) values. An example.
   This is an example of a table:
      code | description      ----- | ------------          1  | desc 1          2  | desc 2          6  | desc 6
  7  | desc 7
 

   In access if i execute "INSERT INTO table (description) VALUES 
('desc 8'), the result row is  8 |  desc 8   But in postgres the same query te result row is 3 | desc 8
   My question is, can i do something to make ANY sequence to take the 
last value from his associated table, and not a "lost" value?
   Thank you very much

On Mon, Nov 24, 2008 at 12:12 PM, Tk421 <vrobador@gmail.com> wrote:
>   Hello everybody.
>
>   I've got an vb aplication that uses an Access database. I'm trying to
> convert the database to postgres. The conversion was done ok, but i've got a
> little problem that i don't know how to solve. Let's see if anyone can help
> me.
>
>   The conversion from access database to postgres worked fine. Everithing
> it's ok. But now, when i use my database i've found a problem with
> sequences. In the conversion, the "autonumeric" fields from access have been
> converted to sequences, everithing ok in a first view. The problem comes
> because the autonumeric fields in access always return the last value of the
> table +1, but postgres no. Postgres returns "lost" (i don't know how to call
> them) values. An example.
>
>   This is an example of a table:
>
>      code | description
>      ----- | ------------
>          1  | desc 1
>          2  | desc 2
>          6  | desc 6
>          7  | desc 7
>
>
>   In access if i execute "INSERT INTO table (description) VALUES ('desc 8'),
> the result row is  8 |  desc 8
>   But in postgres the same query te result row is 3 | desc 8
>
>   My question is, can i do something to make ANY sequence to take the last
> value from his associated table, and not a "lost" value?

The sequence should be set to the next value available after loading
data and then left alone.  You can set the value with
setval('seqname');  It looks to me like if you did a few more inserts,
you'd hit the value of 6 for your id field and your insert would fail
until the sequence got past 7 then it would start working.

Note that in postgresql, the value given by nextval is the next value
of the sequence, not max(val)+1 as max(val)+1 doesn't scale / isn't
really transaction safe.

On Nov 24, 2008, at 2:12 PM, Tk421 wrote:

>   Hello everybody.
>
>   I've got an vb aplication that uses an Access database. I'm trying  
> to convert the database to postgres. The conversion was done ok, but  
> i've got a little problem that i don't know how to solve. Let's see  
> if anyone can help me.
>
>   The conversion from access database to postgres worked fine.  
> Everithing it's ok. But now, when i use my database i've found a  
> problem with sequences. In the conversion, the "autonumeric" fields  
> from access have been converted to sequences, everithing ok in a  
> first view. The problem comes because the autonumeric fields in  
> access always return the last value of the table +1, but postgres  
> no. Postgres returns "lost" (i don't know how to call them) values.  
> An example.
>
>   This is an example of a table:
>
>      code | description
>      ----- | ------------
>          1  | desc 1
>          2  | desc 2
>          6  | desc 6
>          7  | desc 7
>
>
>   In access if i execute "INSERT INTO table (description) VALUES  
> ('desc 8'), the result row is  8 |  desc 8
>   But in postgres the same query te result row is 3 | desc 8
>
>   My question is, can i do something to make ANY sequence to take  
> the last value from his associated table, and not a "lost" value?
>
>   Thank you very much
>
>

This sounds like if the start of the sequence is set incorrectly:

Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);

btw, you should also not expect a specific value from the sequence  
except that you will always get the next value from the sequence.
it's also generally a bad idea to do select max(someid)+1 from table.  
The whole concept of a sequence is thus much better.

Ries

Tk421 wrote:
>    Hello everybody.
>
>    I've got an vb aplication that uses an Access database. I'm trying to
> convert the database to postgres. The conversion was done ok, but i've
> got a little problem that i don't know how to solve. Let's see if anyone
> can help me.
>
>    The conversion from access database to postgres worked fine.
> Everithing it's ok. But now, when i use my database i've found a problem
> with sequences. In the conversion, the "autonumeric" fields from access
> have been converted to sequences, everithing ok in a first view. The
> problem comes because the autonumeric fields in access always return the
> last value of the table +1, but postgres no. Postgres returns "lost" (i
> don't know how to call them) values.

Access doesn't understand server-side generated keys very well.

You need to use a Before Insert event on the form to issue a passthrough
query using Visual Basic that invokes nextval('seqname') to get the next
ID from the sequence, then fills the primary key field with the return
value. That way, when Access submits the INSERT statement it'll include
the primary key generated by Pg, so you land up with the same effect as
if you left it DEFAULT, except that Access knows what the new ID will be
and doesn't get confused when it can't find the record after inserting it.

Access 2007 has a special quirk, where it'll execute any passthrough
query (including stored procedures) invoked via Visual Basic twice if it
returns a resultset. Consequently, you must EITHER retrieve a return
value from a query OR invoke one with side effects, not both.

If you call nextval(...) and read the return value, you'll actually have
two IDs generated from the sequence, the first of which is discarded.
You probably don't care about this, but it's probably best to avoid the
problem anyway. What you need to do is fire the
    SELECT nextval('seqname')
query with ReturnsRecords=False . You then invoke:
    SELECT currval('seqname')
to read the generated ID. currval(...) will be called twice, but you
don't care as it has no side-effects.

I've attached some Visual Basic code that demonstrates this. It's easily
adapted to other stored procedures. In fact, I've included a sample of
another stored procedure invocation in another routine; just delete it.
form_module.txt contains an example use of the code on a form. It should
be included in the module for the form(s) you want to handle, and
adapted to use the right field name for the primary key. The other file,
passthrough.txt contains a VB module named "passthrough" that contains
the actual passthrough query handling code used by the before insert
routine in the form module.

Note that you're in for some other exciting issues if you use access
with Pg. You will need to load a set of casts and operators to ensure
that Access's odd handling of Boolean values works; you'll need to set
some specific ODBC driver options; you may want to set the
transform_null_equals option in Pg to let Access use it's horrible
"value = NULL" tests, etc. See:

http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html

for sample operator/cast definitions. There are some notes in the top of
the "passthrough.txt" module I attached that explain the ODBC driver
options you'll want.

--
Craig Ringer
Private Sub Form_BeforeInsert(Cancel As Integer)
    'Assign a primary key, since Access isn't smart enough to retrieve the
    'database-generated one (or ODBC provides no generic mechanism for doing so).
    If Not IsNull(Me.booking_id) Then
        Error (1)
    End If
    Me.booking_id = nextval("booking_booking_id_seq")
End SubOption Compare Database

'The following options must be set in the ODBC driver:
'
'Row Versioning       (tells the driver to use only the primary key in record searches)
'True as -1
'(unchecked) bools as char
'
'Additionally, the database must have a set of operators loaded
'to permit certain type comparisons required by MS Access, like
'integer to boolean compares.
'
'Note that Access cannot correctly determine the value of a
'generated primary key. It is necessary to explicitly get the
'next value of the target sequence with a passthrough query
'to get nextval() on the sequence.
'
'-------------------------
' WARNING WARNING WARNING
'-------------------------
' Access 2007 appears to have an quirk bug in  passthrough query support.
' If the ReturnsRecords property is set, a query appears to be fired twice.
' This is ugly if we're invoking a stored procedure that's intended to have
' side effects and return a value. Consider `nextval(seqname)' for example.
' To work around this, no stored procedure with side effects may be called
' with ReturnsRecords = true ; instead, it must make its result available
' via the side effects.
'
Public Function DSN() As String
    DSN = "ODBC;DRIVER={PostgreSQL
Unicode};DATABASE=dbname;SERVER=server.name.example.com;PORT=5432;CA=r;A6=;A7=100;A8=4096;B0=255;B1=8190;BI=0;C2=dd_;CX=1b890ab9;A1=7.4-1"
End Function

Public Function nextval(seqName As String) As Integer
    'Call the nextval(text) function in PostgreSQL with
    'the passed sequence name, and return the resulting value
    'from the sequence. We need this because Access doesn't use,
    'or the ODBC driver doesn't provide, any way to get the ID(s)
    'generated by an INSERT into PostgreSQL using sequence defaults
    'for a primary key.
    Dim MyDB As Database, MyQ As QueryDef, MyRS As Recordset

    On Error GoTo NoOp
    CurrentDb.QueryDefs.Delete "nextval"

NoOp:
    On Error GoTo Err_Execute

    Set MyDB = CurrentDb()
    Set MyQ = MyDB.CreateQueryDef("nextval")
    MyQ.Connect = DSN

    ' Generate the new key, discarding the result. We must do
    ' this to ensure that Access doesn't call nextval(seqname)
    ' twice, as it will if ReturnsRecords is set.
    MyQ.SQL = "SELECT nextval('" & seqName & "')"
    MyQ.ReturnsRecords = False
    MyQ.Execute

    ' Now that there's a value in the context of the current
    ' transaction, retrieve it. Since this procedure has no
    ' side effects, it'll do no harm when Access calls it twice.
    MyQ.SQL = "SELECT currval('" & seqName & "') AS new_id"
    MyQ.ReturnsRecords = True
    Set MyRS = MyQ.OpenRecordset()
    MyRS.MoveFirst
    nextval = MyRS!new_id

    CurrentDb.QueryDefs.Delete "nextval"
    MyQ.Close
    MyRS.Close
    MyDB.Close
    Exit Function

Err_Execute:
    CurrentDb.QueryDefs.Delete "nextval"
    ' Error return
    nextval = -1

End Function


Public Function SplitBooking(bookingID As Integer, FromDate As String) As Integer
    'Invoke the split_booking() stored procedure in PostgreSQL
    Dim MyDB As Database, MyQ As QueryDef, MyRS As Recordset

    On Error GoTo NoOp
    CurrentDb.QueryDefs.Delete "splitTemp"

NoOp:
    On Error GoTo Err_Execute

    Set MyDB = CurrentDb()
    Set MyQ = MyDB.CreateQueryDef("splitTemp")
    MyQ.Connect = DSN

    ' First, split the booking, and discard the return value. Thanks to Access's
    ' quirks, if we set ReturnsRecords=True to get the return value we will call
    ' the procedure twice. Instead, we'll rely on currval(seqname) being set by
    ' the INSERT issued by the procedure.
    MyQ.SQL = "SELECT split_booking(" & bookingID & ", " & FromDate & ")"
    MyQ.ReturnsRecords = False
    MyQ.Execute

    ' OK, the record has been inserted. Now obtain the id using currval(...)
    MyQ.SQL = "SELECT currval('booking_booking_id_seq') AS new_id"
    MyQ.ReturnsRecords = True
    Set MyRS = MyQ.OpenRecordset()
    MyRS.MoveFirst
    SplitBooking = MyRS!new_id

    CurrentDb.QueryDefs.Delete "splitTemp"
    MyQ.Close
    MyRS.Close
    MyDB.Close
    Exit Function

Err_Execute:
    CurrentDb.QueryDefs.Delete "splitTemp"
    ' Error return
    SplitBooking = -1
End Function

Public Function CopyBooking(bookingID As Integer) As Integer
    CopyBooking = SplitBooking(bookingID, "NULL")
End Function

Earlier, Craig Ringer wrote:

> for sample operator/cast definitions. There are some notes in the top of 
> the "passthrough.txt" module I attached that explain the ODBC driver 
> options you'll want.

Also, note that in the message I just posted the function DSN() must be 
adapted to include the approprate DSN for your database. You can most 
easily obtain this by opening an ODBC linked table in design mode 
(ignore the warning about changes not being saved), opening the 
properties palette, and fishing the linked table DSN out from there.

Thinking about it, I should adjust my code to pull the DSN out of a 
linked table at runtime. When I initially put it together I didn't 
realise it was stored as a linked table property, so I hand-wrote a 
suitable DSN.

If you try to use the module I attached without changing the DSN to 
match your database it just won't work.

--
Craig Ringer

At 11:20 PM 11/24/2008, pgsql-sql-owner@postgresql.org wrote:
>Message-Id: <0FCB821D-C666-4FB9-B9FE-BA7B9EECE8C5@rvt.dds.nl>
>From: ries van Twisk <pg@rvt.dds.nl>
>To: Tk421 <vrobador@gmail.com>
>In-Reply-To: <492AFC8C.302@gmail.com>
>Subject: Re: Sequence and nextval problem
>Date: Mon, 24 Nov 2008 16:21:40 -0500
>References: <492AFC8C.302@gmail.com>
>X-Archive-Number: 200811/144
>X-Sequence-Number: 31928
>
>On Nov 24, 2008, at 2:12 PM, Tk421 wrote:
>>   The conversion from access database to postgres worked fine.
>>Everithing it's ok. But now, when i use my database i've found a
>>problem with sequences. In the conversion, the "autonumeric" fields
>>from access have been converted to sequences, everithing ok in a
>>first view. The problem comes because the autonumeric fields in
>>access always return the last value of the table +1, but postgres
>>no. Postgres returns "lost" (i don't know how to call them) values.
>>An example.
>>
>>[snip]
>>   In access if i execute "INSERT INTO table (description) VALUES
>>('desc 8'), the result row is  8 |  desc 8
>>   But in postgres the same query te result row is 3 | desc 8
>>
>>   My question is, can i do something to make ANY sequence to take
>>the last value from his associated table, and not a "lost" value?
>
>This sounds like if the start of the sequence is set incorrectly:
>
>Try this : SELECT setval('NAME OF SEQUENCE', SOME_INTEGER, true);
>
>btw, you should also not expect a specific value from the sequence
>except that you will always get the next value from the sequence.
>it's also generally a bad idea to do select max(someid)+1 from 
>table.
>The whole concept of a sequence is thus much better.

I think this is sound general advice for a production database.

However if you control the database such that you can prevent access to 
it while you are updating it, you can run something like:

SELECT setval('NAME OF SEQUENCE', (select max(id)+1 from 
table_of_sequence), true);

Where "table_of_sequence" is the name of the table which the sequence 
is attached to.

The reason you don't use that syntax is that it's not multi-user safe. 
But if you know there are no other users running changes to that 
sequence when you run your updates, then you're good to go. It's a very 
fast way to update all your tables to make sure the sequence #'s are 
all valid, without having to look up the max value on each one (which 
would also require that you shut off access to the table and for a much 
longer time).

Hope that helps,

Steve