Redshift does not support sequences about sqlalchemy-redshift HOT 19 OPEN

I think I got it working by setting

RedshiftDialect.supports_sequences = False
RedshiftDialect.preexecute_autoincrement_sequences = False
**RedshiftDialect.postfetch_lastrowid = True**

I'm still debugging things, but so far there's no been any primary key related errors.

from sqlalchemy-redshift.

Hey nwbvt, thank you so much!
It kind of works.
The '(psycopg2.ProgrammingError) relation "tablename_model_id_seq" does not exist' error is solved by setting those two variables to False, but now I'm getting the following:

sqlalchemy.orm.exc.FlushError: Instance [ENTITY] has a NULL identity key. 
If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. 
Ensure also that this flush() is not occurring at an inappropriate time, such as within a load() event.

I guess there should be a

SELECT MAX([id_column])
FROM [entity_table]

immediately following the INSERT INTO [entity_table] statement.

I'm kind of new to SqlAlchemy, do you have any idea where/what should I modify in sqlalchemy_redshift to add this?

Thanks again,

Mario

from sqlalchemy-redshift.

@aboedo done

from sqlalchemy-redshift.

Any updates on this?

from sqlalchemy-redshift.

I haven't run into that. I'd be interested to see what situations produce this error.

from sqlalchemy-redshift.

Hi guys,

I'm currently trying to deal with this problem.
Anyone knows some solution/workaround for it?

Thanks!

from sqlalchemy-redshift.

Its a pretty simple fix which I was able to make on my fork, unfortunately I didn't get a chance to resolve the questions that came up from the pull request (I blame a combination of a conference, illness, and me just plain forgetting about it).

from sqlalchemy-redshift.

I think that was against an older version though, so it might not work if you are using something more recent.

from sqlalchemy-redshift.

@mtambos your solution works. could you make a PR?

from sqlalchemy-redshift.

I'm pretty sure it's impossible

from sqlalchemy-redshift.

setting the autoincrement=False in the class definition solved the issue for me.

from sqlalchemy-redshift.

it prevents the sequence auto increment behavior in sqlalchemy when you try to insert something without the id (in my case an identity field).

from sqlalchemy-redshift.

I believe the architecture of redshift means that you must use application generated IDs

from sqlalchemy-redshift.

not for identity fields:

IDENTITY(seed, step)
Clause that specifies that the column is an IDENTITY column. An IDENTITY column contains unique auto-generated values. These values start with the value specified as seed and increment by the number specified as step. The data type for an IDENTITY column must be either INT or BIGINT.

ref. http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html

from sqlalchemy-redshift.

Yeah but there's no way of getting the ID back

from sqlalchemy-redshift.

@graingert, according to this answer (and one of the comments above) you can execute INSERT and then SELECT MAX(id) in the same transaction and it is supposed to work.

from sqlalchemy-redshift.

from sqlalchemy-redshift.

Sorry to re-open this topic, but facing same problem here.

I know the proposed solution was accepted but later reverted as may seen here: b11bf01

Which solution would be acceptable so? Getting a crash like it works currently, or some workaround which makes it works at some cases?

I personally prefer the "sometimes works", but with some "warning" instead, so may be properly managed by the developer instead. Crashing with error f405 poor like managed. Personal opinion.

from sqlalchemy-redshift.

Fixing this with a better error is probably a good idea.

Eg crashing with an error and a link to a discussion on how to use application generated PKs with SQLAlchemy ORM and redshift

from sqlalchemy-redshift.