Re: [HACKERS] tuplestore API problem

[Hitoshi Harada]

2009/3/28 Tom Lane :
> Hitoshi Harada  writes:
>> 2009/3/27 Hitoshi Harada :
>>> 2009/3/27 Tom Lane :
 A brute-force solution is to change tuplestore_gettupleslot() so that it
 always copies the tuple, but this would be wasted cycles for most uses
 of tuplestores.  I'm thinking of changing tuplestore_gettupleslot's API
 to add a bool parameter specifying whether the caller wants to force
 a copy.
>
>> Here's the patch. Hope there are no more on the same reason. It seems
>> that we'd need to implement something like garbage collector in
>> tuplestore, marking and tracing each row references, if the complete
>> solution is required.
>
> I don't like this; I'm planning to go with the aforementioned API
> change instead.  The way you have it guarantees an extra copy cycle
> even when tuplestore is already making a copy internally; and it doesn't
> help if we find similar problems elsewhere.  (While I'm making the
> API change I'll take a close look at each call site to see if it has
> any similar risk.)

You're right. It kills performance even after dumptuples(). Thinking
more, I found the cause is only around dumptuples(). If you can trace
TupleTableSlots that points to memtuples inside tuplestore, you can
materialize them just before WRITETUP() in dumptuples().
So I tried pass EState.es_tupleTables to tuplestore_begin_heap() to
trace those TupleTableSlots. Note that if you pass NULL the behavior
is as before so nothing's broken. Regression passes but I'm not quite
sure EState.es_tupleTable is only place that holds TupleTableSlots
passed to tuplestore...

I know you propose should_copy boolean parameter would be added to
tuplestore_gettupleslot(). That always adds overhead even if
tuplestore *doesn't* dump tuples. That case doesn't need copy tuples,
I guess.

Regards,



-- 
Hitoshi Harada


tuplestore_apichange.20090328.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 release notes proof reading 1/2

[David E. Wheeler]

On Mar 27, 2009, at 6:40 PM, Bruce Momjian wrote:


Thanks, text updated:

  While semi-joins merely replace existing IN joins, anti-joins
  are a new capability for NOT EXISTS clauses (Tom) This improves
  optimization possibilities.


I'm not enough of a relational algebra geek to really understand what  
that means. Will there be a link or something to some documentation or  
even, *gasp*, a blog entry explaining what this means and why it's  
important?


Thanks,

David

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] TODO item

[Jaime Casanova]

In the TODO list there is an item "[D] Completed itemAdd array_agg()
and UNNEST functions for arrays " marked as done but 5 items below
there is: "Add SQL-standard array_agg() and unnest() array functions "
it's the same item so this one should be removed... or there is a
difference between the array_agg() and unnest() implemented versus
SQL-standard array_agg() and unnest()?

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] psql \d* and system objects

[Bruce Momjian]

The psql system object display issue has not been completely resolved
for 8.4;  see 8.4 open items:

http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items#Changes

So what is the proposal?  Have U/S/A flags for all commands and have
different system display default for each command?

I think this:

http://archives.postgresql.org/pgsql-hackers/2009-01/msg01443.php

makes a strong argument that having a pattern control system object
display will be too confusing.

FYI, right now 'S' is "include-system";  we have no system-only display
option.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PQinitSSL broken in some use casesf

[Bruce Momjian]

Andrew Chernow wrote:
> Bruce Momjian wrote:
> 
> > I think there is a good argument that PQinitSSL(X) where X > 1 would
> > work fine for more fine-grained control.  The new libpq init function
> > idea was interesting, but having a documented solution for
> > WSAStartup()/WSACleanup() usage, we now don't have another libpq init
> > use-case so it is hard to suggest a new libpq function.
> 
> If you look back through the list, the PQinit idea was offered up 
> several times while discussing WSA* stuff.  There were few buyers.  I 
> don't see how having or not having a documented solution for WSA* usage 
> would make a bit of difference.

It only means we don't have _another_ use for a more general libpq init
function.

> > I am figuring we have to keep the current behavior and see what happens
> > after 8.4;  the new documentation should make the behavior clear and
> > perhaps trigger other users to report suggestions.
> > 
> > 
> 
> This is not a battle I find worth fighting.  But I am having trouble 
> staying completely quiet; I typically have this issue when I disagree :) 
>   This patch merely documents the problem, when another fully documented 
> working patch "fixed" it; following the discussions on the list.
> 
> http://archives.postgresql.org//pgsql-hackers/2009-02/msg01018.php
> 
> Was this reviewed and/or rejected?

Comments Tom made were that there was no consensus on the proper
fix/direction, and I agree.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Bruce Momjian]

Josh Berkus wrote:
> 
> > Josh, this isn't a rejection. Both Tom and I asked for more exploration
> > of the implications of doing as you suggest. Tom has been more helpful
> > than I was in providing some scenarios that would cause problems. It is
> > up to you to solve the problems, which is often possible. 
> 
> OK, well, barring the context issues, what do people think of the idea?
> 
> What I was thinking was that this would be a setting on the SET ROLE 
> statement, such as:
> 
> SET ROLE special WITH SETTINGS
> 
> ... or similar; I'd need to find an existing keyword which works.
> 
> I think this bypasses a lot of the issues which Tom raises, but I'd want 
> to think about the various permutations some more.

I have added the following TODO:

Allow role-specific ALTER ROLE SET variable settings to be processed
independently of login; SET ROLE does not process role-specific variable
settings

* 
http://archives.postgresql.org/message-id/49b82cd7.20...@agliodbs.com 

and the attached patch which better documents our current behavior.

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

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/alter_role.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_role.sgml,v
retrieving revision 1.11
diff -c -c -r1.11 alter_role.sgml
*** doc/src/sgml/ref/alter_role.sgml	14 Nov 2008 10:22:45 -	1.11
--- doc/src/sgml/ref/alter_role.sgml	28 Mar 2009 03:23:57 -
***
*** 79,97 
 password is MD5-encrypted.

  
!   
!The remaining variants change a role's session default for
!a specified configuration variable.  Whenever the role subsequently
!starts a new session, the specified value becomes the session default,
!overriding whatever setting is present in postgresql.conf
!or has been received from the postgres command line.
!(For a role without LOGIN privilege, session defaults have
!no effect.)
!Ordinary roles can change their own session defaults.
!Superusers can change anyone's session defaults.
!Roles having CREATEROLE privilege can change defaults for
!non-superuser roles.
!Certain variables cannot be set this way, or can only be
 set if a superuser issues the command.

   
--- 79,96 
 password is MD5-encrypted.

  
!
!The remaining variants change a role's session default for a
!specified configuration variable. Whenever the role subsequently
!starts a new session, the specified value becomes the session
!default, overriding whatever setting is present in
!postgresql.conf or has been received from the postgres
!command line. This only happens at login time, so configuration
!settings associated with a role to which you've  will be ignored.
!Superusers can change anyone's session defaults. Roles having
!CREATEROLE privilege can change defaults for non-superuser
!roles. Certain variables cannot be set this way, or can only be
 set if a superuser issues the command.

   
***
*** 163,168 
--- 162,173 
 
  
 
+ Role-specific variable setting take effect only at login;
+ 
+ does not process role-specific variable settings.
+
+ 
+
  See  and  for more information about allowed
  parameter names and values.
Index: doc/src/sgml/ref/set_role.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/set_role.sgml,v
retrieving revision 1.6
diff -c -c -r1.6 set_role.sgml
*** doc/src/sgml/ref/set_role.sgml	14 Nov 2008 10:22:47 -	1.6
--- doc/src/sgml/ref/set_role.sgml	28 Mar 2009 03:23:57 -
***
*** 92,97 
--- 92,104 

  

+SET ROLE does not process session variables as specified by
+the role's  settings;  this only happens during
+login.
+   
+ 
+   
 SET ROLE cannot be used within a
 SECURITY DEFINER function.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PQinitSSL broken in some use casesf

[Andrew Chernow]

Bruce Momjian wrote:


I think there is a good argument that PQinitSSL(X) where X > 1 would
work fine for more fine-grained control.  The new libpq init function
idea was interesting, but having a documented solution for
WSAStartup()/WSACleanup() usage, we now don't have another libpq init
use-case so it is hard to suggest a new libpq function.


If you look back through the list, the PQinit idea was offered up 
several times while discussing WSA* stuff.  There were few buyers.  I 
don't see how having or not having a documented solution for WSA* usage 
would make a bit of difference.




I am figuring we have to keep the current behavior and see what happens
after 8.4;  the new documentation should make the behavior clear and
perhaps trigger other users to report suggestions.




This is not a battle I find worth fighting.  But I am having trouble 
staying completely quiet; I typically have this issue when I disagree :) 
 This patch merely documents the problem, when another fully documented 
working patch "fixed" it; following the discussions on the list.


http://archives.postgresql.org//pgsql-hackers/2009-02/msg01018.php

Was this reviewed and/or rejected?

Andrew Chernow



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] parallel restore

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Tom Lane wrote:
> > Andrew Dunstan  writes:
> >   
> >> Tom Lane wrote:
> >> 
> >>> There is an unfinished TODO item here: we really ought to make it work
> >>> for tar-format archives.  That's probably not hugely difficult, but
> >>> I didn't look into it, and don't think we should hold up applying the
> >>> existing patch for it.
> >>>   
> >
> >   
> >> Right. Were you thinking this should be done for 8.4?
> >> 
> >
> > If you have time to look into it, sure.  Otherwise we should just put it
> > on the TODO list.
> >
> > 
> >   
> 
> I've had a look at this. If our tar code supported out of order 
> restoration(using fseeko) I'd be done. But it doesn't, and I won't get 
> that done for 8.4, if at all. I'm not sure what would be involved in 
> making it work.

Added to TODO:

Allow parallel restore of tar dumps

* http://archives.postgresql.org/pgsql-hackers/2009-02/msg01154.php 

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] pg_migrator progress

[Bruce Momjian]

Tom Lane wrote:
> Gregory Stark  writes:
> > Tom Lane  writes:
> >> No, but this would just be the same situation that prevails after
> >> OID-counter wraparound, so I don't see a compelling need for us to
> >> change the OID counter in the new DB.  If the user has done the Proper
> >> Things (ie, made unique indexes on his OIDs) then it won't matter.
> >> If he didn't, his old DB was a time bomb anyway.
> 
> > Also I wonder about the performance of skipping over thousands or even
> > millions of OIDs for something like a toast table.
> 
> I think that argument is a red herring.  In the first place, it's
> unlikely that there'd be a huge run of consecutive OIDs *in the same
> table*.  In the second place, if he does have such runs, the claim that
> he can't possibly have dealt with OID wraparound before seems pretty
> untenable --- he's obviously been eating lots of OIDs.
> 
> But having said that, there isn't any real harm in fixing the OID
> counter to match what it was.  You need to run pg_resetxlog to set the
> WAL position and XID counter anyway, and it can set the OID counter too.

FYI, I decided against restoring the oid counter because it might
collide with an oid assigned during pg_migrator schema creation.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] PQinitSSL broken in some use casesf

[Bruce Momjian]

Merlin Moncure wrote:
> On Tue, Feb 10, 2009 at 5:02 PM, Bruce Momjian  wrote:
> > Merlin Moncure wrote:
> >> > PQinitSSL(0) was specifically designed to allow applications to set up
> >> > SSL on their own.  How does this not work properly?
> >>
> >> this has nothing to do with who initializes ssl.  this is all about
> >> *crypto*.  remember,  crypto and ssl are two separate libraries.  The
> >> application or library in question may not even link with ssl or use
> >> ssl headers.
> >>
> >> The problem is PQinitSSL (re-) initializes crypto without asking if that's 
> >> ok.
> >
> > PQinitSSL(false) initializes crypto?  Please point me to exact function
> > calls that are the problem?  Everything is very vague.
> 
> nooo, you are not listening :-)
> 
> PQinitSSL(0) initializes libpq for ssl but leaves crypto and ssl
> initialization to the app
> PQinitSSL(1) initializes libpq, crypto, and ssl libraries
> 
> Now, consider an app that uses libcrypto for its own requirements *but
> not libssl*.  It initializes libcrypto, passing its own lock vector,
> etc.  It cannot however initialize ssl because it does not link with
> ssl, or include ssl headers.  There are no ssl functions to call, and
> it wouldn't make sense to expect the app to do this even if there
> were.
> 
> Now, if this app also has libpq dependency, it needs a way to tell
> libpq: 'i have already initialized the crypto library, but could you
> please set up libssl'.  otherwise you end up re-initializing libcrypto
> with different lock vector which is very bad if there are any locks
> already in use, which is quite likely.
> 
> There is no way to do that with libpqso you see that no matter how
> you call PQinitSSL, the application is broken in some way.  Passing 0
> breaks because ssl never ends up getting set up, and passing 1 breaks
> because libcrypto's locks get messed up.
> 
> The main problem is that libpq PQinitSSL makes broad (and extremely
> dangerous assumption) that it is the only one interested in libcrypto
> lock vector.  In short, it's broken.

I am back to looking at this.  I dropped off this discussion back in
February because I felt people didn't want to answer questions I had,
but now it seems we have to close this out somehow.

I have applied the attached patch which does several things:

o  documents that libssl _and_ libcrypto initialization is
   turned off by PQinitSSL(0)
o  clarified cases where this behavior is important
o  added comments that the CRYPTO_set_* calls reference
   libcrypto, not libssl

I think we can now say that the current behavior is not a bug because it
is documented, even though the PQinitSSL() function name is inaccurate.

In fact, 8.4 is the first time we are documenting the valid parameter
value to PQinitSSL(), in 8.3 we have:

   to inside libpq), you can use
   PQinitSSL(int) to tell libpq
   that the SSL library has already been initialized by your
   application.

So we have some flexibility in defining how it behaves, and this also
illustrates how little the function is used because no one ever
complained about it.

I think there is a good argument that PQinitSSL(X) where X > 1 would
work fine for more fine-grained control.  The new libpq init function
idea was interesting, but having a documented solution for
WSAStartup()/WSACleanup() usage, we now don't have another libpq init
use-case so it is hard to suggest a new libpq function.

I am figuring we have to keep the current behavior and see what happens
after 8.4;  the new documentation should make the behavior clear and
perhaps trigger other users to report suggestions.

I assume this item is closed for 8.4 unless I hear otherwise.

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

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/libpq.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v
retrieving revision 1.279
diff -c -c -r1.279 libpq.sgml
*** doc/src/sgml/libpq.sgml	23 Mar 2009 01:45:29 -	1.279
--- doc/src/sgml/libpq.sgml	28 Mar 2009 01:17:21 -
***
*** 6169,6179 

  

!If you are using SSL inside your application (in addition
!to inside libpq), you can call
!PQinitSSL(int) with 0 to tell
!libpq that the SSL library
!has already been initialized by your application.
 
 See http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html";>
--- 6169,6181 

  

!If your application initializes libssl or
!libcrypto libraries and libpq
!is built with SSL support, you should call
!PQinitSSL(0) to tell libpq
!that the libssl and libcrypto libraries
!have been initialized by your application so
!libpq will not initialize those libraries.
 
 See http://h71000.www7.hp.com/doc/83final/BA554_90007/ch04.html";>
Index: src/interfaces/libpq/

Re: [HACKERS] DTrace probes broken in HEAD on Solaris?

[Tom Lane]

Robert Lor  writes:
> Tom Lane wrote:
>> [ complaining about disabled probes not being no-ops ]

> 1) Only use if (foo_ENABLED()) test  for probes with expensive function 
> call/computation in arguments. This will keep the code clean, and we can 
> document this in the "Definine New Probes" section in the online doc.
> ...
> I prefer option 1 the most and 3 the least.

I got the same advice from the systemtap people, so we'll do it that
way.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Alvaro Herrera]

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> > > Alvaro Herrera wrote:

> > > > Huh?  What false positive problem?
> > > 
> > > typedefs listed on platforms that match identifiers in our code that are
> > > _not_ typedefs.
> > 
> > Does this actually happen anywhere?
> 
> No idea;  it was more a theoretical issue to say that having more
> typedefs is not necessarily a good thing;  they should ideally be the
> typedefs we use, and Windows adds a lot of typedefs we don't use.

Okay, so I went over the mingw list a bit (not exhaustively) and found
no typedef that's used as an identifier in our code.

Huh ... just found one.  It's called "timezone", but it's used as an
identifier only in the function declaration (dt2local), not in the
function definition, which uses "tz" instead.

There's also ACL, but we only use it in macro definitions.

There are a bunch of other typedefs that the mingw port adds, but
several of them are actually used in our code (HANDLE, BOOL, etc).

I think this is minor enough that it should be ignored.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Bruce Momjian]

Andrew Dunstan wrote:
> 
> 
> Bruce Momjian wrote:
> > Anyway, I think a diff of using my list and Andrew's list will show us
> > which one gets things clearest;  the diff is going to highlight only
> > cases where the typedef lists change formatting.
> >
> > Andrew, where exactly is the list I should try?
> >
> >   
> 
> fetch it from 

Thanks.  I will run tests when we are ready for pg_indent and we can
then make a decision.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Andrew Dunstan]

Bruce Momjian wrote:

Anyway, I think a diff of using my list and Andrew's list will show us
which one gets things clearest;  the diff is going to highlight only
cases where the typedef lists change formatting.

Andrew, where exactly is the list I should try?

  


fetch it from 

cheers

andrew

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Bruce Momjian]

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> > Alvaro Herrera wrote:
> > > Bruce Momjian wrote:
> > > 
> > > > Well, as you, I was hoping for a clear solution, and it seems we don't
> > > > have one.  I think the false-positives problem is real and might make
> > > > the greater code coverage of the buildfarm worse than what we did for
> > > > 8.3.
> > > 
> > > Huh?  What false positive problem?
> > 
> > typedefs listed on platforms that match identifiers in our code that are
> > _not_ typedefs.
> 
> Does this actually happen anywhere?

No idea;  it was more a theoretical issue to say that having more
typedefs is not necessarily a good thing;  they should ideally be the
typedefs we use, and Windows adds a lot of typedefs we don't use.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Bruce Momjian]

Peter Eisentraut wrote:
> Bruce Momjian wrote:
> > Peter Eisentraut wrote:
> >> Bruce Momjian wrote:
> >>> I thought the logical solution to this was to place the socket in a
> >>> secure directory and not bother with SSL at all.
> >> How would a client algorithmically determine whether the server socket 
> >> was in a "secure" directory?
> > 
> > You have to configure your client to know that, but don't you need to
> > configure your client for SSL too?
> 
> Yes, but how exactly would a client know?  How is a "secure directory" 
> defined, in terms of C library calls, say?

I assume directory permissions controlling access to the socket file
would be enough.  You are going to have to set up SSL certificates
anyway for this so isn't that just as hard as telling the client where
the socket file is located?

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 release notes proof reading 1/2

[Bruce Momjian]

Andrew Gierth wrote:
> > "Guillaume" == Guillaume Smet  writes:
> 
>  Guillaume> - "While semi-joins merely replace existing IN joins,
>  Guillaume> anti-joins are a new capability for NOT IN and NOT EXIST
>  Guillaume> clauses (Tom) This improves optimization possibilities."
>  Guillaume> -> remove the "(Tom)", s/EXIST/EXISTS/
> 
> Also  s/NOT IN and //  (anti-joins are not used for NOT IN due to the
> awkward semantics of nulls). Should possibly also mention the fact that
> some outer joins can be planned as anti-joins too.

Thanks, text updated:

   While semi-joins merely replace existing IN joins, anti-joins
   are a new capability for NOT EXISTS clauses (Tom) This improves
   optimization possibilities.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Alvaro Herrera]

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> > 
> > > Well, as you, I was hoping for a clear solution, and it seems we don't
> > > have one.  I think the false-positives problem is real and might make
> > > the greater code coverage of the buildfarm worse than what we did for
> > > 8.3.
> > 
> > Huh?  What false positive problem?
> 
> typedefs listed on platforms that match identifiers in our code that are
> _not_ typedefs.

Does this actually happen anywhere?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Bruce Momjian]

Robert Haas wrote:
> > Wow, that is a large list. ?Getting this all on a wiki is really what
> > needed to happen. ?I can't keep an open list current enough to be
> > useful.
> 
> Ah, glad you like.   I thought you'd been arguing the other side of
> that point with me for several days, but no matter - it seems like we
> might be converging on some kind of consensus here.

I prefer to do as little as possible.

> >> I think we should also boot everything in the "pre-existing bugs"
> >> category, and the first two items from the "questions" category, which
> >> don't seem important enough to worry about at this stage of the game.
> >> That would leave us with 14 items, all of which look reasonably
> >> relevant and 8.4-related.
> >
> > I think pushing "pre-existing bugs" to 8.5 is a mistake, first from a
> > software quality standpoint, and second because we are going to have a
> > lots of downtime during beta while we wait for feedback, so we can work
> > on some of these issues then. ?These things are not going to be any
> > easier to fix during 8.5 than now so let's make 8.4 as good as we can
> > without overly-delaying it.
> 
> What is the threshold for "has to be fixed before we can go to beta"
> versus "has to be fixed before release"?  I'm not opposed to fixing
> the bugs, but it seems like every day that we postpone cutting a beta
> is one more day until release, and so I think our immediate goal
> should be to fix all of the things that need to be fixed before beta
> can start.

Well, we don't want to be changing user-visible behavior during beta,
but anything we would fix in a minor release can be fixed during beta
too.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Bruce Momjian]

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> 
> > Well, as you, I was hoping for a clear solution, and it seems we don't
> > have one.  I think the false-positives problem is real and might make
> > the greater code coverage of the buildfarm worse than what we did for
> > 8.3.
> 
> Huh?  What false positive problem?

typedefs listed on platforms that match identifiers in our code that are
_not_ typedefs.

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian  writes:
> > Frankly, I don't remember anyone complaining we didn't find any typedefs
> > in pgindent,
> 
> There are lots and lots of places where it's obvious that pgindent
> was misinformed on the subject, because it puts in a space where there
> should not be one, eg "typename * ptr" instead of "typename *ptr".
> Maybe I'm just being too anal in worrying about a space here or a space
> there ... but then why do we run the thing at all?

Sure, why not make it as good as we can.

> This is the first time I've heard anyone suggest that false positives
> could be a problem.  What exactly would be the results of a false
> match?  Would it look worse than the false negatives do?

Well, I assume a false positive would do the opposite, meaning it would
not have a space where it should have one.  I am also worried about a
typedef list that changes from release to release as buildfarm members
change;  variability might be worse than correctness in this case.

Anyway, I think a diff of using my list and Andrew's list will show us
which one gets things clearest;  the diff is going to highlight only
cases where the typedef lists change formatting.

Andrew, where exactly is the list I should try?

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

  + If your life is a hard drive, Christ can be your backup. +

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Any reason not to return row_count in cursor of plpgsql?

[David Fetter]

On Fri, Mar 27, 2009 at 08:59:29PM +, Andrew Gierth wrote:
> > "Tom" == Tom Lane  writes:
> 
>  > Andrew Gierth  writes:
>  >> GET DIAGNOSTICS ROW_COUNT is documented as working for all commands;
>  >> if it doesn't work for MOVE (and FETCH), that's a bug.
> 
>  Tom> Or a documentation problem.  I don't see any claim that it works for
>  Tom> "all commands" anyway.
> 
> "This command allows retrieval of system status indicators. Each item
> is a key word identifying a state value to be assigned to the
> specified variable (which should be of the right data type to receive
> it). The currently available status items are ROW_COUNT, the number of
> rows processed by the last SQL command sent down to the SQL engine,
> and RESULT_OID, the OID of the last row inserted by the most recent
> SQL command. Note that RESULT_OID is only useful after an INSERT
> command into a table containing OIDs."
> 
> The idea that fetch/move should _intentionally_ not set ROW_COUNT is
> beyond ludicrous.

It's a flat-out bug not to have FETCH/MOVE set this.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Any reason not to return row_count in cursor of plpgsql?

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 > Andrew Gierth  writes:
 >> GET DIAGNOSTICS ROW_COUNT is documented as working for all commands;
 >> if it doesn't work for MOVE (and FETCH), that's a bug.

 Tom> Or a documentation problem.  I don't see any claim that it works for
 Tom> "all commands" anyway.

"This command allows retrieval of system status indicators. Each item
is a key word identifying a state value to be assigned to the
specified variable (which should be of the right data type to receive
it). The currently available status items are ROW_COUNT, the number of
rows processed by the last SQL command sent down to the SQL engine,
and RESULT_OID, the OID of the last row inserted by the most recent
SQL command. Note that RESULT_OID is only useful after an INSERT
command into a table containing OIDs."

The idea that fetch/move should _intentionally_ not set ROW_COUNT is
beyond ludicrous.

-- 
Andrew.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] small but useful patches for text search

[Tom Lane]

Oleg Bartunov  writes:
> I and Teodor have several small, but useful patches for text search:
> ...
> We would like to have your opinion what to do with these patches - leave them
> for 8.5 or provide them to  hackers to review for 8.4.

I think the general consensus is that these were submitted too late for
8.4.  Sorry ... the unaccent filter sounds particularly useful.  Please
submit them as patches for CommitFest 2009-First.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] improving concurrent transactin commit rate

[Sam Mason]

On Wed, Mar 25, 2009 at 01:48:03PM -0500, Kenneth Marshall wrote:
> On Wed, Mar 25, 2009 at 05:56:02PM +, Sam Mason wrote:
> > On Wed, Mar 25, 2009 at 12:01:57PM -0500, Kenneth Marshall wrote:
> > > Are you sure that you are able to actually drive the load at the
> > > high end of the test regime? You may need to use multiple clients
> > > to simulate the load effectively.
> > 
> > Notice that the code is putting things into the background and then
> > waiting for them to finish so there will be multiple clients.  Or maybe
> > I'm misunderstanding what you mean.
> 
> I did notice how your test harness was designed.

OK, that's turned out to be a good point.  I've now written five
different versions and they don't seem to give the results I'm expecting
at all!

Running tests from another machine seems to slow all tests down; I'd put
this down to the increased latency between server and client but am not
sure how to demonstrate (i.e. "prove" in layman terms) this.

I've got my original shell based approach, a Python version and three
C versions (fork, pthreads and select based concurrency).  The most
scalable, by quite a long way, is the Python version and I don't
understand why.  I've plotted the mean transactions per second (and
standard deviation) for all tests in the following SVG file:

  http://samason.me.uk/~sam/pg-concurrency/compare.svg

The Python version is pretty linear up to 18 clients and then seems to
hit a wall; all the other versions petered out much earlier.  The fact
I'm IO bound would mean the shell and C based approaches are going to
be similar, but why is the Python version so much faster?  CPU time was
highest in the shell based version, generally topping out around 50%
utilisation but the others topped out at around 35%; so I'd say I was
still IO bound.

The source for the tests is available here:

  http://samason.me.uk/~sam/pg-concurrency/concurrent.sh
  http://samason.me.uk/~sam/pg-concurrency/concurrent.py
  http://samason.me.uk/~sam/pg-concurrency/concurrent-fork.c
  http://samason.me.uk/~sam/pg-concurrency/concurrent-pthreads.c
  http://samason.me.uk/~sam/pg-concurrency/concurrent-select.c

I think I'm abusing things a bit with my fork based version; it
all seems to work OK but I wouldn't trust this style in real code.
Otherwise, if people have comments about how to improve things I'd be
interested to know.

-- 
  Sam  http://samason.me.uk/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Tom Lane]

It seems that we have full consensus about the following Open Items
not being material for 8.4, so I'm going to move them to the TODO
list or Commitfest 2009-First as appropriate:

* Change behavior of statement-level triggers for inheritance cases?

No patch, no interest in making it happen for 8.4 -> TODO

* PQinitSSL broken in some use cases

Arguably a bug, but Merlin was one of the instigators; since he's not
happy with where we are, it should be postponed.

* postgresql.conf: patch to have ParseConfigFile report all parsing errors, 
then bail

Not ready, and submitted very long past feature freeze -> commitfest

* small but useful patches for text search

Also submitted too late -> commitfest

* Have \d show child tables that inherit from the specified parent

Ditto.  (The other \d issues relate to already-made 8.4 changes,
though, so they still need consideration; or else an explicit
decision that the current behavior is OK.)

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Documentation Update: Document pg_start_backup checkpoint behavior

[Michael Renner]

Hi,

small patch for the documentation describing the current pg_start_backup 
checkpoint behavior as per 
http://archives.postgresql.org//pgsql-general/2008-09/msg01124.php .


Should we note down a TODO to revisit the current checkpoint handling?

best regards,
Michael
diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index 02545f1..6ea9488 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -737,12 +737,8 @@ SELECT pg_start_backup('label');
  (see the configuration parameter
  ).  Usually
  this is what you want because it minimizes the impact on query
- processing.  If you just want to start the backup as soon as
- possible, execute a CHECKPOINT command
- (which performs a checkpoint as quickly as possible) and then
- immediately execute pg_start_backup.  Then there
- will be very little for pg_start_backup's checkpoint
- to do, and it won't take long.
+ processing.  Unfortunately it's currently not possible to expedite
+ the checkpointing done by pg_start_backup.
 



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Solaris getopt_long and PostgreSQL

[Tom Lane]

Zdenek Kotala  writes:
> Dne 17.03.09 19:48, Chuck McDevitt napsal(a):
>> Any obviously, we don't just use ours for platforms with no or broken 
>> getopt_long, 
>> since we are talking Solaris (which has a bug in getopt, but 
>> getopt_long works fine)

> Just for clarification:

> It is not bug in Solaris.

Well, "bug" in the sense that it doesn't do what we want it to.

After reviewing this thread and the one that led up to the 8.3 behavior,
it seems clear that we failed to draw a distinction between getopt and
getopt_long when we should have.  We don't like Solaris' getopt but
there seems no reason not to use Solaris' getopt_long.  So Zdenek's
suggestion to change configure seems the correct fix, and I've done
that.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Any reason not to return row_count in cursor of plpgsql?

[Tom Lane]

Andrew Gierth  writes:
> GET DIAGNOSTICS ROW_COUNT is documented as working for all commands;
> if it doesn't work for MOVE (and FETCH), that's a bug.

Or a documentation problem.  I don't see any claim that it works for
"all commands" anyway.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Error message and infinite date and timestamp conversion in XML

[Tom Lane]

Bernd Helmle  writes:
> map_sql_value_to_xml_value() currently errors out with a more or less vague 
> error message, when a date or timestamp datatype with an infinite value is 
> converted to XML. This is likely to create some confusion, especially when 
> you have to debug some complex procedures and involved XML conversions. I 
> propose to add the attached DETAIL to this error message, so people will 
> get an idea what's currently going wrong.

Done, but I noticed while testing that it's not real consistent:

regression=# select xmlelement(name foo, 'infinity'::timestamp);
ERROR:  timestamp out of range
DETAIL:  XML does not support infinite timestamp values.
regression=# select xmlelement(name foo, xmlattributes('infinity'::timestamp as 
bar));   
  xmlelement   
---
 
(1 row)

Should we consider doing something about that, or is it okay as-is?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Tom Lane]

Josh Berkus  writes:
> And Magnus fixed this one:
>  * Path separator consistency on Windows

Uh, no, that's still an open issue.  Magnus put up a proposed patch that
I didn't like.  I think it's arguable that we should be going the other
way: convert backslashes to slashes.  Magnus's patch is the first step
towards trying to make the Windows port uniformly work with backslashes,
which is going to be an enormous mess and not worth the trouble IMHO.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Robert Haas]

On Fri, Mar 27, 2009 at 1:46 PM, Josh Berkus  wrote:
> All,
>
>> On Fri, Mar 27, 2009 at 2:58 AM, Robert Haas
>>  wrote:
>>>
>>> I think we should also boot everything in the "pre-existing bugs"
>>> category,
>
> I don't agree.  I think we should fix as many of those as we can without
> holding up the release.  Having been (briefly) in charge of Another Open
> Source Database's bug list, I've seen where ignoring pre-existing bugs can
> lead, and it's not at all pretty.

I wish to recant my previous statement.  I don't want to boot them - I
just don't want to hold up beta for them.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Josh Berkus]

All,


On Fri, Mar 27, 2009 at 2:58 AM, Robert Haas  wrote:

I think we should also boot everything in the "pre-existing bugs"
category,


I don't agree.  I think we should fix as many of those as we can without 
holding up the release.  Having been (briefly) in charge of Another Open 
Source Database's bug list, I've seen where ignoring pre-existing bugs 
can lead, and it's not at all pretty.


These bugs strike me as especially pernicious and to need fixing before 
8.4 release (but NOT before Beta):


* GiST picksplit (maybe GIN too?) can fail
* Perl/libxml incompatibility
* BUG #4721: bad side-effects of limiting number of clauses that 
predtest will consider

* BUG #4694: uppercase path problem on Windows

This one is also really bad, but probably only Doc-patchable.  However, 
can SQL/XML really be said to be core functionality if it only works in 
UTF-8?

* BUG #4622: xpath only work in utf-8 server encoding

I'll also take a stab at this one, because it looks easy:
* contrib/intarray opclass definition needs updating

And Magnus fixed this one:
* Path separator consistency on Windows

The other "existing" bugs I think relate to extreme corner cases (e.g. 
ENUMs of DOMAINS) and/or may be feature requests rather than bugs (e.g. 
Cover Density Ranking) so I think can safely be put off until 8.4.1 or 
later.


--Josh Berkus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Robert Haas]

On Fri, Mar 27, 2009 at 12:33 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> I think this is way over-engineered.  All we really need here is a
>> command along the lines of RESET ALL AS CURRENT USER that gives every
>> GUC the value it would have had if you logged in under the current
>> user's account.  Simple, clean, no new keywords.
>
> Doesn't do anything for autovacuum though...

Nope, but I think that can be solved separately.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Tom Lane]

Josh Berkus  writes:
>> Simon's idea of "profiles" sounds worth pursuing to me, but clearly
>> it's not happening for 8.4.

> I don't see why having a *separate* concept of profiles in addition to 
> the ROLES is helpful.  It seems like building a whole new house when all 
> we really need is to expand the garage.

Simon already pointed out one major reason: we can define the semantics
of such things without creating any backwards-compatibility issues,
whereas fooling with the behavior of roles by themselves is likely to
create some issues.

However, this is all 8.5 material in any case, and I'm going to stop
paying attention now because I'm trying to get to 8.4 beta.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Merlin Moncure]

On Fri, Mar 27, 2009 at 11:42 AM, Tom Lane  wrote:
>> PQinitSSL broken in some use cases
>
> This is a hard case.  It's arguably a bug fix, but not one that we could
> back-patch.  I think we would have applied it by now if there were
> consensus on which solution to pick.

I think the consensus we were headed towards was wrong, or at least
not completely hashed out.  IMO, the correct solution to this class of
problem is a generic library initialization routine (PQinit) that can
handle things of this nature without breaking the ABI down the line.
This solution felt controversial, and I definitely don't want to hold
up 8.4 trying to get it right.

So, my vote is to document the issue for 8.4, and hopefully come up
with something better for 8.5.

merlin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Josh Berkus]

Tom,


BTW, does pg_dumpall know to dump ALTER USER SET settings attached
to built-in roles (such as the proposed "autovacuum" role)?  I'd bet
it doesn't do that.  Even if it does, that seems like a more awkward
way to push settings over to a new installation than copying your
postgresql.conf file.

Simon's idea of "profiles" sounds worth pursuing to me, but clearly
it's not happening for 8.4.


I don't see why having a *separate* concept of profiles in addition to 
the ROLES is helpful.  It seems like building a whole new house when all 
we really need is to expand the garage.


--Josh

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Tom Lane]

Robert Haas  writes:
> On Fri, Mar 27, 2009 at 9:47 AM, Peter Eisentraut  wrote:
>> But there is `hostname` and `hostname --fqdn`, both of which are well-defined
>> independent of a connection.

> But they aren't guaranteed to return anything useful, and IME often don't.

I think "well defined" is stretching it anyway.  I see different
behaviors (partially or fully qualified hostname) on my different Unix
machines.  --fqdn appears particularly useless, as Fedora 10 reports
this:

$ hostname --fqdn
localhost.localdomain

and my other machines don't recognize the switch at all.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Tom Lane]

Robert Haas  writes:
> I think this is way over-engineered.  All we really need here is a
> command along the lines of RESET ALL AS CURRENT USER that gives every
> GUC the value it would have had if you logged in under the current
> user's account.  Simple, clean, no new keywords.

Doesn't do anything for autovacuum though...

BTW, does pg_dumpall know to dump ALTER USER SET settings attached
to built-in roles (such as the proposed "autovacuum" role)?  I'd bet
it doesn't do that.  Even if it does, that seems like a more awkward
way to push settings over to a new installation than copying your
postgresql.conf file.

Simon's idea of "profiles" sounds worth pursuing to me, but clearly
it's not happening for 8.4.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Robert Haas]

On Fri, Mar 27, 2009 at 9:47 AM, Peter Eisentraut  wrote:
> On Friday 27 March 2009 14:46:32 Greg Stark wrote:
>> Regarding using the hostname of the system... There's no such thing.
>> Interfaces have names, hosts can have multiple interfaces so the can
>> have multiple names...
>
> But there is `hostname` and `hostname --fqdn`, both of which are well-defined
> independent of a connection.

But they aren't guaranteed to return anything useful, and IME often don't.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Robert Haas]

On Fri, Mar 27, 2009 at 4:04 AM, Simon Riggs  wrote:
> On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote:
>
>> I was just noticing that doing SET ROLE changes the current session's
>> priviledges, but not any runtime configuration parameters (like work_mem
>> or statement_timeout) associated with the new role.
>>
>> This is as documented (although I want to add a line to SET ROLE docs)
>> but is it the behavior we want?  I for one would like SET ROLE to change
>> runtime configs.
>
> Thinking some more about the requirements for this and various
> objections.
>
> I'm guessing that there's a small cluster of parameters you want to
> alter using this. It seems easier to think about those parameters and to
> look at ways of managing those. Perhaps what we need is not parameters
> on roles, but a related concept: profiles.

I think this is way over-engineered.  All we really need here is a
command along the lines of RESET ALL AS CURRENT USER that gives every
GUC the value it would have had if you logged in under the current
user's account.  Simple, clean, no new keywords.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Potential problem with HOT and indexes?

[Tom Lane]

I wrote:
> We could reorganize the function slightly so that the IndexInfo is
> still available after the PG_TRY block, and then do something like
> "if (!indexInfo->ii_BrokenHotChain) reset indcheckxmin".  This would
> ensure that we didn't wipe the flag if index_build had just set it.
> If I'm wrong and this can never happen, no harm done.

Committed with that change.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Tom Lane]

Robert Haas  writes:
> http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items

> That includes a whole slough of patches that weren't submitted until
> after November 1st and which I think should probably be bumped en
> masse to 8.5:

> Change behavior of statement-level triggers for inheritance cases?

Agreed: no patch submitted, not a bug (we'd never consider back-patching
such a change), and no obvious reason why it should be part of 8.4
rather than waiting.

> GetCurrentVirtualXIDs() (is this patch safe?)

This is actually an extract from the hot standby patch, so I think it's
unfair to claim it was submitted too late.  If I thought it were safe
I'd apply it, but I'm not convinced.

> PQinitSSL broken in some use cases

This is a hard case.  It's arguably a bug fix, but not one that we could
back-patch.  I think we would have applied it by now if there were
consensus on which solution to pick.

> postgresql.conf: patch to have ParseConfigFile report all parsing
> errors, then bail

Agreed, this is in the "too late" category.

> small but useful patches for text search

Ditto.

> Additional DTrace Probes

This arguably is part of the existing 8.4 dtrace-related changes,
but it hasn't gotten any review that I saw.  (And after having found
a number of problems in the earlier dtrace patches, I'm disinclined
to let it in without close review ...)

> pg_standby trigger behavior is dangerous

Another sort-of-a-bug case; I'm inclined to leave it on the list
until a bit of consensus emerges.

> psql \d commands and information_schema (already in CommitFest 2009-First)

This isn't a feature, it's a bug fix for the already committed changes
in \d's system-vs-user filtering.  (Which I remain terribly unhappy with
in general, but that's a different list entry...)

> Have \d show child tables that inherit from the specified parent
> (already in CommitFest 2009-First)

Agreed, this is a new feature that can wait.

> I think we should also boot everything in the "pre-existing bugs"
> category,

Well, some of the things on this page are beta blockers and some are
just stuff that we'd like to address before final.  Of the "pre existing
bugs", the only one that I'm really concerned about addressing before
beta is the polymorphic types vs. domains issue.  Changing that has some
potential for breaking user apps so it'd be polite to make it happen
before beta starts.  The rest should stay on the page, though, to be
addressed during beta.

> and the first two items from the "questions" category, which
> don't seem important enough to worry about at this stage of the game.

Both of those things are related to 8.4 feature changes, so we should
either do them now or decide we won't do them.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tuplestore API problem

[Tom Lane]

Hitoshi Harada  writes:
> 2009/3/27 Hitoshi Harada :
>> 2009/3/27 Tom Lane :
>>> A brute-force solution is to change tuplestore_gettupleslot() so that it
>>> always copies the tuple, but this would be wasted cycles for most uses
>>> of tuplestores.  I'm thinking of changing tuplestore_gettupleslot's API
>>> to add a bool parameter specifying whether the caller wants to force
>>> a copy.

> Here's the patch. Hope there are no more on the same reason. It seems
> that we'd need to implement something like garbage collector in
> tuplestore, marking and tracing each row references, if the complete
> solution is required.

I don't like this; I'm planning to go with the aforementioned API
change instead.  The way you have it guarantees an extra copy cycle
even when tuplestore is already making a copy internally; and it doesn't
help if we find similar problems elsewhere.  (While I'm making the
API change I'll take a close look at each call site to see if it has
any similar risk.)

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] psql: Make tab completion work for ANALYZE VERBOSE ...

[Heikki Linnakangas]

Greg Sabino Mullane wrote:

I find that that particular rule is formatted differently than the
others. It took me a while to figure out how it works.


Yeah, me too, but I was trying to keep my change inline with the local logic, so
to speak. +1 to making it more consistent.


Ok, committed with the more consistent formatting.

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] typedefs for indent

[Alvaro Herrera]

Bruce Momjian wrote:

> Well, as you, I was hoping for a clear solution, and it seems we don't
> have one.  I think the false-positives problem is real and might make
> the greater code coverage of the buildfarm worse than what we did for
> 8.3.

Huh?  What false positive problem?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] delete quite skip updated rows

[Tom Lane]

Pavel Stehule  writes:
> explain analyze signalise one deleted row, but this is not true

This is not a bug.  The explain output tells you that one row was
found by the seqscan, which is true.  It doesn't tell you whether
the delete machinery did anything with that row.  The row could
be suppressed from deletion by a BEFORE DELETE trigger, or because
it was already updated in the current command (as is the case here).

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Simon Riggs]

On Fri, 2009-03-27 at 10:25 -0400, Tom Lane wrote:
> Peter Eisentraut  writes:
> > Simon Riggs wrote:
> >> If we go with this, I would suggest we make *neither* the default by
> >> removing -t, and adopting two new options: something like -f == fast
> >> failover, -p == patient failover.
> 
> > -m smart|fast|immediate :-)
> 
> +1 for using a "-m something" type of syntax instead of having to try to
> pick single-letter switches that are mnemonic for the different cases.
> But -1 to those particular mode names --- I think it will invite
> confusion with pg_ctl's behavior.

The choice is between

* one parameter with the option being given as text within trigger file

* two parameters naming different types of trigger file

I don't mind which, as long as it is one of those two, unless there is a
third way to specify things so that user has control at failover time. A
single -m option would hardcode that decision ahead of time, which is
undesirable behaviour, hence the additional complexity being discussed.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] delete quite skip updated rows

[Pavel Stehule]

Hello

one my customer reported following problem (checked on 8.4 and 8.3.6)

test case:

create table test(a integer);

create or replace function getid(_a integer) returns integer as $$
begin
  update test set a = _a where a = _a;
  return _a;
end;
$$ language plpgsql;

insert into test values(20);
select * from test where a = 20; --> one row
select getid(20); --> 20

the problem:

postgres=# delete from test where a = getid(20);
DELETE 0
Time: 1,510 ms

but

postgres=# explain analyze delete from test where a = getid(20);
   QUERY PLAN
-
 Seq Scan on test  (cost=0.00..640.00 rows=12 width=6) (actual
time=0.149..0.154 rows=1 loops=1)
   Filter: (a = getid(20))
 Total runtime: 0.221 ms
(3 rows)


explain analyze signalise one deleted row, but this is not true

regards
Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Tom Lane]

Peter Eisentraut  writes:
> Simon Riggs wrote:
>> If we go with this, I would suggest we make *neither* the default by
>> removing -t, and adopting two new options: something like -f == fast
>> failover, -p == patient failover.

> -m smart|fast|immediate :-)

+1 for using a "-m something" type of syntax instead of having to try to
pick single-letter switches that are mnemonic for the different cases.
But -1 to those particular mode names --- I think it will invite
confusion with pg_ctl's behavior.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] postgresql.conf: patch to have ParseConfigFile report all parsing errors, then bail

[Tom Lane]

Selena Deckelmann  writes:
> ParseConfigFile currently exits on the first parsing error. Changed 
> guc_file.l to report all parsing errors before exiting:

This seems like basically a good idea, but consider what happens if
you make a really major-league screwup in your postgresql.conf
(say, you accidentally copy the text of "War and Peace" into it).
You'll get megabytes of mostly-useless bleating in your log file.
Multiply that by the number of active backends, if you're unlucky
enough to have done it at log level DEBUG2.  And not only are you
bloating your log, but it's going to take a fair amount of time
for all the backends to read and complain (or not) about the whole
file.

So I think a couple of safety valves would be prudent:

1. If IsUnderPostmaster, fall out after the first error, same as now.

2. Even in the postmaster, count the number of errors reported,
and give up after say 100.  By that point it's much more likely
that you're reading War and Peace than that you're continuing to
contribute to the enlightenment of the DBA.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Mentors needed urgently for SoC & PostgreSQL Student Internships

[Gabriele Bartolini]

Ciao Josh,

Josh Berkus ha scritto:
What this all hinges on is getting some really solid mentors who have 
projects they'd like students to work on, and can commit 
unconditionally to having 5 hours a week or more, over a 3-month 
period, to work with the student.
Thanks for letting us know. However for this year we (as 2ndQuadrant) 
have just planned to collaborate with some Italian Universities, 
starting from the University of Pisa (I spoke to their IT students last 
Monday). I don't think we can dedicate more time to mentoring in the 
short period (that's a pity, I know). :(


However, thanks again for keeping us informed.

Ciao,
Gabriele

--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Robert Haas]

On Thu, Mar 26, 2009 at 11:06 PM, Guillaume Smet
 wrote:
> On Fri, Mar 27, 2009 at 2:58 AM, Robert Haas  wrote:
>> That includes a whole slough of patches that weren't submitted until
>> after November 1st and which I think should probably be bumped en
>> masse to 8.5:
>>
>> postgresql.conf: patch to have ParseConfigFile report all parsing
>> errors, then bail
>
> Not sure about this one. A similar patch for the pg_hba.conf file
> submitted after the commit fest (IIRC) has already been commited.

Well, if we keep slipping one more thing in, we're never going to get
this thing out the door.  The fact that we've already let some extra
things slip in is not a good reason to slip in moor things.  The patch
may be great stuff and if a committer wants to pick it up and commit
it, that's their prerogative.  But to my mind this sounds like an
enhancement, and since we've supposedly been in feature freeze for
almost 5 months, I see no reason to argue that it MUST happen before
beta.

>> pg_standby trigger behavior is dangerous
>
> This one has to be fixed IMHO. I'm not sure how but we have to take a
> decision. The current behaviour is really dangerous for most of our
> users.

Perhaps so, but again, it's not a new regression, so why should it be
considered a blocker for 8.4beta?

Most of these are great patches.  I would love to see them committed.
But at some point you have to draw a line in the sand.  A decision was
taken that the last commitfest for 8.4 began 11/1/08.  We have finally
managed to END that commitfest.  I don't wish to have another,
unofficial one before beta.  I want to go to beta as soon as humanly
possible and try to get this release out the door sometime in 2009.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Peter Eisentraut]

On Friday 27 March 2009 14:46:32 Greg Stark wrote:
> Regarding using the hostname of the system... There's no such thing.
> Interfaces have names, hosts can have multiple interfaces so the can
> have multiple names...

But there is `hostname` and `hostname --fqdn`, both of which are well-defined 
independent of a connection.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] psql: Make tab completion work for ANALYZE VERBOSE ...

[Greg Sabino Mullane]

> I find that that particular rule is formatted differently than the
> others. It took me a while to figure out how it works.

Yeah, me too, but I was trying to keep my change inline with the local logic, so
to speak. +1 to making it more consistent.

> While we're at it, any idea what the logic behind this rule is:
> 
>> else if ((pg_strcasecmp(prev_wd, "ANALYZE") == 0 &&
>>   pg_strcasecmp(prev2_wd, "VERBOSE") == 0) ||
>>  (pg_strcasecmp(prev_wd, "VERBOSE") == 0 &&
>>   pg_strcasecmp(prev2_wd, "ANALYZE") == 0))
>> COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
> 
> ? The first part of that I understand, "ANALYZE VERBOSE", but "VERBOSE
> ANALYZE" isn't valid SQL.

I suspect this is to catch "VACUUM VERBOSE ANALYZE" (where VACUUM would be the
implicit prev3_wd)

-- 
Greg Sabino Mullane g...@endpoint.com
End Point Corporation
PGP Key: 0x14964AC8



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] 8.4 open items list

[Guillaume Smet]

On Fri, Mar 27, 2009 at 9:38 AM, Magnus Hagander  wrote:
> That can be argued to just be completing the pg_hba rewrite stuff that
> happened long before november with the final logical step.
>
> I guess if you stretch that definition as well, this could also be an
> extension to that :)

Yes, that was my point. I think it's better to be consistent for all
the configuration files.

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Simon Riggs]

On Fri, 2009-03-27 at 13:19 +0100, Guillaume Smet wrote:
> On Fri, Mar 27, 2009 at 12:56 PM, Peter Eisentraut  wrote:
> > Simon Riggs wrote:
> >>
> >> If we go with this, I would suggest we make *neither* the default by
> >> removing -t, and adopting two new options: something like -f == fast
> >> failover, -p == patient failover.
> >
> > -m smart|fast|immediate :-)
> 
> The advantage of having 2 options (or the ability to put a string
> value in the trigger file) is that you can choose the behaviour when
> you need to trigger it (you just have to use the 2 options with 2
> different filenames). I don't think it's the case with your proposal.

Yes, sorry. I meant we should use the naming Peter suggests.

So we would have two triggers, but call them fast and smart, rather than
fast and patient.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Crash in gist insertion on pathological box data

[Sergey Konoplev]

On Thu, Mar 26, 2009 at 5:39 PM, Andrew Gierth
 wrote:
> A user on IRC reported a crash (backend segfault) in GiST insertion
> (in 8.3.5 but I can reproduce this in today's HEAD) that turns out
> to be due to misbehaviour of gist_box_picksplit.
>
> The nature of the problem is this: if gist_box_picksplit doesn't find
> a good disposition on the first try, then it tries to split the data
> again based on the positions of the box centers. But there's a problem
> here with floating-point rounding; it's possible for the average of N
> floating-point values to be strictly greater (or less) than all of the
> values individually, and the function then returns with, for example,
> all the entries assigned to the left node, and nothing in the right
> node. This causes gistSplit to try and split the left node again, with
> predictable results.
>

I probably have a workaround. As I understand the problem it touches
gist indexes with one box type field only. After googling picksplit
and reading some info I supposed that If another (distinctive) field
would be appended to the index (after the box field) then another
(old) picksplit functionality would be started instead of new (buggy)
one. Andrew approved my assumption on IRC. So I found all the indexes
(gist) with one box field and recreated them with extra column (bigint
PK field). Well on this moment our DB has been working for a 22 hour
without crashes and errors.

Of course not being pg-hacker I can't guaranty that my assumption is
absolutely correct and I welcome your criticism.

-- 
Regards,
Sergey Konoplev
--
PostgreSQL articles in english & russian
http://gray-hemp.blogspot.com/search/label/postgresql/

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Heikki Linnakangas]

Fujii Masao wrote:

On Thu, Mar 26, 2009 at 8:54 PM, Guillaume Smet
 wrote:

On Thu, Mar 26, 2009 at 11:50 AM, Simon Riggs  wrote:
I like the idea of removing -t and adding 2 new options so that people
are warned about the intended behavior.


OK, I'll change the patch as Simon suggested; removing -t and adding
two new options: -f = fast failover (existing behavior), -p patient failover.
Also I'll default the patient failover, so it's performed when the signal
(SIGINT or SIGUSR1) is received.


Uh oh, that's going to be quite tricky with signals. Remember that 
pg_standby is called for each file. A trigger file persists until it's 
deleted, but a signal will only be received by the pg_standby instance 
that happens to be running at the time.


Makes me wonder if the trigger pg_standby with signals is reliable to 
begin with. What if the backend is just processing a file when the 
signal is fired, and there's no pg_standby process running at the moment 
to receive it? Seems like the signaler needs to loop until it has 
successfully delivered the signal to a pg_standby process, which seems 
pretty ugly.


Given all the recent trouble with signals, and the fact that it's 
undocumented, perhaps we should just rip out the signaling support from 
pg_standby.


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Greg Stark]

Regarding using the hostname of the system... There's no such thing.  
Interfaces have names, hosts can have multiple interfaces so the can  
have multiple names...


I haven't follwes the discussion so I'm not sure if you have an  
existing connection. If so you can get the local interface address  
from the connection and look up the name for that address. But that  
only works if you already have a socket.


--
Greg


On 27 Mar 2009, at 07:49, Magnus Hagander  wrote:


Peter Eisentraut wrote:

Magnus Hagander wrote:

Perhaps it's enough to add a "localssl" row to pg_hba.conf?
That defeats the point, I think.  You don't want the server to  
determine

whether the client should verify the server.


Good point. OTOH, client behavior can be controlled now fine by  
setting

it to "require" or "prefer" - I think that's enough.

However, we might want a simple ssl_local=true/false parameter on  
the

server that turns it on/off completely.


But the choice is that of the client:

1) I want a connection with server authentication. or

2) I want a fast connection, I don't care about server  
authentication.


No configuration knob in the server can ever solve this.

Of course the client has all the sslmode parameters to make its  
wishes

known, but the point here is that the *default* should possibly be a
different one in the case of Unix-domain sockets.


Agreed, except I really don't see why the default should be different.



Another thought I had is to somehow employ hostaddr, as in
"hostaddr=/tmp host=real.hostname.lan".


That seems rather abusive.


True, but Kerberos more or less works this way.  hostaddr is where to
connect, host is what to use for authentication.


Yeah, and it has always annoyed me :)

I think it'd be better to just gtet the hostname of the system, and  
use

that.


Another^2 thought is to just examine the certificate for the  
local host

name, which the client can find out itself.


That could work. In which case we should probably consider doing the
same thing for "localhost" or "127.0.0.1" connections.


Hmm, as per my statement above, this would be the right thing to  
do. But

I think https works differently.  Tricky ...


Yeah, https requires the cert to be named "localhost". It's a smaller
issue on a Unix system since the http/https ports require root to bind
to them, and if somebody is root no amount of SSL is going to help you
anyway...

//Magnus

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Guillaume Smet]

On Fri, Mar 27, 2009 at 8:27 AM, Guillaume Smet
 wrote:
> 2009/3/27 Tatsuhito Kasahara :
>> But if I can also check last query string, I guess which apllication
>> do that and point out the problem point.
>
> Oh, I just understand why you want this patch. I usually have one
> database per server so I didn't see your point.

Thinking a bit more about it: the datname column in the
pg_stat_activity view gives you the database concerned and usename the
user used. So I still don't see your point: you can use different user
to distinguish the applications.

Moreover, if you're using connection pooling (which is more and more
common) and the same user for connecting to the database, you won't be
able to know if it's really the last query which causes the problem
(from my experience, it's usually not).

Being able to detect which application is running which query on the
very same database with the very same user seems like something not so
obvious and the use case seems to be pretty narrow. And IMHO, even if
we suppose you can make the difference between the applications with
only one query, you won't be able to limit your investigation to this
application.

So, in fact, I'm still not convinced. Could you detail a bit more how
you plan to use it?

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Guillaume Smet]

On Fri, Mar 27, 2009 at 3:38 AM, Fujii Masao  wrote:
> OK, I'll change the patch as Simon suggested; removing -t and adding
> two new options: -f = fast failover (existing behavior), -p patient failover.
> Also I'll default the patient failover, so it's performed when the signal
> (SIGINT or SIGUSR1) is received.

I'm wondering if we should consider backpatching this one. Even if the
feature works as advertised in the documentation.

It's a very surprising behaviour and I'm pretty sure someone will
shoot himself in the foot with it, if not already done.

Considering backpatching might change the way we want to fix it.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Guillaume Smet]

On Fri, Mar 27, 2009 at 12:56 PM, Peter Eisentraut  wrote:
> Simon Riggs wrote:
>>
>> If we go with this, I would suggest we make *neither* the default by
>> removing -t, and adopting two new options: something like -f == fast
>> failover, -p == patient failover.
>
> -m smart|fast|immediate :-)

The advantage of having 2 options (or the ability to put a string
value in the trigger file) is that you can choose the behaviour when
you need to trigger it (you just have to use the 2 options with 2
different filenames). I don't think it's the case with your proposal.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Guillaume Smet]

On Fri, Mar 27, 2009 at 4:24 AM, Robert Haas  wrote:
> Perhaps so, but again, it's not a new regression, so why should it be
> considered a blocker for 8.4beta?

I agree they shouldn't. You were talking about bumping them to 8.5
which is a totally different thing.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] tuplestore API problem

[Hitoshi Harada]

2009/3/27 Hitoshi Harada :
> 2009/3/27 Tom Lane :
>> By chance I discovered that this query in the regression tests
>>
>> SELECT ntile(NULL) OVER (ORDER BY ten, four), ten, four FROM tenk1 LIMIT 2;
>>
>> stops working if work_mem is small enough: it either dumps core or
>> delivers wrong answers depending on platform.
>>
>> After some tracing I found out the reason.  ExecWindowAgg() does this:
>>
>>    if (!tuplestore_gettupleslot(winstate->buffer, true,
>>                                 winstate->ss.ss_ScanTupleSlot))
>>        elog(ERROR, "unexpected end of tuplestore");
>>
>> and then goes off and calls the window functions (ntile() here), and
>> expects the ScanTupleSlot to still be valid afterwards.  However,
>> ntile() forces us to read to the end of the input to find out the number
>> of rows.  If work_mem is small enough, that means the tuplestore is
>> forced into dump-to-disk mode, which means it releases all its in-memory
>> tuples.  And guess what: the ScanTupleSlot is pointing at one of those,
>> it doesn't have its own copy of the tuple.  So we wind up trying to read
>> from a trashed bit of memory.
>>
>> A brute-force solution is to change tuplestore_gettupleslot() so that it
>> always copies the tuple, but this would be wasted cycles for most uses
>> of tuplestores.  I'm thinking of changing tuplestore_gettupleslot's API
>> to add a bool parameter specifying whether the caller wants to force
>> a copy.
>>
>> Comments, better ideas?
>
> Is this tuplestore API problem? ISTM this is window function's
> problem. I think my early code was holding heaptuple instead of
> tupleslot for the current row. At a glance, the issue appears in only
> current row in window function, which fetches row and uses it later
> after storing following rows in some cases. So a brute-force solution
> might be that ExecWindowAgg() copies the current row from tuplestore
> instead of pointing directly to inside tuplestore memory, not changing
> tuplestore API.
Here's the patch. Hope there are no more on the same reason. It seems
that we'd need to implement something like garbage collector in
tuplestore, marking and tracing each row references, if the complete
solution is required.

Regards,

-- 
Hitoshi Harada


windowagg_tempslot.20090327.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Simon Riggs]

On Fri, 2009-03-27 at 13:56 +0200, Peter Eisentraut wrote:
> Simon Riggs wrote:
> > If we go with this, I would suggest we make *neither* the default by
> > removing -t, and adopting two new options: something like -f == fast
> > failover, -p == patient failover.
> 
> -m smart|fast|immediate :-)

Yes, a better suggestion.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] New trigger option of pg_standby

[Peter Eisentraut]

Simon Riggs wrote:

If we go with this, I would suggest we make *neither* the default by
removing -t, and adopting two new options: something like -f == fast
failover, -p == patient failover.


-m smart|fast|immediate :-)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Guillaume Smet]

On Fri, Mar 27, 2009 at 9:07 AM, Simon Riggs  wrote:
> Or take it further back still and think about why "idle in transaction"
> occurs at all and fix *that*. Maybe not in Postgres at all, possibly in
> the driver or even higher up client stack.

>From my experience, the main reasons for this problem are:
- a poorly coded connection pool (a lot of people think they really
should write their connection pooling solution themselves...);
- a poorly coded application;
- bugs in the code.

So you really should try to fix them in your application first.

That said, I have one case in mind where I wasn't able to fix
completely the connection pool and the application and we still
encounter idle in transaction connections from time to time.

For this sort of case, a timeout would be a nice solution.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Magnus Hagander]

Peter Eisentraut wrote:
> Magnus Hagander wrote:
 Perhaps it's enough to add a "localssl" row to pg_hba.conf?
>>> That defeats the point, I think.  You don't want the server to determine
>>> whether the client should verify the server.
>>
>> Good point. OTOH, client behavior can be controlled now fine by setting
>> it to "require" or "prefer" - I think that's enough.
>>
>> However, we might want a simple ssl_local=true/false parameter on the
>> server that turns it on/off completely.
> 
> But the choice is that of the client:
> 
> 1) I want a connection with server authentication. or
> 
> 2) I want a fast connection, I don't care about server authentication.
> 
> No configuration knob in the server can ever solve this.
> 
> Of course the client has all the sslmode parameters to make its wishes
> known, but the point here is that the *default* should possibly be a
> different one in the case of Unix-domain sockets.

Agreed, except I really don't see why the default should be different.


>>> Another thought I had is to somehow employ hostaddr, as in
>>> "hostaddr=/tmp host=real.hostname.lan".
>>
>> That seems rather abusive.
> 
> True, but Kerberos more or less works this way.  hostaddr is where to
> connect, host is what to use for authentication.

Yeah, and it has always annoyed me :)

I think it'd be better to just gtet the hostname of the system, and use
that.


>>> Another^2 thought is to just examine the certificate for the local host
>>> name, which the client can find out itself.
>>
>> That could work. In which case we should probably consider doing the
>> same thing for "localhost" or "127.0.0.1" connections.
> 
> Hmm, as per my statement above, this would be the right thing to do. But
> I think https works differently.  Tricky ...

Yeah, https requires the cert to be named "localhost". It's a smaller
issue on a Unix system since the http/https ports require root to bind
to them, and if somebody is root no amount of SSL is going to help you
anyway...

//Magnus

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Peter Eisentraut]

Magnus Hagander wrote:

Perhaps it's enough to add a "localssl" row to pg_hba.conf?

That defeats the point, I think.  You don't want the server to determine
whether the client should verify the server.


Good point. OTOH, client behavior can be controlled now fine by setting
it to "require" or "prefer" - I think that's enough.

However, we might want a simple ssl_local=true/false parameter on the
server that turns it on/off completely.


But the choice is that of the client:

1) I want a connection with server authentication. or

2) I want a fast connection, I don't care about server authentication.

No configuration knob in the server can ever solve this.

Of course the client has all the sslmode parameters to make its wishes 
known, but the point here is that the *default* should possibly be a 
different one in the case of Unix-domain sockets.




Yeah, the problem is that there is only one server certificate.  Is it
possible/does it make sense to add an additional cn to the certificate?


Yes on both. Except the current version of libpq doesn't support this
:-( I was planning to add it (and still do), but the way to do it
appears to be fairly complex and completely undocumented. I did some
searching to find examples of it, but never followed through.


After further thought, this is probably not the right solution anyway. 
The security of all this depends on the fact that each common name only 
exists once within the scope of a CA.  The socket name or directory, 
however, is more or less them same for everyone.



Another thought I had is to somehow employ hostaddr, as in
"hostaddr=/tmp host=real.hostname.lan".


That seems rather abusive.


True, but Kerberos more or less works this way.  hostaddr is where to 
connect, host is what to use for authentication.



Another^2 thought is to just examine the certificate for the local host
name, which the client can find out itself.


That could work. In which case we should probably consider doing the
same thing for "localhost" or "127.0.0.1" connections.


Hmm, as per my statement above, this would be the right thing to do. 
But I think https works differently.  Tricky ...


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSL over Unix-domain sockets

[Peter Eisentraut]

Bruce Momjian wrote:

Peter Eisentraut wrote:

Bruce Momjian wrote:

I thought the logical solution to this was to place the socket in a
secure directory and not bother with SSL at all.
How would a client algorithmically determine whether the server socket 
was in a "secure" directory?


You have to configure your client to know that, but don't you need to
configure your client for SSL too?


Yes, but how exactly would a client know?  How is a "secure directory" 
defined, in terms of C library calls, say?


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Guillaume Smet]

On Fri, Mar 27, 2009 at 2:58 AM, Robert Haas  wrote:
> I think we should also boot everything in the "pre-existing bugs"
> category, and the first two items from the "questions" category, which
> don't seem important enough to worry about at this stage of the game.
> That would leave us with 14 items, all of which look reasonably
> relevant and 8.4-related.
>
> Comments?

FWIW, I posted my comments in the wiki. I tried to post useful
information about the status of each item I have an opinion about.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Magnus Hagander]

Guillaume Smet wrote:
> On Fri, Mar 27, 2009 at 2:58 AM, Robert Haas  wrote:
>> That includes a whole slough of patches that weren't submitted until
>> after November 1st and which I think should probably be bumped en
>> masse to 8.5:
>>
>> postgresql.conf: patch to have ParseConfigFile report all parsing
>> errors, then bail
> 
> Not sure about this one. A similar patch for the pg_hba.conf file
> submitted after the commit fest (IIRC) has already been commited.

That can be argued to just be completing the pg_hba rewrite stuff that
happened long before november with the final logical step.

I guess if you stretch that definition as well, this could also be an
extension to that :)


//Magnus


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] psql: Make tab completion work for ANALYZE VERBOSE ...

[Heikki Linnakangas]

Greg Sabino Mullane wrote:

Quick patch to fix the fact that the EXPLAIN ANALYZE VERBOSE is clobbering
tab-completion for ANALYZE VERBOSE.


Thanks.


*** tab-complete.c  24 Feb 2009 10:06:34 -  1.180
--- tab-complete.c  27 Mar 2009 01:29:06 -
***
*** 1627,1633 
else if (pg_strcasecmp(prev_wd, "VERBOSE") == 0 &&
 pg_strcasecmp(prev3_wd, "VACUUM") != 0 &&
 pg_strcasecmp(prev4_wd, "VACUUM") != 0 &&
!(pg_strcasecmp(prev2_wd, "ANALYZE") == 0 ||
  pg_strcasecmp(prev2_wd, "EXPLAIN") == 0))
{
static const char *const list_EXPLAIN[] =
--- 1627,1634 
else if (pg_strcasecmp(prev_wd, "VERBOSE") == 0 &&
 pg_strcasecmp(prev3_wd, "VACUUM") != 0 &&
 pg_strcasecmp(prev4_wd, "VACUUM") != 0 &&
! 			 ((pg_strcasecmp(prev2_wd, "ANALYZE") == 0 && 
! 			   pg_strcasecmp(prev3_wd, "EXPLAIN") == 0) ||

  pg_strcasecmp(prev2_wd, "EXPLAIN") == 0))
{
static const char *const list_EXPLAIN[] =


I find that that particular rule is formatted differently than the 
others. It took me a while to figure out how it works. All the others 
check the keywords from left to right, but this checks that the previous 
word is VERBOSE and works to the left from there, kind of. I also don't 
understand why the explicit check for VACUUM is there. It only makes a 
difference if you write something like "VACUUM EXPLAIN VERBOSE", which 
isn't valid. I guess it was needed before this fix to not match "VACUUM 
ANALYZE", but isn't anymore.


I'd suggest to write it like this:


else if ((pg_strcasecmp(prev2_wd, "EXPLAIN") == 0 &&
  pg_strcasecmp(prev_wd, "VERBOSE") == 0) ||
 (pg_strcasecmp(prev3_wd, "EXPLAIN") == 0 &&
  pg_strcasecmp(prev2_wd, "ANALYZE") == 0 &&
  pg_strcasecmp(prev_wd, "VERBOSE") == 0))



While we're at it, any idea what the logic behind this rule is:


else if ((pg_strcasecmp(prev_wd, "ANALYZE") == 0 &&
  pg_strcasecmp(prev2_wd, "VERBOSE") == 0) ||
 (pg_strcasecmp(prev_wd, "VERBOSE") == 0 &&
  pg_strcasecmp(prev2_wd, "ANALYZE") == 0))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);


? The first part of that I understand, "ANALYZE VERBOSE", but "VERBOSE 
ANALYZE" isn't valid SQL.


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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Any reason not to return row_count in cursor of plpgsql?

[Andrew Gierth]

> "Bruce" == Bruce Momjian  writes:

 >> hi all,
 >> 
 >> I read the code that it seems easy for the cursor in plpgsql to
 >> return ROW_COUNT after MOVE LAST etc. The SPI_processed variable
 >> already there, but didn't put it into estate structure, any reason
 >> for that?
 >> 
 >> thanks and best regards

 Bruce> Sorry, we have decided against this change because it might
 Bruce> break existing applications.

As they say on wikipedia, [citation needed]

GET DIAGNOSTICS ROW_COUNT is documented as working for all commands;
if it doesn't work for MOVE (and FETCH), that's a bug. It might be one
that's not appropriate to backpatch, but that's no excuse for not
fixing it in a new release.

It's especially egregious in that MOVE _does_ set FOUND.

diff -c -r1.235 pl_exec.c
*** pl_exec.c   23 Feb 2009 10:03:22 -  1.235
--- pl_exec.c   27 Mar 2009 10:44:08 -
***
*** 3368,3373 
--- 3368,3375 
exec_set_found(estate, n != 0);
}
  
+   estate->eval_processed = n;
+ 
return PLPGSQL_RC_OK;
  }
  
-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 release notes proof reading 1/2

[Andrew Gierth]

> "Guillaume" == Guillaume Smet  writes:

 Guillaume> - "While semi-joins merely replace existing IN joins,
 Guillaume> anti-joins are a new capability for NOT IN and NOT EXIST
 Guillaume> clauses (Tom) This improves optimization possibilities."
 Guillaume> -> remove the "(Tom)", s/EXIST/EXISTS/

Also  s/NOT IN and //  (anti-joins are not used for NOT IN due to the
awkward semantics of nulls). Should possibly also mention the fact that
some outer joins can be planned as anti-joins too.

-- 
Andrew (irc:RhodiumToad)

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] 8.4 open items list

[Robert Haas]

On Thu, Mar 26, 2009 at 10:11 PM, Bruce Momjian  wrote:
>> Hmm, well, Tom dropped a filtered version of your list into the open
>> items wiki page.
>>
>> http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items
>>
>> That includes a whole slough of patches that weren't submitted until
>> after November 1st and which I think should probably be bumped en
>> masse to 8.5:
>>
>> Change behavior of statement-level triggers for inheritance cases?
>> GetCurrentVirtualXIDs() (is this patch safe?)
>> PQinitSSL broken in some use cases
>> postgresql.conf: patch to have ParseConfigFile report all parsing
>> errors, then bail
>> small but useful patches for text search
>> Additional DTrace Probes
>> pg_standby trigger behavior is dangerous
>> psql \d commands and information_schema (already in CommitFest 2009-First)
>> Have \d show child tables that inherit from the specified parent
>> (already in CommitFest 2009-First)
>
> Wow, that is a large list.  Getting this all on a wiki is really what
> needed to happen.  I can't keep an open list current enough to be
> useful.

Ah, glad you like.   I thought you'd been arguing the other side of
that point with me for several days, but no matter - it seems like we
might be converging on some kind of consensus here.

>> I think we should also boot everything in the "pre-existing bugs"
>> category, and the first two items from the "questions" category, which
>> don't seem important enough to worry about at this stage of the game.
>> That would leave us with 14 items, all of which look reasonably
>> relevant and 8.4-related.
>
> I think pushing "pre-existing bugs" to 8.5 is a mistake, first from a
> software quality standpoint, and second because we are going to have a
> lots of downtime during beta while we wait for feedback, so we can work
> on some of these issues then.  These things are not going to be any
> easier to fix during 8.5 than now so let's make 8.4 as good as we can
> without overly-delaying it.

What is the threshold for "has to be fixed before we can go to beta"
versus "has to be fixed before release"?  I'm not opposed to fixing
the bugs, but it seems like every day that we postpone cutting a beta
is one more day until release, and so I think our immediate goal
should be to fix all of the things that need to be fixed before beta
can start.

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Tatsuhito Kasahara]

Simon Riggs wrote:
>> Does that answer your question ?
> 
> Not really. I want to understand the actual problem with
> idle-in-transaction so we can consider all ways to solve it, rather than
> just focus on one method.
"idle in transaction timeout" feature may be one of the ways.
But I have no specific idea about it now.

-- 
Tatsuhito Kasahara
kasahara.tatsuh...@oss.ntt.co.jp

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Simon Riggs]

On Fri, 2009-03-27 at 16:49 +0900, Tatsuhito Kasahara wrote:
> Simon Riggs wrote:
> >> Does that answer your question ?
> > 
> > Not really. I want to understand the actual problem with
> > idle-in-transaction so we can consider all ways to solve it, rather than
> > just focus on one method.
> "idle in transaction timeout" feature may be one of the ways.
> But I have no specific idea about it now.

Or take it further back still and think about why "idle in transaction"
occurs at all and fix *that*. Maybe not in Postgres at all, possibly in
the driver or even higher up client stack.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Should SET ROLE inherit config params?

[Simon Riggs]

On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote:

> I was just noticing that doing SET ROLE changes the current session's 
> priviledges, but not any runtime configuration parameters (like work_mem 
> or statement_timeout) associated with the new role.
> 
> This is as documented (although I want to add a line to SET ROLE docs) 
> but is it the behavior we want?  I for one would like SET ROLE to change 
> runtime configs.

Thinking some more about the requirements for this and various
objections.

I'm guessing that there's a small cluster of parameters you want to
alter using this. It seems easier to think about those parameters and to
look at ways of managing those. Perhaps what we need is not parameters
on roles, but a related concept: profiles. 

Profiles define the limits and priorities given to certain categories of
work. So one profile might be work_mem = 128M and constraint_exclusion =
on, others could differ. If we invent a new concept, we get to define
the semantics from scratch. Maybe RESET doesn't work with profiles,
maybe you can't change user parameters set by a profile, maybe they
allow you to define maximum values. Maybe. Maybe. Nice clear
distinction: roles manage privileges, profiles manage
resources/optimisation.

The main reason for abstraction is that we can avoid hardcoding resource
management data into applications, so that when we upgrade we don't need
to retune or re-arrange everything.

8.5 obviously. But if some time is given to a coherent design that
focuses on what we actually want rather than on a specific solution, we
may find there is a neat way to do this without breaking anything.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Guillaume Smet]

2009/3/27 Tatsuhito Kasahara :
> But if I can also check last query string, I guess which apllication
> do that and point out the problem point.

Oh, I just understand why you want this patch. I usually have one
database per server so I didn't see your point.

Considering this usage, +1 for me.

-- 
Guillaume

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Simon Riggs]

On Fri, 2009-03-27 at 15:44 +0900, Tatsuhito Kasahara wrote:
> Simon Riggs wrote:
> >> If we can also check previous query_string of idle-in-transaction,
> >> it is useful for analysis of long transaction problem.
> > 
> > I'm more interested in the problem itself. Why do you think there is a
> > problem and why does knowing this help you? I had similar problems
> > recently, so I'm interested in thoughts around this.
> In many case, applications don't know what queries they are doing.
> So, if a long transaction problem (unworking defragmnetation by HOT and
> VACUUM) occured, I don't understand which application is the cause with
> only " in transaction" message.
> 
> But if I can also check last query string, I guess which apllication
> do that and point out the problem point.
> # As I said, I think a long transaction is not a problem itself.
> 
> Does that answer your question ?

Not really. I want to understand the actual problem with
idle-in-transaction so we can consider all ways to solve it, rather than
just focus on one method.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] display previous query string of idle-in-transaction

[Tatsuhito Kasahara]

Simon Riggs wrote:
>> If we can also check previous query_string of idle-in-transaction,
>> it is useful for analysis of long transaction problem.
> 
> I'm more interested in the problem itself. Why do you think there is a
> problem and why does knowing this help you? I had similar problems
> recently, so I'm interested in thoughts around this.
In many case, applications don't know what queries they are doing.
So, if a long transaction problem (unworking defragmnetation by HOT and
VACUUM) occured, I don't understand which application is the cause with
only " in transaction" message.

But if I can also check last query string, I guess which apllication
do that and point out the problem point.
# As I said, I think a long transaction is not a problem itself.

Does that answer your question ?

Best regards,

-- 
Tatsuhito Kasahara
kasahara.tatsuh...@oss.ntt.co.jp

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers