Re: serial columns & loads misfeature?

Trying for 2 at once...

Tom Lane wrote:
> Kevin Brannen <kevinb(at)nurseamerica(dot)net> writes:
>
>>I was expecting the system to realize new "keys" had been inserted, and
>>so when the "nextval" that implicitly happens on a serial field is run,
>>it would "know" that it was too small and return "max(s)+1".
>
>
> It does not, and I'm not convinced that it should. Manual insertion
> of values into an autonumbering column seems like, well, a manual
> operation. If you're going to bypass the autonumbering then you should
> also be responsible for setting the sequence counter to whatever you
> want it to be afterwards.
>

Neil Conway wrote:
> On Fri, Jun 28, 2002 at 02:50:46PM -0400, Gregory Seidman wrote:
>
>>Kevin Brannen sez:
...
>>} and things will be fine from here after, but surely this is a common
>>} enough problem after a bulk load that there is something already built
>>} in to handle this and I just don't have it configured correctly (or is
>>} this a bug?).
>>
>>It's a known problem.
>
>
> IMHO, it's not a problem. If you abuse sequences (i.e. INSERT into a
> SERIAL column a value that is not generated by its sequence), you're
> going to run into trouble -- so the easy solution is, "don't do that".

I picked these 2 comments just because there were expressing the "other
viewpoint". I think that when all is said and done, there is no correct
answer to whether the SERIAL column (or actually the the underlying
sequence) should or should not pay attention to other inserted values.
Some people seem expect it one way, because we came from other products
that do that, while others don't expect that, because they think it to
be incorrect or for some other reason.

I suppose I could change the question to: Why can't we have it both
ways? You know, have that ability controlled by a param in the
postgresql.conf file; it could even be defaulted to "off". I really
think this could be important, especially if the ultimate goal is world
domination. ;-) After all, bulk loads and transformations are a fact of
life that must be dealt with (so "don't do that" is not an option unless
I don't use Pg, else I'll never migrate away from mysql).

And I really appreciate all the advice and help given to help me
understand the Pg way on this topic, and the magic statement(s) to fix
it! A Perl program to reset all 35 sequences should be simple.

Thanks!
Kevin