[GENERAL] updating data but the constraint is set immediate
Hi, I've realised that my tables' constraint was set to IMMEDIATE. So, when I update one table, there's error because of referential integrity. Reading the docs, I read that SET CONSTRAINTS has no effect on IMMEDIATE (and I've confirmed that by trying it out). Is there anyway I can circumvent this? I remembered I can delete using pgAdminII. However, I don't have a windows box now, and therefore I can't use pgAdminII. Can anyone help? __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(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] numeric formats in SELECT
Hi all; I've got a table with real type data. And, in the SELECT, some of the columns are displayed in scientific format. I don't like this, and I would prefer that the number were shown with two decimal digits. Is this possible? Best regards Javier ---(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] Create Data Base fails
OK, the picture is the latest CVS of phpPgAdmin on PostgreSQl 7.3.1. I created a user with createDB option. Here OK. I connect to phpPgAdmin as that user and try to create a DB and I get this error: Error de SQL: ERROR: CREATE DATABASE: source database template1 is being accessed by other users En la declaracio'n: CREATE DATABASE phpbb WITH ENCODING='LATIN1' I'm crossposting because I think it may be more of a PostgreSQL situation then a phpPgAdmin one. -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 2 different versions of postgres on the same system
On Tue, 1 Jul 2003, Madhavi Daroor wrote: Hi all, I have installed postgres 7.3 in my Red hat linux 7.2 system. I want to also install postgres 7.2.3 in the same system. Is it possible?? Can somebody please tell me how I can do this...ASAP!!! Just what the other message said, with one caveat. If you're prone to dumbass attacks (I know I am some days) then install each under a different account that doesn't have access to the other's directory. This has two advantages. 1: If you're rm -Rf ing the data directory you don't have to worry about doing it to the wrong one as much, since you're command prompt will have the account name showing (name the accounts something like pgsql72 and pgsql73) and 2: You can assign ownership of the two databases to two different people, and not worry about person1 destroying person2's database. They need to run on different ports too, of course. ---(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] 2 different versions of postgres on the same system
On Tue, 1 Jul 2003, Madhavi Daroor wrote: Hi all, I have installed postgres 7.3 in my Red hat linux 7.2 system. I want to also install postgres 7.2.3 in the same system. Is it possible?? Can somebody please tell me how I can do this...ASAP!!! Oh, and you need to ./configure --prefix=/usr/local/pgsql72 for one, and ./configure --prefix=/usr/local/pgsql73 for the other kinda thing too. ---(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] numeric formats in SELECT
On Tue, 1 Jul 2003, javier garcia - CEBAS wrote: Hi all; I've got a table with real type data. And, in the SELECT, some of the columns are displayed in scientific format. I don't like this, and I would prefer that the number were shown with two decimal digits. Is this possible? Does something like this work?: select realcolumn::numeric(10,2) from table; ---(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] C++ functions under Windows
Hello ! I have to develop c++ function for PostgreSQL 7.3.1 under Windows 2000, what compilers and linkers may be used for this purposes ? I have Visual C++ 6.0 and C++Builder 5.0 . May I use them ? Best regards, Sincerely yours, Yuriy Rusinov. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Cannot create unique index
Unlikely. Create index takes a write lock on a table. Reindex takes an exclusive lock (or something like that). Unless something really bad happens it shouldn't be a problem. I've had index corruption before. We're running 7.0 (upgrading Real Soon Now) and there are certain situations that can corrupt indexes, mostly involving backend segmentation faults. More recent versions should be quite resiliant against this. On Tue, Jul 01, 2003 at 06:50:36AM -0600, scott.marlowe wrote: how odd. Since reindex works by dropping the index then recreating it, is it possible that some process inserted duplicates in the split second there was no index? for safety's sake, I've always reindexed in a transaction: begin; drop index bubba; create index bubba on ... commit; -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ the West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do. - Samuel P. Huntington pgp0.pgp Description: PGP signature
Re: [GENERAL] Create Data Base fails
Are you using advanced authentication in phppgadmin? Could this be caused when phppgadmin is configured to have the database superuser connect to template1 to manage user logins? Andrew Gould --- Martin Marques [EMAIL PROTECTED] wrote: OK, the picture is the latest CVS of phpPgAdmin on PostgreSQl 7.3.1. I created a user with createDB option. Here OK. I connect to phpPgAdmin as that user and try to create a DB and I get this error: Error de SQL: ERROR: CREATE DATABASE: source database template1 is being accessed by other users En la declaracio'n: CREATE DATABASE phpbb WITH ENCODING='LATIN1' I'm crossposting because I think it may be more of a PostgreSQL situation then a phpPgAdmin one. -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués | [EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Cannot create unique index
Hello, before the reindexing starts, the webserver is always shut down. as there are no database-users except the web-users, there should not be a possibility of inserting a non-unique value just between DROP INDEX and CREATE INDEX allthough, sometimes an apache child process may take a few seconds to exit... I will put a delay between webserver-stop and reindexing, maybe this helps... the other possibility is segfaults or other hardware problems. We have often had this kind of problem with earlier postgres versions before (this has been discussed on the list several times). but since 7.3.3 we have never had it again. On none of our servers. -- Mit freundlichem Gruß Henrik Steffen Geschäftsführer top concepts Internetmarketing GmbH Am Steinkamp 7 - D-21684 Stade - Germany http://www.topconcepts.com Tel. +49 4141 991230 mail: [EMAIL PROTECTED] Fax. +49 4141 991233 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc) Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de System-Partner gesucht: http://www.franchise.city-map.de Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563 - Original Message - From: Martijn van Oosterhout [EMAIL PROTECTED] To: scott.marlowe [EMAIL PROTECTED] Cc: Henrik Steffen [EMAIL PROTECTED]; Andrew Gould [EMAIL PROTECTED]; pgsql [EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 3:04 PM Subject: Re: [GENERAL] Cannot create unique index ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PlPython
Kevin Jacobs [EMAIL PROTECTED] writes: On Mon, 30 Jun 2003, Tom Lane wrote: I've also commented out the paragraph about global data and function arguments pending resolution of the question. Given the reference to restricted execution objects in the original text, I would think that the removal of rexec usage means that plpython functions can now get at any global data. But I'm not a Python user and might be misunderstanding the point. The scope of each function is still private, so global data are not shared unless explicitly stored in the GD dictionary. This is still the case with my updated code. Okay, but surely the reference to restricted execution objects is now wrong? Can you give me corrected text for the para in question? Each function gets its own restricted execution object in the Python interpreter, so that global data and function arguments from functionmyfunc/function are not available to functionmyfunc2/function. The exception is the data in the varnameGD/varname dictionary, as mentioned above. regards, tom lane ---(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] PlPython
Kevin Jacobs [EMAIL PROTECTED] writes: Only a slight modification is needed: Each function gets its own execution enviornment in the Python interpreter, ... Got it, thanks. (For some reason this message seems to have been delayed.) regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Accent insensitive search
On Tuesday 01 July 2003 17:11, Alejandro Javier Pomeraniec wrote: Hi ! Does anyone knows how to make accent insensitive searches?? For example i have this data in a table Colón Polo I need that this query SELECT * FROM testtable WHERE testfield like '%olo%'; brings both results instead of only showing Polo. One solution[*]: SELECT * FROM testtable WHERE to_ascii(testfield,'LATIN1') LIKE '%olo%' Note this might not work with all database encodings, especially UNICODE. [*] no doubt someone will be along in a moment with another. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Accent insensitive search
On Tue, Jul 01, 2003 at 03:11:54PM +, Alejandro Javier Pomeraniec wrote: Hi ! Does anyone knows how to make accent insensitive searches?? Convert both the pattern and the column to ASCII with to_ascii SELECT * FROM testtable WHERE testfield like '%olo%'; SELECT * FROM testtable WHERE to_ascii(testfield) like '%olo%'; Do you know the pgsql-ayuda mailing list? You may like it. http://tlali.iztacala.unam.mx/mailman/listinfo/pgsql-ayuda -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura (Perelandra, CSLewis) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] updating data but the constraint is set immediate
On Tue, 1 Jul 2003, Rudy Koento wrote: I've realised that my tables' constraint was set to IMMEDIATE. So, when I update one table, there's error because of referential integrity. Reading the docs, I read that SET CONSTRAINTS has no effect on IMMEDIATE (and I've confirmed that by trying it out). Actually SET CONSTRAINTS has no effect on NOT DEFERRABLE constraints (which is the default for initially immediate iirc but not required). Is there anyway I can circumvent this? I remembered I The best way is probably to drop and re-add the constraint with the attributes you want (possibly deferrable initially immediate if you want to be normally immediate but with the possibility of set constraints) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] updating data but the constraint is set immediate
On Tue, Jul 01, 2003 at 02:35:48 -0700, Rudy Koento [EMAIL PROTECTED] wrote: Hi, I've realised that my tables' constraint was set to IMMEDIATE. So, when I update one table, there's error because of referential integrity. Reading the docs, I read that SET CONSTRAINTS has no effect on IMMEDIATE (and I've confirmed that by trying it out). Is there anyway I can circumvent this? I remembered I can delete using pgAdminII. However, I don't have a windows box now, and therefore I can't use pgAdminII. You can use alter table to drop the constraint and then to add the corrected constraint. I am not sure if this was available prior to 7.3. ---(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] Create Data Base fails
On Mar 01 Jul 2003 10:11, Andrew Gould wrote: Are you using advanced authentication in phppgadmin? Could this be caused when phppgadmin is configured to have the database superuser connect to template1 to manage user logins? phpPgAdmin has nothing to do, now that I made some tests. 1) I conected to template1 using user postgres using psql 2) With user bbuser (which can create databases) I try to create a database from inside psql: phpbb= create database pruebatp; ERROR: CREATE DATABASE: source database template1 is being accessed by other users phpbb= So it seems that when template1 is been accessed, create database can't be used (there seems to be a good reason for that, no?). Now, could this be a problem with phpPgAdmin conecting by default to template1? -- Porqué usar una base de datos relacional cualquiera, si podés usar PostgreSQL? - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telematica Universidad Nacional del Litoral - ---(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] postgresql.org is unreliable
Hi Does anyone else find the site postgresql.org kinda unreliable? Many times it's stalled for a while. And it's not just today, but very often. It's really frustrating especially when I regularly use it to look up documentation. Is there any mirror to the site ? Thanks. RDB -- Reuben D. Budiardja Department of Physics and Astronomy The University of Tennessee, Knoxville, TN - /\ ASCII Ribbon Campaign against HTML \ / email and proprietary format X attachments. / \ - Have you been used by Microsoft today? Choose your life. Choose freedom. Choose LINUX. - ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql.org is unreliable
On Tue, Jul 01, 2003 at 06:12:44PM -0400, Reuben D. Budiardja wrote: Does anyone else find the site postgresql.org kinda unreliable? Many times it's stalled for a while. And it's not just today, but very often. It's really frustrating especially when I regularly use it to look up documentation. Yeah, I think it's not what one would expect. Sometimes I also hate that the plain documentation needs to use a PHP script for displaying -- it'd probably be faster with direct access. Because of this I just downloaded the documentation tarball from ftp.postgresql.org... of course it's also much faster having it on a local machine. Is there any mirror to the site ? The problem is the site is database backed. Someone suggested using a replicating setup some days ago and Marc Fournier thought it was a good idea, so hopefully it's on someone's TODO list. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El realista sabe lo que quiere; el idealista quiere lo que sabe (Anonimo) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] IPv6 data type
It will be in 7.4. --- Richard Welty wrote: an IPv6 data type is on the todo list, but it looks like it's been there a while. is there any work being done on this, and is there an ETA? i have a project that could use it, but if it wasn't coming soon (say in 7.4), i can work around it. it'd just be nice and all that. richard -- Richard Welty [EMAIL PROTECTED] Averill Park Networking 518-573-7592 Unix, Linux, IP Network Engineering, Security ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] PlPython
elein kirjutas T, 24.06.2003 kell 00:42: There is a realtively clean hack one can use to convert plpython functions to plpythonu manually - just rename the language for the time of loading functions - do as superuser update pg_language set lanname = 'plpython' where lanname = 'plpythonu'; LOAD YOUR Pl/Python FUNCTIONS ; update pg_language set lanname = 'plpythonu' where lanname = 'plpython'; PS: I've built and tested the plpython patch against 7.3.2 and am happy it does not affect the features I count on. As it should. The untrusted language gives you *more* power, not less. The untrusted status means that the user has to be trusted to use that much power. Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Duplicate key insert question
Alvaro Herrera wrote: (B (B No, only the "second" one will fail (though it's difficult which one is (B the second) (B (BFrom: (B (Bhttp://marc.theaimsgroup.com/?l=postgresql-generalm=105656988915991w=2 (B (BIan Barwick wrote: (B (B[...] (B (BI proposed that same solution 3 years ago. Tom shoots it down: (B (B[...] (B (BI couldn't get the link to work so I couldn't read why Tom shot it down. (BBut if Tom shot down this idea down ... then it mustn't be correct. (B (BIf I followed all the arguments correctly according to the thread there (Bis *no* way to do what I (and you ;) want in one simple query. (B (B (B No, there's not. (B (BYou say no, but at first you say that the proposed method works. The (Bproposed method, if it is correct, is simple enough for me. By simple I (Bmean all can be done with one query. (B (B You should check the returned value from the insertion (B function to see if it succeeded or not. (B (BNo, what I want if to have one query that will *always* insert if there (Bis no record with this primary key and *always* do nothing (not fail, (Bnot generate an error) if there is already a record with this primary (Bkey. I don't want to check return values :) (B (BThanks, (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate key insert question
Finding if the duplicate value exists and inserting if not. (B (BAs for the race condition ( your other post ) (BI do not know how that will work for PG, but in Microsoft SQL Server (Byou can do following (BBEGIN TRANSACTION (BUPDATE [val] = [val] (BWHERE (BINSERT ... (BCOMMIT TRANSACTION (B (BPretty general approach tho, should work on any SQL system with (Btransaction and locking support. (B (Bso basically by updating specific row ( let say you have such row ) (Bin transaction, row/page lock will be held until end of transaction (Band concurrent UPDATE will wait until you are done. (BKind of semaphore. (B (BPractical example table that holds unique rows, let say documents, (Byou can have extra row with let say [id] = -1 or whatever you like, (Bso during insert into that table you can update that row in a (Btransaction, (Bsearch/insert unique values, commit transaction. (B (B-Original Message- (BFrom: Jean-Christian Imbeault [mailto:[EMAIL PROTECTED] (BSent: Tuesday, July 01, 2003 5:47 PM (BTo: Maksim Likharev (BCc: [EMAIL PROTECTED] (BSubject: Re: [GENERAL] Duplicate key insert question (B (B (BMaksim Likharev wrote: (B (B Do search using IF EXISTS SELECT ... or LEFT OUTER JOIN ... WHERE ... (BIS (B NULL. (B works pretty fast. (B (BSorry, I don't understand. Works pretty fast for what? (B (BIs that a way of finding if a value exists? or a way of doing the (Binsertion? (B (BThanks, (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
Re: [GENERAL] Duplicate key insert question
Maksim Likharev wrote: (B (B Finding if the duplicate value exists and inserting if not. (B (BOk, thanks but I think it is still vulnerable to a race condition. (B (B I do not know how that will work for PG, but in Microsoft SQL Server (B you can do following (B BEGIN TRANSACTION (B UPDATE [val] = [val] (B WHERE (B INSERT ... (B COMMIT TRANSACTION (B (B so basically by updating specific row ( let say you have such row ) (B in transaction, row/page lock will be held until end of transaction (B and concurrent UPDATE will wait until you are done. (B Kind of semaphore. (B (BWhy the UPDATE? And in postgres every query runs in it's own transaction (Bso no need for the explicit BEGIN / END block. (B (BSo can't see how your solution is any better than the previous one :) (B (BThanks, (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PSQL NEWBIE - RUNTIME ERROR
If you link to a library on your system that is not in your library path, you will get this problem. The solutions is basically one of: - Create a symlink to the lib in a directory that is in the path (like /usr/local/lib) - Add the directory to your /etc/ld.so.conf - Add it to your LD_LIBRARY_PATH environment variable Rerun ldconfig in any case. Hope this helps. On Fri, Jun 27, 2003 at 05:07:53PM +0200, FTL Africa wrote: Hello, I am a fairly new user of postgresql on linux. I have followed the instruction quite well, I'm sure. I have been working on the first program example in C and am getting an error when I run the first program test testlibpq.c (I haven't tried the other tests). This is the error msg I am getting: ./testlibpq:error in loading shared libraries libpq.so.2: cannot open shared object file: No such file or directory. These are the version I am working with: postgreSQL : 7.2.1 linux : 2.0.32 gcc : 2.7.2.3 I compiled using: cc -c -I/usr/local/pgsql/include testlibpq.c linked with: cc -0 testlibpq testlibpq.0 -L/usr/local/psql/lib -lpq ran with: ./testlibpq Any help will be highly appreciated. I am stuck at this point right now. Thanks In Advance, Litso -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ the West won the world not by the superiority of its ideas or values or religion but rather by its superiority in applying organized violence. Westerners often forget this fact, non-Westerners never do. - Samuel P. Huntington pgp0.pgp Description: PGP signature
Re: [GENERAL] Duplicate key insert question
On Wed, Jul 02, 2003 at 09:58:28AM +0900, Jean-Christian Imbeault wrote: Alvaro Herrera wrote: No, only the second one will fail (though it's difficult which one is the second) 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. 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 The solution is not correct in that there _is_ a race condition. 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 :) No way. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No es bueno caminar con un hombre muerto ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Duplicate key insert question
On Tuesday 01 July 2003 08:45 pm, Jean-Christian Imbeault wrote: (B Reuben D. Budiardja wrote: (B Hi, not sure if this is answering your question, but I just asked similar (B questions here. I asked about using INSERT WHERE NOT EXISTS (which you (B can do in PostgreSQL). Here is what you can do: (B (B INSERT INTO mytable (B SELECT 'value1', 'value2' (B WHERE NOT EXISTS (B (SELECT NULL FROM mytable (B WHERE mycondition) (B (B http://marc.theaimsgroup.com/?l=postgresql-generalw=2r=1s=WHERE+NOT+EX (B ISTSq=b (B (B Thanks for the link! (B (B I read the thread and it looks like even the above solution is not (B perfect because of a possible race condition where two inserts trying to (B insert a row with a pk not in the table will both get think it is ok to (B do so, try it and then both will fail? (B (BNo, onlu *one* of them will fail, but yes, the other will then generate error. (BSo it really is a trade off. Another way would be to lock the table, as other (Bhas suggested. But then there is disadvantages to that also. (B (BRDB (B (B (B-- (BReuben D. Budiardja (BDepartment of Physics and Astronomy (BThe University of Tennessee, Knoxville, TN (B- (B/"\ ASCII Ribbon Campaign against HTML (B\ / email and proprietary format (B X attachments. (B/ \ (B- (BHave you been used by Microsoft today? (BChoose your life. Choose freedom. (BChoose LINUX. (B- (B (B (B---(end of broadcast)--- (BTIP 9: the planner will ignore your desire to choose an index scan if your (B joining column's datatypes do not match
Re: [GENERAL] Duplicate key insert question
Alvaro Herrera wrote: (B (B The thread is here: (B 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 (B (BThanks! (B (B The solution is not correct in that there _is_ a race condition. (B (BI thought so :( (B (BNo, what I want if to have one query that will *always* insert if there (Bis no record with this primary key and *always* do nothing (not fail, (Bnot generate an error) if there is already a record with this primary (Bkey. I don't want to check return values :) (B (B (B No way. (B (BI was beginning to think so. Thanks for confirming my suspicions. (B (BIn your opinion what is the best solution, if we define best as not (Bgenerating any error messages and executing as quickly as possible? (B (BThanks, (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate key insert question
-Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 5:51 PM To: Jean-Christian Imbeault Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [GENERAL] Duplicate key insert question On Wed, Jul 02, 2003 at 09:45:23AM +0900, Jean-Christian Imbeault wrote: Reuben D. Budiardja wrote: [snip] 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 should check the returned value from the insertion function to see if it succeeded or not. Sadly, an error will cause the whole transaction to abort, but if they come from the MySQL side it will hardly matter. But you should try to use a sequence if at all possible to avoid all these problems. Does not really avoid the named issue. Suppose that you have a dictionary of working part numbers (e.g. Boeing might have 3 million distinct parts in their database). They would like to create a domain for these parts. So, naturally, they take their list and do %cat list.dat|sort|uniqlist.sor And then bulk load list.sor. Unfortunately, the operation fails, because one part was duplicated: PartIDPartDescription - 94v-975b High speed saphire needle bearing 94V-975B High speed saphire needle bearing It would have been nice if after loading 1.7 million of the 3 million parts, it could simply skip over the obvious error instead of rolling everything back. Of course, it is also possible that 94v-975b and 94V-975B are distinct parts. So the one who designs the database must make that decision in allowing an IGNORE option. I think it would be a useful addition to PostgreSQL, but I have an easy work around for what I want to do by simply capitalizing the strings I am inserting into a dictionary or domain and use select distinct to filter. The rare times I want to do something like that incrementally, I can just request a table lock. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate key insert question
Reuben D. Budiardja wrote: (B (B No, onlu *one* of them will fail, but yes, the other will then generate error. (B So it really is a trade off. Another way would be to lock the table, as other (B has suggested. But then there is disadvantages to that also. (B (BReally? I just got a post form Alvaro Herrera saying; (B (B"The solution is not correct in that there _is_ a race condition." (B (BMaybe I misunderstood, but "not correct" doesn't sound good :) (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PSQL NEWBIE - RUNTIME ERROR
Assuming the standard install to /usr/local/pgsql you need to add /usr/local/pgsql/lib to the linker's search path and (if Linux) run ldconfig ./testlibpq:error in loading shared libraries libpq.so.2: cannot open shared object file: No such file or directory. = Chris Albertson Home: 310-376-1029 [EMAIL PROTECTED] Cell: 310-990-7550 Office: 310-336-5189 [EMAIL PROTECTED] KG6OMK __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(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] Duplicate key insert question
On Tue, Jul 01, 2003 at 06:26:08PM -0700, Dann Corbit wrote: But you should try to use a sequence if at all possible to avoid all these problems. Does not really avoid the named issue. Suppose that you have a dictionary of working part numbers (e.g. Boeing might have 3 million distinct parts in their database). They would like to create a domain for these parts. So, naturally, they take their list and do %cat list.dat|sort|uniqlist.sor And then bulk load list.sor. Oh, sure. The sequence thing won't apply everywhere. But maybe it can be applied in his scenario, which I don't know. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Voy a acabar con todos los humanos / con los humanos yo acabaré voy a acabar con todos / con todos los humanos acabaré (Bender) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Duplicate key insert question
Alvaro Herrera wrote: (B (B Well, he is right. One will fail, the other will not. The race (B condition is for the application. If you want to ignore it, you can do (B that, but there _will_ be an ERROR thrown and the transaction will be (B aborted. (B (BAh ... then maybe this solution is 'good enough'. It will still generate (B an error message some of the time (when there is a race condition) but (Bwill definitely generate fewer error messages than the current method (Bused which is just to do the insert and let it fail if there is already (Ba record with the same primary key. (B (BThanks for the help! (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Duplicate key insert question
-Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 01, 2003 6:37 PM To: Jean-Christian Imbeault Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [GENERAL] Duplicate key insert question On Wed, Jul 02, 2003 at 10:25:54AM +0900, Jean-Christian Imbeault wrote: 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 :) 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. The other transaction _will_ insert the tuple, though, and it won't be aborted. Note that for the race condition to show there has to be a race, i.e. two backends trying to insert the same primary key at the same time. If one finishes half a second before the other, they will behave that way you want, i.e. there will one tuple inserted and no error generated. I assume that PostgreSQL would simply time out both transactions if it happened in a deadly-embrace pair? I searched the PG docs, but could not find a clear answer. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Duplicate key insert question
On Tue, Jul 01, 2003 at 06:48:29PM -0700, Dann Corbit wrote: I assume that PostgreSQL would simply time out both transactions if it happened in a deadly-embrace pair? I searched the PG docs, but could not find a clear answer. No, the deadlock will the detected and one of the transactions will be aborted. This should happen within a second or so (configurable, AFAIR). The other transaction will continue normally. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El que vive para el futuro es un iluso, y el que vive para el pasado, un imbécil (Luis Adler, Los tripulantes de la noche) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: language c is not trusted
Chris Albertson wrote: Thanks for the hint. This fixed it: alberts=# UPDATE pg_language SET lanpltrusted = true WHERE lanname = 'c'; UPDATE 1 alberts=# grant USAGE ON LANGUAGE c TO alberts; GRANT Ummm, I doubt that's really what you wanted, was it? Quoting the fine manual, with emphasis added: USAGE For procedural languages, allows the use of the specified language for the creation of functions in that language. This is the only type of ^ privilege that is applicable to procedural languages. EXECUTE Allows the use of the specified function and the use of any ^ operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions. (This syntax works for aggregate functions, as well.) Did you just want to allow non-superusers to execute C language functions, or create their own? The latter is a huge, gaping security hole, which is why the language is marked untrusted. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Lotus Domino and PostgreSql in Linux
I'm working on pgSQL integration with Domino6 (DECS, LCLSX) (there will be documentation in the coming weeks). I have a question a couple of questions for you: 1) Are you using Domino 6 or 5.x 2) In your DSN setup on (NT?) do you have the valid account information to connect to Pg? 3) If you are running agent are you sure you have the security structure that will permit that? For instance, in Domino 6 DECS (unlike DCR's) require admin priviledge to set up (which probably in not the same as the designer privs). In addition, there is an option you set for the database to allow external connection. Once that is done you have to make sure any agents running against this database have the appropriate privs as well Hope this help a bit. I'm on the same journey too :) Quoting Kallol Nandi [EMAIL PROTECTED]: I am running an agent in the domino server that connects to a database in Postgresql through odbc dsn.Both are installed in the same Linux box. I am getting an error Error Creating product object at the line Set con = New ODBCConnection Here is the code : Option Public Uselsx *LSXODBC Sub Initialize Dim con As ODBCConnection Dim qry As ODBCQuery Dim result As ODBCResultSet Dim id As Integer Dim nam As String,job As String Am getting Error here Set con = New ODBCConnection Set qry = New ODBCQuery Set result = New ODBCResultSet Set qry.Connection = con Set result.Query = qry status = con.ConnectTo(debug) qry.SQL = select * from testtable result.Execute Do result.NextRow id = result.GetValue(a, id) nam = result.GetValue(b, nam) Loop Until result.IsEndOfData result.Close(DB_CLOSE) con.Disconnect End Sub I guess it is an error related to Domino. But not sure. may be related to the ODBC driver also. Is there any way to solve it? Regards, Kallol. This email account is being host by: VCSN, Inc : http://vcsn.com ---(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] Duplicate key insert question
On Tuesday 01 July 2003 09:25 pm, Jean-Christian Imbeault wrote: (B Reuben D. Budiardja wrote: (B No, onlu *one* of them will fail, but yes, the other will then generate (B error. So it really is a trade off. Another way would be to lock the (B table, as other has suggested. But then there is disadvantages to that (B also. (B (B Really? I just got a post form Alvaro Herrera saying; (B (B "The solution is not correct in that there _is_ a race condition." (B (B Maybe I misunderstood, but "not correct" doesn't sound good :) (B (BIf you want to avoid the race condition as well, then use the locking (Bmechanism for transaction. Combine it with the previous INSERT ... SELECT ... (BWHERE NOT EXISTS, it should give you what you want. I suspect it's slower (Bthough. Eg: (B (BBEGIN WORK; (B (BINSERT INTO mytable (BSELECT 'value1', 'value2' (B WHERE NOT EXISTS (B(SELECT NULL FROM mytable (BWHERE mycondition) (B (BCOMMIT WORK; (B (BThis should solve the Race Condition, since other transaction have to wait. (BBut if the PK already exists, this will quit without error. (B (BRDB (B (B---(end of broadcast)--- (BTIP 2: you can get off all lists at once with the unregister command (B(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] postgresql.org is unreliable
On Tuesday 01 July 2003 08:47 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: On Tuesday 01 July 2003 06:54 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: On Tuesday 01 July 2003 06:27 pm, The Hermit Hacker wrote: On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: Hi Does anyone else find the site postgresql.org kinda unreliable? snip What OS are you running on? I take it, from using links, that its Unix based? I am on Redhat Linux 7.3. I just upgrade to the last stable mozilla, and it seems fine, at least for now with initial try. Still don't know what's wrong. Thanks. RDB ---(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] updating data but the constraint is set immediate
--- Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, Jul 01, 2003 at 02:35:48 -0700, Rudy Koento [EMAIL PROTECTED] wrote: Hi, I've realised that my tables' constraint was set to IMMEDIATE. So, when I update one table, there's error because of referential integrity. Reading the docs, I read that SET CONSTRAINTS has no effect on IMMEDIATE (and I've confirmed that by trying it out). Is there anyway I can circumvent this? I remembered I can delete using pgAdminII. However, I don't have a windows box now, and therefore I can't use pgAdminII. You can use alter table to drop the constraint and then to add the corrected constraint. I am not sure if this was available prior to 7.3. I solved it by using pgAdminII remotely through ssh port forwarding. I'm curious though, as to why does pgAdminII is able to defer the constraints whereas using command line cannot... __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(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.org is unreliable
On Tue, 1 Jul 2003, Reuben D. Budiardja wrote: I just upgrade to the last stable mozilla, and it seems fine, at least for now with initial try. Still don't know what's wrong. I'd be curious as to whether or not you are getting any packet loss between your machine and www.postgresql.org, specifically around the time where you are finding it stalling ... ---(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
Reuben D. Budiardja wrote: (B (B INSERT INTO mytable (B SELECT 'value1', 'value2' (BWHERE NOT EXISTS (B (SELECT NULL FROM mytable (B WHERE mycondition) (B (BThank you to everyone who helped out on my question. I am trying to (Bimplement the above solution but I'm having problems getting this to (Bwork when I want to insert more than one value: (B (BTAL=# create table b (a text primary key, b text); (BNOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' (Bfor table 'b' (BCREATE TABLE (BTAL=# insert into b select 'a'; (BINSERT 335311 1 (BTAL=# insert into b select 'b', select 'b'; (BERROR: parser: parse error at or near "select" at character 27 (B (B (BDid I get the syntax wrong? (B (BThanks, (B (BJean-Christian Imbeault (B (B (B---(end of broadcast)--- (BTIP 5: Have you checked our extensive FAQ? (B (B http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Duplicate key insert question
On Tuesday 01 July 2003 11:08 pm, Jean-Christian Imbeault wrote: (B Reuben D. Budiardja wrote: (B INSERT INTO mytable (B SELECT 'value1', 'value2' (B WHERE NOT EXISTS (B (SELECT NULL FROM mytable (B WHERE mycondition) (B (B Thank you to everyone who helped out on my question. I am trying to (B implement the above solution but I'm having problems getting this to (B work when I want to insert more than one value: (B (B TAL=# create table b (a text primary key, b text); (B NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'b_pkey' (B for table 'b' (B CREATE TABLE (B TAL=# insert into b select 'a'; (B INSERT 335311 1 (B TAL=# insert into b select 'b', select 'b'; (B ERROR: parser: parse error at or near "select" at character 27 (B (B (BI don't see what you're trying to do. Why do you have two select ? (B (BRDB (B (B (B---(end of broadcast)--- (BTIP 7: don't forget to increase your free space map settings
[GENERAL] test
test ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Duplicate key insert question
On Wed, Jul 02, 2003 at 12:08:56 +0900, Jean-Christian Imbeault [EMAIL PROTECTED] wrote: TAL=# insert into b select 'b', select 'b'; ERROR: parser: parse error at or near select at character 27 You probably want: insert into b select 'b', 'b'; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Duplicate key insert question
On Wednesday 02 July 2003 02:58, Jean-Christian Imbeault wrote: (B Alvaro Herrera wrote: (B No, only the "second" one will fail (though it's difficult which one is (B the second) (B (B From: (B (B http://marc.theaimsgroup.com/?l=postgresql-generalm=105656988915991w=2 (B (B Ian Barwick wrote: (B (B [...] (B (B I proposed that same solution 3 years ago. Tom shoots it down: (B (B(This quote is not from Mike Mascari, not me) (B (B [...] (B (B I couldn't get the link to work so I couldn't read why Tom shot it down. (B But if Tom shot down this idea down ... then it mustn't be correct. (B (Bsee: (Bhttp://archives.postgresql.org/pgsql-general/2000-12/msg00970.php (B (Bentire thread: (Bhttp://archives.postgresql.org/pgsql-general/2000-12/msg00947.php (B (B (BIan Barwick (B[EMAIL PROTECTED] (B (B (B---(end of broadcast)--- (BTIP 6: Have you searched our list archives? (B (B http://archives.postgresql.org
[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] ERROR: Relation pg_user does not exist
S.Peppe [EMAIL PROTECTED] writes: it returns this: ERROR: Relation pg_user does not exist Uh ... how old is the server you're connecting to, exactly? The pg_user view has been standard for awhile, I can't blame your psql for assuming it exists ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Duplicate key insert question
Jean-Christian Imbeault wrote: 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. The transaction semantics of your application should dictate the use of BEGIN/END. If you aren't using BEGIN/END (as many non-InnoDB MySQL applications fail to do), I doubt you can guarantee logical consistency and performance will be less than what it should be. With WAL, the difference is less noticeable, but it is there. The use of BEGIN/END to guarantee logical consistency will help performance. You kill two birds with one stone. So can't see how your solution is any better than the previous one :) The solution works because: Backend #1: BEGIN; UPDATE foo SET bar = bar WHERE keyid = 1; Backend #2: BEGIN; UPDATE foo SET bar = bar WHERE keyid = 1; -- Now blocks because of #1 Backend #1: INSERT INTO weirdtable SELECT weirdkey, field1, field2 WHERE NOT EXISTS ( SELECT 1 FROM weirdtable w WHERE w.key = weirdkey ); -- Performs the insert (or not) COMMIT; Backend #2: INSERT INTO weirdtable SELECT weirdkey, field1, field2 WHERE NOT EXISTS ( SELECT 1 FROM weirdtable w WHERE w.key = weirdkey ); -- Does nothing, as INSERT was (possibly) caused by Backend #1 COMMIT; The UPDATE acts as a lock with row granularity, preventing the race condition caused by multiple INSERT..WHERE NOT EXISTS. Tuple visibility rules in READ COMMITTED will allow Backend #2 to see the new INSERT performed by Backend #1, since Backend #2 won't continue past the UPDATE until Backend #1 has issued a COMMIT or ABORT. However, the solution prohibits you from using SERIALIZABLE, since the UPDATE of the same lock row will cause a serialization error. If READ COMMITTED is sufficient, the question arises as to how to get row granularity from the lock. One could have a lock table composed of the keys being inserted but how do the keys get into the lock table without a race condition there? If row-lock granularity cannot be achieved, and you absolutely cannot handle a race condition causing a unique key violation, you might as well just use LOCK TABLE. You might also want to investigate the userlocks in /contrib, although I haven't used them so I cannot speak to their usefulness. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
