Re: [GENERAL] Trigger loop question
Mike Nolan <[EMAIL PROTECTED]> writes: >> Actually, I wasn't thinking very clearly. The easiest way to break >> the loop is to avoid updating the other table when OLD.x = NEW.x >> in the trigger's arguments. The other way requires a rather-redundant >> SELECT to see what is in the other table. > If I have to update the other table for any other purpose as part of > that trigger, or if some other trigger updates that table, couldn't that > result in an infinite loop? Well, I'm assuming that your update logic converges to a fixed state; if it doesn't, seems like you've got problems anyway ... > Here's a really weird question. If in the trigger for table A I have > more than one statement that updates table B, or if more than one trigger > procedure updates table B, does that cause multiple firings of either > before or after update triggers on table B? Yes, and yes. You get one firing per row update event, IIRC, no matter where that update came from. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Trigger loop question
> Actually, I wasn't thinking very clearly. The easiest way to break > the loop is to avoid updating the other table when OLD.x = NEW.x > in the trigger's arguments. The other way requires a rather-redundant > SELECT to see what is in the other table. If I have to update the other table for any other purpose as part of that trigger, or if some other trigger updates that table, couldn't that result in an infinite loop? It seems like the select-and-check method, even though it may be redundant most of the time, is the belt-and-suspenders way of avoiding an infinite loop. Here's a really weird question. If in the trigger for table A I have more than one statement that updates table B, or if more than one trigger procedure updates table B, does that cause multiple firings of either before or after update triggers on table B? -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trigger loop question
Mike Nolan <[EMAIL PROTECTED]> writes: > Yes it does. OK, that means Tom's original suggestion of checking > the other table for the same value before updating it should prevent > an infinite loop, providing that's done from a pair of 'after update' > triggers, using the NEW.column entries in the triggered table to update > the other table. Actually, I wasn't thinking very clearly. The easiest way to break the loop is to avoid updating the other table when OLD.x = NEW.x in the trigger's arguments. The other way requires a rather-redundant SELECT to see what is in the other table. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Postmaster won't run as service on Cygwin
Update 2: OK, I've got it narrowed down even further. Don't laugh ! It's the name of the service "postmaster". This line creates a service that won't start. $ cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /cygdrive/i/db_data/pgsql_data -i" --dep ipc-daemon2 --termsig INT --user postgres --shutdown but this line works: $ cygrunsrv --install postmaster5 --path /usr/bin/postmaster --args "-D /cygdrive/i/db_data/pgsql_data -i" --dep ipc-daemon2 --termsig INT --user postgres --shutdown It's not the data directory. Right now I have both services installed, but not started. "postmaster5" can be started but "postmaster" throws error 1067 go figure ... Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Trigger loop question
Mike Nolan wrote: However, if I update table 'B' and the 2nd trigger fires, that trigger will still see the OLD value if does a query on table 'A', since I think transaction atomic rules require that any updated values aren't made available to the outside world (including other triggers) until the transaction is complete. I could be mistaken here, but... I don't believe that is the case. The transaction can see what's going on inside of itself. Everything outside of the transaction typically won't see what is inside the transaction, until it is committed anyway. I tested this, and the 2nd trigger still sees the original value of the field from the first table, which I think is the proper result. I think this has more to do with whether the first trigger was fired BEFORE or AFTER the UPDATE. If the first trigger is fired BEFORE the UPDATE, then the second trigger (fired on the UPDATE) will not see the AFTER values of the first trigger. If you fire the first trigger as AFTER, I bet you'll see the changes. Greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postmaster won't run as service on Cygwin
Update: If I let postgresql put it's data where Cygwin proposes /usr/share/postgresl/data then the service starts as expected. Obviously it's either some hardcoded path in the binaries or I have an permission issue with Windows 2000. I'd bet on the permissions. But if the permissions aren't OK, then how can postmaster work when it is started by hand ? Where is the difference between user postgres starting postmaster as service and doing it on the cygwin-console in regards of using a data path that starts with /cygdrive/... Help ? Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Trigger loop question
> Mike Nolan <[EMAIL PROTECTED]> writes: > > If I set up an on update trigger for table 'A' that updates the > > corresponding column in table 'B', and one for table 'B' that updates > > the corresponding column in table 'A', does that create an endless loop? > > Yes. > > You could break the loop perhaps by not issuing an UPDATE if the data is > already correct in the other table. The trigger on table 'A' is obviously going to see both the old value and the new value for the column. If it queries table 'B', it would see the current value there. However, if I update table 'B' and the 2nd trigger fires, that trigger will still see the OLD value if does a query on table 'A', since I think transaction atomic rules require that any updated values aren't made available to the outside world (including other triggers) until the transaction is complete. I tested this, and the 2nd trigger still sees the original value of the field from the first table, which I think is the proper result. -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] boolean to int
Pavel Stehule wrote: create or replace function int2bool (integer) returns boolean as ' select case when $1=1 then ''t''::boolean else ''f''::boolean end; ' language sql; I'd do it slightly differently, if only to cater to the principle of least surprise: create or replace function int2bool (integer) returns boolean as ' select case when $1=0 then false else true end; ' language sql That way, 0 maps to false, any non-zero value becomes true. create or replace function bool2int (boolean) returns integer as ' select case when $1 then 0 else 1 end; ' language sql; And that's back-to-front ;) create or replace function bool2int (boolean) returns integer as ' select case when $1 then 1 else 0 end; ' language sql Thanks for the example of the use of casts. Alex Satrapa ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PostgeSQL problem (server crashed?)
Edwin Pauli wrote: I've put a strace on my webspace. http://epauli.dyndns.org/strace I've never used the strace command. I see no crazy things in the strace, is that opinion true? -- Edwin Pauli ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Question on Opteron performance
Reece Hart wrote: On Wed, 2004-03-10 at 18:23, William Yu wrote: /At this time, only Newisys offers a Quad Opteron box and it carries a hefty premium. (Sun's upcoming 4X machine is a rebadged Newisys machine and it's possible HP's will be also.)/ There are several vendors with quad opterons out there. Off the top of my head, I know that Aspen, Penguin Computing, Appro, and Polywell all have them. I just googled quad opteron and see that there are bunches of others too. I'm pretty sure most of these guys just rebadge the Newisys box (at this time). ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Postmaster won't run as service on Cygwin
Hi, I installed an postmaster for trainig and developement on my home box. It should run as win 2000 service on cygwin. Cygwin sits in I:\cygwin Data is I:\db_data\psql_data Data folder belongs to the userpostgres.benutzer I (postgres) can start the postmaster on a command line. I can log in and everything seems to be OK. Then I wanted to start it as service and ran as administrator on a cygwin console: cygrunsrv --install postmaster --path /usr/bin/postmaster --args "-D /cygdrive/i/db_data/pgsql_data -i" --dep ipc-daemon2 --termsig INT --user postgres --shutdown postmaster shows up in the service-management-console When I try to mouse-click it, it shows 2 boxes on the progress bar, then stalls and after quite a while it throws Error 1067 Process got unexpectedly stopped "net start postmaster" run as administrative user throws the same. "net start postmaster" run as uster postgres gives System-Error 5 access denied What now ? Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] type definitions
Where in the postgres source code can i find the source that declares all the data types that postgres has? Where does it tell postfgres that these types exist ena d here are there functions? thanks -- Alexander Cohen http://www.toomuchspace.com (819) 348-9237 (819) 432-3443 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Character escape in "CREATE FUNCTION ..."
Tom Lane wrote: Shilong Stanley Yao <[EMAIL PROTECTED]> writes: I am trying to write a function in Postgresql, which takes 2 floats and returns a box. But seems the nested single-quote in the AS clause prevent $1 and $2 from being expanded. Besides writing a C function instead of a SQL one, is there any way to solve this problem? CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box AS 'SELECT box \'(($1, $2), (1.3, 1.4))\'' LANGUAGE 'sql' WITH (ISCACHABLE); This is never going to work because you are trying to use the typed-literal syntax with something that you don't actually want to be a literal constant. You need to think in terms of a function, not a literal. In this case I think what you want is the box-from-two-points constructor function, together with the point-from-two-floats constructor: ... AS 'SELECT box(point($1, $2), point(1.3, 1.4))' If you had a mind to, you could write the constant point as a literal: ... AS 'SELECT box(point($1, $2), point \'1.3, 1.4\')' but you can't write the variable point as a literal. regards, tom lane Thank you very much for this nice solution. It worked very well! BTW, a spatial query involving RTREE indexes showes that SQL function is much slower than C function, which is within the expectation. Thanks everyone of the previous responses for your help too! Stan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgeSQL problem (server crashed?)
Richard Huxton wrote: On Monday 15 March 2004 18:49, Edwin Pauli wrote: There are no Postgres binaries in /usr/local/pgsql, but in /usr/local/bin. Because i've only copied /usr/local/pgsql, the binaries are no changed. If you're binaries work with the old data but not the new, then they are old binaries. You cannot use binaries from different major versions (e.g. 7.2 vs 7.3 vs 7.4). I don't use binaries from 7.2 or 7.3. The tables are created in 7.4 and the binaries are from 7.4. The backup from 3 weeks ago is also from 7.4 Edwin Pauli -- E-mail : [EMAIL PROTECTED] : [EMAIL PROTECTED] Website : http://www.quicksteps.nl/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] boolean to int
Dear Mage , I'm wondering why pgsql doesn't support boolean typecasts like select true::int; Many client applications including php assign 1 to true and 0 to false This was a issue PHP < 4.2 + < PostgreSQL 7.3.x and supports it till now for backward compatibility -- Best Regards, Vishal Kashyap Director / Lead Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com Jabber IM: [EMAIL PROTECTED] ICQ : 264360076 Yahoo IM: [EMAIL PROTECTED] --- You yourself, as much as anybody in the entire universe, deserve your love and affection. - Buddha --- pgsql=# select marital_status from vishals_life; marital_status -- Single not looking 1 Row(s) affected ___ //\\\ ( 0_0 ) o0o-o0o- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] boolean to int
Pavel Stehule wrote: Hello, you can use own cast. I think I have to create an own type too, because I don't want to use typecast in every select. You gave me the idea, thank you. Mage ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Character escape in "CREATE FUNCTION ..."
On Monday 15 March 2004 18:38, Shilong Stanley Yao wrote: > Dear All, > I am trying to write a function in Postgresql, which takes 2 floats and > returns a box. But seems the nested single-quote in the AS clause > prevent $1 and $2 from being expanded. Besides writing a C function > instead of a SQL one, is there any way to solve this problem? > CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box > AS 'SELECT box \'(($1, $2), (1.3, 1.4))\'' > LANGUAGE 'sql' > WITH (ISCACHABLE); If the box constructor is supposed to take a string, try something like: SELECT box \'((\' || $1 || \'...etc That is to say - use string concatenation -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] boolean to int
Hello, you can use own cast. create or replace function int2bool (integer) returns boolean as ' select case when $1=1 then ''t''::boolean else ''f''::boolean end; ' language sql; create or replace function bool2int (boolean) returns integer as ' select case when $1 then 0 else 1 end; ' language sql; create cast (integer as boolean) with function int2bool(integer) as implicit; create cast (boolean as integer) with function bool2int(boolean) as implicit; regards Pavel Stehule On Mon, 15 Mar 2004, Mage wrote: >Hello, > > I'm wondering why pgsql doesn't support boolean typecasts like select > true::int; > Many client applications including php assign 1 to true and 0 to false > > I see no use of pgsql boolean type with php, I use int2 or integer. > >Mage > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] returning row numbers in select
On Wednesday 10 March 2004 12:25 pm, Randall Skelton wrote: > Is there a way to return an integer row number for a query? Note > that there may be a large number of rows so I would rather not have > joined selects... Well...if your result has a unique column you can do something like this: steve=# select (select count(*) from bar as barcount where barcount.sec<=bar.sec) as rownum, sec from bar order by sec; rownum |sec + 1 | 1063966688 2 | 1063966689 3 | 1063966690 4 | 1063966691 5 | 1063966692 6 | 1063966693 7 | 1063966694 8 | 1063966695 9 | 1063966696 10 | 1063966697 11 | 1063966698 12 | 1063966699 13 | 1063966700 14 | 1063966701 15 | 1063966702 16 | 1063966703 17 | 1063966704 18 | 1063966705 As you might guess, this is not a fast query - more of a brute-force kludge. It's likely that you will be better off postprocessing the query to select every n records or possibly writing a function that will handle the situation. Cheers, Steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] boolean to int
Hello, I'm wondering why pgsql doesn't support boolean typecasts like select true::int; Many client applications including php assign 1 to true and 0 to false I see no use of pgsql boolean type with php, I use int2 or integer. Mage ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgeSQL problem (server crashed?)
Frank Finner wrote: Seems we have been hunting the wrong bugs in the beginning. Could it be possible that there was a major version update of the database engine (7.2.x -> 7.4.x for example), without a database reload? I have noticed several new "features" for queries, I think, 7.4 is much closer to SQL standards than 7.3, which, in turn, is closer than 7.2, especially regarding the necessity of type casts. In one of our applications we had to rewrite most of the queries, because they did not work properly with newer PostgreSQL releases. So that could have caused the PHP-Errors, and the restart of the database without a dump/restore could then cause the start up failure. Just a theory. 3 weeks ago, i've upgraded my PostgreSQL server from 7.2.4 to 7.4.1. In 7.2 i've dumped the tables with pg_dump and after the upgrade the tables are reloaded with psql. Did you compare the actual binaries and the binaries of the backup? Are they identical? There are no Postgres binaries in /usr/local/pgsql, but in /usr/local/bin. Because i've only copied /usr/local/pgsql, the binaries are no changed. -- E-mail : [EMAIL PROTECTED] : [EMAIL PROTECTED] Website : http://www.quicksteps.nl/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Send a variable 123k
On Mar 15, 2004, at 11:57 AM, Edwin Quijada wrote: I need to store a picture into my DB. I am converting this into text Base64 and store it into text field. Everything looked fine but when I did the insert I got an error about pqread() and this was disconnected. Seem a text so big it is not supported for pqread(). well. it SHOULD support text that big. Was there anything in the log? Also, you may want to consider bytea instead of base64 stored in a text. -- Jeff Trout <[EMAIL PROTECTED]> http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Character escape in "CREATE FUNCTION ..."
Dear All, I am trying to write a function in Postgresql, which takes 2 floats and returns a box. But seems the nested single-quote in the AS clause prevent $1 and $2 from being expanded. Besides writing a C function instead of a SQL one, is there any way to solve this problem? Thanks a lot. -Stan CREATE OR REPLACE FUNCTION func_radec_to_box(float, float) RETURNS box AS 'SELECT box \'(($1, $2), (1.3, 1.4))\'' LANGUAGE 'sql' WITH (ISCACHABLE); Error message: psql:func_radec_to_box.sql:4: ERROR: Bad box external representation '(($1, $2), (1.3, 1.4))' ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
