Re: [HACKERS] Application name patch - v2

2009-10-19 Thread Magnus Hagander
2009/10/20 Bruce Momjian :
> Robert Haas wrote:
>> > I do agree with Peter's concerns about limiting the character set of the
>> > name string, and maybe there should be some sort of length limit too.
>>
>> I don't have a strong feeling about this.  If limiting this to 7-bit
>> characters solves some nasty encoding problems or something, then
>> fine, but otherwise I think we can just escape what we emit into the
>> log and say that users who log this information should have a
>> sufficiently sophisticated log parser to cope with it.
>
> Once problem I can imagine is someone with a long log_line prefix, like
> '%t %a|', and assuming that the pipe is the end of the log_prefix
> arguments.  If someone adds a pipe to the application name, log parsing
> code will assume the %a pipe ends the log_line_prefix, and we have no
> system of escaping things like pipes in log_line_prefix.
>
> Effectively, if you use %a, there is no good way to terminate
> log_line_prefix with a known unique character.

If you're going to parse your logfile, you should probably be using
CSV format logs, which I believe would not have this issue...

-- 
 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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Bruce Momjian
Bruce Momjian wrote:
> > 1. Invent a GUC that has the settings backwards-compatible,
> > oracle-compatible, throw-error (exact spellings TBD).  Factory default,
> > at least for a few releases, will be throw-error.  Make it SUSET so that
> > unprivileged users can't break things by twiddling it; but it's still
> > possible for the DBA to set it per-database or per-user.
> > 
> > 2. Also invent a #option syntax that allows the GUC to be overridden
> > per-function.  (Since the main GUC is SUSET, we can't just use a
> > per-function SET to override it.  There are other ways we could do this
> > but none seem less ugly than #option...)
> 
> I don't see the logic to making the setting SUSET.  The user wrote the
> function;  what logic is there to say the resolution rules are not under
> their control?
> 
> Also, I think to GUC that throws an error or not is a lot safer than one
> that changes resolution semantics.  Changing resolution semantics sounds
> like the autocommit GUC to me.  :-O
> 
> Also, I am not really keen on the "keep it for a few releases" --- we
> often don't come back to finally change it, so maybe just error/no error
> and using Oracle semantics is the way to go, with 'error' as the
> default.  Our change in casting for 8.3 seemed much more major than
> this.

Oh, two more things.  First, with the Oracle resolution rules, I think
it is possible to change the behavior of a function by adding or
renaming a column that wasn't referenced in the function because a
new/renamed column might mask a function variable --- that is not nice.

Second, I can see the value of having the GUC be SUSET if changing the
setting could possible break the function or cause insecure behavior,
but I wasn't clear that was possible.

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

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

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


Re: [HACKERS] UTF8 with BOM support in psql

2009-10-19 Thread Itagaki Takahiro

Bruce Momjian  wrote:

> Itagaki Takahiro wrote:
> > When psql opens a file with -f or \i, it checks first 3 bytes of the
> > file. If they are BOM, discard the 3 bytes and change client encoding
> > to UTF8 automatically.
> 
> Seems there is community support for accepting BOM:
>   http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php

Thank yor for information.
I read the thread that we discussed about BOM handling in *data types*.
I agree the decision in the thead that we should not skip BOM characters,
but we can handle BOM in a different way in the head of *files* for psql
and COPY input.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center



-- 
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] UTF8 with BOM support in psql

2009-10-19 Thread Bruce Momjian
Itagaki Takahiro wrote:
> UTF8 encoding text files with BOM (Byte Order Mark) are commonly
> used in Windows, though BOM was designed for UTF16 text originally.
> However, psql cannot read such format even if we set client encoding
> to UTF8. Is it worth supporting those format in psql?
> 
> When psql opens a file with -f or \i, it checks first 3 bytes of the
> file. If they are BOM, discard the 3 bytes and change client encoding
> to UTF8 automatically.
> 
> Is this change reasonable? Comments welcome.

Seems there is community support for accepting BOM:

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

Should I add this as a TODO item?

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

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

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


Re: [HACKERS] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Peter Eisentraut
On Mon, 2009-10-19 at 14:08 -0700, Ron Mayer wrote:
> Tom Lane wrote:
> > What are the probabilities that the OpenACSes of the world will just
> > set the value to "backward compatible" instead of touching their code?
> 
> Would postgres get considerably cleaner if a hypothetical 9.0 release
> skipped backward compatibility and removed anything that's only
> maintained for historical reasons?

Probably not.  Most of the examples you cite of documented deprecated or
historical behavior would be one-line changes to get rid of.



-- 
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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Bruce Momjian
Tom Lane wrote:
> Andrew Dunstan  writes:
> > Tom Lane wrote:
> >> (a) Nobody but me is afraid of the consequences of treating this as
> >> a GUC.  (I still think you're all wrong, but so be it.)
> 
> > I can't say I'm happy about it. For one thing, the granularity seems all 
> > wrong.  I'd rather be able to keep backwards compatibility on a function 
> > by function basis. Or would the value of the GUC at the time the 
> > function was created stick?
> 
> Again, I can't see making a GUC that works fundamentally differently
> from the rest of them.
> 
> Given this round of feedback, I make the following proposal:
> 
> 1. Invent a GUC that has the settings backwards-compatible,
> oracle-compatible, throw-error (exact spellings TBD).  Factory default,
> at least for a few releases, will be throw-error.  Make it SUSET so that
> unprivileged users can't break things by twiddling it; but it's still
> possible for the DBA to set it per-database or per-user.
> 
> 2. Also invent a #option syntax that allows the GUC to be overridden
> per-function.  (Since the main GUC is SUSET, we can't just use a
> per-function SET to override it.  There are other ways we could do this
> but none seem less ugly than #option...)

I don't see the logic to making the setting SUSET.  The user wrote the
function;  what logic is there to say the resolution rules are not under
their control?

Also, I think to GUC that throws an error or not is a lot safer than one
that changes resolution semantics.  Changing resolution semantics sounds
like the autocommit GUC to me.  :-O

Also, I am not really keen on the "keep it for a few releases" --- we
often don't come back to finally change it, so maybe just error/no error
and using Oracle semantics is the way to go, with 'error' as the
default.  Our change in casting for 8.3 seemed much more major than
this.

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

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

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


[HACKERS] UTF8 with BOM support in psql

2009-10-19 Thread Itagaki Takahiro
UTF8 encoding text files with BOM (Byte Order Mark) are commonly
used in Windows, though BOM was designed for UTF16 text originally.
However, psql cannot read such format even if we set client encoding
to UTF8. Is it worth supporting those format in psql?

When psql opens a file with -f or \i, it checks first 3 bytes of the
file. If they are BOM, discard the 3 bytes and change client encoding
to UTF8 automatically.

Is this change reasonable? Comments welcome.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center


-- 
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] Application name patch - v2

2009-10-19 Thread Bruce Momjian
Robert Haas wrote:
> > I do agree with Peter's concerns about limiting the character set of the
> > name string, and maybe there should be some sort of length limit too.
> 
> I don't have a strong feeling about this.  If limiting this to 7-bit
> characters solves some nasty encoding problems or something, then
> fine, but otherwise I think we can just escape what we emit into the
> log and say that users who log this information should have a
> sufficiently sophisticated log parser to cope with it.

Once problem I can imagine is someone with a long log_line prefix, like
'%t %a|', and assuming that the pipe is the end of the log_prefix
arguments.  If someone adds a pipe to the application name, log parsing
code will assume the %a pipe ends the log_line_prefix, and we have no
system of escaping things like pipes in log_line_prefix.

Effectively, if you use %a, there is no good way to terminate
log_line_prefix with a known unique character.

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

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

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


Re: [HACKERS] per table random-page-cost?

2009-10-19 Thread Greg Smith

On Mon, 19 Oct 2009, Jeff Davis wrote:


On Mon, 2009-10-19 at 21:22 -0500, Kevin Grittner wrote:

I'd bet accounts receivable applications often hit that.
(Most payments on recent billings; a sprinkling on older ones.)
I'm sure there are others.


You worded the examples in terms of writes (I think), and we're talking
about read caching, so I still don't entirely understand.


No, that part was fair.  The unfortunate reality of accounts receivable is 
that reports run to list people who owe one money happen much more often 
than posting payments into the system does.



Also, the example sounds like you'd like to optimize across queries.
There's no mechanism for the planner to remember some query executed a
while ago, and match it up to some new query that it's trying to plan.


Some of the use-cases here involve situations where you know most of a 
relation is likely to be in cache just because there's not much going on 
that might evict it.  In any case, something that attempts to model some 
average percentage you can expect a relation to be in cache is in effect 
serving as a memory of past queries.



I'm not clear on the scenario that we're trying to improve.


Duh, that would be the situation where someone wants optimizer hints but 
can't call them that because then the idea would be reflexively rejected!


Looks like I should dust off the much more complicated proposal for 
tracking and using in-cache hit percentages I keep not having time to 
finish writing up.  Allowing a user-set value for that is a lot more 
reasonable if the system computes a reasonable one itself under normal 
circumstances.  That's what I think people really want, even if it's not 
what they're asking for.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
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] Rejecting weak passwords

2009-10-19 Thread Bruce Momjian
Tom Lane wrote:
> "Albe Laurenz"  writes:
> > Bruce Momjian wrote:
> >> Password checks might include password complexity or non-reuse of
> >> passwords. This facility will require the client to send the password to
> >> the server in plain-text, so SSL and 'password' authentication is
> >> necessary to use this features.
> 
> > So in my opinion that should be:
> > This facility will require to send new and changed password to
> > the server in plain-text, so it will require SSL, and the use
> > of encrypted passwords in CREATE/ALTER ROLE will have to be
> > disabled.
> 
> Actually, not one word of *either* version should be in TODO.  All of
> that is speculation about policies that a particular add-on module
> might or might not choose to enforce.

Agreed, updated:

|Allow server-side enforcement of password policies
|Password checks might include password complexity or non-reuse of
passwords.  This facility will require the client to send password
creation/changes to the server in plain-text, not MD5.

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

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

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


Re: [HACKERS] per table random-page-cost?

2009-10-19 Thread Jeff Davis
On Mon, 2009-10-19 at 21:22 -0500, Kevin Grittner wrote:
> I'd bet accounts receivable applications often hit that.
> (Most payments on recent billings; a sprinkling on older ones.)
> I'm sure there are others.

You worded the examples in terms of writes (I think), and we're talking
about read caching, so I still don't entirely understand.

Also, the example sounds like you'd like to optimize across queries.
There's no mechanism for the planner to remember some query executed a
while ago, and match it up to some new query that it's trying to plan.
Maybe there should be, but that's an entirely different feature. 

I'm not clear on the scenario that we're trying to improve.

Regards,
Jeff Davis


-- 
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] per table random-page-cost?

2009-10-19 Thread Kevin Grittner
Jeff Davis  wrote:
  
> what kind of scenario
> would involve a stable 90% cache hit ratio for a table?
 
I'd bet accounts receivable applications often hit that.
(Most payments on recent billings; a sprinkling on older ones.)
I'm sure there are others.
 
-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] per table random-page-cost?

2009-10-19 Thread Jeff Janes
On Mon, Oct 19, 2009 at 4:29 PM, Greg Stark  wrote:
> On Mon, Oct 19, 2009 at 4:21 PM, Tom Lane  wrote:
>> marcin mank  writes:
 This proposal is just "hints by the back door", ISTM. As Tom says, there is
 a justification for having it on tablespaces but not on individual tables.
>>
>>> If the parameter is defined as "the chance that a page is in cache"
>>> there is very real physical meaning to it.
>>
>> We have no such parameter...
>
>
> And we want our parameters to be things the DBA has a chance of being
> able to estimate.

Do the current parameters meet that standard?  When setting
seq_page_cost now, don't people have a lot of "Well, we're about this
likely to find it in the cache anyway" built into their settings?


Jeff

-- 
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] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Tom Lane
"Marc G. Fournier"  writes:
> Just curious, but with that thought in mind, are we doing any code 
> cleanups as far as EOL releases?  Ie. is there any code in our tree right 
> now that is for 'backward compatibility' for 7.3.x versions that could be 
> cleaned out?

Well, we were just trying to pull out add_missing_from, and look how far
that's gotten ...

Something that has been suggested is to rip out pg_dump's support for
pre-7.3 servers, which would probably be easier to get through because
it wouldn't create any user-visible feature loss.  It wouldn't be a
monstrous savings but it'd certainly simplify matters somewhat.

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] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Marc G. Fournier

On Mon, 19 Oct 2009, Tom Lane wrote:


Ron Mayer  writes:

Would postgres get considerably cleaner if a hypothetical 9.0 release
skipped backward compatibility and removed anything that's only
maintained for historical reasons?


Yeah, and our user community would get a lot smaller too :-(

Actually, I think any attempt to do that would result in a fork,
and a consequent splintering of the community.  We can get away
with occasionally cleaning up individual problematic behaviors
(example: implicit casts to text), but any sort of all-at-once
breakage would result in a lot of people Just Saying No.


Just curious, but with that thought in mind, are we doing any code 
cleanups as far as EOL releases?  Ie. is there any code in our tree right 
now that is for 'backward compatibility' for 7.3.x versions that could be 
cleaned out?


I realize that this might not make a huge difference, but it would be 
easier to do a 'gradual clean up', then an 'all-at-once' scenario, no?





Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org

Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org

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


[HACKERS] Add a pgstat config column to pg_database, so this, entire thing can be enabled/disabled on a per db basis

2009-10-19 Thread Euler Taveira de Oliveira
Hi,

Looking at the code, it seems the $SUBJECT (comment) is obsolete. Indeed, we
can set it with 'ALTER DATABASE foo SET track_*'.

One thing that bothers me is the fact that if i turn it off, do a lot of stuff
and then turn it on my counters will be wrong. :( Maybe should we call
pgstat_reset_counters() in a new-function (assign_track_counts(?) that will be
in assign_hook for guc 'track_counts')?


-- 
  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


[HACKERS] SE-PgSQL developer documentation (Re: Reworks for Access Control facilities (r2363))

2009-10-19 Thread KaiGai Kohei
After the long trial-and-errors, we learned a few approaches which
use common entry points for both of DAC and MAC were rocky-path more
than what we initially imagined.
So, we came back to the original design. It deploys MAC hooks on
the strategic points of core routines. On the other hand, people
complained about this approach without clear documentation, because
most of people are not familiar to both of SELinux and PgSQL.
Heikki suggested that a clear developer documentation should be
provided to understand pgsql-hackers this new concept.
(And, Peter has also suggested before a developer documentation will
be a good source of user documentations.)

I plan to submit SE-PgSQL/lite patch with developer documentations
on the next commit-fest.
I can understand what I want to develop and the purpose of codes.
However, it may not match with what you want to know.

So, I'd like to ask what should be included within the developer
documentation at first prior to making a documentation.

I plans the developer documentation should be put as a REAME file,
not a SGML documentation or a certain wiki page.
And I think it should contain the following items.

* overview
 - general overview of SE-PgSQL
 - introduction of SELinux specific terms (such as "security context")

* internal architecture
 - the purpose of sub-components (such as management of security
   context, caches of access control decision and so on)
 - differences from similar permissions in DAC

* object classes and permissions defined in SELinux model
 - list of them and when/where they should be checked.

* specification of SE-PgSQL hooks
  (It should be put on the source code comments for easy maintenance.)
 - what this hook does, what arguments are required, what result will
   be returned.

* code examples
 - a few examples to add MAC checks within 3rd party modules.

Do you have any comments? What should be added to? or removed from?

Thanks,

KaiGai Kohei wrote:
> Heikki Linnakangas wrote:
>> KaiGai Kohei wrote:
>>> When we create a new object, we can provide an explicit security context
>>> to be assigned on the new object, instead of the default one.
>> To get started, do we really need that feature? It would make for a
>> significantly smaller patch if there was no explicit security labels on
>> objects.
> 
> The importance of the feature is relatively minor than MAC itself.
> So, I can agree to omit code corresponding to statement support
> from the first patch. (IIRC, about 300-400 lines can be reduced.)
> But it will be necessary feature at the next step, because DBA cannot
> create a special purpose table without statement support.
> 
> For example, if security policy allows DBA to create read-writable
> table (in default) and read-only table. He cannot set up read-only
> table without explicit security label support.
> 
> On the other hand, the default PG model allows to bypass checks on
> certain objects. For example, column-level privileges are only checked
> when a user does not have enough permissions on the target table.
> If "SELECT a,b FROM t" is given, pg_attribute_aclcheck() may not invoked
> when user has needed privileges on the table t.
 Hmm, I see. Yes, it does seem like we'd need to change such permission
 checks to accommodate both models.
>>> I'm not clear why we need to rework the permission checks here.
>>> DAC and MAC perform orthogonally and independently.
>>> DAC allows to override column-level privileges by table-level privileges
>>> according to the default PG's model. It seems to me fine.
>>> On the other hand, MAC checks both of permissions. It is also fine.
>> I meant we need to refactor the code doing the permission checks. The
>> existing checks are doing the right thing for DAC, but as you point out,
>> if the MAC checks are within pg_*_aclcheck() functions,
>> pg_attribute_aclcheck() needs to be called even if you have privilege on
>> the table.
> 
> I think we already learned refactoring DAC checks need widespread code
> changes and pushes a burden to reviewers.
> 
> In this case, I think the point just after invocation of ExecCheckRTEPerms()
> in ExecCheckRTPerms() is the best point to put SE-PgSQL's checks.
> Needless to say, its specification should be clearly documented.
> 
> 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] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Aidan Van Dyk
* Tom Lane  [091019 18:45]:
> Ron Mayer  writes:
> > Would postgres get considerably cleaner if a hypothetical 9.0 release
> > skipped backward compatibility and removed anything that's only
> > maintained for historical reasons?
> 
> Yeah, and our user community would get a lot smaller too :-(
> 
> Actually, I think any attempt to do that would result in a fork,
> and a consequent splintering of the community.  We can get away
> with occasionally cleaning up individual problematic behaviors
> (example: implicit casts to text), but any sort of all-at-once
> breakage would result in a lot of people Just Saying No.

I don't know... What if this hypothetical "baggage-free" version came
with configurable syncrhonous master-slave replication, writable CTEs,
and everything ;-)

Couple it with a libpq/protocol increase that allows fixing of the
various warts of the current connection (like encoding, etc), and you
still have a *very* attractive platform...

And then just do the rename official to Postgres, and people can support
both PostgreSQL, warts and all, or Postgres, the super-duper
database-to-rule-them-all...

;-)

/me crawls back into his hole

a.

-- 
Aidan Van Dyk Create like a god,
ai...@highrise.ca   command like a king,
http://www.highrise.ca/   work like a slave.


signature.asc
Description: Digital signature


Re: [HACKERS] per table random-page-cost?

2009-10-19 Thread Jeff Davis
On Mon, 2009-10-19 at 16:39 -0700, Greg Stark wrote:
> But the long-term strategy here I think is to actually have some way
> to measure the real cache hit rate on a per-table basis. Whether it's
> by timing i/o operations, programmatic access to dtrace, or some other
> kind of os interface, if we could know the real cache hit rate it
> would be very helpful.

Maybe it would be simpler to just get the high-order bit: is this table
likely to be completely in cache (shared buffers or os buffer cache), or
not?

The lower cache hit ratios are uninteresting: the performance difference
between 1% and 50% is only a factor of two. The higher cache hit ratios
that are lower than "almost 100%" seem unlikely: what kind of scenario
would involve a stable 90% cache hit ratio for a table?

Regards,
Jeff Davis


-- 
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] per table random-page-cost?

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 5:54 PM, Kevin Grittner
 wrote:
> Robert Haas  wrote:
>
>> I've been wondering if it might make sense to have a
>> "random_page_cost" and "seq_page_cost" setting for each TABLESPACE,
>> to compensate for the fact that different media might be faster or
>> slower, and a percent-cached setting for each table over top of
>> that.
>
> [after recovering from the initial cringing reaction...]
>
> How about calculating an effective percentage based on other
> information.  effective_cache_size, along with relation and database
> size, come to mind.  How about the particular index being considered
> for the plan?  Of course, you might have to be careful about working
> in TOAST table size for a particular query, based on the columns
> retrieved.

I think that a per-tablespace page cost should be set by the DBA, same
as we do with global page-costs now.

OTOH, I think that a per-relation percent-in-cache should be
automatically calculated by the database (somehow) and the DBA should
have an option to override in case the database does the wrong thing.
I gave a lightning talk on this topic at PGcon.

> I have no doubt that there would be some major performance regressions
> in the first cut of anything like this, for at least *some* queries.
> The toughest part of this might be to get adequate testing to tune it
> for a wide enough variety of real-life situations.

Agreed.

...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] per table random-page-cost?

2009-10-19 Thread Greg Stark
On Mon, Oct 19, 2009 at 2:54 PM, Kevin Grittner
 wrote:
> How about calculating an effective percentage based on other
> information.  effective_cache_size, along with relation and database
> size, come to mind.

I think previous proposals for this have fallen down when you actually
try to work out a formula for this. The problem is that you could have
a table which is much smaller than effective_cache_size but is never
in cache due to it being one of many such tables.

I think it would still be good to have some naive kind of heuristic
here as long as it's fairly predictable for DBAs.

But the long-term strategy here I think is to actually have some way
to measure the real cache hit rate on a per-table basis. Whether it's
by timing i/o operations, programmatic access to dtrace, or some other
kind of os interface, if we could know the real cache hit rate it
would be very helpful.

Perhaps we could extrapolate from the shared buffer cache percentage.
If there's a moderately high percentage in shared buffers then it
seems like a reasonable supposition to assume the filesystem cache
would have a similar distribution.

-- 
greg

-- 
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] per table random-page-cost?

2009-10-19 Thread Greg Stark
On Mon, Oct 19, 2009 at 4:21 PM, Tom Lane  wrote:
> marcin mank  writes:
>>> This proposal is just "hints by the back door", ISTM. As Tom says, there is
>>> a justification for having it on tablespaces but not on individual tables.
>
>> If the parameter is defined as "the chance that a page is in cache"
>> there is very real physical meaning to it.
>
> We have no such parameter...


And we want our parameters to be things the DBA has a chance of being
able to estimate. How would you come up with sensible figures for this
hypothetical parameter?


-- 
greg

-- 
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] per table random-page-cost?

2009-10-19 Thread Tom Lane
marcin mank  writes:
>> This proposal is just "hints by the back door", ISTM. As Tom says, there is
>> a justification for having it on tablespaces but not on individual tables.

> If the parameter is defined as "the chance that a page is in cache"
> there is very real physical meaning to it.

We have no such parameter...

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] per table random-page-cost?

2009-10-19 Thread marcin mank
> This proposal is just "hints by the back door", ISTM. As Tom says, there is
> a justification for having it on tablespaces but not on individual tables.

If the parameter is defined as "the chance that a page is in cache"
there is very real physical meaning to it. And this is per-table, not
per-tablespace. A "users" table will likely be fetched from cache all
the time, while a "billing_records" table will be fetched mostly from
disk.

Greetings
Marcin

-- 
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] per table random-page-cost?

2009-10-19 Thread Andrew Dunstan



marcin mank wrote:

I've been thinking about this a bit, too.  I've been wondering if it
might make sense to have a "random_page_cost" and "seq_page_cost"
setting for each TABLESPACE, to compensate for the fact that different
media might be faster or slower, and a percent-cached setting for each
table over top of that.




I thought about making it per-table, but realistically I think most
people don`t use tablespaces now. I would not want to be telling
people "to be able to hint the planner to (not) index-scan the table,
You must move it to a separate tablespace".
  


This is just plain wrong, in my experience. *Every* large installation I 
deal with uses tablespaces.


This proposal is just "hints by the back door", ISTM. As Tom says, there 
is a justification for having it on tablespaces but not on individual 
tables.


If you want to argue for full blown planner hints, that's a whole other 
story. Have you read the previous debates on the subject?


cheers

--
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] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Tom Lane
Ron Mayer  writes:
> Would postgres get considerably cleaner if a hypothetical 9.0 release
> skipped backward compatibility and removed anything that's only
> maintained for historical reasons?

Yeah, and our user community would get a lot smaller too :-(

Actually, I think any attempt to do that would result in a fork,
and a consequent splintering of the community.  We can get away
with occasionally cleaning up individual problematic behaviors
(example: implicit casts to text), but any sort of all-at-once
breakage would result in a lot of people Just Saying No.

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] LATERAL

2009-10-19 Thread Andrew Gierth
> "Greg" == Greg Stark  writes:

 >> Why not?  As Andrew pointed out, what we're really trying to
 >> accomplish here is consider sub-join plans that are parameterized
 >> by a value obtained from an outer relation.  I think we shouldn't
 >> artificially limit what we consider.

 Greg> Am I understanding you right that a typical case of this might
 Greg> be something like

 Greg> nested loop
 Greg> index scan expecting 1 record
 Greg> merge join
 Greg> index scan on partial index where col = outer.foo and col2
 Greg> between a and b
 Greg> some other scan

no, because you could never pick the partial index at plan time.

 Greg> or

 Greg> nested loop
 Greg> index scan expecting 1 record
 Greg> merge join
 Greg> index scan on  where col1 = outer.foo and col2
 Greg> between a and b
 Greg> some other scan

 Greg> Ie, where the nested loop is a degenerate nested loop which
 Greg> only expects a single value and provides a parameter which
 Greg> allows some partial index to work or allows for some other
 Greg> index scan by providing a higher order key element?

The nested loop does NOT have to be degenerate. Consider queries of
this form:

select * from small
  left join (big1 join big2 on (big1.id=big2.id))
  on (small.id=big1.id);

Right now, the only way pg can plan this is to do a hashjoin or
mergejoin of the _entire content of big1 and big2_ and join the
result against "small" (again in a hashjoin or mergejoin plan).
This becomes excessively slow compared to the "ideal" plan:

  nested loop
  seqscan on small
  nested loop
 indexscan on big1 where id=small.id
 indexscan on big2 where id=small.id (or big1.id which is equiv)

(The same argument applies if "small" is not actually small but has
restriction clauses)

-- 
Andrew (irc:RhodiumToad)

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


Re: [HACKERS] per table random-page-cost?

2009-10-19 Thread Tom Lane
marcin mank  writes:
> I thought about making it per-table, but realistically I think most
> people don`t use tablespaces now. I would not want to be telling
> people "to be able to hint the planner to (not) index-scan the table,
> You must move it to a separate tablespace".

Per-table is not physically sensible.  Per-tablespace has some rationale
to 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] LATERAL

2009-10-19 Thread Tom Lane
Greg Stark  writes:
> nested loop
> index scan expecting 1 record
> merge join
> index scan on  where col1 = outer.foo and col2
> between a and b
> some other scan

> Ie, where the nested loop is a degenerate nested loop which only
> expects a single value and provides a parameter which allows some
> partial index to work or allows for some other index scan by providing
> a higher order key element?

Right.  I don't see any particular reason to assume the inner path
is iterated only once, either.  If the key value coming from the outer
path is sufficiently useful, this could be a win even with multiple
iterations, as compared to having to scan the whole of some large
relation or other ...

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] per table random-page-cost?

2009-10-19 Thread marcin mank
> I thought about making it per-table***space***, but realistically I

-- 
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] per table random-page-cost?

2009-10-19 Thread marcin mank
> I've been thinking about this a bit, too.  I've been wondering if it
> might make sense to have a "random_page_cost" and "seq_page_cost"
> setting for each TABLESPACE, to compensate for the fact that different
> media might be faster or slower, and a percent-cached setting for each
> table over top of that.
>

I thought about making it per-table, but realistically I think most
people don`t use tablespaces now. I would not want to be telling
people "to be able to hint the planner to (not) index-scan the table,
You must move it to a separate tablespace".

A global default, a per-tablespace default overriding it, and a
per-table value overriding them both seems like over-engineering to
me.

Greetings
Marcin

-- 
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] per table random-page-cost?

2009-10-19 Thread Kevin Grittner
Robert Haas  wrote:
 
> I've been wondering if it might make sense to have a
> "random_page_cost" and "seq_page_cost" setting for each TABLESPACE,
> to compensate for the fact that different media might be faster or
> slower, and a percent-cached setting for each table over top of
> that.
 
[after recovering from the initial cringing reaction...]
 
How about calculating an effective percentage based on other
information.  effective_cache_size, along with relation and database
size, come to mind.  How about the particular index being considered
for the plan?  Of course, you might have to be careful about working
in TOAST table size for a particular query, based on the columns
retrieved.
 
I have no doubt that there would be some major performance regressions
in the first cut of anything like this, for at least *some* queries. 
The toughest part of this might be to get adequate testing to tune it
for a wide enough variety of real-life situations.
 
-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] per table random-page-cost?

2009-10-19 Thread Greg Stark
On Mon, Oct 19, 2009 at 2:08 PM, marcin mank  wrote:
> Currently random_page_cost is a GUC. I propose that this could be set 
> per-table.

Or per-tablespace.

Yes, I think there are a class of GUCs which describe the physical
attributes of the storage system which should be per-table or
per-tablespace. random_page_cost, sequential_page_cost,
effective_io_concurrency come to mind.

While this isn't a simple flag to change it does seem like a bit of a
SMOP. The GUC infrastructure stores these values in global variables
which the planner and other systems consult directly. They would
instead have to be made storage parameters which the planner and other
systems check on the appropriate table and default to the global GUC
if they're not set.

-- 
greg

-- 
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] per table random-page-cost?

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 5:08 PM, marcin mank  wrote:
> Currently random_page_cost is a GUC. I propose that this could be set 
> per-table.
>
> I think this is a good idea for widely-wanted planner hints. This way
> You can say "I do NOT want this table to be index-scanned, because I
> know it is not cached" by setting it`s random_page_cost to a large
> value (an obviously You can do the other way around, when setting the
> random_page_cost to 1 You say "I don`t care how You fetch the pages,
> they are all in cache")
>
> The value for the per-table setting could be inferred from
> pg_stat(io)?.*tables . We could have a tool to suggest appropriate
> values.
>
> We could call it something like cached_percentage (and have the cost
> of a random tuple fetch be inferred from the global random_page_cost,
> seq_tuple_cost and the per-table cached_percentage). Then we could set
> the global random_page_cost to a sane value like 200. Now one can
> wonder why the planner works while having such blantantly unrealistic
> values for random_page_cost :)
>
> What do You think?

I've been thinking about this a bit, too.  I've been wondering if it
might make sense to have a "random_page_cost" and "seq_page_cost"
setting for each TABLESPACE, to compensate for the fact that different
media might be faster or slower, and a percent-cached setting for each
table over top of that.

...Robert

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


[HACKERS] per table random-page-cost?

2009-10-19 Thread marcin mank
Currently random_page_cost is a GUC. I propose that this could be set per-table.

I think this is a good idea for widely-wanted planner hints. This way
You can say "I do NOT want this table to be index-scanned, because I
know it is not cached" by setting it`s random_page_cost to a large
value (an obviously You can do the other way around, when setting the
random_page_cost to 1 You say "I don`t care how You fetch the pages,
they are all in cache")

The value for the per-table setting could be inferred from
pg_stat(io)?.*tables . We could have a tool to suggest appropriate
values.

We could call it something like cached_percentage (and have the cost
of a random tuple fetch be inferred from the global random_page_cost,
seq_tuple_cost and the per-table cached_percentage). Then we could set
the global random_page_cost to a sane value like 200. Now one can
wonder why the planner works while having such blantantly unrealistic
values for random_page_cost :)

What do You think?

Greetings
Marcin Mank

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


[HACKERS] Could postgres be much cleaner if a future release skipped backward compatibility?

2009-10-19 Thread Ron Mayer
Tom Lane wrote:
> What are the probabilities that the OpenACSes of the world will just
> set the value to "backward compatible" instead of touching their code?

Would postgres get considerably cleaner if a hypothetical 9.0 release
skipped backward compatibility and removed anything that's only
maintained for historical reasons?

I notice the docs are filled with passages like the quotes
below - which suggest that there's a fair amount of stuff
that might be done differently if it weren't for backward
compatibility.




"For historical reasons (i.e., this is clearly wrong but it's far
 too late to change it), subscripting of fixed-length array types
 starts from zero, rather than from one as for variable-length arrays. "

"Most of the alternative names listed in the "Aliases" column are
 the names used internally by PostgreSQL for historical reasons.
 In addition, some internally used or deprecated types are available,
 but are not listed here. "

"Note:  The name "oid2name" is historical, and is actually
 rather misleading"

"Note:  Native Kerberos authentication has been deprecated
 and should be used only for backward compatibility."

"Old-style functions are now deprecated because of portability
 problems and lack of functionality, but they are still
 supported for compatibility reasons. "

"Although they still work, they are deprecated due to poor
 error handling, inconvenient methods of detecting end-of-data,
 and lack of support for binary or nonblocking transfers."

"The PostgreSQL usage of SELECT INTO to represent table
 creation is historical. It is best to use CREATE TABLE AS
 for this purpose in new code. "

"regular expression metasyntax ...
 option...m: historical synonym for n"

"Such comments are more a historical artifact than a useful facility,
 and their use is deprecated; use the expanded syntax instead."

"The CAST syntax conforms to SQL; the syntax with :: is historical
 PostgreSQL  usage."

"timeofday() is a historical PostgreSQL function."

"(This does not match non-slice behavior and is done for
 historical reasons.)"

"The SQL standard requires the use of the ISO 8601 format. The name of
 the "SQL" output format is a historical accident."

"attribute ... The historical way to specify optional pieces of
 information about the function. "
Caution

"Caution: If the configuration parameter standard_conforming_strings
 is off, then PostgreSQL recognizes backslash escapes in both regular
 and escape string constants. This is for backward compatibility
 with the historical"

"historical alias for stddev_samp ... historical alias for var_samp"

"For historical reasons, this variable contains two independent components"

"For historical reasons, the same function doesn't just return a
 boolean result; instead it has to store the flag at the location
 indicated by the third argument. "

"For historical reasons, there are two levels of notice handling,"

"Note that subscripting is 1-based, whereas for historical
 reasons proargtypes is subscripted from 0 "

"The term attribute is equivalent to column and is used
 for historical reasons. "

"For historical reasons, ALTER TABLE can be used with
 sequences too; but the only variants of ALTER TABLE
 that are allowed with sequences are"

"While this still works, it is deprecated and the special
meaning of \. can be expected to be removed in a future release."

"Use of this parameter is deprecated as of PostgreSQL 8.3;"


-- 
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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Eric B. Ridge

On Oct 19, 2009, at 3:46 PM, Tom Lane wrote:


Sorry if this is obvious to everyone else, but *when* will the error
throw?


Whenever we do semantic analysis of the particular query or  
expression.


That's what I figured.


During CREATE FUNCTION or during runtime?  I'm secretly hoping
that it'll throw during CREATE FUNCTION.


Be careful what you ask for, you might get it ;-)




Yeah, and we've got at least one function that does the CREATE TEMP  
TABLE foo (...) pattern.  So I understand.


We want to our schema to keep pace with whatever the default settings  
are for stuff like this, so it'd be great if we could find and resolve  
the issues sooner rather than later.  We implemented better coding  
practices later on in the project to help us disambiguate between  
variables and columns, but there's still a bunch of legacy stuff  
that's going to be broken.


eric

--
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] LATERAL

2009-10-19 Thread Greg Stark
On Sun, Oct 18, 2009 at 12:57 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> You could probably convince me that a merge join is not going to be
>> too useful (how often can you want a merge join on the inner side of a
>> nested loop?
>
> Why not?  As Andrew pointed out, what we're really trying to accomplish
> here is consider sub-join plans that are parameterized by a value
> obtained from an outer relation.  I think we shouldn't artificially
> limit what we consider.

Am I understanding you right that a typical case of this might be something like

nested loop
index scan expecting 1 record
merge join
index scan on partial index where col = outer.foo and col2
between a and b
some other scan

or

nested loop
index scan expecting 1 record
merge join
index scan on  where col1 = outer.foo and col2
between a and b
some other scan

Ie, where the nested loop is a degenerate nested loop which only
expects a single value and provides a parameter which allows some
partial index to work or allows for some other index scan by providing
a higher order key element?

-- 
greg

-- 
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] Application name patch - v2

2009-10-19 Thread Magnus Hagander
2009/10/19 Dave Page :
> On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin  wrote:
>>
>> Would'nt this also make sense for PostgreSQL? That is, when no environment
>> is set, and no SET-command is issued, that the application name becomes the
>> default?
>
> That needs to be set by the application. As discussed previously,
> there's no way for libpq to get at argv[0].

Um, that has to be platform dependent, no? On windows for example, you
can use GetCommandLine().


-- 
 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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 12:23 PM, Tom Lane wrote:


That is, the specification of options is made outside of the language
in question.


I don't think I particularly care for this.  It's inventing a global
mechanism to cover a problem that we currently have one instance of
in one PL.  That's a mighty thin justification.  Also, I tend to think
that you should have several instances of a problem before you venture
to design a global solution --- else your one-size-fits-all solution
might turn out to be a lot less general than you thought.


Sure, just an idea to keep in mind for when you do have a second and a  
third option to add…


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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
"Eric B. Ridge"  writes:
> On Oct 19, 2009, at 2:47 PM, Tom Lane wrote:
>> 1. Invent a GUC that has the settings backwards-compatible,
>> oracle-compatible, throw-error (exact spellings TBD).  Factory  
>> default,
>> at least for a few releases, will be throw-error.

> Sorry if this is obvious to everyone else, but *when* will the error  
> throw?

Whenever we do semantic analysis of the particular query or expression.

> During CREATE FUNCTION or during runtime?  I'm secretly hoping  
> that it'll throw during CREATE FUNCTION.

Be careful what you ask for, you might get it ;-)

The problem with doing more than minimal syntax checking during CREATE
FUNCTION, or even at the start of function execution, is that people
are far too accustomed to being able to do things like

CREATE TEMP TABLE foo ( ... );
INSERT INTO foo ... ;

and of course the second command will fail outright if foo doesn't exist
--- or even if we made that not fail, how will we do any meaningful
semantic checking of later SELECTs against foo?  Another example is a
fairly common pattern in trigger functions:

if tg_op = 'insert' then
... do something with new.* ...
else if tg_op = 'delete' then
... do something with old.* ...
... etc ...

where semantic checking on the non-executed parts of the function would
certainly throw error.

I would love to offer an option that "fully" checks plpgsql functions
but I think it would break so much code that no one could really use it.

In any case this is pretty much unrelated to the current patch...

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
"David E. Wheeler"  writes:
> On Oct 19, 2009, at 12:05 PM, Tom Lane wrote:
>> Where exactly would you put the modifier, and why is that better than
>> the existing #option convention?

> CREATE OR REPLACE FUNCTION foo()
> RETURNS BOOLEAN
> LANGUAGE plpgsql WITH opt1, opt2
> AS $$...$$;

> That is, the specification of options is made outside of the language  
> in question.

I don't think I particularly care for this.  It's inventing a global
mechanism to cover a problem that we currently have one instance of
in one PL.  That's a mighty thin justification.  Also, I tend to think
that you should have several instances of a problem before you venture
to design a global solution --- else your one-size-fits-all solution
might turn out to be a lot less general than you thought.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
I wrote:
> Where exactly would you put the modifier, and why is that better than
> the existing #option convention?

BTW, it occurs to me that since that's undocumented, not everyone might
know what I'm talking about.  There's some code in plpgsql that allows
you to write
#option dump
at the very beginning of a plpgsql function body, and get a dump of the
plpgsql syntax tree.  Since this was never intended for anything except
low-level debugging, it never got documented --- but the obvious
intention was that other sorts of options might come along later.
Now we have a case where a per-function option seems like just the
ticket.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 12:05 PM, Tom Lane wrote:


What about adopting the modifier syntax you're adding to COPY?


Where exactly would you put the modifier, and why is that better than
the existing #option convention?


CREATE OR REPLACE FUNCTION foo()
RETURNS BOOLEAN
LANGUAGE plpgsql WITH opt1, opt2
AS $$...$$;

That is, the specification of options is made outside of the language  
in question. It might only effect a particular language (plpgsql in  
this case) and be ignored otherwise (or trigger an exception), but  
it's clean and very much like what you have elsewhere.


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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Eric B. Ridge

On Oct 19, 2009, at 2:47 PM, Tom Lane wrote:


1. Invent a GUC that has the settings backwards-compatible,
oracle-compatible, throw-error (exact spellings TBD).  Factory  
default,

at least for a few releases, will be throw-error.


Sorry if this is obvious to everyone else, but *when* will the error  
throw?  During CREATE FUNCTION or during runtime?  I'm secretly hoping  
that it'll throw during CREATE FUNCTION.  I'd rather have my entire  
schema creation transaction abort so I can fix the problems up-front,  
rather than at "random" while the application is running.


eric




--
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 8.3.8 and Solaris 10_x86 64 bit problems?

2009-10-19 Thread Zdenek Kotala
Andrew Chernow píše v po 19. 10. 2009 v 14:14 -0400:
> > # ./pg_ctl
> > ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file 
> > /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 
> > 0xfd7fff1cf210 does not fit
> > Killed
> > 
> 
> "symbol (unknown)".  Can you turn on debugging symbols?  Knowing the 
> symbol may point to a library that was not compiled properly.

Also you can try to run
LD_DEBUG=basic ./pg_ctl

and also 

elfdump  | grep R_AMD64_32

it should show when symbols came from. By theway what S10 version do you
use? 

ld -V  and uname -a also helps.


> > So I run 'ldd pg_ctl' to see if everything is linking ok.
> > 
> > And I'm wondering if there is a problem with libpq.so.5 as mentioned in 
> > the original error
> > 
> > # file /usr/local/postgres64/lib/libpq.so.5
> > 
> > 
> > /usr/local/postgres64/lib/libpq.so.5:   ELF 64-bit LSB dynamic lib AMD64 
> > Version 1 [SSE CMOV], dynamically linked, not stripped
> > 
> > Ok.  So looking good. Maybe there is a library or header libpq needs 
> > that I'm missing in 64 bit?
> > 
> > # ldd /usr/local/postgres64/lib/libpq.so.5
> 
> Are you sure that all pg_ctl referenced libraries and all libpq.so 
> referenced libraries were built as 64-bit using PIC?  Are you linking 
> with any static library that may contain 32-bit objects?  That error is 
> most commonly PIC or arch-mismatch.
> 

Agree, I went through linker bugs and missing PIC is often root cause of
this problem. See

http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6261066

Problem was that ./configure badly setup PIC switch on amd64 platform.

Please, could you compile pure postgreSQL without other own libraries
like readline and openssl? It should help to find which library is
culprit.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
"David E. Wheeler"  writes:
> On Oct 19, 2009, at 11:47 AM, Tom Lane wrote:
>> 2. Also invent a #option syntax that allows the GUC to be overridden
>> per-function.  (Since the main GUC is SUSET, we can't just use a
>> per-function SET to override it.  There are other ways we could do  
>> this but none seem less ugly than #option...)

> What about adopting the modifier syntax you're adding to COPY?

Where exactly would you put the modifier, and why is that better than
the existing #option convention?

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 11:47 AM, Tom Lane wrote:


1. Invent a GUC that has the settings backwards-compatible,
oracle-compatible, throw-error (exact spellings TBD).  Factory  
default,
at least for a few releases, will be throw-error.  Make it SUSET so  
that

unprivileged users can't break things by twiddling it; but it's still
possible for the DBA to set it per-database or per-user.

2. Also invent a #option syntax that allows the GUC to be overridden
per-function.  (Since the main GUC is SUSET, we can't just use a
per-function SET to override it.  There are other ways we could do  
this

but none seem less ugly than #option...)


What about adopting the modifier syntax you're adding to COPY?

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
Andrew Dunstan  writes:
> Tom Lane wrote:
>> (a) Nobody but me is afraid of the consequences of treating this as
>> a GUC.  (I still think you're all wrong, but so be it.)

> I can't say I'm happy about it. For one thing, the granularity seems all 
> wrong.  I'd rather be able to keep backwards compatibility on a function 
> by function basis. Or would the value of the GUC at the time the 
> function was created stick?

Again, I can't see making a GUC that works fundamentally differently
from the rest of them.

Given this round of feedback, I make the following proposal:

1. Invent a GUC that has the settings backwards-compatible,
oracle-compatible, throw-error (exact spellings TBD).  Factory default,
at least for a few releases, will be throw-error.  Make it SUSET so that
unprivileged users can't break things by twiddling it; but it's still
possible for the DBA to set it per-database or per-user.

2. Also invent a #option syntax that allows the GUC to be overridden
per-function.  (Since the main GUC is SUSET, we can't just use a
per-function SET to override it.  There are other ways we could do this
but none seem less ugly than #option...)

Given that the global default will be throw-error, I don't feel a need
to kluge up pg_dump to insert #option in old function definitions;
that's ugly and there are too many cases it would not cover.  But that
could be added to this proposal if folks feel strongly enough.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
Merlin Moncure  writes:
> Maybe invent a new language handler?  plpgsql2 or shorten to pgsql?
> Now you can mess around all you want (and maybe fix some other
> compatibility warts at the same time).

Well, pl/psm is out there, and might even make it into core someday.
I don't find a lot of attraction in inventing a new language type that's
only marginally different from plpgsql --- that approach doesn't scale
up to handling multiple compatibility issues, at least not unless you
fix them all at the same time.

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 8.3.8 and Solaris 10_x86 64 bit problems?

2009-10-19 Thread Andrew Chernow

# ./pg_ctl
ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file 
/usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 
0xfd7fff1cf210 does not fit

Killed



"symbol (unknown)".  Can you turn on debugging symbols?  Knowing the 
symbol may point to a library that was not compiled properly.



So I run 'ldd pg_ctl' to see if everything is linking ok.

And I'm wondering if there is a problem with libpq.so.5 as mentioned in 
the original error


# file /usr/local/postgres64/lib/libpq.so.5


/usr/local/postgres64/lib/libpq.so.5:   ELF 64-bit LSB dynamic lib AMD64 
Version 1 [SSE CMOV], dynamically linked, not stripped


Ok.  So looking good. Maybe there is a library or header libpq needs 
that I'm missing in 64 bit?


# ldd /usr/local/postgres64/lib/libpq.so.5


Are you sure that all pg_ctl referenced libraries and all libpq.so 
referenced libraries were built as 64-bit using PIC?  Are you linking 
with any static library that may contain 32-bit objects?  That error is 
most commonly PIC or arch-mismatch.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Andrew Dunstan



Tom Lane wrote:

(a) Nobody but me is afraid of the consequences of treating this as
a GUC.  (I still think you're all wrong, but so be it.)
  



I can't say I'm happy about it. For one thing, the granularity seems all 
wrong.  I'd rather be able to keep backwards compatibility on a function 
by function basis. Or would the value of the GUC at the time the 
function was created stick?



What are the probabilities that the OpenACSes of the world will just
set the value to "backward compatible" instead of touching their code?
  


Quite high, I should say.


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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Kevin Grittner
Tom Lane  wrote:
 
> (a) Nobody but me is afraid of the consequences of treating this as
> a GUC.
 
Well, it seems dangerous to me, but I'm confident we can cover this
within our shop, so I'm reluctant to take a position on it.  I guess
the main question is whether we want to allow an Oracle-compatibility
mode, knowing it's a foot-gun.  Without it we'd likely make extra work
for someone converting from Oracle to PostgreSQL, although they would
be likely to fix bugs during the cleanup work.  Based on previous
decisions I've seen here, I would have expected people to just go with
an error, period; especially since it would simplify the code.
 
> (b) Everybody agrees that a "throw error" setting would be helpful.
 
That's the only setting I would use on any of our databases, if it
were a GUC.
 
-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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
Robert Haas  writes:
> On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane  wrote:
>> (a) Nobody but me is afraid of the consequences of treating this as
>> a GUC.  (I still think you're all wrong, but so be it.)

> I'm afraid of it, I'm just not sure I have a better idea.  It wouldn't
> bother me a bit if we made the only available behavior "throw an
> error", but I'm afraid it will bother someone else.

> Is there a chance we could make this a GUC, but only allow it to be
> changed at the function level, with no way to override the server
> default?  It seems to me that the chances of blowing up the world
> would be a lot lower that way, though possibly still not low enough.

I don't particularly care to invent a new GUC class just for this,
but if we think the issue is important enough, we could

(a) make the GUC superuser-only

(b) invent a #option or similar syntax to override the GUC per-function.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane  wrote:
> Pavel Stehule  writes:
>> ambiguous identifiers is probably the top reason of some plpgsql's
>> mysterious errors. More times I found wrong code - sometime really
>> important (some security checks). I never found good code with
>> ambiguous identifiers - so for me, exception is good. But - there will
>> be lot of working applications that contains this hidden bug - and
>> works "well". So it could be a problem. GUC should be a solution.
>
> So the conclusions so far are:
>
> (a) Nobody but me is afraid of the consequences of treating this as
> a GUC.  (I still think you're all wrong, but so be it.)

I'm afraid of it, I'm just not sure I have a better idea.  It wouldn't
bother me a bit if we made the only available behavior "throw an
error", but I'm afraid it will bother someone else.

Is there a chance we could make this a GUC, but only allow it to be
changed at the function level, with no way to override the server
default?  It seems to me that the chances of blowing up the world
would be a lot lower that way, though possibly still not low enough.

> (b) Everybody agrees that a "throw error" setting would be helpful.
>
> I am not sure there's any consensus on what the default setting should
> be, though.  Can we get away with making the default be "throw error"?
> What are the probabilities that the OpenACSes of the world will just
> set the value to "backward compatible" instead of touching their code?
> Do we need/want a hack in pg_dump to attach a SET to functions dumped
> from old DBs?

I've already commented on most of these (recap: yes, very high, yes)
so I'll refrain from beating a dead horse.

...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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
Pavel Stehule  writes:
> ambiguous identifiers is probably the top reason of some plpgsql's
> mysterious errors. More times I found wrong code - sometime really
> important (some security checks). I never found good code with
> ambiguous identifiers - so for me, exception is good. But - there will
> be lot of working applications that contains this hidden bug - and
> works "well". So it could be a problem. GUC should be a solution.

So the conclusions so far are:

(a) Nobody but me is afraid of the consequences of treating this as
a GUC.  (I still think you're all wrong, but so be it.)

(b) Everybody agrees that a "throw error" setting would be helpful.

I am not sure there's any consensus on what the default setting should
be, though.  Can we get away with making the default be "throw error"?
What are the probabilities that the OpenACSes of the world will just
set the value to "backward compatible" instead of touching their code?
Do we need/want a hack in pg_dump to attach a SET to functions dumped
from old DBs?

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 8.3.8 and Solaris 10_x86 64 bit problems?

2009-10-19 Thread u235sentinel

Zdenek Kotala wrote:

I can point on this article:

http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html

Zdenek


  
Ok so I'm checking everything in my environment.  The system actually 
builds postgres with openssl98k.  Comes back and says it's ready to 
install.  I run 'make install'  and try to run something like pg_ctl 
again.  Seem to be seeing the same results.



# file pg_ctl
pg_ctl: ELF 64-bit LSB executable AMD64 Version 1 [SSE FXSR CMOV 
FPU], dynamically linked, not stripped


# ./pg_ctl
ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file 
/usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 
0xfd7fff1cf210 does not fit

Killed

So I run 'ldd pg_ctl' to see if everything is linking ok.

   libpq.so.5 =>/usr/local/postgres64/lib/libpq.so.5
   libm.so.2 => /usr/lib/64/libm.so.2
   libxml2.so.2 =>  /usr/lib/64/libxml2.so.2
   libz.so.1 => /usr/lib/64/libz.so.1
   libreadline.so.6 =>  /usr/local/lib/libreadline.so.6
   libcurses.so.1 =>/usr/lib/64/libcurses.so.1
   librt.so.1 =>/usr/lib/64/librt.so.1
   libsocket.so.1 =>/usr/lib/64/libsocket.so.1
   libc.so.1 => /usr/lib/64/libc.so.1
   libpthread.so.1 =>   /usr/lib/64/libpthread.so.1
   libnsl.so.1 =>   /lib/64/libnsl.so.1
   libgcc_s.so.1 => /usr/sfw/lib/amd64/libgcc_s.so.1
   libaio.so.1 =>   /lib/64/libaio.so.1
   libmd.so.1 =>/lib/64/libmd.so.1
   libmp.so.2 =>/lib/64/libmp.so.2
   libscf.so.1 =>   /lib/64/libscf.so.1
   libdoor.so.1 =>  /lib/64/libdoor.so.1
   libuutil.so.1 => /lib/64/libuutil.so.1
   libgen.so.1 =>   /lib/64/libgen.so.1

And I'm wondering if there is a problem with libpq.so.5 as mentioned in 
the original error


# file /usr/local/postgres64/lib/libpq.so.5


/usr/local/postgres64/lib/libpq.so.5:   ELF 64-bit LSB dynamic lib AMD64 
Version 1 [SSE CMOV], dynamically linked, not stripped


Ok.  So looking good. Maybe there is a library or header libpq needs 
that I'm missing in 64 bit?


# ldd /usr/local/postgres64/lib/libpq.so.5
   libsocket.so.1 =>/usr/lib/64/libsocket.so.1
   libpthread.so.1 =>   /usr/lib/64/libpthread.so.1
   libc.so.1 => /usr/lib/64/libc.so.1
   libnsl.so.1 =>   /lib/64/libnsl.so.1
   libmp.so.2 =>/lib/64/libmp.so.2
   libmd.so.1 =>/lib/64/libmd.so.1
   libscf.so.1 =>   /lib/64/libscf.so.1
   libdoor.so.1 =>  /lib/64/libdoor.so.1
   libuutil.so.1 => /lib/64/libuutil.so.1
   libgen.so.1 =>   /lib/64/libgen.so.1
   libm.so.2 => /lib/64/libm.so.2

Looks good.  I'm not sure where to go from here.  I have everything else 
I need built in 64 bit except for Postgres with ssl :/


I've spent the last few weeks googling and talking to people about it.  
Not sure what I'm missing here.


Thanks!



--
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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane  wrote:
> "David E. Wheeler"  writes:
>> I'd sure love $, as it's like shell, Perl, and other stuff.
>
> This discussion has gotten utterly off track.  The problem I am trying
> to solve is a non-Oracle-compatible behavior in plpgsql.  I have got
> substantially less than zero interest in proposals that "solve" the
> problem by introducing notations that don't even pretend to be
> compatible.

OK.  In that case, it seems like we should offer options #2 and #3
with a GUC or #option to switch between them.  Nobody has made an
argument in favor of keeping #1 around.  I'm still strongly of the
opinion that #3 (error) should be the default behavior to avoid silent
failures.

...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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Merlin Moncure
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane  wrote:
> "David E. Wheeler"  writes:
>> I'd sure love $, as it's like shell, Perl, and other stuff.
>
> This discussion has gotten utterly off track.  The problem I am trying
> to solve is a non-Oracle-compatible behavior in plpgsql.  I have got
> substantially less than zero interest in proposals that "solve" the
> problem by introducing notations that don't even pretend to be
> compatible.

Personally, I'd vote against a GUC option. I just plain don't like the
idea that a function could do different things depending on server
configuration.   TBH, I'm not very happy with #option either.   That
said, I agree that Oracle method is far better.

Maybe invent a new language handler?  plpgsql2 or shorten to pgsql?
Now you can mess around all you want (and maybe fix some other
compatibility warts at the same time).

merlin

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


Re: [HACKERS] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Pavel Stehule
2009/10/19 Kevin Grittner :
> "David E. Wheeler"  wrote:
>
>> I'd be in favor of a GUC that I could turn on to throw an error
>> when there's an ambiguity.
>
> I would consider hiding one definition with another very bad form, so
> I would prefer to have plpgsql throw an error when that happens.  I
> don't particularly care whether that is the only supported behavior or
> whether there's a GUC to control it, or what its default is, if
> present.
>

ambiguous identifiers is probably the top reason of some plpgsql's
mysterious errors. More times I found wrong code - sometime really
important (some security checks). I never found good code with
ambiguous identifiers - so for me, exception is good. But - there will
be lot of working applications that contains this hidden bug - and
works "well". So it could be a problem. GUC should be a solution.

Pavel


> -Kevin
>
> --
> 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] Rejecting weak passwords

2009-10-19 Thread Tom Lane
Andrew Dunstan  writes:
> Alvaro Herrera wrote:
>> We do, if you have you server grabbing passwords from LDAP or whatever
>> external auth service you use.  That would be more secure than anything
>> mentioned in this thread, because the password enforcement could work on
>> unencrypted passwords without adverse consequences.

> We don't have it today for passwords that postgres manages. Unless we're 
> going to rely on an external auth source completely, I think there's a 
> good case for the hooks, but not for any of the other "adjustments" that 
> people have suggested.

Yeah.  Installing LDAP or Kerberos or whatever is sensible if you have
a need for a central auth server anyway.  If you are just trying to run a
database, it's a major additional investment of effort, and I can't
quibble at all with people who think that it's unreasonable to have to
do that just to have some modicum of a password policy.

I also am of the opinion that it's reasonable to provide a hook or two
for this purpose, but not to go further than that.

regards, tom lane

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


Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue

2009-10-19 Thread Dean Rasheed
2009/10/19 Robert Haas :
> On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed
>  wrote:
>> This is a WIP patch to replace the after-trigger queues with TID bitmaps
>> to prevent them from using excessive amounts of memory. Each round of
>> trigger executions is a modified bitmap heap scan.
>
> If the bitmap becomes lossy, how do you preserve the correct semantics?
>
> ...Robert
>

The idea is that it filters by the transaction ID and command ID of
modified rows to see what's been updated in the command(s) the trigger
is for...

 - Dean

-- 
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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Kevin Grittner
"David E. Wheeler"  wrote:
 
> I'd be in favor of a GUC that I could turn on to throw an error
> when there's an ambiguity.
 
I would consider hiding one definition with another very bad form, so
I would prefer to have plpgsql throw an error when that happens.  I
don't particularly care whether that is the only supported behavior or
whether there's a GUC to control it, or what its default is, if
present.
 
-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] Scaling up deferred unique checks and the after trigger queue

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed
 wrote:
> This is a WIP patch to replace the after-trigger queues with TID bitmaps
> to prevent them from using excessive amounts of memory. Each round of
> trigger executions is a modified bitmap heap scan.

If the bitmap becomes lossy, how do you preserve the correct semantics?

...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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 9:49 AM, Tom Lane wrote:


I'd sure love $, as it's like shell, Perl, and other stuff.


This discussion has gotten utterly off track.  The problem I am trying
to solve is a non-Oracle-compatible behavior in plpgsql.  I have got
substantially less than zero interest in proposals that "solve" the
problem by introducing notations that don't even pretend to be
compatible.


Party pooper.

I'd be in favor of a GUC that I could turn on to throw an error when  
there's an ambiguity. As for which way it should go, I have no dog in  
that pony hunt. Or something.


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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Tom Lane
"David E. Wheeler"  writes:
> I'd sure love $, as it's like shell, Perl, and other stuff.

This discussion has gotten utterly off track.  The problem I am trying
to solve is a non-Oracle-compatible behavior in plpgsql.  I have got
substantially less than zero interest in proposals that "solve" the
problem by introducing notations that don't even pretend to be
compatible.

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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 9:29 AM, Stephen Frost wrote:


Uh, what dollar quoting?  $_$ is what I typically use, so I wouldn't
expect a $ prefix to cause a problem.  I think it'd be more of an  
issue

because pl/pgsql still uses $1 and whatnot internally (doesn't it?).


Yes, but that's no more an issue than it is in Perl, where the same $n  
variables are globals. The issue with dollar quoting is that you can  
put anything between the dollar signs. So if you have two $variables,  
they can get in the way. Potentially. But perhaps the lexer and/or  
Parser won't be confused by that, Tom?


I'd sure love $, as it's like shell, Perl, and other stuff.

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] Application name patch - v2

2009-10-19 Thread Pavel Stehule
2009/10/19 Stephen Frost :
> * Pavel Stehule (pavel.steh...@gmail.com) wrote:
>> 2009/10/19 Stephen Frost :
>> > * Pavel Stehule (pavel.steh...@gmail.com) wrote:
>> >> Superuser permission could not be a problem. Simple security definer
>> >> function can do it.
>> >
>> > Then you've defeated the point of making it superuser-only.
>>
>> no. Because when I write security definer function, then I explicitly
>> allow an writing for some roles. When I don't write this function,
>> then GUC is secure.
>
> And what happens when those 'some roles' are used by broken
> applications?  You don't get to say "make it superuser only" and then
> turn around and tell people to hack around the fact that it's superuser
> only to be able to use it.  That's not a solution.

You don't understand me. When I would to have a secure environment,
then I don't write this function. So there will not be a posibility to
change application name from session.

Pavel

>
>        Stephen
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkrchUYACgkQrzgMPqB3kij8nACfUrF/wkpsORpXiN0QgbXvONdi
> ghYAn19MpPNnRrf9BxmIOVBRR212JU6c
> =c5tL
> -END PGP SIGNATURE-
>
>

-- 
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] Rejecting weak passwords

2009-10-19 Thread Tom Lane
I wrote:
> A server-side plugin can provide a guarantee that there are no bad
> passwords (for some value of bad, and with some possible adverse
> consequences).  We don't have that today.

BTW, it strikes me that ALTER USER RENAME introduces an interesting
hazard for such a plugin.  Consider

CREATE USER joe;
ALTER USER joe PASSWORD joe;  -- presumably, plugin will reject this
ALTER USER joe PASSWORD mumblefrotz;  -- assume this is considered OK
ALTER USER joe RENAME TO mumblefrotz;

Now we have a user with name equal to password, which no sane security
policy will think is a good thing, but the plugin had no chance to
prevent it.

In the case where the password is stored MD5-crypted, we clear it on
RENAME because of the fact that the username is part of the hash.
(We had always thought that was a bug^Wimplementation restriction,
but now it looks like a feature.)  So in normal practice the above
hazard doesn't exist; but it would for cleartext passwords.

One thing we could do is *always* clear the password on RENAME.
Another is to keep the cleartext password, but pass the new name
and password through the plugin before allowing the RENAME to succeed.
Since the PW is cleartext, presumably the plugin won't have any problem
checking it.  The latter however seems like we are getting a
security-critical behavior out of a chance combination of implementation
artifacts, which doesn't make me feel comfortable.

Thoughts?

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] Rejecting weak passwords

2009-10-19 Thread Andrew Dunstan



Alvaro Herrera wrote:

Except that your first statement is false.  It is not possible currently
for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
A server-side plugin can provide a guarantee that there are no bad
passwords (for some value of bad, and with some possible adverse
consequences).  We don't have that today.



We do, if you have you server grabbing passwords from LDAP or whatever
external auth service you use.  That would be more secure than anything
mentioned in this thread, because the password enforcement could work on
unencrypted passwords without adverse consequences.
  


We don't have it today for passwords that postgres manages. Unless we're 
going to rely on an external auth source completely, I think there's a 
good case for the hooks, but not for any of the other "adjustments" that 
people have suggested.



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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Stephen Frost
* David E. Wheeler (da...@kineticode.com) wrote:
> On Oct 19, 2009, at 8:36 AM, Robert Haas wrote:
>
>> I think warnings are too easy to miss, but I agree your other
>> suggestion.  I know you can write function_name.variable_name, but
>> that's often massively long-winded.  We either need a short, fixed
>> prefix, or some kind of sigil.  I previously suggested ? to parallel
>> ECPG, but Tom didn't like it.  I still do.  :-)
>
> I suppose that $ would interfere with dollar quoting. What about @ or @@ 
> (sorry, I did mess with MSSQL back in the 90s).

Uh, what dollar quoting?  $_$ is what I typically use, so I wouldn't
expect a $ prefix to cause a problem.  I think it'd be more of an issue
because pl/pgsql still uses $1 and whatnot internally (doesn't it?).

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 8:36 AM, Robert Haas wrote:


I think warnings are too easy to miss, but I agree your other
suggestion.  I know you can write function_name.variable_name, but
that's often massively long-winded.  We either need a short, fixed
prefix, or some kind of sigil.  I previously suggested ? to parallel
ECPG, but Tom didn't like it.  I still do.  :-)


I suppose that $ would interfere with dollar quoting. What about @ or  
@@ (sorry, I did mess with MSSQL back in the 90s).


Hrm…PostgreSQL is starting to have the same problem as Perl: running  
out of characters because they're used for operators. :var would be  
perfect, if it wasn't for psql. ?var is okay, I guess, if a bit… 
questionable. Are {braces} used for anything?


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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread David E. Wheeler

On Oct 19, 2009, at 7:54 AM, Stephen Frost wrote:


4. Resolve ambiguous names as query column, but throw warning

#4 would be my vote, followed by #3.  To be perfectly honest, I'd be a
whole lot happier with a pl/pgsql that let me prefix variable names  
with

a '$' or similar to get away from this whole nonsense.  I've been very
tempted to tell everyone I work with to start prefixing their  
variables

names with '_' except that it ends up looking just plain ugly.


+1, just what I was thinking.

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] Rejecting weak passwords

2009-10-19 Thread Alvaro Herrera
Tom Lane escribió:
> Peter Eisentraut  writes:
> > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
> >> I guess I misunderstood something there, but I had assumed that the
> >> checkbox item read something like: "Does the product offer password
> >> policy enforcement?" (to quote Dave Page).
> 
> > The answer to that is currently "Yes, with external tools".  Using the
> > plugin approach, the answer will remain "Yes, with external tools".  So
> > we wouldn't gain much.
> 
> Except that your first statement is false.  It is not possible currently
> for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
> A server-side plugin can provide a guarantee that there are no bad
> passwords (for some value of bad, and with some possible adverse
> consequences).  We don't have that today.

We do, if you have you server grabbing passwords from LDAP or whatever
external auth service you use.  That would be more secure than anything
mentioned in this thread, because the password enforcement could work on
unencrypted passwords without adverse consequences.

-- 
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] Rejecting weak passwords

2009-10-19 Thread Tom Lane
Peter Eisentraut  writes:
> On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
>> I guess I misunderstood something there, but I had assumed that the
>> checkbox item read something like: "Does the product offer password
>> policy enforcement?" (to quote Dave Page).

> The answer to that is currently "Yes, with external tools".  Using the
> plugin approach, the answer will remain "Yes, with external tools".  So
> we wouldn't gain much.

Except that your first statement is false.  It is not possible currently
for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
A server-side plugin can provide a guarantee that there are no bad
passwords (for some value of bad, and with some possible adverse
consequences).  We don't have that today.

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] Application name patch - v2

2009-10-19 Thread Kevin Grittner
Robert Haas  wrote:
> Tom Lane  wrote:
 
>> I think Pavel's entire line of argument is utter nonsense.
 
> +1.  I can't even understand why we're still arguing about this.
 
Agreed.  One premise of the whole concept was "don't even think of
using it for security"[1].  That's not it's purpose; so any criticisms
on that basis are irrelevant.  Claims that it opens new security holes
if you *don't* try to use it for this purpose don't seem to have any
merit that I can see; I don't think Pavel has even attempted to put
such an argument forward.
 
-Kevin
 
[1]
http://archives.postgresql.org/message-id/407d949e0907161237r76ebd92av6836c6563d8a2...@mail.gmail.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] Rejecting weak passwords

2009-10-19 Thread Peter Eisentraut
On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
> Peter Eisentraut wrote:
> > Note that this solution will still not satisfy the original checkbox
> > requirement.
> 
> I guess I misunderstood something there, but I had assumed that the
> checkbox item read something like: "Does the product offer password
> policy enforcement?" (to quote Dave Page).

The answer to that is currently "Yes, with external tools".  Using the
plugin approach, the answer will remain "Yes, with external tools".  So
we wouldn't gain much.


-- 
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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

2009-10-19 Thread Simon Riggs
On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote:
> Simon Riggs wrote:
> > 
> > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote:
> > > Tom Lane wrote:
> > > > Peter Eisentraut  writes:
> > > > > Is there a good reason for $subject, other than that the code is 
> > > > > entangled 
> > > > > with other ALTER TABLE code?
> > > > 
> > > > I think it could be lower, but it would take nontrivial restructuring of
> > > > the ALTER TABLE support.  In particular, consider what happens when you
> > > > have a list of subcommands that don't all require the same lock level.
> > > > I think you'd need to scan the list and find the highest required lock
> > > > level before starting ...
> > > 
> > > IIRC there was a patch from Simon to address this issue, but it had some
> > > holes which he didn't have time to close, so it sank.  Maybe this can be
> > > resurrected and fixed.
> > 
> > I was intending to finish that patch in this release cycle.
> 
> Since you're busy with Hot Standby, any chance you could pass it on?

If you'd like. It's mostly finished, just one last thing to finish:
atomic changes to pg_class via an already agreed API.

-- 
 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


Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock

2009-10-19 Thread Alvaro Herrera
Simon Riggs wrote:
> 
> On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote:
> > Tom Lane wrote:
> > > Peter Eisentraut  writes:
> > > > Is there a good reason for $subject, other than that the code is 
> > > > entangled 
> > > > with other ALTER TABLE code?
> > > 
> > > I think it could be lower, but it would take nontrivial restructuring of
> > > the ALTER TABLE support.  In particular, consider what happens when you
> > > have a list of subcommands that don't all require the same lock level.
> > > I think you'd need to scan the list and find the highest required lock
> > > level before starting ...
> > 
> > IIRC there was a patch from Simon to address this issue, but it had some
> > holes which he didn't have time to close, so it sank.  Maybe this can be
> > resurrected and fixed.
> 
> I was intending to finish that patch in this release cycle.

Since you're busy with Hot Standby, any chance you could pass it on?


-- 
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] Application name patch - v2

2009-10-19 Thread Kevin Grittner
David Fetter  wrote: 
 
> Could you point to a reference for this?  It could help the rest of
us
> see what you're aiming for even better :)
 
Sybase Adaptive Server Enterprise (ASE)
 
clientapplname varchar(30) column in sysprocesses table:
 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.tables/html/tables/tables50.htm
 
It can be set (for example) in the LOGINREC structure with DBSETLAPP:
 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32600.1500/html/dblib/dblib18.htm
 
SET clientapplname command to set on the fly by running SQL on the
connection:
 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands85.htm
 
-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] Writeable CTEs and side effects

2009-10-19 Thread Tom Lane
Marko Tiikkaja  writes:
> I'm looking at this, and if I understood correctly, you're suggesting
> we'd add a WithClause to InsertStmt.  Would we also allow this?

Yeah, we could eventually do all that.  I think supporting it in SELECT
would be plenty to start with, though.

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] Application name patch - v2

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 10:36 AM, Tom Lane  wrote:
> Dave Page  writes:
>> On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule  
>> wrote:
>>> I thing, so change of original name should generate warning.
>
>> Well, if other people think that's necessary, it's certainly possible.
>
> I think Pavel's entire line of argument is utter nonsense.  He's setting
> up a straw man that has nothing to do with any actually likely use of
> the variable.

+1.  I can't even understand why we're still arguing about this.
Other than Pavel, everyone thinks this is a complete non-problem, and
Pavel's hypothesis basically boils down to "someone might use this
feature in a stupid and naive way".  Well, sure.  They might.  So
what?

> I do agree with Peter's concerns about limiting the character set of the
> name string, and maybe there should be some sort of length limit too.

I don't have a strong feeling about this.  If limiting this to 7-bit
characters solves some nasty encoding problems or something, then
fine, but otherwise I think we can just escape what we emit into the
log and say that users who log this information should have a
sufficiently sophisticated log parser to cope with it.

...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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 10:54 AM, Stephen Frost  wrote:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> I think there are basically three behaviors that we could offer:
>>
>> 1. Resolve ambiguous names as plpgsql (historical PG behavior)
>> 2. Resolve ambiguous names as query column (Oracle behavior)
>> 3. Throw error if name is ambiguous (useful for finding problems)
>
> 4. Resolve ambiguous names as query column, but throw warning
>
> #4 would be my vote, followed by #3.  To be perfectly honest, I'd be a
> whole lot happier with a pl/pgsql that let me prefix variable names with
> a '$' or similar to get away from this whole nonsense.  I've been very
> tempted to tell everyone I work with to start prefixing their variables
> names with '_' except that it ends up looking just plain ugly.

I think warnings are too easy to miss, but I agree your other
suggestion.  I know you can write function_name.variable_name, but
that's often massively long-winded.  We either need a short, fixed
prefix, or some kind of sigil.  I previously suggested ? to parallel
ECPG, but Tom didn't like it.  I still do.  :-)

...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] COPY enhancements

2009-10-19 Thread Robert Haas
On Mon, Oct 19, 2009 at 11:21 AM, Alvaro Herrera
 wrote:
> Gokulakannan Somasundaram escribió:
>
>> Actually this problem is present even in today's transaction id scenario and
>> the only way we avoid is by using freezing. Can we use a similar approach?
>> This freezing should mean that we are freezing the sub-transaction in order
>> to avoid the sub-transaction wrap around failure.
>
> This would mean we would have to go over the data inserted by the
> subtransaction and mark it as "subxact frozen".  Some sort of sub-vacuum
> if you will (because it obviously needs to work inside a transaction).
> Doesn't sound real workable to me.

Especially because the XID consumed by the sub-transaction would still
be consumed, advancing the global XID counter.  Reclaiming the XIDs
after the fact doesn't fix anything as far as I can see.

...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] Application name patch - v2

2009-10-19 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
> 2009/10/19 Stephen Frost :
> > * Pavel Stehule (pavel.steh...@gmail.com) wrote:
> >> Superuser permission could not be a problem. Simple security definer
> >> function can do it.
> >
> > Then you've defeated the point of making it superuser-only.
> 
> no. Because when I write security definer function, then I explicitly
> allow an writing for some roles. When I don't write this function,
> then GUC is secure.

And what happens when those 'some roles' are used by broken
applications?  You don't get to say "make it superuser only" and then
turn around and tell people to hack around the fact that it's superuser
only to be able to use it.  That's not a solution.

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] COPY enhancements

2009-10-19 Thread Alvaro Herrera
Gokulakannan Somasundaram escribió:

> Actually this problem is present even in today's transaction id scenario and
> the only way we avoid is by using freezing. Can we use a similar approach?
> This freezing should mean that we are freezing the sub-transaction in order
> to avoid the sub-transaction wrap around failure.

This would mean we would have to go over the data inserted by the
subtransaction and mark it as "subxact frozen".  Some sort of sub-vacuum
if you will (because it obviously needs to work inside a transaction).
Doesn't sound real workable to me.

-- 
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] Application name patch - v2

2009-10-19 Thread Pavel Stehule
2009/10/19 Stephen Frost :
> * Pavel Stehule (pavel.steh...@gmail.com) wrote:
>> Superuser permission could not be a problem. Simple security definer
>> function can do it.
>
> Then you've defeated the point of making it superuser-only.

no. Because when I write security definer function, then I explicitly
allow an writing for some roles. When I don't write this function,
then GUC is secure.

Pavel


>
> I don't think that changing the app name deserves a warning, to be
> perfectly honest.  Notice should be sufficient.
>
>        Thanks,
>
>                Stephen
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkrceMsACgkQrzgMPqB3kihrpwCePXXJLxXIpvfHF0fMXbO6Pn94
> uJcAn2cnT97QNqeRW2coKRDZfWVKaXxz
> =xvXs
> -END PGP SIGNATURE-
>
>

-- 
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] Application name patch - v2

2009-10-19 Thread Dave Page
On Mon, Oct 19, 2009 at 3:49 PM, Tom Lane  wrote:
> Stephen Frost  writes:
>> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>>> I do agree with Peter's concerns about limiting the character set of the
>>> name string, and maybe there should be some sort of length limit too.
>
>> I was thinking we might just declare it of type 'name'..
>
> 'name' wouldn't help, since it's pretty character-set-agnostic.
> Anyway this is a GUC not a table column.

As a sidenote, in the stats part of this patch I did limit the length
to NAMEDATALEN for the purposes of sizing the shared memory
allocation, however it's otherwise unlimited in length. Practically
that just means that like the current query, the application name may
be truncated when viewed through pg_stat_get_activity().

> Thinking about it more, it should be sufficient if we can ensure that
> the value is in the database encoding; logging of statements will
> already cause pretty much any legal DB-encoded string to be written to
> the log, so if you have a problem with that then you've already got
> a problem to fix.

Right - that's what I was saying to Peter earlier. That can of course
be turned off though

> This is no issue for an ordinary SET, but AFAIR we do not have a good
> story for handling non-ASCII stuff arriving within the initial
> connection request packet.  It might be time to try to do something
> about that.  Or we could just restrict those values to ASCII.

It would seem sensible to apply the same rule to the connection string
and SET, if only for consistency (at least as far as application_name
is concerned). I know that use of Japanese/Chinese characters in
database names is not uncommon however, so restricting connection
strings to ASCII in general might not go down well.

-- 
Dave Page
EnterpriseDB UK:   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] Controlling changes in plpgsql variable resolution

2009-10-19 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> I think there are basically three behaviors that we could offer:
> 
> 1. Resolve ambiguous names as plpgsql (historical PG behavior)
> 2. Resolve ambiguous names as query column (Oracle behavior)
> 3. Throw error if name is ambiguous (useful for finding problems)

4. Resolve ambiguous names as query column, but throw warning

#4 would be my vote, followed by #3.  To be perfectly honest, I'd be a
whole lot happier with a pl/pgsql that let me prefix variable names with
a '$' or similar to get away from this whole nonsense.  I've been very
tempted to tell everyone I work with to start prefixing their variables
names with '_' except that it ends up looking just plain ugly.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Application name patch - v2

2009-10-19 Thread Tom Lane
Stephen Frost  writes:
> * Tom Lane (t...@sss.pgh.pa.us) wrote:
>> I do agree with Peter's concerns about limiting the character set of the
>> name string, and maybe there should be some sort of length limit too.

> I was thinking we might just declare it of type 'name'..

'name' wouldn't help, since it's pretty character-set-agnostic.
Anyway this is a GUC not a table column.

Thinking about it more, it should be sufficient if we can ensure that
the value is in the database encoding; logging of statements will
already cause pretty much any legal DB-encoded string to be written to
the log, so if you have a problem with that then you've already got
a problem to fix.

This is no issue for an ordinary SET, but AFAIR we do not have a good
story for handling non-ASCII stuff arriving within the initial
connection request packet.  It might be time to try to do something
about that.  Or we could just restrict those values to ASCII.

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] Application name patch - v2

2009-10-19 Thread Dave Page
On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin  wrote:
>
> Would'nt this also make sense for PostgreSQL? That is, when no environment
> is set, and no SET-command is issued, that the application name becomes the
> default?

That needs to be set by the application. As discussed previously,
there's no way for libpq to get at argv[0].


-- 
Dave Page
EnterpriseDB UK:   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] Application name patch - v2

2009-10-19 Thread Massa, Harald Armin
> Sure. Here's a nice example from SQL Server as well as related doc links:
>
> http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html
> http://msdn.microsoft.com/en-us/library/ms189770.aspx
>
> http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx
>

that looks as if the entry defaults to the application name (argv[0])



> Similar features are available in Oracle:
>
> http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm


and I definitely know from Oracle, that the application name is the default
without the progammer / user issuing any SET command.

Would'nt this also make sense for PostgreSQL? That is, when no environment
is set, and no SET-command is issued, that the application name becomes the
default?

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
%s is too gigantic of an industry to bend to the whims of reality


Re: [HACKERS] Application name patch - v2

2009-10-19 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote:
> Dave Page  writes:
> > Well, if other people think that's necessary, it's certainly possible.
> 
> I think Pavel's entire line of argument is utter nonsense.  He's setting
> up a straw man that has nothing to do with any actually likely use of
> the variable.
> 
> I do agree with Peter's concerns about limiting the character set of the
> name string, and maybe there should be some sort of length limit too.

I was thinking we might just declare it of type 'name'..

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Application name patch - v2

2009-10-19 Thread Tom Lane
David Fetter  writes:
> On Mon, Oct 19, 2009 at 11:39:58AM +0100, Dave Page wrote:
>> Please bear in mind that this feature is based on similar features in
>> other DBMSs (and in fact, a feature in the JDBC spec)

> Could you point to a reference for this?  It could help the rest of us
> see what you're aiming for even better :)

Yeah.  One thing I would like to see is a confirmation that this feature
can actually be used by the JDBC driver to implement the relevant
feature(s) of the JDBC spec.

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] Application name patch - v2

2009-10-19 Thread Dave Page
On Mon, Oct 19, 2009 at 3:17 PM, David Fetter  wrote:

> Could you point to a reference for this?  It could help the rest of us
> see what you're aiming for even better :)

Sure. Here's a nice example from SQL Server as well as related doc links:

http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html
http://msdn.microsoft.com/en-us/library/ms189770.aspx
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx

Kris pointed out the JDBC usage of this (and some related properties):

http://archives.postgresql.org/pgsql-hackers/2009-10/msg00726.php

Similar features are available in Oracle:

http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm
http://www.dba-oracle.com/security/the_client_identifier.htm (in this
case, it is considered a security/auditing feature)



-- 
Dave Page
EnterpriseDB UK:   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] Application name patch - v2

2009-10-19 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
> 2009/10/19 Heikki Linnakangas :
> > Or are you saying that it should not be possible for the client to
> > change the value after connecting? That limits the usefulness with
> > connection pools.
> 
> What I know, connections from connection pool without reset are shared
> by one application. But I am not against some possibility to change
> this value from application. I am against to possibility an change by
> normal user. When we allow it, then this value has not any wight,
> because any broken appliaction (via SQL injection) can change it.

Broken applications have much bigger problems than this.  Predicating
what we would/should do on the assumption of an application that's
broken just doesn't make sense to me.

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Application name patch - v2

2009-10-19 Thread Tom Lane
Dave Page  writes:
> On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule  
> wrote:
>> I thing, so change of original name should generate warning.

> Well, if other people think that's necessary, it's certainly possible.

I think Pavel's entire line of argument is utter nonsense.  He's setting
up a straw man that has nothing to do with any actually likely use of
the variable.

I do agree with Peter's concerns about limiting the character set of the
name string, and maybe there should be some sort of length limit too.

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] Application name patch - v2

2009-10-19 Thread Stephen Frost
* Pavel Stehule (pavel.steh...@gmail.com) wrote:
> Superuser permission could not be a problem. Simple security definer
> function can do it.

Then you've defeated the point of making it superuser-only.

I don't think that changing the app name deserves a warning, to be
perfectly honest.  Notice should be sufficient.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Rejecting weak passwords

2009-10-19 Thread Tom Lane
"Albe Laurenz"  writes:
> Bruce Momjian wrote:
>> Password checks might include password complexity or non-reuse of
>> passwords. This facility will require the client to send the password to
>> the server in plain-text, so SSL and 'password' authentication is
>> necessary to use this features.

> So in my opinion that should be:
> This facility will require to send new and changed password to
> the server in plain-text, so it will require SSL, and the use
> of encrypted passwords in CREATE/ALTER ROLE will have to be
> disabled.

Actually, not one word of *either* version should be in TODO.  All of
that is speculation about policies that a particular add-on module
might or might not choose to enforce.

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] Writeable CTEs and side effects

2009-10-19 Thread Marko Tiikkaja

Tom Lane wrote:

Merlin Moncure  writes:

Is the above form:
with x as (delete .. returning *) insert into y select * from x
going to be allowed?  I was informed on irc that it wasn't...it would
have to be written as:
insert into y with x as (delete .. returning *) select * from x


I would think that we would require the former and forbid the latter.
One of the basic limitations of the feature is going to be that you
can only have WITH (something RETURNING) at the top level, and the
latter syntax doesn't look like that to me.


I'm looking at this, and if I understood correctly, you're suggesting
we'd add a WithClause to InsertStmt.  Would we also allow this?

WITH t1 AS (DELETE FROM foo RETURNING *)
INSERT INTO bar
WITH t2 AS (VALUES(0))
SELECT * FROM t1 UNION ALL
SELECT * FROM t2;

I could also see use for adding this for UDPATE and DELETE too, i.e.

WITH t AS (DELETE FROM foo RETURNING id)
UPDATE bar SET foo_id = NULL FROM t WHERE t.id = bar.foo_id;

Did I misunderstand something here?


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


  1   2   >