On Tue, 2003-07-01 at 13:33, Tom Lane wrote: > Erik Erkelens <[EMAIL PROTECTED]> writes: > > DECLARE > > new_max_records ALIAS FOR $1; > > BEGIN > > CREATE SEQUENCE my_sequence MAXVALUE new_max_records CYCLE; > > > ERROR: parser: parse error at or near "$1" at character 39 > > You'll need to use EXECUTE to construct and execute that CREATE > SEQUENCE. Utility statements generally don't accept runtime parameters, > which is what the plpgsql variable looks like to the main parser. > > > Also, if there is a better mechanism to implement > > this, I'm all ears... > > There's an ALTER SEQUENCE command in CVS tip, though I'm not sure > I trust it in concurrent-usage scenarios :-(
It shouldn't be trusted anymore than setval() should be. That is, changes take place immediately. Seems to me you might be better off just creating a 'count' table. Update the single row when it changes. By dropping / recreating the sequence you've already blocked concurrent transactions. The single row would have less to vacuum, where the sequence has quite a bit more. Another alternative is to use setval() on the sequence BUT first pull a FOR UPDATE lock on some blocking row (for concurrency reasons). SELECT * FROM pg_class WHERE relname = 'sequence name' FOR UPDATE; SELECT setval(<max number>); This would work equally well with ALTER SEQUENCE in 7.4. -- Rod Taylor <[EMAIL PROTECTED]> PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
