Re: sequence's plpgsql

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 single message 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 do not 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