[SQL] summing tables
hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik -- Erik Thiele ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] summing tables
update table_name set c = a + b + (select c from table_name as x where x.seq = seq-1) where c is null; additional checks are required if you want to update c when c is not null if all the c are null then this query will do nothing god help u :) - Original Message - From: "Erik Thiele" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 3:09 PM Subject: [SQL] summing tables > hi, > > i have a table consisting of 4 integers. > > seq is for making the table ordered. (ORDER BY SEQ ASC) > a,b,c maybe null > > > seq | a | b | c > -+++--- >0 | 1 | 2 | 3 >1 | 1 | 2 | >2 | 5 | 7 | >3 | -2 | -4 | > > > i am needing a sql statement to do > > c=a+b+"the c of the row with seq one less than myself" > > this statement has to run over the whole table, in seq order. > > > how can this be acomplished??? > > > cu&thanks > erik > > > -- > Erik Thiele > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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] summing tables
Erik Thiele wrote: hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik Hi Erik, this should do the trick: (I have not yet considered the NULLS though...) UPDATE table t set c = a + b + (Select c from table tt where tt.seq = t.seq -1) Cheers, Dani ---(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] summing tables
On Tue, 15 Jul 2003 15:16:21 +0300 "Viorel Dragomir" <[EMAIL PROTECTED]> wrote: > update table_name > set c = a + b + (select c from table_name as x where x.seq = seq-1) > where c is null; hmmm. the query is run row by row, isn't it? but it will have different results depending on the order of those rows. look, the c value is set by one row-query and read by the row-query of the row below. does sql specify some "order is magically always as you expect it" rule? still i am a little confused. and i am sorry, i didn't initially specify that the "seq" are not gapless. i.e. seq-1 does not always exist. but seq-13 could be the next lower one! zeit=# select * from foo; seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) i created this (gapless for easiness) table and run your query: zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null; UPDATE 6 6 updates??? really??? zeit=# select * from foo; seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | 6 | -1 | -2 | 5 | -2 | -2 | 4 | 0 | 1 | (7 rows) h. let's try the statement of the other reply to my initial mail: UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1) zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1); ERROR: parser: parse error at or near "t" h any clues? cu & thx erik > additional checks are required if you want to update c when c is not null > if all the c are null then this query will do nothing > > i have a table consisting of 4 integers. > > > > seq is for making the table ordered. (ORDER BY SEQ ASC) > > a,b,c maybe null > > > > > > seq | a | b | c > > -+++--- > >0 | 1 | 2 | 3 > >1 | 1 | 2 | > >2 | 5 | 7 | > >3 | -2 | -4 | > > > > > > i am needing a sql statement to do > > > > c=a+b+"the c of the row with seq one less than myself" > > > > this statement has to run over the whole table, in seq order. -- Erik Thiele ---(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
[SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
Hi there, though this question has been asked several times before (but never really answered), I have to give it another try. I have a multi-user application which synchronizes access to certain datasets via the database itself. If a user has a project in that application open no other user should be able to work on it too. When developing the application I considered the database to be a place to handle the synchronization since transactions are (normally) an integral part of a database system. When a user opens a project the application firstly locks a corresponding row. But since I don't want the application to block if that row is already locked I use the Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive the lock on the specified row and if that row is already locked it returns with an error without blocking and I can tell the user that the project is already in use. Now that the application is to be ported to PG I need a similar functionality. Is there that an animal? If not, what would you recommend? TIA, - Jan. ---(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
[SQL] Cannot insert dup id in pk
Hello,
I sent this question yesterday morning, but it was not allowed because I
wasn't subscribed to the list. If it did make it through, I appologize
for the dup.
I am having strange behavior when I try to do an insert. Postgres tells
me that it cannot insert a duplicate key into a primary key index, when
I am not trying to insert into it. Can anyone shed light on why this is
happening, or point out the error of my ways?
Here are the details:
wormbase=> \d fdata
Table "public.fdata"
Column | Type | Modifiers
---++---
-
fid | integer| not null default nextval('public.fdata
_fid_seq'::text)
fref | character varying(100) | not null default ''
fstart| integer| not null default '0'
fstop | integer| not null default '0'
fbin | double precision | not null default '0.00'
ftypeid | integer| not null default '0'
fscore| double precision |
fstrand | character varying(3) |
fphase| character varying(3) |
gid | integer| not null default '0'
ftarget_start | integer|
ftarget_stop | integer|
Indexes: pk_fdata primary key btree (fid),
fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
fdata_ftypeid_idx btree (ftypeid),
fdata_gid_idx btree (gid)
Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f
strand = '-'::character varying))
"chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase
= '1'::character varying)) OR (fphase = '2'::character varying))
Now a chunk from my query log:
Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key
into unique index pk_fdata
Note that I do not try to insert anything into fid, the primary key on
this table. Why does Postgres think I am?
Thanks much,
Scott
--
Scott Cain, Ph. D. [EMAIL PROTECTED]
GMOD Coordinator (http://www.gmod.org/) 216-392-3087
Cold Spring Harbor Laboratory
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] help with troublesome query
I have three tables customers which 1-many with requests which is 1-1 with applications. all customers have at least one request but not all requests have an application. I want a query to return all the customers and their application data if they have any ( or else null). So this is like a left join of customers with applications but I do not want multiple rows of customers UNLESS they have an application. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] summing tables
Ok. I disregarded the complexity of this problem. :) You may wish to do this thing from a programming language with every row at a time [in php, asp...]. Anyway here is a function in plpgsql. It solves your problem, i hope; but i don't recommend it. create function update_nulls() returns int as ' declare var1 integer; var2 integer; begin select into var1 count(*) from tab where c is null; var2 := var1; while var1 > 0 loop update table_name set c = a + b + (select x.c from table_name as x where x.seq = table_name.seq-1) where c is null; var1 := var1 - 1; end loop; return var2; end; ' language 'plpgsql'; - Original Message - From: "Erik Thiele" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 3:39 PM Subject: Re: [SQL] summing tables > On Tue, 15 Jul 2003 15:16:21 +0300 > "Viorel Dragomir" <[EMAIL PROTECTED]> wrote: > > > update table_name > > set c = a + b + (select c from table_name as x where x.seq = seq-1) > > where c is null; > > hmmm. the query is run row by row, isn't it? > but it will have different results depending on the order of those rows. > > look, the c value is set by one row-query > and read by the row-query of the row below. > > does sql specify some "order is magically always as you expect it" rule? > > still i am a little confused. > > > and i am sorry, i didn't initially specify that the "seq" are not gapless. > i.e. seq-1 does not always exist. but seq-13 could be the next lower one! > > zeit=# select * from foo; > seq | a | b | c > -+++--- >0 | 1 | 2 | 3 >1 | 1 | 2 | >2 | 5 | 7 | >3 | -2 | -4 | >6 | -1 | -2 | >5 | -2 | -2 | >4 | 0 | 1 | > (7 rows) > > i created this (gapless for easiness) table and run your query: > > zeit=# update foo set c = a + b + (select c from foo as x where x.seq = seq-1) where c is null; > UPDATE 6 > > 6 updates??? really??? > > zeit=# select * from foo; > seq | a | b | c > -+++--- >0 | 1 | 2 | 3 >1 | 1 | 2 | >2 | 5 | 7 | >3 | -2 | -4 | >6 | -1 | -2 | >5 | -2 | -2 | >4 | 0 | 1 | > (7 rows) > > > h. let's try the statement of the other reply to my initial mail: > > UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1) > > zeit=# UPDATE foo t set c = a + b + (Select c from foo tt where tt.seq = t.seq -1); > ERROR: parser: parse error at or near "t" > > h > > any clues? > > cu & thx > erik > > > additional checks are required if you want to update c when c is not null > > if all the c are null then this query will do nothing > > > i have a table consisting of 4 integers. > > > > > > seq is for making the table ordered. (ORDER BY SEQ ASC) > > > a,b,c maybe null > > > > > > > > > seq | a | b | c > > > -+++--- > > >0 | 1 | 2 | 3 > > >1 | 1 | 2 | > > >2 | 5 | 7 | > > >3 | -2 | -4 | > > > > > > > > > i am needing a sql statement to do > > > > > > c=a+b+"the c of the row with seq one less than myself" > > > > > > this statement has to run over the whole table, in seq order. > > -- > Erik Thiele > > ---(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 ---(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] help with troublesome query
On Tue, Jul 15, 2003 at 14:26:16 +0100, teknokrat <[EMAIL PROTECTED]> wrote: > I have three tables customers which 1-many with requests which is 1-1 > with applications. all customers have at least one request but not all > requests have an application. > > I want a query to return all the customers and their application data if > they have any ( or else null). So this is like a left join of customers > with applications but I do not want multiple rows of customers UNLESS > they have an application. I think you want to first do an inner join of requests and applications and then left join the customers and the previous join. ---(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
[SQL] Count dates distinct within an interval
Hi, everybody!
I was trying to formulate a sql query, that I now think is impossible :-(
I just thought, I'd run it by you before I give up - perhaps, you guys
would have some ideas...
Suppose, I have a table like this
create table test
(
stuff int,
stamp timestamp
);
Now, I want to count the occurences of each value of stuff in the table,
but so that entries within 24 hours from each other count as one...
The closest I could think of is:
select stuff, count (distinct date_trunc ('day', stamp)) from test group
by stuff;
This doesn't do exactly what I need though - for example, if one entry
is one minute before midnight, and the other one is two minutes later,
they'd get counted as 2, and what I am looking for is the way to get
them collapsed into one as long as they are less then 24 hours apart...
Now, I am pretty sure, it is simply impossible to do what I want with
count (distinct...) because my 'equality' is not transitive - for
example, three entries, like
A = 2001 - 01- 01 20:20:00
B = 2001 - 01 - 02 20:19:00
C = 2001 - 01 - 02 20:21:00
Should be counted as *two* (A === B, and B === C, but *not* A === C)...
Also, I could certainly write a simple function, that would get all the
entries in order, and scan through them, counting according to my rules...
But I was hoping to find some way to do this in plain sql though...
Any ideas?
Thanks!
Dima
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] max length of sql select statement (long!)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> ... I don't want to take GO:06 into account (two parents in which
> I am intested in). That menas, whenever I ask for children of two nodes,
> I want a DISTINCT SET of children.
To start with, you can avoid the Java and do this in SQL:
SELECT child FROM gograph WHERE parent='GO:002' OR parent='GO:005'
EXCEPT
(SELECT child FROM gograph WHERE parent='GO:002'
INTERSECT
SELECT child FROM gograph WHERE parent='GO:005');
And yes, I would certainly start by normalizing things a little bit:
CREATE SEQUENCE goid_seq;
CREATE TABLE goID (
idname TEXT,
id INTEGER NOT NULL DEFAULT nextval('goid_seq')
);
INSERT INTO goid(idname) SELECT DISTINCT parent FROM gograph;
INSERT INTO goid(idname)
SELECT DISTINCT child FROM gograph WHERE NOT EXISTS (SELECT 1 FROM goID WHERE idname =
child);
CREATE TABLE gomap (
parent INTEGER,
child INTEGER
);
INSERT INTO gomap SELECT
(SELECT id FROM goid WHERE idname=parent),
(SELECT id FROM goid WHERE idname=child)
FROM gograph
As far as the binaryInteraction table, a little more information is needed:
how are each of these tables being populated? Why the distinct? Is it because
there may be duplicate rows in the table? The reason I as is that it might be
better to ue triggers to compute some of the information as it comes in,
depending on which tables are changes and how often.
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200307151035
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/FBGrvJuQZxSWSsgRAlEfAKCL4ttDdTRHxPRW9N00nowPh1/q/QCgqrkv
e7Ncj4al4aJ4ihktEyweJJo=
=Z/rk
-END PGP SIGNATURE-
---(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: [SQL] Cannot insert dup id in pk
Title: RE: [SQL] Cannot insert dup id in pk
I suspect the sequence is out of sync with the values actually in you primary key (which I gues is fid.
Try this query
SELECT setval('public.fdata _fid_seq'::text,MAX(fid)+1) FROM fdata;
This should set the value of the sequence to the current maximum+1
hth,
- Stuart
P.S. Sorry about the format change the disclaimer adder forces
> -Original Message-
> From: Scott Cain [mailto:[EMAIL PROTECTED]]
> Sent: 15 July 2003 14:00
> To: [EMAIL PROTECTED]
> Subject: [SQL] Cannot insert dup id in pk
>
>
> THIS EMAIL HAS BEEN SWEPT FOR VIRUSES BY THE NORTHCLIFFE
> GROUP MAILSWEEPER SERVER.
>
> Hello,
>
> I sent this question yesterday morning, but it was not
> allowed because I
> wasn't subscribed to the list. If it did make it through, I
> appologize
> for the dup.
>
> I am having strange behavior when I try to do an insert.
> Postgres tells
> me that it cannot insert a duplicate key into a primary key
> index, when
> I am not trying to insert into it. Can anyone shed light on
> why this is
> happening, or point out the error of my ways?
>
> Here are the details:
> wormbase=> \d fdata
> Table "public.fdata"
> Column | Type |
> Modifiers
>
> ---++-
> -- -
> fid | integer | not null default
> nextval('public.fdata _fid_seq'::text)
> fref | character varying(100) | not null default ''
> fstart | integer | not null default '0'
> fstop | integer | not null default '0'
> fbin | double precision | not null default '0.00'
> ftypeid | integer | not null default '0'
> fscore | double precision |
> fstrand | character varying(3) |
> fphase | character varying(3) |
> gid | integer | not null default '0'
> ftarget_start | integer |
> ftarget_stop | integer |
> Indexes: pk_fdata primary key btree (fid),
> fdata_fref_idx btree (fref, fbin, fstart, fstop,
> ftypeid, gid),
> fdata_ftypeid_idx btree (ftypeid),
> fdata_gid_idx btree (gid)
> Check constraints: "chk_fdata_fstrand" ((fstrand =
> '+'::character varying) OR (f strand = '-'::character varying))
> "chk_fdata_fphase" (((fphase =
> '0'::character varying) OR (fp hase = '1'::character
> varying)) OR (fphase = '2'::character varying))
>
> Now a chunk from my query log:
> Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG:
> query: INSERT INTO fdata
> (fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftar
> get_start,ftarget_stop)
> Jul 14 12:48:47 localhost postgres[2998]: [107-2]
> VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'
> 12358',NULL,NULL)
> Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR:
> Cannot insert a duplicate key into unique index pk_fdata
>
> Note that I do not try to insert anything into fid, the primary key on
> this table. Why does Postgres think I am?
>
> Thanks much,
> Scott
>
> --
> --
> --
> Scott Cain, Ph. D.
> [EMAIL PROTECTED]
> GMOD Coordinator (http://www.gmod.org/)
> 216-392-3087
> Cold Spring Harbor Laboratory
>
>
> ---(end of
> broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee. Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful. Please immediately contact the sender if you have received this message in error. Thank you.
Re: [SQL] Cannot insert dup id in pk
You must have your sequence out of date with the content of the table
(so that the next value in the sequence has already been inserted).
One way to get into a situation like that is loading the table data with
COPY (the input contains the pks, and the COPY command does not update
the sequence, you have to do that manually after the copy is done).
Another way is simply inserting a row with an explicitly specified pkey:
insert into fdata (fid,...) values (100, ...);
Now, assuming, that you current sequence value is less then 100, and
that the statement above succeedes (i.e., there is no fid=100 in the
table yet), you'll get your sequence out of date. You'll still be able
to use it, and insert the rows into the table *until* the current value
reaches 100 - once that happens, an attempt to insert with the default
fid will cause an error, because the sequence will generate a key, that
already exists.
To fix this, you need to do something like:
select setval ('fdata_fid_seq', (select fid from fdata order by fid
limit 1));
This will make sure that the next value your sequence generates is
greater than any key that already exists in the table.
I hope, it helps...
Dima
insert into fdata
Scott Cain wrote:
Hello,
I sent this question yesterday morning, but it was not allowed because I
wasn't subscribed to the list. If it did make it through, I appologize
for the dup.
I am having strange behavior when I try to do an insert. Postgres tells
me that it cannot insert a duplicate key into a primary key index, when
I am not trying to insert into it. Can anyone shed light on why this is
happening, or point out the error of my ways?
Here are the details:
wormbase=> \d fdata
Table "public.fdata"
Column | Type | Modifiers
---++--- -
fid | integer| not null default nextval('public.fdata _fid_seq'::text)
fref | character varying(100) | not null default ''
fstart| integer| not null default '0'
fstop | integer| not null default '0'
fbin | double precision | not null default '0.00'
ftypeid | integer| not null default '0'
fscore| double precision |
fstrand | character varying(3) |
fphase| character varying(3) |
gid | integer| not null default '0'
ftarget_start | integer|
ftarget_stop | integer|
Indexes: pk_fdata primary key btree (fid),
fdata_fref_idx btree (fref, fbin, fstart, fstop, ftypeid, gid),
fdata_ftypeid_idx btree (ftypeid),
fdata_gid_idx btree (gid)
Check constraints: "chk_fdata_fstrand" ((fstrand = '+'::character varying) OR (f strand = '-'::character varying))
"chk_fdata_fphase" (((fphase = '0'::character varying) OR (fp hase = '1'::character varying)) OR (fphase = '2'::character varying))
Now a chunk from my query log:
Jul 14 12:48:47 localhost postgres[2998]: [107-1] LOG: query: INSERT INTO fdata
(fref,fstart,fstop,fbin,ftypeid,fscore,fstrand,fphase,gid,ftarget_start,ftarget_stop)
Jul 14 12:48:47 localhost postgres[2998]: [107-2]
VALUES('XX',7327656,7327658,'1000.007327','41',NULL,'+',NULL,'12358',NULL,NULL)
Jul 14 12:48:47 localhost postgres[2998]: [108] ERROR: Cannot insert a duplicate key
into unique index pk_fdata
Note that I do not try to insert anything into fid, the primary key on
this table. Why does Postgres think I am?
Thanks much,
Scott
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Count dates distinct within an interval
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Now, I want to count the occurences of each value of stuff in the table, > but so that entries within 24 hours from each other count as one... >... > A = 2001 - 01- 01 20:20:00 > B = 2001 - 01 - 02 20:19:00 > C = 2001 - 01 - 02 20:21:00 > Should be counted as *two* (A === B, and B === C, but *not* A === C)... You need to elaborate on your logic some more, and state exactly what you would want in the A,B,C case above. Does B get lumped with A or with C? It is within 24 hours of both, after all. Does C not get lumped in with B simply because B has already been lumped in with A? Perhaps showing us the sample output of an ideal query would be best, along with some sample rows (e.g. use real insert statements) - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200307151045 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE/FBS9vJuQZxSWSsgRAnk3AJ0bqyDk6iZWqSZuHfZslFCjxwl7fgCfaZ7r XdwpPsO4OaTa9YpjmXx1hmA= =IFRz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
> > though this question has been asked several times before (but never really > answered), I have to give it another try. > > I have a multi-user application which synchronizes access to certain datasets > via the database itself. If a user has a project in that application open no > other user should be able to work on it too. When developing the application I > considered the database to be a place to handle the synchronization since > transactions are (normally) an integral part of a database system. When a user > opens a project the application firstly locks a corresponding row. But since I > don't want the application to block if that row is already locked I use the > Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive > the lock on the specified row and if that row is already locked it returns > with an error without blocking and I can tell the user that the project is > already in use. > > Now that the application is to be ported to PG I need a similar functionality. > Is there that an animal? If not, what would you recommend? > PostgreSQL 7.3.2 T1: begin; select * from foo for update; T2: set STATEMENT_TIMEOUT = 1000; -- milliseconds T2: select * from foo for update; T2: ERROR: Query was cancelled. HTH, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] summing tables
To solve this problem efficiently you probably need the lead/lag analytic functions. Unfortunately Postgres doesn't have them. You could do it with something like: update foo set c = a+b+(select c from foo as x where seq < foo.seq ORDER BY seq desc LIMIT 1) or the more standard but likely to be way slower: update foo set c = a+b+(select c from foo as x where seq = (select max(seq) from foo as y where seq < foo.seq)) However, i would suggest that if you have an implicit relationship between records you should make that relationship explicit with a foreign key. If you had a column that contained the seq of the parent record then this would be easy. I'm really puzzled how this query as currently specified could be useful. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Count dates distinct within an interval
You need to elaborate on your logic some more, and state exactly what you would want in the A,B,C case above. Does B get lumped with A or with C? It is within 24 hours of both, after all. Does C not get lumped in with B simply because B has already been lumped in with A? Yes. The first (earliest) entry is counted, then all the later ones a skipped as long as they are within 24 hours from the last one, that's counted. In this case, A is counted, B is skipped, because it is within 24 hours of A, then C is counted, because it is more than 24 hours from A. Thanks! Dima ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] summing tables
Erik, If you intent is to get a running total of a and b ordered by seq, you should try this (assuming the table name is t): update t set c = ( select sum(a) + sum(b) from t t1 where t1.seq <= t.seq); You should have an index on seq. If the table is very large, it is going to be painfully slow. In that case you may want to think about using a function to step thru each row. JLL Erik Thiele wrote: > > hi, > > i have a table consisting of 4 integers. > > seq is for making the table ordered. (ORDER BY SEQ ASC) > a,b,c maybe null > > seq | a | b | c > -+++--- >0 | 1 | 2 | 3 >1 | 1 | 2 | >2 | 5 | 7 | >3 | -2 | -4 | > > i am needing a sql statement to do > > c=a+b+"the c of the row with seq one less than myself" > > this statement has to run over the whole table, in seq order. > > how can this be acomplished??? > > cu&thanks > erik > > -- > Erik Thiele > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] summing tables
Erik Thiele <[EMAIL PROTECTED]> writes: > "Viorel Dragomir" <[EMAIL PROTECTED]> wrote: >> update table_name >> set c = a + b + (select c from table_name as x where x.seq = seq-1) >> where c is null; > hmmm. the query is run row by row, isn't it? > but it will have different results depending on the order of those rows. No, it won't, because the SELECTs will not see the changes from the not-yet-completed UPDATE. The above command is almost right; it needs to be update table_name set c = a + b + (select c from table_name as x where seq = table_name.seq-1) where c is null; because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's table. You didn't say exactly what you wanted to do with null inputs, so that issue may need more thought. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
There is no such thing, as far as I know :-( Here is the poor man solution I used to emulate this 'nowait' behaviour: create table master_lock ( projectid text primary key, locker int ); Now, the application first acquires an exclusive lock on the table, then, while the table is locked it looks at the projectid row and tests the 'locker' column. If it is null, the app sets it to its connection id (the pid of the backend), and COMMITS (to release the table lock), then starts another transaction and goes about its business, after it is done, it updates the master_lock, and sets the locker back to null. If the locker column is not null, I use pg_stat_activity to test if the connection with this pid still exists (in case the app that locked this project had crashed before releasing the lock)... if the pid isn't there, it considers the project unlock, and does what's described above. Otherwise, it releases the lock on the table, and generates an error message, reporting that the project is locked. Also see the earlier reply to your message, about STATEMENT_TIMEOUT as an alternative... I am using 7.2, so this wasn't an option for me when I came up with this work around I doubt, I'd use that timeout thing if I was on 7.3 anyway though - one problem is, you'd have to parse the actual error message to figure out if the cause of the error is really a timeout, or just something bad happenning in the database... Another problem is that the timeout number is arbitrary - if it is too small, you risk to get a situation when the row is not locked, but the query still gets canceled, because the database is slow at the moment, if it is too large, you'll have to wait for a long time before getting the response (and even then, you can't be 100% sure it really happened because of the lock) I hope, it helps.. Dima Jan Bernhardt wrote: Hi there, though this question has been asked several times before (but never really answered), I have to give it another try. I have a multi-user application which synchronizes access to certain datasets via the database itself. If a user has a project in that application open no other user should be able to work on it too. When developing the application I considered the database to be a place to handle the synchronization since transactions are (normally) an integral part of a database system. When a user opens a project the application firstly locks a corresponding row. But since I don't want the application to block if that row is already locked I use the Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive the lock on the specified row and if that row is already locked it returns with an error without blocking and I can tell the user that the project is already in use. Now that the application is to be ported to PG I need a similar functionality. Is there that an animal? If not, what would you recommend? TIA, - Jan. ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
Jan Bernhardt <[EMAIL PROTECTED]> writes: > I have a multi-user application which synchronizes access to certain datasets > via the database itself. If a user has a project in that application open no > other user should be able to work on it too. When developing the application I > considered the database to be a place to handle the synchronization since > transactions are (normally) an integral part of a database system. When a user > opens a project the application firstly locks a corresponding row. But since I > don't want the application to block if that row is already locked I use the > Oracle "NOWAIT" feature (SELECT ... FOR UPDATE NOWAIT) which tries to receive > the lock on the specified row and if that row is already locked it returns > with an error without blocking and I can tell the user that the project is > already in use. You might be able to use the contrib/userlock module for this. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Cannot insert dup id in pk
Scott Cain <[EMAIL PROTECTED]> writes:
> Note that I do not try to insert anything into fid, the primary key on
> this table. Why does Postgres think I am?
But you *are* trying to insert something into fid, namely the default
value:
default nextval('public.fdata _fid_seq'::text)
My guess is that you have been inconsistent about whether you used the
sequence or explicit assignment to fid, and now you have some rows in
the table that have fid values higher than the current sequence value.
I'd suggest adjusting the sequence, along the lines of
SELECT setval('public.fdata _fid_seq', max(fid)+1) FROM fdata;
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] summing tables
Indeed it was a mistake not to put the table_name. in where clause. But this doesn't resolve the problem. Do you know in which order the update will modify the rows? My lucky guess is that it takes from last inserted rows to the first row. In this way only one row gets updated correctly. I add the result from my unfortunate solution, even corrected. select * from table_name; seq | a | b | c -+---+---+--- 1 | 1 | 2 | 3 2 | 5 | 9 | 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 | (6 rows) update table_name set c = a + b + (select c from table_name as x where seq = table_name.seq-1) where c is null; select * from table_name; seq | a | b | c -+---+---+ 1 | 1 | 2 | 3 2 | 5 | 9 | 17 3 | 1 | 2 | 4 | 4 | 7 | 5 | 4 | 2 | 6 | 0 | 1 | - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Erik Thiele" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 6:14 PM Subject: Re: [SQL] summing tables > Erik Thiele <[EMAIL PROTECTED]> writes: > > "Viorel Dragomir" <[EMAIL PROTECTED]> wrote: > >> update table_name > >> set c = a + b + (select c from table_name as x where x.seq = seq-1) > >> where c is null; > > > hmmm. the query is run row by row, isn't it? > > but it will have different results depending on the order of those rows. > > No, it won't, because the SELECTs will not see the changes from the > not-yet-completed UPDATE. The above command is almost right; it needs > to be > > update table_name > set c = a + b + (select c from table_name as x where seq = table_name.seq-1) > where c is null; > > because inside the sub-SELECT, unadorned "seq" will refer to the SELECT's > table. > > You didn't say exactly what you wanted to do with null inputs, so that > issue may need more thought. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(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: [SQL] summing tables
"Viorel Dragomir" <[EMAIL PROTECTED]> writes: > Anyway, in real life this update modifies only one row with a value wich is > diff of null. It was really handy if it was specified the option ORDER for > the update command. Are you hoping to produce a running total? That's very difficult in standard SQL. That would be very different from the query you asked for. Running totals, ranking, lead/lag, are all things that are very difficult to do in standard SQL. They don't fit in the unordered set model that SQL follows so doing them without special non-standard functions is very hard and inefficient. The functions to do them don't fit well within the SQL universe either, which might be why they don't exist yet in postgres. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Count dates distinct within an interval
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Also, I could certainly write a simple function, that would get all the > entries in order, and scan through them, counting according to my rules... > But I was hoping to find some way to do this in plain sql though... In this example, you are best off using a function. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200307151137 -BEGIN PGP SIGNATURE- Comment: http://www.turnstep.com/pgp.html iD8DBQE/FCAgvJuQZxSWSsgRAg1jAJ9kS9PpIiMkij6TtOg63O59TeezPACgzhMF ZM/84SEPP4doDR8fsGpnUBU= =w5Wa -END PGP SIGNATURE- ---(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] summing tables
"Viorel Dragomir" <[EMAIL PROTECTED]> writes: > Indeed it was a mistake not to put the table_name. in where clause. > But this doesn't resolve the problem. > Do you know in which order the update will modify the rows? No, and *it does not matter*. You are forgetting that this all runs under MVCC rules. The sub-SELECTs will see the pre-existing versions of the rows, whether or not the UPDATE has yet produced new versions. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] summing tables
- Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Viorel Dragomir" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, July 15, 2003 6:42 PM Subject: Re: [SQL] summing tables > "Viorel Dragomir" <[EMAIL PROTECTED]> writes: > > Indeed it was a mistake not to put the table_name. in where clause. > > But this doesn't resolve the problem. > > > Do you know in which order the update will modify the rows? > > No, and *it does not matter*. You are forgetting that this all runs > under MVCC rules. The sub-SELECTs will see the pre-existing versions > of the rows, whether or not the UPDATE has yet produced new versions. Yes that was correct. I forgot that the subselect sees only the old rows. And the ORDER option will not make any difference, only making fool out of myself. :) I don't know if the problem is solved with my posted function. Anyway this is the mail that started all [i get a lot of emails and i'm not really searching for a solution on this matter]: " hi, i have a table consisting of 4 integers. seq is for making the table ordered. (ORDER BY SEQ ASC) a,b,c maybe null seq | a | b | c -+++--- 0 | 1 | 2 | 3 1 | 1 | 2 | 2 | 5 | 7 | 3 | -2 | -4 | i am needing a sql statement to do c=a+b+"the c of the row with seq one less than myself" this statement has to run over the whole table, in seq order. how can this be acomplished??? cu&thanks erik -- Erik Thiele " ---(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] Cannot insert dup id in pk
On Tue, 2003-07-15 at 10:43, Dmitry Tkach wrote: > You must have your sequence out of date with the content of the table > (so that the next value in the sequence has already been inserted). > One way to get into a situation like that is loading the table data with > COPY (the input contains the pks, and the COPY command does not update > the sequence, you have to do that manually after the copy is done). Yes, this is exactly what happened. I had no idea that copy didn't update the sequence. I suspect I've got users who are being bitten by this and don't realize it. I'll have to change my "bulk loading" script to update the sequence after the load is done. Thanks much, Scott -- Scott Cain, Ph. D. [EMAIL PROTECTED] GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory ---(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] Count dates distinct within an interval
Dmitry Tkach wrote:
Hi, everybody!
I was trying to formulate a sql query, that I now think is impossible :-(
I just thought, I'd run it by you before I give up - perhaps, you guys
would have some ideas...
Suppose, I have a table like this
create table test
(
stuff int,
stamp timestamp
);
Now, I want to count the occurences of each value of stuff in the table,
but so that entries within 24 hours from each other count as one...
The closest I could think of is:
select stuff, count (distinct date_trunc ('day', stamp)) from test group
by stuff;
This doesn't do exactly what I need though - for example, if one entry
is one minute before midnight, and the other one is two minutes later,
they'd get counted as 2, and what I am looking for is the way to get
them collapsed into one as long as they are less then 24 hours apart...
Now, I am pretty sure, it is simply impossible to do what I want with
count (distinct...) because my 'equality' is not transitive - for
example, three entries, like
A = 2001 - 01- 01 20:20:00
B = 2001 - 01 - 02 20:19:00
C = 2001 - 01 - 02 20:21:00
Should be counted as *two* (A === B, and B === C, but *not* A === C)...
Also, I could certainly write a simple function, that would get all the
entries in order, and scan through them, counting according to my rules...
But I was hoping to find some way to do this in plain sql though...
Any ideas?
Thanks!
Dima
You would probably be able to speed the following up using immutable
funtions to aid the query, or just a function to do it. However I think
this does what you asked in a query. I've put a script at the end.
hth,
- Stuart
-- s is the stuff to group by
-- dt is the datetime thing
create table Q (
s int4,
dt timestamptz);
truncate Q;
INSERT INTO Q (s,dt) VALUES (1,'2003/01/01'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/02 04:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/01 08:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/02'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/05 23:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/06 22:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (1,'2003/01/07 05:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (3,'2003/01/01'::timestamptz);
INSERT INTO Q (s,dt) VALUES (4,'2003/01/01'::timestamptz);
INSERT INTO Q (s,dt) VALUES (4,'2003/01/02 05:00'::timestamptz);
INSERT INTO Q (s,dt) VALUES (4,'2003/01/03 04:00'::timestamptz);
SELECT COUNT(*),s FROM Q WHERE dt IN (SELECT min(R.dt) FROM Q AS R WHERE
Q.s=R.s)
OR dt IN (SELECT min(P.dt) FROM Q AS P WHERE P.s=Q.s AND
P.dt>=date_trunc('day',Q.dt)-
CASE WHEN
(SELECT max(dt)::time FROM Q AS T WHERE Q.s=T.s AND T.dt
(SELECT * FROM Q AS U WHERE T.dt-'1 day'::intervalU.dt
AND U.s=Q.s))
>Q.dt::time THEN '1 day'::interval
ELSE
'0 day'::interval
END
+(SELECT max(dt)::time FROM Q AS T WHERE Q.s=T.s AND T.dt
(SELECT * FROM Q AS U WHERE T.dt-'1 day'::intervalU.dt
AND U.s=Q.s)))
GROUP BY s;
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] Functional Indexes
Hello all, I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com". I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK). urltld uses urlhost to do it's job (how should be apparent). Now the question: is there a single index I can create that will be used when my WHERE clause contains either urlhost or urltld? I could create two functional indexes, but that seems a bit silly to me. Any thoughts? -- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Functional Indexes
The two functions do not group data the same way, so a common index is not possible. urlhost would put pgsql.org and pgsql.com close together. urltld would but pgsql.com and xyz.com close together. Frank At 01:36 PM 7/15/03, David Olbersen wrote: Hello all, I have a function (urlhost) which finds the 'host' portion of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com". I also have a function (urltld) which returns the TLD of a URL. In the case of http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot is OK). urltld uses urlhost to do it's job (how should be apparent). Now the question: is there a single index I can create that will be used when my WHERE clause contains either urlhost or urltld? I could create two functional indexes, but that seems a bit silly to me. Any thoughts? -- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Functional Indexes
"David Olbersen" <[EMAIL PROTECTED]> writes: > I have a function (urlhost) which finds the 'host' portion of a URL. In the case of > http://www.foobar.com/really/long/path/to/a/file it returns "www.foobar.com". > I also have a function (urltld) which returns the TLD of a URL. In the case of > http://www.foobar.com/really/long/path/to/a/file it returns ".com" (the leading dot > is OK). > urltld uses urlhost to do it's job (how should be apparent). > Now the question: is there a single index I can create that will be > used when my WHERE clause contains either urlhost or urltld? I do not see any way with functions declared like that. Quite aside from implementation limitations, the portion of the 'host' string that urltld is interested in would be the low-order part of the indexed strings, and you can't usefully use an index to search for low-order digits of the key. Could you instead define an index over the reversed host name (eg, com.foobar.www)? This would seem to provide about the same functionality for searches on urlhost, and you could exploit the index for TLD searching via prefixes. For example: regression=# create table t1 (f1 text); CREATE TABLE regression=# create index t1i on t1 (lower(f1)); CREATE INDEX regression=# explain select * from t1 where lower(f1) like 'com.%'; QUERY PLAN Index Scan using t1i on t1 (cost=0.00..17.08 rows=5 width=32) Index Cond: ((lower(f1) >= 'com.'::text) AND (lower(f1) < 'com/'::text)) Filter: (lower(f1) ~~ 'com.%'::text) (3 rows) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Functional Indexes
David Olbersen wrote:
Now the question: is there a single index I can create that will be
used when my WHERE clause contains either urlhost or urltld? I could
create two functional indexes, but that seems a bit silly to me.
I can't think of how to do only one index in 7.3.x and earlier, but
FWIW, this works in 7.4devel (which should be in beta next Monday):
create or replace function tld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',3)
' language 'sql' STRICT IMMUTABLE;
regression=# select tld('http://www.foobar.com/really/long/path/to/a/file');
tld
-
com
(1 row)
create or replace function sld(text) returns text as '
select split_part(split_part(substr($1,8,length($1)),''/'',1),''.'',2)
' language 'sql' STRICT IMMUTABLE;
regression=# select sld('http://www.foobar.com/really/long/path/to/a/file');
sld
foobar
(1 row)
create table urls(f1 text);
insert into urls values('http://www.foobar.com/really/long/path/to/a/file');
create index urls_idx1 on urls(tld(f1),sld(f1));
-- just to see index usage on toy table
set enable_seqscan to off;
regression=# explain analyze select * from urls where tld(f1) = 'com';
QUERY PLAN
--
Index Scan using urls_idx1 on urls (cost=0.00..4.69 rows=1 width=32)
(actual time=0.07..0.07 rows=1 loops=1)
Index Cond: (split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text)
Total runtime: 0.18 msec
(3 rows)
regression=# explain analyze select * from urls where tld(f1) = 'com'
and sld(f1) = 'foobar';
QUERY PLAN
-
Index Scan using urls_idx1 on urls (cost=0.00..4.70 rows=1 width=32)
(actual time=0.08..0.09 rows=1 loops=1)
Index Cond: ((split_part(split_part(substr(f1, 8, length(f1)),
'/'::text, 1), '.'::text, 3) = 'com'::text) AND
(split_part(split_part(substr(f1, 8, length(f1)), '/'::text, 1),
'.'::text, 2) = 'foobar'::text))
Total runtime: 0.21 msec
(3 rows)
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Non-Blocking Locks (i.e. Oracle NOWAIT)
Christoph Haller wrote: PostgreSQL 7.3.2 T1: begin; select * from foo for update; T2: set STATEMENT_TIMEOUT = 1000; -- milliseconds Seems like setting it to 1 (ms) emulates the NOWAIT condition better. Can't set it to 0, though. T2: select * from foo for update; T2: ERROR: Query was cancelled. How picky are we in borrowing syntax and idiocyncracies (?) from other DBs? Since we can closely emulate Oracle's behaviour on SELECT ... FOR UPDATE NOWAIT, should we just add NOWAIT to the next (non-feature frozen version) of postgres? -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 10:30am up 202 days, 1:35, 6 users, load average: 5.40, 5.14, 5.05 pgp0.pgp Description: PGP signature
