Postgres how to autoincrement sequence number for new records, partitioned by column?

[Duke Dougal]

I'm using POstgres 9.5, Python 3 and SqlAlchemy 1.0.14

When a new Issue is created, I want it to have sequence_number equal to the largest existing sequence number + 1

Each thread_id has its own sequence starting at 1.

However when I create a new record I get:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) aggregate function calls cannot contain window function calls



    class Issues(db.Base):
   
        __tablename__ = 'issues'
   
        id = Column(String, primary_key=True)
        thread_id = Column(String, nullable=False)
        sequence_number = Column(Integer, default=select([func.coalesce(func.max(func.row_number().over(partition_by=thread_id)) + 1,1)]))

I've spent all day muttering spells but cannot find the correct incantation. Can anyone suggest what I can do to get this going please?

[Mike Bayer]

why not use "SELECT MAX(sequence_number) + 1 FROM table WHERE
thread_id=:thread_id" ?

Note that this approach is not safe against concurrent inserts from
other threads or processes, you can easily have duplicate
sequence_numbers on a thread_id.








>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Duke Dougal]

I'm sorry sqlalchemy hasn't fully clicked for me yet. I'm still stuck on this.

A I meant to be translating the sql that Michael has suggested into an ORM query?

This is as far as I have got which results in :

NameError: name 'Issues' is not defined

Even then I still don't see how to get the "where" clause into the select.

from sqlalchemy import Column, String, Integer, DateTime, Boolean
from db_global import db
from sqlalchemy.sql import func, select

class Issues(db.Base):

    __tablename__ = 'issues'

    id = Column(String, primary_key=True)
    thread_id                   = Column(String, nullable=False)

    sequence_number             = Column(Integer, default=select([func.coalesce(func.max(Issues.sequence_number) + 1, 1)]))

 

[Mike Bayer]

On 09/06/2016 04:31 PM, Duke Dougal wrote:
> I'm sorry sqlalchemy hasn't fully clicked for me yet. I'm still stuck on
> this.
>
> A I meant to be translating the sql that Michael has suggested into an
> ORM query?

We're going to use the form illustrated at
http://docs.sqlalchemy.org/en/latest/core/defaults.html#context-sensitive-default-functions,
except we'll invoke a SQL statement. The example there is a little
scant and does not illustrate other common members of the "context",
that should be fixed.

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


def new_sequence(context):
return context.connection.scalar(
select([func.coalesce(func.max(Issues.sequence_number), 0) + 1]).
where(Issues.thread_id == context.current_parameters['thread_id'])
)


class Issues(Base):

__tablename__ = 'issues'

id = Column(String, primary_key=True)

thread_id = Column(Integer, nullable=False)
sequence_number = Column(Integer, unique=True, default=new_sequence)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.create_all(e)

s = Session(e)
s.add(Issues(id='issue1', thread_id=1))
s.commit()

s.add(Issues(id='issue2', thread_id=1))
s.commit()

print(s.query(Issues.thread_id, Issues.sequence_number).all())

>
> This is as far as I have got which results in :
>
> NameError: name 'Issues' is not defined
>
> Even then I still don't see how to get the "where" clause into the select.
>
>
>
> from sqlalchemy import Column, String, Integer, DateTime, Boolean
> from db_global import db
> from sqlalchemy.sql import func, select
>
> class Issues(db.Base):
>
> __tablename__ = 'issues'
>
> id = Column(String, primary_key=True)
> thread_id = Column(String, nullable=False)
> sequence_number = Column(Integer,
> default=select([func.coalesce(func.max(Issues.sequence_number) + 1, 1)]))
>
>
>