Re: [BUGS] BUG #6183: FATAL: canceling authentication due to timeout
On 8/29/11 5:50 PM, Tom Lane wrote:
> "Thorvald Natvig" writes:
>> We get a lot of "FATAL: canceling authentication due to timeout" in the
>> log, with accompanying closed connections to clients.
> Well, the only known cause of that (other than genuine timeout
> conditions) is in fact fixed in 9.1rc1. You have not provided any
> information that would permit anyone to look for another cause.
This is a database server with fairly high traffic to multiple
databases. It seems to be related to multiple concurrent connections,
but I haven't had time to isolate a repeatable minimal testcase yet. I
was hoping that whatever was wrong was related to something obvious, or
that someone else had seen similar issues and were able to help with
isolating it.
Since this artifact is influencing the usability of the machine, I've
disabled the issuing of 'vacuumdb' for now (which "fixes" the issue).
>> There does indeed seem to be a correlation between doing vacuum and seeing
>> this error.
> Are you doing VACUUM FULLs on pg_authid (and if so, why)? If you are,
> is it possible that those are queuing up behind other queries that
> access pg_authid, and for some reason aren't releasing their locks
> promptly?
>
> regards, tom lane
Databases are created from plain-text backups with createdb and psql,
minimal modifications are done to a few rows, and then
vacuumdb -q -z ${db}
A bit later, this database is renamed, a copy of it is created with
'createdb -T olddb newdb', a lot of deletions (between 0 and 90% of the
rows) are performed and then
vacuumdb -q -f -z ${newdb}
The script doing this is run from several machines working on different
databases, all hosted on the same server. So it's possible there are
multiple full vacuums issued at the same time. However, there are no
users connected to the databases being vacuumed during this time, but
there are hundreds of connections to other databases on the same server;
these are the ones that fail. All of these databases have at one point
been created with -T on a database from the above process. As far as I
know, there are no direct queries to pg_ tables. All operations are
performed over tcp with the same user.
I don't know if this helps with where to look. If it doesn't, I'll try
to make a repeatable testcase on the weekend, when this server isn't
quite so essential.
Regards,
Thorvald
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6183: FATAL: canceling authentication due to timeout
"Thorvald Natvig" writes: > We get a lot of "FATAL: canceling authentication due to timeout" in the > log, with accompanying closed connections to clients. Well, the only known cause of that (other than genuine timeout conditions) is in fact fixed in 9.1rc1. You have not provided any information that would permit anyone to look for another cause. > There does indeed seem to be a correlation between doing vacuum and seeing > this error. Are you doing VACUUM FULLs on pg_authid (and if so, why)? If you are, is it possible that those are queuing up behind other queries that access pg_authid, and for some reason aren't releasing their locks promptly? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6182: /etc/init.d/postgresql-8.4 is incomplete for chkconfig
"Steven Williams" writes: > Description:/etc/init.d/postgresql-8.4 is incomplete for chkconfig This file is not distributed by us. You probably need to contact the SUSE packager of postgresql. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6182: /etc/init.d/postgresql-8.4 is incomplete for chkconfig
On Mon, Aug 29, 2011 at 1:19 PM, Steven Williams wrote: > > The following bug has been logged online: > > Bug reference: 6182 > Logged by: Steven Williams > Email address: stwilli...@novell.com > PostgreSQL version: 8.5.3 > I'm guessing you mean 8.4.3? > Operating system: SuSE Linux 11 > Description:/etc/init.d/postgresql-8.4 is incomplete for chkconfig > Details: > > currently in the script, there is the following: > > # chkconfig: 2345 85 15 > # description: Starts and stops the PostgreSQL 8.4 database server > > > The following also needs to be added: > > ### BEGIN INIT INFO > # Provides: PostgreSQL > # Required-Start: > # Required-Stop: > # Default-Start: 3 5 > # Default-Stop: 0 1 6 > # Description: Start/Stop Script for PostgreSQL 8.4 database server > ### END INIT INFO > > Where did you download postgres from / how did you install it? --Scott > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
[BUGS] BUG #6183: FATAL: canceling authentication due to timeout
The following bug has been logged online: Bug reference: 6183 Logged by: Thorvald Natvig Email address: thorv...@medallia.com PostgreSQL version: 9.1rc1 Operating system: RHEL6 Description:FATAL: canceling authentication due to timeout Details: We get a lot of "FATAL: canceling authentication due to timeout" in the log, with accompanying closed connections to clients. We first saw this on 9.0.4. Googling around, I saw a reference on postgresql-hackers to http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=592b615d71 caac8a3504276a805a6fd024c40041 There does indeed seem to be a correlation between doing vacuum and seeing this error. Seeing as that commit was included in 9.1rc1, we tried upgrading to 9.1rc1 doing a full dump/restore. However, the exact same problem still remains. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6182: /etc/init.d/postgresql-8.4 is incomplete for chkconfig
The following bug has been logged online: Bug reference: 6182 Logged by: Steven Williams Email address: stwilli...@novell.com PostgreSQL version: 8.5.3 Operating system: SuSE Linux 11 Description:/etc/init.d/postgresql-8.4 is incomplete for chkconfig Details: currently in the script, there is the following: # chkconfig: 2345 85 15 # description: Starts and stops the PostgreSQL 8.4 database server The following also needs to be added: ### BEGIN INIT INFO # Provides: PostgreSQL # Required-Start: # Required-Stop: # Default-Start: 3 5 # Default-Stop: 0 1 6 # Description: Start/Stop Script for PostgreSQL 8.4 database server ### END INIT INFO -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
On Mon, Aug 29, 2011 at 9:00 AM, Kevin Grittner wrote: > Merlin Moncure wrote: > >> yeah, that's the correct way, but why does this work? >> select val from random() as val; > > If you look at the PostgreSQL reference docs for the SELECT > statement, a from_item can be a SELECT statement in parentheses or a > function call (among other things). It cannot be an arbitrary > expression containing operators (like ::). right -- duh. I knew that...the canonical case for this is the definition of pg_locks view. I've just never seen it used for a regular scalar function. The whole thing is pretty peculiar IMO (but useful occasionally). merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
Alexey Klyukin writes: > On Aug 29, 2011, at 5:47 PM, Tom Lane wrote: >> There is a dump/reload bug though :-( ... if you were to do >> >> create view vv as select val from CAST(random() as integer) as val; >> >> you will find that the system prints it out with the :: syntax, >> which won't work. > Would it be acceptable/sufficient to output CAST(...) instead of '::' for > all casts in pg_dump to fix this problem, assuming that CAST can be used > anywhere where '::' is accepted? I'm not really excited about that; CAST is more verbose and not particularly more readable (at least IMO). What I was wondering about was altering the internal representation to remember which format had been used, and reverse-listing in that same format. That would both fix this issue, and please users who have a stylistic preference for one or the other format. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6181: concat_ws() incompatibility with MySQL
"Itagaki Takahiro" writes:
> For query concat_ws('/', NULL, 'X'),
> PostgreSQL 9.1 returns '/X', but MySQL returns 'X'.
> I think postgres should return 'X' because of compatibility.
> Another reason is that both of the two queries below return 'X'.
> - concat_ws('/', 'X', NULL)
> - array_to_string(ARRAY[NULL, 'X'], '/')
Yeah, I think you're right. Fortunately it's not too late to change
this without introducing backwards-compatibility issues of our own.
Will fix.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
On Aug 29, 2011, at 5:47 PM, Tom Lane wrote: > "Kevin Grittner" writes: >> Merlin Moncure wrote: >>> yeah, that's the correct way, but why does this work? >>> select val from random() as val; > >> If you look at the PostgreSQL reference docs for the SELECT >> statement, a from_item can be a SELECT statement in parentheses or a >> function call (among other things). It cannot be an arbitrary >> expression containing operators (like ::). > > Right. We also accept things that look syntactically like function > calls, so as to avoid debates with newbies about whether, say, > COALESCE(...) is a function or not. CAST() falls into that category, > while :: doesn't. I was wondering exactly why CAST() is permitted, while it's not a function (in Kevin's example, texteq is a function) and the explanation above answers my question. > > There is actually a practical reason for this policy beyond the question > of whether CAST is a function call or not: the structure name(...) has > a well-defined syntactic extent, so there are no issues of operator > precedence to worry about when it's embedded in a larger construct. > IIRC, we ran into exactly that problem with the CREATE INDEX syntax, > which is why an expression index column has to be parenthesized unless > it looks like a function call. > > So IMO there is no syntax bug here. I agree, thank you and Kevin for the great explanation! > There is a dump/reload bug though :-( ... if you were to do > > create view vv as select val from CAST(random() as integer) as val; > > you will find that the system prints it out with the :: syntax, > which won't work. Would it be acceptable/sufficient to output CAST(...) instead of '::' for all casts in pg_dump to fix this problem, assuming that CAST can be used anywhere where '::' is accepted? -- Alexey Klyukinhttp://www.commandprompt.com The PostgreSQL Company – Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
"Kevin Grittner" writes: > Merlin Moncure wrote: >> yeah, that's the correct way, but why does this work? >> select val from random() as val; > If you look at the PostgreSQL reference docs for the SELECT > statement, a from_item can be a SELECT statement in parentheses or a > function call (among other things). It cannot be an arbitrary > expression containing operators (like ::). Right. We also accept things that look syntactically like function calls, so as to avoid debates with newbies about whether, say, COALESCE(...) is a function or not. CAST() falls into that category, while :: doesn't. There is actually a practical reason for this policy beyond the question of whether CAST is a function call or not: the structure name(...) has a well-defined syntactic extent, so there are no issues of operator precedence to worry about when it's embedded in a larger construct. IIRC, we ran into exactly that problem with the CREATE INDEX syntax, which is why an expression index column has to be parenthesized unless it looks like a function call. So IMO there is no syntax bug here. There is a dump/reload bug though :-( ... if you were to do create view vv as select val from CAST(random() as integer) as val; you will find that the system prints it out with the :: syntax, which won't work. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
"Kevin Grittner" wrote: > test=# select val from (select 'a' = 'a') as val; > val > - > (t) > (1 row) Also note the difference between a record and a scalar here. I forgot to write it to return val as a scalar, which seems to be what you're after. It should have been: test=# select val from (select 'a' = 'a') as x(val); val - t (1 row) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
Alexey Klyukin wrote:
> On Aug 29, 2011, at 5:02 PM, Kevin Grittner wrote:
>
>> Alexey Klyukin wrote:
>>
>>> Function calls can appear in the FROM clause. (This is
>>> especially useful for functions that return result sets, but any
>>> function can be used.) This acts as though its output were
>>> created as a temporary table for the duration of this single
>>> SELECT command.
>>
>> It doesn't say that operators which provide equivalent
>> functionality to functions can also be used.
>
> I agree, but why is it possible to use the type casting with CAST
> there?
Because the syntax is that of a function, which is allowed.
> Doesn't this break the promise of equivalency between the
> 'CAST .. ' and '::'?
No. Equivalent functionality doesn't imply that the different
syntax forms can be used in the same places; just that they do the
same thing when used. This is hardly unique to casting.
Comparison of two text values is done through the texteq function.
test=# select val from texteq('a', 'a') as val;
val
-
t
(1 row)
test=# select val from 'a' = 'a' as val;
ERROR: syntax error at or near "'a'"
LINE 1: select val from 'a' = 'a' as val;
^
test=# select val from (select 'a' = 'a') as val;
val
-
(t)
(1 row)
> select val from CAST(random() as integer) as val;
> val
> -
>1
> (1 row)
Right. A function is allowed as a from_item. Arbitrary expressions
using operators which happen to provide equivalent services are not.
-Kevin
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
On Aug 29, 2011, at 5:02 PM, Kevin Grittner wrote: > Alexey Klyukin wrote: > >> Function calls can appear in the FROM clause. (This is especially >> useful for functions that return result sets, but any function can >> be used.) This acts as though its output were created as a >> temporary table for the duration of this single SELECT command. > > It doesn't say that operators which provide equivalent functionality > to functions can also be used. I agree, but why is it possible to use the type casting with CAST there? Doesn't this break the promise of equivalency between the 'CAST .. ' and '::'? select val from CAST(random() as integer) as val; val - 1 (1 row) -- Alexey Klyukinhttp://www.commandprompt.com The PostgreSQL Company – Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
Alexey Klyukin wrote: > Function calls can appear in the FROM clause. (This is especially > useful for functions that return result sets, but any function can > be used.) This acts as though its output were created as a > temporary table for the duration of this single SELECT command. It doesn't say that operators which provide equivalent functionality to functions can also be used. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
Merlin Moncure wrote: > yeah, that's the correct way, but why does this work? > select val from random() as val; If you look at the PostgreSQL reference docs for the SELECT statement, a from_item can be a SELECT statement in parentheses or a function call (among other things). It cannot be an arbitrary expression containing operators (like ::). -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
On Mon, Aug 29, 2011 at 7:49 AM, Kevin Grittner wrote: > Alexey Klyukin wrote: > >> The following statement produces an error message in PostgreSQL 8.4 >> - 9.2 (head): >> >> postgres=# select val from random()::integer as val; > >> The same statement rewritten with CAST AS works as expected: >> >> postgres=# select val from CAST(random() as integer) as val; > >> The documentation says these casts are equivalent, so either that's >> wrong, or this is a bug. > > Please point out where you think the documentation says that. The > way I read it, this is the correct syntax: > > test=# select val from (select random()::integer) as x(val); > val > - > 1 > (1 row) > > Not only are you missing required parentheses and the SELECT keyword, > you're returning a record rather than a scalar value. yeah, that's the correct way, but why does this work? select val from random() as val; That's illegal IMO, and walls you off from syntax (like::) you normally should be able to use. merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
On Aug 29, 2011, at 3:49 PM, Kevin Grittner wrote: > Alexey Klyukin wrote: > >> The following statement produces an error message in PostgreSQL 8.4 >> - 9.2 (head): >> >> postgres=# select val from random()::integer as val; > >> The same statement rewritten with CAST AS works as expected: >> >> postgres=# select val from CAST(random() as integer) as val; > >> The documentation says these casts are equivalent, so either that's >> wrong, or this is a bug. > > Please point out where you think the documentation says that. Here: > > A type cast specifies a conversion from one data type to another. PostgreSQL > accepts two equivalent syntaxes for type casts: > > CAST ( expression AS type ) > expression::type > http://www.postgresql.org/docs/9.0/interactive/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS > The way I read it, this is the correct syntax: > > test=# select val from (select random()::integer) as x(val); > val > - > 1 > (1 row) > > Not only are you missing required parentheses and the SELECT keyword, > you're returning a record rather than a scalar value. SELECT val FROM random() AS val (same as the problematic query, but w/o casts) doesn't produce any errors and IMO is a valid syntax. Here's a quote from the SELECT documentation: > Function calls can appear in the FROM clause. (This is especially useful for > functions that return result sets, but any function can be used.) This acts > as though its output were created as a temporary table for the duration of > this single SELECT command. http://www.postgresql.org/docs/9.0/interactive/sql-select.html The problem is that 2 types of casts behave differently when applied to random() in this query. -- Alexey Klyukinhttp://www.commandprompt.com The PostgreSQL Company – Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] the '::' cast doesn't work in the FROM clause
Alexey Klyukin wrote: > The following statement produces an error message in PostgreSQL 8.4 > - 9.2 (head): > > postgres=# select val from random()::integer as val; > The same statement rewritten with CAST AS works as expected: > > postgres=# select val from CAST(random() as integer) as val; > The documentation says these casts are equivalent, so either that's > wrong, or this is a bug. Please point out where you think the documentation says that. The way I read it, this is the correct syntax: test=# select val from (select random()::integer) as x(val); val - 1 (1 row) Not only are you missing required parentheses and the SELECT keyword, you're returning a record rather than a scalar value. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6181: concat_ws() incompatibility with MySQL
The following bug has been logged online:
Bug reference: 6181
Logged by: Itagaki Takahiro
Email address: itagaki.takah...@gmail.com
PostgreSQL version: 9.1 RC1 (x86)
Operating system: Windows 7 (64bit)
Description:concat_ws() incompatibility with MySQL
Details:
For query concat_ws('/', NULL, 'X'),
PostgreSQL 9.1 returns '/X', but MySQL returns 'X'.
I think postgres should return 'X' because of compatibility.
Another reason is that both of the two queries below return 'X'.
- concat_ws('/', 'X', NULL)
- array_to_string(ARRAY[NULL, 'X'], '/')
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] the '::' cast doesn't work in the FROM clause
Hello, The following statement produces an error message in PostgreSQL 8.4 - 9.2 (head): postgres=# select val from random()::integer as val; ERROR: syntax error at or near "::" LINE 1: select val from random()::integer as val; The same statement rewritten with CAST AS works as expected: ^ postgres=# select val from CAST(random() as integer) as val; val - 1 (1 row) The '::' cast works normally when used in a target list: postgres=# select random()::integer as val; val 1 (1 row) The documentation says these casts are equivalent, so either that's wrong, or this is a bug. The target OS is Mac OS X 10.7.1 with llvm-gcc-4.2 used as a compiler. -- Alexey Klyukinhttp://www.commandprompt.com The PostgreSQL Company – Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
