Re: [HACKERS] FSM search modes

[Kevin Grittner]

Tom Lane  wrote: 
 
> (Hm, so we might want to make the probability depend on
> max_connections?)
 
Without doing rigorous math on it, I'd guess that to prevent
contention among n connections you'd want the probably of resetting
the sweep to be about 1 / (n * 2).  That would mean you'd advance to
the nth page about 60.6% of the time without resetting the sweep.  For
less contention, 1 / (n * 4) would let you get to the nth page about
77.9% of the time.
 
> Maybe what we want is some bias against inserting in the last half
> or quarter of the table, or some such rule, rather than necessarily
> heading for the start of the relation.
 
I think it would make sense to just start using this once you get into
the last half or quarter of the free pages.  If you go with the last
quarter, then you might want to use a higher probability than I
suggested above, although that would tend to leave you with contention
when all the free space was in the last quarter.  I'd be inclined to
use something like the above probability and start using it at 50%.
 
-Kevin

-- 
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] FSM search modes

[Kevin Grittner]

"Kevin Grittner"  wrote: 
 
> I think it would make sense to just start using this once you get
> into the last half or quarter of the free pages.  If you go with the
> last quarter, then you might want to use a higher probability than I
> suggested above, although that would tend to leave you with
> contention when all the free space was in the last quarter.  I'd be
> inclined to use something like the above probability and start using
> it at 50%.
 
Fuzzy thinking there -- if it's the last quarter of the *free* pages,
the suggested probabilities should be fine.  (Somehow I got to
thinking, for a moment, that it would be the last quarter of the
relation's overall pages)
 
-Kevin

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

[HACKERS] CREATE OR REPLACE FUNCTION vs ownership

[Tom Lane]

Whilst fooling with the default ACLs patch I noticed that there's a
pre-existing bug in CREATE OR REPLACE FUNCTION.  It will let you
replace a function if pg_proc_ownercheck passes, which these days
does *not* mean that you are exactly the role mentioned in
pg_proc.proowner; it only means you are some member of that role.
It then proceeds with the function replacement, while keeping the
old proowner value.  It then proceeds to wipe out the old dependency
info and record *you* as the owner in pg_shdepend.  So pg_shdepend
will be inconsistent with proowner if you're a member of the owning
role but not running with SET ROLE to the owning role.

(I was led to this after noticing that the patch similarly bollixes
the grant dependencies ...)

My inclination is to think that the right behavior for REPLACE FUNCTION
is to keep the old proowner and proacl values, because that's what it
always has done and nobody's complained.  But I suppose a case could
be made that you're completely replacing the function and so you should
replace its ownership/permissions too.  The CREATE FUNCTION reference
page fails to specify either way, which is a documentation bug as well.

Comments?

Whichever way you think it should work, there's a bug here that goes
back several versions, and I rather suspect we may have the same issue
for other REPLACE-type commands ...

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] FSM search modes

[Tom Lane]

"Kevin Grittner"  writes:
> Fuzzy thinking there -- if it's the last quarter of the *free* pages,
> the suggested probabilities should be fine.  (Somehow I got to
> thinking, for a moment, that it would be the last quarter of the
> relation's overall pages)

It's going to be the latter --- we do not know, and are *not* going to
invest the cycles to find out, how many pages have a useful amount of
free space.  Even finding out the relation physical length might be
more cycles than we want to spend 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] Postgres server goes in recovery mode repeteadly

[daveg]

On Tue, Sep 29, 2009 at 09:52:06PM +0530, kunal sharma wrote:
> Hi ,
> We are using Postgres 8.4 and its been found going into recovery
> mode couple of times. The server process seems to fork another child process
> which is another postgres server running under same data directory and after
> some time it goes away while the old server is still running. There were few
> load issues on the server but the load didnt went above "32".
> 
>We are running opensuse 10.2 x86_64 with 32Gb of physical memory.
> Checking the logs I found that theres a segmentation fault ,
> 
> 
> Sep 26 05:39:54 pace kernel: postgres[28694]: segfault at 0030
> rip 0066ba8c rsp 7fffd364da30 error 4
> 
> gdb dump shows this
> 
> Reading symbols from /lib64/libdl.so.2...done.
> Loaded symbols for /lib64/libdl.so.2
> Reading symbols from /lib64/libm.so.6...done.
> Loaded symbols for /lib64/libm.so.6
> Reading symbols from /lib64/libc.so.6...done.
> Loaded symbols for /lib64/libc.so.6
> Reading symbols from /lib64/ld-linux-x86-64.so.2...done.
> Loaded symbols for /lib64/ld-linux-x86-64.so.2
> Reading symbols from /lib64/libnss_files.so.2...done.
> Loaded symbols for /lib64/libnss_files.so.2
> 0x2ad6d7b8c2b3 in __select_nocancel () from /lib64/libc.so.6
> (gdb)
> 
>   Any suggestions what is causing this segmentation fault?

I work with Kunal and have been looking into this. It appears to be the same
as the bug described in:

  http://archives.postgresql.org/pgsql-bugs/2009-09/msg00355.php

as I have localized it to a NULL pointer deference in
RelationCacheInitializePhase2() as well. Tom speculates in:

  http://archives.postgresql.org/pgsql-bugs/2009-09/msg00372.php

that large numbers of table drops might trigger this. The system in question
creates and drops temp tables at a high rate which tends to confirm this. 

-dg

-- 
David Gould   da...@sonic.net  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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] CREATE OR REPLACE FUNCTION vs ownership

[David E. Wheeler]

On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:

My inclination is to think that the right behavior for REPLACE  
FUNCTION

is to keep the old proowner and proacl values, because that's what it
always has done and nobody's complained.  But I suppose a case could
be made that you're completely replacing the function and so you  
should

replace its ownership/permissions too.  The CREATE FUNCTION reference
page fails to specify either way, which is a documentation bug as  
well.


Comments?


The latter, I think. If I replace a function, I should be the new  
owner. To me it makes no sense for someone else to own it.


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] Buffer usage in EXPLAIN and pg_stat_statements (review)

[Euler Taveira de Oliveira]

Itagaki Takahiro escreveu:
> I fixed the confusions of get, hit and read in your patch.
> 
Works for me. Will mark it ready for a committer.

PS> BTW, your patch (20091001112006.9c36.52131...@oss.ntt.co.jp) doesn't seem
to be in archives.p.o. though I've received a copy from the server.


-- 
  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] Buffer usage in EXPLAIN and pg_stat_statements (review)

[Alvaro Herrera]

Euler Taveira de Oliveira wrote:
> Itagaki Takahiro escreveu:
> > I fixed the confusions of get, hit and read in your patch.
> > 
> Works for me. Will mark it ready for a committer.
> 
> PS> BTW, your patch (20091001112006.9c36.52131...@oss.ntt.co.jp) doesn't seem
> to be in archives.p.o. though I've received a copy from the server.

That's indeed very strange -- I have it locally and I wasn't CCed, so
Majordomo must have delivered it.

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

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

Re: [HACKERS] CREATE OR REPLACE FUNCTION vs ownership

[Euler Taveira de Oliveira]

David E. Wheeler escreveu:
> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
> 
>> My inclination is to think that the right behavior for REPLACE FUNCTION
>> is to keep the old proowner and proacl values, because that's what it
>> always has done and nobody's complained.  But I suppose a case could
>> be made that you're completely replacing the function and so you should
>> replace its ownership/permissions too.  The CREATE FUNCTION reference
>> page fails to specify either way, which is a documentation bug as well.
>>
>> Comments?
> 
> The latter, I think. If I replace a function, I should be the new owner.
> To me it makes no sense for someone else to own it.
> 
Hmm... Using the same logic, if I add a new column should I be the table
owner? If you're changing the function that is because you have permission.

IMHO the owner should be preserved. In my mind, REPLACE is for changing the
content and not the properties (name, owner, etc).


-- 
  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] Buffer usage in EXPLAIN and pg_stat_statements (review)

[Alvaro Herrera]

Alvaro Herrera wrote:
> Euler Taveira de Oliveira wrote:
> > Itagaki Takahiro escreveu:
> > > I fixed the confusions of get, hit and read in your patch.
> > > 
> > Works for me. Will mark it ready for a committer.
> > 
> > PS> BTW, your patch (20091001112006.9c36.52131...@oss.ntt.co.jp) doesn't 
> > seem
> > to be in archives.p.o. though I've received a copy from the server.
> 
> That's indeed very strange -- I have it locally and I wasn't CCed, so
> Majordomo must have delivered it.

Something was wrong with last month's archive.  For some reason it had
96000 files in that directory.  I have rerun mhonarc on it and it has
normalized now (~2100 files).

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

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

Re: [HACKERS] CREATE OR REPLACE FUNCTION vs ownership

[Robert Haas]

On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
 wrote:
> David E. Wheeler escreveu:
>> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
>>
>>> My inclination is to think that the right behavior for REPLACE FUNCTION
>>> is to keep the old proowner and proacl values, because that's what it
>>> always has done and nobody's complained.  But I suppose a case could
>>> be made that you're completely replacing the function and so you should
>>> replace its ownership/permissions too.  The CREATE FUNCTION reference
>>> page fails to specify either way, which is a documentation bug as well.
>>>
>>> Comments?
>>
>> The latter, I think. If I replace a function, I should be the new owner.
>> To me it makes no sense for someone else to own it.
>>
> Hmm... Using the same logic, if I add a new column should I be the table
> owner? If you're changing the function that is because you have permission.
>
> IMHO the owner should be preserved. In my mind, REPLACE is for changing the
> content and not the properties (name, owner, etc).

I disagree.  I think David has this one right.  I expect the results
of CREATE OR REPLACE to be the same as the result of CREATE would have
been had the object not existed.

...Robert

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

Re: [HACKERS] Limit allocated memory per session

[Robert Haas]

On Thu, Oct 1, 2009 at 12:15 PM, Euler Taveira de Oliveira
 wrote:
> Robert Haas escreveu:
>> On Thu, Oct 1, 2009 at 11:47 AM, Tom Lane  wrote:
>>> Euler Taveira de Oliveira  writes:
 Tom Lane escreveu:
> daveg  writes:
>> I'd like to propose adding a new GUC to limit the amount of memory a 
>> backend
>> can allocate for its own use.
> Use ulimit.
>
 What about plataforms (Windows) that don't have ulimit?
>>> Get a real operating system ;-)
>>>
>>> Seriously, the proposed patch introduces overhead into a place that is
>>> already a known hot spot, in return for not much of anything.  It will
>>> *not* bound backend memory use very accurately, because there is no way
>>> to track raw malloc() calls.  And I think that 99% of users will not
>>> find it useful.
>>
>> What WOULD be useful is to find a way to provide a way to configure
>> work_mem per backend rather than per executor node.  But that's a much
>> harder problem.
>>
> I see. Tough problem is: how do we get per backend memory usage accurately? Is
> it relying on OS specific API the only way?

As I see it, this is really a planning problem, not an executor
problem, so measuring ACTUAL memory usage is not really important: the
problem is taking memory usage into account during planning.  The
difficulty with adjusting work_mem right now is that the correct value
depends not only on the number of queries that are concurrently
executing (which isn't a constant) but also on the number of sort/hash
operations being performed per query (which is also not a constant).
So if your queries become more complex, a value of work_mem that was
previously OK may start to cause swapping, which encourages setting
work_mem conservatively.  But setting it conservatively can cause the
planner to pick plans that save memory at a LARGE performance cost.

Fixing this isn't simple.  Right now, when planning a particular
joinrel, we only keep track of the best plans for each possible set of
path keys, regardless of how much or little memory they use.  So if we
do something naive, like just track the total amount of memory that
each candidate path is forecast to use and avoid letting it go above
some ceiling, query planning might fail altogether, because the
lower-level joinrels use as much memory as they want and the higher
level nodes, which for some reason can't be done without memory, can't
be planned.  Or we might just end up with a badly suboptimal plan,
because we pick a slightly cheaper plan lower down in the tree that
uses a LOT more memory over a slightly more expensive one that uses
much less.  Later we'll wish we hadn't, but by that point it's too
late.

Another possible angle of attack is to try to give the planner a range
for work_mem rather than a hard limit.  The planner would ordinarily
construct paths as though the lower end of the range was the limit,
but for a sufficiently large cost savings it would be willing to adopt
a path that used more memory.  Potentially this willingness could also
be conditioned on the amount of memory used by the path so far,
although that has the same problems described above in kind if not in
degree.  I'm not really sure whether something like this can be made
to work; I'm not sure there's really enough information available when
constructing paths for any sort of local decision-making to prove
fruitful.

The other idea I have is to adopt a strategy where each plan node has
upper and lower bounds on cost, as I previously suggested here with
respect to index-only scans.

http://archives.postgresql.org/pgsql-hackers/2009-09/msg01379.php

The idea would basically be to estimate the lower-bound for the cost
of a sort based on the idea that we'll have the maximum possible
amount of memory to work with (say, the budget for the whole query)
and the upper-bound cost based on the idea that we'll have the minimum
possible amount of memory (zero, or whatever the minimal amount is).
We can also estimate the most memory we think we can usefully use (for
example, a hash join with a smaller inner rel doesn't benefit from
more memory than the amount required to hold the entire hash table in
memory).

After we complete the first round of planning, we look at the
resulting paths and decide which sorts or hashes will get funded with
how much memory.  I'm hand-waving a little bit here, because there may
be a knapsack problem in here (which is NP-complete), since the cost
as a function of memory probably has sharp cliffs with not much change
in between them - certainly for hashing, and I suspect for sorting as
well, but it might be that in practice N is small enough not to
matter, or we might be able to find an approximation that is good
enough that we can live with it.  Even if we can get past that hurdle,
though, there's still all the caveats from the original email,
principally that it's unclear that the necessary computations can be
done without blowing planning time out of the water.  Plus, if we used
this strate

Re: [HACKERS] [PATCH] DefaultACLs

[Robert Haas]

On Thu, Oct 1, 2009 at 1:37 PM, Tom Lane  wrote:
> Petr Jelinek  writes:
>> because it seems like merging privileges seems to be acceptable for most
>> (although I am not sure I like it, but I don't have better solution for
>> managing conflicts), I changed the patch to do just that.
>
> It's not clear to me whether we have consensus on this approach.
> Last chance for objections, anyone?
>
> The main argument I can see against doing it this way is that it doesn't
> provide a means for overriding the hard-wired public grants for object
> types that have such (principally functions).  I think that a reasonable
> way to address that issue would be for a follow-on patch that allows
> changing the hard-wired default privileges for object types.  It might
> well be that no one cares enough for it to matter, though.  I think that
> in most simple cases what's needed is a way to add privileges, not
> subtract them --- and we're already agreed that this mechanism is only
> meant to simplify simple cases.

I'm going to reiterate what I suggested upthread...  let's let the
default, global default ACL contain the hard-wired privileges, instead
of making them hardwired.  Then your objects will get those privileges
not because they are hard-wired, but because you haven't changed your
global default ACL to not contain them.

...Robert

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

Re: [HACKERS] CREATE OR REPLACE FUNCTION vs ownership

[KaiGai Kohei]

Robert Haas wrote:
> On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
>  wrote:
>> David E. Wheeler escreveu:
>>> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
>>>
 My inclination is to think that the right behavior for REPLACE FUNCTION
 is to keep the old proowner and proacl values, because that's what it
 always has done and nobody's complained.  But I suppose a case could
 be made that you're completely replacing the function and so you should
 replace its ownership/permissions too.  The CREATE FUNCTION reference
 page fails to specify either way, which is a documentation bug as well.

 Comments?
>>> The latter, I think. If I replace a function, I should be the new owner.
>>> To me it makes no sense for someone else to own it.
>>>
>> Hmm... Using the same logic, if I add a new column should I be the table
>> owner? If you're changing the function that is because you have permission.
>>
>> IMHO the owner should be preserved. In my mind, REPLACE is for changing the
>> content and not the properties (name, owner, etc).

If so, it seems to me CREATE OR REPLACE is equivalent to ALTER FUNCTION
with currently unsupported option. In this case, it is not necessary to
check CREATE privilege on the namespace because it does not affect to
its name/schema.

> I disagree.  I think David has this one right.  I expect the results
> of CREATE OR REPLACE to be the same as the result of CREATE would have
> been had the object not existed.

If so, it seems to me CREATE OR REPLACE is equivalent to a pair of
actions: 1) DROP FUNCTION (if exist) and 2) CREATE FUNCTION.

I think the current implementation intend the later perspective.
(It also checks ownership of the older definition, only if exists.)

However, I'm not sure which is the better way, currently.

Thanks,
-- 
OSS Platform Development Division, NEC
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] CREATE OR REPLACE FUNCTION vs ownership

[Robert Haas]

2009/10/1 KaiGai Kohei :
> Robert Haas wrote:
>> On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
>>  wrote:
>>> David E. Wheeler escreveu:
 On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:

> My inclination is to think that the right behavior for REPLACE FUNCTION
> is to keep the old proowner and proacl values, because that's what it
> always has done and nobody's complained.  But I suppose a case could
> be made that you're completely replacing the function and so you should
> replace its ownership/permissions too.  The CREATE FUNCTION reference
> page fails to specify either way, which is a documentation bug as well.
>
> Comments?
 The latter, I think. If I replace a function, I should be the new owner.
 To me it makes no sense for someone else to own it.

>>> Hmm... Using the same logic, if I add a new column should I be the table
>>> owner? If you're changing the function that is because you have permission.
>>>
>>> IMHO the owner should be preserved. In my mind, REPLACE is for changing the
>>> content and not the properties (name, owner, etc).
>
> If so, it seems to me CREATE OR REPLACE is equivalent to ALTER FUNCTION
> with currently unsupported option. In this case, it is not necessary to
> check CREATE privilege on the namespace because it does not affect to
> its name/schema.

Right - so the subtle point here is that ALTER means something
different from CREATE OR REPLACE.  "ALTER" means to make a
modification to something; to change it; to adjust one particular
property of the object without disturbing the others.  On the other
hand, "REPLACE" means to get rid of something and replace it with an
entirely new thing.  I think that is exactly why we have ALTER TABLE
but CREATE OR REPLACE FUNCTION.

Now, if we want to have an ALTER FUNCTION that replaces the function
definition and leaves the owner intact - fine!  But that is not what
REPLACE means.

>> I disagree.  I think David has this one right.  I expect the results
>> of CREATE OR REPLACE to be the same as the result of CREATE would have
>> been had the object not existed.
>
> If so, it seems to me CREATE OR REPLACE is equivalent to a pair of
> actions: 1) DROP FUNCTION (if exist) and 2) CREATE FUNCTION.

Except that you don't have to drop and recreate the dependencies, if any.

...Robert

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

Re: [HACKERS] CREATE OR REPLACE FUNCTION vs ownership

[KaiGai Kohei]

Robert Haas wrote:
> 2009/10/1 KaiGai Kohei :
>> Robert Haas wrote:
>>> On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
>>>  wrote:
 David E. Wheeler escreveu:
> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
>
>> My inclination is to think that the right behavior for REPLACE FUNCTION
>> is to keep the old proowner and proacl values, because that's what it
>> always has done and nobody's complained.  But I suppose a case could
>> be made that you're completely replacing the function and so you should
>> replace its ownership/permissions too.  The CREATE FUNCTION reference
>> page fails to specify either way, which is a documentation bug as well.
>>
>> Comments?
> The latter, I think. If I replace a function, I should be the new owner.
> To me it makes no sense for someone else to own it.
>
 Hmm... Using the same logic, if I add a new column should I be the table
 owner? If you're changing the function that is because you have permission.

 IMHO the owner should be preserved. In my mind, REPLACE is for changing the
 content and not the properties (name, owner, etc).
>> If so, it seems to me CREATE OR REPLACE is equivalent to ALTER FUNCTION
>> with currently unsupported option. In this case, it is not necessary to
>> check CREATE privilege on the namespace because it does not affect to
>> its name/schema.
> 
> Right - so the subtle point here is that ALTER means something
> different from CREATE OR REPLACE.  "ALTER" means to make a
> modification to something; to change it; to adjust one particular
> property of the object without disturbing the others.  On the other
> hand, "REPLACE" means to get rid of something and replace it with an
> entirely new thing.  I think that is exactly why we have ALTER TABLE
> but CREATE OR REPLACE FUNCTION.
> 
> Now, if we want to have an ALTER FUNCTION that replaces the function
> definition and leaves the owner intact - fine!  But that is not what
> REPLACE means.
> 
>>> I disagree.  I think David has this one right.  I expect the results
>>> of CREATE OR REPLACE to be the same as the result of CREATE would have
>>> been had the object not existed.
>> If so, it seems to me CREATE OR REPLACE is equivalent to a pair of
>> actions: 1) DROP FUNCTION (if exist) and 2) CREATE FUNCTION.
> 
> Except that you don't have to drop and recreate the dependencies, if any.

Indeed, but here is one other issue from the perspective of security.

For example, a superuser can define a new type which has input/output
handler using user defined functions. Its ownership is not limited to
superuser, so it means non-privilege user can replace the type handler
owned by himself later.

If we also rebuild dependencies on the CREATE OR REPLACE FUNCTION,
we can prevent other user implicitly invokes replaced malicaious
function (it may perform as a trojan-horse), because dependency
mechanism abort this peudo DROP FUNCTION.

However, similar issue can be happen on ALTER FUNCTION OWNER TO.
IMO, we need a mechanism to prevent ALTER or REPLACE functions
which are used for other stuff without permission checks to
execute it.

Thanks,
-- 
OSS Platform Development Division, NEC
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] CREATE OR REPLACE FUNCTION vs ownership

[Robert Haas]

2009/10/1 KaiGai Kohei :
> Robert Haas wrote:
>> 2009/10/1 KaiGai Kohei :
>>> Robert Haas wrote:
 On Thu, Oct 1, 2009 at 8:52 PM, Euler Taveira de Oliveira
  wrote:
> David E. Wheeler escreveu:
>> On Oct 1, 2009, at 3:42 PM, Tom Lane wrote:
>>
>>> My inclination is to think that the right behavior for REPLACE FUNCTION
>>> is to keep the old proowner and proacl values, because that's what it
>>> always has done and nobody's complained.  But I suppose a case could
>>> be made that you're completely replacing the function and so you should
>>> replace its ownership/permissions too.  The CREATE FUNCTION reference
>>> page fails to specify either way, which is a documentation bug as well.
>>>
>>> Comments?
>> The latter, I think. If I replace a function, I should be the new owner.
>> To me it makes no sense for someone else to own it.
>>
> Hmm... Using the same logic, if I add a new column should I be the table
> owner? If you're changing the function that is because you have 
> permission.
>
> IMHO the owner should be preserved. In my mind, REPLACE is for changing 
> the
> content and not the properties (name, owner, etc).
>>> If so, it seems to me CREATE OR REPLACE is equivalent to ALTER FUNCTION
>>> with currently unsupported option. In this case, it is not necessary to
>>> check CREATE privilege on the namespace because it does not affect to
>>> its name/schema.
>>
>> Right - so the subtle point here is that ALTER means something
>> different from CREATE OR REPLACE.  "ALTER" means to make a
>> modification to something; to change it; to adjust one particular
>> property of the object without disturbing the others.  On the other
>> hand, "REPLACE" means to get rid of something and replace it with an
>> entirely new thing.  I think that is exactly why we have ALTER TABLE
>> but CREATE OR REPLACE FUNCTION.
>>
>> Now, if we want to have an ALTER FUNCTION that replaces the function
>> definition and leaves the owner intact - fine!  But that is not what
>> REPLACE means.
>>
 I disagree.  I think David has this one right.  I expect the results
 of CREATE OR REPLACE to be the same as the result of CREATE would have
 been had the object not existed.
>>> If so, it seems to me CREATE OR REPLACE is equivalent to a pair of
>>> actions: 1) DROP FUNCTION (if exist) and 2) CREATE FUNCTION.
>>
>> Except that you don't have to drop and recreate the dependencies, if any.
>
> Indeed, but here is one other issue from the perspective of security.
>
> For example, a superuser can define a new type which has input/output
> handler using user defined functions. Its ownership is not limited to
> superuser, so it means non-privilege user can replace the type handler
> owned by himself later.
>
> If we also rebuild dependencies on the CREATE OR REPLACE FUNCTION,
> we can prevent other user implicitly invokes replaced malicaious
> function (it may perform as a trojan-horse), because dependency
> mechanism abort this peudo DROP FUNCTION.
>
> However, similar issue can be happen on ALTER FUNCTION OWNER TO.
> IMO, we need a mechanism to prevent ALTER or REPLACE functions
> which are used for other stuff without permission checks to
> execute it.

Good point.

...Robert

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

Re: [HACKERS] Using results from INSERT ... RETURNING

[Robert Haas]

On Mon, Sep 28, 2009 at 3:19 PM, Marko Tiikkaja
 wrote:
> Robert Haas wrote:
>>
>> Can you at least take a stab at it?  We can fix your grammar, but
>> guessing what's going on without documentation is hard.
>
> With some help from David Fetter, I took another try at it.  I hope
> someone finds this helpful.  I'm happy to answer any questions.

Thanks.  I read through this patch some more tonight and I guess I am
a bit confused about what it accomplishes.  AIUI, the point here is to
lay the groundwork for a future patch to allow writeable CTEs, and I
guess I'm not understanding how it's going to do that.

rhaas=# create table project (id serial primary key, name varchar not
null);NOTICE:  CREATE TABLE will create implicit sequence
"project_id_seq" for serial column "project.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"project_pkey" for table "project"
CREATE TABLE
rhaas=# create table shadow (id integer not null primary key, name
varchar not null);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"shadow_pkey" for table "shadow"
CREATE TABLE
rhaas=# create rule clone as on insert to project do also insert into
shadow (id, name) values (NEW.id, NEW.name);
CREATE RULE
rhaas=# insert into project (name) values ('Writeable CTEs') returning id;
 id

  1
(1 row)

INSERT 0 1
rhaas=# explain insert into project (name) values ('Writeable CTEs')
returning id;
   QUERY PLAN

 Insert  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)

 Insert  (cost=0.00..0.01 rows=1 width=0)
   ->  Result  (cost=0.00..0.01 rows=1 width=0)
(5 rows)

Now the point here is that I eventually want to be able to write
something like this:

with foo as (insert into project (name) values ('Writeable CTEs')
returning id) select * from foo;

...but how does this get me any closer?  It seems to me that the plan
for THAT statement has to be a CTE scan over top of BOTH of the
inserts, but here I have two insert nodes that comprise two separate
plans.  The DML node, as presently implemented, supports a list of
plans, but they all have to be of the same type, so it's really only
useful for handling append, and as previously discussed, it's not
clear that the proposed handling is any better than what we already
have.

What am I missing?

...Robert

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

Re: [HACKERS] Using results from INSERT ... RETURNING

[David Fetter]

On Thu, Oct 01, 2009 at 10:48:41PM -0400, Robert Haas wrote:
> On Mon, Sep 28, 2009 at 3:19 PM, Marko Tiikkaja
>  wrote:
> > Robert Haas wrote:
> >>
> >> Can you at least take a stab at it?  We can fix your grammar, but
> >> guessing what's going on without documentation is hard.
> >
> > With some help from David Fetter, I took another try at it.  I
> > hope someone finds this helpful.  I'm happy to answer any
> > questions.
> 
> Thanks.  I read through this patch some more tonight and I guess I
> am a bit confused about what it accomplishes.  AIUI, the point here
> is to lay the groundwork for a future patch to allow writeable CTEs,
> and I guess I'm not understanding how it's going to do that.

There's another branch in the repository
 called
actually_write which has the beginnings of an implementation based on
this.  If you'd like, I can send along either the patch vs.
writeable_cte, or a patch against the main branch.

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

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

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

Re: [HACKERS] hstore crasesh on 64bit Sparc

[Zdenek Kotala]

Tom Lane píše v čt 01. 10. 2009 v 12:28 -0400:
> Zdenek Kotala  writes:
> > I'm looking why cometh_month fails and it is problem with last hstore 
> > putback:
> 
> I think this is the same 64-bit problem we fixed last night --- wait for
> the next rebuild before worrying.

Correct, last build seems OK. There only remains issue with plpython
UTF8 test which need some investigation.

thanks Zdenek


-- 
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] FSM search modes

[Simon Riggs]

On Thu, 2009-10-01 at 17:08 -0400, Tom Lane wrote:
> The discussion at the moment is about ways
> of reducing the probability of getting into that situation in the
> first place.  

Definitely. 

> That doesn't preclude also providing some more-invasive tools that
> people can use when they do get into that situation

Yes, this is a separate thread specifically so we can get both; the
VACUUM FULL replacement discussion had/was progressing well so ought not
need to bring it up again here.

-- 
 Simon Riggs   www.2ndQuadrant.com


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