ALTER SYSTEM between upgrades

[Bruce Momjian]

On Tue, Jul  7, 2020 at 04:18:21PM -0400, Alvaro Herrera wrote:
> On 2020-Jul-07, Amit Kapila wrote:
> 
> > I don't think this is true.  We seem to have introduced three new guc
> > variables in a 9.3.3 minor release.
> 
> Yeah, backporting GUCs is not a big deal.  Sure, the GUC won't appear in
> postgresql.conf files generated by initdb prior to the release that
> introduces it.  But users that need it can just edit their .confs and
> add the appropriate line, or just do ALTER SYSTEM after the minor
> upgrade.  For people that don't need it, it would have a reasonable
> default (probably work_mem, so that behavior doesn't change on the minor
> upgrade).

I am creating a new thread to discuss the question raised by Alvaro of
how many ALTER SYSTEM settings are lost during major upgrades.  Do we
properly document that users should migrate their postgresql.conf _and_
postgresql.auto.conf files during major upgrades?  I personally never
thought of this until now.

-- 
  Bruce Momjian  https://momjian.us
  EnterpriseDB https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Re: TIMESTAMP

[Tom Lane]

J Lumby  writes:
> I am curious about the syntactic nature of the expression :
> TIMESTAMP '2020-07-13'
> in this expression, is the token TIMESTAMP a function, typecast,  or other?
> I don't see a description of it as a function and there are no 
> parentheses in this example.

This construct is a typed constant, as described at
"4.1.2.7. Constants Of Other Types" on this page:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

It's a wart, frankly, and we wouldn't have it if the SQL standard
didn't insist.  Of the three syntaxes described by 4.1.2.7:

type 'string'
'string'::type
CAST ( 'string' AS type )

the second and third actually work for casting any expression,
but the first one *only* works for a literal-string constant.
People are constantly burnt by that, for example by expecting
that they can write

TIMESTAMP $1

You can plug a parameter symbol into the other two syntaxes,
but not that one.  On the whole I prefer using 'string'::type,
which is slightly more typing effort, but it generalizes correctly.

> I also tried
> FLOAT8 '7.3'
> and that is accepted as well.

IIRC, the SQL standard only defines this syntax for certain types
such as TIMESTAMP and INTERVAL; but Postgres allows it for any
type name.

regards, tom lane

TIMESTAMP

[J Lumby]

I am curious about the syntactic nature of the expression :


TIMESTAMP '2020-07-13'


in this expression,  is the token TIMESTAMP a function, typecast,  or other?

I don't see a description of it as a function and there are no 
parentheses in this example.


But this expression also doesn't seem to conform to the described forms 
for a typecast.


I do see this text in the description of typecast :

-

It is also possible to specify a type cast using a function-like syntax:

typename ( expression )

- 



which also lists some types which can be used this way,  including 
timestamp, but this description implies that parentheses are mandatory 
and also says that in the case of timestamp the name must be double-quoted.



I also tried

FLOAT8 '7.3'

and that is accepted as well. So maybe the above syntax description 
should indicate that parentheses are optional?



Or if not,  where in the documentation is this syntax described?



Cheers,   John Lumby

Re: docs: psql and variable interpolation

[David G. Johnston]

On Monday, July 13, 2020, Philippe Beaudoin 
wrote:
>
> The first sentence of this SQL interpolation chapter says "A key feature
> of psql variables is that you can substitute (“interpolate”) them into
> regular SQL statements, as well as the arguments of meta-commands." But
> nothing in this chapter indicates that there are exceptions.
>
> May be we could have a wording adjustment with something like : "A key
> feature of psql variables is that you can substitute (“interpolate”) them
> into regular SQL statements, as well as the arguments of meta-commands
> (unless specifically noted)."
>

Having “unless otherwise noted” be implied doesn’t seem like a big
failing.  As for this case it is documented as being an exception:

“Unlike most other meta-commands, the entire remainder of the line is
always taken to be the arguments of \copy, and neither variable
interpolation nor backquote expansion are performed in the arguments.”

If it is only \copy that is an exception I’d rather just say (except \copy}
and not have the user meticulously read each item for the single
exception.  This extends to if there are a few.

David J.

Re: docs: psql and variable interpolation

[Pavel Stehule]

po 13. 7. 2020 v 15:09 odesílatel Philippe Beaudoin <
philippe.beaud...@dalibo.com> napsal:

> Hi all,
>
> I recently used the nice variable capabilities in psql, after having read
> the "SQL Interpolation" chapter in the psql page (
> https://www.postgresql.org/docs/12/app-psql.html).
>
> But I spent a lot of time trying to understand why using a variable in a
> \copy command failed ... until a colleague of mine showed me this was
> actually written elsewhere in the documentation (formerly in the \copy
> chapter).
>
> The first sentence of this SQL interpolation chapter says "A key feature
> of psql variables is that you can substitute (“interpolate”) them into
> regular SQL statements, as well as the arguments of meta-commands." But
> nothing in this chapter indicates that there are exceptions.
>
> May be we could have a wording adjustment with something like : "A key
> feature of psql variables is that you can substitute (“interpolate”) them
> into regular SQL statements, as well as the arguments of meta-commands
> (unless specifically noted)."
>
+1

or maybe better - if it is possible reduce a exceptions

Pavel


> Regards. Philippe.
> --
> 
> *DALIBO*
> *L'expertise PostgreSQL*
> 43, rue du Faubourg Montmartre
> 75009 Paris *Philippe Beaudoin*
> *Consultant Avant-Vente*
> +33 (0)1 84 72 76 11
> +33 (0)7 69 14 67 21
> philippe.beaud...@dalibo.com
> Valorisez vos compétences PostgreSQL, certifiez-vous chez Dalibo
>  !
>

docs: psql and variable interpolation

[Philippe Beaudoin]

Hi all,

I recently used the nice variable capabilities in psql, after having 
read the "SQL Interpolation" chapter in the psql page 
(https://www.postgresql.org/docs/12/app-psql.html).


But I spent a lot of time trying to understand why using a variable in a 
\copy command failed ... until a colleague of mine showed me this was 
actually written elsewhere in the documentation (formerly in the \copy 
chapter).


The first sentence of this SQL interpolation chapter says "A key feature 
of psql variables is that you can substitute (“interpolate”) them into 
regular SQL statements, as well as the arguments of meta-commands." But 
nothing in this chapter indicates that there are exceptions.


May be we could have a wording adjustment with something like : "A key 
feature of psql variables is that you can substitute (“interpolate”) 
them into regular SQL statements, as well as the arguments of 
meta-commands (unless specifically noted)."


Regards. Philippe.



*DALIBO*
*L'expertise PostgreSQL*
43, rue du Faubourg Montmartre
75009 Paris *Philippe Beaudoin*
*Consultant Avant-Vente*
+33 (0)1 84 72 76 11
+33 (0)7 69 14 67 21
philippe.beaud...@dalibo.com
Valorisez vos compétences PostgreSQL, certifiez-vous chez Dalibo 
 !

Re: Additional Chapter for Tutorial

[Daniel Gustafsson]

> On 13 Jul 2020, at 14:20, Naresh gandi  wrote:

(please avoid top-posting)

> Which version is this application for?
> 
> I tried for v12 and v13 Beta, both failed.

Unless being a bugfix, all patches are only considered against the main
development branch in Git. As this is new material, it would be for v14.

cheers ./daniel

Re: Additional Chapter for Tutorial

[Naresh gandi]

Which version is this application for?

I tried for v12 and v13 Beta, both failed.

Regards,
Naresh G

On Mon, Jul 13, 2020 at 11:45 AM Jürgen Purtz  wrote:

>
> On 12.07.20 22:45, Daniel Gustafsson wrote:
> > This patch no longer applies, due to conflicts in start.sgml, can you
> please
> > submit a rebased version?
>
> ok. but I need some days.  juergen
>
>
>
>
>

Re: initdb - creating clusters

[Daniel Gustafsson]

> On 12 Jul 2020, at 00:24, Tom Lane  wrote:
> 
> Daniel Gustafsson  writes:
>>> On 11 Jul 2020, at 23:36, Tom Lane  wrote:
>>> +  For example, there may be special scripts for creating a database
>>> +  cluster.  There almost certainly will be a mechanism for starting
>>> +  the server,
> 
>> Aren't we really talking about "running the server as a service" and not just
>> starting it?  Perhaps thats hair-splitting territory?
> 
> Yeah, but that terminology might itself be a bit platform-specific.

I guess thats a good point.

> I considered giving specific examples, like systemd unit files,
> but was afraid that that'd just confuse people on other platforms.
> Not sure what the best way to approach this is.

Hmm, since the section is aimed at reducing confusion for inexperienced users I
agree that adding more detail might be detrimental to the point.

Re-reading it with bug-reports etc in mind, I think the only thing that I
propose would be to expand the terminology for what a package is to be
"pre-packaged or vendor-supplied".

cheers ./daniel

Re: Additional Chapter for Tutorial

[Jürgen Purtz]

On 12.07.20 22:45, Daniel Gustafsson wrote:

This patch no longer applies, due to conflicts in start.sgml, can you please
submit a rebased version?


ok. but I need some days.  juergen