[BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement

[fuminori ido]

The following bug has been logged online:

Bug reference:  3589
Logged by:  fuminori ido
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 7.4.7
Operating system:   GNU/Linux, Debian3.1
Description:/etc/init.d/postgresql reload doesn't reflect
/etc/postgresql/postgresql.conf log_statement
Details: 

Dear,

When I operate the following steps from command-line, postgres still
generates SQL statement.  Is this bug or spec?:

1) At first, set log_statement=true in /etc/postgresql/postgresql.conf

2) do reload by /etc/init.d/postgresql reload
  I can see a lot of SQL in /var/log/postgresql/postgres.log.

3) Then, I commented-out the log_statement=true.  This means I thought
default setting(log_statement=false) would be effective.

4) do reload as the same; /etc/init.d/postgresql reload
  Still SQL is genrated as follows:

>  (lot of SQLs in the log)
>  :
> 2007-08-30 11:32:45 [3791] LOG:  received SIGHUP, reloading configuration
files
> 2007-08-30 11:32:47 [3833] LOG:  statement: start transaction;set
transaction isolation level serializable;select last_value from
"_mrs".sl_action_seq;
>  :
>  (still lot of SQLs in the log)

This is not what I expected.  At step 3) above, I think commented-out means
set to default value.  However, this behavior means, I guess, reload does
nothing when config statement is missing.  Maybe I need to write explicitly
"log_statement=false" even though it is default value in order to be
effective on reload?

I couldn't get answer by searching Google so I'm posting this unknown
behavior here.

Much appreciate for your answer!

Best Regards

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement

[Magnus Hagander]

On Thu, Aug 30, 2007 at 03:02:54AM +, fuminori ido wrote:
> 
> The following bug has been logged online:
> 
> Bug reference:  3589
> Logged by:  fuminori ido
> Email address:  [EMAIL PROTECTED]
> PostgreSQL version: 7.4.7
> Operating system:   GNU/Linux, Debian3.1
> Description:/etc/init.d/postgresql reload doesn't reflect
> /etc/postgresql/postgresql.conf log_statement
> Details: 
> 
> Dear,
> 
> When I operate the following steps from command-line, postgres still
> generates SQL statement.  Is this bug or spec?:
> 
> 1) At first, set log_statement=true in /etc/postgresql/postgresql.conf
> 
> 2) do reload by /etc/init.d/postgresql reload
>   I can see a lot of SQL in /var/log/postgresql/postgres.log.
> 
> 3) Then, I commented-out the log_statement=true.  This means I thought
> default setting(log_statement=false) would be effective.

There's your problem right there: it does *not* mean that the default
setting would be effective. It means that the setting won't be changed.
Remove the comment and set it to false, and it'll work.
(if you restart the server instead of reload it *will* fall back to the
default, which isn't very consistent)

This is a known problem (though arguably not actually a bug) but it's far
from trivial to fix which is why it hasn't been fixed yet.

//Magnus

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement

[Kris Jurka]

On Thu, 30 Aug 2007, Magnus Hagander wrote:


There's your problem right there: it does *not* mean that the default
setting would be effective. It means that the setting won't be changed.
Remove the comment and set it to false, and it'll work.
(if you restart the server instead of reload it *will* fall back to the
default, which isn't very consistent)

This is a known problem (though arguably not actually a bug) but it's far
from trivial to fix which is why it hasn't been fixed yet.



Actually this will be fixed in 8.3:

http://archives.postgresql.org/pgsql-committers/2007-03/msg00097.php

Kris Jurka

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[BUGS] to_tsquery stack overflow

[Heikki Linnakangas]

Passing a query with enough nested parenthesis in it causes a segfault.
Attached is a handy little program to generate such a query, the actual
query was too big to get through to the list.

The problem seems to be unbounded recursion in the makepol function that
converts the input query from infix to polish notation. An easy fix
would be to just add a level parameter to makepol that's incremented on
each recursion, and throw an error if it grows bigger than some safe
limit. There might be a similar problem in TS_execute as well, if you
can somehow pass a complex enough TSQuery to the system, perhaps with a
custom libpq client and tsqueryrecv.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
#include 
#include 


int main(int argc, char **argv)
{
  int i;
  int n;

  if(argc == 1)
n = 10;
  else
n = atoi(argv[1]);

  printf("SELECT to_tsquery('simple', '");

  for(i=0;i < n; i++)
  {
	  printf("%d|(", i + 1);
  }
  printf("end");

  for(i=0;i < n; i++)
  {
	  printf(")");
  }
  printf("')\n");
}

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [BUGS] to_tsquery stack overflow

[Heikki Linnakangas]

Heikki Linnakangas wrote:
> Passing a query with enough nested parenthesis in it causes a segfault.
> Attached is a handy little program to generate such a query, the actual
> query was too big to get through to the list.
> 
> The problem seems to be unbounded recursion in the makepol function that
> converts the input query from infix to polish notation. An easy fix
> would be to just add a level parameter to makepol that's incremented on
> each recursion, and throw an error if it grows bigger than some safe
> limit. There might be a similar problem in TS_execute as well, if you
> can somehow pass a complex enough TSQuery to the system, perhaps with a
> custom libpq client and tsqueryrecv.

Actually, the right way to fix that is of course to call
check_stack_depth() in makepol and TS_execute.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement

[Fuminori Ido]

Dear Kris and Magnus,

Thanks you very much for your prompt reply.  I'm now clear the behavior
and its future plan.

Thanks again!

best regards,


Kris Jurka wrote:
> 
> 
> On Thu, 30 Aug 2007, Magnus Hagander wrote:
> 
>> There's your problem right there: it does *not* mean that the default
>> setting would be effective. It means that the setting won't be changed.
>> Remove the comment and set it to false, and it'll work.
>> (if you restart the server instead of reload it *will* fall back to the
>> default, which isn't very consistent)
>>
>> This is a known problem (though arguably not actually a bug) but it's far
>> from trivial to fix which is why it hasn't been fixed yet.
>>
> 
> Actually this will be fixed in 8.3:
> 
> http://archives.postgresql.org/pgsql-committers/2007-03/msg00097.php
> 
> Kris Jurka

-- 
===
株式会社 ネットワーク応用通信研究所
NaCl
井戸 文則 <[EMAIL PROTECTED]>
===

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [BUGS] BUG #3589: /etc/init.d/postgresql reload doesn't reflect /etc/postgresql/postgresql.conf log_statement

[Tom Lane]

Kris Jurka <[EMAIL PROTECTED]> writes:
> On Thu, 30 Aug 2007, Magnus Hagander wrote:
>> This is a known problem (though arguably not actually a bug) but it's far
>> from trivial to fix which is why it hasn't been fixed yet.

> Actually this will be fixed in 8.3:
> http://archives.postgresql.org/pgsql-committers/2007-03/msg00097.php

Given the long history of failed attempts to fix it, I wouldn't be
surprised if this one crashes and burns too ...

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [BUGS] to_tsquery stack overflow

[Tom Lane]

"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> The problem seems to be unbounded recursion in the makepol function that
> converts the input query from infix to polish notation. An easy fix
> would be to just add a level parameter to makepol that's incremented on
> each recursion, and throw an error if it grows bigger than some safe
> limit.

check_stack_depth() seems the correct fix.

regards, tom lane

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: [BUGS] BUG #3587: EXECUTE and trigger problem [VASCL:A1226546842]

[Cyrus Downey]

but the documentation does not say I shouldn't be able to do it. 
Additionally, an alternate method (which was not included) using
temporary tables works fine.  It fails when switching to the old record.

thanks
cyrus

Tom Lane wrote:

  Cyrus Downey <[EMAIL PROTECTED]> writes:
  
  
According to the documentation I should be able to use the OLD record
in the dynamic command passed to the Execute statement.

  
  
The documentation promises no such thing.

			regards, tom lane

  

Re: [BUGS] BUG #3588: coalesce not working in join

[Tom Lane]

"Richard Harris" <[EMAIL PROTECTED]> writes:
> The queries, query1 and query2, below are identical except that query1 has a
> 'left join' where query2 has a 'join'. Both queries return three rows.
> However query2 (with the 'join') returns all non-null values in column
> t1b_pkt1 where query 1 with the left join returns some null values. 
> This is the behavior when the queries are run on PG 8.2.4. When the queries
> are run on PG 8.0.3, both queries return the same results as query2 with the
> 'join'.

After poking at this for a bit I have concluded that the manipulations
done in make_outerjoininfo() have a fundamental error: when it concludes
that two outer joins are unsafe to commute, it enforces this by adding
the lower OJ's min_lefthand+min_righthand relation sets to the
min_lefthand of the upper OJ.  This is inadequate when more than 2 OJs
are involved: in this example, it leaves us thinking that we can legally
apply the t2/t2a outer join after the t1a/t1b join, because the t2a/t1a
join's min_lefthand excludes t2 (since it *can* commute with the t2/t2a
join).  It looks to me like we need to add the full *syntactic* extent
of the lower OJ to the upper min_lefthand.  This cannot be done with the
OuterJoinInfo data structure as it stands, because it doesn't track the
syntactic extent only the minimum relsets.  (I had hoped we didn't need
to store that info throughout planning, but that was clearly
overoptimistic.)  Fortunately this data structure is just a planner
local and isn't stored on disk, so we can fix it without initdb.

It's possible that the syntactic-extent rule is stronger than necessary
and some intermediate compromise could be found.  However, non-strict
join conditions are the exception not the rule, so it's probably not
worth expending a whole lot of skull sweat on this case.

The other half of the testing, where we decide that it's OK to commute
with something in our RHS, should probably be using syntactic extent not
minimum relsets as well.

It strikes me also that the bug we fixed awhile back with intervening
non-strict conditions in the RHS-test is probably lurking in the
LHS-test too ... I had thought that it wasn't an issue there but
now I can't reconstruct an argument why not.

Comments anyone?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[BUGS] to_date gives odd results

[Robert Treat]

example from docs:
pagila=# select to_date('05 Dec 2000', 'DD Mon ');
to_date

2000-12-05
(1 row)
 
slight modification:
pagila=# select to_date('05 December 2000', 'DD Month ');
to_date
---
0001-12-05 BC
(1 row)

I can't imagine that's expected behavior bug? 
oh, 
 
pagila=# select version();
version
-
PostgreSQL 8.2.4 on i386-pc-solaris2.10, compiled by cc -Xa
(1 row)

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[BUGS] Re: BUG #3504: Some listening sessions never return from writing, problems ensue

[Marshall, Steve]

Peter,

I don't know if this is directly related to your problem, but the 
version of dbd_db_pg_notifies you are using has a memory leak in it.


The memory leak causes a small amount of memory to be leaked every time 
pg_notifies is called from your perl code.   This memory leak affected 
several versions of Pg-DBD, up to version 1.49.  It should be fixed in 
later versions of Pg-DBD.


The fix is a one line change; a patch file is attached.

I hope this is helpful,
Steve Marshall
*** dbdimp.c.1.49   2006-08-29 16:04:37.0 -0400
--- dbdimp.c2006-08-29 16:23:32.0 -0400
***
*** 832,838 
  
retsv = newRV(sv_2mortal((SV*)ret));

!   return retsv;
  
  } /* end of dbd_db_pg_notifies */
  
--- 832,838 
  
retsv = newRV(sv_2mortal((SV*)ret));

!   return sv_2mortal(retsv);
  
  } /* end of dbd_db_pg_notifies */
  

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] to_date gives odd results

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> pagila=# select to_date('05 December 2000', 'DD Month ');
> to_date
> ---
> 0001-12-05 BC
> (1 row)

> I can't imagine that's expected behavior bug? 

You needed to say FMMonth, else it expects fixed-width column.

to_date and friends are fairly awful in terms of not throwing errors
when the input doesn't really match the format.  I think what you
shoulda got here is a bad-input error.  However, somebody's going to
have to do a major rewrite of formatting.c to make it much better...

(Hmmm ... actually, in CVS HEAD this produces 2000-12-05 with or without
FM, which looks like a rather ill-considered change to try to make
things work "nicely".  The problem that it goes south on actually bad
input is still there.)

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] to_date gives odd results

[Robert Treat]

On Thursday 30 August 2007 21:15, Tom Lane wrote:
> Robert Treat <[EMAIL PROTECTED]> writes:
> > pagila=# select to_date('05 December 2000', 'DD Month ');
> > to_date
> > ---
> > 0001-12-05 BC
> > (1 row)
> >
> > I can't imagine that's expected behavior bug?
>
> You needed to say FMMonth, else it expects fixed-width column.

Wow, I don't get that reading the docs. It says "FM suppresses leading zeroes 
and trailing blanks that would otherwise be added to make the output of a 
pattern be fixed-width."   The word output led me to believe that had 
something to do with what would be displayed, not interpreting information 
given to the function. Guess it could be argued this is a display issue 
somehow. 

>
> to_date and friends are fairly awful in terms of not throwing errors
> when the input doesn't really match the format.  I think what you
> shoulda got here is a bad-input error.  However, somebody's going to
> have to do a major rewrite of formatting.c to make it much better...
>
> (Hmmm ... actually, in CVS HEAD this produces 2000-12-05 with or without
> FM, which looks like a rather ill-considered change to try to make
> things work "nicely".  The problem that it goes south on actually bad
> input is still there.)
>

ISTR to_date and friends are designed to be oracle compatable, so I'd guess 
that change was made in an effort to mimic oracle, which returns the same 
value for both syntax's I used originally.  

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [BUGS] to_tsquery stack overflow

[Tom Lane]

"Heikki Linnakangas" <[EMAIL PROTECTED]> writes:
> Actually, the right way to fix that is of course to call
> check_stack_depth() in makepol and TS_execute.

Done.

That is, I did it in HEAD's integrated tsearch code.  I suppose the same
hazard exists in all back-branch contrib/tsearch2 versions ... how
excited are we about fixing those?

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq