[GENERAL] Problems with JDBC site and Postgres mailing list subscription page
http://jdbc.postgresql.org/ seems to be down. Also I can't connect to the mailing list subscription page at: http://webmail.postgresql.org/mj/mj_wwwusr?domain=postgresql.orgfunc=lists-long-fullextra=pgsql-jdbc Can anyone point me to another site where I can download the postgres jdbc drivers? Thanks, Jc Sorry about the disclaimer. . Note: This e-mail contains privileged and confidential information and is for the sole use of the intended recipient(s). If you are not an intended recipient, you are hereby kindly requested to refrain from printing, copying, or distributing the information contained herein. Furthermore, any other use of the information contained herein is strictly prohibited. If you have received this transmission in error, please kindly notify the sender immediately and destroy all copies of the original message. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Postgres vs Sybase ASE
Does anyone know of any recent comparisons of postgres vs, Sybase ASE 12.5? I've looked around but can't find anything useful. Thanks, -- Jean-Christian Imbeault ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] install; readline error with 7.3.4
On my RH 9.0 linux system when ./configuring 7.3.4 I get an error about not finding the readline libs, however both the readline and readline-devel rpms are installed: # rpm -qa | grep readline readline-4.3-5 readline-devel-4.3-5 What library/rpm am I missing? The config.log file has this to say: configure:5786: result: no configure:5800: checking for readline configure:5837: gcc -o conftest -O2 conftest.c -lreadline -lcrypt -lresolv -lnsl -ldl -lm -lbsd 5 /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetnum' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgoto' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetflag' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `BC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tputs' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `PC' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetent' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `UP' /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/../../../libreadline.so: undefined reference to `tgetstr' collect2: ld returned 1 exit status Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Foreign key constraint accepted even when not same data type
Is it right for postgres to accept a foreign key constraint when the type of the field is not the same as that of the foreign key? For example: # Create table a (id int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a' CREATE TABLE # Create table b (id2 text references a(id)); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE # \d a Table public.a Column | Type | Modifiers +-+--- id | integer | not null Indexes: a_pkey primary key btree (id) # \d b Table public.b Column | Type | Modifiers +--+--- id2| text | Foreign Key constraints: $1 FOREIGN KEY (id2) REFERENCES a(id) ON UPDATE NO ACTION ON DELETE NO ACTION Jean-Christian Imbeault ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] adding on delete cascade constraint?
Currently I have a table defined as such: TAL=# \d internal_keywords Table public.internal_keywords Column | Type | Modifiers -+-+--- keyword | text| not null pid | integer | not null Indexes: internal_keywords_pkey primary key btree (keyword, pid) Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON UPDATE NO ACTION ON DELETE NO ACTION How can I change the ON DELETE action to CASCADE for column pid? I've check the alter table documentation but cannot find any reference to this. Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] adding on delete cascade constraint?
Oliver Elphick wrote: Foreign Key constraints: $1 FOREIGN KEY (pid) REFERENCES products(id) ON UPDATE NO ACTION ON DELETE NO ACTION Drop the constraint; then add an amended one. Ok, how do I drop the constraint, it has no name. The documentation is rather poor on how to get the name of unamed constraints: To remove a constraint you need to know its name. If you gave it a name then that's easy. Otherwise the system assigned a generated name, which you need to find out. The psql command \d tablename can be helpful here Secondly what is the correct syntax for adding a new constraint with ON DELETE CASCADE? Thanks, Jean-Christian Imbeault ---(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] Port?
b b wrote: What port does the postgresql server listen on. It listens on whichever port you tell it to :) 5432 by default I think. How do we know the port postgresql is listening on? Make sure that tcp connections are enabled in the conf file. TCP connection are *not* enabled by default. Jean-Christian Imbeault ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] select null + 0 question
Why is it that select null + 1 gives null but select sum(a) from table where there are null entries returns an integer? Shouldn't the sum() and + operators behave the same? TAL=# select null + 0; ?column? -- (1 row) TAL=# select * from a; a --- 1 (3 rows) TAL=# select sum(a) from a; sum - 1 (1 row) Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Duplicate key insert question
Alvaro Herrera wrote: No, only the "second" one will fail (though it's difficult which one is the second) From: http://marc.theaimsgroup.com/?l=postgresql-generalm=105656988915991w=2 Ian Barwick wrote: [...] I proposed that same solution 3 years ago. Tom shoots it down: [...] I couldn't get the link to work so I couldn't read why Tom shot it down. But if Tom shot down this idea down ... then it mustn't be correct. If I followed all the arguments correctly according to the thread there is *no* way to do what I (and you ;) want in one simple query. No, there's not. You say no, but at first you say that the proposed method works. The proposed method, if it is correct, is simple enough for me. By simple I mean all can be done with one query. You should check the returned value from the insertion function to see if it succeeded or not. No, what I want if to have one query that will *always* insert if there is no record with this primary key and *always* do nothing (not fail, not generate an error) if there is already a record with this primary key. I don't want to check return values :) Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate key insert question
Maksim Likharev wrote: Finding if the duplicate value exists and inserting if not. Ok, thanks but I think it is still vulnerable to a race condition. I do not know how that will work for PG, but in Microsoft SQL Server you can do following BEGIN TRANSACTION UPDATE [val] = [val] WHERE INSERT ... COMMIT TRANSACTION so basically by updating specific row ( let say you have such row ) in transaction, row/page lock will be held until end of transaction and concurrent UPDATE will wait until you are done. Kind of semaphore. Why the UPDATE? And in postgres every query runs in it's own transaction so no need for the explicit BEGIN / END block. So can't see how your solution is any better than the previous one :) Thanks, Jean-Christian Imbeault ---(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] Duplicate key insert question
Alvaro Herrera wrote: The thread is here: http://groups.google.com/groups?hl=enlr=ie=UTF-8oe=UTF-8threadm=3A4D6116.1A613402%40mascari.comrnum=1prev=/groups%3Fq%3DMike%2BMascari%2BINSERT%2BNOT%2BEXISTS%26ie%3D Thanks! The solution is not correct in that there _is_ a race condition. I thought so :( No, what I want if to have one query that will *always* insert if there is no record with this primary key and *always* do nothing (not fail, not generate an error) if there is already a record with this primary key. I don't want to check return values :) No way. I was beginning to think so. Thanks for confirming my suspicions. In your opinion what is the best solution, if we define best as not generating any error messages and executing as quickly as possible? Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate key insert question
Reuben D. Budiardja wrote: No, onlu *one* of them will fail, but yes, the other will then generate error. So it really is a trade off. Another way would be to lock the table, as other has suggested. But then there is disadvantages to that also. Really? I just got a post form Alvaro Herrera saying; "The solution is not correct in that there _is_ a race condition." Maybe I misunderstood, but "not correct" doesn't sound good :) Jean-Christian Imbeault ---(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] Duplicate key insert question
Alvaro Herrera wrote: Well, he is right. One will fail, the other will not. The race condition is for the application. If you want to ignore it, you can do that, but there _will_ be an ERROR thrown and the transaction will be aborted. Ah ... then maybe this solution is 'good enough'. It will still generate an error message some of the time (when there is a race condition) but will definitely generate fewer error messages than the current method used which is just to do the insert and let it fail if there is already a record with the same primary key. Thanks for the help! Jean-Christian Imbeault ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Duplicate key insert question
Reuben D. Budiardja wrote: INSERT INTO mytable SELECT 'value1', 'value2' WHERE NOT EXISTS (SELECT NULL FROM mytable WHERE mycondition) Thank you to everyone who helped out on my question. I am trying to implement the above solution but I'm having problems getting this to work when I want to insert more than one value: TAL=# create table b (a text primary key, b text); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' for table 'b' CREATE TABLE TAL=# insert into b select 'a'; INSERT 335311 1 TAL=# insert into b select 'b', select 'b'; ERROR: parser: parse error at or near "select" at character 27 Did I get the syntax wrong? Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Performance question
I'm trying to convince another open-source project (phpOpenTracker) to modify their current INSERT sql queries. Currently they just do an INSERT into a table without first checking if their might be a record with the same primary key. The reason for this that they need fast inserts and most user I assume are using MySQL which silently drops INSERT queries that violate primary key constraints. But postgres on the other hand (and rightly so) issues and logs an error. I have suggested that their current INSERT INTO t VALUES() be changed to: INSERT INTO T SELECT 'v1', 'v2' WHERE NOT EXISTS ( SELECT NULL FROM t WHERE pk='v1' ) However one of the developers is worried that this would cause a performance drop for MySQL users b/c of the extra SELECT in my version of the INSERT query. I had thought that the 'extra' SELECT isn't really extra at all since *any* DB still has to check that there is a record with the primary key that we are trying to insert. So whereas in my query the SELECT is explicitly stated in the regular version of a simple INSERT, the select is still there but implicit since there was a primary key defined on the table. So there really shouldn't be much, if any of a performance drop. Is there any truth to my logic in the previous paragraph? :) I'd like to test my theory on a real MySQL database but I don't have access to one right now, and I am not sure how to go about testing a MySQL db or even what kind of testing. If I can get a spare machine I will give it a go though. Thanks, Jean-Christian Imbeault ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Selecting dupes from table
Uros wrote: I want to list all entries with duplicate urls. I tried this: select id,url,title from directory where url IN (select url from directory group by url having count(url) 1) ORDER by url; Try: select id,url,title from directory where group by id, url, title having count(url) 1 order by url; Think it should work, Jan-Christian Imbeault ---(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
