Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Pavel Stehule]

2016-12-11 18:23 GMT+01:00 Pavel Stehule :

> Hi
>
> 2016-12-09 18:39 GMT+01:00 Pavel Stehule :
>
>> 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
> příliš žluťoučký kůň se napil
> žluté vody
>
>
second update - + doc

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

Regards

Pavel


>
>> Regards
>>
>> Pavel
>>
>
>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 9915731..7e2fa96 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1944,6 +1944,31 @@ hello 10
 
   
 
+
+  
+\gstore [ filename ]
+\gstore [ |command ]
+\gbstore [ filename ]
+\gbstore [ |command ]
+
+
+ Sends the current query input buffer to the server and stores the
+ raw query's output into stores the query's output in filename or pipes the output
+ to the shell command command.  The file or command is
+ written to only if the query successfully returns exactly one row
+ one column non null result, not if the query fails or is a 
+ non-data-returning SQL command. For example:
+
+= SELECT avatar FROM users WHERE id = 123
+- \gbstore ~/avatar.png
+
+
+
+  
+
+
   
 \h or \help [ command ]
 
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..e8fabb9 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,27 @@ exec_command(const char *cmd,
 		status = PSQL_CMD_SEND;
 	}
 
+	/* \gstore [filename], \gbstore [filename] -- send query and store result in (binary) file */
+	else if (strcmp(cmd, "gstore") == 0 ||
+			  (strcmp(cmd, "gbstore") == 0))
+	{
+		char	   *fname = psql_scan_slash_option(scan_state,
+   OT_FILEPIPE, NULL, false);
+
+		if (!fname)
+			pset.gfname = pg_strdup("");
+		else
+		{
+			expand_tilde();
+			pset.gfname = pg_strdup(fname);
+		}
+
+		pset.raw_flag = true;
+		pset.binres_flag = (strcmp(cmd, "gbstore") == 0);
+		free(fname);
+		status = PSQL_CMD_SEND;
+	}
+
 	/* help */
 	else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
 	{
@@ -1064,7 +1085,6 @@ exec_command(const char *cmd,
 		free(opt2);
 	}
 
-
 	/* \o -- set query output */
 	else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0)
 	{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..d4b4f15 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -854,6 +854,85 @@ StoreQueryTuple(const PGresult *result)
 	return success;
 }
 
+/*
+ * StoreRawResult: the returned value (possibly binary) is displayed
+ * or stored in file. The result should be exactly one row, one column.
+ */
+static bool
+StoreRawResult(const PGresult *result)
+{
+	bool		success = true;
+
+	if (PQntuples(result) < 1)
+	{
+		psql_error("no rows returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQntuples(result) > 1)
+	{
+		psql_error("more than one row returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) < 1)
+	{
+		psql_error("no columns returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) > 1)
+	{
+		psql_error("more than one column returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQgetisnull(result, 0, 0))
+	{
+		psql_error("returned value is null for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else
+	{
+		char	   *value;
+		int			length;
+		FILE	   *fout = NULL;
+		bool		is_pipe = false;
+
+		value = PQgetvalue(result, 0, 0);
+		length = PQgetlength(result, 0, 0);
+
+		if (pset.gfname && *(pset.gfname) != '\0')
+		{
+			if (!openQueryOutputFile(pset.gfname, , _pipe))
+success = false;
+			if (success && is_pipe)
+disable_sigpipe_trap();
+		}
+
+		if (success)
+		{
+			success = fwrite(value, 1, length, fout != NULL ? fout : pset.queryFout) == length;
+			if (!success)
+psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (success)
+success = fflush(fout != NULL ? fout : pset.queryFout) == 0;
+
+			if (!success)
+psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if 

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Pavel Stehule]

Hi

2016-12-09 18:39 GMT+01:00 Pavel Stehule :

> 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
příliš žluťoučký kůň se napil
žluté vody


> Regards
>
> Pavel
>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index a9a2fdb..2cf54bf 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -929,6 +929,27 @@ exec_command(const char *cmd,
 		status = PSQL_CMD_SEND;
 	}
 
+	/* \gstore [filename], \gbstore [filename] -- send query and store result in (binary) file */
+	else if (strcmp(cmd, "gstore") == 0 ||
+			  (strcmp(cmd, "gbstore") == 0))
+	{
+		char	   *fname = psql_scan_slash_option(scan_state,
+   OT_FILEPIPE, NULL, false);
+
+		if (!fname)
+			pset.gfname = pg_strdup("");
+		else
+		{
+			expand_tilde();
+			pset.gfname = pg_strdup(fname);
+		}
+
+		pset.raw_flag = true;
+		pset.binary_result = (strcmp(cmd, "gbstore") == 0);
+		free(fname);
+		status = PSQL_CMD_SEND;
+	}
+
 	/* help */
 	else if (strcmp(cmd, "h") == 0 || strcmp(cmd, "help") == 0)
 	{
@@ -1064,7 +1085,6 @@ exec_command(const char *cmd,
 		free(opt2);
 	}
 
-
 	/* \o -- set query output */
 	else if (strcmp(cmd, "o") == 0 || strcmp(cmd, "out") == 0)
 	{
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index a7789df..b2e437a 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -854,6 +854,85 @@ StoreQueryTuple(const PGresult *result)
 	return success;
 }
 
+/*
+ * StoreRawResult: the returned value (possibly binary) is displayed
+ * or stored in file. The result should be exactly one row, one column.
+ */
+static bool
+StoreRawResult(const PGresult *result)
+{
+	bool		success = true;
+
+	if (PQntuples(result) < 1)
+	{
+		psql_error("no rows returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQntuples(result) > 1)
+	{
+		psql_error("more than one row returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) < 1)
+	{
+		psql_error("no columns returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQnfields(result) > 1)
+	{
+		psql_error("more than one column returned for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else if (PQgetisnull(result, 0, 0))
+	{
+		psql_error("returned value is null for \\gstore or \\gbstore\n");
+		success = false;
+	}
+	else
+	{
+		char	   *value;
+		int			length;
+		FILE	   *fout = NULL;
+		bool		is_pipe = false;
+
+		value = PQgetvalue(result, 0, 0);
+		length = PQgetlength(result, 0, 0);
+
+		if (pset.gfname && *(pset.gfname) != '\0')
+		{
+			if (!openQueryOutputFile(pset.gfname, , _pipe))
+success = false;
+			if (success && is_pipe)
+disable_sigpipe_trap();
+		}
+
+		if (success)
+		{
+			success = fwrite(value, 1, length, fout != NULL ? fout : pset.queryFout) == length;
+			if (!success)
+psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (success)
+success = fflush(fout != NULL ? fout : pset.queryFout) == 0;
+
+			if (!success)
+psql_error("%s: %s\n", pset.gfname, strerror(errno));
+
+			if (fout != NULL)
+			{
+if (is_pipe)
+{
+	pclose(fout);
+	restore_sigpipe_trap();
+}
+else
+	fclose(fout);
+			}
+		}
+	}
+
+	return success;
+}
 
 /*
  * ExecQueryTuples: assuming query result is OK, execute each query
@@ -1124,6 +1203,8 @@ PrintQueryResults(PGresult *results)
 success = ExecQueryTuples(results);
 			else if (pset.crosstab_flag)
 success = PrintResultsInCrosstab(results);
+			else if (pset.raw_flag)
+success = StoreRawResult(results);
 			else
 success = PrintQueryTuples(results);
 			/* if it's INSERT/UPDATE/DELETE RETURNING, also print status */
@@ -1278,7 +1359,8 @@ SendQuery(const char *query)
 	}
 
 	if (pset.fetch_count <= 0 || pset.gexec_flag ||
-		pset.crosstab_flag || !is_select_command(query))
+		pset.crosstab_flag || !is_select_command(query) ||
+		pset.raw_flag)
 	{
 		/* Default fetch-it-all-and-print mode */
 		instr_time	before,
@@ -1287,7 +1369,16 @@ SendQuery(const char *query)
 		if (pset.timing)
 			INSTR_TIME_SET_CURRENT(before);
 
-		results = PQexec(pset.db, query);
+		if (pset.binary_result)
+			results = PQexecParams(pset.db, query,
+	0,
+	NULL,
+	NULL,
+	NULL,
+	NULL,
+	pset.binary_result);
+		else
+			

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Pavel Stehule]

2016-12-10 7:11 GMT+01:00 Pavel Stehule :

>
>
> 2016-12-10 2:27 GMT+01:00 Jim Nasby :
>
>> 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.


>
> 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  `cat file`
> \set USE_BINARY on
> INSERT INTO tab(id, data) VALUES(1, :::bytea);
>
> B)
> \set  `cat file`
> INSERT INTO tab(id, data) VALUES (1, :x''); -- use bytea escape
>
> C)
> \load_binary  file
> INSERT INTO tab(id, data) VALUES(1, :'');
>
> D)
> \load  file
> \set USE_BINARY on
> INSERT INTO tab(id, data) VALUES(1, :::bytea);
>
> E)
> \set  ``cat file``
> INSERT INTO tab(id, data) VALUES (1, :'');
>
> 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)
>>
>
>

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Pavel Stehule]

2016-12-10 2:27 GMT+01:00 Jim Nasby :

> 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  `cat file`
\set USE_BINARY on
INSERT INTO tab(id, data) VALUES(1, :::bytea);

B)
\set  `cat file`
INSERT INTO tab(id, data) VALUES (1, :x''); -- use bytea escape

C)
\load_binary  file
INSERT INTO tab(id, data) VALUES(1, :'');

D)
\load  file
\set USE_BINARY on
INSERT INTO tab(id, data) VALUES(1, :::bytea);

E)
\set  ``cat file``
INSERT INTO tab(id, data) VALUES (1, :'');

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)
>

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Andrew Dunstan]

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

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Jim Nasby]

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

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Pavel Stehule]

2016-12-09 19:48 GMT+01:00 Oleksandr Shulgin :

> On Dec 9, 2016 18:40, "Pavel Stehule"  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


> --
> Alex
>
>
>

Re: [HACKERS] proposal: psql statements \gstore \gstore_binary (instead COPY RAW)

[Oleksandr Shulgin]

On Dec 9, 2016 18:40, "Pavel Stehule"  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

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

[Pavel Stehule]

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?

Regards

Pavel