pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug with sequence
19+ messages / 11 participants
[nested] [flat]
* Bug with sequence
@ 2002-11-18 15:45 Thomas Aichinger <taich@gmx.at>
2002-11-19 01:22 ` Re: Bug with sequence Lee Harr <missive@frontiernet.net>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
0 siblings, 2 replies; 19+ messages in thread
From: taich@gmx.at @ 2002-11-18 15:45 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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.
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"
3.) Sometimes one gets 'not enogh privileges' error when using
nexval()
Am I the first one who discovered that?
Thanks
Thomas
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
@ 2002-11-19 01:22 ` Lee Harr <missive@frontiernet.net>
1 sibling, 0 replies; 19+ messages in thread
From: Lee Harr @ 2002-11-19 01:22 UTC (permalink / raw)
To: pgsql-general@postgresql.org
In article <92c0776e.0211180745.49911131@posting.google.com>,
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.
>
I am pretty sure this has been fixed in version 7.3 (due to be
release real soon now.)
> 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 actually the way sequences work (ie, it's a feature, not
a bug, heh heh)
currval() gives you the current value of the sequence
*as seen in this session* ... so until you do a setval()
or a nextval() there is no value seen by this session.
> 3.) Sometimes one gets 'not enogh privileges' error when using
> nexval()
>
nextval()? hmm. well. If the user does not have privelege to
update the sequence, then the call will fail.
>
> Am I the first one who discovered that?
>
Nah, and probably not the first one to avoid the FAQ either.
heh heh.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
@ 2002-11-20 08:53 ` Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
1 sibling, 1 reply; 19+ messages in thread
From: Oliver Elphick @ 2002-11-20 08:53 UTC (permalink / raw)
To: Thomas Aichinger <taich@gmx.at>; +Cc: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
@ 2002-11-20 22:12 ` Oliver Elphick <olly@lfix.co.uk>
2002-11-20 23:20 ` Re: [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 19:14 ` Re: [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 3 replies; 19+ messages in thread
From: Oliver Elphick @ 2002-11-20 22:12 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; +Cc: pgsql-hackers@postgresql.org
On Wed, 2002-11-20 at 21:35, Robert Treat wrote:
> On Wed, 2002-11-20 at 03:53, Oliver Elphick wrote:
> > 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
> >
>
> out of curiosity, do you know the logic that implements this fix? I have
> a couple of tables that use the same sequence; I'm wondering if dropping
> one of the tables removes the sequence or if I have to drop all tables
> before the sequence is removed
I just tried it.
I created a sequence using SERIAL when I created a table. I used the
same sequence for another table by setting a column default to
nextval(sequence).
I deleted the first table. The sequence was deleted too, leaving the
default of the second table referring to a non-existent sequence.
Could this be a TODO item in 7.4, to add a dependency check when a
sequence is set as the default without being created at the same time?
--
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
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
@ 2002-11-20 23:20 ` Rod Taylor <rbt@rbt.ca>
2 siblings, 0 replies; 19+ messages in thread
From: Rod Taylor @ 2002-11-20 23:20 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; PostgreSQL-development <pgsql-hackers@postgresql.org>
This requires changing the nextval() function to be an attribute of the
sequence.
ie. sequence.nextval and sequence.currval to deal with the sequence.
It should also be on the todo list.
On Wed, 2002-11-20 at 17:12, Oliver Elphick wrote:
> On Wed, 2002-11-20 at 21:35, Robert Treat wrote:
> > On Wed, 2002-11-20 at 03:53, Oliver Elphick wrote:
> > > 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
> > >
> >
> > out of curiosity, do you know the logic that implements this fix? I have
> > a couple of tables that use the same sequence; I'm wondering if dropping
> > one of the tables removes the sequence or if I have to drop all tables
> > before the sequence is removed
>
> I just tried it.
>
> I created a sequence using SERIAL when I created a table. I used the
> same sequence for another table by setting a column default to
> nextval(sequence).
>
> I deleted the first table. The sequence was deleted too, leaving the
> default of the second table referring to a non-existent sequence.
>
>
> Could this be a TODO item in 7.4, to add a dependency check when a
> sequence is set as the default without being created at the same time?
--
Rod Taylor <rbt@rbt.ca>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
@ 2002-11-21 02:40 ` Justin Clift <justin@postgresql.org>
2002-11-21 03:44 ` Re: [GENERAL] Bug with sequence Neil Conway <neilc@samurai.com>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 19+ messages in thread
From: Justin Clift @ 2002-11-21 02:40 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Oliver Elphick wrote:
>
> On Wed, 2002-11-20 at 21:35, Robert Treat wrote:
> > On Wed, 2002-11-20 at 03:53, Oliver Elphick wrote:
> > > 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
> > >
> >
> > out of curiosity, do you know the logic that implements this fix? I have
> > a couple of tables that use the same sequence; I'm wondering if dropping
> > one of the tables removes the sequence or if I have to drop all tables
> > before the sequence is removed
>
> I just tried it.
>
> I created a sequence using SERIAL when I created a table. I used the
> same sequence for another table by setting a column default to
> nextval(sequence).
>
> I deleted the first table. The sequence was deleted too, leaving the
> default of the second table referring to a non-existent sequence.
This sounds like a serious bug in our behaviour, and not something we'd
like to release.
Specifically in relation to people's existing scripts, and also to
people who are doing dump/restore of specific tables (it'll kill the
sequences that other tables depend on too!)
No real issue with the nicety for newbies, but am very concerned about
the lack of a dependancy check here.
:-/
Regards and best wishes,
Justin Clift
> Could this be a TODO item in 7.4, to add a dependency check when a
> sequence is set as the default without being created at the same time?
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
@ 2002-11-21 03:44 ` Neil Conway <neilc@samurai.com>
2002-11-21 03:46 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
1 sibling, 1 reply; 19+ messages in thread
From: Neil Conway @ 2002-11-21 03:44 UTC (permalink / raw)
To: Justin Clift <justin@postgresql.org>; +Cc: Oliver Elphick <olly@lfix.co.uk>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Justin Clift <justin@postgresql.org> writes:
> This sounds like a serious bug in our behaviour, and not something
> we'd like to release.
It's not ideal, I agree, but I *definately* don't think this is
grounds for changing the release schedule.
> No real issue with the nicety for newbies, but am very concerned
> about the lack of a dependancy check here.
Well, how would you suggest we fix this? ISTM this is partially a
result of the fact that we don't produce dependancy information for
function bodies. While it might be possible to do so (in 7.4) for
certain types of functions (e.g. for functions defined in SQL,
PL/PgSQL, etc.), I can't see a general solution (e.g. for functions
defined in C).
And adding random hacks to get specific functions (e.g. nextval()) to
work does not strike me as a very good idea.
Cheers,
Neil
--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 03:44 ` Re: [GENERAL] Bug with sequence Neil Conway <neilc@samurai.com>
@ 2002-11-21 03:46 ` Justin Clift <justin@postgresql.org>
0 siblings, 0 replies; 19+ messages in thread
From: Justin Clift @ 2002-11-21 03:46 UTC (permalink / raw)
To: Neil Conway <neilc@samurai.com>; +Cc: Oliver Elphick <olly@lfix.co.uk>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Neil Conway wrote:
>
> Justin Clift <justin@postgresql.org> writes:
> > This sounds like a serious bug in our behaviour, and not something
> > we'd like to release.
>
> It's not ideal, I agree, but I *definately* don't think this is
> grounds for changing the release schedule.
Hey, I'm no fan of slowing the release schedule either.
Bug this is definitely sounding like a bug.
> > No real issue with the nicety for newbies, but am very concerned
> > about the lack of a dependancy check here.
>
> Well, how would you suggest we fix this? ISTM this is partially a
> result of the fact that we don't produce dependancy information for
> function bodies. While it might be possible to do so (in 7.4) for
> certain types of functions (e.g. for functions defined in SQL,
> PL/PgSQL, etc.), I can't see a general solution (e.g. for functions
> defined in C).
Absolutely *no* idea.
> And adding random hacks to get specific functions (e.g. nextval()) to
> work does not strike me as a very good idea.
Agreed. Random hacks aren't always a good approach.
Regards and best wishes,
Justin Clift
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
@ 2002-11-21 04:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 05:03 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
1 sibling, 2 replies; 19+ messages in thread
From: Tom Lane @ 2002-11-21 04:11 UTC (permalink / raw)
To: Justin Clift <justin@postgresql.org>; +Cc: Oliver Elphick <olly@lfix.co.uk>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Justin Clift <justin@postgresql.org> writes:
> Oliver Elphick wrote:
>> I created a sequence using SERIAL when I created a table. I used the
>> same sequence for another table by setting a column default to
>> nextval(sequence).
>>
>> I deleted the first table. The sequence was deleted too, leaving the
>> default of the second table referring to a non-existent sequence.
> This sounds like a serious bug in our behaviour, and not something we'd
> like to release.
We will be releasing it whether we like it or not, because
nextval('foo') doesn't expose any visible dependency on sequence foo.
(If you think it should, how about nextval('fo' || 'o')? If you think
that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
The long-term answer is to do what Rod alluded to: support the
Oracle-style syntax foo.nextval, so that the sequence reference is
honestly part of the parsetree and not buried inside a string
expression.
In the meantime, I consider that Oliver was misusing the SERIAL
feature. If you want multiple tables fed by the same sequence object,
you should create the sequence as a separate object and then create
the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
he did amounts to sticking his fingers under the hood of the SERIAL
implementation; if he gets his fingers burnt, it's his problem.
> Specifically in relation to people's existing scripts, and also to
> people who are doing dump/restore of specific tables (it'll kill the
> sequences that other tables depend on too!)
7.3 breaks no existing schemas, because older schemas will be dumped
as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
commands.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-21 05:03 ` Justin Clift <justin@postgresql.org>
1 sibling, 0 replies; 19+ messages in thread
From: Justin Clift @ 2002-11-21 05:03 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Oliver Elphick <olly@lfix.co.uk>; Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Tom Lane wrote:
>
> Justin Clift <justin@postgresql.org> writes:
> > Oliver Elphick wrote:
> >> I created a sequence using SERIAL when I created a table. I used the
> >> same sequence for another table by setting a column default to
> >> nextval(sequence).
> >>
> >> I deleted the first table. The sequence was deleted too, leaving the
> >> default of the second table referring to a non-existent sequence.
>
> > This sounds like a serious bug in our behaviour, and not something we'd
> > like to release.
>
> We will be releasing it whether we like it or not, because
> nextval('foo') doesn't expose any visible dependency on sequence foo.
Awww rats.
<snip>
> 7.3 breaks no existing schemas, because older schemas will be dumped
> as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> commands.
Ok.
Thanks Tom. :)
Regards and best wishes,
Justin Clift
> regards, tom lane
--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-11-21 18:53 ` Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:30 ` Re: [HACKERS] [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 19+ messages in thread
From: Thomas O'Connell @ 2002-11-21 18:53 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org; pgsql-sql
It seems worth pointing out, too, that some SQL purists propose not
relying on product-specific methods of auto-incrementing.
I.e., it is possible to do something like:
insert into foo( col, ... )
values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
and this is easily placed in a trigger.
-tfo
In article <7017.1037851915@sss.pgh.pa.us>,
tgl@sss.pgh.pa.us (Tom Lane) wrote:
> Justin Clift <justin@postgresql.org> writes:
> > Oliver Elphick wrote:
> >> I created a sequence using SERIAL when I created a table. I used the
> >> same sequence for another table by setting a column default to
> >> nextval(sequence).
> >>
> >> I deleted the first table. The sequence was deleted too, leaving the
> >> default of the second table referring to a non-existent sequence.
>
> > This sounds like a serious bug in our behaviour, and not something we'd
> > like to release.
>
> We will be releasing it whether we like it or not, because
> nextval('foo') doesn't expose any visible dependency on sequence foo.
>
> (If you think it should, how about nextval('fo' || 'o')? If you think
> that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
>
> The long-term answer is to do what Rod alluded to: support the
> Oracle-style syntax foo.nextval, so that the sequence reference is
> honestly part of the parsetree and not buried inside a string
> expression.
>
> In the meantime, I consider that Oliver was misusing the SERIAL
> feature. If you want multiple tables fed by the same sequence object,
> you should create the sequence as a separate object and then create
> the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> he did amounts to sticking his fingers under the hood of the SERIAL
> implementation; if he gets his fingers burnt, it's his problem.
>
> > Specifically in relation to people's existing scripts, and also to
> > people who are doing dump/restore of specific tables (it'll kill the
> > sequences that other tables depend on too!)
>
> 7.3 breaks no existing schemas, because older schemas will be dumped
> as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> commands.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
@ 2002-11-21 19:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
1 sibling, 1 reply; 19+ messages in thread
From: Bruce Momjian @ 2002-11-21 19:11 UTC (permalink / raw)
To: Thomas O'Connell <tfo@monsterlabs.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql
Of course, those would be SQL purists who _don't_ understand
concurrency issues. ;-)
---------------------------------------------------------------------------
Thomas O'Connell wrote:
> It seems worth pointing out, too, that some SQL purists propose not
> relying on product-specific methods of auto-incrementing.
>
> I.e., it is possible to do something like:
>
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
>
> and this is easily placed in a trigger.
>
> -tfo
>
> In article <7017.1037851915@sss.pgh.pa.us>,
> tgl@sss.pgh.pa.us (Tom Lane) wrote:
>
> > Justin Clift <justin@postgresql.org> writes:
> > > Oliver Elphick wrote:
> > >> I created a sequence using SERIAL when I created a table. I used the
> > >> same sequence for another table by setting a column default to
> > >> nextval(sequence).
> > >>
> > >> I deleted the first table. The sequence was deleted too, leaving the
> > >> default of the second table referring to a non-existent sequence.
> >
> > > This sounds like a serious bug in our behaviour, and not something we'd
> > > like to release.
> >
> > We will be releasing it whether we like it or not, because
> > nextval('foo') doesn't expose any visible dependency on sequence foo.
> >
> > (If you think it should, how about nextval('fo' || 'o')? If you think
> > that's improbable, consider nextval('table' || '_' || 'col' || '_seq').)
> >
> > The long-term answer is to do what Rod alluded to: support the
> > Oracle-style syntax foo.nextval, so that the sequence reference is
> > honestly part of the parsetree and not buried inside a string
> > expression.
> >
> > In the meantime, I consider that Oliver was misusing the SERIAL
> > feature. If you want multiple tables fed by the same sequence object,
> > you should create the sequence as a separate object and then create
> > the tables using explicit "DEFAULT nextval('foo')" clauses. Doing what
> > he did amounts to sticking his fingers under the hood of the SERIAL
> > implementation; if he gets his fingers burnt, it's his problem.
> >
> > > Specifically in relation to people's existing scripts, and also to
> > > people who are doing dump/restore of specific tables (it'll kill the
> > > sequences that other tables depend on too!)
> >
> > 7.3 breaks no existing schemas, because older schemas will be dumped
> > as separate CREATE SEQUENCE and CREATE TABLE ... DEFAULT nextval()
> > commands.
> >
> > regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-11-21 19:22 ` Rod Taylor <rbt@rbt.ca>
2002-11-21 20:09 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 19+ messages in thread
From: Rod Taylor @ 2002-11-21 19:22 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Thomas O'Connell <tfo@monsterlabs.com>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-sql
On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> Of course, those would be SQL purists who _don't_ understand
> concurrency issues. ;-)
Or they're the kind that locks the entire table for any given insert.
--
Rod Taylor <rbt@rbt.ca>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
@ 2002-11-21 20:09 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-21 20:23 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 19+ messages in thread
From: scott.marlowe @ 2002-11-21 20:09 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Thomas O'Connell <tfo@monsterlabs.com>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-sql
On 21 Nov 2002, Rod Taylor wrote:
> On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> > Of course, those would be SQL purists who _don't_ understand
> > concurrency issues. ;-)
>
> Or they're the kind that locks the entire table for any given insert.
Isn't that what Bruce just said? ;^)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
2002-11-21 20:09 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-21 20:23 ` Rod Taylor <rbt@rbt.ca>
2002-11-21 21:52 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
0 siblings, 1 reply; 19+ messages in thread
From: Rod Taylor @ 2002-11-21 20:23 UTC (permalink / raw)
To: scott.marlowe <scott.marlowe@ihs.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Thomas O'Connell <tfo@monsterlabs.com>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-sql
On Thu, 2002-11-21 at 15:09, scott.marlowe wrote:
> On 21 Nov 2002, Rod Taylor wrote:
>
> > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> > > Of course, those would be SQL purists who _don't_ understand
> > > concurrency issues. ;-)
> >
> > Or they're the kind that locks the entire table for any given insert.
>
> Isn't that what Bruce just said? ;^)
I suppose so. I took what Bruce said to be that multiple users could
get the same ID.
I keep having developers want to make their own table for a sequence,
then use id = id + 1 -- so they hold a lock on it for the duration of
the transaction.
--
Rod Taylor <rbt@rbt.ca>
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
2002-11-21 20:09 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
2002-11-21 20:23 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
@ 2002-11-21 21:52 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-26 15:30 ` Re: [GENERAL] Bug with sequence Peter Gulutzan <pgulutzan@ocelot.ca>
0 siblings, 1 reply; 19+ messages in thread
From: scott.marlowe @ 2002-11-21 21:52 UTC (permalink / raw)
To: Rod Taylor <rbt@rbt.ca>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Thomas O'Connell <tfo@monsterlabs.com>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-sql
On 21 Nov 2002, Rod Taylor wrote:
> On Thu, 2002-11-21 at 15:09, scott.marlowe wrote:
> > On 21 Nov 2002, Rod Taylor wrote:
> >
> > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> > > > Of course, those would be SQL purists who _don't_ understand
> > > > concurrency issues. ;-)
> > >
> > > Or they're the kind that locks the entire table for any given insert.
> >
> > Isn't that what Bruce just said? ;^)
>
> I suppose so. I took what Bruce said to be that multiple users could
> get the same ID.
>
> I keep having developers want to make their own table for a sequence,
> then use id = id + 1 -- so they hold a lock on it for the duration of
> the transaction.
I was just funnin' with ya, but the point behind it was that either way
(with or without a lock) that using something other than a sequence is
probably a bad idea. Either way, under parallel load, you have data
consistency issues, or you have poor performance issues.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Re: [HACKERS] [GENERAL] Bug with sequence Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
2002-11-21 20:09 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
2002-11-21 20:23 ` Re: [HACKERS] [GENERAL] Bug with sequence Rod Taylor <rbt@rbt.ca>
2002-11-21 21:52 ` Re: [HACKERS] [GENERAL] Bug with sequence scott.marlowe <scott.marlowe@ihs.com>
@ 2002-11-26 15:30 ` Peter Gulutzan <pgulutzan@ocelot.ca>
0 siblings, 0 replies; 19+ messages in thread
From: pgulutzan@ocelot.ca @ 2002-11-26 15:30 UTC (permalink / raw)
To: pgsql-hackers@postgresql.org
scott.marlowe@ihs.com ("scott.marlowe") wrote in message news:<Pine.LNX.4.33.0211211450100.23804-100000@css120.ihs.com>...
> On 21 Nov 2002, Rod Taylor wrote:
>
> > On Thu, 2002-11-21 at 15:09, scott.marlowe wrote:
> > > On 21 Nov 2002, Rod Taylor wrote:
> > >
> > > > On Thu, 2002-11-21 at 14:11, Bruce Momjian wrote:
> > > > > Of course, those would be SQL purists who _don't_ understand
> > > > > concurrency issues. ;-)
> > > >
> > > > Or they're the kind that locks the entire table for any given insert.
> > >
> > > Isn't that what Bruce just said? ;^)
> >
> > I suppose so. I took what Bruce said to be that multiple users could
> > get the same ID.
> >
> > I keep having developers want to make their own table for a sequence,
> > then use id = id + 1 -- so they hold a lock on it for the duration of
> > the transaction.
>
> I was just funnin' with ya, but the point behind it was that either way
> (with or without a lock) that using something other than a sequence is
> probably a bad idea. Either way, under parallel load, you have data
> consistency issues, or you have poor performance issues.
>
>
I'm not familiar with these "SQL purists" (perhaps the reference is to
J. Celko?) but the fact is that it's hard to call SEQUENCE
product-specific now that it's in Oracle, DB2, and SQL:2003. The
syntaxes do differ a little, usually due to choice of abbreviation,
but as far as I can tell the internals are similar across
implementations.
Peter Gulutzan
Author of "Sequences And Identity Columns"
(http://dbazine.com/gulutzan4.html)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [HACKERS] [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-21 02:40 ` Re: [GENERAL] Bug with sequence Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Re: [GENERAL] Bug with sequence Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 18:53 ` Re: [GENERAL] Bug with sequence Thomas O'Connell <tfo@monsterlabs.com>
@ 2002-11-21 19:30 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 19+ messages in thread
From: Tom Lane @ 2002-11-21 19:30 UTC (permalink / raw)
To: Thomas O'Connell <tfo@monsterlabs.com>; +Cc: pgsql-hackers@postgresql.org; pgsql-sql
"Thomas O'Connell" <tfo@monsterlabs.com> writes:
> It seems worth pointing out, too, that some SQL purists propose not
> relying on product-specific methods of auto-incrementing.
> I.e., it is possible to do something like:
> insert into foo( col, ... )
> values( coalesce( ( select max( col ) from foo ), 0 ) + 1, ... );
> and this is easily placed in a trigger.
... but that approach is entirely unworkable if you want any concurrency
of insert operations. (Triggers are a tad product-specific, too :-()
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: [GENERAL] Bug with sequence
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-20 08:53 ` Re: Bug with sequence Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Re: [GENERAL] Bug with sequence Oliver Elphick <olly@lfix.co.uk>
@ 2002-11-21 19:14 ` Bruce Momjian <pgman@candle.pha.pa.us>
2 siblings, 0 replies; 19+ messages in thread
From: Bruce Momjian @ 2002-11-21 19:14 UTC (permalink / raw)
To: Oliver Elphick <olly@lfix.co.uk>; +Cc: Robert Treat <xzilla@users.sourceforge.net>; pgsql-hackers@postgresql.org
Oliver Elphick wrote:
> I deleted the first table. The sequence was deleted too, leaving the
> default of the second table referring to a non-existent sequence.
>
>
> Could this be a TODO item in 7.4, to add a dependency check when a
> sequence is set as the default without being created at the same time?
Added to TODO:
* Have sequence dependency track use of DEFAULT sequences, seqname.nextval
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2002-11-26 15:30 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-18 15:45 Bug with sequence Thomas Aichinger <taich@gmx.at>
2002-11-19 01:22 ` Lee Harr <missive@frontiernet.net>
2002-11-20 08:53 ` Oliver Elphick <olly@lfix.co.uk>
2002-11-20 22:12 ` Oliver Elphick <olly@lfix.co.uk>
2002-11-20 23:20 ` Rod Taylor <rbt@rbt.ca>
2002-11-21 02:40 ` Justin Clift <justin@postgresql.org>
2002-11-21 03:44 ` Neil Conway <neilc@samurai.com>
2002-11-21 03:46 ` Justin Clift <justin@postgresql.org>
2002-11-21 04:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 05:03 ` Justin Clift <justin@postgresql.org>
2002-11-21 18:53 ` Thomas O'Connell <tfo@monsterlabs.com>
2002-11-21 19:11 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-11-21 19:22 ` Rod Taylor <rbt@rbt.ca>
2002-11-21 20:09 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-21 20:23 ` Rod Taylor <rbt@rbt.ca>
2002-11-21 21:52 ` scott.marlowe <scott.marlowe@ihs.com>
2002-11-26 15:30 ` Peter Gulutzan <pgulutzan@ocelot.ca>
2002-11-21 19:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-11-21 19:14 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox