pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNew to SQL; hopefully simple question
3+ messages / 2 participants
[nested] [flat]
* New to SQL; hopefully simple question
@ 2003-04-18 22:58 foo2@on-spammers2.com
2003-04-21 04:28 ` Re: New to SQL; hopefully simple question foo2@on-spammers2.com
2003-04-23 17:40 ` Re: New to SQL; hopefully simple question Rod Taylor <rbt@rbt.ca>
0 siblings, 2 replies; 3+ messages in thread
From: foo2@on-spammers2.com @ 2003-04-18 22:58 UTC (permalink / raw)
To: pgsql-sql
I have a situation where I want to keep a bunch of ordinals in a row
of a table maintained by the database. From this table, I want to
obtain a number, and increment it in the table. This is the general
idea, abstracted to the key parts (and it works, as far as it goes:
"select valueone from ordtable"
VALUE = PQgetvalue(res,0,0)
...compute VALUE = VALUE + 1
"update ordtable SET valueone = VALUE"
...as I said, this works. however, as far as I understand it, it's
divisible and thus subject to multi-user fugue. What I need,
conceptually, is:
lock the table so anyone else waits for the unlock (not fails, waits)
do the above
unlock the table
Can anyone point me in the right direction for this? I looked at lock,
but it seems to only apply across one DB action. Yes? No?
Thanks!
Walt
Walt
Software Engineer
Black Belt Systems
pages: http://www.blackbeltsystems.com/
email: http://www.blackbeltsystems.com/contact.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: New to SQL; hopefully simple question
2003-04-18 22:58 New to SQL; hopefully simple question foo2@on-spammers2.com
@ 2003-04-21 04:28 ` foo2@on-spammers2.com
1 sibling, 0 replies; 3+ messages in thread
From: foo2@on-spammers2.com @ 2003-04-21 04:28 UTC (permalink / raw)
To: pgsql-sql
Ok, for anyone else who has run into this, the answer seems to be:
CREATE SEQUENCE name START integer
The docs for CREATE SEQUENCE cover the bare bones of what to do
with/to them.
The mailing-list subscribe thingee on the postgeSQL page seems to be
broken, also. For anyone who cares.
Walt
Software Engineer
Black Belt Systems
pages: http://www.blackbeltsystems.com/
email: http://www.blackbeltsystems.com/contact.html
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: New to SQL; hopefully simple question
2003-04-18 22:58 New to SQL; hopefully simple question foo2@on-spammers2.com
@ 2003-04-23 17:40 ` Rod Taylor <rbt@rbt.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Rod Taylor @ 2003-04-23 17:40 UTC (permalink / raw)
To: the2@newsgroup2.com; +Cc: pgsql-sql
On Fri, 2003-04-18 at 18:58, foo2@on-spammers2.com wrote:
> I have a situation where I want to keep a bunch of ordinals in a row
> of a table maintained by the database. From this table, I want to
> obtain a number, and increment it in the table. This is the general
> idea, abstracted to the key parts (and it works, as far as it goes:
>
> "select valueone from ordtable"
> VALUE = PQgetvalue(res,0,0)
> ...compute VALUE = VALUE + 1
> "update ordtable SET valueone = VALUE"
>
> ...as I said, this works. however, as far as I understand it, it's
> divisible and thus subject to multi-user fugue. What I need,
> conceptually, is:
>
> lock the table so anyone else waits for the unlock (not fails, waits)
> do the above
> unlock the table
>
> Can anyone point me in the right direction for this? I looked at lock,
> but it seems to only apply across one DB action. Yes? No?
No.. table locks will persist for the length of the transaction.
BEGIN;
LOCK TABLE ...;
SELECT ...
<computations>
UPDATE ...
COMMIT;
However, does your update really change the entire table? If you are
updating the same row as you selected (same WHERE clause in SELECT and
UPDATE statements) you can:
BEGIN;
SELECT ... WHERE ... FOR UPDATE OF ordtable;
<computations>
UPDATE ... WHERE ...;
COMMIT;
The above locks only the rows you intend to work with rather than the
entire table. This can allow work to happen on the unlocked
(non-selected) rows on the table.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-23 17:40 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-18 22:58 New to SQL; hopefully simple question foo2@on-spammers2.com
2003-04-21 04:28 ` foo2@on-spammers2.com
2003-04-23 17:40 ` Rod Taylor <rbt@rbt.ca>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox