Re: [GENERAL] PostgreSQL and AMD?
Dear Greg, Thanks for your valuable and extensive reply. You were right that it is the HP machine the client is wanting, I did also find a configuration that has a Quad Core Intel processor and they will probably go with that for continuity with their current hardware. The PostgreSQL database on the server is to be used as an authentication gateway for enterprise installations of SAP, SQL Server and a bunch of GIS and other data applications, so there won't be a big processing or data transfer load. There will be two identical machines each with a hot swap drive bay as well as an internal 160GB drive. Initially there will only be around 50 non concurrent users so again, low load. I have used PostgreSQL for around four years, but always on Intel chipsets and I had never thought to investigate processor brands. When the client mentioned AMD I thought uh oh this could be a black hole here. I note your comments about disk controllers and will investigate that area too for our next, larger, install. Thanks again and regards John Greg Smith wrote: On Sun, 15 Jun 2008, John Tregea wrote: The machines would be running Windows XP Pro (our clients requirement). Can anyone tell me if PostgreSQL runs fine on the AMD platform and specifically does anyone have experience with the AMD Phenom™ Quad Core Processors 9600B. Once you've settled on Windows as your PostgreSQL platform, you've kind of given up on prioritizing performance at that point--there's a couple of issues that limit how good that can possibly be no matter what hardware you throw at it. Details like which processor you're using are pretty trivial in comparision. Also, the real questions you should be asking are ones like did I get a good disk controller for database use? which is a really serious concern in this space. My guess is you're talking about an HP DC5850. I am rather skeptical of the disk subsystem in that system (at most two disks and just a crappy BIOS RAID) working well in a database context. It's probably fine for a non-critical system, but I wouldn't run a business on it. In general, AMD has been lagging just a bit behind Intel's products recently on systems with a small number of sockets. There are occasional reports where multi-socket multi-core systems from AMD are claimed to do better than similar Intel systems due to AMD's better bus design, I haven't seen that big difference either way myself in recent products. I've been using several different types of Opteron and X2 processors systems from AMD the last couple of years and typically they work just fine. But Phenom has really been a troubled platform launch for AMD and I think that's why nobody has offered any suggestions to you yet--I haven't heard any reports from people using that chip in a server environment yet. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL and AMD?
Hi, I have been asked to specify a pair of HP PC's to set up a PostGreSQL server (and backup) for a client. The HP model we are looking at has an AMD Phenom™ Quad Core Processor (9600B). The machines would be running Windows XP Pro (our clients requirement). Can anyone tell me if PostgreSQL runs fine on the AMD platform and specifically does anyone have experience with the AMD Phenom™ Quad Core Processors 9600B. Thanks in advance John Tregea Technology Director Debraneys www.debraneys.com
Re: [GENERAL] Return the primary key of a newly inserted row?
Hi all, Thanks for the continued suggestions on this question. I will reply again once it is implemented and working. Kind regards John Alban Hertroys wrote: Scott Ribe wrote: You won't have duplicates[1], it's a sequence. It's its purpose. Now I may have missed something, I didn't follow this thread. Yes, what you quoted was more the intro. The actual question was how to find out what ids were generated during a sequence of insertions. That's where you use currval ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Return the primary key of a newly inserted row?
Scott, Ken and Tim, Thanks for the assistance, I appreciate the advice. Scott, The example of select id1 = nextval(somesequence) could work for me. I have multiple users with our GUI and imagine I could use transaction protection to ensure no duplicates between selecting and incrementing the somesequence... Thanks again all. Regards John Scott Ribe wrote: SQL Server had a nifty feature here. You could simply toss a SELECT statement at the end of a trigger of sproc and the results would be returned. This in effect made a table the potential return type of all commands, which could be exploited very powerfully. Do the hackers have any thoughts along those lines? It's also a for instance where inline creation of variables is useful. As in: select id1 = nextval(somesequence) insert into tbl (id...) values (id1...) select id2 = nextval(somesequence) insert into tbl (id...) values (id2...) select id3 = nextval(somesequence) insert into tbl (id...) values (id3...) select id1, id2, id3; Or returning multiple result sets... insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; insert into tbl (id...) values (nextval(somesequence)...) returning new.id; ---(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] Return the primary key of a newly inserted row?
Greeting again, I am writing records to postgreSQL from an IDE called revolution. At the time I perform the INSERT command I need to retrieve the value of the serial_id column from the newly created row. Is it possible to have a specified column value returned after the INSERT (rather than the number of rows affected) ? That would save me doing a SELECT select statement after every INSERT. Please excuse the terminology if it is not SQL'esque, but I hope you know what I am getting at. Thanks in advance John Tregea ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Return the primary key of a newly inserted row?
Sorry, I just realised this should have gone to the SQL list... (Bloody Newbie's) :-[ John Tregea wrote: Greeting again, I am writing records to postgreSQL from an IDE called revolution. At the time I perform the INSERT command I need to retrieve the value of the serial_id column from the newly created row. Is it possible to have a specified column value returned after the INSERT (rather than the number of rows affected) ? That would save me doing a SELECT select statement after every INSERT. Please excuse the terminology if it is not SQL'esque, but I hope you know what I am getting at. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Return the primary key of a newly inserted row?
Hi Tim, Thanks for the advice, it saves me continuing to dig in the help files and my reference books any longer. I don't know how much help I could be in adding features but I am glad to participate in any way I can in the community. I will follow your link to the TODO pages. Thanks again. Regards John Tim Allen wrote: John Tregea wrote: Greeting again, I am writing records to postgreSQL from an IDE called revolution. At the time I perform the INSERT command I need to retrieve the value of the serial_id column from the newly created row. Is it possible to have a specified column value returned after the INSERT (rather than the number of rows affected) ? That would save me doing a SELECT select statement after every INSERT. Please excuse the terminology if it is not SQL'esque, but I hope you know what I am getting at. Thanks in advance John Tregea It's not supported now, however it has been discussed several times, and there is a TODO entry for it at http://www.postgresql.org/docs/faqs.TODO.html using syntax along the lines of INSERT ... RETURNING ... Search for the word returning in the todo list and you'll find the entry. Your options include waiting for someone to make it happen (no telling how long that will be), or helping to make it happen (for which we would all thank you :-) ). In the meantime you'll have to work around it, as you suggested. Tim ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help with storing spatial (map coordinates) data?
Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Tino, Thanks, I had just found the contrib directory and the spatial_ref_sys file as well. The database is to manage security assessments in supply chains and will store locations of buildings as well as points that define transportation routes. So the data will not be searched on but will be used to put risk assessment matrices into the correct order along a supply chain. I will try as you suggest and look at the cube datatype Thanks for your fast reply. Regards John T Tino Wildenhain wrote: John Tregea schrieb: Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I'd suggest starting w/ the contrib package and its cube datatype. This datatype maintains 3d-coordinates and has long/lat input and output. In theory if its just storing you could also just store the longitude, latitude in numeric fields. It really depends on what you really want to do with that data in the database. (e.g. what kind of searches you want to do) cube datatype for example is indexable which really helps in search queries. Regards Tino Wildenhain ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Brent, I will look at postGIS today. I will try and keep the whole GIS functionality as a separate schema to avoid confusing myself, so a postGIS may be exactly what I am looking for. Thanks and regards John Brent Wood wrote: On Mon, 12 Jun 2006, John Tregea wrote: Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. I stongly suggest you do not use tne native Postgres geometry capability, but install PostGIS and use this instead. See www.postgis.org Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Thanks Greg, I don't know in practice if I will need the minutes and seconds, as you say degrees with decimal information is probably more accurate. If I store degrees in decimal I will need to convert back and forth though as people will use GPS to enter lat and long into the system. I need to be able to work out route lengths for various transport modes as well as integrating (in the future) with GIS mapping software such as ESRi or MapInfo. I have subscribed to the Open Geospatial Consortium and downloaded their common architecture which includes an SQL model... But it is a lot to take in, so any guidance is appreciated. Thanks and regards John Gregory S. Williamson wrote: We have had good success with postGIS for storing various spatial data sets (polygons, points and lines). They can be found at http://postgis.refractions.net/. We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that minutes/seconds exists. You may want to subscribe to and post your question on the postGIS mailing list. There are windows-ready compiled versions which seem to work well, although I've only played with them for prototypes (our real database servers are all linux so I can't be of any help on the Windoze front). In general support for this extension of postgres is quite helpful, so I would suggest asking on their general list. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of John Tregea Sent: Sun 6/11/2006 11:18 PM To: pgsql-general@postgresql.org Cc: Subject:[GENERAL] Help with storing spatial (map coordinates) data? Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend !DSPAM:448d0905111031804284693! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Thanks Brent, I will be cautious in my approach. The public schema is the place that I wanted to use to store the geometry attributes, so from your points, that sounds like the best place. The other schemas contain controlled (security) information in proprietary data structures so I that was my reluctance to modify those tables with the necessary geometry functions, types etc. Regards John Brent Wood wrote: On Tue, 13 Jun 2006, John Tregea wrote: Hi Brent, I will look at postGIS today. I will try and keep the whole GIS functionality as a separate schema to avoid confusing myself, so a postGIS may be exactly what I am looking for. Ummm... one caution: The lovely side effect, apart from all the SQL functions to query analyse spatial data in Postgres, is that any table with a properly created geometry attribute is automatically available as a GIS layer in a GIS map window, using GIS applications like QGIS, mezoGIS, JUMP uDIG (even ArcInfo via the PostGIS SDE), or to a less well integrated extent, GRASS. It can also be a layer in a web map server application using something like UMN mapserver. However, not all of these support the concept of schema's, so only tables in the public schema may be able to be plotted/mapped. Also, from a data modelling perspective, a geometry attribute is not inherently different to a numeric, int, varchar or text attribute, so unless there is some other reason to divide entities with geometries into a separate schema frpom those without, I'm not sure it is good practice. Cheers, Brent ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Brent, Excellent advice, thanks for taking the time with what must be a fairly newbie question in GIS terms. I appreciate your help. Cheers John Brent Wood wrote: On Tue, 13 Jun 2006, John Tregea wrote: Thanks Brent, I will be cautious in my approach. The public schema is the place that I wanted to use to store the geometry attributes, so from your points, that sounds like the best place. The other schemas contain controlled (security) information in proprietary data structures so I that was my reluctance to modify those tables with the necessary geometry functions, types etc. Sounds eminently sensible :-) One point you might note, the AddGeometryColumn() function does two things. It adds a geometry column of the appropriate projection type to the specified table. It also writes a metadata record to the geometry_columns table. This is where many application look to find tables with geometries. If you create a view on a table with a geometry column, or create a table with a geometry column without using the AddGeometryColumn() function (eg: create table foo1 as select * from foo0;), then some applications will not recognise the table or view as a GIS table. If you are adding geometries to tables via views, which it sounds like you may be doing, you may need to manually insert the appropriate data into the geometry_columns table to be fully compliant with the OGC specs PostGIS implementation. If you create such a geometry table or view the GIS package you are using fails to make it available as a data source, this is almost certainly why :-) Cheers, Brent ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
