search

Snowflake-Labs / django-snowflake

MIT License
61 stars 16 forks source link

Support for Snowflake Hybrid Tables #97

Open davepaulsanders opened 5 months ago

davepaulsanders commented 5 months ago

I see in the README under known issues:

"Snowflake doesn’t support last_insert_id to retrieve the ID of a newly created object. Instead, this backend issues the query SELECT MAX(pk_name) FROM table_name to retrieve the ID. "

I am currently struggling with this, and came across Snowflake hybrid tables. I'm wondering if this issue could be resolved by supporting Hybrid Tables creation?

https://docs.snowflake.com/user-guide/tables-hybrid

timgraham commented 4 months ago

I tried to run Django's test suite with hybrid tables. Initial discoveries:

A hybrid table limitation: "Unique and foreign-key constraints can only be defined at table creation time." This is problematic for Django's schema editor as it uses deferred SQL quite often. I don't know if we can work around this.

Secondly, as you mentioned, I understood the promise of hybrid tables is that we wouldn't have to do SELECT MAX(pk_name) FROM table_name to find the inserted primary key, however, I'm not sure what the method for getting the id with hybrid tables looks like. For most databases, it's cursor.lastrowid but this is still None in snowflake-connector-python. Searching that repo for "hybrid" gives no results, so it's unclear if any changes are in the works.