I'll see what I can do then.
I'm not interested in using binary cursors though, my usecase is purely
COPY statements, and I'm already successfully using those to insert
binary vector data.
Thank you for the very helpful answers!
On 4/8/25 17:59, Paul Ramsey wrote:
On Apr 8, 2025, at 8:52 AM, Lukas Panhirsch <lukas.panhir...@senselabs.de>
wrote:
Does this mean that a PR adding this would be considered?
I don't desperately need it, but considering my code directly
encodes/decodes WKB, using binary send/recv would be more efficient and
simpler than converting it to/from HEX first.
Sure, that would be a fine PR.
Word of warning on getting all excited about binary cursors, you have to push
*all* the data through the binary cursor, so you need to send the ints as ints,
and the varchars as the postgresql binary storage format for varchar, etc, etc.
Also binary cursors are finicky about only being run in transaction in a way
text cursors don’t seem to be, so you have to be careful to begin/commit around
things cleanly (this was a real problem with the mapserver driver for a long
time, and something that got a lot simpler when we just stopped using binary
cursors anymore… mind you that was reading not writing, so it felt even more
annoying to have to worry over neatly handling transactions for reads)
p.
On 4/8/25 17:48, Paul Ramsey wrote:
On Apr 8, 2025, at 8:46 AM, Lukas Panhirsch <lukas.panhir...@senselabs.de>
wrote:
Huh, I wouldn't have expected that, I guess I'll have to benchmark, thanks!
Though also, if the reasoning for not having binary send/recv is that
the format is not standardized, wouldn't the same apply to the text format?
That’s not the reasoning. The reasoning is we never had anyone who desperately
felt they needed send/recv, so they were never implemented. All the code is
there, so it would be an easy addition. Nobody has bothered.
P.
Or was the text format considered fast enough that binary send/recv is
redundant?
On 4/8/25 17:44, Paul Ramsey wrote:
On Apr 8, 2025, at 8:41 AM, Lukas Panhirsch <lukas.panhir...@senselabs.de>
wrote:
From what I understand I'd be prevented from using the BINARY format
though, as it relies on send/recv. The available alternative would be to
use the TEXT format of the raster, which is effectively just the binary
WKB encoded in ASCII HEX. And I'd assume that the bandwidth and
This is the same assume that we held and then discarded back in the day.
Hexdecoding is cheap cheap cheap. The win of using COPY over INSERT is orders
of magnitude higher than any overhead from the hex encoding. If you’re worried
about network bandwidth, connect over SSL which includes a compression layer.
ATB
P
computational overhead of said TEXT form is too high to be more
efficient than simple INSERTs using the binary format+ST_RastFromWKB for
a relatively low amount of rows, though I could be wrong.
Sincerely,
Lukas
On 4/8/25 17:31, Paul Ramsey wrote:
Copy vs insert is legit going to be faster, but that’s not a binary
cursor thing, it’s batching up all the transactions and parsing nicely
into one big bundle as COPY does. You should still be able to do a bulk
copy without using send/recv.
P
On Apr 8, 2025, at 8:30 AM, Lukas Panhirsch
<lukas.panhir...@senselabs.de> wrote:
That makes sense, thank you both.
I believe I've seen some performance improvements with COPY instead of
INSERT for large amounts of vector rows against a PostgreSQL server with
relatively high storage latency, but on the other hand I'm inserting far
fewer rows in the raster case, so I doubt I'd see the same improvement
there.
Sincerely,
Lukas
On 4/8/25 17:18, Paul Ramsey wrote:
Yes this. The utility of the send/recv function are in supporting a
client that is using a binary cursor. This doesn’t even happen much for
vector data anymore (there was a brief period when we all felt it was de
rigeur, and then it got benchmarked and it wasn’t that noticeable at all
as a performance tweak, so everyone stopped, because it added
complexity).
P
On Apr 8, 2025, at 8:15 AM, <l...@pcorp.us> <l...@pcorp.us> wrote:
I think it’s because postgis raster is not an OGC standard or any kind
of standard outside of PostGIS so there hasn’t been much need for it.
For example if you wanted to backup your postgis raster table, you’d
just use standard pg_dump / pg_restore tools.
Most use cases beyond that are importing from a different raster
format or exporting to a different raster format which functions
https://postgis.net/docs/en/RT_ST_FromGDALRaster.html <https://
postgis.net/docs/en/RT_ST_FromGDALRaster.html><https://
postgis.net/docs/en/RT_ST_FromGDALRaster.html <http://postgis.net/
docs/en/RT_ST_FromGDALRaster.html>>
andhttps://postgis.net/docs/en/RT_ST_AsGDALRaster.html <andhttps://
postgis.net/docs/en/RT_ST_AsGDALRaster.html><https://
postgis.net/docs/en/RT_ST_AsGDALRaster.html <http://postgis.net/docs/
en/RT_ST_AsGDALRaster.html>>
as well as the raster2pgsql commandline tool.
*From:*Lukas Panhirsch via postgis-users <postgis-users@lists.osgeo.org
<mailto:postgis-users@lists.osgeo.org>>
*Sent:*Tuesday, April 8, 2025 9:28 AM
*To:*postgis-users@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org>
*Subject:*Why does the raster type not have send/receive defined?
Hello,
I haven't used this mailing list before and couldn't figure out how to
search in the archives, so I apologize if this has been answered before.
Is there any specific reason why the raster type does not have the
send/receive functions in its SQL type definition?
The geometry type has these defined as conversions to/from EWKB, which
are equivalent to ST_AsEWKB/ST_GeomFromEWKB if I understand correctly.
Equivalent (E?)WKB conversion functions exist for rasters in the form
of (RT_)ST_AsBinary/ST_RastFromWKB, but have to be called explicitly.
I would like to use PostgreSQL features like COPY ... WITH BINARY with
rasters, but it seems this is preventing me from doing so.
Sincerely,
Lukas
