Re: [HACKERS] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread Heikki Linnakangas

On 24/08/10 23:56, Andres Freund wrote:

I have to ask one question: On a short review of the discussion and
the patch I didn't find anything about the concurrency issues
involved (at least nodeModifyTable.c didnt show any).


The SQL spec doesn't require MERGE to be an atomic "upsert" operation.


Whats the plan to go forward at that subject? I think the patch needs
to lock tables exclusively (the pg level, not access exclusive) as
long as there is no additional handling...


Well, you can always do LOCK TABLE before calling MERGE if that's what 
you want, but I don't think doing that automatically would make people 
happy.


--
  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] security label support, part.2

2010-08-24 Thread KaiGai Kohei
 7. I think we need to write and include in the fine documentation some
 "big picture" documentation about enhanced security providers. Of
 course, we have to decide what we want to say. But the SECURITY LABEL
 documentation is just kind of hanging out there in space right now; it
 needs to connect to a broad introduction to the subject.

>>> OK, I'll try to describe with appropriate granularity.
>>> Do we need an independent section in addition to the introduction of
>>> SECURITY LABEL syntax?
>>
>> I think so. I suggest a new chapter called "Enhanced Security
>> Providers" just after "Database Roles and Privileges".
>>
> OK,
> 

Now I'm under describing the new chapter.
http://git.postgresql.org/gitweb?p=users/kaigai/sepgsql.git;a=blob;f=doc/src/sgml/esp.sgml;hb=devel/seclabel

However, I'm wondering whether the topic about security hooks and some
others are appropriate for the "III. Server Administration" part.

Perhaps, it is a good idea a new section at the last of "Database Roles
and Privileges" which introduce a fact that PostgreSQL allows plugins
to make access control decision, and a new chapter in the "VII. Internals"
part.

How about the idea?

Thanks,
-- 
KaiGai Kohei 

-- 
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] patch: Add JSON datatype to PostgreSQL (GSoC, WIP)

2010-08-24 Thread Itagaki Takahiro
Hi, I start to review JSON patch.

On Fri, Aug 13, 2010 at 7:33 PM, Joseph Adams
 wrote:
> Updated patch:  the JSON code has all been moved into core, so this
> patch is now for a built-in data type.

I think the patch can be split into two pieces:
  1. Basic I/O support for JSON type (in/out/validate)
  2. JSONPath support and functions for partial node management

It is better to submit only 1 at first. Of course we should consider
about JSONPath before deciding the internal representation of JSON,
but separated patches can be easily reviewed.

I have several questions about the spec and implementation.
Sorry if you have already discussed about some of them, but I cannot
understand why the current code is the best design from the patch...

* Should we accept a scalar value as a valid JSON?
According to RFC, the root element of JSON text must be an object
or array. But to_json() and from_json() accept scalar values.

* JSON to a scalar value by from_json()
How about to have json_to_array(JSON) instead of from_json()?
JSON value is always an array or object, it's nonsense to convert
it to a scalar value directly; to an array seems to match better.
In addition, an array can be indexed with GIN; index-able JSON
type is very attractive.

* struct JSON seems to be too complex for me.
Can we use List (pg_list.h) instead of linked-list? 'key' and 'key_length'
fields should be held in the parent's List. i.e, JSON_ARRAY has List of
JSON, and JSON_OBJECT has List of {string, JSON} pairs.

We could also discard 'parent' field. It might be needed by JSONPath,
but we can have parent information in variables on C-stack because we
search JSON trees from root to children, no?

I think we don't need 'orig' field because the original input text is
not so important in normal use cases. Instead, we could have formatter
function something like json_pretty(json) RETURNS text.

* On-disk format of JSON values
(There might be some discussions before... What is the conclusion?)
The current code stores the original input text, but we can use
some kinds of pre-parsed format to store JSON, like hstore.
It can be different from BSON.

* Completeness of JSONPath APIs
json_get() can be replaced with json_path(), no?
Also, we can replace existing nodes with json_set(), but we cannot
append new nodes. What do you think modification of JSON value?
If the design is too difficult, it'd be better only to have search
APIs at this moment. Modification APIs will be added in the future.

-- 
Itagaki Takahiro

-- 
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] git: uh-oh

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> 1. The new conversion seems to have stolen the apostrophe from "D'Arcy
> J.M. Cain ", rendering him "DArcy J.M. Cain
> ".

Yeah, I see that too.  It's probably bad input rather than the
converter's fault ;-)

> 2. Any non-ASCII characters in, for example, contributor's names show
> up differently in the two repos.  Generally, the original repo is OK
> and the new repo is garbled; although I found one very old example
> that went the other way.

What it looks like to me is that a Latin1->UTF8 conversion has been
applied to the log text.  Which might be a good idea if it all *was*
Latin1, but a fair-sized percentage isn't.  Applying this conversion to
UTF8 entries results in garbage, of course.  Even if this could be done
reliably, I think this counts as editorializing on the historical
record, and should be switched off if possible.

> There are also a number of commits that differ in order between the
> two repos, and an even larger number where commits are duplicated or
> merged in one repository relative to the other.

I suspect that this is an artifact of the converter trying to merge
nearby commits into one commit, which it more or less *has* to do for
sanity since CVS commits aren't atomic.  I don't have a problem with
the concept, but I notice cases where the converted commit has a
timestamp some minutes later than what the cvs2cl output claims.
I suspect this is what the converter was using as a cutoff time.
Would it be possible to make sure that the converted commit is always
timestamped with the latest individual file update timestamp from the
included CVS commits?

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] Performance Farm Release

2010-08-24 Thread Luxenberg, Scott I.
Hey all,

 

This is just my email to notify you all that the project I've been
working on with Stephen, the PostgreSQL Performance Farm, has been
released. As of now, it only supports 9.0, due to the use of workers.
More details can be found in the readme. The Git repository is located
here: http://github.com/slux/Postgre-Performance-Farm

 

Scott Luxenberg

703-610-1823 (W)

703-303-5189 (C)

scott.luxenb...@noblis.org

 



Re: [HACKERS] SQLSTATE of notice PGresult

2010-08-24 Thread Tom Lane
Euler Taveira de Oliveira  writes:
> What I'm thinking is something like

You didn't actually read what I said, did you?  That patch will have
precisely zero effect on the OP's example.

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] git: uh-oh

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> On Fri, Aug 20, 2010 at 1:56 PM, Max Bowsher  wrote:
>> My guess at this point is that there may be a (very old?) version of cvs
>> which, when adding a file to a branch, actually misrecorded the file as
>> having existed on the branch from the moment it was first added to trunk
>> - this would explain this anomaly.

> I think this is what is happening, except I'm unable to account for it
> by the age of the CVS version we're runnning.  The machine the CVS
> repo is running on is running 1.11.17-FreeBSD (client/server).

Um, how old do you think that is?  A look at the cvs sources says 2004...

It looks to me like the bogus commits for back-branch additions are
indeed part of our CVS history.  While perhaps it would be nice if the
git conversion cleaned them up, I'm not sure that we want to put off
doing the conversion for however long it might take to make that happen.

> The odder cases are the ones involving deletion.  There are a couple
> of branches/tags that, or so I'm guessing, are only present for a
> subset of the files in the repository: ecpg_big_bison, creation,
> Release-1-6-0, MANUAL_1_0, REL2_0B, and SUPPORT.  I'm wondering if we
> shouldn't just nuke those, or at least nuke them from the copy of the
> repository upon which we are running the conversion.

Yeah, I noticed some of those in my copy of the test repository too,
but I see a slightly different set:

  remotes/origin/REL2_0B
  remotes/origin/REL6_4
  remotes/origin/Release_1_0_3
  remotes/origin/WIN32_DEV
  remotes/origin/ecpg_big_bison

I doubt they're of any more than archaeological interest, but do we want
to be deleting history?  What seemed more likely to be artifacts were
these:

  remotes/origin/unlabeled-1.44.2
  remotes/origin/unlabeled-1.51.2
  remotes/origin/unlabeled-1.59.2
  remotes/origin/unlabeled-1.87.2
  remotes/origin/unlabeled-1.90.2

Any idea where those came from?

> This series of commits also seems pretty messed up:
> http://archives.postgresql.org/pgsql-committers/2007-04/msg00222.php
> http://archives.postgresql.org/pgsql-committers/2007-04/msg00223.php

You can find out about the reasons for that in this *other* discussion
of conversion to git:
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00670.php
particularly here:
http://archives.postgresql.org/pgsql-hackers/2007-04/msg00685.php

> ... pretty crazy.  I think we should try to do something to clean this up,
> perhaps by doctoring the file on the CVS side.

On the whole I feel that you're moving the goalposts.  AFAIR the agreed
criteria for an acceptable SCM conversion were that it reproduce the
historical states of our tree at least at all the release tags, and that
it provide a close approximation of the CVS commit logs.  I think that
manufactured commits that correspond to CVS's artifacts might be a bit
ugly, but trying to get rid of them sounds way too much like putting
lipstick on a pig.  And if it means removing real, if ugly, history,
I'm not sure I'm in favor of it 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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Tom Lane escreveu:
> The real issue
> here is that there are no SQLSTATEs assigned for any error/warning
> conditions generated internally in libpq.
> 
Did you mean successful conditions? Only warning/error conditions produce a
SQLSTATE.

> As far as this particular example goes, I think it's highly debatable
> whether "out of range parameter number" should be only a NOTICE, and
> almost certainly wrong to say that it ought to be associated with an
> 0 SQLSTATE.  But figuring out what it ought to be is part of the
> dogwork that nobody's done yet.
> 
It should match the actual PostgreSQL behavior. There are two classes (01xxx
and 02xxx) for warnings.

What I'm thinking is something like

*** src/interfaces/libpq/fe-protocol3.c 28 Apr 2010 13:46:23 -  1.43
--- src/interfaces/libpq/fe-protocol3.c 21 Aug 2010 02:41:01 -
***
*** 206,211 
--- 206,219 
if (!conn->result)
return;
}
+   /*
+* If the command was successful completed, set the
+* appropriate SQLSTATE. Pre-9.1 don't set it.
+* ERRCODE_SUCCESSFUL_COMPLETION code (aka 0) is
+* hardcoded here because we avoid including elog routines
+* here.
+*/
+   pqSaveMessageField(conn->result, PG_DIAG_SQLSTATE, "0");
strncpy(conn->result->cmdStatus, conn->workBuffer.data,
CMDSTATUS_LEN);
conn->asyncStatus = PGASYNC_READY;


(I only patch the 'Command Complete' message here but it is necessary to patch
other success messages too.)


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Robert Haas escreveu:
> It appears to me that it already is documented.  The very first
> sentence of the documentation reads:
> 
> Returns an individual field of an error report.
> 
> And a few sentences later it says:
> 
> NULL is returned if the PGresult is not an error or warning result
> 
I'm referring to [1].

> I suppose we could change the function to return 0 always when the
> operation is not an error or warning report, rather than NULL, but
> certainly we wouldn't want to include those bytes in *every* success
> message, so they'd have to be something that the libpq inferred.  And
> I'm not clear why that behavior would be any more useful than what we
> have now; indeed, it seems like it would needlessly break backward
> compatibility.  If you're arguing that this behavior is required by
> the spec, let's have a cite.  I find it a bit surprising that the spec
> would cover the behavior of individual libpq functions in this level
> of detail.
> 
It seems we can't infer the success message from libpq; it is necessary to
build the sql state message field. As I said both behaviors have the same goal
(in this case, NULL means success, i.e. sqlstate is not assigned) but it
doesn't match the spec.


[1] http://www.postgresql.org/docs/9.0/static/errcodes-appendix.html


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] SQLSTATE of notice PGresult

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> I suppose we could change the function to return 0 always when the
> operation is not an error or warning report, rather than NULL, but
> certainly we wouldn't want to include those bytes in *every* success
> message, so they'd have to be something that the libpq inferred.  And
> I'm not clear why that behavior would be any more useful than what we
> have now; indeed, it seems like it would needlessly break backward
> compatibility.

Um.  You're missing the point here.  This isn't a message from the
backend, it's a complaint generated internally by libpq.  The real issue
here is that there are no SQLSTATEs assigned for any error/warning
conditions generated internally in libpq.  Fixing this is just a Small
Matter Of Programming, but no one's yet taken an interest in doing it.
Seeing that that's been a TODO item since 7.4, I wouldn't advise holding
your breath.

As far as this particular example goes, I think it's highly debatable
whether "out of range parameter number" should be only a NOTICE, and
almost certainly wrong to say that it ought to be associated with an
0 SQLSTATE.  But figuring out what it ought to be is part of the
dogwork that nobody's done yet.

> If you're arguing that this behavior is required by
> the spec, let's have a cite.  I find it a bit surprising that the spec
> would cover the behavior of individual libpq functions in this level
> of detail.

I believe the text about "always present" is cribbed from our FE/BE
protocol specification.  It is true (or at least should be true) for
error and notice messages sent from the backend.

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] SQLSTATE of notice PGresult

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 9:44 PM, Euler Taveira de Oliveira
 wrote:
> Robert Haas escreveu:
>> On Fri, Aug 20, 2010 at 11:05 AM, Euler Taveira de Oliveira
>>  wrote:
>>> Dmitriy Igrishin escreveu:
   /* NOT presents - NULL. Why not "0" ? */
   const char* sqlstate = Pg::PQresultErrorField(res, PG_DIAG_SQLSTATE);

>>> That's because the protocol doesn't set error field when the command
>>> succeeded. IMHO it's an oversight (the documentation is correct but the code
>>> is not) and should be correct because the spec enforces it.
>>
>> Seems like a waste of bytes.
>>
> Ugh? It is a matter of correctness. I'm not arguing in favor of it but if we
> don't implement it, it is better document it.



It appears to me that it already is documented.  The very first
sentence of the documentation reads:

Returns an individual field of an error report.

And a few sentences later it says:

NULL is returned if the PGresult is not an error or warning result

> I don't actually rely on sql
> state to check errors but can have applications out there that expect the spec
> behavior but we don't provide it and, also fail to document it. Talking about
> the patch, it is just pqSaveMessageField() calls in *Complete messages. I can
> provide a patch for it.

I suppose we could change the function to return 0 always when the
operation is not an error or warning report, rather than NULL, but
certainly we wouldn't want to include those bytes in *every* success
message, so they'd have to be something that the libpq inferred.  And
I'm not clear why that behavior would be any more useful than what we
have now; indeed, it seems like it would needlessly break backward
compatibility.  If you're arguing that this behavior is required by
the spec, let's have a cite.  I find it a bit surprising that the spec
would cover the behavior of individual libpq functions in this level
of detail.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] SQLSTATE of notice PGresult

2010-08-24 Thread Euler Taveira de Oliveira
Robert Haas escreveu:
> On Fri, Aug 20, 2010 at 11:05 AM, Euler Taveira de Oliveira
>  wrote:
>> Dmitriy Igrishin escreveu:
>>>   /* NOT presents - NULL. Why not "0" ? */
>>>   const char* sqlstate = Pg::PQresultErrorField(res, PG_DIAG_SQLSTATE);
>>>
>> That's because the protocol doesn't set error field when the command
>> succeeded. IMHO it's an oversight (the documentation is correct but the code
>> is not) and should be correct because the spec enforces it.
> 
> Seems like a waste of bytes.
> 
Ugh? It is a matter of correctness. I'm not arguing in favor of it but if we
don't implement it, it is better document it. I don't actually rely on sql
state to check errors but can have applications out there that expect the spec
behavior but we don't provide it and, also fail to document it. Talking about
the patch, it is just pqSaveMessageField() calls in *Complete messages. I can
provide a patch for it.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.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] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread David Fetter
On Wed, Aug 25, 2010 at 08:11:18AM +0800, Boxuan Zhai wrote:
> On Wed, Aug 25, 2010 at 4:56 AM, Andres Freund  wrote:
> 
> > On Tue, Aug 24, 2010 at 11:02:41PM +0300, Heikki Linnakangas wrote:
> > > On 24/08/10 16:35, Boxuan Zhai wrote:
> > > >Hi,
> > > >
> > > >I finished the MERGE on inheritance tables. Now comes the
> > > >merge_v201
> > >
> > > Oh, great! That means that all the known issues are fixed now,
> > > and all that's left is fixing any issues raised in review.
> > >
> > > I've added this to the September commitfest, but I hope I'll
> > > find some time to look at this before that. I welcome anyone
> > > else to review this too!
> > I have to ask one question: On a short review of the discussion
> > and the patch I didn't find anything about the concurrency issues
> > involved (at least nodeModifyTable.c didnt show any).  Whats the
> > plan to go forward at that subject? I think the patch needs to
> > lock tables exclusively (the pg level, not access exclusive) as
> > long as there is no additional handling...
> >
> > Thanks for the work Boxuan!
> 
> The concurrency issues are not involved. I don't know much about
> this part.  I think we need more discussion on it.

I seem to recall Simon had volunteered some of 2ndQuadrant's time on
this. :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread David Fetter
On Tue, Aug 24, 2010 at 08:17:15PM -0400, Robert Haas wrote:
> On Tue, Aug 24, 2010 at 5:11 PM, Tom Lane  wrote:
> > Robert Haas  writes:
> >> There is some amount of user code (I'm not sure preceisely how
> >> much) that runs after shared memory is mapped and before the
> >> deadman switch is engaged.
> >
> > Er ... what would you define as "user code"?
> 
> Our code, as opposed to the failure-inducing boatload of crap
> injected by the operating system.

Don't hold back.  Tell us how you *really* feel ;)

Cheers,
David (who thinks Robert's view of that platform may be a good deal
too sunny)
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] security hook on authorization

2010-08-24 Thread KaiGai Kohei
I tried to revise the patch. It allows plugins to get control next to
client authentication, but before returning the status to users.

This change enables plugins which should be invoked on authentication
failed to utilize this hook, not only assignment of session security
label.
At the same time, it disables to hook on SET SESSION AUTHORIZATION.
But it is a bit unclear whether we should hook here, or not.

(2010/08/24 14:19), KaiGai Kohei wrote:
> (2010/08/22 21:38), KaiGai Kohei wrote:
>> (2010/08/22 0:20), Robert Haas wrote:
>>> On Aug 20, 2010, at 8:27 PM, KaiGai Kohei  wrote:
 (2010/08/20 23:34), Robert Haas wrote:
> 2010/8/19 KaiGai Kohei:
> I think our standard criteria for the inclusion of hooks is that you
> must demonstrate that the hook can be used to do something interesting
> that couldn't be done without the hook. So far I'm unconvinced.
>
 We cannot handle an error of labeled networking (getpeercon(3)),
 if we don't have any hook during client authorization stage.

 If and when a connection came from a host but we don't accept the
 delivered security label, or labeled networking is misconfigured,
 getpeercon(3) returns NULL. In this case, server cannot identify
 what label should be applied on the client, then, we should
 disconnect this connection due to the error on database login,
 not any access control decision.

 In similar case, psm_selinux.so disconnect the connection when
 it cannot identify what security label shall be assigned on the
 session, due to some reasons such as misconfigurations.

 Without any hooks at authorization stage (but it might be different
 place from this patch, of course), we need to delay the error
 handling by the time when SE-PostgreSQL module is invoked at first.
 But it is already connection established and user sends a query.
 It seems to me quite strange behavior.
>>>
>>> You mentioned that before. I'm not totally sure I buy it, and I think
>>> there are other applications that might benefit from a hook in this area.
>>> We need to think about trying to do this in a way that is as general as
>>> possible. So I'd like to see some analysis of other possible
>>> applications.
>>>
>> Yes, I also think this kind of authorization hook should benefit other
>> applications, not only label based mac features.
>>
>> For example, something like 'last' command in operations system which
>> records username and login-time. Stephen mentioned pam_tally that locks
>> down certain accounts who failed authentication too much.
>> Perhaps, PAM modules in operating system give us some hints about other
>> possible applications.
>>
> 
> I've checked some documentation files of pam modules in operating system
> to think about other possible applications.
> 
> * pam_env.so
> It allows to set/unset environment variables, perhaps, per users.
> In PG, we may be able to assume a module which set/unset guc variables
> depending on authenticated user?
> 
> * pam_faildelay.so
> It enables to delay to disconnect when authentication was failed.
> It prevents brute-force attack on passwords.
> 
> * pam_lastlog.so
> It enables to display a line of information about the last login of
> the user. In addition, the module maintains the /var/log/lastlog file.
> 
> * pam_selinux.so
> It sets up the default security context for the next execed shell.
> It is equivalent to set up a set of privileges of the authenticated
> user.
> 
> * pam_tally.so
> It maintains a count of attempted accesses, can reset count on success,
> can deny access if too many attempts fail.
> 
> 
> If and when we try to provide something similar features of them,
> the pam_env.so, pam_lastlog.so and pam_selinux.so need to be called
> on the code path of authentication succeeded only.
> But the pam_faildelay.so needs to be called on authentication failed
> path, and the pam_tally.so needs to be called on both paths because
> it maintain a count of authentication failed and locks down certain
> user accounts which failed too many.
> 
> In the current patch, I put the authorization hook on SetSessionUserId()
> but it is only called when authentication succeeded path.
> 
> Here is only one place where we can put the authorization hook where
> is called on both of authentication succeeded and failed.
> 
> The ClientAuthentication() has a big switch statement which branches
> to each authentication methods, then status will be updated to
> STATUS_OK or others.
> How about the security hook just after the big switch statement but
> before sending a response to the client, as follows?
> 
>void
>ClientAuthentication(Port *port)
>{
>int status = STATUS_ERROR;
>  :
>/*
> * Now proceed to do the actual authentication check
> */
>switch (port->hba->auth_method)
>{
>case uaReject:
>  :
>}
> 
> + if (ClientAuthe

Re: [HACKERS] SQLSTATE of notice PGresult

2010-08-24 Thread Robert Haas
On Fri, Aug 20, 2010 at 11:05 AM, Euler Taveira de Oliveira
 wrote:
> Dmitriy Igrishin escreveu:
>>   /* NOT presents - NULL. Why not "0" ? */
>>   const char* sqlstate = Pg::PQresultErrorField(res, PG_DIAG_SQLSTATE);
>>
> That's because the protocol doesn't set error field when the command
> succeeded. IMHO it's an oversight (the documentation is correct but the code
> is not) and should be correct because the spec enforces it.

Seems like a waste of bytes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 4:56 PM, Andres Freund  wrote:
> Whats the plan to go forward at that subject? I think the patch needs
> to lock tables exclusively (the pg level, not access exclusive) as
> long as there is no additional handling...

That sounds like it might cause more problems than it solves.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 5:11 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> There is some amount of user code (I'm not sure preceisely how much)
>> that runs after shared memory is mapped and before the deadman switch
>> is engaged.
>
> Er ... what would you define as "user code"?

Our code, as opposed to the failure-inducing boatload of crap injected
by the operating system.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] git: uh-oh

2010-08-24 Thread Robert Haas
On Fri, Aug 20, 2010 at 1:56 PM, Max Bowsher  wrote:
> My guess at this point is that there may be a (very old?) version of cvs
> which, when adding a file to a branch, actually misrecorded the file as
> having existed on the branch from the moment it was first added to trunk
> - this would explain this anomaly.

I think this is what is happening, except I'm unable to account for it
by the age of the CVS version we're runnning.  The machine the CVS
repo is running on is running 1.11.17-FreeBSD (client/server).  I
don't know how long it's been that way, but there are examples of this
in the relatively recent past - like July 2nd of this year.  I am 100%
positive that what I did was 'cvs add' one new file, 'cvs delete' one
old file, modify a few other things, and commit the whole deal.  But
in the git conversion there are two commits, one of which adds a copy
of the file as it exists in HEAD and the other of which contains the
balance of the changes.  Every recent manufactured commit is of this
same form: it immediately precedes the commit of which (in my view) it
should be considered a part.

Looking back a bit further in history, there is some stranger stuff.

commit ec0274633871c43da670fa90d0ac4fd7090639f2
Author: PostgreSQL Daemon 
Date:   Mon Jun 6 16:30:43 2005 +

This commit was manufactured by cvs2svn to create branch 'REL8_0_STABLE'.

Cherrypick from master 2005-06-06 16:30:42 UTC Bruce Momjian 
Date:   Tue Oct 4 14:17:44 2005 +

Add FAQ_hungarian.html to 8.0.X branch.

What really happened is:

http://archives.postgresql.org/pgsql-committers/2005-10/msg00044.php

So that's pretty much the same thing, except the time lag between the
two commits that should be married is much larger.

The odder cases are the ones involving deletion.  There are a couple
of branches/tags that, or so I'm guessing, are only present for a
subset of the files in the repository: ecpg_big_bison, creation,
Release-1-6-0, MANUAL_1_0, REL2_0B, and SUPPORT.  I'm wondering if we
shouldn't just nuke those, or at least nuke them from the copy of the
repository upon which we are running the conversion.

This series of commits also seems pretty messed up:

http://archives.postgresql.org/pgsql-committers/2007-04/msg00222.php
http://archives.postgresql.org/pgsql-committers/2007-04/msg00223.php

The commit messages make it clear that CVS did something funky,
although it's not exactly clear retrospectively what it was.  At any
rate, it's evidently still not right, because in the converted
repository we get a whole slough of commits like this:

commit c50da22b6050e0bdd5e2ef97541d91aa1d2e63fb
Author: PostgreSQL Daemon 
Date:   Sat Dec 2 08:36:42 2006 +

This commit was manufactured by cvs2svn to create branch 'REL8_2_STABLE'.

Sprout from master 2006-12-02 08:36:41 UTC PostgreSQL Daemon http://www.enterprisedb.com
The Enterprise Postgres Company

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


[HACKERS] No documentation for filtering dictionary feature?

2010-08-24 Thread Tom Lane
There's an entry in the 9.0 release notes saying that we've got
filtering dictionaries now.  Cool, but I don't see any documentation
of the feature in textsearch.sgml.  Shouldn't there be some?

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] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread Boxuan Zhai
On Wed, Aug 25, 2010 at 4:56 AM, Andres Freund  wrote:

> On Tue, Aug 24, 2010 at 11:02:41PM +0300, Heikki Linnakangas wrote:
> > On 24/08/10 16:35, Boxuan Zhai wrote:
> > >Hi,
> > >
> > >I finished the MERGE on inheritance tables. Now comes the merge_v201
> >
> > Oh, great! That means that all the known issues are fixed now, and
> > all that's left is fixing any issues raised in review.
> >
> > I've added this to the September commitfest, but I hope I'll find
> > some time to look at this before that. I welcome anyone else to
> > review this too!
> I have to ask one question: On a short review of the discussion and
> the patch I didn't find anything about the concurrency issues
> involved (at least nodeModifyTable.c didnt show any).
> Whats the plan to go forward at that subject? I think the patch needs
> to lock tables exclusively (the pg level, not access exclusive) as
> long as there is no additional handling...
>
> Thanks for the work Boxuan!
>
>

The concurrency issues are not involved. I don't know much about this part.
I think we need more discussion on it.



> Andres
>
> PS: The patch reintroduces some whitespace damage...
>


Re: [HACKERS] HS/SR on AIX

2010-08-24 Thread Tom Lane
Steve Singer  writes:
> I think I've been able to reproduce the issue floating around with 
> streaming replication on AIX.

Excellent, because we weren't getting much from the original reporter.

> This worked fine with beta2 but now seems to be an issue on beta4.

> If I do
> export LIBPATH=/opt/dbs/pgsql9-beta2/lib/
> before starting the standby postmaster then it seems to work.

Fascinating.  That seems to prove that it's an rpath problem.  My
first guess is that the relevant change between beta2 and beta4 is
my LDFLAGS changes.  See
http://archives.postgresql.org/pgsql-committers/2010-07/msg00060.php
and following commits.

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] why restrict role "public" but not "Public"?

2010-08-24 Thread Tom Lane
Alvaro Herrera  writes:
> The reason I'm asking is that I'm trying to allow "public" to be passed
> to has_table_privileges(), and have it check for the PUBLIC pseudo-role.
> Originally I had coded it using pg_strcasecmp() on the grounds that any
> case should refer to this.

That would be incorrect, IMO.  Ordinary role names passed to that
function would certainly not be treated case-insensitively, so this
one should not be either.

> It seems a complicated rule to document.  Seems better to just disallow
> creating a role "public" regardless of case.

Perhaps, but the above is not a good argument for changing it.

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] why restrict role "public" but not "Public"?

2010-08-24 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar ago 24 19:04:14 -0400 2010:
> Alvaro Herrera  writes:
> > I just noticed that we restrict creation of a role named "public", but
> > this is case-sensitive -- i.e. we don't restrict roles named PUBLIC,
> > etc.
> 
> > Is this intended?
> 
> Yes.  If you had a role named that, you might think that
> GRANT whatever TO PUBLIC
> should refer to that role.

However
GRANT whatever TO "Public"
is allowed?  Seems inconsistent.  My point is that we allow others, not
that we disallow that one.

The reason I'm asking is that I'm trying to allow "public" to be passed
to has_table_privileges(), and have it check for the PUBLIC pseudo-role.
Originally I had coded it using pg_strcasecmp() on the grounds that any
case should refer to this.  However, if "Public" and other combinations
are allowed, only lowercase "public" would work as input for that
function; using any uppercase letter would mean that it'd refer to a
role named like that.

It seems a complicated rule to document.  Seems better to just disallow
creating a role "public" regardless of case.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 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] why restrict role "public" but not "Public"?

2010-08-24 Thread Tom Lane
Alvaro Herrera  writes:
> I just noticed that we restrict creation of a role named "public", but
> this is case-sensitive -- i.e. we don't restrict roles named PUBLIC,
> etc.

> Is this intended?

Yes.  If you had a role named that, you might think that
GRANT whatever TO PUBLIC
should refer to that role.

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] why restrict role "public" but not "Public"?

2010-08-24 Thread Alvaro Herrera
Hi,

I just noticed that we restrict creation of a role named "public", but
this is case-sensitive -- i.e. we don't restrict roles named PUBLIC,
etc.

Is this intended?

-- 
Álvaro Herrera 

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


[HACKERS] HS/SR on AIX

2010-08-24 Thread Steve Singer
I think I've been able to reproduce the issue floating around with 
streaming replication on AIX.



LOG:  could not bind IPv6 socket: The socket name is already in use.
HINT:  Is another postmaster already running on port 5433? If not, wait 
a few seconds and retry.

LOG:  database system was shut down in recovery at 2010-08-24 21:08:37 UTC
LOG:  entering standby mode
cp: cannot stat `/opt/rg/data_tb3/steve/wals/00010001': 
A file or directory in the path name does not exist.

LOG:  redo starts at 0/120
LOG:  record with zero length at 0/1012280
cp: cannot stat `/opt/rg/data_tb3/steve/wals/00010001': 
A file or directory in the path name does not exist.
FATAL:  could not load library 
"/opt/dbs/pgsql9-beta2/lib/libpqwalreceiver.so": 0509-022 Cannot 
load module /opt/dbs/pgsql9-beta2/lib/libpqwalreceiver.so.
0509-150   Dependent module libpq.a(libpq.so.5) could 
not be loaded.

0509-022 Cannot load module libpq.a(libpq.so.5).
0509-026 System error: A file or directory in the path 
name does not exist.
0509-022 Cannot load module 
/opt/dbs/pgsql9-beta2/lib/libpqwalreceiver.so.
0509-150   Dependent module 
/opt/dbs/pgsql9-beta2/lib/libpqwalreceiver.so could not be loaded.



This worked fine with beta2 but now seems to be an issue on beta4.

If I do
export LIBPATH=/opt/dbs/pgsql9-beta2/lib/
before starting the standby postmaster then it seems to work.

I haven't yet tried running truss to try to look at where it is looking 
for libpq from.  liblibpqwalreceiver is being linked with this


gcc -maix64 -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing 
-fwrapv -g  -o libpqwalreceiver.so liblibpqwalreceiver.a 
-Wl,-bE:liblibpqwalreceiver.exp -L../../../../src/port 
-Wl,-bmaxdata:0x8000,-bbigtoc  -L/opt/freeware/lib 
-Wl,-blibpath:/opt/dbs/pgsql9-beta2/lib:/opt/freeware/lib:/usr/lib:/lib 
 -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE -L../../../../src/interfaces/libpq 
-lpq -Wl,-bI:../../../../src/backend/postgres.imp



I'll try to look into this a bit more tomorrow or thursday.


--
Steve Singer
Afilias Canada
Data Services Developer
416-673-1142

--
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] git: uh-oh

2010-08-24 Thread Robert Haas
On Mon, Aug 23, 2010 at 4:50 AM, Magnus Hagander  wrote:
> If we need to do it at all. Tom's latest lookover indicates that he
> thinks it may be good the way it is, and we need some more detailed
> checks. I know Robert has said he wants to dedicate some time to doing
> such checks this week, and I'll see if I can find some time for that
> as well. If anybody else would like to help us dig through mainly the
> backbranches - with focus on branchpoints and taggings - to look for
> any kind of "weird stuff" (meaning anything that's not a straight
> commit), then please do so and let us know your results!

So far I've found a couple of minor issues by comparing 'git log
master' on the current, incremental conversion with the
git-migration-test repo (incidentally, what happened to discipline in
naming these repos?).

1. The new conversion seems to have stolen the apostrophe from "D'Arcy
J.M. Cain ", rendering him "DArcy J.M. Cain
".

2. Any non-ASCII characters in, for example, contributor's names show
up differently in the two repos.  Generally, the original repo is OK
and the new repo is garbled; although I found one very old example
that went the other way.

There are also a number of commits that differ in order between the
two repos, and an even larger number where commits are duplicated or
merged in one repository relative to the other.  So far, all the
examples I've checked have appeared to be saner in the new repository
than in the old one, but I have not done a full audit.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> There is some amount of user code (I'm not sure preceisely how much)
> that runs after shared memory is mapped and before the deadman switch
> is engaged.

Er ... what would you define as "user code"?

The deadman switch is engaged at the point where we create a PGPROC.
Before that, it's entirely impossible to take either LWLocks or
heavyweight locks, which means that practically any access to shared
memory would be illegal anyway.  If there's anything very interesting
going on in that stretch, I'd be surprised.

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] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread Andres Freund
On Tue, Aug 24, 2010 at 11:02:41PM +0300, Heikki Linnakangas wrote:
> On 24/08/10 16:35, Boxuan Zhai wrote:
> >Hi,
> >
> >I finished the MERGE on inheritance tables. Now comes the merge_v201
>
> Oh, great! That means that all the known issues are fixed now, and
> all that's left is fixing any issues raised in review.
>
> I've added this to the September commitfest, but I hope I'll find
> some time to look at this before that. I welcome anyone else to
> review this too!
I have to ask one question: On a short review of the discussion and
the patch I didn't find anything about the concurrency issues
involved (at least nodeModifyTable.c didnt show any).
Whats the plan to go forward at that subject? I think the patch needs
to lock tables exclusively (the pg level, not access exclusive) as
long as there is no additional handling...

Thanks for the work Boxuan!

Andres

PS: The patch reintroduces some whitespace damage...

-- 
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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 4:44 PM, Josh Berkus  wrote:
> On 8/24/10 12:42 PM, Tom Lane wrote:
>> Marko Tiikkaja  writes:
>>> Is this method not reliable then?  Can something go wrong even if the
>>> user does exactly what the documentation says?
>>
>> It is not.  This whole discussion started from somebody reporting that
>> it didn't work.
>
> Again, given that this is a method which is (a) fairly minority-need,
> and (b) not at all tested in the field, I do not think it belongs in the
> main docs.  Let's put it on the wiki and blog about it, and AFTER we've
> collected bug reports and have something relatively bulletproof for 9.1,
> THEN we put it in the main docs.
>
> We really shouldn't have anything in the main docs related to backup
> which isn't 100% tested.

Yeah.  This whole procedure sounds pretty flakey to me.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Joshua D. Drake
On Tue, 2010-08-24 at 13:44 -0700, Josh Berkus wrote:
> On 8/24/10 12:42 PM, Tom Lane wrote:
> > Marko Tiikkaja  writes:
> >> Is this method not reliable then?  Can something go wrong even if the 
> >> user does exactly what the documentation says?
> > 
> > It is not.  This whole discussion started from somebody reporting that
> > it didn't work.
> 
> Again, given that this is a method which is (a) fairly minority-need,
> and (b) not at all tested in the field, I do not think it belongs in the
> main docs.  Let's put it on the wiki and blog about it, and AFTER we've
> collected bug reports and have something relatively bulletproof for 9.1,
> THEN we put it in the main docs.
> 
> We really shouldn't have anything in the main docs related to backup
> which isn't 100% tested.

+1

JD

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 9:58 AM, Tom Lane  wrote:
> Bruce Momjian  writes:
>> Robert Haas wrote:
>>> Yeah, that seems very plausible, although exactly how to verify I don't 
>>> know.
>
>> And here is confirmation from the Microsoft web site:
>
>>       In some instances, calling GetExitCode() against the failed process
>>       indicates the following exit code:
>>       128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for.
>
> Given the existence of the deadman switch mechanism (which I hadn't
> remembered when this thread started), I'm coming around to the idea that
> we could just treat exit(128) as nonfatal on Windows.  If for some
> reason the child hadn't died instantly at startup, the deadman switch
> would distinguish that from the case described here.

So the options are:

(1) If running on Windows and the exit code is 128 and the deadman
switch is not engaged, don't crash-and-restart.
(2) If running on Windows, create a mutex in the parent process and
take it in the child; if the mutex has not been taken, don't
crash-and-restart.

There is some amount of user code (I'm not sure preceisely how much)
that runs after shared memory is mapped and before the deadman switch
is engaged.  If we go with option #1, it would probably behoove us to
try to minimize the amount of such code (at least in HEAD).  There is
probably not a great deal of danger that we could manage to scribble
on shared memory and then exit normally (rather than via signal),
never mind the need to exit with exactly 128.  But "not a great deal"
is not the same as "none".  If we go with option #2, the principal
danger seems to be that the code Magnus wrote will turn out to be less
robust than we might hope; for example, it might not work on all
versions of Windows, or be prone to some other installation-dependent
mischief.

Another question is how far either of these fixes could be
back-patched.  I believe the dead-man switch only exists as far back
as 8.4, but the original commit message mentioned the possibility of
eventually back-patching it further:

Although this problem is of long standing, the lack of field complaints
seems to mean it's not critical enough to risk back-patching; at least
not till we get some more testing of this mechanism.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Josh Berkus
On 8/24/10 12:42 PM, Tom Lane wrote:
> Marko Tiikkaja  writes:
>> Is this method not reliable then?  Can something go wrong even if the 
>> user does exactly what the documentation says?
> 
> It is not.  This whole discussion started from somebody reporting that
> it didn't work.

Again, given that this is a method which is (a) fairly minority-need,
and (b) not at all tested in the field, I do not think it belongs in the
main docs.  Let's put it on the wiki and blog about it, and AFTER we've
collected bug reports and have something relatively bulletproof for 9.1,
THEN we put it in the main docs.

We really shouldn't have anything in the main docs related to backup
which isn't 100% tested.


-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] gSoC add MERGE command new patch -- merge_v104

2010-08-24 Thread Heikki Linnakangas

On 24/08/10 16:35, Boxuan Zhai wrote:

Hi,

I finished the MERGE on inheritance tables. Now comes the merge_v201


Oh, great! That means that all the known issues are fixed now, and all 
that's left is fixing any issues raised in review.


I've added this to the September commitfest, but I hope I'll find some 
time to look at this before that. I welcome anyone else to review this too!


--
  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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Tom Lane
Marko Tiikkaja  writes:
> Is this method not reliable then?  Can something go wrong even if the 
> user does exactly what the documentation says?

It is not.  This whole discussion started from somebody reporting that
it didn't work.

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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Magnus Hagander
On Tue, Aug 24, 2010 at 21:39, Robert Haas  wrote:
> On Tue, Aug 24, 2010 at 3:10 PM, Magnus Hagander  wrote:
>> On Tue, Aug 24, 2010 at 15:58, Tom Lane  wrote:
>>> Bruce Momjian  writes:
 Robert Haas wrote:
> Yeah, that seems very plausible, although exactly how to verify I don't 
> know.
>>>
 And here is confirmation from the Microsoft web site:
>>>
       In some instances, calling GetExitCode() against the failed process
       indicates the following exit code:
       128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait 
 for.
>>>
>>> Given the existence of the deadman switch mechanism (which I hadn't
>>> remembered when this thread started), I'm coming around to the idea that
>>> we could just treat exit(128) as nonfatal on Windows.  If for some
>>> reason the child hadn't died instantly at startup, the deadman switch
>>> would distinguish that from the case described here.
>>
>> Just because I had written it before you posted that, here's how the
>> win32-specific-set-a-flag-when-we're-in-control thing would look. But
>> if we're convinced that just ignoring error 128 is safe, then that's
>> obviously a simpler patch..
>
> So, if we do this, what will happen to the client connection that was
> due to be handled by the backend being spawned?  Is this going to lead
> to extra fds accumulating or any such thing?

I don't see why. The process goes away, and with it goes all the
handles. And the postmaster still closes all sockets and handles the
same way it did before.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 3:10 PM, Magnus Hagander  wrote:
> On Tue, Aug 24, 2010 at 15:58, Tom Lane  wrote:
>> Bruce Momjian  writes:
>>> Robert Haas wrote:
 Yeah, that seems very plausible, although exactly how to verify I don't 
 know.
>>
>>> And here is confirmation from the Microsoft web site:
>>
>>>       In some instances, calling GetExitCode() against the failed process
>>>       indicates the following exit code:
>>>       128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait 
>>> for.
>>
>> Given the existence of the deadman switch mechanism (which I hadn't
>> remembered when this thread started), I'm coming around to the idea that
>> we could just treat exit(128) as nonfatal on Windows.  If for some
>> reason the child hadn't died instantly at startup, the deadman switch
>> would distinguish that from the case described here.
>
> Just because I had written it before you posted that, here's how the
> win32-specific-set-a-flag-when-we're-in-control thing would look. But
> if we're convinced that just ignoring error 128 is safe, then that's
> obviously a simpler patch..

So, if we do this, what will happen to the client connection that was
due to be handled by the backend being spawned?  Is this going to lead
to extra fds accumulating or any such thing?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Marko Tiikkaja

On 2010-08-24 9:04 PM +0300, Josh Berkus wrote:

... and my viewpoint is that the procedure described should be *cut*
from the official docs and put on the Wiki, with warnings.  It's simply
far too hackish and dependant on DBA understanding of PostgreSQL
internals to belong in the main docs.

In 9.1 we'll probably have some machinery to make taking snapshots of
the standby more reliable (like running pg_start_backup on the master),
and then that procedure will belong in the main docs.


Is this method not reliable then?  Can something go wrong even if the 
user does exactly what the documentation says?



Regards,
Marko Tiikkaja

--
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Magnus Hagander
On Tue, Aug 24, 2010 at 21:14, Bruce Momjian  wrote:
> Magnus Hagander wrote:
>> On Tue, Aug 24, 2010 at 15:58, Tom Lane  wrote:
>> > Bruce Momjian  writes:
>> >> Robert Haas wrote:
>> >>> Yeah, that seems very plausible, although exactly how to verify I don't 
>> >>> know.
>> >
>> >> And here is confirmation from the Microsoft web site:
>> >
>> >> ? ? ? In some instances, calling GetExitCode() against the failed process
>> >> ? ? ? indicates the following exit code:
>> >> ? ? ? 128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait 
>> >> for.
>> >
>> > Given the existence of the deadman switch mechanism (which I hadn't
>> > remembered when this thread started), I'm coming around to the idea that
>> > we could just treat exit(128) as nonfatal on Windows. ?If for some
>> > reason the child hadn't died instantly at startup, the deadman switch
>> > would distinguish that from the case described here.
>>
>> Just because I had written it before you posted that, here's how the
>> win32-specific-set-a-flag-when-we're-in-control thing would look. But
>> if we're convinced that just ignoring error 128 is safe, then that's
>> obviously a simpler patch..
>
> Can we please link to one of those URLs I mentioned so we have
> definitive information on what is happening?  I think the Microsoft URL is
> best:
>
>        http://support.microsoft.com/kb/156484

That URL is specifically labeled to only be valid for NT4.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Bruce Momjian
Magnus Hagander wrote:
> On Tue, Aug 24, 2010 at 15:58, Tom Lane  wrote:
> > Bruce Momjian  writes:
> >> Robert Haas wrote:
> >>> Yeah, that seems very plausible, although exactly how to verify I don't 
> >>> know.
> >
> >> And here is confirmation from the Microsoft web site:
> >
> >> ? ? ? In some instances, calling GetExitCode() against the failed process
> >> ? ? ? indicates the following exit code:
> >> ? ? ? 128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait 
> >> for.
> >
> > Given the existence of the deadman switch mechanism (which I hadn't
> > remembered when this thread started), I'm coming around to the idea that
> > we could just treat exit(128) as nonfatal on Windows. ?If for some
> > reason the child hadn't died instantly at startup, the deadman switch
> > would distinguish that from the case described here.
> 
> Just because I had written it before you posted that, here's how the
> win32-specific-set-a-flag-when-we're-in-control thing would look. But
> if we're convinced that just ignoring error 128 is safe, then that's
> obviously a simpler patch..

Can we please link to one of those URLs I mentioned so we have
definitive information on what is happening?  I think the Microsoft URL is
best:

http://support.microsoft.com/kb/156484

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

  + It's impossible for everything to be true. +

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Magnus Hagander
On Tue, Aug 24, 2010 at 15:58, Tom Lane  wrote:
> Bruce Momjian  writes:
>> Robert Haas wrote:
>>> Yeah, that seems very plausible, although exactly how to verify I don't 
>>> know.
>
>> And here is confirmation from the Microsoft web site:
>
>>       In some instances, calling GetExitCode() against the failed process
>>       indicates the following exit code:
>>       128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for.
>
> Given the existence of the deadman switch mechanism (which I hadn't
> remembered when this thread started), I'm coming around to the idea that
> we could just treat exit(128) as nonfatal on Windows.  If for some
> reason the child hadn't died instantly at startup, the deadman switch
> would distinguish that from the case described here.

Just because I had written it before you posted that, here's how the
win32-specific-set-a-flag-when-we're-in-control thing would look. But
if we're convinced that just ignoring error 128 is safe, then that's
obviously a simpler patch..

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


win32_early_death.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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Bruce Momjian
Bruce Momjian wrote:
> Josh Berkus wrote:
> > All,
> > 
> > I've been looking at the open item which belongs with this doc:
> > 
> > http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
> > 
> > ... and my viewpoint is that the procedure described should be *cut*
> > from the official docs and put on the Wiki, with warnings.  It's simply
> > far too hackish and dependant on DBA understanding of PostgreSQL
> > internals to belong in the main docs.
> > 
> > In 9.1 we'll probably have some machinery to make taking snapshots of
> > the standby more reliable (like running pg_start_backup on the master),
> > and then that procedure will belong in the main docs.
> 
> FYI, I am working on a doc update that will use pg_start/stop backup on
> the master, as suggested by Fujii Masao:
> 
>   http://archives.postgresql.org/pgsql-bugs/2010-08/msg00237.php

I have created a doc patch (attached) to document a clean way to take a
backup of the standby. This can be used for pitr, and to create
additional slaves, so I do think this will be used more during 9.0.  You
can see the output here:

http://momjian.us/tmp/pgsql/backup-incremental-updated.html

The actual list of steps only appeared in 9.0 so I suggest this only be
applied to HEAD and 9.0.X.

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

  + It's impossible for everything to be true. +
Index: doc/src/sgml/high-availability.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/high-availability.sgml,v
retrieving revision 1.80
diff -c -c -r1.80 high-availability.sgml
*** doc/src/sgml/high-availability.sgml	24 Aug 2010 15:22:12 -	1.80
--- doc/src/sgml/high-availability.sgml	24 Aug 2010 19:05:06 -
***
*** 1913,1921 
 
  If we take a file system backup of the standby server's data
  directory while it is processing
! logs shipped from the primary, we will be able to reload that backup and
! restart the standby's recovery process from the last restart point.
! We no longer need to keep WAL files from before the standby's restart point.
  If recovery is needed, it will be faster to recover from the incrementally
  updated backup than from the original base backup.
 
--- 1913,1922 
 
  If we take a file system backup of the standby server's data
  directory while it is processing
! logs shipped from the primary, we will be able to use that new
! backup to create a new standby or for continuous archive recovery.
! We then no longer need to keep WAL files from before the new backup.
  If recovery is needed, it will be faster to recover from the incrementally
  updated backup than from the original base backup.
 
***
*** 1926,1984 
 
  
   
!   Perform the backup, without using pg_start_backup and
!   pg_stop_backup. Note that the pg_control
!   file must be backed up first, as in:
! 
! cp /var/lib/pgsql/data/global/pg_control /tmp
! cp -r /var/lib/pgsql/data /path/to/backup
! mv /tmp/pg_control /path/to/backup/data/global
! 
!   pg_control contains the location where WAL replay will
!   begin after restoring from the backup; backing it up first ensures
!   that it points to the last restartpoint when the backup started, not
!   some later restartpoint that happened while files were copied to the
!   backup.
   
  
  
   
!   Make note of the backup ending WAL location by calling the 
!   pg_last_xlog_replay_location function at the end of the backup,
!   and keep it with the backup.
  
! psql -c "select pg_last_xlog_replay_location();" > /path/to/backup/end_location
  
-   When recovering from the incrementally updated backup, the server
-   can begin accepting connections and complete the recovery successfully
-   before the database has become consistent. To avoid that, you must
-   ensure the database is consistent before users try to connect to the
-   server and when the recovery ends. You can do that by comparing the
-   progress of the recovery with the stored backup ending WAL location:
-   the server is not consistent until recovery has reached the backup end
-   location. The progress of the recovery can also be observed with the
-   pg_last_xlog_replay_location function, though that requires
-   connecting to the server while it might not be consistent yet, so
-   care should be taken with that method.
   
   
   
  
 
 
- 
-
- Since the standby server is not live, it is not possible to
- use pg_start_backup() and pg_stop_backup()
- to manage the backup process; it will be up to you to determine how
- far back you need to keep WAL segment files to have a recoverable
- backup. That is determined by the last restartpoint when the backup
-

Re: [HACKERS] About debug two versions of postgresql in eclipse

2010-08-24 Thread Pei He
Thanks, Nicolas.
It works.

--
Pei

On Tue, Aug 24, 2010 at 2:38 PM, Nicolas Barbier
 wrote:
> 2010/8/24 Pei He :
>
>> I want to run two different versions of postgresql-8.2.5 under eclipse.
>>
>> But, it requires me to change PGDATA and LD_LIBRARY_PATH to switch.
>>
>> Moreover, to let eclipse know the changes, I need to modify .profile
>> under my home folder, and log out and log in. (I am using Ubuntu.)
>>
>> Is there a way that I can debug two versions of postgresql without
>> closing eclipse?
>
> In the each run configuration, in the "Environment" tab, add those
> environment variables, and given them values that correspond to the PG
> version that corresponds to that run configuration.
>
> Nicolas
>

-- 
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] About debug two versions of postgresql in eclipse

2010-08-24 Thread Nicolas Barbier
2010/8/24 Pei He :

> I want to run two different versions of postgresql-8.2.5 under eclipse.
>
> But, it requires me to change PGDATA and LD_LIBRARY_PATH to switch.
>
> Moreover, to let eclipse know the changes, I need to modify .profile
> under my home folder, and log out and log in. (I am using Ubuntu.)
>
> Is there a way that I can debug two versions of postgresql without
> closing eclipse?

In the each run configuration, in the "Environment" tab, add those
environment variables, and given them values that correspond to the PG
version that corresponds to that run configuration.

Nicolas

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
"David E. Wheeler"  writes:
> On Aug 24, 2010, at 10:21 AM, Tom Lane wrote:
>> The order was bothering me a bit too, but there's a generic decision
>> in there that the tlist is shown before any node-type-specific items.
>> Not sure that we want to move it to the bottom for all of them.

> Does it make a difference for the other ouputs (JSON, YAML, XML)?

It shouldn't should it?  The order of fields in a node should not
matter too much for any readers of those formats.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread David E. Wheeler
On Aug 24, 2010, at 10:21 AM, Tom Lane wrote:

>> This may be the ultimate bike-shed but Wouldn't this be clearer the
>> other way around? I generally think input comes first and then output.
> 
> The order was bothering me a bit too, but there's a generic decision
> in there that the tlist is shown before any node-type-specific items.
> Not sure that we want to move it to the bottom for all of them.

Does it make a difference for the other ouputs (JSON, YAML, XML)?

Best,

David


-- 
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] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Bruce Momjian
Josh Berkus wrote:
> All,
> 
> I've been looking at the open item which belongs with this doc:
> 
> http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html
> 
> ... and my viewpoint is that the procedure described should be *cut*
> from the official docs and put on the Wiki, with warnings.  It's simply
> far too hackish and dependant on DBA understanding of PostgreSQL
> internals to belong in the main docs.
> 
> In 9.1 we'll probably have some machinery to make taking snapshots of
> the standby more reliable (like running pg_start_backup on the master),
> and then that procedure will belong in the main docs.

FYI, I am working on a doc update that will use pg_start/stop backup on
the master, as suggested by Fujii Masao:

http://archives.postgresql.org/pgsql-bugs/2010-08/msg00237.php

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

  + It's impossible for everything to be true. +

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


[HACKERS] Backups from the standby (Incrementally Updated Backups), open item

2010-08-24 Thread Josh Berkus
All,

I've been looking at the open item which belongs with this doc:

http://www.postgresql.org/docs/9.0/static/backup-incremental-updated.html

... and my viewpoint is that the procedure described should be *cut*
from the official docs and put on the Wiki, with warnings.  It's simply
far too hackish and dependant on DBA understanding of PostgreSQL
internals to belong in the main docs.

In 9.1 we'll probably have some machinery to make taking snapshots of
the standby more reliable (like running pg_start_backup on the master),
and then that procedure will belong in the main docs.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] [ADMIN] Unable to drop role

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 1:19 PM, Tom Lane  wrote:
> Alvaro Herrera  writes:
>> Happy to change it to whatever is deemed appropriate.  "privileges for %s"
>> sounds good; I'll do that unless somebody comes up with a better idea
>> which outvotes this one.
>
>> Backpatch all the way to 8.1?  Code doesn't exist prior to that.
>
> I'd vote for fixing it in HEAD and perhaps 9.0, but not earlier.
> Changing this will cause problems for translators, and it doesn't
> seem important enough to mess up stable-branch translations.

+1.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 1:21 PM, Tom Lane  wrote:
> Greg Stark  writes:
>> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane  wrote:
>>>  Function Scan on pg_catalog.unnest f  (cost=0.00..1.50 rows=100 width=96)
>>>   Output: (((lv)::text || op) || (rv)::text)
>>>   Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, 
>>> ('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])
>
>> This may be the ultimate bike-shed but Wouldn't this be clearer the
>> other way around? I generally think input comes first and then output.
>
> The order was bothering me a bit too, but there's a generic decision
> in there that the tlist is shown before any node-type-specific items.
> Not sure that we want to move it to the bottom for all of them.

I don't think we do.  Although, it has seemed to me at times that we
might want to have "output" be controlled by its own EXPLAIN option.

EXPLAIN (VERBOSE on, OUTPUT off) ...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Typing Records

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 12:27 PM, Tom Lane  wrote:
> This is a crash in released branches, so we have to have a
> back-patchable fix.  Anything that gets out from under the typmod issue
> isn't going to be back-patchable.

I nominate that comment for "understatement of the year".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
Greg Stark  writes:
> On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane  wrote:
>>  Function Scan on pg_catalog.unnest f  (cost=0.00..1.50 rows=100 width=96)
>>   Output: (((lv)::text || op) || (rv)::text)
>>   Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, 
>> ('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])

> This may be the ultimate bike-shed but Wouldn't this be clearer the
> other way around? I generally think input comes first and then output.

The order was bothering me a bit too, but there's a generic decision
in there that the tlist is shown before any node-type-specific items.
Not sure that we want to move it to the bottom for all of 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] [ADMIN] Unable to drop role

2010-08-24 Thread Tom Lane
Alvaro Herrera  writes:
> Happy to change it to whatever is deemed appropriate.  "privileges for %s"
> sounds good; I'll do that unless somebody comes up with a better idea
> which outvotes this one.

> Backpatch all the way to 8.1?  Code doesn't exist prior to that.

I'd vote for fixing it in HEAD and perhaps 9.0, but not earlier.
Changing this will cause problems for translators, and it doesn't
seem important enough to mess up stable-branch translations.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Greg Stark
On Tue, Aug 24, 2010 at 5:06 PM, Tom Lane  wrote:
>  Function Scan on pg_catalog.unnest f  (cost=0.00..1.50 rows=100 width=96)
>   Output: (((lv)::text || op) || (rv)::text)
>   Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, 
> ('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])

This may be the ultimate bike-shed but Wouldn't this be clearer the
other way around? I generally think input comes first and then output.

On the other hand the plan tree does read from bottom up with the
outputs going up to the next level up.


-- 
greg

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


[HACKERS] About debug two versions of postgresql in eclipse

2010-08-24 Thread Pei He
Hi,
I want to run two different versions of postgresql-8.2.5 under eclipse.

But, it requires me to change PGDATA and LD_LIBRARY_PATH to switch.

Moreover, to let eclipse know the changes, I need to modify .profile
under my home folder, and log out and log in. (I am using Ubuntu.)

Is there a way that I can debug two versions of postgresql without
closing eclipse?


Thanks
--
Pei

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread David Fetter
On Tue, Aug 24, 2010 at 12:06:34PM -0400, Tom Lane wrote:
> I wrote:
> > Robert Haas  writes:
> >> If you try to put all that on the same line, I think it might get
> >> awkwardly long.  Perhaps something like:
> 
> >> Function Scan on function_name
> >> Expression: function_name(function_arg1, function_arg2, ...)
> 
> > Yeah, that's what I had in mind, but I'm still fumbling for the right
> > label.  "Expression" seems a bit too generic.
> 
> How about "Function Call"?
> 
> A moment's hacking later:
> 
> regression=# explain verbose SELECT lv|| op|| rv FROM unnest(ARRAY[
> ROW('1.2.2'::semver,  '='::text, '1.2.2'::semver),
> ROW('1.2.23', '=', '1.2.23')
> ]) AS f(lv semver, op text, rv semver);
>QUERY PLAN 
>   
> 
>  Function Scan on pg_catalog.unnest f  (cost=0.00..1.50 rows=100 width=96)
>Output: (((lv)::text || op) || (rv)::text)
>Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, 
> ('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])
> (3 rows)
> 
> Look reasonable?

This would have helped me a good deal a couple of times this week :)

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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] [ADMIN] Unable to drop role

2010-08-24 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar ago 24 09:36:05 -0400 2010:
> "McGehee, Robert"  writes:
> > Thanks Tom and Alvaro for clearing up my confusion.
> > \l showed that a485099 had both (C)reate and (T)emporary access.
> > Revoking those allowed me to drop the role. Thanks for the help!
> 
> I wonder whether Robert's confusion doesn't stem from a poor choice
> of message wording:
> 
> >> template1=# DROP ROLE a485099;
> >> ERROR:  role "a485099" cannot be dropped because some objects depend on it
> >> DETAIL:  access to database template1
> 
> I can see how "access to" might be read as specifically meaning "CONNECT
> privilege for".  Should we change this message from "access to whatever"
> to "privileges for whatever", or some such wording?

Code is here:
else if (deptype == SHARED_DEPENDENCY_ACL)
appendStringInfo(descs, _("access to %s"), 
objdesc);
in StoreObjectDescription().

Happy to change it to whatever is deemed appropriate.  "privileges for %s"
sounds good; I'll do that unless somebody comes up with a better idea
which outvotes this one.

Backpatch all the way to 8.1?  Code doesn't exist prior to that.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Problem Using PQcancel in a Synchronous Query

2010-08-24 Thread Eric Simon
Tom,

That was it!  My implementation of cancel() was swallowing the result
message.  Thanks so much, I've got it working now.

-- 
Eric Simon
The IQ Group, Inc.


-Original Message-
From: pgsql-hackers-ow...@postgresql.org
[mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, August 23, 2010 7:06 PM
To: Eric Simon
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Problem Using PQcancel in a Synchronous Query 

"Eric Simon"  writes:
> Now that I've established some context, here's where I'm at: I've written
> $sth->cancel() for DBD::Pg using PQcancel(), and it works (it returns the
> status 57014: QUERY CANCELED).  The problem is that the $sth->execute call
> (which resides between the two alarm() calls above) doesn't continue on,
but
> rather stays frozen, waiting for data.  Does PQcancel not communicate back
> to the execute statement so that it unblocks?

Um ... PQcancel returns no such thing, only true or false.  I'm guessing
you've coded your signal handler in such a way that it eats the query
result message intended for the mainline execute code.  You should not
be calling anything except PQcancel itself in the signal handler.

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


-- 
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] Typing Records

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> On Tue, Aug 24, 2010 at 11:32 AM, Tom Lane  wrote:
>> I think it wouldn't take too much code to defend against this in
>> transformArrayExpr, but I'm a tad worried about whether there are
>> similar cases elsewhere.  The generic problem is that we suppose that
>> different values are compatible if they have the same type OID, but
>> for RECORD types that's really not true.

> We've argued about this before: it's not really true for array types
> either.

Yeah, I'm starting to feel that the typmod hack for this is just not
good enough.  However ...

> Nevertheless, there's not much hope of better than a localized fix for
> this particular bug.

This is a crash in released branches, so we have to have a
back-patchable fix.  Anything that gets out from under the typmod issue
isn't going to be back-patchable.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
I wrote:
> Robert Haas  writes:
>> If you try to put all that on the same line, I think it might get
>> awkwardly long.  Perhaps something like:

>> Function Scan on function_name
>> Expression: function_name(function_arg1, function_arg2, ...)

> Yeah, that's what I had in mind, but I'm still fumbling for the right
> label.  "Expression" seems a bit too generic.

How about "Function Call"?

A moment's hacking later:

regression=# explain verbose SELECT lv|| op|| rv FROM unnest(ARRAY[
ROW('1.2.2'::semver,  '='::text, '1.2.2'::semver),
ROW('1.2.23', '=', '1.2.23')
]) AS f(lv semver, op text, rv semver);
   QUERY PLAN   


 Function Scan on pg_catalog.unnest f  (cost=0.00..1.50 rows=100 width=96)
   Output: (((lv)::text || op) || (rv)::text)
   Function Call: unnest(ARRAY[ROW(('1.2.2'::text)::semver, '='::text, 
('1.2.2'::text)::semver), ROW('1.2.23', '=', '1.2.23')])
(3 rows)

Look reasonable?

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Alvaro Herrera
Excerpts from Tom Lane's message of mar ago 24 11:36:50 -0400 2010:
> Andres Freund  writes:
> > On Tuesday 24 August 2010 16:40:30 Tom Lane wrote:
> >> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
> >> report.  Seems like this would be a good thing to print.  Should
> >> it appear always, or just with VERBOSE, or ???
> 
> > I vote for only showing it with verbose
> 
> That'd be fine with me.  The lack of prior complaints suggests that
> most of the time people don't need it.

Yeah, and if the expression is large, it could get cumbersome (consider
crosstab functions which are often called with large gobs of text).  On
the other hand, the same case makes it very useful to have the text for
further research on query behavior.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Typing Records

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 11:32 AM, Tom Lane  wrote:
> I think it wouldn't take too much code to defend against this in
> transformArrayExpr, but I'm a tad worried about whether there are
> similar cases elsewhere.  The generic problem is that we suppose that
> different values are compatible if they have the same type OID, but
> for RECORD types that's really not true.

We've argued about this before: it's not really true for array types
either.  A one-dimensional array is not the same type as a
two-dimensional array, but we treat it that way because bloating
pg_type by a factor of seven is even less appealing than bloating it
by a factor of two.  And then there are other kinds of types people
might want to define: hashes, sets, functions, etc.  This shoe is
going to rub for so long as we keep wearing it.

Nevertheless, there's not much hope of better than a localized fix for
this particular bug.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Andres Freund
On Tuesday 24 August 2010 17:36:50 Tom Lane wrote:
> Andres Freund  writes:
> > On Tuesday 24 August 2010 16:40:30 Tom Lane wrote:
> >> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
> >> report.  Seems like this would be a good thing to print.  Should
> >> it appear always, or just with VERBOSE, or ???
> > 
> > I vote for only showing it with verbose
> 
> That'd be fine with me.  The lack of prior complaints suggests that
> most of the time people don't need it.
I personally missed it but never got around to complain/do something.

Andres

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
Andres Freund  writes:
> On Tuesday 24 August 2010 16:40:30 Tom Lane wrote:
>> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
>> report.  Seems like this would be a good thing to print.  Should
>> it appear always, or just with VERBOSE, or ???

> I vote for only showing it with verbose

That'd be fine with me.  The lack of prior complaints suggests that
most of the time people don't need it.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> If you try to put all that on the same line, I think it might get
> awkwardly long.  Perhaps something like:

> Function Scan on function_name
>Expression: function_name(function_arg1, function_arg2, ...)

Yeah, that's what I had in mind, but I'm still fumbling for the right
label.  "Expression" seems a bit too generic.

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] Typing Records

2010-08-24 Thread Tom Lane
"David E. Wheeler"  writes:
> On Aug 24, 2010, at 7:05 AM, Tom Lane wrote:
>> I get a core dump on that one ... looking ...

> Well I'm glad I reported it, then.

The issue seems to be that given a construct like

ARRAY[
ROW('1.2.2'::semver,  '='::text, '1.2.2'::semver),
ROW('1.2.23', '=', '1.2.23')
]

the parser is satisfied upon finding that all the array elements are
of type RECORD.  It doesn't do anything to make sure they are all of
the *same* anonymous record type ... and here they are not.  The
second one is just RECORD(UNKNOWN, UNKNOWN, UNKNOWN), which doesn't
even have a compatible representation with the first one.  So at runtime
we end up trying to disassemble a tuple containing three UNKNOWN fields
using a tupledesc for the other rowtype.

I think it wouldn't take too much code to defend against this in
transformArrayExpr, but I'm a tad worried about whether there are
similar cases elsewhere.  The generic problem is that we suppose that
different values are compatible if they have the same type OID, but
for RECORD types that's really not true.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Andres Freund
Hi,

On Tuesday 24 August 2010 16:40:30 Tom Lane wrote:
> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
> report.  Seems like this would be a good thing to print.  Should
> it appear always, or just with VERBOSE, or ???
I vote for only showing it with verbose - not that its a new problem, but too 
long argument lists (arrays) you could make otherwise readable plans 
unreadable.


Andres

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 11:06 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas  wrote:
>>> I think showing it always is reasonable.  I'd like to see it printed
>>> in a form such that casting to regproc will succeed.
>
>> On second thought, that second sentence may not make sense.
>
> It does not, because it's not the *name* of the function that I care
> about --- it's the actual executable expression including arguments.
>
>> What exactly did you have in mind for this to look like?
>
> Wheeler's example involves
>
>        select ... from unnest(array[blah blah blah])
>
> and I'd like it to regurgitate the whole unnest(array[blah blah blah])
> expression.  Not sure how to label it exactly.  Right now you only see
>
>  Function Scan on unnest f  (cost=0.00..1.50 rows=100 width=96)
>
> or with VERBOSE, it'll give you some info about the targetlist (the ...
> above), but still nothing about the FROM expression.

If you try to put all that on the same line, I think it might get
awkwardly long.  Perhaps something like:

Function Scan on function_name
   Expression: function_name(function_arg1, function_arg2, ...)

?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Typing Records

2010-08-24 Thread David E. Wheeler
On Aug 24, 2010, at 7:05 AM, Tom Lane wrote:

> You could do it like this:
> 
>SELECT cmp_ok(lv, op, rv) FROM unnest(ARRAY[
>ROW('1.2.2',  '=', '1.2.2'),
>ROW('1.2.23', '=', '1.2.23')
>]::vcmp[]);

Oh, duh. :-)

>> psql:t/types.pg:205: ERROR:  invalid memory alloc request size 
>> 18446744071604011012
>> Wha??
>> That seems like a bug.
> 
> I get a core dump on that one ... looking ...

Well I'm glad I reported it, then.

Best,

David


-- 
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] Typing Records

2010-08-24 Thread David E. Wheeler
On Aug 23, 2010, at 11:24 PM, Joe Conway wrote:

> Maybe something like this?
> 
> select cmp_ok(a,b,c)
> from
> (
>values('1.2.2'::varchar,  '='::text, '1.2.2'::varchar),
>  ('1.2.23', '=', '1.2.23'),
>  ('1.2.42', '=', '1.2.32')
> ) as ss(a, b, c);
> cmp_ok
> 
> t
> t
> f
> (3 rows)

Yes, exactly what I wanted. I knew I was missing something subtle (I had too 
many parens when I did it). Thanks Joe!

Best,

David


-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
Robert Haas  writes:
> On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas  wrote:
>> I think showing it always is reasonable.  I'd like to see it printed
>> in a form such that casting to regproc will succeed.

> On second thought, that second sentence may not make sense.

It does not, because it's not the *name* of the function that I care
about --- it's the actual executable expression including arguments.

> What exactly did you have in mind for this to look like?

Wheeler's example involves

select ... from unnest(array[blah blah blah])

and I'd like it to regurgitate the whole unnest(array[blah blah blah])
expression.  Not sure how to label it exactly.  Right now you only see

 Function Scan on unnest f  (cost=0.00..1.50 rows=100 width=96)

or with VERBOSE, it'll give you some info about the targetlist (the ...
above), but still nothing about the FROM expression.

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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 10:56 AM, Robert Haas  wrote:
> On Tue, Aug 24, 2010 at 10:40 AM, Tom Lane  wrote:
>> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
>> report.  Seems like this would be a good thing to print.  Should
>> it appear always, or just with VERBOSE, or ???
>
> I think showing it always is reasonable.  I'd like to see it printed
> in a form such that casting to regproc will succeed.

On second thought, that second sentence may not make sense.  What
exactly did you have in mind for this to look like?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 10:40 AM, Tom Lane  wrote:
> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
> report.  Seems like this would be a good thing to print.  Should
> it appear always, or just with VERBOSE, or ???

I think showing it always is reasonable.  I'd like to see it printed
in a form such that casting to regproc will succeed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread David Fetter
On Tue, Aug 24, 2010 at 10:40:30AM -0400, Tom Lane wrote:
> So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
> report.  Seems like this would be a good thing to print.  Should
> it appear always, or just with VERBOSE, or ???

+1 for always.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

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


[HACKERS] EXPLAIN doesn't show the actual function expression for FunctionScan

2010-08-24 Thread Tom Lane
So I got annoyed by $SUBJECT just now while chasing Wheeler's bug
report.  Seems like this would be a good thing to print.  Should
it appear always, or just with VERBOSE, or ???

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] Typing Records

2010-08-24 Thread Tom Lane
"David E. Wheeler"  writes:
> I've been trying to come up with a simpler way to iterate over a series of 
> values in pgTAP tests than by creating a table, inserting rows, and then 
> selecting from the table. The best I've come up with so far is:

> CREATE TYPE vcmp AS ( lv semver, op text, rv semver);

> SELECT cmp_ok(lv, op, rv) FROM unnest(ARRAY[
> ROW('1.2.2',  '=', '1.2.2')::vcmp,
> ROW('1.2.23', '=', '1.2.23')::vcmp
> ]);

> Not bad, but I was hoping that I could cast all the rows at once,

You could do it like this:

SELECT cmp_ok(lv, op, rv) FROM unnest(ARRAY[
ROW('1.2.2',  '=', '1.2.2'),
ROW('1.2.23', '=', '1.2.23')
]::vcmp[]);


> psql:t/types.pg:205: ERROR:  invalid memory alloc request size 
> 18446744071604011012
> Wha??
> That seems like a bug.

I get a core dump on that one ... looking ...

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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Bruce Momjian
Tom Lane wrote:
> Bruce Momjian  writes:
> > Robert Haas wrote:
> >> Yeah, that seems very plausible, although exactly how to verify I don't 
> >> know.
> 
> > And here is confirmation from the Microsoft web site:
>   
> > In some instances, calling GetExitCode() against the failed process
> > indicates the following exit code:
> > 128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for. 
> 
> Given the existence of the deadman switch mechanism (which I hadn't
> remembered when this thread started), I'm coming around to the idea that
> we could just treat exit(128) as nonfatal on Windows.  If for some
> reason the child hadn't died instantly at startup, the deadman switch
> would distinguish that from the case described here.

Here is a more detailed explaination of the failure and its relation to
desktop heap:

http://kbalertz.com/Feedback.aspx?kbNumber=184802

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

  + It's impossible for everything to be true. +

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Bruce Momjian
Tom Lane wrote:
> Bruce Momjian  writes:
> > Robert Haas wrote:
> >> Yeah, that seems very plausible, although exactly how to verify I don't 
> >> know.
> 
> > And here is confirmation from the Microsoft web site:
>   
> > In some instances, calling GetExitCode() against the failed process
> > indicates the following exit code:
> > 128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for. 
> 
> Given the existence of the deadman switch mechanism (which I hadn't
> remembered when this thread started), I'm coming around to the idea that
> we could just treat exit(128) as nonfatal on Windows.  If for some
> reason the child hadn't died instantly at startup, the deadman switch
> would distinguish that from the case described here.

Agreed.  My guess is that there is some kind of Win32 OS race condition
in allocating desktop heap memory, and that sometimes with concurrent
CreateProcess() calls, a process gets started but can't complete its
creation.

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

  + It's impossible for everything to be true. +

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Tom Lane
Bruce Momjian  writes:
> Robert Haas wrote:
>> Yeah, that seems very plausible, although exactly how to verify I don't know.

> And here is confirmation from the Microsoft web site:

>   In some instances, calling GetExitCode() against the failed process
>   indicates the following exit code:
>   128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for. 

Given the existence of the deadman switch mechanism (which I hadn't
remembered when this thread started), I'm coming around to the idea that
we could just treat exit(128) as nonfatal on Windows.  If for some
reason the child hadn't died instantly at startup, the deadman switch
would distinguish that from the case described 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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Bruce Momjian
Robert Haas wrote:
> >> This isn't really a "fix" for the bug in the sense that the nicest
> >> thing of all would be to prevent the child from exiting abnormally in
> >> the first place. ?But it's far from clear that we can control that.
> >
> > This URL has some interesting details on our problem:
> >
> > ? ? ? 
> > ?http://stackoverflow.com/questions/139090/getexitcodeprocess-returns-128
> >
> > Error code 128 is identified as:
> >
> > ? ? ? ?error code 128 RROR_WAIT_NO_CHILDREN 128 0x80 There are no child
> > ? ? ? ?processes to wait for
> >
> > and the suggested cause is:
> >
> > ? ? ? ?Have a look at Desktop Heap memory.
> >
> > ? ? ? ?Essentially the desktop heap issue comes down to exhausted resources 
> > (eg
> > ? ? ? ?starting too many processes). When your app runs out of these 
> > resources,
> > ? ? ? ?one of the symptoms is that you won't be able to start a new process,
> > ? ? ? ?and the call to CreateProcess will fail with code 128.
> >
> > My guess is that at the time of CreateProcess(), there is enough desktop
> > heap memory, but at some later time, perhaps caused by a logout, there
> > isn't and the process never gets started.
> 
> Yeah, that seems very plausible, although exactly how to verify I don't know.

And here is confirmation from the Microsoft web site:

http://support.microsoft.com/kb/156484

Cmd.exe, Perl.exe, or other console-mode applications may fail to
initialize properly and terminate prematurely when launched by a service
using the CreateProcess() or CreateProcessAsUser() APIs. The calling
process has no way of knowing that the launched console-mode application
has terminated prematurely.

In some instances, calling GetExitCode() against the failed process
indicates the following exit code:
128L ERROR_WAIT_NO_CHILDREN - There are no child processes to wait for. 
...
Internet Information Server (IIS) may exhibit this problem
intermittently when processing CGI or Perl scripts. In this case the
browser returns the following error when executing CGI scripts:

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

  + It's impossible for everything to be true. +

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Robert Haas
On Tue, Aug 24, 2010 at 8:57 AM, Bruce Momjian  wrote:
> Robert Haas wrote:
>> [moving to -hackers]
>>
>> On Thu, Aug 19, 2010 at 9:43 PM, Robert Haas  wrote:
>> > I suspect this is the same problem as bug #4897, and probably also the
>> > same problem as this:
>> > http://archives.postgresql.org/pgsql-bugs/2009-08/msg00114.php
>> >
>> > and maybe also this and this:
>> > http://archives.postgresql.org/pgsql-bugs/2010-02/msg00179.php
>> > http://archives.postgresql.org/pgsql-admin/2009-05/msg00105.php
>> >
>> > Unfortunately, it seems that no one has been able to get a stack trace yet.
>>
>> Bruce pointed out yet another report of this problem to me:
>>
>> http://archives.postgresql.org/pgsql-general/2010-08/msg00550.php
>>
>> After some discussion with Magnus, I think what is going on here is
>> that the postmaster kicks off a new child process, which terminates
>> before it actually starts running our code, either in OS-supplied code
>> or some sort of "filter" like anti-spam or anti-virus software.  It's
>> presumably NOT dying in our code because - at least AFAICS - we don't
>> exit(128) anywhere.  One way we could possibly improve the situation
>> is to not treat this as a child crash - that is, don't do a
>> crash-and-restart cycle; just treat that backend as having done
>> elog(FATAL).  The trick is that you need a reliable way to distinguish
>> between a regular child crash and an "early" child crash.  Magnus
>> suggested perhaps we could create a mutex that the child grabs before
>> mapping shared memory; the postmaster could check whether the mutex
>> had been taken.  If so, we handle the crash normally; if not, we just
>> chalk it up to experience and continue on.
>>
>> This isn't really a "fix" for the bug in the sense that the nicest
>> thing of all would be to prevent the child from exiting abnormally in
>> the first place.  But it's far from clear that we can control that.
>
> This URL has some interesting details on our problem:
>
>        
> http://stackoverflow.com/questions/139090/getexitcodeprocess-returns-128
>
> Error code 128 is identified as:
>
>        error code 128 RROR_WAIT_NO_CHILDREN 128 0x80 There are no child
>        processes to wait for
>
> and the suggested cause is:
>
>        Have a look at Desktop Heap memory.
>
>        Essentially the desktop heap issue comes down to exhausted resources 
> (eg
>        starting too many processes). When your app runs out of these 
> resources,
>        one of the symptoms is that you won't be able to start a new process,
>        and the call to CreateProcess will fail with code 128.
>
> My guess is that at the time of CreateProcess(), there is enough desktop
> heap memory, but at some later time, perhaps caused by a logout, there
> isn't and the process never gets started.

Yeah, that seems very plausible, although exactly how to verify I don't know.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

-- 
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] Return of the Solaris vacuum polling problem -- anyone remember this?

2010-08-24 Thread Bruce Momjian

Applied.

---

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian  writes:
> > > OK, I have attached a proposed patch to improve this.  I moved the
> > > pg_clog mention to a new paragraph and linked it to the reason the
> > > default is relatively low.
> > 
> > The references to "vacuum freeze" are incorrect; autovacuum does NOT
> > do the equivalent of VACUUM FREEZE.  Please stop playing around with
> > the perfectly good existing wording.
> 
> Uh, so VACUUM FREEZE unconditionally freezes all rows, while vacuum just
> freezes rows who's xid is older than vacuum_freeze_min_age?  I saw that
> in our current docs in reference to VACUUM FREEZE:
> 
>   Selects aggressive "freezing" of tuples. Specifying FREEZE is
>   equivalent to performing VACUUM with the vacuum_freeze_min_age
>   parameter set to zero. The FREEZE option is deprecated and
>   will be removed in a future release; set the parameter instead.
> 
> Updated patch attached.
> 
> -- 
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
> 
>   + It's impossible for everything to be true. +


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

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

  + It's impossible for everything to be true. +

-- 
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] [BUGS] BUG #5305: Postgres service stops when closing Windows session

2010-08-24 Thread Bruce Momjian
Robert Haas wrote:
> [moving to -hackers]
> 
> On Thu, Aug 19, 2010 at 9:43 PM, Robert Haas  wrote:
> > I suspect this is the same problem as bug #4897, and probably also the
> > same problem as this:
> > http://archives.postgresql.org/pgsql-bugs/2009-08/msg00114.php
> >
> > and maybe also this and this:
> > http://archives.postgresql.org/pgsql-bugs/2010-02/msg00179.php
> > http://archives.postgresql.org/pgsql-admin/2009-05/msg00105.php
> >
> > Unfortunately, it seems that no one has been able to get a stack trace yet.
> 
> Bruce pointed out yet another report of this problem to me:
> 
> http://archives.postgresql.org/pgsql-general/2010-08/msg00550.php
> 
> After some discussion with Magnus, I think what is going on here is
> that the postmaster kicks off a new child process, which terminates
> before it actually starts running our code, either in OS-supplied code
> or some sort of "filter" like anti-spam or anti-virus software.  It's
> presumably NOT dying in our code because - at least AFAICS - we don't
> exit(128) anywhere.  One way we could possibly improve the situation
> is to not treat this as a child crash - that is, don't do a
> crash-and-restart cycle; just treat that backend as having done
> elog(FATAL).  The trick is that you need a reliable way to distinguish
> between a regular child crash and an "early" child crash.  Magnus
> suggested perhaps we could create a mutex that the child grabs before
> mapping shared memory; the postmaster could check whether the mutex
> had been taken.  If so, we handle the crash normally; if not, we just
> chalk it up to experience and continue on.
> 
> This isn't really a "fix" for the bug in the sense that the nicest
> thing of all would be to prevent the child from exiting abnormally in
> the first place.  But it's far from clear that we can control that.

This URL has some interesting details on our problem:

http://stackoverflow.com/questions/139090/getexitcodeprocess-returns-128

Error code 128 is identified as:

error code 128 RROR_WAIT_NO_CHILDREN 128 0x80 There are no child
processes to wait for

and the suggested cause is:

Have a look at Desktop Heap memory.

Essentially the desktop heap issue comes down to exhausted resources (eg
starting too many processes). When your app runs out of these resources,
one of the symptoms is that you won't be able to start a new process,
and the call to CreateProcess will fail with code 128.

My guess is that at the time of CreateProcess(), there is enough desktop
heap memory, but at some later time, perhaps caused by a logout, there
isn't and the process never gets started.

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

  + It's impossible for everything to be true. +

-- 
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] Fw: patch for pg_ctl.c to add windows service start-type

2010-08-24 Thread Magnus Hagander
On Tue, Aug 24, 2010 at 2:05 PM, Quan Zongliang  wrote:
> Which files need be modified?
> sgml, html, and man-page or only sgml?
> I am not familiar with this.

Only SGML. HTML and man pages are generated from the SGML.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] WIP: extensible enums

2010-08-24 Thread Bruce Momjian
Andrew Dunstan wrote:
> 
> 
> On 08/23/2010 07:12 PM, Bruce Momjian wrote:
> > Josh Berkus wrote:
> >> On 8/23/10 12:20 PM, Tom Lane wrote:
> >>> Josh Berkus  writes:
>  I really don't see the value in making a command substantially less
>  intuitive in order to avoid a single keyword, unless it affects areas of
>  Postgres outside of this particular command.
> >>> It's the three variants to do two things that I find unintuitive.
> >> Actually, it's 3 different things:
> >>
> >> 1. BEFORE adds a value before the value cited.
> >> 2. AFTER adds a value after the value cited.
> >> 3. unqualified adds a value at the end.
> >>
> >> The fact that AFTER allows you to add a value at the end is
> >> circumstantial overlap.  While executing an AFTER, you wouldn't *know*
> >> that you were adding it to the end, necessarily.
> >>
> >> The other reason to have AFTER is that, in scripts, the user may not
> >> have the before value handy due to context (i.e. dynamically building an
> >> enum).
> >>
> >> Anyway, this'll still be useful with BEFORE only.  I'm just convinced
> >> that we'll end up adding AFTER in 9.2 or 9.3 after we get a bunch of
> >> user complaints and questions.  So why not add it now?
> > CREATE ENUM in PG 9.0 allows you to create an enum with no columns,
> > e.g.:
> >
> > test=>  CREATE TYPE etest AS ENUM ();
> > CREATE TYPE
> >
> > so I think we have to have the ability add an enum without a
> > before/after.  This ability was added for pg_upgrade.
> >
> 
> No we don't. pg_upgrade calls a C function. There is no support for this 
> at the SQL level AIUI. And the ability to add labels at arbitrary 
> positions in the sort order is an essential part of this feature.

pg_upgrade calls a C API to add labels, but the ability to create an
enum with no labels is supported at the SQL level, as I showed above.  I
am not saying we don't need before/after, but I am saying we need the
ability to add labels without using before/after because there are no
labels in an empty enum.

I am not sure what you are arguing for/against.  I thought we were
agreed to allow before/after, and no specification too.  I am just
pointing out that we need the "no specification" syntax for logical as
well as practical reasons.

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

  + It's impossible for everything to be true. +

-- 
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] Fw: patch for pg_ctl.c to add windows service start-type

2010-08-24 Thread Quan Zongliang
Which files need be modified?
sgml, html, and man-page or only sgml?
I am not familiar with this.

On Sun, 22 Aug 2010 08:25:59 -0700
David Fetter  wrote:

> On Sun, Aug 22, 2010 at 10:03:32PM +0800, Quan Zongliang wrote:
> > Sure, I agree.
> > New patch attached. How about this?
> 
> Docs re-added.  Please not to leave these out in future patches. :)
> 
> Cheers,
> David.
> -- 
> David Fetter  http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter  XMPP: david.fet...@gmail.com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
> 
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
Quan Zongliang 

-- 
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] patch (for 9.1) string functions

2010-08-24 Thread Erik Rijkers
On Tue, August 24, 2010 08:32, Itagaki Takahiro wrote:
> I applied the attached patch to HEAD. concat(), concat_ws(), left(),
> right(), and reverse() are in it, but format() and sprintf() are not.

+1 to add also sprintf


Erik Rijkers



-- 
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] WIP: extensible enums

2010-08-24 Thread Andrew Dunstan



On 08/23/2010 07:12 PM, Bruce Momjian wrote:

Josh Berkus wrote:

On 8/23/10 12:20 PM, Tom Lane wrote:

Josh Berkus  writes:

I really don't see the value in making a command substantially less
intuitive in order to avoid a single keyword, unless it affects areas of
Postgres outside of this particular command.

It's the three variants to do two things that I find unintuitive.

Actually, it's 3 different things:

1. BEFORE adds a value before the value cited.
2. AFTER adds a value after the value cited.
3. unqualified adds a value at the end.

The fact that AFTER allows you to add a value at the end is
circumstantial overlap.  While executing an AFTER, you wouldn't *know*
that you were adding it to the end, necessarily.

The other reason to have AFTER is that, in scripts, the user may not
have the before value handy due to context (i.e. dynamically building an
enum).

Anyway, this'll still be useful with BEFORE only.  I'm just convinced
that we'll end up adding AFTER in 9.2 or 9.3 after we get a bunch of
user complaints and questions.  So why not add it now?

CREATE ENUM in PG 9.0 allows you to create an enum with no columns,
e.g.:

test=>  CREATE TYPE etest AS ENUM ();
CREATE TYPE

so I think we have to have the ability add an enum without a
before/after.  This ability was added for pg_upgrade.



No we don't. pg_upgrade calls a C function. There is no support for this 
at the SQL level AIUI. And the ability to add labels at arbitrary 
positions in the sort order is an essential part of this feature.


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] INSERT and parentheses

2010-08-24 Thread Marko Tiikkaja

On 2010-08-24 8:25 AM +0300, igor polishchuk wrote:

Marko et al,
This is my first ever attempt of a patch review just for learning the
procedure. I'm not a postgres developer, so the review is partial and mostly
from the usability prospective.


That's all right.  I'm sure any help is appreciated.


The patch provides a HINT for unclear error. This should clarify for a user
what exactly is wrong with the sql.
However, the actual HINT text provided with the patch is not very clear,
too.
The Stephen Frost's suggestion would add clarity:

errhint("insert appears to be a single column with a record-type rather than
multiple columns of non-composite type."),


This isn't entirely accurate, either; the columns are not necessarily of 
non-composite types.



Regards,
Marko Tiikkaja

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


Re: Interruptible sleeps (was Re: [HACKERS] CommitFest 2009-07: Yay, Kevin! Thanks, reviewers!)

2010-08-24 Thread Heikki Linnakangas

On 24/08/10 04:08, Alvaro Herrera wrote:

Excerpts from Tom Lane's message of lun ago 23 19:44:02 -0400 2010:

Heikki Linnakangas  writes:



[ "latch" proposal ]


This seems reasonably clean as far as signal conditions generated
internally to Postgres go, but I remain unclear on how it helps for
response to actual signals.


This could probably replace the signalling between postmaster and
autovac launcher, as well.


Hmm, postmaster needs to stay out of shared memory..

--
  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: [Glue] [HACKERS] Deadlock bug

2010-08-24 Thread Kevin Grittner
Josh Berkus  wrote:
 
>> the behavior was the same up to the second UPDATE on Process 2, at
>> which point there was no deadlock.  Process 2 was able to commit,
>> at which point Process 1 failed with:
>>  
>> ERROR:  could not serialize access due to concurrent update
>
> Does this happen immediately, not waiting 2 seconds for deadlock
> checking?
 
The deadlock checking delay never comes into play.  Process 2 would
never be blocked, and Process 1 would fail on the COMMIT of Process
2.
 
Without a detailed scenario I can't comment on exact behavior, but in
a serializable-only environment, with SSI enforcement of RI, you can
count on only having blocking on write/write conflicts, so it would
only be a cycle of those which could ever cause a deadlock.  Anything
where deadlocks currently occur because of SELECT FOR SHARE or SELECT
FOR UPDATE would not have the same deadlock issues.
 
-Kevin


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