每年重新开始增量为0 =重新发明序列机制？ [英] Restart increment to 0 each year = re-invent the sequences mecanism?
问题描述
您好，
我必须设计一个能存储一些行动报告的表格。每个
报告的ID都是这个1/2004,2 / 2004，......每年，它们都会重新启动到1（1/2004,1 / 2005） ，1/2006，...）。
所以，我想把它分成两个字段：一个带增量
和一个带有年。但我不知道如何处理序列
因为我每年必须重新启动到0 ...
你觉得我应该吗？用第二个
表和存储过程重新发明序列机制，只是为了管理这些''自制''序列？
或者应该我创建了一些像myseq_2004，myseq_2004，
my_seq_2005，...的序列，并在调用nextval和curvall时使用myseq_字符串和
当前年份的串联？ />
或者还有另一种方法吗？
提前致谢:-)
- -
Bruno Baguette - pg******@baguette.net
---------------------------（广播结束）--------- ------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
订阅 - nomail命令 ma*******@postgresql.org 这样你的
消息就可以干净利落地进入邮件列表
Bruno Baguette说：< blockquote class =post_quotes>我必须设计一个能存储一些行动报告的表格。每个
报告都有一个ID，如1/2004,2 / 2004，......每年，他们重启到1（1/2004年，1/2005年，1/2006年，... ）。
所以，我想把它分成两个领域：一个是增量
，另一个是年份。但我不知道我怎么能管理这些序列
因为我每年都要重新开始...
你认为我应该用一秒钟重新发明序列机制吗？
表和存储过程，只管理这些''自制'序列
或者我应该创建一些像myseq_2004，myseq_2004，
这样的序列my_seq_2005，...并且在调用nextval和curvall时使用myseq_ string和
当前年份的串联？
在可用的选项中，我会使用序列解决方案;每年一个序列
对象。
您可以在存储过程中按照以下行执行某些操作
（可能用于创建报告记录）：
....
- 将年份值传递给程序
- 或确定当前年份的年份部分
yearNumberTxt =''2004'';
- 获取当年的下一个ID
选择nextval（yearNumberTxt）到seqNum;
- 并生成你的报告编号
reportID：= seqNum || ''/''yearNumberTxt;
- 使用新的报告ID插入您的ReportTable
....
如果您使用额外的表和自己管理递增字段（你的其他建议），然后你需要知道
访问和更新计数器（年份）时的并发问题。你需要在一个函数中锁定
行，该函数生成一年中的下一个数字，这个
将阻止任何其他想要同时处理数字的处理。
当然，如果你不经常创建报告，或者同时创建报告，那么
不是问题。
顺序解决方案不会阻止，但如果在您发出下一个函数后，由于某种原因无法插入记录，则可能会在您的编号中留下空白吗？
您的编号顺序对象。
John Sidney-Woollett
------------------- --------（广播结束）---------------------------
提示8 ：解释分析是你的朋友
Bruno Baguette说：我必须设计一个表格，它会存储一些行动报告。每个
报告都有一个ID，如1/2004,2 / 2004，......每年，他们重启到1（1/2004年，1/2005年，1/2006年，... ）。
所以，我想把它分成两个领域：一个是增量
，另一个是年份。但我不知道我怎么能管理这些序列
因为我每年都要重新开始...
你认为我应该用一秒钟重新发明序列机制吗？
表和存储过程，只管理这些''自制'序列
或者我应该创建一些像myseq_2004，myseq_2004，
这样的序列my_seq_2005，...并且在调用nextval和curvall时使用myseq_ string和
当前年份的串联？
在可用的选项中，我会使用序列解决方案;每年一个序列
对象。
您可以在存储过程中按照以下行执行某些操作
（可能用于创建报告记录）：
....
- 将年份值传递给程序
- 或确定当前年份的年份部分
yearNumberTxt =''2004'';
- 获取当年的下一个ID
选择nextval（yearNumberTxt）到seqNum;
- 并生成你的报告编号
reportID：= seqNum || ''/''yearNumberTxt;
- 使用新的报告ID插入您的ReportTable
....
如果您使用额外的表和自己管理递增字段（你的其他建议），然后你需要知道
访问和更新计数器（年份）时的并发问题。你需要在一个函数中锁定
行，该函数生成一年中的下一个数字，这个
将阻止任何其他想要同时处理数字的处理。
当然，如果你不经常创建报告，或者同时创建报告，那么
不是问题。
顺序解决方案不会阻止，但如果在您发出下一个函数后，由于某种原因无法插入记录，则可能会在您的编号中留下空白吗？
您的编号顺序对象。
John Sidney-Woollett
------------------- --------（广播结束）---------------------------
提示8 ：解释分析是你的朋友
你不必乱用序列。
如果有的话是两个字段ID和年份，然后下一个数字是：
next_number：=（从table_name中选择ID，其中year = year_from_current_date
按ID desc limit 1排序）+ 1;
问候，
Clodoaldo
--- Bruno Baguette< pg *** ***@baguette.net> escreveu：>你好，
我必须设计一个能存储一些行动报告的表格。每个
报告都有一个ID，如1/2004,2 / 2004，......每年，他们重启到1（1/2004年，1/2005年，1/2006年，... ）。
所以，我想把它分成两个领域：一个是增量
，另一个是年份。但是我不知道怎样才能管理序列
因为每年我必须重新开始0 ...
你认为我应该重新发明序列机制吗？第二个表和一个存储过程，只管理这些''自制'序列？
或者我应该创建一些像myseq_2004，myseq_2004，
my_seq_2005这样的序列。 ..并且在调用nextval和curvall时使用myseq_ string和
当前年份的串联？
或者还有其他方法可以做到这一点吗？
谢谢提前:-)
- Bruno Baguette - pg ****** @ baguette.net
---------------------------（播出结束）--- ------------------------
提示3：如果通过Usenet发布/阅读，请发送适当的
subscribe-nomail命令 ma*******@postgresql.org ，以便您的
消息可以通过邮寄l ist cleanly
__________________________________________________ ____________________
Yahoo! Messenger - Fale com seus amigos online。 Instale agora！
http://br.download.yahoo.com/信使/
---------------------------（播出结束） ---------------------------
提示9：计划员将忽略您选择索引扫描的愿望你的
加入列的数据类型不匹配
Hello,
I have to design a table wich will store some action reports. Each
report have an ID like this 1/2004, 2/2004, ... and each years, they
restart to 1 (1/2004, 1/2005, 1/2006,...).
So, I was thinking to split that in two fields : one with the increment
and one with the year. But I don''t know how can I manage the sequences
since I have to restart to 0 each year...
Do you think I should re-invent the sequences mecanism with a second
table and a stored procedure, only to manage theses ''home-made'' sequences ?
Or should I create some sequences like myseq_2004, myseq_2004,
my_seq_2005,... and use a concatenation of the myseq_ string and the
current year when calling nextval and curvall ?
Or is there another way to do that ?
Thanks in advance :-)
--
Bruno Baguette - pg******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
Bruno Baguette said:I have to design a table wich will store some action reports. Each
report have an ID like this 1/2004, 2/2004, ... and each years, they
restart to 1 (1/2004, 1/2005, 1/2006,...).
So, I was thinking to split that in two fields : one with the increment
and one with the year. But I don''t know how can I manage the sequences
since I have to restart to each year...
Do you think I should re-invent the sequences mecanism with a second
table and a stored procedure, only to manage theses ''home-made'' sequences
?
Or should I create some sequences like myseq_2004, myseq_2004,
my_seq_2005,... and use a concatenation of the myseq_ string and the
current year when calling nextval and curvall ?
Of the options available, I would use the sequence solution; one sequence
object for each year.
You can do something along the following lines in a stored procedure
(which could be used to create the report records):
....
-- pass in the year value to the procedure
-- or determine year from year part of current date
yearNumberTxt = ''2004'';
-- get the next ID for the year
select nextval(yearNumberTxt) into seqNum;
-- and generate your report number
reportID := seqNum || ''/'' yearNumberTxt;
-- insert into your ReportTable using new report ID
....
If you use an extra table and manage the incrementing field yourself (your
other suggestion), then you need to be aware of concurrency issues when
accessing and updating the counter (for the year). You need to lock the
row in a function which generates the next number for the year, and this
will block any other processing wanting a number at the same time. Of
course if you don''t create reports frequently, or concurrently then this
isn''t an issue.
The sequence solution will not block, but could leave you with gaps in
your numbering if a record fails to insert for some reason after you issue
the nextval function on the sequence object.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Bruno Baguette said:I have to design a table wich will store some action reports. Each
report have an ID like this 1/2004, 2/2004, ... and each years, they
restart to 1 (1/2004, 1/2005, 1/2006,...).
So, I was thinking to split that in two fields : one with the increment
and one with the year. But I don''t know how can I manage the sequences
since I have to restart to each year...
Do you think I should re-invent the sequences mecanism with a second
table and a stored procedure, only to manage theses ''home-made'' sequences
?
Or should I create some sequences like myseq_2004, myseq_2004,
my_seq_2005,... and use a concatenation of the myseq_ string and the
current year when calling nextval and curvall ?
Of the options available, I would use the sequence solution; one sequence
object for each year.
You can do something along the following lines in a stored procedure
(which could be used to create the report records):
....
-- pass in the year value to the procedure
-- or determine year from year part of current date
yearNumberTxt = ''2004'';
-- get the next ID for the year
select nextval(yearNumberTxt) into seqNum;
-- and generate your report number
reportID := seqNum || ''/'' yearNumberTxt;
-- insert into your ReportTable using new report ID
....
If you use an extra table and manage the incrementing field yourself (your
other suggestion), then you need to be aware of concurrency issues when
accessing and updating the counter (for the year). You need to lock the
row in a function which generates the next number for the year, and this
will block any other processing wanting a number at the same time. Of
course if you don''t create reports frequently, or concurrently then this
isn''t an issue.
The sequence solution will not block, but could leave you with gaps in
your numbering if a record fails to insert for some reason after you issue
the nextval function on the sequence object.
John Sidney-Woollett
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
You don''t have to mess with sequences.
If there are two fields ID and year then the next number is:
next_number := ( select ID from table_name where year = year_from_current_date
order by ID desc limit 1 ) + 1;
Regards,
Clodoaldo
--- Bruno Baguette <pg******@baguette.net> escreveu: > Hello,
I have to design a table wich will store some action reports. Each
report have an ID like this 1/2004, 2/2004, ... and each years, they
restart to 1 (1/2004, 1/2005, 1/2006,...).
So, I was thinking to split that in two fields : one with the increment
and one with the year. But I don''t know how can I manage the sequences
since I have to restart to 0 each year...
Do you think I should re-invent the sequences mecanism with a second
table and a stored procedure, only to manage theses ''home-made'' sequences ?
Or should I create some sequences like myseq_2004, myseq_2004,
my_seq_2005,... and use a concatenation of the myseq_ string and the
current year when calling nextval and curvall ?
Or is there another way to do that ?
Thanks in advance :-)
--
Bruno Baguette - pg******@baguette.net
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
__________________________________________________ ____________________
Yahoo! Messenger - Fale com seus amigos online. Instale agora!
http://br.download.yahoo.com/messenger/
---------------------------(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
这篇关于每年重新开始增量为0 =重新发明序列机制？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
