Re: [GENERAL] "Ghost" colmumn with primary key
[EMAIL PROTECTED] a écrit : Hello, I have a table 'customers', with 2 records: SELECT * FROM customers; customerID | customerName --+- myFriend | myFriend's Name test | testing user (2 rows) but when I'm asking about customerID column, I get the answer: SELECT customerID FROM customers; ERROR: column "customerid" does not exist What happens? I'm using PostgreSQL 8.1.3 Hello, You have to use double-quotes since your column contains some uppercase characters. Try this : SELECT "customerID" FROM customers; Hope this helps, -- Bruno BAGUETTE - [EMAIL PROTECTED] "Nous n'avons pas à garantir la sécurité des produits alimentaires génétiquement modifiés (OGM). Notre intérêt est d'en vendre le plus possible." Propos de Monsanto, in le Monde Diplomatique, Décembre 98. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] update impossible, constraint prevents it but it shouldn't
I apologize if I'm running an old version (7.2.1) maybe this has been fixed or maybe it's not a bug but I can't look at the changelogs now. UPDATE has a non-standard FROM clause where you can do joins etc so columns from other tables can appear in WHERE. I run the following query: update personal set closed_date = flt_date + 20 from enr inner join personal p on enr.id = p.id_enr where (...columns from enr, columns from personal...) this runs for a while then fails because of a constraint but if I rewrite the above as a select with the exact same from and where clauses looking for any offending rows there are none. The constraint says flt_date <= closed_date so it's clear the update cannot violate it. - Has anyone encountered anything like this? I can fix it creating functions that take the primary key and return the value I want from the related table I'm referencing (it's 1:1) so I can take the inner join out of the from, I know this works but I'd like to avoid so many function calls. Thanks! Lucia ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] RAID + PostgreSQL?
Hello, we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. When we do a big SELECT-query the whole maschine becomes very very very slowly or stands. The maschine has 3 GB RAM, so we suppose it`s the RAID. Has anyone some experience with RAID + PostgreSQL? Where does PostgreSQL saves his temporary files? Perhaps these are the reason for the bad performance. Greetings Michaela
[GENERAL] unique attributes in profile management system
Hi all, we are developing a system for profile management. The simplified schema is composed by three tables: * tbl_user : the users table; contains the unique id of the users and the profile id (only one profile for each user), and some other information * tbl_data_type : contains the data type of the profile, their id and their names. E.g.: id=1, data type name="last name"; id=2, data type name="address", and so on * tbl_data : the data of all the profiles of the system; it has three columns: the id of the profile the data belongs to (linked to the tbl_user), the data type id (linked to tbl_data_type) and the value of the data. E.g.: profile=1, data_type_1=1, value="Smith", and so on The problem involves the management of the values of the profile that must be unique. Suppose we have a data type named "unique_id", which value should be stored in tbl_data. The value must be unique in the whole system, so the profiles store only one "unique_id", and the value of this parameter must belong only to this profile. Generating such a unique id it's not a problem, using e.g. a sequence. The problem is the user can change this value accessing to the proper stored procedure, and the system should check that the value chosen do not violate the requirement of uniqueness. I have only two solutions, I'd be glad to hear from you if they are correct, or if you have already encountered similar problems and you can point me to some useful document. The first solution: Using access exclusive lock inside of the stored procedure mentioned before. Since stored procedures make a local copy of the data, each stored procedure accessing to the tbl_data for updating the unique_id would have its own copy of data; so, a different locking strategy should not have the desired effect. However, I think that locks should be avoided if possible. Furthermore, the unique_id should have a slow update rate, so it should not be a big problem, but the exclusive lock would affect the whole system, even the research (SELECT) on tbl_data. The second solution: Using a support table to take advantage of the UNIQUE constraint. I'd have a fourth table, named "tbl_unique_id_support", storing the the unique_id(s) with the unique constraint. With this, if a new unique_id is proposed, I should try to add it to the support table; if the operation fails, the id already exists, so it cannot be added to the tbl_data table. Otherwise, I can safely add it to the tbl_data. Pro: get rid of lock. Con: more memory is required for support table. The system is less flexible, because for each "unique_id"-kind of data, I should have a support table dedicated. Please, do you have any suggestion about that? Thank you regards, Francesco -- Francesco Formenti - TVBLOB S.r.l. Software Engineer Via G. Paisiello, 9 20131 Milano, Italia - Phone +39 02 36562440 Fax +39 02 20408347 Web Site http://www.tvblob.com E-mail [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] "Ghost" colmumn with primary key
Hello, I have a table 'customers', with 2 records: SELECT * FROM customers; customerID | customerName --+- myFriend | myFriend's Name test | testing user (2 rows) but when I'm asking about customerID column, I get the answer: SELECT customerID FROM customers; ERROR: column "customerid" does not exist What happens? I'm using PostgreSQL 8.1.3 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Return the primary key of a newly inserted row?
John Tregea wrote: The example of select id1 = nextval(somesequence) could work for me. I have multiple users with our GUI and imagine I could use transaction protection to ensure no duplicates between selecting and incrementing the somesequence... You won't have duplicates[1], it's a sequence. It's its purpose. Now I may have missed something, I didn't follow this thread. [1] Unless you manage to make it wrap around after 2^32 (or 2^64?) calls of nextval. But that's quite unlikely. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] update impossible, constraint prevents it but it shouldn't
On Mon, Jun 26, 2006 at 01:00:19PM +0200, lrotger wrote: > I run the following query: > > update personal set closed_date = flt_date + 20 > from enr inner join personal p on enr.id = p.id_enr > where (...columns from enr, columns from personal...) I think your problem is that "personal" and "personal p" refer to different instances of the same table. Use EXPLAIN to check how many times "personal" appears in the resulting query. Oh yeah, 7.2 will eat your data eventually, you have been warned. Please upgrade to something newer. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Return the primary key of a newly inserted row?
On Mon, Jun 26, 2006 at 11:31:32AM +0200, Alban Hertroys wrote: > [1] Unless you manage to make it wrap around after 2^32 (or 2^64?) calls > of nextval. But that's quite unlikely. Even then, only if have wrapping enabled. With wrapping disabled, nextval() will simply fail rather than return a value already returned. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] unique attributes in profile management system
"Francesco Formenti - TVBLOB S.r.l." <[EMAIL PROTECTED]> writes: > we are developing a system for profile management. The simplified schema > is composed by three tables: > * tbl_user : the users table; contains the unique id of the users > and the profile id (only one profile for each user), and some > other information > * tbl_data_type : contains the data type of the profile, their id > and their names. E.g.: id=1, data type name="last name"; id=2, > data type name="address", and so on > * tbl_data : the data of all the profiles of the system; it has > three columns: the id of the profile the data belongs to (linked > to the tbl_user), the data type id (linked to tbl_data_type) and > the value of the data. E.g.: profile=1, data_type_1=1, > value="Smith", and so on I think you need to refactor your schema. You want to have one table that is clearly the "defining" table for profiles, and then put a unique constraint on that table's ID column, and probably foreign key constraints on other tables that mention profile IDs. It's not real clear to me why you're bothering with a separation between tbl_data_type and tbl_data, either ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] RAID + PostgreSQL?
MG wrote: Hello, we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. When we do a big SELECT-query the whole maschine becomes very very very slowly or stands. The maschine has 3 GB RAM, so we suppose it`s the RAID. Has anyone some experience with RAID + PostgreSQL? Where does PostgreSQL saves his temporary files? Perhaps these are the reason for the bad performance. Greetings Michaela What kind of RAID? I know if you have it set up to mirror it becomes slow as pond water. I have a server that was a hand me down so I did not have a choice in the RAID and it was set up to mirror with two drives and the performance on large selects was very bad. The performance would increase by 50 or more percent if fsync = offis set in the postgresql.conf file. Do a search of the mailing list archives, there is a lot of talk about RAID and battery backed write caches etc. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] unique attributes in profile management system
Tom Lane wrote: "Francesco Formenti - TVBLOB S.r.l." <[EMAIL PROTECTED]> writes: we are developing a system for profile management. The simplified schema is composed by three tables: * tbl_user : the users table; contains the unique id of the users and the profile id (only one profile for each user), and some other information * tbl_data_type : contains the data type of the profile, their id and their names. E.g.: id=1, data type name="last name"; id=2, data type name="address", and so on * tbl_data : the data of all the profiles of the system; it has three columns: the id of the profile the data belongs to (linked to the tbl_user), the data type id (linked to tbl_data_type) and the value of the data. E.g.: profile=1, data_type_1=1, value="Smith", and so on I think you need to refactor your schema. You want to have one table that is clearly the "defining" table for profiles, and then put a unique constraint on that table's ID column, and probably foreign key constraints on other tables that mention profile IDs. It's not real clear to me why you're bothering with a separation between tbl_data_type and tbl_data, either ... regards, tom lane Hi Tom, thank you for your response. Unfortunately, I'm not sure to understand properly your advice. In the complete schema, we already have constraints on columns, but those constraints do not help us solving the problem. In fact, the (interesting part of the) complete schema is something like: CREATE TABLE public.tbl_user ( guid integer PRIMARY KEY, username varchar(25) UNIQUE NOT NULL, password varchar(25), status integer NOT NULL REFERENCES tbl_user_status (status_id) ON DELETE RESTRICT, timestamp_create timestamptz DEFAULT now(), timestamp_update timestamptz DEFAULT now() ) WITHOUT OIDS; -- *** tbl_profile *** CREATE TABLE public.tbl_profile ( profile_id int4 PRIMARY KEY, guid int4 UNIQUE NOT NULL REFERENCES tbl_user (guid) ON DELETE RESTRICT, timestamp_create timestamp with time zone NOT NULL DEFAULT now() ) WITHOUT OIDS; -- *** tbl_data_type *** CREATE TABLE public.tbl_data_type ( data_type_id int4 PRIMARY KEY, xml_name varchar(50) UNIQUE NOT NULL, ord int4 NOT NULL DEFAULT 0 ) WITHOUT OIDS; -- *** tbl_data *** CREATE TABLE public.tbl_data ( data_id serial PRIMARY KEY, profile_id int4 NOT NULL REFERENCES tbl_profile ON DELETE RESTRICT, data_type_id int4 NOT NULL REFERENCES tbl_data_type ON DELETE RESTRICT, value varchar(300), timestamp_create timestamp with time zone NOT NULL DEFAULT now(), timestamp_update timestamp with time zone DEFAULT now() ) WITHOUT OIDS; I can put any kind of value in tbl_data (names, birthdates, addresses, ...). The problem arises when the data must be unique, referring to its data_type: how can I atomically check that the SELECT * FROM put_new_data_into_tbl_data(my_profile_id, data_type_id_that_needs_uniqueness, my_new_value_that_must_be_unique) is really putting a unique value for the specified data_type_id? About all of the defined data_type_id refer to non-unique values; however, I have a certain data_type_id (e.g., having an id of "10") that wants all its related values being unique; so, if I select all value(s) from tbl_data where data_type_id=10, all those values should satisfy the unique condition (all those values should be different). The problem is I don't know how to atomically check this condition, when I make an insertion like this one in the tbl_data. We separated tbl_data from tbl_data_type because we can easily check if the data_type is allowed or not. We use something like a 2xN matrix to insert new data into the profiles, where the first row of the matrix contains the names of the data_types, where the second row stores the values associated. This helps us to keep the profiles flexible, containing (if needed) only a part of the whole data_type allowed. Thank you Regards, Francesco -- Francesco Formenti - TVBLOB S.r.l. Software Engineer Via G. Paisiello, 9 20131 Milano, Italia - Phone +39 02 36562440 Fax +39 02 20408347 Web Site http://www.tvblob.com E-mail [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] update impossible, constraint prevents it but it shouldn't
Martijn van Oosterhout wrote: On Mon, Jun 26, 2006 at 01:00:19PM +0200, lrotger wrote: I run the following query: update personal set closed_date = flt_date + 20 from enr inner join personal p on enr.id = p.id_enr where (...columns from enr, columns from personal...) I think your problem is that "personal" and "personal p" refer to different instances of the same table. Use EXPLAIN to check how many times "personal" appears in the resulting query. it worked, I thought I had to specify the table being updated in the from clause too if only to have something to join to the other table, as it turns out, it's not necessary. I hope the manual for 8.1.x explains this or at least gives some examples of using this nonstandard from clause. Oh yeah, 7.2 will eat your data eventually, you have been warned. Please upgrade to something newer. I'll have to allocate some time to read changelogs before I upgrade across major versions. thanks a lot, Lucia ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] RAID + PostgreSQL?
On Monday 26 June 2006 00:36, MG wrote: > Hello, > > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. I'm sorry. > > When we do a big SELECT-query the whole maschine becomes very very very > slowly or stands. The maschine has 3 GB RAM, so we suppose it`s the RAID. Or your database structure, a badly written query, lack of statistics, too many dead rows, lack of vacuuming, bad postgresql.conf settings, or using an aggregate without a where clause over millions of rows. > > Has anyone some experience with RAID + PostgreSQL? Lots...What type of raid? How many drives? > > Where does PostgreSQL saves his temporary files? Perhaps these are the > reason for the bad performance. I would suggest looking at the above half a dozen possibilities as well. Sincerely, Joshua D. Drake > > Greetings > > Michaela ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] initlocation on 8.0+
Hi All, I am using initlocation utility to have two separate PGDATAs, that can then host different databases. In my case I only have one on each and I am using version 7.3.2. But I am not able to find this utility on 8.1.0, is there an alternative utility to this or this feature is no more supported. Regards, Nitin Verma ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Return the primary key of a newly inserted row?
> You won't have duplicates[1], it's a sequence. It's its purpose. > > Now I may have missed something, I didn't follow this thread. Yes, what you quoted was more the intro. The actual question was how to find out what ids were generated during a sequence of insertions. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] initlocation on 8.0+
On Mon, 2006-06-26 at 10:31, Nitin Verma wrote: > Hi All, > > I am using initlocation utility to have two separate PGDATAs, that can then > host different databases. In my case I only have one on each and I am using > version 7.3.2. > > But I am not able to find this utility on 8.1.0, is there an alternative > utility to this or this feature is no more supported. Locations was a useful little hack to use as an interim until table spaces got implemented. Look for table spaces in the docs, that's what you want now. They're much less error prone, in general, than were locations. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Return the primary key of a newly inserted row?
Scott Ribe wrote: You won't have duplicates[1], it's a sequence. It's its purpose. Now I may have missed something, I didn't follow this thread. Yes, what you quoted was more the intro. The actual question was how to find out what ids were generated during a sequence of insertions. That's where you use currval ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] initlocation on 8.0+
On Mon, Jun 26, 2006 at 08:31:22AM -0700, Nitin Verma wrote: > I am using initlocation utility to have two separate PGDATAs, that can then > host different databases. In my case I only have one on each and I am using > version 7.3.2. > > But I am not able to find this utility on 8.1.0, is there an alternative > utility to this or this feature is no more supported. In 8.0 and later you can use tablespaces to put files in different locations. http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] initlocation on 8.0+
Thanx Scott and Michael -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Monday, June 26, 2006 9:18 PM To: Nitin Verma Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] initlocation on 8.0+ On Mon, Jun 26, 2006 at 08:31:22AM -0700, Nitin Verma wrote: > I am using initlocation utility to have two separate PGDATAs, that can then > host different databases. In my case I only have one on each and I am using > version 7.3.2. > > But I am not able to find this utility on 8.1.0, is there an alternative > utility to this or this feature is no more supported. In 8.0 and later you can use tablespaces to put files in different locations. http://www.postgresql.org/docs/8.1/interactive/manage-ag-tablespaces.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RAID + PostgreSQL?
On Mon, 2006-06-26 at 08:59, Tony Caduto wrote: > MG wrote: > > Hello, > > > > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. > > > > When we do a big SELECT-query the whole maschine becomes very very > > very slowly or stands. > > The maschine has 3 GB RAM, so we suppose it`s the RAID. > > > > Has anyone some experience with RAID + PostgreSQL? > > > > Where does PostgreSQL saves his temporary files? Perhaps these are the > > reason for the bad performance. > > > > Greetings > > > > Michaela > What kind of RAID? I know if you have it set up to mirror it becomes > slow as pond water. I have to say this has NOT been my experience. With a pair of U320 drives on an LSI-Megaraid with battery backed cache (256M or 512M, not sure which it was, it's been a few years) our pg server was noticeable faster with a mirror set than with a single IDE drive (with cache disabled) on the same machine. And faster than a single SCSI drive with no RAID controller as well. > I have a server that was a hand me down so I did not have a choice in > the RAID and it was set up to mirror with two drives and > the performance on large selects was very bad. Wow, something's wrong then. normally, selects are much faster on mirror sets rather than on a single drive. Do you have a lot of sorts spilling onto disc? > The performance would > increase by 50 or more percent if > fsync = offis set in the postgresql.conf file. Of selects? Or performance in general? I can see if for performance in general, but selects really shouldn't be greatly affected by fsync. In another vein, I agree with Joshua. There could be LOTS of causes of poor performance. I wouldn't just assume it's RAID until it's been proven to be the cause of the problem. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] limit over attribute size if index over it exists
Hi everybody, I have got an issue with PostgreSQL. There is a limitation on the column length of a tuple, in case there is an index over it. In the actual project I am working on, I meet such a situation. I have got an attribute over which I am doing a search (that is, I need an index over it), but this attribute can be in some cases very large (100KB+). The log message I get from Postgres, if I try to insert a tuple with such a big attribute (e.g. 10K) is the following: ERROR: index row requires 15704 bytes, maximum size is 8191 (PostgreSQL 8.07 under Linux. The index is a btree index.) I have thought of a possible workaround. I would like to know if it seems reasonable. The idea would be to build a hash, on the client side, over the problematic column (let's say column a). I then store in the db the attribute a (without index) and the hash(a) (with an index). Then when I am doing a select, I use firstly a sub-select to choose all tuples with the right hash (quick, with index), and then an outer select to choose the tuple with the right attribute a (slow, sequential scan, but normally few tuples, because few collisions). Something like that: SELECT b FROM ( SELECT a, b FROM foo WHERE hash='' ) as bar WHERE bar.a='' (Actually, in my case the situation is slightly more complicated because I don't have just one attribute but 2+, so there are some index types that I cannot use. Anyway the principle is the same). Does this solution seem reasonable, or is there other (more elegant) ways to do that? Thank you in advance. Cheers, Pat ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] inheritance and table
hi i don't have understand how works inheritance of tables... if table B inherits from table A - A and B must share primary keys? - if i isert record in B the record is replaced in A ? can i avoid this? i would like to have this scenario: table A table B inheridts from A table C inheridts from A if i insert a record in B it must be insered only in B! if i insert a record in C it must be insered only in C! is it possible? thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] auto-vacuum & Negative "anl" Values
So can I assume that this is a bug?The only resolution I can see right now is to setup a cron job that will perform an ANALYZE periodically, as the pg_autovacuum ANALYZE threshold is never reached.Any other suggestions? Thanks for the input! --Dylan HansenEnterprise Systems DeveloperOn 24-Jun-06, at 4:09 PM, Matthew T. O'Connor wrote:Tom Lane wrote: Dylan Hansen <[EMAIL PROTECTED]> writes: I have been spending some time looking into how auto-vacuum is performing on one of our servers. After putting the PostgreSQL logs in debug I noticed that the threshold for ANALYZE was never being hit for a particular table because the calculated value becomes increasingly negative. Hmm, it shouldn't ever be negative at all, I would think. Thecalculation in question is anltuples = tabentry->n_live_tuples + tabentry->n_dead_tuples - tabentry->last_anl_tuples;Apparently somehow last_anl_tuples has managed to get to be bigger thann_live_tuples, which maybe could happen after a delete. Should we beclamping last_anl_tuples to not exceed n_live_tuples somewhere?Alvaro and Matthew, what do you think? I think I had something in the contrib version that checked this. I always assumed it would be caused by a stats reset which was more common in earlier PGSQL releases since stats_reset_on_startup (or whatever the correct spelling of that is) was enabled by default. ---(end of broadcast)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RAID + PostgreSQL?
On Mon, 2006-26-06 at 11:08 -0500, Scott Marlowe wrote: > On Mon, 2006-06-26 at 08:59, Tony Caduto wrote: > > MG wrote: > > > Hello, > > > > > > we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. > > > > > > When we do a big SELECT-query the whole maschine becomes very very > > > very slowly or stands. > > > The maschine has 3 GB RAM, so we suppose it`s the RAID. > > > > > > Has anyone some experience with RAID + PostgreSQL? > > > > > > Where does PostgreSQL saves his temporary files? Perhaps these are the > > > reason for the bad performance. > > > > > > Greetings > > > > > > Michaela > > What kind of RAID? I know if you have it set up to mirror it becomes > > slow as pond water. > > I have to say this has NOT been my experience. With a pair of U320 > drives on an LSI-Megaraid with battery backed cache (256M or 512M, not > sure which it was, it's been a few years) our pg server was noticeable > faster with a mirror set than with a single IDE drive (with cache > disabled) on the same machine. And faster than a single SCSI drive with > no RAID controller as well. > > > > I have a server that was a hand me down so I did not have a choice in > > the RAID and it was set up to mirror with two drives and > > the performance on large selects was very bad. > > Wow, something's wrong then. normally, selects are much faster on > mirror sets rather than on a single drive. Do you have a lot of sorts > spilling onto disc? > > > The performance would > > increase by 50 or more percent if > > fsync = offis set in the postgresql.conf file. > > Of selects? Or performance in general? I can see if for performance in > general, but selects really shouldn't be greatly affected by fsync. > > In another vein, I agree with Joshua. There could be LOTS of causes of > poor performance. I wouldn't just assume it's RAID until it's been > proven to be the cause of the problem. > Confusion Abounds. Mirroring does not improve performance. Mirroring writes the same data to two sets of identically sized partitions/slices, and reads the data from both and verifies the integrity of the returned data. A good RAID controller will add some latency on first read, but with caching can improve the speed of some drive operations. Large selects may very well produce more data than the cache on the controller can hold so the speed will be limited by the sustained throughput of the drive and controller with a number of other factors that can cause lower levels of performance. Some performance improvements can be made using RAID methods that distribute the data over multiple drives like striping, but even with striping large selects can still be larger than the cache, but the data can usually be accessed more quickly than with a single drive, or straight mirroring. The main advantage mirroring has is that it can provide redundancy in the event of premature drive failure, as is usually the least expensive data redundancy solution. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] RAID + PostgreSQL?
Scott Marlowe wrote: On Mon, 2006-06-26 at 08:59, Tony Caduto wrote: MG wrote: Hello, we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6. When we do a big SELECT-query the whole maschine becomes very very very slowly or stands. The maschine has 3 GB RAM, so we suppose it`s the RAID. Has anyone some experience with RAID + PostgreSQL? Where does PostgreSQL saves his temporary files? Perhaps these are the reason for the bad performance. Greetings Michaela What kind of RAID? I know if you have it set up to mirror it becomes slow as pond water. I have to say this has NOT been my experience. With a pair of U320 drives on an LSI-Megaraid with battery backed cache (256M or 512M, not sure which it was, it's been a few years) our pg server was noticeable faster with a mirror set than with a single IDE drive (with cache disabled) on the same machine. And faster than a single SCSI drive with no RAID controller as well. Hi Scott, We are just using the compaq hardware SCSI //raid included with the server and it does not have a battery backed cache. Actually my test DB running the same queries on a cheap IDE drive (on a Athlon 2200+ where 50% faster than the compaq running the mirrored SCSI drives. The compaq system was also a Dual 2.4 gzh Xeon with 2.5 GB of memory and the Athlon had 512mb. Some of the performance on the Athlon could be attributed to the CPU, but mostly the IDE drive just blew away the mirrored SCSI drive On the Compaq system setting Fsync to false increased the speed of all select statements, not just complex ones with sorting. I didn't set up the hardware, so I don't know if the system admin guy screwed anything up in the raid setup. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] limit over attribute size if index over it exists
On Mon, Jun 26, 2006 at 02:52:56AM -0700, pajai wrote: > I have thought of a possible workaround. I would like to know if it > seems reasonable. The idea would be to build a hash, on the client > side, over the problematic column (let's say column a). I then store in > the db the attribute a (without index) and the hash(a) (with an index). > Then when I am doing a select, I use firstly a sub-select to choose all > tuples with the right hash (quick, with index), and then an outer > select to choose the tuple with the right attribute a (slow, sequential > scan, but normally few tuples, because few collisions). Something like > that: Perhaps you should look into functional indexes. Indexes over a function. CREATE INDEX foo_index ON foo( hash(a) ); This index will automatically be used if you make a query like this: ... WHERE hash(a) = 'blah'; Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] list or regular expressions
Hi all,
can i search in a list or regular expressioneg
"select yadi from ya where yadiya in ('old', 'ulk', 'orb')"
but instead of in ther'd be another operator or a LIKE IN.
so it'd be a shorcut for typing yadiya ~* 'old' or yadiya ~* 'ulk' etc.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] list or regular expressions
"Rhys Stewart" <[EMAIL PROTECTED]> writes:
> can i search in a list or regular expressioneg
> "select yadi from ya where yadiya in ('old', 'ulk', 'orb')"
> but instead of in ther'd be another operator or a LIKE IN.
You could use " ANY" --- "IN" is just a shorthand for "= ANY".
I don't think the parser will take LIKE ANY, but you could use the
equivalent operator name ("~~" I think, check the manual).
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] limit over attribute size if index over it exists
"pajai" <[EMAIL PROTECTED]> writes: > I have got an issue with PostgreSQL. There is a limitation on the > column length of a tuple, in case there is an index over it. In the > actual project I am working on, I meet such a situation. I have got an > attribute over which I am doing a search (that is, I need an index over > it), but this attribute can be in some cases very large (100KB+). It sounds to me a lot like you may be in need of full-text-index code --- see contrib/tsearch2. If you were not trying to roll-your-own text searcher, please give more details. I can hardly imagine a situation in which it is useful to make a btree index on 100KB values. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] inheritance and table
> i don't have understand how works inheritance of tables... > if table B inherits from table A > - A and B must share primary keys? No, currently there is no unique constraint that will force uniqueness across parent/child/sibling tables. Just think of them as being nothing more than seperate table that share simlar data-definitions. When you select * from parent; you are essentially preforming a: select * from parent union select * from childa union select * from childb ; if you want to only see the records in A then select * from only parent; > - if i isert record in B the record is replaced in A ? > > can i avoid this? This will not happen, you will end up with two records one A and one in B. > i would like to have this scenario: > > table A > > table B inheridts from A > table C inheridts from A > > if i insert a record in B it must be insered only in B! > if i insert a record in C it must be insered only in C! This is how is it will work. http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html Notice 5.8.1. Caveats Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] RAID + PostgreSQL?
On Mon, 2006-06-26 at 12:10, Guy Fraser wrote: > On Mon, 2006-26-06 at 11:08 -0500, Scott Marlowe wrote: > > On Mon, 2006-06-26 at 08:59, Tony Caduto wro > > > > I have to say this has NOT been my experience. With a pair of U320 > > drives on an LSI-Megaraid with battery backed cache (256M or 512M, not > > sure which it was, it's been a few years) our pg server was noticeable > > faster with a mirror set than with a single IDE drive (with cache > > disabled) on the same machine. And faster than a single SCSI drive with > > no RAID controller as well. > > > > > > Confusion Abounds. > > Mirroring does not improve performance. Mirroring writes the same data > to two sets of identically sized partitions/slices, and reads the data > from both and verifies the integrity of the returned data. I know of no RAID controllers that do that with mirroring during reads. The linux kernel RAID module also does not do this. While all writes go to all drives, reads are normally interleaved between drives. You can even use > 2 drives in a mirror set for improved read performance of things like a data warehouse, where the loads are in bulk during off hours and then many many users read different sections of the same data sets at the same time. If you've an example of a hardware or software RAID implementation that reads from both drives and compares them to be sure the data has integrity, I'd be interested in seeing them, as there are some uses for that kind of setup, although you'd really need three disks, at least, to assure a good read, since a failure in a two drive mirror would result in a state where you knew the data was corrupted, but without check digits no way of knowing which drive is failing. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] list or regular expressions
Rhys Stewart wrote:
Hi all,
can i search in a list or regular expressioneg
"select yadi from ya where yadiya in ('old', 'ulk', 'orb')"
but instead of in ther'd be another operator or a LIKE IN.
so it'd be a shorcut for typing yadiya ~* 'old' or yadiya ~* 'ulk' etc.
---(end of broadcast)---
TIP 6: explain analyze is your friend
you mean, like
yadiya ~ 'old|ulk'
(or parametrically)
yadiya ~ ?::text ||'|'|| ?::text ||'|'|| ... :-)
--
Engineers think that equations approximate reality.
Physicists think that reality approximates the equations.
Mathematicians never make the connection.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Return the primary key of a newly inserted row?
Hi all, Thanks for the continued suggestions on this question. I will reply again once it is implemented and working. Kind regards John Alban Hertroys wrote: Scott Ribe wrote: You won't have duplicates[1], it's a sequence. It's its purpose. Now I may have missed something, I didn't follow this thread. Yes, what you quoted was more the intro. The actual question was how to find out what ids were generated during a sequence of insertions. That's where you use currval ;) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] RAID + PostgreSQL?
Compaq RAID controllers are known to be slow under linux.Alex.On 6/26/06, Tony Caduto <[EMAIL PROTECTED] > wrote:Scott Marlowe wrote:> On Mon, 2006-06-26 at 08:59, Tony Caduto wrote: >>> MG wrote:> Hello,>> we are using PostgreSQL 8.0.3 together with RAID on OpenServer 6.>> When we do a big SELECT-query the whole maschine becomes very very >>> very slowly or stands.>>> The maschine has 3 GB RAM, so we suppose it`s the RAID.>> Has anyone some experience with RAID + PostgreSQL?>> Where does PostgreSQL saves his temporary files? Perhaps these are the >>> reason for the bad performance.>> Greetings>> Michaela> What kind of RAID? I know if you have it set up to mirror it becomes >> slow as pond water. I have to say this has NOT been my experience. With a pair of U320> drives on an LSI-Megaraid with battery backed cache (256M or 512M, not> sure which it was, it's been a few years) our pg server was noticeable > faster with a mirror set than with a single IDE drive (with cache> disabled) on the same machine. And faster than a single SCSI drive with> no RAID controller as well.>>Hi Scott, We are just using the compaq hardware SCSI //raid included with theserver and it does not have abattery backed cache. Actually my test DB running the same queries on acheap IDE drive (on a Athlon 2200+ where 50% faster than the compaq running the mirroredSCSI drives. The compaq system was also a Dual 2.4 gzh Xeon with 2.5 GBof memory and the Athlon had 512mb. Some of the performance on theAthlon could be attributed to the CPU, but mostly the IDE drive just blew away the mirrored SCSI driveOn the Compaq system setting Fsync to false increased the speed of allselect statements, not just complex ones with sorting.I didn't set up the hardware, so I don't know if the system admin guy screwed anything up in the raid setup.Later,--Tony CadutoAM Software Designhttp://www.amsoftwaredesign.comHome of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration---(end of broadcast)---TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] inheritance and table
nik600 wrote: hi i don't have understand how works inheritance of tables... if table B inherits from table A - A and B must share primary keys? - if i isert record in B the record is replaced in A ? can i avoid this? i would like to have this scenario: table A table B inheridts from A table C inheridts from A if i insert a record in B it must be insered only in B! if i insert a record in C it must be insered only in C! is it possible? thanks Do you mean like this? (Notice the use of LIKE instead of INHERITS): CREATE TABLE table_1 ( a int, b int ) CREATE TABLE table_2 ( LIKE table_1 ) (**Note: CREATE TABLE with INHERITS uses different syntax!**) INSERT INTO table_1 (a, b) VALUES (1, 2); INSERT INTO table_2 (a, b,) VALUES (3, 4); Now, SELECT * FROM table_1; yeilds, _a | b_ 1 | 2 and not, _a | b _1 | 2 3 | 4 as it would've if you'd used INHERITS instead of LIKE. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] inheritance and table
Inheritance in postgre means you will have same fields definition like the inherited table plus its own fields. So if table B is inherit table A, table B will have same field definition like A plus table B own unique field(s). It wont share primary keys, table B just have primary key in the same field(s) like A and records in table A wont be replaced by record(s) inserted to table B or vice versa. table A and table B basically a different entity, they just have same fields definition. Hope that will help you hi i don't have understand how works inheritance of tables... if table B inherits from table A - A and B must share primary keys? - if i isert record in B the record is replaced in A ? can i avoid this? i would like to have this scenario: table A table B inheridts from A table C inheridts from A if i insert a record in B it must be insered only in B! if i insert a record in C it must be insered only in C! is it possible? thanks ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] auto-vacuum & Negative "anl" Values
Dylan Hansen wrote:
> So can I assume that this is a bug?
Definitively a bug.
> The only resolution I can see right now is to setup a cron job that
> will perform an ANALYZE periodically, as the pg_autovacuum ANALYZE
> threshold is never reached.
>
> Any other suggestions? Thanks for the input!
I just committed a fix, so the other alternative is get a CVS checkout
from the 8.1 branch and put it up to see if it fixes your problem. The
relevant patch is below.
Index: src/backend/postmaster/pgstat.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/postmaster/pgstat.c,v
retrieving revision 1.111.2.3
diff -c -p -r1.111.2.3 pgstat.c
*** src/backend/postmaster/pgstat.c 19 May 2006 15:15:38 -
1.111.2.3
--- src/backend/postmaster/pgstat.c 27 Jun 2006 03:36:03 -
*** pgstat_recv_vacuum(PgStat_MsgVacuum *msg
*** 2919,2924
--- 2919,2930
tabentry->n_dead_tuples = 0;
if (msg->m_analyze)
tabentry->last_anl_tuples = msg->m_tuples;
+ else
+ {
+ /* last_anl_tuples must never exceed n_live_tuples */
+ tabentry->last_anl_tuplse = Min(tabentry->last_anl_tuples,
+
msg->m_tuples);
+ }
}
/* --
*** pgstat_recv_tabstat(PgStat_MsgTabstat *m
*** 3055,3061
tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;
! tabentry->n_live_tuples += tabmsg[i].t_tuples_inserted;
tabentry->n_dead_tuples += tabmsg[i].t_tuples_updated +
tabmsg[i].t_tuples_deleted;
--- 3061,3068
tabentry->tuples_updated += tabmsg[i].t_tuples_updated;
tabentry->tuples_deleted += tabmsg[i].t_tuples_deleted;
! tabentry->n_live_tuples += tabmsg[i].t_tuples_inserted -
! tabmsg[i].t_tuples_deleted;
tabentry->n_dead_tuples += tabmsg[i].t_tuples_updated +
tabmsg[i].t_tuples_deleted;
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] FKs Lock Contention
Hello, I need some help to understand better the way PostgreSQL works internally: Oracle 8.1.7 used to have a severe lock contention when FKs had no index (causing an sx table lock). AFAIK this was "fixed" on 9i with the addition of "shared row locking". Reading the docs I found that PostgreSQL team implemented "shared row locking" on 8.1 (my personal thanks and admiration to those who did it), so we now can expect much less contention. With this new scenario, I wonder which FKs should really get an index and which not (especially for composed FKs)? How the order of my PKs and FKs would influence that? I know this is not a simple question, but hope that someone could show me the light. :-) Best Regards, Bruno Almeida do Lago ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] auto-vacuum & Negative "anl" Values
Alvaro Herrera <[EMAIL PROTECTED]> writes: > + /* last_anl_tuples must never exceed n_live_tuples */ If we actually believe the above statement, it seems like your patch to pgstat_recv_tabstat() opens a new issue: with that patch, it is possible for pgstat_recv_tabstat() to decrease n_live_tuples, and therefore a clamp needs to be applied in pgstat_recv_tabstat() too. No? The reason I didn't patch it myself is that I'm not quite clear on what *should* be happening here. What effect should a large delete have on the ANALYZE threshold, exactly? You could argue that a deletion potentially changes the statistics (by omission), and therefore inserts, updates, and deletes should equally count +1 towards the analyze threshold. I don't think we are implementing that though. If we want to do it that way, I suspect last_anl_tuples as currently defined is not the right comparison point. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Preformace boost -- by 8.0.4 upgrade to 8.1.4
Hello list, I am happy to report that I am seeing a 150% average increase in select performance since I upgraded to 8.1.4. Version PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9) Not to mention, the upgrade was a snap! I am happy most of all for the documentation of the 8.1.4 manual. The total upgrade time, including building from source was about an hour. I want to express my appreciation for all of the hard work, ingenuity, and support offered by the PostgreSQL Developers and Community. Regards, Richard Broersma Jr. ---(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
