Hey everyone, any ideas if I can avoid the no defa...

# exposed

[Stelios Papamichail]

Hey everyone, any ideas if I can avoid the no default value errors for a custom column type I've got since that column's value is set using a PSQL trigger later on? Making it nullable seems to set its initial value to null and then the trigger does not change it. Is there any documentation that may help me find out what i'm missing?

i've checked that the triggers & their respective functions exist, are defined as they should be and that the user has permissions to run them, however they don't seem to run when queries are sent through the ktor/exposed backend.

[Stelios Papamichail]

After further analysis, i've set the trigger to run AFTER inserts and updates but the issue persists. If i understand correctly, that's because either way, in the transaction block below, the insert statement for the phd is executed first, then the trigger fires while the other relationship operations are still running and thus leads to null value. Is there any way around this?

override suspend fun addDissertation(phdDissertationDTO: PhdDissertationDTO): PhdDissertationDTO =
    suspendTransaction {
        try {
            val phdDissertation =
                PhdDissertationEntity.new {
                    title = phdDissertationDTO.title
                    abstractGR = phdDissertationDTO.abstractGR
                    abstractEN = phdDissertationDTO.abstractEN
                    date = phdDissertationDTO.date
                    url = phdDissertationDTO.url
                    language = phdDissertationDTO.language
                    views = phdDissertationDTO.views.toLongOrNull() ?: 0
                    reads = phdDissertationDTO.reads.toLongOrNull() ?: 0
                    updatedAt = Clock.System.now()
                    createdAt = Clock.System.now()
                }

            // Add tags
            phdDissertation.tags = SizedCollection(phdDissertationDTO.tags.map { tag ->
                PhdTagEntity.find { PhdTagsTable.title eq tag.lowercase() }.firstOrNull() ?: PhdTagEntity.new {
                    title = tag.lowercase()
                    updatedAt = Clock.System.now()
                    createdAt = Clock.System.now()
                }
            })

            // Add authors
            phdDissertation.authors = SizedCollection(phdDissertationDTO.authors.map { author ->
                PhdAuthorEntity.find { PhdAuthorsTable.fullName eq author.lowercase() }.firstOrNull() ?: PhdAuthorEntity.new {
                    fullName = author.lowercase()
                    updatedAt = Clock.System.now()
                    createdAt = Clock.System.now()
                }
            })

            // Add scientific fields
            phdDissertation.scientificFields = SizedCollection(phdDissertationDTO.scientificFields.map { field ->
                PhdScientificFieldEntity.find { PhdScientificFieldsTable.field eq field.lowercase() }.firstOrNull()
                    ?: PhdScientificFieldEntity.new {
                        title = field.lowercase()
                        updatedAt = Clock.System.now()
                        createdAt = Clock.System.now()
                    }
            })

            // Add exam board members
            phdDissertation.examBoardMembers = SizedCollection(phdDissertationDTO.examBoardMembers.map { member ->
                PhdExamBoardMemberEntity.find { PhdExamBoardTable.examinerFullName eq member.lowercase() }.firstOrNull()
                    ?: PhdExamBoardMemberEntity.new {
                        fullName = member.lowercase()
                        updatedAt = Clock.System.now()
                        createdAt = Clock.System.now()
                    }
            })

            // add institutions
            phdDissertation.institutions =
                SizedCollection(phdDissertationDTO.institutions.map { institution ->
                    PhdInstitutionEntity.find { PhdInstitutionsTable.title eq institution.lowercase() }
                        .firstOrNull()
                        ?: PhdInstitutionEntity.new {
                            title = institution.lowercase()
                            updatedAt = Clock.System.now()
                            createdAt = Clock.System.now()
                        }
                })

            return@suspendTransaction phdDissertation.mapToDto()
        } catch (e: Exception) {
            throw InsertionException(e.localizedMessage)
        }
    }

The update functions work fine, I imagine that's to be expected due to the data being present.

[Chantal Loncle]

Hi @Stelios Papamichail If I'm understanding what you need correctly, have you tried defining the column in the table as databaseGenerated()?
Documentation for more details.

[Stelios Papamichail]

Hi Chantal, this looks perfect. Sadly the issue with the data being empty/null upon insert is persisting but that is almost certainly due to the way the tsvector_content data is populated (requires other table inserts first) so it makes sense. Thank you nonetheless for the new knowledge!

[Stelios Papamichail]

What i've thought off is performing a no-op update query at the end of the transaction (after all relational data has been inserted and the phd created) in order to fire the trigger again and update the tsvector_content column. However, even though i do:

PhdDissertationsTable.update({ PhdDissertationsTable.id eq phdDissertation.id }) {
    it[updatedAt] = Clock.System.now()
}

where phdDissertation is the entity returned via the insertion, the trigger does not seem to fire (but it does fire for normal updates via a diff endpoint call).