Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
Alright, my benchmarks might have been a bit naïve. When it comes to hardware, my webserver is a SunFire X2100 with an Opteron 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04 LTS. When it comes to the resource usage section of my postgresql.conf, the only thing that are not commented out are: shared_buffers = 24MB max_fsm_pages = 153600 I freely admit that the reason I haven't messed with these values is that I have next to no clue what the different things do and how they affect performance, so perhaps an apology is in order. As Scott wrote, Without a realistic test scenario and with no connection pooling and with no performance tuning, I don't think you should make any decisions right now about which is faster. My apologies. -- Kind regards, Mikkel Høgh [EMAIL PROTECTED] On 13/10/2008, at 06.54, Stephen Frost wrote: * Mikkel Høgh ([EMAIL PROTECTED]) wrote: I have been testing it a bit performance-wise, and the numbers are worrying. In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm unsure whether this is indicative for PostgreSQL performance in general or perhaps a misconfiguration on my part. The comments left on your blog would probably be a good first step, if you're not doing them already.. Connection pooling could definitely help if you're not already doing it. Drupal's MySQL-isms don't help things either, of course. Also, you don't post anything about the PostgreSQL config, nor the hardware it's running on. The default PostgreSQL config usually isn't appropriate for decent hardware and that could be a contributing factor here. It would also be useful to make sure you've analyze'd your tables and didn't just do a fresh load w/o any statistics having been gathered. We run Drupal on PostgreSQL for an internal site and it works reasonably well. We havn't had any performance problems but it's not a terribly large site either. The issues we've had tend to come from PostgreSQL's somewhat less-than-supported status with Drupal. I've been meaning to look into Drupal's PG support to see about improving it. Perhaps this winter I'll get a chance to. Thanks, Stephen smime.p7s Description: S/MIME cryptographic signature
[GENERAL] Out of memory in create index
After dropping an index to do some full-table updating, I'm running into an out of memory issue recreating one of my indices. This is on 8.3 running on linux. The table in question has about 300m rows. The index is on a single integer column. There are approximately 4000 unique values among the rows. create index val_datestamp_idx on vals(datestamp) tablespace space2; About 30 seconds into the query, I get: ERROR: out of memory DETAIL: Failed on request of size 536870912. Increasing maintenance_work_mem from 1GB to 2GB changed nothing at all- exact same error at exact same time. Watching memory on the machine shows the out of memory error happens when the machine is only at about 35% user. create index concurrently shows an identical error. Two other indexes (multicolumn) on the same table have already been successfully recreated, so this puzzles me. Actually, while I was writing this, I added an additional column to the index and it now appears to be completing (memory has reached about the point it had been failing at and is now holding steady, and the query has been going for significantly longer than the 30 seconds or so it took to error out previously). I sort by both columns at times, so the extra column may in fact turn out to be useful, but the failure of the single column create index in the face of the other successful creates has me confused. Can anyone shed some light on the situation? -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Sun, 12 Oct 2008 22:14:53 -0700 Uwe C. Schroeder [EMAIL PROTECTED] wrote: I have been testing it a bit performance-wise, and the numbers are worrying. In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm unsure whether this is indicative for PostgreSQL performance in general or perhaps a misconfiguration on my part. In my experience the numbers are always worrying in a read-only environment. I've used MySQL, but found it rather disturbing when it comes to integrity. MySQL has just some things I can't live with (i.e. silently ignoring overflowing charater types etc). That aside, MySQL IS fast when it comes to read operations. That's probably because it omits a lot of integrity checks postgres and other standard compliant databases do. I'm replying here but I could be replying to Scott and others... I use nearly exclusively Postgresql. I do it mainly because it makes me feel more comfortable as a programmer. I'm not the kind of guy that is satisfied if things work now. I prefer to have something that gives me higher chances they will work even when I turn my shoulders and Postgresql give me the feeling it is easier to achieve that result. Anyway I don't find myself comfortable with replies in these 2 lines of reasoning: 1) default configuration of PostgreSQL generally doesn't perform well 2) PostgreSQL may be slower but mySQL may trash your data. I think these answers don't make a good service to PostgreSQL. 1) still leave the problem there and doesn't give any good reason why Postgresql comes with a doggy default configuration on most hardware. It still doesn't explain why I've to work more tuning PostgreSQL to achieve similar performances of other DB when other DB don't require tuning. I know that a Skoda Fabia requires much less tuning than a Ferrari F1... but well a Ferrari F1 will run faster than a Skoda with or without tuning. Making performance comparable without expert tuning will a) stop most too easy critics about PostgreSQL performances b) give developers much more feedback on PostgreSQL performance in nearer to optimal setup. 1000 developers try PostgreSQL, 500 find it slow compared to other DBs, 50 comes back to the list asking, 30 were looking for a magic receipt that solved their problem, didn't find it and gave up, 10 at least could hear they had to tune the DB but couldn't get convinced to actually do so because it looked too expensive to them to learn. If it is easy to write a tool that will help you to tune PostgreSQL, it seems it would be something that will really help PostgreSQL diffusion and improvements. If it is *complicated* to tune PostgreSQL so that it's performance can be *comparable* (I didn't write optimal) with other DB we have a problem. Developer time is valuable... if it is complicated to tune PostgreSQL to at least have comparable performances to other DB PostgreSQL look less as a good investment. Then other people added in the equation connection pooling as a MUST to compare MySQL and PostgreSQL performances. This makes the investment to have PostgreSQL in place of mySQL even higher for many, or at least it is going to puzzle most. Or maybe... it is false that PostgreSQL doesn't have comparable performance to other DB with default configuration and repeating over and over the same answer that you've to tune PostgreSQL to get comparable performance doesn't play a good service to PostgreSQL. 2) I never saw a trashing data benchmark comparing reliability of PostgreSQL to MySQL. If what I need is a fast DB I'd chose mySQL... I think this could still not be the best decision to take based on *real situation*. Do we really have to trade integrity for speed? Is it a matter of developers time or technical constraints? Is MyISAM really much faster in read only operations? Is Drupal a read only applications? Does it scale better with PostgreSQL or MySQL? These are answers that are hard to answer even because it is hard to have valuable feedback. What I get with that kind of answer is: an admission: - PostgreSQL is slow and a hard to prove claim: - MySQL will trash your data. Unless you circumstantiate I'd say both things are false. From my point of view the decision was easy. I needed transactions. Functions would have made dealing with transactions much easier. PostgreSQL had a much more mature transaction and function engine. I like to sleep at night. But is PostgreSQL competitive as a DB engine for apps like Drupal for the average user? Judging on real experience with Drupal on PostgreSQL I'd say maybe. Judging on the replies I often read I'd say NO. Unfortunately replies aren't turning that maybe into a NO for any reasonable reasons. If there are reasonable reasons to turn that maybe into a NO... there may be some work to be done on the PostgreSQL code. If there aren't reasonable reasons to turn that maybe into a NO... please stop to give that kind of answers. or both... -- Ivan Sergio Borgonovo
Re: [GENERAL] problem with check constraints
Anton Andreev wrote: When I create a check constraint in PgAdmin3 1.8.4 on a Postgresql 8.3.3: ((A and B) or (C and D)) I get with create script: (A and B or C and D) which is wrong. No, it isn't - both are the same. AND has higher operator precedence than OR, see http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-PRECEDENCE-TABLE Yours, Laurenz Albe -- 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
Robert Treat wrote: 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:) DELETE FROM t t1 USING t t2 WHERE t1.id = t2.id AND t1.temp_id = t2.temp_id AND t1.ctid t2.ctid; note that one problem the delete from approaches have that the temp table solutions dont is that you can end up with a lot of dead tuples if there were a lot of duplicates... so if you can afford the locks, its not a bad idea to do begin; lock table t1 in access exclsuive mode; create temp table x as select ... from t1; truncate t1; insert into t1 select * from x; create unique index ui1 on t1(...); commit; this way you're now unique table will be nice and compacted, and wont get any more duplicate rows. Very true; an alternative way to achieve that is to VACUUM FULL t after deleting the duplicate rows. As for the UNIQUE index, that's of course the right thing to do, but I wasn't sure if Yi Zhao wanted to change the database design. At any rate, I had thought that a unique constraint was preferrable to a unique index because - while doing the same thing - the former will also show up in pg_catalog.pg_constraint. Yours, Laurenz Albe -- 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] DB and Unicode problem (was: user and DB confusion)
On Mon, Oct 13, 2008 at 5:11 PM, Tino Wildenhain [EMAIL PROTECTED] wrote: Well exactly that. What information is missing? There is a parameter on createdb command line (As well as CREATEDB command in SQL) which needs to be set to unicode. yes my friend :) -E utf8 thanks Hint: to get collation right, you might want to initdb first (with empty $PGDATA) with correct locale settings (including charset UTF-8 which will then be the default encoding for all subsequent created databases. default is ISO-8859. I can't make it to UTF8 because then my VLC stops working :\ . Quite very unusual problem. -- http://uttre.wordpress.com/2008/05/14/the-lost-love-of-mine/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; I'd say returning multiple recordset is useful to save connections and transferred data. You can't get the same with a left join (users fields will be repeated over and over) and you can't get the same with 2 separated statements since they will need 2 connections. But from the client side, suppose it PHP... if the first statement return no record and the second one return 3 records, how can I know? Well, (in MySQL at least) in that case you're still going to get a result set, it's just going to be an empty one (result with no rows). So, no matter how many rows the SELECT statements resolve to, you're always going to get two result sets :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] what are those 3 passwords for ?
arnuld uttre [EMAIL PROTECTED] writes: I see postgres has 3 password mechanism: 'createuser -d -P arnuld' -- which will ask for the password according to P flag used here. 'createuser -d -P -W arnuld' -- which will ask for the 2 passwords belonging to P and W flags. It does not matter whether both passwords are same or different. because it always creates the user successfuly. -P is about the password you intend to give to the new user. -W is about the password of the user doing the creating. I don't recommend using -W, because it will ask you for a password whether or not the database is actually going to check that password. This is more likely to be confusing than helpful. It would appear that you are using an authentication method that doesn't require a password, which is why it doesn't matter what you put in for -W. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] user and DB confusion
I created a new user and then a new DB with that user, where the user is the owner: -bash-3.00$ createuser -d -P Enter name of user to add: arnuld Enter password for new user: Enter it again: Shall the new user be allowed to create more new users? (y/n) n CREATE USER [EMAIL PROTECTED] ~]$ createdb -O arnuld -U arnuld arnuldforum CREATE DATABASE when I pass this username arnuld and the password for arnuld to phpBB3 (for installation) it says could not connect to database while I can connect to database without any problem. I don't understand why phpbb says there is something wrong with DB settings [EMAIL PROTECTED] ~]$ psql arnuldforum Welcome to psql 7.4.17, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit arnuldforum= -- http://uttre.wordpress.com/2008/05/14/the-lost-love-of-mine/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Slony1-general] Re: [GENERAL] Stripping out slony after / before / during pg_restore?
I'm setting us up a separate staging / test server and I want to read in a pg_dump of our current origin stripping out all the slony stuff. I was thinking this could serve two purposes a) test out backups restore properly and b) provide us with us with the staging / test server What's the best way to remove all the slony bits? Well, you can always just drop the slony schema (with a cascade) - that should do it. Not quite. There are two things that *doesn't* hit: So what was the final recommended process for building a stand alone database from a pg_dump of a replicated node? pg_dump --oids --format=c --file=master.dump master_db createdb staging_db pg_restore -d staging_db master.dump slonik EOM cluster name = sl; node 1 admin conninfo = 'dbname=staging_db user=slony'; uninstall node (id = 1); EOM This process dies on the last step with: stdin:3: PGRES_FATAL_ERROR select _sl.uninstallNode(); - ERROR: Slony-I: alterTableRestore(): Table with id 1 not found CONTEXT: SQL statement SELECT _sl.alterTableRestore( $1 ) PL/pgSQL function uninstallnode line 14 at PERFORM Failed to exec uninstallNode() for node 1 So if I'm reading this thread correctly, the alternative is 'DROP _sl CASCADE;', which doesn't do a full cleanup. Is there no supported disaster recovery procedure? -- Stuart Bishop [EMAIL PROTECTED] http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] what are those 3 passwords for ?
I see postgres has 3 password mechanism: 'createuser -d -P arnuld' -- which will ask for the password according to P flag used here. 'createuser -d -P -W arnuld' -- which will ask for the 2 passwords belonging to P and W flags. It does not matter whether both passwords are same or different. because it always creates the user successfuly. createdb --owner arnuld -W arnuldforum -- one password asked because of W flag. Now I want to ask , when you pass the username and password to some forum software like phpbb3, which password you will pass when you can pass only one ? -- http://uttre.wordpress.com/2008/05/14/the-lost-love-of-mine/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what are those 3 passwords for ?
In response to arnuld uttre [EMAIL PROTECTED]: I see postgres has 3 password mechanism: 'createuser -d -P arnuld' -- which will ask for the password according to P flag used here. 'createuser -d -P -W arnuld' -- which will ask for the 2 passwords belonging to P and W flags. In order to create a user, you have to connect as an existing user. When you use -W, you tell the createuser command to _always_ prompt for the (existing) password before allowing you to connect, even if your configuration does not require password authentication. So, even with the second command, you're still only using 1 password when you create the account. I feel that the man page for createuser explains this pretty well. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Hello 2008/10/13 Ivan Sergio Borgonovo [EMAIL PROTECTED]: On Mon, 13 Oct 2008 15:19:33 +0300 Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: Well, (in MySQL at least) in that case you're still going to get a result set, it's just going to be an empty one (result with no rows). So, no matter how many rows the SELECT statements resolve to, you're always going to get two result sets :) It seems anyway that the usefulness of this feature largely depends on the language library. eg. I can't see a way to support it with php right now but it is supported by python. Am I missing something? Out of curiosity, what language are you using? I know so multirecordsets are well supported for php and MySQL, and in all Microsoft environments - Microsoft SQL Server use it very hard. These functionality has lot of advantage, mainly in stateless environment like plpgsql. regards Pavel Stehule -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Chart of Accounts
Gregory Stark wrote: justin [EMAIL PROTECTED] writes: special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. FWIW I think this is wrong. You need to use precisely the number of decimal places that each datum needs. If you use extra it's just as wrong as if you use too few. For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you get charged $8.00 not $7.996. If you fail to round at that point you'll find that your totals don't agree with the amount of money in your actual bank account. I agree to a point. just went through this with our application and had total fits with compound rounding errors as one table stored 4 other stored 6 and 8 and the general ledger table stored 2. when it came time to balance the transactions to the General Ledger Entries we where off thousands of dollars in different accounts as the GL almost always was higher due to rounding and it was wrong to the detail side. The entire database uses the same precession as a whole then rounded on the display side.In our Case we make parts that consume .000113 lbs of a metal that sales for 25.76 a pound = 0.002911. When the transaction to remove the value from the inventory account in the Generial ledger table has an entry 0.00 not 0.002911. We just had to big discussion on this thread about rounding and precession which i kicked off.
[GENERAL] Re: multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Hi Ivan, It seems anyway that the usefulness of this feature largely depends on the language library. eg. I can't see a way to support it with php right now but it is supported by python. Am I missing something? Yes, the client library will have to support multiple result sets too. For example, the PHP MySQLi lib does that by providing functions to check for and retrieve outstanding result sets in the buffer: bool mysqli_more_results ($link) bool mysqli_next_result ($link) It seems like the PHP PG binding does allow (?) retrieval of multiple result sets through pg_get_result(), but only for requests issued asynchronously: http://bg2.php.net/manual/en/function.pg-get-result.php Out of curiosity, what language are you using? For MySQL I've been mostly using PHP, occasionally Java, Python and C. Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Oct 13, 2008, at 4:08 AM, admin wrote: I am also evaluating Drupal + PostgreSQL at the moment. We are building a local government website/intranet that doesn't need to be lightning fast or handle millions of hits a day, but it does need to be rock solid and potentially needs to manage complex business processes. So PostgreSQL seems a good choice. However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? Can anyone recommend an alternative CMS with the features and flexibility of Drupal that supports PostgreSQL 100%? What about the Python world, what is Plone like with PostgreSQL support? I have been running TYPO3 on PostgreSQL. It's not easy but very very doable. As soon as the main CMS system is MySQL based, then you always hit problems with any other DB. Ries I don't really want to kick off another round of Python vs PHP, just looking for a CMS that is a good match for PostgreSQL. Mick -- 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] DB and Unicode problem (was: user and DB confusion)
arnuld uttre wrote: ... and I phpBB can connect to the DB now but with a new problem from phoBB3:The database you have selected was not created in UNICODE or UTF8 encoding. Try installing with a database in UNICODE or UTF8 encoding. what to do about it ? Well exactly that. What information is missing? There is a parameter on createdb command line (As well as CREATEDB command in SQL) which needs to be set to unicode. Hint: to get collation right, you might want to initdb first (with empty $PGDATA) with correct locale settings (including charset UTF-8 which will then be the default encoding for all subsequent created databases. HTH Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
2008/10/13 Vladimir Dzhuvinov [EMAIL PROTECTED]: So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? it's true. Thank you for the definite answer, Pavel :) I came across a blog post of yours ( http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html ) as well as several list posts indicating that multiple result sets might be in the working. Should I check the situation again when 8.4 is released? I have only very raw prototype, so I am sure, so this feature will not be in 8.4, and I am not sure about 8.5. It's nice feature, but I am not force to complete and clean code, and I am not able create patch. If you would do it, I am, with pleasure, send you source code, that allows multirecord sets. You can use setof cursors instead. Cursors, unfortunately, look cumbersome in this situation and will break the existing API (all transactions encapsulated within SPs, clients allowed to do CALL only). Anyway, thanks everyone for the cursors tip :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
multi recordset and data type check was: Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On Mon, 13 Oct 2008 12:17:21 +0300 Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; I'd say returning multiple recordset is useful to save connections and transferred data. You can't get the same with a left join (users fields will be repeated over and over) and you can't get the same with 2 separated statements since they will need 2 connections. But from the client side, suppose it PHP... if the first statement return no record and the second one return 3 records, how can I know? What about functions like pg_num_fields? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
On Mon, Oct 13, 2008 at 12:00 AM, Mikkel Høgh [EMAIL PROTECTED] wrote: Alright, my benchmarks might have been a bit naïve. When it comes to hardware, my webserver is a SunFire X2100 with an Opteron 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04 LTS. When it comes to the resource usage section of my postgresql.conf, the only thing that are not commented out are: shared_buffers = 24MB max_fsm_pages = 153600 Well, 24MB is pretty small. See if you can increase your system's shared memory and postgresql's shared_buffers to somewhere around 256M to 512M. It likely won't make a big difference in this scenario, but overall it will definitely help. I freely admit that the reason I haven't messed with these values is that I have next to no clue what the different things do and how they affect performance, so perhaps an apology is in order. As Scott wrote, Without a realistic test scenario and with no connection pooling and with no performance tuning, I don't think you should make any decisions right now about which is faster. My apologies. No need for apologies. You're looking for the best database for drupal, and you're asking questions and trying to test to see which one is best. You just need to look deeper is all. I would, however, posit that you're putting the cart before the horse by looking at performance first, instead of reliability. On a machine with properly functioning hardware, postgresql is nearly indestructable. MySQL has a lot of instances in time where, if you pull the plug / lose power it will scramble your db / lose part or all of your data. Databases are supposed to be durable. InnoDB, the table handler, is pretty good, but it's surrounded by a DB that was designed for speed not reliability. There was a time when Microsoft was trying to cast IIS as faster than Apache, so they released a benchmark showing IIS being twice as fast as apache at delivering static pages. Let's say it was 10mS for apache and 2mS for IIS. Seems really fast. Problem is, static pages are cheap to deliver. I can buy a $500 server to serve the static content and if I need more speed, I can throw more servers at the problem for $500, no OS license fees. But for dynamic content, the difference was the other way around, and the delivery times were much higher for IIS, like 50mS for apache and 250mS for IIS. Suddenly, a handful of dynamic pages and the IIS server was noticeably slower. The same type of comparison tends to hold true for MySQL versus PostgreSQL. MySQL tends to be very very fast at select * from table where id=5 while PostgreSQL is much faster at 4 page long reporting queries with 5 levels of subselects and a couple of unions. Things that make MySQL run so slow as to be useless. Also, PostgreSQL tends to keep better read performance as the number of writes increase. This is the real test, so the point I was making before about realistic tests is very important. It's about graceful degradation. PostgreSQL has it, and when your site is getting 20 times the traffic you ever tested for, it's a little late to figure out you might have picked the wrong DBMS. Note I'm not saying MySQL is the wrong choice, I'm saying you don't know because you haven't proven it capable. -- 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] [PERFORM] Drupal and PostgreSQL - performance issues?
On Mon, Oct 13, 2008 at 8:19 AM, Scott Marlowe [EMAIL PROTECTED] wrote: There was a time when Microsoft was trying to cast IIS as faster than Apache, so they released a benchmark showing IIS being twice as fast as apache at delivering static pages. Let's say it was 10mS for apache and 2mS for IIS. Dyslexia strikes again! That was supposed to be 5mS... anywho. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On Mon, 13 Oct 2008 16:48:33 +0300 Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: It seems like the PHP PG binding does allow (?) retrieval of multiple result sets through pg_get_result(), but only for requests issued asynchronously: http://bg2.php.net/manual/en/function.pg-get-result.php Interesting. Out of curiosity, what language are you using? For MySQL I've been mostly using PHP, occasionally Java, Python and C. pardon the noise I forgot to check mysql*i* functions. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Oct 13, 2008, at 5:08 AM, admin wrote: However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? My hope is that Drupal is moving in the right direction. With version 6 they completely abstracted the schema building API. Previously, MySQL and PostgreSQL had to be specified separately which is the main reason a lot of modules did not work with PostgreSQL. Things should improve as modules are upgraded to Drupal 6. John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
John DeSoi wrote: On Oct 13, 2008, at 5:08 AM, admin wrote: However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? My hope is that Drupal is moving in the right direction. With version 6 they completely abstracted the schema building API. Previously, MySQL and PostgreSQL had to be specified separately which is the main reason a lot of modules did not work with PostgreSQL. Things should improve as modules are upgraded to Drupal 6. I have been working with the Drupal team on version 7. They are moving in a much better direction. They do some things oddly due to their MySQL heritage but for the most part it is coming along very well. Feel free to help :) Joshua D. Drake -- 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] Drupal and PostgreSQL - performance issues?
I am curious as to the ability to update individual configurations so lets say i want to change Apache maxRequests MaxKeepAliveRequests 200 or possibly update the PHP module? LoadModule php4_module $APACHE_HOME/modules/php4apache2.dll OR staying on topic lets say i want to update postgres max connections at postgresql.conf from 100 to 200? max_connections = 200 How does one achieve configuration for the DB or Apache updates without affecting the other components Has drupal solved the problem of apache prefork mpm? thanks/ Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. CC: pgsql-general@postgresql.org From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [GENERAL] Drupal and PostgreSQL - performance issues? Date: Mon, 13 Oct 2008 10:45:17 -0400 On Oct 13, 2008, at 5:08 AM, admin wrote: However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? My hope is that Drupal is moving in the right direction. With version 6 they completely abstracted the schema building API. Previously, MySQL and PostgreSQL had to be specified separately which is the main reason a lot of modules did not work with PostgreSQL. Things should improve as modules are upgraded to Drupal 6. John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general _ See how Windows connects the people, information, and fun that are part of your life. http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/
[GENERAL] DB and Unicode problem (was: user and DB confusion)
2008/10/13 arnuld uttre [EMAIL PROTECTED]: On Mon, Oct 13, 2008 at 3:15 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: what does your pg_hba.conf says ? ..SNIP... local allall ident sameuser okay, I have changed that line to:local allall trust and I phpBB can connect to the DB now but with a new problem from phoBB3:The database you have selected was not created in UNICODE or UTF8 encoding. Try installing with a database in UNICODE or UTF8 encoding. what to do about it ? -- http://uttre.wordpress.com/2008/05/14/the-lost-love-of-mine/ -- 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] Improve dump and restore time
On Fri, 10 Oct 2008, Pascal Cohen wrote: Are there best practices to reduce the migration time ? There's a number of resources in this area listed at http://wiki.postgresql.org/wiki/Bulk_Loading_and_Restores I wanted to have an expectation of the migration duration and performed it on a less powerful machine than the one we have in Production. Note that if your production server has any sort of decent disk controller in it, but the test machine doesn't, you can end up with results that don't extrapolate very well. That's particularly true if you don't follow the standard good practice on the restore (like using the default value for checkpoint_segments). As mentioned in the above, using syncronous commit can help a lot there on some systems. If you do any restore tests again, try and look at what the bottleneck is on the system using something like vmstat, and make sure you check the database log files (that will tell you if the checkpoint stuff is setup reasonably or not). It's really hard to say whether any of the things you were asking about will be helpful or not without knowing what the limiting factor on your system is. If you're CPU limited for example, you'd want to stay away from compression; if I/O limited that might make sense. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] user and DB confusion
2008/10/13 arnuld uttre [EMAIL PROTECTED]: On Mon, Oct 13, 2008 at 3:15 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: what does your pg_hba.conf says ? # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD # IPv4-style local connections: #hostall all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: #hostall all ::1 :::::::trust # Using sockets credentials for improved security. Not available everywhere, # but works on Linux, *BSD (and probably some others) local allall ident sameuser Most likely phpbb is trying to connect via ipv4 which you don't have turned on. Just a guess though. -- 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] PQexecParams question
Grzegorz Jaśkiewicz [EMAIL PROTECTED] writes:
that would be a type mismatch, heh.
prepare select * from foo where a = any($1::int[])
then pass {1,2,3}
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL
training!
--
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] Out of memory in create index
On Mon, Oct 13, 2008 at 6:44 AM, Gregory Stark [EMAIL PROTECTED] wrote: How much memory the OS allows Postgres to allocate will depend on a lot of external factors. At a guess you had some other services or queries running at the same time the first time which reduced the available memory. I'm sorry- I was insufficiently clear. Postgres was the only service running, and there were no additional queries happening at the same time. (This database is on a dedicated machine; the only other things that run are some decision-support applications that were all off at the time.) In addition, the 35% memory usage number was for user-space processes in total, not for postgres specifically; the swap space was completely clear. maintenance_work_mem + work_mem is well under the total amount of RAM on the system, and certainly well under RAM + swap. I'll give a try to building that index with a lower maintenance_work_mem this evening when I can shut off the other processes again, though given the above it strikes me as unlikely to be the problem. Also, the thing that has me even more confused is the fact that it worked when I added an additional column to the index. -- - David T. Wilson [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
2008/10/13 Vladimir Dzhuvinov [EMAIL PROTECTED]: Hi, I've got a financial MySQL database where the application accesses data through a layer of stored procedures. For various reasons I'm currently investigating my options to migrate to another SQL RDBMS. Postgresql seems to offer a few nice advantages over MySQL (e.g. stricter data integrity through checks and constraints, etc.) and I got quite excited about it. However, after consulting the docs and running a few tests, it looks like Postgresql misses a crucial feature which my application depends upon - returning multiple SELECT result sets from functions/stored procedures. To illustrate, I've got a number of MySQL stored procedures that look approximately like this: CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? Hello, it's true. You can use setof cursors instead. http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html regards Pavel Stehule Vladimir Dzhuvinov -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C -- 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] Opteron vs. Xeon performance differences
On Fri, 10 Oct 2008, Bart Grantham wrote: The Opterons are 2220 SE's, the Xeons are 5450's I think (family 15, model 6). Xeon - 3056 MB in 2.00 seconds = 1527.85 MB/sec Opteron - 4944 MB in 2.00 seconds = 2472.50 MB/sec There's something wrong with that Xeon system. That number should be twice that and your Xeon smoking those Opterons by 25% or so on benchmarks. My Q6600 system at home has a slower bus and clock speed than your Xeon, but hits 3891MB/s on cached hdparm even with the slowest of the RAM I have here. Now that I got the first round right, can I make a double or nothing bet that your Xeon system is either a) not running your RAM in dual-channel mode or b) is getting throttled by power management? Should I cross post to pgsql-performance? Or are most of the people on that list here, too? That would have been a better place to start at, but don't bother switching now--there's a lot of overlap. Cross-posting to the lists here is bad, partly because then replies by people who only belong to one of the two end up bugging the list admins. One of these days I'm going to summarize the main lore on this topic into a Wiki article anyway, which will pull the good stuff out of here regardless of the originating list. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Chart of Accounts
justin [EMAIL PROTECTED] writes: special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. FWIW I think this is wrong. You need to use precisely the number of decimal places that each datum needs. If you use extra it's just as wrong as if you use too few. For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you get charged $8.00 not $7.996. If you fail to round at that point you'll find that your totals don't agree with the amount of money in your actual bank account. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- 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] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
I came across a blog post of yours ( http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html ) as well as several list posts indicating that multiple result sets might be in the working. Should I check the situation again when 8.4 is released? I have only very raw prototype, so I am sure, so this feature will not be in 8.4, and I am not sure about 8.5. It's nice feature, but I am not force to complete and clean code, and I am not able create patch. If you would do it, I am, with pleasure, send you source code, that allows multirecord sets. Yes, I'll be glad to examine your patch. At least to get an idea of what's involved in implementing multiple result sets. Please, send the code or a link to it directly to my email (so as not to spam the list ;) Greetings from Bulgaria, Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] user and DB confusion
what does your pg_hba.conf says ? you sure you want to use 7.4 on new installations ? that's like - years behind.
[GENERAL] Column level triggers
Hi, According to the documentation ( http://www.postgresql.org/docs/8.3/interactive/sql-createtrigger.html ), the feaure SQL allows triggers to fire on updates to specific columns (e.g., AFTER UPDATE OF col1, col2) is missing. After a bit of research, I found that this feature was in the TODO list ( http://wiki.postgresql.org/wiki/Todo#Triggers ), and that a patch was proposed on 2005/07. Is it going to be implemented soon ? It would greatly help, IMHO, for load, and simplify the write of plpgsql functions called by before triggers. Regards, and keep up the good work, that DBMS (mostly;) rocks ! Laurent -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [Slony1-general] Re: [GENERAL] Stripping out slony after / before / during pg_restore?
On Mon, Oct 13, 2008 at 5:05 PM, Stuart Bishop [EMAIL PROTECTED] wrote: So what was the final recommended process for building a stand alone database from a pg_dump of a replicated node? So if I'm reading this thread correctly, the alternative is 'DROP _sl CASCADE;', which doesn't do a full cleanup. Is there no supported disaster recovery procedure? So to (hopefully) answer my own question, the following seems to Do The Right Thing™: pg_dump --oids --format=c --file=master.dump master_db createdb staging_db pg_restore -d staging_db master.dump slonik EOM cluster name = sl; node 1 admin conninfo = 'dbname=staging_db user=slony'; repair config (set id = 1, event node = 1, execute only on = 1); repair config (set id = 2, event node = 1, execute only on = 1); uninstall node (id = 1); EOM Can anyone who actually knows what they are doing confirm or ridicule this recipe? -- Stuart Bishop [EMAIL PROTECTED] http://www.stuartbishop.net/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Hi, I've got a financial MySQL database where the application accesses data through a layer of stored procedures. For various reasons I'm currently investigating my options to migrate to another SQL RDBMS. Postgresql seems to offer a few nice advantages over MySQL (e.g. stricter data integrity through checks and constraints, etc.) and I got quite excited about it. However, after consulting the docs and running a few tests, it looks like Postgresql misses a crucial feature which my application depends upon - returning multiple SELECT result sets from functions/stored procedures. To illustrate, I've got a number of MySQL stored procedures that look approximately like this: CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? Vladimir Dzhuvinov -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? it's true. Thank you for the definite answer, Pavel :) I came across a blog post of yours ( http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html ) as well as several list posts indicating that multiple result sets might be in the working. Should I check the situation again when 8.4 is released? You can use setof cursors instead. Cursors, unfortunately, look cumbersome in this situation and will break the existing API (all transactions encapsulated within SPs, clients allowed to do CALL only). Anyway, thanks everyone for the cursors tip :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On Mon, Oct 13, 2008 at 8:09 AM, Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: I came across a blog post of yours ( http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html ) as well as several list posts indicating that multiple result sets might be in the working. Should I check the situation again when 8.4 is released? I have only very raw prototype, so I am sure, so this feature will not be in 8.4, and I am not sure about 8.5. It's nice feature, but I am not force to complete and clean code, and I am not able create patch. If you would do it, I am, with pleasure, send you source code, that allows multirecord sets. Yes, I'll be glad to examine your patch. At least to get an idea of what's involved in implementing multiple result sets. Stored procedure support is a pretty complicated feature. They differ with functions in two major areas: *) input/output syntax. this is what you are dealing with *) manual transaction management. stored procedures should allow you emit 'BEGIN/COMMIT' and do things like vacuum. IIRC, I don't think there was a consensus on the second point or if it was ok to implement the syntax issues without worrying about transactions. I'll give you two other strategies for dealing with multiple result sets in pl/pgsql: *) temp tables: it's very easy to create/dump/drop temp tables and use them in later transactions. previous to 8.3 though, doing it this way was a pain because of plan invalidation issues. *) arrays of composites (8.2+) create table foo(a int, b int, c int); create table bar(a text, b text, c text); pl/sql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ select (select array(select foo from foo)), (select array(select bar from bar)); $$ language sql; pl/pgsql: create function foobar(foos out foo[], bars out bar[]) returns record as $$ begin foos := array(select foo from foo); bars := array(select bar from bar); return; end; $$ language plpgsql; select foos[1].b from foobar(); Customize the above to taste. For example you may want to return the array dims. By the way, if you are writing client side code in C, you may want to look at libpqtypes (http://libpqtypes.esilo.com/)...it makes dealing with arrays and composites on the client sides much easier. For 8.3 though it requires a patched libpq. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] More schema design advice requested
I track employee qualifications in one table and I track job requirements in another table. A job requires zero-to-many qualifications, and for an employee to be qualified for that job, the employee must have ALL the requirements. For example, In my job requirements table, I record that a nurse must have a TB test and a nursing license like this: (nurse job ID, TB test ID) (nurse job ID, nursing license ID) Then I record employee qualifications for each employee like this: (Alice's ID, TB test ID) (Alice's ID, nursing license ID) (Bob's ID, TB test ID) Alice is qualified to work as a nurse. Bob is halfway there, but he still needs to get the nursing license. When I want to find all jobs that employee #2 is qualified for, I do something like this: select job_id, bool_and(is_qualified) from ( select job_requirement.job_id, requirement_id, requirement_id in ( select requirement_id from employee_qualification where employee_id = 2 ) as is_subscribed from job_requirement) as x group by job_id; This works, but man, it makes me dizzy. Any advice? I wonder if this is a sign of a bad design, or maybe if there's some nicer SQL techniques I could use. Thanks in advance! Matt -- 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] Drupal and PostgreSQL - performance issues?
I am also evaluating Drupal + PostgreSQL at the moment. We are building a local government website/intranet that doesn't need to be lightning fast or handle millions of hits a day, but it does need to be rock solid and potentially needs to manage complex business processes. So PostgreSQL seems a good choice. However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? Can anyone recommend an alternative CMS with the features and flexibility of Drupal that supports PostgreSQL 100%? What about the Python world, what is Plone like with PostgreSQL support? I don't really want to kick off another round of Python vs PHP, just looking for a CMS that is a good match for PostgreSQL. Mick -- 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] multi recordset and data type check was: Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On Mon, 13 Oct 2008 15:19:33 +0300 Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: Well, (in MySQL at least) in that case you're still going to get a result set, it's just going to be an empty one (result with no rows). So, no matter how many rows the SELECT statements resolve to, you're always going to get two result sets :) It seems anyway that the usefulness of this feature largely depends on the language library. eg. I can't see a way to support it with php right now but it is supported by python. Am I missing something? Out of curiosity, what language are you using? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] user and DB confusion
On Mon, Oct 13, 2008 at 3:15 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: what does your pg_hba.conf says ? # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD # IPv4-style local connections: #hostall all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: #hostall all ::1 :::::::trust # Using sockets credentials for improved security. Not available everywhere, # but works on Linux, *BSD (and probably some others) local allall ident sameuser you sure you want to use 7.4 on new installations ? that's like - years behind. Its my office machine, running CentOS 4.6, which itself is quite old. I can't help that as my boss decides what to be done on that machine, except that I can install new softwares. I want to run my own forum and thats why I am working on this stuff and somehow I like PostgreSQL more that MySQL. -- http://uttre.wordpress.com/2008/05/14/the-lost-love-of-mine/ -- 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] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
am Mon, dem 13.10.2008, um 12:17:21 +0300 mailte Vladimir Dzhuvinov folgendes: However, after consulting the docs and running a few tests, it looks like Postgresql misses a crucial feature which my application depends upon - returning multiple SELECT result sets from functions/stored procedures. So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? You can write so called SRF (Set Returning Function), read more about this here: http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS Simple example: test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$begin a:=1;b:=1;return next;a:=2;b:=3; return next; end;$$language plpgsql; CREATE FUNCTION test=*# select * from srf(); a | b ---+--- 1 | 1 2 | 3 (2 rows) or, simpler in plain sql: test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$language sql; CREATE FUNCTION test=*# test=*# test=*# select * from srf(); a | b ---+--- 1 | 3 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
am Mon, dem 13.10.2008, um 11:34:03 +0200 mailte A. Kretschmer folgendes: or, simpler in plain sql: test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$language sql; CREATE FUNCTION test=*# test=*# test=*# select * from srf(); a | b ---+--- 1 | 3 (1 row) Sorry, i have overlooked that this isn't the expected result and thanks to Pavel for the rectification. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Drupal and PostgreSQL - performance issues?
On Mon, Oct 13, 2008 at 11:57 AM, Mikkel Høgh [EMAIL PROTECTED] wrote: In any case, if anyone has any tips, input, etc. on how best to configure PostgreSQL for Drupal, or can find a way to poke holes in my analysis, I would love to hear your insights :) It'd be more accurate to configure Drupal for PostgreSQL. We use PostgreSQL for almost everything, including many drupal sites, but the usage pattern of Drupal puts PostgreSQL at a disadvantage. In short, Drupal issues a lot of small, simple SQL (100+ is the norm), that makes tuning hard. To make it faster, you'd need to turn on Drupal's caches (and PHP opcode caches) to reduce the number of SQLs issued. To get even better numbers, you'd need to get Drupal to use memcached instead of calling PostgreSQL for the simple lookups. You can use the devel module in Drupal to have a look at the SQLs issued. Not pretty, IMHO. See: http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html http://2bits.com/articles/advcache-and-memcached-benchmarks-with-drupal.html The most promising Drupal performance module for performance looks like: http://drupal.org/project/cacherouter (900 req/s!) but I haven't got the chance to give it a go yet. I'm a die-hard PostgreSQL and Drupal supporter, but in this case, I concede straight up Drupal+MySQL will always be faster than Drupal+PostgreSQL because of the way Drupal uses the database. We still use PostgreSQL for our Drupal sites though, because while it's slower, it's plenty fast enough. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NATURAL JOINs
Hi all.
I'm running v8.3.3
First point.
Is there a way to know how a NATURAL JOIN is actually done?
That is, which fields are actually used for the join?
The EXPLAIN directive doesn't show anyting useful.
Second point.
I have this:
CREATE TABLE tab_dictionary ( item text primary key );
CREATE TABLE tab_atable(
item1 TEXT NOT NULL REFERENCES tab_dictionary( item ),
item2 TEXT NOT NULL REFERENCES tab_dictionary( item ),
trans NUMERIC NOT NULL
);
INSERT INTO tab_dictionary VALUES ( 'meters' ),('feet' );
INSERT INTO tab_atable VALUES ( 'meters','feet',3.28084 );
SELECT * FROM tab_atable NATURAL JOIN tab_dictionary;
item1 | item2 | trans | item
+---+-+
meters | feet | 3.28084 | meters
meters | feet | 3.28084 | feet
(2 rows)
Very likely I'm wrong, but this output looks wrong to me (and shold be wrong
also accordingly to the documentation).
Is there ant good explaination to this behaviour?
Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Out of memory in create index
David Wilson [EMAIL PROTECTED] writes: create index val_datestamp_idx on vals(datestamp) tablespace space2; About 30 seconds into the query, I get: ERROR: out of memory DETAIL: Failed on request of size 536870912. Increasing maintenance_work_mem from 1GB to 2GB changed nothing at all- exact same error at exact same time. Watching memory on the machine shows the out of memory error happens when the machine is only at about 35% user. create index concurrently shows an identical error. Try *lowering* maintenance_work_mem. That's how much memory you're telling the index build to use. Evidently your machine doesn't have enough RAM/swap to handle 1G of temporary sort space. In practice values over a few hundred megs don't seem to help much anyways. Try 512M or 256M. Also, a little known fact is that an index build can actually allocate maintenance_work_mem plus an extra work_mem. So if you have work_mem set unreasonably high that could be contributing to the problem. Actually, while I was writing this, I added an additional column to the index and it now appears to be completing (memory has reached about the point it had been failing at and is now holding steady, and the query has been going for significantly longer than the 30 seconds or so it took to error out previously). I sort by both columns at times, so the extra column may in fact turn out to be useful, but the failure of the single column create index in the face of the other successful creates has me confused. Can anyone shed some light on the situation? How much memory the OS allows Postgres to allocate will depend on a lot of external factors. At a guess you had some other services or queries running at the same time the first time which reduced the available memory. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] Drupal and PostgreSQL - performance issues?
On Mon, Oct 13, 2008 at 10:08 AM, admin [EMAIL PROTECTED] wrote: I am also evaluating Drupal + PostgreSQL at the moment. We are building a local government website/intranet that doesn't need to be lightning fast or handle millions of hits a day, but it does need to be rock solid and potentially needs to manage complex business processes. So PostgreSQL seems a good choice. However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable, but who really knows where it will end up? that's the thing. For whatever reason, everyone starts a web project with mysql underneeth. They even don't structure dbs well, not to mention using transactions, and stuff like that. At the end of day, postgresql shares more or less the same queries - if implemented - and same type of primitive db schema, with all its problems. Can anyone recommend an alternative CMS with the features and flexibility of Drupal that supports PostgreSQL 100%? W hat about the Python world, what is Plone like with PostgreSQL support? You seriously don't want to go that route. Plone is such a pain to support, and slow thing as well. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- GJ
Re: [GENERAL] NATURAL JOINs
On Mon, Oct 13, 2008 at 9:52 AM, Reg Me Please [EMAIL PROTECTED] wrote: Is there a way to know how a NATURAL JOIN is actually done? Here is what the manual says about natural joins: http://www.postgresql.org/docs/8.3/interactive/queries-table-expressions.html#QUERIES-FROM ... Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of exactly those column names that appear in both input tables. As with USING, these columns appear only once in the output table. ... ... USING is a shorthand notation: it takes a comma-separated list of column names, which the joined tables must have in common, and forms a join condition specifying equality of each of these pairs of columns. Furthermore, the output of a JOIN USING has one column for each of the equated pairs of input columns, followed by all of the other columns from each table. Thus, USING (a, b, c) is equivalent to ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) with the exception that if ON is used there will be two columns a, b, and c in the result, whereas with USING there will be only one of each. ... -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] Reg: Permission error in Windows psql while trying to read sql commands from file
Thank you very much, Josh. This worked. The backward slashes were the problem. I had tried the quote part earlier, but even that did not work because of the backward slashes. Is there somewhere I can document this for other newbies facing the same issue? Again, thank you very much. On Sun, Oct 12, 2008 at 11:47 PM, Josh Williams [EMAIL PROTECTED] wrote: On Sun, 2008-10-12 at 09:25 +0530, Raj K wrote: Since it is in windows - I could not find any specific file permission mechanisms similar to linux. (This is my first foray in windows - so I am a newbie there too ) The computer is not in a network. So, through googling, I found that to share it, we have to move it to C:\Documents and Settings\All Users\Documents\ - which I did. But even that did not help - as mentioned in the first mail. Try using forward slashes in your path: testdb=# \i c:/testdb.txt And if you have spaces, enclose the path in quotation marks: # \i 'C:/Documents and Settings/All Users/Documents/DB/testdb.txt' If you could help me on this I would be much obliged.. Regards Raj - Josh Williams
Re: [GENERAL] More schema design advice requested
On Mon, Oct 13, 2008 at 9:29 AM, Matthew Wilson [EMAIL PROTECTED] wrote: Any advice? I wonder if this is a sign of a bad design, or maybe if there's some nicer SQL techniques I could use. I don't have the book in front of me at the moment, but I remember this exact problem and a unique solution using a schema redesign around skill sets that would return results very quickly. The method described in the query was referred to as full disjunction. http://www.elsevier.com/wps/find/bookdescription.librarians/710075/description#description sell the problem: 17: EMPLOYMENT AGENCY PUZZLE Sorry that I can't be of more help than this. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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] More schema design advice requested
On Mon, Oct 13, 2008 at 1:11 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Mon, Oct 13, 2008 at 9:29 AM, Matthew Wilson [EMAIL PROTECTED] wrote: I don't have the book in front of me at the moment, but I remember this exact problem and a unique solution using a schema redesign around skill sets that would return results very quickly. The method described in the query was referred to as full disjunction. Perhaps you can try: http://pgfoundry.org/projects/fulldisjunction/ -- Jonah H. Harris, Senior DBA myYearbook.com -- 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] NATURAL JOINs
Richard Broersma [EMAIL PROTECTED] writes: On Mon, Oct 13, 2008 at 9:52 AM, Reg Me Please [EMAIL PROTECTED] wrote: Is there a way to know how a NATURAL JOIN is actually done? Here is what the manual says about natural joins: http://www.postgresql.org/docs/8.3/interactive/queries-table-expressions.html#QUERIES-FROM Finally, NATURAL is a shorthand form of USING: it forms a USING list consisting of exactly those column names that appear in both input tables. As with USING, these columns appear only once in the output table. The OP's case is actually giving a cartesian product, because the tables don't have any column names in common. You'd think this should be an error, but AFAICS the SQL spec requires it to behave that way. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Chart of Accounts
On Mon, Oct 13, 2008 at 6:33 AM, Gregory Stark [EMAIL PROTECTED] wrote: justin [EMAIL PROTECTED] writes: special note do not use only 2 decimal points in the accounting tables. If your application uses 10 decimal places somewhere then every table in the database that has decimals needs to have the same precision. Nothing is more annoying where a transaction says 1.01 and the other side says 1.02 due to rounding. FWIW I think this is wrong. You need to use precisely the number of decimal places that each datum needs. If you use extra it's just as wrong as if you use too few. For example, when you buy gas/petrol at $1.999/gallon and buy 4 gallons you get charged $8.00 not $7.996. If you fail to round at that point you'll find that your totals don't agree with the amount of money in your actual bank account. I wonder if there's a more general way to say that, something like: With a transaction between two systems of different precision, the greater precision system rounds at that point. If you want to take a particular system out to extra digits, it's probably good to record the rounding error as a separate component of the transaction (that is, if you want everything to balance out perfectly). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] It is official, Replicator is now BSD
Hello, We finally got around to releasing Replicator as FOSS. It is BSD licensed and available here: https://projects.commandprompt.com/public/replicator/wiki (Yes it is a self signed cert, its on the list to fix). Enjoy folks! Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ -- 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] It is official, Replicator is now BSD
Great news. Thanks.
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
Hi Merlin, Stored procedure support is a pretty complicated feature. They differ with functions in two major areas: *) input/output syntax. this is what you are dealing with *) manual transaction management. stored procedures should allow you emit 'BEGIN/COMMIT' and do things like vacuum. IIRC, I don't think there was a consensus on the second point or if it was ok to implement the syntax issues without worrying about transactions. I understand the situation, that a range of facets such as syntax, SP i/o and the overall fit of SPs into the architecture of PG should be considered. What do the Postgres gurus say about stored procedures? My SQL experience is rather limited, but I've got the impression that every RDBMS has got its own philosophy about matters relational and I expect Posgresql to be no different. So probably an improvised hack wouldn't be of much use here and things should be thought over. Anyway, at this point I'm finished with my evaluation of Postgresql. The MySQL solution which I've got now works reasonably well. It's just that at this moment my investment into MySQL is still relatively small and I wanted to check my options before I dig myself too deeply into MySQL to make a potential sensible migration too expensive :) Maybe I'm going to revisit Postgresql again in 2009 or 2010 :) Vladimir -- Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C signature.asc Description: OpenPGP digital signature
Re: [GENERAL] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
CREATE PROCEDURE list_user_accounts(IN user_id INT) BEGIN -- Return first result set (single row) SELECT * FROM users WHERE id = user_id; -- Return second result set (zero or more rows) SELECT * FROM accounts WHERE account_holder = user_id; END; So, is it true that as of Postgresql 8.3 there is no way to have a pgpqsql function return multiple SELECTs? Vladimir Dzhuvinov Have you considered returning XML instead? You should be able to get what your looking for much easier with an XMLAGG. Artacus -- 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] PL/pgSQL stored procedure returning multiple result sets (SELECTs)?
On Mon, Oct 13, 2008 at 8:56 PM, Vladimir Dzhuvinov [EMAIL PROTECTED] wrote: Maybe I'm going to revisit Postgresql again in 2009 or 2010 :) good luck, we'll pray for your data to be safe with mysql. cos you can't trust the thing without a good prayer. one thing, all software works differently. If you want to switch to any DBE, you have to spend more than one day on it. trust me.
Re: [GENERAL] Chart of Accounts
If you want to take a particular system out to extra digits, it's probably good to record the rounding error as a separate component of the transaction (that is, if you want everything to balance out perfectly). I think you have two different problems here. On the one hand you have rounding errors which are material when aggregated on the other hand most sales transactions (for example) will come to a dollar and cents figure. If you have two accounts with different precision then I think from an accounting perspective you need to say something like this when posting between the two: DR My 2 Decimal Precision Account 2.00 DR Accumulated Rounding (4 Decimal) 0.0010 CROriginal 4 Decimal Account 2.0010 Then at period end you can including your rounding account and everything will balance. Craig -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: BUG #4078: ERROR: operator does not exist: numeric = character varying
Peter Eisentraut, April 1 2008 Am Dienstag, 1. April 2008 schrieb rupesh: ERROR: operator does not exist: numeric = character varying at character 675 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. (0.735 sec) This was previously working in 8.2.3 but not in 8.3 This was an intentional change. Please read the release notes about fixing your code. I read the release notes, and the idea of removing this to avoid problematic automatic casts seems reasonable, but why not add an additional operator so the useful cases actually still work? I created this, which seems to solve the problem: create function casting_eq_operator(integer, char) returns boolean as 'begin return $1 = cast ($2 as integer); end;' language plpgsql immutable strict; CREATE OPERATOR = (PROCEDURE = casting_eq_operator, LEFTARG = integer , RIGHTARG = char, COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES ); Can this be included by default? On a related note, I originally wrote this using 'character varying' instead of 'char', but that caused simple string comparisons in queries to fail. e.g. select * from foo where mycol = 'abc'; when mycol is a varchar column complained about invalid input syntax for integer. Is there a simple explanation for why it wouldn't just try to do a plain string comparison instead of using my custom operator? eric -- 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] Chart of Accounts
Craig Bennett wrote: If you want to take a particular system out to extra digits, it's probably good to record the rounding error as a separate component of the transaction (that is, if you want everything to balance out perfectly). I think you have two different problems here. On the one hand you have rounding errors which are material when aggregated on the other hand most sales transactions (for example) will come to a dollar and cents figure. If you have two accounts with different precision then I think from an accounting perspective you need to say something like this when posting between the two: DR My 2 Decimal Precision Account 2.00 DR Accumulated Rounding (4 Decimal) 0.0010 CROriginal 4 Decimal Account 2.0010 Then at period end you can including your rounding account and everything will balance. Craig Thats not the problem its the different tables having different precision. We have a WIP tables that notes all the labor and material consumed by all the jobs for an accounting period. So you have some jobs all ways open crossing periods so you need to audit that WIP process account which means going to the WIP tables and verifying that the values in the WIP account equal to the jobs in the WIP tables. If the detail differs even a a penny you have a problem you are not allowed to simply call it rounding error. Pushing it into another account called rounding error does not solve the problem. Values in the wip tables need to equal the values in the General ledger tables The problem occurs when the WIP tables store 6 and 8 decimals and the GL tables have only 2. it creates all kinds of rounding problems and it gets worst when you have thousands of transactions a day a penny multiplied by 1000 becomes 10 bucks times 30 days in a accounting period = 300 bucks. Thats getting pretty big for a rounding mistake and this is only one account. Now take that and multiply that by 10 accounts each going every which way. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Photos from PostgreSQL Conference West 2008
I attended the PostgreSQL Conference West 2008 and had a great time again this year. My photos of the event are up here: http://db.endpoint.com/pg-conf-08 I also blogged about the event: http://blog.endpoint.com/2008/10/postgresql-conference-west-2008-report.html -- Daniel Browning End Point Corporation http://www.endpoint.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: BUG #4078: ERROR: operator does not exist: numeric = character varying
Eric Haszlakiewicz [EMAIL PROTECTED] writes: I created this, which seems to solve the problem: create function casting_eq_operator(integer, char) returns boolean as 'begin return $1 = cast ($2 as integer); end;' language plpgsql immutable strict; CREATE OPERATOR = (PROCEDURE = casting_eq_operator, LEFTARG = integer , RIGHTARG = char, COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES ); Can this be included by default? No. Even if we desired to reverse the decision about not having implicit casting behavior, this definition of the operator would not be appropriate because it provides the opposite of the old behavior. The pre-8.3 behavior would have been to cast the integer to text and apply a textual comparison; which gives different comparison behavior, eg leading zeroes in the string would affect the result. Not to mention that the cast to integer in this definition would fail outright if the string didn't look like an integer. A large part of the reasoning for getting rid of the implicit casts was exactly that it's not very clear what a comparison of this sort should act like, and most people who are accidentally invoking it haven't thought that through either. (Some other problems: I'm pretty sure you meant to refer to text or varchar not char; you referenced commutator and negator operators without defining them; this operator certainly does not hash, and I don't think it merges either, though maybe you could make the latter work if you'd provided all the requisite btree-opfamily infrastructure.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Chart of Accounts
There are a couple of ways to solve your problem Heres my thoughts off the top of my head and what little i know about auctions and how they are run. Also i hope the formating comes out. please note these table do not contain all columns i would have in them its just an idea of how i would get all the tables linked together and laid out. Create Table contact ( contact_id serial not null , first_name text, last_name text, phone text, email text, company_name text, amIaCustomer boolean, amIaVendor boolean) Create Table AuctionHeader( action_id serial not null, date_to_have_action date, date_to_end_action date, auction_description text, auction_percent_take_for_each_item_sold numeric (10, 8) ) Create Table AuctionItems ( auction_id integer, item_id serial not null, item_description text, start_bid money, dont_sell_itemprice money, sold_price money, vendor_id integer, who_Brought_id integer, other_notes_ text) Create table InvoiceHeader ( invoice_id serial not null, item_id integer, vendor_id integer, customer_id integer, invoice_posted_to_gl boolean invoice_paid boolean payment_terms integer, invoice_issue_date date Payment_method text (Credit Card, Money, Check) ) Create Table AR_Header ( account_receivable_id serial not null invoice_id, invoice_total money, date_created date, notes text,) Create Table AR_PaymentsReceived ( ar_item serial not null, account_receivable_id integer, payment_method text, amount_received money, date_received date) Create Table InvoiceItems( item_id serial not null, sold_price money, actual_price_paid money) Create Table general_ledger_transactions( transaction_id serial not null reference_type character, (Am i a Invoice, JE, Credit Memor, Debit Memo, Inventory ) reference_id integer, ( the primary key to the reference table) journal_entry_id integer, (this is used to keep transctions that linked to together like You have debit and Credit account and some Journal Entries may hit 100 accounts ) coa_id integer, accounting_period integer, debit numeric(20,10) , credit numeric(20,10), transaction_date datestamp) primary key (transaction_id) ) When An item is sold by the auctioneer sold and an invoice is Created you would sum up the values Put a Debit to Vendors Account into the GL then Credit the Customer Owes Me Account, then when the money is collected Debit the Customer Owes Me Account credit into a Revenue Account. the gl transactions for the Invoice Creation could look like this TransAtion_id --- Ref_type Reference_id Jorunal_ID---Coa_id - debit--Credit 5784 Invoice Invoice: 785 78485 54 aka CustomerOwesMe$25 5785 Invoice Invoice: 785 78485 67 aka I owe Vendor$20 5786 Invoice Invoice: 785 78485 15 aka Money I could be making $5 5787 ARAR: 4785 78486 5 aka CustomerOwesMe $25 5788 ARAR: 4785 78486 25 aka BillPaidAccount $25 Then Simple selects with joins and a few Case statements can get everything linked together. Also note i am not an accountant by any imagination what so ever. all my stuff is reviewed by CPA and an in house accountant to make sure i get all the debits and credits correct Jeff Williams wrote: Hi Justin I like your method. A question I am in the process of developing an piece of auction software. How would you handle all the bidders and vendors so they all come from a table called contacts and have a serial number. Each Purchase/Payment needs to recorded against each contact as well in the general ledger. We need to get daily balances about each contact. Regards Jeff WIlliams Australia - Original Message - From: justin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Date: Sun, 12 Oct 2008 20:57:59 -0400 Subject: Re: [GENERAL] Chart of Accounts You are making this far to complicated. I just redid the accounting side of an application we have access to source code, so been here and done this. If i was not for the rest of the application i would have completely redone the accounting table layout something like this 3 Accounting Tables One has you chart of Accounts Create table coa ( coa_id serial not null, parent_id int not null default 0, doIhaveChildren boolean default false account_name text null ) primary key(coa_id) Create Table general_ledger_transactions( transaction_id serial not null coad_id integer, accounting_period integer, debit
Re: [GENERAL] Drupal and PostgreSQL - performance issues?
On Mon, Oct 13, 2008 at 1:02 AM, Ivan Sergio Borgonovo [EMAIL PROTECTED] wrote: snip Anyway I don't find myself comfortable with replies in these 2 lines of reasoning: 1) default configuration of PostgreSQL generally doesn't perform well 2) PostgreSQL may be slower but mySQL may trash your data. I think these answers don't make a good service to PostgreSQL. 1) still leave the problem there and doesn't give any good reason why Postgresql comes with a doggy default configuration on most hardware. It still doesn't explain why I've to work more tuning PostgreSQL to achieve similar performances of other DB when other DB don't require tuning. This is a useful question, but there are reasonable answers to it. The key underlying principle is that it's impossible to know what will work well in a given situation until that situation is tested. That's why benchmarks from someone else's box are often mostly useless on your box, except for predicting generalities and then only when they agree with other people's benchmarks. PostgreSQL ships with a very conservative default configuration because (among other things, perhaps) 1) it's a configuration that's very unlikely to fail miserably for most situations, and 2) it's assumed that if server performance matters, someone will spend time tuning things. The fact that database X performs better than PostgreSQL out of the box is fairly irrelevant; if performance matters, you won't use the defaults, you'll find better ones that work for you. Making performance comparable without expert tuning will a) stop most too easy critics about PostgreSQL performances b) give developers much more feedback on PostgreSQL performance in nearer to optimal setup. Most of the complaints of PostgreSQL being really slow are from people who either 1) use PostgreSQL assuming its MySQL and therefore don't do things they way a real DBA would do them, or 2) simply repeat myths they've heard about PostgreSQL performance and have no experience to back up. While it would be nice to be able to win over such people, PostgreSQL developers tend to worry more about pleasing the people who really know what they're doing. (The apparent philosophical contradiction between my statements above and the fact that I'm writing something as inane as PL/LOLCODE doesn't cause me much lost sleep -- yet) If it is easy to write a tool that will help you to tune PostgreSQL, it seems it would be something that will really help PostgreSQL diffusion and improvements. If it is *complicated* to tune PostgreSQL so that it's performance can be *comparable* (I didn't write optimal) with other DB we have a problem. It's not easy to write such a tool; the lists talk about one every few months, and invariable conclude it's harder than just teaching DBAs to do it (or alternatively letting those that need help pay those that can help to tune for them). As to whether it's a problem that it's a complex thing to tune, sure it would be nice if it were easier, and efforts are made along those lines all the time (cf. GUC simplification efforts for a contemporary example). But databases are complex things, and any tool that makes them overly simple is only glossing over the important details. Then other people added in the equation connection pooling as a MUST to compare MySQL and PostgreSQL performances. This makes the investment to have PostgreSQL in place of mySQL even higher for many, or at least it is going to puzzle most. Anyone familiar with high-performance applications is familiar with connection pooling. Or maybe... it is false that PostgreSQL doesn't have comparable performance to other DB with default configuration and repeating over and over the same answer that you've to tune PostgreSQL to get comparable performance doesn't play a good service to PostgreSQL. Why not? It's the truth, and there are good reasons for it. See above. 2) I never saw a trashing data benchmark comparing reliability of PostgreSQL to MySQL. If what I need is a fast DB I'd chose mySQL... I think this could still not be the best decision to take based on *real situation*. If you've got an important application (for some definition of important), your considerations in choosing underlying software are more complex than is it the fastest option. Horror stories about MySQL doing strange things to data, because of poor integrity constraints, ISAM tables, or other problems are fairly common (among PostgreSQL users, at least :) But I will also admit I have none of my own; my particular experience in life has, thankfully, prevented me from much MySQL exposure. Do we really have to trade integrity for speed? Yes. Sanity checks take time. Is MyISAM really much faster in read only operations? Yes. See above. What I get with that kind of answer is: an admission: - PostgreSQL is slow People aren't saying that. They're saying it works better when someone who knows what they're doing runs it. But is PostgreSQL
Re: [GENERAL] postgres user account on OSX
Hi Shane,
I'm trying to untangle some postgresql issues on OSX. I'm now using a
macport installation for postgresql 8.3.4 and I'm using my own custom
Portfile to configure the installation (hardly changed from the main
Portfile, really). Anyhow, the macport install creates a lauchdeamon config
that uses this startup call below. When I test it directly, it's failing:
sudo su postgres -c /opt/local/lib/postgresql83/bin/pg_ctl -D
${POSTGRESQL83DATA:=/opt/local/var/db/postgresql83/defaultdb} start -w -l
/opt/local/var/log/postgresql83/postgres.log -o \-i -l\
waiting for server to start...2008-10-13 19:50:21.734 pg_ctl[43992:617]
CFPreferences: user home directory at /Library/PostgreSQL/8.3 is
unavailable. User domains will be volatile.
could not start
server
Have you seen anything like this before? I have no idea what this means:
CFPreferences: user home directory at /Library/PostgreSQL/8.3 is
unavailable
It looks like a hangover from using a binary installer and I have no idea
how to get rid of that CFPreference.
Any tips much appreciated ;-)
Thanks, Darren
On Fri, Sep 12, 2008 at 8:52 AM, Shane Ambler [EMAIL PROTECTED] wrote:
Darren Weber wrote:
If you want a GUI to alter the home location of the existing user
account run NetInfo Manager which is in /Applications/Utilities
I have OSX Server. This user account doesn't appear in the usual System
Preferences Accounts. I did find it eventually under Applications
Server Workgroup Manager, when I selected a local domain to administer.
That would be a 10.5 machine.
Seems Apple has dropped netinfo manager in 10.5 and replacing it with
Directory and Directory Utility. (Data storage has changed too)
Workgroup Manager is a OSX Server app that isn't a standard part of the
client installs (but can be added by installing the server admin tools)
and (pretty sure) it will only connect to an OSX Server to administer it
- not useful for adjusting a client machine.
You could call it a more user friendly form of netinfo manager (edits
the same data)
System Preferences Accounts will only list accounts normally created
within the Accounts Tab (I believe the criteria is userid's 500) which
makes it easy for the novice user as they don't get to see all the
system accounts like mailman, nobody, postmaster and so on, just the
ones they have manually created.
--
Shane Ambler
pgSQL (at) Sheeky (dot) Biz
Get Sheeky @ http://Sheeky.Biz
