Thread: sequence's plpgsql
Hi,
I've hit a little problem and was wondering if anyone might be able to
give some help.
Set-up:
- JBoss appserver using entity beans to access database
- Using sequence pattern to update primary keys for several tables.
Basically it is a method of getting primary keys without hitting the
database. It actually hits the database every 10th go and updates the
counter by 10 in the database.
(now the bold bit)
- We have a stored procedure that actually updates one of the sequences
as well. It increments one at a time using this code:
SELECT INTO NewPK pkIndex FROM PrimaryKeyGenerator WHERE Name =
SequenceName FOR UPDATE;
UPDATE PrimaryKeyGenerator SET pkIndex = NewPK + 1 WHERE Name =
SequenceName;
I believe the "FOR UPDATE" won't actually do any good inside a plpgsql
call. Am I right?
Problem:
I have just called this stored procedure from outside the system using
10 threads and have got some errors due to duplicate entries on the
unique index. If it was only the stored procedures using this pk
generator then I could use a postgresql sequence but it isn't. If the
entity beans were to use the sequence, they'd have to make a database
call every time.
Any thoughts?
I'm thinking I may need to switch to using a sequence because the entity
beans don't actually update this particular table very often but would
prefer not to for portability reasons.
Thanks,
Tim
On Wednesday 24 September 2003 17:40, Tim McAuley wrote:
> Hi,
>
> I've hit a little problem and was wondering if anyone might be able to
> give some help.
>
> Set-up:
>
> - JBoss appserver using entity beans to access database
> - Using sequence pattern to update primary keys for several tables.
> Basically it is a method of getting primary keys without hitting the
> database. It actually hits the database every 10th go and updates the
> counter by 10 in the database.
You might want to try just using sequences - PG does some caching of generated
values for you. Sorry - can't remember how you alter the cache size, but try
SELECT * FROM my_sequence;
to see the details of its settings.
Use sequences, and from your sequence-holding class do something like:
SELECT nextval('myseq'),nextval('myseq'),...10 times...
That will give you a block of 10 sequence values in one go, and off you go.
If you'd rather have the values in one column, create a single-column table
"seq_count" and populate with values 1..10 then:
SELECT nextval('myseq'),seq_count.id FROM seq_count;
That any use?
--
Richard Huxton
Archonet Ltd
Richard Huxton wrote:
>On Wednesday 24 September 2003 17:40, Tim McAuley wrote:
>
>
>>Hi,
>>
>>I've hit a little problem and was wondering if anyone might be able to
>>give some help.
>>
>>Set-up:
>>
>>- JBoss appserver using entity beans to access database
>>- Using sequence pattern to update primary keys for several tables.
>>Basically it is a method of getting primary keys without hitting the
>>database. It actually hits the database every 10th go and updates the
>>counter by 10 in the database.
>>
>>
>
>You might want to try just using sequences - PG does some caching of generated
>values for you. Sorry - can't remember how you alter the cache size, but try
> SELECT * FROM my_sequence;
>to see the details of its settings.
>
>Use sequences, and from your sequence-holding class do something like:
> SELECT nextval('myseq'),nextval('myseq'),...10 times...
>That will give you a block of 10 sequence values in one go, and off you go.
>
>If you'd rather have the values in one column, create a single-column table
>"seq_count" and populate with values 1..10 then:
>SELECT nextval('myseq'),seq_count.id FROM seq_count;
>
>That any use?
>
>
Thanks for that. I investigated using your suggestion but actually
discovered that these tables were only being updated by the stored
procedures so this meant it was safe to use sequences there. I have now
set these up however I am now getting "deadlock detection" errors.
I see from a previous email on the list that someone was able to get
decent debug out of the locks, i.e.
Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by
How can I get this? I must be overlooking something because I've set the debug level to debug5 and still only get a
singlemessage saying
ERROR: deadlock detected
DEBUG: AbortCurrentTransaction
I've switched from using Postgresql 7.3.2 on a linux server to 7.3.4 running on my own machine through cygwin.
I've got output from "select * from pg_locks;" but am not getting very far with this. The only locks marked as false
donot give a table oid, only the transaction id. i.e.
| | 11515901 | 30440 | ShareLock | f
Will continue working away to see if I can locate the deadlock.
Tim
Tim McAuley <mcauleyt@tcd.ie> writes:
> I see from a previous email on the list that someone was able to get
> decent debug out of the locks, i.e.
> Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected
> Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
> for AccessExclusiveLock on relation 18028 of database 17140; blocked by
> How can I get this?
This display is a new feature in 7.4. Can you try your problem on a
7.4 beta release?
regards, tom lane
>>I see from a previous email on the list that someone was able to get >>decent debug out of the locks, i.e. >> >>Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected >>Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits >>for AccessExclusiveLock on relation 18028 of database 17140; blocked by >> >> >>How can I get this? >> >> > >This display is a new feature in 7.4. Can you try your problem on a >7.4 beta release? > > > Ah, that would make sense. Another question now. I am unable to compile Postgresql 7.4 beta 3 under cygwin (Windows 2K, using cgyipc 2). I am getting the error: " creating information schema... ERROR: end-of-copy marker does not match previous newline style CONTEXT: COPY FROM, line 361 " Any ideas? Thanks, Tim