Re: [GENERAL] Programmatic method to determine currently installed
Will Wright wrote: Hi Magnus, thanks for the quick response. Unfortunately I still have an issue with this regsitry identification method because I'd like to code my installer so that it can also identify the postreSQL versions that were not available at the time I authored my install check, i.e. future versions. You could issue a SELECT version() - that is about as definitive as you can get. Don't forget you'll need to cope with the case where a user has 2 or more versions of PostgreSQL running on the same machine. I'm not sure it's safe to assume only one installation. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] odbc in postgresql and php
Bob Powell wrote: Hello everyone, Has anyone installed the postgres php driver. I would like to know how to install it on Linux and then what the proper way to access it is from a php web page. I have been unable to find actual code for php and doing this. Please help. Thanks. Almost any version of Linux will have packaged versions of PostgreSQL, PHP and the libraries to connect the two. The pg_xxx functions are documented in the manuals and more recent versions have database wrappers in the Pear addon repository. Does that help at all? Oh - I'm not clear on what ODBC has to do with this. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Perl::DBI and interval syntax
On Mon, Nov 07, 2005 at 05:58:04PM -0500, Tom Lane wrote: Allen [EMAIL PROTECTED] writes: SELECT count(*) from post where post_ts = current_date - interval ? This is not right, and never has been right, even though it may have accidentally failed to fail with some client libraries. Try CAST(? as interval) (SQL standard) ?::interval (Postgres-ism) The interval something syntax is only legal when something is a bare string literal. This seems to be an example of breakage caused by DBI switching from substitute params in client to use new protocol to substitute params in server (prepare/execute). AIUI, if you disable use of the new protocol, it should work as before. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpp7X0Oxm8l8.pgp Description: PGP signature
[GENERAL] unsubscribe
Ramachandran.Chidambram 91(422)2496638 (Coimbatore.Res) 91(4923)262362 (Kollengode.Res) 91(4923)275576 (Kollengode.Works) Check Out the new free AIM(R) Mail -- 2 GB of storage and industry-leading spam and email virus protection. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ident client authentication
I have a problem with ident client authentication. My server is debian sarge, pg version is 7.4.7, apache 2.0.54, mod_python 2.3. A python script is placed on the server and runs under mod_python in apache. I make a request from firefox on a win 2k box to this server. If I have local all jim trust in my pg_hba.conf file, all is well, my python script runs and delivers the expected output. However, if i have local all jim ident sameuser I get FATAL: IDENT authentication failed for user jim, via mod-python debug and in postgres log. jim is both a unix user (linux) and a postgres user. Is this what would be expected? How can I make ident authentication work? Any help would be appreciated, including where this message should be posted if this list is inappropriate. Paul Hide
Re: [GENERAL] ident client authentication
Paul Hide wrote: I have a problem with ident client authentication. My server is debian sarge, pg version is 7.4.7, apache 2.0.54, mod_python 2.3. A python script is placed on the server and runs under mod_python in apache. However, if i have local all jim ident sameuser I get FATAL: IDENT authentication failed for user jim, via mod-python debug and in postgres log. jim is both a unix user (linux) and a postgres user. Is this what would be expected? How can I make ident authentication work? At a guess, your Python script is running as the same user as your webserver (usually apache/www-data/nobody or similar). This is why the ident sameuser isn't working. You'll either need to log in as the webserver user, or use password authentication. Any help would be appreciated, including where this message should be posted if this list is inappropriate. This list is fine. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Beyond the 1600 columns limit on windows
Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. Regards -Evandro-- Evandro M Leite JrPhD Student Software developerUniversity of Southampton, UKPersonal website: http://evandro.orgAcademic website: http://www.soton.ac.uk/~evandroPlease, use Jr(at)evandro.org for personal messages
Re: [GENERAL] Aggregates, group, and order by
On Nov 7, 2005, at 17:47 , David Fetter wrote: On Mon, Nov 07, 2005 at 05:12:05PM +0900, Michael Glaesmann wrote: I'm trying to concatenate strings in variable orders using a custom aggregate. However, I'm having a difficult time figuring out the SQL I need to use to accomplish this. How about using the ARRAY() constructor as below? Thanks for the idea, David. I'll give it a look! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) wrote: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I don't think so. Are you sure you need more than 1600 columns? That's many more than I've ever wanted or needed. If you can share some details of the problem you are trying to solve, perhaps someone can see a different solution for you. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I would like to know who on earth needs 1600 columns and even beyond? Hint: you can have practically unlimited rows in your n:m table :-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] database export problem
Hello,I am in a strange trouble: I cannot export database in by postgresPostgres version - 7.2.3Database in it: List of databases Name | Owner | Encoding ---+--+--database | nsadmin | UNICODE template0 | postgres | UNICODEtemplate1 | postgres | UNICODE (3 rows)When I export the database, the process just stuck in the middle.What should I look for? Koala
Re: [GENERAL] Perl::DBI and interval syntax [side question]
Martijn van Oosterhout wrote: On Mon, Nov 07, 2005 at 05:58:04PM -0500, Tom Lane wrote: Allen [EMAIL PROTECTED] writes: SELECT count(*) from post where post_ts = current_date - interval ? This is not right, and never has been right, even though it may have accidentally failed to fail with some client libraries. Try CAST(? as interval) (SQL standard) ?::interval (Postgres-ism) [...] This seems to be an example of breakage caused by DBI switching from substitute params in client to use new protocol to substitute params in server (prepare/execute). [...] Can this be the cause of a huge loss of perf? I have the following query in a Perl script using DBI + DBD::Pg, AutoCommit = 0: SELECT stats_put_sources(?, ?, int4(?), int4(?)) This syntax runs almost 10x faster than: SELECT stats_put_sources(?, ?, ?::int4, ?::int4) I can find where is the real difference, maybe this protocol stuff. In the facts the speed difference was so great that there were no need to use EXPLAIN to know what was the best option. N.B.: The stats_put_source(varchar,varchar,int4,int4) function is a rewrite of the INSERT OR UPDATE function described in the docs. Thanks, -- MaXX ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] autovacuum,8.1, Win
What needs to be configured in order autovacuum processbe active?
Re: [GENERAL] Beyond the 1600 columns limit on windows
I'm doing a PhD in data mining and I need more than 1600 columns. I gotan error message saying that I can not use more than 1600 columns. It is happening because I have to change categorical values to binarycreating new columns. Do you know if oracle can handle it? -- Evandro M Leite Jr. PhD Student Software developer University of Southampton, UKPersonal website: http://evandro.orgAcademic website: http://www.soton.ac.uk/~evandroMobile 079 068 70740 Office 023 8055 3644 Home 023 8055 9160 On 11/8/05, Tino Wildenhain [EMAIL PROTECTED] wrote: Evandro's mailing lists (Please, don't send personal messages to thisaddress) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres.I would like to know who on earth needs 1600 columns and even beyond?Hint: you can have practically unlimited rows in your n:m table :-) ---(end of broadcast)---TIP 4: Have you searched our list archives?http://archives.postgresql.org -- Evandro M Leite JrPhD Student Software developerUniversity of Southampton, UKPersonal website: http://evandro.org Academic website: http://www.soton.ac.uk/~evandroPlease, use Jr(at)evandro.org for personal messages
Re: [GENERAL] Perl::DBI and interval syntax
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
This seems to be an example of breakage caused by DBI switching from
substitute params in client to use new protocol to substitute params
in server (prepare/execute).
AIUI, if you disable use of the new protocol, it should work as before.
This is correct. Though generally not recommeded, you can switch it off
with the pg_server_prepare attribute like so:
$dbh-{pg_server_prepare} = 0;
This will force DBD::Pg to do the quoting itself, with the subsequent
penalty of speed and loss of auto type casting.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200511080815
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDcKU7vJuQZxSWSsgRArwmAKDKe75V/TY4oYWLkiICN2osmJTmBwCcDXGy
p+yPZqpu0sv0Ov8hlBN0XkU=
=w+aj
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] ident client authentication
On Tue, Nov 08, 2005 at 10:02:14 +, Paul Hide [EMAIL PROTECTED] wrote: I have a problem with ident client authentication. My server is debian sarge, pg version is 7.4.7, apache 2.0.54, mod_python 2.3. A python script is placed on the server and runs under mod_python in apache. I make a request from firefox on a win 2k box to this server. If I have local all jim trust in my pg_hba.conf file, all is well, my python script runs and delivers the expected output. However, if i have local all jim ident sameuser I get FATAL: IDENT authentication failed for user jim, via mod-python debug and in postgres log. jim is both a unix user (linux) and a postgres user. Is this what would be expected? Does the web server run as user jim? That seems unusual. You may need to set up an ident map that allows user 'apache' (or whatever applies on your machine) to connect as postgres user 'jim'. How can I make ident authentication work? Any help would be appreciated, including where this message should be posted if this list is inappropriate. Paul Hide ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] database export problem
On Nov 8, 2005, at 19:13 , Shu Hung (Koala) wrote: I am in a strange trouble: I cannot export database in by postgres Postgres version - 7.2.3 Database in it: List of databases Name| Owner | Encoding ---+--+-- database | nsadmin | UNICODE template0 | postgres | UNICODE template1 | postgres | UNICODE (3 rows) When I export the database, the process just stuck in the middle. What should I look for? How are you exporting the database? Are you using pg_dump? What error are you getting? How do you know it's stuck? If the database is large, it may take a while to dump. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] WinXP - statistics collector errors
Hello, In my log files I have an error that occurs frequently : FATAL: could not read from statistics collector pipe and FATAL: could not write to statistics collector pipe: No connection could be made because the target machine actively refused it. The server release is 8.0.3 and is running on WinXP Pro sp2 The pg_autovacuum deamon is running (if it can help...) I would appreciate any help. I wonder if this error is really FATAL or not. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) wrote: I'm doing a PhD in data mining and I need more than 1600 columns. I got an error message saying that I can not use more than 1600 columns. It is happening because I have to change categorical values to binary creating new columns. Perhaps you don't want a relational database at all if you are stretching it to match your client application in this way. Do I have it right that you have something like Table: bird_sighting_facts (bird, category, value) 1 | wingspan| 120mm 2 | beak-colour | red 3 | chest-colour| blue ... And are converting it into: expanded_bird_facts (bird, cat_wingspan, cat_beak_colour, cat_chest_colour, ...) In which case since you'll almost certainly be throwing away any relational integrity you had in the first case I'd just throw a lightweight wrapper around some dbfile files or similar. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connect to a database in a .sql file
Assad Jarrahian [EMAIL PROTECTED] writes: \c does not work in .sql script run in psql. Nonsense. Try it again, and show us exactly what you did and what message you got, rather than leaping to silly conclusions. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Detect Locked Row Without Blocking
Is there a recommended postgres way to determine if a certain row is locked... without blocking? In my custom postgres client app I'd like to be able to determine if another user is modifying a given record. If so, I would present a dialog to the user such as Record Locked. Sam Smith is already modifying this record. Try again later. I've looked at SELECT FOR UPDATE which looks good except for that it blocks. I don't want my UI to freeze up... I just want to notify the user that it's locked and move on. Any thoughts? Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connect to a database in a .sql file
Assad Jarrahian wrote: \c does not work in .sql script run in psql. That sounds unlikely. What sort of error message are you getting? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Perl::DBI and interval syntax [side question]
MaXX [EMAIL PROTECTED] writes: Can this be the cause of a huge loss of perf? I have the following query in a Perl script using DBI + DBD::Pg, AutoCommit = 0: SELECT stats_put_sources(?, ?, int4(?), int4(?)) This syntax runs almost 10x faster than: SELECT stats_put_sources(?, ?, ?::int4, ?::int4) You probably have no idea how hard that is to believe --- they should certainly be just the same. Let's see a self-contained test case that exhibits this problem. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) [EMAIL PROTECTED] writes: I'm doing a PhD in data mining and I need more than 1600 columns. I don't think so --- consider redesigning your data model instead. For instance, maybe you could combine similar columns into an array. Or split the table into an m:n linking structure. Even coming close to that implementation limit suggests bad SQL design; if we thought it was a realistic problem we would have increased it long ago... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Detect Locked Row Without Blocking
Joe Lester [EMAIL PROTECTED] writes: Is there a recommended postgres way to determine if a certain row is locked... without blocking? 8.1 has a SELECT FOR UPDATE NOWAIT option. Alternatively, just do a wait while having a very short statement_timeout. In my custom postgres client app I'd like to be able to determine if another user is modifying a given record. If so, I would present a dialog to the user such as Record Locked. Sam Smith is already modifying this record. Try again later. However, I think the question is moot because it's predicated on a terrible underlying approach. You should NEVER design a DB app to hold a lock while some user is editing a record (and answering the phone, going out to lunch, etc). Fetch the data and then let the user edit it while you are not in a transaction. When he clicks UPDATE, do BEGIN; SELECT the row FOR UPDATE; check for any changes since you fetched the data originally if none, UPDATE and commit else rollback and tell user about it If you do see conflicting changes, then you have enough info to resolve the conflicts or abandon the update. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: I'm doing a PhD in data mining and I need more than 1600 columns. I got an error message saying that I can not use more than 1600 columns. It is happening because I have to change categorical values to binary creating new columns. Do you know if oracle can handle it? pardon, but as PhD you should be able to do sensible database design. Even if you would have more then 1600 columns, you cannot expect very good performance with it (on nearly any database). I'd strongly recommend to replan your table layout. You can get help here if you provide more information on your plans. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Beyond the 1600 columns limit on windows
Alex Stapleton schrieb: On 8 Nov 2005, at 12:50, Tino Wildenhain wrote: Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I would like to know who on earth needs 1600 columns and even beyond? Hint: you can have practically unlimited rows in your n:m table :-) Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? I guess it eases implementation and there is no reason to go so high on columns either. The limit could even be lower w/o and hurts but 1600 seems skyrocket high enough (read unlimited :-) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Beyond the 1600 columns limit on windows
Tino Wildenhain [EMAIL PROTECTED] writes: Alex Stapleton schrieb: Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? I guess it eases implementation and there is no reason to go so high on columns either. The limit could even be lower w/o and hurts but 1600 seems skyrocket high enough (read unlimited :-) The rationale is laid out in excruciating detail in src/include/access/htup.h: /* * MaxTupleAttributeNumber limits the number of (user) columns in a tuple. * The key limit on this value is that the size of the fixed overhead for * a tuple, plus the size of the null-values bitmap (at 1 bit per column), * plus MAXALIGN alignment, must fit into t_hoff which is uint8. On most * machines the upper limit without making t_hoff wider would be a little * over 1700. We use round numbers here and for MaxHeapAttributeNumber * so that alterations in HeapTupleHeaderData layout won't change the * supported max number of columns. */ #define MaxTupleAttributeNumber 1664/* 8 * 208 */ /*-- * MaxHeapAttributeNumber limits the number of (user) columns in a table. * This should be somewhat less than MaxTupleAttributeNumber. It must be * at least one less, else we will fail to do UPDATEs on a maximal-width * table (because UPDATE has to form working tuples that include CTID). * In practice we want some additional daylight so that we can gracefully * support operations that add hidden resjunk columns, for example * SELECT * FROM wide_table ORDER BY foo, bar, baz. * In any case, depending on column data types you will likely be running * into the disk-block-based limit on overall tuple size if you have more * than a thousand or so columns. TOAST won't help. *-- */ #define MaxHeapAttributeNumber 1600/* 8 * 200 */ regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Beyond the 1600 columns limit on windows
On Tue, 2005-11-08 at 09:45, Tino Wildenhain wrote: Alex Stapleton schrieb: On 8 Nov 2005, at 12:50, Tino Wildenhain wrote: Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I would like to know who on earth needs 1600 columns and even beyond? Hint: you can have practically unlimited rows in your n:m table :-) Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? I guess it eases implementation and there is no reason to go so high on columns either. The limit could even be lower w/o and hurts but 1600 seems skyrocket high enough (read unlimited :-) I'd have to vote with Tino here. Why worry about an arbitrary limit you should never really be approaching anyway. If a table has more than several dozen columns, you've likely missed some important step of normalization. Once you near 100 columns, something is usually horribly wrong. I cannot imagine having a table that actually needed 1600 or more columns. And, Evandro, nothing is free. If someone went to the trouble of removing the limit of 1600, we'd probably pay in some other way, most likely with poor performance. There are other, far more important features to work on, I'd think. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] odbc in postgresql and php
ODBC has nothing to do with this -- you compile postgreSQL support directly intoPHP when you configure PHP just before the install... ./Configure --with-postgres --without-mysql After that -- you need to join a PHP newsgroup for PHP questions ""Bob Powell"" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Hello everyone, Has anyone installed the postgres php driver. I would like to know how to install it on Linux and then what the proper way to access it is from a php web page. I have been unable to find actual code for php and doing this. Please help. Thanks. Bob PowellDatabase Administrator
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's == Evandro's mailing lists (Please, don't send personal messages to this address) [EMAIL PROTECTED] writes: [I would have replied to your personal address, but I'm not about to copy it from a footer.] Evandro's I'm doing a PhD in data mining and I need more than 1600 columns. I got an Evandro's error message saying that I can not use more than 1600 columns. Evandro's It is happening because I have to change categorical values to binary Evandro's creating new columns. Do you know if oracle can handle it? /me boggles You are doing a PhD in data mining, and you have a table that needs more than 1600 columns? /me gasps What are they *teaching* these days? If you have a design that has more than 20 or so columns, you're probably already not normalizing properly. There just aren't *that* many attributes of a object before you should start factoring parts of it out, even if it means creating some 1-1 tables. In programming, if I ever see someone name a sequence of variables, like thing1 and thing2, I know there's going to be trouble ahead, because that should have been a different data structure. Similarly, I bet some of your columns are foo1 and foo2. Signs of brokenness in the design. Or do you really have 1600 *different* attributes, none of which have a number in their name? That requires a serious amount of creativity. :) -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 merlyn@stonehenge.com URL:http://www.stonehenge.com/merlyn/ Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Beyond the 1600 columns limit on windows
On 8 Nov 2005, at 12:50, Tino Wildenhain wrote: Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I would like to know who on earth needs 1600 columns and even beyond? Hint: you can have practically unlimited rows in your n:m table :-) Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Perl::DBI and interval syntax [side question]
Tom Lane wrote: MaXX [EMAIL PROTECTED] writes: Can this be the cause of a huge loss of perf? I have the following query in a Perl script using DBI + DBD::Pg, AutoCommit = 0: SELECT stats_put_sources(?, ?, int4(?), int4(?)) This syntax runs almost 10x faster than: SELECT stats_put_sources(?, ?, ?::int4, ?::int4) You probably have no idea how hard that is to believe --- they should certainly be just the same. Let's see a self-contained test case that exhibits this problem. You're right and I'm stupid again... I found that I've changed from the Pg way to the SQL way *AND* commented a '$dbm-commit;' inside the loop. Removing the comment make the script slow as hell... Thats the only explanation. I may need a lot of rest... Sorry again, -- MaXX ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Beyond the 1600 columns limit on windows
On 8 Nov 2005, at 16:06, Scott Marlowe wrote: On Tue, 2005-11-08 at 09:45, Tino Wildenhain wrote: Alex Stapleton schrieb: On 8 Nov 2005, at 12:50, Tino Wildenhain wrote: Evandro's mailing lists (Please, don't send personal messages to this address) schrieb: Hi guys, I would like to know if it is possible to have more than 1600 columns on windows without recompiling postgres. I would like to know who on earth needs 1600 columns and even beyond? Hint: you can have practically unlimited rows in your n:m table :-) Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? I guess it eases implementation and there is no reason to go so high on columns either. The limit could even be lower w/o and hurts but 1600 seems skyrocket high enough (read unlimited :-) I'd have to vote with Tino here. Why worry about an arbitrary limit you should never really be approaching anyway. If a table has more than several dozen columns, you've likely missed some important step of normalization. Once you near 100 columns, something is usually horribly wrong. I cannot imagine having a table that actually needed 1600 or more columns. And, Evandro, nothing is free. If someone went to the trouble of removing the limit of 1600, we'd probably pay in some other way, most likely with poor performance. There are other, far more important features to work on, I'd think. Oh wait, PG is written in C isn't it. I guess fixed size things are a bit easier to deal with. Pardon me then :) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Beyond the 1600 columns limit on windows
Sorry, It has nothing to do with normalisation. It is a program for scientific applications. Datavalues are broken into column to allow multiple linear regression and multivariate regression trees computations. Even SPSSthe most well-known statistic sw uses the same approach and data structure that my software uses. Probably I should use another data structure but would not be as eficient and practical as the one I use now. Many thanks -Evandro On 08 Nov 2005 05:30:07 -0800, Randal L. Schwartz merlyn@stonehenge.com wrote: Evandro's == Evandro's mailing lists (Please, don't send personal messages to this address) [EMAIL PROTECTED] writes:[I would have replied to your personal address, but I'm not aboutto copy it from a footer.]Evandro's I'm doing a PhD in data mining and I need more than 1600 columns. I got an Evandro's error message saying that I can not use more than 1600 columns.Evandro'sIt is happening because I have to change categorical values to binaryEvandro's creating new columns. Do you know if oracle can handle it? /me bogglesYou are doing a PhD in data mining, and you have a table that needsmore than 1600 columns?/me gaspsWhat are they *teaching* these days?If you have a design that has more than 20 or so columns, you're probably already not normalizing properly.There just aren't *that*many attributes of a object before you should start factoring parts ofit out, even if it means creating some 1-1 tables.In programming, if I ever see someone name a sequence of variables, like thing1 and thing2, I know there's going to be trouble ahead,because that should have been a different data structure.Similarly,I bet some of your columns are foo1 and foo2.Signs of brokenness in the design.Or do you really have 1600 *different* attributes, none of which havea number in their name?That requires a serious amount ofcreativity. :)--Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 merlyn@stonehenge.com URL:http://www.stonehenge.com/merlyn/Perl/Unix/security consulting, Technical writing, Comedy, etc. etc. See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!-- Evandro M Leite JrPhD Student Software developer University of Southampton, UKPersonal website: http://evandro.orgAcademic website: http://www.soton.ac.uk/~evandroPlease, use Jr(at)evandro.org for personal messages
[GENERAL] Number of items in a cursor...
Is there any way to get the numbers of items inside a cursor? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ident client authentication
Many thanks for your reply. The web server runs as what/whoever it does by default. I haven't changed it. I understand what you are getting at, because Richard Huxton pointed out to me that the script is probably running as the web server user. The script runs as 'apache' (let us say), the connect in the python program is by 'jim'. Then what does sameuser mean in the hba file. Paul HideOn 11/8/05, Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, Nov 08, 2005 at 10:02:14 +,Paul Hide [EMAIL PROTECTED] wrote: I have a problem with ident client authentication. My server is debian sarge, pg version is 7.4.7, apache 2.0.54, mod_python 2.3. A python script is placed on the server and runs under mod_python in apache. I make a request from firefox on a win 2k box to this server. If I have local all jim trust in my pg_hba.conf file, all is well, my python script runs and delivers the expected output. However, if i have local all jim ident sameuser I get FATAL: IDENT authentication failed for user jim, via mod-python debug and in postgres log. jim is both a unix user (linux) and a postgres user. Is this what would be expected?Does the web server run as user jim? That seems unusual. You may need to set up an ident map that allows user 'apache' (or whatever applies on your machine)to connect as postgres user 'jim'. How can I make ident authentication work? Any help would be appreciated, including where this message should be posted if this list is inappropriate. Paul Hide
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1.0 Release Candidate 1
Can I be removed from this mailing list. Thanks Gerard -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marc G. Fournier Sent: 31 October 2005 04:48 To: pgsql-announce@postgresql.org Cc: pgsql-general@postgresql.org Subject: [ANNOUNCE] PostgreSQL 8.1.0 Release Candidate 1 After a couple of months of testing, and alot of bug reports (with fixes), we are pleased to announce the first Release Candidate of PostgreSQL 8.1.0. As with all pre-releases, but especially now that we are in the final stretch, testing is paramount to a successful, and bug free, release. As such, we ask everyone able who is able to do so to, to run RC1 through its paces and report any bugs to us through [EMAIL PROTECTED] At this time, our plans for full release are the week of November 7th, but this depends on the success of this Release Candidate. To download via FTP, please go to: http://www.postgresql.org/ftp/source/v8.1beta For RPMs, please visit: http://developer.postgresql.org/~devrim/rpms/8.1/rc1 Windows Binaries to be announced shortly. Marc G. Fournier PostgreSQL Core Group ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ident client authentication
Thanks for the reply. Is this right then? For ident to work could I, as Bruno Wolff III suggested, map the apache user to jim using pg_ident. I suppose if I do that it might be rather risky from a security point of view. Since any script running would then run as jim. Perhaps I shoulkd do as you suggested and use password authentication. Paul HideOn 11/8/05, Richard Huxton dev@archonet.com wrote: Paul Hide wrote: I have a problem with ident client authentication. My server is debian sarge, pg version is 7.4.7, apache 2.0.54, mod_python 2.3. A python script is placed on the server and runs under mod_python in apache. However, if i have local all jim ident sameuser I get FATAL: IDENT authentication failed for user jim, via mod-python debug and in postgres log. jim is both a unix user (linux) and a postgres user. Is this what would be expected? How can I make ident authentication work?At a guess, your Python script is running as the same user as yourwebserver (usually apache/www-data/nobody or similar). This is why the ident sameuser isn't working.You'll either need to log in as the webserver user, or use passwordauthentication. Any help would be appreciated, including where this message should be posted if this list is inappropriate.This list is fine.HTH-- Richard Huxton Archonet Ltd
Re: [GENERAL] Programmatic method to determine currently installed Windows PostrgreSQL version
Thanks Richard, I will look into whether or not I will have access to the SELECT statement. Regards, Will --- Richard Huxton dev@archonet.com wrote: Will Wright wrote: Hi Magnus, thanks for the quick response. Unfortunately I still have an issue with this regsitry identification method because I'd like to code my installer so that it can also identify the postreSQL versions that were not available at the time I authored my install check, i.e. future versions. You could issue a SELECT version() - that is about as definitive as you can get. Don't forget you'll need to cope with the case where a user has 2 or more versions of PostgreSQL running on the same machine. I'm not sure it's safe to assume only one installation. -- Richard Huxton Archonet Ltd __ Start your day with Yahoo! - Make it your home page! http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ident client authentication
On Tue, Nov 08, 2005 at 17:18:32 +, Paul Hide [EMAIL PROTECTED] wrote: Many thanks for your reply. The web server runs as what/whoever it does by default. I haven't changed it. I understand what you are getting at, because Richard Huxton pointed out to me that the script is probably running as the web server user. The script runs as 'apache' (let us say), the connect in the python program is by 'jim'. Then what does sameuser mean in the hba file. That is a special mapping that says that ident should return the same string as is used for the postgres username. Based on your description above, they aren't going to match. However, you can create custom ident maps that allow either apache or jim to connect as postgres user jim. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Beyond the 1600 columns limit on windows
Well this screams random arbitrary limit to me. Why does this limit exist? What ever happened to the holy 0,1,infinity triumvirate? I guess it eases implementation and there is no reason to go so high on columns either. The limit could even be lower w/o and hurts but 1600 seems skyrocket high enough (read unlimited :-) It is probably what fits in a single block. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) wrote: Sorry, It has nothing to do with normalisation. It is a program for scientific applications. It has everything to do with normalisation. You appear to be pushing application presentation issues into the structure of your database. If SQL allowed you, this would break 1NF. Data values are broken into column to allow multiple linear regression and multivariate regression trees computations. Sounds like you want an array then (or perhaps several arrays). Even SPSS the most well-known statistic sw uses the same approach and data structure that my software uses. Ah - and they've made a good choice? Probably I should use another data structure but would not be as eficient and practical as the one I use now. The structure you use inside your application and the data definition used by the database are two separate things. You presumably are doing some transformation of data on fetching it anyway - I'd switch rows-columns over then. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connect to a database in a .sql file
Sorry, I am a linux/OS X person. Was working on PGADMIN in windows and thought their query tool was the same as psql. Just ran the script in psql on my linux box and it worked. My apologies. Tom, I am not leaping to silly conclusions. Calm down please. We are all trying to learn from people with knowledge. Be curtious. Thanks. -assad On 11/8/05, Richard Huxton dev@archonet.com wrote: Assad Jarrahian wrote: \c does not work in .sql script run in psql.That sounds unlikely. What sort of error message are you getting?-- Richard Huxton Archonet Ltd
Re: [GENERAL] Connect to a database in a .sql file
Assad Jarrahian wrote: Sorry, I am a linux/OS X person. Was working on PGADMIN in windows and thought their query tool was the same as psql. Nope - psql is psql, pgadmin is pgadmin. All the backslash commands are psql-only rather than being part of the backend. Of course, pgadmin can duplicate them if they want. Just ran the script in psql on my linux box and it worked. And on Windows too. Not sure if you need to change the line-endings though. My apologies. Tom, I am not leaping to silly conclusions. Calm down please. We are all trying to learn from people with knowledge. Be curtious. The problem is Assad that your email saying \c doesn't work in psql is now in the archive forever. In a couple of days Google will index it and then the world at large will be coming across it. And it's not worded as a question I can't get \c to work in psql - just says it doesn't work, so it might be that a questioner doesn't look any further, assuming you were right. It's not the leaping to a conclusion that's done the damage, and it's not the instant archiving, but the combination of the two. Of course Tom could have been a little more gentle in his dressing down, but since I'd guess he's been working pretty hard recently getting 8.1 out of the door (released today!) I'm prepared to cut him some slack. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Programmatic method to determine currently installed Windows PostrgreSQL version
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\{317D0ED1
-8845-40DD-A028-0A3EB8E24F2E}
Version=8.1-beta4
So it looks like the version number is underneath
a key that
changes with every new version (new Product
number). And I
am therefore not sure if I can use this knowledge
to identify
the installed version.
It changes once for each major version, meaning there is
one for 8.0
and 8.1. The whole 8.0.x series share the same id, as will
the whole
8.1 series (at least that's the plan). It basically changes when
initdb is required to upgrade, which is why it also changes between
betas.
You can safely rely on these once you've seen the ones for
a release.
For example, don't use the beta4 one, wait for the
8.1 release one.
Does anyone have a better idea for a more reliable
identification method?
Using these registry keys will reliably identify any PostgreSQL
installations done using the MSI installer - it won't pick up any
other install methods. For those, you could either search the
filesystem, or attempt a connect to the default port etc -
but I think
those would either be too slow or a lot less reliable.
Hi Magnus, thanks for the quick response.
Unfortunately I still have an issue with this regsitry
identification method because I'd like to code my installer
so that it can also identify the postreSQL versions that were
not available at the time I authored my install check, i.e.
future versions.
In essence I know that my product works with PostrgeSQL 8.0
and 8.1 and it my guees is that it will continue to work with
future PostgreSQL versions. So I'd like my installer to be
able to check that 8.0 or above is installed.
To do this, enumerate the keys under PostgreSQL\Installations. You can
count on future versions to register there with a different GUID - so if
you enumerate everything that's there, you can look at the Version value
to see the actual version.
//Magnus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Detect Locked Row Without Blocking
I see... For my purposes, I'd still rather notify the user up-front that the record is in modify (kind of like FileMaker does)... even though now I understand that a row lock is not the right mechanism for that. Is there a best-practice for this approach? What about using a field to flag the record as in modify? But I guess then you'd have to protect against two different users selecting/updating the field at roughly the same time, each user then thinking that he has gained modify privileges for that record. I'm not sure a row lock would help any in this circumstance... and I don't want to resort to table locks for performance reasons. On Nov 8, 2005, at 10:14 AM, Tom Lane wrote: Joe Lester [EMAIL PROTECTED] writes: In my custom postgres client app I'd like to be able to determine if another user is modifying a given record. If so, I would present a dialog to the user such as Record Locked. Sam Smith is already modifying this record. Try again later. However, I think the question is moot because it's predicated on a terrible underlying approach. You should NEVER design a DB app to hold a lock while some user is editing a record (and answering the phone, going out to lunch, etc). Fetch the data and then let the user edit it while you are not in a transaction. When he clicks UPDATE, do BEGIN; SELECT the row FOR UPDATE; check for any changes since you fetched the data originally if none, UPDATE and commit else rollback and tell user about it If you do see conflicting changes, then you have enough info to resolve the conflicts or abandon the update. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Connect to a database in a .sql file
Got it. I will be more careful with words. Thanks tom for getting 8.1! one more question. Where does the directory lie for psql (so I can put a .sql file in there and run it) for windows? thanks. -assad On 11/8/05, Richard Huxton dev@archonet.com wrote: Assad Jarrahian wrote: Sorry, I am a linux/OS X person. Was working on PGADMIN in windows and thought their query tool was the same as psql.Nope - psql is psql, pgadmin is pgadmin. All the backslash commands are psql-only rather than being part of the backend. Of course, pgadmin canduplicate them if they want. Just ran the script in psql on my linux box and it worked.And on Windows too. Not sure if you need to change the line-endings though. My apologies. Tom, I am not leaping to silly conclusions. Calm down please. We are all trying to learn from people with knowledge. Be curtious.The problem is Assad that your email saying \c doesn't work in psql is now in the archive forever. In a couple of days Google will index it andthen the world at large will be coming across it. And it's not worded asa question I can't get \c to work in psql - just says it doesn't work, so it might be that a questioner doesn't look any further, assuming youwere right.It's not the leaping to a conclusion that's done the damage, and it'snot the instant archiving, but the combination of the two. Of course Tom could have been a little more gentle in his dressing down,but since I'd guess he's been working pretty hard recently getting 8.1out of the door (released today!) I'm prepared to cut him some slack. -- Richard Huxton Archonet Ltd
[GENERAL] upgrading from backend version 811 to 812
Hi, is there any way to upgrade an 811 backend version cluster to the current 812 version? Any don't try at home-type tricks? I'm willing to risk cluster corruption, because this is just a test database (but it is rather large). thanks, alex ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Beyond the 1600 columns limit on windows
Evandro's mailing lists (Please, don't send personal messages to this address) wrote: It has nothing to do with normalisation. It is a program for scientific applications. Data values are broken into column to allow multiple linear regression and multivariate regression trees computations. Having done similar things in the past, I wonder if your current DB design includes a column for every feature-value combination: instanceID color=red color=blue color=yellow ... height=71 height=72 - 42 True False False 43 False TrueFalse 44 False False True ... This is likely to be extremely sparse, and you might use a sparse representation accordingly. As several folks have suggested, the representation in the database needn't be the same as in your code. Even SPSS the most well-known statistic sw uses the same approach and data structure that my software uses. Probably I should use another data structure but would not be as eficient and practical as the one I use now. The point is that, if you want to use Postgres, this is not in fact efficient and practical. In fact, it might be the case that mapping from a sparse DB representation to your internal data structures is =more= efficient than naively using the same representation in both places. - John D. Burger MITRE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Setting max_fsm_pages
You might have to bump up shmmax, but fsm is completely unrelated to shared_buffers. On Mon, Nov 07, 2005 at 02:55:41PM -0500, Carlos Oliva wrote: Hi Jim, Thank you for your help. We are going to increase the max_fxm_pages according to the test I have been running through out the week. If we increase the max_fsm_pages, do we need to bump up the shared_buffers and the size of the shared memory segment of the Linux kernel(shmmax)? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: Monday, November 07, 2005 2:38 PM To: Carlos Oliva Cc: 'Tom Lane'; pgsql-general@postgresql.org Subject: Re: [GENERAL] Setting max_fsm_pages On Sun, Nov 06, 2005 at 08:05:29PM -0500, Carlos Oliva wrote: Thank you for your response Tom. Should I set the max_fsm_pages to the total pages needed obtained from a full vacuum or from a analize vacuum? When I run a vacuum analyze (vacuumdb -z -v), I get a smaller number of pages needed than when I run a full vacuum with analyze (vacuumdb -f -z -v) There shouldn't be any difference because of analyze. But remember that as the tables change in size (as well as in the number of dead tuples), total pages needed can change. For example, if you run a vacuum immediately after a vacuum full on a system with no other activity, you'll get: INFO: free space map: 0 relations, 0 pages stored; 0 total pages needed That's because there's no dead space to be reclaimed. Your best bet is to do a vacuum verbose (vacuumdb -v) after the database has been running for a while using whatever vacuuming scheme you're going to use (such as pg_autovacuum). That will give you a pretty good estimate of how many pages you really need. Even that's not 100% reliable though, so you still need to include extra space as a safety margin. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] autovacuum,8.1, Win
On Tue, Nov 08, 2005 at 10:50:53AM +0100, Zlatko Mati? wrote: What needs to be configured in order autovacuum process be active? http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Best way to use indexes for partial match at beginning
Well, for starters, see if PostgreSQL is currently using any indexes via EXPLAIN. First rule of performance tuning: don't. If it's not (which is probably the case), then your best bet is to create functional indexes; ie: CREATE INDEX mytable__col1_4 ON mytable( substring( col1 for 4 ) ); You can then either SELECT ... WHERE substring( col1 for 4 ) = blah or SELECT ... WHERE substring( col1 for 4 ) LIKE 'bla%' Though that last one might not use the index; you'll have to check and see. Also, keep in mind that PostgreSQL doesn't store CHAR the same as most other databases; the internal storage is the same as what's used for VARCHAR and TEXT. On Sun, Nov 06, 2005 at 11:03:01PM +0200, Andrus Moor wrote: I have 10 CHARACTER columns in table mytable. Mytable is indexed by some of those columns. CREATE TABLE mytable ( col1 CHARACTER(10), col2 CHARACTER(10),col3 CHARACTER(10),col4 CHARACTER(10),col5 CHARACTER(10),col6 CHARACTER(10),col7 CHARACTER(10),col8 CHARACTER(10),col9 CHARACTER(10), col10 CHARACTER(10) ); CREATE INDEX i1 ON mytable(col1); CREATE INDEX i2 ON mytable(col2); I need to select records by knowing some characters from beginning. I know always 1-10 first characters of col1. So my LIKE pattern starts always with constant characters and ends with % . I can use LIKE: SELECT * FROM mytable WHERE col1 LIKE 'A%' AND col2 LIKE 'BC%' AND col3 LIKE 'DEF%' AND col4 LIKE 'G%'; or substring(): SELECT * FROM mytable WHERE substring(col1 for 1)='A' AND substring(col2 for 2)= 'BC' AND substring(col3 for 3)='DEF' AND substring(col4 for 1) ='G'; Can Postgres 8.1 use indexes to speed the queries above ? Which is the best way to to write the where clause in this case so that index is used ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] upgrading from backend version 811 to 812
811? 812? We don't have anything close to those version numbers... If you're upgrading to 8.1 you need to dump/reload, unless you're comming from a recent RC or beta. You *might* be able to get away with a simple drop-in upgrade in that case. On Tue, Nov 08, 2005 at 08:25:31PM +0100, Alex Mayrhofer wrote: Hi, is there any way to upgrade an 811 backend version cluster to the current 812 version? Any don't try at home-type tricks? I'm willing to risk cluster corruption, because this is just a test database (but it is rather large). thanks, alex ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Setting max_fsm_pages
As we're talking about fsm, I've got a question I've been asking myself for some time : If a cluster is restarted, is the fsm forgotten ? (so does it mean one should run a vacuum as soon as the database is restarted ?) Le Dimanche 06 Novembre 2005 03:44, Carlos Oliva a écrit : Should I set the max_fsm_pages to the value reported (vacuum verbose) as pages stored or the value reported as total pages needed? I ran full + analyze vacuums in my database (vacuumdb -f -z -v database name) a couple of times and I got the following reports: INFO: free space map: 454 relations, 22274 pages stored; 147328 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory And INFO: free space map: 454 relations, 22242 pages stored; 147328 total pages needed DETAIL: Allocated FSM size: 1000 relations + 2 pages = 178 kB shared memory I am not sure if I need to raise the max_fsm_pages to something larger than 22242 (e.g. 4) or larger than 147,328. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] autovacuum,8.1, Win
Zlatko Matić wrote: What needs to be configured in order autovacuum process be active? I assume you are talking about the Windows version. If so, and if you used the installer, then you don't need to do anything. It appears (at least on my RC1 install) that autovacuum is enabled by default. Matt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] upgrading from backend version 811 to 812
Jim C. Nasby wrote: 811? 812? We don't have anything close to those version numbers... I'm well aware that PostgreSQL itself is currently at 8.1.0 - however, i'm upgrading from 8.1beta2, and it seems to me that the backend version was modified between those two versions - 811 and 812 seem to be backend version identifiers which are reported when i'm trying to start the 8.1.0 postmaster on the 8.1beta2 cluster: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 811, but the server was compiled with PG_CONTROL_VERSION 812. HINT: It looks like you need to initdb. If you're upgrading to 8.1 you need to dump/reload, unless you're comming from a recent RC or beta. You *might* be able to get away with a simple drop-in upgrade in that case. tried that - does not work. That's why i'm asking... thanks, Alex ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] autovacuum,8.1, Win
Correct, the default setting for PostgreSQL 8.1 W32 value in postgresql.conf is 'autovacuum' to 'on'. You can see this and more settings in pgAdmin III by visiting 'Tools', 'Server Configuration', then the config file of your choice. Now, if only I could setup my home to autovaccum. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Matthew T. O'Connor Sent: Tuesday, November 08, 2005 12:05 PM To: Zlatko Matić Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] autovacuum,8.1, Win Zlatko Matić wrote: What needs to be configured in order autovacuum process be active? I assume you are talking about the Windows version. If so, and if you used the installer, then you don't need to do anything. It appears (at least on my RC1 install) that autovacuum is enabled by default. Matt ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting max_fsm_pages
Marc Cousin [EMAIL PROTECTED] writes: If a cluster is restarted, is the fsm forgotten ? Given a normal postmaster shutdown, no. In a crash-restart situation, yes. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] upgrading from backend version 811 to 812
Alex Mayrhofer [EMAIL PROTECTED] writes: Jim C. Nasby wrote: 811? 812? We don't have anything close to those version numbers... I'm well aware that PostgreSQL itself is currently at 8.1.0 - however, i'm upgrading from 8.1beta2, and it seems to me that the backend version was modified between those two versions - 811 and 812 seem to be backend version identifiers which are reported when i'm trying to start the 8.1.0 postmaster on the 8.1beta2 cluster: FATAL: database files are incompatible with server DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 811, but the server was compiled with PG_CONTROL_VERSION 812. Those are internal version numbers that no one normally ever sees, and certainly no one thinks about. You shouldn't try to outsmart us by referring to versions in other than the standard terms. The short answer, though, is that if the postmaster won't restart then you need a dump with the old code and reload with the new. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] autovacuum,8.1, Win
Wes Williams wrote: Correct, the default setting for PostgreSQL 8.1 W32 value in postgresql.conf is 'autovacuum' to 'on'. You can see this and more settings in pgAdmin III by visiting 'Tools', 'Server Configuration', then the config file of your choice. Now, if only I could setup my home to autovaccum. Sounds like what you need is a roomba: http://www.irobot.com/sp.cfm?pageid=122 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Connect to a database in a .sql file
Assad Jarrahian wrote: I converted your HTML message to plain text for you. Where does the directory lie for psql (so I can put a .sql file in there and run it) for windows? thanks. It's in the bin directory underneath wherever you install PostgreSQL. But you don't need to put your .sql script there. Either put the bin directory in your path, or pass the full path to your .sql script to psql. -- Guy Rouillier ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connect to a database in a .sql file
On Tue, 2005-11-08 at 07:31, Assad Jarrahian wrote: Hi, Lets say the script is called myDBSetup.sql And the script contains: //CREATE DATABASE section //CREATE USERS SECTION //COnnect to db //CREATE TABLES, FUNCTIONS etc. this script will be called from psql. The user will log connect to template1 and then run my script. What I really need is after the CREATE DB, I need to switch from template1 to the database name (so the CREATE tables etc will correspond to the right db). This has to be automated and done within the script. \c does not work in .sql script run in psql. Any suggestions would be helpful. Thanks. -assad As others have mentioned \c works from sql script, small example follows -- test.sql CREATE database test3; \c test3 CREATE TABLE test_table ( field1 integer, field2 varchar(10)); INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1'); INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2'); SELECT * FROM test_table; -- Cut [EMAIL PROTECTED] projects]$ psql -a -f test.sql template1 CREATE database test3; CREATE DATABASE \c test3 You are now connected to database test3. CREATE TABLE test_table ( field1 integer, field2 varchar(10)); CREATE TABLE INSERT INTO test_table (field1, field2) VALUES (1,'VALUE1'); INSERT 25513 1 INSERT INTO test_table (field1, field2) VALUES (2,'VALUE2'); INSERT 25514 1 SELECT * FROM test_table; field1 | field2 + 1 | VALUE1 2 | VALUE2 (2 rows) -- Sigurdur ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Perl::DBI and interval syntax
On Nov 8, 2005, at 8:16 AM, Greg Sabino Mullane wrote:
This is correct. Though generally not recommeded, you can switch it
off
with the pg_server_prepare attribute like so:
$dbh-{pg_server_prepare} = 0;
This will force DBD::Pg to do the quoting itself, with the subsequent
penalty of speed and loss of auto type casting.
And a reduction in bugs... :-(
One I found the other day: if you set $dbh-{InactiveDestroy} it
still destroys all of your prepared statements. Context is when you
fork a child which needs to open its own connection, and the parent's
prepared statements go away.
Need to find some tuits to file the formal bug report.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] upgrading from backend version 811 to 812
Tom Lane wrote: DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 811, but the server was compiled with PG_CONTROL_VERSION 812. Those are internal version numbers that no one normally ever sees, and certainly no one thinks about. You shouldn't try to outsmart us by referring to versions in other than the standard terms. sorry, that was of course unintentional. I regret that i haven't been more precise in my first message. The short answer, though, is that if the postmaster won't restart then you need a dump with the old code and reload with the new. ok, thanks. cheers alex ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Troubles with array_ref
Hi, sorry for the question but I still having serious troubles with the
array_ref function. The function is not documented and I can't get a useful
example inside the contrib directory. The function is defined as:
Datum array_ref(ArrayType *array, int nSubscripts, int *indx,
int arraylen, int elmlen, bool elmbyval, char elmalign,
bool *isNull);
I guess nSubscripts is the number of dimensions of the array and indx is
the index number of the element I want to get; arraylen I guess is the
length of the ArrayType structure and I also guess that if ArrayType is a
varlena element I could get it with VARSIZE() [if that is wrong somebody
could tell me how to get that info?]; elmlen I guess is the size of any of
the members of the array; elmbyval and elmalign are the passed by val and
align properties of each of the elements in the array; and of course isNull
is just to show if the array could have null values or not. [again, if any
of these asserts are false then please correct me and I will try to document
it as soon as possible].
Well, anyway, this is the Stored Function I've been workin on; it simply
take an array and an integer just to return this item from the array; The
array could have any kind of elements so I declare it as anyarray (the
parameter) and anyelement (the return value), please help me, I don't know
where to get info about it.
= THIS IS THE FUNCTION ==
PG_FUNCTION_INFO_V1(test);
Datum
test(PG_FUNCTION_ARGS)
{
ArrayType *v = PG_GETARG_ARRAYTYPE_P(1);
Datum element;
Oidarray_type = ARR_ELEMTYPE(PG_GETARG_ARRAYTYPE_P(1));
int16 typlen;
bool typbyval;
char typalign;
inti = PG_GETARG_INT32(0);
get_typlenbyvalalign(array_type, typlen, typbyval, typalign);
element = array_ref(v, 1, i, VARSIZE(v), typlen, typbyval,
typalign, false);
PG_RETURN_DATUM(element);
}
= THIS IS THE DECLARATION IN SQL =
CREATE OR REPLACE FUNCTION test(integer, anyarray) RETURNS anyelement AS
'test.so' LANGUAGE 'C' STABLE;
AND THIS IS THE ERROR ===
SELECT test(1, array[1,2,3]);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: WARNING:
terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exite
d abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
Failed.
Thanks a lot for your help...
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] Troubles with array_ref
Cristian Prieto [EMAIL PROTECTED] writes: Well, anyway, this is the Stored Function I've been workin on; it simply take an array and an integer just to return this item from the array; The array could have any kind of elements so I declare it as anyarray (the parameter) and anyelement (the return value), please help me, I don't know where to get info about it. You could save yourself a lot of time if you enabled warnings from your C compiler (eg, -Wall for gcc) and then paid some attention to them. The last parameter to array_ref is a bool *, not a bool, and I have no doubt that the backend is crashing while trying to dereference false. (Another problem is that the fourth parameter should be -1 not VARSIZE.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Transactions, Triggers and Error Messages
Hi all, Sorry for the trouble but I am trying to use triggers inside transactions to perform some multiplicity checking on the data inserted onto the tables but I am having some problems retrieving the error message. I have two tables declared as follows: create table pers ( pid int not null primary key, pname text not null ); create table tasks ( taskid serial not null primary key, pid int not null constraint tasks__ref_p references pers deferrable, task text not null ); My function and trigger are as below: create function check_mult() returns trigger as $$ declare cnt integer := 0; begin select count(*) into cnt from tasks where pid=new.pid; if cnt2 then raise exception '3 tasks already exists for person with pid %', new.pid; end if; return new; end; $$ language plpgsql; create trigger ass_mult before insert or update on tasks for each row execute procedure check_mult(); Now when I run the following (after inserting person with id=5 into pers table): begin; set constraints all deferred; insert into tasks (pid, task) values (5, 'firstTask'); insert into tasks (pid, task) values (5, 'secondTask'); insert into tasks (pid, task) values (5, 'thirdTask'); insert into tasks (pid, task) values (5, 'forthTask'); insert into tasks (pid, task) values (5, 'fifthTask'); commit; the transaction is correctly aborted but the error message I get is not the one that raised the exception ie "3 tasks already exists for person with pid 5" but the one from trying to insert the fifth task (ie last statement) which is "ERROR: current transaction is aborted, commands ignored until end of transaction block" Is there anyway I can stop the transaction after the exception is raised so that I can retrieve the correct error message? I know rollbacks inside triggers are simply ignored (I tried that) but I thought maybe there was another way. Thank you in advance for any help, Ledina PS My transactions have to be deferred by that shouldn't make a difference
Re: [GENERAL] Transactions, Triggers and Error Messages
Ledina Hido [EMAIL PROTECTED] writes: Now when I run the following (after inserting person with id=5 into pers table): begin; set constraints all deferred; insert into tasks (pid, task) values (5, 'firstTask'); insert into tasks (pid, task) values (5, 'secondTask'); insert into tasks (pid, task) values (5, 'thirdTask'); insert into tasks (pid, task) values (5, 'forthTask'); insert into tasks (pid, task) values (5, 'fifthTask'); commit; the transaction is correctly aborted but the error message I get is not the one that raised the exception ie 3 tasks already exists for person with pid 5 but the one from trying to insert the fifth task (ie last statement) which is ERROR: current transaction is aborted, commands ignored until end of transaction block What are you running this in? ISTM this is a problem with bad structure of client-side code, not something to be fixed on the server side. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Transactions, Triggers and Error Messages
Quoting Tom Lane [EMAIL PROTECTED]: What are you running this in? ISTM this is a problem with bad structure of client-side code, not something to be fixed on the server side. regards, tom lane I'm using pgAdmin3. Basically when I run the query the first time it gives the correct error but if I re-run it, then it says ERROR: current transaction is aborted, commands ignored until end of transaction block. Should I be doing something else, ie should I be explicitly rolling back once the exception is raised so I don't get this error? And if so how can I catch the exception. Sorry for my ignorance :( ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Beyond the 1600 columns limit on windows
On Tue, Nov 08, 2005 at 02:14:58PM -0500, John D. Burger wrote: Evandro's mailing lists (Please, don't send personal messages to this address) wrote: It has nothing to do with normalisation.? It is a program for scientific applications. Data?values are broken into column to allow multiple linear regression and multivariate regression trees computations. Having done similar things in the past, I wonder if your current DB design includes a column for every feature-value combination: instanceID color=red color=blue color=yellow ... height=71 height=72 - 42 True False False 43 False TrueFalse 44 False False True ... This is likely to be extremely sparse, and you might use a sparse representation accordingly. As several folks have suggested, the representation in the database needn't be the same as in your code. Even SPSS?the most well-known statistic sw uses the same approach and data structure that my software uses. Probably I should use another data structure but would not be as eficient and practical as the one I use now. The point is that, if you want to use Postgres, this is not in fact efficient and practical. In fact, it might be the case that mapping from a sparse DB representation to your internal data structures is =more= efficient than naively using the same representation in both places. s/Postgres/just about any database/ BTW, even if you're doing logic in the database that doesn't mean you have to stick with the way you're representing things. There's ways to get the same info via conventional SQL that doesn't involve building a huge crosstab. Something interesting is that the data structure presented here looks a hell of a lot like a bitmap index, something new in 8.1 (well, at least bitmap index scans). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting max_fsm_pages
On Tue, Nov 08, 2005 at 03:36:40PM -0500, Tom Lane wrote: Marc Cousin [EMAIL PROTECTED] writes: If a cluster is restarted, is the fsm forgotten ? Given a normal postmaster shutdown, no. In a crash-restart situation, yes. Does that include restarts due to things like failed asserts and kill -9'ing a backend? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Transactions, Triggers and Error Messages
On Tue, Nov 08, 2005 at 11:03:50PM +, Ledina Hido wrote: Quoting Tom Lane [EMAIL PROTECTED]: What are you running this in? ISTM this is a problem with bad structure of client-side code, not something to be fixed on the server side. regards, tom lane I'm using pgAdmin3. Basically when I run the query the first time it gives the correct error but if I re-run it, then it says ERROR: current transaction is aborted, commands ignored until end of transaction block. Should I be doing something else, ie should I be explicitly rolling back once the exception is raised so I don't get Yes. this error? And if so how can I catch the exception. Sorry for my http://lnk.nu/postgresql.org/5sl.html -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Transactions, Triggers and Error Messages
That's great. Thank you very much for you help.LedinaOn Tue, Nov 08, 2005 at 11:03:50PM +, Ledina Hido wrote: Quoting Tom Lane [EMAIL PROTECTED]: What are you running this in? ISTM this is a problem with bad structure of client-side code, not something to be fixed on the server side. regards, tom lane I'm using pgAdmin3. Basically when I run the query the first time it gives the correct error but if I re-run it, then it says "ERROR: current transaction is aborted, commands ignored until end of transaction block". Should I be doing something else, ie should I be explicitly rolling back once the exception is raised so I don't get Yes. this error? And if so how can I "catch" the exception. Sorry for my http://lnk.nu/postgresql.org/5sl.html
Re: [GENERAL] Programmatic method to determine currently installed Windows PostrgreSQL version
Thanks Magnus. Much appreciated.
Will
--- Magnus Hagander [EMAIL PROTECTED] wrote:
HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\{317D0ED1
-8845-40DD-A028-0A3EB8E24F2E}
Version=8.1-beta4
So it looks like the version number is
underneath
a key that
changes with every new version (new Product
number). And I
am therefore not sure if I can use this
knowledge
to identify
the installed version.
It changes once for each major version, meaning
there is
one for 8.0
and 8.1. The whole 8.0.x series share the same
id, as will
the whole
8.1 series (at least that's the plan). It
basically changes when
initdb is required to upgrade, which is why it
also changes between
betas.
You can safely rely on these once you've seen
the ones for
a release.
For example, don't use the beta4 one, wait for
the
8.1 release one.
Does anyone have a better idea for a more
reliable
identification method?
Using these registry keys will reliably identify
any PostgreSQL
installations done using the MSI installer - it
won't pick up any
other install methods. For those, you could
either search the
filesystem, or attempt a connect to the default
port etc -
but I think
those would either be too slow or a lot less
reliable.
Hi Magnus, thanks for the quick response.
Unfortunately I still have an issue with this
regsitry
identification method because I'd like to code my
installer
so that it can also identify the postreSQL
versions that were
not available at the time I authored my install
check, i.e.
future versions.
In essence I know that my product works with
PostrgeSQL 8.0
and 8.1 and it my guees is that it will continue
to work with
future PostgreSQL versions. So I'd like my
installer to be
able to check that 8.0 or above is installed.
To do this, enumerate the keys under
PostgreSQL\Installations. You can
count on future versions to register there with a
different GUID - so if
you enumerate everything that's there, you can look
at the Version value
to see the actual version.
//Magnus
__
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Transactions, Triggers and Error Messages
On Tue, Nov 08, 2005 at 11:03:50PM +, Ledina Hido wrote: Quoting Tom Lane [EMAIL PROTECTED]: What are you running this in? ISTM this is a problem with bad structure of client-side code, not something to be fixed on the server side. regards, tom lane I'm using pgAdmin3. Basically when I run the query the first time it gives the correct error but if I re-run it, then it says "ERROR: current transaction is aborted, commands ignored until end of transaction block". Should I be doing something else, ie should I be explicitly rolling back once the exception is raised so I don't get Yes. this error? And if so how can I "catch" the exception. Sorry for my http://lnk.nu/postgresql.org/5sl.htmlThinking about it, the EXCEPTION statement would be inside my user-defined function (where I raise the exception in the first place), so I cannot see how that would help. As far as I could understand, I cannot call "ROLLBACK" (which is what I want to do) inside a user defined function. I tried calling it and it was simply ignored. Or am I missing something here?
[GENERAL] clustering by partial indexes
This might have been discussed before but I wanted to know if clustering tables by partial indexes will be availble in a later release of pgSQL? For the record, this is the error I get in 8.1: iprism=# cluster hrs_idx on report; ERROR: cannot cluster on partial index hrs_idx hrs_idx is defined as: iprism=# \d hrs_idx Index public.hrs_idx Column | Type +-- stamp | timestamp with time zone btree, for table public.report, predicate (thehour(stamp) = 0::double precision AND thehour(stamp) = 23::double precision) -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Transactions, Triggers and Error Messages
Hi, I am trying to use triggers inside transactions to perform some multiplicity checking on the data inserted onto the tables but I am having some problems retrieving the error message. I have two tables declared as follows: create table pers ( pid int not null primary key, pname text not null ); create table tasks ( taskid serial not null primary key, pid int not null constraint tasks__ref_p references pers deferrable, task text not null ); My function and trigger are as below: create function check_mult() returns trigger as $$ declare cnt integer := 0; begin select count(*) into cnt from tasks where pid=new.pid; if cnt2 then raise exception '3 tasks already exists for person with pid %', new.pid; end if; return new; end; $$ language plpgsql; create trigger ass_mult before insert or update on tasks for each row execute procedure check_mult(); Now when I run the following (after inserting person with id=5 into pers table): begin; set constraints all deferred; insert into tasks (pid, task) values (5, 'firstTask'); insert into tasks (pid, task) values (5, 'secondTask'); insert into tasks (pid, task) values (5, 'thirdTask'); insert into tasks (pid, task) values (5, 'forthTask'); insert into tasks (pid, task) values (5, 'fifthTask'); commit; the transaction is correctly aborted but the error message I get is not the one that raised the exception ie 3 tasks already exists for person with pid 5 but the one from trying to insert the fifth task (ie last statement) which is ERROR: current transaction is aborted, commands ignored until end of transaction block Is there anyway I can stop the transaction after the exception is raised so that I can retrieve the correct error message? I know rollbacks inside triggers are simply ignored (I tried that) but I thought maybe there was another way. Thank you in advance for any help, Ledina PS My transactions have to be deferred by that shouldn't make a difference ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql error on quitting...
Some more info, the history is actually written! ( ' ' seems to be translated to \40 ) Still get the error message... Jerry Subject: psql error on quitting... Hi, I just upgraded from 8.0.4 to 8.1.0 this afternoon and the only thing bad I have noticed is that whenever I quit psql I get a message: could not save history to file /Users/jerry/.psql_history: Invalid argument This is on MacOS X 10.4.3 Thanks for any info... Jerry ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgreSQL 8.1.0 Officially Released
8 November 2005, Frankfurt, Germany (OpenDBCon): The PostgreSQL Global Development Group proudly announces the release of PostgreSQL 8.1, further extending PostgreSQL's lead as the most advanced open source database management system. Designed, built, and tested by a large and thriving community and backed by a growing number of corporate sponsors and support companies, version 8.1 will expand the scope of PostgreSQL application development. The new release includes performance improvements and advanced SQL features which will support bigger data warehouses, higher-volume transaction processing, and more complex distributed enterprise software. Major new features in this release include: Roles: PostgreSQL now supports database roles, which simplify the management of large numbers of users with complex overlapping database rights. IN/OUT Parameters: PostgreSQL functions now support IN, OUT and INOUT parameters, which substantially improves support of complex business logic for J2EE and .NET applications. Two-Phase Commit (2PC): Long in demand for WAN applications and heterogeneous data centers using PostgreSQL, this feature allows ACID-compliant transactions across widely separated servers. Some Performance Enhancements found in thie relese include: Improved Multiprocessor (SMP) Performance: The buffer manager for 8.1 has been enhanced to scale almost linearly with the number of processors, leading to significant performance gains on 8-way, 16-way, dual-core, and multi-core CPU servers. Bitmap Scan: indexes will be dynamically converted to bitmaps in memory when appropriate, giving up to twenty times faster index performance on complex queries against very large tables. Table Partitioning: the query planner is now able to avoid scanning whole sections of a large table using a technique known as Constraint Exclusion. Shared Row Locking: PostgreSQL's better than row-level locking now supports even higher levels of concurrency through the addition of shared row locks for foreign keys. For a more complete listing of changes in this release, please see the Release Notes visible at: http://www.postgresql.org/docs/current/static/release.html PostgreSQL 8.1.0 can be downloaded from the following locations: FTP Mirrors - http://www.postgresql.org/ftp/latest/ Bittorrent - http://www.postgresql.org/download/bittorrent Windows Installer - http://www.postgresql.org/ftp/binary/v8.1.0/win32 Other Binaries, including Linux, Mac OSx and Solaris - http://www.postgresql.org/ftp/binary/v8.1.0/ Sourceforge - http://sourceforge.net/projects/pgsql ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] psql error on quitting...
Hi, I just upgraded from 8.0.4 to 8.1.0 this afternoon and the only thing bad I have noticed is that whenever I quit psql I get a message: could not save history to file /Users/jerry/.psql_history: Invalid argument This is on MacOS X 10.4.3 Thanks for any info... Jerry ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] clustering by partial indexes
Keith C. Perry [EMAIL PROTECTED] writes: This might have been discussed before but I wanted to know if clustering tables by partial indexes will be availble in a later release of pgSQL? What in the world would it mean to do that? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] psql error on quitting...
Jerry LeVan [EMAIL PROTECTED] writes: I just upgraded from 8.0.4 to 8.1.0 this afternoon and the only thing bad I have noticed is that whenever I quit psql I get a message: could not save history to file /Users/jerry/.psql_history: Invalid argument This is on MacOS X 10.4.3 The Postgres code in that area hasn't changed at all. Maybe in this build you linked against Apple's builtin libedit instead of libreadline? libedit seems to have a bizarre definition of the result value from write_history() :-( regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] PostgreSQL 8.1.0 RPMs are available for download
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - - PostgreSQL New RPM Set 2005-11-08 Version: 8.1.0 Set labels: 8.1.0-2PGDG - - - - Release Info: PostgreSQL RPM Building Project has released RPMs for 8.1.0, and they are available in main FTP site and its mirrors. We currently have RPMs for: - - Fedora Core 3 - - Fedora Core 4 - - Fedora Core 4-x86_64 - - Red Hat Linux 9 - - Red Hat Enterprise Linux Enterprise Server 3.0 - - Red Hat Enterprise Linux Enterprise Server 4 - - Red Hat Enterprise Linux Enterprise Server 4-x86_64 - - Red Hat Enterprise Linux Advanced Server 4 - - Red Hat Enterprise Linux Advanced Server 4-x86_64 More may come later. Each RPM has been signed by the builder, and each directory contains a CURRENT_MAINTAINER file which includes the name/email of the package builder and link to their PGP key. If you experience problems with the RPMs or if you have feature requests, please join pgsqlrpms-general ( at ) pgfoundry ( dot ) org More info about the list is found at: http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general The project page is: http://pgfoundry.org/projects/pgsqlrpms Please do not use these resources for issue running or using PostgreSQL once it is installed. Please download these files from: http://www.postgresql.org/ftp/binary/v8.1.0/linux/ or from Bittorrent (Thanks to David Fetter and Magnus Hagander) : http://www.postgresql.org/download/bittorrent Regards, - -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFDcZvW4zE8DGqpiZARAjVTAJ9YqN4bkcAmidhDWu43onX5JAFORACdE38V mwAad0sWk81+cy/O7yORMpM= =rLqE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Connect to a database in a .sql file
Assad Jarrahian wrote: Got it. I will be more careful with words. Thanks tom for getting 8.1! one more question. Where does the directory lie for psql (so I can put a .sql file in there and run it) for windows? I'm not familiar with the Windows version, but the psql.exe file should be with all the other executables. You can use the -f flag to supply a file to process: psql -f path-to-sql-file Also, if you start psql from a command-prompt you can process a file from within psql using: \i path-to-sql-file See the documentation for full details. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Transactions, Triggers and Error Messages
Ledina Hido wrote: Thinking about it, the EXCEPTION statement would be inside my user- defined function (where I raise the exception in the first place), so I cannot see how that would help. As far as I could understand, I cannot call ROLLBACK (which is what I want to do) inside a user defined function. I tried calling it and it was simply ignored. Or am I missing something here? Yes - you want to read up on SAVEPOINTs to handle exceptions at the applicaton level. You do something like: SAVEPOINT foo; ...command that works... ...command that works... ...oops, this one gives me an error... ROLLBACK TO SAVEPOINT foo; Exceptions in plpgsql are just a wrapper to this process. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
