Re: Improve documentation about include_dir parameter.
Hi, when I choose conf files instead of postgresql.conf I needed uncomment "include_dir" parameter in postgresql.conf to work fine. It is a exception over defaults parameters in postgresql.conf. Documentation don't explain that. How can submit improve patch in documentation about that. -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
Confusing information in sections 8.5 and 9.9 (date and time types, functions and operators)
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/datatype-datetime.html Description: Both sections mention syntax TIMESTAMP '2019-01-20 08:00' as valid for input of timestamp data. This seems to work fine in psql, however, this syntax causes Syntax error when used in library access to the server (in my case it is via pg module in NodeJS). After spending half a day looking for error, I learned from the javascript module developer that the mentioned syntax cannot be used for programmatic access. Instead, he recommended to use cast operator form '2019-01-20 08:00'::timestamp. I tested his suggestion (it works, of course) and was looking for some mention or confirmation in the documentation, but found nothing. I think this is a serious issue, especially because the alternative possibility of using ::timestamp is not even mentioned in chapters 8.5 or 9.9. If someone (like me) looks for specific information how to handle date & time literals, they will inevitably fall into the same trap. Since the experienced Syntax error is contrary to what one would expect after reading the SQL language manual, could you please at least add some hyperlink in both sections 8.5 and 9.9 to attract reader's attention to this specific behavior of the database server? Thank you.
Section 4.1.2.7 contains false information
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/sql-syntax-lexical.html Description: QUOTE: The CAST() syntax conforms to SQL. The type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with :: is historical PostgreSQL usage, as is the function-call syntax. UNQUOTE In fact, this is not 100% true. TIMESTAMP 'string' does not work this way. It is mentioned in section 4.2.9 that TIMESTAMP( 'string') cannot be used, but it does not mention TIMESTAMP 'string'. I would really prefer if the respective information could be clear, unequivocal, complete and included in other sections of the manual where it is due, i.e. in sections 8.5 and 9.9 which deal with date & time types and operators.
Re: Section 4.1.2.7 contains false information
PG Doc comments form writes: > QUOTE: > The CAST() syntax conforms to SQL. The type 'string' syntax is a > generalization of the standard: SQL specifies this syntax only for a few > data types, but PostgreSQL allows it for all types. The syntax with :: is > historical PostgreSQL usage, as is the function-call syntax. > UNQUOTE > In fact, this is not 100% true. TIMESTAMP 'string' does not work this way. Looks like it works to me: regression=# select timestamp '2019-04-09 11:49'; timestamp - 2019-04-09 11:49:00 (1 row) If you feel that the documentation is unclear, you need to be clearer about how it's unclear ;-) (Reading between the lines of this complaint and your adjacent one, I kind of suspect that you were trying to use "TIMESTAMP something" where the something wasn't a literal string constant. But surely 4.1.2.7 makes it plain that the discussed syntax is for constants. You might need to read 4.2.9 "Type Casts" instead.) regards, tom lane
Mark a reloption as indexterm
Hi, I'd like to propose to mark reloptions as indexterms, like GUC, so that users can more easily search the pages describing a reloption in document. Attached is the patch which does this. Is this helpful? Thought? Regards, -- Fujii Masao reloption-doc-v1.patch Description: Binary data
Re: Mark a reloption as indexterm
On 2019-Apr-10, Fujii Masao wrote: > Hi, > > I'd like to propose to mark reloptions as indexterms, like GUC, > so that users can more easily search the pages describing > a reloption in document. Attached is the patch which does this. > Is this helpful? Thought? +1 for adding index entries to all reloptions. I'm not sure what you're achieving by splitting the text for some existing index entries in two and putting two words in the that were part of the , though. I'd just put the whole text in (obviously the option name must be the first word of that). -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: asynchronous commit risk window is overly optimistic
On Wed, Mar 20, 2019 at 02:50:21PM -0400, Jeff Janes wrote: > https://www.postgresql.org/docs/current/wal-async-commit.html: > > "If the database crashes during the risk window between an asynchronous commit > and the writing of the transaction's WAL records, then changes made during > that > transaction will be lost. The duration of the risk window is limited because a > background process (the “WAL writer”) flushes unwritten WAL records to disk > every wal_writer_delay milliseconds. The actual maximum duration of the risk > window is three times wal_writer_delay because the WAL writer is designed to > favor writing whole pages at a time during busy periods." > > I think the phrase "actual maximum duration" here is far too reassuring. There > is no guarantee that the kernel will wake WAL writer three times in a row at > the times it requested, or even any other smalish multiple of that time. Even > if the wal_writer does repeatedly wake on schedule and requests a fsync, that > fsync itself can take a very large multiple of wal_writer_delay milliseconds > before it takes effect. > > If your server experiences a sudden power failure during normal operations > with > uncongested IO, then it is very likely that anything asynchronously committed > more than three wal_writer_delay (plus two disk rotations) ago has made it to > disk. But if it crashes for some other reason than a sudden power failure, it > is less likely to be on disk. A stricken server can go wobbly for a long time > before finally falling over. > > Maybe it should be replaced with something less confident, like "Under normal > conditions, the flush will be initiated within three times wal_writer_delay > because the WAL writer is designed to favor writing whole pages at a time > during busy periods." > > Although the whole "because" clause seems to be more inside baseball than is > warranted here. I think we can go with: "Under normal conditions, the flush will be initiated within roughly three times wal_writer_delay". -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
RE: Section 4.1.2.7 contains false information
Dear Tom, it works only in psql, it does not work when you talk to the server
using postgresql protocol. That is my point.
e.g. in the following code the query causes Syntax error (see the text in red).
That means it does not work. Interestingly, when I use $4::timestamp or CAST (
$4 as TIMESTAMP ) the syntax error does not occur.
// contest.ts
import { Pool } from 'pg' ;
/** Class representing database access */
export class Db {
/** PG connection pool */
pool : Pool ;
constructor() {
this.pool = new Pool( {user: 'contest_owner', database: 'contest'} ); //
database name and owner
}
...
/** Create new contest instance record
* @param contestId {string} Identifier of the contest as specified in the
contest log format
* @param contestName {string} Human readable contest name for software user
* @param startUtc{string} Start date and time of the contest in UTC
'-MM-DD hh:mm'
* @param endUtc {string} End date and time of the contest in UTC
'-MM-DD hh:mm'
* @param format {string} Name of the expected log format (Cabrillo,
EDI, ADIF, Garlic)
*/
createContest( contestId: string, periodId: string, contestName : string,
startUtc: string, endUtc: string, format : string ):
Promise {
if( !startUtc.match(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}(\:\d{2})?$/)) {
return Promise.reject('Incorrect format of UTC start date and time,
should be "-MM-DD hh:mm[:ss]"');
}
if( !endUtc.match(/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}(\:\d{2})?$/)) {
return Promise.reject('Incorrect format of UTC end date and time,
should be "-MM-DD hh:mm[:ss]"');
}
return this.pool.query(
"INSERT INTO public.contest (contest_id, period_id, contest_name,
start_ts, end_ts, default_format, status ) "
+ "VALUES ($1, $2, $3, TIMESTAMP $4, TIMESTAMP $5, $6, 'NEW' )
RETURNING contest_key",
[contestId, periodId, contestName, startUtc, endUtc, format]
).then( result => { if( result.rowCount > 0 ) { return
result.rows[0].contest_key } else { return undefined; } });
}
...
}
-Original Message-
From: Tom Lane
Sent: Tuesday, April 9, 2019 5:57 PM
To: jindr...@vavruska.cz
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Section 4.1.2.7 contains false information
PG Doc comments form mailto:nore...@postgresql.org>>
writes:
> QUOTE:
> The CAST() syntax conforms to SQL. The type 'string' syntax is a
> generalization of the standard: SQL specifies this syntax only for a
> few data types, but PostgreSQL allows it for all types. The syntax
> with :: is historical PostgreSQL usage, as is the function-call syntax.
> UNQUOTE
> In fact, this is not 100% true. TIMESTAMP 'string' does not work this way.
Looks like it works to me:
regression=# select timestamp '2019-04-09 11:49';
timestamp
-
2019-04-09 11:49:00
(1 row)
If you feel that the documentation is unclear, you need to be clearer about how
it's unclear ;-)
(Reading between the lines of this complaint and your adjacent one, I kind of
suspect that you were trying to use "TIMESTAMP something"
where the something wasn't a literal string constant. But surely
4.1.2.7 makes it plain that the discussed syntax is for constants.
You might need to read 4.2.9 "Type Casts" instead.)
regards, tom lane
Re: Section 4.1.2.7 contains false information
On Tue, Apr 9, 2019 at 3:03 PM Jind?ich Vavru?ka wrote: > Dear Tom, it works only in psql, it does not work when you talk to the > server using postgresql protocol. That is my point. > > e.g. in the following code the query causes Syntax error (see the text in > red). That means it does not work. Interestingly, when I use $4::timestamp or > CAST ( $4 as TIMESTAMP ) the syntax error does not occur. > > > > "INSERT INTO public.contest (contest_id, period_id, contest_name, > start_ts, end_ts, default_format, status ) " > > + "VALUES ($1, $2, $3, *TIMESTAMP $4*, *TIMESTAMP $5*, $6, 'NEW' > ) RETURNING contest_key", > > > And further on in that section its plainly states: "To avoid syntactic ambiguity, the type 'string' syntax can only be used to specify the type of a simple literal constant." Since what you've provided is not a simple literal constant it is expected to not work - and that either :: or cast() needs to be used instead. If you'd like to make a concrete documentation suggestion please do so. Given the scarcity of complaints seen here, and the fact that not everything can and should be documented everywhere, I'm not presently seeing an issue large enough to change the status quo. Especially since its sounds more like you didn't read the section the explicitly covers the limitations of typename 'literal' and the universality of actual casting syntax (either SQL standard CAST or PostgreSQL double-colon). There is indeed a lot of material here and sometime a quick question on the email lists is more efficient a learning mechanism than trying to get the documentation perfect. This seems like one of those times. David J.
