[GENERAL] Help-Urgent
Hi, I am using Redhat Linux 9 and PostgreSQL 7.4.5. While shutdown the linux server i forget to stop the postgresql. After starting the linux server, now i am unable to stop the postgres. When stopping the postgres i am getting the following error as bash-2.05b$ pg_ctl status pg_ctl: postmaster is running (PID: 17173) Command line was: /usr/local/pgsql/bin/postmaster bash-2.05b$ pg_ctl stop /usr/local/pgsql/bin/pg_ctl: line 274: kill: (17173) - No such process waiting for postmaster to shut down failed pg_ctl: postmaster does not shut down Note: please help me to shutdown the postgresql. -- Warm Regards, S.ElayaRaja Mobile: (+91) 98450 59540 E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Sun, Apr 24, 2005 at 04:34:31PM -0600, Michael Fuhr wrote: gnumed= select version(); version --- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3 That *definitely* isn't anything I'd want. That version of PostgreSQL Well :-) gnumed= create teable test (f timestamp with time zone); CREATE Does that really work in 7.1.3? Trying to create a teable fails This results from script logging backspaces into the log file instead of deleting characters in there and my not spotting that when cleaning up the log file. Nothing to do with the question at hand. insert into test (f) values (now()); INSERT 37830 1 SELECT * from test; f --- 2005-04-24 22:25:51.669218+00 (1 row) select f, f::timestamp at time zone 'MEZ' from test; f | timezone ---+ 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218 (1 row) Huh ? This IS different time zones from the same timestamp within the same query, isn't it ?? The fact that different versions of PostgreSQL get it right or wrong in a variety of ways indicates that the logic may need to be fixed but does show that in principle it is quite possible. If that's not what you wanted to do then I did misunderstand your original question. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
Karsten Hilbert [EMAIL PROTECTED] writes: The fact that different versions of PostgreSQL get it right or wrong in a variety of ways indicates that the logic may need to be fixed but does show that in principle it is quite possible. 7.1's version of AT TIME ZONE was so badly broken that it doesn't really matter whether it accidentally failed to malfunction in your particular test case. That's simply not relevant to later versions. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Immutable attributes?
On Sun, Apr 24, 2005 at 02:53:51PM -0700, David Fetter [EMAIL PROTECTED] wrote a message of 21 lines which said: PostgreSQL 7.4, switching to 8.0 would be difficult. Now is easier than later. Do you mean that PostgreSQL 8 has immutable attributes ? I do not find that. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Immutable attributes?
On Sun, Apr 24, 2005 at 10:05:53PM -0600, Michael Fuhr [EMAIL PROTECTED] wrote a message of 85 lines which said: CREATE FUNCTION check_immutable() RETURNS trigger AS ' for col in TD[args]: Ah, yes, much better than mine. Thanks. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Mon, Apr 25, 2005 at 03:05:12AM -0400, Tom Lane wrote: The fact that different versions of PostgreSQL get it right or wrong in a variety of ways indicates that the logic may need to be fixed but does show that in principle it is quite possible. 7.1's version of AT TIME ZONE was so badly broken that it doesn't really matter whether it accidentally failed to malfunction in your particular test case. That's simply not relevant to later versions. Neither am I complaining about 7.1 malfunctioning nor am I saying that the actual result matters. What I *am* saying is that - although some versions may be wrong or right in different ways - one can surely get values mapped to different time zones for one and the same stored timestamp out of the database in a single query. Which was what I thought the OP said wasn't possible. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Help-Urgent
ElayaRaja S schrieb: Hi, I am using Redhat Linux 9 and PostgreSQL 7.4.5. While shutdown the linux server i forget to stop the postgresql. After starting the linux server, now i am unable to stop the postgres. When stopping the postgres i am getting the following error as bash-2.05b$ pg_ctl status pg_ctl: postmaster is running (PID: 17173) Command line was: /usr/local/pgsql/bin/postmaster bash-2.05b$ pg_ctl stop /usr/local/pgsql/bin/pg_ctl: line 274: kill: (17173) - No such process waiting for postmaster to shut down failed pg_ctl: postmaster does not shut down Note: please help me to shutdown the postgresql. Hi remove your pidfile in /usr/local/pgsql or elsewhere. Then start the postgresql server. best regards -- Mario Günterberg mattheis. werbeagentur IT Engineer / Projektleiter Zillestrasse 105a. D - 10585 Berlin Tel#49-(0)30 . 34 80 633 - 0 Fax#49-(0)30 . 34 80 633 50 http://www.mattheis-berlin.de ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] What does tuple concurrently updated mean?
Hi During a batch load of records (using COPY) I got the following postgres error today tuple concurrently updated. What could have caused this? The only two occurences of the message in the source is in the functions simple_head_delete and simple_heap_update in src/backend/access/heap/heapam.c greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] SQLException Connection is closed. Operation is not
On Mon, 2005-04-25 at 03:38 +0530, Rajiv Verma wrote: I'm accessing postgres database through tomcat 4.3.1. I'm able to execute the select and update query through my application but Insert query is giving following SQLException : Connection is closed. Operation is not permitted. looks like your application is closing the database connection before the insert. are you doing the insert within the same tomcat request as the selects ? con you reproduce this using the simplest possible test case. i.e: have your doPost() only a) open db connection b) perform select c) perform insert d) close db connection e) return ok page gnari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] column size in libpq
How to determinate column size in query in libpq? Especially when column is declared for exapmle as varchar(64). PQfsize return -1 which mean that is variable and PQfmod returns 68. Piotr Filipczuk ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Installation problem with the version 8.0.2
Hi I am trying to install the 8.0.2 version in windows 2000 server. After opening the installer, I have got the welcome screen. I have selected the Language English/English in the welcome screen and clicked the start button. I am getting the following error. There is a problem with this windows installer package. A DLL required for this install to complete could not be run. Contact your support personnel or package vendor. What could be the problem?. While replying to this mail, I am requesting everyone, please send your reply to [EMAIL PROTECTED] Regards K.RajaSekar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SQLException Connection is closed. Operation is not
On Monday 25 April 2005 10:54, you wrote: On Mon, 2005-04-25 at 03:38 +0530, Rajiv Verma wrote: I'm accessing postgres database through tomcat 4.3.1. I'm able to execute the select and update query through my application but Insert query is giving following SQLException : Connection is closed. Operation is not permitted. looks like your application is closing the database connection before the insert. are you doing the insert within the same tomcat request as the selects ? con you reproduce this using the simplest possible test case. i.e: have your doPost() only a) open db connection b) perform select c) perform insert d) close db connection e) return ok page gnari I am having a similar problem from a stand-alone application.The log says something about an unexpected EOF , so I thought maybe it was the program closing the connection.But this is not the case because it closes while iterating through a result set.It reads the first one , then when rs.next() is called again , it says : Connection is closed. Operation is not permitted. Clifton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Optimising Union Query.
Jim C. Nasby wrote on 25/04/2005 01:28: On Sat, Apr 23, 2005 at 10:39:14PM +, Patrick TJ McPhee wrote: In article [EMAIL PROTECTED], Rob Kirkbride [EMAIL PROTECTED] wrote: % I've done a explain analyze and as I expected the database has to check % every row in each of the three tables below but I'm wondering if I can This is because you're returning a row for every row in the three tables. % select l.name,l.id from pa i,locations l where i.location=l.id union % select l.name,l.id from andu i,locations l where i.location=l.id union % select l.name,l.id from idu i,locations l where i.location=l.id; You might get some improvement from select name,id from locations where id in (select distinct location from pa union select distinct location from andu union select distinct location from idu); Note that SELECT DISTINCT is redundant with a plain UNION. By definition, UNION does a DISTINCT. In fact, this is going to hurt you; you'll end up doing 4 distinct operations (one for each SELECT DISTINCT and one for the overall UNION). Unless some of those tables have a lot of duplicated location values, you should either use UNION ALLs or drop the DISTINCTs. Note that going with DISTINCTs is different than what your original query does. You should also consider this: SELECT name, id FROM locations l WHERE EXISTS (SELECT * FROM pa p WHERE p.location=l.id) OR EXISTS (SELECT * FROM andu a WHERE a.location=l.id) OR EXISTS (SELECT * FROM idu i WHERE i.location=l.id) This query would definately be helped by having indexes on (pa|andu|idu).location. Thanks for that. I tried a few things, including using DISTINCTS and UNION ALLs but none made a big difference. However your query above sped things up by a factor of more than 2. Thanks very much! Rob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Hosting Service Recommendations
It seems the consensus on this list is that when running postgres, Opterons outperform Xeons by a significant margin not to mention the CS storms postgres seems to cause on Xeons. We are looking for a hosting service for a postgresql based application. Unfortunately, it seems most services standardize on Dell and by extension Xeon hardware. Do anyone have recommendations for hosting services that would be able to provide Opteron based hardware and have decent service? Or are we stuck with Xeons? -- Chris Kratz ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
On Mon, Apr 25, 2005 at 08:28:47AM +0200, Karsten Hilbert wrote: gnumed= create teable test (f timestamp with time zone); CREATE Does that really work in 7.1.3? Trying to create a teable fails This results from script logging backspaces into the log file instead of deleting characters in there and my not spotting that when cleaning up the log file. Nothing to do with the question at hand. It doesn't matter in this particular case because we could see what was intended (I honestly wondered if that was a typo that 7.1.3 accepted), but sometimes subtle differences can matter so it's better to post code that others can copy and paste verbatim into their database -- that way the corrections themselves don't introduce different behavior. select f, f::timestamp at time zone 'MEZ' from test; f | timezone ---+ 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218 (1 row) Huh ? This IS different time zones from the same timestamp within the same query, isn't it ?? No, it isn't. In the above example from 7.2.7 the second column has no time zone specification whatsoever; if you cast it to timestamp with time zone then you get the following: select f, (f::timestamp at time zone 'MEZ')::timestamptz from test; f | timezone ---+--- 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+00 (1 row) There's only one time zone displayed here: +00. Those are different times being displayed in the same time zone, not the same time being displayed in different time zones. Other versions of PostgreSQL give varying output, but they all show both columns in the same time zone. The problem I was discussing involves getting *different* time zone specifictions in the output. That is, something like this (which apparently is possible in 7.1.3 but not in later versions): f | timezone ---+--- 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01 The fact that different versions of PostgreSQL get it right or wrong in a variety of ways indicates that the logic may need to be fixed but does show that in principle it is quite possible. Certainly it's possible in principle; the question is how to do it in practice in modern versions of PostgreSQL. As far as I can tell it's not possible (short of changing the type to text, as one of my earlier examples did), but again, I'd be pleased to be corrected (preferably with an example that works in 8.0.2). BTW, this is mostly academic to me, but others have asked similar questions in the past so I've been curious about whether it could be done. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Installation problem with the version 8.0.2
http://www.microsoft.com/downloads/details.aspx?FamilyID=4b6140f9-2d36-4977-8fa1-6f8a0f5dca8fDisplayLang=en On Mon, 2005-04-25 at 15:37 +0530, K.RajaSekar wrote: Hi I am trying to install the 8.0.2 version in windows 2000 server. After opening the installer, I have got the welcome screen. I have selected the Language English/English in the welcome screen and clicked the start button. I am getting the following error. There is a problem with this windows installer package. A DLL required for this install to complete could not be run. Contact your support personnel or package vendor. What could be the problem?. While replying to this mail, I am requesting everyone, please send your reply to [EMAIL PROTECTED] Regards K.RajaSekar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Hosting Service Recommendations
Chris Kratz [EMAIL PROTECTED] writes: It seems the consensus on this list is that when running postgres, Opterons outperform Xeons by a significant margin not to mention the CS storms postgres seems to cause on Xeons. We are looking for a hosting service for a postgresql based application. Unfortunately, it seems most services standardize on Dell and by extension Xeon hardware. Do anyone have recommendations for hosting services that would be able to provide Opteron based hardware and have decent service? Or are we stuck with Xeons? -- Chris Kratz You might consider a co-located server. You supply the hardware of your choosing and the ISP puts it in their datacenter. Less popular than dedicated server (owned by the ISP) but a good choice if you have special hardware needs. Not all ISPs do colo so, better check around. HTH -- --- Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant 305 321-1144 (mobilehttp://www.JerrySievers.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Immutable attributes?
On Mon, Apr 25, 2005 at 09:18:16AM +0200, Stephane Bortzmeyer wrote: On Sun, Apr 24, 2005 at 02:53:51PM -0700, David Fetter [EMAIL PROTECTED] wrote PostgreSQL 7.4, switching to 8.0 would be difficult. Now is easier than later. Do you mean that PostgreSQL 8 has immutable attributes ? I do not find that. I think that was a comment about upgrading in general, not an implication that 8.0 supports a particular feature. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Pgpool questions
On Sun, 2005-04-24 at 00:53, Ron Mayer wrote: Tatsuo Ishii wrote: Is there anyway to load balance selects to more than 2 servers? ex. 1 master with 2 slaves? Run multiple instances? It's on my TODO but will not be a trivial change. Could I set up 2 pairs of pgpool-balanced servers, and use a third pgpool to load balance across the two pools? Or is that just silly. I've actually thought of doing the same thing. I can't see a reason why it wouldn't work. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Pgpool questions
Thanks. Is it on your todo list to something like this? It is the opposite of /* NO LOAD BALANCE */. Make a /* SLAVE */ type comment to force it to a slave? It would be helpful in forcing a query to a reporting server if you know your what your reporting queries are. Set weight_secondary to 0. Did you mean Set weight_master to 0. Unfortunately this would not help on a query by query basis, but the above would. -Original Message- From: Tatsuo Ishii [mailto:[EMAIL PROTECTED] Sent: Saturday, April 23, 2005 7:23 PM To: Brian Maguire Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Pgpool questions I have a couple Pgpool questions regarding running pgpool as a load balancer with Sony as the replicator. Is there anyway to load balance selects to more than 2 servers? ex. 1 master with 2 slaves? Run multiple instances? It's on my TODO but will not be a trivial change. Is there any way to tell a select (ex. with a function) to go only the master and not the slave? Pgpool will send any query not starting with SELECT or select to the master. So you can insert a white space or a comment, for example /* NO LOAD BALANCE */ at the beginning of the line to prevent the query to be sent to the slave. Is there any way to tell a select query to only go to the slave? Set weight_secondary to 0. -- Tatsuo Ishii
Re: [GENERAL] Pgpool questions
On Sun, 2005-04-24 at 00:53, Ron Mayer wrote: Tatsuo Ishii wrote: Is there anyway to load balance selects to more than 2 servers? ex. 1 master with 2 slaves? Run multiple instances? It's on my TODO but will not be a trivial change. Could I set up 2 pairs of pgpool-balanced servers, and use a third pgpool to load balance across the two pools? Or is that just silly. I've actually thought of doing the same thing. I can't see a reason why it wouldn't work. Oh, cascading pgpool! Actually once I have tried it and it definitly worked. The only concern is the overhead due to the cascading... -- Tatsuo Ishii ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] OpenRPT (was Re: [ANNOUNCE] == PostgreSQL Weekly News - April 24 2005 ==)
David Fetter wrote: == PostgreSQL Product News == OpenRPT is a graphical SQL report writer, designer and rendering engine, optimized for PostgreSQL. WYSIWYG display, GUI built with Qt, runs on Linux, Windows, Mac OS X. Server-side rendering engine. Reports can be saved as XML, either as files or in a database. http://pgfoundry.org/projects/openrpt/ Dave, thanks for the plug - and thanks to everyone who's expressed an interest in OpenRPT so far. It seems there are still some performance issues with pgFoundry (pending setup of new servers?) - so for now, we're going to do our best not to contribute to the load, and point people to OpenRPT's home on SourceForge, where the iron is big and the pipes fat: http://openrpt.sf.net/ We're very interested in engaging with the PostgreSQL community to help make OpenRPT better. For those who aren't familiar with us, the OpenRPT project has spun out of an ERP application called OpenMFG. I'm the CEO of OpenMFG LLC, the company; I was previously one of the founders of Great Bridge, a first-generation Postgres company that was a casualty of the dot-com boom/bust cycle four years ago. I formed OpenMFG to bring a Postgres-based ERP system to market (www.openmfg.com) ... and along the way, part of what we built with our ERP was a multiplatform report writer. The OpenMFG ERP Suite is inexpensive but not free; source code provided and patches accepted, but not OSI-certified open source. We decided that the report writer level of the stack was different, however, and decided to release OpenRPT under the GPL for anyone and everyone who wants to abide by the terms of the GPL. For those who would like to opt out of the GPL, we also offer a commercial license. For more on the licensing, as well as downloads, CVS, screenshots, docs, project info, etc., please check out the project homepage at http://openrpt.sf.net. Thanks very much, Ned -- Ned Lilly President and CEO OpenMFG, LLC 420 North Center Drive Building 11, Suite 115 Norfolk, VA 23502 tel: 757-461-3022 mailto: [EMAIL PROTECTED] www.openmfg.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Hosting Service Recommendations
I have found that even the larger hosting companies such as Rackspace are pretty clueless when trying to configure even a mid size database server. They gave us a 4 drive raid 5 even after being explicitly asked for a RAID 10. They also put the controller in write through cache mode. Pretty bad config - the server blew chunks, our single drive ATA cheapo box ran significantly faster. I would definatley suggest looking at co-lo. Alex Turner netEconomist On 4/25/05, Chris Kratz [EMAIL PROTECTED] wrote: It seems the consensus on this list is that when running postgres, Opterons outperform Xeons by a significant margin not to mention the CS storms postgres seems to cause on Xeons. We are looking for a hosting service for a postgresql based application. Unfortunately, it seems most services standardize on Dell and by extension Xeon hardware. Do anyone have recommendations for hosting services that would be able to provide Opteron based hardware and have decent service? Or are we stuck with Xeons? -- Chris Kratz ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] UltraSPARC versus AMD
I am looking at options for a customer with an installed base of ~5000 Sun workstations running 400-500MHz UltraSPARCs. They're not getting the performance they need. They shipped me two Tadpole Bullfrog machines, a Bullfrog I and a Bullfrog II for evaluation. http://www.tadpole.com 1.28GHz single or dual CPU UltraSPARCs. On board SCSI, but they installed IDE drives instead. In my *utter* lack of enthusiasm over this option, I was gathering ammunition for better hardware. I went to spec.org for speed comparisons, and sun.com for price comparisons. Sun's *entry* level servers are more powerful when running AMD CPUs. I note with interest and appreciation comments about the bigger iron from Sun and IBM. That's not what I'm in the market for, but good info as always. My evaluation is that a single or dual core AMD 64 Athlon in a rugged laptop is going to give a performance enhancement (SPECMark wise) of about an order of magnitude over their current hardware base. And it's cheaper. The current hardware base contains a 10k SCSI Fast Wide Ultra single disk on a 440MHz CPU as well as a 7200 IDE on a 500MHz CPU. The SCSI with the slower CPU runs the application 8% faster. Obviously I'll need to work on the proper I/O subsystem because that's apparently more of a limiter than the CPU speed. Cheers, Rick [EMAIL PROTECTED] wrote on 04/23/2005 11:02:17 AM: As someone who works in a nationwide bank, let me tell you why we choose IBM and Sun hardware: support. If we want to get a server for a project, we can't just go get the most cost-efficient thing out there for the job. We have a short list of servers that can be picked from, and that's it. A given server makes it onto that list if and only if it can be supported by a vendor in a matter of hours for at least 3 years. We don't always purchase that support, but bank policy says it has to be an option. We don't generally purchase monster machines. Sure, there are some mainframes, but they are few and far between. Everything else doesn't really have anything more than 32 procs. On Apr 23, 2005, at 2:58 AM, William Yu wrote: As for why financial/insurance institutions use IBMs or Suns -- I would suggest limited choice is a bigger issue than any specific sub-system performance factor. A nationwide bank doesn't have any choice except to pick a monster 100+ processor machine because anything slower couldn't handle their 20,000 employees. Not many options really. Plus, people in big companies tend to make safe decisions -- get the company with the most name value so you don't get fired if the machine turns out to be a lemon. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] What does tuple concurrently updated mean?
Florian G. Pflug [EMAIL PROTECTED] writes: During a batch load of records (using COPY) I got the following postgres error today tuple concurrently updated. I don't think plain COPY could have produced that, since it only inserts tuples. Tell us about triggers and foreign keys on this table? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
select f, f::timestamp at time zone 'MEZ' from test; f | timezone ---+ 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218 (1 row) Huh ? This IS different time zones from the same timestamp within the same query, isn't it ?? No, it isn't. In the above example from 7.2.7 the second column has no time zone specification whatsoever; You are correct. However, how about this: version --- PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3 (1 row) = select now() as MESZ, now() at time zone 'MEZ' as MEZ, now() at time zone 'UTC' as UTC ; MESZ | MEZ | UTC ++ 2005-04-25 17:13:19+02 | 2005-04-25 16:13:19+01 | 2005-04-25 15:13:19+00 (1 row) Is that eventually it ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] DDL from psql console?
Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy paste the DDL CREATE TABLE statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. Any thoughts? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] artificial keys or not?
Scott Ribe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] ..so the only question is: drop the the serials as PKs and just use short text handles? our database size is arround 290 mb. there shouldn't be speed issues as long as proper indexes exit, right? some of the conditions are dynamic though, so there can't be an proper index in any case. any other concerns? Someone (a manager) somewhere (probably marketing) sometime (when you least expect it) will demand that those short text names be changed, possibly for a completely irrational reason. I know there are still text books that claim that artificial keys are evil for some inscrutable reason, but hard experience has taught me to never, ever, under any circumstance, make a primary key out of data that comes from humans, and to be very suspicious of using data that will be visible to humans. This is my experience also. But opinions vary, and I wouldn't be surprised to see a further post from someone which argues the exact opposite. :-) -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 665-7007 voice ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 3: 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] installation as service failed on windows xp
Hi, The first time I install postgresql in Windows XP, I install it as service. The installer asked me to create postgres account. Ok, fine... then someday I uninstalled postgresql so I can install the newer version of postgresql. But first to make it clean, I delete the postgres account from right clicking the My Computer, then click the properties. Choose the advanced tab, then click the user profiles setting buttons. Then when I try to install as service with postgres account, it said : Invalid user name specified: Logon failure: unknown user name or bad password. I try to create postgres account from start menu --- account or control panel -- user account, it said that The account already exists. But there is no postgres account in User Accounts window. So the questions is: 1. How do I really delete the postgres account that created by postgresql installer??? Thank you. Regards, Akbar ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] timezone() with timeofday() converts the wrong direction?
Michael Fuhr [EMAIL PROTECTED] writes: The problem I was discussing involves getting *different* time zone specifictions in the output. That is, something like this (which apparently is possible in 7.1.3 but not in later versions): f | timezone ---+--- 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01 It was not really possible in 7.1 either (nor any previous version). The secret to the above is that in 7.1 the timezone() function's result is *text*, not a timestamp object. So it's purely a display artifact ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] DDL from psql console?
Any reason not to use pg_dump -s? -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 25, 2005, at 10:29 AM, John Browne wrote: Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy paste the DDL CREATE TABLE statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. Any thoughts? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] DDL from psql console?
Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) On 4/25/05, John Browne [EMAIL PROTECTED] wrote: Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy paste the DDL CREATE TABLE statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. Any thoughts? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DDL from psql console?
On Mon, 2005-04-25 at 10:29, John Browne wrote: Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy paste the DDL CREATE TABLE statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. I don't think you can get it from within psql, but you can get it with pg_dump from the command line: pg_dump -st tablename dbname ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Hosting Service Recommendations
On Mon, Apr 25, 2005 at 11:04:27AM -0400, Alex Turner wrote: I have found that even the larger hosting companies such as Rackspace are pretty clueless when trying to configure even a mid size database server. They gave us a 4 drive raid 5 even after being explicitly asked for a RAID 10. They also put the controller in write through cache mode. Pretty bad config - the server blew chunks, our single drive ATA cheapo box ran significantly faster. I would definatley suggest looking at co-lo. Definitely, yes. The managed facilities tend to be tuned for webserver usage rather than anything else until you get to the high end. plug We use http://www.asaservers.com/ as a build-to-order hardware vendor. They also offer very reasonably priced colocation. So you can spec the box you want and they'll build it, install an OS and drop it in their rack (at a decent colo facility). It makes real colo as painless to set up as a managed hosting outfit. If you call 'em, ask for Abhi. /plug On 4/25/05, Chris Kratz [EMAIL PROTECTED] wrote: It seems the consensus on this list is that when running postgres, Opterons outperform Xeons by a significant margin not to mention the CS storms postgres seems to cause on Xeons. We are looking for a hosting service for a postgresql based application. Unfortunately, it seems most services standardize on Dell and by extension Xeon hardware. Do anyone have recommendations for hosting services that would be able to provide Opteron based hardware and have decent service? Or are we stuck with Xeons? Cheers, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] DDL from psql console?
John Browne [EMAIL PROTECTED] writes: I was curious if there was a way to get the DDL for a particular table from the psql client console? No. Try pg_dump -s -t tablename dbname regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DDL from psql console?
On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) You could do \!pg_dump ... -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Playing with PostgreSQL and Access VBA
Tony Caduto [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] In a real application that uses a client/server type database you should NEVER construct queries that return a million rows. You have to keep in mind that each record has to be sent accross the wire via TCP/IP and it is going to take a lot of time to bring back 1 million rows and I am sure your network admin would notice increased network traffic. In a real application you would use something like the Delphi Tdataset based components which will only bring back the records that can be seen on the screen, then when you scroll, it brings back more. you can manually create something like this using Postgresql handy LIMIT and OFFSET sql keywords in your query to build a paging system. Access is actually a very poor choice for client/server application development, like I said before, invest some time learning Delphi, it's Access may be a poor choice for a Delphi developer to use. But for many of us, it's an excellent choice. Everything depends on your situation and on your needs. The OP is starting from ground zero in learning about Access (or anything, apparently) and client-server apps. Of course there will be difficulty, regardless of the choice of interface. not difficult at all and you won't have these problems. You can get a copy of Delphi 7 personal edition and you can use the zeos components with it. http://www.zeoslib.net Tony . When I had it loop and add 1000 rows, it was ok... When I told it to add a million rows then after 250,000 the Access application hung (but not the whole machine, and not the ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: 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] Delphi personal (was Playing with PostgreSQL and Access VBA)
I'm pretty sure that zeosdbo needs a version of Delphi with TDataset support. I don't think that the Personal editions have that. You can use the direct access parts of Zeos with the personal editions but then you might as well use Free Pascal and Lazarus as they have just ported ZeosDbo see http://www.lazarus.freepascal.org/. Ben Tony Caduto [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] All you need is the Personal Edition of say Delphi 6 or Delphi 7, and even Delphi 2005. For Delphi 2005 personal edition see this link: http://andy.jgknet.de/oss/kylix/wiki/index.php/Delphi_2005_Personal_Edition_xmlrtl.dcp_fake Heck, Delphi 5 is still extremely viable and can do anything 6,7 or 2005 can do, and I bet you can find version 5 on ebay or elseware for dirt cheap. The personal editions do not include database access components by themselves, but you certainly can use third party tools such as Zeos (http://www.zeoslib.net) or those from http://www.microolap.com. You could even grab a copy of libpq.pas if you want to do direct access to PG. You get what you pay for and there is nothing available for Python or WXwidgets that even comes close to Delphi for RAD/GUI database development. Not to mention all the other stuff you can do with Delphi, it's also a great programming language for console apps, services, TCP/IP servers. There are even remote control applications built with it ala PC Anywhere. Plus if you call borland, I am sure they would give you the upgrade price if you have a old copy of VB laying around for a competive upgrade. I was very impressed by what one member said regarding Delphi, but, when I looked at pricing,... well I would have to lie through my teeth to get the cheap academic version, and the personal version sounds like it doesnt have the file access abilities for PostgreSQL. And the prices for enterprise versions at programmersparadise.com like $4000, sort of puts me off. ---(end of broadcast)--- TIP 3: 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] DDL from psql console?
Tom Lane wrote: John Browne [EMAIL PROTECTED] writes: I was curious if there was a way to get the DDL for a particular table from the psql client console? No. Try pg_dump -s -t tablename dbname Oh, from psql: :-) (We really should have an easier way of show all information from psql) test= CREATE TABLE test (x SERIAL); NOTICE: CREATE TABLE will create implicit sequence test_x_seq for serial column test.x CREATE TABLE test= \! pg_dump -s -t test test -- -- PostgreSQL database dump -- SET client_encoding = 'SQL_ASCII'; SET check_function_bodies = false; SET client_min_messages = warning; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: test; Type: TABLE; Schema: public; Owner: postgres; Tablespace: -- CREATE TABLE test ( x serial NOT NULL ); ALTER TABLE public.test OWNER TO postgres; -- -- PostgreSQL database dump complete -- -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DDL from psql console?
Actually, that's a thought.. I could even create a bash wrapper script so I wouldn't have to type the database name each time. Will give it a shot. Thanks On 4/25/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) You could do \!pg_dump ... -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DDL from psql console?
John Browne wrote: Actually, that's a thought.. I could even create a bash wrapper script so I wouldn't have to type the database name each time. Will give it a shot. Thanks On 4/25/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) You could do \!pg_dump ... Also, what is it you want to see that \d doesn't give you? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] timezone() with timeofday() converts the wrong direction?
On Mon, Apr 25, 2005 at 11:36:23AM -0400, Tom Lane wrote: ---+--- 2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218+01 It was not really possible in 7.1 either (nor any previous version). The secret to the above is that in 7.1 the timezone() function's result is *text*, not a timestamp object. So it's purely a display artifact ... Aha, there's the explanation. Thanks, Tom. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] What does tuple concurrently updated mean?
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: During a batch load of records (using COPY) I got the following postgres error today tuple concurrently updated. I don't think plain COPY could have produced that, since it only inserts tuples. Tell us about triggers and foreign keys on this table? Sorry, I was confused. The error didn't actually happen while copying, it happened while analyze-ing the tables after the import. I confused this, because the analyze is started from our import-script, and the log just showed the error-message postgres gave ;-) So - does tuple concurrently updated make sense for analyze? greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] What does tuple concurrently updated mean?
Florian G. Pflug [EMAIL PROTECTED] writes: So - does tuple concurrently updated make sense for analyze? Yeah, it's entirely possible, if you have a background process that might issue analyzes too (do you use autovacuum?). The error comes when two sessions concurrently try to update the same row in pg_statistic. I've looked at preventing it, but the cure seems worse than the disease --- we don't really want ANALYZE to take any strong locks on the table, and in any case all you are losing is presumably-near-duplicate stats from one session or the other. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What does tuple concurrently updated mean?
Tom Lane wrote: Florian G. Pflug [EMAIL PROTECTED] writes: So - does tuple concurrently updated make sense for analyze? Yeah, it's entirely possible, if you have a background process that might issue analyzes too (do you use autovacuum?). The error comes when two sessions concurrently try to update the same row in pg_statistic. I've looked at preventing it, but the cure seems worse than the disease --- we don't really want ANALYZE to take any strong locks on the table, and in any case all you are losing is presumably-near-duplicate stats from one session or the other. Hm... could this be turned into a warning then? I'll fix this by ignoring db-errors when issuing analyze, but I belive this will bite more people... Anyway, thanks for your fast reply - I'll sleep much better, now that I know the cause for this ;-) greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] unsubscribe
---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Playing with PostgreSQL and Access VBA
I guess I was thinking more of the big picture Your right of course, for someone just fooling around at home or for a very small business Access is great, but I am saying from experience that Access is a poor choice for any kind of large project or corp use. Access may be a poor choice for a Delphi developer to use. But for many of us, it's an excellent choice. Everything depends on your situation and on your needs. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] plpgsql function not working
Title: plpgsql function not working I found this function on line [Thanks to Jeff Eckermann and Juerg Rietmann] that takes the results of a query and creates a comma delimited string. However when I run it I get no values. Any help would be appreciated. I apologize if this is the wrong forum. Jeff Ruff Tycoelectronics Power Systems Function: DROP FUNCTION userinfo.group_list(text); CREATE FUNCTION userinfo.group_list(text) RETURNS text AS' DECLARE rec RECORD; string text := NULL; BEGIN FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP string := string || rec.groupname || \',\'; END LOOP; string := substr(string, 1, length(string)-1); RETURN string; END; 'LANGUAGE 'plpgsql'; Acts on database listing name userdb_groups key username groupname === 1023 jruff srcadm 1024 jruff libadm 1025 jruff mpdev 1026 jruff systems 1027 jruff ug 1027 jruff cadadm 1028 jruff mppm 1029 jruff corerd 1030 jruff weblive Jeff Ruff Tyco Electronics Power Systems, Inc. CAD Support Group phone: 972-284-4267 email: [EMAIL PROTECTED]
Re: [GENERAL] column size in libpq
On Apr 25, 2005, at 4:53 AM, Piotr Filipczuk wrote: How to determinate column size in query in libpq? Especially when column is declared for exapmle as varchar(64). PQfsize return -1 which mean that is variable and PQfmod returns 68. 68 - 4 is the correct size for varchar(64). There is 4 bytes of overhead included in the return value. If you are trying to do something like column formatting where you need to know the longest length, maybe take a look at the psql source and see how it works there. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Calculated bigserial column in a view
Please keep replies copied to the list so that other people can learn from and comment on the discussion unless to have a good reason to make the thread private. On Mon, Apr 25, 2005 at 21:46:20 +0200, Zlatko Matic [EMAIL PROTECTED] wrote: The reason for such crazy idea is my front-end MS Access which considers views as tables and have problem with tables ( views also) if there is no unique numeric field... Anyway, how to do it ? Thanks. I don't know MS Access, so I am not going to be able to help much with this. There may be some way to trick MS Access into thinking that your view has a unique numeric field. It would help to know how it knows that a column has those properties and how it uses the information. - Original Message - From: Bruno Wolff III [EMAIL PROTECTED] To: Zlatko Matic [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Monday, April 25, 2005 6:31 AM Subject: Re: [GENERAL] Calculated bigserial column in a view On Sun, Apr 24, 2005 at 22:16:14 +0200, Zlatko Matic [EMAIL PROTECTED] wrote: I know that it sounds crazy, but I need a bigserial coulumn in a view that is consisted of several tables. That column should not be based on bigserial column of any table, but should be a calculated column... How can I accomplish it ? This doesn't really make sense. It sounds like you want a key that is unique accross the union of several tables and that doesn't change when other rows in the union are added or removed. There are a couple of ways you might go about doing something like this. Is this what you really want? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] plpgsql function not working
On Apr 25, 2005, at 3:40 PM, Ruff, Jeffry C. SR. wrote: I found this function on line [Thanks to Jeff Eckermann and Juerg Rietmann] that takes the results of a query and creates a comma delimited string. However when I run it I get no values. Any help would be appreciated. I apologize if this is the wrong forum. Try changing string text := NULL; to string text := ''; Any text concatenated with NULL results in a NULL value. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] rollback vs. commit for closing read-only transaction
If an applicationtransaction is known to be read-only, is there any reason to prefer COMMIT or ROLLBACK for closing that transaction? Would there be any performance difference between the two commands? - DAP--David Parker Tazz Networks (401) 709-5130
Re: [GENERAL] rollback vs. commit for closing read-only transaction
David Parker wrote: If an application transaction is known to be read-only, is there any reason to prefer COMMIT or ROLLBACK for closing that transaction? Would there be any performance difference between the two commands? Doesn't matter. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] rollback vs. commit for closing read-only transaction
Probably, turning fsync off would be helpful, since you know it is read-only. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of Bruce Momjian Sent: Monday, April 25, 2005 3:01 PM To: David Parker Cc: postgres general Subject: Re: [GENERAL] rollback vs. commit for closing read-only transaction David Parker wrote: If an application transaction is known to be read-only, is there any reason to prefer COMMIT or ROLLBACK for closing that transaction? Would there be any performance difference between the two commands? Doesn't matter. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] rollback vs. commit for closing read-only transaction
Dann Corbit [EMAIL PROTECTED] writes: Probably, turning fsync off would be helpful, since you know it is read-only. Wouldn't make any difference: a transaction that hasn't modified the database doesn't bother to write any commit/abort WAL record at all. regards, tom lane ---(end of broadcast)--- TIP 3: 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] Toad Adm. Datasource Location
I am just curious to see if I can get Toad to connect to Postgresql. I have Access talking to it via ODBC. Toad is asking for Datasource and Location. I cant figure out what to put there. I have done some searching in google. Thanks for any suggestions.
Re: [GENERAL] Intuitive Software and Mario Brothers
Title: Message WELL... I did that, and , LO AND BEHOLD,... there the darn thing is now WHO in the name of Heaven would ever dream of going there and right clicking? Maybe it was some bloke called Gates who couldn't figure out what to do with a single button mouse. After all, it is "intuitive", isn't it? -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, April 24, 2005 1:46 AMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Intuitive Software and Mario Brothers I just had an amusing thought, this morning. You know those old Mario Brothers games, video games , where you have to bounce on everything you see, every rock and tree and flower until you discover the secret place where you get power points, or warp into the next level? Well. A lot of software is just like that! Last night, trying to make a VBA routine to talk to Postgresql through MSAccess with ODBC it is like BOINKING and poking on every single place, nook and cranny until you get it right, by accident. Come to think of it, I did hours of boinking and poking to get Postgresql windows install to work. (They should just have a few words for dummies like me, saying "Hey, dont be afraid to install this as a windows service, because the uninstall can undo everything,... AND dont think of installing it as the other option, unless you really know what you are doing, and can do the initdb thingey yourself, because it just wont work.) Some guy had a tutorial about Excel and VBA and he says "go here to add a BUTTON" to excel... so... I launch excel, and look EVERYWHERE,... and nothing in sight,... SO, I go back to his tutorial and he says "right click ANYWHERE in the task bar, and these options open up" ... WELL... I did that, and , LO AND BEHOLD,... there the darn thing is now WHO in the name of Heaven would ever dream of going there and right clicking?
[GENERAL] Corruption on production system
We've got a PG 7.3.6 installation that just started receiving this error: 2005-04-25 19:28:54 ERROR: Invalid page header in block 1110 of the_table_name We're also unable to dump that table using pg_dump. Postgres isn't actually crashing, so I'm not sure how to get a backtrace out of it. SELECT version(): PostgreSQL 7.3.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031218 (Gentoo Linux 3.3.2-r5, propolice-3.3-7) (Don't get upset that it says Gentoo -- I built it myself from official Postgres sources) Is page header corruption a known issue with 7.3.6 and should I upgrade to 7.3.9 at the same time that I'm restoring this table from backup? Any quick advice will be greatly appreciated! thanks eric ---(end of broadcast)--- TIP 3: 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] question about about future 8.1 and IN, INOUT, and OUT parameters
On Sun, Apr 24, 2005 at 11:05:57PM -0500, Tony Caduto wrote: I saw that the recently released pl/java has the ablity to use IN, INOUT, and OUT parameters. Is the syntax going to be exactly like it is in Oracle? I'm not familiar with the Oracle syntax and I don't know how close the PostgreSQL implementation is to being final, but here's an example that works with the most recent code from HEAD (8.1devel): CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$ BEGIN y := y + 5; z := x + 5; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; SELECT foo(10, 20); foo - (25,15) (1 row) SELECT (foo(10, 20)).*; y | z + 25 | 15 (1 row) SELECT (foo).* FROM (SELECT foo(10, 20)) AS s; y | z + 25 | 15 (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Corruption on production system
On Mon, Apr 25, 2005 at 08:24:03PM -0400, Eric B. Ridge wrote: Is page header corruption a known issue with 7.3.6 and should I upgrade to 7.3.9 at the same time that I'm restoring this table from backup? Well, you can get a page header corruption on any release as long as you have faulty hardware ... RAM randomly dropping bits is not unheard of. Have you run memtest? If you don't mind losing that page of data, you could turn on zero_damaged_pages on postgresql.conf. If you do, you could try pg_filedump (to be found somewhere on sources.redhat.com/rhdb IIRC) to examine the damaged page more closely. -- Alvaro Herrera ([EMAIL PROTECTED]) Vivir y dejar de vivir son soluciones imaginarias. La existencia está en otra parte (Andre Breton) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Corruption on production system
On Apr 25, 2005, at 9:42 PM, Alvaro Herrera wrote: Well, you can get a page header corruption on any release as long as you have faulty hardware ... RAM randomly dropping bits is not unheard of. Have you run memtest? No doubt. :) What I was asking was if 7.3.9 (or .8 or .7) had a known issue that caused page corruption and if upgrading would fix that bug. Regardless, I went ahead and upgraded. eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] UltraSPARC versus AMD
[EMAIL PROTECTED] wrote: In my *utter* lack of enthusiasm over this option, I was gathering ammunition for better hardware. I went to spec.org for speed comparisons, and sun.com for price comparisons. Sun's *entry* level servers are more powerful when running AMD CPUs. Just in case people still hold a bias against CISC processors capable of running x86 code as necessarily inferior to more expensive 64-bit RISC processors, in spite of the overwhelmingly obvious specint results to the contrary, I'd like to offer up this little baby: http://www.cray.com/products/xt3/index.html Something everyone should have in their office... Mike Mascari ---(end of broadcast)--- TIP 3: 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] pgexplorer adm
I went to http://www.pgexplorer.com and downloaded their adm program for postgresql running on windows. The site proclaims that it is now FREE, yet there is a moment of confusion when you go to install and it talks about 30 evaluation period. Yet, once you install, if you click on the REGISTER button, it tells you that this is a FREE copy. Installed and ran with no effort on my part. I think I shall try this out for a while. I have yet to get Toad to run because I cant figure out what it wants me to fill in in the odbc dsn setup for datasource and location. But I imagine that pgexplorer.com will work ok for now. But thanks everyone for your suggestions. I am still shopping around for a language to use for front end. I suppose that Access will do a lot for me. I feel very comfortable with Liberty Basic, but it does not offer any easy way to do odbcconnections. I am looking at Truebasic and Powerbasic. I really wish that there were some knd of easy to use IDE window painter like Powerbuilder used to be. But, I guess if I use MSAccess, and VBA, then I shall be learning some useful skills. I realize the virtues of Delphi, but I just dont feel like attempting it at this stage of the game. But, at least, each week, I am making a little more progress, and learning some interesting things. Is there some kind of drag and drop Java ide painter which would be fairly straight forward, without having to become an expert in Java, just curious. I know nothing about Java. I tried out a free Cincom Smalltalk, which has a nice tutorial, but I have not seen a tutorial which walks you through doing a menu and windows for a SQL application.
[GENERAL] pgadminIII - creating servers
I just now tried creating a second server with pgadminIII. I am confused as to why one would have more than one server. There does not seem to be any way to "switch" from one server to another, or to log into one particular server (and not the other), or to create tables specifically for one server. I find the concept rather confusing. I understand that if I limit myself to one server, then I may define different database, and log to a particular database, and see only the tables in that database. Any explanation regarding more than one server will be appreciated. Thanks.
[GENERAL] I can't create a new database with GB18030 encoding
I want create a new database with the GB18030 encoding.But the pgAdminIII tell a error GB18030 is not a valid encoding name.How can i do for it? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Migrating MySQL app to postgres?
Hi Rich, Guess now's the time. I'm trying to find a copy of pygresql to use, but it's not easy (www.pygresql.org leads to druid.net and there's nothing on the page about pygresql). I'm learning python to use on a major project here (but the core libraries will remain in C) so I'd like to use that for the UI on this project, too. I'm still not sure about the reports, but one step at a time. That is, will the pygresql display nicely formatted reports based on sql queries to the backend? Need to find out. I have found using python with reportlab (http://www.reportlab.org/rl_toolkit.html) very easy to create pdf reports based on queries. Hope this helps, Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Postgres source (tar file) for Fedora Core OS?
From where can I download latest Postgres source (tar file) for Fedora Core OS? Regards Dinesh Pandey
Re: [GENERAL] question about about future 8.1 and IN, INOUT, and OUT parameters
Michael Fuhr [EMAIL PROTECTED] writes: On Sun, Apr 24, 2005 at 11:05:57PM -0500, Tony Caduto wrote: Is the syntax going to be exactly like it is in Oracle? I'm not familiar with the Oracle syntax and I don't know how close the PostgreSQL implementation is to being final, but here's an example that works with the most recent code from HEAD (8.1devel): CREATE FUNCTION foo(IN x integer, INOUT y integer, OUT z integer) AS $$ FWIW, we will also take the more Oracle-ish spelling of the parameter list: CREATE FUNCTION foo(x IN integer, y IN OUT integer, z OUT integer) AS ... ... although given all the other in-detail discrepancies between plpgsql and Oracle's pl/sql, I'm not sure how much this will really make anyone's life easier. In any case, Michael's example is the preferred syntax because it is what the SQL spec calls for. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] I can't create a new database with GB18030 encoding
Huang GeHua [EMAIL PROTECTED] writes: I want create a new database with the GB18030 encoding.But the pgAdminIII tell a error GB18030 is not a valid encoding name.How can i do for it? The docs say that GB18030 is supported as a client-side encoding only. So pick any superset encoding (UTF8 maybe?) as the database encoding, and then set client_encoding to GB18030. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Toad Adm. Datasource Location
[EMAIL PROTECTED] wrote: I am just curious to see if I can get Toad to connect to Postgresql. I have Access talking to it via ODBC. Toad is asking for Datasource and Location. I cant figure out what to put there. I have done some searching in google. Thanks for any suggestions. Every version of Toad I've ever used connects to Oracle using the OCI driver. You'll never get the OCI driver talking to PG - completely different communication protocol (well, never say never, but not today anyway...) -- Guy Rouillier ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] optimal hardware for postgres?
William Yu wrote: On other note -- if you are thinking about SMP Opteron, you may actually get better performance from 1x275 (Dual Core 2.2ghz) versus 2x248 (2.2ghz). Full duals have twice the bandwidth but without good NUMA support, memory has to be interleaved between CPUs. I thought the 2.6 kernel had good NUMA support? (I realize I just made an assumption that the original poster was running PG on Linux 2.6.) -- Guy Rouillier ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match