Re: [GENERAL] What are the benefits of using a clustered index?
Mike Christensen wrote: > However, if the query > planner can't assume the data is in a certain order on disk, what's the > point of having this at all? One benefit is that it reduces the number of pages that must be read from disk to retrieve all tuples that have a particular value for the indexed field. Because the tuples tend to be clustered by (in your case) recipeid, you'll normally have quite a few relevant tuples on a page and won't have as much uninteresting and irrelevent data getting read in along with what you actually want. That'll also improve efficiency of memory/cache use. I think it also helps keep the index smaller, since it doesn't need to refer to as many pages for a given value of interest. If you set a non-default FILLFACTOR on the table (and proably index) Pg will leave gaps in the table and I think it will try to insert tuples to maintain clustering order for smaller indexes and faster index scans. The downside is that there are holes in the table that may slow down a sequential scan somewhat. -- 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
Re: [GENERAL] postgreSQL & amazon ec2 cloud
On Tue, Mar 3, 2009 at 11:53 AM, Sanjay Arora wrote: > Is it possible to host postgreSQL on Amazon's cloud? What are the issues > involved? Runs just fine under Ubuntu. We haven't tried it under serious load though. -- Stuart Bishop http://www.stuartbishop.net/ -- 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] using window functions
Daniel Manesajian, 17.03.2009 04:33: Hi, I'm trying to get an advance taste of the window function feature that I believe is supposed to be in 8.4. I'm running 8.4devel snapshot (dated Jan-01 which seems kind of old) grabbed from the snapshot page on the postgresql website. When I try a simple query "select avg(my_int) over (order by my_int rows unbounded preceding) order by 1;" I get an error: ERROR: syntax error at or near "over" Do I have to build from source to try window functions? I'm using the build from January 1st (on Windows, provided by EnterpriseDB) and your statement gives me an error at the keyword "unbounded". If I leave that out or add the missing range, it works. Are you sure you are connecting to the correct installation? Btw: I'm not sure whether your statement makes sense. As you are not using partion by or selecting other columns from the table, the same could be achieved without a window function. But I guess you just wanted to play around ;) Thomas -- 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] Postgres 8.2.x support on Windows 2008
Windows 2008 is basically a vista with some (unfortunately not enough) of the cruft removed and some nice additions in the server side totally irrelevant to postgresql. So as long as it's fine on vista, it should be fine on 2008. I don't regal offhand if 8.2 is or if it requires 8.3, but the buildfarm should be clear on that. /Magnus On 17 mar 2009, at 03.21, "Raji Sridar (raji)" wrote: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Thanks Raji -Original Message- From: Joshua D. Drake [mailto:j...@commandprompt.com] Sent: Monday, March 16, 2009 7:10 PM To: Raji Sridar (raji) Cc: Scott Marlowe; r...@iol.ie; pgsql-general@postgresql.org Subject: RE: [GENERAL] Postgres 8.2.x support on Windows 2008 On Mon, 2009-03-16 at 18:12 -0700, Raji Sridar (raji) wrote: Thanks for the prompt responses. None of the urls mention Windows 2008. Looks like it is not supported. Who should I approach to get this supported and how? How do you mean "supported"? I doubt that the community is going to back patch support for 8.2 Win32 to Windows 2008. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Postgres 8.2.x support on Windows 2008
On 17 mar 2009, at 05.22, "Joshua D. Drake" wrote: On Mon, 2009-03-16 at 23:02 -0400, Tom Lane wrote: "Raji Sridar (raji)" writes: I don't mind going to 8.3 or later. I want to ensure that Postgres is tested on Windows 2008 and patches created like other platforms. How do I enable that? Contribute a test machine to the buildfarm: http://www.pgbuildfarm.org/index.html (Although I have to concede never having heard of "Windows 2008". You sure you're not talking about Vista? If so, we have that covered.) Its beta of Windows 7 server essentially. http://www.microsoft.com/windowsserver2008/en/us/default.aspx That's just plain wrong. Windows 2008 is very much a production release (insert appropriate comment about windows vs production ready here). It's closer to "server vista" (though not as broken), but it's really an in between version. Microsoft stopped doing coordinated desktop/server releases years ago. /Magnus Joshua D. Drake regards, tom lane -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] How to configure on a machine with a lot of memory?
Start by looking here http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY And if you search the lists you'll find whole discussions on this topic that have been repeated over and over. Without generalising too much, for a dedicated machine you should be looking at setting effective_cache_size to approx 2/3 of ram, shared_buffers to something like 10-15% of ram, and work_mem to something reasonable dependant on amount of connections and types of queries, but remember to keep this small-ish, mine is set at 2mb. --- On Tue, 17/3/09, A B wrote: > From: A B > Subject: [GENERAL] How to configure on a machine with a lot of memory? > To: pgsql-general@postgresql.org > Date: Tuesday, 17 March, 2009, 9:20 AM > Hi there! > > If I have a database that have a size of a few GB and run > this on a > machine with 48 GB of ram, What parameters should I set to > make > Postgres use all the memory that is available? For a not so > very > technical person it is a little cryptic to find out. > > Thanks. > > -- > Sent via pgsql-general mailing list > (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to configure on a machine with a lot of memory?
Hi there! If I have a database that have a size of a few GB and run this on a machine with 48 GB of ram, What parameters should I set to make Postgres use all the memory that is available? For a not so very technical person it is a little cryptic to find out. Thanks. -- 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] Postgres 8.2.x support on Windows 2008
On Tue, Mar 17, 2009 at 4:22 AM, Joshua D. Drake wrote: > On Mon, 2009-03-16 at 23:02 -0400, Tom Lane wrote: >> "Raji Sridar (raji)" writes: >> > I don't mind going to 8.3 or later. I want to ensure that Postgres is >> > tested on Windows 2008 and patches created like other platforms. How do >> > I enable that? >> >> Contribute a test machine to the buildfarm: >> http://www.pgbuildfarm.org/index.html >> >> (Although I have to concede never having heard of "Windows 2008". >> You sure you're not talking about Vista? If so, we have that >> covered.) > > Its beta of Windows 7 server essentially. No it isn't. It's the server version of Vista, but came out far enough behind Vista that they managed to fix much of the crap that bogs Vista down. And 8.3.x should work fine on it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Records Number
Hi all, I have a little problem, I would like to execute select * from table and i would like to retrieve the number of records without make select count(*) from table I could use directly the table instead of select, and in this this case I'm searching for something like the reltuples field in the pg_class table, but I need this value in real time. Any suggestion? Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 -- 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 versus MySQL for GPS Data
Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Thanks in advance Juan Karlos.
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 12:25:08PM +0100, Juan Pereira wrote: > I'm currently developing a program for centralizing the vehicle fleet GPS > information -http://openggd.sourceforge.net-, written in C++. > > The database should have these requirements: ... > - The database also should create a table for every truck -around 100 > trucks-. Why ? This smells like a design problem. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] PostgreSQL versus MySQL for GPS Data
Hi Juan, First of all congratulations on you project :) We, at MADEIRA GPS, use Postgresql and PostGIS as the corner stone of our fleet management solution and have tens of *millions* of records in a single vehicles history table without any visible performance problem (we do however clean it every year). A thought, however, regarding your plans for gps data acquisition/storage: every second... isn't that a bit too much? We, for most of our customers, offer minute-by-minute tracking and, this is important, *optimize* the vehicles' history table when writing data into it by means of comparing the data from the last record - i.e. if the info is the same *don't* write it! This will surely save you space ;-) About simultaneous queries: Last we checked we had ~200 of them with PGSQL still pumping at full speed... ;-) As a final note, IMHO, PGSQL/PostGIS is better than MySQL for a number of reasons: - proven robustness - tight integration with PostGIS - large user base (an always friendly bunch willing to help out each other ;-) ) - ... Regards, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Tuesday 17 March 2009 11:25:08 am Juan Pereira wrote: > Hello, > > I'm currently developing a program for centralizing the vehicle fleet GPS > information -http://openggd.sourceforge.net-, written in C++. > > The database should have these requirements: > > - The schema for this kind of data consists of several arguments -latitude, > longitude, time, speed. etc-, none of them is a text field. > - The database also should create a table for every truck -around 100 > trucks-. > - There won't be more than 86400 * 365 rows per table -one GPS position > every second along one year-. > - There won't be more than 10 simultaneously read-only queries. > > The question is: Which DBMS do you think is the best for this kind of > application? PostgreSQL or MySQL? > > > Thanks in advance > > Juan Karlos. signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan Pereira wrote: > - The database also should create a table for every truck -around 100 > trucks-. Why? That's a rather clumsy design that makes it really hard to get aggregate data across the fleet or do many interesting queries. You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Read up on PostgreSQL's table partitioning features. > The question is: Which DBMS do you think is the best for this kind of > application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Pg's table partitioning support is also an ideal fit for your application. -- Craig Ringe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] different results for large objects
I am getting correct results with following query i.e. binary objects as they are inserted. select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename; but when I created a function as follows, binary objects are not shown, rather a number represting the large object on the server is shown. CREATE OR REPLACE FUNCTION software.uf_searchdocimages(integer, integer, integer) RETURNS SETOF docimages AS $BODY$ --select images for all object of specified type select docimages.* from docimages where docimages.accobjecttype= $2 ORDER BY docimages.docimagename; $BODY$ LANGUAGE 'sql' VOLATILE SECURITY DEFINER COST 1 ROWS 10; The problem is arising for a field of type 'lo'. Why this function is not giving the actual large object? I am using Windows XP sp3, PostgreSQL 8.3.6. The table definitions is like below- CREATE TABLE docimages ( diid serial NOT NULL, docimagename character varying(50), docimage lo, accobjectid integer, accobjecttype integer, CONSTRAINT pk_docimages PRIMARY KEY (diid) ) WITH (OIDS=FALSE); Please reply. Thanks in adavnce. CPKulkarni
Re: [GENERAL] Postgres 8.2.x support on Windows 2008
Raji Sridar (raji) wrote: Hi, I would like to know if Postgres 8.2.x is supported on Windows 2008 (32 bit). If not, what is version of Postgres supported on Windows 2008? PLs let me know. Thanks Raji Hi Raji, Windows Server 2008 is excellent (Sorry if I upset any *nix fanbois). And has the vista aero rubbish as an optional addon (i.e. don't do it!) I have been running the 8.3.6. windows build on the 64bit version of windows 2008 for the last month or so and so far it is working seamlessly. For those that do not have the option of running postgres on a *nix machine I recommend it. Howard. www.selestial.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Escaping special characters
About the string "Smith \& Jones".
According to the documentation,
INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
must work. But it doesn't. So, double the enclosed quotes:
INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');
Doesn't.
It works fine, but with a warning, as
INSERT INTO thing (name) VALUES ('Smith \\& Jones');
But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
So 40,000 years from now I'll be on 8.2.3.
Granted, I'm not very bright. Would appreciate your help.
--Elle
--
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] Records Number
Enrico Pirozzi wrote: > and i would like to retrieve the number of records without make > > select count(*) from table > > I could use directly the table instead of select, and in this this > case I'm searching for > something like the reltuples field in the pg_class table, but I need > this value in real time. If you want an accurate, up-to-date count then you'll need to use count(*) or have a trigger keep a summary-count for you. A simple implementation will reduce concurrency to writes on that table however. Lots of discussion in the mailing-list archives on this. -- Richard Huxton Archonet Ltd -- 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] Escaping special characters
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't. So, double the enclosed quotes:
>
> INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');
>
> Doesn't.
>
> It works fine, but with a warning, as
>
> INSERT INTO thing (name) VALUES ('Smith \\& Jones');
>
> But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
> So 40,000 years from now I'll be on 8.2.3.
>
I could be wrong, but shouldn't it be:
INSERT INTO thing (name) VALUES ('Smith E'\\& Jones');
I'm not sure why you're including an extra single or double-quote in the
string.
Regards
Thom
Re: [GENERAL] Escaping special characters
Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't.
I think you'll find the documentation says to use:
SELECT E'Smith \\& Jones';
Note that the "E" precedes the quoted string, it isn't embedded in it.
If there's an example in the docs that looks like yours, that's a bug.
> But it mightn't if I upgrade from 8.2.3. Deprecated. Can't risk it.
> So 40,000 years from now I'll be on 8.2.3.
Doubtful - you're missing 9 releases of bugfixes already. Probably find
all your data gets eaten by a bug long before then. Read the release
notes for 8.2.x and upgrade to 8.2. at your earliest convenience.
--
Richard Huxton
Archonet Ltd
--
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] Escaping special characters
On Tue, Mar 17, 2009 at 10:35:20PM +1030, Neanderthelle Jones wrote:
> About the string "Smith \& Jones".
>
> According to the documentation,
>
> INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
>
> must work. But it doesn't.
You're putting things in the wrong places! The "E" says that the
following literal is using C style escaping. I.e. you want to say:
E'Smith \\& Jones'
Hope that helps!
--
Sam http://samason.me.uk/
--
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] Escaping special characters
2009/3/17 Thom Brown
>
> I could be wrong, but shouldn't it be:
>
> INSERT INTO thing (name) VALUES ('Smith E'\\& Jones');
>
> I'm not sure why you're including an extra single or double-quote in the
> string.
>
> Regards
>
> Thom
>
Sorry, (damn copy & paste). I meant:
INSERT INTO thing (name) VALUES (E'Smith \\& Jones');
Thom
Re: [GENERAL] different results for large objects
c k writes: > I am getting correct results with following query i.e. binary objects as > they are inserted. > select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY > docimages.docimagename; > but when I created a function as follows, binary objects are not shown, There is no server-side behavior that would cause that query to show you a large object's contents instead of its identifying number. I suppose that you have some client-side code that does something magic when it sees a result column of type "lo", and that for some reason this code is not successfully recognizing the case where the column is coming out of a function. Since you've shown us neither the troublesome query nor the client-side code, it's hard to speculate further than that. regards, tom lane -- 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] Escaping special characters
Neanderthelle Jones wrote:
About the string "Smith \& Jones".
According to the documentation,
INSERT INTO thing (name) VALUES ('Smith E'\\'& Jones');
must work. But it doesn't. So, double the enclosed quotes:
INSERT INTO thing (name) VALUES ('Smith E''\\''& Jones');
The E can't be inside the string, it must appear before the quote
starting the string.
But first, you need to choose a setting for
standard_conforming_strings, especially if you're concerned with
compatibility against future versions. Either your session has
standard_conforming_strings set to ON or set to OFF. This is what
defines which characters have to be quoted and how.
if OFF you must escape the backslash:
test=> set standard_conforming_strings=off;
SET
test=> select E'Smith \\& Jones';
?column?
Smith \& Jones
(1 row)
if ON you don't:
test=> set standard_conforming_strings=on;
SET
test=> select 'Smith \& Jones';
?column?
Smith \& Jones
(1 row)
ON is supposed to become the default at some point in the future.
Cordialement,
--
Daniel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Uploading data to postgresql database
Hi All, I wanted to know if there is a way to upload kml/gpx data directly to the DB. So far, I have been using shp2pgsql to upload data from shape files. I don't want to convert it to shape and then upload it. If someone knows of a way to upload without converting to shape file, please let me know. I use postgresql 8.3.1 with postgis 1.3.3. Thanks for the help. Subha -- 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] What are the benefits of using a clustered index?
Mike Christensen wrote: > I'm wondering what the performance advantage is to using a clustered > index. In Postgres you don't cluster indexes. You cluster tables. It only means that the table is written from scratch, following the index order. So what it gives you is locality of access for queries that follow ranges of that index, nothing more. It seems very obvious that in this implementation a new tuple is not going to follow the index order; it'll just be stored wherever there is free space. If you run CLUSTER again, they'll be put in the right place. (There was a patch to enhance this so that a new insertion would instead use space closer to where the tuple would be if it followed the order. But it was only a hint; if there wasn't enough free space in the right spot, it would be stored elsewhere. Still, the patch was not committed.) > I'm quite sure SQL Server doesn't work this way and I'm not sure about > Oracle. Can someone enlighten me on the exact benefit of this? Thanks!! Yeah, they use a completely different definition of "clustered index" from ours. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 7:47 AM, Craig Ringer wrote: > Juan Pereira wrote: > > >> - The database also should create a table for every truck -around 100 >> trucks-. > > Why? > > That's a rather clumsy design that makes it really hard to get aggregate > data across the fleet or do many interesting queries. > > You're almost always better off using a single table with a composite > primary key like (truckid, datapointid) or whatever. If you'll be doing > lots of queries that focus on individual vehicles and expect performance > issues then you could partition the table by truckid, so you actually do > land up with one table per truck, but transparently accessible via table > inheritance so you can still query them all together. > > Read up on PostgreSQL's table partitioning features. If there is little/no reason to span queries over various trucks, then the OP's approach is ok, better than standard TP even. I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). merlin -- 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] Uploading data to postgresql database
Subha Ramakrishnan wrote: Hi All, I wanted to know if there is a way to upload kml/gpx data directly to the DB. By upload you mean...? You can always just insert the date opaque as raw field or text or use large object (lo). So far, I have been using shp2pgsql to upload data from shape files. I don't want to convert it to shape and then upload it. If someone knows of a way to upload without converting to shape file, please let me know. If you plan to do anything with the data inside the database (and your reference to PostGIS indicates that) you need to insert it in a structured way as opposed to just "upload the data". What is wrong with the conversion step you mention? Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] different results for large objects
Yes, the problem is with PGAdmin and ODBC connection to MS access. The function (given is first mail) is giving same results i.e. only IDs not the actual data from PGAdmin and ODBC. But for following query PGAdmin gives again only IDs, but MS Access through ODBC gives the actual binary data. select docimages.* from docimages where docimages.accobjecttype=9 ORDER BY docimages.docimagename; So I think ODBc is making some magic here for showing large object data. If this is correct how can I retrieve the actual binary data from a function/single statement as above? Thanks, CPK On Tue, Mar 17, 2009 at 6:13 PM, Tom Lane wrote: > c k writes: > > I am getting correct results with following query i.e. binary objects as > > they are inserted. > > > select docimages.* from docimages where docimages.accobjecttype=9 ORDER > BY > > docimages.docimagename; > > > but when I created a function as follows, binary objects are not shown, > > There is no server-side behavior that would cause that query to show you > a large object's contents instead of its identifying number. I suppose > that you have some client-side code that does something magic when it > sees a result column of type "lo", and that for some reason this code > is not successfully recognizing the case where the column is coming out > of a function. Since you've shown us neither the troublesome query > nor the client-side code, it's hard to speculate further than that. > >regards, tom lane >
Re: [GENERAL] What are the benefits of using a clustered index?
On Tue, 2009-03-17 at 09:16 -0400, Alvaro Herrera wrote: > > I'm quite sure SQL Server doesn't work this way and I'm not sure > about > > Oracle. Can someone enlighten me on the exact benefit of this? > Thanks!! > > Yeah, they use a completely different definition of "clustered index" > from ours. Hopefully we regard it as a missing feature rather than as a separate definition. We could cluster the index, we just don't, yet. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] PostgreSQL versus MySQL for GPS Data
Juan, * Juan Pereira (juankarlos.open...@gmail.com) wrote: > - The schema for this kind of data consists of several arguments -latitude, > longitude, time, speed. etc-, none of them is a text field. I would think you might want *some* text fields, for vehicle identification, as a seperate table about trucks. > - The database also should create a table for every truck -around 100 > trucks-. As mentioned elsewhere, you're probably fine with 1 table, but if it becomes a problem you can always partition it up and have one view across all of them (make sure to set up your constraints correctly and enable constraint_exclusion if you go with this route). You could then have, say, 10 tables, with 10 trucks in each. > - There won't be more than 86400 * 365 rows per table -one GPS position > every second along one year-. As mentioned, you might want to eliminate duplicate entries; no sense storing information that can be trivially derived. > - There won't be more than 10 simultaneously read-only queries. While this is good to know, I kind of doubt it's accurate, and more important is the number of simultaneous writers. I'm assuming 100, but is that correct? > The question is: Which DBMS do you think is the best for this kind of > application? PostgreSQL or MySQL? Given the list you posted to, I would say you're likely to get alot of PostgreSQL recommendations. Assuming you posted something similar to a MySQL list, I would recommend that you not pick a solution based on the number of responses you get but rather what you're most comfortable with and understand best. If there is a learning curve either way, I think PostgreSQL would be the best solution. If you're thinking about what to have your application support, you might consider trying to support both. Doing that from the beginning is usually best since you'll develop your system at a high enough level to mitigate the problems (syntax differences, performance differences, etc) between the databases. As an aside, and I don't know where the MySQL community is on this, but we have the US Census TIGER Shapefile data set loaded into PostgreSQL with PostGIS, with a geocoder that works with it. We should have a complete packaged solution for loading it, indexing, etc, soon. That's a fairly large, free, data set of all streets, addresses, etc, in the US with lat/long information. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] Uploading data to postgresql database
* Subha Ramakrishnan (su...@gslab.com) wrote: > So far, I have been using shp2pgsql to upload data from shape files. > I don't want to convert it to shape and then upload it. Have you looked at ogr2ogr? It looks to support KML as a format, and has PostGIS support, though I'm not sure if it can handle the direction you're asking for. Stephen signature.asc Description: Digital signature
Re: [GENERAL] Records Number
> If you want an accurate, up-to-date count then you'll need to use > count(*) or have a trigger keep a summary-count for you. A simple > implementation will reduce concurrency to writes on that table however. Yes I solved by a trigger > Lots of discussion in the mailing-list archives on this. Thank you for your time Enrico -- That's one small step for man; one giant leap for mankind Enrico Pirozzi Tel. +39 0861 1855771 Mob.+39 328 4164437 Fax +39 0861 1850310 www.enricopirozzi.info i...@enricopirozzi.info Skype sscotty71 -- 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] Maximum transaction rate
John R Pierce wrote: > Stefan Kaltenbrunner wrote: >> So in my understanding LVM is safe on disks that have write cache >> disabled or "behave" as one (like a controller with a battery backed >> cache). > > what about drive write caches on battery backed raid controllers? do > the controllers ensure the drive cache gets flushed prior to releasing > the cached write blocks ? If LVM/dm is lying about fsync(), all this is moot. There's no point talking about disk caches. BTW. This discussion is continuing on the linux-lvm mailing list. https://www.redhat.com/archives/linux-lvm/2009-March/msg00025.html I have some PG databases on LVM systems, so I need to know for sure I have have to move them elsewhere. It seemed to me the right place for asking about the issue. Someone there pointed out that fsycn() is not LVM's responsibility. Correct. For sure, there's an API (or more than one) a filesystem uses to force a flush on the underlying block device, and for sure it has to called while inside the fsync() system call. So "lying to fsync()" maybe is more correct than "lying about fsync()". .TM. -- 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] PostgreSQL versus MySQL for GPS Data
Craig Ringer wrote: >> You're almost always better off using a single table with a composite >> primary key like (truckid, datapointid) or whatever. If you'll be doing >> lots of queries that focus on individual vehicles and expect performance >> issues then you could partition the table by truckid, so you actually do >> land up with one table per truck, but transparently accessible via table >> inheritance so you can still query them all together. Quite interesting! The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? >> My main reasons are that in a proper transactional environment (ie >> you're not using scary MyISAM tables) Pg is *much* better about handling >> concurrent load, particularly concurrent activity by readers and writers. >> 2009/3/17 Craig Ringer Quite interesting again. Thank you for your answers Juan Karlos
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan Pereira wrote: > Craig Ringer wrote: > > > >> You're almost always better off using a single table with a composite > >> primary key like (truckid, datapointid) or whatever. If you'll be doing > >> lots of queries that focus on individual vehicles and expect performance > >> issues then you could partition the table by truckid, so you actually do > >> land up with one table per truck, but transparently accessible via table > >> inheritance so you can still query them all together. > > Quite interesting! > > The main reason why we thought using a table per truck was because > concurrent load: if there are 100 trucks trying to write in the same table, > maybe the performance is worse than having 100 tables, due to the fact that > the table is blocked for other queries while the writing process is running, > isn't it? Wow, you are carrying around a lot of MySQL baggage with you. ;-) You should probably read this: http://www.postgresql.org/docs/8.3/static/mvcc-intro.html -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Records Number
Richard Huxton, 17.03.2009 13:26: Enrico Pirozzi wrote: and i would like to retrieve the number of records without make select count(*) from table I could use directly the table instead of select, and in this this case I'm searching for something like the reltuples field in the pg_class table, but I need this value in real time. If you want an accurate, up-to-date count then you'll need to use count(*) or have a trigger keep a summary-count for you. A simple implementation will reduce concurrency to writes on that table however. Lots of discussion in the mailing-list archives on this. Can a trigger solution really give an accurate count in a concurrent insert/delete scenario? Thomas -- 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] Records Number
Thomas Kellerer writes: > Can a trigger solution really give an accurate count in a concurrent > insert/delete scenario? In principle yes, but AFAIK no one has really coded it up in full detail. See the design that was hashed out in some previous mailing-list thread, involving delta-count records entered into a tracking table by each transaction that inserts or deletes. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] COPY command question
Hi all, executing the following command inside pgAdmin on my Windows Vista (please avoid comment, I pray you) : copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV I get the following error: WARNING: nonstandard use of \\ in a string literal LINE 1: copy anagrafica_import from 'C:\\temp\\anagraficaANIDIs.csv'... ^ HINT: Use the escape string syntax for backslashes, e.g., E'\\'. ERROR: could not open file "C:\temp\anagraficaANIDIs.csv" for reading: No such file or directory ** Errore ** ERROR: could not open file "C:\temp\anagraficaANIDIs.csv" for reading: No such file or directory Stato SQL: 58P01 The problem is the file C:\temp\anagraficaANIDIs.csv is there and I have granted reading rights to everyone . Any suyggestion? -- 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] COPY command question
On 17/03/2009 14:45, Ivano Luberti wrote: > Hi all, executing the following command inside pgAdmin on my Windows > Vista (please avoid comment, I pray you) : > > copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV Try putting an 'E' in front of the path, like this: from E'c:\\temp\\anagraficaANIDIs.csv' with csv; Also, remember that the file needs to be on the same machine as the server; if you're running pgAdmin on a different machine, this won't work. HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE of several columns using SELECT statement
Hello, I am facing the following problem, nothing tough, I guess (hope)..think it should be some syntactical problem: 1. When trying to update two columns UPDATE myFirstTable SET (fistCol, secCol) = ( SELECT anotherFistCol, anotherSecondCol FROM mySecondTable ) I am getting: ERROR: syntax error at or near "select" 2. When changed to (only the parentheses are changed): UPDATE myFirstTable SET (fistCol, secCol) = (( SELECT anotherFistCol, anotherSecondCol FROM mySecondTable )) I am getting: ERROR: number of columns does not match number of values Does anybody know, what I am doing wrong? Thank you very much and wish you a luxurious day Adam
Re: [GENERAL] COPY command question
Thanks but it keeps on not finding the file: the warning has disappeared ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: No such file or directory ** Errore ** ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: No such file or directory Stato SQL: 58P01 I have also tried uppercasing C without success. I'm sure about the path because I have copied and pasted from the properties window. Raymond O'Donnell ha scritto: > On 17/03/2009 14:45, Ivano Luberti wrote: > >> Hi all, executing the following command inside pgAdmin on my Windows >> Vista (please avoid comment, I pray you) : >> >> copy anagrafica_import from 'c:\\temp\\anagraficaANIDIs.csv' WITH CSV >> > > Try putting an 'E' in front of the path, like this: > > from E'c:\\temp\\anagraficaANIDIs.csv' with csv; > > Also, remember that the file needs to be on the same machine as the > server; if you're running pgAdmin on a different machine, this won't work. > > HTH, > > Ray. > > -- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > r...@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > -- > > -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- 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] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 8:25 AM, Juan Pereira wrote: > Craig Ringer wrote: > > >>> You're almost always better off using a single table with a composite >>> primary key like (truckid, datapointid) or whatever. If you'll be doing >>> lots of queries that focus on individual vehicles and expect performance >>> issues then you could partition the table by truckid, so you actually do >>> land up with one table per truck, but transparently accessible via table >>> inheritance so you can still query them all together. > > Quite interesting! > > The main reason why we thought using a table per truck was because > concurrent load: if there are 100 trucks trying to write in the same table, > maybe the performance is worse than having 100 tables, due to the fact that > the table is blocked for other queries while the writing process is running, > isn't it? Using MySQL has a tendency to teach people bad habits, and this assumption would be one of them. :) -- 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] COPY command question
On 17/03/2009 15:04, Ivano Luberti wrote: > Thanks but it keeps on not finding the file: the warning has disappeared > > > ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: > No such file or directory You haven't said whether the file is on the same machine as the server - is this the case? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] PostgreSQL versus MySQL for GPS Data
Stephen Frost wrote: > As mentioned, you might want to eliminate duplicate entries; no sense > storing information that can be trivially derived. It's pretty easy to do that with a trigger - and you can add a degree of noise correction too, so that "wobble" in GPS position doesn't get recorded - you only log changes of more than a certain distance. -- 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
Re: [GENERAL] Escaping special characters
On Tue, 17 Mar 2009, Sam Mason wrote: > You're putting things in the wrong places! The "E" says that the > following literal is using C style escaping. I.e. you want to say: > > E'Smith \\& Jones' Thanks. Now I understand. Elle. -- 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] UPDATE of several columns using SELECT statement
On Tue, Mar 17, 2009 at 03:55:47PM +0100, adam.slachta wrote: > 1. When trying to update two columns > > UPDATE myFirstTable SET (fistCol, secCol) = ( SELECT anotherFistCol, > anotherSecondCol FROM mySecondTable ) The more common say of expressing this would be something like: UPDATE myFirstTable a SET fistCol = b.anotherFistCol, secCol = b.anotherSecondCol FROM mySecondTable b WHERE a.expr = b.expr; > 2. When changed to (only the parentheses are changed): > > UPDATE myFirstTable SET (fistCol, secCol) = ((SELECT anotherFistCol, > anotherSecondCol FROM mySecondTable )) > > I am getting: ERROR: number of columns does not match number of values PG is somewhat ad-hoc with its support of its record syntax and unfortunately doesn't support the above at the moment. The error is coming from very early on (I think maybe when parsing) and I don't think it ever gets as far as checking that the value coming back from the select is a record of the same structure as is on the left hand side. -- Sam http://samason.me.uk/ -- 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] COPY command question
I'm sorry, you are right that is the problem I had interpreted that as the file should reside on the same machine where pgAdmin (or another client) runs , not the server. Thank you again Raymond O'Donnell ha scritto: > On 17/03/2009 15:04, Ivano Luberti wrote: > >> Thanks but it keeps on not finding the file: the warning has disappeared >> >> >> ERROR: could not open file "c:\temp\anagraficaANIDIs.csv" for reading: >> No such file or directory >> > > You haven't said whether the file is on the same machine as the server - > is this the case? > > Ray. > > -- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > r...@iol.ie > Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals > -- > > -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- 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] UPDATE of several columns using SELECT statement
adam.slachta wrote: > > 1. When trying to update two columns > > UPDATE myFirstTable SET (fistCol, secCol) = ( SELECT anotherFistCol, > anotherSecondCol FROM mySecondTable ) In addition to Sam's reply what you've got there would never work anyway unless mySecondTable only contains one row. You'd need a join. Like Sam says though, we don't support that syntax. -- Richard Huxton Archonet Ltd -- 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] COPY command question
On 17/03/2009 15:28, Ivano Luberti wrote: > I'm sorry, you are right that is the problem > I had interpreted that as the file should reside on the same machine > where pgAdmin (or another client) runs , not the server. > > Thank you again You're welcome! That actually cost me a half-hour or so of frustration not long agoso I was feeling your pain. :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running Postgresl in a virual machine
Hello, I am creating a small web app that uses Postgres. The number of hits/day will be small, maybe a 1000 pages/day. I'm planning on running this in a virtual machine, (Windows Server 2008 with the virtual machine hosting Postgres running Windows Server 2003 R2 x64, or maybe XP x64). The database will have it's own mirrored disks, and the server has plenty of horsepower and RAM. Backup will be copying the quiesced virtual machine disks to a NAS. Does anyone have any experience running Postgres in a virtual machine, or recommendations (or could point me to a place where I could read more about Postgres and Virtual machines)? Thanks. Rich S.
Re: [GENERAL] Running Postgresl in a virual machine
Richard Sickler wrote: Hello, I am creating a small web app that uses Postgres. The number of hits/day will be small, maybe a 1000 pages/day. I'm planning on running this in a virtual machine, (Windows Server 2008 with the virtual machine hosting Postgres running Windows Server 2003 R2 x64, or maybe XP x64). The database will have it's own mirrored disks, and the server has plenty of horsepower and RAM. Backup will be copying the quiesced virtual machine disks to a NAS. Does anyone have any experience running Postgres in a virtual machine, or recommendations (or could point me to a place where I could read more about Postgres and Virtual machines)? Thanks. Rich S. Here is something that might interest you http://wiki.postgresql.org/wiki/Running_&_Installing_PostgreSQL_On_Native_Windows -- 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] Running Postgresl in a virual machine
In response to Richard Sickler : > Hello, > > I am creating a small web app that uses Postgres. The number of hits/day > will be small, maybe a 1000 pages/day. I'm planning on running this in a > virtual machine, (Windows Server 2008 with the virtual machine hosting > Postgres running Windows Server 2003 R2 x64, or maybe XP x64). The database > will have it's own mirrored disks, and the server has plenty of horsepower > and RAM. > > Backup will be copying the quiesced virtual machine disks to a NAS. > > Does anyone have any experience running Postgres in a virtual machine, or > recommendations (or could point me to a place where I could read more about > Postgres and Virtual machines)? Thanks. For the load you're talking about, I wouldn't expect any problems. The biggest issue I've had with virtual machines is that the disk IO is usually crap compared to native access to the disks (experience is mostly using VMWare, other hypervisors may not have this problem) -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Running Postgresl in a virual machine
Richard Sickler wrote: > Does anyone have any experience running Postgres in a virtual machine, or > recommendations (or could point me to a place where I could read more about > Postgres and Virtual machines)? Thanks. Not done it on a Windows VM, but there shouldn't be any special difficulties. Performance shouldn't be a real issue with only a handful of hits. I've had problems with disk i/o under VMs, but that might just be me. You might want to try with quite a small amount of memory allocated to the VM. It could be that a small VM with the host left to handle disk caching is fine for your needs. Oh, if you really want to squeeze it down look at running a pared-down freebsd/linux vm with just PostgreSQL running. I'd have thought you could turn off more services in *nix than Windows. -- Richard Huxton Archonet Ltd -- 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] PostgreSQL versus MySQL for GPS Data
Juan, * Juan Pereira (juankarlos.open...@gmail.com) wrote: > The main reason why we thought using a table per truck was because > concurrent load: if there are 100 trucks trying to write in the same table, > maybe the performance is worse than having 100 tables, due to the fact that > the table is blocked for other queries while the writing process is running, > isn't it? That assumption is incorrect with regard to PostgreSQL, as you'll find if you go through the other links suggested. Writing to a table does not require a table-level write lock in PostgreSQL. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] PostgreSQL versus MySQL for GPS Data
Merlin, > I agree though > that a single table approach is best unless 1) the table has to scale > to really, really large sizes or 2) there is a lot of churn on the > data (lots of bulk inserts and deletes). while agreeing, an additional question: could you please pronounce "really, really large" in other units, like Gigabytes or Number of rows (with average rowlength in bytes, of course) That is: what table size would you or anybody consider really, really large actually? Harakd -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- 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] PostgreSQL versus MySQL for GPS Data
Harald Armin Massa, 17.03.2009 15:00: That is: what table size would you or anybody consider really, really large actually? I recently attended and Oracle training by Tom Kyte and he said (partially joking though) that a database is only large when the size is measured in terrabytes :) So really, really large would mean something like 100 petabytes My personal opinion is that a "large" database has more than ~10 million rows in more than ~10 tables. Thomas -- 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] PostgreSQL versus MySQL for GPS Data
On Tue, 2009-03-17 at 17:44 +0100, Thomas Kellerer wrote: > Harald Armin Massa, 17.03.2009 15:00: > > That is: what table size would you or anybody consider really, really > > large actually? > > I recently attended and Oracle training by Tom Kyte and he said (partially > joking though) that a database is only large when the size is measured in > terrabytes :) > > So really, really large would mean something like 100 petabytes > > > My personal opinion is that a "large" database has more than ~10 million rows > in more than ~10 tables. It entirely depends on workload and hardware. Joshua D. Drake > > Thomas > > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] UPDATE of several columns using SELECT statement
Well, I should have been more specific and not use such a simplified example, which only lead you into wrong direction. What I am really tried to solve instead of UPDATE myFirstTable SET (fistCol, secCol) = ( SELECT anotherFistCol, anotherSecondCol FROM mySecondTable ) is this: xx UPDATE limit_breach lb SET (max_breach, limit_value) = (( SELECT ABS(ov.outright_volume) - NVL(ov.hedge_limit,0), hedge_limit FROM ( outrightvolume_breach ) ov WHERE ov.hedging_desk=lb.hedging_desk AND ov.idmarket = lb.idmarket AND ov.symbol = lb.symbol AND ov.limit_name = lb.limit_name AND lb.breach_end IS NULL )) WHERE lb.breach_end IS NULL AND (lb.hedging_desk, lb.idmarket, lb.symbol, lb.limit_name) IN ( SELECT hedging_desk, idmarket, symbol, limit_name FROM ( outrightvolume_breach ) ov WHERE (ABS(ov.outright_volume) - NVL(ov.hedge_limit,0)) > lb.max_breach ) ; xx Do you think there might be a way how to solve this not leading into splitting the update into two separate update statements for each of the two columns (max_breach, limit_value) ? Adam
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 10:00 AM, Harald Armin Massa wrote: > Merlin, > >> I agree though >> that a single table approach is best unless 1) the table has to scale >> to really, really large sizes or 2) there is a lot of churn on the >> data (lots of bulk inserts and deletes). > > while agreeing, an additional question: could you please pronounce > "really, really large" in other units, like Gigabytes or Number of > rows (with average rowlength in bytes, of course) > > That is: what table size would you or anybody consider really, really > large actually? A good rule of thumb for large is table size > working ram. Huge (really large) is 10x ram. merlin -- 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] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 05:44:48PM +0100, Thomas Kellerer wrote: > So really, really large would mean something like 100 petabytes > > My personal opinion is that a "large" database has more than ~10 million > rows in more than ~10 tables. Surely anything like "large" or "small" is a relative measure that depends on personal experience. Because this mailing list is such a diverse group I'm not sure if they'd ever be particularly useful descriptions. If you're talking with a more cohesive group or you've already defined what you're talking about then maybe--i.e. this database is larger than that one, and so on. I'd suggest we try and not describe things as small or large and just use simple and unambiguous numeric descriptions; i.e. I'm expecting to have a couple of tables with 10 to 100 million rows and the remaining 10 to 20 supporting tables having a few hundred rows. I wouldn't expect row counts to be more accurate than a decimal log and table counts to be more accurate than a ratio of two. That's my two cents anyway! -- Sam http://samason.me.uk/ -- 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 with or without Plus?
Which one should one install? Is there any difference? They are both free right? And both has fulltext support, right? Jen
Re: [GENERAL] PostgreSql with or without Plus?
On Tue, 2009-03-17 at 18:23 +0100, Jennifer Trey wrote: > Which one should one install? Is there any difference? > > > They are both free right? And both has fulltext support, right? > It depends on your requirements. I always suggest using a version that natively supports your operating system packaging. As far as I know Postgres Plus does not do this on Linux. If you are running windows, use what you want :) Joshua D. Drake > Jen > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] UPDATE of several columns using SELECT statement
On Tue, Mar 17, 2009 at 05:57:01PM +0100, adam.slachta wrote: > Do you think there might be a way how to solve this not leading into > splitting the update into two separate update statements for each of the two > columns (max_breach, limit_value) ? That's quite a query; I'm guessing it's from Oracle as it refers to a NVL function and that this should be translated into COALESCE for PG. Would the following do what you want: UPDATE limit_breach lb SET limit_value = ov.hedge_limit, max_breach = ov.max_breach FROM ( SELECT hedging_desk, idmarket, symbol, limit_name, hedge_limit, ABS(ov.outright_volume) - COALESCE(ov.hedge_limit,0) AS max_breach FROM outrightvolume_breach) ov WHERE lb.hedging_desk = ov.hedging_desk AND lb.idmarket = ov.idmarket AND lb.symbol = ov.symbol AND lb.limit_name = ov.limit_name AND lb.breach_end IS NULL AND lb.max_breach <= ov.max_breach; I've obviously not tested it, but I think it's correct. It's also got the advantage of much less duplication of code. -- Sam http://samason.me.uk/ -- 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] using window functions
Hi Tom, psql --version returns 8.4devel. I would in fact like to try a newer snapshot, but I'm not sure how to get one. The devel snapshot 'one-click-installer' link provided at postgresql.org gives a 2009-01-01 version with no options for anything newer or older. I was thinking my next step would be to build from source by grabbing the next daily snapshot. Is this the thing to do? Regards, Daniel > To: mane...@hotmail.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] using window functions > Date: Mon, 16 Mar 2009 23:58:25 -0400 > From: t...@sss.pgh.pa.us > > Daniel Manesajian writes: > > When I try a simple query "select avg(my_int) over (order by my_int rows > > unbounded preceding) order by 1;" I get an error: > > ERROR: syntax error at or near "over" > > You sure you're actually talking to the 8.4 server? Because that's > exactly what you'd get if you tried to feed the command to an older > release. > > You might in fact need a newer snapshot too. The main window functions > commit went in 2008-12-28 but I recall that there were some mop-up fixes > afterwards. At best a 1-Jan snapshot would be a bit unstable. > > regards, tom lane _ Windows Live™ Groups: Create an online spot for your favorite groups to meet. http://windowslive.com/online/groups?ocid=TXT_TAGLM_WL_groups_032009
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones wrote: > > On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: > > The question is: Which DBMS do you think is the best for this kind of >>> application? PostgreSQL or MySQL? >>> >> >> As you can imagine, PostgreSQL. >> >> My main reasons are that in a proper transactional environment (ie >> you're not using scary MyISAM tables) Pg is *much* better about handling >> concurrent load, particularly concurrent activity by readers and writers. >> > > Actually, following this comment it should be noted that if you were to > choose MySQL you'd pretty much be making a decision to *not* be using > transactions at all. The reason for this is that while InnoDB does support > MySQL's geometry data types it does *not* support indexes on geometry > columns, only MyISAM does which does not support transactions. Call me old > fashioned if you like, but I like my data to have integrity ;) > > Erik Jones, Database Administrator > Engine Yard > Support, Scalability, Reliability > 866.518.9273 x 260 > Location: US/Pacific > IRC: mage2k > > > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- - John L Cheng
Re: [GENERAL] Maximum transaction rate
On Tue, 17 Mar 2009, Marco Colombo wrote: If LVM/dm is lying about fsync(), all this is moot. There's no point talking about disk caches. I decided to run some tests to see what's going on there, and it looks like some of my quick criticism of LVM might not actually be valid--it's only the performance that is problematic, not necessarily the reliability. Appears to support fsync just fine. I tested with kernel 2.6.22, so certainly not before the recent changes to LVM behavior improving this area, but with the bugs around here from earlier kernels squashed (like crummy HPA support circa 2.6.18-2.6.19, see https://launchpad.net/ubuntu/+source/linux-source-2.6.20/+bug/82314 ) You can do a quick test of fsync rate using sysbench; got the idea from http://www.mysqlperformanceblog.com/2006/05/03/group-commit-and-real-fsync/ (their command has some typos, fixed one below) If fsync is working properly, you'll get something near the RPM rate of the disk. If it's lying, you'll see a much higher number. I couldn't get the current sysbench-0.4.11 to compile (bunch of X complains from libtool), but the old 0.4.8 I had around still works fine. Let's start with a regular ext3 volume. Here's what I see against a 7200 RPM disk (=120 rotations/second) with the default caching turned on: $ alias fsynctest="~/sysbench-0.4.8/sysbench/sysbench --test=fileio --file-fsync-freq=1 --file-num=1 --file-total-size=16384 --file-test-mode=rndwr run | grep \"Requests/sec\"" $ fsynctest 6469.36 Requests/sec executed That's clearly lying as expected (and I ran all these a couple of times, just reporting one for brevity sake; snipped some other redundant stuff too). I followed the suggestions at http://www.postgresql.org/docs/current/static/wal-reliability.html to turn off the cache and tested again: $ sudo /sbin/hdparm -I /dev/sdf | grep "Write cache" *Write cache $ sudo /sbin/hdparm -W0 /dev/sdf /dev/sdf: setting drive write-caching to 0 (off) $ sudo /sbin/hdparm -I /dev/sdf | grep "Write cache" Write cache $ fsynctest 106.05 Requests/sec executed $ sudo /sbin/hdparm -W1 /dev/sdf $ fsynctest 6469.36 Requests/sec executed Great: I was expecting ~120 commits/sec from a 7200 RPM disk, that's what I get when caching is off. Now, let's switch to using a LVM volume on a different partition of that disk, and run the same test to see if anything changes. $ sudo mount /dev/lvmvol/lvmtest /mnt/ $ cd /mnt/test $ fsynctest 6502.67 Requests/sec executed $ sudo /sbin/hdparm -W0 /dev/sdf $ fsynctest 112.78 Requests/sec executed $ sudo /sbin/hdparm -W1 /dev/sdf $ fsynctest 6499.11 Requests/sec executed Based on this test, it looks to me like fsync works fine on LVM. It must be passing that down to the physical disk correctly or I'd still be seeing inflated rates. If you've got a physical disk that lies about fsync, and you put a database on it, you're screwed whether or not you use LVM; nothing different on LVM than in the regular case. A battery-backed caching controller should also handle fsync fine if it turns off the physical disk cache, which most of them do--and, again, you're no more or less exposed to that particular problem with LVM than a regular filesystem. The thing that barriers helps out with is that it makes it possible to optimize flushing ext3 journal metadata when combined with hard drives that support the appropriate cache flushing mechanism (what hdparm calls "FLUSH CACHE EXT"; see http://forums.opensuse.org/archives/sls-archives/archives-suse-linux/archives-desktop-environments/379681-barrier-sync.html ). That way you can prioritize flushing just the metadata needed to prevent filesystem corruption while still fully caching less critical regular old writes. In that situation, performance could be greatly improved over turning off caching altogether. However, in the PostgreSQL case, the fsync hammer doesn't appreciate this optimization anyway--all the database writes are going to get forced out by that no matter what before the database considers them reliable. Proper barriers support might be helpful in the case where you're using a database on a shared disk that has other files being written to as well, basically allowing caching on those while forcing the database blocks to physical disk, but that presumes the Linux fsync implementation is more sophisticated than I believe it currently is. Far as I can tell, the main open question I didn't directly test here is whether LVM does any write reordering that can impact database use because it doesn't handle write barriers properly. According to https://www.redhat.com/archives/linux-lvm/2009-March/msg00026.html it does not, and I never got the impression that was impacted by the LVM layer before. The concern is nicely summarized by the comment from Xman at http://lwn.net/Articles/283161/ : "fsync will block until the outstanding requests have been sync'd do
Re: [GENERAL] Maximum transaction rate
Greg Smith wrote:
> There are some known limitations to Linux fsync that I remain somewhat
> concerned about, independantly of LVM, like "ext3 fsync() only does a
> journal commit when the inode has changed" (see
> http://kerneltrap.org/mailarchive/linux-kernel/2008/2/26/990504 ). The
> way files are preallocated, the PostgreSQL WAL is supposed to function
> just fine even if you're using fdatasync after WAL writes, which also
> wouldn't touch the journal (last time I checked fdatasync was
> implemented as a full fsync on Linux). Since the new ext4 is more
Indeed it does.
I wonder if there should be an optional fsync mode
in postgres should turn fsync() into
fchmod (fd, 0644); fchmod (fd, 0664);
to work around this issue.
For example this program below will show one write
per disk revolution if you leave the fchmod() in there,
and run many times faster (i.e. lying) if you remove it.
This with ext3 on a standard IDE drive with the write
cache enabled, and no LVM or anything between them.
==
/*
** based on http://article.gmane.org/gmane.linux.file-systems/21373
** http://thread.gmane.org/gmane.linux.kernel/646040
*/
#include
#include
#include
#include
#include
#include
int main(int argc,char *argv[]) {
if (argc<2) {
printf("usage: fs \n");
exit(1);
}
int fd = open (argv[1], O_RDWR | O_CREAT | O_TRUNC, 0666);
int i;
for (i=0;i<100;i++) {
char byte;
pwrite (fd, &byte, 1, 0);
fchmod (fd, 0644); fchmod (fd, 0664);
fsync (fd);
}
}
==
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Benetl, a free ETL tool for files using postgreSQL, is out in version 2.5 !
Dear all, Benetl, a free ETL tool for files using postgreSQL, is out in version 2.6 !!! You can freely download it at : www.benetl.net You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier www.benetl.net Founder of Benetl & Java project manager -- 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] What are the benefits of using a clustered index?
I would only like this as a feature if the optimizer can really take advantage of this. Clustering on every insert or update just for the fun of it won't really give us anything but more expensive writes. I kinda figured if SQL Server and Oracle have it, they probably take full advantage of it for reads.. Mike Simon Riggs wrote: On Tue, 2009-03-17 at 09:16 -0400, Alvaro Herrera wrote: I'm quite sure SQL Server doesn't work this way and I'm not sure about Oracle. Can someone enlighten me on the exact benefit of this? Thanks!! Yeah, they use a completely different definition of "clustered index" from ours. Hopefully we regard it as a missing feature rather than as a separate definition. We could cluster the index, we just don't, yet.
Re: [GENERAL] What are the benefits of using a clustered index?
On Tue, 2009-03-17 at 15:26 -0700, Mike Christensen wrote: > I would only like this as a feature if the optimizer can really take > advantage of this. Clustering on every insert or update just for the > fun of it won't really give us anything but more expensive writes. > > I kinda figured if SQL Server and Oracle have it, they probably take > full advantage of it for reads.. With SQLServer and Oracle the optimization is implicit in the physical design of the index. The Postgres equivalent would be a grouped index, not necessarily clustered in the same way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- 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] Maximum transaction rate
On Tue, 17 Mar 2009, Ron Mayer wrote: I wonder if there should be an optional fsync mode in postgres should turn fsync() into fchmod (fd, 0644); fchmod (fd, 0664); to work around this issue. The test I haven't had time to run yet is to turn the bug exposing program you were fiddling with into a more accurate representation of WAL activity, to see if that chmod still changes the behavior there. I think the most dangerous possibility here is if you create a new WAL segment and immediately fill it, all in less than a second. Basically, what XLogFileInit does: -Open with O_RDWR | O_CREAT | O_EXCL -Write XLogSegSize (16MB) worth of zeros -fsync Followed by simulating what XLogWrite would do if you fed it enough data to force a segment change: -Write a new 16MB worth of data -fsync If you did all that in under a second, would you still get a filesystem flush each time? From the description of the problem I'm not so sure anymore. I think that's how tight the window would have to be for this issue to show up right now, you'd only be exposed if you filled a new WAL segment faster than the associated journal commit happened (basically, a crash when WAL write volume >16MB/s in a situation where new segments are being created). But from what I've read about ext4 I think that window for mayhem might widen on that filesystem--that's what got me reading up on this whole subject recently, before this thread even started. The other ameliorating factor here is that in order for this to bite you, I think you'd need to have another, incorrectly ordered write somewhere else that could happen before the delayed write. Not sure where that might be possible in the PostgreSQL WAL implementation yet. -- * Greg Smith gsm...@gregsmith.com 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
Re: [GENERAL] Maximum transaction rate
Greg Smith wrote: > On Tue, 17 Mar 2009, Marco Colombo wrote: > >> If LVM/dm is lying about fsync(), all this is moot. There's no point >> talking about disk caches. > > I decided to run some tests to see what's going on there, and it looks > like some of my quick criticism of LVM might not actually be valid--it's > only the performance that is problematic, not necessarily the > reliability. Appears to support fsync just fine. I tested with kernel > 2.6.22, so certainly not before the recent changes to LVM behavior > improving this area, but with the bugs around here from earlier kernels > squashed (like crummy HPA support circa 2.6.18-2.6.19, see > https://launchpad.net/ubuntu/+source/linux-source-2.6.20/+bug/82314 ) I've run tests too, you can seen them here: https://www.redhat.com/archives/linux-lvm/2009-March/msg00055.html in case you're looking for something trivial (write/fsync loop). > You can do a quick test of fsync rate using sysbench; got the idea from > http://www.mysqlperformanceblog.com/2006/05/03/group-commit-and-real-fsync/ > (their command has some typos, fixed one below) > > If fsync is working properly, you'll get something near the RPM rate of > the disk. If it's lying, you'll see a much higher number. Same results. -W1 gives x50 speedup, it must be waiting for something at disk level with -W0. [...] > Based on this test, it looks to me like fsync works fine on LVM. It > must be passing that down to the physical disk correctly or I'd still be > seeing inflated rates. If you've got a physical disk that lies about > fsync, and you put a database on it, you're screwed whether or not you > use LVM; nothing different on LVM than in the regular case. A > battery-backed caching controller should also handle fsync fine if it > turns off the physical disk cache, which most of them do--and, again, > you're no more or less exposed to that particular problem with LVM than > a regular filesystem. That was my initial understanding. > The thing that barriers helps out with is that it makes it possible to > optimize flushing ext3 journal metadata when combined with hard drives > that support the appropriate cache flushing mechanism (what hdparm calls > "FLUSH CACHE EXT"; see > http://forums.opensuse.org/archives/sls-archives/archives-suse-linux/archives-desktop-environments/379681-barrier-sync.html > ). That way you can prioritize flushing just the metadata needed to > prevent filesystem corruption while still fully caching less critical > regular old writes. In that situation, performance could be greatly > improved over turning off caching altogether. However, in the > PostgreSQL case, the fsync hammer doesn't appreciate this optimization > anyway--all the database writes are going to get forced out by that no > matter what before the database considers them reliable. Proper > barriers support might be helpful in the case where you're using a > database on a shared disk that has other files being written to as well, > basically allowing caching on those while forcing the database blocks to > physical disk, but that presumes the Linux fsync implementation is more > sophisticated than I believe it currently is. This is the same conclusion I came to. Moreover, once you have barriers passed down to the disks, it would be nice to have a userland API to send them to the kernel. Any application managing a 'journal' or 'log' type of object, would benefit from that. I'm not familiar with PG internals, but it's likely you can have some records you just want to be ordered, and you can do something like write-barrier-write-barrier-...-fsync instead of write-fsync-write-fsync-... Currenly fsync() (and friends, O_SYNC, fdatasync(), O_DSYNC) is the only way to enforce ordering on writes from userland. > Far as I can tell, the main open question I didn't directly test here is > whether LVM does any write reordering that can impact database use > because it doesn't handle write barriers properly. According to > https://www.redhat.com/archives/linux-lvm/2009-March/msg00026.html it > does not, and I never got the impression that was impacted by the LVM > layer before. The concern is nicely summarized by the comment from Xman > at http://lwn.net/Articles/283161/ : > > "fsync will block until the outstanding requests have been sync'd do > disk, but it doesn't guarantee that subsequent I/O's to the same fd > won't potentially also get completed, and potentially ahead of the I/O's > submitted prior to the fsync. In fact it can't make such guarantees > without functioning barriers." Sure, but from userland you can't set barriers. If you fsync() after each write you want ordered, there can't be any "subsequent I/O" (unless there are many different processes cuncurrently writing to the file w/o synchronization). > Since we know LVM does not have functioning barriers, this would seem to > be one area where PostgreSQL would be vulnerable. But since ext3 > doesn't have barriers turned by default either
[GENERAL] Question about Warm Standby
I set up a failover system with one primary server and one standby server. In the standby server's log, I saw the lines like below: Command for restore: copy "E:\archive\0001004C" "pg_xlog\RECOVERYXLOG" The above line means that: the transaction log file 0001004C was copied and renamed to RECOVERYXLOG in the pg_xlog folder of the standby server, right? So the standby server only keeps each transaction log from the primary server temporarily in its pg_xlog folder during the recovery procedure, right? Thanks. Jack
Re: [GENERAL] Maximum transaction rate
Ron Mayer wrote:
> Greg Smith wrote:
>> There are some known limitations to Linux fsync that I remain somewhat
>> concerned about, independantly of LVM, like "ext3 fsync() only does a
>> journal commit when the inode has changed" (see
>> http://kerneltrap.org/mailarchive/linux-kernel/2008/2/26/990504 ). The
>> way files are preallocated, the PostgreSQL WAL is supposed to function
>> just fine even if you're using fdatasync after WAL writes, which also
>> wouldn't touch the journal (last time I checked fdatasync was
>> implemented as a full fsync on Linux). Since the new ext4 is more
>
> Indeed it does.
>
> I wonder if there should be an optional fsync mode
> in postgres should turn fsync() into
> fchmod (fd, 0644); fchmod (fd, 0664);
> to work around this issue.
Question is... why do you care if the journal is not flushed on fsync?
Only the file data blocks need to be, if the inode is unchanged.
> For example this program below will show one write
> per disk revolution if you leave the fchmod() in there,
> and run many times faster (i.e. lying) if you remove it.
> This with ext3 on a standard IDE drive with the write
> cache enabled, and no LVM or anything between them.
>
> ==
> /*
> ** based on http://article.gmane.org/gmane.linux.file-systems/21373
> ** http://thread.gmane.org/gmane.linux.kernel/646040
> */
> #include
> #include
> #include
> #include
> #include
> #include
>
> int main(int argc,char *argv[]) {
> if (argc<2) {
> printf("usage: fs \n");
> exit(1);
> }
> int fd = open (argv[1], O_RDWR | O_CREAT | O_TRUNC, 0666);
> int i;
> for (i=0;i<100;i++) {
> char byte;
> pwrite (fd, &byte, 1, 0);
> fchmod (fd, 0644); fchmod (fd, 0664);
> fsync (fd);
> }
> }
> ==
>
I ran the program above, w/o the fchmod()s.
$ time ./test2 testfile
real0m0.056s
user0m0.001s
sys 0m0.008s
This is with ext3+LVM+raid1+sata disks with hdparm -W1.
With -W0 I get:
$ time ./test2 testfile
real0m1.014s
user0m0.000s
sys 0m0.008s
Big difference. The fsync() there does its job.
The same program runs with a x3 slowdown with the fsyncs, but that's
expected, it's doing twice the writes, and in different places.
.TM.
-
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] Maximum transaction rate
On Wed, 18 Mar 2009, Marco Colombo wrote: If you fsync() after each write you want ordered, there can't be any "subsequent I/O" (unless there are many different processes cuncurrently writing to the file w/o synchronization). Inside PostgreSQL, each of the database backend processes ends up writing blocks to the database disk, if they need to allocate a new buffer and the one they are handed is dirty. You can easily have several of those writing to the same 1GB underlying file on disk. So that prerequisite is there. The main potential for a problem here would be if a stray unsynchronized write from one of those backends happened in a way that wasn't accounted for by the WAL+checkpoint design. What I was suggesting is that the way that synchronization happens in the database provides some defense from running into problems in this area. The way backends handle writes themselves is also why your suggestion about the database being able to utilize barriers isn't really helpful. Those trickle out all the time, and normally you don't even have to care about ordering them. The only you do need to care, at checkpoint time, only a hard line is really practical--all writes up to that point, period. Trying to implement ordered writes for everything that happened before then would complicate the code base, which isn't going to happen for such a platform+filesystem specific feature, one that really doesn't offer much acceleration from the database's perspective. only when the journal wraps around there's a (extremely) small window of vulnerability. You need to write a careful crafted torture program to get any chance to observe that... such program exists, and triggers the problem Yeah, I've been following all that. The PostgreSQL WAL design works on ext2 filesystems with no journal at all. Some people even put their pg_xlog directory onto ext2 filesystems for best performance, relying on the WAL to be the journal. As long as fsync is honored correctly, the WAL writes should be re-writing already allocated space, which makes this category of journal mayhem not so much of a problem. But when I read about fsync doing unexpected things, that gets me more concerned. -- * Greg Smith gsm...@gregsmith.com 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] Question Concerning PostgreSQL license.
If one wishes to use one's own database server of PostGreSQL for commercial purposes, does one need to purchase a commercial license from the "PostgreSQL Global Development Group"? or may it be used for commercial purpose, legally, at no cost?
Re: [GENERAL] Question Concerning PostgreSQL license.
On Mar 17, 2009, at 8:41 PM, Zachary Mitchell, BCIS wrote: If one wishes to use one's own database server of PostGreSQL for commercial purposes, does one need to purchase a commercial license from the "PostgreSQL Global Development Group"? or may it be used for commercial purpose, legally, at no cost? You can use PostgreSQL legally at no cost. There is no requirement to purchase any kind of license for commercial (or non-commercial) purposes. John DeSoi, Ph.D. - 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] Question Concerning PostgreSQL license.
Zachary Mitchell, BCIS wrote: If one wishes to use one's own database server of PostGreSQL for commercial purposes, does one need to purchase a commercial license from the "PostgreSQL Global Development Group"? or may it be used for commercial purpose, legally, at no cost? absolutely any use you want, up to and including repackaging, modified or not, and selling. see http://www.postgresql.org/about/licence - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [pgsql-advocacy] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
On Sat, 2009-03-14 at 15:27 -0700, Christophe wrote: > Hi, > > The video is now available for download! You can find it at: > > http://blog.thebuild.com/sfpug/sfpug-unison-20090311.mov > It is also on Vimeo: http://www.vimeo.com/3732938 Joshua D. Drake > Thanks, > -- Christophe > -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 - 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] Question Concerning PostgreSQL license.
2009/3/18 John R Pierce : > > see http://www.postgresql.org/about/licence > http://www.fsf.org/licensing/licenses/index_html and see there for a broad listing of the kinds of licenses - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postmaster never finishes starting up, silent to boot
Greetings, I've gotten myself in a pickle and had a postgresql (8.2) instance fill its disk completely and shutdown itself down. I've moved the entire data directory to a new, larger slice however postmaster never finishes "starting". Despite configuring postgresql.conf for excessive 'verboseness' nothing gets outputted to syslog or the --log specified file. I have a feeling I'm just not hitting the right search terms, but shouldn't I be able to simply start a fully copied data directory without issue? at the very least I'd expect some kind of output to some kind of log. I have to kill it with a 'pg_ctl stop -D /mnt/data -m i' -- immediate is the only one that actually kills it; and I get this in syslog: Mar 17 22:36:49 rtg postgres[1879]: [8-1] WARNING: terminating connection because of crash of another server process Mar 17 22:36:49 rtg postgres[1879]: [8-2] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server Mar 17 22:36:49 rtg postgres[1879]: [8-3] process exited abnormally and possibly corrupted shared memory. Mar 17 22:36:49 rtg postgres[1879]: [8-4] HINT: In a moment you should be able to reconnect to the database and repeat your command. Mar 17 22:36:49 rtg postgres[1879]: [8-5] CONTEXT: xlog redo zeropage: 16645 there are no other postgres instances running on this machine; actually there is nothing else but the OS running on this machine. Appreciate a cluebat hit. thanks, aaron.glenn - 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] Re: [pgsql-advocacy] Video from the 2009-03-11 SFPUG talk on Unison, by Reese Hart
On Mar 17, 2009, at 9:57 PM, Joshua D. Drake wrote: It is also on Vimeo: http://www.vimeo.com/3732938 Joshua D. Drake Thanks! - 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] postmaster never finishes starting up, silent to boot
On Tue, 17 Mar 2009, Aaron Glenn wrote: Despite configuring postgresql.conf for excessive 'verboseness' nothing gets outputted to syslog or the --log specified file. You shouldn't trust those destinations for getting really unusual errors starting the server. Change your log_destination temporarily back to just "stderr" and start the server with a simple "pg_ctl start". Errors will show up on the terminal. If that works but doesn't tell you anything interesting about why startup isn't going correctly, try changing log_min_messages=debug2 ; that will show you a bunch more information about what's happening. If you didn't delete the pid file after the crash or the old server is still holding onto some shared memory/ports, that might be causing your problem. Another possibility is that you don't have the right permissions on the copy. Once you get the log output on the screen it should narrow the possibilities here. -- * Greg Smith gsm...@gregsmith.com 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
Re: [GENERAL] PostgreSql with or without Plus?
Joshua D. Drake wrote: On Tue, 2009-03-17 at 18:23 +0100, Jennifer Trey wrote: Which one should one install? Is there any difference? They are both free right? And both has fulltext support, right? It depends on your requirements. I always suggest using a version that natively supports your operating system packaging. As far as I know Postgres Plus does not do this on Linux. If you are running windows, use what you want :) Joshua D. Drake Jen Postgres Plus does support Linux and it includes all the PostgreSQL features as well as have many other additional useful features.One of which is its dyna tune module. You can check through the webpage http://www.enterprisedb.com/products/postgres_plus.do for detail additional features. I am using it on Linux and so far haven't faced any problem :-) With Regards --Ashish - 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] Uploading data to postgresql database
Hi, Thanks for the reply. I did take a look at ogr2ogr which can convert kml to shape. But i was wondering if there's some direct way..:) And by upload I meant adding geometry data to the DB. Thanks & regards, Subha Stephen Frost wrote: * Subha Ramakrishnan (su...@gslab.com) wrote: So far, I have been using shp2pgsql to upload data from shape files. I don't want to convert it to shape and then upload it. Have you looked at ogr2ogr? It looks to support KML as a format, and has PostGIS support, though I'm not sure if it can handle the direction you're asking for. Stephen - 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] postmaster never finishes starting up, silent to boot
On top of what the other poster said, I'm wondering if you're not getting any kind of "postmaster not cleanly shutdown, recovery initiated or something like that when you first start it up. You don't tend to see a lot of messages after that until recovery is completed. What does top and / or vmstat or other system monitoring tools say about activity? I'm guessing you just need more patience while pgsql recovers from the unclean shutdown. But it could be something more. Hard to say with what you've told us so far. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
