Re: [GENERAL] Connection question
- Original Message - From: "Craig Ringer" To: "Bayless Kirtley" Cc: "List, Postgres" Sent: Thursday, September 02, 2010 10:15 PM Subject: Re: [GENERAL] Connection question On 2/09/2010 11:59 PM, Bayless Kirtley wrote: Thanks a lot Craig. The register is connecting through localhost so it's not that. I guess I'm not surprised about the Windows thing. I suppose we'll just have to live with it then. I wouldn't be too sure yet. Did you run the test where you reboot the POS system with the ethernet unplugged? Does it still connect to the database OK? I didn't think XP broke TCP/IP connections to *localhost* when an ethernet interface went up or down, unless the connection was to a local IP associated with that interface. If you're connected to 127.0.0.1 from 127.0.0.1 it shouldn't, AFAIK, matter if you lose your wifi/ethernet. Come to think of it, you might want to check to make sure the local Ethernet interface is set to use DHCP. If it's using a static IP, Windows might permit that IP to be used to talk to the local host even when there's no link on the interface. So - to be sure, you can disable the Ethernet interface in the network control panel before your reboot for testing. Anyway, if you really can't make it work properly, there's a simple if mildly clumsy workaround. You can prevent XP from realizing it's lost its connection to the router by placing another device between the XP machine(s) and the router, like a plain old cheap switch. That way Ethernet link on the XP machines is never lost when you reboot the router - only between the router and the switch, where the XP machines can't tell. When the router goes down they lose their DHCP server, but that doesn't matter if it comes up again before they try to renew their DHCP leases, and for local-to-local traffic that's about all they'll be relying on the router for. You could even avoid that by setting static IP addresses. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/ Hmmm, more to consider then. I have not done the test you suggested. Guess I pounced on the Windows losing TCP/IP prematurely. It has been a while but I think I set them up for static IP addresses initially. I did check to be certain it is using localhost though. I will catch a slow day soon and try your ideas. I'll report the results back soon. Thanks Craig, Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection question
Thanks a lot Craig. The register is connecting through localhost so it's not that. I guess I'm not surprised about the Windows thing. I suppose we'll just have to live with it then. Fortunately it doesn't happen too often. But as fate would have it, most often seems when several customers are standing in line trying to pay. I tried initially to get the customer to go Linux instead of Windows but without much luck. Thanks again for the thorough explanation. Bayless - Original Message - From: "Craig Ringer" To: "Bayless Kirtley" Cc: "List, Postgres" Sent: Wednesday, September 01, 2010 10:08 PM Subject: Re: [GENERAL] Connection question On 09/01/2010 11:22 PM, Bayless Kirtley wrote: About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. With modern OSes, and many much older ones, it's not "of course" at all. Windows XP Pro is quite odd in that it breaks TCP/IP connections when an interface goes down and comes back up with the same IP address. Windows 7 even retains my SSH connections, made over wifi, when suspended and resumed! They only break if PuTTY tries to send a packet while the interface is still down after resume. Really, resetting a switch, unplugging a network cable and plugging it back in, etc shouldn't break TCP/IP connections, unless it triggers the connected host to do a new DHCP request, and the DHCP server hands out a different IP. No decent DHCP server will do that, but some cheap and nasty modem/router units don't store DHCP leases across a reboot so they "forget" their MAC address to IP address mappings. Anyway, that's a bit of a tangent, since you're on XP Pro and stuck with its rather less than ideal handling of connection loss. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error "Unable to write to the backend" or something very close to that. Is the register application connecting to localhost (127.0.0.1) or to the public IP address assigned by DHCP to the register's ethernet interface? If the latter, you're being bitten by Windows XP tossing out all TCP/IP connections involving that IP. If you're not sure, the easiest way I can think of to find out is to unplug the register from the network, restart the router and see if it can still connect. It should be able to. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connection question
I have a two-user point-of-sale application on Windows XP PRO. The DB runs on the cash register. The second user is a manager's computer. They are connected through a wired router which is also connected to an internet cable modem. The manager's computer just checks statistics on the DB and maintains a connection all the time. The cash register application does not do any network operations and is not even aware of the network. About twice per month, it is necessary to reset the modem and router. This, of course, loses the manager's connection to the DB. The problem is, it also seems to break the connection at the cash register. The next time it tries to record a transaction, it gets the error "Unable to write to the backend" or something very close to that. The DB itself seems fine as we can restart the cash register and all is well. Anyone have any idea why this happens? We are running PostgreSQL 8.3.1-1 and the applications are Java 1.6.14. Bayless
Re: [GENERAL] PostgreSQL Performance issue
Thanks a lot for your help. However I am new to Postgres database therefore it would be nice if you can let me know how to set autocommit off. I know from psql client issuing “\set Autocommit Off” would set it off but unfortunately it doesn’t set it off. It's a client-side setting, not a server-side one. Hence you need to turn it off in your java program, probably in the database connection settings. P.S. Your message was so mangled that I couldn't find which part of the original message you were referring to, so I deleted the remainder. Top-posting is considered bad form in mailing-lists. Alban Hertroys Actually, top posting is considered bad form in THIS mailing list. All the others I participate in top post regularly. That said though, it is considered good form to post in the manner expected by the list that one is posting to. ;-) Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
- Original Message - From: Adam Rich To: Sam Mason ; vinny Cc: pgsql-general@postgresql.org Sent: Monday, August 24, 2009 2:58 PM Subject: Re: R: [GENERAL] Field's position in Table From: vinny Subject: Re: R: [GENERAL] Field's position in Table To: "Sam Mason" Cc: pgsql-general@postgresql.org Date: Monday, August 24, 2009, 2:38 PM On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: > On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: > > I can't really think of any real reason to put the field at a > > particular position, applications don't reallty care about the order > > of fields. > > Because it's very convenient for ad-hoc queries! PG currently assumes > that the column order is the same as when it was created but there are > (unimplemented) suggestions about how to "fix" this. See for example: > > http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php > > -- > Sam http://samason.me.uk/ > But how is it convenient exactly, is it just a timesaver so you can SELECT * instead of having to type SELECT firstname, lastname, email? For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file. Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public. Most programmers habitually organize their source code to keep related functions together. It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions. And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones. Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code. So when I'm working in PgAdmin, I like to see my primary & foreign keys listed first, then data fields in logical groupings, and finally the standard "footer" fields we add to all tables like create & update by/date. Whenever I'm developing and need to reference a table definition, (or do a select * in pgAdmin for sample data) I lose productivity having to scan through all the fields repeatedly instead of seeing at a glance the fields I want because I know where they *should* be in the listing. Sometimes I have to scan through the fields several times before I finally see the one I want, because it was in the middle of unrelated items. I *never* code my applications to depend on field order; I'm referring to development convenience only. (Just my two cents, YMMV, etc) Just another two cents agreeing here. I think programmers tend to be a bit anal about this sort of thing. True, it makes no material difference but one just tends to be more comfortable with everything nicely organized. Bayless
Re: [GENERAL] Does derby have an embedded Mode like Derby ?
- Original Message - From: "Paul Taylor" To: Sent: Tuesday, August 04, 2009 9:56 AM Subject: Re: [GENERAL] Does derby have an embedded Mode like Derby ? Of course you can always use Derby for testing the Postgres later. I have found HSQLDB to be closer to Postgres than Derby is. I do seem to recall the Netbeans group recommends using sever mode during development then switch to imbedded for distribution. I have done it both ways with equal success and similar efforts. Bayless Thanks I am trying to use Derby but fallen foul of Postgres array datatype which Derby doesn't support, Ive just had a quick look at HSQLDB and it doesn't appear to support it either. Paul Yes, I'm afraid you're gonna be out of luck on finding the array type in any of the smaller embedded databases. Honestly, the beg project I've been on for a year or so has used Postgres right through full development and testing. It's not hard to start and stop the database whenever I need to although I usually just leave it running. Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does derby have an embedded Mode like Derby ?
Paul Taylor, 04.08.2009 15:48: Thats a shame, I wanted to write junit test for a java program that queried a database, requiring a full database to be available for unit tests is not really an environment I want to have. Well if you want to test the database, you will need a full database. But you can easily setup and start a Postgres server through a batch file/shell script. I have a small batch file that simply uses the binary ZIP distribution to run initdb and create the initial databases. We are using this to bundle Postgres e.g. with a Tomcat web application. You don't really need to run an installer and/or create registry entries (for windows). This would then resemble more the Derby network server setup. Thomas Of course you can always use Derby for testing the Postgres later. I have found HSQLDB to be closer to Postgres than Derby is. I do seem to recall the Netbeans group recommends using sever mode during development then switch to imbedded for distribution. I have done it both ways with equal success and similar efforts. Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Serious JDBC problem
For some reason, the PostgreSQL JDBC driver fails to properly recognize daylight saving time. When retrieving the current time as a java.sql.Time, if returns an hour behind when DST is in effect. Here is a tested sample Java code that demonstrates the error.. private void getTime(Connection conn) { try { Statement stmt = conn.createStatement(); ResultSet result = stmt.executeQuery("Select CURRENT_TIME"); result.next(); String timeStr = result.getString(1); System.out.println("String: " + timeStr); java.sql.Time time = result.getTime(1); System.out.println("Time: " + time); java.sql.Time sysTime = new java.sql.Time(new java.util.Date().getTime()); System.out.println("Run at:" + sysTime); } catch (SQLException e) { System.out.println(e.getMessage()); } } The output from running this code is: String: 09:23:37.302-05 Time: 08:23:37 Run at: 09:23:37 As you can see it was run at 09:23:37 local daylight saving time today. Retrieving PostgreSQL time as a String also gives that same value. Retrieving it as a Time gives a value of one hour earlier. Presumably it is not recognizing daylight saving time. Since it produces two quite different values for the same query, this seems to be a serious flaw in the JDBC driver. The driver in use is "org.postgresql.Driver" with PostgreSQL 8.3.1-1, installed about a year ago. I have tried this with postgresql-8.3-603.jdbc3.jar and postgresql-8.3-603.jdbc4.jar with the same result. I am using Java 1.6 but it did the same thing last year with 1.5. Bayless
Re: [GENERAL] Switching databases over JDBC/ODBC
You can establish a new connection without dropping the old one. Thus you can be connected to 2 databases at the same time. Just use the appropriate connection for the database you want. Bayless - Original Message - From: "Scott Bailey" To: "pgsql-general" Sent: Friday, May 29, 2009 6:34 PM Subject: [GENERAL] Switching databases over JDBC/ODBC Is there an equivalent to psql's \connect database or MySQL/MS SQL's use database command that will work with JDBC? It doesn't seem like I should need to drop the connection and establish a new one just to switch databases. Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight saving time question
Apparently this did not go through the first time. I'll try again. > Bayless Kirtley wrote: >> Thanks Tom and Scott. You got me looking in the right direction. In this >> case >> the client and server are on the same machine (testing/development) and >> psql >> does return the right result. I tried all the possibilities from the java >> program, >> "show timezone", "select current_time" and "select current_timestamp". >> These >> were all JDBC queries. When I used result.getString(), the values looked >> right. When I used result.getTime(), they were off by one hour as if >> daylight saving were not in effect. > > If 'result' is a Java 'java.util.Date' type (or one of its java.sql > subtypes), then it only holds milliseconds since epoch as if in GMT; > 'Date' holds no timezone information as such. In that situation, > 'result.getTime()' returns a 'long' value. > > How exactly are you displaying 'result.getTime()'? How exactly are you > determining that its value is "off" by one hour? Can you show us Java > code? > >> Is this a flaw in the JDBC driver or is that the expected behavior? In >> either case I do now have a workaround but would like to know. > > It is not a flaw in the JDBC driver. > > -- > Lew > > -- Here is a simplified and tested version of the exact Java code. private void run() { try { Statement stmt = connection.createStatement(); ResultSet result = stmt.executeQuery("Select CURRENT_TIME"); result.next(); String timeStr = result.getString(1); System.out.println("String: " + timeStr); java.sql.Time time = result.getTime(1); System.out.println("Time: " + time); java.sql.Time sysTime = new java.sql.Time(new java.util.Date().getTime()); System.out.println("Run at:" + sysTime); } catch (SQLException e) { System.out.println(e.getMessage()); } } The output from running this code is: String: 09:17:46.427-05 Time: 08:17:46 Run at: 09:17:46 As you can see it was run at 09:17:46 local daylight saving time today. Retrieving Postgres time as a String also gives that same value. Retrieving it as a Time gives a value of one hour earlier. Presumably it is not recognizing daylight saving time. Since it produces two quite different values for the same query, I would suggest that this is in fact a flaw in the JDBC driver, unless, of course, it is actually intended to behave in that manner for some reason I am not aware of. As stated earlier, the driver in use is "org.postgresql.Driver" and I am using PostgreSQL 8.3.1-1, installed about a year ago. Bayless
Re: [GENERAL] Daylight saving time question
- Original Message - From: "Lew" To: Sent: Saturday, May 23, 2009 2:18 PM Subject: Re: [GENERAL] Daylight saving time question Bayless Kirtley wrote: Thanks Tom and Scott. You got me looking in the right direction. In this case the client and server are on the same machine (testing/development) and psql does return the right result. I tried all the possibilities from the java program, "show timezone", "select current_time" and "select current_timestamp". These were all JDBC queries. When I used result.getString(), the values looked right. When I used result.getTime(), they were off by one hour as if daylight saving were not in effect. If 'result' is a Java 'java.util.Date' type (or one of its java.sql subtypes), then it only holds milliseconds since epoch as if in GMT; 'Date' holds no timezone information as such. In that situation, 'result.getTime()' returns a 'long' value. How exactly are you displaying 'result.getTime()'? How exactly are you determining that its value is "off" by one hour? Can you show us Java code? Is this a flaw in the JDBC driver or is that the expected behavior? In either case I do now have a workaround but would like to know. It is not a flaw in the JDBC driver. -- Lew -- Here is a simplified and tested version of the exact Java code. private void run() { try { Statement stmt = connection.createStatement(); ResultSet result = stmt.executeQuery("Select CURRENT_TIME"); result.next(); String timeStr = result.getString(1); System.out.println("String: " + timeStr); java.sql.Time time = result.getTime(1); System.out.println("Time: " + time); java.sql.Time sysTime = new java.sql.Time(new java.util.Date().getTime()); System.out.println("Run at:" + sysTime); } catch (SQLException e) { System.out.println(e.getMessage()); } } The output from running this code is: String: 09:17:46.427-05 Time: 08:17:46 Run at: 09:17:46 As you can see it was run at 09:17:46 local daylight saving time today. Retrieving Postgres time as a String also gives that same value. Retrieving it as a Time gives a value of one hour earlier. Presumably it is not recognizing daylight saving time. Since it produces two quite different values for the same query, I would suggest that this is in fact a flaw in the JDBC driver, unless, of course, it is actually intended to behave in that manner for some reason I am not aware of. As stated earlier, the driver in use is "org.postgresql.Driver" and I am using PostgreSQL 8.3.1-1, installed about a year ago. Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight saving time question
- Original Message - From: "Tom Lane" To: "Bayless Kirtley" Cc: "John R Pierce" ; "PostgreSQL" ; Sent: Monday, May 18, 2009 3:22 PM Subject: Re: [GENERAL] Daylight saving time question "Bayless Kirtley" writes: Is this a flaw in the JDBC driver or is that the expected behavior? You'd be more likely to get the correct answer on pgsql-jdbc. regards, tom lane Tom, I'm using org.postgresql.Driver from the jar file postgresql-8.3-603.jdbc3.jar that came with my distribution. Is that not the correct one? Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight saving time question
Thanks Tom and Scott. You got me looking in the right direction. In this case the client and server are on the same machine (testing/development) and psql does return the right result. I tried all the possibilities from the java program, "show timezone", "select current_time" and "select current_timestamp". These were all JDBC queries. When I used result.getString(), the values looked right. When I used result.getTime(), they were off by one hour as if daylight saving were not in effect. Is this a flaw in the JDBC driver or is that the expected behavior? In either case I do now have a workaround but would like to know. Thanks again. - Original Message - From: "Tom Lane" To: "Bayless Kirtley" Cc: "John R Pierce" ; "PostgreSQL" Sent: Monday, May 18, 2009 12:26 PM Subject: Re: [GENERAL] Daylight saving time question "Bayless Kirtley" writes: For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as "timezone = 'America/Chicago'" and "timezone = 'CST6CDT'" both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. I have a Java application on Windows XP PRO and the way I am getting the time is "Select CURRENT_TIME". Is there something I am missing or is there another way I should be getting the time? Are you sure the system's time is actually set correctly on the server machine? Seems like confusion between standard and daylight time in setting the server's clock might be the underlying issue here. Another theory is that the database is perfectly fine but there's something wacko happening on the Java side. Have you tried running "select current_time" from some other application, like psql? (In that connection I note that "select current_time" only gives time of day not a full timestamp, so I'd not exactly be surprised if it does confuse Java. "select current_timestamp" produces a much less ambiguous result.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Daylight saving time question
- Original Message - From: "John R Pierce" To: "Bayless Kirtley" ; "PostgreSQL" Sent: Sunday, May 17, 2009 10:19 PM Subject: Re: [GENERAL] Daylight saving time question Bayless Kirtley wrote: How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. it uses your client's specified local time zone to determine whether or not DST is in effect. SET TIME ZONE 'America/New York'; or SET TIME ZONE 'PST8PDT'; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general For some reason I can't seem to make it work. I have tried setting the timezone in postgresql.conf as "timezone = 'America/Chicago'" and "timezone = 'CST6CDT'" both of which still returned one hour behind. I also tried both of your suggestions as SQL statements right after establishing a database connection and still get the same wrong time. I have a Java application on Windows XP PRO and the way I am getting the time is "Select CURRENT_TIME". Is there something I am missing or is there another way I should be getting the time? Thanks again -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Daylight saving time question
How can I tell PostgreSQL to use daylight saving time when applicable? Times returned by the database are one hour behind. TIA Bayless
Re: [GENERAL] open transaction?
- Original Message - From: "Terry Lee Tucker" To: Sent: Monday, December 29, 2008 5:57 AM Subject: Re: [GENERAL] open transaction? On Monday 29 December 2008 06:48, blackwater dev wrote: I just logged into postgres from the command line and did: begin: select blah; select blah; \q Without thinking I closed by connection before committing or rolling back my transaction. Did postgres handle this for me? How do I see if the transaction is still open? Thanks! If you don't commit, it is rolled back when you exit. -- Terry Lee Tucker Turbo's IT Manager Turbo, division of OHL 2251 Jesse Jewell Pkwy Gainesville, GA 30501 tel: (336) 372-6821 cell: (336) 404-6987 te...@turbocorp.com www.turbocorp.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general But if all you did was SELECT, as you indicated, then no transaction should have been started and it makes no difference. Bayless -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Database access over the Internet...
Michelle, I don't think the list is going to change its operations for one disgruntled user. Since you seem unwilling or unable to employ the advice already given, maybe your only acceptable option is to unsubscribe from the list. At least that would eliminate much of the noise that currently is cluttering everyone's inboxes. - Original Message - From: "Michelle Konzack" <[EMAIL PROTECTED]> To: Sent: Sunday, November 16, 2008 4:41 AM Subject: Re: [GENERAL] Database access over the Internet... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MS Access and PostgreSQL - a warning to people thinking about it
Right, passthrough queries is the answer. I once had to build an Access front end to a very large Oracle database for a client. As you discovered, directAccess queries can be extermely slow and large. With a passthrough, all the real work is done on the host database and just the result you want is returned. Bayless - Original Message - From: "Richard Huxton" <[EMAIL PROTECTED]> To: "Craig Ringer" <[EMAIL PROTECTED]> Cc: "PostgreSQL" Sent: Tuesday, November 18, 2008 3:46 AM Subject: Re: [GENERAL] MS Access and PostgreSQL - a warning to people thinking about it Craig Ringer wrote: Hi all I've been forced into a project that uses MS Access with PostgreSQL. [snip] The big issue is with Access's linked table support via ODBC (at least as of Access 2007). Unlike tools like Hibernate, which are capable of executing filters, queries across multiple tables, etc server-side, Access will ALWAYS fetch the full contents of the linked table then do its filters and joins client-side. Yep - you want "pass through" queries if you want to execute them server-side. You can "save" the query definition and use it as a view within Access then though. What I've had to do where I need proper parameterised query support is build it myself from regular expressions and string-replacements. Ick. This might not matter too much if your tables are small, your database load is light, and you're on a fast link. If your tables are large and your users want to work over a GSM/HSDPA mobile phone link, on the other hand... Access can, of course, transparently execute queries server-side if used with MS SQL server, as it doesn't use its ODBC linked table support for this but rather different and more capable features targeted specifically at MS SQL Server. I'm guessing they both share some library code for query parsing etc. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgdump problem or question?
Early Friday morning a bad record caused me to reload the Thursday night pgdump backup. I performed a pgdump first to study later. In the backup I found several incomplete transactions all done at the end of the day. Investigating later I found the original bad record from the Friday dump and fixed it then found that those incomplete transactions were complete in that version. The client shuts the system down nightly by first closing the application then performing a pgdump on the database before shutting it down then shuts down the computer (Windows XP PRO, PostgreSQL 8.3.3). The pgdump would have taken place at least a couple of minutes after the application shutdown. I thought pgdump was suitable for a live backup of the database. This would seem to be wrong if it dumps partial transactions. Do I understand correctly? If so, is there something else I need to do before the dump? Any help will be greatly appreciated. This does shake my confidence in my backup procedures. Thanks, Bayless
[GENERAL] Moved database question
I had to move my database to a new disk. I dropped the original after a pg_dump and then removed the old directory. I then ran initdb on the new location, created the database and restored from the pg_dump. My problem now is that I have to supply the full pathnames to start or stop the server. Otherwise it still wants to access the old location which no longer exists. What am I missing to make the new (and only existing) location be the Postgres default? Thanks, Bayless
[GENERAL] Cause of error message?
An old app is giving a new error message. I query a table and make one update. Later I come back to query the table again and get this error: ERROR: current transaction is aborted, commands ignored until end of transaction block The error only occurs after I have made an update. As long as no updates, I can query multiple times. Can anyone give me an idea of what sort of actions or errors usually cause this error? Thanks, Bayless
[GENERAL] Closed ResultSet error
I am migrating from another database to PostgreSQL and have run into a serious problem. The Java program runs fine on the other DB but under PostgreSQL it gives the error, "ResultSet is closed." This result set is created in a method that processes each row sequentially. Within the processing, it passes the result set to another method that retrieves columns from it and creates additional result sets to retrieve related information. This second method can also call itself recursively. This works fine with HSQLDB but fails when returning back to the original method and attempting another loop on the original result set, claiming it is closed. I have removed all occurrences of closing any result sets and there are no instances of closing underlying statements within those methods. Finally, I have stepped through the entire operation with a debugger and found no unexpected events. Each time a new result set was created, the debugger indicated it to be a new entity. When finally returning to the original, it appeared to be the correct instance. Is there a known problem along these lines? Any help or suggestions will be most appreciated. Thanks, Bayless
[GENERAL] No server after starting
First, I am new to Postgres. I am hoping to migrate an existing Java application from a couple of tried but unreliable open source Java databases. I have a fair amount of experience with Oracle, Informix and DB2 but it has been a few years. No on to my problem. I finally got PostgreSQL installed on Windows XP as a service. After restarting the computer, the Task Manager shows 5 postgres.exe processes running. From a CMD window, I issue a pg_ctl status command and get the response: "No server running." What am I doing wrong. I took all the defaults on installation except for assigning a password rather than a random generated one. Earlier today, I had it installed as a program and all worked fine after creating and initializing the database cluster. I was able to start the database from a non-admin user. Then I could check status and even shutdown as an admin user. I have added the ...\bin directory to the PATH and added the PGDATA environment variable. After this, I decided to remove Postgres and reinstall it as a service. Thanks for any help, Bayless