Re: [GENERAL] regarding threads and transactions - problem 2
Title: Re: [GENERAL] regarding threads and transactions - problem 2 patient_key is the unique key and the primary key is patient_id, which is a bigserial. actually this is what the stored procedure does: a patient comes and it is associated with patient_key ...if is not present in the table, then insert it into the table. when this patient gets inserted , the Stored procedure will return the id (bigserial) of this patient to the user. and if the patient is alread in the table, then the user should get the id of this patient(which is alread present in the table). is not the stored procedure correctly coded according to the above scenario? CREATE OR REPLACE FUNCTION insert_patient (varchar(65),varchar(65),date,varchar(256)) RETURNS retval AS'DECLARE patId bigint; oid1 int4; val retval; patKey text;BEGIN patKey := $4; select patient_id into patId from patient where patient_key = patKey; if not found THEN insert into patient(patient_name,org_pat_id,birth_date,patient_key) values($1,trim($2),$3,$4); SELECT patient_id INTO val.id from patient where patient_key = patKey; SELECT INTO val.insert TRUE; else val.id := patId; SELECT INTO val.insert FALSE; end if;RETURN val;END;'LANGUAGE plpgsql; From: [EMAIL PROTECTED] on behalf of Richard HuxtonSent: Fri 8/26/2005 2:02 PMTo: Surabhi Ahuja Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED]Subject: Re: [GENERAL] regarding threads and transactions - problem 2 ***Your mail has been scanned by InterScan VirusWall.***-***Surabhi Ahuja wrote: BEGIN patKey := $4; select patient_id into patId from patient where patient_key = patKey; if not found THEN insert into patient(patient_name,org_pat_id,birth_date,patient_key) values($1,trim($2),$3,$4); The output that i am getting (on executing it on a dual processor machine) is as follows: Status is : PGRES_FATAL_ERROR Result message : ERROR: duplicate key violates unique constraint "patient_patient_key_key" CONTEXT: SQL statement "insert into patient(patient_name,org_pat_id,birth_date,patient_key) values( $1 ,trim( $2 ), $3 , $4 )" Please check the block in red. Why is it happening? insnt the call to the stored procedure considered one atomic operation? Please tell me what is going wrong?(For those viewing in plain-text, the red block is the "duplicate pkey"error) Cant I avoid such red blocks? and get messages like the ones obained from the other threads I can impose locks but would not that lower down the performance? Please suggest other solutionsThere is no free solution to the problem of concurrent updates to thesame resource. You have two options:1. Optimistically try the insert and if you get an error catch it andissue the update instead.2. Lock the resource for the duration of your update and deal with thefact that some updates might time-out/fail to get the lock and need tobe retried.3. Don't actually have a shared resource (e.g. use auto-generatedsequence values for meaningless ID numbers).In a nutshell, those are the options available to you, but I wouldrecommend getting a good technical book on concurrency and spending acouple of days with it.In your example, I'm a little confused as to what your primary key is(patient_id or patient_key) and what purpose the other column serves.-- Richard Huxton Archonet Ltd---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Planner create a slow plan without an available index
Hi All, I got a weird problem with the planner which cause my queries to take ages... ill try to explain it shortly and summarized... :) I got the following table (which got 1.2 million rows): Table public.items Column | Type | Modifiers +--+- items_id | text | not null price | numeric(8,2) | not null left | integer | right | integer | Indexes: items_items_id_key UNIQUE, btree (items_id) items_left btree (left) items_left_right btree (left, right) From that table I created the next table in order to save ORDER BY price at the queries: bh.com=# CREATE TABLE items_price AS SELECT * FROM items ORDER BY price; After the creation of the table I created indexes which are exactly the same as the items table has (the source table). Later I ran on both tables VACUUM FULL ANALYZE. Now here start the weird stuff bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left=(SELECT left FROM category WHERE category_id=821) AND right=(SELECT right FROM category WHERE category_id=821) OFFSET 24 LIMIT 13; QUERY PLAN --- Limit (cost=58.27..86.55 rows=13 width=619) (actual time=0.811..130.993 rows=9 loops=1) InitPlan - Index Scan using category_pkey on category (cost=0.00..3.03 rows=1 width=4) (actual time=0.118..0.124 rows=1 loops=1) Index Cond: (category_id = 821) - Index Scan using category_pkey on category (cost=0.00..3.03 rows=1 width=4) (actual time=0.012..0.016 rows=1 loops=1) Index Cond: (category_id = 821) - Index Scan using items_left_right on items (cost=0.00..294897.72 rows=135553 width=619) (actual time=0.314..130.815 rows=33 loops=1) Index Cond: ((left = $0) AND (right = $1)) Total runtime: 131.140 ms (9 rows) bh.com=# ANALYZE items; ANALYZE bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left=(SELECT left FROM category WHERE category_id=821) AND right=(SELECT right FROM category WHERE category_id=821) OFFSET 24 LIMIT 13; QUERY PLAN Limit (cost=57.11..84.77 rows=13 width=626) (actual time=45.512..145316.423 rows=9 loops=1) InitPlan - Index Scan using category_pkey on category (cost=0.00..3.03 rows=1 width=4) (actual time=0.185..0.191 rows=1 loops=1) Index Cond: (category_id = 821) - Index Scan using category_pkey on category (cost=0.00..3.03 rows=1 width=4) (actual time=0.026..0.032 rows=1 loops=1) Index Cond: (category_id = 821) - Index Scan using items_left on items (cost=0.00..293408.52 rows=137924 width=626) (actual time=45.008..145316.246 rows=33 loops=1) Index Cond: (left = $0) Filter: (right = $1) Total runtime: 145316.590 ms (10 rows) The ANALYZE items actually made the planner work without the INDEX and by that the query became a lot slower! after running VACUUM ANALYZE on the items table I receive good results back again. Now I do know the diffrence between the 2 actions (VACUUM ANALYZE vs. ANALYZE) but whats bug me is that when I do the exact same operations on items_price (which is the same table exactly with the same indexes just ordered diffrently) I receive a slow result no matter what I do! I tried to mess with ALTER TABLE items_price ALTER right SET STATISTICS ; (and also on left) with diffrent values up to even 1000 but that didnt help a bit (I did ran VACUUM ANALYZE after each change). I'm quite clueless and also quite in a hurry to finish this project so any help or a piece of clue will be welcomed gladly! Thanks alot in advance (even only for reading what I wrote :P), Ben-Nes Yonatan Canaan Surfing ltd. http://www.canaan.net.il ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Planner create a slow plan without an available index
Ben-Nes Yonatan [EMAIL PROTECTED] writes: Indexes: items_items_id_key UNIQUE, btree (items_id) items_left btree (left) items_left_right btree (left, right) You could get rid of the items_left index --- it's redundant with the first column of the combined index anyway. bh.com=# EXPLAIN ANALYZE SELECT * FROM items WHERE left=(SELECT left FROM category WHERE category_id=821) AND right=(SELECT right FROM category WHERE category_id=821) OFFSET 24 LIMIT 13; Doing OFFSET/LIMIT without an ORDER BY is just asking for trouble. If you were to specify ORDER BY left, right that would probably convince the planner to use the index you want. However ... this query is basically going to suck with any btree index, because btree can't usefully do range checks on two separate variables. There's an exactly similar problem being discussed over in pgsql-novice: http://archives.postgresql.org/pgsql-novice/2005-08/msg00243.php regards, tom lane ---(end of broadcast)--- TIP 1: 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] stack depth limit exceeded
I think that you forgot the table name. CREATE TRIGGER updateContact AFTER INSERT OR UPDATE ON contacts FOR EACH ROW EXECUTE PROCEDURE contacts.addContactField(); Frank Jamie Deppeler wrote: What i am trying to do is update the field contact with field values in firstname and lastname Trigger CREATE TRIGGER updateContact AFTER INSERT OR UPDATE ON FOR EACH ROW EXECUTE PROCEDURE contacts.addContactField(); Procedure CREATE OR REPLACE FUNCTION contacts.addContactField () RETURNS trigger AS $body$ begin update contacts.person set contact = new.firstname where person.primary = new.primary; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; Tom Lane wrote: Jamie Deppeler [EMAIL PROTECTED] writes: At the moment i am trying to execute a very simple function but i am getting the following error stack depth limit exceeded You didn't really show the complete context, but seeing that this is a trigger and it's trying to do an UPDATE person internally, I'll bet a nickel that the trigger itself is on update events on person, and therefore that you've written an infinite recursion. Had you shown more context, I could have given some advice on a better way to do it. If you're trying to alter the row that's about to be stored, you just have to assign to field(s) of the NEW row within the trigger. If you want to do something else, you need to explain what. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] About dropped notifications
The docs state: NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients may get only one notification event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifications received. Instead, use NOTIFY to wake up applications that need to pay attention to something, and use a database object (such as a sequence) to keep track of what happened or how many times it happened. I'm considering setting up a script that listens for notifications for a table and if a row is deleted the script will delete that row's corresponding files. If there are thousands of rows in the table, and I do delete from table, or even delete from table where id 1000 and id2000, will the script be notified of the deletion of each and every row (and subsequently be able to delete that row's files), or will only one notify event be received (or some number less than the actual number of rows deleted)? Thanks, CSN Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] max_connections
I've recently succeeded in lobbying my sysadmins to upgrade from 7.2.0 to 7.4.8 (thanks to everyone for the advice on how to leverage this). I'm now fiddling with some of the performance parameters, and I'm wondering about max_connections. The default appears to be 100 - this is at least an order of magnitude higher than I need. Would much be saved by dropping this down to 10 or less? I gather I could dial shared_buffers up slightly (SHMMAX is 32M on our Solaris boxes), but is there any substantive benefit to conservatively setting max_connections? Thanks. - John D. Burger MITRE ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] About dropped notifications
CSN [EMAIL PROTECTED] writes: I'm considering setting up a script that listens for notifications for a table and if a row is deleted the script will delete that row's corresponding files. If there are thousands of rows in the table, and I do delete from table, or even delete from table where id 1000 and id2000, will the script be notified of the deletion of each and every row (and subsequently be able to delete that row's files), or will only one notify event be received (or some number less than the actual number of rows deleted)? Depends where you are doing the notify from ... but I think with the current implementation, a transaction will emit only one notify per notify event name, even if NOTIFY is executed many times within the transaction. regards, tom lane ---(end of broadcast)--- TIP 1: 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] revoke on database not working as expected
Hi, I'm running into a setup problem (I guess) while trying to prevent a user from creating tables in a database. The setup is a FreeBSD 5.4 database server accessed from a FreeBSD 6.0 development box, both running PostgreSQL 8.0.3. This is what I have configured on the database server (firsa): %%% [EMAIL PROTECTED] /net/postgresql tail pg_hba.conf local all @usersmd5 hostall @users 127.0.0.1/32 md5 hostall @users 192.168.1.0/24md5 local privtesttestpriv md5 hostprivtesttestpriv127.0.0.1/32 md5 hostprivtesttestpriv192.168.1.0/24md5 [EMAIL PROTECTED] /net/postgresql cat users stijn %%% This is what I do on the dev box (tangaloor): %%% [EMAIL PROTECTED] ~ host tangaloor tangaloor.lzee.sandcat.nl has address 192.168.1.105 [EMAIL PROTECTED] ~ psql -U stijn -h firsa template1 Password: Welcome to psql 8.0.3, the PostgreSQL interactive terminal. template1=# \du List of users User name | User ID | Attributes | Groups ---+-++ pgsql | 1 | superuser, create database | stijn | 100 | superuser, create database | template1=# create database privtest; CREATE DATABASE template1=# create user testpriv password 'password'; CREATE USER template1=# \c privtest testpriv Password: You are now connected to database privtest as user testpriv. privtest= create table foo (i varchar(40)); CREATE TABLE privtest= \c template1 stijn Password: You are now connected to database template1 as user stijn. template1=# revoke all on database privtest from testpriv; REVOKE template1=# \c privtest testpriv Password: You are now connected to database privtest as user testpriv. privtest= create table bar (i varchar(40)); CREATE TABLE %%% My question is: why can I still create table 'bar', in a database that was not created by user 'testpriv', having explicitly revoked all privileges on that database by a superuser? What access credential am I missing? I've searched the handbook but all it says is 'use \z in psql to view privileges' and that doesn't list general database privileges. Thanks for any clues. Please CC me as I'm not subscribed. --Stijn -- The right half of the brain controls the left half of the body. This means that only left handed people are in their right mind. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] max_connections
John D. Burger [EMAIL PROTECTED] writes: I'm now fiddling with some of the performance parameters, and I'm wondering about max_connections. The default appears to be 100 - this is at least an order of magnitude higher than I need. Would much be saved by dropping this down to 10 or less? Nothing at all, really, AFAIK; just a little bit of shared memory. On certain platforms (OS X at least) there is a penalty to oversized max_connections because each per-backend-slot semaphore is an open file that has to be passed down when a new backend process is forked. But this is not true on Solaris. I doubt you'd see any difference. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] revoke on database not working as expected
Stijn Hoop [EMAIL PROTECTED] writes: template1=# revoke all on database privtest from testpriv; That doesn't do what you evidently think it does --- it revokes the right to create temp tables, and the right to create new schemas, but not every right in existence. Please read the GRANT/REVOKE manual pages. (Hint: revoking CREATE on the public schema would get you closer to what you want.) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. DYNAMIC is something I made up. ALTER VIEW RECOMPILE is Oraclese but I'm not sure what we're talking about here is exactly the same purpose. I'm not sure it even does anything in Oracle any more. It used to be that *any* DDL on underlying tables caused view on them to become invalid and produce errors until they were recompiled. I think that's changed and recompile may be a noop now on Oracle. It's still necessary in Oracle 9i. Any time a table is changed that has a view on it Bad Things Happen. Jeff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Select gives the wrong results
Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) db_name=# SELECT * from person_detail WHERE field='2' AND value'946702800' AND value'1104555600'; id | person | field | value ++---+--- (0 rows) (select values in DB (date stamps) between Jan 1, 2003 and Jan 1, 2005, 4 results (!)) db_name=# SELECT * from person_detail WHERE field='2' AND value'1041397200' AND value'1104555600'; id | person | field | value --++---+ 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 (4 rows) The first select should have those 4 results plus any more. We tried putting quotes () around the word 'value' to see if that made a difference, and no it didn't. We tried reversing the two clauses and that made no difference. Here's another funny one. Not the one that doesn't belong. db_name=# SELECT * from person_detail WHERE field='2' AND value='11' AND value='1104555600'; id | person | field | value --++---+ 3 |218 | 2 | 1017464400 253 |295 | 2 | 1002340800 514 |323 | 2 | 100155600 1126 |405 | 2 | 1006750800 1179 |439 | 2 | 1035172800 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 1152 |434 | 2 | 1032321600 1129 |410 | 2 | 1024027200 (11 rows) Anyone see what's going on here? Thanks! Crystle -- Crystle Numan, B.Sc., Web Developer Guided Vision: the possibilities are endless 905.528.3095 http://guidedvision.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
John D. Burger [EMAIL PROTECTED] writes: Well, they would have access to every world readable file on the system, ie /etc, /usr, /lib, ... most files are world readable. There's a lot of discussion about this, yet no-one has demonstrated that COPY FROM STDIN isn't just as good and avoids all the issues entirely. Well they're world-readable. So, uh, huh? I haven't completely followed the details of this, but I took the point to be that the files might be readable for anyone with a real account on the server machine, but that doesn't mean they should be accessible to every remote DB user. I was only suggesting using this from a local unix user where you can actually authoritatively say something about the uid of the connecting user. I suggested that if the owner of the file matches the uid of the connecting user (which you can get on a unix domain socket) then there's no reason not to grant access to the file. There isn't really any gain to be had from remote users since they have to get the data to the server one way or another anyways. There's no good reason for piping it over a libpq connection to be any less efficient than an ftp connection (though it might be in practice, that's just an engineering problem to solve). If you already have files sitting on the server and want to grant access to them to remote users then I would say using a SECURITY DEFINER function is exactly what you want. The server doesn't know anything about the remote user so it definitely needs the dba to tell it to allow access to the files. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: In any case here's some quick results from my system. There seems to a greater than 21% slowdown associated with piping the data through two processes instead of reading directly. Well, if the penalty is order of 20% (as opposed to integer multiples) I think the discussion is over. We're not going to introduce arguable security holes for that sort of gain --- there are other places we could find that much speedup for much less risk. Well it's not like it's an either or thing. a 40% speed increase would be even better. I can't see how letting users read files they own can possibly be a security hole. The only case would be if there are files they own in directories they don't have access to read. Which would be a pretty strange circumstance. I could see saying it's not worth the effort to implement it. (Though what I suggested would be a pretty simple patch.) So if I went and implemented it and/or the solution based on passing an fd to the server would it be accepted (assuming the code quality was up to snuff)? (BTW, were you testing CVS tip or 8.0? The recent COPY FROM speedup patch would have affected this test.) No. Actually sadly this is 7.4. I would expect the parsing changes to help in either case though, no? In any case my test was pretty unscientific. I just wanted to say it's not going to be zero effect. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select gives the wrong results
On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) snip results Looks to me like value is a string type, is this possible? ORDER BY value should make it more obvious. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpxGGI5c4X1A.pgp Description: PGP signature
Re: [GENERAL] Select gives the wrong results
On Mon, 29 Aug 2005, Crystle Numan wrote: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) db_name=# SELECT * from person_detail WHERE field='2' AND value'946702800' AND value'1104555600'; id | person | field | value ++---+--- (0 rows) (select values in DB (date stamps) between Jan 1, 2003 and Jan 1, 2005, 4 results (!)) db_name=# SELECT * from person_detail WHERE field='2' AND value'1041397200' AND value'1104555600'; id | person | field | value --++---+ 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 (4 rows) The first select should have those 4 results plus any more. We tried putting quotes () around the word 'value' to see if that made a difference, and no it didn't. We tried reversing the two clauses and that made no difference. Here's another funny one. Not the one that doesn't belong. db_name=# SELECT * from person_detail WHERE field='2' AND value='11' AND value='1104555600'; id | person | field | value --++---+ 3 |218 | 2 | 1017464400 253 |295 | 2 | 1002340800 514 |323 | 2 | 100155600 1126 |405 | 2 | 1006750800 1179 |439 | 2 | 1035172800 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 1152 |434 | 2 | 1032321600 1129 |410 | 2 | 1024027200 (11 rows) Anyone see what's going on here? What type is value? I think you're expecting a numeric comparison but getting a string one. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select gives the wrong results
a beter idea is to use -mm-dd hh:mi:ss format 2005/8/29, Martijn van Oosterhout kleptog@svana.org: On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) snip results Looks to me like value is a string type, is this possible? ORDER BY value should make it more obvious. -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. -- Atte Moises Alberto Lindo Gutarra Consultor y Desarrollador Java / Open Source TUMI Solutions SAC Tel: +51.13481104 Cel: +51.197366260 MSN : [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select gives the wrong results
It looks like your value column is of a varchar(), char() or text type. The and operators compare the ordinal value of the text when used on text types. You'll want to use ALTER TABLE ... ALTER COLUMN ... to change value into a numeric type (probably INT or BIGINT), and then you'll get the intended result On 8/29/05, Crystle Numan [EMAIL PROTECTED] wrote: Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) db_name=# SELECT * from person_detail WHERE field='2' AND value'946702800' AND value'1104555600'; id | person | field | value ++---+--- (0 rows) (select values in DB (date stamps) between Jan 1, 2003 and Jan 1, 2005, 4 results (!)) db_name=# SELECT * from person_detail WHERE field='2' AND value'1041397200' AND value'1104555600'; id | person | field | value --++---+ 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 (4 rows) The first select should have those 4 results plus any more. We tried putting quotes () around the word 'value' to see if that made a difference, and no it didn't. We tried reversing the two clauses and that made no difference. Here's another funny one. Not the one that doesn't belong. db_name=# SELECT * from person_detail WHERE field='2' AND value='11' AND value='1104555600'; id | person | field | value --++---+ 3 |218 | 2 | 1017464400 253 |295 | 2 | 1002340800 514 |323 | 2 | 100155600 1126 |405 | 2 | 1006750800 1179 |439 | 2 | 1035172800 1187 |454 | 2 | 1051156800 1188 |460 | 2 | 1053316800 1219 |472 | 2 | 1057723200 1181 |441 | 2 | 1042520400 1152 |434 | 2 | 1032321600 1129 |410 | 2 | 1024027200 (11 rows) Anyone see what's going on here? Thanks! Crystle -- Crystle Numan, B.Sc., Web Developer Guided Vision: the possibilities are endless 905.528.3095 http://guidedvision.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: 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] About dropped notifications
[EMAIL PROTECTED] (Tom Lane) writes: CSN [EMAIL PROTECTED] writes: I'm considering setting up a script that listens for notifications for a table and if a row is deleted the script will delete that row's corresponding files. If there are thousands of rows in the table, and I do delete from table, or even delete from table where id 1000 and id2000, will the script be notified of the deletion of each and every row (and subsequently be able to delete that row's files), or will only one notify event be received (or some number less than the actual number of rows deleted)? Depends where you are doing the notify from ... but I think with the current implementation, a transaction will emit only one notify per notify event name, even if NOTIFY is executed many times within the transaction. An interesting question is whether or not the relevant tuple in pg_listener gets invalidated once, or whether doing 2500 attempted NOTIFY requests blows through 2500 copies. -- cbbrowne,@,cbbrowne.com http://www.ntlug.org/~cbbrowne/internet.html As long as each individual is facing the TV tube alone, formal freedom poses no threat to privilege. --Noam Chomsky ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] GiST access is not concurrent
Does this mean that read access is not concurrent, or write access, or both? --John
Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug
oops i forgot to attach logfile output for the second case (LIBPQ.SO.3.1) : [EMAIL PROTECTED] DBApi]# cat /var/lib/pgsql/logfile LOG: database system was interrupted at 2005-08-29 15:01:11 PDT LOG: checkpoint record is at 0/655FF630 LOG: redo record is at 0/655FF630; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 15567; next OID: 11920206 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/655FF66C LOG: redo is not required LOG: database system is ready LOG: could not accept SSL connection: peer did not return a certificate On 8/26/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Fri, Aug 26, 2005 at 01:57:36PM -0700, vishal saberwal wrote: I am not sure as to how i can find the version of libpq that i am using on my server. My test file has sslmode=prefer. This is what i did: (a) [EMAIL PROTECTED] serv]# ./bin/test_lib Connection failed: SSL error: sslv3 alert handshake failureI asked what appeared in the server's logs when this happened butI don't see that information in your post.Those log entries might be useful, so please post them if you continue to have trouble. ret=-1 [EMAIL PROTECTED] serv]# ldd ./bin/test_lib ... libpq.so.3 = /usr/lib/libpq.so.3 (0x002ee000) What's the output of the following command?ls -l /usr/lib/libpq.so*In my 7.4.8 installation I see the following:libpq.so - libpq.so.3.1libpq.so.3 - libpq.so.3.1libpq.so.3.1 As I recall, 8.0.1's libpq was libpq.so.3.2 (this was changed tolibpq.so.4.0 in 8.0.2), so the library's minor version should tellyou which version of PostgreSQL you're linked against.I asked if the certificate works with psql, and if it does, for you to show the output of ldd psql.I don't see that output -- doespsql work?If so then please post its ldd output. [EMAIL PROTECTED] libk2]# ./bin/test_k2 Connection failed: could not open certificate file /root/.postgresql/postgresql.crt: No such file or directoryWhat's test_k2?It's looking for the certificate, although perhapsnot where you want it to.What happens if you run this program as a user that has a certificate, or if you install the certificateand key in /root/.postgresql?Also, I don't see test_k2's lddoutput -- is it linked against /usr/local/pgsql/lib/libpq.so.3?If so, what's the output of the following command? ls -l /usr/local/pgsql/lib/libpq.so*I'm wondering if you have PostgreSQL 7.4's libraries installed in/usr/lib and 8.0.1's libraries in /usr/local/pgsql/lib.Is thatwhat you've done?-- Michael Fuhr
Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug
hi michael and tom, (A) With LIBPQ.SO.3.2 After reading your response i copied the libpq.so.3.2 from the compiled source tree to /usr/lib where the version available was libpq.so.3.1. I recreated the symbolic links and now the links are as below: [EMAIL PROTECTED] DBApi]# ls -l /usr/lib/libpq* -rw-r--r-- 1 postgres root 1480452 Mar 10 2004 /usr/lib/libpq.a lrwxrwxrwx 1 postgres root 21 Aug 29 15:00 /usr/lib/libpq.so - /usr/lib/libpq.so.3.2 lrwxrwxrwx 1 postgres root 21 Aug 29 14:59 /usr/lib/libpq.so.3 - /usr/lib/libpq.so.3.2 -rwxr-xr-x 1 postgres root 113988 Mar 10 2004 /usr/lib/libpq.so.3.1 -rwxr-xr-x 1 postgres root 122177 Aug 26 12:55 /usr/lib/libpq.so.3.2 [EMAIL PROTECTED] DBApi]# ls -l /usr/local/pgsql/lib/libpq.so* lrwxrwxrwx 1 root root 12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so - libpq.so.3.2 lrwxrwxrwx 1 root root 12 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3 - libpq.so.3.2 -rwxr-xr-x 1 root root 122177 Aug 26 13:17 /usr/local/pgsql/lib/libpq.so.3.2 [EMAIL PROTECTED] DBApi]# which psql /usr/bin/psql [EMAIL PROTECTED] DBApi]# psql --version psql (PostgreSQL) 8.0.1 contains support for command-line editing [EMAIL PROTECTED] DBApi]# env |grep -i LD_LIBRARY LD_LIBRARY_PATH=/usr/local/pgsql/lib [EMAIL PROTECTED] DBApi]# The postmaster command is: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data/ -i logfile 21 As postgres user: -bash-2.05b$ which psql /usr/local/pgsql/bin/psql -bash-2.05b$ psql --version psql (PostgreSQL) 8.0.1 contains support for command-line editing -bash-2.05b$ env |grep -i LD_LIBRARY LD_LIBRARY_PATH=/usr/local/pgsql/lib/ -bash-2.05b$ test_k2 was a typo (sorry about that) ... [EMAIL PROTECTED] serv]# ldd ./bin/test_lib linux-gate.so.1 = (0x00a4e000) libpthread.so.0 = /lib/tls/libpthread.so.0 (0x003c8000) libpq.so.3 = /usr/lib/libpq.so.3 (0x008b4000) libstdc++.so.5 = /usr/lib/libstdc++.so.5 (0x0059) libm.so.6 = /lib/tls/libm.so.6 (0x002b) libgcc_s.so.1 = /lib/libgcc_s.so.1 (0x004e7000) libc.so.6 = /lib/tls/libc.so.6 (0x00193000) /lib/ld-linux.so.2 = /lib/ld-linux.so.2 (0x00176000) libssl.so.4 = /lib/libssl.so.4 (0x00c6a000) libcrypto.so.4 = /lib/libcrypto.so.4 (0x0076f000) libcrypt.so.1 = /lib/libcrypt.so.1 (0x00caa000) libresolv.so.2 = /lib/libresolv.so.2 (0x003ff000) libnsl.so.1 = /lib/libnsl.so.1 (0x00c53000) libgssapi_krb5.so.2 = /usr/lib/libgssapi_krb5.so.2 (0x00758000) libkrb5.so.3 = /usr/lib/libkrb5.so.3 (0x006aa000) libcom_err.so.2 = /lib/libcom_err.so.2 (0x006a5000) libk5crypto.so.3 = /usr/lib/libk5crypto.so.3 (0x00714000) libdl.so.2 = /lib/libdl.so.2 (0x002d5000) libz.so.1 = /usr/lib/libz.so.1 (0x002db000) [EMAIL PROTECTED] serv]# now i ran the program i had that has a conect command with (hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=prefer) parameters. [EMAIL PROTECTED] serv]# ./bin/test_lib Connection failed: could not open certificate file /root/.postgresql/postgresql.crt: No such file or directory ret=-1 I don't think i need to have ~/.postgresql/postgresql.crt on server. I thought that was the requirement only with the clients ... so, i think i shouldn't be getting this error. On server (as per documentation) i need to have the files in $PGDATA rather than in ~/.postgresql. Hence this question. [EMAIL PROTECTED] serv]# cat /var/lib/pgsql/logfile LOG: database system was interrupted at 2005-08-29 12:56:46 PDT LOG: checkpoint record is at 0/655FF5F4 LOG: redo record is at 0/655FF5F4; undo record is at 0/0; shutdown FALSE LOG: next transaction ID: 15567; next OID: 11920206 LOG: database system was not properly shut down; automatic recovery in progress LOG: record with zero length at 0/655FF630 LOG: redo is not required LOG: database system is ready LOG: could not accept SSL connection: peer did not return a certificate LOG: could not accept SSL connection: peer did not return a certificate LOG: could not accept SSL connection: peer did not return a certificate LOG: could not accept SSL connection: peer did not return a certificate Now with allow: hostaddr=169.254.59.60 dbname=dbm user=postgres sslmode=allow [EMAIL PROTECTED] libk2]# ./bin/test_k2 ret=0 GOT CONNECTION NAME AGE me 1 you 2 they 3 us 4 [EMAIL PROTECTED] libk2]# (B) With LIBPQ.SO.3.1 [EMAIL PROTECTED] DBApi]# ll /usr/lib/libpq* -rw-r--r-- 1 postgres root 1480452 Mar 10 2004 /usr/lib/libpq.a lrwxrwxrwx 1 postgres root 21 Aug 29 15:00 /usr/lib/libpq.so - /usr/lib/libpq.so.3.2 lrwxrwxrwx 1 postgres root 21 Aug 29 14:59 /usr/lib/libpq.so.3 - /usr/lib/libpq.so.3.2 -rwxr-xr-x 1 postgres root 113988 Mar 10 2004 /usr/lib/libpq.so.3.1 -rwxr-xr-x 1 postgres root 122177 Aug 26 12:55 /usr/lib/libpq.so.3.2 [EMAIL PROTECTED] DBApi]# rm /usr/lib/libpq.so.3 rm: remove symbolic link `/usr/lib/libpq.so.3'? y [EMAIL PROTECTED] DBApi]# ln -s /usr/lib/libpq.so.3.1 /usr/lib/libpq.so.3 [EMAIL PROTECTED] DBApi]# rm /usr/lib/libpq.so rm: remove symbolic link `/usr/lib/libpq.so'? y [EMAIL PROTECTED]
Re: [GENERAL] About dropped notifications
Chris Browne [EMAIL PROTECTED] writes: [EMAIL PROTECTED] (Tom Lane) writes: with the current implementation, a transaction will emit only one notify per notify event name, even if NOTIFY is executed many times within the transaction. An interesting question is whether or not the relevant tuple in pg_listener gets invalidated once, or whether doing 2500 attempted NOTIFY requests blows through 2500 copies. Once. (Per transaction...) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About ERROR: must be *superuser* to COPY to or from a file
Greg Stark [EMAIL PROTECTED] writes: I was only suggesting using this from a local unix user where you can actually authoritatively say something about the uid of the connecting user. I suggested that if the owner of the file matches the uid of the connecting user (which you can get on a unix domain socket) ... on some platforms ... and half the world connects over TCP even on local connections ... then there's no reason not to grant access to the file. Assuming that the server itself can get at the file, which is questionable if the file is owned by the connecting user rather than the server (and, for instance, may be located under a not-world-readable home directory). And then there are interesting questions like whether the server and the user see eye-to-eye on the name of the file (consider server inside chroot jail, AFS file systems, etc). There are enough holes in this to make it less than attractive. We'd spend more time answering questions about why doesn't this work than we do now, and I remain unconvinced that there would be no exploitable security holes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] update functions locking tables
I have almost completed one web site migration from mysql to pgsql. It is already running totally pgsql. But there is one last conversion problem. Most of the queries use tables populated every 3 hours. In mysql, for the site to not be unavailable during updating i was creating temporary tables, populating them, dropping the main tables and then renaming the temp tables to the main tables. The updating is not trivial (for me) and needs some coding effort to be done. Since pgsql has MVCC I wanted to eliminate the table rotation step and use a transaction to update the tables. But what is happening is that the plpgsql update functions are locking the tables and this is what the web clients are getting (from ps ax): 26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting 23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting 31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting 21601 ? S 0:00 postgres: user database 127.0.0.1(46770) SELECT waiting I've been trying for 3 days to figure out what is happening to no avail. What am i missing about transactions and MVCC? What could make a plpgsql update function lock a table? The indexes are default btree. Otherwise the functions are behaving exactly as expected. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] update functions locking tables
Clodoaldo Pinto [EMAIL PROTECTED] writes: I've been trying for 3 days to figure out what is happening to no avail. What am i missing about transactions and MVCC? What could make a plpgsql update function lock a table? What is the function doing to the table, exactly? DDL changes generally take exclusive locks ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] update functions locking tables
On Mon, Aug 29, 2005 at 09:41:21PM -0300, Clodoaldo Pinto wrote: 26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting 23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting 31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting 21601 ? S 0:00 postgres: user database 127.0.0.1(46770) SELECT waiting I've been trying for 3 days to figure out what is happening to no avail. What am i missing about transactions and MVCC? What could make a plpgsql update function lock a table? The indexes are default btree. In general, writers shouldn't block readers. Have you examined pg_locks? Do you know exactly what the blocked queries are, or can you find out from pg_stat_activity (stats_command_string must be enabled)? Are you doing any explicit locking (LOCK statement)? -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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] Access more than one database from pgAdmin III
How can we access more than one database template1from pgAdmin III? Currently in my pgAdmin III, I'm already accessing one database. When I tried to create another new database it says ERROR: database template1 already exists. May I know if we can actually connect to 2nd database? e.g. template1
Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug
On Mon, Aug 29, 2005 at 04:23:13PM -0700, vishal saberwal wrote: now i ran the program i had that has a conect command with (hostaddr= 169.254.59.60 http://169.254.59.60 dbname=dbm user=postgres sslmode=prefer) parameters. [EMAIL PROTECTED] serv]# ./bin/test_lib Connection failed: could not open certificate file /root/.postgresql/postgresql.crt: No such file or directory ret=-1 I don't think i need to have ~/.postgresql/postgresql.crt on server. I thought that was the requirement only with the clients ... so, i think i shouldn't be getting this error. On server (as per documentation) i need to have the files in $PGDATA rather than in ~/.postgresql. Hence this question. An application that connects to the database is a client, regardless of what machine it runs on. If the client (the application) makes a TCP connection to the server (the database) and the server requests a certificate, then the client must provide a certificate or the server will reject the connection. To learn more about what files go where and how they're used, see Secure TCP/IP Connections with SSL and SSL Support in the documentation: http://www.postgresql.org/docs/8.0/static/ssl-tcp.html http://www.postgresql.org/docs/8.0/static/libpq-ssl.html (a) Where am i going wrong? You're trying to do client authentication with a version of libpq that won't work, and when you do link with a good version of libpq then you're not providing a client certificate. (b) Why are the error messages different? Because the failure modes are different. In one case the client is apparently attempting to make an SSL connection without a certificate; in the other case the client is looking for a certificate and can't find one. (c) When LD_LIBRARY_PATH is set to /usr/local/pgsql/lib, then why does it matter if the links on /usr/lib/libpq.so are changed? That's a system issue, not a PostgreSQL issue. Some people consider LD_LIBRARY_PATH to be an ugly hack anyway and recommend against its use except for testing purposes. You might want to consider using linker options that tell the executable where to find its shared libraries at run time; see your build tools' documentation for details. -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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] stack depth limit exceeded
On Mon, Aug 29, 2005 at 01:45:32PM +1000, Jamie Deppeler wrote: CREATE TRIGGER updateContact AFTER INSERT OR UPDATE ON FOR EACH ROW EXECUTE PROCEDURE contacts.addContactField(); Please show the actual commands that you're running; the above fails with a syntax error because it's missing a table name. Is this trigger on contacts.person? CREATE OR REPLACE FUNCTION contacts.addContactField () RETURNS trigger AS $body$ begin update contacts.person set contact = new.firstname where person.primary = new.primary; return null; end; $body$ LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; As Tom mentioned, if you want to modify the record being inserted then simply assign a value to one of NEW's columns and have the function return NEW. In such a case the function will need to be called in a BEFORE trigger. See Triggers and Trigger Procedures in the documentation for more information: http://www.postgresql.org/docs/8.0/static/triggers.html http://www.postgresql.org/docs/8.0/static/plpgsql-trigger.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] [SQL] question
The issue has been solved thanks to a custom
nullif_int() function. Which if anyone has the same
issue, it was solved with...
CREATE FUNCTION nullif_int(text) RETURNS integer AS
'SELECT nullif($1,)::int;' LANGUAGE SQL;
SELECTS were not the issue; INSERT INTO a non-text
column was the issue.
Thanks anyway,
Matthew
--- Thomas F. O'Connell [EMAIL PROTECTED] wrote:
Matt,
In PostgreSQL 8.0.3, I see:
postgres=# select nullif( '1', '' );
nullif
1
(1 row)
postgres=# select nullif( '', '' ) is null;
?column?
--
t
(1 row)
What behavior are you expecting?
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
On Aug 24, 2005, at 12:05 AM, Matt A. wrote:
I have a rating section on a website. It has radio
buttons with values 1-5 according to the rating
level.
Lastly there is a null option for n/a. We use null
for
n/a so it's excluded from the AVG() calculations.
We used nullif('$value','') on inserts in mssql.
We
moved to postgres and love it but the nullif()
doesn't
match empty strings to each other to return null
other
than a text type, causing an error. This is a
major
part of our application.
AKA nullif('1','') would insert 1 as integer even
though wrapped in ''. Also nullif('','') would
evaluate NULL and insert the not a value
accordingly.
Is there a workaround for this so it doesn't break
our
rating system? We cannot always enter a value for
a
integer column. Is there any other way to
accomplish
this? Please help.
__
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Start your day with Yahoo! - make it your home page
http://www.yahoo.com/r/hs
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
