Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
"Magnus Hagander" wrote: >> - Max_connections is set to 500. I did that originally because I kept >> seeing a message about no connection available and I thought it was >> because I was not allocating enough connections. My machine has 2GB of RAM. > >There's your problem. 500 is way above what the windows version can handle. >IIRC the hard max is somewhere around 200 depending on some OS factors that >we don't entirely know. I'd never recommend going above 100-150. With no more >than 2Gb ram, not above 100. My guess is that Windows is running out of handles. Each backend uses about 150 handles. 100 Backends means 15000 handles. Depending how many other programs are currently running the no. of startable backends will vary depending on the total handle limit Windows imposes. Rainer ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
> > > Maybe we should put an #ifdef WIN32 into guc.c to limit > > > max_connections to something we know the platform can stand? It'd > > > be more comfortable if we understood exactly where the limit was, > > > but I think I'd rather have an "I'm sorry Dave, I can't do that" > > > than random-seeming crashes. > > > > Yeayh, that's probably a good idea - except we never managed to > > figure out where the limit is. It appears to vary pretty wildly > > between different machines, for reasons we don't really know why > > (total RAM has some effect on it, but that's not the only one, for > > example) > > How about we just emit a warning.. > > WARNING: Connections above 250 on Windows platforms may have > unpredictable results. > That's probably a better idea. I'll go look at that unless people feel we should just stick it in docd/faq? /Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] uniquely indexing Celko's nested set model
On 10/19/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > Is it possible to constraint both the LEFT and RIGHT fields of a record to > use the same index? I am looking for a way to ensure for all LEFTs and > RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to > same value. I found the celko's approach to be not very scalable...if you do any inserts at all into the tree the table will thrash terribly. Have you eliminated other approaches, such as arrays, ltree, etc? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
"Magnus Hagander" <[EMAIL PROTECTED]> writes: >> How about we just emit a warning.. >> >> WARNING: Connections above 250 on Windows platforms may have >> unpredictable results. > That's probably a better idea. I'll go look at that unless people feel we > should just stick it in docd/faq? Unless we've got some credible basis for citing a particular number, I don't think this will help much. Rainer Bauer <[EMAIL PROTECTED]> writes: > My guess is that Windows is running out of handles. Each backend uses about > 150 handles. 100 Backends means 15000 handles. Depending how many other > programs are currently running the no. of startable backends will vary > depending on the total handle limit Windows imposes. I find this theory very interesting; for one thing it explains the reported variability of results, since the non-Postgres demand for handles could be anything. Is there any way we could check it? If it's accurate, what we ought to be whining about is some combination of max_connections and max_files_per_process, rather than only considering the former. regards, tom lane ---(end of broadcast)--- TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit
Tom Lane wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: >>> How about we just emit a warning.. >>> >>> WARNING: Connections above 250 on Windows platforms may have >>> unpredictable results. > >> That's probably a better idea. I'll go look at that unless people feel we >> should just stick it in docd/faq? > > Unless we've got some credible basis for citing a particular number, > I don't think this will help much. ok. Maybe a note in the docs or FAQ at least? > Rainer Bauer <[EMAIL PROTECTED]> writes: >> My guess is that Windows is running out of handles. Each backend uses about >> 150 handles. 100 Backends means 15000 handles. Depending how many other >> programs are currently running the no. of startable backends will vary >> depending on the total handle limit Windows imposes. > > I find this theory very interesting; for one thing it explains the > reported variability of results, since the non-Postgres demand for > handles could be anything. Is there any way we could check it? > If it's accurate, what we ought to be whining about is some > combination of max_connections and max_files_per_process, rather > than only considering the former. It's not that simple. Merlin ran some checks, and drastically reducing max_files_per_process made no measurable difference. My best guess is it's due to the non-paged pool. Handles are a part of what goes in there, but only a part. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] uniquely indexing Celko's nested set model
On Oct 20, 2007, at 7:33 , Merlin Moncure wrote: On 10/19/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: Is it possible to constraint both the LEFT and RIGHT fields of a record to use the same index? I am looking for a way to ensure for all LEFTs and RIGHTs in a table, that is it is impossible for any LEFT or RIGHT to have to same value. I found the celko's approach to be not very scalable...if you do any inserts at all into the tree the table will thrash terribly. Have you eliminated other approaches, such as arrays, ltree, etc? I believe it's a trade off: if you're doing a lot of aggregate work and not very many updates, nested sets works very well: adjacency lists aren't as good for this because of the necessity of following the hierarchy from parent to child. If your hierarchy is updated frequently, yes, you'll have a lot of thrashing as everything above and to the left of the update must be updated as well. AFAIK, there isn't currently a single best solution for representing trees in SQL. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/17/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: > On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote: > > Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections > > to something we know the platform can stand? It'd be more comfortable > > if we understood exactly where the limit was, but I think I'd rather > > have an "I'm sorry Dave, I can't do that" than random-seeming crashes. > > Yeayh, that's probably a good idea - except we never managed to figure out > where the limit is. It appears to vary pretty wildly between different > machines, for reasons we don't really know why (total RAM has some effect > on it, but that's not the only one, for example) I tried generating idle connections in an effort to reproduce Laurent's problem, but I ran into a local limit instead: for each backend, postmaster creates a thread and burns 4MB of its 2GB address space. It fails around 490. Laurent's issue must depend on other load characteristics. It's possible to get a trace of DLL loads, but I haven't found a noninvasive way of doing that. It seems to require a debugger be attached. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > "Magnus Hagander" wrote: > > >> - Max_connections is set to 500. I did that originally because I kept > >> seeing a message about no connection available and I thought it was > >> because I was not allocating enough connections. My machine has 2GB of RAM. > > > >There's your problem. 500 is way above what the windows version can handle. > >IIRC the hard max is somewhere around 200 depending on some OS factors that > >we don't entirely know. I'd never recommend going above 100-150. With no > >more than 2Gb ram, not above 100. > > My guess is that Windows is running out of handles. Each backend uses about > 150 handles. 100 Backends means 15000 handles. Depending how many other > programs are currently running the no. of startable backends will vary > depending on the total handle limit Windows imposes. Those are kernel object handles; the ceiling does depend on available kernel memory, but they're cheap, and postgres is in no danger of running into that limit. Most of the handle limits people talk about are on USER (window etc) objects, which come from a single shared pool. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Order-independent multi-field uniqueness constraint?
On 10/19/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> "Kynn Jones" <[EMAIL PROTECTED]> writes:
>
> > CREATE OR REPLACE FUNCTION canonicalize( anyelement, anyelement )
> > RETURNS anyarray AS
> > $$
> > BEGIN
> > IF $1 < $2 THEN RETURN ARRAY[ $1, $2 ];
> > ELSERETURN ARRAY[ $2, $1 ];
> > END IF;
> > END;
> > $$ LANGUAGE plpgsql;
>
> You need to add IMMUTABLE as well.
>
> > and this function works as expected, but when I try to use it in a
> > constraint I get the error:
> >
> > -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
> > ERROR: 42601: syntax error at or near "("
> > LINE 1: ...E foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));
>
> What you need is:
>
> CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y));
Yep, that did the trick.
> > I found this puzzling; it's not clear to me why UNIQUE(UPPER(x)) is OK
> > syntax but not UNIQUE(my_function(x)).
>
> Really? It doesn't work for me in the ADD CONSTRAINT syntax.
My mistake, sorry. I was probably misremembering something I saw in a
CREATE INDEX statement.
Thanks!
kj
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
--- "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > > How about we just emit a warning.. > > WARNING: Connections above 250 on Windows platforms may have > unpredictable results. > > Joshua D. Drake > I'd personally vote for a lower warning limit like 175 as I can consistently crash Postgresql on Windows system right around the 200th connection. Regards, Shelby Cain __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question regarding Hibernate ORDER BY issue
On Friday 19 October 2007 3:03 pm, Valerie Cole wrote:
> Hello
>
>
>
> I have a problem and am pretty sure Hibernate is the culprit but was
> wondering if anybody knew of a fix. We are working on legacy code and
> converted a class from Hibernate 2 xml mappings to Hibernate 3 with
> annotations. On one of the One To Many attributes we have used the
> @OrderBy("displayPosition"). The SQL generated by Hibernate outputs the
> column name as DisplayPosition with no quoting, and Postgres kicks back
> an error saying the column does not exist. Our tables/columns have all
> been created with quotes and must be accessed with quotes (I don't know
> if that is the norm, I am somewhat of a Postgres newb). I have been
> Googling for about an hour and unable to come up with anything, so I
> thought I would drop a line.
>
>
>
> Thanks,
>
>
>
> V. Cole
You might to look at:
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html
See esp. section 5.4 on SQL quoted identifiers
--
Adrian Klaver
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/20/07, Shelby Cain <[EMAIL PROTECTED]> wrote: > I'd personally vote for a lower warning limit like 175 as I can > consistently crash Postgresql on Windows system right around the 200th > connection. What error gets logged for your crashes? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] UNIQUE INDEX and PRIMARY KEY
This is a follow-up to a question I asked earlier. On 10/19/07, Gregory Stark <[EMAIL PROTECTED]> wrote: > What you need is: > > CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y)); > > > LOCATION: base_yyerror, scan.l:795 OK, now, what if instead of this -> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y)); what I was trying to do was this -> ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY(canonicalize(x,y)); Of course, this also elicits a syntax error, but is there a way to achieve the same effect by creating some index? I realize that, as far as the indexing and the enforcement of the uniqueness constraint are concerned, the earlier solution using "CREATE UNIQUE INDEX" works perfectly fine. But some software that I use (Perl modules, etc.) get very confused whenever a table does not have a primary key. Of course, I could just add one more PRIMARY KEY constraint: ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY(x, y); which would be satisfied automatically whenever the UNIQUE constraint implicit in the index foo_uniq_x_y (see above) is satisfied. But this amounts to maintaining an entirely superfluous index. In short, my question is: is there a way to designate a pre-existing UNIQUE INDEX (based on data contained in NOT NULL fields) as the basis for a table's PRIMARY KEY? TIA! kj ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] UNIQUE INDEX and PRIMARY KEY
"Kynn Jones" <[EMAIL PROTECTED]> writes: > In short, my question is: is there a way to designate a pre-existing > UNIQUE INDEX (based on data contained in NOT NULL fields) as the basis > for a table's PRIMARY KEY? No. If there were, that client software you mention would very likely still get confused. Both the SQL standard and a lot of code assume that the constituents of a PRIMARY KEY are just-plain-columns. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
"Trevor Talbot" wrote: >On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: >> "Magnus Hagander" wrote: >> >> >> - Max_connections is set to 500. I did that originally because I kept >> >> seeing a message about no connection available and I thought it was >> >> because I was not allocating enough connections. My machine has 2GB of >> >> RAM. >> > >> >There's your problem. 500 is way above what the windows version can handle. >> >IIRC the hard max is somewhere around 200 depending on some OS factors >> >that we don't entirely know. I'd never recommend going above 100-150. With >> >no more than 2Gb ram, not above 100. >> >> My guess is that Windows is running out of handles. Each backend uses about >> 150 handles. 100 Backends means 15000 handles. Depending how many other >> programs are currently running the no. of startable backends will vary >> depending on the total handle limit Windows imposes. > >Those are kernel object handles; the ceiling does depend on available >kernel memory, but they're cheap, and postgres is in no danger of >running into that limit. Most of the handle limits people talk about >are on USER (window etc) objects, which come from a single shared >pool. You are right. I just did a quick test and depending on the handle type these limits are quite high. I could create 5 millions events or 4 millions semaphores or 3,5 millions mutexes before the system returned error 1816 ERROR_NOT_ENOUGH_QUOTA "Not enough quota is available to process this command.". Rainer ---(end of broadcast)--- TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit
--- Trevor Talbot <[EMAIL PROTECTED]> wrote: > On 10/20/07, Shelby Cain <[EMAIL PROTECTED]> wrote: > > > I'd personally vote for a lower warning limit like 175 as I can > > consistently crash Postgresql on Windows system right around the > 200th > > connection. > > What error gets logged for your crashes? > It's been a while but IIRC there wasn't anything in the logs other than an entry noting that a backend had crashed unexpectedly so the postmaster was restarting all active backends. I can trivially reproduce it at work on my workstation if you need the exact error text. Regards, Shelby Cain __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
I wrote: >You are right. I just did a quick test and depending on the handle type these >limits are quite high. I could create 5 millions events or 4 millions >semaphores or 3,5 millions mutexes before the system returned error 1816 >ERROR_NOT_ENOUGH_QUOTA "Not enough quota is available to process this >command.". [Does some further testing] The limit is high, but nonetheless Postgres is running out of handles. Setting to 1 and starting postgres _without_ any connection consumes 4 handles. This correspodends to the 4 Postgres processes running after the server was started. Every new connection consumes another 1 handles. I don't know the Postgres code involved, but it seems that every backend consumes at least handles. Hence increasing this value will have the opposite effect once a certain threshold is met. Rainer ---(end of broadcast)--- TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/20/07, Shelby Cain <[EMAIL PROTECTED]> wrote: > > --- Trevor Talbot <[EMAIL PROTECTED]> wrote: > > > On 10/20/07, Shelby Cain <[EMAIL PROTECTED]> wrote: > > > > > I'd personally vote for a lower warning limit like 175 as I can > > > consistently crash Postgresql on Windows system right around the > > 200th > > > connection. > > > > What error gets logged for your crashes? > > > > It's been a while but IIRC there wasn't anything in the logs other than > an entry noting that a backend had crashed unexpectedly so the > postmaster was restarting all active backends. I can trivially > reproduce it at work on my workstation if you need the exact error > text. I think it would be useful; if nothing else, maybe it'll tell us if you can see the same problem Laruent does, or if it's a different limit entirely. ---(end of broadcast)--- TIP 1: 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] 8.2.3: Server crashes on Windows using Eclipse/Junit
Shelby Cain wrote: >--- Trevor Talbot <[EMAIL PROTECTED]> wrote: > >> On 10/20/07, Shelby Cain <[EMAIL PROTECTED]> wrote: >> >> > I'd personally vote for a lower warning limit like 175 as I can >> > consistently crash Postgresql on Windows system right around the >> 200th >> > connection. >> >> What error gets logged for your crashes? >> > >It's been a while but IIRC there wasn't anything in the logs other than >an entry noting that a backend had crashed unexpectedly so the >postmaster was restarting all active backends. I can trivially >reproduce it at work on my workstation if you need the exact error >text. I could reproduce this here: Server closed the connection unexpectedly This probaly means the server terminated abnormally before or while processing the request 2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code -1073741502 Shelby, are you using the /3GB switch by chance? This will half the no. of available handles on your system. Rainer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > I could reproduce this here: > > Server closed the connection unexpectedly > This probaly means the server terminated abnormally before or while processing > the request > > 2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code > -1073741502 How? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
--- Rainer Bauer <[EMAIL PROTECTED]> wrote: > I could reproduce this here: > > Server closed the connection unexpectedly > This probaly means the server terminated abnormally before or while > processing > the request > > 2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit > code > -1073741502 > > > Shelby, are you using the /3GB switch by chance? This will half the > no. of > available handles on your system. > > Rainer > Probably not although I haven't examined boot.ini. My workstation only has 1.5 GB of ram so I'm highly doubtful that IBM would have configured it to boot with the /3GB switch. Regards, Shelby Cain __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Fwd: Re[2]: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
I wrote > Anyway, the problem are the no. of semaphores created by Postgres: > Every backend creates at least 4* semaphores. Sorry, this must read semaphores, not 4 times. Rainer ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Hello Trevor, Sunday, October 21, 2007, 12:15:25 AM, you wrote: TT> On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: >> I could reproduce this here: >> >> Server closed the connection unexpectedly >> This probaly means the server terminated abnormally before or while >> processing >> the request >> >> 2007-10-20 23:33:42 LOG: server process (PID 5240) exited with exit code >> -1073741502 TT> How? Seems like the mailiming list is not catching up fast enough (I am posting through usenet)... Anyway, the problem are the no. of semaphores created by Postgres: Every backend creates at least 4* semaphores. Just increase to an unusual high value (say 1) and start creating new connections while monitoring the handle count. Rainer ---(end of broadcast)--- TIP 1: 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
[GENERAL] Perhaps silly question about empty search_path
Hello; To begin with, I'm not certain how useful it would be... that said; it seems odd that a role or DB can have it's config search_path set to empty string but you can't explicitly set it that way. One possible use for this might be to force complete schema qualification when developing DB update scripts. Comments? [EMAIL PROTECTED] = select version(); version --- PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.1 20060525 (Red Hat 4.1.1-1) (1 row) [EMAIL PROTECTED] = alter database jerry set search_path to ''; NOTICE: schema "" does not exist ALTER DATABASE [EMAIL PROTECTED] = set search_path to ''; ERROR: schema "" does not exist [EMAIL PROTECTED] = \c jerry You are now connected to database "jerry". [EMAIL PROTECTED] = show search_path; search_path - "" (1 row) [EMAIL PROTECTED] = -- --- Jerry Sievers 732 365-2844 (work) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Perhaps silly question about empty search_path
Jerry Sievers <[EMAIL PROTECTED]> writes:
> Hello;
>
> To begin with, I'm not certain how useful it would be... that said; it
> seems odd that a role or DB can have it's config search_path set to
> empty string but you can't explicitly set it that way.
Ugh!
Sorry. I forgot to see if set_config('search_path', '', true/false) does
the trick and it does.
Well, anyway... I got stuck on it since accomplishing what I had
intended wasn't straight forward :-)
Thanks
--
---
Jerry Sievers 732 365-2844 (work) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] keeping an index in memory
Hi, relating to my previous queries on doing spatial searches on 10M rows, it seems that most of my queries return within 2 minutes. Generally this is not too bad, though faster is always better. Interestingly, it appears that the CUBE index for the table in question is about 3GB (the table itself is about 14GB). Not knowing the details of the postgres internals, I assume that when a query tries to use the index, it will need to read a 3GB file. Is this a correct assumption? In such a situation, is there a way to keep the index in memory? My machine has 8GB installed and currently has about 7.4GB free RAM (64 bit linux 2.6.9) A side effect of the size of the index is that if I do a query that performs a seq scan (say using cube_distance) it takes longer than when an index is used, but not significantly longer. And this is on a 10M row table. What strategies do people follow when the index becomes very big? Thanks, --- Rajarshi Guha <[EMAIL PROTECTED]> GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE --- Does Ramanujan know Polish? -- E.B. Ross ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Inheritance foreign key unexpected behaviour
Hi all,
Im trying to use table inheritance in my database. I need it because i want
to be able to link any object in the database to another. So i created a
table my_object which has a serial, nothing more. All the other tables in
the system are inherited from this my_object table. Im having difficulties
adding foreign keys to the tables. This is my test setup:
PostgreSQL 8.2.5 on Windows
-- BEGIN OF SQL
CREATE TABLE my_object
(
id serial NOT NULL,
CONSTRAINT "myobject_PK" PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE TABLE my_child
(
title text,
CONSTRAINT "child_PK" PRIMARY KEY (id)
) INHERITS (my_object)
WITHOUT OIDS;
CREATE TABLE my_link
(
foreign_object_id integer,
CONSTRAINT "link_PK" PRIMARY KEY (id),
CONSTRAINT "link_FK_object" FOREIGN KEY (foreign_object_id)
REFERENCES my_object (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT
) INHERITS (my_object)
WITHOUT OIDS;
INSERT INTO my_child(title) values('test object');
-- Now when i try to add a row to my_link referencing to the newly created
object in the my_child table and thus also available in the my_object table.
INSERT INTO my_link(foreign_object_id) values(1);
-- I get ERROR: insert or update on table "my_link" violates foreign key
constraint "link_FK_object"
-- SQL status:23503
-- Detail:Key (foreign_object_id)=(1) is not present in table "my_object".
-- But if we do a simple select from the my_object table:
SELECT * FROM my_object WHERE id=1;
-- We do get the row.
-- END_OF_SQL
So what's wrong here? Is this improper use of the inheritance features or a
bug? Please help!
Thanks!
Matthieu van Egmond
Re: [GENERAL] uniquely indexing Celko's nested set model
--- On Sat, 10/20/07, Merlin Moncure <[EMAIL PROTECTED]> wrote: > Have you eliminated other approaches, such as arrays, ltree, etc? Actually I haven't considered using arrays to store hierarchal information. I've seen ltree mentioned from time to time. Is it true that it works with adjacency list model? If the nested set model is chosen, would having a table and index fill factor of 50% be a good idea in this case if periodic updates were expected? Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] uniquely indexing Celko's nested set model
On Oct 20, 2007, at 21:24 , Richard Broersma Jr wrote: I've seen ltree mentioned from time to time. Is it true that it works with adjacency list model? I don't believe so. I think it's path-based, but you can check it out for yourself in contrib/ If the nested set model is chosen, would having a table and index fill factor of 50% be a good idea in this case if periodic updates were expected? "fill factor" wrt nested set means not using consecutive numbering of the bounds, leaving space for inserted nodes. Table rewrites might be necessary from time to time as inserts fill in the gaps. You could also do nested sets using numeric rather than integer, which gives you a lot more flexibility. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem of installation on Mac
[EMAIL PROTECTED] wrote: If I understand correctly, the user "pgsql", besides having its own file system not related to others, is the administrator of the postgresql db administrator. Only "pgsql" can modify the db, but the other users can query the db but not modify it. Am I rigth? What I should do, if other users need to update the db. Thank you very much. I might just clarify this a little as some find it confusing - you have two user accounts with the name pgsql, one is a system user account the other is a postgresql database user. The two account names are completely separate and don't need to have the same names. The postgresql server (the program itself running on your machine) is run using the system pgsql account allowing the server to have access to your file system based on the privileges setup on for your system user account. The pgsql database user account is used to determine who can connect to the postgresql server through a client application (whether run remotely or from the same machine) and what data held within the postgresql server they can access. You can create other database users and specify what they can access and what tasks they can perform. So you would keep pgsql as the admin user with unlimited access to all db's and then create a user1 account and then say that user1 can connect to mydb1 and can select, insert and update data in mydb1. You can then create user2 and say they can connect to mydb1 and can only perform selects on mydb1. Within postgresql these user accounts are referred to as roles - see the manual on CREATE ROLE for more info. http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On 10/20/07, Rainer Bauer <[EMAIL PROTECTED]> wrote: > Anyway, the problem are the no. of semaphores created by Postgres: > Every backend creates at least 4* semaphores. Just > increase to an unusual high value (say 1) and > start creating new connections while monitoring the handle count. Hmm, they're actually the same semaphores, so the only cost is for slots in each process's handle table, which comes from kernel paged pool. Testing shows I can easily create about 30 million handles to a given object on this machine. This is under win2003 with 1.25GB RAM, which gives it a paged pool limit of 352MB. I tried going up to 2 max_connections, and still blew postmaster's VM space long before paged pool was exhausted. I couldn't test any higher values, as there's some interaction between max_connections and shared_buffers that prevents it from mapping the buffer contiguously. Something's missing though, since I'm not hitting the same issue you are. How are you generating the connections? I just have an app calling PQconnectdb() in a loop, but I guess that's not good enough. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] keeping an index in memory
On 10/20/07, Rajarshi Guha <[EMAIL PROTECTED]> wrote: > Hi, relating to my previous queries on doing spatial searches on 10M > rows, it seems that most of my queries return within 2 minutes. > Generally this is not too bad, though faster is always better. > > Interestingly, it appears that the CUBE index for the table in > question is about 3GB (the table itself is about 14GB). Not knowing > the details of the postgres internals, I assume that when a query > tries to use the index, it will need to read a 3GB file. Is this a > correct assumption? Not sure. I assume not, but will wait for someone more conversant with pgsql index usage to post that answer. > In such a situation, is there a way to keep the index in memory? My > machine has 8GB installed and currently has about 7.4GB free RAM (64 > bit linux 2.6.9) The kernel will tend to keep it in memory. Usually it does a pretty good job of that. > A side effect of the size of the index is that if I do a query that > performs a seq scan (say using cube_distance) it takes longer than > when an index is used, but not significantly longer. And this is on a > 10M row table. Depending on how much of the table you're getting back, eventually a seq scan will outperform an index scan, because in pgsql-land, you ALWAYS have to hit the table whether there's an index entry or not, for visibility reasons. So, at some point, a certain percentage of the table being retrieved (1 to 25%) will trigger a sequential scan, and rightly so. > What strategies do people follow when the index becomes very big? You can only fit so much data into memory. Once a db gets big enough that it simply can't be stuffed into memory, you need a fast storage subsystem. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] looking for some real world performance numbers
I'm working through the architecture design for a new product. We have a small group working on this. It's a web app that will be using ruby on rails. The challenge I'm running into is that the latest conventional wisdom seems to be that since obviously databases don't scale on the web, you should just not use them at all. I have a group of otherwise very bright people trying to convince me that a rdbms is not a good place to store relational data because eventually it won't scale. And of course we don't even have version 1 of our product out of the door. I'll admit we do have a very good chance of actually getting tons of traffic, but my position is to use a rdbms for relational data, and then if and when it won't scale any more, deal with it then. So what would really help me is some real world numbers on how postgresql is doing in the wild under pressure. If anyone cares to throw some out I would really appreciate it. Chris ---(end of broadcast)--- TIP 1: 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
