Skip to content

ignore sequence / identity order it in other dbs, only use it on oracle #10207

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
CaselIT opened this issue Aug 8, 2023 Discussed in #10203 · 10 comments
Closed

ignore sequence / identity order it in other dbs, only use it on oracle #10207

CaselIT opened this issue Aug 8, 2023 Discussed in #10203 · 10 comments
Labels
bug Something isn't working oracle postgresql schema things related to the DDL related objects like Table, Column, CreateIndex, etc.
Milestone
1.4.x

Comments

@CaselIT
Copy link
Member

CaselIT commented Aug 8, 2023 •
edited
Loading

plan is for 1.4 to just make pg ignore order and to rename in in v2, deprecating order

Discussed in #10203

Originally posted by AbdealiLoKo August 8, 2023
I believe this is a bug - but reporting it here just in case I am wrong ... as I am using Identity() for the first time.

I am trying to use Identity() for postgres and oracle in my code.
I generally support: MySQL, sqlite, mssql, oracle, and postgres in my application

Reproducible example:

from sqlalchemy import Table, Identity, Column, BigInteger, MetaData
from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects import sqlite, mysql, oracle, mssql, postgresql
meta = MetaData()
table = Table(
    'mytable',
    meta,
    Column('mycol', BigInteger(), Identity(order=True), primary_key=True),
    sqlite_autoincrement=True
)
print('sqlite', CreateTable(table).compile(dialect=sqlite.dialect()))
print('mysql', CreateTable(table).compile(dialect=mysql.dialect()))
print('oracle', CreateTable(table).compile(dialect=oracle.dialect()))
print('mssql', CreateTable(table).compile(dialect=mssql.dialect()))
print('postgresql', CreateTable(table).compile(dialect=postgresql.dialect()))

This gives me the output:

sqlite
CREATE TABLE mytable (
        mycol BIGINT NOT NULL PRIMARY KEY AUTOINCREMENT
)

mysql
CREATE TABLE mytable (
        mycol BIGINT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (mycol)
)

oracle
CREATE TABLE mytable (
        mycol NUMBER(19) GENERATED BY DEFAULT AS IDENTITY (ORDER),
        PRIMARY KEY (mycol)
)

mssql
CREATE TABLE mytable (
        mycol BIGINT NOT NULL IDENTITY,
        PRIMARY KEY (mycol)
)

postgresql
CREATE TABLE mytable (
        mycol BIGINT GENERATED BY DEFAULT AS IDENTITY (ORDER),
        PRIMARY KEY (mycol)
)

When I run the last one i postgres, it gives the following error:

SQL Error [42601]: ERROR: syntax error at or near "ORDER"
  Position: 80

I couldn't find any documentation that says ORDER is a valid concept in postgres. So, I feel like sqlalchemy is generating it wrongly.
ORDER is only valid for oracle (For RAC support)

I am using:

  • python: 3.7.16
  • sqlalchemy: 1.4.47
@CaselIT CaselIT added bug Something isn't working postgresql schema things related to the DDL related objects like Table, Column, CreateIndex, etc. oracle labels Aug 8, 2023
@CaselIT CaselIT added this to the 1.4.x milestone Aug 8, 2023
@CaselIT
Copy link
Member Author

CaselIT commented Aug 9, 2023

on_null is also oracle only, but it's currently correctly ignored by other dialects

@zzzeek
Copy link
Member

zzzeek commented Aug 9, 2023

why dont we do that for now, just dont render ORDER on other dialects

@zzzeek
Copy link
Member

zzzeek commented Aug 9, 2023

also why 1.4 ?

@CaselIT
Copy link
Member Author

CaselIT commented Aug 9, 2023

It's a bug that is also on 1.4. the fix there would just be to ignore the order

@sqla-tester
Copy link
Collaborator

Federico Caselli has proposed a fix for this issue in the main branch:

Fix rendering of order in sequences and identity columns. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4799

@sqla-tester
Copy link
Collaborator

Federico Caselli has proposed a fix for this issue in the rel_1_4 branch:

Fix rendering of order in sequences and identity columns. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4800

@CaselIT
Copy link
Member Author

CaselIT commented Aug 9, 2023

The cherry pick for 1.4 is partial. The change on v2 break alembic so I'll have to check it better

@AbdealiLoKo AbdealiLoKo mentioned this issue Aug 10, 2023
Merged
@sqla-tester
Copy link
Collaborator

Federico Caselli has proposed a fix for this issue in the rel_1_4 branch:

Fix rendering of order in sequences and identity columns. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4827

@sqla-tester
Copy link
Collaborator

Federico Caselli has proposed a fix for this issue in the main branch:

Fix rendering of order in sequences and identity columns. https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/4828

@CaselIT CaselIT mentioned this issue Aug 16, 2023
Closed
@CaselIT CaselIT changed the title Rename sequence / identity order to oracle_order, ignore it in other dbs ignore sequence / identity order it in other dbs, only use it on oracle Aug 16, 2023
@CaselIT
Copy link
Member Author

CaselIT commented Aug 16, 2023

additional changes will be done in #10247

sqlalchemy-bot pushed a commit that referenced this issue Aug 17, 2023
@CaselIT @zzzeek
Fix rendering of order in sequences and identity columns.
0cc93aa 
Fixes the rendering of the Oracle only ``order`` attribute in
Sequence and Identity that was passed also when rendering
the DDL in PostgreSQL.

Fixes: #10207
Change-Id: I5b918eab38ba68fa10a213a79e2bd0cc48401a02
(cherry picked from commit 5615ab52c81e2343330069f91ec3544840519956)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working oracle postgresql schema things related to the DDL related objects like Table, Column, CreateIndex, etc.
Projects
None yet
Milestone
1.4.x
Development

No branches or pull requests
3 participants
@zzzeek @sqla-tester @CaselIT