Re: 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

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 possibility
>
> What'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

:

2014-11-22 12:24 GMT+01:00 Marko Tiikkaja <marko(at)joh(dot)to>:

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

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

>
> .marko
>
>

2014-11-22 12:26 GMT+01:00 Antonin Houska <ah(at)cybertec(dot)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 possibility
> >
> > What'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

>
> --
> 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(at)postgresql(dot)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

Pavel Stehule wrote
> :
>
> 2014-11-22 12:24 GMT+01:00 Marko Tiikkaja &lt;

> marko@

> &gt;:
>
>> 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?
>>
>
> 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
>
>
>>
>> .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.

2014-11-22 15:18 GMT+01:00 Andrew Dunstan <andrew(at)dunslane(dot)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

>
>
> cheers
>
> andrew
>
>
>