Re: [GENERAL] [OT] CSS Mailinglist?
Hi Michelle, Michelle Konzack wrote: Hello, I am changeing my website from crappy HTML Tables to CSS :-D and need some help but failed to find mailinglists for it. Does someone from you know one? I have yet to see a competent mailinglist on HTML/CSS but if websites are ok, I'd recomment http://alistapart.com/ (not every article out there but some are really helpful). HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PDF Documentation for 8.3?
Michelle Konzack schrieb: Hello, I am using Debian GNU/Linux Etch with PostgreSQL 8.1.11 and since the next release of Debian will use 8.3 I am searching for documentation which can be print out... Ma last Printed version was Practical PostgreSQL from O'Reilly which cover only 7.4. I was searching the site but there are no PDF's for 8.3 in format A4 or do I missing something? Note: The american Letter format sucks, because I am printing two A4 pages on ONE A4 side and with the Letter format I get very huge borders... Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant Hello, what is wrong with this PDF? http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf Greetings from Berlin Sven Marcel Buchholz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] R-tree, order by, limit
Hello, I am implementing a map application. There are towns with altitude, longitude and population. One of the tasks is to be able to query N biggest (by population) towns within a rectangle. Something like (maybe the syntax in not quite right, but the idea is obvious): SELECT * FROM towns where alt1 = alt = alt2 AND long1 = long = long2 ORDER BY population LIMIT 10; If I create an R-tree index on coordinates (alt, long) this will speed up the query significantly. But it is still far from optimal: Despite we need only 10 biggest towns, all towns in the rectangle specified will be examined. What if we include population into R-tree index? This index will handle a 3D space with coordinates (alt, long, population). Will this 3D index perform better than that 2D index? In fact, I lack some details on how Postges handles ORDER_BY and LIMIT inside R-tree indexes. Extensive answers and links are appreciated. Thanks. Anton. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] R-tree, order by, limit
On Sun, 21 Sep 2008, Anton Belyaev [EMAIL PROTECTED] writes: SELECT * FROM towns where alt1 = alt = alt2 AND long1 = long = long2 ORDER BY population LIMIT 10; You're absolutely on the wrong path. Don't try to implement a logic, that has been implemented by PostgreSQL in the most possibly efficient way in its bounds. See geographic data types[1] (e.g. box) and geographic functions[2] (e.g. @ a.k.a contains). Regards. [1] http://www.postgresql.org/docs/current/interactive/datatype-geometric.html [2] http://www.postgresql.org/docs/current/interactive/functions-geometry.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] R-tree, order by, limit
2008/9/21 Volkan YAZICI [EMAIL PROTECTED]: On Sun, 21 Sep 2008, Anton Belyaev [EMAIL PROTECTED] writes: SELECT * FROM towns where alt1 = alt = alt2 AND long1 = long = long2 ORDER BY population LIMIT 10; You're absolutely on the wrong path. Don't try to implement a logic, that has been implemented by PostgreSQL in the most possibly efficient way in its bounds. See geographic data types[1] (e.g. box) and geographic functions[2] (e.g. @ a.k.a contains). Regards. [1] http://www.postgresql.org/docs/current/interactive/datatype-geometric.html [2] http://www.postgresql.org/docs/current/interactive/functions-geometry.html Volkan, Thanks you for your reply. Geometry types and functions use R-tree indexes anyways. I can rephrase the query using geometry language of Postgres: SELECT * FROM towns WHERE towns.coordinates @ box(alt1, long1, alt2, long2) ORDER BY population LIMIT 10; And the questions about population remain the same: How to avoid examination of all the towns in the rectangle knowing that we need only 10 biggest? Does population worth including into a (3D) point (In order to create a 3D R-tree)? Does Postgres perform ODRER/LIMIT efficiently in this case? Thanks. Anton. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] R-tree, order by, limit
2008/9/21 Anton Belyaev [EMAIL PROTECTED]: Hello, I am implementing a map application. There are towns with altitude, longitude and population. One of the tasks is to be able to query N biggest (by population) towns within a rectangle. Something like (maybe the syntax in not quite right, but the idea is obvious): SELECT * FROM towns where alt1 = alt = alt2 AND long1 = long = long2 ORDER BY population LIMIT 10; If I create an R-tree index on coordinates (alt, long) this will speed up the query significantly. But it is still far from optimal: Despite we need only 10 biggest towns, all towns in the rectangle specified will be examined. What if we include population into R-tree index? This index will handle a 3D space with coordinates (alt, long, population). Will this 3D index perform better than that 2D index? In fact, I lack some details on how Postges handles ORDER_BY and LIMIT inside R-tree indexes. Extensive answers and links are appreciated. Thanks. Anton. Sorry, I meant latitude (lat) instead of altitude (alt). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PDF Documentation for 8.3?
At 1:50am -0400 on Sun, 21 Sep 2008, Joshua D. Drake wrote: Kevin Hunter wrote: http://www.postgresql.org/docs/manuals/ Hmm, this page seems to advertise both US Letter and A4. A cursory inspection suggests that the A4 document at least has larger pages and less of them ... First, I have been drinking so if this sounds stupid, blame the vodka :). Second, A4 is larger than letter so it would have less pages would it not? Exactly. :-) Just proposing that the A4 size was -- in fact -- A4, and not US Letter set on a larger page. Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] R-tree, order by, limit
On Sun, Sep 21, 2008 at 06:17:39PM +0400, Anton Belyaev wrote: Geometry types and functions use R-tree indexes anyways. I can rephrase the query using geometry language of Postgres: SELECT * FROM towns WHERE towns.coordinates @ box(alt1, long1, alt2, long2) ORDER BY population LIMIT 10; And the questions about population remain the same: How to avoid examination of all the towns in the rectangle knowing that we need only 10 biggest? I don't know if it solves your problem, but you should be able to do a multi-column GiST index with both the position data and the population data in it. However, I'm unsure if postgresql will correctly use the index to solve the order by... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
[GENERAL] PL/Python - Execute return results
Hi, plan = plpy.prepare(SELECT last_name FROM my_users WHERE first_name = $1, [ text ]) rv = plpy.execute(plan, [ name ], 5) return rv[last_name] If the SELECT command does not return any results, how do I catch/check for this? if rv == {} ? or maybe try: rv = plpy.execute(plan, [ name ], 5) return rv[last_name] except: ... else: ... can't seem to get either to work. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] i can't drop an idex
Pau Marc Munoz Torres wrote: but when i try to fo it I get the following error mhc2db= drop index antic; ERROR: could not open relation with OID 596166 You might want to provide some more details, like: - Your operating system and version - What version of PostgreSQL you are using - If you're on Windows, whether you're using a virus scanner or desktop search program - Your disk configuration, particularly whether you have some form of redundant storage (RAID, SAN, etc) - Whether you've recently had any file system problems - Whether you have ever directly altered anything in the PostgreSQL data directories -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Triggers not working
Hi, I'm running PostgreSQL 8.3.3 and I'm having trouble with triggers not always working. I have the following tables and functions as documented below. My problem is that if I perform an update on the Entity table and modify the Code field, why doesn't the trigger for the Entity table execute? (Row was initially added via the Account table.) Dale. CREATE TABLE Entity ( ID bigserial NOT NULL, Code character varying(20) NOT NULL, Name character varying(50) NOT NULL, Modified timestamp(1) with time zone NOT NULL DEFAULT session_timestamp(), ModifiedBy bigint DEFAULT userid(), CONSTRAINT pkEntity PRIMARY KEY (ID) ); CREATE TABLE Account ( Balance money NOT NULL DEFAULT '$0.00'::money, CONSTRAINT pkAccount PRIMARY KEY (ID), CONSTRAINT uniqAccountCode UNIQUE (Code) ) INHERITS (Entity); CREATE OR REPLACE FUNCTION entitytrigger() RETURNS trigger AS $BODY$BEGIN --Update modified details raise notice '% being called for % of %.', TG_NAME, TG_OP, TG_TABLE_NAME; new.Modified := Session_TimeStamp(); new.ModifiedBy := UserID(); return new; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER trEntityUpdate BEFORE UPDATE ON Entity FOR EACH ROW EXECUTE PROCEDURE entitytrigger(); CREATE TRIGGER trAccountUpdate BEFORE UPDATE ON Account FOR EACH ROW EXECUTE PROCEDURE entitytrigger();
[GENERAL] Largest PostgreSQL 8.x DB someone is running?
What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product. Any information you can provide would be very helpful. Thanks, Keaton
Re: [GENERAL] Largest PostgreSQL 8.x DB someone is running?
Keaton Adams wrote: What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product. Any information you can provide would be very helpful. See this thread from last month http://archives.postgresql.org/pgsql-general/2008-08/msg00553.php klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Largest PostgreSQL 8.x DB someone is running?
Keaton Adams wrote: What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product. Any information you can provide would be very helpful. I have customers running over a Terabyte. Joshua D. Drake Thanks, Keaton -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Largest PostgreSQL 8.x DB someone is running?
On Sun, Sep 21, 2008 at 10:14 PM, Keaton Adams [EMAIL PROTECTED] wrote: What is the the largest PostgreSQL 8.x database that is running in a production environment that you are aware of? We top out at roughly 400 GB but have a need for a new project to go much, much larger (in the several TB range). I am attempting to get a feel for how large one should take a single PostgreSQL database, given all of the operational concerns such as overall performance with a thousand+ concurrent users, times/space requirements for backups and restores, how to upgrade to newer upcoming versions of the software, etc. especially since there are no parallel operations/features in the product. Any information you can provide would be very helpful. First and foremost, don't think of 8.0, 8.1, 8.2, 8.3 and the soon to be released 8.4 as being the same main branch. They're not. Each is a major release in its own right. If you're going to be building a new system then start with 8.3.4 (due out this week) as it's got major performance improvements of the previous versions that make scaling much easier. Next, size is only important if you're operating on the whole dataset all the time. If you've got 1,000 users running update table set field=field+100 where id=3 you can get by on a lot less horsepower than if you're running select avg(field) from table with no where clause. The first update updates one row, the select hits the whole table. So your usage patterns will matter. Where I work we have older machines with one hard drive running pg 8.1 at work that handle 600 to 1200 connections all alive at once, with dozens and dozens active at the same time. But they're all like the simple update above reading, updating, inserting, and deleting single rows for a session manager. OTOH, we have 8 CPU machines with lots of memory and hard drives, that can be expanded, that handle several hundred concurrent operations which are often hitting dozens to thousands of rows. Those machines have to be bigger to handle the load. I fail to see how the (possibly non-)issue you mention above of parallelism would negatively affect postgresql from handling 1000s of active backends. Splitting a single query to multiple CPUs is quite likely to be counterproductive in such an environment. For backups of very large systems I'd look at either slony replication slaves for backup, or PITR, or both. Finally, compared to the commercial products on offer, if you had an 8 or 16 core machine and you had the licenses for all the cool stuff, you could be looking at a yearly licensing fee well into the hundreds of thousands of dollars. You can buy a lot of hardware to throw at a problem for that price. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PDF Documentation for 8.3?
NOthing bad, except that a number of tables are actually unreadable and some code example lines are going past the right margin. Apart of this, I would say it's great documentation. On Sunday 21 September 2008 11:52:44 Sven Marcel Buchholz wrote: Michelle Konzack schrieb: Hello, I am using Debian GNU/Linux Etch with PostgreSQL 8.1.11 and since the next release of Debian will use 8.3 I am searching for documentation which can be print out... Ma last Printed version was Practical PostgreSQL from O'Reilly which cover only 7.4. I was searching the site but there are no PDF's for 8.3 in format A4 or do I missing something? Note: The american Letter format sucks, because I am printing two A4 pages on ONE A4 side and with the Letter format I get very huge borders... Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer Tamay Dogan Network Debian GNU/Linux Consultant Hello, what is wrong with this PDF? http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf Greetings from Berlin Sven Marcel Buchholz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general