Support the ltree data type

[uonr]

The result should be just String.

https://www.postgresql.org/docs/9.1/ltree.html

[uonr]

thread '<unnamed>' panicked at 'called `Result::unwrap()` on an `Err` value: Error(Conversion(WrongType(Type(Other(Other { name: "ltree", oid: 17197, kind: Simple, schema: "public" })))))', libcore/result.rs:1009:5

[gtors]

The ltree type is not supports binary format, but this crate is forces to use only binary form:

rust-postgres/tokio-postgres/src/proto/client.rs

Lines 302 to 315 in d884298

	let r = frontend::bind(
	name,
	statement.name(),
	Some(1),
	params.iter().zip(statement.params()).enumerate(),
	|(idx, (param, ty)), buf| match param.to_sql_checked(ty, buf) {
	Ok(IsNull::No) => Ok(postgres_protocol::IsNull::No),
	Ok(IsNull::Yes) => Ok(postgres_protocol::IsNull::Yes),
	Err(e) => {
	error_idx = idx;
	Err(e)
	}
	},
	Some(1),

The sixth argument (Some(1)) specifies the return type for columns (1 - binary format, 0 - text -format)

[mamcx]

I get hit for this too. I try to run a select and get:

DbError { severity: \"ERROR\", parsed_severity: Some(Error), code: SqlState(\"42883\"), message: \"no binary output function available for type ltree\", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some(\"lsyscache.c\"), line: Some(2759), routine: Some(\"getTypeBinaryOutputInfo\") }

I'm able to use it with other languages. A workaround is to cast with ::TEXT but this not work if is required to just run a sql directly (SELECT *...) like for making a REPL.

[jlgerber]

Any thoughts on this. I am using ltree extensively, and it is forcing me to write pgpsql functions to handle the issue. A pain to optimize.

[gtors]

The bug is still not fixed. As ad-hoc solution, you may try changing the ltree type by adding custom receive_function and send_function.

https://www.postgresql.org/docs/12/sql-createtype.html#id-1.9.3.94.6

receive_function

The name of a function that converts data from the type's external binary form to its internal form.

send_function

The name of a function that converts data from the type's internal form to its external binary form.

[mamcx]

Any sample in how alter the type with the workaround?

[gtors]

update pg_type set typreceive = 'textrecv', typsend = 'textsend' where typname = 'ltree'

But after that I got another error:

cannot convert between the Rust type &str and the Postgres type ltree

[gtors]

I've got it working (mostly)!

Cargo.toml

[package]
name = "demo"
version = "0.1.0"
edition = "2018"

[dependencies]
postgres = "*"
postgres-types = "*"
postgres-protocol = "*"

src/main.rs

use postgres::{Client, NoTls};
use postgres_protocol::types;
use postgres_types::{FromSql, Type};
use std::error::Error;


struct Ltree<'a>(&'a str);


impl<'a> FromSql<'a> for Ltree<'a> {
    fn from_sql(_: &Type, raw: &'a [u8]) -> Result<Ltree<'a>, Box<dyn Error + Sync + Send>> {
        let t = types::text_from_sql(raw)?;
        Ok(Ltree(t))
    }

    fn accepts(ty: &Type) -> bool {
        ty.name() == "ltree"
    }
}


fn main() {
    let mut client = Client::connect("host=localhost user=postgres dbname=demo", NoTls).unwrap();

    // create table t(p ltree);
    for row in client.query("SELECT p FROM t", &[]).unwrap() {
        let path: Ltree = row.get(0);
        println!("{}", path.0); // BUG: dots missed  here! Seems need to customize receive/send functions somehow
    }
}

[EPashkin]

Has almost same issue with columns in pg_catalog.pg_type table (and many functions in pg_catalog).

Example

    for row in client.query("select oid, typname, typinput, * from pg_type limit 1", &[])? {
        let oid: Oid = row.get(0);
        let typname: &str = row.get(1);
        let typinput: &str = row.get(2);

        println!("{} {} {:?}", oid, typname, typinput);
    }

@sfackler Is acceptable to add to Client new function query_with_text_results that pass Some(0) to frontend::bind ?

[sfackler]

I've been thinking about doing something like that, yeah. On the ToSql side, we can add the ability for the values to tell the library if they are speaking the text or binary format so this would all be handled automatically. This actually used to be the case many years ago but I got rid of it because everything at the time used the binary protocol.

On the FromSql side, things are unfortunately a bit less straightforward since at query time we know the raw Postgres types that will be returned, but not if those types support the binary format or not. Adding another method like query_with_text_results that returns a row type that just gives out strings rather than working with ToSql is probably best, but unfortunately duplicates a bunch of API surface area.

[LLFourn]

According to patch notes ltree got binary support in postgres 13:

Add support for binary I/O of ltree, lquery, and ltxtquery types (Nino Floris)

https://www.postgresql.org/docs/13/release-13.html

So all that remains is to add the type for this to work?

[halfmatthalfcat]

I've looked into taking this to the finish line and there are a couple issues, however I have a working prototype that I'll create a PR for soon.

1. The oid for ltree, lquery and ltxtquery are non-static and will change per-database unfortunately. That means that loading the Type by oid is a nonstarter. However, we can apply the same semantics that are used for citext and look for ltree, lquery and ltxtquery as a string value and parse it appropriately.
2. Per Nino Floris's changes to postgres 13.x and the implementation in their C# driver, l{tree,query,txtquery} all have a version number prepended to the string literal that is sent over the wire. This needs to be accounted for when (de)serializing the string.

[LLFourn]

@halfmatthalfcat I can confirm both points. See: https://github.com/LLFourn/olivia/blob/4d1181d995d9cb6bf15734b20593cc1d612e1d7d/olivia/src/db/postgres.rs#L36 for what you have to do as an application to use this as is.

[pencilcheck]

Looks like upgrading to postgres 13 fixed the binary output problem