Support for Postgres composite types

[ebkalderon]

Setup

Versions

• Rust: 1.26.0
• Diesel: 1.3.0
• Database: Postgres
• Operating System macOS 10.13.4

Feature Flags

• diesel: ["postgres", "extra"]

Problem Description

Hello everyone! I know that Diesel supports defining custom enum types as well as Rust-side newtypes via diesel-derive-newtype. I am trying to understand whether Diesel supports user-defined newtypes and composite types on the Postgres side as well; the documentation is particularly sparse on this subject.

What are you trying to accomplish?

I am currently trying to map a user-defined composite type from Postgres to a Rust struct using Diesel. Some of these composite types are essentially newtypes with a single field, and others have multiple fields. I would like to be able to insert and query these composite types into and from Postgres.

What is the expected output?

N/A

What is the actual output?

When my insert_into query is turned into a string with debug_query, I see the following:

INSERT INTO "movements" ("timestamp", "entries") VALUES ($1, $2) -- binds: [2018-05-25T14:00:31.179542Z, [Entry { amount: BigDecimal { int_val: BigInt { sign: Plus, data: BigUint { data: [2764472320, 232830] } }, scale: 15 }, currency: Uuid { bytes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] }}]]

This generated SQL looks pretty good to me. However, the insert fails and returns the following error:

thread 'db::tests::blah' panicked at 'called `Result::unwrap()` on an `Err`
value: Query(DatabaseError(__Unknown, "wrong number of columns: 65536, expected 2"))', libcore/result.rs:945:5
note: Run with `RUST_BACKTRACE=1` for a backtrace.

Are you seeing any additional errors?

No, everything else seems to work fine.

Steps to reproduce

Below is a reduced and redacted form of the codebase I'm working on. I am using a struct with multiple fields for the sake of demonstrating the problem more thoroughly.

/// Native Rust type
#[derive(Debug, FromSqlRow, AsExpression)]
#[sql_type = "DbEntry"]
pub struct Entry {
    pub amount: BigDecimal,
    pub currency: Uuid,
}

/// Handle to SQL type
#[derive(Debug, SqlType)]
#[postgres(type_name = "entry")]
pub struct DbEntry;

/// Database schema
table! {
    use super::DbEntry;
    use diesel::sql_types::*;
    transactions (id) {
        id -> Int4,
        timestamp -> Timestamptz,
        entries -> Array<DbEntry>,
    }
}

impl ToSql<DbEntry, Pg> for Entry {
    fn to_sql<W: Write>(&self, out: &mut serialize::Output<W, Pg>) -> serialize::Result {
        ToSql::<sql_types::Decimal, Pg>::to_sql(self.amount, out)?;
        ToSql::<sql_types::Uuid, Pg>::to_sql(self.currency, out)?;
        Ok(IsNull::No)
    }
}

impl FromSql<DbEntry, Pg> for Entry {
    fn from_sql(&self, bytes: Option<&[u8]>) -> deserialize::Result<Self> {
        let bytes = not_none!(bytes);

        let fixed_types_len = PG_UUID_BYTE_LEN;
        let amount_len = bytes.len() - fixed_types_len;

        let amount = &bytes[0..(amount_len - 1)];
        let currency = &bytes[amount_len..];

        Ok(Entry {
            amount: FromSql::<sql_types::Decimal, Pg>::from_sql(Some(amount))?,
            currency: FromSql::<sql_types::Uuid, Pg>::from_sql(Some(currency))?,
        })
    }
}

/// Insert statement
#[derive(Debug, Insertable)]
#[table_name = "transactions"]
pub struct InsertEntry {
    pub timestamp: chrono::DateTime<Utc>,
    pub entries: Vec<Entry>,
}

This is the database migration script I have in place.

-- up.sql

CREATE TYPE entry AS (
    amount DECIMAL,
    currency UUID
);

CREATE TABLE IF NOT EXISTS transactions (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ NOT NULL,
    entries ENTRY[] NOT NULL
);

Checklist

• I have already looked over the issue tracker for similar issues.
• This issue can be reproduced on Rust's stable channel.

[ebkalderon]

EDIT: Sorry, I had to edit the example to make sure it was correct and showcased the problem more visibly. I decided to use a more complex composite type to demonstrate the problem.

[weiznich]

I know that Diesel supports defining custom enum types as well as Rust-side newtypes via diesel-derive-newtype. I am trying to understand whether Diesel supports user-defined newtypes and composite types on the Postgres side as well; the documentation is particularly sparse on this subject.

Supporting composite types on Postgres side should work the same way like supporting custom enum types there. Documentation is sparse because nobody have written something about that yet. (Presumably because at most only a few people have tried that 😉, so If you get it working, maybe write a short guide?)

Regarding to the example code: (There are several small error in there pointed out by the compiler, I will not attempt to fix them here, just apply the compiler suggesting )
In general your are on the right track. Similar to you I've tried to do an insert and it seems like the serialization is wrong. For postgresql FromSql is basically something that constructs the binary representation of values passed to libpq, so one must figure out how this representation looks like. Unfortunately this seems to be similar good documented as the feature in diesel (a quick google search showed basically nothing 😟 ). From toying a bit around with the implementation I found out the following things:

• First you need to write the number of fields as Integer to the final representation
• Then you need to write the Oid of the type of the first field.
• Then you need to write the length in byte of the following type(For BigDecimal this is not that easy because it has a variable length, for Uuid it is easy the size is 16)
  (It's quite late here, so I've stopped playing around further, and rather started to write this comment)
  Updated implementation of ToSql

impl ToSql<DbEntry, Pg> for Entry {
    fn to_sql<W: Write>(&self, out: &mut serialize::Output<W, Pg>) -> serialize::Result {
        ToSql::<sql_types::Integer, Pg>::to_sql(&1, out)?;
        ToSql::<sql_types::Oid, Pg>::to_sql(&1700, out)?;
        ToSql::<sql_types::Integer, Pg>::to_sql(&/*Somehow figure out the size here*/, out)?;
        ToSql::<sql_types::Decimal, Pg>::to_sql(&self.amount, out)?;
        ToSql::<sql_types::Oid, Pg>::to_sql(&2950, out)?;
        ToSql::<sql_types::Integer, Pg>::to_sql(&16, out)?;
        ToSql::<sql_types::Uuid, Pg>::to_sql(&self.currency, out)?;
        Ok(IsNull::No)
    }
}

The FromSql implementation should be similar.

(I will close this issue because this is nothing actionable for diesel itself. Feel free to response here, or even better move to our Gitter channel.)

[sgrif]

You should also be able to send an anonymous record which gets coerced automatically (but this will transmit as ($1, $2) instead of a single bind parameter. See http://docs.diesel.rs/diesel/pg/types/sql_types/struct.Record.html for details.

[ebkalderon]

These are some excellent responses! Thank you both so much. I will look further into how the libpg internal representation for composite types' works. I still have two more questions, though. I might just join your Gitter channel for further assistance, to avoid clogging the issue tracker.

[ebkalderon]

Referencing #1735 and #1738, should future readers want to see the solution.