[SQL] Postgres schema comparison.
Hi all, I've got a master database with many other databases that all have (or is supposed to have) the same exact same schema as the master database (the master database is basically an empty template database containing the schema definition). The problem is that none of the schemas actually match the master schema. e.g. missing columns, columns not in the correct order (attnum), missing indexes and primary keys, and in severe cases, missing sequences and tables. I have the wonderful job of re-synch'ing all the schemas out there not conforming to the master. I've looked everywhere for something that will help doing this. I'm specifically looking for a way to do a sumcheck or something similar on tables and/or schema as a whole to be able to do a table comparison with the master database. It will be a bonus to pick up exactly what is missing, but for now, just identifying differences is what I want to achieve. I'm using postgres 7.3 mostly, but I may want to use this for 7.4 and 8.0 databases as well. Has anybody got some suggestions of what I can do or use to do this. TIA Kind Regards Stefan ---(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: [SQL] Postgres schema comparison.
Hi, Stef, Stef schrieb: > It will be a bonus to pick up exactly what is missing, but for now, just > identifying > differences is what I want to achieve. I'm using postgres 7.3 mostly, but > I may want to use this for 7.4 and 8.0 databases as well. > > Has anybody got some suggestions of what I can do or use to do this. There are (at least) two independently developed pgdiff applications, they can be found at: http://pgdiff.sourceforge.net/ http://gborg.postgresql.org/project/pgdiff/projdisplay.php I did not try the first one, but the latter one worked on some of my datas, but fails on others. I filed a bug report some time ago, but got no answer, so I'm afraid this tool currently is unmaintained: http://gborg.postgresql.org/project/pgdiff/bugs/bugupdate.php?895 But maybe a pg_dump --schema-only on all the databases, and then manually diffing the files may already fulfil your needs. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] simple update from select ??
Hi folks I've got a table of pieces of equipment. One piece of equipment may be owned by another piece of equipment, and may own multiple other pieces. To hold the relationship I have a piece_pieces table holding the owner (pp_id) part id and the owned part ids (pp_part). I'v realised I can store this within the original table so I've added an owner attribute. I need to now populate this now from the other table, but I can't work out how. I've got pieces table p_id-- part id p_owner -- new owner attribute piece_pieces table pp_id -- id of owner pp_part -- id of owned -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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: [SQL] [ADMIN] Postgres schema comparison.
On Mar 7, 2005, at 4:33 AM, Stef wrote: I have the wonderful job of re-synch'ing all the schemas out there not conforming to the master. I've looked everywhere for something that will help doing this. I'm specifically looking for a way to do a sumcheck or something similar on tables and/or schema as a whole to be able to do a table comparison with the master database. Develop a function that builds a string describing the tables/schemas you want to compare. Then have your function return the md5 sum of the string as the result. This will give you a 32 character value you can use to determine if there is a mismatch. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] [SQL] Postgres schema comparison.
Markus Schaber mentioned : => There are (at least) two independently developed pgdiff applications, => they can be found at: => => http://pgdiff.sourceforge.net/ => => http://gborg.postgresql.org/project/pgdiff/projdisplay.php Thanks a lot! => I did not try the first one, but the latter one worked on some of my => datas, but fails on others. I filed a bug report some time ago, but got => no answer, so I'm afraid this tool currently is unmaintained: => http://gborg.postgresql.org/project/pgdiff/bugs/bugupdate.php?895 => => But maybe a pg_dump --schema-only on all the databases, and then => manually diffing the files may already fulfil your needs. I've tested something similar, that seems to work ok for me for now : pg_dump -s -t [TABLE] [DBNAME] | grep -v "^--" | md5sum The problem I have with this, is that I have to run the command per table, and seeing that I have over 500 tables in each database, this takes quite a long time. I'll test some of the above pgdiffs, and see if either can do it better. Kind Regards Stefan ---(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
[SQL] Maintaining production DBs, making one schema look like the other
OK, the usual thing: There exists a DB-schema. It is on one hand already in production usage. On the other hand it is still being developed as functions and non-structural stuff are concerned. I found out that EMS Database Comparer helps to replicate the schema differences in form of SQL-statements. From my view this seems to be what I want. What is the psql-way to address this problem? How does one maintain two schemas with PostgreSQL/ are there other tools? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [SQL] Postgres schema comparison.
Hi, Stef, Stef schrieb: > The problem I have with this, is that I have to run the command per table, > and seeing that I have over 500 tables in each database, this takes quite a > long time. Some weeks ago, I posted here a script that uses psql to create split dumps. Maybe you can reuse some of its logics to create per-table md5sums for all tables in a database automatically. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org dump_split.sh Description: application/shellscript ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [ADMIN] Postgres schema comparison.
Are you just synching the schemas, or do you also need to synch the data? Rick John DeSoi <[EMAIL PROTECTED]> To: Stef <[EMAIL PROTECTED]> Sent by: cc: pgsql-ADMIN@postgresql.org, pgsql-sql@postgresql.org [EMAIL PROTECTED]Subject: Re: [SQL] [ADMIN] Postgres schema comparison. tgresql.org 03/07/2005 07:39 AM On Mar 7, 2005, at 4:33 AM, Stef wrote: > I have the wonderful job of re-synch'ing all the schemas out there not > conforming to the master. I've looked everywhere for something that > will help doing this. I'm specifically looking for a way to do a > sumcheck > or something similar on tables and/or schema as a whole to be able to > do a table comparison with the master database. > Develop a function that builds a string describing the tables/schemas you want to compare. Then have your function return the md5 sum of the string as the result. This will give you a 32 character value you can use to determine if there is a mismatch. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [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: [SQL] [ADMIN] Postgres schema comparison.
[EMAIL PROTECTED] mentioned : => Are you just synching the schemas, or do you also need to synch the data? Schemas now, data later. To do the data part, I'm thinking of using slony, because it seems to be able to do pretty much everything I need from that side. But, unfortunately I can't even start fixing the data before the schemas aren't fixed. Kind Regards Stefan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] [SQL] Postgres schema comparison.
Stef <[EMAIL PROTECTED]> writes: > Markus Schaber mentioned : > => But maybe a pg_dump --schema-only on all the databases, and then > => manually diffing the files may already fulfil your needs. > I've tested something similar, that seems to work ok for me for now : > pg_dump -s -t [TABLE] [DBNAME] | grep -v "^--" | md5sum > The problem I have with this, is that I have to run the command per table, Why? If the problem is varying order of table declarations, try 8.0's pg_dump. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [ADMIN] Postgres schema comparison.
John DeSoi mentioned : => Develop a function that builds a string describing the tables/schemas => you want to compare. Then have your function return the md5 sum of the => string as the result. This will give you a 32 character value you can => use to determine if there is a mismatch. OK, this may be exactly what I need. I've compiled and installed contrib/pgcrypto and I want to use either one of : Result data type | Schema | Name | Argument data types --+++- bytea| public | digest | bytea, text bytea| public | digest | text, text Is it possible to somehow pass the output of : "\d [TABLE NAME]" to this function? If not, what would return me consistent text that will describe the columns, indexes and primary keys of a table? Kind Regards Stefan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] [SQL] Postgres schema comparison.
Markus Schaber mentioned : => Some weeks ago, I posted here a script that uses psql to create split => dumps. Maybe you can reuse some of its logics to create per-table => md5sums for all tables in a database automatically. Thanks, but I've got something very similar to this already. I almost thought you managed to split the output of the single schema dump of "pg_dump --schema-only" onto portions belonging to the various tables. That would be very impressive :) Kind Regards Stefan ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] simple update from select ??
On Monday 07 March 2005 12:28 pm, you wrote: > Hi folks > > I've got a table of pieces of equipment. > One piece of equipment may be owned by another piece of equipment, > and may own multiple other pieces. > > To hold the relationship I have a piece_pieces table holding the > owner (pp_id) part id and the owned part ids (pp_part). > > I'v realised I can store this within the original table so I've added > an owner attribute. I need to now populate this now from the other > table, but I can't work out how. > > I've got pieces table > > p_id -- part id > p_owner -- new owner attribute > > piece_pieces table > > pp_id -- id of owner > pp_part -- id of owned For my own future reference as much as anything, here's the required statement, which is a syntactically corrected version of a suggestion by Sean Davis update pieces set p_owner=piece_pieces.pp_id from piece_pieces where piece_pieces.pp_part=pieces.p_id; Under Sean's suggestion I did this inside a transaction block so I could check that it did what I wanted. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] [SQL] Postgres schema comparison.
Tom Lane mentioned : => > The problem I have with this, is that I have to run the command per table, => => Why? => => If the problem is varying order of table declarations, try 8.0's => pg_dump. Yes, this will solve the global schema check, but I will still need to split it into "per table" dumps , to do "per table" comparisons. Kind Regards Stefan ---(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: [SQL] [ADMIN] Postgres schema comparison.
On Mar 7, 2005, at 10:09 AM, Stef wrote: Is it possible to somehow pass the output of : "\d [TABLE NAME]" to this function? If not, what would return me consistent text that will describe the columns, indexes and primary keys of a table? I'm not sure you can use \d directly, but if you startup psql with the -E option it will show you all the SQL it is using to run the \d command. It should be fairly easy to get the strings you need from the results of running a similar query. The psql source is a good place to look also. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] [SOLVED] Postgres schema comparison.
John DeSoi mentioned :
=> I'm not sure you can use \d directly, but if you startup psql with the
=> -E option it will show you all the SQL it is using to run the \d
=> command. It should be fairly easy to get the strings you need from the
=> results of running a similar query. The psql source is a good place to
=> look also.
Sometimes you just need to see things from a different perspective.
Thanks!
Here's my final solution that runs in less than a minute for +- 543 tables :
for x in $(psql -tc "select relname from pg_class where relkind = 'r' and
relname not like 'pg_%'")
do
echo "$(psql -tc "select encode(digest('$(psql -c '\d '${x}'' mer9188_test
| tr -d \"\'\")', 'md5'), 'hex')" mer9188_test | grep -v "^$"|tr -d " "):${x}"
done > compare_list.lst
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Links between rows in a table
On Sun, Mar 06, 2005 at 20:26:50 +0100, PFC <[EMAIL PROTECTED]> wrote: > >>It would probably be better to always have either both or neither of > >>the symmetric relationships in the table. You could make a set of > >>triggers > >>to enforce this. > > Because your relation is symmetric, you should not name them "user" > and "friend". > The duplication is useless if you add a constraint : see this > > create table friendship ( > user_id_1 integer references ... on delete cascade, > user_id_2 integer references ... on delete cascade, > > CHECK( user_id_1 < user_id_2 ) > ); The trouble with this approach is that for some ways of using this data you will need to worry about the ordering of of the values. The advantage of this method is that the space needed to store the data is half of what is needed to store both pairs for each friendship. > user_id_1 < user_id_2 means : > - a user can't be his own friend > - only one row per friend > - when you want to know if A is friend of B, no need to make two > selects, just select where user_id_1 = min(user_id_A, user_id_B) AND > user_id_2 = max(user_id_A, user_id_B) Note that you can't literally use 'min' and 'max' as above, as those functions don't do that. You could use 'case' to do that. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] [SQL] Postgres schema comparison.
Stef, I use dblink to attach to both databases and query pg_namespace, pg_class, pg_attribute ... to get the diffs. See attached as an example. look for the dblink_connect lines to specify your database. You will need to install contrib/dblink. I used this with 7.4.X series and have NOT tested yet with 8.0.X. You can adjust the output to fit your needs. Jim -- Original Message --- From: Stef <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: pgsql-ADMIN@postgresql.org, pgsql-sql@postgresql.org Sent: Mon, 7 Mar 2005 17:31:55 +0200 Subject: Re: [ADMIN] [SQL] Postgres schema comparison. > Tom Lane mentioned : > => > The problem I have with this, is that I have to run the command per > table, > => > => Why? > => > => If the problem is varying order of table declarations, try 8.0's > => pg_dump. > > Yes, this will solve the global schema check, but I will still need to split > it into "per table" dumps , to do "per table" comparisons. > > Kind Regards > Stefan > > ---(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 Original Message --- db_compare.sql Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Lambda expressions in SQL
Coming from functional programming, I often wish to write something like that: (LAMDA "expesiveFcn"(x y z) as exfcn update "Tbl5" SET "Column" = exfcn ) In this case "expensiveFcn" is VOLATILE... Is there a way? At least: substituting the lambda by a select doesn't work with update as subquery ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Links between rows in a table
The trouble with this approach is that for some ways of using this data you will need to worry about the ordering of of the values. Tradeoffs, always tradeoffs... It depends on the application. Note also that it eliminates duplicates ; moreover without such a condition, any relation A-B could have the rows [(A,B)] or [(B,A)] or [(A,B),(B,A)] which promises to cause headaches if you need to get rid of the duplicates... I used this scheme for an "also purchased products" thingy on a website, it works well. In this case the row must be unique because we have (A,B,count) which is the number of times products A and B have been purchased together, in this case having rows (B,A) and (A,B) separated wouldn't help in sorting by this count, which is in my case very fast thanks to a multicolumn index. Note that you can't literally use 'min' and 'max' as above, as those functions don't do that. You could use 'case' to do that. ... yes, it was just a way of saying it. You can define functions that take integers as arguments (I wish these basic bricks were defined by default)... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] drop view even with dependencies?
Is there a way to drop a VIEW in postgres without the need to drop all the dependencies? VIEW 3 | VIEW 2 | VIEW 1 In my case, VIEW 3 depends on VIEW 2, and VIEW 2 depends on VIEW 1. Is there a way to drop VIEW 3 without dropping VIEW 1 and 2? I tried CREATE OR REPLACE VIEW but replace will only work if they have the same number of fields. Also with tables, can I drop a table even if there are multiple views that depend on it? It's such a pain in the neck to have to drop all the dependencies and recreate them all over again. Any help would greatly appreciated. I am using Postgres 7.3.2 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] drop view even with dependencies?
On Mon, Mar 07, 2005 at 04:38:53PM -0500, Henry Ortega wrote: > Is there a way to drop a VIEW in postgres without > the need to drop all the dependencies? > Also with tables, can I drop a table even if there are multiple views > that depend on it? I'm not aware of a way. It could be argued that doing so would be A Bad Thing: a view has a particular meaning, and by dropping the things the view depends on you'd be subverting that meaning. The DBMS doesn't know what your intentions are, so it doesn't allow you to break the dependencies. > It's such a pain in the neck to have to drop all the dependencies and > recreate them all over again. Do you not keep the definitions in a file? It can be useful to put all the necessary DROP and CREATE statements in a file, so if you need to change anything you simply edit the file and run "psql -f filename.sql". -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] SQL query help?
SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0) Work:- +---+---++-+--+ | ID | JobID | UserID | Finished | Comment | +---+---++-+--+ | 1 | 1| user1 | 0 | ...| | 2 | 1| user1 | 1 | ...| | 3 | 2| user2 | 0 | ...| | 4 | 3| user1 | 0 | ...| | 5 | 2| user2 | 0 | ...| | 6 | 2| user1 | 1 | ...| | 7 | 3| user1 | 0 | ...| +---+---++-+--+ All I want it to do is return the number of unfinished jobs for a specific user. In this example it would return 1 because job number 3 is not finished and user1 was the last person working on it. but I keep getting MySQL error # - Invalid use of group function ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Simple delete takes hours
Another way to speed it up is to use bind variables. It sped my deletes up by a factor of 280/1. -- Lynwood "Thomas Mueller" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi there, > > I have a simple database: > > CREATE TABLE pwd_description ( > id SERIALNOT NULL UNIQUE PRIMARY KEY, > name varchar(50) NOT NULL > ); > > CREATE TABLE pwd_name ( > id SERIALNOT NULL UNIQUE PRIMARY KEY, > description integer NOT NULL REFERENCES pwd_description(id), > name varchar(50) NOT NULL, > added timestamp DEFAULT now() > ); > > CREATE TABLE pwd_name_rev ( > id SERIALNOT NULL UNIQUE PRIMARY KEY, > description integer NOT NULL REFERENCES pwd_description(id), > rev_of integer NOT NULL REFERENCES pwd_name(id) ON DELETE > CASCADE, > name varchar(50) NOT NULL > ); > > The indexes shouldn't matter I think. > > pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT) > when something is inserted to pwd_name. Both tables contain about > 4.500.000 emtries each. > > I stopped 'delete from pwd_name where description=1' after about 8 hours > (!). The query should delete about 500.000 records. > Then I tried 'delete from pwd_name_rev where description=1' - this took 23 > seconds (!). > Then I retried the delete on pwd_name but it's running for 6 hours now. > > I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz > with 512 MB RAM. > > PostgreSQL should do a full table scan I think, get all records with > description=1 and remove them - I don't understand what's happening for > >8 hours. > > > Any help is appreciated. > > > Thomas > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Postgres performance
--- PFC <[EMAIL PROTECTED]> wrote: > You mean, you have no foreign keys in your database ? > In SELECT they are definitely useful (think select > for update, isolation > level serializable...) No, I haven't foreign keys in the older version, in that new I've it... however I manage relations from app code (PHP)... doesn't MYSQL allow to use 'foreign keys' in sure and fast way then? > > - why postgres use a new process for every query ? > > (mySQL, if I'm not wrong, use threads... I think > its > > faster) > > Not for every query, for every CONNECTION. > You are using persistant connections are you. Are you ? I'm using PHP and every user (can be from 1 user to 100 users) must connect to the database... do you know how I can use persistant connection? I think it's impossible... I'm wrong? > > > - why connection time is slower? (compared to > mySQL)? > > This is of no importance as everyone uses > persistent connections anyway. See last answer... > > - why postgres require analyze? (mySQL, if I'm not > > wrong, don't require it) > > Yours answers will be very apreciated! Thx > > So it has a planner which knows what it's doing ;) > instead of just > guessing in the dark. > > And MySQL requires analyze too (read the docs), > optimize table which > looks like vacuum to me, and sometimes repair > table... Ok... they are conceptually implemented in the same mode... Thx a lot! Now my ideas about pg & mySQL are cleared (and corrects ;) ) Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [ADMIN] Postgres schema comparison.
My favorite for this task is WinSql available from http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp. It can compare the structure and content of the two tables. -Original Message- From: John DeSoi [mailto:[EMAIL PROTECTED] Sent: Monday, March 07, 2005 7:40 AM To: Stef Cc: pgsql-ADMIN@postgresql.org; pgsql-sql@postgresql.org Subject: Re: [ADMIN] Postgres schema comparison. On Mar 7, 2005, at 4:33 AM, Stef wrote: > I have the wonderful job of re-synch'ing all the schemas out there not > conforming to the master. I've looked everywhere for something that > will help doing this. I'm specifically looking for a way to do a > sumcheck > or something similar on tables and/or schema as a whole to be able to > do a table comparison with the master database. > Develop a function that builds a string describing the tables/schemas you want to compare. Then have your function return the md5 sum of the string as the result. This will give you a 32 character value you can use to determine if there is a mismatch. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Postgres performance
> > No, I haven't foreign keys in the older version, > in > > that new I've it... however I manage relations > from > > app code (PHP)... > > Really ? > In my experience this is a sure way to get > inconsistencies slowly > creeping into your database, and you also get a load > of funky concurrency > issues. Yes, you are rigth... my insert/update are very simple and without problems and so I think to use 'foreign key' coded to make faster/simpler the management and don't overloading the db (and use exception code management )... but I had a problem with pgSQL because server was very busy and the same query was replicate (because users refresh the page... :( ) so now I've foreign keys... > Now, in PHP, you can use mysql_pconnect instead of > mysql_connect to get a > persistent connection. mod_php keeps a pool of > connections. The same thing > probably applies for postgres, but as I don't use it > with PHP (only with > Python) I can't tell. Look in the docs for > "persistent connections". mmm...This is interesting... I see it's usefull but there are some 'warnings' about use it (see http://it.php.net/manual/en/features.persistent-connections.php) : "Note, however, that this can have some drawbacks if you are using a database with connection limits that are exceeded by persistent child connections. If your database has a limit of 16 simultaneous connections, and in the course of a busy server session, 17 child threads attempt to connect, one will not be able to. If there are bugs in your scripts which do not allow the connections to shut down (such as infinite loops), the database with only 16 connections may be rapidly swamped. Check your database documentation for information on handling abandoned or idle connections." What about it? (i think it's the same with mySQL...) I don't know how users can connect to... 1, 10, 1000... I must create a pool with 1000 connections? is this fine? if connections aren't released I must reset manually, it is dangerous... do you think? Thx! Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Postgres performance
> > Yes, you are rigth... my insert/update are very > simple > > and without problems and so I think to use > 'foreign > > key' coded to make faster/simpler the management > and > > don't overloading the db (and use exception code > > management )... but I had a problem with pgSQL > because > > server was very busy and the same query was > replicate > > (because users refresh the page... :( ) so now > I've > > foreign keys... > > It's a typical case, program being stopped between > insertion of parent > and child row. Although in this case FK's is not the > solution, > transactions are. I insert a tuple (in the table answers) if the key 'iduser->idquestion' don't exists but when the key exists I update the value; this is coded in php because I have thought that otherwise I must use functions... there's no parent/child rows... how can I use transactions here? Mauro ___ Nuovo Yahoo! Messenger: E' molto più divertente: Audibles, Avatar, Webcam, Giochi, Rubrica Scaricalo ora! http://it.messenger.yahoo.it ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL query help?
John McGough wrote: SELECT Count(*) FROM Work WHERE (UserID='user1' AND MAX(Finished)=0) Work:- +---+---++-+--+ | ID | JobID | UserID | Finished | Comment | +---+---++-+--+ | 1 | 1| user1 | 0 | ...| | 2 | 1| user1 | 1 | ...| | 3 | 2| user2 | 0 | ...| | 4 | 3| user1 | 0 | ...| | 5 | 2| user2 | 0 | ...| | 6 | 2| user1 | 1 | ...| | 7 | 3| user1 | 0 | ...| +---+---++-+--+ All I want it to do is return the number of unfinished jobs for a specific user. In this example it would return 1 because job number 3 is not finished and user1 was the last person working on it. but I keep getting MySQL error # - Invalid use of group function John, I may be missing something but how about SELECT count(id) AS unfinished FROM work WHERE userid = 'user1' AND finished = 0 GROUP BY jobid; -- Kind Regards, Keith ---(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: [SQL] SQL query help?
On Mon, Mar 07, 2005 at 04:22:15PM -, John McGough wrote: > +---+---++-+--+ > | ID | JobID | UserID | Finished | Comment | > +---+---++-+--+ This table output doesn't look like PostgreSQL's usual format. > but I keep getting MySQL error # - Invalid use of group function And this error definitely doesn't look like something from PostgreSQL. Are you sure you're asking the right list? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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
