Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 UTC on 18 March 2024, to get $100 off your ticket.
Problem/Motivation
#2183231: Make ContentEntityDatabaseStorage generate static database schemas for content entities changed the schema of file_managed.uri
to have a prefixed unique key, i.e. a unique key with a length.
This is problematic as
- this is currently not supported on PostreSQL: #2278493: Drop support for unique keys specified as an array of column name and length (breaks PG installation)
- files that start with the same characters but are different in the end can no longer be inserted into the database
Proposed resolution
Swap the prefixed unique key for a proper unique key.
Remaining tasks
User interface changes
API changes
Comment | File | Size | Author |
---|---|---|---|
#1 | postresql-schema.patch | 1.32 KB | tstoeckler |
Comments
Comment #1
tstoecklerHere's the patch from #2183231-424: Make ContentEntityDatabaseStorage generate static database schemas for content entities.
Also assigned the issue to @plach as he knows why we did this in the first place.
Comment #2
sunThe unique key can't be the only validation. Don't we have a uniqueness validation for the URI in PHP code, too?
If we do, why don't we simply change the unique index into a regular index?
FWIW, Postgres's limitation is quite reasonable. Limiting the length of a unique key is a bit illogical... But I guess that rather belongs into #2278493: Drop support for unique keys specified as an array of column name and length (breaks PG installation)
Comment #3
tstoecklerSo I didn't actually try it, but I'm pretty sure that it's not being checked in PHP. At least I couldn't find anything remotely related.
We should really have a
Unique
constraint which we can just slap on various field definitions.Comment #4
mradcliffeManually tested patch on postgresql 9.3.4. No unique index issues encountered during installation though the installation did not complete due to another issue, which I will will hunt down and file/find.
Ninja edit: Looks like shortcut's setting a serial column to 0.
Comment #5
mradcliffeWelp. I think the static database schemas mega patch causes all database storage entities to try to set their initial id as 0 (#2183231: Make ContentEntityDatabaseStorage generate static database schemas for content entities).
Comment #6
mradcliffeSorry for the comment spam. I did not realize that #2279395: The PostgreSQL backend does not handle NULL values for serial fields gracefully covered that already.
Comment #7
stefan.r CreditAttribution: stefan.r commented#2278493: Drop support for unique keys specified as an array of column name and length (breaks PG installation) is blocked by this issue
Comment #8
plachThe change makes sense to me: the current field schema definition allows for a max size of 2048 characters, but previously the
uri
column had a max size of 255, so this should not cause problems during a migration from D7.If we want to address the unique constraint here then this is NW otherwise RTBC.
Edit: if we want to allow for bigger URIs then also @sun's suggestion of turning the unique constraint into a regular index makes perfect sense to me.
Comment #9
tstoecklerOK, thanks. Marking RTBC then, as this breaks PGSQL which we still do support ATM.
Making the URI non-unique would be a change in behavior - or otherwise the uniqueness would have to be validated in PHP, thus would rely on such a UniqueConstraint. Let's explore that in a follow-up.
Comment #10
alexpottCommitted 6140e65 and pushed to 8.x. Thanks!