I have a need to have an auto increasing field, but I don't think I can use
the serial type.

I have a table similar to:

Jobs
====

Id (bigserial/unique)
Name(varchar)
Companyid(bigint)
Jobno(bigint)

Primary Key: name/companyid

Now what I want is to increase the job number by 1 every time I do an
insert. But it must effectively be a sequence for each companyid in the
table for example:

1/a job/1/1
2/another job/1/2
3/a job/2/1
4/something else/1/3
5/yet one more/2/2

There shouldn't be any gaps in the sequence jobno sequence either. Is there
an easy way to do this?

I thought of doing:

SELECT max(jobno) from jobs where companyid=1;

Then use the result as the job number, is there a way I can lock the table
to do this or a better way of achieving the required result.

Thanks

Jake


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

On 9/9/04 4:52 pm, "bugant" <bugant (AT) opinioni (DOT) net> wrote:

I don't want lots of sequences, but how best would I go about locking the
table?

Thanks

Jake


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Jake Stride writes:

I think SELECT FOR UPDATE should work fine here.

See: http://www.postgresql.org/docs/7.4/static/mvcc.html

HTH
Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Andreas Seltenreich <seltenreich (AT) gmx (DOT) de> writes:
Nope; he'll get something like

regression=# select max(unique1) from tenk1 for update;
ERROR: SELECT FOR UPDATE is not allowed with aggregate functions

His best bet is probably

BEGIN;
LOCK TABLE jobs;
SELECT max(jobno) from jobs where companyid=1;
INSERT INTO jobs ...
COMMIT;

This is pretty horrid from a concurrency point of view but I don't think
there's any other way to meet the "no gaps" requirement.

You could reduce the strength of the lock a bit, for instance
LOCK TABLE jobs IN EXCLUSIVE MODE;
which would allow readers of the jobs table to proceed concurrently,
but not writers. If you were willing to assume that all inserters into
jobs are cooperating by explicitly obtaining the correct lock, you
could reduce it to
LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
which is the lowest self-conflicting table lock type. This would allow
unrelated updates to the jobs table to proceed concurrently too (though
not VACUUMs). See

http://www.postgresql.org/docs/7.4/s...t-locking.html

regards, tom lane

---------------------------(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

--- Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> escribió:
Hi,
Talking about lock tables there is a way to do a
select ... for update
and then a
update .. where current of ...

I think it require a select for update in a cursor.

Thanx in advance,
Jaime Casanova

__________________________________________________ _______
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

---------------------------(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

Tom Lane wrote:

I saw.
Now - as a beginner in the transaction-locking stuff - I'm wondering
about the last paragraph.
"So long as no deadlock situation is detected, a transaction seeking
either a table-level or row-level lock will wait indefinitely for
conflicting locks to be released. This means it is a bad idea for
applications to hold transactions open for long periods of time (e.g.,
while waiting for user input)."

I'm working on an MsAccess application that uses PG as backend. Up until
recently I didn't bother for cuncurrency issues but I like to improve
things now that PG manages the data.

For this application I read records into forms and subforms.

If - by accident - 2 users open one record, say customer #123, and I
start a transaction on opening the form and SELECT ... FOR UPDATE to
read data, then the second user's application stalls until the first
COMMITs.

With SELECT without FOR UPDATE the UPDATEs of the first committing
transaction get overwritten.

I can't predict how long a user lets open a form. I just need to protect
the displayd data against updates in the meantime.

This must be a common problem regardless what client software is used.
Are there some guidelines on locks for forms ?


Regards
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly