Re: [HACKERS] proposal: psql statements \gstore \gstore_binary(instead COPY RAW)
| От | Pavel Stehule |
|---|---|
| Тема | Re: [HACKERS] proposal: psql statements \gstore \gstore_binary(instead COPY RAW) |
| Дата | |
| Msg-id | CAFj8pRBzMtiyBLqWW+iiim+5zfc0VvAY0+17G_vPK_kwMztVSQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: [HACKERS] proposal: psql statements \gstore \gstore_binary(instead COPY RAW) (Jim Nasby <Jim.Nasby@BlueTreble.com>) |
| Ответы |
Re: [HACKERS] proposal: psql statements \gstore \gstore_binary(instead COPY RAW)
(Pavel Stehule <pavel.stehule@gmail.com>)
|
| Список | pgsql-hackers |
2016-12-10 2:27 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
On 12/9/16 9:39 AM, Pavel Stehule wrote:
SELECT image FROM accounts WHERE id = xxx
\gstore_binary ~/image.png
What do you think about this proposal?
Seems reasonable.
I've lost track at this point... is there a way to go the other direction with that as well? Namely, stick the contents of a file into a field via an INSERT or UPDATE?
a target of this feature is storing only. For import there should be another statements.
I am think so there is a consensus (with Tom) on binary passing mode. Some like
\set USE_BINARY on
What is not clean (where is not a agreement is a way how to get a some content) - if we use a variables with content (not references), then we can or cannot to have special statement
so some ways how to push some binary content to server
A)
\set xxxx `cat file`
\set USE_BINARY on
INSERT INTO tab(id, data) VALUES(1, :xxxx::bytea);
B)
\set xxxx `cat file`
INSERT INTO tab(id, data) VALUES (1, :x'xxxx'); -- use bytea escape
C)
\load_binary xxxx file
INSERT INTO tab(id, data) VALUES(1, :'xxxx');
D)
\load xxxx file
\set USE_BINARY on
INSERT INTO tab(id, data) VALUES(1, :xxxx::bytea);
E)
\set xxxx ``cat file``
INSERT INTO tab(id, data) VALUES (1, :'xxxx');
any from mentioned variants has some advantages - and I don't see a clean winner. I like a binary mode for passing - the patch is small and clean and possible errors are well readable (not a MBytes of hexa numbers). Passing in text mode is safe - although the some errors, logs can be crazy. I would to use some form of "load" backslash command ("load", "load_binary"): a) we can implement a file tab complete, b) we can hide some platform specific ("cat" linux, "type" windows).
Now, only text variables are supported - it is enough for passing XML, JSON - but not for binary data (one important variant is passing XML binary for automatic XML internal encoding transformation). So we should to encode content before storing to variable, or we should to introduce binary variables. It is not hard - introduce new functions, current API will supports text variables.
The implementation of these variants is short, simple - we can implement more than exactly one way - @E is general, but little bit magic, and without a autocomplete possibility, @C is very clear
The discussion can be about importance following features:
1. binary passing (important for XML, doesn't fill a logs, a speed is not important in this context)
2. tab complete support
3. verbosity, readability
I would to know how these points are important, interesting for other people? It can helps with choosing variant or variants that we can implement. I don't expect some significant differences in implementation complexity of mentioned variants - the code changes will be +/- same.
Regards
Pavel
I've done that in the past via psql -v var=`cat file`, but there's obviously some significant drawbacks to that...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "Karl O. Pinc"Дата:
Сообщение: Re: [HACKERS] Patch to implement pg_current_logfile() function
Следующее
От: Keith FiskeДата:
Сообщение: Re: [HACKERS] [COMMITTERS] pgsql: Implement table partitioning.