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.
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,
"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
> htt
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 fi
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
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
);
No
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
- 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 ta
"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
-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 be
"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 stand
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 th
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
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
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 lo
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 th
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.
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 (earli
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
>
> 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 sh
-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
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,
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 maximu
-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:
SELE
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 t
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 cus
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 '
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 le
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
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 shou
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 th
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
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" <
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
34 matches
Mail list logo
