[SQL] query optimazation & starting postmaster with -B option
hai, Q 1 : Posgresql is running on Linux machine with 512MB ram. My question is When i start the postmaster with -B 3000 then there is no problem. If the start the postmaster with more than the 3000 then errors msg is popped out. How do I increase this buffer option morethan 3000 and what are things to be considered before doing this Q 2: postmaster with -N option. doc says 32 connection is default . I need more than that 32 connection. Before increase this connection to 100 what things (like memory,processor speed .. etc ) we have to take consideration. Q3 : If i increase the -B more than 3000 then will postgresql execute the query very fast thanks advance. kind regds. p. ashok kumar __ Do You Yahoo!? Get Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] Why do I need to set UPDATE permissions for fkey check?
Jon Lapham wrote: > On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote: > > > > It's a known problem in the foreign key code. The reason is that > > the fk triggers use SELECT FOR UPDATE to select the matching > > rows that it is checking and the reason for using FOR UPDATE is > > to lock those rows so that someone cannot delete/change them out > > from under your nose while you're looking at them. However, > > SELECT FOR UPDATE is asking for update permissions because it > > grabs that row lock. > > Oh, okay, I understand your explanation, and it fits with what I am > seeing. > > But... > > ...this is a READ ONLY table! Maybe it would be possible to have the fkey > triggers look to see if the table is read-only, and then simply use SELECT > instead of SELECT FOR UPDATE and then not perform the row locking? Since > this is a read-only table, there would be no risk of deleting/changing any > of the data. Yeah, I realize that with this solution, you cannot > guarantee that the table doesn't become 'writable' sometime during the > fkey lookup. The problem only exists for concurrent access. If the rows don't get locked, any user with write permissions could delete a row where another one actually inserts a reference for. And you cannot take write permissions away from superusers. This would violate the constraint "silently", because the "check" on the fkey table is already done, but the insert not yet committed, while the "referential action" on the pkey table saw no references and permits deletion. > It would seem to me that this is a serious problem. I absolutely cannot > have my data table be writable, and I need to maintain fkey integrity. > Urg this is very bad, the fkey integrity check is the reason I > installed Pg v7. I would think that keeping read-only static data table > would be a common database occurance, any suggestions on how to get around > this issue? Possibly with a (gulp) permissions switching trigger (gulp)? It is a serious problem, indeed. I'll post a proposal to fix it for 7.1 in a separate message. I have something in mind so far, but need to play around with the code before knowing all the odds and ends. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] create function - user permissions
Markus Wagner wrote: > Hi, > > one of my users wants to create functions using the C language, but pgs > says "no permission". > > How can I permit the user to do this, while avoiding to give him root > access rights? Assuming with "root" access you mean "DB-superuser" rights. No way! From the C language, he has total access to the DB backend, running under the OS-side database system owners UID. That means, he has complete OS access to the entire DB installation! He could read/write system catalogs without any permission checks (SPI is only for convienience, someone can use heap-am as well). So in fact, he IS a superuser then. And that should be reflected in his rights. Giving him the right would also prevent him from doing stupid things to gain access for data he shouldn't have, possibly corrupting you DB installation by accident. If that all isn't an option for you, give him his own DB installation where he is his own superuser. There he can develop those things and after code revision, you install the modules in the production system. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] query optimazation & starting postmaster with -B option
As I've found through countless trial and error and many emails to this list, performance is mostly in how you structure queries and how you use the backend (indexes, proper VACUUMing etc etc).. Increasing the size passed as -S and -B options will help -- there is probably much more that can be done if you can get specific with us about your tables and what queries you're having trouble with.. Good luck!! -Mitch - Original Message - From: "sathya priya" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 22, 2000 4:34 AM Subject: [SQL] query optimazation & starting postmaster with -B option > hai, > > Q 1 : > > > Posgresql is running on Linux machine with 512MB ram. > My question is When i start the postmaster with -B > 3000 then there is no problem. If the start the > postmaster with more than the 3000 then errors msg is > popped out. How do I increase this buffer option > morethan 3000 and what are things to be considered > before doing this > > > > Q 2: postmaster with -N option. > doc says 32 connection is default . > I need more than that 32 connection. Before increase > this connection to 100 what things (like > memory,processor speed .. etc ) we have to take > consideration. > > Q3 : If i increase the -B more than 3000 then will > postgresql execute the query very fast > > > thanks advance. > > kind regds. > p. ashok kumar > > > __ > Do You Yahoo!? > Get Yahoo! Mail - Free email you can access from anywhere! > http://mail.yahoo.com/ >
