Thread: BUG #12991: RESTART IDENTITY is not doing anything

The following bug has been logged on the website:

Bug reference:      12991
Logged by:          Goutte
Email address:      antoine@goutenoir.com
PostgreSQL version: 9.3.6
Operating system:   Linux 3.13.0-45-generic x86_64 GNU/Linux
Description:

The bug is pretty straightforward, the doc is crystal clear about the role
of RESTART IDENTITY, and this is not listed is the known bugs.

For my test suite, I have to restart the identity sequences while
truncating.

I need the TRUNCATE to CASCADE too, but I don't know if it is meaningful.

Example, say Article has `id` as primary key :

TRUNCATE TABLE Article RESTART IDENTITY CASCADE;


On the next insertion, the sequence will continue from where it was before
instead of being resetted. The table is successfully truncated, though.

I solve it by doing, right after :
ALTER SEQUENCE Article_id_seq RESTART WITH 1;

This works and restarts the sequence.

---

I observe this behavior (or non-behavior in this case) on the cli using the
`psql` binary, and programmatically in PHP, so I'm sure this is not my
connector acting up.

---

Please note that I'm a pgSQL absolute noob (first day), and I'm using it
because of the awesome `earthdistances` extension.

Maybe I missed something obvious !

Good day to you,

Antoine

On 2015-04-07 04:07:56 +0000, antoine@goutenoir.com wrote:
> Example, say Article has `id` as primary key :

How is that table defined? Restart identity will only work if the
sequence is 'owned' by the id column. That happens if you either create
it by specifying 'serial' as the column type, or if you explicitly use
ALTER SEQUENCE ... OWNED BY table.col;

Greetings,

Andres Freund

On Tue, Apr 7, 2015 at 5:02 PM, Andres Freund <andres@anarazel.de> wrote:

> On 2015-04-07 04:07:56 +0000, antoine@goutenoir.com wrote:
> > Example, say Article has `id` as primary key :
>
> How is that table defined? Restart identity will only work if the
> sequence is 'owned' by the id column. That happens if you either create
> it by specifying 'serial' as the column type, or if you explicitly use
> ALTER SEQUENCE ... OWNED BY table.col;
>

Oh, I created (actually, Doctrine created) those with :

CREATE TABLE Article (id INT NOT NULL, name VARCHAR(16) NOT NULL, PRIMARY
KEY(id));
CREATE SEQUENCE Article_id_seq INCREMENT BY 1 MINVALUE 1 START 1;

Instead of :

CREATE SEQUENCE Article_id_seq INCREMENT BY 1 MINVALUE 1 START 1 OWNED BY
Article.id;

---

I did not knew about "ownership" of sequences, because the id incremented
so I thought the system knew what field to alter through the sequence name.

Thanks for the tip, I'll fork Doctrine to add it if there's no BC break
ripple effect resulting from the change.

Sorry about the bother,

Antoine

On Tue, Apr 7, 2015 at 12:06 PM, Antoine Goutenoir <antoine@goutenoir.com>
wrote:

> On Tue, Apr 7, 2015 at 5:02 PM, Andres Freund <andres@anarazel.de> wrote:
>
>> On 2015-04-07 04:07:56 +0000, antoine@goutenoir.com wrote:
>> > Example, say Article has `id` as primary key :
>>
>> How is that table defined? Restart identity will only work if the
>> sequence is 'owned' by the id column. That happens if you either create
>> it by specifying 'serial' as the column type, or if you explicitly use
>> ALTER SEQUENCE ... OWNED BY table.col;
>>
>
> Oh, I created (actually, Doctrine created) those with :
>
> CREATE TABLE Article (id INT NOT NULL, name VARCHAR(16) NOT NULL, PRIMARY
> KEY(id));
> CREATE SEQUENCE Article_id_seq INCREMENT BY 1 MINVALUE 1 START 1;
>
> Instead of :
>
> CREATE SEQUENCE Article_id_seq INCREMENT BY 1 MINVALUE 1 START 1 OWNED BY
> Article.id;
>
>
=E2=80=8BYou seem to be missing something here because the reported Doctrin=
e code
does nothing to change the Article.id field to use the sequence.
Typically, you would do:

CREATE TABLE Article (id serial)

And the resultant column definition would include:

[...] id integer DEFAULT =E2=80=8B

=E2=80=8Bnextval('sequence_name_here')=E2=80=8B [...]

=E2=80=8BThat particular code doesn't directly impact "Ownership"=E2=80=8B =
though, which is
strictly (I think) the mechanism by which RESTART works.

David J.