Subject: Re: Bug with sequence
From: Oliver Elphick <olly@lfix.co.uk>
To: Thomas Aichinger <taich@gmx.at>
Cc: pgsql-general@postgresql.org
In-Reply-To: <92c0776e.0211180745.49911131@posting.google.com>
References: <92c0776e.0211180745.49911131@posting.google.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 20 Nov 2002 08:53:11 +0000
Message-Id: <1037782392.29678.88.camel@linda>
Mime-Version: 1.0

On Mon, 2002-11-18 at 15:45, Thomas Aichinger wrote:
> Hi,
> 
> I recently installed pg 7.2.3 on my linux box and discovered that
> there are some problems with datatype serial and sequence.
> 
> 1.) If you create a table with a datatype serial, the corrsponding
> sequence will be created, but if you drop the table the sequence is
> not dropped.

This is fixed in 7.3

> 2.) If you create a sequence and grant it to public one cant use
> currval() until one used setval() or nextval().
> "ERROR:  midnr.currval is not yet defined in this session"

This is how it is intended to work.  Read the manual...
And it is nothing to do with its being granted to public; it is
fundamental to what currval() does, which is to provide the last value
given by nextval() *in*the*current*session*.  If there has been no use
of nextval(), currval() cannot report anything.

If you want the last value given by anyone on the sequence, you could
use "select last_value from <sequence_name>", but that would not give
you anything done by uncompleted transactions.  That's why currval()
exists.

> 3.) Sometimes one gets 'not enogh privileges' error when using
> nexval()

When the sequence is created, you need to grant access rights on it to
other users who will need it.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                            
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If my people, which are called by my name, shall 
      humble themselves, and pray, and seek my face, and 
      turn from their wicked ways; then will I hear from 
      heaven, and will forgive their sin, and will heal 
      their land."               II Chronicles 7:14