[GENERAL] Cannot open include file: 'nodes/nodes.h'
hi all, I try to use the file pg_type.h who containing, pre-defined type like INT4IOD, INT2OID, INT8OID. So I include in the file#include server/catalog/pg_type.h but I have the followed error C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(22) : fatal error C1083: Cannot open include file: 'nodes/nodes.h': No such file or directory I tried , in the command line to put -I$(PGSQL_HOME)\include -I(PGSQL_HOME)\include\server but, nothing change. any idea?? thanks a++ -- http://deathboater.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PHP PDO-PGSQL and transactions
I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module. What happens is this: On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error Duplicate key violates unique constraint This error should just be logged and the code should continue with the second $subItem. And it does, but the second $checkSubscription also fails with error Current transaction is aborted, commands ignored until end of transaction block And that was not what I meant to happen. Mark that I've only included part of the script. In the foreach loop several other updates and inserts happen. Basically one of those updates and inserts failing should just be logged and the code should continue. When the loop has finished all commands that executed without failure should be commited. How should I change my code to make that happen? Thanks for any help. -- $dbh = new PDO('pgsql:host='.$dbIP.';dbname='.$dbName.';port='.$dbPort, $dbUser, $dbPwd, array(PDO::ATTR_PERSISTENT = true)); $dbh-setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $checkSubscription = $dbh-prepare(select uid from subscriptions where uid=:uid and aboname=:subItem); $insertReminderEntry = $dbh-prepare(insert into reminders (uid,nl) values (:uid,:aboname)); $subscription=array('uktapsps','uktapsem'); $uidArray['uid'] = 'W200705084162'; $dbh-beginTransaction(); foreach ($subscription as $subItem) { $checkSubscription-bindParam(':uid',$uidArray['uid']); $checkSubscription-bindParam(':subItem',$subItem); try { $checkSubscription-execute(); } catch (Exception $e) { $msg.=ERROR: Subscription could not be checked! aboname: $subItem\n; } $insertReminderEntry-bindParam(':uid',$uidArray['uid']); $insertReminderEntry-bindParam(':aboname',$subItem); try { $insertReminderEntry-execute(); } catch (Exception $e) { $msg.=ERROR: Entry could not be inserted into the reminder table! aboname: $subItem\n; } }
Re: [GENERAL] PHP PDO-PGSQL and transactions
On Mon, Dec 01, 2008 at 10:14:52AM +0100, Bart Degryse wrote: I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module. What happens is this: On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error Duplicate key violates unique constraint This error should just be logged and the code should continue with the second $subItem. And it does, but the second $checkSubscription also fails with error Current transaction is aborted, commands ignored until end of transaction block And that was not what I meant to happen. That's the way postgresql works. Either all statements in a transaction succeed, or none of them do. Postgres doesn't do the half-half thing you're looking for. There are a number of ways to deal with it: first check if the row exists, use a stored procedure to make the checking transparent. You can use savepoints to acheive your goal also. The archives may describe other solutions. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] configure options
Siddharth Shah wrote: I am compiling postgres, I have some doubts on ./configure options --enable-nls[=LANGUAGES] enable Native Language Support Do I need to supply each language name which i am going to store in my database or just have to take support UTF-8 ? You need to have support for the locale which you want to use for character sorting, case conversion and character classification (set at initdb time) as well as support for the locale in which you want server messages to be, and for the one you want for date and numeric formatting. You don't have to include support for all languages you store in the database (but the database should then use UTF-8 as server encoding). and --disable-largefile Does it refers to for storing blob objects to store or anything else ? I couldn't find this configure switch in the documentation for 8.3 or 8.4. Can you explain where you found it and which version you are using? 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] configure options
Albe Laurenz wrote: Siddharth Shah wrote: I am compiling postgres, I have some doubts on ./configure options --enable-nls[=LANGUAGES] enable Native Language Support Do I need to supply each language name which i am going to store in my database or just have to take support UTF-8 ? You need to have support for the locale which you want to use for character sorting, case conversion and character classification (set at "initdb" time) as well as support for the locale in which you want server messages to be, and for the one you want for date and numeric formatting. You don't have to include support for all languages you store in the database (but the database should then use UTF-8 as server encoding). and --disable-largefile Does it refers to for storing blob objects to store or anything else ? I couldn't find this configure switch in the documentation for 8.3 or 8.4. Can you explain where you found it and which version you are using? Yours, Laurenz Albe I am using 8.3.5 and I have found these options in configure help ./configure --help -Siddharth
Re: [GENERAL] configure options
Siddharth Shah wrote: I am compiling postgres, I have some doubts on ./configure options --disable-largefile Does it refers to for storing blob objects to store or anything else ? I couldn't find this configure switch in the documentation for 8.3 or 8.4. Can you explain where you found it and which version you are using? I am using 8.3.5 and I have found these options in configure help ./configure --help I see. It seems to be missing in the documentation. This has nothing to do with large objects. This disables use of the large file API for operating system files. If you disable this, you will probably not be able to use operating system files that exceed 2GB in size, which PostgreSQL normally does not do anyway. My recommendation is to leave this switch alone and use the default; you should not encounter any problems. 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] configure options
Albe Laurenz wrote: Siddharth Shah wrote: I am compiling postgres, I have some doubts on ./configure options --disable-largefile Does it refers to for storing blob objects to store or anything else ? I couldn't find this configure switch in the documentation for 8.3 or 8.4. Can you explain where you found it and which version you are using? I am using 8.3.5 and I have found these options in configure help ./configure --help I see. It seems to be missing in the documentation. This has nothing to do with large objects. This disables use of the large file API for operating system files. If you disable this, you will probably not be able to use operating system files that exceed 2GB in size, which PostgreSQL normally does not do anyway. My recommendation is to leave this switch alone and use the default; you should not encounter any problems. Yours, Laurenz Albe In My Application I have only 256MB storage device and I have to manage many other application in same storage So, have to remove use less files , From configuration disabling unwanted options helps to reduce the size. So, I need detail description on compile options and postgres installation files. If this is provided then will help me. -Siddharth -- 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] configure options
Albe Laurenz wrote: Siddharth Shah wrote: I am compiling postgres, I have some doubts on ./configure options --disable-largefile Does it refers to for storing blob objects to store or anything else ? I couldn't find this configure switch in the documentation for 8.3 or 8.4. Can you explain where you found it and which version you are using? I am using 8.3.5 and I have found these options in configure help ./configure --help I see. It seems to be missing in the documentation. This has nothing to do with large objects. This disables use of the large file API for operating system files. If you disable this, you will probably not be able to use operating system files that exceed 2GB in size, which PostgreSQL normally does not do anyway. pg_dump/pg_restore deals with files 2GB, so you will have backup related issues if you disable it. My recommendation is to leave this switch alone and use the default; you should not encounter any problems. Agreed - the cost for supporting it is zero, AFAIK, so there is no reason to turn it off unless the OS has a buggy implementation. //Magnus -- 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] PHP PDO-PGSQL and transactions
Thanks Martijn! Martijn van Oosterhout [EMAIL PROTECTED] 2008-12-01 10:54 On Mon, Dec 01, 2008 at 10:14:52AM +0100, Bart Degryse wrote: I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module. What happens is this: On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error Duplicate key violates unique constraint This error should just be logged and the code should continue with the second $subItem. And it does, but the second $checkSubscription also fails with error Current transaction is aborted, commands ignored until end of transaction block And that was not what I meant to happen. That's the way postgresql works. Either all statements in a transaction succeed, or none of them do. Postgres doesn't do the half-half thing you're looking for. There are a number of ways to deal with it: first check if the row exists, use a stored procedure to make the checking transparent. You can use savepoints to acheive your goal also. The archives may describe other solutions. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines.
Re: [GENERAL] configure options
Siddharth Shah wrote: In My Application I have only 256MB storage device and I have to manage many other application in same storage So, have to remove use less files , From configuration disabling unwanted options helps to reduce the size. So, I need detail description on compile options and postgres installation files. If this is provided then will help me. I don't think that --disable-largefile will reduce the size of the PostgreSQL executables noticably, if at all. It will certainly not reduce the amount of disk space required for your database. Do you only need the PostgreSQL client or also the server? Will that small storage device also contain the database? Reducing the number of languages with --enable-nls=languages will certainly help some. Disable all Optional Packages you don't need (they should be described in the documentation). 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] configure options
Albe Laurenz wrote: Siddharth Shah wrote: In My Application I have only 256MB storage device and I have to manage many other application in same storage So, have to remove use less files , From configuration disabling unwanted options helps to reduce the size. So, I need detail description on compile options and postgres installation files. If this is provided then will help me. I don't think that --disable-largefile will reduce the size of the PostgreSQL executables noticably, if at all. It will certainly not reduce the amount of disk space required for your database. Do you only need the PostgreSQL client or also the server? Will that small storage device also contain the database? Yes, I need to run Client Server both application.Yes database reside in same storage Will small size make any issue ? Reducing the number of languages with --enable-nls=languages will certainly help some. Disable all Optional Packages you don't need (they should be described in the documentation). At this time problem is If I want to support some new foreign language in future on deployed machine. Will it support, while I configured postgres with specific language option. Documentation on these parameters from postgres appreciable 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] Cannot open include file: 'nodes/nodes.h'
2008/12/1 elekis [EMAIL PROTECTED] hi all, I try to use the file pg_type.h who containing, pre-defined type like INT4IOD, INT2OID, INT8OID. So I include in the file#include server/catalog/pg_type.h but I have the followed error C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(22) : fatal error C1083: Cannot open include file: 'nodes/nodes.h': No such file or directory I tried , in the command line to put -I$(PGSQL_HOME)\include -I(PGSQL_HOME)\include\server but, nothing change. did you try to locate nodes.h in your filesystem? on my Linux it's in $PREFIX/include/postgresql/server/nodes/ so I guess you should set -I$(PGSQL_HOME)\include\postgresql and use #include catalog/pg_type.h #include nodes/nodes.h and so on. -- Filip Rembiałkowski
Re: [GENERAL] db backup script in gentoo
Scott, OK, find out how to create users on your gentoo server, and see if you can figure out what's broken in emerge so you can install lftp. It's much better than regular ftp, as it has assured transfer completion and other useful features if you're interested in making sure the file gets transferred. Or switch to rsync, which is how I do it. ftp exists in windows also. So I can create protable scripts which works in linux/windows probably. Backup ftp server is located in ISP (probably Debian) which offers ftp and http access. So it seems that I cannot use rsync. Unresolved is rotating issue: This script creates different backup file for every weekday containing weekday number in name. ftp account in ISP is public account which allows only to upload files, not to overwrite existing ones or download them. So backup fails after a week if files are not deleted manually. How to use http POST command to send backup to this server? In this case it may be possible to create php script in server which handles rotating. Or any other idea ? Andrus. -- 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] configure options
Siddharth Shah wrote: In My Application I have only 256MB storage device and I have to manage many other application in same storage Do you only need the PostgreSQL client or also the server? Will that small storage device also contain the database? Yes, I need to run Client Server both application.Yes database reside in same storage Will small size make any issue ? Well, if your database grows, it might exceed the available storage space and stop working. I guess you planned for that. Reducing the number of languages with --enable-nls=languages will certainly help some. Disable all Optional Packages you don't need (they should be described in the documentation). At this time problem is If I want to support some new foreign language in future on deployed machine. Will it support, while I configured postgres with specific language option. There will be no problem storing and retrieving data in various languages, as long as the database encoding is UTF-8. Documentation on these parameters from postgres appreciable http://www.postgresql.org/docs/current/static/install-procedure.html 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
[GENERAL] Indexes on NULL's and order by ... limit N queries
When i read about 8.3 support indexed queries on NULL values (like rubric_id is NULL) i was really happy. But reality strike again... look like NULL in WHERE don't allow effective using index on (rubric_id, pos) for queries like: ... WHERE rubric_id IS NULL ORDER BY pos LIMIT 5 Here is some details about my issue (all tests on fresh loaded/analyzed into empty 8.3.5 DB): mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=8 order by pos limit 5; QUERY PLAN --- Limit (cost=1337.02..1337.03 rows=5 width=28) (actual time=27.556..27.575 rows=5 loops=1) - Sort (cost=1337.02..1340.77 rows=1501 width=28) (actual time=27.552..27.558 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB - Seq Scan on cluster_weight (cost=0.00..1312.09 rows=1501 width=28) (actual time=0.058..25.008 rows=1501 loops=1) Filter: (rubric_id = 8) Total runtime: 27.638 ms (7 rows) ok so we need index on (rubric_id, pos), lets add it: mboguk_billing=# CREATE INDEX cluster_weight_2 on cluster_weight(rubric_id, pos); CREATE INDEX And try again: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=8 order by pos limit 5; QUERY PLAN - Limit (cost=0.00..1.70 rows=5 width=28) (actual time=0.095..0.122 rows=5 loops=1) - Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..509.31 rows=1501 width=28) (actual time=0.090..0.104 rows=5 loops=1) Index Cond: (rubric_id = 8) Total runtime: 0.176 ms (4 rows) Ok... so now query works as intended... Lets check are index used on search NULL values: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL; QUERY PLAN -- Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1034.21 rows=26435 width=28) (actual time=0.053..48.123 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 85.210 ms (3 rows) Yes it is working... Now lets try main query over NULL: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY pos LIMIT 5; QUERY PLAN -- Limit (cost=1473.29..1473.30 rows=5 width=28) (actual time=92.220..92.239 rows=5 loops=1) - Sort (cost=1473.29..1539.37 rows=26435 width=28) (actual time=92.216..92.223 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB - Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1034.21 rows=26435 width=28) (actual time=0.033..47.333 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 92.310 ms (7 rows) Ooops... that is surprise... I wasn't ready see that plan here... and performance difference over 1000. Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on where something is NULL order by ... limit ... queries. Thanks for any responses and sorry for not so good English. -- SY, Maxim Boguk -- 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 backup script in gentoo
Reid, since he noted the distro is '3 years old', odds are the portage tree is out of date. you can try a sudo emerge --sync and then sudo emerge --ask lftp note that if a sync has not been done in a long time, many packages may be out of date ( possibly why the 'get' of left failed ). if lftp has dependencies, they will most likely have to be rebuilt, and if the dependencies have dependencies, etc, etc, etc -- so the results of the above emerge could be very large and have a lot of impact. Will existing PostgreSql 8.1.4 server continue working after running those commands ? Or is there possibility that it stops working ? Will those commands require re-booting server ? If ssh/scp are already on the box it might be easier to setup a passwordless login configuration and use either scp or sftp to push the backup file. backup ftp server is located in ISP. It offers ftp and http access only. So I cannot use scp nor sftp probably. Andrus. -- 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] Cannot open include file: 'nodes/nodes.h'
elekis [EMAIL PROTECTED] writes: I tried , in the command line to put -I$(PGSQL_HOME)\include -I(PGSQL_HOME)\include\server but, nothing change. If that's really what you put, then you forgot a dollar sign... 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] PHP PDO-PGSQL and transactions
Martijn van Oosterhout wrote: On Mon, Dec 01, 2008 at 10:14:52AM +0100, Bart Degryse wrote: I'm having a transaction problem when executing prepared statements using the PHP PDO-PGSQL module. What happens is this: On the first $subItem, $checkSubscription goes well, but $insertReminderEntry fails with error Duplicate key violates unique constraint This error should just be logged and the code should continue with the second $subItem. And it does, but the second $checkSubscription also fails with error Current transaction is aborted, commands ignored until end of transaction block And that was not what I meant to happen. That's the way postgresql works. Either all statements in a transaction succeed, or none of them do. Postgres doesn't do the half-half thing you're looking for. There are a number of ways to deal with it: first check if the row exists, use a stored procedure to make the checking transparent. You can use savepoints to acheive your goal also. The archives may describe other solutions. Have a nice day, There's also the possibility that you're hitting a bug in some PHP versions' PostgreSQL PDO support. Versions in the 5.1.x range cause prepared statements to generate errors for some reason which kill transactions, even if the SQL is flawless. This caused me a lot of headaches until I did some digging, but it is easily cured with 5.2.x or later. Raymond
Re: [GENERAL] configure options
Magnus Hagander [EMAIL PROTECTED] writes: Albe Laurenz wrote: My recommendation is to leave this switch alone and use the default; you should not encounter any problems. Agreed - the cost for supporting it is zero, AFAIK, so there is no reason to turn it off unless the OS has a buggy implementation. Yeah. The reason it's not documented is we don't officially support it --- it's something that autoconf adds on its own. It would only be interesting if autoconf's test for largefile support obtained the wrong answer on some platform; which is probably not an interesting case anymore anyway. 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] configure options
Siddharth Shah [EMAIL PROTECTED] writes: In My Application I have only 256MB storage device and I have to manage many other application in same storage Quite honestly, you're going to need some other database besides Postgres if you need a disk footprint that's only a fraction of 256MB. It's just not designed for that. Maybe sqllite or bdb would be closer to what you need. 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] configure options
Tom Lane wrote: Siddharth Shah [EMAIL PROTECTED] writes: In My Application I have only 256MB storage device and I have to manage many other application in same storage Quite honestly, you're going to need some other database besides Postgres if you need a disk footprint that's only a fraction of 256MB. It's just not designed for that. Maybe sqllite or bdb would be closer to what you need. regards, tom lane Hi Tom, I have explored SQLite DBD but they have limitations on concurrency My Requirement falls In between lighter enterprise databases So finally concluded postgres, My database size on pg is almost 12 MB Does any cons which I am going to face with pg with slower size then please mention. Transaction frequencies : more select queries than insert / update Almost 20 queries/sec is executing with current database. - Siddharth -- 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] Indexes on NULL's and order by ... limit N queries
Maxim Boguk [EMAIL PROTECTED] writes: Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on where something is NULL order by ... limit ... queries. There's nothing wrong with the plan; you've just got too many NULLs to make it worth using the index for that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot open include file: 'nodes/nodes.h'
hi all, I tried your both solutions (and effectively, I forget the $ signe, sorry) but know , I have that C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(390) : error C2054: expected '(' to follow 'PGDLLIMPORT' C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(390) : error C2085: 'newNodeMacroHolder' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(414) : error C2085: 'nodeToString' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(419) : error C2085: 'stringToNode' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(424) : error C2085: 'copyObject' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(429) : error C2085: 'equal' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(440) : error C2085: 'Selectivity' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(441) : error C2085: 'Cost' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(461) : error C2085: 'CmdType' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server\nodes/nodes.h(497) : error C2085: 'JoinType' : not in formal parameter list C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(43) : error C2061: syntax error : identifier 'CATALOG' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(217) : error C2143: syntax error : missing '{' before '*' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2146: syntax error : missing ')' before identifier 'OID' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2061: syntax error : identifier 'OID' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2059: syntax error : ';' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2513: '/*global*/ ' : no variable declared before '=' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2064: term does not evaluate to a function taking -22 arguments C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2143: syntax error : missing ')' before 'type' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2017: illegal escape sequence C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(265) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(266) : error C2143: syntax error : missing ')' before 'string' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(266) : error C2143: syntax error : missing '{' before 'string' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(266) : error C2059: syntax error : 'Unknown' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(266) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2146: syntax error : missing ')' before identifier 'OID' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2061: syntax error : identifier 'OID' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2059: syntax error : ';' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2513: '/*global*/ ' : no variable declared before '=' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2064: term does not evaluate to a function taking -22 arguments C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2065: 'bytea' : undeclared identifier C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2146: syntax error : missing ')' before identifier 'PGNSP' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2017: illegal escape sequence C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(269) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(270) : error C2143: syntax error : missing ')' before 'string' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(270) : error C2143: syntax error : missing '{' before 'string' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(270) : error C2059: syntax error : 'Unknown' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(270) : error C2059: syntax error : ')' C:\Program Files\PostgreSQL\8.3\include\server/catalog/pg_type.h(273) : error C2146: syntax error : missing ')' before identifier 'OID' C:\Program
Re: [GENERAL] Question on libpq parameters
On Mon, Dec 1, 2008 at 12:10 AM, Owen Hartnett [EMAIL PROTECTED] wrote: At 1:26 PM -0500 11/30/08, Tom Lane wrote: Owen Hartnett [EMAIL PROTECTED] writes: Yes, it did. I'm confused. My first parameter is a string, but the following two are integers. I thought the paramType parameter indicated the type. Do the integers need to be sprintf'd to strings? Yes. Alternatively, you could pass the integers as binary, but that's not notation-free either (you need htonl or some such). regards, tom lane Thanks, that did it. I got confused with the binary parameters, and PQgetvalue returning the binary through a char * when it returns a binary value. you may want to check out libpqtypes: http://libpqtypes.esilo.com/ http://pgfoundry.org/projects/libpqtypes/ merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot open include file: 'nodes/nodes.h'
elekis [EMAIL PROTECTED] writes: and my include is #include catalog/pg_type.h #include nodes/nodes.h You need postgres.h first. 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] Detecting changes to certain fields in 'before update' trigger functions
Hi list, First steps in trigger functions and PL/pgSQL so please bear with me... How can one detect changes to certain fields in before update trigger functions? IF (NEW.column-name != OLD.column-name) THEN ... doesn't work, so obviously my understanding of the values of the varriables NEW and OLD in before update trigger functions is wrong; I had thought that OLD holds the record as it was before the update, and that NEW holds the record as it is since the update (but before the update has been committed)? How should one go about detecting changes to certain fields in before update trigger functions? Any help/advice much appreciated. Sebastian -- 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] Trigger before delete does fire before, but delete doesn't not happen
On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: Snip Adrian Klaver a écrit : When I run this test case I get: test=# 2nd step : Deletion of command 1 test=# delete from commande where id=1; ERROR: update or delete on table commande violates foreign key constraint commandeligne_id_commande_fkey on table commandeligne DETAIL: Key (id)=(1) is still referenced from table commandeligne. The FK in commandeligne (id_commande integer NOT NULL references commande (id)) is preventing the trigger from completing. Here, I don't get that error. Maybe you could try creating the commandeligne table like that : CREATE TABLE commandeligne ( id_commande integer NOT NULL -- references commande (id) -- on delete cascade on update cascade , montant real, id_produit integer NOT NULL, CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) )with oids; I'm running PG 8.3.5 or 8.2.11, result is the same. Regards, It works if you change this to an AFTER DELETE trigger: DROP TRIGGER IF EXISTSp_commande_bd ON commande; CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); Use this version DROP TRIGGER IF EXISTSp_commande_bd ON commande; CREATE TRIGGER p_commande_bd after DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); The problem as far as I can tell is tuple visibility. By using a BEFORE trigger for the first function the OLD.* values are still available when the second trigger fires so UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande; has values to update in the commande table. For further clarifciation see: http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html In particular: The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level before trigger, because it hasn't happened yet. -- Adrian Klaver [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] Trigger before delete does fire before, but delete doesn't not happen
On Monday 01 December 2008 7:27:48 am Adrian Klaver wrote: On Sunday 30 November 2008 11:18:12 pm Stéphane A. Schildknecht wrote: Snip Adrian Klaver a écrit : When I run this test case I get: test=# 2nd step : Deletion of command 1 test=# delete from commande where id=1; ERROR: update or delete on table commande violates foreign key constraint commandeligne_id_commande_fkey on table commandeligne DETAIL: Key (id)=(1) is still referenced from table commandeligne. The FK in commandeligne (id_commande integer NOT NULL references commande (id)) is preventing the trigger from completing. Here, I don't get that error. Maybe you could try creating the commandeligne table like that : CREATE TABLE commandeligne ( id_commande integer NOT NULL -- references commande (id) -- on delete cascade on update cascade , montant real, id_produit integer NOT NULL, CONSTRAINT clef PRIMARY KEY (id_commande, id_produit) )with oids; I'm running PG 8.3.5 or 8.2.11, result is the same. Regards, It works if you change this to an AFTER DELETE trigger: DROP TRIGGER IF EXISTSp_commande_bd ON commande; CREATE TRIGGER p_commande_bd before DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); Use this version DROP TRIGGER IF EXISTSp_commande_bd ON commande; CREATE TRIGGER p_commande_bd after DELETE ON commande FOR Each row EXECUTE PROCEDURE p_commande_bd(); The problem as far as I can tell is tuple visibility. By using a BEFORE trigger for the first function the OLD.* values are still available when the second trigger fires so UPDATE commande SET montant=montant-OLD.montant WHERE id = OLD.id_commande; has values to update in the commande table. For further clarifciation see: http://www.postgresql.org/docs/8.3/interactive/trigger-datachanges.html In particular: The data change (insertion, update, or deletion) causing the trigger to fire is naturally not visible to SQL commands executed in a row-level before trigger, because it hasn't happened yet. Sorry, this applies to a trigger calling the function on the same table. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [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] Detecting changes to certain fields in 'before update' trigger functions
On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote: Hi list, First steps in trigger functions and PL/pgSQL so please bear with me... How can one detect changes to certain fields in before update trigger functions? IF (NEW.column-name != OLD.column-name) THEN ... doesn't work, so obviously my understanding of the values of the varriables NEW and OLD in before update trigger functions is wrong; I had thought that OLD holds the record as it was before the update, and that NEW holds the record as it is since the update (but before the update has been committed)? How should one go about detecting changes to certain fields in before update trigger functions? Any help/advice much appreciated. Sebastian It works here. Can you be more specific? Full function code, table schema,etc. Thanks, -- Adrian Klaver [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] Detecting changes to certain fields in 'before update' trigger functions
On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... The != operator doesn't work the way you might think when nulls are thrown into the mix. I asked a similar question a while back and was kindly pointed to the following syntax: IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... -- 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
[GENERAL] [TSearch2] Chinese dictionary?
Hello. I am looking for a Chinese dictionary for TSearch2 Postgresql feature, with no success yet. I found a reference on a chinese webpage: http://code.google.com/p/nlpbamboo/wiki/TSearch2 With the help of translate.google.com, I thought managed to figure out what to do: I installed nlpbamboo on my Redhat server, but I failed to follow the next steps because of compilation errors trying to make postgres/bamboo Anyways, even if i had successfully followed the different steps described, I would still have lacked the most important part: the dictionary files themselves... Has one of you managed to instaleld a chinese TSearch2 dictionary on Postgres? Thanks for your attention, Daniel Chiaramello -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Adrian Klaver [EMAIL PROTECTED]: On Monday 01 December 2008 7:18:51 am Sebastian Tennant wrote: I had thought that OLD holds the record as it was before the update, and that NEW holds the record as it is since the update (but before the update has been committed)? '42.10 Trigger Procedures' seems to confirm this: `NEW' Data type `RECORD'; variable holding the new database row for `INSERT'/`UPDATE' operations in row-level triggers. This variable is `NULL' in statement-level triggers. `OLD' Data type `RECORD'; variable holding the old database row for `UPDATE'/`DELETE' operations in row-level triggers. This variable is `NULL' in statement-level triggers. It works here. Can you be more specific? Full function code, table schema,etc. Of course. timestamper.sql starts here -- \i ./timestamper.sql DROP TABLE IF EXISTS tt; CREATE TEMP TABLE tt (username character varying(12), delisted boolean, created_at timestamp(0) without time zone, updated_at timestamp(0) without time zone, delisted_at timestamp(0) without time zone); CREATE OR REPLACE FUNCTION timestamper() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN NEW.created_at := current_timestamp(0); END IF; IF (TG_OP = 'UPDATE') THEN NEW.updated_at := current_timestamp(0); IF ((NEW.delisted = true) AND (NEW.delisted != OLD.delisted)) THEN NEW.delisted_at := current_timestamp(0); END IF; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER timestamper_before_insert BEFORE INSERT ON tt FOR EACH ROW EXECUTE PROCEDURE timestamper(); CREATE TRIGGER timestamper_before_update BEFORE UPDATE ON tt FOR EACH ROW EXECUTE PROCEDURE timestamper(); -- DROP FUNCTION timestamper() CASCADE; -- no need to drop temporary tables timesatmper.sql ends here testdb= \i ./timestamper.sql DROP TABLE CREATE TABLE CREATE FUNCTION CREATE TRIGGER CREATE TRIGGER testdb= insert into tt values (foo'); INSERT 0 1 testdb= select * from tt; -[ RECORD 1 ] username| foo delisted| created_at | 2008-12-01 16:17:37 updated_at | delisted_at | testdb= update tt set username=bar'; UPDATE 1 testdb= select * from tt; -[ RECORD 1 ] username| bar delisted| created_at | 2008-12-01 16:17:37 updated_at | 2008-12-01 16:18:27 delisted_at | testdb= update tt set delisted=true where username='bar'; UPDATE 1 testdb= select * from tt; -[ RECORD 1 ] username| bar delisted| t created_at | 2008-12-01 16:17:37 updated_at | 2008-12-01 16:19:01 delisted_at | The triggers for the initial insert and the first update do what I want them to, but the second update (that marks 'foo' as delisted) fails to update the delisted_at timestamp. Sebastian -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 7:18 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... The != operator doesn't work the way you might think when nulls are thrown into the mix. I asked a similar question a while back and was kindly pointed to the following syntax: IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... That's it! Thanks very much Richard. I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. I wasn't planning on spending four hours doing just that, but now I suppose I'm almost glad I did. Sebastian -- 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 backup script in gentoo
sudo emerge --sync ... deleting xfce-extra/xfce4-weather/files/digest-xfce4-weather-0.4.9 deleting xfce-extra/xfce4-weather/files/digest-xfce4-weather-0.4.1 deleting xfce-extra/xfce4-weather/files/digest-xfce4-weather-0.4.0 deleting xfce-extra/xfce4-weather/files/digest-xfce4-weather-0.3.9.1-r1 deleting xfce-extra/xfce4-weather/files/ deleting xfce-extra/xfce4-weather/xfce4-weather-0.4.9.ebuild deleting xfce-extra/xfce4-weather/xfce4-weather-0.4.9-r1.ebuild deleting xfce-extra/xfce4-weather/xfce4-weather-0.4.1.ebuild deleting xfce-extra/xfce4-weather/xfce4-weather-0.4.0.ebuild deleting xfce-extra/xfce4-weather/xfce4-weather-0.3.9.1-r1.ebuild deleting xfce-extra/xfce4-xkb/xfce4-xkb-0.3.5.ebuild deleting xfce-extra/xfce4-xkb/xfce4-xkb-0.3.2-r2.ebuild deleting xfce-extra/xfce4-xkb/files/digest-xfce4-xkb-0.3.5 deleting xfce-extra/xfce4-xkb/files/digest-xfce4-xkb-0.3.2-r2 Number of files: 130388 Number of files transferred: 91288 Total file size: 169664640 bytes Total transferred file size: 148443324 bytes Literal data: 148443324 bytes Matched data: 0 bytes File list size: 3196842 File list generation time: 12.983 seconds File list transfer time: 0.000 seconds Total bytes sent: 2125704 Total bytes received: 156054763 sent 2125704 bytes received 156054763 bytes 195889.12 bytes/sec total size is 169664640 speedup is 1.07 Updating Portage cache: 100% Performing Global Updates: /usr/portage/profiles/updates/3Q-2004 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' ... Performing Global Updates: /usr/portage/profiles/updates/1Q-2005 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' . Performing Global Updates: /usr/portage/profiles/updates/3Q-2005 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' .. Performing Global Updates: /usr/portage/profiles/updates/4Q-2006 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' . Performing Global Updates: /usr/portage/profiles/updates/1Q-2007 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' . Performing Global Updates: /usr/portage/profiles/updates/2Q-2007 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' .. Performing Global Updates: /usr/portage/profiles/updates/3Q-2007 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' Performing Global Updates: /usr/portage/profiles/updates/4Q-2007 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' . Performing Global Updates: /usr/portage/profiles/updates/1Q-2008 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' Performing Global Updates: /usr/portage/profiles/updates/2Q-2008 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move' s='/var/db SLOT move' S='binary SLOT move' p='update /etc/portage/package.*' . Performing Global Updates: /usr/portage/profiles/updates/3Q-2008 (Could take a couple of minutes if you have a lot of binary packages.) .='update pass' *='binary update' @='/var/db move'
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. I wouldn't say it is intentionally buried. I would say that the PostgreSQL manual focuses primarily is on What are the PG features. While the manual may at times document some of the good/best practices to use by combining various PG features, I wouldn't say that its intention isn't to be an authoritative source on How to use PG features. On the other hand, there are many ANSI-SQL books that focus on good practices. For example, the need for the IS DISTINCT FROM when dealing with nulls would be discussed in an SQL book. Once you have the theory down, you can turn to the PostgreSQL manual to find out how PostgreSQL implements this functionality. -- 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] Detecting changes to certain fields in 'before update' trigger functions
Sebastian Tennant [EMAIL PROTECTED] writes: I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. Where exactly do you think we should document it, if not in the manual? In any case it's SQL-standard behavior that any book about SQL will tell you. 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] pg_dump restore as transaction?
If my perusal of the sql generated by pg_dump is correct, then it doesn't appear that it's wrapped in a transaction, and thus might be able to only complete a partial restore? Or does psql myDatabase mypg_dumpfile wrap the file stream in a transaction? If not, is there a reason why it can't be done so (some process that cannot be run as a transaction inside the file)? Or should I just add begin and commit statements at the beginning and end of file? I want to provide a mechanized daily update of one schema into a differently named database, and I'd like it to rollback if if fails. -Owen -- 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] pg_dump restore as transaction?
Owen Hartnett [EMAIL PROTECTED] writes: If my perusal of the sql generated by pg_dump is correct, then it doesn't appear that it's wrapped in a transaction, and thus might be able to only complete a partial restore? That's correct, and intentional. You can use pg_restore's -1 switch or add begin/end manually if you don't want it to work 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] pg_dump restore as transaction?
Owen Hartnett wrote: If my perusal of the sql generated by pg_dump is correct, then it doesn't appear that it's wrapped in a transaction, and thus might be able to only complete a partial restore? You're right, it is not. Try pg_restore --single-transaction. (You'll need pg_dump -Fc though.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] pg_dump restore as transaction?
Quoting Owen Hartnett [EMAIL PROTECTED]: If my perusal of the sql generated by pg_dump is correct, then it doesn't appear that it's wrapped in a transaction, and thus might be able to only complete a partial restore? Or does psql myDatabase mypg_dumpfile Try to use pg_restore with the following option: -1, --single-transaction restore as a single transaction Or psql with the following option: -1 (one) execute command file as a single transaction Kind regards, Peter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump restore as transaction?
At 12:37 PM -0500 12/1/08, Tom Lane wrote: Owen Hartnett [EMAIL PROTECTED] writes: If my perusal of the sql generated by pg_dump is correct, then it doesn't appear that it's wrapped in a transaction, and thus might be able to only complete a partial restore? That's correct, and intentional. You can use pg_restore's -1 switch or add begin/end manually if you don't want it to work that way. regards, tom lane Thanks to everybody for their help. You guys are great. -Owen -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Richard Broersma [EMAIL PROTECTED]: On Mon, Dec 1, 2008 at 8:35 AM, Sebastian Tennant [EMAIL PROTECTED] wrote: IF (NEW.column-name != OLD.column-name) THEN ... IF( NEW.* IS DISTINCT FROM OLD.* ) THEN ... I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. I wouldn't say it is intentionally buried. I would say that the PostgreSQL manual focuses primarily is on What are the PG features. While the manual may at times document some of the good/best practices to use by combining various PG features, I wouldn't say that its intention isn't to be an authoritative source on How to use PG features. On the other hand, there are many ANSI-SQL books that focus on good practices. For example, the need for the IS DISTINCT FROM when dealing with nulls would be discussed in an SQL book. Once you have the theory down, you can turn to the PostgreSQL manual to find out how PostgreSQL implements this functionality. That's sound advice and I take your point about the manual focussing on Postgre features rather than SQL per se. I have read one or two SQL books but I'm very much a learn by doing person... and the fact is, I haven't done much doing, until now. May I wriggle out a little by saying that I didn't really mean what I said, or rather, I failed to say what I really meant; that it sometimes feels as if a gotcha has been buried in order to make you read the manual. Sebastian -- 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 backup script in gentoo
On Mon, Dec 1, 2008 at 12:04, Andrus [EMAIL PROTECTED] wrote: sudo emerge --sync * An update to portage is available. It is _highly_ recommended * that you update portage now, before any other packages are updated. * Please run 'emerge portage' and then update ALL of your * configuration files. * To update portage, run 'emerge portage'. root ~ # emerge portage !!! ARCH is not set... Are you missing the '/etc/make.profile' symlink? !!! Is the symlink correct? Is your portage tree complete? So it seems that emerge portage fails. Also I do'nt understand this: your /usr/portage directory is probably not mounted properly check that again and then check /etc/make.profile points to a valid directory which will be something along the lines of /usr/portage/profiles/default-linux/arch/version for example on one of my old amd64 boxes it's /usr/portage/profiles/default-linux/amd64/2007.0
Re: [GENERAL] db backup script in gentoo
your /usr/portage directory is probably not mounted properly check that again and then check /etc/make.profile points to a valid directory which will be something along the lines of /usr/portage/profiles/default-linux/arch/version jomanda etc # ls /etc/make.profile -l lrwxrwxrwx 1 root root 48 19. juuni 2006 /etc/make.profile - ../usr/portage/profiles/default-linux/x86/2006.0 jomanda etc # ls -l ../usr/portage/profiles/default-linux/x86/2006.0 ls: ../usr/portage/profiles/default-linux/x86/2006.0: No such file or directory jomanda etc # ls -l ../usr/portage/profiles/default-linux/x86/2007.0 kokku 12 drwxr-xr-x 2 root root 54 14. apr2007 desktop -rw-r--r-- 1 root root 486 8. aug2007 make.defaults -rw-r--r-- 1 root root 664 14. apr2007 packages -rw-r--r-- 1 root root 3 14. apr2007 parent drwxr-xr-x 2 root root 60 14. apr2007 server jomanda amd64 # emerge portage Calculating dependencies... done! !!! Error: the sys-apps/portage-2.1.4_rc1 package conflicts with another package; !!!the two packages cannot be installed on the same system together. !!!Please use 'emerge --pretend' to determine blockers. For more information about Blocked Packages, please refer to the following section of the Gentoo Linux x86 Handbook (architecture is irrelevant): http://www.gentoo.org/doc/en/handbook/handbook-x86.xml?full=1#blocked jomanda etc # emerge portage !!! Your current profile is deprecated and not supported anymore. !!! Please upgrade to the following profile if possible: default/linux/x86/2008.0 To upgrade do the following steps: # cd /etc/ # rm make.profile # ln -s ../usr/portage/profiles/default/linux/x86/2008.0 make.profile Calculating dependencies... done! !!! Error: the sys-apps/portage-2.1.4_rc1 package conflicts with another package; !!!the two packages cannot be installed on the same system together. !!!Please use 'emerge --pretend' to determine blockers. For more information about Blocked Packages, please refer to the following section of the Gentoo Linux x86 Handbook (architecture is irrelevant): http://www.gentoo.org/doc/en/handbook/handbook-x86.xml?full=1#blocked jomanda etc # cd /etc/ jomanda etc # rm make.profile jomanda etc # ln -s ../usr/portage/profiles/default/linux/x86/2008.0 make.profile jomanda etc # emerge portage Traceback (most recent call last): File /usr/bin/emerge, line 4049, in ? emerge_main() File /usr/bin/emerge, line 3768, in emerge_main settings, trees, mtimedb = load_emerge_config() File /usr/bin/emerge, line 3679, in load_emerge_config trees = portage.create_trees(trees=trees, **kwargs) File /usr/lib/portage/pym/portage.py, line 6780, in create_trees config_incrementals=portage_const.INCREMENTALS) File /usr/lib/portage/pym/portage.py, line 902, in __init__ addProfile(os.path.realpath(self.profile_path)) File /usr/lib/portage/pym/portage.py, line 889, in addProfile raise portage_exception.ParseError( NameError: global name 'parents_file' is not defined Andrus. -- 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] Detecting changes to certain fields in 'before update' trigger functions
Quoth Tom Lane [EMAIL PROTECTED]: Sebastian Tennant [EMAIL PROTECTED] writes: I sometimes think this kind of gotcha is purposely buried, or not addressed at all, in order to force users to read the manual. Where exactly do you think we should document it, if not in the manual? I clearly didn't express myself very well. Let me set the record straight by saying that my experience with PostgreSQL over the past three months or so has been fantastic, thanks in no small part to the clear and comprehensive accompanying manual. All I meant was that it sometimes _feels_ as if a vital piece of information has been buried in the manual in order to make you read it. (I wasn't making a serious point and I didn't expect it to be taken literally). Sebastian P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL -- 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 backup script in gentoo
as previously said you need to go to http://forums.gentoo.org/ On Mon, Dec 1, 2008 at 13:16, Andrus [EMAIL PROTECTED] wrote: your /usr/portage directory is probably not mounted properly check that again and then check /etc/make.profile points to a valid directory which will be something along the lines of /usr/portage/profiles/default-linux/arch/version jomanda etc # ls /etc/make.profile -l lrwxrwxrwx 1 root root 48 19. juuni 2006 /etc/make.profile - ../usr/portage/profiles/default-linux/x86/2006.0 jomanda etc # ls -l ../usr/portage/profiles/default-linux/x86/2006.0 ls: ../usr/portage/profiles/default-linux/x86/2006.0: No such file or directory jomanda etc # ls -l ../usr/portage/profiles/default-linux/x86/2007.0 kokku 12 drwxr-xr-x 2 root root 54 14. apr2007 desktop -rw-r--r-- 1 root root 486 8. aug2007 make.defaults -rw-r--r-- 1 root root 664 14. apr2007 packages -rw-r--r-- 1 root root 3 14. apr2007 parent drwxr-xr-x 2 root root 60 14. apr2007 server jomanda amd64 # emerge portage Calculating dependencies... done! !!! Error: the sys-apps/portage-2.1.4_rc1 package conflicts with another package; !!!the two packages cannot be installed on the same system together. !!!Please use 'emerge --pretend' to determine blockers. For more information about Blocked Packages, please refer to the following section of the Gentoo Linux x86 Handbook (architecture is irrelevant): http://www.gentoo.org/doc/en/handbook/handbook-x86.xml?full=1#blocked jomanda etc # emerge portage !!! Your current profile is deprecated and not supported anymore. !!! Please upgrade to the following profile if possible: default/linux/x86/2008.0 To upgrade do the following steps: # cd /etc/ # rm make.profile # ln -s ../usr/portage/profiles/default/linux/x86/2008.0 make.profile Calculating dependencies... done! !!! Error: the sys-apps/portage-2.1.4_rc1 package conflicts with another package; !!!the two packages cannot be installed on the same system together. !!!Please use 'emerge --pretend' to determine blockers. For more information about Blocked Packages, please refer to the following section of the Gentoo Linux x86 Handbook (architecture is irrelevant): http://www.gentoo.org/doc/en/handbook/handbook-x86.xml?full=1#blocked jomanda etc # cd /etc/ jomanda etc # rm make.profile jomanda etc # ln -s ../usr/portage/profiles/default/linux/x86/2008.0 make.profile jomanda etc # emerge portage Traceback (most recent call last): File /usr/bin/emerge, line 4049, in ? emerge_main() File /usr/bin/emerge, line 3768, in emerge_main settings, trees, mtimedb = load_emerge_config() File /usr/bin/emerge, line 3679, in load_emerge_config trees = portage.create_trees(trees=trees, **kwargs) File /usr/lib/portage/pym/portage.py, line 6780, in create_trees config_incrementals=portage_const.INCREMENTALS) File /usr/lib/portage/pym/portage.py, line 902, in __init__ addProfile(os.path.realpath(self.profile_path)) File /usr/lib/portage/pym/portage.py, line 889, in addProfile raise portage_exception.ParseError( NameError: global name 'parents_file' is not defined Andrus.
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
Adrian Klaver [EMAIL PROTECTED] writes: The problem as far as I can tell is tuple visibility. Sort of: the triggers on commandeligne fire (and update the commande row) at completion of the DELETE command inside p_commande_bd. This means that by the time control returns from that trigger, the tuple version that was targeted for deletion is already dead, so there's nothing to do. It doesn't chain up to the newer version of the row. An AFTER trigger would be better for this on general principles, anyway. The rule of thumb is use a BEFORE trigger to adjust what happens to the target row, but use an AFTER trigger to propagate the changes to other rows. If you don't do it that way then you have problems whenever there are multiple triggers, since no individual BEFORE trigger can be sure it knows the final state of the row. 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] Detecting changes to certain fields in 'before update' trigger functions
Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Monty on MySQL 5.1: Oops, we did it again
http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. Cheers, Steve -- 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] Monty on MySQL 5.1: Oops, we did it again
On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) -- GJ
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
On Mon, Dec 1, 2008 at 1:05 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) I'd rather do the paritioning by hand and use slony and know it works than rely on the code that's doing all that in mysql. If your server crashes while updating a partitioned table, you could lose all the data in it. Replication can mysteriously just quit working with no errors or warning. Make your pick, half assed code that sometimes works, or postgresql. :) -- 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] Monty on MySQL 5.1: Oops, we did it again
On Mon, 2008-12-01 at 20:05 +, Grzegorz Jaśkiewicz wrote: On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Depends on your needs, a broken step is worse than a manual one. Joshua D. Drake Now I know why Tom Lane doesn't have a blog :) -- GJ -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
2008/12/1 Scott Marlowe [EMAIL PROTECTED]: I'd rather do the paritioning by hand and use slony and know it works than rely on the code that's doing all that in mysql. If your server crashes while updating a partitioned table, you could lose all the data in it. Replication can mysteriously just quit working with no errors or warning. Make your pick, half assed code that sometimes works, or postgresql. :) FYI, my reference up there was to MySQL doing those things (losing data and not replicating) not pgsql... -- 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] Trigger before delete does fire before, but delete doesn't not happen
- Tom Lane [EMAIL PROTECTED] wrote: Adrian Klaver [EMAIL PROTECTED] writes: The problem as far as I can tell is tuple visibility. Sort of: the triggers on commandeligne fire (and update the commande row) at completion of the DELETE command inside p_commande_bd. This means that by the time control returns from that trigger, the tuple version that was targeted for deletion is already dead, so there's nothing to do. It doesn't chain up to the newer version of the row. Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple for the row with id of 1. This means the original statement delete from commande where id=1 runs against a version of the row that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and changed the row from under the original statement. regards, tom lane Adrian Klaver [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] Monty on MySQL 5.1: Oops, we did it again
On Mon, Dec 01, 2008 at 08:05:48PM +, Grzegorz Jaśkiewicz wrote: well, at least they have replication and partitioning built in. That will provide excellent comfort to the users. HaplessUser: Your replication crashed and took all of my slaves with it, and then my primary database crashed and I had an outage! You cost me $BIGNUM dollars in downtime! MySQLSupport: Well, at least replication is built in! HaplessUser: But it's broken! You broke my database! I lost data! This is buggy! Why am I paying you? MySQLSupport: Built in! Built in! Built in! LALALALA. Yep. Comforting, that. A -- Andrew Sullivan [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] Indexes on NULL's and order by ... limit N queries
Sorry with all my respect to you, you look like wrong. Here example: With NULL's: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY pos LIMIT 5; QUERY PLAN --- Limit (cost=1503.75..1503.76 rows=5 width=28) (actual time=93.334..93.353 rows=5 loops=1) - Sort (cost=1503.75..1569.84 rows=26435 width=28) (actual time=93.329..93.335 rows=5 loops=1) Sort Key: pos Sort Method: top-N heapsort Memory: 25kB - Bitmap Heap Scan on cluster_weight (cost=314.32..1064.67 rows=26435 width=28) (actual time=7.519..48.678 rows=26435 loops=1) Recheck Cond: (rubric_id IS NULL) - Bitmap Index Scan on cluster_weight_2 (cost=0.00..307.72 rows=26435 width=0) (actual time=7.350..7.350 rows=26435 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 93.433 ms (9 rows) Now lets change NULL's to -1 mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL; UPDATE 26435 And ANALYZE mboguk_billing=# ANALYZE cluster_weight; ANALYZE And try same query with -1 instead of NULL: mboguk_billing=# EXPLAIN ANALYZE SELECT * from cluster_weight where cluster_weight.rubric_id=-1 ORDER BY pos LIMIT 5; QUERY PLAN --- Limit (cost=0.00..0.25 rows=5 width=28) (actual time=0.056..0.080 rows=5 loops=1) - Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1334.41 rows=26435 width=28) (actual time=0.053..0.065 rows=5 loops=1) Index Cond: (rubric_id = (-1)) Total runtime: 0.133 ms (4 rows) And plan become normal. So issue not with too many NULL's in my dataset. -- SY, Maxim Boguk Tom Lane wrote: Maxim Boguk [EMAIL PROTECTED] writes: Looks like when indexed search over NULL's added, planner wasn't learned right way use such index on where something is NULL order by ... limit ... queries. There's nothing wrong with the plan; you've just got too many NULLs to make it worth using the index for that. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexes on NULL's and order by ... limit N queries
Maxim Boguk wrote: Sorry with all my respect to you, you look like wrong. The difference is that the plan with -1 does not need to sort the output, because it comes sorted out of the index; so the execution can be stopped as soon as 5 tuples have come out. With NULL, that can't be done. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Trigger before delete does fire before, but delete doesn't not happen
Adrian Klaver [EMAIL PROTECTED] writes: Thanks for the explanation. Just so I am clear,the act of updating the row in p_commandeligne_ad creates a new tuple for the row with id of 1. This means the original statement delete from commande where id=1 runs against a version of the row that no longer exists and becomes a no-op statement. This happens because the trigger was run as BEFORE and changed the row from under the original statement. Right. 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] Monty on MySQL 5.1: Oops, we did it again
Grzegorz Jaśkiewicz wrote: On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) Actually, he has a couple of them: pgsql-general@postgresql.org [EMAIL PROTECTED] . . :) -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin -- 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] Indexes on NULL's and order by ... limit N queries
Alvaro Herrera wrote: Maxim Boguk wrote: Sorry with all my respect to you, you look like wrong. The difference is that the plan with -1 does not need to sort the output, because it comes sorted out of the index; so the execution can be stopped as soon as 5 tuples have come out. With NULL, that can't be done. But why? NULL's have some special representation in index which don't work same as normal values? Eg output with rubric_id is NULL dont come sorted from index? Really my tests show same behavior of -1 and NULL values: mboguk_billing=# SELECT pos from cluster_weight where rubric_id=-1 limit 20; pos - 20 20 25 40 40 50 60 60 80 80 100 120 140 160 180 200 220 240 260 280 (20 rows) mboguk_billing=# UPDATE cluster_weight set rubric_id=NULL where rubric_id=-1; UPDATE 26435 mboguk_billing=# ANALYZE cluster_weight; ANALYZE mboguk_billing=# SELECT pos from cluster_weight where rubric_id is NULL limit 20; pos - 20 20 25 40 40 50 60 60 80 80 100 120 140 160 180 200 220 240 260 280 (20 rows) Eg output with rubric_id is NULL come ordered be pos from index (rubric_id, pos) ( Here is explains: mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id is NULL limit 20; QUERY PLAN --- Limit (cost=0.00..0.99 rows=20 width=2) (actual time=0.050..0.144 rows=20 loops=1) - Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1314.94 rows=26435 width=2) (actual time=0.045..0.082 rows=20 loops=1) Index Cond: (rubric_id IS NULL) Total runtime: 0.214 ms (4 rows) mboguk_billing=# UPDATE cluster_weight set rubric_id=-1 where rubric_id IS NULL; UPDATE 26435 mboguk_billing=# ANALYZE cluster_weight; ANALYZE mboguk_billing=# EXPLAIN ANALYZE SELECT pos from cluster_weight where rubric_id=-1 limit 20; QUERY PLAN --- Limit (cost=0.00..0.95 rows=20 width=2) (actual time=0.050..0.141 rows=20 loops=1) - Index Scan using cluster_weight_2 on cluster_weight (cost=0.00..1259.05 rows=26435 width=2) (actual time=0.045..0.081 rows=20 loops=1) Index Cond: (rubric_id = (-1)) Total runtime: 0.214 ms (4 rows) Plans look same. ) PS: REINDEX do not change situation. -- SY Maxim Boguk -- 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] Nested Loop Left Join always shows rows=1
Scara Maccai [EMAIL PROTECTED] writes: I ran ANALYZE on both tables, but the plan shows rows=1 in the inner table results. If I change the left outer join into an inner join the bitmap index scan of the inner table of the nested loop shows a correct number of rows (not the exact same thing as actual rows, but very close). Yeah, this is a bug: it's effectively double-counting the selectivity of the index clause. The error isn't enormously critical, since the join size estimate is right; but it could perhaps lead to choosing a plain indexscan when a bitmap scan would be better. I've applied a patch. 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] Indexes on NULL's and order by ... limit N queries
Maxim Boguk [EMAIL PROTECTED] writes: But why? NULL's have some special representation in index which don't work same as normal values? In general, NULLs don't work the same as normal values, no. The reason this particular query isn't working as you are expecting is that foo IS NULL isn't seen as an ordering constraint by the planner's pathkey machinery, and so the query doesn't appear to match the index order. You could work around it by explicitly specifying a matching ordering: SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL ORDER BY rubric_id, pos LIMIT 5; ^^ 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] Monty on MySQL 5.1: Oops, we did it again
I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * 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] Monty on MySQL 5.1: Oops, we did it again
Geoffrey wrote: Grzegorz Jaśkiewicz wrote: On Mon, Dec 1, 2008 at 8:00 PM, Steve Crawford [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html All interesting, but especially the part about half-way down under the heading So what went wrong with MySQL 5.1 ? - must-read for anyone involved in selecting a database. well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) Actually, he has a couple of them: pgsql-general@postgresql.org [EMAIL PROTECTED] . . :) I'm very happy and proud to use Postgresql as the developers working on Postgresql deliver a quality product, not claim its quality. -- 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] [pgsql-www] Reg: Nested query
On 01/12/2008 18:39, Vignesh Raaj wrote: Can anyone please help in getting a solution... Cross-posting to lots of inappropriate lists is not going to endear you to many people, and is going to decrease, not increase, your chance of getting an answer. You should post to -general only with this query, as ISTM that this is most appropriate for your query. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
Greg Smith wrote: I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD I quit using MySQL years ago when the default table type did not have transactions and subqueries were not existent. The features I was looking for were already in PostgreSQL for several versions. I am surprised to see such an honest post regarding MySQL. Sun Picks Up MySQL For $1 Billion http://www.techcrunch.com/2008/01/16/sun-picks-up-mysql-for-1-billion-open-source-is-a-legitimate-business-model/ to bad for them they did not go with PostgreSQL. :)
Re: [GENERAL] Monty on MySQL 5.1: Oops, we did it again
On Mon, Dec 1, 2008 at 4:10 PM, Jason Long [EMAIL PROTECTED] wrote: Greg Smith wrote: I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD I quit using MySQL years ago when the default table type did not have transactions and subqueries were not existent. The features I was looking for were already in PostgreSQL for several versions. I am surprised to see such an honest post regarding MySQL. Sun Picks Up MySQL For $1 Billion to bad for them they did not go with PostgreSQL. :) It's free. The pgsql community, however, is priceless. -- 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] Monty on MySQL 5.1: Oops, we did it again
Scott Marlowe wrote: On Mon, Dec 1, 2008 at 4:10 PM, Jason Long [EMAIL PROTECTED] wrote: Greg Smith wrote: I wonder if I'm the only one who just saved a copy of that post for reference in case it gets forcibly removed... Recently I was thinking about whether I had enough material to warrant a 2008 update to Why PostgreSQL instead of MySQL; who would have guessed that Monty would do most of the research I was considering for me? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD I quit using MySQL years ago when the default table type did not have transactions and subqueries were not existent. The features I was looking for were already in PostgreSQL for several versions. I am surprised to see such an honest post regarding MySQL. Sun Picks Up MySQL For $1 Billion to bad for them they did not go with PostgreSQL. :) It's free. The pgsql community, however, is priceless. No doubt. The pgsql community rocks. In fact the support on this mailing list is top notch and free. :) Thank you a million times over to anyone that has give me advice here. I have never gotten bad advice from this list.
Re: [GENERAL] Problem with langage encoding
Stephane Bortzmeyer wrote: On Thu, Nov 27, 2008 at 02:34:17AM +0900, Craig Ringer [EMAIL PROTECTED] wrote a message of 188 lines which said: ERROR: character 0xc3bd of encoding UTF8 has no equivalent in WIN1251 Which it does not; that character is ??? (HANGUL SYLLABLE SSYEG) No, I don't think so. I think that 0xc3bd is the binary value, and 0xc3bd in UTF-8 is U+00FD (LATIN SMALL LETTER Y WITH ACUTE) which is less surprising. It is in Latin-1 but not in WIN1251. Yes, that would make a lot more sense. From there it's easy, see below: In KOI8-R, FD is the Shcha, a common letter in Russian, so I wonder if the database was really in Latin-1. OK, so what we end up with is the following sequence (expressed in Python, which is always handy for encoding conversions etc): print \xc3\xbd.decode(utf-8).encode(latin-1).decode(koi8_r) to yield: Щ Assuming that's right, what presumably happened is that the database was initialized with UTF-8 and loaded with data it was told was in the latin-1 encoding, but was actually in the koi8_r encoding. Therefore, utf-8 sequences were generated based on the interpretation of the bytes for each koi8_r character as the latin-1 character for the same byte value, so: Input file: 0xfd (latin-1: ý, koi8_r: Щ) | | (input interpreted as latin-1) v Database: 0xc3bd (utf-8: ý) To recover the data you must reverse that process. Thankfully it's going to be 100% reversible, ie no information has been lost. To create a tiny test table for the following explanation and fix code I just ran: create table ss ( x text) ; insert into ss (x) values (E'\xc3\xbd'); Now if I: set client_encoding = WIN1251; I get: test= select * from ss; ERROR: character 0xc3bd of encoding UTF8 has no equivalent in WIN1251 just like you. With client_encoding set as utf-8 (which is what my machine is) I get: test= select * from ss; x --- ý (1 row) PostgreSQL's convert() function: http://www.postgresql.org/docs/current/static/functions-string.html may be used now to transform your data. It doesn't assume any encoding for the input string, unlike convert_from and convert_to, so you can use a statement like this to convert your data:(where 'x' is the string of mangled data to be converted): select ( convert_from( convert(x::bytea, 'utf-8', 'latin-1'), 'koi8_r') ) from ss; In other words: Decode the utf-8 sequence in the input and map each utf-8 code point to the corresponding latin-1 character, outputting one byte per latin-1 character. Interpret the sequence of bytes just produced as a sequence of characters in the koi8_r encoding, and map them to the same characters in the database's internal encoding. To copy the converted data to a new table: CREATE TABLE converted (y text); INSERT INTO converted(y) SELECT ( convert_from( convert(x::bytea, 'utf-8', 'latin-1'), 'koi8_r') ) from ss; Now if I SELECT from the table of converted data, I get the right(?) output: test= select * from converted; y --- Щ (1 row) You can easily wrap that up in a simple SQL function: CREATE OR REPLACE FUNCTION fixstr(text) RETURNS text AS $$ SELECT convert_from(convert($1::bytea, 'utf-8', 'latin-1'),'koi8_r') $$ LANGUAGE 'SQL' IMMUTABLE; so you can just: test= select fixstr(x) from ss; fixstr Щ (1 row) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] slow, long-running 'commit prepared'
The transaction where COMMIT PREPARED was slow basically did the following 20 times: - create a child table T1 as select from another child table T2 - drops child table T2 - renames T1 to T2 What here would cause the 2PC state file to grow large? In my rough experiments, its size seems constant in the number of row locks held, and linear in the number of table locks held. Is there any state in that file that grows linearly with the size of the data touched in the transaction? On Wed, Nov 26, 2008 at 5:52 AM, Tom Lane [EMAIL PROTECTED] wrote: John Smith [EMAIL PROTECTED] writes: I have a pg instance with 700GB of data, almost all of which is in one table. When I PREPARE and then COMMIT PREPARED a transaction that reads writes to a large fraction of that data (about 10%, effectively randomly chosen rows and so every file in the table is modified), the COMMIT PREPARED sometimes takes a very long time--2 to 5 minutes. Is this expected? It's impossible to say without knowing more about what the transaction did. But one piece of data you could check easily is the size of the 2PC state file (look into $PGDATA/pg_twophase/). 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] slow, long-running 'commit prepared'
John Smith [EMAIL PROTECTED] writes: The transaction where COMMIT PREPARED was slow basically did the following 20 times: - create a child table T1 as select from another child table T2 - drops child table T2 - renames T1 to T2 What here would cause the 2PC state file to grow large? In my rough experiments, its size seems constant in the number of row locks held, and linear in the number of table locks held. Is there any state in that file that grows linearly with the size of the data touched in the transaction? It was the number of locks I was speculating about. But that pattern shouldn't result in more than 20 or so locks, so it's still not clear what's happening. In any case, a prepared xact is already holding all the locks it needs, so COMMIT PREPARED shouldn't have to block on anything. Have you tried watching the committing process with top, vmstat, select * from pg_stat_activity, etc? That should at least give you a clue whether it's CPU-bound or IO-bound or (against the above theory) blocked on something. 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] Monty on MySQL 5.1: Oops, we did it again
On Mon, Dec 1, 2008 at 8:43 PM, Geoffrey [EMAIL PROTECTED]wrote: Grzegorz Jaśkiewicz wrote: well, at least they have replication and partitioning built in. How reliable it is, is completely another story - but still, they are a step ahead in that regard. Now I know why Tom Lane doesn't have a blog :) Actually, he has a couple of them: pgsql-general@postgresql.org [EMAIL PROTECTED] . which reminds me, of my favourite recent quote: Think I'll go fix this while I'm watching the football game ... -- GJ
[GENERAL] Favorite Tom Lane quotes
On Mon, Dec 1, 2008 at 7:49 PM, Grzegorz Jaśkiewicz [EMAIL PROTECTED] wrote: which reminds me, of my favourite recent quote: Think I'll go fix this while I'm watching the football game ... We really need a favorite Tom Lane quotes thread. Mine is (roughly): We don't support that, but you're free to try it, you just get to keep both pieces if it breaks. -- 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] Monty on MySQL 5.1: Oops, we did it again
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= [EMAIL PROTECTED] writes: which reminds me, of my favourite recent quote: Think I'll go fix this while I'm watching the football game ... Well, the Steelers were up 23-10 at that point, so the game no longer demanded too much attention ... 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] Favorite Tom Lane quotes
Scott Marlowe [EMAIL PROTECTED] writes: We really need a favorite Tom Lane quotes thread. Mine is (roughly): We don't support that, but you're free to try it, you just get to keep both pieces if it breaks. Hate to disillusion you, but that's a standard comment around Red Hat. I have no idea who said it first, but twasn't me. 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] Favorite Tom Lane quotes
Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: We really need a favorite Tom Lane quotes thread. Mine is (roughly): We don't support that, but you're free to try it, you just get to keep both pieces if it breaks. Hate to disillusion you, but that's a standard comment around Red Hat. I have no idea who said it first, but twasn't me. i took this off Wikipedia http://en.wikipedia.org/wiki/Tom_Lane_(Open_Source_Software_Developer) On idiotic benchmark comparisons Try to carry 500 people from Los Angeles to Tokyo in an F-15. No? Try to win a dogfight in a 747. No? But they both fly, so it must be useful to compare them... especially on the basis of the most simplistic test case you can think of. For extra points, use *only one* test case. Perhaps this paper can be described as comparing an F-15 to a 747 on the basis of required runway length
Re: [GENERAL] Detecting changes to certain fields in 'before update' trigger functions
Quoth Alvaro Herrera [EMAIL PROTECTED]: Sebastian Tennant wrote: P.S. Emacs users of PostgreSQL might like to know that there's a texinfo version of the manual (version 8.3.3) available for download from here: http://www.emacswiki.org/PostGreSQL Hmm, we did have a patch to add a texinfo target to the docs Makefile ... apparently it was never applied. Maybe that's a good idea? It's a very good idea IMHO. I would love to see a texinfo target in the docs Makefile. Nothing beats Info for convenience. Sebastian -- 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] problems with special characters
On Wed, Nov 26, 2008 at 3:36 PM, Karina Guardado [EMAIL PROTECTED] wrote: I have a problem I have created a database with encoding SQL_ASCII and when I insert the data using a terminal in linux and insert the data for example Insert into mytable values(1,'Eléctrico'); it works fine but if I try to copy this data from a text file doing \copy mytable from textfile, it insert the data but instead of é writes a ? and I don't have any idea how to copy the data without this problem. From the horse's mouth: The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0-127 according to the ASCII standard, while byte values 128-255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting, because PostgreSQL will be unable to help you by converting or validating non-ASCII characters. Source: http://www.postgresql.org/docs/8.3/static/multibyte.html In other words, use a server encoding that works with your set of non-ASCII characters. Good luck. -- 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] Favorite Tom Lane quotes
Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. -- * 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] Favorite Tom Lane quotes
On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. What is a tape? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Favorite Tom Lane quotes
On Mon, Dec 1, 2008 at 10:42 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. What is a tape? Apparently something to do with backups. But I don't know what those are either... :-P -- 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] Favorite Tom Lane quotes
On Tue, Dec 2, 2008 at 5:44 AM, Scott Marlowe [EMAIL PROTECTED]wrote: Apparently something to do with backups. But I don't know what those are either... :-P well, we're all waiting for built in replication ;) About the tapes, I love that Oracle emps bit :D Sure, they write too much java code, this is equivalent of getting high on h, if you ask me. -- GJ
Re: [GENERAL] Favorite Tom Lane quotes
Scott Marlowe wrote: On Mon, Dec 1, 2008 at 10:42 PM, Joshua D. Drake [EMAIL PROTECTED] wrote: On Tue, 2008-12-02 at 00:37 -0500, Greg Smith wrote: Tom on things that might ruin his tape backups: Then of course there are the *other* risks, such as the place burning to the ground, or getting drowned by a break in the city reservoir that's a couple hundred yards up the hill...Or maybe being burgled by Oracle employees who are specifically after my backup tapes. What is a tape? Apparently something to do with backups. But I don't know what those are either... :-P Never underestimate the bandwidth of a station wagon full of tapes hurtling down the highway. —Tanenbaum, Andrew S. (1996). Computer Networks. New Jersey: Prentice-Hall, 83. ISBN 0-13-349945-6. A modern chrysler town and country(1) has a cargo capacity of 140.1 cubic feet(2) letting it carry 17163 LTO4 (3) tapes at 800GB each. Thats 13730TB. Say it has to get from San Francisco to LA (about 6 hrs according to google maps directions(4)), that gives 2288TB/hour, or 5.1 terabit/second. klint. 1. its more of a minivan than a station wagon these days but close enough. halve the number of tapes if you're thinking of the classic woodie http://www.allpar.com/old/townandcountry.html 2. http://www.chrysler.com/hostc/vsmc/vehicleSpecModels.do?modelYearCode=CUC200908 3. dimensions from http://en.wikipedia.org/wiki/Linear_Tape-Open 4. http://maps.google.com/maps?f=dsaddr=san+franciscodaddr=los+angeleshl=engeocode=mra=lssll=37.0625,-95.677068sspn=58.598104,116.542969ie=UTF8ll=35.939855,-120.330885spn=7.601811,14.567871z=7 -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [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
[GENERAL] Recover data for one table from database dump
Hi, I need to know how I can easily get data for just one table from postgres dump database backup. Kind regards, P.
[GENERAL] pgadmin3
Hi guys, What happened to pgadmin3 rpms for fedora core 10? Aren't they built yet? BR, Pedro Doria Meunier -- 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] Recover data for one table from database dump
am Tue, dem 02.12.2008, um 10:21:51 +0300 mailte Otandeka Simon Peter folgendes: Hi, I need to know how I can easily get data for just one table from postgres dump database backup. Binary dump for pg_restore? If so, you can use the -L - Option. ASCII-Dump: If so, edit the file. 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
Fwd: [GENERAL] Cannot open include file: 'nodes/nodes.h'
#include postgres.h #include catalog/pg_type.h #include nodes/nodes.h heu... I have that now rcqpgsql.c c:\program files\postgresql\8.3\include\server\pg_config.h(116) : warning C4005: 'HAVE_FINITE' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(286) : see previous definition of 'HAVE_FINITE' c:\program files\postgresql\8.3\include\server\pg_config.h(259) : warning C4005: 'HAVE_MEMMOVE' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(267) : see previous definition of 'HAVE_MEMMOVE' c:\program files\postgresql\8.3\include\server\pg_config.h(391) : warning C4005: 'HAVE_STRTOUL' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(307) : see previous definition of 'HAVE_STRTOUL' c:\program files\postgresql\8.3\include\server\pg_config_os.h(7) : warning C4005: '_WIN32_WINNT' : macro redefinition C:\Program Files\MySQL\MySQL Server 5.0\include\config-win.h(24) : see previous definition of '_WIN32_WINNT' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(112) : error C2011: 'fd_set' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(54) : see declaration of 'fd_set' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(147) : warning C4005: 'FD_SET' : macro redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(88) : see previous definition of 'FD_SET' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(156) : error C2011: 'timeval' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(97) : see declaration of 'timeval' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(212) : error C2011: 'hostent' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(153) : see declaration of 'hostent' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(225) : error C2011: 'netent' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(166) : see declaration of 'netent' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(232) : error C2011: 'servent' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(173) : see declaration of 'servent' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(244) : error C2011: 'protoent' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(185) : see declaration of 'protoent' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(400) : error C2011: 'sockaddr_in' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(321) : see declaration of 'sockaddr_in' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(410) : error C2011: 'WSAData' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(331) : see declaration of 'WSAData' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(470) : warning C4005: 'SO_DONTLINGER' : macro redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(411) : see previous definition of 'SO_DONTLINGER' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(527) : warning C4005: 'AF_IPX' : macro redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(469) : see previous definition of 'AF_IPX' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(555) : warning C4005: 'AF_MAX' : macro redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(488) : see previous definition of 'AF_MAX' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(561) : error C2011: 'sockaddr' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(494) : see declaration of 'sockaddr' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(601) : error C2011: 'sockproto' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(503) : see declaration of 'sockproto' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(640) : error C2011: 'linger' : 'struct' type redefinition C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock.h(540) : see declaration of 'linger' C:\Program Files\Microsoft Visual Studio 8\VC\PlatformSDK\include\winsock2.h(653) : warning C4005: