[SQL] row level locking?
I have an update statement (no transaction controls surround these statements): update sessions set sessdate = 0 where sessid in ( long list of ids); How long will the rows being updated be locked for this statement? Will all be locked until all updates are completed or will the row locking only occur for each row being updated? If I have a statement like: update sessions set sessdate = 0 where datetime < 1; (this would be the same criteria that created the list used above) How long will each row be locked for? These queries can be updating a good number of rows ( > 10,000) every 10 minutes and I need to figure out how signifigant of an impact the locking occuring in those updates can be. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] calling a shell script from pl/pgsql
How can I call a shell script from within a pl/pgsql function that is called as from a trigger. I do not want to interact with the script I just want it to run. I do want the trigger to wait for the script it called to complete before returning a value. Any ideas would be greatly appreciated. Thanks, Jeff Barrett ---(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: [SQL] row level locking?
On Mon, 10 Sep 2001, Jeff Barrett wrote: > I have an update statement (no transaction controls surround these > statements): > > update sessions set sessdate = 0 where sessid in ( long list of ids); > > How long will the rows being updated be locked for this statement? Will all > be locked until all updates are completed or will the row locking only occur > for each row being updated? AFAIK until the end of statement (since it's wrapped in an implicit transaction) > If I have a statement like: > > update sessions set sessdate = 0 where datetime < 1; (this would be the > same criteria that created the list used above) Should be the same I would guess. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling a shell script from pl/pgsql
On Mon, 10 Sep 2001, Jeff Barrett wrote: > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. I don't think you can do that from within pl/pgsql. You'd probably need a function in pl/tclu or c. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling a shell script from pl/pgsql
* Stephan Szabo <[EMAIL PROTECTED]> [010910 12:37]: > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > How can I call a shell script from within a pl/pgsql function that is called > > as from a trigger. I do not want to interact with the script I just want it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > I don't think you can do that from within pl/pgsql. You'd probably need a > function in pl/tclu or c. You can also look at Peter Eisentraut's PL/sh, but there are portability issues with it: I can't seem to find the correct page at the moment, but it's out there. LER > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
pl/sh (was Re: [SQL] calling a shell script from pl/pgsql)
On Mon, 10 Sep 2001, Stephan Szabo wrote: > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > How can I call a shell script from within a pl/pgsql function that is called > > as from a trigger. I do not want to interact with the script I just want it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > I don't think you can do that from within pl/pgsql. You'd probably need a > function in pl/tclu or c. Or pl/perlu! (Sorry, had to say it). Actually, I remember that Jan once mentioned something about pl/SH. I don't know what's the status of it? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: pl/sh (was Re: [SQL] calling a shell script from pl/pgsql)
On Mon, 10 Sep 2001, Alex Pilosov wrote: > On Mon, 10 Sep 2001, Stephan Szabo wrote: > > > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > > > How can I call a shell script from within a pl/pgsql function that is called > > > as from a trigger. I do not want to interact with the script I just want it > > > to run. I do want the trigger to wait for the script it called to complete > > > before returning a value. > > > > > > Any ideas would be greatly appreciated. > > > > I don't think you can do that from within pl/pgsql. You'd probably need a > > function in pl/tclu or c. > Or pl/perlu! (Sorry, had to say it). I thought perlu was added after 7.1? I considered mentioning it, but realized that it wouldn't help if it wasn't there yet. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: pl/sh (was Re: [SQL] calling a shell script from pl/pgsql)
On Mon, 10 Sep 2001, Stephan Szabo wrote: > > On Mon, 10 Sep 2001, Alex Pilosov wrote: > > > On Mon, 10 Sep 2001, Stephan Szabo wrote: > > > > > On Mon, 10 Sep 2001, Jeff Barrett wrote: > > > > > > > How can I call a shell script from within a pl/pgsql function that is called > > > > as from a trigger. I do not want to interact with the script I just want it > > > > to run. I do want the trigger to wait for the script it called to complete > > > > before returning a value. > > > > > > > > Any ideas would be greatly appreciated. > > > > > > I don't think you can do that from within pl/pgsql. You'd probably need a > > > function in pl/tclu or c. > > Or pl/perlu! (Sorry, had to say it). > > I thought perlu was added after 7.1? I considered mentioning it, but > realized that it wouldn't help if it wasn't there yet. Sorry, my fault, I thought pl/tclu is also a new language, but it apparently is in 7.1...Yes, for existing postgres, its pltclu or C, sorry :) -alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling a shell script from pl/pgsql
Stephan Szabo wrote:
>
> On Mon, 10 Sep 2001, Jeff Barrett wrote:
>
> > How can I call a shell script from within a pl/pgsql function that is called
> > as from a trigger. I do not want to interact with the script I just want it
> > to run. I do want the trigger to wait for the script it called to complete
> > before returning a value.
> >
> > Any ideas would be greatly appreciated.
>
> I don't think you can do that from within pl/pgsql. You'd probably need a
> function in pl/tclu or c.
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
if its typical perl you should be able to
use backticks
my $system=`date`;
$system should be set to the date
or if you dont need any data back as you say
then just
system('date');
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] calling a shell script from pl/pgsql
Thanks for the suggestions. I am running 7.1.2 and going to 7.1.3 soon. If I use pl/tclu or pl/perlu I can call this executable from within the code? I have a signifigant limitation, I cannot duplicate the action of the programs I want to call in a program I write within postgres, I need to call the executable (In this one case it is a shell script but I have others where it is a binary). I cannot find the pl/sh module. The google links that came up brought me to pages that no longer exist on postgresql.org. I will have to look around some more. Thanks for the advice... great help! Jeff Barrett "Jeff Barrett" <[EMAIL PROTECTED]> wrote in message 9nip2p$1s5o$[EMAIL PROTECTED]">news:9nip2p$1s5o$[EMAIL PROTECTED]... > How can I call a shell script from within a pl/pgsql function that is called > as from a trigger. I do not want to interact with the script I just want it > to run. I do want the trigger to wait for the script it called to complete > before returning a value. > > Any ideas would be greatly appreciated. > > Thanks, > > Jeff Barrett > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calling a shell script from pl/pgsql
* Jeff Barrett <[EMAIL PROTECTED]> [010910 14:48]: > Thanks for the suggestions. > > I am running 7.1.2 and going to 7.1.3 soon. > > If I use pl/tclu or pl/perlu I can call this executable from within the > code? > > I have a signifigant limitation, I cannot duplicate the action of the > programs I want to call in a program I write within postgres, I need to call > the executable (In this one case it is a shell script but I have others > where it is a binary). > > I cannot find the pl/sh module. The google links that came up brought me to > pages that no longer exist on postgresql.org. I will have to look around > some more. I've reported the missing pages to the postgresql.org webmaster. > > Thanks for the advice... great help! > > Jeff Barrett > > "Jeff Barrett" <[EMAIL PROTECTED]> wrote in message > 9nip2p$1s5o$[EMAIL PROTECTED]">news:9nip2p$1s5o$[EMAIL PROTECTED]... > > How can I call a shell script from within a pl/pgsql function that is > called > > as from a trigger. I do not want to interact with the script I just want > it > > to run. I do want the trigger to wait for the script it called to complete > > before returning a value. > > > > Any ideas would be greatly appreciated. > > > > Thanks, > > > > Jeff Barrett > > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: pl/sh (was Re: [SQL] calling a shell script from pl/pgsql)
Alex Pilosov writes: > Actually, I remember that Jan once mentioned something about pl/SH. I > don't know what's the status of it? http://webmail.postgresql.org/~petere/plsh.html It's a toy project of mine. It's usable, but there are probably some portability problems. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] group by weirdness
Could someome explain these error messages to me? Why am I being asked to group by j.id? And why is the subquery worried about ml.oid if ml.oid is used in an aggregate? Follows: script, then output. select version(); create table j (id int, created timestamp default current_timestamp, fkey int); create table mj (jid int, mid int); create table ml (jid int, created timestamp default current_timestamp, state int); insert into j (id, fkey) values (1, 1); insert into j (id, fkey) values (2, 1); insert into mj values(1, 1); insert into mj values(1, 2); insert into mj values(2, 3); insert into mj values(2, 4); insert into mj values(2, 5); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 2); insert into ml(jid, state) values (1, 11); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 2); insert into ml(jid, state) values (2, 11); select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.state IN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; select j.id, j.created, count(mj.mid), (select count(ml.oid) where ml.state <> 11), (select count(ml.oid) where ml.state IN(2,5) ) FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id group by j.id, j.created; drop table j; drop table mj ; drop table ml; === playpen=# select version(); version - PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66 (1 row) playpen=# create table j (id int, created timestamp default current_timestamp, fkey int); CREATE playpen=# create table mj (jid int, mid int); CREATE playpen=# create table ml (jid int, created timestamp default current_timestamp, state int); CREATE playpen=# playpen=# insert into j (id, fkey) values (1, 1); playpen=# insert into ml(jid, state) values (2, 11); INSERT 329676 1 playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id ; ERROR: Attribute j.id must be GROUPed or used in an aggregate function playpen=# playpen=# select j.id, j.created, count(mj.mid), playpen-# (select count(ml.oid) where ml.state <> 11), playpen-# (select count(ml.oid) where ml.state IN(2,5) ) playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND ml.jid = j.id playpen-# group by j.id, j.created; ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query playpen=# playpen=# drop table j; DROP playpen=# drop table mj ; DROP playpen=# drop table ml; DROP -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] group by weirdness
Joseph, The subject line could describe a lot of what I see outside my house every day (I live in San Francisco CA). > Could someome explain these error messages to me? Why am I being > asked to group by j.id? Because you've asked the db engine to count on mj.mid. The parser want you to be specific about whether the other columns are being aggregated or not. > And why is the subquery worried about ml.oid if ml.oid is used in > an aggregate? > playpen=# select j.id, j.created, count(mj.mid), > playpen-# (select count(ml.oid) where ml.state <> 11), > playpen-# (select count(ml.oid) where ml.state IN(2,5) ) > playpen-# FROM j, mj, ml WHERE j.fkey = 1 AND mj.jid = j.id AND > ml.jid = j.id > playpen-# group by j.id, j.created; > ERROR: Sub-SELECT uses un-GROUPed attribute ml.oid from outer query Because you're trying to aggregate two aggregates which are sub-selected in the FROM clause ... a very painful way to not get the results you're looking for. Even if you fixed the GROUPing problem, this query wouldn't parse for other reasons. For example, the subselects you've chosen would return the same count for every row, the total of ml.oid in the database. Try putting your sub-selects in the FROM clause instead. (Personally, I've never found a use for sub-selects in the SELECT clause) SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount FROM j, mj, (SELECTjid, COUNT(oid) as mcount FROM ml WHERE ml.state <> 11 GROUP BY jid) ma1, (SELECT jid, COUNT(oid) as mcount FROM ml WHERE ml.state in (2,5) GROUP BY jid) ma2 WHERE j.fkey = 1 AND mj.jid = j.id AND ma1.jid = j.id AND ma2.jid = j.id GROUP BY j.id, j.created, ma1.mcount, ma2.mcount; -Josh Berkus PS. Thanks for providing such complete data with your question! __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
