Thread: Force Lock

On Thu, Aug 28, 2003 at 15:17:34 -0500,
  Girish Bajaj <gbajaj@tietronix.com> wrote:
> Hello all,
>
>
>
> Is there a way I can force a more aggressive lock on a table in a SELECT
> statement that's being executed in a transaction?
>
>
>
> SELECT FOR UPDATE is not aggressive enough. I need an exclusive lock on that
> particular table the select is accessing.

You can use a LOCK statement to do this.

On Thu, Aug 28, 2003 at 22:11:49 -0400,
  "Luis H." <pgsql-novice@geekhouse.no-ip.com> wrote:
> I have a table where each row has an id unique identifier (separate from the one postgreql does automatically). I
wantto be able to do a certain SELECT query returning various rows, and also be able to check what the maximum id was
amongthe rows in the result. Is it possible to do this all in the same query? Right now I do it in a very dumb way -- I
executethe query to get all the data, and then I re-execute it as a subquery and pull out the max(id).  

Yes and no. You won't be able to return rows and the max id of the rows
without effectively doing the query twice. However, you could order the
output rows by the id and check the first (or last depending on sort
direction) row to get the value you want.

Thanks for the info. I just realized I had thought out the logic of my web
app in the wrong way. I will now be working with the number of items in the
table. I know there is a count() query, and earlier someone asked for a
better solution (that doesn't need to cycle through the table). Tom Lane
mentioned pg_class.reltuples, but said it wasn't a completely accurate
measure. Any suggestions?

Thanks.

----- Original Message -----
From: "Bruno Wolff III" <bruno@wolff.to>
To: "Luis H." <pgsql-novice@geekhouse.no-ip.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Thursday, August 28, 2003 10:36 PM
Subject: Re: [NOVICE] select items, and max id


> On Thu, Aug 28, 2003 at 22:11:49 -0400,
>   "Luis H." <pgsql-novice@geekhouse.no-ip.com> wrote:
> > I have a table where each row has an id unique identifier (separate from
the one postgreql does automatically). I want to be able to do a certain
SELECT query returning various rows, and also be able to check what the
maximum id was among the rows in the result. Is it possible to do this all
in the same query? Right now I do it in a very dumb way -- I execute the
query to get all the data, and then I re-execute it as a subquery and pull
out the max(id).
>
> Yes and no. You won't be able to return rows and the max id of the rows
> without effectively doing the query twice. However, you could order the
> output rows by the id and check the first (or last depending on sort
> direction) row to get the value you want.
>

On Thu, Aug 28, 2003 at 22:46:26 -0400,
  "Luis H." <pgsql-novice@geekhouse.no-ip.com> wrote:
> Thanks for the info. I just realized I had thought out the logic of my web
> app in the wrong way. I will now be working with the number of items in the
> table. I know there is a count() query, and earlier someone asked for a
> better solution (that doesn't need to cycle through the table). Tom Lane
> mentioned pg_class.reltuples, but said it wasn't a completely accurate
> measure. Any suggestions?

If you need an exact number you either have to count the number of records
in the table that match your critera or you need to maintain counts
somewhere. The cost of maintaining the counts can potentially be high so
it won't necessarily be better to do it that way.

Am Fr, 2003-08-29 um 04.11 schrieb Luis H.:
> I have a table where each row has an id unique identifier (separate
> from the one postgreql does automatically).

I would declare this as primary key too, not only unique.
Due to the fact that you didn't mention the words "primary key" I assume
you only have a unique field.
I think it could be useful for you to have something like

CREATE TABLE my_table (
 id_my_table SERIAL PRIMARY KEY,
 ...
 ...
);

SERIAL is in fact of type int but will be auto incremented with each
insert (if omitted).
PRIMARY KEY makes it UNIQUE and NOT NULL automatically and will use it
as default for FOREIGN KEYs.

That wasn't your question, but I think it can't hurt :)

> I want to be able to do a certain SELECT query returning various rows,
> and also be able to check what the maximum id was among the rows in
> the result. Is it possible to do this all in the same query? Right now
> I do it in a very dumb way -- I execute the query to get all the data,
> and then I re-execute it as a subquery and pull out the max(id).

The related keywords are:
aggregate functions
 and
GROUP BY

max is an aggreate function as it does things on all columns but returns
only 1 result.
Every other field you select in the query has to be also in an aggreate
function OR has to be grouped.

e.g.:

given the following table tempo:

id | num | txt
---------------
1  | 16  | a
2  | 23  | a
3  | 25  | b
4  | 25  | b

SELECT max(num) FROM tempo;

gives exactly 1 result:
25

SELECT max(num), txt FROM tempo;
is not possible.

possible would be e.g.
SELECT max(num), avg(num) FROM tempo;
=> 25,22.25

OR if you want the maximum of a GROUP:

SELECT max(num), txt FROM tempo GROUP BY txt;
23,a
25,b

So, as Bruno already stated :), it is not possible to have non-grouped
queries with aggregates and non-aggregates mixed without duplicating the
query (e.g. by a subselect).

BTW: If you want aggregate functions in the WHERE clause you need a
HAVING clause.

HTH
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de