Re: [HACKERS] .ini support for .pgpass
On Tue, Apr 05, 2011 at 11:55:04PM -0700, Joshua D. Drake wrote: > I want to achieve two things: > > 1. More understandable .pgpass format. Yes, I understand our standard > format, most people won't. Like JoshB said, hard to debug. This I understand. > 2. psql foo, gets me into foo. A macro for connections if you will. But this is precisely what the service file achieves, right? ISTM what you d like is to be able to specify the password in the service file, in which case pgpass is not consulted. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle signature.asc Description: Digital signature
Re: [HACKERS] .ini support for .pgpass
On Wed, Apr 6, 2011 at 9:55 AM, Joshua D. Drake wrote: > I want to achieve two things: > > 1. More understandable .pgpass format. Yes, I understand our standard > format, most people won't. Like JoshB said, hard to debug. How about allowing '#'-comments there and putting field list into all templates, examples and manpages? man 5 pgpass? pgpass.sample? IOW, do we need to change format or are we having documentation problem? -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
Well at the minute I am trying to find out sections of postgresql that can be helpful to carry out a database forensics analysis and thought the most useful with be the transaction log. So was actually interested in viewing it. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286040.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC Proposal - Caching query results in pgpool-II
How does this relate to the existing pqc project (
http://code.google.com/p/pqc/)? Seems the goals are fairly similar, and both
are based off pgpool?
/Magnus
On Apr 6, 2011 2:10 AM, "Masanori Yamazaki" wrote:
> Hello
>
> My name is Masanori Yamazaki. I am sending my proposal about
> Google Summer Of Code2011. It would be nice if you could give
> me your opinion.
>
>
> ・title
>
> Caching query results in pgpool-II
>
>
> ・Synopsis
>
> Pgpool-II has query caching functionality using storage provided by
> dedicated PostgreSQL ("system database"). This has several drawbacks
> however. 1)it is slow because it needs to access disk storage 2)it
> does not invalidate the cache automatically.
>
> This proposal tries to solve these problems.
>
> - To speed up the cache access, it will be placed on memory, rather
> than database. The memory will be put on shared memory or external
> memory services such as memcached so that the cache can be shared by
> multiple sessions. Old cache entries will be deleted by LRU manner.
>
> - The cache will be invalidated automatically upon the timing when the
> relevant tables are updated. Note that this is not always possible
> because the query result might come from multiple tables, views or
> even functions. In this case the cache will be invalidated by
> timeout(or they are not cached at all).
>
> - Fine tuning knobs need to be invented to control the cache behavior
> though they are not clear at this moment.
>
>
> ・Benefits to the PostgreSQL Community:
>
>
> Query caching will effectively enhance the performance of PostgreSQL
> and this project will contribute to increase the number of users of
> PostgreSQL, who need more high performance database systems.
>
> Note that implementing query cache in pgpool-II will bring merits not
> only to the latest version of PostgreSQL but to the previous releases
> of PostgreSQL.
>
>
> ・Project Schedule
>
> -April
> preparation
>
> -May 1 - May 22
> write a specification
>
> -May 23 - June 19
> coding
>
> -June 20 - July 22
> test
>
> -July 23 - August 12
> complete of coding and test, commit
>
>
> ・Personal Data and Biographical Information
>
> Name : Masanori Yamazaki
> Born : 23.1.1981
> School :Currently I learn contemporary philosophy, culture and literature
> at Waseda University in Japan.
> Coding :
> 1.About five years job as web application programer(PHP, Java).
> 2.I experienced projects used framework such as Symfony, Zend Framework,
> CakePHP, and Struts.
> 3.I am interested in OSS and like coding.
>
>
> Regards
[HACKERS] Postgresql on multi-core CPU's: is this old news?
Came across the following in a paper from Oct 2010. Was wondering is this is old news I missed in this group. http://pdos.csail.mit.edu/papers/linux:osdi10.pdf about Linux optimization on multi-core CPU's. The group at MIT were exploring how some Linux apps were scaling up --- sometimes badly, mostly due to hidden contention over cache-line consistency across the cores' caches. In a nutshell: if an app, or the system calls it uses, tries to modify anything in a cache line (32-64 byte slice of memory) that another core is using, there's a lot of fumbling in the dark to make sure there is no conflict. When I saw PostgreSQL named in the abstract, I thought, "Aha! Contention over shm". Not so. Skip to page 11 (section 5.5) for most of the PG specifics.
[HACKERS] GSoC Proposal - Caching query results in pgpool-II
Hello
I am sending my proposal about Google Summer Of Code2011.
It would be nice if you could give me your opinion.
・title
Caching query results in pgpool-II
・Synopsis
Pgpool-II has query caching functionality using storage provided by
dedicated PostgreSQL ("system database"). This has several drawbacks
however. 1)it is slow because it needs to access disk storage 2)it
does not invalidate the cache automatically.
This proposal tries to solve these problems.
- To speed up the cache access, it will be placed on memory, rather
than database. The memory will be put on shared memory or external
memory services such as memcached so that the cache can be shared by
multiple sessions. Old cache entries will be deleted by LRU manner.
- The cache will be invalidated automatically upon the timing when the
relevant tables are updated. Note that this is not always possible
because the query result might come from multiple tables, views or
even functions. In this case the cache will be invalidated by
timeout(or they are not cached at all).
- Fine tuning knobs need to be invented to control the cache behavior
though they are not clear at this moment.
・Benefits to the PostgreSQL Community:
Query caching will effectively enhance the performance of PostgreSQL
and this project will contribute to increase the number of users of
PostgreSQL, who need more high performance database systems.
Note that implementing query cache in pgpool-II will bring merits not
only to the latest version of PostgreSQL but to the previous releases
of PostgreSQL.
・Project Schedule
-April
preparation
-May 1 - May 22
write a specification
-May 23 - June 19
coding
-June 20 - July 22
test
-July 23 - August 12
Complete of coding and test, commit
・Personal Data and Biographical Information
Name : Masanori Yamazaki
Born : 23.1.1981
School :Currently I learn contemporary philosophy, culture and literature
at Waseda University in Japan.
Coding :
1.About five years job as web application programer(PHP, Java).
2.I experienced projects used framework such as Symfony, Zend Framework,
CakePHP, and Struts.
3.I am interested in OSS and like coding.
Regards
Re: [HACKERS] [BUGS] Non Win/*nix UTF-8 codepage not known to PostgreSQL developers?!
On 05.04.2011 20:11, Jan-Erik Lärka wrote: Yes, it's the successor to OS/2, eComStation. We don't currently have anyone active in the community running on that platform, so I'm reluctant to add those codepage aliases as I won't be able to test it, and we don't support OS/2 anyway. But if you're interested to set up a buildfarm member on OS/2, and write a patch to do the required codepage changes, I'm happy to review and apply it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] getting to beta
A quick review of the open items list suggests that we have three main areas that need attention before we can declare ourselves ready for beta. In no particular order: 1. There are a bunch of small, outstanding SSI patches. 2. Bugs - plural - related to pg_upgrade & typed tables. 3. Assorted collation issues. There are a couple of smaller items, too, but those are the big ones. Per previous discussion, the viable dates for code freeze for beta1 appear to be April 14th and April 28th. If we want to hit the earlier of those dates, which in my opinion would be a great goal to have, then we need to get all of the above issues resolved in the next 8 days, and I think we're going to need to kick it up a notch if we want that to happen. Most urgently, I believe we need a bit more committer bandwidth. I believe that I could tackle either the SSI patches or the pg_upgrade & typed tables issue, or I could try to make a dent in the collation stuff, but I don't think I can cover two of those areas and I definitely can't cover all three. Especially in the area of SSI, and to some extent as regards typed tables, the patches are written, but we have to get them reviewed and committed. Is anyone available to help with this? There are also a few issues where we need a patch and don't have one. In those cases the patches could be written by either a committer or a non-committer, but we need to make sure we know who is doing it so that everything gets covered. In particular: - SSI needs patch for the issue "SSI: three different HTABs contend for shared memory in a free-for-all" - typed tables needs a patch to allow an existing table to be made into a typed table, and pg_dump --binary-upgrade needs to be made to use that feature - the open collation issues all lack any associated code (but maybe Tom is planning to do this himself?) The other minor issues are: - do latches have memory ordering problems? I think the consensus is that they work OK the way we're using them right now, so maybe we can just drop this item, unless someone wants to pontificate further on it. - sync rep & smart shutdown - someone needs to review & apply Fujii Masao's proposed patch - generate_series boundary issue - I think this isn't a new regression so it's probably not a blocker for beta1, but we might still want to try to fix it. I seem to remember thinking that the prototype patch looked like it needed pretty significant cleanup, but I haven't looked at it in a while so I might be all wet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
On Wed, Apr 6, 2011 at 6:49 AM, aaronenabs wrote: > Well at the minute I am trying to find out sections of postgresql that can be > helpful to carry out a database forensics analysis and thought the most > useful with be the transaction log. So was actually interested in viewing > it. That's pretty tough to do, unfortunately. Depending on the situation, you might want to investigate pg_filedump. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgresql on multi-core CPU's: is this old news?
On Tue, Apr 5, 2011 at 2:21 PM, Mischa Sandberg wrote: > Came across the following in a paper from Oct 2010. Was wondering is this is > old news I missed in this group. > > http://pdos.csail.mit.edu/papers/linux:osdi10.pdf > > about Linux optimization on multi-core CPU’s. > > The group at MIT were exploring how some Linux apps were scaling up --- > sometimes badly, mostly due to hidden contention over cache-line consistency > across the cores’ caches. > > In a nutshell: if an app, or the system calls it uses, tries to modify > anything in a cache line (32-64 byte slice of memory) that another core is > using, there’s a lot of fumbling in the dark to make sure there is no > conflict. When I saw PostgreSQL named in the abstract, I thought, “Aha! > Contention over shm”. Not so. Skip to page 11 (section 5.5) for most of the > PG specifics. Someone posted this before, but unfortunately making this really work in PG is more of a research project than something we can just go do. I made a stab at writing a spinlock-free version of the LWLock code a few months ago (which is one of the things they did in the paper) and I wasn't able to show a lick of benefit. Part of that may be because I didn't have access to anything bigger than an 8-core box, but it's also because these things are fairly workload-dependent. In the test cases I tried I kept bottlenecking on WALInsertLock or, on read-only workloads, the lock manager partition lock for whichever table I was hitting, and the changes they made don't address those bottlenecks. As they write - regarding their benchmark - "This workload is intended to minimize application-level contention within PostgreSQL in order to maximize the stress PostgreSQL places on the kernel." -- i.e. PostgreSQL wasn't really the thing they were trying to stress. It's interesting stuff - I'm just not sure how much near-term practical benefit we can get out of it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas writes: > ... Most urgently, I believe we need a bit more committer bandwidth. I > believe that I could tackle either the SSI patches or the pg_upgrade & > typed tables issue, or I could try to make a dent in the collation > stuff, but I don't think I can cover two of those areas and I > definitely can't cover all three. I intend to return to the collations issues as soon as I've knocked off the GUC assign-hooks patch. That's taking longer than I thought (there are a *lot* of assign hooks) but I think I'll be able to finish it today or tomorrow. I have yet to read any of the SSI code, so I can't offer much help in that area. > The other minor issues are: > - do latches have memory ordering problems? I think the consensus is > that they work OK the way we're using them right now, so maybe we can > just drop this item, unless someone wants to pontificate further on > it. I think this can be left as an open issue for now, to remind us that some harder stress-testing on affected platforms would be a good thing. > - generate_series boundary issue - I think this isn't a new regression > so it's probably not a blocker for beta1, but we might still want to > try to fix it. Again, there's no reason that can't stay on the open items list past beta1. We may or may not choose to fix it for 9.1, but it's not a beta blocker. 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] .ini support for .pgpass
Marko Kreen writes: > On Wed, Apr 6, 2011 at 9:55 AM, Joshua D. Drake > wrote: >> 1. More understandable .pgpass format. Yes, I understand our standard >> format, most people won't. Like JoshB said, hard to debug. > How about allowing '#'-comments there and putting field > list into all templates, examples and manpages? > man 5 pgpass? > pgpass.sample? > IOW, do we need to change format or are we having documentation problem? +1 for seeing this as a documentation problem. I don't think that converting the file to .ini style is going to somehow make it magically easier to use --- people still have to understand it, and frankly .ini format is just another format that not everyone knows. #-comments seem like a fine idea. 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] Transaction log
True, i have looked at pg_dumpfile and worked around that, Seems to be a very important tool for forensic investigations. But looking for any other aspect of the DBMS that can be helpful. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286318.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Can I check if somebody is superuser in stored procedure?
On Mar 28, 2011, at 1:29 AM, Pavel Stehule wrote: >>> Is there some simple possibility to check a rights from stored procedure? >> >> Well, there's the catalog lookup method: >> >> SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname=$1 AND >> rolsuper) >> >> Is that what you had in mind? > > I found this too, but it isn't what I searched - I searched a some > exported function based on internal cache. > > For my purpose is this solution enough. Note that doesn't work if the user has superuser because it was granted via another role. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on system catalog
On Mar 30, 2011, at 3:45 PM, Jan Wieck wrote: > What I would envision for DDL triggers is that they first don't fire on an > object type, but rather on a command completion code, like "CREATE TABLE" or > "DROP SCHEMA". > > To do anything useful with that of course would require that all DDL does go > through tcop's ProcessUtility and actually synthesizes a proper Utility > parsetree. That isn't the case today, so there would be some previous clean > up work to be done. For those that missed my announcement at PG East; we're willing to sponsor some/all of this work (depending on how much money we're talking). -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Wed, Apr 6, 2011 at 9:42 AM, Tom Lane wrote: > Robert Haas writes: >> ... Most urgently, I believe we need a bit more committer bandwidth. I >> believe that I could tackle either the SSI patches or the pg_upgrade & >> typed tables issue, or I could try to make a dent in the collation >> stuff, but I don't think I can cover two of those areas and I >> definitely can't cover all three. > > I intend to return to the collations issues as soon as I've knocked off > the GUC assign-hooks patch. That's taking longer than I thought (there > are a *lot* of assign hooks) but I think I'll be able to finish it today > or tomorrow. I have yet to read any of the SSI code, so I can't offer > much help in that area. > >> The other minor issues are: > >> - do latches have memory ordering problems? I think the consensus is >> that they work OK the way we're using them right now, so maybe we can >> just drop this item, unless someone wants to pontificate further on >> it. > > I think this can be left as an open issue for now, to remind us that > some harder stress-testing on affected platforms would be a good thing. OK, fair enough. >> - generate_series boundary issue - I think this isn't a new regression >> so it's probably not a blocker for beta1, but we might still want to >> try to fix it. > > Again, there's no reason that can't stay on the open items list past > beta1. We may or may not choose to fix it for 9.1, but it's not a beta > blocker. I agree. But again, that's not really what I'm focusing on - the collations stuff, the typed tables patch, and SSI all need serious looking at, and I'm not sure who is going to pick all that up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
On Wed, Apr 6, 2011 at 9:48 AM, aaronenabs wrote: > True, i have looked at pg_dumpfile and worked around that, Seems to be a very > important tool for forensic investigations. But looking for any other aspect > of the DBMS that can be helpful. pageinspect is useful. Also there are hidden xmin and xmax columns in every tuple which can be helpful also. Unfortunately there's no way to shut off visibility checks and see the tuples that MVCC is filtering out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas writes: > I agree. But again, that's not really what I'm focusing on - the > collations stuff, the typed tables patch, and SSI all need serious > looking at, and I'm not sure who is going to pick all that up. Well, I'll take responsibility for collations. If I get done with that before the 14th, I can see what's up with typed tables. I'm not willing to do anything with SSI at this stage. 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] Transaction log
Thanks for that information would look into the xmin and xmax columns. so its not possible to turn the HeapTupleVisiblity to true to view dead tuples by setting it to #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1) -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286515.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Transaction log
On Wed, Apr 6, 2011 at 11:13 AM, aaronenabs wrote: > Thanks for that information would look into the xmin and xmax columns. > > so its not possible to turn the HeapTupleVisiblity to true to view dead > tuples by setting it to > > #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1) Well, if you change the source code you can certainly do it... though I'm not sure that's exactly the right incantation. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Tue, Apr 05, 2011 at 09:44:44AM -0400, Robert Haas wrote: > On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch wrote: > > On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote: > >> Here is a patch that addresses this problem. > > > > This only works when exactly one typed table uses each composite type having > > dropped columns. ?With zero users, the placeholder column never gets > > dropped. > > Actually, it happens to work for >1 user, but only because ALTER TYPE > > mistakenly > > only touches the first table-of-type: > > > > create type t as (x int, y int); > > create table is_a of t; > > create table is_a2 of t; > > alter type t drop attribute y cascade, add attribute z int cascade; > > \d is_a > > ? ? Table "public.is_a" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?z ? ? ?| integer | > > Typed table of type: t > > \d is_a2 > > ? ? Table "public.is_a2" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?y ? ? ?| integer | > > Typed table of type: t > > > > Might be a simple fix; looks like find_typed_table_dependencies() only > > grabs the > > first match. ?Incidentally, this led me to notice that you can hang a typed > > table off a table row type. ?ALTER TABLE never propagates to such typed > > tables, > > allowing them to get out of sync: > > > > create table t (x int, y int); > > create table is_a of t; > > create table is_a2 of t; > > alter table t drop y, add z int; > > \d is_a > > ? ? Table "public.is_a" > > ?Column | ?Type ? | Modifiers > > +-+--- > > ?x ? ? ?| integer | > > ?y ? ? ?| integer | > > Typed table of type: t > > > > Perhaps we should disallow the use of table row types in CREATE TABLE ... > > OF? > > > >> It looks like Noah Misch might have found another problem in this area. > >> We'll have to investigate that. > > > > Your bits in dumpCompositeType() are most of what's needed to fix that, I > > think. > > Where are we on this? Peter, were you planning to complete this? I can take a swing at it, if it would be helpful. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On 06.04.2011 18:02, Tom Lane wrote: Robert Haas writes: I agree. But again, that's not really what I'm focusing on - the collations stuff, the typed tables patch, and SSI all need serious looking at, and I'm not sure who is going to pick all that up. Well, I'll take responsibility for collations. If I get done with that before the 14th, I can see what's up with typed tables. I'm not willing to do anything with SSI at this stage. I can look at the SSI patches, but not until next week, I'm afraid. Robert, would you like to pick that up before then? Kevin & Dan have done all the heavy lifting, but it's nevertheless pretty complicated code to review. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Wed, Apr 6, 2011 at 12:06 PM, Heikki Linnakangas wrote: > On 06.04.2011 18:02, Tom Lane wrote: >>> I agree. But again, that's not really what I'm focusing on - the >>> collations stuff, the typed tables patch, and SSI all need serious >>> looking at, and I'm not sure who is going to pick all that up. >> >> Well, I'll take responsibility for collations. If I get done with that >> before the 14th, I can see what's up with typed tables. I'm not willing >> to do anything with SSI at this stage. > > I can look at the SSI patches, but not until next week, I'm afraid. Robert, > would you like to pick that up before then? Kevin & Dan have done all the > heavy lifting, but it's nevertheless pretty complicated code to review. I'll try, and see how far I get with it. If you can pick up whatever I don't get to by early next week, that would be a big help. I am going to be in Santa Clara next week for the MySQL conference (don't worry, I'll be talking about PostgreSQL!) and that's going to cut into my time quite a bit. The one I'm most worried about is "SSI: three different HTABs contend for shared memory in a free-for-all" - because there's no patch for that yet, and I am wary of breaking something mucking around with it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas wrote: > Heikki Linnakangas wrote: >> I can look at the SSI patches, but not until next week, I'm >> afraid. Robert, would you like to pick that up before then? Kevin >> & Dan have done all the heavy lifting, but it's nevertheless >> pretty complicated code to review. > > I'll try, and see how far I get with it. If you can pick up > whatever I don't get to by early next week, that would be a big > help. I am going to be in Santa Clara next week for the MySQL > conference (don't worry, I'll be talking about PostgreSQL!) and > that's going to cut into my time quite a bit. The one I'm most > worried about is "SSI: three different HTABs contend for shared > memory in a free-for-all" - because there's no patch for that yet, > and I am wary of breaking something mucking around with it. I haven't seen any objection to Heikki's suggestion for how to handle the shared memory free-for-all: http://archives.postgresql.org/message-id/4d94c889.3050...@enterprisedb.com Either Dan or I will put something together along those lines before next week. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
"Kevin Grittner" writes: > Robert Haas wrote: >> ... The one I'm most >> worried about is "SSI: three different HTABs contend for shared >> memory in a free-for-all" - because there's no patch for that yet, >> and I am wary of breaking something mucking around with it. > I haven't seen any objection to Heikki's suggestion for how to > handle the shared memory free-for-all: I confess to not having been reading the discussions about SSI very much, but ... do we actually care whether there's a free-for-all? What's the downside to letting the remaining shmem get claimed by whichever table uses it first? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On 06.04.2011 17:46, Tom Lane wrote: "Kevin Grittner" writes: Robert Haas wrote: ... The one I'm most worried about is "SSI: three different HTABs contend for shared memory in a free-for-all" - because there's no patch for that yet, and I am wary of breaking something mucking around with it. I haven't seen any objection to Heikki's suggestion for how to handle the shared memory free-for-all: I confess to not having been reading the discussions about SSI very much, but ... do we actually care whether there's a free-for-all? What's the downside to letting the remaining shmem get claimed by whichever table uses it first? It's leads to odd behavior. You start the database, and your application runs fine. Then you restart the database, and now you get "out of shared memory" errors from transactions that used to work. It's not the end of the world, but I'd prefer stable, repeatable behavior, even though having the slack shared memory be grabbed by whoever needs it first might in theory lead to better utilization of resources. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On 6 April 2011 17:57, Heikki Linnakangas wrote: > On 06.04.2011 17:46, Tom Lane wrote: >> >> "Kevin Grittner" writes: >>> >>> Robert Haas wrote: ... The one I'm most worried about is "SSI: three different HTABs contend for shared memory in a free-for-all" - because there's no patch for that yet, and I am wary of breaking something mucking around with it. >> >>> I haven't seen any objection to Heikki's suggestion for how to >>> handle the shared memory free-for-all: >> >> I confess to not having been reading the discussions about SSI very >> much, but ... do we actually care whether there's a free-for-all? >> What's the downside to letting the remaining shmem get claimed by >> whichever table uses it first? > > It's leads to odd behavior. You start the database, and your application > runs fine. Then you restart the database, and now you get "out of shared > memory" errors from transactions that used to work. > > It's not the end of the world, but I'd prefer stable, repeatable behavior, > even though having the slack shared memory be grabbed by whoever needs it > first might in theory lead to better utilization of resources. It sounds a bit apocalyptic to me, if that really is happening. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
Robert Haas writes: > On Wed, Apr 6, 2011 at 11:13 AM, aaronenabs wrote: >> Thanks for that information would look into the xmin and xmax columns. >> >> so its not possible to turn the HeapTupleVisiblity to true to view dead >> tuples by setting it to >> >> #define HeapTupleSatisfiesVisibility(tuple, snapshot, buffer)(1) > Well, if you change the source code you can certainly do it... though > I'm not sure that's exactly the right incantation. You could do that, but you'd immediately find that the entire system comes crashing down around your ears: most of the time you *do not want* to see dead tuples, especially not in system catalogs. Even simple matters like creating a table with an index will fail, if both the original and updated versions of its pg_class tuple are visible. The hard part of doing something like this is arranging to make visible only the tuples you want to see, and only when you want to see them. Maybe you could have a GUC switch that specifies a particular (non-system) table in which to show dead tuples. The other hard part is preventing stuff like autovacuum and page compaction from destroying dead tuples that you still want to see. 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] getting to beta
Heikki Linnakangas writes: > On 06.04.2011 17:46, Tom Lane wrote: >> I confess to not having been reading the discussions about SSI very >> much, but ... do we actually care whether there's a free-for-all? >> What's the downside to letting the remaining shmem get claimed by >> whichever table uses it first? > It's leads to odd behavior. You start the database, and your application > runs fine. Then you restart the database, and now you get "out of shared > memory" errors from transactions that used to work. If you get "out of shared memory" at all due to SSI, I'd say that that's the problem, not exactly when it happens. I thought that the patch included provisions for falling back to coarser-grained locks whenever it was short of resources. 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] Transaction log
Wow sounds very complicated. Will have to try that but got to say i am new to postgresql and might find that difficult. so at the moment i would try and to the little i can to find parts in the DBMS that can be of use, as i already tried struggling to try and find a way to set the HeapTuplevisiblity. Cheers -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4286800.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] getting to beta
Tom Lane wrote: > If you get "out of shared memory" at all due to SSI, I'd say that > that's the problem, not exactly when it happens. I thought that > the patch included provisions for falling back to coarser-grained > locks whenever it was short of resources. When one of the tests was getting out of memory errors we were initially having trouble telling where the memory was actually consumed, because it wasn't necessarily due to the type of object being allocated at the point of failure. That was the motivation for my attempt to log when an HTAB grew past its "maximum". The problem turned out to be a field which wasn't properly initialized in certain corner cases, making the cleanup phase fail to clear them when appropriate. There is a patch to fix that bug, but the issue raised in the early phase of investigation is what, if anything we should do about the free-for-all allocation. If we want to call that a feature and take it off the 9.1 list, that's OK with me. It's a new issue with 9.1 in the sense that there used to be only one HTAB which could grab the slack space, and only generate its out of memory error once that slack space was exhausted. Now that there are three, things are a bit less predictable. By the way, the problem with SSI potentially running out of shared memory is rather parallel to how heavyweight locks can run out of shared memory. The SLRU prevents the number of transactions from being limited in that way, and multiple locks per table escalate granularity, but with a strange enough workload (for example, accessing hundreds of tables per transaction) one might need to boost max_pred_locks_per_transaction above the default to avoid shared memory exhaustion. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On sön, 2011-04-03 at 16:04 +0200, Magnus Hagander wrote: > > The documentation appears to claim that the Platform/Windows SDK without > > any Visual Studio should be enough. Is there also an upper limit on the > > supported SDK version then? > > It certainly used to be enough, so I guess if they have bounced the > version of the VC compiler that's included in the SDK then yes, there > needs to be an upper bound on it. > > What version is the compiler that comes along with the SDK reporting? > (not the VS Express one, the one in the SDK itself) > > Guessing fromhttp://en.wikipedia.org/wiki/Microsoft_Windows_SDK, maybe > we need to say up to v6.1 for now? I got it to build now. Here are is a list of notes that would make life easier for future generations: * As discussed, it should be noted that Visual Studio 2010 is not supported yet. * As previously mentioned, change Platform SDK to Windows SDK in the documentation. * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. * The build scripts should be made warnings-free with Perl 5.12, which is the current default from ActiveState. * There appears to be a bug in the GnuWin32 version of Bison that is recommended to use, if you install it into a path that has spaces in it, such as the default path C:\Program Files \GnuWin32. The internal call to m4 chokes on that. Not our bug, but perhaps worth warning about. * vcregress.pl dies if there is no config.pl, even though the other tools treat it as and the documentation claims it is optional. * clean.bat doesn't read buildenv.pl, causing a failure if you have a path setting in there to find msbuild.exe. * The major difficulty was figuring out the right path setting to all the tools. The documentation is a bit hand-wavy about that. In particular, it needed to find both vcbuild.exe and msbuild.exe, which are conveniently hidden in C:\Program Files \Microsoft Visual Studio 9.0\VC\vcpackages and C:\Windows \Microsoft.NET\Framework\v2.0.50727 respectively. I'm not sure if there is a pattern there that could be documented, but it would really be helpful to at least give better hints about this. * It might also be in order to update pg_config.h.win32 relative to the current pg_config.h.in. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Wed, 2011-04-06 at 09:10 +0200, Martijn van Oosterhout wrote: > On Tue, Apr 05, 2011 at 11:55:04PM -0700, Joshua D. Drake wrote: > > I want to achieve two things: > > > > 1. More understandable .pgpass format. Yes, I understand our standard > > format, most people won't. Like JoshB said, hard to debug. > > This I understand. > > > 2. psql foo, gets me into foo. A macro for connections if you will. > > But this is precisely what the service file achieves, right? ISTM what > you d like is to be able to specify the password in the service file, > in which case pgpass is not consulted. Right, as I said (I thought) earlier, after reviewing pg_service it seems that pgpass is (or pg_service is) redundant. It should be one file. JD > > Have a nice day, -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should psql support URI syntax?
On sön, 2011-04-03 at 12:41 -0400, Tom Lane wrote:
> Peter Eisentraut writes:
> > Well, there isn't any requirement that URIs be
>
> > prot://hostname:port/something
>
> > They just have to be
>
> > prot:something
>
> > So you could just turn the existing conninfo syntax into a URI by doing
> > something like
>
> > postgresql:dbname=foo%20hostname=bar
>
> True, but the need for those %20's is annoying. I tend to agree with
> the suggestion that adopting the JDBC syntax would be the way to go,
> assuming that we can use it 100%-as-is (any incompatibility defeats
> the purpose).
Btw., there is also
$dbh = DBI->connect("dbi:Pg:dbname=$dbname", '', '', {AutoCommit => 0});
using a kind-of URI notation.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentraut wrote: > > * I have some doubts about whether the SDK is at all needed or > whether it would suffice by itself. I went with Visual Studio > Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. > * The major difficulty was figuring out the right path setting to > all the tools. The documentation is a bit hand-wavy about that. > In particular, it needed to find both vcbuild.exe and > msbuild.exe, which are conveniently hidden in C:\Program Files > \Microsoft Visual Studio 9.0\VC\vcpackages and C:\Windows > \Microsoft.NET\Framework\v2.0.50727 respectively. I'm not sure > if there is a pattern there that could be documented, but it > would really be helpful to at least give better hints about > this. The SDK and/or Visual Studio should have created some shortcuts on the menu to start command prompt windows with the appropriate environment variables setup for you. On my system (which has Visual Studio 2008 Pro), there are shortcuts for 32 bit and 64 bit build environments. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On ons, 2011-04-06 at 09:47 -0400, Tom Lane wrote: > Marko Kreen writes: > > On Wed, Apr 6, 2011 at 9:55 AM, Joshua D. Drake > > wrote: > >> 1. More understandable .pgpass format. Yes, I understand our standard > >> format, most people won't. Like JoshB said, hard to debug. > > > How about allowing '#'-comments there and putting field > > list into all templates, examples and manpages? > > > man 5 pgpass? > > > pgpass.sample? > > > IOW, do we need to change format or are we having documentation problem? > > +1 for seeing this as a documentation problem. I don't think that > converting the file to .ini style is going to somehow make it > magically easier to use --- people still have to understand it, > and frankly .ini format is just another format that not everyone knows. > > #-comments seem like a fine idea. But it would have to be the user that would put the comment in there, since we can't really install a default file. I think a man page would be the best documentation method for in-the-moment reference. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows build issues
On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentraut wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. 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] Windows build issues
On Wed, 06 Apr 2011 20:27:22 +0300, Peter Eisentraut wrote: I got it to build now. Here are is a list of notes that would make life easier for future generations: You might also want to have a look at my VS2010 patch as it already touches some of those issues. https://commitfest.postgresql.org/action/patch_view?id=523 It's not ready yet but I'm prepared to get back to it as soon as there's some serious interest. My personal plan is to get the good parts of it commited for 9.2 Regards, Brar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Proposal for GSoC : ADJ dashboard (Administration related software)
* Project Title*: ADJ Dashboard Name : Erdinc AKKAYA Email: erdinc.akk...@gmail.com *Synopsis* AnyDBJSP is a database monitoring and reporting solution with a browser based interface. ADJ dashboard mainly will be written for database admins(DBA). This tool will have pre-defined sql queries. In addition to these, DBA can write his/her own sql query and report structure easy. Similar projects are, EnterpiseDB's dashboard, cybertec's pgwatch (www.cybertec.at). *Benefits to the PostgreSQL Community* All the sql queries are stored in xml files. these xml files can be updated easy, with contribution of community this xml file can grow easyly and at the end community will have a strong database monitoring tool. Query xml files will be published on web(like wiki pages) and everyone can easy design his/her own monitoring tool easyly. Other adventage is, after a time, community will have a strong sql queries that monitors databases so if someone wants to study on postgresql database administration ADJ dashboard will be a good tool to start. *Quantifiable results* Easy,understandable and growing sql monitoring tool. *Project Details* Project will be written in JAVA and will run on apache tomcat application server. ADJ dashboard don't need any alternative database to store data. All the statistics will be created on the fly. If someone wants to store it to anywhere, dashboard will export results in XML, PDF, XLS formats(these can be more). Also Dashboard can work as a webservice that exporting XML outputs. So tool can be used on any webpage to show statistics about database. Dashboard will create flash charts also. There are common database statistics like, unused indexes, total transactions(commiteds,rollbacks...) this dashboard should create more and more statistics about database (like database bloats..). On start I will prepare more than 30 statistics and charts. After project getting its shape everyone on the comminity can add more by him/her self. Project have a nice interface with small css,javascripts, easy, understandable,tidy and clean. *Inch-Stones* Collecting the information from community. What DBA's needs? What they prefer to see? First implementation with amount of queries. Prepare tool for concurrent connections to more than one database. Scheduled mail configuration. Documentation, Testing, Commit. *Project Schedule* before 1st of June Collect sqls and ideas from DBA's at community 1-15 June Start coding for first prototype 15-17 Test first prototype. Ask feedback from community 17 June - 1st July Develope 2. prototype with scheduled mail system included. 1-20 July Prepare data outputs.(adj should work also as a web-service) 20 July-1 August Prepare FrontEnd and get feedback from community. 1-15 August Refactoring, add or remove features according to feedbacks. Test and commit project. *Completeness Criteria* ADJ dashboard is ready. Tested on all common linux distributions. Committed and installation guide is ready. *Bio* Birth:1986-July-01 4th Year Computer systems and information technologies student at Bilkent University / Turkey. Working as a software developer on a private firm for 4 years. Github: hzroot Twitter: hzroot FreenodeIrc: hzroot
Re: [HACKERS] Should psql support URI syntax?
Hello, O.k., the basic JDBC syntax is: jdbc:://[:]/ Where driver is the actual database such as postgresql or db2. I am thinking something like: postgres:ssl://localhost:5432/template Many drivers support an extended syntax like: postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true But I don't know if we want to go there. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should psql support URI syntax?
"Joshua D. Drake" wrote: > Many drivers support an extended syntax like: > > postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true > > But I don't know if we want to go there. We've been there for years: http://jdbc.postgresql.org/documentation/head/connect.html -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Can I check if somebody is superuser in stored procedure?
On ons, 2011-04-06 at 09:51 -0500, Jim Nasby wrote: > Note that doesn't work if the user has superuser because it was granted via > another role. You can only be a superuser if your own superuser bit is set. It cannot be granted via some other role. (Not sure whether that's a feature.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Wed, Apr 06, 2011 at 12:25:26PM -0500, Kevin Grittner wrote: > By the way, the problem with SSI potentially running out of shared > memory is rather parallel to how heavyweight locks can run out of > shared memory. The SLRU prevents the number of transactions from > being limited in that way, and multiple locks per table escalate > granularity, but with a strange enough workload (for example, > accessing hundreds of tables per transaction) one might need to > boost max_pred_locks_per_transaction above the default to avoid > shared memory exhaustion. In fact, it's exactly the same: if a backend wants to acquire many heavyweight locks, it doesn't stop at max_locks_per_xact, it just keeps allocating them until shmem is exhausted. So it's possible, if less likely, to have the same problem with regular locks causing the system to run out of shared memory. Which sounds to me like a good reason to address both problems in one place. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should psql support URI syntax?
On Wed, 2011-04-06 at 13:35 -0500, Kevin Grittner wrote: > "Joshua D. Drake" wrote: > > > Many drivers support an extended syntax like: > > > > > postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl=true > > > > But I don't know if we want to go there. > > We've been there for years: > > http://jdbc.postgresql.org/documentation/head/connect.html > Not for psql we haven't. JD > -Kevin > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/python tracebacks v2
On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote: > I finally got around to updating the PL/Python tracebacks patch. The > other day I was writing some very simple PL/Python code and the lack of > tracebacks is extremely annoying. I tweaked this a bit to make the patch less invasive, and then committed it. :) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should psql support URI syntax?
Hi, On Wednesday 06 April 2011 20.31:38 Joshua D. Drake wrote: > postgres:ssl://localhost:5432/template1/?username=jd&password=foobar&ssl= > true > > But I don't know if we want to go there. I would expect that *if* an URI syntax becomes implemented, it should support all possible options. Especially things like ssl but also connecting via Unix socket instead of TCP should be covered. Else applications would need to offer one way to specify the "usual" connection and another way to offer configuration with "special requirements" such as connection to unix socket or via ssl. (... which would probably not be supported by many applications, since "everybody connects to localhost:5432 anyway..." -- vbi -- FORTUNE REMEMBERS THE GREAT MOTHERS: #6 "Johnny, if you fall and break your leg, don't come running to me!" -- Mrs. Emily Barstow, June 16, 1954 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Windows build issues
On Wed, 06 Apr 2011 20:04:37 +0200, Brar Piening wrote: It's not ready yet but I'm prepared to get back to it as soon as there's some serious interest. I've rebased the patch in case somebody wants to try it. http://www.piening.info/VS2010v5.patch Regards, Brar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/python tracebacks v2
On 06/04/11 21:38, Peter Eisentraut wrote: > On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote: >> I finally got around to updating the PL/Python tracebacks patch. The >> other day I was writing some very simple PL/Python code and the lack of >> tracebacks is extremely annoying. > > I tweaked this a bit to make the patch less invasive, and then committed > it. :) Ouch, just today I found a flaw in this, namely that it assumes the lineno from the traceback always refers to the PL/Python function. If you create a PL/Python function that imports some code, runs it, and that code raises an exception, PLy_traceback will get utterly confused. Working on a fix... Jan PS: obviously it'd be great to have PL/Python traceback support in 9.1, but I sure hope we'll get some testing in beta for issues like this... J -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
On Wed, Apr 6, 2011 at 1:15 PM, aaronenabs wrote: > Wow sounds very complicated. Will have to try that but got to say i am new to > postgresql and might find that difficult. so at the moment i would try and > to the little i can to find parts in the DBMS that can be of use, as i > already tried struggling to try and find a way to set the > HeapTuplevisiblity. It is complicated, which I guess isn't that surprising. Recovery a corrupted database is not for the faint of heart. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] too many dotted names
Hi,
Does it make sense to treat these ?
ALTER TABLE "s'd"".s'd"""."s's'd""." ADD COLUMN id bigint DEFAULT
nextval('"s''d".s''d""."s''d".d"s''"');
ERROR: improper relation name (too many dotted names): s'd.s'd"".s'd.d"s'"
SQL state: 42601
PostgreSQL 9.1devel on i686-pc-linux-gnu, compiled by GCC gcc
(Ubuntu/Linaro 4.5.1-7ubuntu2) 4.5.1, 32-bit
Best regards,
Vladimir Kokovic, DP senior, Belgrade, Serbia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
On Wed, Apr 6, 2011 at 3:27 PM, Dan Ports wrote: > On Wed, Apr 06, 2011 at 12:25:26PM -0500, Kevin Grittner wrote: >> By the way, the problem with SSI potentially running out of shared >> memory is rather parallel to how heavyweight locks can run out of >> shared memory. The SLRU prevents the number of transactions from >> being limited in that way, and multiple locks per table escalate >> granularity, but with a strange enough workload (for example, >> accessing hundreds of tables per transaction) one might need to >> boost max_pred_locks_per_transaction above the default to avoid >> shared memory exhaustion. > > In fact, it's exactly the same: if a backend wants to acquire many > heavyweight locks, it doesn't stop at max_locks_per_xact, it just > keeps allocating them until shmem is exhausted. > > So it's possible, if less likely, to have the same problem with regular > locks causing the system to run out of shared memory. Which sounds to > me like a good reason to address both problems in one place. The real fix for this problem is probably to have the ability to actually return memory to the shared pool, rather than having everyone grab as they need it until there's no more and never give back. But that's not going to happen in 9.1, so the question is whether this is a sufficiently serious problem that we ought to impose the proposed stopgap fix between now and whenever we do that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] postgresql.conf error checking strategy
I just spent a rather confused half hour while testing my GUC assign-hook patch, and when I finally figured out what was happening, it made me wonder whether we should redesign the behavior a little bit. The current behavior of ProcessConfigFile is that it runs through all the "name = value" pairs extracted from the file and tries to fully verify each value (by seeing whether set_config_option with changeVal false likes it). Only if every one of them checks out does it actually apply any of the settings. Now this is nice and conservative --- the aim is to avoid applying settings from a possibly corrupted file, in case somebody fat-fingered their edits in a big way. But there's a little problem: 1. It's possible that not all the backends agree on whether a setting is valid. The case I was testing involved setting client_encoding from the config file, so whether it succeeds depends on the database encoding (some conversions might exist and others not). This means that some backends might apply the postgresql.conf settings and others not. That's pretty bad in itself, if something that needs to be consistent system-wide is changing. 2. Only the postmaster reports config file problems at elevel LOG; backends only complain at DEBUG3, to avoid cluttering the log with lots of duplicate messages. This means that if you do have a few backends that fail to adopt a setting, there likely won't be anything in the log to tell you so. (The reason I was so confused is that I'd raised log_min_messages to DEBUG5 to try to understand what was happening ... but my backend-under-test wasn't adopting that setting, and wasn't logging anything to tell me so either ...) So I'm thinking we should adopt a strategy that's less likely to result in divergent behavior among different backends. The idea I have in mind is to have the first "validation" pass only check that each name is a legal GUC variable name, and not look at the values at all. If so, try to apply all the values. Any that fail to apply we log as usual, but still apply the others. ISTM that verifying the names should be enough protection against broken files for practical purposes, and it should be something that all backends will agree on even if there are individual values that are not valid for all. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] lowering privs in SECURITY DEFINER function
Hi, A customer of ours has for a long time the desire to be able to return to the previous privilege level (i.e. the caller privs) inside a SECURITY DEFINER function. I find that this notion is not at all covered in the SQL standard, yet the use case is certainly valid from a security-concious point of view. (Consider, for example, that you may want to enable a user to run some operation to which he is authorized, but you want to carry out some privileged operation before/after doing so: for example, disable triggers, run an update, re-enable triggers.) An easy way to "somewhat solve" this problem is to provide another security definer function that calls the intermediate operation, owned by a role with lower privileges. But this doesn't really solve the problem, because you are then providing a way to return to an arbitrary role, not to the specific role that's calling the function. I think part of the solution here would be to be able to tell what's the "previous role", i.e. the one just below the topmost stack item in the authorization stack. Then, at least you know what to call SET SESSION AUTHORIZATION to. Thoughts? This area seems fraught with security problems, yet it is a necessary piece on the security puzzle. -- Álvaro Herrera -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql.conf error checking strategy
On Wed, Apr 6, 2011 at 10:17 PM, Tom Lane wrote: > So I'm thinking we should adopt a strategy that's less likely to result > in divergent behavior among different backends. The idea I have in mind > is to have the first "validation" pass only check that each name is a > legal GUC variable name, and not look at the values at all. If so, try > to apply all the values. Any that fail to apply we log as usual, but > still apply the others. ISTM that verifying the names should be enough > protection against broken files for practical purposes, and it should be > something that all backends will agree on even if there are individual > values that are not valid for all. > Would it be possible to have a) a policy that GUCs should verify or fail to verify consistently for all backends and b) a way for the backends to scream loudly if they come to a different conclusion than the master when reloading the file? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgresql.conf error checking strategy
On Wed, Apr 6, 2011 at 5:17 PM, Tom Lane wrote: > I just spent a rather confused half hour while testing my GUC > assign-hook patch, and when I finally figured out what was happening, > it made me wonder whether we should redesign the behavior a little bit. > > The current behavior of ProcessConfigFile is that it runs through all > the "name = value" pairs extracted from the file and tries to fully > verify each value (by seeing whether set_config_option with changeVal > false likes it). Only if every one of them checks out does it actually > apply any of the settings. Now this is nice and conservative --- the > aim is to avoid applying settings from a possibly corrupted file, in > case somebody fat-fingered their edits in a big way. But there's a > little problem: > > 1. It's possible that not all the backends agree on whether a setting > is valid. The case I was testing involved setting client_encoding > from the config file, so whether it succeeds depends on the database > encoding (some conversions might exist and others not). This means > that some backends might apply the postgresql.conf settings and others > not. That's pretty bad in itself, if something that needs to be > consistent system-wide is changing. > > 2. Only the postmaster reports config file problems at elevel LOG; > backends only complain at DEBUG3, to avoid cluttering the log with > lots of duplicate messages. This means that if you do have a few > backends that fail to adopt a setting, there likely won't be anything > in the log to tell you so. (The reason I was so confused is that I'd > raised log_min_messages to DEBUG5 to try to understand what was > happening ... but my backend-under-test wasn't adopting that setting, > and wasn't logging anything to tell me so either ...) > > So I'm thinking we should adopt a strategy that's less likely to result > in divergent behavior among different backends. The idea I have in mind > is to have the first "validation" pass only check that each name is a > legal GUC variable name, and not look at the values at all. If so, try > to apply all the values. Any that fail to apply we log as usual, but > still apply the others. ISTM that verifying the names should be enough > protection against broken files for practical purposes, and it should be > something that all backends will agree on even if there are individual > values that are not valid for all. > > Comments? I don't think now is a good time for a major behavior change in this area, and I'm not convinced this is the best possible design. There are a number of parameters which are currently PGC_POSTMASTER rather than PGC_SIGHUP precisely because of the possibility of backends being out of step with each other. wal_level is an obvious example, and one that it would be *really* nice to be able to change without a server restart. It would be nice to have a real solution to that problem, but this isn't it, and I don't want to engineer it right now. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pl/python tracebacks v2
On 06/04/11 22:16, Jan Urbański wrote:
> On 06/04/11 21:38, Peter Eisentraut wrote:
>> On mån, 2011-03-21 at 00:40 +0100, Jan Urbański wrote:
>>> I finally got around to updating the PL/Python tracebacks patch. The
>>> other day I was writing some very simple PL/Python code and the lack of
>>> tracebacks is extremely annoying.
>>
>> I tweaked this a bit to make the patch less invasive, and then committed
>> it. :)
>
> Ouch, just today I found a flaw in this, namely that it assumes the
> lineno from the traceback always refers to the PL/Python function. If
> you create a PL/Python function that imports some code, runs it, and
> that code raises an exception, PLy_traceback will get utterly confused.
>
> Working on a fix...
Here's the fix.
The actual bug was funny. The traceback code was fetching the file line
from the traceback and trying to get that line from the original source
to print it. But sometimes that line was refering to a different source
file, like when the exception originated from an imported module.
In my testing I accidentally had the error (in a separate module) on
line 2, so the traceback code tried to fetch line 2 of the function,
which was completely whitespace. This can never happen in theory,
because you can't have a frame starting at an all-whitespace line. The
code to get that line was misbehaving and trying to do a malloc(-2),
which in turn was causing an "ERROR invalid memory allocation".
All that is fixed with the attached patch.
Cheers,
Jan
PS: and thanks for committing that in the first place! :)
J
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 9352580..b2333b8 100644
*** a/src/pl/plpython/plpython.c
--- b/src/pl/plpython/plpython.c
*** get_source_line(const char *src, int lin
*** 4507,4512
--- 4507,4520
if (next == NULL)
return pstrdup(s);
+ /*
+ * Sanity check, next < s if the line was all-whitespace, which should
+ * never happen if Python reported an frame created on that line, but
+ * check anyway.
+ */
+ if (next < s)
+ return NULL;
+
return pnstrdup(s, next - s);
}
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4603,4608
--- 4611,4617
PyObject *volatile code = NULL;
PyObject *volatile name = NULL;
PyObject *volatile lineno = NULL;
+ PyObject *volatile filename = NULL;
PG_TRY();
{
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4621,4626
--- 4630,4639
name = PyObject_GetAttrString(code, "co_name");
if (name == NULL)
elog(ERROR, "could not get function name from Python code object");
+
+ filename = PyObject_GetAttrString(code, "co_filename");
+ if (filename == NULL)
+ elog(ERROR, "could not get file name from Python code object");
}
PG_CATCH();
{
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4628,4633
--- 4641,4647
Py_XDECREF(code);
Py_XDECREF(name);
Py_XDECREF(lineno);
+ Py_XDECREF(filename);
PG_RE_THROW();
}
PG_END_TRY();
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4638,4643
--- 4652,4658
char *proname;
char *fname;
char *line;
+ char *plain_filename;
long plain_lineno;
/*
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4651,4656
--- 4666,4672
fname = PyString_AsString(name);
proname = PLy_procedure_name(PLy_curr_procedure);
+ plain_filename = PyString_AsString(filename);
plain_lineno = PyInt_AsLong(lineno);
if (proname == NULL)
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4662,4668
&tbstr, "\n PL/Python function \"%s\", line %ld, in %s",
proname, plain_lineno - 1, fname);
! if (PLy_curr_procedure)
{
/*
* If we know the current procedure, append the exact
--- 4678,4686
&tbstr, "\n PL/Python function \"%s\", line %ld, in %s",
proname, plain_lineno - 1, fname);
! /* the code object was compiled with "" as the filename */
! if (PLy_curr_procedure && plain_filename != NULL &&
! strcmp(plain_filename, "") == 0)
{
/*
* If we know the current procedure, append the exact
*** PLy_traceback(char **xmsg, char **tbmsg,
*** 4670,4676
* traceback.py module behavior. We could store the
* already line-split source to avoid splitting it
* every time, but producing a traceback is not the
! * most important scenario to optimize for.
*/
line = get_source_line(PLy_curr_procedure->src, plain_lineno);
if (line)
--- 4688,4696
* traceback.py module behavior. We could store the
* already line-split source to avoid splitting it
* every time, but producing a traceback is not the
! * most important scenario to optimize for. However,
! * do not go as far as traceback.py in reading the source
! * of imported modules.
*/
Re: [HACKERS] lowering privs in SECURITY DEFINER function
On Apr 6, 2011, at 5:33 PM, Alvaro Herrera wrote: > Hi, > > A customer of ours has for a long time the desire to be able to return > to the previous privilege level (i.e. the caller privs) inside a > SECURITY DEFINER function. I find that this notion is not at all > covered in the SQL standard, yet the use case is certainly valid from a > security-concious point of view. > > (Consider, for example, that you may want to enable a user to run some > operation to which he is authorized, but you want to carry out some > privileged operation before/after doing so: for example, disable > triggers, run an update, re-enable triggers.) > > An easy way to "somewhat solve" this problem is to provide another > security definer function that calls the intermediate operation, owned > by a role with lower privileges. But this doesn't really solve the > problem, because you are then providing a way to return to an arbitrary > role, not to the specific role that's calling the function. > > I think part of the solution here would be to be able to tell what's the > "previous role", i.e. the one just below the topmost stack item in the > authorization stack. Then, at least you know what to call SET SESSION > AUTHORIZATION to. > > Thoughts? This area seems fraught with security problems, yet it is a > necessary piece on the security puzzle. That's really strange considering that the new role may not normally have permission to switch to the original role. How would you handle the case where the security definer role is not the super user? How would you prevent general SQL attacks when manually popping the authentication stack is allowed? Cheers, M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] getting to beta
Robert Haas wrote: > The real fix for this problem is probably to have the ability to > actually return memory to the shared pool, rather than having > everyone grab as they need it until there's no more and never give > back. But that's not going to happen in 9.1, so the question is > whether this is a sufficiently serious problem that we ought to > impose the proposed stopgap fix between now and whenever we do > that. There is a middle course between leaving the current approach of preallocating half the maximum size and leaving the other half up for grabs and the course Heikki proposes of making the maximum a hard limit. I submitted a patch to preallocate the maximum, so a request for a particular HTAB object will never get "out of shared memory" unless it is past its maximum: http://archives.postgresql.org/message-id/4d94806602250003c...@gw.wicourts.gov That would leave some extra which is factored into the calculations up for grabs, but each table would be guaranteed at least its maximum number of entries. This seems pretty safe to me, and not very invasive. We could always revisit in this 9.2 if that's not good enough. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] superusers are members of all roles?
I just hit this, which at least violated my sense of least astonishment, if it's not an outright bug: After creating a role foo, I added to following lines to my (9.0) pg_hba.conf: localall +foo reject host all +foo 0.0.0.0/0 reject The surprising (to me) consequence was that every superuser was locked out of the system. I had not granted them (or anyone) the role, but nevertheless these lines took effect. If this is intended, it should at least be documented. But if it is intended then it's ugly anyway, IMNSHO, and we should change it. 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] lowering privs in SECURITY DEFINER function
On Wed, 2011-04-06 at 18:33 -0300, Alvaro Herrera wrote: > (Consider, for example, that you may want to enable a user to run some > operation to which he is authorized, but you want to carry out some > privileged operation before/after doing so: for example, disable > triggers, run an update, re-enable triggers.) I'm not sure I understand the use case. If it's within one function, why not just do it all as the privileged user in the security definer function? The only reason I can think of it if you wanted to make the unprivileged operation arbitrary SQL. But in the example you give, with triggers disabled, it's not safe to allow the user to execute arbitrary operations. In other words, if you wrap an unprivileged operation inside of privileged operations, it seems like the unprivileged operation then becomes privileged. Right? 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] superusers are members of all roles?
* Andrew Dunstan (and...@dunslane.net) wrote: > The surprising (to me) consequence was that every superuser was > locked out of the system. I had not granted them (or anyone) the > role, but nevertheless these lines took effect. As I recall, the way we allow superusers to set role to other roles is by considering the superuser to be a member of every role. Now, I agree that such an approach doesn't make sense for pg_hba consideration. > If this is intended, it should at least be documented. But if it is > intended then it's ugly anyway, IMNSHO, and we should change it. Perhaps the superuser-override should be moved to be at the 'set role' level instead of setting things up such that the superuser is considered a member of every role. That would fix this but would require adding a couple of additional special superuser checks, which isn't something to do lightly, imv. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] GSoC Proposal - Caching query results in pgpool-II
I like this proposal. This would bring big benefit to both the
PostgreSQL and the pgpool project.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> Hello
>
> I am sending my proposal about Google Summer Of Code2011.
> It would be nice if you could give me your opinion.
>
> ・title
>
> Caching query results in pgpool-II
>
>
> ・Synopsis
>
> Pgpool-II has query caching functionality using storage provided by
> dedicated PostgreSQL ("system database"). This has several drawbacks
> however. 1)it is slow because it needs to access disk storage 2)it
> does not invalidate the cache automatically.
>
> This proposal tries to solve these problems.
>
> - To speed up the cache access, it will be placed on memory, rather
> than database. The memory will be put on shared memory or external
> memory services such as memcached so that the cache can be shared by
> multiple sessions. Old cache entries will be deleted by LRU manner.
>
> - The cache will be invalidated automatically upon the timing when the
> relevant tables are updated. Note that this is not always possible
> because the query result might come from multiple tables, views or
> even functions. In this case the cache will be invalidated by
> timeout(or they are not cached at all).
>
> - Fine tuning knobs need to be invented to control the cache behavior
> though they are not clear at this moment.
>
>
> ・Benefits to the PostgreSQL Community:
>
>
> Query caching will effectively enhance the performance of PostgreSQL
> and this project will contribute to increase the number of users of
> PostgreSQL, who need more high performance database systems.
>
> Note that implementing query cache in pgpool-II will bring merits not
> only to the latest version of PostgreSQL but to the previous releases
> of PostgreSQL.
>
>
> ・Project Schedule
>
> -April
> preparation
>
> -May 1 - May 22
> write a specification
>
> -May 23 - June 19
> coding
>
> -June 20 - July 22
> test
>
> -July 23 - August 12
> Complete of coding and test, commit
>
>
> ・Personal Data and Biographical Information
>
> Name : Masanori Yamazaki
> Born : 23.1.1981
> School :Currently I learn contemporary philosophy, culture and literature
> at Waseda University in Japan.
> Coding :
> 1.About five years job as web application programer(PHP, Java).
> 2.I experienced projects used framework such as Symfony, Zend Framework,
> CakePHP, and Struts.
> 3.I am interested in OSS and like coding.
>
>
> 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] Windows build issues
On 04/06/2011 01:47 PM, Andrew Dunstan wrote: On 04/06/2011 01:34 PM, Dave Page wrote: On Wed, Apr 6, 2011 at 6:27 PM, Peter Eisentraut wrote: * I have some doubts about whether the SDK is at all needed or whether it would suffice by itself. I went with Visual Studio Express 2008. The SDK is needed with 2008 Express, but not the non-express version. The SDK on it's own should be enough for command line compilation. When you install VC Express 2008 the SDK is installed with it. A separate install is not required, as it was with VCE 2005. unless you want to build 64 bit binaries. 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] GSoC Proposal - Caching query results in pgpool-II
In my understanding pqc is not designed to be working with pgpool.
Thus if a user want to use both query cache and query dispatching,
replication or failover etc. which are provided by pgpool, it seems
it's not possible. For this purpose maybe user could *cascade* pqc and
pgpool, but I'm not sure. Even if it's possible, it will bring huge
performance penalty.
Another point is cache invalidation. Masanori's proposal includes
cache invalidation technique by looking at write queries, which is
lacking in pqc in my understanding.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp
> How does this relate to the existing pqc project (
> http://code.google.com/p/pqc/)? Seems the goals are fairly similar, and both
> are based off pgpool?
>
> /Magnus
> On Apr 6, 2011 2:10 AM, "Masanori Yamazaki" wrote:
>> Hello
>>
>> My name is Masanori Yamazaki. I am sending my proposal about
>> Google Summer Of Code2011. It would be nice if you could give
>> me your opinion.
>>
>>
>> ・title
>>
>> Caching query results in pgpool-II
>>
>>
>> ・Synopsis
>>
>> Pgpool-II has query caching functionality using storage provided by
>> dedicated PostgreSQL ("system database"). This has several drawbacks
>> however. 1)it is slow because it needs to access disk storage 2)it
>> does not invalidate the cache automatically.
>>
>> This proposal tries to solve these problems.
>>
>> - To speed up the cache access, it will be placed on memory, rather
>> than database. The memory will be put on shared memory or external
>> memory services such as memcached so that the cache can be shared by
>> multiple sessions. Old cache entries will be deleted by LRU manner.
>>
>> - The cache will be invalidated automatically upon the timing when the
>> relevant tables are updated. Note that this is not always possible
>> because the query result might come from multiple tables, views or
>> even functions. In this case the cache will be invalidated by
>> timeout(or they are not cached at all).
>>
>> - Fine tuning knobs need to be invented to control the cache behavior
>> though they are not clear at this moment.
>>
>>
>> ・Benefits to the PostgreSQL Community:
>>
>>
>> Query caching will effectively enhance the performance of PostgreSQL
>> and this project will contribute to increase the number of users of
>> PostgreSQL, who need more high performance database systems.
>>
>> Note that implementing query cache in pgpool-II will bring merits not
>> only to the latest version of PostgreSQL but to the previous releases
>> of PostgreSQL.
>>
>>
>> ・Project Schedule
>>
>> -April
>> preparation
>>
>> -May 1 - May 22
>> write a specification
>>
>> -May 23 - June 19
>> coding
>>
>> -June 20 - July 22
>> test
>>
>> -July 23 - August 12
>> complete of coding and test, commit
>>
>>
>> ・Personal Data and Biographical Information
>>
>> Name : Masanori Yamazaki
>> Born : 23.1.1981
>> School :Currently I learn contemporary philosophy, culture and literature
>> at Waseda University in Japan.
>> Coding :
>> 1.About five years job as web application programer(PHP, Java).
>> 2.I experienced projects used framework such as Symfony, Zend Framework,
>> CakePHP, and Struts.
>> 3.I am interested in OSS and like coding.
>>
>>
>> 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] getting to beta
On Wed, Apr 6, 2011 at 6:32 PM, Kevin Grittner wrote: > Robert Haas wrote: >> The real fix for this problem is probably to have the ability to >> actually return memory to the shared pool, rather than having >> everyone grab as they need it until there's no more and never give >> back. But that's not going to happen in 9.1, so the question is >> whether this is a sufficiently serious problem that we ought to >> impose the proposed stopgap fix between now and whenever we do >> that. > > There is a middle course between leaving the current approach of > preallocating half the maximum size and leaving the other half up > for grabs and the course Heikki proposes of making the maximum a > hard limit. I submitted a patch to preallocate the maximum, so a > request for a particular HTAB object will never get "out of shared > memory" unless it is past its maximum: > > http://archives.postgresql.org/message-id/4d94806602250003c...@gw.wicourts.gov > > That would leave some extra which is factored into the calculations > up for grabs, but each table would be guaranteed at least its > maximum number of entries. This seems pretty safe to me, and not > very invasive. We could always revisit in this 9.2 if that's not > good enough. OK, I agree. We certainly can't have a temporary demand for predicate locks starve out heavyweight locks for the rest of the postmaster lifetime, or visca versa. So we need to do at least that much. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost wrote: > * Andrew Dunstan (and...@dunslane.net) wrote: >> The surprising (to me) consequence was that every superuser was >> locked out of the system. I had not granted them (or anyone) the >> role, but nevertheless these lines took effect. > > As I recall, the way we allow superusers to set role to other roles is > by considering the superuser to be a member of every role. Now, I agree > that such an approach doesn't make sense for pg_hba consideration. See bug #5763, and subsequent emails. Short version: Tom argued it wasn't a bug; Peter and I felt that it was. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] timezone GUC
If you have the timezone configured to a non-default value in postgresql.conf, and you comment it out and reload, it says: LOG: parameter "TimeZone" removed from configuration file, reset to default ...but at least when I tested it, it didn't actually appear to reset it to the default. assign_timezone() has this to say: /* * UNKNOWN is the value shown as the "default" for TimeZone in * guc.c. We interpret it as being a complete no-op; we don't * change the timezone setting. Note that if there is a known * timezone setting, we will return that name rather than UNKNOWN * as the canonical spelling. * * During GUC initialization, since the timezone library isn't set * up yet, pg_get_timezone_name will return NULL and we will leave * the setting as UNKNOWN. If this isn't overridden from the * config file then pg_timezone_initialize() will eventually * select a default value from the environment. */ ...but that seems a few bricks short of a load, because it doesn't handle this case properly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] too many dotted names
On Wed, Apr 6, 2011 at 4:23 PM, Vladimir Kokovic
wrote:
> Hi,
>
> Does it make sense to treat these ?
>
> ALTER TABLE "s'd"".s'd"""."s's'd""." ADD COLUMN id bigint DEFAULT
> nextval('"s''d".s''d""."s''d".d"s''"');
>
> ERROR: improper relation name (too many dotted names): s'd.s'd"".s'd.d"s'"
> SQL state: 42601
Treat them as what?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] superusers are members of all roles?
> See bug #5763, and subsequent emails. Short version: Tom argued it > wasn't a bug; Peter and I felt that it was. Add my vote: it's a bug. Users who fall afoul of this will spend *hours* trying to debug this before they stumble on the correct answer. pg_hba.conf is confusing enough as it is. The only reason we don't get more bug reports on this is that not very many users know about using group roles in pg_hba.conf (and few enough users are using group roles in the first place). If we're not going to fix this, then we need a big warning in the docs and the pg_hba.conf file: "NOTE: Please make sure that at least one rule in pg_hba.conf matches superuser access before any reject rules" -- Josh Berkus PostgreSQL Experts Inc. http://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] superusers are members of all roles?
Robert Haas writes: > On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost wrote: >> * Andrew Dunstan (and...@dunslane.net) wrote: >>> The surprising (to me) consequence was that every superuser was >>> locked out of the system. I had not granted them (or anyone) the >>> role, but nevertheless these lines took effect. >> As I recall, the way we allow superusers to set role to other roles is >> by considering the superuser to be a member of every role. Now, I agree >> that such an approach doesn't make sense for pg_hba consideration. > See bug #5763, and subsequent emails. Short version: Tom argued it > wasn't a bug; Peter and I felt that it was. The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. Therefore, "fixing" this without introducing even-more-surprising behaviors is going to be a very ticklish business. I remain on the side of the fence that says it's not a bug. 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] superusers are members of all roles?
> The problem here is that if Andrew had had the opposite case (a > positive-logic hba entry requiring membership in some group to get into > a database), and that had locked out superusers, he'd be on the warpath > about that too. And with a lot more reason. Actually, I find that behavior surprising -- and undesirable -- too. Enough so that I'm going to have to modify the pg_hba.conf on a couple of production databases tommorrow. -- Josh Berkus PostgreSQL Experts Inc. http://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] superusers are members of all roles?
On 04/07/2011 12:29 AM, Tom Lane wrote: Robert Haas writes: On Wed, Apr 6, 2011 at 7:54 PM, Stephen Frost wrote: * Andrew Dunstan (and...@dunslane.net) wrote: The surprising (to me) consequence was that every superuser was locked out of the system. I had not granted them (or anyone) the role, but nevertheless these lines took effect. As I recall, the way we allow superusers to set role to other roles is by considering the superuser to be a member of every role. Now, I agree that such an approach doesn't make sense for pg_hba consideration. See bug #5763, and subsequent emails. Short version: Tom argued it wasn't a bug; Peter and I felt that it was. The problem here is that if Andrew had had the opposite case (a positive-logic hba entry requiring membership in some group to get into a database), and that had locked out superusers, he'd be on the warpath about that too. And with a lot more reason. In such a case I could add the superusers to the role explicitly, or make the rule cover superusers as well. But as the situation is now, any rule covering a group covers superusers, whether I want it to or not. I'd rather have a choice in the matter (and it's clear I'm not alone in that). The introduction of hot standby has made this pattern more likely to occur. It happened here because we have a bunch of users that are allowed to connect to the standby but not to the master, and the rules I was trying to implement were designed to enforce that exclusion. 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] timezone GUC
Robert Haas writes: > If you have the timezone configured to a non-default value in > postgresql.conf, and you comment it out and reload, it says: > LOG: parameter "TimeZone" removed from configuration file, reset to default > ...but at least when I tested it, it didn't actually appear to reset > it to the default. Hm, interesting. guc-file.l thinks this will fix it in such cases: /* Now we can re-apply the wired-in default */ set_config_option(gconf->name, NULL, context, PGC_S_DEFAULT, GUC_ACTION_SET, true); but for variables where the powerup default is "do nothing just yet", that, um, does nothing just yet. The patch I just applied doesn't change this behavior. I suspect that this "re-apply" logic also fails for cases where the intended default derives from environment variables. Making this work as expected actually looks a bit nasty, because in the case where the config file entry was there at system bootup, we never did compute a state corresponding to its not being there. So it's not just a matter of rolling back to some prior state. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Failed assert ((data - start) == data_size) in heaptuple.c
Hi folks,
I am running a 9.0.3 Hot Standy + Streaming Replication slave which
occasionally segfaults (every 1-2 days). I rebuilt Postgres with
--enable-cassert and --enable-debug, switched on core dumping and
waited for some results.
The first crash since enabling debugging was a failed assert in heaptuple.c:
TRAP: FailedAssertion("!((data - start) == data_size)", File:
"heaptuple.c", Line: 255)
2011-04-07 04:20:20 EST LOG: server process (PID 32195) was
terminated by signal 6: Aborted
2011-04-07 04:20:20 EST LOG: terminating any other active server processes
For context, the only things running on this server are the slave
database, and a tomcat instance. The tomcat instance is the only
connection into this database, which continually runs through a series
of SELECTs (100ms sleep between each run).
The slave database is basically stock standard 9.0.3 config, apart
from the replication setup and shared_buffers increased to 2GB.
Here's the backtrace:
Core was generated by `postgres: backend surecast 127.0.0.1(37155)
SELECT'.
Program terminated with signal 6, Aborted.
#0 0x7f40a93aba75 in *__GI_raise (sig=)
at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
64 ../nptl/sysdeps/unix/sysv/linux/raise.c: No such file or directory.
in ../nptl/sysdeps/unix/sysv/linux/raise.c
(gdb) bt
#0 0x7f40a93aba75 in *__GI_raise (sig=)
at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1 0x7f40a93af5c0 in *__GI_abort () at abort.c:92
#2 0x006f861d in ExceptionalCondition (conditionName=,
errorType=, fileName=,
lineNumber=) at assert.c:57
#3 0x00459b07 in heap_form_minimal_tuple
(tupleDescriptor=0xf6bdd0, values=0x8,
isnull=0xf6c680 "") at heaptuple.c:1459
#4 0x00580d12 in ExecFetchSlotMinimalTuple (slot=0xf6bb90) at
execTuples.c:684
#5 0x00588d10 in ExecHashTableInsert (hashtable=0xf4c3b0, slot=0x7dc3,
hashvalue=6) at nodeHash.c:697
#6 0x00589bf6 in MultiExecHash (node=)
at nodeHash.c:123
#7 0x0058a9ab in ExecHashJoin (node=0xf24008) at nodeHashjoin.c:154
#8 0x005788a8 in ExecProcNode (node=0xf24008) at execProcnode.c:427
#9 0x0058fb21 in ExecNestLoop (node=0xf8eb98) at nodeNestloop.c:120
#10 0x005788c8 in ExecProcNode (node=0xf8eb98) at execProcnode.c:419
#11 0x0057756d in ExecutePlan (queryDesc=0xf8bc30,
direction=32195, count=0)
at execMain.c:1187
#12 standard_ExecutorRun (queryDesc=0xf8bc30, direction=32195,
count=0) at execMain.c:280
#13 0x00642e28 in PortalRunSelect (portal=0xf11f88,
forward=,
count=0, dest=0xe00120) at pquery.c:952
#14 0x006442e9 in PortalRun (portal=,
count=, isTopLevel=,
dest=, altdest=,
completionTag=) at pquery.c:796
#15 0x006419e3 in exec_execute_message (argc=,
argv=, username=) at
postgres.c:2003
#16 PostgresMain (argc=, argv=,
username=) at postgres.c:3988
#17 0x00606a07 in BackendRun () at postmaster.c:3555
#18 BackendStartup () at postmaster.c:3242
#19 ServerLoop () at postmaster.c:1431
#20 0x0060931d in PostmasterMain (argc=14918336, argv=0xdfb160)
at postmaster.c:1092
#21 0x005a9310 in main (argc=5, argv=0xdfb140) at main.c:188
Let me know if there is any additional information I can provide.
Cheers,
BJ
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Postgresql on multi-core CPU's: is this old news?
On 04/05/2011 02:21 PM, Mischa Sandberg wrote: Came across the following in a paper from Oct 2010. Was wondering is this is old news I missed in this group. http://pdos.csail.mit.edu/papers/linux:osdi10.pdf about Linux optimization on multi-core CPU's. Only a little old; http://postgresql.1045698.n5.nabble.com/MIT-benchmarks-pgsql-multicore-up-to-48-performance-td3173545.html shows most of the obvious comments to be made about it. There is more detail explaining why the hand-waving done in the paper about increasing NUM_LOCK_PARTITIONS is not a simple improvement at http://postgresql.1045698.n5.nabble.com/Lock-partitions-td1952557.html -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
Re: [HACKERS] too many dotted names
On 4/7/11, Robert Haas wrote:
> On Wed, Apr 6, 2011 at 4:23 PM, Vladimir Kokovic
> wrote:
>> Hi,
>>
>> Does it make sense to treat these ?
>>
>> ALTER TABLE "s'd"".s'd"""."s's'd""." ADD COLUMN id bigint DEFAULT
>> nextval('"s''d".s''d""."s''d".d"s''"');
>>
>> ERROR: improper relation name (too many dotted names): s'd.s'd"".s'd.d"s'"
>> SQL state: 42601
>
> Treat them as what?
Even nextval('"s''d".s''d""."s''d".d"s''"') is correct literal,
PostgreSQL parser treats them like error.
I think that only solution is to prohibit ". (double quote and dot) to
be part of schema or sequence name.
Best regards,
Vladimir Kokovic, DP senior, Belgrade, Serbia
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] Failed assert ((data - start) == data_size) in heaptuple.c
Brendan Jurd writes:
> TRAP: FailedAssertion("!((data - start) == data_size)", File:
> "heaptuple.c", Line: 255)
[ scratches head ... ] That implies that heap_fill_tuple came to a
different conclusion about a tuple's data size than the immediately
preceding heap_compute_data_size. Which I would sure want to believe
is impossible. Have you checked for flaky memory on this machine?
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
