[BUGS] BUG #7902: lazy cleanup of extraneous WAL files can cause out of disk issues

[jeff]

The following bug has been logged on the website:

Bug reference:  7902
Logged by:  Jeff Frost
Email address:  j...@pgexperts.com
PostgreSQL version: 9.2.3
Operating system:   Ubuntu 12.04
Description:

While doing acceptance testing on a new Ubuntu 12.04 PostgreSQL server
running 9.2.3, we set checkpoint_segments = 128,
checkpoint_completion_target = 0.9 and placed pg_xlog on a separate 20G
partition. Also, archive_mode = off on this system.

According to the docs, you would expect the system to attempt to keep the
WAL files down close to 3 * checkpoint_segments + 1.  Unfortunately, this
does not appear to be the case because a pgbench run would run the pg_xlog
partition out of space.

The pgbench run script looks like this:

#!/bin/bash

dropdb bench
createdb bench
pgbench -i -s 1000 bench
vacuumdb -a --analyze-only
psql -c "checkpoint"
pgbench -c 64 -j 16 -r -T 600 bench

While the pgbench does cause lots of xlog based checkpoints, they never seem
to remove more than a few files and often pg_xlog grows to more than 20G and
the postgresql service falls over.

After moving pg_xlog to a larger partition, it seems it peaks at about 22G
in size. 

A manual checkpoint after the run always brings it back down to ~ 4G in
size.

Interestingly, I was unable to reproduce this with 9.2.3 on our inhouse test
system; however, the inhouse system has much less RAM and CPU resources, so
this may only be an issue on larger systems. The system that exhibits the
issue has 128G of RAM and 16 cores (32 with hyperthreading). 

I also tested 9.2.2 on the affected system and it acted the same.

Hope to test 9.1.8 in the next few days.



-- 
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] new BUG: "postgresql 9.2.3: very long query time"

[Tom Lane]

Jeff Janes  writes:
> The slowness was introduced with this:
> Use parameterized paths to generate inner indexscans more flexibly.

Try increasing from_collapse_limit to 11 or more.

I'm not exactly sure why the param-path patch might have changed this
behavior, but in any case the collapse limits ought to be the first
thing you think of when you see a crappy plan for a many-relation query.

The curious join nesting in this query makes me wonder if perhaps it was
hand-optimized to fit the behavior of some old version of the planner ...

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

[BUGS] BUG #7900: the session can execute sql sentence when kill pg master process after.

[tiyukeji]

The following bug has been logged on the website:

Bug reference:  7900
Logged by:  tan hong wei
Email address:  tiyuk...@126.com
PostgreSQL version: 9.1.4
Operating system:   cent os 5.5
Description:

1 create a db session s1,and execue sql:create table a(i int);  
2 kill -9 pg maseter pid. 
3 execute sql sentence: insert into a (i) values (100); ok!

4 restart db server .

5 table a now rows!!!




-- 
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 #7899: allow key word as alias in subquery but Can't reference it in outer query

[amutu]

The following bug has been logged on the website:

Bug reference:  7899
Logged by:  jov
Email address:  am...@amutu.com
PostgreSQL version: 9.1.3
Operating system:   CentOS 6
Description:

xxx=# select 2 from (select 1 as end) t;   
 ?column? 
--
2
(1 row)

xxx=# select end from (select 1 as end) t;
ERROR:  syntax error at or near "end"
LINE 1: select end from (select 1 as end) t;
   ^

xxx=# select version();  
   version  
   
  

--
 PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5
20110214 (Red Hat 4.4.5-6), 64-bi
t
(1 row)

I think use key word as alias should get a error message such as $key_word
is Key Word,should not be used as identifier bla bla,so for some complex
query error user can save a lot of time. 




-- 
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] new BUG: "postgresql 9.2.3: very long query time"

[Claude Speed]

21.02.2013 21:25, Jeff Janes пишет:

On Wed, Feb 20, 2013 at 5:42 AM, Claude Speed  wrote:

Hi all,

Postgresql 9.2.3 is processing my query is much longer than Postgresql
9.1.8:
Postgresql 9.1.8 - 2292 ms
Postgresql 9.2.3 - 163336 ms

I provided my query in attach and the database dump too,
this bug is reproducible.

Operating system: Gentoo x86_64 Linux 2.6.36

The slowness was introduced with this:

commit e2fa76d80ba571d4de8992de6386536867250474
Author: Tom Lane 
Date:   Fri Jan 27 19:26:38 2012 -0500

 Use parameterized paths to generate inner indexscans more flexibly.


Do you know of simpler queries that also exhibits this behavior?  It
would certainly make analysis of the issue easier.


Cheers,

Jeff
We was trying to find simple query, sadly unlucky. Only this query is 
reproducible.



--
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 #7899: allow key word as alias in subquery but Can't reference it in outer query

[Jov]

can we make a cross reference from doc 4.4.1(
http://www.postgresql.org/docs/devel/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
)
to
doc 7.3.2 (
http://www.postgresql.org/docs/devel/static/queries-select-lists.html#QUERIES-COLUMN-LABELS
)
*to mention that “AS” can make a key word to be a identifier?*
I have read the doc 4.4.1 and can't find this info from there so I thin it
is a bug.

2013/2/22 Jov 

> I have get the description of this behaviour from doc 7.3.2
>
> http://www.postgresql.org/docs/devel/static/queries-select-lists.html#QUERIES-COLUMN-LABELS
> .
>
> from the error message now,I think syntax error may emmit by the
> parser,parser error make it hard to get the really condition such as the
> second token ‘end’ is a select item from a select statement.
>
> thanks
>
> 2013/2/22 Tom Lane 
>
>> "=?ISO-8859-1?B?Sm92?="  writes:
>> > xxx=# select end from (select 1 as end) t;
>> > ERROR:  syntax error at or near "end"
>> > LINE 1: select end from (select 1 as end) t;
>> >^
>>
>> You need to double-quote the outer use of "end", viz
>>
>> regression=# select "end" from (select 1 as end) t;
>>  end
>> -
>>1
>> (1 row)
>>
>> In the context with "as", Postgres can tell the word is meant as a
>> column identifier not a keyword, but there's no way for it to know that
>> in the outer usage.
>>
>> > I think use key word as alias should get a error message such as
>> $key_word
>> > is Key Word,should not be used as identifier bla bla,so for some complex
>> > query error user can save a lot of time.
>>
>> Well, yours is the first complaint we've ever had in that direction,
>> whereas we used to get a lot of complaints in the opposite direction,
>> back when we didn't allow keywords to be used for column names.
>> So I doubt we'll change it.  It is an interesting gotcha though :-(
>>
>> regards, tom lane
>> .
>>
>>
>
>
> --
> Jov
> blog: http:amutu.com/blog 
>



-- 
Jov
blog: http:amutu.com/blog 

Re: [BUGS] BUG #7899: allow key word as alias in subquery but Can't reference it in outer query

[Jov]

I have get the description of this behaviour from doc 7.3.2
http://www.postgresql.org/docs/devel/static/queries-select-lists.html#QUERIES-COLUMN-LABELS
.

from the error message now,I think syntax error may emmit by the
parser,parser error make it hard to get the really condition such as the
second token ‘end’ is a select item from a select statement.

thanks

2013/2/22 Tom Lane 

> "=?ISO-8859-1?B?Sm92?="  writes:
> > xxx=# select end from (select 1 as end) t;
> > ERROR:  syntax error at or near "end"
> > LINE 1: select end from (select 1 as end) t;
> >^
>
> You need to double-quote the outer use of "end", viz
>
> regression=# select "end" from (select 1 as end) t;
>  end
> -
>1
> (1 row)
>
> In the context with "as", Postgres can tell the word is meant as a
> column identifier not a keyword, but there's no way for it to know that
> in the outer usage.
>
> > I think use key word as alias should get a error message such as
> $key_word
> > is Key Word,should not be used as identifier bla bla,so for some complex
> > query error user can save a lot of time.
>
> Well, yours is the first complaint we've ever had in that direction,
> whereas we used to get a lot of complaints in the opposite direction,
> back when we didn't allow keywords to be used for column names.
> So I doubt we'll change it.  It is an interesting gotcha though :-(
>
> regards, tom lane
> .
>
>


-- 
Jov
blog: http:amutu.com/blog 

Re: [BUGS] BUG #7899: allow key word as alias in subquery but Can't reference it in outer query

[Tom Lane]

"=?ISO-8859-1?B?Sm92?="  writes:
> xxx=# select end from (select 1 as end) t;
> ERROR:  syntax error at or near "end"
> LINE 1: select end from (select 1 as end) t;
>^

You need to double-quote the outer use of "end", viz

regression=# select "end" from (select 1 as end) t;
 end 
-
   1
(1 row)

In the context with "as", Postgres can tell the word is meant as a
column identifier not a keyword, but there's no way for it to know that
in the outer usage.

> I think use key word as alias should get a error message such as $key_word
> is Key Word,should not be used as identifier bla bla,so for some complex
> query error user can save a lot of time.

Well, yours is the first complaint we've ever had in that direction,
whereas we used to get a lot of complaints in the opposite direction,
back when we didn't allow keywords to be used for column names.
So I doubt we'll change it.  It is an interesting gotcha though :-(

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

[BUGS] BUG #7899: allow key word as alias in subquery but Can't reference it in outer query

[Jov]

The following bug has been logged on the website:

Bug reference:  7899
Logged by:  jov
Email address:  am...@amutu.com
PostgreSQL version: 9.1.3
Operating system:   CentOS 6
Description:

xxx=# select 2 from (select 1 as end) t;   
 ?column? 
--
2
(1 row)

xxx=# select end from (select 1 as end) t;
ERROR:  syntax error at or near "end"
LINE 1: select end from (select 1 as end) t;
   ^

xxx=# select version();  
   version  
   
  

--
 PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5
20110214 (Red Hat 4.4.5-6), 64-bi
t
(1 row)

I think use key word as alias should get a error message such as $key_word
is Key Word,should not be used as identifier bla bla,so for some complex
query error user can save a lot of time.