psql as an execve(2) interpreter
I would like to use pgsl as an interpreter (in the sense of
execve(2)). In short, if a file begins with the line
#! /path/to/psql -f
it should be interpretable by psql. The normal semantics of execve(2)
ensure that this will work perfectly (indeed a file containing
"#!/path/to/psql -l" works as expected), except for psql's nasty habit
of not interpreting the first line as a comment.
It seems that a simple fix to the following function in
src/bin/psql/input.c would do the trick.
char *
gets_fromFile(FILE *source)
{
PQExpBufferData buffer;
char line[1024];
initPQExpBuffer(&buffer);
while (fgets(line, sizeof(line), source) != NULL)
{
appendPQExpBufferStr(&buffer, line);
if (buffer.data[buffer.len - 1] == '\n')
{
buffer.data[buffer.len - 1] = '\0';
return buffer.data;
}
}
if (buffer.len > 0)
return buffer.data; /* EOF after reading some bufferload(s) */
/* EOF, so return null */
termPQExpBuffer(&buffer);
return NULL;
}
For example, this feature could be achieved by 1) including a static
variable to differentiate the first from subsequent calls and 2)
discarding the first line (and returning the second) on the first call
if the first line begins with #!.
Thus, I have two questions.
- Is this a feature that would be generally accepted and useful for
the postgresql community (i.e., would it be incorporated into the
code base)?
- Is this the correct solution or are there other portions of the code
that need to be considered?
I appreciate any feedback you can give me on this.
Thank you very much.
Cheers,
Brook
<brook@biology.nmsu.edu> writes:
I would like to use pgsl as an interpreter (in the sense of
execve(2)). In short, if a file begins with the line
#! /path/to/psql -f
it should be interpretable by psql. The normal semantics of execve(2)
ensure that this will work perfectly (indeed a file containing
"#!/path/to/psql -l" works as expected), except for psql's nasty habit
of not interpreting the first line as a comment.
Given that # is not a comment introducer in SQL, I would consider
it a bug if it did.
You should instead write a shell script that invokes psql.
regards, tom lane
Tom Lane writes:
Given that # is not a comment introducer in SQL, I would consider
it a bug if it did.
I understand that # is not a comment introducer in SQL. I am
wondering if it would be sensible to introduce an exception for the
first line of a file. To prevent problems the behavior should be
controlled by a command line option (-i?) so that it would never have
this behavior unless explicitly asked for.
I guess you see no value in this and instead would solve the issue
with a separate interpreter that has this property? Note that a shell
script cannot be an interpreter for execve(2); thus, this would
require another binary executable.
My own feeling was that psql could be easily taught to have this
behavior in a way that would not interfer with any existing
applications. I at least can see benefits to having that capability,
but perhaps others do not. For example, some of my large database
applications are built by running a large collection of scripts (some
are shell scripts, some sql, etc.), each of which is responsible for a
portion of the task. It would be very handy to execute each member of
the collection in a uniform manner, i.e., as a direct execution with
execve(2) figuring out which interpreter to use on a script-by-script
basis. Currently, that is not possible, but it could be with a small
modification to psql or the addition of a completely new interpreter.
Thanks for the comments.
Cheers,
Brook
Brook,
I have a lot of shell scripts that run as cron jobs and have considered
this option. However, if you look at it carefully, SQL is totally
different from say perl, php, bash, etc. for scripts which execute from
the shell. Tom is right, it is much more valuable and supportable to
call psql from within a shell script than add the functionality to psql
itself.
-Jonah
brook@biology.nmsu.edu wrote:
Show quoted text
Tom Lane writes:
Given that # is not a comment introducer in SQL, I would consider
it a bug if it did.I understand that # is not a comment introducer in SQL. I am
wondering if it would be sensible to introduce an exception for the
first line of a file. To prevent problems the behavior should be
controlled by a command line option (-i?) so that it would never have
this behavior unless explicitly asked for.I guess you see no value in this and instead would solve the issue
with a separate interpreter that has this property? Note that a shell
script cannot be an interpreter for execve(2); thus, this would
require another binary executable.My own feeling was that psql could be easily taught to have this
behavior in a way that would not interfer with any existing
applications. I at least can see benefits to having that capability,
but perhaps others do not. For example, some of my large database
applications are built by running a large collection of scripts (some
are shell scripts, some sql, etc.), each of which is responsible for a
portion of the task. It would be very handy to execute each member of
the collection in a uniform manner, i.e., as a direct execution with
execve(2) figuring out which interpreter to use on a script-by-script
basis. Currently, that is not possible, but it could be with a small
modification to psql or the addition of a completely new interpreter.Thanks for the comments.
Cheers,
Brook---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Jonah,
Thanks for your comments.
Jonah H. Harris writes:
I have a lot of shell scripts that run as cron jobs and have considered
this option. However, if you look at it carefully, SQL is totally
different from say perl, php, bash, etc. for scripts which execute from
the shell. Tom is right, it is much more valuable and supportable to
call psql from within a shell script than add the functionality to psql
itself.
I, too, have thought a lot about this and I suppose we are reaching
different conclusions. I would very much appreciate hearing your
logic, as the underlying reasoning you imply is not transparent to me.
For what it is worth, here is an outline of part of my thinking.
It is true that the nature of SQL as a language is different from
other traditional programming languages, as it does not have concepts
such as variables and flow control. To my way of thinking, however,
that simply dictates what is possible to express in the language.
Importantly, I do not see how it influences how one might wish to
execute the commands given in the language (or any language for that
matter). In my mind the decision about how to execute something is
based on what it does and what the larger context is, not what the
language can express.
Suppose I have a script S in some language L that is interpretable by
some interpreter I. The language L might be SQL and the inrepreter I
might be psql, but nothing that follows depends on that. Indeed, the
language could be perl and the interpreter perl. The fundamental
questions are:
- What are useful ways to have the interpreter I carry out the
instructions contained within S?
- What determines why those are useful?
- How can those means be achieved?
For most scripting languages L (e.g., shell commands, perl, etc.) the
prior art has identified two useful means of having the interpreter
execute the instructions in S: 1) explicit execution (i.e., execute
the interpreter and explicitly pass the appropriate script S to it)
and 2) implicit execution (i.e., execute the script and magically have
the system invoke the interpreter on it). Interpreting SQL scripts
stands out as one exception to this.
Why would one choose one method over another? In all cases that I can
think of, the decision to use one method over another depends entirely
on considerations that are external to the nature of the language L
itself. I would venture to say that they are governed primarily by
the nature of the external interface one is trying to create. In some
cases, depending on what the script actually does, it is much more
natural to invoke a script directly. An example would be one that is
a wrapper to something else, but must take the responsibility for
setting up the environment first. In other cases, the other mechanism
is more natural. The decision does not bear on what the _language_ is
capable of expressing, but rather on what the particular script is
doing and how it fits into a larger external context.
In my mind, the same is true for SQL. In some cases it is appropriate
to execute the interpreter (i.e., psql) explicitly (that is currently
our only option). In other cases it is appropriate to execute it
implicitly. I see no fundamental difference between this and any
other interpreter.
Clearly, an implicit execution mechanism for SQL cannot work quite as
transparently as for languages that use the hash mark (#) as a comment
introducer. However, supporting this option need not interfere with
other uses of the interpreter nor need it be costly. What is required
is 1) a command line option that differentiates traditional behavior
from the "implicit interpreter" behavior, and 2) a modification of how
the first line of the file is handled depending on the mode. No other
changes are required; no interaction with the vast majority of the
code is needed.
Thus, my analysis suggests no fundamental difference between the set
of invocations that are useful for the majority of interpreters and
the set that would be useful for interpreters of SQL. I also can
envision a means of expanding that set for psql that would have no
impact on either its normal use or its ongoing maintenance and
development. Consequently, I see no compelling reason not to move in
this direction. However, I must be missing something obvious, as
there seems to be conflicting sentiment. I would be very interested
to learn more about what is behind those ideas.
Cheers,
Brook