some ideas from users
Hi
I did a week training and here are some ideas from people, who are starting
with Postgres.
1. possibility to set server side variables simply as psql option.
Motivation - more simple and natural changing datestyle for psql in shell
scripts. "--set" is allocated now, but theoretically we can use any unknown
long option as server side session variable.
echo "select ..." | psql --datestyle=YMD --enable_seqscan=off
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibility
It should not be difficult:
CREATE OR REPLACE FUNCTION public.settings(OUT name text, OUT setting text,
OUT description text)
RETURNS SETOF record
LANGUAGE plpgsql
AS $function$
BEGIN
RETURN QUERY EXECUTE 'SHOW ALL'
RETURN;
END;
$function$
Usage:
postgres=# select * from settings() where name like '%checkpoint%';
name | setting
|
description
------------------------------+---------+------------------------------------------------------------------------------------------
checkpoint_completion_target | 0.5 | Time spent flushing dirty buffers
during checkpoint, as fraction of checkpoint interval.
checkpoint_segments | 3 | Sets the maximum distance in log
segments between automatic WAL checkpoints.
checkpoint_timeout | 5min | Sets the maximum time between
automatic WAL checkpoints.
checkpoint_warning | 30s | Enables warnings if checkpoint
segments are filled more frequently than this.
log_checkpoints | off | Logs each checkpoint.
(5 rows)
Regards
Pavel
On 2014-11-22 12:20 PM, Pavel Stehule wrote:
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibility
What's wrong with pg_settings?
.marko
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/22/2014 12:24 PM, Marko Tiikkaja wrote:
On 2014-11-22 12:20 PM, Pavel Stehule wrote:
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibilityWhat's wrong with pg_settings?
Do you mean pg_show_all_settings() ?
--
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
:
2014-11-22 12:24 GMT+01:00 Marko Tiikkaja <marko@joh.to>:
On 2014-11-22 12:20 PM, Pavel Stehule wrote:
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibilityWhat's wrong with pg_settings?
nothing, I didn't find it in doc
http://www.postgresql.org/docs/9.4/static/functions-admin.html. My mistake.
Maybe near doc "current_setting" can be link
Thank you
Pavel
Show quoted text
.marko
2014-11-22 12:26 GMT+01:00 Antonin Houska <ah@cybertec.at>:
On 11/22/2014 12:24 PM, Marko Tiikkaja wrote:
On 2014-11-22 12:20 PM, Pavel Stehule wrote:
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibilityWhat's wrong with pg_settings?
Do you mean pg_show_all_settings() ?
one is view, second is function, but yesterday I didn't find any
Pavel
Show quoted text
--
Antonin Houska
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de, http://www.cybertec.at--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 11/22/2014 06:20 AM, Pavel Stehule wrote:
Hi
I did a week training and here are some ideas from people, who are
starting with Postgres.1. possibility to set server side variables simply as psql option.
Motivation - more simple and natural changing datestyle for psql in
shell scripts. "--set" is allocated now, but theoretically we can use
any unknown long option as server side session variable.echo "select ..." | psql --datestyle=YMD --enable_seqscan=off
PGOPTIONS=--enable_seqscan=off psql ....
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
Pavel Stehule wrote
:
2014-11-22 12:24 GMT+01:00 Marko Tiikkaja <
marko@
>:
On 2014-11-22 12:20 PM, Pavel Stehule wrote:
2. missing table function with all settings. Like SHOW ALL, but with
filtering possibilityWhat's wrong with pg_settings?
nothing, I didn't find it in doc
http://www.postgresql.org/docs/9.4/static/functions-admin.html. My
mistake.
Maybe near doc "current_setting" can be linkThank you
Pavel
.marko
It might be worth adding this sentence to the 9.26.1 first paragraph:
"There are also SQL commands and a related view for managing settings. See
18.1 for additional information."
Or, after "it corresponds to [...] SHOW and SELECT * FROM pg_settings". The
set_config can also be stated to correspond to UPDATE pg_settings.
We end up being repetitive with section 18.1 but it is only a single
sentence and focused for someone looking at functions.
David J.
--
View this message in context: http://postgresql.nabble.com/some-ideas-from-users-tp5827943p5827956.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
2014-11-22 15:18 GMT+01:00 Andrew Dunstan <andrew@dunslane.net>:
On 11/22/2014 06:20 AM, Pavel Stehule wrote:
Hi
I did a week training and here are some ideas from people, who are
starting with Postgres.1. possibility to set server side variables simply as psql option.
Motivation - more simple and natural changing datestyle for psql in shell
scripts. "--set" is allocated now, but theoretically we can use any unknown
long option as server side session variable.echo "select ..." | psql --datestyle=YMD --enable_seqscan=off
PGOPTIONS=--enable_seqscan=off psql ....
ok, thank you for info. I have to add this option to --help.
Regards
Pavel
Show quoted text
cheers
andrew