[GENERAL] current transaction is aborted, commands ignored until end of transaction block
Hi: After upgrading 7.4.2 to 7.4.5 quite smoothly in a Red Hat 8.0 box, we are having intermitent issues with certain online PHP transactions, returning this error: "Warning: pg_exec() query failed: ERROR: current transaction is aborted, commands ignored until end of transaction block" Half the times we run the query we get the error, the other half it works. Any ideas? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [HACKERS] OT moving from MS SQL to PostgreSQL
You could use Sun's ASP engine which was originally produced by chilisoft. http://wwws.sun.com/software/chilisoft/ -Edwin On Sun, 03 Oct 2004 11:24:28 -0600, Scott Marlowe <[EMAIL PROTECTED]> wrote: > On Sun, 2004-10-03 at 06:33, stig erikson wrote: > > Hello. > > i have an slightly off topic question, but i hope that somebody might know. > > > > at the moment we have a database on a MS SQL 7 server. > > This data will be transfered to PostgreSQL 7.4.5 or PostgreSQL 8 (when > > it is released). so far so good. > > > > the question now arises, this current database is used in web > > application made with ASP on IIS5. The idea is to move the database and > > the application to a linux or unix environment. Is there a tool that can > > be used convert ASP pages into PHP (or any other language suitable for > > linux/unix), or should we prepare to rewrite most of the code? > > > > Is there a tool, some add-in to apache perhaps that can run ASP code on > > linux/unix, this would help to have the system running while we recode > > the application. > > There are a few tools I've seen that will try to convert ASP to PHP, but > for the most part, they can't handle very complex code, so you're > probably better off just rewriting it and learning PHP on the way. > > By the way, I have moved this over to -general, as this is quite off > topic for -hackers. Next person to reply please remove the > pgsql-hackers address from the CC list please. > > ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pgsql 8 beta document
On Tue, 2004-10-05 at 09:08, Mage wrote: > Thank you. And is there some comparing 7.4 with 8.0 document? I mean a > simple "new feature list" or something like that. http://developer.postgresql.org/docs/postgres/release.html#RELEASE-8-0 -Neil ---(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] LOST REFERENTIAL INTEGRITY
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: >> I'd recommend an upgrade to 7.4.5 at your earliest convenience. >> > I have kept up-to-date our Red Hat kernels as you can probably see from > the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own > version of Postgres alongside and compiled into Red Hat's latest and > greatest kernel? If that's true, WHEW! Unfortunately I don't get to dictate Red Hat's backwards-compatibility policies :-( ... and their policy for AS 2.1 is that it's gonna be Postgres 7.1 till it dies. This means that anything that's fundamentally unfixable without an initdb is going to remain broken. > I wonder what version of > Postgres is installed in Red Hat's latest kernel of AS 3.0? RHEL3 uses the PG 7.3 release series, which is a little behind the times but far less likely to eat your data than 7.1. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pgsql 8 beta document
Alvaro Herrera wrote: On Mon, Oct 04, 2004 at 11:58:53PM +0200, Mage wrote: Is there any on the site? Or I shall find in the tar file? It's on http://developer.postgresql.org/docs/postgres Thank you. And is there some comparing 7.4 with 8.0 document? I mean a simple "new feature list" or something like that. Mage ---(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] pgsql 8 beta document
On Mon, Oct 04, 2004 at 11:58:53PM +0200, Mage wrote: > Is there any on the site? Or I shall find in the tar file? It's on http://developer.postgresql.org/docs/postgres -- Alvaro Herrera () Este mail se entrega garantizadamente 100% libre de sarcasmo. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] pgsql 8 beta document
Is there any on the site? Or I shall find in the tar file? Mage -- http://mage.hu ---(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] Cursors and JDBC
On Mon, 4 Oct 2004, Wiebe de Jong wrote: > I am trying to implement cursors using JDBC connector version 7.1b5 > (postgresql-7.1b5.jar), but can't get it to work. Before saying anything else, I have to tell you to get off 7.1, especially a beta version of it. The first JDBC driver to have cursor support was the 7.4 series. It has been tested for backward compatibility to 7.2, but not 7.1. That said I believe it should work against a 7.1 server. So download a 7.4 jar file from http://jdbc.postgresql.org/download.html and see this documentation: http://www.postgresql.org/docs/7.4/static/jdbc-query.html#JDBC-QUERY-WITH-CURSOR Kris Jurka ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 8.0 install woes
A final note…we are up and running. Thanks again for all the help. So post mortem indicates that 1) Port 5432 was not available, despite what our network guys told me; 2) In my rush to get things up and running, I forgot to make relevant changes to the .conf files (problem lay between the keyboard and the chair). Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945 From: Taber, Mark Sent: Monday, October 04, 2004 1:55 PM To: Taber, Mark; [EMAIL PROTECTED] Subject: RE: [GENERAL] PostgreSQL 8.0 install woes Thanks for your help. Over the protests of my network guys, I did a port scan, and sure enough, port 5432 wasn’t available. Also, my .conf file was only looking for localhost. Having worked through this, now there’s something wrong with my pg_hba.conf file. Again, thanks, all. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945 From: Taber, Mark Sent: Monday, October 04, 2004 11:29 AM To: [EMAIL PROTECTED] Subject: [GENERAL] PostgreSQL 8.0 install woes I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. I’m even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host “nnn.nnn.nnn.nnn” and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
On Mon, Oct 04, 2004 at 05:25:59PM -0400, Jimmie H. Apsey wrote: > I have kept up-to-date our Red Hat kernels as you can probably see from > the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own > version of Postgres alongside and compiled into Red Hat's latest and > greatest kernel? If that's true, WHEW! I wonder what version of > Postgres is installed in Red Hat's latest kernel of AS 3.0? The version of your kernel and the version of postgres are completely unrelated, you can upgrade either whenever you like independant of the other. The only thing you may need to look into is the version of libc and other such libraries. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgp8nAvokZvf7.pgp Description: PGP signature
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
Tom Lane wrote: "Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). OH, that's very scary for me that triggers can vanish/be eliminated w/o my direct action. Yes, I do now see that the triggers on my production table have been lost. I built a test table and they appear as expected. Is there any way I can prevent this or become aware that something had done this to my production database? If you are still running 7.1 you obviously do not know the meaning of the word "fear" ;-) --- it not only has lots of since-fixed bugs, but at that time we hadn't yet solved the transaction ID wraparound problem, which means your DB is guaranteed to self-destruct once you reach the 4-billion-transaction mark. I'd recommend an upgrade to 7.4.5 at your earliest convenience. regards, tom lane I have kept up-to-date our Red Hat kernels as you can probably see from the Linux 2.4.9-e.49smp kernel. Am I required to maintain my own version of Postgres alongside and compiled into Red Hat's latest and greatest kernel? If that's true, WHEW! I wonder what version of Postgres is installed in Red Hat's latest kernel of AS 3.0?
[GENERAL] Cursors and JDBC
I am trying to implement cursors using JDBC connector version 7.1b5 (postgresql-7.1b5.jar), but can’t get it to work. Could anybody suggest the proper way to do it, or even some source code? I am stuck with this version and can’t change it. Thanks Wiebe de Jong
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: >> Each FK constraint should have three associated triggers (two on the >> referencing table, one on the referenced table). > OH, that's very scary for me that triggers can vanish/be eliminated w/o > my direct action. Yes, I do now see that the triggers on my production > table have been lost. I built a test table and they appear as > expected. Is there any way I can prevent this or become aware that > something had done this to my production database? If you are still running 7.1 you obviously do not know the meaning of the word "fear" ;-) --- it not only has lots of since-fixed bugs, but at that time we hadn't yet solved the transaction ID wraparound problem, which means your DB is guaranteed to self-destruct once you reach the 4-billion-transaction mark. I'd recommend an upgrade to 7.4.5 at your earliest convenience. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL 8.0 install woes
Thanks for your help. Over the protests of my network guys, I did a port scan, and sure enough, port 5432 wasn’t available. Also, my .conf file was only looking for localhost. Having worked through this, now there’s something wrong with my pg_hba.conf file. Again, thanks, all. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945 From: Taber, Mark Sent: Monday, October 04, 2004 11:29 AM To: [EMAIL PROTECTED] Subject: [GENERAL] PostgreSQL 8.0 install woes I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. I’m even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host “nnn.nnn.nnn.nnn” and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945
Re: [GENERAL] Random not so random
"D. Stimits" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Hmm. postmaster.c does this during startup of each backend process: >> >> gettimeofday(&now, &tz); >> srandom((unsigned int) now.tv_usec); > If it uses the same seed from the connection, then all randoms within a > connect that has not reconnected will use the same seed. Which means the > same sequence will be generated each time, which is why it is > pseudo-random and not random. For it to be random not just the first > call of a new connection, but among all calls of new connection, it > would have to seed it based on time at the moment of query and not at > the moment of connect. A pseudo-random generator using the same seed > will generate the same sequence. Did you read what I said? Or experiment? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] OT moving from MS SQL to PostgreSQL
On P, 2004-10-03 at 20:24, Scott Marlowe wrote: > On Sun, 2004-10-03 at 06:33, stig erikson wrote: > > Is there a tool, some add-in to apache perhaps that can run ASP code on > > linux/unix, this would help to have the system running while we recode > > the application. > > There are a few tools I've seen that will try to convert ASP to PHP, but > for the most part, they can't handle very complex code, so you're > probably better off just rewriting it and learning PHP on the way. > > By the way, I have moved this over to -general, as this is quite off > topic for -hackers. Next person to reply please remove the > pgsql-hackers address from the CC list please. If you are adventurous you could also try to run ASP.NET on Mono as described in: http://www.pcquest.com/content/search/showarticle.asp?arid=47162&way=search -- Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
Tom Lane wrote: "Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. 7.1 is mighty ancient, but ... I do not know how to disable referential integrity on a column in a table. I do not know how to view what Postgres thinks my referential integrity constraints are on this table. In that version, you'd be talking about triggers on the tables, and it seems that psql's \d didn't learn to display triggers till later. You'll need to look at pg_trigger directly. For example, regression=# select version(); version -- PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3 (1 row) regression=# create table foo (f1 int primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# create table bar (f2 int references foo); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE regression=# \d foo Table "foo" Attribute | Type | Modifier ---+-+-- f1| integer | not null Index: foo_pkey -- drat, no trigger display regression=# select * from pg_trigger order by oid desc limit 3; tgrelid |tgname| tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs -+--+++---++--+---+--++-++ 2913646 | RI_ConstraintTrigger_2913673 | 1655 | 17 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913646 | RI_ConstraintTrigger_2913671 | 1654 | 9 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913659 | RI_ConstraintTrigger_2913669 | 1644 | 21 | t | t | | 2913646 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 (3 rows) regression=# Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). You can sort out which is which by looking at the tgargs field --- note how the referencing and referenced table and field names are embedded in that. I suspect that some of these triggers got dropped or disabled. If you don't find all three triggers for some one constraint, the best bet is to drop any remaining triggers from the set and then issue ALTER TABLE ADD FOREIGN KEY to re-make a consistent trigger set. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org OH, that's very scary for me that triggers can vanish/be eliminated w/o my direct action. Yes, I do now see that the triggers on my production table have been lost. I built a test table and they appear as expected. Is there any way I can prevent this or become aware that something had done this to my production database? On my machine: [~]$ mpt -c"select version();" version - PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) [~]$ I'll now go, as suggested by you, drop triggers on the test database to see to it that it actually works as expected. Then I'll re-build the FK triggers within the test database before I do it to the production database.
Re: [GENERAL] Random not so random
Tom Lane wrote: "Arnau Rebassa" <[EMAIL PROTECTED]> writes: I'm using a debian linux as OS with a 2.4 kernel running on it. Incidentally, are you reconnecting every time or is it that multiple calls in a single session are returning the same record? I'm reconnecting each time I want to retrieve a message. Hmm. postmaster.c does this during startup of each backend process: gettimeofday(&now, &tz); srandom((unsigned int) now.tv_usec); If it uses the same seed from the connection, then all randoms within a connect that has not reconnected will use the same seed. Which means the same sequence will be generated each time, which is why it is pseudo-random and not random. For it to be random not just the first call of a new connection, but among all calls of new connection, it would have to seed it based on time at the moment of query and not at the moment of connect. A pseudo-random generator using the same seed will generate the same sequence. D. Stimits, stimits AT comcast DOT net ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.0 install woes
Taber, Mark wrote: I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. I’m even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host “nnn.nnn.nnn.nnn” and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. **Mark Taber** State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 //x//294 On the off-chance that you're running ZoneAlarm, make sure that postgres is allowed to run as a server. Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgreSQL 8.0 install woes
Title: Meddelande Make sure the server actually listens on connections other than 127.0.0.1 - check "listen_addresses" in postgresql.conf. //Magnus -Ursprungligt meddelande-Från: Taber, Mark [mailto:[EMAIL PROTECTED] Skickat: den 4 oktober 2004 20:29Till: [EMAIL PROTECTED]Ämne: [GENERAL] PostgreSQL 8.0 install woes I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. Im even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host nnn.nnn.nnn.nnn and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945
Re: [GENERAL] trouble installing plpgsql
Wiebe de Jong <[EMAIL PROTECTED]> writes: > When I attempt to run 'createlang plpgsql template1' I get the following > error: > Error at or near "createlang" at character 1 createlang is a shell script, not an SQL command. Run it from the shell. 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
Re: [GENERAL] PostgreSQL 8.0 install woes
"Taber, Mark" <[EMAIL PROTECTED]> writes: > Error connecting to the server: could not connect to the server: Connection > refused (0x274D/10061) That means the operating system rejected the connection, which probably means a firewall/packet filter problem. If the request had made it as far as the postmaster, you'd have gotten a different response. > The answer is, yes the server is running on the host, and there are no > restrictions on either machine for port 5432. I think you're dead wrong on that last assertion. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] LOST REFERENTIAL INTEGRITY
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes: > Referential Integrity on one of our production tables seems to have been > lost. I am running Postgres 7.1.3 embedded within Red Hat > kernel-2.4.9-e.49. 7.1 is mighty ancient, but ... > I do not know how to disable referential integrity on a column in a table. > I do not know how to view what Postgres thinks my referential integrity > constraints are on this table. In that version, you'd be talking about triggers on the tables, and it seems that psql's \d didn't learn to display triggers till later. You'll need to look at pg_trigger directly. For example, regression=# select version(); version -- PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3 (1 row) regression=# create table foo (f1 int primary key); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo' CREATE regression=# create table bar (f2 int references foo); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE regression=# \d foo Table "foo" Attribute | Type | Modifier ---+-+-- f1| integer | not null Index: foo_pkey -- drat, no trigger display regression=# select * from pg_trigger order by oid desc limit 3; tgrelid |tgname| tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs -+--+++---++--+---+--++-++ 2913646 | RI_ConstraintTrigger_2913673 | 1655 | 17 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913646 | RI_ConstraintTrigger_2913671 | 1654 | 9 | t | t | | 2913659 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 2913659 | RI_ConstraintTrigger_2913669 | 1644 | 21 | t | t | | 2913646 | f| f | 6 || \000bar\000foo\000UNSPECIFIED\000f2\000f1\000 (3 rows) regression=# Each FK constraint should have three associated triggers (two on the referencing table, one on the referenced table). You can sort out which is which by looking at the tgargs field --- note how the referencing and referenced table and field names are embedded in that. I suspect that some of these triggers got dropped or disabled. If you don't find all three triggers for some one constraint, the best bet is to drop any remaining triggers from the set and then issue ALTER TABLE ADD FOREIGN KEY to re-make a consistent trigger set. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] trouble installing plpgsql
Hello, When I attempt to run ‘createlang plpgsql template1’ I get the following error: Error at or near “createlang” at character 1 Running psql 7.3.4 on redhat 9 Logged in as postgres Verified existence of /usr/lib/pgsql/plpgsql.so Any ideas on how to fix this problem? Wiebe de Jong
[GENERAL] PostgreSQL 8.0 install woes
I have Postgres 8.0-beta2 set up on two machines (one Windows 2000 Server, the other Windows XP Pro); I have Postgres up and running as a service on both machines, no problem. I’m even able to go into psql and putz around. However, I am not able to log on remotely using pgAdmin III. Whenever I attempt to add a server, I get the following message: An error has occurred: Error connecting to the server: could not connect to the server: Connection refused (0x274D/10061) Is the server running on host “nnn.nnn.nnn.nnn” and accepting TCP/IP connections on port 5432? The answer is, yes the server is running on the host, and there are no restrictions on either machine for port 5432. It fails in both directions. The machines can see each other on the network. Thanks in advance for all your help. Mark Taber State of California Department of Finance Infrastructure & Architecture Unit 916.323.3104 x2945
[GENERAL] LOST REFERENTIAL INTEGRITY
Referential Integrity on one of our production tables seems to have been lost. I am running Postgres 7.1.3 embedded within Red Hat kernel-2.4.9-e.49. Within that I have a table with referential integrity constraints which no longer work. I do not know how to disable referential integrity on a column in a table. I do not know how to view what Postgres thinks my referential integrity constraints are on this table. I do ...-c"\d table_with_referential_integrity" and here's what I get: [~]$ mpt -c"\d pat_emp_ins" Table "pat_emp_ins" Attribute | Type | Modifier ---+---+-- pat_id| text | not null ins_co_id | text | not null employer_id | text | not null insurance_group| text | note| text | print_note_primary | boolean | print_note_secondary | boolean | Indices: pat_emp_ins_employer_id_key, pat_emp_ins_ins_co_id_key, pat_emp_ins_pat_id_key [~ create_tables_for_database]$ And here is the SQL I used to generate this table: -- create table pat_emp_ins (pat_id text not null references patient, ins_co_id text not null references insurance_company, employer_id text not null references employer, insurance_group text, note text, print_note_primary boolean, print_note_secondary boolean, unique(pat_id,ins_co_id,employer_id)); -- Problem is, my users using my application are able to insert rows into "pat_emp_ins" table which have values for "employer_id" and/or "ins_co_id" which do not exist in the referenced tables. This seems to have happened recently but I do not know how recently. This application has been running production since 2003-11-07. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] VACUUM FULL on 24/7 server
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Matthew T. O'Connor wrote: | Gaetano Mendola wrote: | |> Matthew T. O'Connor wrote: |> |>> Since you are running autovacuum I doubt the doing vacuumdb -a -z is 3 |>> times a day buying you much. It's not a bad idea to do once in a while. |> |> |> |> The reason is that I have few tables of about 5 milion with ~ 1 |> insert per |> day. Even with setting -v 300 -V 0.1 this means these tables will be |> analyzed |> each 50 days. So I have to force it. | | | I understand, but 10,000 new rows increate your table size only 0.2%, so | it won't significantly effect anything. Also, if they really are just | inserts then vacuum is totally unnecessary. I agree that for these | situations pg_autovacuum should be supplemented by vacuumdb -a -z every | once in a while, all I was pointing out was that 3 times a day is | probably excessive. Right, but the table collect logs, so is mandatory have the statistics up-to-date in order to obtain index scan for queries that are involving the last 24 hours. For the vacuum vs the analyze I do vacuum because other tables are not in this category of "only update" so instead of write tons of line in my crontab I prefer only one line. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.5 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBYYm77UpzwH2SGd4RAmilAJ98skWgiKI7mqOgYIgigzgpLe0JpQCfRm8/ IPXFZwZVcdJP0RQCE1fPXpw= =CExm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Random not so random
On Mon, Oct 04, 2004 at 18:58:41 +0200, Marco Colombo <[EMAIL PROTECTED]> wrote: > > Actually, that should be done each time the random() function > is evaluated. (I have no familiarity with the code, so please That may be overkill, since I don't think that random has been advertised as a secure or even particularly strong random number generator. > bear with me if the suggestion is unsound). I'd even add a parameter > for "really" random data to be provided, by reading /dev/random > instead of /dev/urandom (but read(2) may block). You don't want to use /dev/random. You aren't going to get better random numbers that way and blocking reads is a big problem. > How about the following: > random() = random(0) = traditional random() > random(1) = best effort random() via /dev/urandom > random(2) = wait for really random bits via /dev/random It might be nice to have a secure random function available in postgres. Just using /dev/urandom is probably good enough to provide this service. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] i'm really desperate: invalid memory alloc request
Am Freitag, 1. Oktober 2004 10:56 schrieb Richard Huxton: > Janning Vygen wrote: > > tonight my database got corruppted. before it worked fine. > > in the morning some sql queries failed. it seems only one table was > > affected. i stopped all web access and tried to backup the current > > database: > > > > pg_dump: ERROR: invalid memory alloc request size 0 > > pg_dump: SQL command to dump the contents of table "fragentipps" failed: > > PQendcopy() failed. > > pg_dump: Error message from server: ERROR: invalid memory alloc request > > size 0 > > pg_dump: The command was: COPY public.fragentipps (tr_kurzname, mg_name, > > fr_id, aw_antworttext) TO stdout; > > Does it do this consistently at the same place? Yes. It is in one table if i select a certain row. How can stuff like this can happen? > > i tried to recover from backup which was made just before clustering > > but i got > > ERROR: index row requires 77768 bytes, maximum size is 8191 > > There are a few steps - you've already done the first > 1. Stop PG and take a full copy of the data/ directory > 2. Check your installation - make sure you don't have multiple > versions of pg_dump/libraries/etc installed > 3. Try dumping individual tables (pg_dump -t table1 ...) > 4. Reindex/repair files > 5. Check hardware to make sure it doesn't happen again. > > Once you've dumped as many individual tables as you can, you can even > try selecting data to a file avoiding certain rows if they are causing > the problem. Ok, i can recreate most of the data. My main question is now: - Why does things like this can happen? - how often do they happen? > There's more you can do after that, but let's see how that works out. > > PS - your next mail mentions sig11 which usually implies hardware > problems, so don't forget to test the machine thoroughly once this is over. first i ran the long smart selftest: * === START OF READ SMART DATA SECTION === SMART Self-test log structure revision number 1 Num Test_DescriptionStatus Remaining LifeTime(hours) LBA_of_first_error # 1 Extended off-line Completed without error 00% 4097 - * AND * # smartctl -Hc /dev/hda smartctl version 5.1-18 Copyright (C) 2002-3 Bruce Allen Home page is http://smartmontools.sourceforge.net/ === START OF READ SMART DATA SECTION === SMART overall-health self-assessment test result: PASSED [...] * so SMART tells me that everything is fine. but in my messages * Oct 2 14:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 62 to 61 Oct 2 14:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 62 to 61 Oct 2 14:59:00 p15154389 /USR/SBIN/CRON[11428]: (root) CMD ( rm -f /var/spool/cron/lastrun/cron.hourly) Oct 2 15:19:55 p15154389 -- MARK -- Oct 2 15:20:46 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 61 to 63 Oct 2 15:20:46 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 61 to 63 Oct 2 15:31:22 p15154389 su: pam_unix2: session finished for user root, service su Oct 2 15:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Prefailure Attribute: 1 Raw_Read_Error_Rate changed from 63 to 61 Oct 2 15:50:45 p15154389 smartd[11205]: Device: /dev/hda, SMART Usage Attribute: 195 Hardware_ECC_Recovered changed from 63 to 61 * don't know what it means. after that i run memtest via a serial console for hours and hours but no errors where found! Its a little bit strange. It would feel much nicer if harddisk oder memory were damaged. so what could be the reason for SIG11?? is it save to use this machine again after testing memory and hardware? kind regards janning ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Random not so random
On Mon, 4 Oct 2004, Tom Lane wrote: "Arnau Rebassa" <[EMAIL PROTECTED]> writes: I'm using a debian linux as OS with a 2.4 kernel running on it. Incidentally, are you reconnecting every time or is it that multiple calls in a single session are returning the same record? I'm reconnecting each time I want to retrieve a message. Hmm. postmaster.c does this during startup of each backend process: gettimeofday(&now, &tz); srandom((unsigned int) now.tv_usec); which would ordinarily be fairly good at mixing things up. On some platforms I might worry that the microseconds part of gettimeofday might only have a few bits of accuracy, but I don't think that's an issue on Linux. It occurs to me that you might be seeing predictability as an indirect result of something else you are doing that somehow tends to synchronize the backend start times. Are you connecting from a cron script that would tend to be launched at the same relative instant within a second? It might improve matters to make the code do something like srandom((unsigned int) (now.tv_sec ^ now.tv_usec)); How about reading from /dev/urandom on platforms that support it? Actually, that should be done each time the random() function is evaluated. (I have no familiarity with the code, so please bear with me if the suggestion is unsound). I'd even add a parameter for "really" random data to be provided, by reading /dev/random instead of /dev/urandom (but read(2) may block). How about the following: random() = random(0) = traditional random() random(1) = best effort random() via /dev/urandom random(2) = wait for really random bits via /dev/random .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(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] Random not so random
On Mon, Oct 04, 2004 at 10:14:19 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > It occurs to me that you might be seeing predictability as an indirect > result of something else you are doing that somehow tends to synchronize > the backend start times. Are you connecting from a cron script that > would tend to be launched at the same relative instant within a second? > > It might improve matters to make the code do something like > > srandom((unsigned int) (now.tv_sec ^ now.tv_usec)); Using /dev/urandom, where available, might be another option. However, some people may not want their entropy pool getting 4 bytes used up on every connection start up. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Server getting crushed
On Mon, 2004-10-04 at 08:59, Bryan wrote: > What would be the recommended server specs to use for a postgresql > database server that needs to be able to support upto 800 active > connections at one time, searching a table that in theory could be over > 2G in size? We have attempted this with a supermicro superserver > dualproc Xeon 2.8G with 6G of Ram, with a fiber array for database > storage and its not keeping up. We are pondering either trying a Quad > Xeon or a Dual Opertron. Unless someone has tips on how we could improve > the current server. That depends. Is your current server I/O or CPU bound, and what have you done to optimize its performance? ---(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
[GENERAL] Server getting crushed
What would be the recommended server specs to use for a postgresql database server that needs to be able to support upto 800 active connections at one time, searching a table that in theory could be over 2G in size? We have attempted this with a supermicro superserver dualproc Xeon 2.8G with 6G of Ram, with a fiber array for database storage and its not keeping up. We are pondering either trying a Quad Xeon or a Dual Opertron. Unless someone has tips on how we could improve the current server. Thanks, Bryan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] VACUUM FULL on 24/7 server
Gaetano Mendola wrote: Matthew T. O'Connor wrote: Since you are running autovacuum I doubt the doing vacuumdb -a -z is 3 times a day buying you much. It's not a bad idea to do once in a while. The reason is that I have few tables of about 5 milion with ~ 1 insert per day. Even with setting -v 300 -V 0.1 this means these tables will be analyzed each 50 days. So I have to force it. I understand, but 10,000 new rows increate your table size only 0.2%, so it won't significantly effect anything. Also, if they really are just inserts then vacuum is totally unnecessary. I agree that for these situations pg_autovacuum should be supplemented by vacuumdb -a -z every once in a while, all I was pointing out was that 3 times a day is probably excessive. Matthew ---(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] Random not so random
"Arnau Rebassa" <[EMAIL PROTECTED]> writes: > I'm using a debian linux as OS with a 2.4 kernel running on it. >> Incidentally, are you reconnecting every time or is it that multiple calls >> in a single session are returning the same record? > I'm reconnecting each time I want to retrieve a message. Hmm. postmaster.c does this during startup of each backend process: gettimeofday(&now, &tz); srandom((unsigned int) now.tv_usec); which would ordinarily be fairly good at mixing things up. On some platforms I might worry that the microseconds part of gettimeofday might only have a few bits of accuracy, but I don't think that's an issue on Linux. It occurs to me that you might be seeing predictability as an indirect result of something else you are doing that somehow tends to synchronize the backend start times. Are you connecting from a cron script that would tend to be launched at the same relative instant within a second? It might improve matters to make the code do something like srandom((unsigned int) (now.tv_sec ^ now.tv_usec)); regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] mirroring/replication
Is anybody using contrib/dbmirror in a production environment? - DAP == David ParkerTazz Networks(401) 709-5130 ---(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] Is Win32 port good for operational use?
I've been using the Win32 port of PostgreSQL for several months now on a pre-production test machine without much difficulty, in in my environment I've had more trouble with MySQL in general than PostgreSQL on Windows I'd recommend installing the beta and trying it out. Depending on the features you wish to utilize it sounds like SQLite may also be an option, it is a light-weight embeddable SQL solution, it uses a single file to store the database in making end user backups much easier and does not require an additional process on the client OS since it is embedded in the application. Jason Antonios Christofides wrote: Hi, I know we will be using at our own risk, I have read the "experimental" warnings, but still PostgreSQL is a very attractive RDBMS for the Windows application we are developing. Most customers will want a simple single-machine version, where the program will be storing its data in a local database without the user knowing much about it. Some customers will want a central database and will probably be given the option to choose between PostgreSQL and Oracle. The problem is to choose the RDBMS for the single-machine, single-user version. What if we have crashes or other critical bugs? Should we expect reasonable support from the developers in such cases? Is the number of Win32 developers decent? I mean, the Win32 project is not, I hope, supported by one or two key people and would go down if they decided to become sailors instead? :-) Needless to say, the developers will have all kind of help from us in tracking down important bugs, even access to our machines if necessary. ---(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 4: Don't 'kill -9' the postmaster
[GENERAL] weird issues with plpgsql calls.
Hi, I've made up 2 plpgsql calls, the table and function schema is available at http://www.bignose.ca/help/pg.txt I call them like this select ad_hits(33760); select ad_inquiries(33760); i did an "update ad_base set hits = 0, inquiries = 0" to initialize both rows, since i added them using alter table. if i call ad_inquiries a few times, the number increments just fine, but if i call ad_hits, it increments hits like it should, but also sets inquiries to zero. which is very strange and annoying ;) any help would be great, thanks ! PostgreSQL 7.3.4 on i386-unknown-freebsd5.1, compiled by GCC gcc (GCC) 3.2.2 [FreeBSD] 20030205 (release) -- Jeff MacDonald http://www.bignose.ca ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Is Win32 port good for operational use?
Hi, I know we will be using at our own risk, I have read the "experimental" warnings, but still PostgreSQL is a very attractive RDBMS for the Windows application we are developing. Most customers will want a simple single-machine version, where the program will be storing its data in a local database without the user knowing much about it. Some customers will want a central database and will probably be given the option to choose between PostgreSQL and Oracle. The problem is to choose the RDBMS for the single-machine, single-user version. What if we have crashes or other critical bugs? Should we expect reasonable support from the developers in such cases? Is the number of Win32 developers decent? I mean, the Win32 project is not, I hope, supported by one or two key people and would go down if they decided to become sailors instead? :-) Needless to say, the developers will have all kind of help from us in tracking down important bugs, even access to our machines if necessary. ---(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] Bug with updateable Views and inherited tables?
Tom Lane wrote: =?ISO-8859-1?Q?Sebastian_B=F6ck?= <[EMAIL PROTECTED]> writes: I investigated a little bit further and can be more precisely about the whole thing. This (wrong) behaviour only occurs, if the view has an order by clause. The bug is triggered by the combination of an inherited UPDATE target and an unflattenable sub-Query. I verified that it's been broken for as long as we've had such features :-(. I've applied the attached patch to 8.0. Thank you! > You could probably adapt it for 7.4, but I'm hesitant to put such a nontrivial change into a stable branch without a lot more testing. It isn't that necessary for me, just wondered 'bout the strange behaviour. Sebastian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Random not so random
Arnau Rebassa wrote: I don't know if there is the possibility to seed the random number generator manually, anybody knows it? setseed() -Neil ---(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] Random not so random
Hi Greg, What OS is this? Postgres is just using your OS's random()/srandom() calls. On some platforms these may be poorly implemented and not very random. I'm using a debian linux as OS with a 2.4 kernel running on it. Incidentally, are you reconnecting every time or is it that multiple calls in a single session are returning the same record? I'm reconnecting each time I want to retrieve a message. The idea is I have a lilbrary of messages and I want to pick one of it randomly. I don't know if there is the possibility to seed the random number generator manually, anybody knows it? Thanks to all -- Arnau _ ¿Cuánto vale tu auto? Tips para mantener tu carro. ¡De todo en MSN Latino Autos! http://latino.msn.com/autos/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
