\dn [PATTERN] handling not quite right...
I haven't looked in great detail into why this is happpening, but it
seems as though processNamePattern() doesn't handle ?'s correctly in
the negative lookahead context correctly.
1) \dn [pattern] strips ?'s and replaces them with periods. This may
be intentional (as the comment in describe.c suggests, converting input
from shell-style wildcards gets converted into regexp notation), but is
quite annoying. Ex:
test=# \dn foo(?!_log|_shadow)
********* QUERY **********
SELECT n.nspname AS "Name",
u.usename AS "Owner"
FROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_user u
ON n.nspowner=u.usesysid
WHERE (n.nspname NOT LIKE 'pg\\_temp\\_%' OR
n.nspname = (pg_catalog.current_schemas(true))[1])
AND n.nspname ~ '^foo(.!_log|_shadow)$'
ORDER BY 1;
**************************
Which is incorrect, IMHO. Instead the last bit of the query should be:
AND n.nspname ~ '^foo(?!_log|_shadow)$'
2) This brings up a large deficiency with the way that \d? [pattern]
handling is done in psql(1). It'd be slick if there was a way to have
psql's pattern routine look at the first non-whitespace character or
two to change change the structure of the query. Something like \dn
!.*_shadow% would change the RE operator from ~ to !~ and \dn %bar%
would translate to LIKE('bar%'). Doing the regexp equiv of
!LIKE('%_shadow') isn't trivial because '^.*(?!_shadow)$' doesn't
return the expected result for various reasons. Oh! This'd be a "gun
pointed at foot" feature, but having the first character being an =
would, without escaping, drop the remainder of the input directly into
the query (ex: \dn =nspname != (LIKE('%_log') OR LIKE('%_shadow'))).
Maybe a psql(1) variable that changes the behavior of the pattern
queries from using an RE to a LIKE statement could also be a
possibility. The more I think about this, a leading pipe could be used
to pipe the output to a utility, so that \dn | egrep -v '(log|shadow)
would work and would be the easiest solution.
Maybe a better "bug report" would be, what's the suggested way of doing:
n.nspname !~ '_(log|shadow)$'?
from a list pattern?
-sc
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
I haven't looked in great detail into why this is happpening, but it
seems as though processNamePattern() doesn't handle ?'s correctly in
the negative lookahead context correctly.
Negative lookahead context!? You are several sigmas beyond the subset
of regex functionality that \d and friends are intended to support.
Given that we're defining * and ? as shell-style wildcards, it's not
going to be feasible to handle very much of ordinary regex usage let
alone advanced.
The more I think about this, a leading pipe could be used
to pipe the output to a utility, so that \dn | egrep -v '(log|shadow)
would work and would be the easiest solution.
This on the other hand seems more like a potentially useful feature,
although I'm unclear on what you expect to get sent through the pipe
exactly --- you want column headers for instance? What if you're using
a nondefault display layout?
regards, tom lane
I haven't looked in great detail into why this is happpening, but it
seems as though processNamePattern() doesn't handle ?'s correctly in
the negative lookahead context correctly.Negative lookahead context!? You are several sigmas beyond the subset
of regex functionality that \d and friends are intended to support.
Given that we're defining * and ? as shell-style wildcards, it's not
going to be feasible to handle very much of ordinary regex usage let
alone advanced.
I was worried you'd say as much. I'm in a situation where I've got a
few hundred schemas floating around now and about half of them end with
_log or _shadow and I was surprised at how non-trivial it was to filter
out the _log or _shadow schemas with \dn. I tried thinking up the psql
equiv of tcsh's fignore but had no luck (ex: set fignore = (\~ .o
.bak)).
The more I think about this, a leading pipe could be used
to pipe the output to a utility, so that \dn | egrep -v '(log|shadow)
would work and would be the easiest solution.This on the other hand seems more like a potentially useful feature,
although I'm unclear on what you expect to get sent through the pipe
exactly --- you want column headers for instance? What if you're using
a nondefault display layout?
Instead of using printf(), fprintf(), fwrite(), or whatever it is that
psql(1) uses internally for displaying result sets, have it use the
following chunk of pseudo code:
if (pipe_symbol_found) {
char buf[8192];
size_t len = 0;
memset(&buf, 0, sizeof(buf));
fh = popen(..., "r+"); /* or setsocketpair() + fork() */
fwrite(formatted_output_buffer, strlen(formatted_output_buffer), 1,
fh);
while((len = read(fileno(fh), buf, sizeof(buf)))) {
fwrite(buf, len, 1, stdout);
}
} else {
/* whatever the current code does */
}
That doesn't take into account the set option that lets you write the
output to a file, but that is trivial to handle. To answer your
question, I'd send _everything_ through the pipe and use the pipe as a
blanket IO filter. I haven't thought about this, but would it be
possible to hand the data off to sh(1) and have it handle the
pipe/redirection foo that way psql doesn't have to have any
pipe/redirection brains? If so, I think that'd be slick since you
could do things like '\dn | tail -n +3 | grep -v blah' to handle your
concern about having the header sent through and a utility not wanting
it.
Too bad tee(1) doesn't support a -p option to have tee(1)'s argument
sent to sh(1) or a pipe instead of a file, then there'd be some real
interesting things that one could script. Ex:
\dn | tee -p 'head -n 3 >> /dev/stdout' | tail -n +3 | egrep -v
'_(log|shadow)$'
Which'd show you the header, but everything after the header would be
sent to egrep(1). I can't understand why win32 users think *NIX's CLI
can be confusing.... *grin*.
Just some thoughts. -sc
--
Sean Chittenden