Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
2009/3/19 Shane Ambler pg...@sheeky.biz: 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. Thomas I would say that as far as GPS data goes the street maps of the world would be pretty big. openstreetmap.org is still a work in progress but their current db dumps gzip down to 6.4GB. It was a while back that I noseyed around with it but I do recall that it compressed well and was very large uncompressed. Don't recall how many rows it contained. I wonder what an almost complete world street map like google maps comes in at? Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. I think this is a big pat on the back for PostgreSQL and a sign that PostgreSQL is now gaining the level of users that it always should have had The 6.4Gb is BZipped XML, its over 150G of XML and is not actually the total size of the OSM database, as that has extra historical and who done it data as well, plus index etc. I would want to have at least 1/2TB minimum to put it on a machine probably more. Peter. Peter. -- 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 remove the duplicate records from a table
2008/10/7 Yi Zhao [EMAIL PROTECTED]: I have a table contains some duplicate records, and this table create without oids, for example: id | temp_id +- 10 | 1 10 | 1 10 | 1 20 | 4 20 | 4 30 | 5 30 | 5 I want get the duplicated records removed and only one is reserved, so the results is: 10 1 20 4 30 5 I know create a temp table will resolve this problem, but I don't want this way:) can someone tell me a simple methold? any help is appreciated, thanks, I would not say this is easier 1. alter table t add key serial; 2. delete from table where key not in (select max(key) from table group on id,temp_id); The truth is this is not any less work then using a temporary table (whole table still needs rewriting). Which method you select really depends on why these duplicate records exist in the first place. Regards Peter -- 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] is a unique key on null field bad?
On 20/02/2008, Geoffrey [EMAIL PROTECTED] wrote: So, we are trying to track down some problems we're having with an implementation of slony on our database. I've posted to the slony list about this issue, but I wanted to get a more generic response from the perspective of postgresql. Is it a 'bad thing' to have a unique key on a field that is often times null? This application has been running along just fine for a couple of years now, but when we try to implement a slony replication solution, this one table consistently has inconsistent data between the primary node and the slave. The problem we are having with slony seems to be related to a table that has just such a key, so we are trying to figure out if this is causing the problem. Its not a problem as such, but it will not exactly be unique as there could be multiple records with null values in that table. So it can't be the primary key, (Hence why Slony has a problem) However it you want to ensure that the field is either Unique or Null (ie not known) then this is a good way of doing it for example with Car Number Plates where the details are not known yet but must be unique once they are known... Regards Peter.
Re: [GENERAL] How to cope with low disk space
On 14/02/2008, Michiel Holtkamp [EMAIL PROTECTED] wrote: Hello list, Just joined, read the archives, but couldn't find a solution to my problem. My question is: 'How can I best determine when to delete data?'. Hopefully this is the right place to ask, otherwise kindly redirect me to the proper list. The situation is as follows: we use PostgreSQL 8.1 to store large amounts of data (we are talking GB's). This data is stored as large objects and when we delete data, we don't forget to delete the corresponding large objects as well. The data stored is deleted after a while (usually a couple of weeks), so far so good. Due to the nature of the information (sound data, recording triggered on certain technical details) the amount of information is not very predictable. Sometimes a lot of data is stored over a period of a few days and the disk runs out of free space (this is not theoretical, in one case it happened already). For this situation we decided that we don't mind deleting some data earlier than normal, to ensure that we can store newly generated data (newer data is more important than older data). The problem: Somehow we have to decide when to delete data earlier than normal. We can't do this by checking disk-space, because postgres reserves disk-space. Freeing disk-space can be done by doing a full vacuum, but this locks tables and could cause data to be lost, besides I don't mind that postgres reserves tables, it's more efficient anyway. If anyone has ideas about this problem, it would be greatly appreciated, I'm sure this is a problem encountered by more persons. I've already looked at certain system tables (specifically pg_stat_user_tables) and at docs like: http://www.postgresql.org/docs/8.1/static/diskusage.html but so for no satisfying solution emerged. Thanks, Michiel Holtkamp ---(end of broadcast)--- TIP 6: explain analyze is your friend I think you need to know depending on a mix of free disk space and free space map usage. If you do a standard Vacuum Verbose it will tell you how full the fsm is. You need to ensure that you have enough free disk space and or a (relativly) full fsm. When the fsm is empty the database has to use disk space, I probably not making any sence
Re: [GENERAL] Conditional updateable view
On 16/12/2007, snacktime [EMAIL PROTECTED] wrote: I can't seem to find an example of how to add restrictions to the where clause of an updateable view created via the rule system. For example I don't want the update to complete if a where clause is missing entirely, and in some cases I want to only allow the update if the where clause specifies a particular column. Is there a way to do this? Quick answer no. Long answer. You can limit which fields can be updated and indeed rename fields or update completely different tables using rules and/or triggers. But you not can say that the where clause must include something. Peter
Re: [GENERAL] Using hashtext and unique constraints together
On 11/12/2007, Mason Hale [EMAIL PROTECTED] wrote:
I'm thinking that an insert trigger that ensures (SELECT count(*) FROM
page WHERE hashtext(url) =
hashtext('http://www.postgresql.org'http://www.postgresql.org%27)
AND url = ' http://www.postgresql.org' ) = 0 won't work given MVCC, as two
transactions could simultaneously insert the same url at the same time.
Why not so long as it also locks the table (share lock should be enough) but
it could slow the table down if lots of transactions write to the table at
once.
Regards
Peter.
Re: [GENERAL] Hijack!
On 11/12/2007, Obe, Regina [EMAIL PROTECTED] wrote: Well said Greg. I have the same problem too of having a crippled mail reader :) Really I find mid posting hard to follow especially if I'm the one that posted the question. I hope we aren't going to hit people with hammers over this minor infraction. It really makes one feel unwelcome. I guess we have beaten this horse enough though. -- ** Hmm Can't stop laughing I think you managed to break every rule in the book with that post. Peter.
Re: [GENERAL] top posting
On 12/12/2007, Stephen Cook [EMAIL PROTECTED] wrote: I am subscribed to some other technical mailing lists on which the standard is top posting. Those people claim that filing through interleaved quotes or scrolling to the bottom just to see a sentence or two is a waste of their time. It is the same thing only backwards. Me, I don't care either way. I try to conform to whatever is the standard for whatever list it is. Why annoy the people giving free support? I suspect that neither is truly better, and that some of the original / very early / expert members just preferred bottom posting for whatever reasons, and it propagated into the standard for this list. Top posting is bad grammar its like English if I wrote the sentence backwards would you under stand it? Its as simple as that I can't under stand whats going on if I need to start at the back of (or bottom) and work back. Its like reading a book you start at the beginning and work to the end, Top Posting is like putting the last chapter or the conclusion at the start. It just does not work. Cutting the original is summarizing what gone before so we can we know the story so far quickly. Maybe we should start teaching this in schools? Different languages have different rules there are languages that do read right to left rather than left to right it does not mean there is anything wrong with those languages, They are just not used here. It understand you would backwards sentence the wrote I. If English like its grammar bad is posting top. (Sounds like something from Star Wars and the meaning has changed) Peter Childs
Re: [GENERAL] Server crashed and now experiencing slow running queries
On 05/12/2007, Keaton Adams [EMAIL PROTECTED] wrote: We're running PostgreSQL 8.1.4 on RHEL. I'm running a vacuum analyze on the mxl_fs_size table to see if that shows anything. -Keaton On 12/4/07 10:50 PM, Keaton Adams [EMAIL PROTECTED] wrote: We have two servers configured the same way running the same type of processes that write/read to the database. Server 2 filled up pg_xlog and crashed. When it came back we began to experience slow query performance. I ran an ANALYZE against the tables involved in the query, but for some reason the optimizer is still choosing a hash join instead of a nested loop join, which is killing performance. The query on Server 1 runs in 14 seconds and the same query on Server 2 runs in 15 minutes: Server 1 - HashAggregate (cost=501922.84..501922.85rows=1 width=532) - Nested Loop (cost= 250961.41..501922.83 rows=1 width=532) Join Filter: ((outer.host)::text = (inner.host)::text) - HashAggregate (cost= 250961.40..250961.41 rows=1 width=26) - Index Scan using mxl_fs_size_did_cre_idx on mxl_fs_size (cost=0.01..250961.40 rows=1 width=26) Index Cond: ((created = (now() - '01:00:00'::interval)) AND (created = now())) - Index Scan using mxl_fs_size_did_cre_idx on mxl_fs_size lh (cost=0.01..250961.40 rows=1 width=42) Index Cond: ((created = (now() - '01:00:00'::interval)) AND (created = now())) Server 2 - HashAggregate (cost=1814101.48..1814129.36rows=2230 width=532) - Hash Join (cost= 906978.28..1814079.18 rows=2230 width=532) Hash Cond: ((outer.host)::text = (inner.host)::text) - Index Scan using mxl_fs_size_did_cre_idx on mxl_fs_size lh (cost=0.01..906877.88rows=40147 width=42) Index Cond: ((created = (now() - '01:00:00'::interval)) AND (created = now())) - Hash (cost= 906978.27..906978.27 rows=1 width=516) - HashAggregate (cost= 906978.25..906978.26 rows=1 width=26) - Index Scan using mxl_fs_size_did_cre_idx on mxl_fs_size (cost=0.01..906877.88 rows=40147 width=26) Index Cond: ((created = (now() - '01:00:00'::interval)) AND (created = now())) Besides ANALYZE, what else can I do / look at to figure out why the optimizer is making the choices it is on Server 2, now causing slow performance problems? Look at table pg_stats eg select * from pg_stats where tablename='msl_fs_size'; also read, http://www.postgresql.org/docs/8.2/interactive/planner-stats.htmland http://www.postgresql.org/docs/8.2/interactive/planner-stats-details.html Can be a little hard going but it might help. On the other hand it might just baffle you with science. Peter Thanks, Keaton
Re: [GENERAL] postgresql table inheritance
On 30/11/2007, Lincoln Yeoh [EMAIL PROTECTED] wrote: Hi, Found this post on Slashdot which I found interesting, any comments? --- post follows --- by Anonymous Coward on Wed Nov 28, '07 03:23 PM (#21509173) Speak for your database -- postgresql does. Postgresql's table inheritance is a flawed concept and has nothing to do with the *type system*. Relations contain tuples, and tuples contain attributes, which are a name plus a VALUE. Those values are chosen from TYPES (sets of possible values). Those types are the TYPE SYSTEM. Table inheritence doesn't even make sense. Tables are analogous to relations. All relations are the same type, the relation type (think set or array to make it easier). How can one value of a type (one table) be a subtype of another value (another table)? That's like saying, 3 is a subtype of 5, if your types are integers. What if you use the expression 3+2 Is that 5 still the subtype of 3? likewise, when you make complex queries with a base table, does the result have any connection with the sub table? It's like gobbledygook, just mashing words together without any understanding. That's why the postgresql table inheritance concept doesn't see more widespread use. Many people quickly discover the limitations (and incorrectly think it's just unfinished, when it actually is flawed). The correct way to store types and subtypes in the database is to store them in the columns. In other words, choose attribute VALUES from a TYPE SYSTEM. Nothing else in the relational model needs to be changed. Something like this, in hypothetical SQL-like language: CREATE TABLE People ( INT id, PERSON_CLASS person ) p1 = PERSON_CLASS.new(name: joe, etc) p2 = CUSTOMER_CLASS.new(name: bob, etc) // assume CUSTOMER_CLASS subclass of PERSON_CLASS INSERT INTO People VALUES (1, p1), (2, p2) SELECT person FROM People WHERE person.name = bob SELECT person, order FROM People JOIN Orders // can't do this in the typical object database This is a solved problem (see The Third Manifesto). It's just a matter of getting somebody to implement it. But the vendors are clueless, thinking object databases are a different model and not wanting to confuse programmers, and programmers are clueless, not even understanding SQL or types and values half the time, so they don't demand anything new from vendors... we never move forward. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Seams like two completely different concepts are getting confused. ie that of Table Inheritance and that of Type Inheritance. They are completely different concepts. Table Inheritance is table structure ie a child table has all the same columns as the old one with some added columns that sore specialist items. This feature is used heavily used in Table Partitioning. Perhaps it should be renamed. Type Inheritance is adding extra features to types eg Varchar(5) is a child of text that adds a maximum length limit of 4 and char(5) is a type of text with a fixed length of 5. But they are all text. This is a very silly example. Just thoughts. Peter.
Re: [GENERAL] PostgresSQL vs Ingress
On 30/11/2007, Alexander Staubo [EMAIL PROTECTED] wrote: On 11/30/07, Peter Childs [EMAIL PROTECTED] wrote: Now We used somthing called Ingres at University (I graduated in 2000) but I've not heard anything about it since and google does not return anything. So we might be talking about different products? http://en.wikipedia.org/wiki/Ingres Alexander. I found ingres website but no mention of a database system so I though they were something else that had taken on the name... The website seams to suggest a RAD development tool and middleware and use other databases underneath. In short the current Ingres is related to Postgresql like Xorg is related to XFree86 or Ubuntu to Debian but much much much older. Wikipeadia also suggests a similar relationship between Posrgresql and SQL Server! I guess this is one of the benifits of the BSD License. Peter.
Re: [GENERAL] PostgresSQL vs Ingress
On 30/11/2007, Ow Mun Heng [EMAIL PROTECTED] wrote: I was browsing the net yesterday after reading through the thread on PG vs Informix and I was curious as to Ingress. Ingress is also an open source RDBM (and DataWarehouseing) and I'm wondering if anyone here has anything to say about it. They also offer community editions but I've not gone to see how much it differs/offers compared to PG. I've tried to DL the community edition, but upon log-in, I only get a blank page. (tried on both firefox and opera) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ I think this can be answered easily see http://www.postgresql.org/about/history. Ingres was postgresql about 22 years ago! Now We used somthing called Ingres at University (I graduated in 2000) but I've not heard anything about it since and google does not return anything. So we might be talking about different products? So I'm slightly confused. Peter Childs
Re: [GENERAL] Unused item pointers - can someone explain?
On 26/11/2007, Elmer [EMAIL PROTECTED] wrote: On 23 Lis, 10:23, [EMAIL PROTECTED] (Peter Childs) wrote: Yes, however found x removable have just been found and are now unused, so on the next run this number will be added to the unused unless they get used again in the mean time. The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. If its high it may be worth clustering or running vacuum full but only if you don't think you table will never or unlikely to grow (insert) or change (update) by less than that number of records before you next run vacuum. Generally only worry if the number is very very high (over 1). The best way of understanding the numbers is to run vacuum at regular intervals and compare the output. Peter. Thank you for your explanation - it's not easy to get help in this subject... But how it is possible that new unused. You wrote: The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. This is important information for me but I still can't understand why this number keeps growing.Correct me if I wrong but if there is 17000 unused tuples in free space map, they should be used in first place for creating new tuples versions. This should cause that next 17000 operations (consists of INSERT,UPDATE,DELETE) would use fsm for row version creation instead of creating entirely new tuples at the end of table file. If I understand it correct number of unused item pointers should shrink between vacuums (but it still grows)... Hmm that should have been number of tuples left in the free space map at the start of the vacuum. So if you run a second vacuum in quick succession the number under removable will have been added the the unused value to become the new unused value. If you have a growing table unused should be 0 (or shrinking) If you have a busy table thats not vacuumed often enough unused should be 0 but removable will be high (so long as you vacuum at regular intervals) If you have a shrinking table unused will grow. If you have a steady table that was larger at some point the past eg an update to the whole table. unused will be large If unused is the amount of bloat you have but is only useful depending on when you last vacuumed if you vacuumed twice within a couple of seconds the second number is not very useful. If I'm wrong I'm sure someone will correct me. Peter Childs
Re: [GENERAL] Unused item pointers - can someone explain?
On 21/11/2007, Elmer [EMAIL PROTECTED] wrote: Hello, I vacuumed one of my database tables. In vacuum output I saw the following information about unused item pointers: There were 19310 unused item pointers. As I understand It means that my table's file consists 19310 free tuple pointers. After some minutes I run vacuum again. It was quite unexpected for me that number of unused item pointers was increased to 19351. I thought unused item pointers are used in first place when new tuples versions are created but this example makes me think I am wrong. Can someone explain this to me? Yes, however found x removable have just been found and are now unused, so on the next run this number will be added to the unused unless they get used again in the mean time. The number ie the unused is the number of tuples left in the free space map unused since the last vacuum. If its high it may be worth clustering or running vacuum full but only if you don't think you table will never or unlikely to grow (insert) or change (update) by less than that number of records before you next run vacuum. Generally only worry if the number is very very high (over 1). The best way of understanding the numbers is to run vacuum at regular intervals and compare the output. Peter.
Re: [GENERAL] Primary Key
On 21/11/2007, Sascha Bohnenkamp [EMAIL PROTECTED] wrote: Fie on you evil synthetic key lovers. Long live the Natural Key! think of an foreign key referencing a primary key with 6 columns *urgs* never! The worse thing I meet is people who think primary keys need to be integer single field unique serial fields I tend to agree that primary keys should be single fields if they need to be referenced but should also be natural if at all possible. ie use car number plates rather than some serial int. Peter.
Re: [GENERAL] Resurrected data files - problem?
On 08/11/2007, Albe Laurenz [EMAIL PROTECTED] wrote: We use a tape backup software that does incremental backups as follows: - In a full backup, all files are backed up. - In an incremental backup, only the files with modification date after the last backup are backed up. Now when such a backup is restored, you first have to restore the full backup, and then the incremental backup. The problem is that files which were deleted between the full and the incremental backup will get resurrected after such a restore. So if we perform our database backups with incremental backups as described above, we could end up with additional files after the restore, because PostgreSQL files can get deleted (e.g. during DROP TABLE or TRUNCATE TABLE). My question is: Could such resurrected files (data files, files in pg_xlog, pg_clog or elsewhere) cause a problem for the database (other than the obvious one that there may be unnecessary files about that consume disk space)? This will not work at all. Try re-reading the instructions on backup in the manual. oh and always, always, always test your backup works before you actually need it! Peter Childs
Re: [GENERAL] conditional alter table add ?
On 17/10/2007, Lothar Behrens [EMAIL PROTECTED] wrote: Hi, I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. Is there any way to do this ? Not easily in a straight forward sql script. your going to need to write your script in a scripting language (like perl, python or ruby) then do the alter table query dependent on other queries to the database, Peter.
Re: [GENERAL] 8.3b1 in production?
On 24/10/2007, Gregory Stark [EMAIL PROTECTED] wrote: rihad [EMAIL PROTECTED] writes: Hi, Does anyone have an idea how risky it is to start using 8.3b1 in production, with the intention of upgrading to release (or newer beta) as soon as it becomes available? Risky compared to running a release, that is. Beta - release upgrades might be less tricky than 8.2 - 8.3. Well nobody's going to be able to guess at what problems haven't been found yet. All we can say decisively is what bugs have already been found: . On Windows UTF8 encoding isn't allowed . VACUUM does an unnecessarily large amount of I/O . Toaster could cause failures on machines with strict alignment . Resources limits in Windows limit the number of clients . pg_tablespace_size() on pg_global fails even for superuser . ABI break with old libpq for applications which depend on encoding IDs (such as initdb -- you can't run initdb with an 8.2 libpq against an 8.3server) . invalid tsvector input could cause crashes . ALTER COLUMN TYPE would reset the index's options, possibly moving it to the default tablespace or worse Also: . A new data type, txid, was added . Several new contrib modules were added to aid tsearch migration . Some tsearch functions were removed or modified . tsearch word categories were redefined and renamed . Make plan invalidation work for dropped sequences (etc) . Be careful to get share lock on each page before computing its free space. . This avoids useless checkpoint activity if XLogWrite is executed when we have a very stale local copy of RedoRecPtr. . Teach planagg.c that partial indexes specifying WHERE foo IS NOT NULL can be used to perform MIN(foo) or MAX(foo) . Remove an Assert that's been obsoleted by recent changes in the parsetree representation of DECLARE CURSOR. Report and fix by Heikki. . Ensure that the result of evaluating a function during constant-expression simplification gets detoasted before it is incorporated into a Const node. . Make dumpcolors() have tolerable performance when using 32-bit chr, as we do . Make role is not permitted to log in errors not be hidden . Remove quotes around locale names in some places for consistency. . Add missing entry for PG_WIN1250 encoding, per gripe from Pavel Stehule. Also enable translation of PG_WIN874 Hmm looks like December release might be a dream then I was wondering why my PITR base back up was taking 2 hours on my 8.3 test database where as it takes 50 minutes on 8.1 and the database files are meant to be smaller on a freshly installed 8.3 server rather than a 8.1.1server that aint been rebuilt since 8.1.1 was newly out. I was planning to upgrade to 8.3 once its out... Down time for upgrades is somwhat lacking in a 24x7 business. Oh my 8.1 server has been up for well over a year with out being down at all. the database for longer which really show how good postgres really is 377 days uptime on computer and I think that was to move a plug. Peter Childs
Re: [GENERAL] Performance Issues
On 23/09/2007, Alvaro Herrera [EMAIL PROTECTED] wrote: Christian Schröder wrote: Alvaro Herrera wrote: Christian Schröder wrote: I think it is my job as db admin to make the database work the way my users need it, and not the user's job to find a solution that fits the database's needs ... Is there really nothing that I can do? You can improve the selectivity estimator function. One idea is that if you are storing something that's not really a general character string, develop a specific datatype, with a more precise selectivity estimator. If you are you up to coding in C, that is. Hm, that sounds interesting! I will definitely give it a try. Will that also solve the problem of combining more than one of these conditions? As far as I can see, the main issue at the moment is that we often have ... where test like '11%' and test not like '113%' in our queries. Even if the selectivity estimation of the single condition will be improved, it will still be wrong to multiply the selectivities. Unless you can come up with an operator that expresses better the starts with 11 but not with 113 type of condition. For example if these were telephone number prefixes or something like that, probably there's some way to do that in a single operation instead of two, and the selectivity function could produce a much more accurate estimate saving the need to multiply. select a from b where a ~ '^11[^3]' Is that what you want? I usually find using ~ far better than like. Peter Childs -- Alvaro Herrera http://www.advogato.org/person/alvherre I think my standards have lowered enough that now I think 'good design' is when the page doesn't irritate the living f*ck out of me. (JWZ) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Reordering columns, will this ever be simple?
On 13/08/07, novnov [EMAIL PROTECTED] wrote: I would like to make a request for this feature to be added to postgres. Postgres is a really great database. I'm still very much a novice at using postgres but in general, it's been a very good experience and I plan to use it as often as I can. The community is very helpful. My projects tend to be more seat of the pants than your average enterprise database. Adding the ability to reorder columns in a simple manner would be a very nice addition. I think such a feature would add to the 'curb appeal' of postgres...make it more user friendly, more flexible. I don't know anything about the internals of databases, how they actually work, but from my 'ignorance is bliss' perspective, it seems odd that one can alter field names at will in postgres but reordering columns is such a big deal. Maybe internally columns are id'd via index position, thus order matters, and the name is relatively light duty property? Maybe too hard to explain it in simple terms. Yes thats it or nearer enough, It was not until quite recently that you could even drop a column. Even now dropping a column is not a very good idea, It leaves the column around inside the database marked as deleted data still intact (if I remember correctly) So you can even get the data back by hmm messing with the database internals (not a very good idea I believe an update on pg_attribute will do the trick:)). Hence if you do more than drop the odd column here or there you may be better dumping and reloading anyway! Further more if you then add another column the already dropped column does not get recycled. I also remember some 7.2 drivers not coping correctly with 7.3 due to dropped columns. Don't quote me on that I just remember having some problems about 3 years ago with Qt! Hmm this is beginning to sound like a leak. Peter Childs Decibel! wrote: On Tue, Aug 07, 2007 at 02:28:20PM -0500, Tony Caduto wrote: Gregory Stark wrote: novnov [EMAIL PROTECTED] writes: Is there any plan to add such a capability to postgres? It's been talked about. I wouldn't be surprised to see it in 8.4 but nobody's said they'll be doing it yet and there are a lot of other more exciting ideas too. From a admin tool developers perspective the ability to reorder columns without manually copying to a new table and all that is pretty exiting :-) Patches welcome. :) BTW, this is much more likely to happen if we divorce presentation order from actual storage order, something that there is some desire to do because it would allow the engine to automagically store things in the optimal ordering from an alignment standpoint. -- Decibel!, aka Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) -- View this message in context: http://www.nabble.com/Reordering-columns%2C-will-this-ever-be-simple--tf4231761.html#a12129772 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [NOVICE] alter table table add column
On 31/07/07, Ronald Rojas [EMAIL PROTECTED] wrote: Oh yes you have a good point. But then I will still have to test insert and update on views. Thanks a lot michael! On Tue, 2007-07-31 at 00:56 -0500, Michael Glaesemann wrote: On Jul 31, 2007, at 0:23 , Ronald Rojas wrote: Yes I know that procedure but I would like to insert in between because I have at third party software that will call the said schema and one of its dependencies with the mapping is it should have the correct order with what the receiving end will be use for the mapping. And in this case, I can't modify the receiving structure (third-party) and the tables that I will be using is in on production state. So would only mean that I have to schedule a very fast maintenance, probably 5 to 10 secs just to re-initialize the tables. Another option would be to use views to change the column order, which would work for selects. I believe you could create rules for insert and update as well, if necessary. Perhaps this would be a solution to your problem. Michael Glaesemanngrzm seespotcode net But really you should not be using select * from ... anyway, Always list your column names, That way you will aways get the columns in the order you want rather than in the order they are stored. This really belongs in a FAQ Peter.
Re: [GENERAL] list all columns in db
On 07/06/07, Jon Sime [EMAIL PROTECTED] wrote:
Jonathan Vanasco wrote:
Does anyone have a trick to list all columns in a db ?
No trickery, just exploit the availability of the SQL standard
information_schema views:
select table_schema, table_name, column_name
from information_schema.columns
where table_schema not in ('pg_catalog','information_schema')
order by 1,2,3
Is there any easy way to remove the views from the query?
Peter.
If you want an equivalent that uses pg_catalog (non-portable outside of
PostgreSQL) you could instead do:
select n.nspname as table_schema, c.relname as table_name,
a.attname as column_name
from pg_catalog.pg_attribute a
join pg_catalog.pg_class c on (a.attrelid = c.oid)
join pg_catalog.pg_namespace n on (c.relnamespace = n.oid)
where c.relkind in ('r','v') and a.attnum 0
and n.nspname not in ('pg_catalog','information_schema')
order by 1,2,3
-Jon
--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Creditcard Number Security was Re: [GENERAL] Encrypted column
On 05/06/07, Andrew Sullivan [EMAIL PROTECTED] wrote: On Tue, Jun 05, 2007 at 09:28:00AM -0500, Ron Johnson wrote: If he is a CC customer, the system (which I am DBA of) bills his card directly, saving the customer much time and effort. So surely what you have is a completely separate system that has exactly one interface to it, that is signaled to provide a transaction number and that only ever returns such a transaction number to the online system, and that is very tightly secured, right? It is possible to make trade-offs in an intelligent manner, for sure, but you sure as heck don't want that kind of data stored online with simple reversible encryption. A Unfortunately you still need to store them somewhere, and all systems can be hacked. Yes its a good idea to store them on a separate system and this is an important part of designing your systems to ensure that the simple user interface is somehow limited. Peter.
Re: [GENERAL] Rounding datetimes
On 22 May 2007 10:08:24 -0700, jws [EMAIL PROTECTED] wrote:
Is there a way to round an interval to the nearest minute or do I need
to create a function for this?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
date_trunc('minute',interval)
see
http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Peter Childs
Re: [GENERAL] are foreign keys realized as indexes?
On 09/05/07, Lew [EMAIL PROTECTED] wrote: Felix Kater wrote: I am not bound to indexes, however, wonder if foreign keys itself are non-atomic functionality. I mean: if foreign keys are based on some other lower level functionality like indexes or anything else which I could use as a substitute--in what way ever. Of course, I want to gain the same (referential integrity etc.). If foreign keys are, however, something unique which can't be replaced by any other pg function (I am of course not taking into account things like multiple queries bound together by transactions...) then I have to go though it and implement it into my pg interface (looking at the information_schema: This seems to be quite a bunch of work...). Semantics are not a trivial thing. Foreign keys are a fundamental semantic of the relational model. They do not mean the same thing as an index at all. I find it strange that anyone would resist the notions of primary and foreign keys, when they are the basis of the relational model. Indexes aren't even part of the relational model - they are a hack to enhance performance. Sure they ultimately break down to machine instructions, but that's in a whole different domain of discourse. A data model is built up from primary keys, foreign keys and dependent data. They are fundamental. They /are/ the building blocks of your database. Expressing these molecular concepts in terms of their constituent atoms will not convey the molecular properties; you lose a tremendous amount of information. Just use the syntax that best expresses your structure: PRIMARY KEY and FOREIGN KEY. Apart from anything a unique constraint is NOT the same as a unique index, as you need a not null constraint on the column as well. Peter.
Re: [GENERAL] Business days
On 26/04/07, Greg Sabino Mullane [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 However, you quickly run into the problem of holidays. While you could construct a helper table listing all the holidays, ones that don't fall on the same day every year (e.g. Easter) will trip you up. Er, isn't Easter usually on a Sunday? I meant the same numerical date, e.g. Christmas is always December 25th, and so is a little easier programatically than the rules for Easter. If you meant that Sunday is never a business day, then yes, it was a bad example. :) Anyway, I also found this, the first hit if you google sql holidays: http://www.sqlmag.com/Article/ArticleID/25899/sql_server_25899.html The big ugly union might need to be munged a bit, but most of the non- weekend US holidays seem to be there. Sure, that's an alternative, but it seems a bit too much reinventing an already existing wheel. I was amused to see the script had the ill-fated Lee-Jackson-King day in it. Ideally, we'd want a Postgres table that describes the rules for each holiday, and then a function that reads it on the fly. Perhaps a project for another day... More complicated than that Easter read Good Friday and Easter Monday. Christmas Eve (does it count or not) Christmas Day, Boxing Day if it falls on a Weekend, Bank holidays are applied in loo on the following monday and tuesday as necessary. There are some quite good list available but you will have to work out what your local logic actually is. Peter. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL][OT] cutting out the middleperl
On 27/03/07, Merlin Moncure [EMAIL PROTECTED] wrote: On 22 Mar 2007 14:58:15 -0700, Kev [EMAIL PROTECTED] wrote: Hi everyone, I'm still in the design phase of a project. I was just wondering if anyone has any thoughts or experience on the idea of cutting the P out of the LAMP (or in my case, WAMP for now) stack. What I mean is having everything encapsulated into sql (or plpgsql or plperl where needed) functions stored in the pgsql server, and have Apache communicate with pgsql via a tiny C program that pretty much just checks whether the incoming function is on the allowed list and has the proper data types, then passes it straight in. Any errors are logged as potential security breaches. I'm really new to mod_perl too, so another question would be if this would be much faster than a simple perl script that did the same thing. I ask this because I realize I need to carefully check data coming into pgsql functions as well as at the client end. Why maintain a bunch of scripts with names similar to the functions they're calling and all performing similar checks anyway? I was kinda salivating at the thought of how fast things would be if you cut out the A as well, by using a Flash applet to give socket access to JavaScript. But then I guess you have to make your pgsql server itself publicly accessible on some port. Is that just asking for trouble? I appreciate any comments or thoughts anyone might have on this. IMO, I think 'thin middleware' approach is a great way to design applications...so you are right on the money. The web server. IMO, should be mostly concerned about rendering html. I don't think eliminating the middleware is really practical. While you could use a thick-client javascript framework like GWT and write your queries in javascript (getting data back via json), I don't think it's really possible to secure this properly without killing the 'ease of implementation' factor. Then again, it's no worse then your typical old school visual basic or delphi in-house application so common in the 90's. I really miss the simplicity of Delphi. Strangely the in-house application is often still the better way to go. The web can make everything 3 times more complicated than it needs to be. Toolkits like GWT help this but you still need to write middleware even when you can trust the trust the end user. Hence most places still use in-house applications except the VB or Delphi gets replaced with Ruby or Python. Here we use C++ and Qt but thats another story. The web should still be used for mass market apps and heavy communication apps and not standard desktop answers. (Unless you particularly like writing everything twice) The secret is to use the right tool for the right job, and not try and find the does everything spanner that fits all nuts and also undoes screws too. Its never going to work in every case. Unfortunately some people like this idea. Peter. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] select all matches for a regular expression ?
On 23/02/07, Anton Melser [EMAIL PROTECTED] wrote: Hi, I need to be able to get all the matches for a particular regexp from a text field that I need to use in another query in a function. Is this possible with plpgsql? Do I have to install the perl language? Cheers Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster yes. use like or ~ see http://www.postgresql.org/docs/8.2/static/functions-matching.html There is no need to use perl. Peter. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Questions about horizontal partitioning
On 09/01/07, Ron Johnson [EMAIL PROTECTED] wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/08/07 20:39, Tom Lane wrote: John Sales [EMAIL PROTECTED] writes: By doing this, I'm hoping that the query optimizer is smart enough to see that if a query comes in and requests only the six columns (that are in the narrower table) that PostgreSQL won't have to load the wider table into the buffer pool, and thereby actually have to only access about 10% the amount of disk that it presently does. Is this a sound theory? No. It still has to touch the second table to confirm the existence of rows to join to. But if a query /requests *only* the six columns (that are in the narrower table)/, why will the optimizer care about the other 224 columns? If you are doing an inner join (read normal join) the column has to exist in both tables to be in the final result. If your doing an outer join it depends upon its type (left, right or full) and then postgres may not optimise it out. Peter Childs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Autovacuum Improvements
On 19/12/06, Chris Browne [EMAIL PROTECTED] wrote: matthew@zeut.net (Matthew O'Connor) writes: 2) Once we can have multiple autovacuum workers: Create the concept of hot tables that require more attention and should never be ignored for more that X minutes, perhaps have one autovacuum worker per hot table? (What do people think of this?) One worker per hot table seems like overkill to me; you could chew up a lot of connections that way, which could be a DOS. Sounds like a max workers config varible would work quite well here. Bit like the max connections varible. If we run out of workers we just have to wait for one to finish. I think we need one daemon to analyse what needs vacuuming and then lauch workers to do the actual work.. Peter Childs That you have a foot gun is guaranteed; I think I'd rather that it come in the form that choosing the hot list badly hurts the rate of vacuuming than that we have a potential to chew up numbers of connections (which is a relatively non-renewable resource). -- (format nil [EMAIL PROTECTED] cbbrowne cbbrowne.com) http://linuxdatabases.info/info/ There are no civil aviation for dummies books out there and most of you would probably be scared and spend a lot of your time looking up if there was one. :-) -- Jordan Hubbard in c.u.b.f.m ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] advanced index (descending and table-presorted descending)
On 21/11/06, Vic Cekvenich [EMAIL PROTECTED] wrote: Can pgSQL 8.x do descending indexes like mySQL 5.1? (so 1st column is descending and rest are asscending?) Can pgSQL 8.x do physically sorted table (like a forced index order) so we don't need order by? tia, .V No and I don't really believe mySQL can if it can its a bug, I would not trust it. If you want your results sorted you need the order by.. However, Cluster might work for you, but you need to re-cluster after every updates or inserts, so it will probably be fine for static data. Which I suspect is the same for mySql but I can't be bothered to check, If mysql really works like this its worse that I originally thought it was. Even so I would always include the order by clause for safety. (Its a bug because the table(file) will always grow and grow and grow and eventually take up the entire disk with tonnes of gaps which can be closed without unsorting the data or rewriting the entire file ie cluster) Peter. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [ANNOUNCE] == PostgreSQL Weekly News - April 01 2005 ==
David Fetter wrote: == PostgreSQL Weekly News - April 01 2005 == As of today, the license of PostgreSQL, including all its libraries, is changing from the unfashionable BSD license to the more popular GPL. Fine I can't see the difference, but have you not got to clear this with every developer past and present to get it through. Tom Lane has accepted a job at MySQL AB. He announced his departure rather abruptly on the Hackers list yesterday: So long, suckers! I wonder why? With the change in leadership, core team members will implement changes to the project. One which has picked up a lot of enthusiasm is migrating the PostgreSQL code base away from C. Bruce Momjian explains, C has served us pretty well for 20 years. But we think it's time to move to a more modern, and let's face it, more popular programming language. With the JVM testing, and the performance degradation, and retraining, we probably won't do any development for 2 years, but we figure it's worth a try. Ok the reason, If you think you can write a database that runs faster than a snail in Java, you need to re-think. Tom obvouly knows this and when to jump ship. Perl or Python would be faster!!! (And I know I've worked in them and know the only thing slower than Java is good old paper and pencil.) PWN notes that the migration to Java will affect only the query analyzer, executor, and storage engine. The client tools will be re-written in COBOL. Could that sentance please be reversed I could understand the client tools being written in Java (just about) Cobol can more than cope with a database, its older than C, A new language translation set of .po files for into 1337. w3lc0m3 t0 t|-|3 n3\/\/ /\/\3/\/\|3rz 0f teh c0mm|_|n1t'/!!1!! Either this message is a joke, Or you have all gone mad? I see a postpostgres on the horizon. Peter Childs == PostgreSQL Product News == PL/APL procedural language project launched http://www.pgfoundry.org/projects/plapl Bricolage 1.8.7.2.9.11.0.6.201.7a released. http://www.bricolage.cc/news/announce/changes/bricolage-1.8.7.2.9.11.4.6.201.7a/ == PostgreSQL in the News == General Bits: http://www.varlena.com/GeneralBits/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] problems with 8.0 Rc4
On Tue, 11 Jan 2005, Bruno Wolff III wrote: On Tue, Jan 11, 2005 at 15:40:07 +, Peter Childs [EMAIL PROTECTED] wrote: I thought pg_autovaccum was going to be built into 8.0 or was that only a rumor. Due to some unfortunate things it was not ready by the beta cutoff, so it is staying in contrib for the 8.0 release. Oh dear, so thats one feature that we will expect to see in 8.1! I've finnally worked out what was wrong and you're all going to laugh. I had installed postgres 8.0 under a fresh install of Debian 8.0 testing, switched my drives to ext3 and started postgres up. Fine you might all say, Well first I noticed a message in the log saying it had mounted the drives at ext2 and then I got a few kernel panics, So I unmounted my database drive and remounted it. and guess what the errors went away, So I upgraded the kernel to the one in Debian Unstable and now its all fine. In short its the old bug in Debian that by the time Debian is ready its actually out of date. Peter Childs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] problems with 8.0 Rc4
I've been trying to check that my current application working on postgres 7.4 will work with 8.0. I've not checked the application yet but I'm getting a few error messages, I don't understand and are worring me slightly. I need to get 8.0 to work because I'm keen to start using the new pitr feature in 8.0 as soon as it goes stable. The error messages run as follows ERROR: tuple concurrently updated STATEMENT: ANALYZE public.streetuniq and ERROR: could not access status of trasaction 393311 DETAIL: could not read file /database/db/pg_clog/ at offset 98304: Success STATEMENT: ANALYZE public.backedup and ERROR: relation people TID 362/32: OID is invalid STATEMENT: vacuum full verbose analyze; I'm running pg_autovacuum which caused the first two errors and ran a vacuum full verbose analyze straght after loading the data. hence the third message. I thought pg_autovaccum was going to be built into 8.0 or was that only a rumor. I've also read though the pitr documentation and it seams slightly complicated. I currently backup every 12 hours (3am and 3pm) with a dump, So I'm thinking to get a complete pitr backup with full backup every 12 hours, I need to take a copy of the data directory and store all the logs from the start of the backup to the end of the next backup to have a complete log of that period of time. I'm planning to store my backup on CD with a new set of CDs for each backup period. Whats the basic principle to do this and how can I save on the number of discs I need, My database is 2.8Gb on disk currently after a fresh load from dump, the dump is 766Mb uncompressed. Peter Childs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Link to development version of docs on website?
Devrim GUNDUZ wrote: Hi, On Mon, 10 Jan 2005, Chris wrote: I know they can be accessed at developer.postgresql.org, but I didn't see a link to the docs for postgresql 8 on the new website, did I miss it somewhere? www.PostgreSQL.org holds docs only for stable releases. Regards, -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html However http://developer.postgresql.org/docs/postgres/index.html has the Beta docs, Its under Testing just above Beta Information on the developer web site. Very Very Important if you are beta testing since quite a few things have changed for 8.0. Now why is RC4 crashing on my debain box. Currently its causing a panic in ext3 mid dump load I'll post more details later... Peter Childs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] 8.0 rc2 Problem
On Mon, 27 Dec 2004, Peter Childs wrote: Tom Lane wrote: Peter Childs [EMAIL PROTECTED] writes: 2. I loaded out 7.4 database on via a dump and reload. Once done I keep getting errors when it trys to analyse (I'm using autovacuum) Nothing unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR: could not access status of transaction 2684354560 Is this repeatable if you start over (re-initdb and reload)? If so I'd be very interested to see the dump file. regards, tom lane I've dumped twice and reloaded twice same problem both times. Its difficult to let you see the dump as due to Data Protection. I re-inited once and reloaded just to check. I'll try a dump without data and see if that causes the same problem If that fails I'll can send you that. Then I might try adding data for tables that are not a risk. I'll do it tomarrow now, as I'm off as my Wife had a baby yestarday. Peter Childs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Further examination I orignally used by 7.4 backup (taken every 12 hours and well tested that they work under 7.4 anyway) rather than redumping using pg_dump 8.0. I have now dumped using pg_dump version 8.0 same problem. I dumped just the schema. upon load into 8.0 a couple of lines saying ERROR: relation test_id_seq does not exist STATEMENT: REVOKE ALL ON TABLE shopping_id_seq TO PUBLIC; ERROR: relation test_id_seq does not exist STATEMENT: GRANT ALL ON TABLE test_id_seq TO PUBLIC; Not that important but no problem with the empty database but then its empty! Since the dumps are just sql right? I'm guessing that this has to be some internal problem. Peter Childs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] 8.0 rc2 Problem
Tom Lane wrote: Peter Childs [EMAIL PROTECTED] writes: 2. I loaded out 7.4 database on via a dump and reload. Once done I keep getting errors when it trys to analyse (I'm using autovacuum) Nothing unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR: could not access status of transaction 2684354560 Is this repeatable if you start over (re-initdb and reload)? If so I'd be very interested to see the dump file. regards, tom lane I've dumped twice and reloaded twice same problem both times. Its difficult to let you see the dump as due to Data Protection. I re-inited once and reloaded just to check. I'll try a dump without data and see if that causes the same problem If that fails I'll can send you that. Then I might try adding data for tables that are not a risk. I'll do it tomarrow now, as I'm off as my Wife had a baby yestarday. Peter Childs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] 8.0 rc2 Problem
Right just installed rc2 to test so that we can use 8.0 once its out. Loads of nice features, Nice to see them all. Postgres 8.0 seams faster, but this might be due to the reload. Oh the point in time recovery feature are there any example scripts to say copy the files to somewhere then print them onto cd or somthing once created. Oh and is it possible to examin the point in time logs and see what happerened when, ie which table got updated to what, very useful when research problems later Probably would require some form of viewer I guess. 2 Problems encountered. 1. Confirguration file is completly different, so it you have one that you have configured carfull for 7.4 it will not work at all with 8.0 many configuration options have changed there names or changed how they work. I can understand new ones and dropping some of the old ones but why change it so complely? 2. I loaded out 7.4 database on via a dump and reload. Once done I keep getting errors when it trys to analyse (I'm using autovacuum) Nothing unsuall just a straght forward pg_dump from 7.4 loaded into 8.0. Dec 27 07:34:45 ex37 postgres[5745]: [28-1] ERROR: could not access status of transaction 2684354560 Dec 27 07:34:45 ex37 postgres[5745]: [28-2] DETAIL: could not open file /data/db/pg_clog/0A00: No such file or directory Dec 27 07:34:45 ex37 postgres[5745]: [28-3] STATEMENT: ANALYZE public.driverworklog This is running on an almost new install of Debian Sarge. Peter Childs ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Trigger
On Thu, 23 Oct 2003, Tom Lane wrote: Peter Childs [EMAIL PROTECTED] writes: Great idea shame drivers to get at these are rare. Anyway you still need a trigger to fire the notify and these get sent when the query is done not when its commented. hmmm But the NOTIFY isn't delivered until and unless the transaction commits. This gets around the AFTER-trigger-can-still-roll-back problem. regards, tom lane Notify is also not very flexable it tells you somthing has triggerged it not the information that a trigger is supplied with, like what has changed to what from what. Peter Childs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Recomended FS
On Tue, 21 Oct 2003, Ben-Nes Michael wrote: what about mirroring only ? raid 1 ? I always thought that raid 1 is the fastest, am I true ? I don't really need more then 3GB data and I have two 36GB HD. so I don't need lvl 0 nor lvl 5 unless raid 1 is slower. Raid 1 should not be slower than raid 5. hence Raid 0 Write = Deciede which disk, Write Read = Deciede Which disk, Read Raid 1 Write = Write Disk 1, Write Dist 2 Read = Read (Don't matter which one) Raid 5 Write = Write Disk 1, Write Disk 2, Calc Check Sum, Write Disk 3 Read = Read Disk 1, Read Disk 2, Regenate Data. Peter Childs -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://www.canaan.net.il -- - Original Message - From: Markus Wollny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, October 21, 2003 11:00 AM Subject: Re: [GENERAL] Recomended FS Theory vs. real life. In Theory, RAID5 is faster because less data have to be written to disk. But it's true, many RAID5 controllers don't have enough CPU power. I think it might not be just CPU-power of the controller. For RAID0+1 you just have two disc-I/O per write-access: writing to the original set and the mirror-set. For RAID5 you have three additional disc-I/O-processes: 1. Read the original data block, 2. read the parity block (and calculate the new parity block, which is not a disk I/O), 3. write the updated data block and 4. write the updated parity block. Thus recommendations by IBM for DB/2 and several Oracle-consultants state that RAID5 is the best compromise for storage vs. transaction speed, but if your main concern is the latter, you're always best of with RAID0+1; RAID0+1 does indeed always and reproducably have better write performance that RAID0+1 and read-performance is almost always also slightly better. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] XOR logical operator
On Fri, 17 Oct 2003, Nagib Abi Fadel wrote: Is there a XOR logical operator in Postgresql, or a function for XOR ?? I only found in the docs a Binary XOR (#). I need to do the following checkup: (field1 is NULL XOR field2 is NULL XOR filed3 is NULL) Good idea. hmmm CREATE FUNCTION xor(bool,bool) RETURNS bool AS ' SELECT ($1 AND NOT $2) OR (NOT $1 AND $2); ' LANGUAGE 'sql'; CREATE OPERATOR ~| (PROCEDURE='xor',LEFTARG=bool,RIGHTARG=bool); May not be all that neat but it does work so long as you use brackets... I think improvements could be made. (10 minute job if that) Peter Childs i can't right it like this: ( (field1 is NUll or field2 is NUll) and (field1 is NUll or field3 is NUll) and (field2 is NUll or field3 is NUll) ) But if i have alot of fields : field1,field2,...,field5 ... this will take a hell of a time I can write a function F1 that does the following: if a field is NULL it will return 1 else it will return 0 then i can do: (F1(field1) # F1(field2) # F1(field3) ...) but i just wanted to see if XOR already exists ... __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] A conditional DROP TABLE function
On Thu, 25 Sep 2003, David Link wrote:
Hi All,
Here's a Conditional drop_table func for those interested. There was a
thread on this a long time back.
We do this all the time :
DELETE TABLE sales;
CREATE TABLE sales (...);
But nobody likes
ERROR: table sales does not exist
Fine why not
BEGIN;
DELETE TABLE sales;
CREATE TABLE sales (...);
COMMIT;
This is not the same as create or replace is mysql as it will
delete all the data!
This is also the same as
DELETE FROM sales;
The advantage of this is you keep the indexes.
Peter Childs
which we see all the time in the logs. I want to show the logs to none
db folk -- so we can't have those error messages in it.
(There must be some explaination why postgresql (and Oracle as well) do
not have CREATE OR REPLACE TABLE as it does for VIEWs, and FUNCTIONs.
Anybody know?)
Anyway here's drop_table ():
CREATE or REPLACE function drop_table (varchar) returns varchar as '
DECLARE
tablename alias for $1;
cntint4;
BEGIN
SELECT into cnt count(*) from pg_class where relname =
tablename::name;
if cnt 0 then
execute \'DROP TABLE \' || tablename;
return tablename || \' DROPPED\';
end if;
return tablename || \' does not exist\';
END;'
language 'plpgsql' ;
And here's it's usage in an SQL script:
\set QUIET
\pset format unaligned
\pset tuples_only
\unset QUIET
select drop_table('sale');
CREATE TABLE sale ( ... );
Regards, DAvid
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Optimizer picks an ineffient plan
On Thu, 4 Sep 2003, Tom Lane wrote: Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Yes, that's the real crux of the matter. Should the optimizer spend cycles on *every* query to detect cases where the user has written useless sort keys? I've got grave doubts that it's a win. Well I'm sure the same arguments were made 30 years ago about optimizing compilers. But thankfully the optimizer-geeks won the argument. Um ... I *am* an optimizer-geek. You can find my name in the credits for Bliss/11, which was the best optimizing compiler on the planet about thirty years ago. I stand by my comment that there's a tradeoff between the potential gain from an optimization and the time spent to find it. PG is at a disadvantage compared to typical compilation scenarios, in that a compiler assumes its output will be executed many times, while SQL queries often are planned and then executed but once. There's been some talk of working harder when planning a prepared statement, but so far I've not seen very many places where I'd really want to alter the planner's behavior on that basis. An intresting point. Perhaps storing some stats on Views would help. Maybe adding a cache facility for views would speed some things up. I don't really see anything against storing stats on Prepared Statements and Views like we do on Tables. Maybe indexs on View would be useful but keeping them uptodate would be a hazard. Peter Childs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] different transaction handling between postgresql and
On Mon, 14 Jul 2003, Jörg Schulz wrote: Suppose the following: create table test (a int primary key); insert into test values (1); select * from test; a = 1 In Postgresql if you do the following in a transaction (either with autocommit=off or with an explizit begin): insert into test values (2); - ok insert into test values (1); - error (duplicate key) insert into test values (3); - error (transaction aborted) commit; You get: select * from test; a = 1 In Oracle/MySQL if you do the same you get: insert into test values (2); - ok insert into test values (1); - error (duplicate key) insert into test values (3); - ok commit; select * from test; a = 1 2 3 Which behavior is right? The first I believe Transactions have to be committed in there entirety or not at all. MySql does not do transactions on its standard tables anyway you have to switch them on at table create time (early versions could not cope with them at all!) I have this feeling the reason Oracle gives this result may be again because transactions have been switched off. If you want the second result in Postgres just switch auto-commit on! Peter Childs Is there a way to make Postgresql behave like the other databases? Which other Databases act like Postgresql and which do it like Oracle/MySQL? Jörg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] different transaction handling between postgresql and
On Mon, 14 Jul 2003, Mike Mascari wrote: Jörg Schulz wrote: ... I have this feeling the reason Oracle gives this result may be again because transactions have been switched off! This snippet comes from the Oracle console: (table name is a not test / messages are in german) ... SQL select * from a; A -- 1 3 4 2 Presumably Oracle is not rolling back a duplicate key violation, allowing the transaction to continue. This is an often requested feature not present in PostgreSQL. Bug. Not Feature Transactions must be all or nothing. If one step fails for what ever reason all steps must be failed and rolled back. While in this simple case ignoring the statment may look fine in more complex examples (where the is more data in the table...) this can mean data loss and massive problems! Peter Childs Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is SQL silly as an RDBMS-app interface?
On Mon, 14 Jul 2003, Alvaro Herrera wrote: On Mon, Jul 14, 2003 at 12:54:55AM -0500, Ron Johnson wrote: SQL is only one possible relational query language. It didn't become de facto standard until the mid- to late-80s. It is an outgrowth of SEQEL (Structured English QuEry Language), which was IBM's 1st try at a descriptive query language. DEC had RDML (Relational Data Manipulation Language) to access it's RDBMS. I'm sure that Burroughs, etc, had their own access methods, too. Of course, in the context of a PostgreSQL list you can't forget QUEL and PostQUEL, Ingres and POSTGRES query languages respectively. SQL is almost the worst standard I've come across. Its the computer equivalent of VHS. Its not readable by computer or humans. (Enough Flaming on to why I think this) SQL is verbose it often make you repeat your self when its obvious what you mean. INSERT INTO a (b,c) SELECT a+4 as b, c*6 as c from a; SQL has many different ways of writing the same thing for different purposes. eg INSERT INTO a (b,c) VALUES (1,2); UPDATE a set b=1, c=2 WHERE d=3; Why not INSERT INTO a set b=1, c=3; its certainly more readable and consistent. Parsing is hard work No Meta Standard (How do you find out the structure of your table using pure SQL? Very difficult to operate with Trees and simple hierarchal data. I could continue. Still its a language we all love to hate. Peter Childs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Question regarding performance (large objects involved)
On Thu, 26 Jun 2003, u15074 wrote: I have a small test program (using libpq) inserting a lot of data into the database. Each command inserts a small large object (about 5k) into the database and inserts one row into a table, that references the large object oid. I repeat this 100.000 times. Each insert consists of his own transaction (begin - insert large object, insert row - commit ...). I also measure the time taken for always 100 inserts. The performance is ok and stays constant over the whole time. But I have the following effect: from time to time a short interruption occurs (my test program is standing still for a moment) and then it goes on. Has anyone an idea what might cause these pauses? Is it due to caching mechanisms of the database? Another question is concerning the reading of the written data. When I finished the test, I used psql to check the written data. Therefore I started some queries, searching for certain large objects in pg_largeobject (... where loid = XX). These queries took very much time (about 5 seconds or more). After calling vacuum on the database, the queries got fast. Can anyone explain this? Is the index on pg_largeobject built by calling vacuum? Never used large objects but I guess its like everything else. Vacuum changes nothing (except rubbish its a garbage collection routine). Analyse however looks at the table and decides which indexes are worth using in which querys. There are times like if there are only 5 records in a table where reading the entire table is quicker that reading the index and then reading the right bit of the table. If the statisics created by analyse are out of date a analyse will be required. When to run what. Analyse Needs to be run when the data structure has changed. That means that the data distribution has changed. Also need to be run when the indexes change. If your table is constatnly changing but the structure does not really change. (ie status data or logs) a daily analyse may be advisable in case the structure is changing very slowly Vacuum Need to be run after deletes and updates (a delete is actually a delete and an insert) if you do it with verbose on. Look at the vac number if it is big you need to do it more often if its zero you can probably get away with less often. Should be done on a table by table bases. If possible durring a quite period. (if no quite periods are available do it more often as it will be quicker. Vaccum does not need to be done on static tables. however a vacuum will not harm it and may help after the initial insert. Vacuum Full The file system equivlent is defrag. Needs to be done if Unused (from vacuum) has grown too big. It also means that you need to vacuum more often. Vacuum Full will lock your table while it works so will stop any clients using the table. If you find your self vacuum fulling the whole database you may be better off dumping the database and rebuilding. This is the information I have gathered from reading this and the other postgres newsgroups over the last few months. I would surgest that somthing like this was added to the manual. If I'm wrong (which I probably am) I'm sure somone will correct me. Peter Childs ---(end of broadcast)--- TIP 8: explain analyze is your friend
