Decoding tstzrange(-infinity, infinity) crashes sqlx

[dns2utf8-novaziun]

I have found these related issues/pull requests

This is also happening in Array's containing any infinities

Description

sqlx crashes when a query contains +/-infinity in any result

Reproduction steps

Use a query like:

-- broken
SELECT tstzrange('-infinity'::timestamptz, 'infinity'::timestamptz) AS valid_range;
-- works
SELECT tstzrange('1970-01-01'::timestamptz, '3050-01-01'::timestamptz) AS valid_range;

 let rows = sqlx::query(sql)
        .fetch_all(executor)
        .await
        .context("could not query")?;

for row in rows {
    let range: Option<PgRange<OffsetDateTime>> = row.try_get(1).context("valid_range row lookup failed")?;
    println!("found range: {range:#?}");
}

SQLx version

0.8.2

Enabled SQLx features

default-features = false, features = [ "runtime-tokio-rustls", "postgres", "chrono", "time" ]

Database server and version

Postgres 16.3-1.pgdg22.04+1

Operating system

Linux or Docker

Rust version

1.84.0

[joeydewaal]

'-infinity'::timestamptz creates a timestamptz with '-infinity' as value. The problem with decoding to an OffsetDateTime is that it doesn't have a way to represent this value. (So the problem is not specific to tstzrange or ARRAY.)

One way to solve this problem is to create a wrapper type that does take this into account.

#[derive(Debug)]
enum PgTimestampTz {
    Infinite,
    NegativeInfinite,
    DateTime(OffsetDateTime),
}

impl Type<Postgres> for PgTimestampTz {
    fn type_info() -> PgTypeInfo {
        <OffsetDateTime as Type<Postgres>>::type_info()
    }
}

impl<'r> Decode<'r, Postgres> for PgTimestampTz {
    fn decode(value: PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        match value.format() {
            PgValueFormat::Text => match value.as_str()? {
                "infinity" => Ok(PgTimestampTz::Infinite),
                "-infinity" => Ok(PgTimestampTz::NegativeInfinite),
                _ => OffsetDateTime::decode(value).map(PgTimestampTz::DateTime),
            },
            PgValueFormat::Binary => match value.as_bytes()? {
                b"\x7F\xFF\xFF\xFF\xFF\xFF\xFF\xFF" => Ok(PgTimestampTz::Infinite),
                b"\x80\x00\x00\x00\x00\x00\x00\x00" => Ok(PgTimestampTz::NegativeInfinite),
                _ => OffsetDateTime::decode(value).map(PgTimestampTz::DateTime),
            },
        }
    }
}

[abonander]

@dns2utf8-novaziun when you say "crash", do you mean the code panics?

[dns2utf8-novaziun]

Yes, it panics with an error like this:

.cargo/registry/src/index.crates.io-6f17d22bba15001f/time-0.3.37/src/primitive_date_time.rs:943:14:
resulting value is out of range

I notice that I have the same error with dates that are far in the future like this:

{
  "[\"2024-01-01 00:00:00+00\",\"2050-01-01 00:00:00+00\")",
  "[\"2024-08-01 00:00:00+00\",\"2025-01-01 00:00:00+00\")"
}

In sqlx I get the type PgTypeInfo(TstzRangeArray) if that helps to locate.
The query always returns an Array. Wrapping it in an Option did not work, as soon as I try to decode it to Vec<PgRange<OffsetDateTime>> it breaks.

Is there a way to tell sqlx that there is no need for the Option?

[dns2utf8-novaziun]

I tried the solution from @joeydewaal too, but I can not get it to compile.

I get there two errors

valid_range: row.try_get(8).context("valid_range fail")?,
                 ^^^^^^^ the trait `Type<Postgres>` is not implemented for `PgRange<PgTimestampTz>`

ranges: row.try_get(9).context("ranges fail")?,
            ^^^^^^^ the trait `Type<Postgres>` is not implemented for `PgRange<PgTimestampTz>`

I extended your code to this:

#[derive(Debug)]
pub enum PgTimestampTz {
    Infinite,
    NegativeInfinite,
    DateTime(OffsetDateTime),
}

impl sqlx::Type<sqlx::Postgres> for PgTimestampTz {
    fn type_info() -> sqlx::postgres::PgTypeInfo {
        <OffsetDateTime as sqlx::Type<sqlx::Postgres>>::type_info()
    }
}

impl sqlx::postgres::PgHasArrayType for PgTimestampTz {
    fn array_type_info() -> sqlx::postgres::PgTypeInfo {
        //PgType::TimestamptzArray
        PgTypeInfo::with_oid(Oid(1185))
    }
}

impl<'r> sqlx::Decode<'r, sqlx::Postgres> for PgTimestampTz {
    fn decode(value: sqlx::postgres::PgValueRef<'r>) -> Result<Self, sqlx::error::BoxDynError> {
        match value.format() {
            sqlx::postgres::PgValueFormat::Text => match value.as_str()? {
                "infinity" => Ok(PgTimestampTz::Infinite),
                "-infinity" => Ok(PgTimestampTz::NegativeInfinite),
                _ => OffsetDateTime::decode(value).map(PgTimestampTz::DateTime),
            },
            sqlx::postgres::PgValueFormat::Binary => {
                let bytes = value.as_bytes()?;
                info!("decoding PgTimestampTz bytes {bytes:#?}");
                match bytes {
                    b"\x7F\xFF\xFF\xFF\xFF\xFF\xFF\xFF" => Ok(PgTimestampTz::Infinite),
                    b"\x80\x00\x00\x00\x00\x00\x00\x00" => Ok(PgTimestampTz::NegativeInfinite),
                    _ => OffsetDateTime::decode(value).map(PgTimestampTz::DateTime),
                }
            }
        }
    }
}

Do I assume correctly, that I will not be able to implement the trait on PgRange<PgTimestampTz> because it is not within my own trait?