proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

On Dec 9, 2016 18:40, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:

Hi

Long time I am pushing a COPY RAW - without success.

Now I propose functionally similar solution - reduced to only to psql
console

Now we have a statement \g for execution query, \gset for exec and store
result in memory and I propose \gstore for storing result in file and
\gstore_binary for storing result in file with binary passing. The query
result should be one row, one column.

Usage:

SELECT image FROM accounts WHERE id = xxx
\gstore_binary ~/image.png

What do you think about this proposal?

I might be missing something, but is it different from:

\t
\a
\o output_filename
SELECT ...
\o

?

--
Alex

2016-12-09 19:48 GMT+01:00 Oleksandr Shulgin <oleksandr.shulgin@zalando.de>:

On Dec 9, 2016 18:40, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:

Hi

Long time I am pushing a COPY RAW - without success.

Now I propose functionally similar solution - reduced to only to psql
console

Now we have a statement \g for execution query, \gset for exec and store
result in memory and I propose \gstore for storing result in file and
\gstore_binary for storing result in file with binary passing. The query
result should be one row, one column.

Usage:

SELECT image FROM accounts WHERE id = xxx
\gstore_binary ~/image.png

What do you think about this proposal?

I might be missing something, but is it different from:

\t
\a
\o output_filename
SELECT ...
\o

?

The \gstore is same like these commands - but it is user friendly - one
liner statement.

For \gstore_binary there is not any workaround

Pavel

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?

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)

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 12/09/2016 08:27 PM, Jim Nasby wrote:

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?

I've done that in the past via psql -v var=`cat file`, but there's
obviously some significant drawbacks to that...

It all looks eerily familiar ...

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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

2016-12-10 7:11 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

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

I was wrong - the agreement is on passing psql parameters as query
parameters - not on binary mode. Binary mode can be interesting for
importing xml, but it is really corner case.

Hi

2016-12-09 18:39 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

Long time I am pushing a COPY RAW - without success.

Now I propose functionally similar solution - reduced to only to psql
console

Now we have a statement \g for execution query, \gset for exec and store
result in memory and I propose \gstore for storing result in file and
\gstore_binary for storing result in file with binary passing. The query
result should be one row, one column.

Usage:

SELECT image FROM accounts WHERE id = xxx
\gstore_binary ~/image.png

What do you think about this proposal?

here is a poc patch

Regards

Pavel

Usage:

postgres=# set client_encoding to 'latin2';
SET
Time: 1,561 ms
postgres=# select a from foo
postgres-# \gbstore ~/doc.xml
Time: 1,749 ms

content of doc.xml
<?xml version="1.0" encoding="LATIN2"?><a>příliš žluťoučký kůň se napil
žluté vody</a>

2016-12-11 18:23 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

2016-12-09 18:39 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Hi

Long time I am pushing a COPY RAW - without success.

Now I propose functionally similar solution - reduced to only to psql
console

Now we have a statement \g for execution query, \gset for exec and store
result in memory and I propose \gstore for storing result in file and
\gstore_binary for storing result in file with binary passing. The query
result should be one row, one column.

Usage:

SELECT image FROM accounts WHERE id = xxx
\gstore_binary ~/image.png

What do you think about this proposal?

here is a poc patch

Regards

Pavel

Usage:

postgres=# set client_encoding to 'latin2';
SET
Time: 1,561 ms
postgres=# select a from foo
postgres-# \gbstore ~/doc.xml
Time: 1,749 ms

content of doc.xml
<?xml version="1.0" encoding="LATIN2"?><a>příliš žluťoučký kůň se napil
žluté vody</a>

second update - + doc

the export import regress tests are little bit heavy - I'll write it for
loading content file together.

Regards

Pavel