Sequence enhancement

[Former user]

I'm working on a fairly large project that uses PostgreSQL as the back-end. I use many sequences in many different namespaces, and I wrote a small patch that enhances the Sequence object a bit.

I added two attributes to the Sequence object: "schema" and "current".

The first one is similar to the "schema" attribute of the Table object; the second one holds the last value extracted from the sequence.

The patch fixes a bug in the quoting of sequence names during an insert.

This patch, based on the trunk, is incomplete, currently it only works with PostgreSQL: before completing it, I'd like to know whether this work may be of interest to anyone else. Thank you.

david

[Former user]

the patch against the current trunk

[Mike Bayer]

i like it a lot. however the issue in PGDefaultRunner.get_column_default() is that method is executed a lot, literally for every INSERT statement, and its too expensive to create a new Sequence object every time. thats why it uses the straight string execute. the two ways to fix this are either to attach a Sequence object to the Column under a name like "_pg_sequence" or similar, or to apply the identifier_preparer to just the strings by themselves without passing a Sequence.

so if we work that out then this could be implemented for all the sequence-supporting DBs.

[Former user]

Replying to zzzeek:

i like it a lot. however the issue in PGDefaultRunner.get_column_default() is that method is executed a lot, literally for every INSERT statement, and its too expensive to create a new snip

I'm glad :)

I rewrite my patch, and following your advices, I try another approch.

1. I add the code to reflect a Sequence from the db and add it as the default attribute of a column
2. When a primary_key column is created I add a Sequence object as the default attribute of the column

What do you think?

[Former user]

the second patch

[Mike Bayer]

partially implemented in 32dcfdf80801051971ce9638a1d9292262b375aa ; see #761 for details on a related oracle which should be fixed by this changeset.

[Mike Bayer]

I'd like to support "next" and "current" SQL expressions, probably as SQL functions, in some comprehensive way.

[Mike Bayer]

as far as this ticket, we've got schema in sequence. the current part of it definitely isn't going to scale since the state of a sequence changes independently of a single application - you need to query the DB for the currval() of a sequence in all cases.

adding currval is something we should do.

[Mike Bayer]

Removing milestone: 0.6.0 (automated comment)