Inconsistent casing in custom PostgreSQL types

[LucaCappelletti94]

At this time, when I define a custom type like:

#[cfg_attr(feature = "diesel", derive(diesel::FromSqlRow, diesel::AsExpression))]
#[cfg_attr(feature = "diesel", diesel(sql_type = crate::diesel_impls::NameplateCategory))]
/// Enum representing the tool categories.
pub enum NameplateCategory {
    Permanent,
    SemiPermanent,
}

with its SqlType being:

/// The [`NameplateCategory`] SQL type.
#[derive(diesel::query_builder::QueryId, diesel::sql_types::SqlType,)]
#[cfg_attr(
    feature = "postgres",
    diesel(postgres_type(name = "NameplateCategory"))
)]
pub struct NameplateCategory;

and the postgres SQL binding being:

CREATE TYPE NameplateCategory AS ENUM (
	'Permanent',
	'SemiPermanent'
);

When running queries including the aforementioned type I get:

"type \"NameplateCategory\" does not exist"

as the type being checked for from the pg_type table is lowercased, and equal to nameplatecategory, and therefore it requires me to change the postgres_type name to:

/// The [`NameplateCategory`] SQL type.
#[derive(diesel::query_builder::QueryId, diesel::sql_types::SqlType,)]
#[cfg_attr(
    feature = "postgres",
    diesel(postgres_type(name = "nameplatecategory"))
)]
pub struct NameplateCategory;

Should we:

1. Update the query that looks up the OID of the type from thepg_type table to lowercase the value before executing the check? I am not sure where this happens in diesel btw, but I am certain it happens as I see the message being sent via wireshark.
2. Or, constraint the postgres_type name to solely accept lowercased values and raise a compile time error when the provided value is not lowercased?

Happy to do a PR to fix either things. I would be partial to the option 1 as I like the casing consistency, but also option 2 is absolutely viable for me as I primarily want compile time checks that let me know about this issues before I try to run the code.

Luca

[weiznich]

Can you indicate how the NameplateCategory struct was created? Was it generated by diesel-cli print-schema or did you manually wrote that type? If it's the later one you just need to change the type name to that what actually happens in postgresql. If it's the former one it's a bug in diesel-cli that would be something to fix, but I would be rather surprised by that at this point.

As for your "solutions": Neither one is a solution as the type name can totally be upper-case there. So it's not helpful to reject these values or change the query. See for example this test

[LucaCappelletti94]

Turns out the issue was all regarding the use of quotes around the name of the custom type:

This will be inserted in pg_type as My_Type:

CREATE TYPE "My_Type" AS ENUM ('foo', 'bar');

while this will be inserted as my_type.

CREATE TYPE My_Type AS ENUM ('foo', 'bar');

I will make a PR to pgrx to add the quotes to avoid such issues in the future.