pg.ddx.io  psycopg/psycopg GitHub issues and pull requests  
help / color / mirror / Atom feed
[psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
10+ messages / 3 participants
[nested] [flat]

* [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 09:32 "zogzog (@zogzog)" <noreply+zogzog@github.com>
  0 siblings, 0 replies; 10+ messages in thread

From: zogzog (@zogzog) @ 2025-04-04 09:32 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Here's a small test to explain it all:

```python
    with conn.transaction():
        conn.execute(
            'create extension if not exists ltree;'
            'create table if not exists tree ('
            '  id serial primary key,'
            '  path ltree'
            ')'
        )
    with conn.transaction():
        conn.execute('insert into tree (path) values (\'Hello\')')
        r = conn.execute('select path from tree').fetchone()
        assert r == ('Hello',)

        # binary mode: issue
        r = conn.execute('select path from tree', binary=True).fetchone()
        assert r == (b'\x01Hello',)
```

Is this a known issue ?

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:01 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-04-04 11:01 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

It is an unsupported feature. The ltree type is not known by psycopg therefore, as per documentation, it will be returned as unparsed text or as unparsed binary (see [docs](Every type returned by the query should have a [Loader](https://www.psycopg.org/psycopg3/docs/api/adapt.html#psycopg.adapt.Loader) configured, otherwise the data will be returned as unparsed str (for text results) or buffer (for binary results).)).

A (scary) long time ago I wrote an [ltree extension support module](https://github.com/dvarrazzo/py-ltree) but it is for psycopg2. I actually ported it to psycopg 3 for a private project, but haven't released the new version. It doesn't support the binary format anyway.

I can try and release such package and, if stable enough, add it to psycopg, but it will take some work and I don't have the resources to do it for free.

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:02 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-04-04 11:02 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo applied label "enhancement".

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:02 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-04-04 11:02 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@dvarrazzo applied label "funding requested".

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:41 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: zogzog (@zogzog) @ 2025-04-04 11:41 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Yes, I have seen your older version for psycopg2.
Thanks for the answer, I'll do without the binary protocol, that's probably sufficient for my needs.


^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:42 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: zogzog (@zogzog) @ 2025-04-04 11:42 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Ancillary question: do you know the reason for the \x01 being prepended to the byte strings ?

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 11:53 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-04-04 11:53 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Probably it's a version indicator. The ltree module was developed by Oleg Bartunov and Teodor Sigaev, the same authors of the jsonb component, and I saw something similar: jsonb doesn't have a specific binary format but has a byte followed by the textual representation. See jsonb loader:

https://github.com/psycopg/psycopg/blob/ef3a92c8891d53cf90b1f0fa51abbe9e78bcb572/psycopg/psycopg/typ...

And yes, the source confirms that:

https://github.com/postgres/postgres/blob/7afca7edef751b8d7c0f5b6402ffcefc11c67fdd/contrib/ltree/ltr...

So, if you have your own ltree adapter already, it's easy to make your own binary loaders/dumpers: just prepend 0x01 on dump, and expect it as the first byte on load.

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-04-04 12:14 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: zogzog (@zogzog) @ 2025-04-04 12:14 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Thanks. I'll eventually investigate a binary loader/adapter. Still not sure if it's worth it (the point would be to not forget an occasional binary=False where it matters) since the performance implications of binary vs text are a bit unclear (I noticed a quite small decrease of the run time of our test suites with binary, but nothing truly amazing; I probably should write a micro-benchmark to highlight its effect where performance really matters).

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2025-09-25 22:29 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2025-09-25 22:29 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Hello,

FYI, I have dumped the state of the Ltree module working with Psycopg 3 in the [pg3 branch](https://github.com/dvarrazzo/py-ltree/commits/pg3) of the py-ltree project. The readme has [Psycopg 3 usage notes](https://github.com/dvarrazzo/py-ltree/tree/pg3?tab=readme-ov-file#using-with-psycopg-3). I have also added support for binary dump/load.

This is not production ready as it is, it needs Postgres tests. In a conversation with a friend the topic cropped up and I realized I never published this bit of work. So I'm pushing it out for testing.

I am not sure yet if this is the good design candidate to be included in Psycopg, but if you want to do some tests with it and report your findings it would be very useful.

^ permalink  raw  [nested|flat] 10+ messages in thread

* Re: [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode
@ 2026-11-12 11:57 ` "Binary-Refinery (@Binary-Refinery)" <noreply+Binary-Refinery@github.com>
  8 siblings, 0 replies; 10+ messages in thread

From: Binary-Refinery (@Binary-Refinery) @ 2026-11-12 11:57 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

I can run a text and binary round trip against PostgreSQL using the py-ltree pg3 branch, and report the adapter registration, server version and returned values here.

^ permalink  raw  [nested|flat] 10+ messages in thread


end of thread, other threads:[~2026-11-12 11:57 UTC | newest]

Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-04 09:32 [psycopg/psycopg] issue #1040: Oddity with ltree + query in binary mode "zogzog (@zogzog)" <noreply+zogzog@github.com>
2025-04-04 11:01 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-04-04 11:02 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-04-04 11:02 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-04-04 11:41 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
2025-04-04 11:42 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
2025-04-04 11:53 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2025-04-04 12:14 ` "zogzog (@zogzog)" <noreply+zogzog@github.com>
2025-09-25 22:29 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
2026-11-12 11:57 ` "Binary-Refinery (@Binary-Refinery)" <noreply+Binary-Refinery@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox