Thread: Renaming sequences

Are there any consequences for renaming sequences to match the tables which own them?

In an existing production Django project I have just converted auth.user into common.user and company.userprofile into common.userprofile.

Having gone through the migration process more or less unscathed the original sequences are owned by the renamed tables. Eg public.auth_user_id_seq is owned by public.common_user.id

Everything seems to work fine but my unit tests are playing up and error messages are showing the original (and still correct) sequence names. It would make much visual sense to me now and especially to the future me (or anyone else) if the sequences were renamed as well.

I know how I could do it but I just need to know if I should.

Thanks for any advice

Mike

If you are referring to a sequence which meets the same definition below. That is to say a persistent number generator. 

Surely by  changing  the sequence name then any  code using that sequence by name will fail. That's from an application developer's view.

Yes.

I think I'd better ask on the Django users list.

The fact that it is working with mismatched names probably means it would stop working if I adjusted them. Django probably reads the migration record to establish which sequence to use.

Thanks

A sequence inPostgreSQL is a user-defined schema-bound object that generates asequence of integers based on a specified specification. To create a sequence in PostgreSQL, you use the CREATE SEQUENCE statement.

After a sequence is created, you use the 

functions nextval, currval, and setval to operate on the sequence. These functions are documented in Section 9.16.

Although you cannot update a sequence directly, you can use a query like:

SELECT * FROM name;

On Wed, 18 Dec 2019, 05:33 Mike Dewhirst, <miked@dewhirst.com.au> wrote:

Are there any consequences for renaming sequences to match the tables which own them?

In an existing production Django project I have just converted auth.user into common.user and company.userprofile into common.userprofile.

Having gone through the migration process more or less unscathed the original sequences are owned by the renamed tables. Eg public.auth_user_id_seq is owned by public.common_user.id

Everything seems to work fine but my unit tests are playing up and error messages are showing the original (and still correct) sequence names. It would make much visual sense to me now and especially to the future me (or anyone else) if the sequences were renamed as well.

I know how I could do it but I just need to know if I should.

Thanks for any advice

Mike

I just had a quick look at django. It is not something I know of or use.

Just a front end framework using python language as far as I can see.

Depends on your perspective.

From the database it is a front end.  It has a very clever ORM which interfaces with all the popular DBMSs.

If you're a web application developer it is a back end where the ORM (optionally) hides the database.

I like it.

M

On 2019-Dec-18, Mike Dewhirst wrote:

> On 18/12/2019 5:03 pm, Zahid Rahman wrote:
> > 
> > If you are referring to a sequence which meets the same definition
> > below. That is to say a persistent number generator.
> > 
> > Surely by  changing  the sequence name then any  code using that
> > sequence by name will fail. That's from an application developer's view.
> 
> Yes.
> 
> I think I'd better ask on the Django users list.
> 
> The fact that it is working with mismatched names probably means it would
> stop working if I adjusted them. Django probably reads the migration record
> to establish which sequence to use.

I don't know django, but it's certainly possible that it's obtaining the
sequence name from the Postgres catalogs, not from its own migration
record; there's introspection facilities in Postgres for that.  I would
be surprised if it breaks just because you rename a sequence whose name
does not appear directly in its database definition.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-Dec-18, Mike Dewhirst wrote:

> That sounds promising. I would really like the sequence name aligned
> with the table name. I was thinking of simply editing a dump file and
> reloading to achieve it.Is that a rational approach?

I don't find it so, but it seems subjective ... YMMV.

I would just copy the database (CREATE DATABASE WITH TEMPLATE) to a test
throwaway one, do the ALTER SEQUENCE there, point the application to it,
and see if it works.  If it does, drop that database and repeat the
ALTER SEQUENCE in your original database.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

as the saying goes "can not see it from house", or "when it breaks you keep both pieces".    (hope my humor comes across)

My wife also thought that was funny :)

ORM  has to know the name of the sequence or it can not pre-fetch the sequence, or modify its values

i went and looked  to double check  ORM does control the naming  see

https://github.com/django/django/blob/master/django/db/backends/postgresql/schema.py

lines 10 -13 and line 96,

Yes. In the end I made the changes with scripted psql so the sequences match what the ORM is looking for. Postgres seems less critical because it appears to keep track of which sequence is used by which key field when psql does the adjustment.

Postgresql auto naming  for indexes and sequences is tabel_column_type(idx,seq..) the ORM follows a similar logic

changing names outside of the ORM will bugger the app

In this case I was trying to game the ORM because the django migration system cannot relocate django_auth_user to myapp_common_user while retaining existing content. This isn't an ORM problem but rather an app design and auto-migration problem. I began the project using Django's contributed auth app with its user model. This pre-dated Django's current auth system with a swappable user table. New requirements forced conversion of the user table so I can tweak a small portion of the auth functionality.

Retro-fitting a swapped user table is v.difficult using the Django migrations system but dead easy for a new project. Fortunately there are plenty of giants out there to help midgets like me.

Thank you for your help

Everything is working and I'm happy

Cheers

Mike