Re: [HACKERS] Query progress indication - an implementation

[Peter Eisentraut]

On Sunday 28 June 2009 20:38:59 Robert Haas wrote:
 The only other use case I can think of for functionality of this type
 is some kind of dashboard view on a system with very long-running
 queries, where you want to see how far you have yet to go on each one
 (maybe to approximate when you can submit the next one) without having
 detailed knowledge of how expensive each individual query was project
 to be.  But that's a pretty narrow use case, and I'm not sure it
 really justifies the overhead of instrumenting every query in this
 way.

Well, progress information is probably only going to be useful for long-
running processes anyway, and then only when the admin is too bored and 
wonders what he can usefully cancel.  So it seems import to figure out exactly 
when to turn this on and when not to without causing too much overhead 
elsewhere.

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

Re: [HACKERS] Query progress indication - an implementation

[Scara Maccai]

 You might want to take a look at this:
 http://wiki.postgresql.org/wiki/Submitting_a_Patch

I will; I'm sorry it wasn't in the proper format. It was just a proof of 
concept, I guess I should have talked about it before even sending the patch.


 As to the content of the patch, I think that what you are
 doing is
 comparing the actual number of operations with the
 expected number
 of operations.  If that's correct, I'm not sure it's
 really all that
 useful, because it will only give you accurate
 percentage-of-completion information when the estimates are
 correct.
 But when the estimates are correct, you probably have a
 pretty good
 idea how long the query will take to run anyway.  

That would be a first step. Having an idea of how much a query progressed is 
very important for long-running queries. It's like copying files: even if you 
have a rough idea of how much time a copy will take, having an interface that 
tells you the percentage done is quite useful (IMHO).

 When
 the estimates
 are off, you'll find that the actual number of operations
 is more than
 the expected number of operations, but that won't really
 tell you how
 far you have to go.

The second phase would be using histograms to help refine the statistics at 
runtime.


 The only other use case I can think of for functionality of
 this type
 is some kind of dashboard view on a system with very
 long-running
 queries, where you want to see how far you have yet to go
 on each one
 (maybe to approximate when you can submit the next one)
 without having
 detailed knowledge of how expensive each individual query
 was project
 to be.  But that's a pretty narrow use case

I don't think it's that narrow: it is important, for long running queries, to 
know how far the query processed (the user wants to know how much of a query 
has yet to be executed). That's why you find so many papers on query progress 
indicators.
The real problem is that they don't give you a solution :)


 Greg Stark was (is?) working on a way to get
 EXPLAIN-ANALYZE type
 statistics on running queries; you might want to take a
 look at some
 of that work and see what you think.
 
 http://wiki.postgresql.org/wiki/Greg_Stark%27s_Development_Projects#EXPLAIN_queries_in_progress
 


That's interesting. I'll take a look!

Thank you very much for your comments.

Is anyone interested in such a progress indicator???






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

Re: [HACKERS] Proposal: More portable way to support 64bit platforms

[Tsutomu Yamada]

Peter Eisentraut pete...@gmx.net wrote:
  On Friday 26 June 2009 12:07:24 Tsutomu Yamada wrote:
   Proposal: More portable way to support 64bit platforms
  
   Short description:
  
   Current PostgreSQL implementation has some portability issues to
   support 64bit platforms: pointer calculations using long is not
   portable, for example on Windows x64 platform. We propose to use
   intptr_t instead of long, which appears in in C99.
  
  This makes sense.  You can also review the archives for previous iterations 
  of 
  this discussion (search for intptr_t).

Yes, I have read through the discusion but it seems somewhat faded
out. This is because no platform other than Windows has 64bit
pointer issues IMO. I think using intptr_t is cleaner and will bring
more portability. Moreover it will solve Windows 64bit pointer issues,
I believe.

  You might want to add your patch to the next commit fest.

Yes, I would like to submit patches for the next commit fest.

-- 
Tsutomu Yamada
SRA OSS, Inc. Japan

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

Re: [HACKERS] Extensions User Design

[Peter Eisentraut]

On Thursday 25 June 2009 01:09:17 Andrew Dunstan wrote:
 Well, I think in our case that would be going too far. I think there is
 a very good case for keeping a few key extensions in core both as
 exemplars and to make it easy to validate the extension mechanism
 itself. There have been suggestions in the past about throwing a bunch
 of things overboard, sometimes out of a passion for neatness more than
 anything else ISTM, but there have been good arguments against as well,
 particularly in the case of the PLs, which are tied so closely to the
 backend.

Another thing we might want to consider once we have a robust extension 
mechanism is to move some things out of the backend into extensions.  
Candidates could be uuid, legacy geometry types, inet/cidr, for example.  
These extensions would still be available and probably installed by default, 
but they need not be hardcoded into the backend.  But a policy of shipping 
zero extensions with the postgresql tarball obviously leaves very little 
flexibility to do any sort of thing like this.

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

Re: [HACKERS] Extensions User Design

[Richard Huxton]

Peter Eisentraut wrote:


Another thing we might want to consider once we have a robust extension 
mechanism is to move some things out of the backend into extensions.  
Candidates could be uuid, legacy geometry types, inet/cidr, for example.  
These extensions would still be available and probably installed by default, 
but they need not be hardcoded into the backend.


Presumably would help the prospective upgrader too. Upgrade tool can't 
cope with the change to inet types? No problem, I *know* they're not in 
use, since they're not loaded.


--
  Richard Huxton
  Archonet Ltd

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

[HACKERS] [PATCH] user mapping extension to pg_ident.conf

[Lars Kanis]

Hi all,

this patch adds the possibility to map the login-rolename to a different 
rolename actually used for permissions.

What is it used for?

I'm working with smartcard based TLS-authentication to connect to the PG 
server. Authentication is done with the keys and certificates from the card 
within the TLS handshake. Certificate-CN and login-username have to be the 
same or have to match by the pg_ident.conf. The role actually used for 
permissions is always the login-username. This patch allowes, to change the 
actually permissions to a role based on the certificate-CN. It is realised by 
an additional column in pg_ident.conf.

When using ODBC, you have to setup a fixed username which is used for login. 
Different permissions depending on the CN of the certificate on the current 
smartcard could be achieved by the following line:

# MAPNAME SYSTEM-USERNAMEPG-USERNAME   EFFECTIVE-USERNAME
ssl-user  /(.*)  dummy \1

The extension could be similar used for kerberos authentication, too.

Bytheway I refactored the pg_ident-code a little bit, to avoid duplicated code 
and to allow substitution of more than one match (\2, \3 etc).

Questions (I'm quite new to the PG-sources and used to write Ruby code):
- Is this something useful - or is there a much easier way?
- Are there any implementation shortcomings?

regards
Lars Kanis
diff -ur postgresql-8.4rc1.orig/src/backend/libpq/auth.c postgresql-8.4rc1/src/backend/libpq/auth.c
--- postgresql-8.4rc1.orig/src/backend/libpq/auth.c	2009-06-11 16:48:57.0 +0200
+++ postgresql-8.4rc1/src/backend/libpq/auth.c	2009-06-29 14:02:40.0 +0200
@@ -777,7 +777,7 @@
 	}
 
 	ret = check_usermap(port-hba-usermap, port-user_name, kusername,
-		pg_krb_caseins_users);
+		pg_krb_caseins_users, port-user_name);
 
 	krb5_free_ticket(pg_krb5_context, ticket);
 	krb5_auth_con_free(pg_krb5_context, auth_context);
@@ -1069,7 +1069,7 @@
 	}
 
 	ret = check_usermap(port-hba-usermap, port-user_name, gbuf.value,
-		pg_krb_caseins_users);
+		pg_krb_caseins_users, port-user_name);
 
 	gss_release_buffer(lmin_s, gbuf);
 
@@ -1360,12 +1360,12 @@
 
 		namebuf = palloc(strlen(accountname) + strlen(domainname) + 2);
 		sprintf(namebuf, %...@%s, accountname, domainname);
-		retval = check_usermap(port-hba-usermap, port-user_name, namebuf, true);
+		retval = check_usermap(port-hba-usermap, port-user_name, namebuf, true, port-user_name);
 		pfree(namebuf);
 		return retval;
 	}
 	else
-		return check_usermap(port-hba-usermap, port-user_name, accountname, true);
+		return check_usermap(port-hba-usermap, port-user_name, accountname, true, port-user_name);
 }
 #endif   /* ENABLE_SSPI */
 
@@ -1847,7 +1847,7 @@
 			return STATUS_ERROR;
 	}
 
-	return check_usermap(port-hba-usermap, port-user_name, ident_user, false);
+	return check_usermap(port-hba-usermap, port-user_name, ident_user, false, port-user_name);
 }
 
 
@@ -2184,9 +2184,9 @@
 		port-user_name)));
 		return STATUS_ERROR;
 	}
-
+	
 	/* Just pass the certificate CN to the usermap check */
-	return check_usermap(port-hba-usermap, port-user_name, port-peer_cn, false);
+	return check_usermap(port-hba-usermap, port-user_name, port-peer_cn, false, port-user_name);
 }
 
 #endif
diff -ur postgresql-8.4rc1.orig/src/backend/libpq/hba.c postgresql-8.4rc1/src/backend/libpq/hba.c
--- postgresql-8.4rc1.orig/src/backend/libpq/hba.c	2009-06-11 16:48:58.0 +0200
+++ postgresql-8.4rc1/src/backend/libpq/hba.c	2009-06-29 15:08:08.0 +0200
@@ -31,6 +31,7 @@
 #include storage/fd.h
 #include utils/flatfiles.h
 #include utils/guc.h
+#include utils/memutils.h
 
 
 
@@ -1418,6 +1419,68 @@
 	return true;
 }
 
+/* case (in-)sensitive string compare */
+static int strcmp_with_case( const char *str1, const char *str2, bool case_insensitive ){
+	if (case_insensitive)
+	{
+		return pg_strcasecmp(str1, str2);
+	}
+	else
+	{
+		return strcmp(str1, str2);
+	}
+}
+
+/*
+	Substitudes \1, \2, etc. within subst_in_str, based on the regexp-matches in extract_from_str.
+  
+	returns substituded string. It has to be pfree'd.
+*/
+static char *regexp_substitude(size_t nr_matches, regmatch_t *matches, const char *extract_from_str, const char *subst_in_str){
+	char	   *ofs;
+	char	   *psubst_out_str;
+	int	   nr_match;
+	char	*psubst_in_str;
+	
+	psubst_in_str = psubst_out_str = pstrdup(subst_in_str);
+
+	for(nr_match = 1; nr_match = nr_matches; nr_match++){
+		char subst_marker[5];
+		
+		snprintf(subst_marker, sizeof(subst_marker), \\%d, nr_match);
+		
+		if ((ofs = strstr(psubst_in_str, subst_marker)) != NULL)
+		{
+			/* substitution of the first argument requested */
+			if (matches[nr_match].rm_so  0)
+			{
+pfree(psubst_in_str);
+return NULL;
+			}
+			
+			/*
+* length: original length minus length of \1 plus length of match
+* plus null terminator
+*/
+			psubst_out_str = palloc0(strlen(psubst_in_str) - 2 + (matches[nr_match].rm_eo - matches[nr_match].rm_so) + 1);
+			

Re: [HACKERS] Query progress indication - an implementation

[Peter Hunsberger]

On Mon, Jun 29, 2009 at 3:47 AM, Scara Maccaim_li...@yahoo.it wrote:

 Is anyone interested in such a progress indicator???


I'm relatively new to Postgres and just starting to look at starting
to look at what we might do with it for handling large  genomic
datasets. I've used Toad for Oracle to have a look at whats going on
inside long running Oracle queries.  Knowing that a particular step is
doing a particular activity is useful for diagnostics as well as being
assured that you actually are making forward progress. IMO any
diagnostics you can provide for a low cost are useful.  The more
detail, the better.  Step 1 of 10 is good, 80% complete on step 1
of 10 is better.  80% complete on step 1, 10% complete on 10 steps
is even better.

-- 
Peter Hunsberger

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

Re: [HACKERS] Query progress indication - an implementation

[Robert Haas]

On Mon, Jun 29, 2009 at 4:47 AM, Scara Maccaim_li...@yahoo.it wrote:
 As to the content of the patch, I think that what you are doing is
 comparing the actual number of operations with the expected number
 of operations.  If that's correct, I'm not sure it's really all that
 useful, because it will only give you accurate
 percentage-of-completion information when the estimates are
 correct. But when the estimates are correct, you probably have a
 pretty good idea how long the query will take to run anyway.

 That would be a first step. Having an idea of how much a query progressed 
 is very important for long-running queries. It's like
 copying files: even if you have a rough idea of how much time a copy will 
 take, having an interface that tells you the percentage
 done is quite useful (IMHO).

I am all in favor of a query progress indicator; the trick is
constructing one that will actually be useful.  It's easy to have
estimates that are off by a factor of two or three, though, so I think
you'd frequently have situations when the query completed when the
progress estimater was at 40% or 250%. Those kinds of progress
indicators tend to annoy users, and for good reason.  File copying is
the sort of thing where it's pretty easy to estimate percentage of
completion by files and bytes; query execution is not.

So, I'm all in favor of what you're trying to conceptually; I just
don't like your proposed implementation.

...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] Proposal: More portable way to support 64bit platforms

[Tom Lane]

Tsutomu Yamada tsut...@sraoss.co.jp writes:
 Yes, I have read through the discusion but it seems somewhat faded
 out. This is because no platform other than Windows has 64bit
 pointer issues IMO. I think using intptr_t is cleaner and will bring
 more portability. Moreover it will solve Windows 64bit pointer issues,
 I believe.

The problem with this is that it's barely the tip of the iceberg.
One point I recall is that there are lots of places where %lu is
assumed to be the correct format to print Datums with.  If it were
actually possible to support Win64 with only a couple of dozen lines
of changes, we would have done it long since.

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] [PATCH] user mapping extension to pg_ident.conf

[Tom Lane]

Lars Kanis ka...@comcard.de writes:
 this patch adds the possibility to map the login-rolename to a different 
 rolename actually used for permissions.

This seems like an ugly addition with a very narrow use case.  Can't
you accomplish what you want with the existing usermap facility?

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] Proposal: More portable way to support 64bit platforms

[Peter Eisentraut]

On Monday 29 June 2009 17:20:09 Tom Lane wrote:
 The problem with this is that it's barely the tip of the iceberg.
 One point I recall is that there are lots of places where %lu is
 assumed to be the correct format to print Datums with. 

Hmm.  I tried this out.  typedef Datum to be long long int on a 32-bit 
platform and compile.  You get lots of warnings, but none about a format 
problem.  But if you explicitly insert a call like elog(INFO datum is %lu, 
somedatum), then you see a warning.  So this problem might not be very 
widespread.

 If it were
 actually possible to support Win64 with only a couple of dozen lines
 of changes, we would have done it long since.

Possibly, or everyone was too confused and didn't know where to start.

I think this proposed change is a step in the right direction, and it doesn't 
make things worse for anyone else.

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

Re: [HACKERS] Proposal: More portable way to support 64bit platforms

[Tom Lane]

Peter Eisentraut pete...@gmx.net writes:
 On Monday 29 June 2009 17:20:09 Tom Lane wrote:
 If it were
 actually possible to support Win64 with only a couple of dozen lines
 of changes, we would have done it long since.

 Possibly, or everyone was too confused and didn't know where to start.

Well, the previous proposal involved a massively invasive patch IIRC,
so I'm suspicious of this one being so short...

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] Query progress indication - an implementation

[m_lists]

 It's
 easy to have
 estimates that are off by a factor of two or three, though,
 so I think
 you'd frequently have situations when the query completed
 when the
 progress estimater was at 40% or 250%. 

I thought about implementing a given perfect estimates indicator at first 
then, as a second step, using histograms to leverage the indicator precision at 
run time. Of course, this doesn't mean the user wouldn't see the query 
completed at 40% or slowing down in a lot of cases...

I started this patch after reading the papers in 
http://wiki.postgresql.org/wiki/Query_progress_indication
Apparently they were able to predict query execution remaining time (in case of 
a perfect estimates query) with a very simple algorithm.

Given that:
1) The algorithm (driver node hypothesis) is so easy 
2) My project fits in the category of perfect estimates queries

I thought I'll give it a try.

Well: I have no idea how they got their results.

IMHO it's not possible to get max 10% error on query remaing time on most of 
the tpcd queries using that method, since the driver nodes have all the same 
importance. I had to introduce a lot of complexity (not in the patch that I 
posted) to have it somehow working, giving the nodes different work per tuple 
according to the node type (example: in a loop join the time it takes to read a 
row of the outer relation can't be compared to, say, the time it takes to read 
a row from a table scan: but the driver node hypothesis says they will take the 
same time...).

So the code that I have right now works pretty well for the 10 queries of my 
project, but I guess won't work for general queries :(

 So, I'm all in favor of what you're trying to conceptually;
 I just
 don't like your proposed implementation.

What kind of implementation would you propose?

Thank you very much for your comments.





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

Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf

[Lars Kanis]

Am Montag, 29. Juni 2009 16:26:56 schrieben Sie:
 Lars Kanis ka...@comcard.de writes:
  this patch adds the possibility to map the login-rolename to a different
  rolename actually used for permissions.

 This seems like an ugly addition with a very narrow use case.  Can't
 you accomplish what you want with the existing usermap facility?

You're right, my description is a bit incomplete.

The problem I have, is that I want to use an ordinary windows application, 
which connects to an arbitrary ODBC data source. This application stores a 
fixed username und password for the connection within it's own binary data 
file. It doesn't know anything about TLS-connection nor smartcard based 
authentication. All this is done in the libpg.dll.

It works fine so far, as long as I want to work with the sigle role given by 
the fixed username. I could map any cn-contents to this one user by writing:
# MAPNAME SYSTEM-USERNAMEPG-USERNAME
ssl-user  /.*fixed_user

The db internal role is always that given by the application. But I need to 
work with the role of the certificate-cn of the current smartcard, the 
application doesn't know about. Because the username is stored within the 
applications own binary data file I'm not able to change it according to the 
pluged in card.

I think the same problem occurs with kerberos authentication. You can't get 
the role based on your kerberos ticket, when the username is not set 
likewise.

So it seemed to me quite useful, to not just set which external name matches 
which login-username, but also to set which userrole is actually used for 
granted privilegs. This is done by an additional column with the same 
characteristic as column PG-USERNAME.

Another way could be to add an parameter to the hba line which tells the 
server to not care about the login username, but to only use the external 
(CN) name. But this wouldn't have the flexibility of regexps like in 
pg_ident.conf.

Hope this clarifies a bit.


regards
Lars Kanis


signature.asc
Description: This is a digitally signed message part.

Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf

[Tom Lane]

Lars Kanis ka...@comcard.de writes:
 The problem I have, is that I want to use an ordinary windows application, 
 which connects to an arbitrary ODBC data source. This application stores a 
 fixed username und password for the connection within it's own binary data 
 file. It doesn't know anything about TLS-connection nor smartcard based 
 authentication. All this is done in the libpg.dll.

This seems to boil down to I'm willing to damage PG's authentication
mechanisms to an unlimited extent to work around one broken proprietary
windows app.  I'm not excited about it.  Have you even considered the
potential for security problems arising from this?

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] Query progress indication - an implementation

[Scara Maccai]

 IMO
 any
 diagnostics you can provide for a low cost are
 useful.  The more
 detail, the better.  Step 1 of 10 is good, 80%
 complete on step 1
 of 10 is better.  80% complete on step 1, 10%
 complete on 10 steps
 is even better.

Well, I guess  Step 1 of 10 would be pretty trivial to implement (given the 
tree plan, how many branches have finished executing).
This doesn't tell you anything about the remaining time though.
That is: Step 1 of 10 doesn't mean that you are at 10%, nor that you are at 
50%, or 99%. In fact, I'm afraid it doesn't tell you anything...
I don't understand how useful such an implementation would be... being
assured that you actually are making forward progress... in which cases could 
you not be making forward progress?

Still, this won't take much time: if it's needed by someone else I guess it 
could be easily done.






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

[HACKERS] Multi-Dimensional Histograms

[David Fetter]

Folks,

For things like PostGIS, which will want to index in 4 dimensions
(x, y, z, t), we might want to have multi-dimensional selectivity
histograms and some way to use same.

Anybody here qualified to check out this paper on the subject, please
speak up :)

http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.23.8475

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

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

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

Re: [HACKERS] pre-proposal: permissions made easier

[Tom Lane]

Andrew Dunstan and...@dunslane.net writes:
 David Fetter wrote:
 There have been previous discussions of prospective permissions
 changes.  Are we restarting them here?

 It's not on the TODO list. I recall it being raised from time to time 
 but I certainly don't recall a consensus that it should be done, nor 
 how, so if you're implying that such a thing is a settled decision I 
 suspect you're not entirely correct. Of course, my memory has been known 
 to have errors ...

I think there's widespread agreement that SQL permissions are a pain in
the neck to manage.  We haven't got a consensus on a solution to that,
but looking at possibilities is certainly reasonable.

Jeff's idea does amount to granting prospective permissions in one
sense.  If you (in the future) grant some permissions to role foo,
then role foo_ro would automatically get some of those permissions too.
I think it has to be looked at in comparison to more general
prospective-permissions schemes; it clearly doesn't do everything you
could wish for in that line, and so we have to ask whether there'd be
much use-case left for it if we do implement something more general.
It also seems to me that a lot of the potential objections are shared
with more general schemes --- in particular, ooops, I forgot this was
in place and indirectly granted some permissions I shouldn't have...

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] pre-proposal: permissions made easier

[Jeff Davis]

On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote:
 I think it has to be looked at in comparison to more general
 prospective-permissions schemes; 

When I searched google for prospective permissions, all I found were
links to messages in this thread ;)

Can you refer me to a general prospective-permissions scheme that is
more widely accepted? Being more widely accepted also has the benefit
that users will feel more comfortable with the behavior.

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

[HACKERS] pg_restore -t table concerns

[Mike Toews]

Hey hackers,

I have a few concerns with the usability and documentation for 
pg_restore (note: I'm on 8.3, but I've checked the documentation for 8.4).


In my attempts to restore a table (using -t) from a backup file with -v 
(verbose) selected, I noticed a few things:


  1. If a non-existent table is used, e.g., -t idontexist no warnings
 are thrown. (It took me a while to figure out why a table was not
 being restored, since no warnings were issued.)
  2. It appears that the -t (table) option does not allow full table
 names, such as myschema.mytable, however this is not stated in
 the documentation for -t table. Is it possible to allow -t table
 to accept [schema.]table qualifiers? If not, the documentation
 should at least suggest that -n namespace be used to specify the
 schema where the table resides.
  3. It appears the the default for -n namespace uses the default_path
 variable (typically public). However, I'm only guessing this
 based on the behaviour, and it isn't mentioned in the
 documentation for -n namespace.

Thanks for any input, and apologies if this has already been discussed.

-Mike


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

Re: [HACKERS] pre-proposal: permissions made easier

[Tom Lane]

Jeff Davis pg...@j-davis.com writes:
 Can you refer me to a general prospective-permissions scheme that is
 more widely accepted?

Well, the point of my post was that nothing's gotten to the point of
being widely accepted.  But there are people working on a default
ACLs scheme that would cover some of that territory.
http://wiki.postgresql.org/wiki/DefaultACL

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] pg_restore -t table concerns

[Tom Lane]

Mike Toews mwto...@sfu.ca writes:
 I have a few concerns with the usability and documentation for 
 pg_restore (note: I'm on 8.3, but I've checked the documentation for 8.4).

There's a TODO item about that already...

* Add support for multiple pg_restore -t options, like pg_dump

pg_restore's -t switch is less useful than pg_dump's in quite a
few ways: no multiple switches, no pattern matching, no ability
to pick up indexes and other dependent items for a selected
table. It should be made to handle this switch just like pg_dump
does.

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] pre-proposal: permissions made easier

[Andrew Dunstan]

Jeff Davis wrote:

On Mon, 2009-06-29 at 12:55 -0400, Tom Lane wrote:
  

I think it has to be looked at in comparison to more general
prospective-permissions schemes; 



When I searched google for prospective permissions, all I found were
links to messages in this thread ;)

Can you refer me to a general prospective-permissions scheme that is
more widely accepted? Being more widely accepted also has the benefit
that users will feel more comfortable with the behavior.


  


Think of MySQL's wildcard permissions. They apply to any object whether 
that object is created before or after the rule is set, AIUI. That means 
the wildcard pattern is applied at the time the permission rule is 
referenced, rather than when the rule is created, thus applying it 
prospectively.


It's a feature many users would like to have, although, as Tom rightly 
points out, it can be a bit of a footgun if used carelessly.


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] pg_restore -t table concerns

[Andrew Dunstan]

Tom Lane wrote:

Mike Toews mwto...@sfu.ca writes:
  
I have a few concerns with the usability and documentation for 
pg_restore (note: I'm on 8.3, but I've checked the documentation for 8.4).



There's a TODO item about that already...

* Add support for multiple pg_restore -t options, like pg_dump

pg_restore's -t switch is less useful than pg_dump's in quite a
few ways: no multiple switches, no pattern matching, no ability
to pick up indexes and other dependent items for a selected
table. It should be made to handle this switch just like pg_dump
does.


  


It should also be pointed out that there is a workaround using the 
--use-list option.


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] pre-proposal: permissions made easier

[Josh Berkus]

All,

First, let me talk about the problem: it's been my observation that the 
majority of users, including public commercial web sites, which I run 
into in the field do not employ permissions in any useful way to protect 
their data.  An awful lot of these applications are running as the 
superuser or the database owner, partly because the company can't deal 
with object permissions management, especially when the application is 
under continuous development.


The pgAdmin widget doesn't really help much in this respect.

I want to avoid anything which requires an additional permissions check 
or any other check at runtime.  Instead, we need two tools:


1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default 
permissions, by ROLE and object type, on new objects.


2) a statement to set privs on all existing objects by type and role 
within a schema.


These two tools would make it vastly easier for admins to manage ROLE 
privileges without needing any additional runtime checks or limiting 
flexibility in object permissions assignment.  Further, they would make 
it very simple to build the kind of very simple ROLE-based permissions 
management Jeff is talking about on top of it (which is, BTW, very 
popular; it's one of MSSQL's major selling points to small businesses).


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] Query progress indication - an implementation

[Josh Berkus]

All,

Actually, an indicator of even just what step of the query was being 
executed would be very useful for checking on stuck queries.  If a DBA 
checks once that the query is on bitmapscan on table_x(index_y), and 
it's still on that 15 minutes later, he/she can guess that the query is 
thrashing due to HW or bad plan issues and kill it.


If the query is on sort rowset by col1 then the DBA knows not to kill 
it because it's almost done.


So, while an actual % completed indicator would be perfect, a query 
steps completed, current step = would still be very useful and a large 
improvement over what we have now.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] Query progress indication - an implementation

[Tom Lane]

Josh Berkus j...@agliodbs.com writes:
 So, while an actual % completed indicator would be perfect, a query 
 steps completed, current step = would still be very useful and a large 
 improvement over what we have now.

I think this is pretty much nonsense --- most queries run all their plan
nodes concurrently to some extent.  You can't usefully say that a query
is on some node, nor measure progress by whether some node is done.

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] pre-proposal: permissions made easier

[Jeff Davis]

On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote:
 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default 
 permissions, by ROLE and object type, on new objects.
 
 2) a statement to set privs on all existing objects by type and role 
 within a schema.

I don't see why either of these things should be properties of the
schema. It seems to make much more sense for these defaults to be a
property of the user who creates the objects. 

If #1 and #2 are both implemented as properties of the user, I think
that solves the use case I brought up. It would still be difficult to
see the overall scheme at a glance, but I don't think that's a major
problem.

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] Query progress indication - an implementation

[Robert Haas]

On Mon, Jun 29, 2009 at 11:15 AM, m_li...@yahoo.it wrote:
 So the code that I have right now works pretty well for the 10 queries of 
 my project, but I guess won't work for general queries :(

I think that's probably right.

 So, I'm all in favor of what you're trying to conceptually;
 I just
 don't like your proposed implementation.

 What kind of implementation would you propose?

I don't really have a proposed implementation in mind; I think it's a
hard problem.  That's why I suggested looking at the
EXPLAIN-ANALYZE-in-progress stuff.  By providing a lot more detail, a
human being can take a look at that output and make a judgment about
what's going on.  That's not as easy-to-use as what you're trying to
do, but I suspect it's more useful in practice.  It might be that
after reading a few hundred of those someone could propose some rules
of thumb to estimate the percentage of completion, which we could then
incorporate back into the system.  If not, we lose nothing by
implementing that feature first, since it is independently useful.

 Thank you very much for your comments.

No problem.

...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] Query progress indication - an implementation

[Joshua Tolley]

On Mon, Jun 29, 2009 at 02:07:23PM -0400, Tom Lane wrote:
 Josh Berkus j...@agliodbs.com writes:
  So, while an actual % completed indicator would be perfect, a query 
  steps completed, current step = would still be very useful and a large 
  improvement over what we have now.
 
 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

What about showing the outermost node where work has started?

--
Josh / eggyknap
End Point Corp.
www.endpoint.com


signature.asc
Description: Digital signature

Re: [HACKERS] pre-proposal: permissions made easier

[Tom Lane]

Jeff Davis pg...@j-davis.com writes:
 On Mon, 2009-06-29 at 10:52 -0700, Josh Berkus wrote:
 1) ALTER SCHEMA SET DEFAULT PRIVILEGES statements which sets default 
 permissions, by ROLE and object type, on new objects.
 
 2) a statement to set privs on all existing objects by type and role 
 within a schema.

 I don't see why either of these things should be properties of the
 schema. It seems to make much more sense for these defaults to be a
 property of the user who creates the objects. 

That seems fairly backwards to me.  I agree that tying it to schemas
is a bit less flexible than one could wish, but that doesn't make
attaching it to the user the right thing.

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] Query progress indication - an implementation

[Tom Lane]

Joshua Tolley eggyk...@gmail.com writes:
 On Mon, Jun 29, 2009 at 02:07:23PM -0400, Tom Lane wrote:
 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

 What about showing the outermost node where work has started?

That's always the outermost node; what would it tell you?

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] pre-proposal: permissions made easier

[Josh Berkus]

Jeff,


I don't see why either of these things should be properties of the
schema. It seems to make much more sense for these defaults to be a
property of the user who creates the objects.


The main reason is existing practice.  Currently, most applications I 
see in the field which bother with having several ROLES have all 
database objects belonging to one ROLE (db_owner).  So for most people 
setting permissions for all objects belonging to a specific user would 
amount to setting permissions for all objects of that type in a given 
database.


There's also the fact that SCHEMAs currently have their own visibility 
rules and permissions, which seems to me to dovetail nicely with the ACLs.


This is, of course, assuming that we are talking about setting 
permissions in saved objects, that is, all the object belonging to a 
particular user.


The approach I could see as valuable in vastly simplyfying things would 
be to set the permission on the user regardless of object properties; 
that is, the user is defined as WITH SELECT, INSERT, UPDATE ON ALL 
TABLES.  These user permissions would supercede any object permissions 
for that role.


This would make DBA's lives vastly simpler and make them more likely to 
use permissions.  But would it actually benefit security?


The problem I see with this approach is that in 95% of the applications 
I run across there are a few tables which really need to be locked 
down and restricted from most user access (maybe accessed only by an 
SRF).  In large development shops where more than one person has their 
hands on the DB, I can easily see one developer accidentally bypassing 
object-level security set up by another DBA through this mechanism.


The second, and bigger problem I can see is that this opens a whole new 
set of security holes by allowing an end-run around the existing access 
control structure with attackers can try to exploit.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] [PATCH] [v8.5] Security checks on largeobjects

[Bernd Helmle]

--On 29. Juni 2009 08:32:29 +0900 KaiGai Kohei kai...@ak.jp.nec.com wrote:


Yes, it intends to assign an identifier string not only numeric
large object identifier. The identifier string can be qualified
with a certain namespace as follows.

E.g)
 SELECT lo_open('my_picture01', x'4'::int);
 SELECT lo_create('pg_temp.my_musid02');

In the later case, the new largeobject will be reclaimed after
the session closed due to the temporary namespace.


I'm not sure about the usefulness of this. While having an identifier for a 
LO is nice, i believe most users store additional metadata about objects 
within their own tables anyways, linking the LO there. Also i doubt there 
is much need for temporary large objects (at least, i have no idea about 
this).


It might be interesting to dig into your proposal deeper in conjunction 
with TOAST (you've already mentioned this TODO). Having serial access with 
a nice interface into TOAST would be eliminating the need for 
pg_largeobject completely (i'm not a big fan of this one-big-system-table 
approach the old LO interface currently is).


--
Thanks

Bernd

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

Re: [HACKERS] pre-proposal: permissions made easier

[Tom Lane]

Josh Berkus j...@agliodbs.com writes:
 The second, and bigger problem I can see is that this opens a whole new 
 set of security holes by allowing an end-run around the existing access 
 control structure with attackers can try to exploit.

Yeah.  I'm very concerned about any scheme that invents additional
sources of permissions that aren't visible in the object's own ACL list.
Even if it's secure in its own terms, it'll blindside people and
programs who are used to the existing ways of doing things.

From what I recall of prior discussions, there is rough consensus that
the two types of facilities you mentioned (setting up default ACLs to be
applied at creation of objects created later, and providing a way to
change multiple objects' permissions with one GRANT) are desirable,
though there is plenty of argument about the details.  Neither of these
result in creating any new sources of permissions --- a given object's
ACL is still the whole truth.

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] Query progress indication - an implementation

[Peter Hunsberger]

On Mon, Jun 29, 2009 at 1:07 PM, Tom Lanet...@sss.pgh.pa.us wrote:
 Josh Berkus j...@agliodbs.com writes:
 So, while an actual % completed indicator would be perfect, a query
 steps completed, current step = would still be very useful and a large
 improvement over what we have now.

 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

What you get in Toad for Oracle is the ability to see long running
processes with multiple lines, one per process currently underway. If
I recall correctly, the returned information includes what operation
is underway (eg, physical reads), the % complete, start time, time
remaining and elapsed time.  Time remaining has been mostly useless
every time I've had to drill down to this level, but otherwise this
has been relatively useful information.

-- 
Peter Hunsberger

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

Re: [HACKERS] [PATCH] [v8.5] Security checks on largeobjects

[Tom Lane]

Bernd Helmle maili...@oopsware.de writes:
 It might be interesting to dig into your proposal deeper in conjunction 
 with TOAST (you've already mentioned this TODO). Having serial access with 
 a nice interface into TOAST would be eliminating the need for 
 pg_largeobject completely (i'm not a big fan of this one-big-system-table 
 approach the old LO interface currently is).

Yeah, it would be more useful probably to fix that than to add
decoration to the LO facility.  Making LO more usable is just going to
encourage people to bump into its other limitations (32-bit OIDs,
32-bit object size, finite maximum size of pg_largeobject, lack of
dead-object cleanup, etc etc).

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] [PATCH] user mapping extension to pg_ident.conf

[Stephen Frost]

Lars,

* Lars Kanis (ka...@comcard.de) wrote:
 The problem I have, is that I want to use an ordinary windows application, 
 which connects to an arbitrary ODBC data source. This application stores a 
 fixed username und password for the connection within it's own binary data 
 file. It doesn't know anything about TLS-connection nor smartcard based 
 authentication. All this is done in the libpg.dll.

To be honest, I think the problem as described above makes it a no-go.
What you're asking for is essentially I want the server to ignore the
username passed in which just doesn't make sense.  It's unfortunate
that this Windows application using ODBC doesn't allow for the username
to be changed, but that's not something PG is going to solve for you.  I
would recommend you work on fixing the application.

 It works fine so far, as long as I want to work with the sigle role given by 
 the fixed username. I could map any cn-contents to this one user by writing:
 # MAPNAME SYSTEM-USERNAMEPG-USERNAME
 ssl-user  /.*fixed_user

What you really want is:
ssl-user  user1_cn user1
ssl-user  user2_cn user2
ssl-user  user3_cn user3

or so..

And then have your application accept and use a username provided to it
when connecting to ODBC.  Having your application pass some hard-coded
username to PG all the time just isn't going to work.

 I think the same problem occurs with kerberos authentication. You can't get 
 the role based on your kerberos ticket, when the username is not set 
 likewise.

With Kerberos you do pass in the username (or possibly the username is
figured out from the principal, but it's the same to the server side)
you actually want to be in the database as part of the connection and then
the validation is done against the Kerberos credentials.  Perhaps your
Windows application also wouldn't work with Kerberos, but in the general
case people are logging in with their username, not with some fixed
username.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Problem building from source on Windows

[Ms swati chande]

Thanks for your help.
It worked.

Regards
Swati


  

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

[HACKERS] facing problem with createdb.

[Ms swati chande]

Hi,

I built postgresql from source using Windows 2005.

After installation, I am trying to create a database using

createdb.

It then asks for a password. What password is it? There are now passwords set 
on my system. Also tried editing pg_hba.conf. But could not solve the problem.

How do I move ahead.

Thanks

Regards



  

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

Re: [HACKERS] pre-proposal: permissions made easier

[Josh Berkus]

Tom,


 From what I recall of prior discussions, there is rough consensus that
the two types of facilities you mentioned (setting up default ACLs to be
applied at creation of objects created later, and providing a way to
change multiple objects' permissions with one GRANT) are desirable,
though there is plenty of argument about the details.  Neither of these
result in creating any new sources of permissions --- a given object's
ACL is still the whole truth.


yeah, that's why I've been working on that approach.  It doesn't 
simplify things as much as some DBAs might want, but it's the most 
side-effect-free approach.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] pre-proposal: permissions made easier

[Greg Stark]

On Mon, Jun 29, 2009 at 7:41 PM, Josh Berkusj...@agliodbs.com wrote:
 The main reason is existing practice.

I haven't followed the entire conversation so i'm not sure who I'm
going to be disagreeing with or agreeing with here. But I wanted to
mention that existing practice may not be a very useful place to start
here. Whatever mechanism we invent is going to change the calculus of
people deciding how to set up their schemas and roles since they'll
want to arrange things to take advantage of this new mechanism.

In particular, one early question was whether to use wildcard patterns
or schema names. People were saying wildcard patterns would be more
flexible because people don't always set up their objects in different
schemas. But if we had a mechanism someone wanted to use which
depended on schemas they would be far more likely to choose to set up
schemas for objects which belong in different security classes.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] pre-proposal: permissions made easier

[Josh Berkus]

Greg,


In particular, one early question was whether to use wildcard patterns
or schema names. People were saying wildcard patterns would be more
flexible because people don't always set up their objects in different
schemas. But if we had a mechanism someone wanted to use which
depended on schemas they would be far more likely to choose to set up
schemas for objects which belong in different security classes.


What I'm saying is that there are many users currently using schema for 
security classes.  I personally haven't ever encountered a DBA who used 
role ownership of objects as a mechanism for security context.  There's 
nothing conceptually invalid about the latter approach, but it would be 
hard for DBAs to grasp, and as a result less of them would use it.


Mainly that's because the concept of schema easily maps (even if 
inaccurately) to the concept of directories, whose permissions IT staff 
are used to managing.  So it's more intuitive for a DBA to say This is 
sensitive data so I'm going to put it in the SENSITIVE schema than to 
say this is sensitive data so I'm going to have the table belong to the 
SENSITIVE role.


Further, it's common practice on other DBMSes to have a database owner 
role which owns all database objects.  So DBA who learn Postgres second 
are going to do that.


If we were going for a theoretically pure approach, we'd actually have a 
security context concept which would include a bundle of permissions 
and each object would belong to one.


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] Multi-Dimensional Histograms

[Nathan Boley]

 For things like PostGIS, which will want to index in 4 dimensions
 (x, y, z, t), we might want to have multi-dimensional selectivity
 histograms and some way to use same.


Another use case is cross column statistics.

 Anybody here qualified to check out this paper on the subject, please
 speak up :)


Well, I don't know if I'm qualified per se, but here goes...

It seems seems like a very reasonable approach to multidimensional histograms.

For those who haven't had time to read the paper, it describes SGRID,
a greedy algorithm for partitioning an n-dimensional space into
non-overlapping n-dimensional boxes and 'outliers' ( points that dont
fit into any boxes, but dont warrant their own boxes ). Then, they
compare their method to a regression technique,  where the space is
fit into a fixed grid histogram and then the grid densities are
estimated via regression. They also compare a plane splitting
algorithm, called MHIST, where the planes are constrained to be
orthogonal to the naive dimension vectors. They dismiss singular value
decomposition and the discrete wavelet transform as being too
parametric ( which is silly, IMHO ) and briefly mention a couple
standard clustering techniques ( which are probably not appropriate
for us, given their complexity ). Unsurprisingly, it does well on the
two test sets that they consider.

It think the general idea is fine, but it would certainly need some
modification to work for postgres.

In particular,

Using the naive dimension vectors ( ie, north and east for geo data,
or column a and column b for cross-column stats ) in combination with
the box constraint will probably lead to problems. Consider, for
example, a river that goes in a straight line north-east, and a table
that stores camp sites which are mostly along the river. Because SGRID
can only create boxes with north east edges, you would end up with a
bunch of tiny box on the river where one, long north-east pointing box
would do.

We would probably want to replace the error term that SGRID uses to
determine when to create a new box by a statistical test ( maybe
homogeneity of means? ) ( also, the same holds for the outliers ).

Finally, this creates the partition but ( AFAICT ) it doesn't describe
a method for locating the histogram estimate given a point ( although
that doesn't seem too difficult ).

-Nathan

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

Re: [HACKERS] Multi-Dimensional Histograms

[David Fetter]

On Mon, Jun 29, 2009 at 01:28:01PM -0700, Nathan Boley wrote:
  For things like PostGIS, which will want to index in 4 dimensions
  (x, y, z, t), we might want to have multi-dimensional selectivity
  histograms and some way to use same.
 
 Another use case is cross column statistics.

Good to see there's more than one use case.  I was hoping people would
chime in with more use cases, and here one is, fast! :)

  Anybody here qualified to check out this paper on the subject, please
  speak up :)
 
 Well, I don't know if I'm qualified per se, but here goes...
 
 It seems seems like a very reasonable approach to multidimensional
 histograms.
 
 For those who haven't had time to read the paper, it describes
 SGRID, a greedy algorithm for partitioning an n-dimensional space
 into non-overlapping n-dimensional boxes and 'outliers' ( points
 that dont fit into any boxes, but dont warrant their own boxes ).
 Then, they compare their method to a regression technique,  where
 the space is fit into a fixed grid histogram and then the grid
 densities are estimated via regression. They also compare a plane
 splitting algorithm, called MHIST, where the planes are constrained
 to be orthogonal to the naive dimension vectors. They dismiss
 singular value decomposition and the discrete wavelet transform as
 being too parametric ( which is silly, IMHO )

Should we have a separate discussion about eigenvalues?  Wavelets?

 and briefly mention a couple standard clustering techniques ( which
 are probably not appropriate for us, given their complexity ).

Good to know.

 Unsurprisingly, it does well on the two test sets that they
 consider.

Wait.  You mean they might have carefully chosen data to make their
point?!?

;)

 It think the general idea is fine, but it would certainly need some
 modification to work for postgres.
 
 In particular,
 
 Using the naive dimension vectors ( ie, north and east for geo data,
 or column a and column b for cross-column stats ) in combination with
 the box constraint will probably lead to problems. Consider, for
 example, a river that goes in a straight line north-east, and a table
 that stores camp sites which are mostly along the river. Because SGRID
 can only create boxes with north east edges, you would end up with a
 bunch of tiny box on the river where one, long north-east pointing box
 would do.
 
 We would probably want to replace the error term that SGRID uses to
 determine when to create a new box by a statistical test ( maybe
 homogeneity of means? ) ( also, the same holds for the outliers ).
 
 Finally, this creates the partition but ( AFAICT ) it doesn't describe
 a method for locating the histogram estimate given a point ( although
 that doesn't seem too difficult ).

Is that not difficult, in terms of the math that needs doing, or
not difficult, in terms of how well PostgreSQL is already set up to
implement, or...?

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

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

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

Re: [HACKERS] facing problem with createdb.

[Filip Rembiałkowski]

2009/6/29 Ms swati chande swat...@yahoo.com


 Hi,

 I built postgresql from source using Windows 2005.


Did you read
http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows?

Why not use prebuilt versions (see
http://www.postgresql.org/download/windows)?




 After installation, I am trying to create a database using

 createdb.


Please read these:

http://www.postgresql.org/docs/current/static/manage-ag-createdb.html
http://www.postgresql.org/docs/8.3/static/app-createdb.html



 It then asks for a password. What password is it? There are now passwords
 set on my system. Also tried editing pg_hba.conf. But could not solve the
 problem.

 How do I move ahead.



it's not clear what is wrong there, but try this:

1. Add this line to the beginning of your pg_hba.conf:
host all all 127.0.0.1/32 trust

2. restart postgresql server.

3. you should be able to connect without password.


Please post such basic questions to pgsql-general mailing list.

You should REALLY read some manual pages, especially these:
http://www.postgresql.org/docs/8.3/static/reference-client.html


good luck!



-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [HACKERS] Query progress indication - an implementation

[Simon Riggs]

On Mon, 2009-06-29 at 14:07 -0400, Tom Lane wrote:
 Josh Berkus j...@agliodbs.com writes:
  So, while an actual % completed indicator would be perfect, a query 
  steps completed, current step = would still be very useful and a large 
  improvement over what we have now.
 
 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

The requirement is not nonsense, even if the detail was slightly off.

We can regard plans as acting in phases with each blocking node
separating the plan. We know which nodes those are, so we can report
that.

For each phase, it may be very hard to say what percentage is truly
complete, but we could at least report how much work has been done and
provide a percentage against planned numbers.

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


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

Re: [HACKERS] Multi-Dimensional Histograms

[Tom Lane]

David Fetter da...@fetter.org writes:
 On Mon, Jun 29, 2009 at 01:28:01PM -0700, Nathan Boley wrote:
 ... They dismiss
 singular value decomposition and the discrete wavelet transform as
 being too parametric ( which is silly, IMHO )

 Should we have a separate discussion about eigenvalues?  Wavelets?

I think it'd be a short discussion: what will you do with non-numeric
datatypes?  We probably don't really want to assume anything stronger
than that the datatype has a total ordering.

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] Query progress indication - an implementation

[Tom Lane]

Simon Riggs si...@2ndquadrant.com writes:
 On Mon, 2009-06-29 at 14:07 -0400, Tom Lane wrote:
 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

 The requirement is not nonsense, even if the detail was slightly off.

I was applying the word nonsense to the proposed implementation,
not the desire to have query progress indications ...

 We can regard plans as acting in phases with each blocking node
 separating the plan. We know which nodes those are, so we can report
 that.

[ shrug... ] You can regard them that way, but you won't get
particularly helpful results for a large fraction of real queries.
The system is generally set up to prefer streaming evaluation
as much as it can.  Even in nominally blocking nodes like Sort and Hash,
there are operational modes that look more like streaming, or at least
chunking.

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] Multi-Dimensional Histograms

[David Fetter]

On Mon, Jun 29, 2009 at 06:43:35PM -0400, Tom Lane wrote:
 David Fetter da...@fetter.org writes:
  On Mon, Jun 29, 2009 at 01:28:01PM -0700, Nathan Boley wrote:
  ... They dismiss singular value decomposition and the discrete
  wavelet transform as being too parametric ( which is silly, IMHO
  )
 
  Should we have a separate discussion about eigenvalues?  Wavelets?
 
 I think it'd be a short discussion: what will you do with
 non-numeric datatypes?  We probably don't really want to assume
 anything stronger than that the datatype has a total ordering.

That sounds about like the discussion we needed unless we later decide
we need to do something special for approximations of R^n :)

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

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

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

Re: [HACKERS] pre-proposal: permissions made easier

[Greg Stark]

On Mon, Jun 29, 2009 at 9:02 PM, Josh Berkusj...@agliodbs.com wrote:
 What I'm saying is that there are many users currently using schema for
 security classes.  I personally haven't ever encountered a DBA who used role
 ownership of objects as a mechanism for security context.  There's nothing
 conceptually invalid about the latter approach, but it would be hard for
 DBAs to grasp, and as a result less of them would use it.

 Mainly that's because the concept of schema easily maps (even if
 inaccurately) to the concept of directories, whose permissions IT staff are
 used to managing.  So it's more intuitive for a DBA to say This is
 sensitive data so I'm going to put it in the SENSITIVE schema than to say
 this is sensitive data so I'm going to have the table belong to the
 SENSITIVE role.

 Further, it's common practice on other DBMSes to have a database owner
 role which owns all database objects.  So DBA who learn Postgres second are
 going to do that.

It sounds to me like they *are* using the owner to provide security then.

 If we were going for a theoretically pure approach, we'd actually have a
 security context concept which would include a bundle of permissions and
 each object would belong to one.

It sounds like you're confounding the the owner of the objects and
roles that users have.

In the databases I've used we had a dba user which owned all the
objects. Then we had a www user which had DML access to most
objects, though lacked update and delete access to others. We actually
didn't need any other users but if we had a more extensive backend
interface we would have had a www-backend and reports and so on.

So I'm not sure what problem we're trying to solve here.

There's I just created a new www-backend role which I want to have
access to everything www has and then I'll go add a few additional
privileges. We can do that already by having www-backend belong to
the www role and then add the extra privileges manually.

And there's I just created a new table, I want www and
www-backend to get their usual privileges without thinking about it.
You want to be able to specify default grants that an object gets
based on the schema? That seems mostly reasonable though it might be a
good idea to have a WITH DEFAULT GRANTS or something like that on the
CREATE statement so that the dba has to make it explicit. It does
seems slightly silly since surely anyone creating a new object would
just paste in their grants from another object or some common source
anyways, but I suppose that's the way with convenience features.


-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] Query progress indication - an implementation

[Greg Stark]

 On Mon, 2009-06-29 at 14:07 -0400, Tom Lane wrote:
 I think this is pretty much nonsense --- most queries run all their plan
 nodes concurrently to some extent.  You can't usefully say that a query
 is on some node, nor measure progress by whether some node is done.

Right, that was why my proposed interface was to dump out the explain
plan with the number of loops, row counts seen so far, and approximate
percentage progress.

My thinking was that a human could interpret that to understand where
the bottleneck is if, say you're still on the first row for the top
few nodes but all the nodes below a certain sort have run to
completion that the query is busy running the sort...

But a tool like psql or pgadmin would receive that and just display
the top-level percent progress. pgadmin might actually be able to
display its graphical explain with some graphical representation of
the percent progress of each node.

We can actually do *very* well for percent progress for a lot of
nodes. Sequential scans or bitmap scans, for example, can display
their actual percent done in terms of disk blocks.

The gotcha I ran into was what to do with a nested loop join. The safe
thing to do would be to report just the outer child's percentage
directly. But that would perform poorly in the not uncommon case where
there's one expected outer tuple. If we could trust the outer estimate
we could report (outer-percentage + (1/outer-estimate *
inner-percentage)) but that will get weird quickly if the
outer-percentage turns out to be underestimated.

Basically I disagree that imperfect progress reports annoy users. I
think we can do better than reporting 250% done or having a percentage
that goes backward though. It would be quite tolerable (though perhaps
for no logical reason) to have a progress indicator which slows done
as it gets closer to 100% and never seems to make it to 100%.

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] Multi-Dimensional Histograms

[Nathan Boley]

 Finally, this creates the partition but ( AFAICT ) it doesn't describe
 a method for locating the histogram estimate given a point ( although
 that doesn't seem too difficult ).

 Is that not difficult, in terms of the math that needs doing, or
 not difficult, in terms of how well PostgreSQL is already set up to
 implement, or...?


I only meant that any implementation would need to address this, but I
can think of simple ways to do it ( for instance, use the fixed width
grid method, and then store a reference to all of the intersecting
boxes ). But I am sure that there are much better ways ( nested
containment lists perhaps? ).

-Nathan

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

Re: [HACKERS] Query progress indication - an implementation

[Ron Mayer]

Greg Stark wrote:

Right, that was why my proposed interface was to dump out the explain
plan with the number of loops, row counts seen so far, and approximate
percentage progress.

My thinking was that a human could interpret that to understand where
the bottleneck is if, say you're still on the first row for the top
few nodes but all the nodes below a certain sort have run to
completion that the query is busy running the sort...


+1.  Especially if I run it a few times and I can see which counters
are still moving.


Basically I disagree that imperfect progress reports annoy users. I
think we can do better than reporting 250% done or having a percentage
that goes backward though. It would be quite tolerable (though perhaps
for no logical reason) to have a progress indicator which slows done
as it gets closer to 100% and never seems to make it to 100%.


-1.A counter that slowly goes from 99% to 99.5% done is
much worse than a counter that takes the same much time
going from 1000% of estimated rows done to 2000% of
estimated rows done.

The former just tells me that it lies about how much is done.
The latter tells me that it's processing each row quickly but
that the estimate was way off.




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

Re: [HACKERS] Multi-Dimensional Histograms

[Nathan Boley]

On Mon, Jun 29, 2009 at 3:43 PM, Tom Lanet...@sss.pgh.pa.us wrote:
 David Fetter da...@fetter.org writes:
 On Mon, Jun 29, 2009 at 01:28:01PM -0700, Nathan Boley wrote:
 ... They dismiss
 singular value decomposition and the discrete wavelet transform as
 being too parametric ( which is silly, IMHO )

 Should we have a separate discussion about eigenvalues?  Wavelets?

 I think it'd be a short discussion: what will you do with non-numeric
 datatypes? We probably don't really want to assume anything stronger
 than that the datatype has a total ordering.

Well, in the general case, we could use their ranks.

At the end of the day, we cant do any dimension reduction unless the
ordering encodes some sort of useful information, and the data type
being in R^n is certainly no guarantee. Consider, for instance, the
cross correlation of zip-codes and area codes - you would really want
to order those by some geographic relation. I think that is why
cross-column stats is so hard in the general case.

That being said, for geographic data in particular, PCA or similar
could work well.

-Nathan

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

Re: [HACKERS] pre-proposal: permissions made easier

[Josh Berkus]

Greg,


And there's I just created a new table, I want www and
www-backend to get their usual privileges without thinking about it.
You want to be able to specify default grants that an object gets
based on the schema? That seems mostly reasonable though it might be a
good idea to have a WITH DEFAULT GRANTS or something like that on the
CREATE statement so that the dba has to make it explicit.


Well, the idea is *user and schema*, not schema alone.  I think Jeff's 
proposal for users was user alone, unmodified by schema.  I'd prefer to 
reverse the switch (i.e. NO DEFAULT GRANTS) just because I'd like 
default grants to work with ORMs and similar.


In other words, my/stephen's proposal amounts to the idea that objects 
in a schema should, by default, be able to inherit permissions from 
their schema at creation time.


It does
 seems slightly silly since surely anyone creating a new object would
 just paste in their grants from another object or some common source
 anyways, but I suppose that's the way with convenience features.

That works fine until you have 6 (or more) defined roles and a couple 
hundred objects, and are in a agile environment where the dev team is 
constantly adding objects which have the wrong permissions.  That's 
whose problem I'm trying to solve (because they're my clients).


--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

[HACKERS] How to register my function into backend?

[Bruce YUAN]

Hi All,

My function is to collect some backend information for user anlysis.
How to register my function into backend? It make that we can called it via
libpg/PQfn().
Thanks!

Best regards,
Bruce

Re: [HACKERS] Query progress indication - an implementation

[Robert Haas]

On Mon, Jun 29, 2009 at 8:15 PM, Ron Mayerrm...@cheapcomplexdevices.com wrote:
 Greg Stark wrote:

 Right, that was why my proposed interface was to dump out the explain
 plan with the number of loops, row counts seen so far, and approximate
 percentage progress.

 My thinking was that a human could interpret that to understand where
 the bottleneck is if, say you're still on the first row for the top
 few nodes but all the nodes below a certain sort have run to
 completion that the query is busy running the sort...

 +1.  Especially if I run it a few times and I can see which counters
 are still moving.

+1 from me, too, as I said upthread.

 Basically I disagree that imperfect progress reports annoy users. I
 think we can do better than reporting 250% done or having a percentage
 that goes backward though. It would be quite tolerable (though perhaps
 for no logical reason) to have a progress indicator which slows done
 as it gets closer to 100% and never seems to make it to 100%.

 -1.    A counter that slowly goes from 99% to 99.5% done is
 much worse than a counter that takes the same much time
 going from 1000% of estimated rows done to 2000% of
 estimated rows done.

 The former just tells me that it lies about how much is done.
 The latter tells me that it's processing each row quickly but
 that the estimate was way off.

I think both of those options are a little wacky.  Maybe 800% **of
estimated rows done** is not so bad, since the tag line provides some
context, but what does it mean exactly?  Rows for the toplevel plan
node?  That doesn't seem like it would always be too useful.  I keep
coming back to thinking you need to see the whole tree.

...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] How to register my function into backend?

[Robert Haas]

On Mon, Jun 29, 2009 at 4:53 AM, Bruce YUANsua...@gmail.com wrote:
 My function is to collect some backend information for user anlysis.
 How to register my function into backend? It make that we can called it via
 libpg/PQfn().
 Thanks!

I'm not sure this is -hackers question; seems like -general might be
more appropriate.

I think you are looking for CREATE OR REPLACE FUNCTION.

http://www.postgresql.org/docs/current/static/sql-createfunction.html
http://www.postgresql.org/docs/current/static/xfunc.html

...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] Multi-Dimensional Histograms

[Robert Haas]

On Mon, Jun 29, 2009 at 8:17 PM, Nathan Boleynpbo...@gmail.com wrote:
 On Mon, Jun 29, 2009 at 3:43 PM, Tom Lanet...@sss.pgh.pa.us wrote:
 David Fetter da...@fetter.org writes:
 On Mon, Jun 29, 2009 at 01:28:01PM -0700, Nathan Boley wrote:
 ... They dismiss
 singular value decomposition and the discrete wavelet transform as
 being too parametric ( which is silly, IMHO )

 Should we have a separate discussion about eigenvalues?  Wavelets?

 I think it'd be a short discussion: what will you do with non-numeric
 datatypes? We probably don't really want to assume anything stronger
 than that the datatype has a total ordering.

 Well, in the general case, we could use their ranks.

 At the end of the day, we cant do any dimension reduction unless the
 ordering encodes some sort of useful information, and the data type
 being in R^n is certainly no guarantee. Consider, for instance, the
 cross correlation of zip-codes and area codes - you would really want
 to order those by some geographic relation. I think that is why
 cross-column stats is so hard in the general case.

 That being said, for geographic data in particular, PCA or similar
 could work well.

I'm finding myself unable to follow all the terminology on this thead.
 What's dimension reduction?  What's PCA?

Based on my last few months of answering questions on -performance,
and my own experience, it seems like a lot of the cases that arise in
practice are those where there is a WHERE clause of the form:

colA = constA and colB op constB

...and it sometimes turns out that the subset of the data where colA =
constA has a very different distribution for colB than the data as a
whole, leading to bad plans.  In many cases, it seems like colA is
storing some discrete type of thing, like a customer ID, so the
distribution of colB where colA = constA tells you nothing about the
distribution of colB where colA = constA + someSmallDeltaA.  It feels
like what you might need is statistics for colB (MCVs and/or a
histogram) for certain particular values of colA.  Unfortunately, in
the general case the set of values of colA for which you need these
statistics might be inconveniently large.

...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] [PATCH] [v8.5] Security checks on largeobjects

[KaiGai Kohei]

Tom Lane wrote:
 Bernd Helmle maili...@oopsware.de writes:
 It might be interesting to dig into your proposal deeper in conjunction 
 with TOAST (you've already mentioned this TODO). Having serial access with 
 a nice interface into TOAST would be eliminating the need for 
 pg_largeobject completely (i'm not a big fan of this one-big-system-table 
 approach the old LO interface currently is).
 
 Yeah, it would be more useful probably to fix that than to add
 decoration to the LO facility.  Making LO more usable is just going to
 encourage people to bump into its other limitations (32-bit OIDs,
 32-bit object size, finite maximum size of pg_largeobject, lack of
 dead-object cleanup, etc etc).

The reason why I tried to mention the named largeobject feature is
that dac security checks on largeobject require them to belong to
a certain schema, so I thought it is quite natural to have a string
name. However, obviously, it is not a significant theme for me.

I can also agree your opinion that largeobject interfaces should be
redefined to access partial stuff of TOAST'ed verlena data structure,
not only pg_largeobject.

In this case, we will need a new pg_type.typstorage option which
force to put the given verlena data on external relation without
compression, because we cannot estimate the data offset in inlined
or compressed external verlena data.

I'll try to submit a design within a few days.
Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.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] pre-proposal: permissions made easier

[Greg Stark]

On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkusj...@agliodbs.com wrote:

It does
 seems slightly silly since surely anyone creating a new object would
 just paste in their grants from another object or some common source
 anyways, but I suppose that's the way with convenience features.

 That works fine until you have 6 (or more) defined roles and a couple
 hundred objects, and are in a agile environment where the dev team is
 constantly adding objects which have the wrong permissions.  That's whose
 problem I'm trying to solve (because they're my clients).

Well I don't understand how you get them wrong if you're just pasting
them from a file. I mean, sure you can pick the wrong template but
nothing can help you there. You could just as easily pick the wrong
template if it's a database feature instead of a text file.

Agile doesn't mean doing things without thinking about them :)

-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] pre-proposal: permissions made easier

[Josh Berkus]

Greg,


Well I don't understand how you get them wrong if you're just pasting
them from a file. I mean, sure you can pick the wrong template but
nothing can help you there. You could just as easily pick the wrong
template if it's a database feature instead of a text file.


I really have to wonder if you've ever managed a production database 
project.


As someone who has managed quite a few, my idea of the feature is 
designed to make my life (and my clients') easier.  It's *vastly* easier 
to tell developers don't touch the permissions, it will take care of 
itself and set it in a central location than to expect them to remember 
to apply a set of permissions each time, or follow them around playing 
catch-up on the objects they add and modify.


Oracle, MSSQL, etc. all have management solutions for this.  For a good 
reason.



Agile doesn't mean doing things without thinking about them :)


In many companies it does.  :-(

--
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

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

Re: [HACKERS] pre-proposal: permissions made easier

[David Fetter]

On Tue, Jun 30, 2009 at 04:24:40AM +0100, Greg Stark wrote:
 On Tue, Jun 30, 2009 at 1:51 AM, Josh Berkusj...@agliodbs.com wrote:
 
  It does seems slightly silly since surely anyone creating a new
  object would just paste in their grants from another object or
  some common source anyways, but I suppose that's the way with
  convenience features.
 
  That works fine until you have 6 (or more) defined roles and a
  couple hundred objects, and are in a agile environment where the
  dev team is constantly adding objects which have the wrong
  permissions.  That's whose problem I'm trying to solve (because
  they're my clients).
 
 Well I don't understand how you get them wrong if you're just
 pasting them from a file. I mean, sure you can pick the wrong
 template but nothing can help you there. You could just as easily
 pick the wrong template if it's a database feature instead of a text
 file.
 
 Agile doesn't mean doing things without thinking about them :)

It does in a lot of shops ;)

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

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

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

Re: [HACKERS] pre-proposal: permissions made easier

[Greg Stark]

On Tue, Jun 30, 2009 at 4:39 AM, Josh Berkusj...@agliodbs.com wrote:


 Well I don't understand how you get them wrong if you're just pasting
 them from a file. I mean, sure you can pick the wrong template but
 nothing can help you there. You could just as easily pick the wrong
 template if it's a database feature instead of a text file.

 I really have to wonder if you've ever managed a production database
 project.

 As someone who has managed quite a few, my idea of the feature is designed
 to make my life (and my clients') easier.  It's *vastly* easier to tell
 developers don't touch the permissions, it will take care of itself and
 set it in a central location than to expect them to remember to apply a set
 of permissions each time, or follow them around playing catch-up on the
 objects they add and modify.

But that's not what we're talking about and there's no way they can
just take care of themselves. The database isn't a mind-reader and
can't know whether this new table is supposed to have the public web
data permission template or the sensitive data permission template.

You can put it in the wrong schema and get the wrong default
permission just as easily as you can choose the wrong text template to
paste into your database creation script.

I'm not saying it's a bad idea to have some sort of short cut for the
default permissions. Actually it sounds like it would lend itself to
the good code practice of being self-documenting which makes it easier
to see that which template's being used which is sounds like quite a
good thing. But you do still have to think carefully about that
choice.

Perhaps tieing it to the schema is wrong and we should actually
require the user to specify the template they want explicitly which
would be even better for that. So it would be something like WITH
GRANTS LIKE sensitive_table.




-- 
greg
http://mit.edu/~gsstark/resume.pdf

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

Re: [HACKERS] Multi-Dimensional Histograms

[Joshua Tolley]

On Mon, Jun 29, 2009 at 10:22:15PM -0400, Robert Haas wrote:
 I'm finding myself unable to follow all the terminology on this thead.
  What's dimension reduction? 

For instance, ask a bunch of people a bunch of survey questions, in hopes of
predicting some value (for instance, whether or not these people will
die of a heart attack in the next three years). After waiting three years,
discover that some of the questions didn't really help you predict the
outcome. Remove them from the dataset. That's dimension reduction.

 What's PCA?

Principal Component Analysis,
http://en.wikipedia.org/wiki/Principal_components_analysis

--
Josh / eggyknap
End Point, Corp.
http://www.endpoint.com


signature.asc
Description: Digital signature

[HACKERS] foreign.h is not installed

[Itagaki Takahiro]

Header file include/foreign/foreign.h is not installed in 8.4.0.

Did we forget to add subdir foreign to Makefile?

Index: src/include/Makefile
===
--- src/include/Makefile(8.4.0)
+++ src/include/Makefile(fixed)
@@ -17,7 +17,7 @@
 
 
 # Subdirectories containing headers for server-side dev
-SUBDIRS = access bootstrap catalog commands executor lib libpq mb \
+SUBDIRS = access bootstrap catalog commands executor foreign lib libpq mb \
nodes optimizer parser postmaster regex rewrite storage tcop \
snowball snowball/libstemmer tsearch tsearch/dicts utils \
port port/win32 port/win32_msvc port/win32_msvc/sys \


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] Query progress indication - an implementation

[Dimitri Fontaine]

Le 30 juin 2009 à 01:34, Greg Stark gsst...@mit.edu a écrit :

Basically I disagree that imperfect progress reports annoy users. I
think we can do better than reporting 250% done or having a percentage
that goes backward though. It would be quite tolerable (though perhaps
for no logical reason) to have a progress indicator which slows done
as it gets closer to 100% and never seems to make it to 100%.


I guess bad stats are such an important problem in planning queries  
that a 250% progress is doing more good than harm in showing users how  
badly they need to review their analyze related settings.


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

[HACKERS] 8.5 development schedule

[Peter Eisentraut]

Now that 8.4.0 is out the door, development for 8.5devel will be opened any 
day now.  But we haven't discussed the development timeline so far.  The core 
team has several proposals:

CommitFest  Alpha
Aug. 1  Sept. 1
Oct. 1  Nov. 1
Dec. 1  Jan ~~ 5
Feb. 1  March 4

Release ~ May 2010

This puts us on track for a release at the same time next year, maybe a little 
earlier.

(Alpha is a semiformal snapshot release at the end of the commitfest, for 
those who haven't heard yet.  Details later.)

If we want to avoid a commitfest in December, then this:

CommitFest  Alpha
Sept. 1 Oct. 1
Nov. 1  Dec. 1
Jan. 1  Feb 1
March 1 April 2

Release ~ June 2010

But this has the drawback of waiting an extra month for the first commit fest, 
for no particularly good reason.  (Check the current list, if you are 
curious.)

Or, one more commitfest:

CommitFest  Alpha
Aug. 1  Sept. 1
Oct. 1  Nov. 1
Dec. 1  Jan ~~ 5
Feb. 1  March 3
April 3 May 3

Release ~ July 2010

But that gets 8.5 out even later than this year, and past PGCon.

Comments?


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