[GENERAL] Getting user who fired a trigger
Dear All, I have made a trigger procedure that logs all table's changes to the log_table in XML format, but i do need to log a user who did that and not succsessful with this. The documentation (including 37.10. Trigger Procedures) give me nothing. Could somene supply me with a solution or documentation part that i should read? My trigger function is written in PL/Ruby because i found no way to make a table's structure independent function with PL/pgsql. And one more question - is there any special type to store XML instead of text? This question arised from the problem, that i'm planning (and i do need this) to store all the database updation history and some small procedures like adding a user will produce from 1 to 10 records in the log table of text type. So updating of 1 byte will produce kilobytes of text. So, maybe there is some compact type for XML storing? (i know that i could pack it and store in zipped or other format, but i would like to have a search possibility); -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Grant Select privileges for all tables in schema
On Tue, Jul 06, 2004 at 15:44:01 -0700, [EMAIL PROTECTED] wrote: Hi there, I am using Postgresql 7.3 and I want to grant select rights to a user on all tables in a schema, including those that may be created in the future but whose names are not yet known. I want to do something like: GRANT SELECT ON schema.* TO user; but select isn't a valid privilege on a schema and I don't see how wildcards are supported. Is there a way to do this, or does a table need to exist before a user can be granted rights to it, and users must be explicitly granted rights to each table and not in a 'global' way. I would appreciate any thoughts on this. You will need to grant select access to tables and sequences individually after they are created. You might also consider using groups so that you can more easily add and remove user access. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Error in postgreSQL
you should check the logs, when postmaster dies right after starting it always leaves a helpfull message there. On Wed, 2004-07-07 at 10:09, Jos Augusto Tovar wrote: Hi all, Im using postgreSQL in WinME and i had a very strange problem. When i start the postgres the process postmaster starts, but it stops soon after. So i cant use the bd. Does someone know the problem? Tks __ Jos Augusto Tovar - This mail sent through IMP: http://horde.org/imp/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly signature.asc Description: This is a digitally signed message part
[GENERAL] Help with query: indexes on timestamps
Ok, I've tried a number of things here and I know I'm missing something but at
this point my head is spinning (i.e. lack of sleep, too much coffee, etc...)
My environment is PG 7.4.3 on Linux with 512Mb of ram and swap. This was just
upgraded from 7.4 (just to make sure I'm current). Some of my settings in
postgresql are giving fatal errors but I don't think my issue is related to my
query problems. I also have a laptop running with the same basic specs (no
raid, slower processor).
I use a recent pgadmin-III as my client.
We're also running this query in MS-SQL.
I have a table with with 1 million records in it. Here is the definition
CREATE TABLE report
(
match int4,
action varchar(16),
stamp timestamptz,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text
)
WITHOUT OIDS;
The is one index:
CREATE INDEX stamp_idx
ON report
USING btree
(stamp);
That query I'm running is:
SELECT date_part('hour'::text, report.stamp) AS hour, count(*) AS count
FROM report
GROUP BY date_part('hour'::text, report.stamp)
ORDER BY date_part('hour'::text, report.stamp);
Here is the plan I get:
QUERY PLAN
Sort (cost=47420.64..47421.14 rows=200 width=8)
Sort Key: date_part('hour'::text, stamp)
- HashAggregate (cost=47412.00..47413.00 rows=200 width=8)
- Seq Scan on report (cost=0.00..42412.00 rows=100 width=8)
(4 rows)
Now from from I understand that, the index I created would not be used since I
would be looking at every row to do the date part. The query under 7.4 ran in
about 8 seconds. In 7.4.3, its taking 37 seconds for the same plan (which is
fine for the system not be tuned yet). On my laptop its taking 6 seconds.
MS-SQL is taking 8 seconds. These runs are after I do vacuum full, vacuum
analyse and reindex on the database and table respectively
My question: How can I get this query to use an index build on the date_part
function. On the MS-SQL side, creating a computed column with the date part and
then don't an index on that column bring the query done to 2 seconds.
I tried creating this function:
CREATE OR REPLACE FUNCTION whathour(timestamptz)
RETURNS int4 AS
'begin
return date_part(\'hour\',$1);
end;'
LANGUAGE 'plpgsql' IMMUTABLE;
and then and index:
CREATE INDEX hour_idx
ON report
USING btree
(stamp)
WHERE whathour(stamp) = 0 AND whathour(stamp) = 23;
but I get the same plan- which makes sense to me because I'm again inspect
quiet a few row. I'm sure I'm missing something...
I couldn't see from the docs how to make a column equal a function (like
MS-SQL's computed column) but to me it seems like I should not have to do
something like that since it really is wasting space in the table. I hoping a
partial index or a function index will solve this and be just as efficient.
However, that method **does** work. Is there a better way?
Thanks to all in advance.
--
Keith C. Perry, MS E.E.
Director of Networks Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Help with query: indexes on timestamps
Keith C. Perry wrote:
I have a table with with 1 million records in it. Here is the definition
CREATE TABLE report
(
match int4,
action varchar(16),
stamp timestamptz,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text
)
WITHOUT OIDS;
The is one index:
CREATE INDEX stamp_idx
ON report
USING btree
(stamp);
That query I'm running is:
SELECT date_part('hour'::text, report.stamp) AS hour, count(*) AS count
FROM report
GROUP BY date_part('hour'::text, report.stamp)
ORDER BY date_part('hour'::text, report.stamp);
You will always get a sequential scan with this query - there is no
other way to count the rows.
With PostgreSQL being MVCC based, you can't know whether a row is
visible to you without checking it - visiting the index won't help. Even
if it could, you'd still have to visit every row in the index.
Assuming the table is a log, with always increasing timestamps, I'd
create a summary table and query that.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Why do subselects in target lists behave differently wrt implicit casts?
This is a really trivial question, but I'm curious. Why do subselects in target lists behave differently than simple queries? Ex: [lexus] create temporary table bar (key varchar(32) not null); CREATE TABLE [lexus] insert into bar select ''; INSERT 1319585 1 [lexus] insert into bar select (select '') as key; ERROR: failed to find conversion function from unknown to character varying [lexus] insert into bar select (select ''::text) as key; INSERT 1319586 1 Just curious, Mike Mascari ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Getting user who fired a trigger
Anton Nikiforov wrote: Dear All, I have made a trigger procedure that logs all table's changes to the log_table in XML format, but i do need to log a user who did that and not succsessful with this. The documentation (including 37.10. Trigger Procedures) give me nothing. Could somene supply me with a solution or documentation part that i should read? Can't remember which part it is, but you want one of: SELECT CURRENT_USER; SELECT SESSION_USER; My trigger function is written in PL/Ruby because i found no way to make a table's structure independent function with PL/pgsql. And one more question - is there any special type to store XML instead of text? This question arised from the problem, that i'm planning (and i do need this) to store all the database updation history and some small procedures like adding a user will produce from 1 to 10 records in the log table of text type. So updating of 1 byte will produce kilobytes of text. So, maybe there is some compact type for XML storing? (i know that i could pack it and store in zipped or other format, but i would like to have a search possibility); Sorry, I don't think I understand. Do you want to store multiple versions of the same XML document? Or are you logging changes to non-XML data as XML text (and if so why)? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Getting user who fired a trigger
Thanks for the replay, Richard. Richard Huxton wrote: Anton Nikiforov wrote: Dear All, I have made a trigger procedure that logs all table's changes to the log_table in XML format, but i do need to log a user who did that and not succsessful with this. The documentation (including 37.10. Trigger Procedures) give me nothing. Could somene supply me with a solution or documentation part that i should read? Can't remember which part it is, but you want one of: SELECT CURRENT_USER; SELECT SESSION_USER; test=# SELECT CURRENT_USER; current_user -- anton (1 row) test=# SELECT SESSION_USER; session_user -- anton (1 row) This gives the same output. Looks like i should check this with the real operatins to understand the difference or RT*M more. My trigger function is written in PL/Ruby because i found no way to make a table's structure independent function with PL/pgsql. And one more question - is there any special type to store XML instead of text? This question arised from the problem, that i'm planning (and i do need this) to store all the database updation history and some small procedures like adding a user will produce from 1 to 10 records in the log table of text type. So updating of 1 byte will produce kilobytes of text. So, maybe there is some compact type for XML storing? (i know that i could pack it and store in zipped or other format, but i would like to have a search possibility); Sorry, I don't think I understand. Do you want to store multiple versions of the same XML document? Or are you logging changes to non-XML data as XML text (and if so why)? I'm storing XML prepared text in the database in the column of type text. And the question is - is there some more wise way to do that? Like column type XML maybe? :) -- Best regads, Anton Nikiforov smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Help with query: indexes on timestamps
Quoting Richard Huxton [EMAIL PROTECTED]:
Keith C. Perry wrote:
I have a table with with 1 million records in it. Here is the definition
CREATE TABLE report
(
match int4,
action varchar(16),
stamp timestamptz,
account varchar(32),
ipaddress inet,
profile varchar(16),
rating text,
url text
)
WITHOUT OIDS;
The is one index:
CREATE INDEX stamp_idx
ON report
USING btree
(stamp);
That query I'm running is:
SELECT date_part('hour'::text, report.stamp) AS hour, count(*) AS count
FROM report
GROUP BY date_part('hour'::text, report.stamp)
ORDER BY date_part('hour'::text, report.stamp);
You will always get a sequential scan with this query - there is no
other way to count the rows.
With PostgreSQL being MVCC based, you can't know whether a row is
visible to you without checking it - visiting the index won't help. Even
if it could, you'd still have to visit every row in the index.
Assuming the table is a log, with always increasing timestamps, I'd
create a summary table and query that.
Yea, actually it a proxy server log each month the databasae is 500k records. I
have two months loaded only to put some stress on the server. Some ever month
I'm loading the data just so I can do some analysis. The optimization question
came up when one of the other database folks wanted to play with the database in
MS-SQL server.
How can I add a column that respresents a function that returns just the
date_part? I wondering if that will increase the speed of the query in similar
fashion as the MS-SQL did.
I hadn't though about the MVCC vs. file locking issue. The MS-SQL server does
not have any load on it and I'm sure if other users were hitting it the same
table with the same query, PG would be perform better.
--
Keith C. Perry, MS E.E.
Director of Networks Applications
VCSN, Inc.
http://vcsn.com
This email account is being host by:
VCSN, Inc : http://vcsn.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] (no subject)
digest-status LISTNAME ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Getting user who fired a trigger
Anton Nikiforov wrote: Could somene supply me with a solution or documentation part that i should read? Can't remember which part it is, but you want one of: SELECT CURRENT_USER; SELECT SESSION_USER; test=# SELECT CURRENT_USER; current_user -- anton (1 row) test=# SELECT SESSION_USER; session_user -- anton (1 row) This gives the same output. Looks like i should check this with the real operatins to understand the difference or RT*M more. Sometimes a function may be running with the permissions of its creator rather than the user running it. See the CREATE FUNCTION reference for details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] (no subject)
set pgsql-general digest ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql +AMD64 +big address spaces - does it work?
Ok - just to end this thread, I think I understand what I was missing. I'll stop this thread, and just comment on my first thread. Thank you everyone who helped ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
There's a good bit of depth in the archives of this list. I would start searching back for discussions of effective_cache_size, as that is involved in *costing* the caching job that the OS is doing. Thanks - that's just what I need to sink my teeth into. I'll have a trawl and get back later. Regards Andy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
Hello Shridhar, Thanks for the reply. There is no reason why you should not do it. How remains to be a point of disagreement though. You don't allocate 16GB of shared buffers to postgresql. That won't give you performance you need. I think in the other thread, Tom was alluding to this too. What is it about the shared buffer cache behaviour that makes it inefficient when it is very large? (assuming that the address space it occupies is allocated to RAM pages) Is there a good place I could look for some in depth details of its behaviour? Many thanks, Andy ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql +AMD64 +big address spaces - does it work?
I get the feeling that, that regardless 64bit support or not, that the *concept* of a database which just happens to all easily fit within RAM isn't one that gets the thumbs down... Oops, I meant to say '*is*' one that gets the thumbs down... ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
Hello again Mike, Thanks for the replies! Here's my next salvo! Perhaps I'm a bit naive about complex data structure caching strategies, but it seems to me that the overhead of tracking tuples (which is what you would want if you are going to manage your own cache, as opposed to simply caching disk pages as the OS does) would be memory-prohibitive except in the most extreme cases of RAMDB SIZE. But doesn't postgresql already make a decision about what needs to be in its workspace prior to executing a plan? I'm not actually suggesting it do anything different. In fact, the only thing I'm suggesting is that the workspace need not be limited in size. Remember - I'm only talking about the scenario where the entire database - every single tuple - can reside in the workspace (the shared buffer cache) at the same time. There is no extra 'managing your own cache' as you put it. Just let postgresql's postmaster do what it does. (assuming it doesn't kick stuff out of its shared buffer cache unless it needs the space for something else... ok -that may be an incorrect assumption - I need to find out for sure) But I think the overall effort is less, not more. Once in the shared buffer cache, a tuple never needs to be kicked out again to make way for another. That's what is so nice. Everything is just there... No need to calculate a least damaging cache replacement strategy, no need to wait for blocks of data to work their way from the disk or linux buffer cache to the postgresql workspace -it all ends up there, and stays there, with only syncing activity needing to take place when updates happen. I find that experience does not bear this out. There is a saying a coworker of mine has about apps that try to solve problems, in this case caching, that are well understood and generally handled well at other levels of the software stack... he calls them too smart by half :) Well, I guess I'd agree and disagree with your friend. What a political animal I am! Here's why. Imagine this scenario. Your application uses 5 bits of data - A B C D and E. Only 3 can sit in memory at once. You want to do two transactions. The first combines A, B and C, so you load those, in order. Once you're finished with that first transaction, you then require to combine A, C and D. Now, an application driven cache will see immediately that the optimal cache replacement is to load D into the slot taken by B. With the OS in charge, you'll probably end up with a cache thrash. This is simplistic, but it is just this sort of smart caching that can lead to dramatic speedups, and I've got quite a bit of experience of seeing just that. I don't want to devalue the linux cache strategy. It is very appropriate for almost all situations. I'm talking about a very specific scenario here though. Does that make sense? The problem this this assumption, as Tom Lane has said, is that generally speaking, a kernel is much faster with respect to cache *lookup* than postgres is. Postgres assumes that only a subset of the data stored in a cluster will be needed at any one time. Under this assumption (that it will need to store and shovel through thousands, instead of millions or more, tuples) some optimizations have been made that would cause performance degradation if used over the entire (potentially enormous) dataset. Again, in my scenario, postgres is *relieved* of the task of having to decide what data to choose to be in the workspace, since ALL of the data is accessible there in my scenario. The point about postgres having to shovel through millions of tuples is a different point. If I do a query which can only be executed by looking at a million tuples, then that is what has to happen, no matter what caching strategy you employ. Obviously, if you're shovelling data in and out of memory, then the *way* you traverse those tuples becomes important to efficiency, as loading sequential stuff off a disk is always faster. If it's *all* in the workspace, well it doesn't matter at all then - the planner can forget about disks and choose the fastest algorithmic option. Again - this is a simplification, but I hope you can see what I'm getting at. Come now, Nov. 2001? Most of the issues brought up here have been solved *in the kernel* since then. (In the general case, with the bevy of IO schedulers, and the interuptable kernel.) The point is that if your data is sitting in two parts of memory at the same time, then that's a waste of memory, and it's extra time copying all that stuff around. The date of the article isn't relevant to this, I don't think. They make the point that no matter how efficient the OS kernel is, in the sort of scenario I am interested in, it just ends up being an unnecessary step. Well, Oracle isn't PG, and I can't speak to their buffering and caching implementation. I'm just saying that these issues are generic issues for any high performance database server, regardless of whether it is Oracle,
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
If all the data fits into memory, then this cache thrashing won't occur, yes? No - it *can* occur in a 2 tier cache strategy. The critical question here is: *If* the data postgresql needs is in the linux buffer cache, what (if anything) does the OS have to do to make it available to the postmaster? The worst case would be an actual memory copy of the data from the linux cache to where postgresql actually asked for the file data to be loaded. I'll need to find out more about the linux buffer cache to answer this question for myself. If a memory copy is required, then this is a good way of visualising it: I live in London, but have to work in 5th avenue, NYC for a week. My company stupidly sets me up in a smelly hotel by JFK airport. London is 'The disk system' 5th Avenue is 'The postgresql workspace' My smelly hotel at JFK is the 'linux buffer cache'. Clearly, it's far quicker to commute to work each day from JFK, than it is to commute from London each day. But wouldn't it be faster still if I had a room in the swanky hotel on Fifth avenue above the office? Yes, I'd still have to arrive at JFK, but then I could stay in 5th avenue all week until I'm finished. And as far as direct IO is concerned, it gets better: that would be like taking a plane right straight from London to 5th avenue, avoiding the tedious immigration line at JFK. Having said all of that, I'll go and find more about the linux buffer cache before I pester the Postrgresql cache experts. snipped your test plan Thanks for that test plan... I'm in the same position with you w.r.t. machinery - hence my thread (I was hoping someone had tried this!). But I will get my hands on something quite soon and I'll do your test and post the results. I knew I'd have no option than to become very familiar with the behaviour of whatever DB I chose, so I'm looking forward to it (sort of) That is true, and will be until PG implements it's own cache memory management AND on-disk storage. I'd be willing to bet that any patches that dramatically increase speed for the majority of use cases would be considered strongly. I have to find out for sure, but since linux is being used in lots of HPC clusters now and for some real time applications, I'd be very surprised if there wasn't already a direct IO strategy in the kernel, but that's one of the things I'm looking at now. That being the case, and assuming that the postgresql cache operates on arbitrary sized pages (it seems set at 8KB pages by default - which, depending on the platform may be good, assuming also that the address space is appropriately machine page aligned), then it might be 'very easy indeed' to upgrade postgresql's cache manager to fire the necessary IO calls at the OS. Again - this is really something I need to talk to a pg cache expert about, but I've got a lot of catching up to do first. By the way - Direct IO doesn't mean the application has to know about the disk configuration or anything messy like that. It is still a block IO operation directed through the kernel. From the programmer point of view the changes are trivial. It just bypasses the OS's own generic file cache, and does a super efficient single DMA memory transfer between the IO system and the host RAM - all of this managed has to be managed by the OS, or you'll end up with a platform specific implementation that is of no real use. I would run a mile if this work required Postgresql to know about actual storage. Finding target data (complex lookup in the PG shared buffers) will take longer than transferring disk pages from the OS cache, based on index information, and then doing a simple, fast scan. This is the current working assumption for postres. This is what scares me a little. Why would the PG cache lookup be complex? That's a question for me to find the answer to... I'll wade through the developer resources and source code etc. before bothering the experts. Your contention, correct me if I've gotten this wrong, is that transferring it from the OS cache will be slower than finding it using a more complex lookup, but without moving the data from the OS cache to the PG workspace. In both scenarios all data is in RAM. Well if the PG cache is slower at dealing with large data, then yes, I concede that this changes the picture. But at this point in time, my gut reaction would be to assume that the PG cache is blazingly efficient. It is, after all, the heart of the DB, and all the DBs I've seen have a similar architecture. I see what you mean about a waste of memory, but if the costing parameters are tuned well for the use of the specific DB then current evidence with postgres shows that the former strategy is faster. I would be interested to see if the latter scenario could be made to work with the current PG codebase, or even if there is a simple patch that could speed up the shared_buffers case. Well it's still not clear that the memory does get wasted, in which case this 'problem' doesn't exist (See
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
It's not that making the cache bigger is inefficient, it's that the cache is not used the way you are thinking. Ok, I think I've got it now. The missing piece of the puzzle was the existence of the Linux buffer cache. So that's what the effective_buffer_cache value is for(!) I read Shridhar Daithankar and Josh Berkus's 'Tuning Postgresql for performance' document, and some things stike me (though only with respect to my RAM much bigger than database scenario.) I think I'm a bit more depressed than I was earlier today! 1. Postgresql is a two tiered cache mechanism. The first tier - the postgresql shared buffer cache sits on the second, larger tier, the linux buffer cache. So bits of the same data end up being in memory...twice, and two cache mechanisms operate at the same time. (That's how I understand it). 2. Even if the linux buffer cache contains all the data required for an execution of a plan, there is still a load of memory copying to do between these two tiers. Though memory copying is faster than disk access, it is still an overhead, and isn't there the real problem of thrashing between these two tiers if the plan can't fit all the data into the top tier, even if the thrashing is restricted to the memory system? 3. The OS will implement a 'Least recently Used' cache replacement strategy on data in its cache. This isn't the optimal cache replacement strategy for the database. If postgresql is in charge of all its RAM resident data, it can make more intelligent decisions about which stuff isn't needed once used. This still leads me to think that the best thing to do in my 'much bigger RAM than database size' scenario would be for postgresql to allocate a shared buffer cache big enough for all the data + a bit. By doing this, there would be some performance enhancing gains, including: 1. If the planner *knew* (rather than guessed) that all the data was effectively 'there' in RAM, in the right place (i.e. its workspace), wouldn't it make choosing the optimal plan easier? (I see that effective_buffer_cache is a stab in that direction, but just because postgresql guesses the data will have been cached by linux, it doesn't mean it actually is - surely an important distinction.) 2. You'd avoid a whole layer of caching, along with the not necessarily aligned memory copies and other overheads that this introduces. Even in the optimal case where all the data needed does reside in RAM, it's not in the right bit of RAM. (I may have misunderstood this relationship between the shared buffer cache and the linux buffer cache - if no memory copying actually occurs - then I'll go away for a bit!) Two interesting things I dug up today: www.linuxjournal.com/article.php?sid=5482 (to do with a high performance DB living in an OS controlled environment) and http://www.ixora.com.au/tips/avoid_buffered_io.htm (to do with Oracle tuning) The way things are, I see that postgresql will 'gracefully degrade' in the very common scenario where the database shares a machine with limited resources and lots of processes, all of them hammering the machine, but in my 'optimal RAM optimised db server ' scenario, the scheme seems actually to reduce the potential for a blistering system. So given all of that, I can't help swinging back to my original question about whether it's a bad thing to make the shared buffer cache huge, so long as you have much more RAM than the size you choose. It'll still require all that nasty Linux cache -postgresql cache memory copying, but it will at least reduce postgresql's exposure to the 'not particularly clever' least recently used cache replacement strategy employed by the OS. Am I getting any closer to understanding the way things are? Thanks for your tolerance, Andy p.s. (It would be great if you could just load the data directly into the shared buffer cache with direct IO, since that OS file buffer-shared buffer cache transfer is just a waste of effort in this scenario), and direct IO allows spectacular IO gains when you're working with certain raid configurations connected with a suitable fat wire.) Ok - I'll shutup now ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] postgresql +AMD64 +big address spaces - does it work?
Hello again and thanks to everyone for the replies so far.
Tom, and all, I hear what you are all saying, and furthermore, in cases
where the amount of RAM is much smaller than the database size, I agree
totally. However, I'm *only* talking about a particular scenario which, till
now, has really only been true of small databases, and that is:
RAM - (total size of database) = (easily enough RAM for everything else to
run smoothly on the server).
I get the feeling that, that regardless 64bit support or not, that the
*concept* of a database which just happens to all easily fit within RAM
isn't one that gets the thumbs down...
...and I think I must be missing something!
If you bear with me, could I just give you a better idea of where I'm coming
from, because I think I may have missed an important concept, or might be
able to sway you. (I don't mind which way this goes, other than my server
dreams will be in tatters if you're right and I'm confused, so be gentle
with me!)
Before I start, just to repeat that that though I have only been using
postgresql for 18 months and haven't run a huge database before, I do have a
large amount of experience of designing memory intensive multi-threaded real
time applications running in a unix virtual memory environment (specifically
SGI IRIX boxes), and am very comfortable with the postrgresql implementation
model. (and I did choose it over MySQL, so I am a fan.)
Ok!
---
Where I'm coming from!
---
I am developing a postgresql based national website, which hopefully will
have a million or two users if successful (though hopefully not all at once
:) )
The details aren't important, other than to say that the functionality I'm
planning is quite advanced - it's more of a web application than website as
such, and the database is going to get an absolute hammering. Most
importantly, a large percentage of the data within the database is going to
be 'active' (i.e. the target of queries) on a minute by minute basis.
Luckily for me, it isn't due to go live just yet :)
There is now 'proper' 64 bit support in Linux for both Intel and AMD and
we've already seen a few linux boxes with half a terabyte of RAM - shareable
as a single address space. (e.g. boxes like the Altix 3000 from SGI) Even
commodity server boxes (such as the HP DL585) now provide the environment
for multiple threads to share a single huge address space backed up by up to
64GB of RAM, accessible by 4 CPUs. I'm not more up to speed on the hardware
side, but this is only going one way: up!
So, with this new hardware and 64 bit OSs, it is theoretically possible to
dramatically reduce the IO bottlenecks on a database server by simply
sticking in enough RAM so that the database ends up in RAM, leaving most of
the IO being 'O' for syncing updates.
We're talking about High Performance Database
Surely this is a very desirable setup for someone like me?
Here's how it could work:
1. The postgresql postmaster (is that right?) - the process responsible for
setting up the shared buffer cache - does what it normally does, except
that it works with 64 bit addressing so your cache can be 40MB, or 200GB.
(CPU limited to 64GB currently in most 64bit processor systems but this will
change)
2. You setup the buffer cache size so that it could easily hold *the entire
database*. (Or the active part... the decision of how big is subject to all
the current guidelines. The only difference is that there is no 2GB limit).
e.g. on my 64GB system, my entire database is 16GB and I instruct postgresql
to setup a 20GB shared buffer cache, in order to allow for headroom for
updates.
3. At the postgresql level, database pages ('buffers?') get cached, and stay
in the buffer cache, because there is enough space for everything to reside
in the postgresql cache and more.
4. Additionally, at the virtual memory level, Linux isn't going to kick
pages of this huge shared buffer cache out to disk, because there's plenty
of RAM kicking around for everything else, and in fact, the target for such
a machine is for it to be running at close to zero swap activity. We're
talking about a high performance system after all, and swap activity is the
last thing you want in a high performance setup.
Note that Linux is still managing *all the memory*. I'm not interfering at
all with virtual memory. (Though IRIX's mpin() is very handy... I must find
out if Linux has a similar call)
5. Assuming your database *does* easily fit in the shared buffer cache,
queries will run like the wind, limited only by CPU availability and the
performance of the front end web servers + connections to deliver queries
and accept the results.
6. You get more bang for your buck, especially in systems with a heavy load
of concurrent postgresql backend activity, which would normally put enormous
strain on the IO system.
7. An extra benefit is that the database
[GENERAL] FATAL: the database system is in recovery mode
We're developing a java app and are using postgres as the database. On our dev server I started the app, closed it, but the java process was still open so I killed it, which caused the above error. I've had to do this in the past but have not had this happen before. I've searched the archives and found a message/reply from Andrew Sullivan about this which implies that it may be more a file system problem. We're using postgresql 7.4.1 on a debian box (and yes, ext2). And fsync is turned on. Aside from not killing processes which hang, is there anything I can do to avoid this problem in the future? Thanks Ron ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Column name 'user' not allowed?
Hi, I tried to install phpopenchat but I can't create this table: poc= CREATE TABLE poc_user_account ( poc( USER varchar(255) NOT NULL, poc( PASSWORD varchar(255), poc( CONFIRM_CODE char(32), poc( DISABLED int NOT NULL DEFAULT '0', poc( KICKED int NOT NULL DEFAULT '0', poc( PASSWORD_NEW varchar(255), poc( PRIMARY KEY (USER) poc( ); ERROR: syntax error at or near USER at character 35 After searching a while what's wrong I renamed column USER to USER2 and now I can create the table! I don't see a reason why column name USER isn't allowed?! I am running Postgres 7.4.2 on Linux. Thanks! Thomas -- http://www.tmueller.com for pgp key (95702B3B) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
