AutoFields, legacy databases and non-standard sequence names.

[Tom Eastman]

Hey guys,

I'm using Django to create an interface for a legacy PostgresQL
database. The primary keys for my tables use sequences that aren't
named the way django expects them to be (i.e. '<table>_<column>_seq'),
this means I can't call them AutoFields.

Essentially, my problem is identical to that described by

> http://stackoverflow.com/questions/2516176/django-postgres-how-to-specify-sequence-for-a-field

and

> http://code.djangoproject.com/ticket/1946

There appear to be three or so half-finished proposed solutions to this
issue. I definitely need to find a way to make this work, and I would
greatly appreciate any guidance on what might be the best way to solve
this problem. If we can find a way to solve it for *everyone* that
would be even better.

Any thoughts or suggestions?

Cheers,

Tom

[Russell Keith-Magee]

Yes - questions of usage and requests for design guidance should be
directed to Django-users.

Unless you have a specific suggestion for how to move ticket #1946
forward, this isn't a topic for django-developers.

Yours,
Russ Magee %-)

[Tom Eastman]

On 06/10/10 16:02, Russell Keith-Magee wrote:
> Yes - questions of usage and requests for design guidance should be
> directed to Django-users.
>
> Unless you have a specific suggestion for how to move ticket #1946
> forward, this isn't a topic for django-developers.

Sorry, I should have been more clear.

What I'm trying to do is solicit suggestions from django developers as
to how I *can* move ticket #1946 forward. I can find a way to work
around it in my own project, but it would be ideal to solve it on the
Django side, for everybody.

I mentioned there were three possible suggestions in the ticket
discussion as to how to solve the problem. If a Django developer can
give me some guidance as to what approach seems to be the best long-term
solution, I'm happy to try my hand at writing a patch that can hopefully
be incorporated into the codebase.

Cheers,

Tom

[Hanne Moa]

On 6 October 2010 04:02, Tom Eastman <tom.e...@otago.ac.nz> wrote:
> I'm using Django to create an interface for a legacy PostgresQL database.
>  The primary keys for my tables use sequences that aren't named the way
> django expects them to be (i.e. '<table>_<column>_seq'), this means I can't
> call them AutoFields.
>

>> http://code.djangoproject.com/ticket/1946

I still use my custom backend that recursively looks up a
sequence-name, which means I have to be extra-careful on every new
django-version since I need to upgrade that backend myself. With 1.2
at least I don't need to mess with django's own directories anymore.

HM

[akaariai]

> Sorry, I should have been more clear.
>
> What I'm trying to do is solicit suggestions from django developers as
> to how I *can* move ticket #1946 forward. I can find a way to work
> around it in my own project, but it would be ideal to solve it on the
> Django side, for everybody.
>
> I mentioned there were three possible suggestions in the ticket
> discussion as to how to solve the problem.  If a Django developer can
> give me some guidance as to what approach seems to be the best long-term
> solution, I'm happy to try my hand at writing a patch that can hopefully
> be incorporated into the codebase.

Django doesn't expect the sequence name to be tablename_columname_seq,
at least not in trunk. The last_insert_id method in backends/
postgresql/operations.py uses select
currval(pg_get_serial_sequence(tablename, columname)).
pg_get_serial_sequence will return the correct sequence only if the
sequence is owned by the tablename, columname combination. If you
happen to have just one table per sequence, then issuing
ALTER SEQUENCE sequencename OWNED BY tablename.columname;
should fix the problem.

There is one more proposal in ticket #13295. The proposed solution
should allow using the same sequence for multiple tables, though
management of the manually defined sequence is a bit hard when trying
to build the schema and when resetting the sequence. Sorry if the
proposal is a bit hard to follow...

I don't know well enough how databases other than postgresql work, so
I don't know if the solution is valid for other databases.

- Anssi

[Hanne Moa]

On 7 October 2010 19:14, akaariai <akaa...@cc.hut.fi> wrote:
> Django doesn't expect the sequence name to be tablename_columname_seq,
> at least not in trunk. The last_insert_id method in backends/
> postgresql/operations.py uses select
> currval(pg_get_serial_sequence(tablename, columname)).
> pg_get_serial_sequence will return the correct sequence only if the
> sequence is owned by the tablename, columname combination. If you
> happen to have just one table per sequence, then issuing
> ALTER SEQUENCE sequencename OWNED BY tablename.columname;
> should fix the problem.

You can't necessarily do this with a legacy database, as other systems
also using that database expect the existing names.

I need to use my own backend because of posgresql's own
table-inheritance. Most tables in the db inherit from the same table
and inherits its primary key and the sequence for that primary key.
Then there are a few tables that inherits from a table that inherits
from the grandfather table that defines the primary key and its
sequence. So, I need to recursively discover the oldest ancestor of
each table and use the sequence of that ancestor.

HM

[akaariai]

On 8 loka, 10:41, Hanne Moa <hanne....@gmail.com> wrote:

> You can't necessarily do this with a legacy database, as other systems
> also using that database expect the existing names.

alter sequence owned by does not change the sequnce name, just what
pg_get_serial_sequence will return for given table, column
combination. But as said, if there are multiple tables using the same
sequence, then alter sequence owned by does not work. In these cases
manually settable sequence names for models is likely the best
solution.