Re: [GENERAL] Delete trigger
Hi Ioana and David. Thank you. Yes, I can see the problem. I will look into your suggestions. Leif - Original Message - > On Friday, September 18, 2015, Leif Jensen wrote: > > >Hello Laurenz, > > > >Thank you for you suggestion. I really want to aviod that someone > > 'accidentally' deletes too much by typing (programming) a not full > > qualified DELETE ... statement. In your case one would have to always use > > the delete function, but no restrictions on using the DELETE statement. > > > > > There is no way you can prevent a superuser from shooting themselves in the > foot. For anyone else you can enforce use of the function to perform the > delete. > > You could make a field called ok-to-delete and add a partial unique index > on it so that only a single record can be marked ok to delete at a time and > then have your trigger abort if it tries to delete a field without the ok > to delete field set to true. > > David J. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete trigger
Hello Laurenz, Thank you for you suggestion. I really want to aviod that someone 'accidentally' deletes too much by typing (programming) a not full qualified DELETE ... statement. In your case one would have to always use the delete function, but no restrictions on using the DELETE statement. Leif - Original Message ----- > Leif Jensen wrote: > >If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete > >anything. I only want to > > delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND > > userid=z". I don't wanna let > > anyone delete more than 1 row at a time. > > I can't think of a way to do that with a trigger. > > I'd write a > FUNCTION delete_devicegroup(groupid integer, ctrlid integer, userid > integer) > RETURNS void CALLED ON NULL INPUT VOLATILE SECURITY DEFINER > that enables the user to delete a row and checks that all arguments > are NOT NULL. The user doesn't get privileges to DELETE from the table > directly. > > Yours, > Laurenz Albe > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete trigger
Hi Charles, If I do "DELETE FROM devicegroup WHERE group=1" I do not want to delete anything. I only want to delete if I do "DELETE FROM devicegroup WHERE groupid=x AND ctrlid=y AND userid=z". I don't wanna let anyone delete more than 1 row at a time. Leif - Original Message - > Hello > > Not sure I get it right, but all three fields are not nullable. So they will > always have a value, which is what I understand of "are specified". > What do you need the trigger for in that case? > > Bye > Charles > > > -Original Message- > > From: pgsql-general-ow...@postgresql.org > > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leif Jensen > > Sent: Freitag, 18. September 2015 10:23 > > To: pgsql-general > > Subject: [GENERAL] Delete trigger > > > >Hi, > > > >I am running PostgreSQL 9.3.4. I have a simple table with 3 fields: > > > > CREATE TABLE devicegroup ( > > groupid integer NOT NULL, > > ctrlid integer NOT NULL, > > userid integer NOT NULL > > ); > > ALTER TABLE ONLY devicegroup > > ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid); > > > >I want to make sure that records are only deleted when all 3 fields are > >specified, so I tried make a trigger: > > > > CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH > > STATEMENT > > EXECUTE PROCEDURE deleteUserDev(); > > > > which could check for NOT NULL on the 3 fields before actual doing the > > delete. Unfortunately this is not possible to > > do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to > > check for NOT NULL. > > > >Any ideas ? > > > > Leif > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Delete trigger
Hi, I am running PostgreSQL 9.3.4. I have a simple table with 3 fields: CREATE TABLE devicegroup ( groupid integer NOT NULL, ctrlid integer NOT NULL, userid integer NOT NULL ); ALTER TABLE ONLY devicegroup ADD CONSTRAINT pk_devicegroup PRIMARY KEY (groupid, ctrlid, userid); I want to make sure that records are only deleted when all 3 fields are specified, so I tried make a trigger: CREATE TRIGGER userDev_trig INSTEAD OF DELETE ON admdevgrid FOR EACH STATEMENT EXECUTE PROCEDURE deleteUserDev(); which could check for NOT NULL on the 3 fields before actual doing the delete. Unfortunately this is not possible to do FOR EACH STATEMENT, and FOR EACH ROW would not give me the chance to check for NOT NULL. Any ideas ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server process crash - Segmentation fault
Hello Tom, Adrian Thank you for your help and the patch. Things works nicely for me now :-). Leif - Original Message - > Adrian Klaver writes: > > On 05/08/2014 07:19 AM, Tom Lane wrote: > >> 9.3 patch is here: > >> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=fc58c39d468587467c7c55b349c28167794eadaf > > > Alright, so I obviously linked to the wrong patch because I pointed > > at > > HEAD and not REL9_3_STABLE. > > I think those patches are the same, actually, but I was trying to be > careful. > > > What I am trying to figure out is what is > > the distinction between commit and commitdiff? > > The "commitdiff" link shows you the actual diffs in the patch, the > other > one doesn't. > > In practice, Leif's going to want to hit the "patch" link anyway to > get a > clean downloadable patch; so likely what we should have pointed him at > is > http://git.postgresql.org/gitweb/?p=postgresql.git;a=patch;h=fc58c39d468587467c7c55b349c28167794eadaf > I'm just in the habit of looking at the "commitdiff" versions of the > web > pages as being the best readability/information tradeoff for casual > examination of a patch. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server process crash - Segmentation fault
Hi Tom, I already compiled postgreSQL myself and now using 9.3.4, so I would very much like a patch. Where can I find that ? Leif - Original Message - > Leif Jensen writes: > >Could it be related to the OFFSET part of the statement ? I have > >another query on the same table without OFFSET, which seems to > >work fine. > > Yeah, the specific code path here involves executing a stable (or > possibly > immutable) SQL function in a LIMIT or OFFSET clause. I was able to > reproduce the crash like so: > > create function foo(int) returns int as 'select $1 limit 1' > language sql stable; > > begin; > > declare c cursor for select * from int8_tbl limit foo(3); > > select * from c; > > move backward all in c; > > select * from c; > > commit; > > You might be able to dodge the problem if you can make the SQL > function > inline-able (the LIMIT 1 in my example is just to prevent that from > happening). A less appealing alternative is to mark the function > VOLATILE, which I think would also prevent this crash, but might have > negative performance consequences. > > If you don't mind building your own PG then you could grab the actual > fix > from our git repo; I should have something committed before long. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server process crash - Segmentation fault
Could it be related to the OFFSET part of the statement ? I have another query on the same table without OFFSET, which seems to work fine. Leif - Original Message - > Leif Jensen writes: > >Here is a gdb dump of the backtrace at the server process crash. > >I have also included the code that generates these calls. As > >mentioned below this specific connection has been used many times > >before the crash. Also, we are aware of the thread caveat that > >only using a connection from one thread at a time. Therefore the > >"strange" connection name that includes both the process id and > >the thread id. This is for the code to make sure that a > >connection is only used in the thread it is meant to. > > Hm. The crash looks like it must be because ActiveSnapshot is null > (not set). Since we're doing a FETCH, the active snapshot ought to > be the one saved for the cursor query by DECLARE CURSOR. It looks > like the problem is that pquery.c only bothers to install that as the > active snapshot while calling ExecutorRun, but in this stack trace > we're in ExecutorRewind. > > I wonder if it's a bad idea for ExecReScanLimit to be executing > user-defined expressions? But it's been like that for awhile, > and I think we might have a hard time preserving the bounded-sort > optimization if we didn't do that. > > Anyway the simple fix would be to ensure we install the query > snapshot as active before calling ExecutorRewind. > > One interesting question is why this issue hasn't been seen before; > it seems like it'd not be that hard to hit. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server process crash - Segmentation fault
Hello Adrian, Thank you for your answer. I can post part of the code that makes these calls, but I'm not sure how much it would help. It is rather large function that makes these calls, and it is called all over the program. The part of the log posted is only a small excerpt of the use of the ApplDBConn_22854_f6adeb70_query, which has been used many many times before the log shown (167 in all to be exact ;-) ). Leif - Original Message - > On 05/06/2014 07:08 AM, Leif Jensen wrote: > > Hello. > > > > I was running PostgreSQL 9.1.4 when I got a server process crash > > (Segmentation fault) as the postgres log shown below. I tried > > upgrade to newest version 9.3.4, but this gives exactly the same > > problem. > > > > It is an (ecpg based) C-program that does tons of these scroll > > cursor exercises. Until recently this worked too but changes to > > totally different part of the program made this happen. (I have > > made way too many changes to this other part to be able to "roll > > back" the code :-( ). The system generates data all the time for > > this lookup, but I can grab the SQL from the postgres log and > > run it through psql and get the result I expect, so I don't see > > how it can be data related. > > > > Please help, > > > > Leif > > > > . > > . > > . > > 22864 2014-05-06 15:37:35.350 CEST LOG: statement: close execcurs > > 22864 2014-05-06 15:37:35.350 CEST LOG: statement: deallocate > > "ApplDBConn_22854_f6adeb70_query" > > 22864 2014-05-06 15:37:35.352 CEST DEBUG: parse > > ApplDBConn_22854_f6adeb70_query: SELECT data_type FROM > > information_schema.columns WHERE table_name = > > 'l2_hb_water_hours_sum' AND column_name = ''; > > 22864 2014-05-06 15:37:35.353 CEST LOG: statement: declare execcurs > > scroll cursor for SELECT data_type FROM information_schema.columns > > WHERE table_name = 'l2_hb_water_hours_sum' AND column_name = ' > > '; > > 22864 2014-05-06 15:37:35.356 CEST LOG: statement: fetch first in > > execcurs > > 22864 2014-05-06 15:37:35.358 CEST LOG: statement: close execcurs > > 22864 2014-05-06 15:37:35.358 CEST LOG: statement: deallocate > > "ApplDBConn_22854_f6adeb70_query" > > 22864 2014-05-06 15:37:35.359 CEST LOG: statement: commit > > 22864 2014-05-06 15:37:35.359 CEST LOG: statement: start transaction > > read only > > 22864 2014-05-06 15:37:35.360 CEST DEBUG: parse > > ApplDBConn_22854_f6adeb70_query: SELECT montime, year, month, day, > > hh, gal_hour, exp_hour, unsched_hour FROM l2_hb_water_hours_sum > > WHERE l2_hb_water_ > > hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET (SELECT CASE > > WHEN count(*) > 2000 THEN count(*) -2000 ELSE 0 END FROM > > l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 86 ); > > 22864 2014-05-06 15:37:35.365 CEST LOG: statement: declare execcurs > > scroll cursor for SELECT montime, year, month, day, hh, gal_hour, > > exp_hour, unsched_hour FROM l2_hb_water_hours_sum WHERE l2_hb > > _water_hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET > > (SELECT CASE WHEN count(*) > 2000 THEN count(*) -2000 ELSE 0 END > > FROM l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 8 > > 6 ); > > The code that generates the above would be helpful. The thing that > catches my eye is that the first time you use > ApplDBConn_22854_f6adeb70_query the parse and cursor queries are the > same and all is good. The second time they are not and you get a > failure. Without seeing what is going in in your code it is hard to > tell > if this significant or not. > > > 22864 2014-05-06 15:37:35.432 CEST LOG: statement: fetch first in > > execcurs > > 21702 2014-05-06 15:37:35.440 CEST DEBUG: server process (PID 22864) > > was terminated by signal 11: Segmentation fault > > 21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was > > running: fetch first in execcurs > > 21702 2014-05-06 15:37:35.440 CEST LOG: server process (PID 22864) > > was terminated by signal 11: Segmentation fault > > 21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was > > running: fetch first in execcurs > > 21702 2014-05-06 15:37:35.440 CEST LOG: terminating any other active > > server processes > > > -- > Adrian Klaver > adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server process crash - Segmentation fault
Hello. I was running PostgreSQL 9.1.4 when I got a server process crash (Segmentation fault) as the postgres log shown below. I tried upgrade to newest version 9.3.4, but this gives exactly the same problem. It is an (ecpg based) C-program that does tons of these scroll cursor exercises. Until recently this worked too but changes to totally different part of the program made this happen. (I have made way too many changes to this other part to be able to "roll back" the code :-( ). The system generates data all the time for this lookup, but I can grab the SQL from the postgres log and run it through psql and get the result I expect, so I don't see how it can be data related. Please help, Leif . . . 22864 2014-05-06 15:37:35.350 CEST LOG: statement: close execcurs 22864 2014-05-06 15:37:35.350 CEST LOG: statement: deallocate "ApplDBConn_22854_f6adeb70_query" 22864 2014-05-06 15:37:35.352 CEST DEBUG: parse ApplDBConn_22854_f6adeb70_query: SELECT data_type FROM information_schema.columns WHERE table_name = 'l2_hb_water_hours_sum' AND column_name = ''; 22864 2014-05-06 15:37:35.353 CEST LOG: statement: declare execcurs scroll cursor for SELECT data_type FROM information_schema.columns WHERE table_name = 'l2_hb_water_hours_sum' AND column_name = ' '; 22864 2014-05-06 15:37:35.356 CEST LOG: statement: fetch first in execcurs 22864 2014-05-06 15:37:35.358 CEST LOG: statement: close execcurs 22864 2014-05-06 15:37:35.358 CEST LOG: statement: deallocate "ApplDBConn_22854_f6adeb70_query" 22864 2014-05-06 15:37:35.359 CEST LOG: statement: commit 22864 2014-05-06 15:37:35.359 CEST LOG: statement: start transaction read only 22864 2014-05-06 15:37:35.360 CEST DEBUG: parse ApplDBConn_22854_f6adeb70_query: SELECT montime, year, month, day, hh, gal_hour, exp_hour, unsched_hour FROM l2_hb_water_hours_sum WHERE l2_hb_water_ hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET (SELECT CASE WHEN count(*) > 2000 THEN count(*) -2000 ELSE 0 END FROM l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 86 ); 22864 2014-05-06 15:37:35.365 CEST LOG: statement: declare execcurs scroll cursor for SELECT montime, year, month, day, hh, gal_hour, exp_hour, unsched_hour FROM l2_hb_water_hours_sum WHERE l2_hb _water_hours_sum.ctrlid = 86 ORDER BY year,month,day,hh OFFSET (SELECT CASE WHEN count(*) > 2000 THEN count(*) -2000 ELSE 0 END FROM l2_hb_water_hours_sum WHERE l2_hb_water_hours_sum.ctrlid = 8 6 ); 22864 2014-05-06 15:37:35.432 CEST LOG: statement: fetch first in execcurs 21702 2014-05-06 15:37:35.440 CEST DEBUG: server process (PID 22864) was terminated by signal 11: Segmentation fault 21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was running: fetch first in execcurs 21702 2014-05-06 15:37:35.440 CEST LOG: server process (PID 22864) was terminated by signal 11: Segmentation fault 21702 2014-05-06 15:37:35.440 CEST DETAIL: Failed process was running: fetch first in execcurs 21702 2014-05-06 15:37:35.440 CEST LOG: terminating any other active server processes 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22896 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22893 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22882 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22751 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22749 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 22748 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 21705 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 21704 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 21706 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 21707 22751 2014-05-06 15:37:35.440 CEST WARNING: terminating connection because of crash of another server process 22751 2014-05-06 15:37:35.440 CEST DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibl y corrupted shared memory. 22751 2014-05-06 15:37:35.440 CEST HINT: In a moment you should be able to reconnect to the database and repeat your command. 21702 2014-05-06 15:37:35.440 CEST DEBUG: sending SIGQUIT to process 21708 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ECPG SET CONNECTION
Hi guys. In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION ; it is stated that "This is not thread-aware". When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it looks very much like it is thread-aware if translated with the --enable-thread-safety option. What should I believe ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rules on views - Changes from 8.4 to 9.1 ?
Hello. We have a system that has been running using PostgreSQL 8.4. We have now upgraded to PostgreSQL 9.1. The system has several rules on views and now most of these does not seem to work anymore. It is rather simple rules with mainly only 1 replacement sql (update for 'on update' and insert for 'on insert'). Have something basic things changed on the rule system and if so, where do I find information about it ? Please help, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update rule on a view - what am I doing wrong
Hi Marc, Thanks a lot. That works fine. The names 'NEW' and 'OLD' works fine. Leif - "Marc Schablewski" wrote: > Hi Leif, > > Am 22.01.2013 14:34, schrieb Leif Jensen: > > > CREATE update_rule_func( old record, new record ) AS ... I am told, > that I cannot use record for the parameter type. Could you please You > should use your view instead of 'record' as parameter type, i.e. > CREATE update_rule_func( old V_YOUR_VIEW, new V_YOUR_VIEW ) AS ... . > Also, I'm not sure if 'new' and 'old' are reserved keywords in > PostgreSQL, so you might want to choose different names for your > parameters if you still have trouble with that function. > > Marc -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Update rule on a view - what am I doing wrong
Hi Jasen. Thank you for your response (also thank you to Tom). I have now tried your suggestion, but I'm not sure how you have implemented the plpgsql function. When I create the function: CREATE update_rule_func( old record, new record ) AS ... I am told, that I cannot use record for the parameter type. Could you please expand a little on your example ? Leif - "Jasen Betts" wrote: > On 2013-01-18, Leif Jensen wrote: > > >I have been fighting a problem with an update rule on a view. I > > have a view that combines two tables where the 'sub' table (scont) > can > > have several rows per row in the 'top' table (icont). The view > > combines these to show only one record per row in the top table. To > be > > able to update on this view I have created a rule 'on update'. The > > rule needs to have both UPDATE, DELETE, and INSERT commands. Is > this > > not possible or am I doing something else wrong ? > > when I hit that issue in 8.4 i used a plpgsql function > > ... do instead select update_rule_func(old,new); > > -- > ⚂⚃ 100% natural > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Update rule on a view - what am I doing wrong
Hello, I have been fighting a problem with an update rule on a view. I have a view that combines two tables where the 'sub' table (scont) can have several rows per row in the 'top' table (icont). The view combines these to show only one record per row in the top table. To be able to update on this view I have created a rule 'on update'. The rule needs to have both UPDATE, DELETE, and INSERT commands. Is this not possible or am I doing something else wrong ? In the included sql script I have tried to show the problem. When the final update statement is executed, I get a 'duplicate key violation' on a record that has never been there. I am using PostgreSQL version 9.1.7 (running on Linux/Ubuntu 12.04). What am I doing wrong ? Please help, Leif DROP VIEW hcont; DROP TABLE icont; CREATE TABLE icont ( cid INTEGER, sid INTEGER, rt INTEGER ); ALTER TABLE icont ADD PRIMARY KEY ( cid, sid ); DROP TABLE scont; CREATE TABLE scont ( cid INTEGER, sid INTEGER, vno INTEGER, val INTEGER ); ALTER TABLE scont ADD PRIMARY KEY ( cid, sid, vno ); CREATE VIEW hcont AS SELECT ic.cid AS oid, ic.cid, ic.sid, ic.rt, s1.val AS value1, s2.val AS value2 FROM icont ic LEFT OUTER JOIN scont s1 ON s1.cid = ic.cid AND s1.sid = ic.sid and s1.vno = 1 LEFT OUTER JOIN scont s2 ON s2.cid = ic.cid AND s2.sid = ic.sid and s2.vno = 2 ; CREATE OR REPLACE RULE hcont_udpate AS ON UPDATE TO hcont DO INSTEAD ( UPDATE icont SET rt = NEW.rt WHERE cid = NEW.cid AND sid = NEW.sid; DELETE FROM scont WHERE cid = NEW.cid AND sid = NEW.sid; INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 1, NEW.value1 ); INSERT INTO scont VALUES ( NEW.cid, NEW.sid, 2, NEW.value2 ); ); -- Populate the tables INSERT INTO icont VALUES ( 1, 1, 20 ); INSERT INTO icont VALUES ( 1, 2, 40 ); INSERT INTO icont VALUES ( 1, 3, 60 ); INSERT INTO scont VALUES ( 1, 1, 1, 3 ); INSERT INTO scont VALUES ( 1, 1, 2, 5 ); SELECT * from scont; UPDATE hcont SET cid = 1, sid = 2, rt = 80, value1 = 7, value2 = 9 WHERE cid = 1; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
PS.: That goes for the AT clause on the GET DESCRIPTOR statement too. The connection name is not included in the ECPGget_desc() call. - "Leif Jensen" wrote: > Hello Bosco, > >Thank you for your comment. Yes, it would be nice to get some more > comments on the allocate/deallocate on a connection issue. > >I have verified that in my case deallocating a prepared statement, > it guesses the wrong connection and returns an error. (The right one > is doing auto-deallocation at disconnect time, though). > >However, I just noticed that allocating a descriptor with the "AT > " clause, > EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname; > generates an ECPGallocate_desc() call without any connection name and > that this can "screw up" the ECPGget_desc() function when guessing a > connection. I could of course use: > EXEC SQL SET CONNECTION ; > before the allocate, but that would need mutex's all over to make sure > that other threads will not set the connection too. > >Any idea why the ecpg pre-compiler doesn't use the named connection > for the ALLOCATE DESCRIPTOR statement even though it allows it ? > >Please help, > > Leif > > > - "Bosco Rama" wrote: > > > Leif Jensen wrote: > > > > > > Is it really not possible to use 2 separate connection within 1 > > thread > > > at the same time ? or is it an error in the ecpg library ? > > > > It should be entirely possible to run multiple connections in a > > single > > thread as long as you manage the 'AT connName' clauses properly. > > > > Though, IIRC, using an 'AT connName' clause on any sort of > > 'deallocate' > > statement generates an error in ecpg: > > > > ecpg -o test.c test.pgc > > test.pgc:35: ERROR: AT option not allowed in DEALLOCATE statement > > > > This happens when trying to deallocate a query or a prepared > > statement. > > I don't use descriptors but the error message indicates it's _any_ > > sort > > of deallocate. > > > > So, it would appear that you can allocate on a connection but not > > deallocate from one. :-( > > > > I'm wonder if Tom or Michael can shine some light on this one? > > > > Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
Hello Bosco, Thank you for your comment. Yes, it would be nice to get some more comments on the allocate/deallocate on a connection issue. I have verified that in my case deallocating a prepared statement, it guesses the wrong connection and returns an error. (The right one is doing auto-deallocation at disconnect time, though). However, I just noticed that allocating a descriptor with the "AT " clause, EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :descname; generates an ECPGallocate_desc() call without any connection name and that this can "screw up" the ECPGget_desc() function when guessing a connection. I could of course use: EXEC SQL SET CONNECTION ; before the allocate, but that would need mutex's all over to make sure that other threads will not set the connection too. Any idea why the ecpg pre-compiler doesn't use the named connection for the ALLOCATE DESCRIPTOR statement even though it allows it ? Please help, Leif - "Bosco Rama" wrote: > Leif Jensen wrote: > > > > Is it really not possible to use 2 separate connection within 1 > thread > > at the same time ? or is it an error in the ecpg library ? > > It should be entirely possible to run multiple connections in a > single > thread as long as you manage the 'AT connName' clauses properly. > > Though, IIRC, using an 'AT connName' clause on any sort of > 'deallocate' > statement generates an error in ecpg: > > ecpg -o test.c test.pgc > test.pgc:35: ERROR: AT option not allowed in DEALLOCATE statement > > This happens when trying to deallocate a query or a prepared > statement. > I don't use descriptors but the error message indicates it's _any_ > sort > of deallocate. > > So, it would appear that you can allocate on a connection but not > deallocate from one. :-( > > I'm wonder if Tom or Michael can shine some light on this one? > > Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
Hello, Thank you for the suggestion, seems the way to go. I have implemented this using both variable descriptor and prepared statement (execquery) in my program and it works nicely, except in one specific situation. What I didn't mention previously is that we are sometimes using 2 connections in the same thread: 1 for reading some tables (doing SELECT), and 1 for writing other tables (doing INSERTs/UPDATEs) for each record from the first, after some complex operations on the data. In this case when I deallocate the execquery (and descriptor) I get an error from the ecpg lib saying: -230:26000 invalid statement name Debugging into the ecpglib, I see that when 'get_connection()' is called (from ECPGdeallocate()) with NULL as parameter, it returns the wrong connection and then uses this and the query name in a call to 'find_prepared_statement()' which of course doesn't find any because of the mismatch of name and connection, hence the error message. Is it really not possible to use 2 separate connection within 1 thread at the same time ? or is it an error in the ecpg library ? Please help, Leif - "Bosco Rama" wrote: > Leif Jensen wrote: > > > > This seems to be working most of the time, but looking at the > generated C > > code from the ecpg compiler and the associated library functions, we > are > > not sure whether we should put mutex locks around the 'select' part > to > > avoid several threads are using "the same" execdesc at the same > time. > > > > We have made sure that each thread uses their own and only their > own > > database connection, but are unsure whether the ecpg library > functions is > > able to handle multiple use of the statical name "execdesc" ? > > You are most probably trashing memory by using the same descriptor > name in > multiple threads. However, given that you have already spent the > effort to > have the connections 'thread-dedicated' I think that rather than > creating a > critical path through an area that is intentionally supposed to be > mutli- > hreaded, I'd be inclined to use the connection name (or some > derivation of > it) as the name of the descriptor. I haven't used descriptors in ecpg > so I > don't know if the syntax works, but you could try: > > exec sql char *dname = _thisDbConn; // Or some derivation > > EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR :dname; > ... > EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR > :dname; > ... > EXEC SQL DEALLOCATE DESCRIPTOR :dname; > > > Just a thought. > > Bosco. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 8.3.5 - ECPG and the use of descriptors and cursors in multi-threaded programs
Hello Guys,
In a multi-threaded server program using Postgresql 8.3.5 with ECPG
interface for C, we have problems using descriptors (and possibly cursors).
We have created a common database interface module with basically 1
function: SQLExec(). In the 'select' part of this function we (statically)
allocate a descriptor as shown below.
This seems to be working most of the time, but looking at the generated C
code from the ecpg compiler and the associated library functions, we are not
sure whether we should put mutex locks around the 'select' part to avoid
several threads are using "the same" execdesc at the same time.
We have made sure that each thread uses their own and only their own
database connection, but are unsure whether the ecpg library functions is able
to handle multiple use of the statical name "execdesc" ?
static int SQLExec( const char *thisDbConn, char *paramStmt )
{
EXEC SQL BEGIN DECLARE SECTION;
const char *_thisDbConn = thisDbConn;
char *stmt = paramStmt;
EXEC SQL END DECLARE SECTION;
.
.
if( "select" ) {
.
.
EXEC SQL AT :_thisDbConn ALLOCATE DESCRIPTOR execdesc; line = __LINE__;
EXEC SQL AT :_thisDbConn PREPARE execquery FROM :stmt; line = __LINE__;
EXEC SQL AT :_thisDbConn DECLARE execcurs CURSOR FOR execquery; line =
__LINE__;
EXEC SQL AT :_thisDbConn OPEN execcurs; line = __LINE__;
while( ok ) {
EXEC SQL AT :_thisDbConn FETCH IN execcurs INTO SQL DESCRIPTOR execdesc;
.
(handle data per row, using execdesc)
.
}
.
(deallocation of stuff)
.
}
}
We experience spurious crashes with SIGSEGV and tracebacks of the core dump
usually ends within some ecpg library function, hence this question.
Please help,
Leif
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ECPG selecting into char arrays
Hi guys, In version 8.2 of the ECPG documentation, section "31.6.3. Different types of host variables" has been added and includes the following (new) note: 'Note that you have to take care of the length for yourself. If you use this host variable as the target variable of a query which returns a string with more than 49 characters, a buffer overflow occurs.' This raises a big question about how to handle fields of type 'text' !? I am pretty sure that in pre-8.0 versions of PostgreSQL ECPG, the arrays were handled in a way that made sure not to write more than the space allowed (why else have the array size and type size included as parameters in the ECPG generated output call to ECPGdo(...) ?). Is this a new "feature" ? With that note in mind, how do I select from a table field of type 'text' ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help on explain analyze
Hi guys,
I have a rather complex view that sometimes takes an awful long time to
execute. I have tried to do an 'explain analyze' on it. My intention was to try
to optimize the tables involved by creating some indexes to help the lookup. I
looked for the "Seq Scan's and created appropriate indexes, I thought. However,
in most cases the search got even slower. I have "expanded" the view as follows:
cims=# explain analyze select * from (SELECT t.id AS oid, d.id AS devid,
d.description AS devname, cd.value AS period, upper(dt.typename::text) AS
devtype, (date_part('epoch'::text, timezone('GMT'::text, t.firstrun))::bigint -
(z.timezone::integer -
CASE
WHEN z.daylightsaving <> 'Y'::bpchar THEN 0
ELSE
CASE
WHEN cy.dl_start < now() AND now() < cy.dl_finish THEN 1
ELSE 0
END
END) * 3600) % 86400::bigint AS firstrun, t."interval", t.id AS tid,
ti.id AS tiid, t.runstatus, t.last, tt.handler, td.value AS ctrlid, td.string
AS alarm, z.timezone AS real_timezone, cy.dl_start < now() AND now() <
cy.dl_finish AS daylight, z.timezone::integer -
CASE
WHEN z.daylightsaving <> 'Y'::bpchar THEN 0
ELSE
CASE
WHEN cy.dl_start < now() AND now() < cy.dl_finish THEN 1
ELSE 0
END
END AS timezone
FROM device d
LEFT JOIN task_info ti ON ti.ctrlid = d.id
LEFT JOIN task t ON t.id = ti.taskid
LEFT JOIN ctrl_definitions cd ON d.id = cd.ctrlid AND cd.name::text =
'IrrPeriodStart'::text, task_type tt, task_definitions td, devtype dt,
ctrl_definitions cd2, zip z, county cy
WHERE td.name = 'UseWSData'::text AND ti.id = td.taskinfoid AND d.devtypeid =
dt.id AND tt.id = t.tasktypeid AND (tt.handler = 'modthcswi.so'::text OR
tt.handler = 'modthcswb.so'::text) AND btrim(cd2.string) = z.zip::text AND
cd2.ctrlid = td.value AND cd2.name::text = 'ZIP'::text AND z.countyfips =
cy.countyfips AND z.state = cy.state AND date_part('year'::text, now()) =
date_part('year'::text, cy.dl_start)) AS wstaskdist
WHERE wstaskdist.ctrlid = 401 AND CAST( alarm AS boolean ) = 't';
The view is actually the sub-SELECT which I have name 'wstaskdist', and my
search criteria is the bottom WHERE. The result of the ANALYZE is:
QUERY PLAN
--
Nested Loop (cost=284.88..9767.82 rows=1 width=109) (actual
time=2515.318..40073.432 rows=10 loops=1)
-> Nested Loop (cost=284.88..9745.05 rows=70 width=102) (actual
time=2515.184..40071.697 rows=10 loops=1)
-> Nested Loop (cost=229.56..5692.38 rows=1 width=88) (actual
time=2512.044..39401.729 rows=10 loops=1)
-> Nested Loop (cost=229.56..5692.07 rows=1 width=80) (actual
time=2511.999..39401.291 rows=10 loops=1)
-> Nested Loop (cost=229.56..5691.76 rows=1 width=77)
(actual time=2511.943..39400.680 rows=10 loops=1)
Join Filter: (ti.id = td.taskinfoid)
-> Seq Scan on task_definitions td
(cost=0.00..13.68 rows=1 width=22) (actual time=0.204..0.322 rows=10 loops=1)
Filter: ((name = 'UseWSData'::text) AND (value
= 401) AND (string)::boolean)
-> Hash Left Join (cost=229.56..5672.72 rows=429
width=59) (actual time=7.159..3939.536 rows=429 loops=10)
Hash Cond: (d.id = cd.ctrlid)
-> Nested Loop (cost=24.66..5442.80 rows=429
width=55) (actual time=6.797..3937.349 rows=429 loops=10)
-> Hash Join (cost=16.65..282.84
rows=429 width=38) (actual time=0.078..6.587 rows=429 loops=10)
Hash Cond: (t.id = ti.taskid)
-> Seq Scan on task t
(cost=0.00..260.29 rows=429 width=30) (actual time=0.022..5.089 rows=429
loops=10)
-> Hash (cost=11.29..11.29
rows=429 width=12) (actual time=0.514..0.514 rows=429 loops=1)
-> Seq Scan on task_info ti
(cost=0.00..11.29 rows=429 width=12) (actual time=0.020..0.302 rows=429
loops=1)
-> Bitmap Heap Scan on device d
(cost=8.01..12.02 rows=1 width=21) (actual time=9.145..9.146 rows=1 loops=4290)
Recheck Cond: (d.id = ti.ctrlid)
-> Bitmap Index Scan on pk_device
(cost=0.00..8.01 rows=1 width=0) (actual time=0.463..0.463 rows=1569
loops=4290)
[GENERAL] ECPG & threads
Hello guys,
PostgreSQL 8.3.5. Compiled and installed on Slackware 10.2, kernel 2.6.15.
We have a transaction based system that creates a new process every time a
user connects (just fork()s, no exec()s). Each of these processes has several
threads. Some database connections are opened before the threads are created
(this is part of the basic design of the system), but each tread only uses one
specific connection assigned to it, no "cross" connection use.
We are using Embedded SQL in C mainly located in 1 source module, accessed
from all threads using function calls. Each thread is of course passing its
allocated connection name in these calls.
However, we have had some problems of database calls locking up or crashing
the program. In the investigation of one of these crashes we had to dig deep
into the source of the ecpg library. In doing this we have been wondering why
the descriptors are not connection specific when most other db accesses are. It
seems that if the db connection is not opened within the thread itself, the
descriptor to be used is randomly chosen among opened connections. Specifically
we are wondering about the reason for the descriptor name to be statically
assigned at compile time (fixed text) and not a variable that could then be
given a connection related name.
One strange thing about the behaviour of our program is that it "seems" to
run ok on some machines but crashes reproducible on others. All these systems
are having the same hardware (HP based PCs) with exactly the same Linux and
PostgreSQL installation.
To try to visualise our problem, I have included some pseudo code of our
program flow below.
What we would like to know is the philosophy of not having descriptors being
connection specific and whether this will change or not.
Leif
DB access module:
.
Db_Open( char *conn )
{
EXEC SQL EXEC SQL BEGIN DECLARE SECTION;
const char *dbname = _dbname;
const char *dbuser = _dbuser;
const char *_thisDbConn = conn;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :dbname AS :_thisDbConn USER :dbuser;
if( sqlca.sqlcode ) {
return( SQLErr_NotOpen );
}
EXEC SQL AT :_thisDbConn SET AUTOCOMMIT TO OFF;
if( sqlca.sqlcode ) {
return( SQLErr_NotOpen );
}
return( SQLErr_Ok );
}
Db_SQLExec( char *conn, char *stmt )
{
.
EXEC SQL ALLOCATE DESCRIPTOR execdesc;
.
.
EXEC SQL DEALLOCATE DESCRIPTOR execdesc;
.
}
Main source module:
pthread_t thA = 0;
pthread_t thB = 0;
main()
{
.
char *connA = "connA";
char *connB = "connB";
Db_Open( connA );
Db_Open( connB );
.
.
rval = pthread_create( &thA, NULL, funcA, NULL );
.
rval = pthread_create( &thB, NULL, funcB, NULL );
.
Db_Close( connA );
Db_Close( connB );
}
Various modules:
funcA( ... )
{
.
// Many calls like:
Db_SQLExec( "connA", );
.
}
funcB( ... )
{
.
// Many calls like:
Db_SQLExec( "connB", );
.
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Open Source references
Hej Guys, I'm looking for design ideas (and implementation specifics, not just tutorials) to a transaction "heavy" multi-process, multi-threaded system using PostgreSQL. Are there some good references to existing open source systems for me to see how other people has solved that kind of problems ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Process- or SessionID with ECPG
Hi guys, I have an ECPG based program and I would very much like to get either the process id of the backend or the session id for debugging purposes. Is there a way to call PQbackendPID() directly from my program (I suppose that will require getting hold of the PGconn from "within" ECPG?) or some other way to interrogate the ECPG system ? My program is multi-threaded. Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slave server: FATAL: incorrect checksum in control file
That is almost too simple ;-) Thanks for the suggestion, Leif - "Christopher Browne" wrote: > On Fri, Jan 16, 2009 at 9:18 PM, Leif Jensen > wrote: > > You are perfectly right, master is 32bit and slave is 64bit. I > didn't even consider that that would matter when "just" copying the > data. First I was using different versions on the two boxes, but ended > up installing 8.3.5 on both of them. > > > > How do I install a 32bit PostgreSql on my 64bit (linux) box ? > > Copy the 32 bit binaries from a 32 bit box. You'd be surprised how > well this works! > > I once had the disconcerting realization that Someone Had Installed > Our RedHat IA-32 build on an AMD-64 (Opteron) system. > > Nobody noticed until I was trying to add a C-based stored function, > and discovered, to my horror, that the binaries that had been running > fine (including Slony-I replication!) were for the wrong architecture > and a different flavour of Linux (compiled for Red Hat, running on > SuSE). I couldn't believe it had worked :-). > -- > http://linuxfinances.info/info/linuxdistributions.html > Bob Hope - "I grew up with six brothers. That's how I learned to > dance - waiting for the bathroom." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slave server: FATAL: incorrect checksum in control file
You are perfectly right, master is 32bit and slave is 64bit. I didn't even consider that that would matter when "just" copying the data. First I was using different versions on the two boxes, but ended up installing 8.3.5 on both of them. How do I install a 32bit PostgreSql on my 64bit (linux) box ? Leif - "Tom Lane" wrote: > Leif Jensen writes: > >So far I don't get any errors, but when I start postgres on the > slave (I'm using pg_ctl), I get the error 'FATAL: incorrect checksum > in control file'. > > >Both servers are running PostgreSQL-8.3.5, configured with > exactly the same options (just prefix and ssl). > > Maybe they aren't the same architecture? (32 vs 64 bit for instance) > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Slave server: FATAL: incorrect checksum in control file
Hi Guys, I'm trying to set up a warm standby server, but have problems with running it on the backup. I feel that I have done like the documentation says: The WAL is being copied to the slave using rsync. Doing SELECT pg_start_backup(); (in psql) Copying the data directory to the slave (using rsync) Doing SELECT pg_stop_backup(); (in psql) Making sure the slave is using it's own postgresql.conf and the recovery.conf is set with the pg_standby command. . So far I don't get any errors, but when I start postgres on the slave (I'm using pg_ctl), I get the error 'FATAL: incorrect checksum in control file'. Both servers are running PostgreSQL-8.3.5, configured with exactly the same options (just prefix and ssl). Any ideas ? Greetings, Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please Help! Was: Multithread problem: Error in transaction processing
Hi, I will have to try again. I know that I was probably not specific enough in my first attempt. The systems on which we have tested and are getting the error: - PostgreSQL 8.2.4 (also tried 8.2.6 without db upgrade) configured with 'enable_thread_safety'. - Linux Slackware 10.2.0 both kernel 2.4.31 and kernel 2.6.15, and Linux Slackware 9.1.0 with kernel 2.4.14. The client (our daemon) has both been compiled based on 8.2.4 and 8.2.6 but seems not to make any difference. Our daemon creates a couple of threads doing database access on each their (named) connection, where one of these threads will receive UDP packages rather often with data to the database Therefore we open the database at the thread start and keep it open all through the execution time (which can be months). When we get a package it results in a database update (1 row updated for each package), and here we often gets the -401 error. Another thread is a 'timer' thread running every 30 seconds checking the database for certain changes (read only). This also gets the error once in a while. I have not made any statistics about how often, but it's more like in 5% of the attempts than 0.1%. In which situations is it possible to get this error? I have of course looked in the documentation, but it is not very specific, just that you can get it during Start Transaction (which is where we see it), Rollback, or Commit. What can be the basic cause of getting the error? How do I dig into getting it resolved? Can someone please point me to some documentation/description/examples on how to access the database in multiple threads. (We have of course looked in the PostgreSQL manual and searched the net. That is why we are using one connection per thread.) As a workaround we close the database and reopen it when we get this error. Is this a normal/bug situation ? Any suggestions will be most welcome, Leif - Original Message - From: "Leif Jensen" <[EMAIL PROTECTED]> To: "Leif Jensen" <[EMAIL PROTECTED]> Sent: Friday, March 14, 2008 9:44:27 AM GMT +01:00 Amsterdam / Berlin / Bern / Rome / Stockholm / Vienna Subject: [GENERAL] Multithread problem: Error in transaction processing Hi Guys, We have a system with a multithreaded daemon running on Linux using PostgreSQL 8.2.4 Embedded SQL in C. Most of the threads are doing database access and they have each their own conection which is opened when the thread is created (as one of the first things within the thread). We pretty often gets the error: -401:08007 Error in transaction processing in line 200 which is where there is an EXEC SQL AT :xxx START TRANSACTION ... I have checked and re-checked the code for non- coomit/rollback and added lots of test output to verify that every transaction is either committed or rolled back. I have also made sure that there is no cross-thread use of connection, especially looking at the test output. Any ideas ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multithread problem: Error in transaction processing
Hi Guys, We have a system with a multithreaded daemon running on Linux using PostgreSQL 8.2.4 Embedded SQL in C. Most of the threads are doing database access and they have each their own conection which is opened when the thread is created (as one of the first things within the thread). We pretty often gets the error: -401:08007 Error in transaction processing in line 200 which is where there is an EXEC SQL AT :xxx START TRANSACTION ... I have checked and re-checked the code for non- coomit/rollback and added lots of test output to verify that every transaction is either committed or rolled back. I have also made sure that there is no cross-thread use of connection, especially looking at the test output. Any ideas ? Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best open source tool for database design / ERDs?
Hi, tedia2sql will do the job: http://tedia2sql.tigris.org Greetings, Leif On Thu, 1 Jun 2006 [EMAIL PROTECTED] wrote: > Anyone know if DIA will generate CREATE TABLE statements from an ER > diagram? > > I'd like to have a program where I can create my db design, then be > able to instantiate the design in PostgreSQL as well as MySQL. > > I'll pay for a good commercial tool if it costs less than USD $100. > > Thanks. > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
Hello, I have with great interrest been following this thread. We have a (small) flame war in house about this and I'm very happy about all the arguments I have seen. I'm a long time user of PostgreSQL (which possibly makes me a bit biased ;-) ) and I think it's great. I'm not a big database expert, but I try to make things as good and standard as I can. In this respect I have 3 questions: 1) I wonder that no one has mentioned anything about security issues in those two. I know that I'm a novice and that I didn't use MySql very much, but it seems to me that the PostgreSQL security is much better than MySql !? 2) I don't know the latest SQL standard (I did say I'm a novice), but how close to some standard is embedded SQL in C in PostgreSQL, Oracle, and MySql ? 3) We are using mambo (the homepage management system), which is based on MySql. I would love to make it use PostgreSQL instead (to ease maintenance, backup, administration, etc.) Does anyone know where to get help/info on this ? Thanks for a good product and a good discusion, Leif On Wed, 22 Mar 2006, Jimbo1 wrote: > Hello there, > > I'm a freelance Oracle Developer by trade (can almost hear the boos now > ;o)), and am looking into developing my own Snowboarding-related > website over the next few years. Anyway, I'm making some decisions now > about the site architecture, and the database I'm going to need is > obviously included. If my site works out, I'm expecting reasonably > heavy traffic, so want a database that I'm confident can cope with it. > > It is out of the question for me to use Oracle, although I am a > (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper > route, and to that end I'm looking at either MySQL or PostgreSQL. > > Regarding MySQL, I've been put off by Oracle's recent purchase of > InnoDB and realise this could badly impact the latest version of the > MySQL database. I can almost hear Larry Ellison's laughter from here > (allegedly)! I've also been put off by the heavy marketing propaganda > on the MySQL website. > > Recently, I've been taking a look at PostgreSQL, and am very impressed > by what I've read, although I've not yet investigated the database > first-hand. To cut to the chase, I would be interested in anybody's > feedback on the advantages that PostgreSQL has over MySQL. > > Also, I've recently read the "Inside MySQL 5.0" (marketing propaganda) > document, and it makes the following claim: > > "With MySQL, customers across all industries are finding they can > easily handle nearly every type of database workload, with performance > and scalability outpacing every other open source rival. As Los Alamos > lab (who uses MySQL to manage their terabyte data warehouse) said, "We > chose MySQL over PostgreSQL primarily because it scales better and has > embedded replication.".". > > If any PostgreSQL devotees on this group can comment on the above and > its accuracy/inaccuracy, I'd really appreciate it. > > Thanks in advance. > > James > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ftp download won't work
Hi all, I have been using PostgreSQL for many years now and have never had any problems downloading it. The last one I got hold of is 8.0.3, but now when I try to get 8.1.2, I can get nothing to work. I am behind a rather strict firewall, it has been like this for years. I checked with the system guys and they claim that nothing has changed regarding ftp. They say our firewall is set up for standard ftp transfer (using contracker and non-passv mode only). It is also blocking most in- and out- going non-priviledged ports. I can do ftp download from other sites without problems. I have also tried bittorrent, but I'm very new to that (never done that before) and couldn't make it work either. Is there any chance of finding somewhere to do an http download ? Greetings, Leif, a happy PostgreSQL'er ---(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] Mambo (CMS) & PostgreSQL
Hi Peter, Good to hear, and now the real question(s): What version of Mambo (and PostgreSQL) are you using and did you have to make many patches? How about 'standard' plugins (as well as 3rd party plugins), will they work without problems ? Thank you for you patience, Leif On Sun, 20 Nov 2005, Peter Eisentraut wrote: > Leif Jensen wrote: > >Anyone using the CMS called Mambo with PostgreSQL ? > > Yes. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > > ---(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 > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Mambo (CMS) & PostgreSQL
Hi all, Anyone using the CMS called Mambo with PostgreSQL ? Leif ---(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] Mambo/Joomla (CMS) on PostgreSQL ?
Hi All, I have been using PostgreSQL ever since version 6.3 and are very happy about it. Many of our company corporate registration systems are based on PostgreSQL and are web based using PHP. Unfortunately someone has decided that our (external) homepage(s) are gonna use the Mambo CMS system which is originated on MySql. We are now running it successfully for our new homepages (all on the outside of our firewall, for now). This makes things a bit akward, especially if/when we want to make some of our internal stuff accessible to the outside world. We do NOT want to maintain/administer 2 DBMS'es for many reasons, e.g. security. I am using some specific stored procedures in my internal systems, but those part will probably never have to be "exported". I wonder if someone in the PostgreSQL community already successfully have been using Mambo or Joomla based on PostgreSQL. The concern here is for keeping things standard (no or at least very few local patches to make things work) as well as the concern for 3rd party plugins/modules to work. We are at the moment using mambo version 4.5.x and PostgreSQL 7.4.5, but an upgrade to 8.0.x would be ok I think. I have seen that Mambo version 5.x is supposed to have support for other databases, but I haven't been able to see any reports on that yet. But the fork of Mambo/Joomla makes this more difficult to get a decision on moving to Mambo 5, I'm afraid. There are also a time issue for me, since we have to put up some other services apart from mambo before the end of the year. I am looking for arguments in terms of "why are PostgreSQL a better choice for security reasons, standards (SQLXX standard) reasons, DBMS handling, and ease of use with Mambo etc." Why do we have to get Mambo running on PostgreSQL in stead of MySql ? I would just hate to have to abandone PostgreSQL in favour of MySql. Does anyone have some good links to comparisons of MySql and PostgreSQL? I'm not too much concerned about performance (both are pretty good for our purposes), commitment to standards are much more of a concern. Any comments are very welcome. It could be some rather small thing that will "tip the cup". Greetings, Leif ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Recursive stored procedure in C.
Hi again, Thanks. No changes on this 7.x.x -> 8.x.x ? .. and just to be sure: SPI_exec does the SPI_push/SPI_pop thing too ? Leif On Thu, 14 Jul 2005, Tom Lane wrote: > Leif Jensen <[EMAIL PROTECTED]> writes: > > Thank you for the suggestions. I didn't know anything about SPI_push and > > SPI_pop and I will walk through my code and stuff them in. Am I looking at > > some wrong documentation ? I never saw anything about those ? > > They weren't well documented in old releases, but see > http://www.postgresql.org/docs/8.0/static/spi-spi-push.html > http://www.postgresql.org/docs/8.0/static/spi-spi-pop.html > > regards, tom lane > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Recursive stored procedure in C.
Hi Tom, Thank you for the suggestions. I didn't know anything about SPI_push and SPI_pop and I will walk through my code and stuff them in. Am I looking at some wrong documentation ? I never saw anything about those ? Do you know any good examples doing such things ? Leif On Thu, 14 Jul 2005, Tom Lane wrote: > Leif Jensen <[EMAIL PROTECTED]> writes: > >I am trying to make a stored procedure in C that is used as a trigger > > on before/after insert/update on a certain table. This procedure might do > > inserts/updates on the same table (recursively triggering itself). I have > > made (pretty) sure that I'm not using 'global' variables in this module > > and that I do an SPI_connect() the very first time I enter and an > > SPI_finish() at the last exit. > > If you want the trigger levels to be independent (which I think you do) Yes. > then you need a SPI_connect and a SPI_finish in *each* trigger call. > Whatever magic you are doing to special-case recursion is all wrong and > should be ripped out, root and branch. Ok, will do. > > The bit you may be missing is that you need to do SPI_push and SPI_pop > around anything that might possibly call another function using SPI (eg, > your own recursive instance, but also anything else that might use SPI). > SPI_execute() and friends do this for you, but if the recursion is not SPI_exec() ? > via a SPI-executed query then you need to do it explicitly. > > regards, tom lane > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Recursive stored procedure in C.
Oops, I forgot to say that I have tried PostgreSQL 7.4.1, 7.4.6, and
7.4.7 all with the same result. I'm running this on a Linux (Slackware
10.0), kernel 2.6.10y.
Leif
On Thu, 14 Jul 2005, Leif Jensen wrote:
>
> Hi all,
>
>I am trying to make a stored procedure in C that is used as a trigger
> on before/after insert/update on a certain table. This procedure might do
> inserts/updates on the same table (recursively triggering itself). I have
> made (pretty) sure that I'm not using 'global' variables in this module
> and that I do an SPI_connect() the very first time I enter and an
> SPI_finish() at the last exit.
>
>This works to some point, but in some instances I get
> SPI_ERROR_UNCONNECTED from an SPI_exec() call a few recursive layers down,
> e.g. the following code yields:
>
> ret = SPI_exec( sql_query, 0 ); => SPI_ERROR_UNCONNECTED
> if( ret == SPI_ERROR_UNCONNECTED ) {
> ret = SPI_finish();=> SPI_ERROR_UNCONNECTED
> ret = SPI_connect(); => SPI_ERROR_CONNECT
> ret = SPI_exec( sql_query, 0 );=> SPI_ERROR_UNCONNECTED
> }
>
> ??? (This 'if' was mainly to try to figure out what was going on, since
> I should not do an SPI_finish() at all until the very end.)
>
>Is such recursivity at all possible ? What are the pitfalls ? Any good
> examples out there?
>
>Any suggestions at all will be much appreciated,
>
> Leif
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] Recursive stored procedure in C.
Hi all,
I am trying to make a stored procedure in C that is used as a trigger
on before/after insert/update on a certain table. This procedure might do
inserts/updates on the same table (recursively triggering itself). I have
made (pretty) sure that I'm not using 'global' variables in this module
and that I do an SPI_connect() the very first time I enter and an
SPI_finish() at the last exit.
This works to some point, but in some instances I get
SPI_ERROR_UNCONNECTED from an SPI_exec() call a few recursive layers down,
e.g. the following code yields:
ret = SPI_exec( sql_query, 0 ); => SPI_ERROR_UNCONNECTED
if( ret == SPI_ERROR_UNCONNECTED ) {
ret = SPI_finish();=> SPI_ERROR_UNCONNECTED
ret = SPI_connect(); => SPI_ERROR_CONNECT
ret = SPI_exec( sql_query, 0 );=> SPI_ERROR_UNCONNECTED
}
??? (This 'if' was mainly to try to figure out what was going on, since
I should not do an SPI_finish() at all until the very end.)
Is such recursivity at all possible ? What are the pitfalls ? Any good
examples out there?
Any suggestions at all will be much appreciated,
Leif
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Newbie question on RULEs .. or .. bug ?
Hello Tom, Thank you for the enlightment, I think I understand what you say. There are however a few things I'm not sure about still. The update seems to work as I would expect when I include one or more in there where clause from the primary key. If I have a field not in the primary key included in the where, I don't get anything updated, e.g. update tasks set seq = 2 where id = 87 and name = '2WWE'; does nothing !?? Two questions: 1) Is there any way to define a view like this where I can insert and update without these rules, just as if it was one real table ? 2) If I need to use rules to do update/insert on tasks, how can I make it 'transparent' as in the above example (the update that does nothing) ? Greetings, Leif On Tue, 17 May 2005, Tom Lane wrote: > Leif Jensen <[EMAIL PROTECTED]> writes: > > CREATE RULE update_tasks2taskshead AS > > ON UPDATE TO tasks WHERE NEW.seq = 0 > > DO NOTHING > > ; > > That rule looks a bit useless ... Yeah, just disabled for now ;-) > > > CREATE RULE update_tasks2ganntinfo AS > > ON UPDATE TO tasks > > DO INSTEAD ( > > update ganntinfo set > > id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name > > -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category > > ; > > ) > > ; > > You definitely need a WHERE clause in that rule; otherwise you get > exactly the result you saw: all rows of ganntinfo are updated. The > comment in the manual about the original WHERE clause really means > that the values of "NEW" will be constrained to take on only the > values determined by the original WHERE. Your update is basically a join > of ganntinfo with the subset of the tasks view determined by the > original WHERE --- so you have to constrain ganntinfo too. I suppose > that you want something like > > update ganntinfo set > category = NEW.category, name = NEW.name > WHERE id = NEW.id AND seq = NEW.seq > ; > > since id/seq is your primary key for ganntinfo. > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Newbie question on RULEs .. or .. bug ?
Hello, I have been working with a great database system called PostgreSQL for many years ;-) but never had to use any RULEs. I now have to use and update through a view and have written a few rules to make this possible as per the manual. My insert rules seems to work fine, but I can't make the update rule do what I want and as I believe stated in the manual. In the attached schema I have 2 basic tables and a view combining those. I have 2 rules for insert on the view and these works fine. The update rule, however, doesn't do what I want. The manual states that the origsal query tree (where) is added to the rule qualification, so I would exspect the update statement below would only update 1 row of the ganntinfo table, but it updates all 5 ? What am I doing wrong ? projtaskdb=# SELECT * FROM ganntinfo ; id | seq | category | name -+-+--+- 46 | 0 | SRC | 2WWE 172 | 0 | SRC | RKD60 138 | 0 | SRC | Diverse 34 | 0 | SRC | VF 87 | 0 | SRC | 2WWE (5 rows) projtaskdb=# UPDATE tasks SET id = 87, category = 'SRC', name = '2WWE', customer = 'Custm', description = 'test' WHERE id = 87 and category = 'SRC'; UPDATE 5 projtaskdb=# SELECT * FROM ganntinfo ; id | seq | category | name +-+--+-- 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE 87 | 0 | SRC | 2WWE (5 rows) projtaskdb=# I tried to put an 'explain' in front of the update within the update rule, but got a syntax error. Why is that ? Please help, Leif SET SESSION AUTHORIZATION 'dba'; CREATE TABLE ganntinfo ( id integer NOT NULL, seq smallint, category character varying(20), name character varying(40) -- PRIMARY KEY( id, seq ) ); CREATE TABLE taskshead ( id integer PRIMARY KEY, category character varying(20), subject character varying(40), customer character varying(40), description character varying(400) ); CREATE VIEW tasks ( id, seq, category, name, subject, customer, description ) AS SELECT g.id, g.seq, g.category, g.name, h.subject, h.customer, h.description FROM taskshead h LEFT OUTER JOIN ganntinfo g USING ( id, category ) -- WHERE g.seq = 0 AND g.id = h.id AND g.category = h.category ; CREATE RULE insert_tasks2taskshead AS ON INSERT TO tasks WHERE NEW.seq = 0 DO ( insert into taskshead values ( NEW.id, NEW.category, NEW.subject, NEW.customer, NEW.description ); ) ; CREATE RULE insert_tasks2ganntinfo AS ON INSERT TO tasks DO INSTEAD ( insert into ganntinfo values ( NEW.id, NEW.seq, NEW.category, NEW.name ); ) ; CREATE RULE update_tasks2taskshead AS ON UPDATE TO tasks WHERE NEW.seq = 0 DO NOTHING ; CREATE RULE update_tasks2ganntinfo AS ON UPDATE TO tasks DO INSTEAD ( update ganntinfo set id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name -- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category ; ) ; ---(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
