[HACKERS] [trivial patch] grammar fixes in doc/src/sgml/high-availability.sgml
diff --git a/doc/src/sgml/high-availability.sgml b/doc/src/sgml/high-availability.sgml new file mode 100644 index ed34dac..c5f3ff9 *** a/doc/src/sgml/high-availability.sgml --- b/doc/src/sgml/high-availability.sgml *** primary_conninfo = 'host=192.168.1.50 po *** 1022,1028 Users will stop waiting if a fast shutdown is requested. However, as ! when using asynchronous replication, the server will does not fully shutdown until all outstanding WAL records are transferred to the currently connected standby servers. --- 1022,1028 Users will stop waiting if a fast shutdown is requested. However, as ! when using asynchronous replication, the server will not fully shutdown until all outstanding WAL records are transferred to the currently connected standby servers. *** primary_conninfo = 'host=192.168.1.50 po *** 1126,1132 If you need to re-create a standby server while transactions are ! waiting, make sure that the commands to run pg_start_backup() and pg_stop_backup() are run in a session with synchronous_commit = off, otherwise those requests will wait forever for the standby to appear. --- 1126,1132 If you need to re-create a standby server while transactions are ! waiting, make sure that the commands pg_start_backup() and pg_stop_backup() are run in a session with synchronous_commit = off, otherwise those requests will wait forever for the standby to appear. Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [HACKERS] invalid search_path complaints
Re: Tom Lane 2012-04-04 <28647.1333558...@sss.pgh.pa.us> > Now, Scott's comment seems to me to offer a principled way out of this: > if we define the intended semantics of search_path as being similar > to the traditional understanding of Unix PATH, then it's not an error > or even unexpected to have references to nonexistent schemas in there. Btw, the default setting does already work like this: "$user",public. It is not an error for "$user" not to exist, but it is a very nice default because it will be used as soon as it appears. It would be logical to treat all other cases the same. I then could put the search_path into my .psqlrc and then have a "one size fits all" search path for all my databases, etc... > But as soon as you say "I want warnings in some cases", I think we have > a mess that nobody is ever going to be happy with, because there will > never be a clear and correct definition of which cases should get > warnings. As it looks impossible to divide the gray area, I'd opt to just drop the warning and accept all syntactically valid strings. Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION
Re: Peter Eisentraut 2012-02-24 <1330107599.32452.15.ca...@vanquo.pezone.net> > On fre, 2012-02-24 at 11:53 -0500, Tom Lane wrote: > > > We have the same problem with testing extensions at build-time in > > the > > > Debian packages. The server's SHAREDIR /usr/share/postgresql/... is > > > only writable by root, while the build is running as buildd user, so > > > there is no way to do "create extension whatimbuildingrightnow" to > > be > > > able to run regression tests, even if this is a cluster I have just > > > created with initdb. > > > > This seems like nonsense. If the build process has installed the > > software, you surely have got permissions to write in that directory. > > The build process just installs the software in a fake root where it > will be wrapped up by the packaging software. > > > If you haven't installed the software, you need to do testing in a > > temporary installation per "make check", and we are able to test > > extensions that way too. > > > That looks like the right answer. Well, I'm trying to invoke the extension's "make check" target at extension build time. I do have a temporary installation I own somehwere in my $HOME, but that is still trying to find extensions in /usr/share/postgresql/9.1/extension/*.control, because I am using the system's postgresql version. The build process is not running as root, so I cannot do an install of the extension to its final location. Still it would be nice to run regression tests. All that seems to be missing is the ability to put extension_control_path = /home/buildd/tmp/extension into the postgresql.conf of the temporary PG installation, or some other way like "CREATE EXTENSION foobar WITH CONTROL '/home/buildd/...'. Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [HACKERS] Runtime SHAREDIR for testing CREATE EXTENSION
Re: Sandro Santilli 2012-02-22 <20120222101656.GB6125@gnash> > I'm not really looking for "inline extensions". > I do want to install the extension objects somewhere, just NOT > in the PostgreSQL builtin SHAREDIR but in an arbitrary staging > directory to use for QA the extension before distribution. We have the same problem with testing extensions at build-time in the Debian packages. The server's SHAREDIR /usr/share/postgresql/... is only writable by root, while the build is running as buildd user, so there is no way to do "create extension whatimbuildingrightnow" to be able to run regression tests, even if this is a cluster I have just created with initdb. Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [HACKERS] [trivial patch] typo in doc/src/sgml/sepgsql.sgml
Re: Robert Haas 2012-02-15 > Fixed, but note that I had to recreate the patch by manual > examination. Including it inline tends to garble things. Hmm, I thought I had :set paste and everything... > > (It is unclear to me why the same example is cited twice here, but the > > text around them is consistent with that.) > > Fixed this too, and did some related rewording and proofreading. Makes much more sense now. Thanks! Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
[HACKERS] [trivial patch] typo in doc/src/sgml/sepgsql.sgml
diff --git a/doc/src/sgml/sepgsql.sgml b/doc/src/sgml/sepgsql.sgml index e45c258..ee0a255 100644 *** a/doc/src/sgml/sepgsql.sgml --- b/doc/src/sgml/sepgsql.sgml *** UPDATE t1 SET x = 2, y = md5sum(y) WHERE *** 358,364 In this case we must have db_table:select in addition to ! db_table:update, because t1.a is referenced within the WHERE clause. Column-level permissions will also be checked for each referenced column. --- 358,364 In this case we must have db_table:select in addition to ! db_table:update, because t1.z is referenced within the WHERE clause. Column-level permissions will also be checked for each referenced column. (It is unclear to me why the same example is cited twice here, but the text around them is consistent with that.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
Re: [HACKERS] pl/perl and utf-8 in sql_ascii databases
Re: Alex Hunsaker 2012-02-10 > Does the attached fix the issue for you? Yes. :) Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
[HACKERS] pl/perl and utf-8 in sql_ascii databases
Hi,
we have a database that is storing strings in various encodings (and
non-encodings, namely the arbitrary byte soup that you might see in
email headers from the internet). For this reason, the database uses
sql_ascii encoding. The columns are text, as most characters are
ascii, so bytea didn't seem the right way to go.
Currently we are on 8.3 and try to upgrade to 9.1, but the plperlu
functions we have are acting up.
Old behavior on 8.3 .. 9.0:
sql_ascii =# create or replace function whitespace(text) returns text
language plperlu as $$ $a = shift; $a =~ s/[\t ]+/ /g; return $a; $$;
CREATE FUNCTION
sql_ascii =# select whitespace (E'\200'); -- 0x80 is not valid utf-8
whitespace
sql_ascii =# select whitespace (E'\200')::bytea;
whitespace
\x80
New behavior on 9.1.2:
sql_ascii =# select whitespace (E'\200');
ERROR: XX000: Malformed UTF-8 character (fatal) at line 1.
KONTEXT: PL/Perl function "whitespace"
ORT: plperl_call_perl_func, plperl.c:2037
A crude workaround is:
sql_ascii =# create or replace function whitespace_utf8_off(text)
returns text language plperlu as $$ use Encode; $a = shift;
Encode::_utf8_off($a); $a =~ s/[\t ]+/ /g; return $a; $$;
CREATE FUNCTION
sql_ascii =# select whitespace_utf8_off (E'\200');
whitespace_utf8_off
-
\u0080
sql_ascii =# select whitespace_utf8_off (E'\200')::bytea;
whitespace_utf8_off
-
\xc280
(Note that the workaround is not perfect as the resulting 0x80..0xff
bytes are still tagged to be utf8.)
I think the bug is in plperl_helpers.h:
/*
* Create a new SV from a string assumed to be in the current database's
* encoding.
*/
static inline SV *
cstr2sv(const char *str)
{
SV *sv;
char *utf8_str = utf_e2u(str);
sv = newSVpv(utf8_str, 0);
SvUTF8_on(sv);
pfree(utf8_str);
return sv;
}
In sql_ascii databases, utf_e2u does not do any recoding, but then
SvUTF8_on still marks the string as utf-8, while it isn't.
(Returned values might also need fixing.)
In my view, this is clearly a bug in pl/perl on sql_ascii databases.
Christoph
--
c...@df7cb.de | http://www.df7cb.de/
signature.asc
Description: Digital signature
Re: [HACKERS] psql 9.1 alpha5: connection pointer is NULL
Re: Tom Lane 2011-04-03 <1397.1301782...@sss.pgh.pa.us> > Yeah, that's clearly a bug --- fix committed, thanks for the patch! > > It could explain Devrim's report if the parameters passed by psql had > some problem that was detectable by conninfo_array_parse(). That seems > a bit unlikely, but I did think of one possibility: if Devrim was > testing 9.1 psql with a 9.0 libpq (perhaps due to an rpath issue) > then 9.0 libpq would spit up on client_encoding, which wasn't a legal > connection parameter in 9.0. Hi, I'm still seeing that problem: 9.1 HEAD compiled in my $HOME, with Debian's 9.0.1-2 libpq5 in /usr/lib: $ LC_ALL=C bin/psql psql: connection pointer is NULL Upgrading to libpq5 9.0.4-1 makes things a bit better: $ LC_ALL=C bin/psql psql: invalid connection option "client_encoding" Setting LD_LIBRARY_PATH fixes it. Arguably, this is not the "standard" setup, but one that will probably be quite frequent for someone trying 9.1 in their ~. Shouldn't psql try to work with older libpq versions by omitting client_encoding? Setting an RPATH seems like an ugly solution. (I'm not arguing for a SONAME bump, but this is kind of an ABI change.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
[HACKERS] psql patch: tab-complete :variables also at buffer start
Currently, tab-completing :variable names in psql does not work at the
beginning of the line. Fix this by moving the code block before the
"empty buffer" case.
(I have several "sql macros" in my .psqlrc like :relsize that prints
table sizes in a nicely formatted way, being able to type : would
be very welcome.)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 1c9623d..a540912 100644
*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(char *text, int start, i
*** 761,766
--- 761,777
if (text[0] == '\\')
COMPLETE_WITH_LIST(backslash_commands);
+ /* Variable interpolation */
+ else if (text[0] == ':' && text[1] != ':')
+ {
+ if (text[1] == '\'')
+ matches = complete_from_variables(text, ":'", "'");
+ else if (text[1] == '"')
+ matches = complete_from_variables(text, ":\"", "\"");
+ else
+ matches = complete_from_variables(text, ":", "");
+ }
+
/* If no previous word, suggest one of the basic sql commands */
else if (!prev_wd)
COMPLETE_WITH_LIST(sql_commands);
*** psql_completion(char *text, int start, i
*** 2767,2783
)
matches = completion_matches(text,
filename_completion_function);
- /* Variable interpolation */
- else if (text[0] == ':' && text[1] != ':')
- {
- if (text[1] == '\'')
- matches = complete_from_variables(text, ":'", "'");
- else if (text[1] == '"')
- matches = complete_from_variables(text, ":\"", "\"");
- else
- matches = complete_from_variables(text, ":", "");
- }
-
/*
* Finally, we look through the list of "things", such as TABLE, INDEX
and
* check if that was the previous word. If so, execute the query to get
a
--- 2778,2783
Christoph
--
c...@df7cb.de | http://www.df7cb.de/
signature.asc
Description: Digital signature
[HACKERS] [trivial patch] Ellipsis whitespace in SQL docs
Hi,
here's a trivial patch:
Changed several occurrences of ",..." to ", ..." in SQL syntax to be
consistent with the rest.
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml
b/doc/src/sgml/ref/alter_default_privileges.sgml
index e1aa293..c27466f 100644
*** a/doc/src/sgml/ref/alter_default_privileges.sgml
--- b/doc/src/sgml/ref/alter_default_privileges.sgml
*** ALTER DEFAULT PRIVILEGES
*** 29,40
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [,...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- 29,40
where abbreviated_grant_or_revoke is one of:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
*** GRANT { EXECUTE | ALL [ PRIVILEGES ] }
*** 44,57
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
! [,...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
--- 44,57
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
! [, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] role_name |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
! [, ...] | ALL [ PRIVILEGES ] }
ON SEQUENCES
FROM { [ GROUP ] role_name |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 082e2e8..72ecc45 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*** PostgreSQL documentation
*** 22,44
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [,...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [,
...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
! [,...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] table_name [,
...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [,...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name
[, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
! GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- 22,44
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
! [, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [,
...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column [, ...] )
! [, ...] | ALL [ PRIVILEGES ] ( column [, ...] ) }
ON [ TABLE ] table_name [,
...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
! [, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name
[, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
! GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
*** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 59,69
ON LANGUAGE lang_name [, ...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
! GRANT { { SELECT | UPDATE } [,...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO { [ GROUP ] role_name |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
! GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { [ GROUP ] role_name |
PU
Re: [HACKERS] per-user pg_service.conf
> > I was surprised/annoyed to find out that there is no way to have > > per-user pg_service.conf, something like ~/.pg_service.conf (well, > > except by export PGSYSCONFDIR). That would be easy to add. > > Comments? > > Here's a patch. Perhaps those who had said they would like that can > validate the behavior. Hi, I just tried the ~/.pg_service.conf patch and it does everything I'd expect from it. It even improves the documentation to include a services file example for which I had been looking several times earlier. There's not much I have to add, maybe the documentation could add a pointer to what keywords are recognized: | The file uses an "INI file" format where the section name is the | service name and the parameters are connection parameters. ... (see Section 30.1 for a list). Independently for what this patch changes, error reporting could be more detailed, currently "syntax error in service file \"%s\", line %d" is reported for "no = in line" and "keyword X is unknown". The latter case deserves a different message, maybe like "keyword \"%s\" is invalid in service file \"%s\", line %d". Even without the proposed changed, I'd very much appreciate the patch getting included. Christoph -- c...@df7cb.de | http://www.df7cb.de/ signature.asc Description: Digital signature
