Re: [GENERAL] Help with installation please...
On Tue, 2009-06-30 at 17:58 +0200, Rosko C.A. wrote: Hi. I'm trying to install Postgresql 8.3 in my computer but a windows appear asking me a password that i no have... If I click next button no continues... i don't know what can I do. Yesterday I tried to install pokertracker 3 (the latest version because i had already another version and everything was working perfect) and now i can't use this program because pokertracker 3 also ask me for a password. Thanks. Please read the documentation for the software you are using: http://www.pokertracker.com/products/PT3/docs/PokerTracker3_Manual_Uninstall_Reinstall_PostgreSQL.pdf If that doesn't help, consider contacting them for support. __ -- 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] The connection closed unexpectedly - C++ and Postgre
Greetings, I have a problem with postgresql that connect to a c++ server. I build some system servers that have connection to a postgresql server. When i add many object in the database, and the system server going to shoot the database server with the query. There will be an exception. The exception sound : Server closed the connection unexpectedly. The configuration of my database server is standard. When i look into the log on database server : connection reset by peer. Please help me with that problem. Thank you. -- View this message in context: http://www.nabble.com/The-connection-closed-unexpectedly---C%2B%2B-and-Postgre-tp24302840p24302840.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] The connection closed unexpectedly - C++ and Postgre
On Thu, Jul 2, 2009 at 2:05 AM, drestajumenadrestajum...@gmail.com wrote: Greetings, I have a problem with postgresql that connect to a c++ server. I build some system servers that have connection to a postgresql server. When i add many object in the database, and the system server going to shoot the database server with the query. There will be an exception. The exception sound : Server closed the connection unexpectedly. The configuration of my database server is standard. When i look into the log on database server : connection reset by peer. Please help me with that problem. That's odd. The client says the server closed the connection, the server says the client closed the connection. Most the time you see something like this it's a firewall or some other networking issue cropping up. Is this limited to certain size, duration, or something else unique about the query? Do you have a firewall between the machines? On one of the machines? I'd run tcpdump on both of them and watch what's happening on each one from there. -- 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] The connection closed unexpectedly - C++ and Postgre
If run the server, the server will be run for several minutes and then the error will happen. Most of error will held if the server run query : INSERT, DELETE. For database server, i use Postgre 8.3.1 on ubuntu 8.10 server. For system servers, i use standard C++. I am going to make an MMORPG. So i implements many servers. 1 MainServer and some AreaServer. Thank you ... -- View this message in context: http://www.nabble.com/The-connection-closed-unexpectedly---C%2B%2B-and-Postgre-tp24302840p24303787.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] 1 Sequence per Row i.e. each customer's first order starts at 1
On Thu, Jul 2, 2009 at 2:46 AM, Merrickmerr...@gmail.com wrote: I was hoping there would be a way to add a field the sequence table postgresql automatically generates so I could rely on whatever mechanism postgresql uses to avoid the problems described thus far. Hm, well you could create a sequence for every customer. I don't think that's the way I would go but in theory it should work. Having thousands or millions of sequences could make dealing with things like pg_dump kind of a challenge though. I should have included more info, it's highly likely that multiple users will be accessing using same customer_id when creating orders thus deadlocks would be an issue I would like to avoid. Well deadlocks are only a problem if you're not careful how you acquire the locks. If you keep the transaction short and this is the only reason you lock the customer record then you won't get a deadlock. Just make sure you acquire the lock before you acquire any weaker lock such as from referential integrity checks from inserting a record which refers to that customer. If it's part of a long transaction which acquires locks on multiple customers then you have to be concerned with what order the locks are acquired. On Wed, Jul 1, 2009 at 6:01 PM, Scott Marlowescott.marl...@gmail.com wrote: The simplest method is to do something like: begin; select * from sometable where cust_id=99 order by order_id desc for update; to lock all the customer records for cust_id 99, then take the first record, which should have the highest order_id, grab that increment it and then insert the new record and commit; the transaction. Assuming your customers aren't ordering dozens of things a second, this should work with minimal locking contention. I don't like the idea of locking all the order records. That sounds like it would lead to even more contention than locking just the customer record. There could be thousands of order records to lock all over the order table. Also, this seems like it would have deadlocks risks no matter how you wrote the code. Since you can't control the order that the locking would take place. If you happened to get a customer with a significant number of orders you'll get a bitmap heap scan or even a sequential scan and that will potentially lock the records in a different order than your order by. If someone else previously locked them when the stats reflected fewer records they would have taken the locks in a different order. It seems to me that locking all the order records here is really just a proxy for locking their parent customer record and that would work better anyways. You could avoid the update to the customer record by combining these two strategies though. Instead of updating a last_order_num field in customer do something like this: begin; select * from customer where customer_id=:0 for update; select max(order_num) from orders where customer_id = :0 insert into orders (order_num,...) values (:0 + 1, ...) commit; This uses the lock on customer to protect your insert against someone else inserting the same order_num but doesn't actually update the customer table. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] 1 Sequence per Row i.e. each customer's first order starts at 1
On Thu, Jul 2, 2009 at 3:28 AM, Greg Starkgsst...@mit.edu wrote: On Wed, Jul 1, 2009 at 6:01 PM, Scott Marlowescott.marl...@gmail.com wrote: The simplest method is to do something like: begin; select * from sometable where cust_id=99 order by order_id desc for update; to lock all the customer records for cust_id 99, then take the first record, which should have the highest order_id, grab that increment it and then insert the new record and commit; the transaction. Assuming your customers aren't ordering dozens of things a second, this should work with minimal locking contention. I don't like the idea of locking all the order records. That sounds like it would lead to even more contention than locking just the customer record. There could be thousands of order records to lock all over the order table. True. Then again, if you're only locking it long enough to get the next sequence, creating an empty record with that sequence, then committing the transaction, it's a short lived lock. Actually, since you're only incrementing from the highest one, you could just lock the id from a select max(orderid) where custid=xyz and you'd only have to lock one row. It seems to me that locking all the order records here is really just a proxy for locking their parent customer record and that would work better anyways. You could avoid the update to the customer record by combining these two strategies though. Instead of updating a last_order_num field in customer do something like this: begin; select * from customer where customer_id=:0 for update; select max(order_num) from orders where customer_id = :0 insert into orders (order_num,...) values (:0 + 1, ...) commit; This uses the lock on customer to protect your insert against someone else inserting the same order_num but doesn't actually update the customer table. Good point. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] An example needed for Serializable conflict...
Hi! Please send me an example (pseudo-code) for Serializable conflict. And I wanna know, if possible, that if more transactions only read the tables in Serializable mode, and one or others write to it, can I got some conflicts in read operation? c = client t = transaction c1t1 begin serial c1t1 select sum(netto) from order_items where order_code = 1 c2t2 begin c2t2 insert into order_items c3t3 begin serial c3t3 select sum(netto) from order_items where order_code = 2 c2t2 commit c4t4 begin serial c4t4 select sum(netto) from order_items where order_code = 1 c1t1 select count(order_items) c4t4 rollback c1t1 rollback c3t3 rollback I wanna understand, which limitations I need to see in my code to avoid conflicts on important queries where my calculated items must be equal (like count(*) = len(fetched(*)). Sorry for the rtfm like question, but is rather DSFE like (Do and See the F*ed Experience). So your experience needed. Thanks for your help: dd
[GENERAL] Delete triggers order in delete cascade (pg 8.3.7).
Hi all. I've come across a problem with delete cascade. I have three tables A, B and C. Table B has a foreign key on A with delete cascade. Table C has a foreign key on B with delete cascade. So, we have this reference chain: C-B-A All three tables have an 'on delete' trigger. My problem is, when I delete a row from A, the delete triggers are fired in the order A then B then C, which is the opposite of what I expected (the row from B should be deleted before the A one, or the reference constraint would break). This happens with 'after' and 'before' triggers. I really need the order to be C then B then A. Is there a reason for the triggers to fire in this order ? Has anyone an idea to reverse it ? Thanks in advance. Michaël Lemaire -- 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] 1 Sequence per Row i.e. each customer's first order starts at 1
On Thu, Jul 2, 2009 at 10:35 AM, Scott Marlowescott.marl...@gmail.com wrote: Actually, since you're only incrementing from the highest one, you could just lock the id from a select max(orderid) where custid=xyz and you'd only have to lock one row. Not really because you would have a race condition between selecting the max() and then locking that record (you can't do FOR UPDATE directly on the aggregate and in any case if you could it would have the same problem). Actually any scheme involving locking the orders would have the same problems. Two transactions can start and try to lock some or all the records. One will wait behind the other and only one transaction will go ahead at a time but when the waiting transaction proceeds it still won't see the newly inserted record and will get the same maximum. You could get it to work as long as you're prepared to retry if you get that race condition. But then if you're prepared to retry you don't need locks at all. Just select max(id) from customer where customer_id = :0 and try to insert -- if you get a unique violation start over and try again. As long as you have an index on customer_id,order_id -- which would presumably be your primary key anyways -- that should actually perform just fine. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Delete triggers order in delete cascade (pg 8.3.7).
Michaël Lemaire wrote: Hi all. I've come across a problem with delete cascade. I have three tables A, B and C. Table B has a foreign key on A with delete cascade. Table C has a foreign key on B with delete cascade. So, we have this reference chain: C-B-A All three tables have an 'on delete' trigger. My problem is, when I delete a row from A, the delete triggers are fired in the order A then B then C, which is the opposite of what I expected (the row from B should be deleted before the A one, or the reference constraint would break). The on delete cascade are (sort of) implemented with system triggers. So deleting a row from A triggers a delete on B where fkey=X and so on. This happens with 'after' and 'before' triggers. I really need the order to be C then B then A. Why? What are you trying to do? -- 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
Re: [GENERAL] Does anyone know of a job scheduler that uses PostgreSQL?
On Wed, 2009-07-01 at 16:16 -0500, Anthony Caduto wrote: I am looking for something similar to: http://www.arcanadev.com/adtempus/ but without the MS SQL server dependency. Thanks, Tony http://www.opensymphony.com/quartz/ might fit the bill -- 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] Delete triggers order in delete cascade (pg 8.3.7).
Richard Huxton d...@archonet.com wrote: Michaël Lemaire wrote: Hi all. I've come across a problem with delete cascade. I have three tables A, B and C. Table B has a foreign key on A with delete cascade. Table C has a foreign key on B with delete cascade. So, we have this reference chain: C-B-A All three tables have an 'on delete' trigger. My problem is, when I delete a row from A, the delete triggers are fired in the order A then B then C, which is the opposite of what I expected (the row from B should be deleted before the A one, or the reference constraint would break). The on delete cascade are (sort of) implemented with system triggers. So deleting a row from A triggers a delete on B where fkey=X and so on. This happens with 'after' and 'before' triggers. I really need the order to be C then B then A. Why? What are you trying to do? The delete triggers add 'command' rows in another table to notify another server of data changes (kind of a replication system but with data convertion). This other server's database doesn't have delete cascades (I can't change this for compatibility with other scripts). So delete commands must be issued in an order that don't break foreign keys. -- 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] Delete triggers order in delete cascade (pg 8.3.7).
Michaël Lemaire wrote: Richard Huxton d...@archonet.com wrote: Michaël Lemaire wrote: Hi all. I've come across a problem with delete cascade. I have three tables A, B and C. Table B has a foreign key on A with delete cascade. Table C has a foreign key on B with delete cascade. So, we have this reference chain: C-B-A All three tables have an 'on delete' trigger. My problem is, when I delete a row from A, the delete triggers are fired in the order A then B then C, which is the opposite of what I expected (the row from B should be deleted before the A one, or the reference constraint would break). The on delete cascade are (sort of) implemented with system triggers. So deleting a row from A triggers a delete on B where fkey=X and so on. This happens with 'after' and 'before' triggers. I really need the order to be C then B then A. Why? What are you trying to do? The delete triggers add 'command' rows in another table to notify another server of data changes (kind of a replication system but with data convertion). This other server's database doesn't have delete cascades (I can't change this for compatibility with other scripts). So delete commands must be issued in an order that don't break foreign keys. You could replace the on delete cascade with your own triggers. They should fire in the order you want. You will want a BEFORE DELETE trigger, but you will want it to fire after any other before triggers so will need to start its name with z or some such. -- 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
Fwd: [GENERAL] High consumns memory
2009/7/1 Albe Laurenz laurenz.a...@wien.gv.at Anderson Valadares wrote: [this is on Windows, DB is accessed with ODBC driver 8.4.3] Thanks for the answer ... But honestly I think that was a misunderstood. The memory increase issue is showed in the DATA column. Look how day by day it increases exponencially. In a few days PostGres goes out of memory, close the connections and enter in a recovery mode. I really don’t know what is causing it. Date 29/06/2009 PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND 9943 postgres 15 0 860m 41m 819m 811m 9604 3540 D 88.3 20.4 0:08.33 postgres: dbtest test 10.255.100.73(4796) SELECT Date 29/06/2009 PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND 9943 postgres 15 0 994m 33m 960m 818m 143m 3540 S 29.5 23.9 48:19.96 postgres: dbtest test 10.255.100.73(4796) idle Date 29/06/2009 PID USER PR NI VIRT RES SHR CODE DATA S %CPU %MEM TIME+ COMMAND 9943 postgres 16 0 1366m 1.3g 818m 3540 515m S 31.2 33.2 192:20.61 postgres: dbtest test 10.255.100.73(4796) SELECT Date 30/06/2009 PID USER PR NI VIRT SWAP RES SHR DATA CODE S %CPU %MEM TIME+ COMMAND 9943 postgres 17 0 1724m 30m 1.7g 821m 873m 3540 R 27.2 42.2 325:54.83 postgres: citgis citgis 10.255.100.73(4796) SELECT Now that is weird. How can the same backend process suddenly be connected to database citgis as user citgis? Do you have an explanation? What is your work_mem setting? This influences the amount of private memory a backend will allocate. Can you say more that executes a PL/pgSQL function in a loop about the workload? Are there long transactions? Which version of PostgreSQL is this? Yours, Laurenz Albe Answering ... 1. PostGre SQL 8.3.6 2. Work_mem – 5MB 3. About the connection citgis citgis ... it was my mistake. Is the same database and the same user, I was renaming to dbtest teste only for security ... 4. About the “function loop”. I have a windows service that execute a select (limit 200) each 500ms and after that it calls a PostGre procedure (developed for us). for each row returned in my select. 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
Fwd: [GENERAL] High consumns memory
2009/7/1 Merlin Moncure mmonc...@gmail.com: On Mon, Jun 29, 2009 at 8:14 PM, Anderson Valadaresanderva...@gmail.com wrote: Hi all I have a software developed in Delphi as a Windows Service, but, i don't know why, it consumns an unexpected large system memory (515m). The service access PostgresSQL by ODBC driver (psqlodbc_08_03_0400) and it consist simply of a loop calling a procedure PL/PGSQL. How to discover what is causing or why this high memory usage ? What objects are being used on this session ? you are definitely leaking. resident memory size (RES) of 1gb+ is not a normal situation. I bet that you have a transaction that is not being completed. First thing to check is: select * from pg_stat_activity; and see if your backend (by pid) is in IDLE, running a query, or 'IDLE in transaction.' Also, try not to top-post(paste your response _below_ mine), and send plain text email where possible. merlin Well, Thank for your answer. I double check what you ask me ... About the leaking memory, i think as you that i’m having it ... but i can find where is it or in which part of the procedure is responsable. The pg_start_activity does not return any uncompleted transation. I work with subtransations inside the main transaction. Any ideia ? -- 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] Delete triggers order in delete cascade (pg 8.3.7).
Richard Huxton d...@archonet.com wrote: Michaël Lemaire wrote: Richard Huxton d...@archonet.com wrote: Michaël Lemaire wrote: Hi all. I've come across a problem with delete cascade. I have three tables A, B and C. Table B has a foreign key on A with delete cascade. Table C has a foreign key on B with delete cascade. So, we have this reference chain: C-B-A All three tables have an 'on delete' trigger. My problem is, when I delete a row from A, the delete triggers are fired in the order A then B then C, which is the opposite of what I expected (the row from B should be deleted before the A one, or the reference constraint would break). The on delete cascade are (sort of) implemented with system triggers. So deleting a row from A triggers a delete on B where fkey=X and so on. This happens with 'after' and 'before' triggers. I really need the order to be C then B then A. Why? What are you trying to do? The delete triggers add 'command' rows in another table to notify another server of data changes (kind of a replication system but with data convertion). This other server's database doesn't have delete cascades (I can't change this for compatibility with other scripts). So delete commands must be issued in an order that don't break foreign keys. You could replace the on delete cascade with your own triggers. They should fire in the order you want. You will want a BEFORE DELETE trigger, but you will want it to fire after any other before triggers so will need to start its name with z or some such. Yes, I think I'll write my own cascade then. I was just hoping a prettier solution existed. Thank you for your answers. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] No password prompt logging into Postgres 8.4
Using th new postgresql 8.4.0 (compiled) On Ubuntu 8.10 I did an initdb, added a password to the postgres user and then changed the pg_hba.conf to: local all all md5 host all all 127.0.0.1/32 md5 Restarted Postgresql. If I log in normally: postg...@ben-desktop:~$ psql Password: psql (8.4.0) Type help for help. postgres=# But if i do : b...@ben-desktop:~$ psql -Upostgres psql (8.4.0) Type help for help. postgres=# You can also do : b...@ben-desktop:~$ psql -hlocalhost -Upostgres psql (8.4.0) Type help for help. postgres=# Note no password prompt either time! Looks like a bug or am I not understanding this properly. Ben -- 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] Replication
On Mon, 2009-06-22 at 17:53 -0400, Gerry Reno wrote: I noticed that the user survey on the community page does not list replication among the choices for development priority. For me, replication is the most important thing that is critically missing from postgresql. We need something as good as MySQL Replication. Both statement-based and row-based replication. And support for Master-Master and full cyclic replication setups. Postgresql is just a toy database without this as far as I am concerned. Just wanted to add thanks for putting this so succinctly. It's important we listen to people that explain why PostgreSQL isn't good enough yet. We'll do what we can in PostgreSQL 8.5. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrading 8.3 to 8.4 on Windows.
Good morning. I am itching to upgrade my 8.3 development database to 8.4 before I move to production. Pg_migrator is listed as beta so I'd like to avoid that. Has anyone made the leap yet? Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294
Re: [GENERAL] No password prompt logging into Postgres 8.4
In response to Ben Trewern ben.trew...@talktalk.net: Using th new postgresql 8.4.0 (compiled) On Ubuntu 8.10 I did an initdb, added a password to the postgres user and then changed the pg_hba.conf to: local all all md5 host all all 127.0.0.1/32 md5 Restarted Postgresql. If I log in normally: postg...@ben-desktop:~$ psql Password: psql (8.4.0) Type help for help. postgres=# But if i do : b...@ben-desktop:~$ psql -Upostgres psql (8.4.0) Type help for help. postgres=# You can also do : b...@ben-desktop:~$ psql -hlocalhost -Upostgres psql (8.4.0) Type help for help. postgres=# Note no password prompt either time! Does user ben have a .pgpass file? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] Upgrading 8.3 to 8.4 on Windows.
Hartman, Matthew wrote: Good morning. I am itching to upgrade my 8.3 development database to 8.4 before I move to production. Pg_migrator is listed as beta so I'd like to avoid that. Has anyone made the leap yet? Just dump/restore with the 8.4 pg_dump/restore if you're still in development. -- 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
Re: [GENERAL] Database schema dumper
On Monday 29 June 2009, fe...@crowfix.com said something like: I'd like to dump a database schema to a file, probably XML but anything reasonable is good enough. By schema, I don't mean the narrow postgres keyword, but rather the table names, columns, foreignkeys, triggers, constraints, etc. I'd really like something that could work for other databases too, including O-, M, etc. But that might be asking too much. A quick google for variations on dump database schema didn't find much. Whether it be a CPAN module, Java class, etc, or a standalone program, none of that matters much. You mentioned CPAN, so I assume you're comfortable with Perl. It's Pg specific, but it's a GREAT utility: http://www.rbt.ca/autodoc/ Does output in HTML, Dot, Dia,, and XML. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE -- 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] Replication and coding good practices
On Mon, 2009-06-29 at 07:34 -0700, David Fetter wrote: On Mon, Jun 29, 2009 at 07:11:43PM +0800, Craig Ringer wrote: On Sun, 2009-06-28 at 09:01 -0700, David Fetter wrote: Are there any rules of thumb to consider for making an application easier to work with a general replication solution? The applications I mostly deal with are e-commerce sites. It really depends on what replication solution you choose, along with the environment you're deploying into. ... and why you need replication. Reliability/Availability? Data storage redundancy? Performance? And if performance, read-mostly performance or write-heavy performance? It's this kind of discussion that you might want to hire experts to help with :) Commandprompt, Endpoint, OmniTI and the outfit I work for, PostgreSQL Experts http://www.pgexperts.com would be examples. Probably should mention 2ndQuadrant also, since we have an Italian office (for the original poster) and we have been developing replication for PostgreSQL for some time now. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgAdmin - no_spool
On Tue, 2009-06-30 at 09:27 +0100, Pedro Doria Meunier wrote: Could someone please tell me why when I click on a cluster pgAdmin exhibits a dialog stating: Column not found in pgSet: no_spool ? - - pgAdmin version 1.10.0 Beta2 rev. 7749 - - Master on a remote server - - Slony-I not installed on localhost You need to ask this on the pgAdmin list. http://www.pgadmin.org/support/list.php -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] compiling postgres for 64 bit windows using mingw64
Hello, I am wondering if anyone has successfully compiled postgres for 64 bit windows using mingw64? I tried doing so but the configure step fails with the following message: conftest.c:50: error: conflicting types for 'accept' The config.log file shows the following additional message: c:/mingw_64/lib/gcc../../x86_64-w64-mingw32/include/winsock2.h:1291: note: previous declaration of 'accept' was here The definition of 'accept' in winsock2.h is: WINSOCK_API_LINKAGE SOCKET WSAPI accept(SOCKET s, struct sockaddr *addr, int *addrlen);, where SOCKET is defined as int *. The definition of SOCKET back in the 32 bit version of mingw was unsigned int, which appears to be what the postgres configure tool expects. Thanks in advance for any help, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] compiling postgres for 64 bit windows using mingw64
McWilliams, Steven steven_mcwilli...@bmc.com writes: I am wondering if anyone has successfully compiled postgres for 64 bit windows using mingw64? We don't currently support 64-bit builds on Windows. There are a pile of issues there, most coming from Microsoft's nonstandard decision to make sizeof(long) != sizeof(pointer). In practice you probably wouldn't get any noticeable performance improvement over a 32-bit build anyway, seeing that large shared_buffers settings don't seem to be useful on Windows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors
I move a compressed pg_dump archives across the wire to a remote host on a regular schedule. The process completes and the archives are restored on the remote site and the resulting database performs as expected. However, I get this returned to me at the end of each dump/transfer/restore pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema public because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema public already exists Command was: CREATE SCHEMA public; WARNING: errors ignored on restore: 2 The pg_dump command is: pg_dump --create --format=c --user=postgres --verbose hll_redmine | gzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz rsync -avz --bwlimit=35 --delete-after --exclude=database.yml --exclude=*.log --exclude=*cache --exclude=*ruby_sess* /var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1 /dev/null The pg_restore command, which generates the error, is: gunzip /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz | pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb --user=postgres --full --analyze hll_redmine 1 /dev/null I speculate that I have set some options on the restore that conflict with those set on the dump, perhaps --create. Regrettably, I lack the expertise to determine if this is the cause or not; and, as this is a production environment, I lack the ability to play with them to determine what is incorrect. If anything is obviously wrong, or even mildly suspicious, I would appreciate a nudge in the right direction. This issue at least has no overlong dates. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simulate multiple primary keys
I have the following table:
gms= \d jobclock
Table public.jobclock
Column| Type |
Modifiers
-++
jobclock_id | integer| not null default
nextval('jobclock_jobclock_id_seq'::regclass)
employee_id | integer| not null
machine_id | character varying(4) | not null
workorder | character varying(8) | not null
operation | integer| not null
bartype | character varying(10) | not null
clockin | timestamp(0) without time zone | not null
clockout| timestamp(0) without time zone | default NULL::timestamp
without time zone
comments| character varying(255) | default NULL::character
varying
Indexes:
jobclock_pkey PRIMARY KEY, btree (jobclock_id)
...
I need to keep jobclock_id unique and not null, but I also need to
ensure that no row is duplicated. Is my best bet to drop the current
primary key and make a primary key out of the columns that I want to
ensure remain unique from row to row?
Thanks.
--
Brandon
--
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] simulate multiple primary keys
Just create a unique constraint on all of the columns.
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
(613) 549- x4294
-Original Message-
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
ow...@postgresql.org] On Behalf Of Brandon Metcalf
Sent: Thursday, July 02, 2009 1:28 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] simulate multiple primary keys
I have the following table:
gms= \d jobclock
Table public.jobclock
Column| Type |
Modifiers
-++-
---
jobclock_id | integer| not null default
nextval('jobclock_jobclock_id_seq'::regclass)
employee_id | integer| not null
machine_id | character varying(4) | not null
workorder | character varying(8) | not null
operation | integer| not null
bartype | character varying(10) | not null
clockin | timestamp(0) without time zone | not null
clockout| timestamp(0) without time zone | default
NULL::timestamp
without time zone
comments| character varying(255) | default
NULL::character
varying
Indexes:
jobclock_pkey PRIMARY KEY, btree (jobclock_id)
...
I need to keep jobclock_id unique and not null, but I also need to
ensure that no row is duplicated. Is my best bet to drop the current
primary key and make a primary key out of the columns that I want to
ensure remain unique from row to row?
Thanks.
--
Brandon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
.now.
--
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] pgAdmin - no_spool
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks Simon, Actually Dave has already made a patch that overcomes this issue and it's already been committed! BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Simon Riggs wrote: On Tue, 2009-06-30 at 09:27 +0100, Pedro Doria Meunier wrote: Could someone please tell me why when I click on a cluster pgAdmin exhibits a dialog stating: Column not found in pgSet: no_spool ? - - pgAdmin version 1.10.0 Beta2 rev. 7749 - - Master on a remote server - - Slony-I not installed on localhost You need to ask this on the pgAdmin list. http://www.pgadmin.org/support/list.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKTO6X2FH5GXCfxAsRAnKNAKCAo3XAieGNTXPoRpCh27Bnetm1GQCdE18M d0EjQ5jjuFBS0FX1kuWl3/g= =vNuh -END PGP SIGNATURE- -- 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] simulate multiple primary keys
M == matthew.hart...@krcc.on.ca writes: M Just create a unique constraint on all of the columns. Ah. Didn't realize you could specify more than one column as part of a unique constraint. Thanks. -- Brandon -- 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] simulate multiple primary keys
In response to Brandon Metcalf bran...@geronimoalloys.com:
I have the following table:
gms= \d jobclock
Table public.jobclock
Column| Type |
Modifiers
-++
jobclock_id | integer| not null default
nextval('jobclock_jobclock_id_seq'::regclass)
employee_id | integer| not null
machine_id | character varying(4) | not null
workorder | character varying(8) | not null
operation | integer| not null
bartype | character varying(10) | not null
clockin | timestamp(0) without time zone | not null
clockout| timestamp(0) without time zone | default NULL::timestamp
without time zone
comments| character varying(255) | default NULL::character
varying
Indexes:
jobclock_pkey PRIMARY KEY, btree (jobclock_id)
...
I need to keep jobclock_id unique and not null, but I also need to
ensure that no row is duplicated. Is my best bet to drop the current
primary key and make a primary key out of the columns that I want to
ensure remain unique from row to row?
Your primary key can span multiple columns, i.e.
PRIMARY KEY(jobclock_id, employee_id, machine_id)
Could be more columns.
Keep in mind that this ensures that the combination of all those
columns is unique, which may or may not be what you want.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
--
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] simulate multiple primary keysx
w == wmo...@potentialtech.com writes: ... w Your primary key can span multiple columns, i.e. w PRIMARY KEY(jobclock_id, employee_id, machine_id) w Could be more columns. w Keep in mind that this ensures that the combination of all those w columns is unique, which may or may not be what you want. Sure. I realize that a primary key can be made up of multiple columns, but a unique constraint across the columns I need as suggested by Matthew is what I was looking for. Thanks. -- Brandon -- 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] pgAdmin - no_spool
On Thu, 2009-07-02 at 18:30 +0100, Pedro Doria Meunier wrote: Actually Dave has already made a patch that overcomes this issue and it's already been committed! I replied to make sure that everybody knew where to post. If you post and then solve the problem its best to post again to say its solved and how it was solved. Otherwise google will not be our friend. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Q: data modeling with inheritance
This is a question about data modeling with inheritance and a way to circumvent the limitation that primary keys are not inherited. I'm considering a project to model genomic variants and their associated phenotypes. (Phenotype is a description of the observable trait, such as disease or hair color.) There are many types of variation, many types of phenotypes, and many types of association. By type, I mean that they have distinct structure (column names and inter-row dependencies). The abstract relations might look like this: variant associationphenotype --- ---- variant_id - variant_id+--- phenotype_id genome_idphenotype_id -+short_descr strand origin_id (i.e., who) long_descr start_coord ts (timestamp) stop_coord There are several types of variants, such as insertions, deletions, inversions, copy-number variants, single nucleotide polymorphisms, translocations, and unknowable future genomic shenanigans. Phenotypes might come from ontologies or controlled vocabularies that need a graph structure, others domains might be free text. Each is probably best-served by a subclass table. Associations might be quantitative or qualitative, and would come from multiple origins. The problem that arises is the combinatorial nature of the schema design coupled with the lack of inherited primary keys. In the current state of PG, one must (I think) make joining tables (association subclasses) for every combination of referenced foreign keys (variant and phenotype subclasses). So, how would you model this data? Do I ditch inheritance? Thanks, Reece -- 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] simulate multiple primary keys
--- On Thu, 7/2/09, Brandon Metcalf bran...@geronimoalloys.com wrote:
From: Brandon Metcalf bran...@geronimoalloys.com
Subject: [GENERAL] simulate multiple primary keys
To: pgsql-general@postgresql.org
Date: Thursday, July 2, 2009, 5:27 PM
I have the following table:
gms= \d jobclock
Table
public.jobclock
Column |
Type
|
Modifiers
-++
jobclock_id | integer
| not null default
nextval('jobclock_jobclock_id_seq'::regclass)
employee_id | integer
| not null
machine_id | character
varying(4) |
not null
workorder | character
varying(8) |
not null
operation |
integer
| not null
bartype |
character varying(10) |
not null
clockin |
timestamp(0) without time zone | not null
clockout | timestamp(0)
without time zone | default NULL::timestamp without time
zone
comments | character
varying(255) | default
NULL::character varying
Indexes:
jobclock_pkey PRIMARY KEY, btree
(jobclock_id)
...
I need to keep jobclock_id unique and not null, but I also
need to
ensure that no row is duplicated. Is my best bet to
drop the current
primary key and make a primary key out of the columns that
I want to
ensure remain unique from row to row?
Thanks.
--
Brandon
--
It depends of a lot of variables, take two stage:
Stage 1
one employee_id can use some machine_id create you can create a unique key to
employee_id
Stage 2
one employee_id can use one machine_id create you can create a unique key to
employee_id,machine_id
is a example, but i think you have to normalize the table
--
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] Q: data modeling with inheritance
variant association phenotype --- --- - variant_id - variant_id +--- phenotype_id genome_id phenotype_id -+ short_descr strand origin_id (i.e., who) long_descr start_coord ts (timestamp) stop_coord Is an association, for example, an experiment that establishes a dependent relationship? So could there be multiple associations between variant and phenotype? The problem that arises is the combinatorial nature of the schema design coupled with the lack of inherited primary keys. In the current state of PG, one must (I think) make joining tables (association subclasses) for every combination of referenced foreign keys (variant and phenotype subclasses). Is your concern that the number of joins will grow exponentially in the number of variants and phenotypes? So, how would you model this data? Do I ditch inheritance? I've put some thought into representing an ontology via table inheritance, and I've never been able to figure out a good way ( of course, that's not to say one doesn't exist... ) . If I understand your problem correctly, I would use composite keys ( ie ( variant type, id ) ) and then use an extra join to separate ontology tables to restrict searches to specific branches. So all variants would be stored in the variants table, all phenotypes are in the phenotypes table, and you join through association. It's not as elegant as inheritance, but it will be much more flexible in both the types of queries that you can write and in case your requirements change. -Nathan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing plpython on 8.4
I'm having trouble installing plpython in 8.4. I tried under Windows (one click installer from EDB) and under Ubuntu (linux binary). In both cases I was told: could not load library 8.4/lib/postgresql/plpython.(so|dll) Both systems have python 2.5 installed. And plpython was working in 8.3 (and I believe 8.4 B1) on both. Any ideas what I'm doing wrong? Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
