timestamptz and json should work for WIT

[akacase]

Bug report

timestamptz and json aren't implemented, even though exposed via the TypeOid and Cell enums.

Describe the bug

No matter the implementation, upon matching column.type_oid() in a match block, it will throw without fail on two needed
column types for us:

timestamptz and json

To Reproduce

Create a foreign table with a json or timestamptz column defined and accept input from an API that should match those types, postgres will spit out these errors:

2024-11-30 21:36:54.885 EST [16441] STATEMENT:  select * from users;
2024-11-30 21:40:20.688 EST [16923] ERROR:  not implemented

Expected behavior

The implementation should match and parse the content with the interfaces provided with the Wasm Interface.

System information

• OS: macOS
• Postgres: 16 (with wrappers built and installed locally)
• Rust: 1.83.0

[burmecia]

Thanks for reporting this issue.

This issue is caused by timestamptz type missing in the type_oid() function. We will fix it soon.

For the json type, we're actually using jsonb internally because it is recommended by Postgres team to as json replacement. So for the json type in WIT we should use corresponding jsonb column type as Postgres table column type.

[akacase]

@burmecia -- thanks for the quick fix and clarification about json and jsonb. Another note I wanted to make here is that the WASM wrappers, in particular, are going to be interfacing mostly with JSON-related APIs (my assumption) as opposed to more native wrappers. Utilizing jiff might make the most sense, modeled after the TC39 temporal proposal, which is unstable in Deno, etc. It has a warm target, which we use for parsing before dropping into microseconds, etc.

Here's a quick whack at it (not tested, could be wrong):

use jiff::{self, Timestamp};
use std::time::{SystemTime, UNIX_EPOCH};

use super::super::bindings::supabase::wrappers::time;
use super::FdwHost;

impl time::Host for FdwHost {
    /// Returns the current time in seconds since the UNIX epoch.
    fn epoch_secs(&mut self) -> i64 {
        Timestamp::now().as_second()
    }

    /// Parses an RFC3339 formatted string and returns microseconds since the UNIX epoch.
    fn parse_from_rfc3339(&mut self, s: String) -> time::TimeResult {
        Timestamp::from_str(&s)
            .map(|ts| ts.as_microsecond())
            .map_err(|e| e.to_string())
    }

    /// Parses a string with a custom format and returns microseconds since the UNIX epoch.
    fn parse_from_str(&mut self, s: String, fmt: String) -> time::TimeResult {
        Timestamp::strptime(fmt.as_bytes(), s.as_bytes())
            .map(|ts| ts.as_microsecond())
            .map_err(|e| e.to_string())
    }

    /// Converts microseconds since the UNIX epoch to an RFC3339 formatted string.
    fn epoch_ms_to_rfc3339(&mut self, msecs: i64) -> Result<String, time::TimeError> {
        let ts = Timestamp::from_microsecond(msecs)?;
        Ok(ts.to_string()) // Assuming `Display` outputs RFC3339
    }

    /// Sleeps for the specified number of milliseconds.
    fn sleep(&mut self, millis: u64) {
        std::thread::sleep(std::time::Duration::from_millis(millis));
    }
}

I can submit PR if needed, thanks again owe you a ☕

[burmecia]

thank you @akacase for the suggestion. Yes the Wasm wrappers are working for JSON-related APIs only, which we think could cover most of our external data sources. And jiff is a great datetime tool and we'd like to use it, but somehow we already used chrono in many native wrappers, so we probably won't introduce another similar datetime lib, but thanks again for the great ideas.