 | |
#21
 
| |||
| |||
|
Re: Sequence in Session -
10-13-2008
, 06:53 AM
|
My series data (that a new table with 1:n series -?> series_data) must not have any holes. The series data values must be continuously. |
Why does it have to be that way? Just because somebody says so or is there
a technical reason?
Quote:
|
My reflection: Every call is within my actually session, that means if I call my stored procedure, create my series id, i will get a new value without collision, than I insert my data without using a sequence (I will use a counter variable). Can be run this code in problems? I think it doesn't, because I can create a series completly or not and if the series is created all series data have a continuiusly numbering. Is that right? Is this a good way or can I do it better? |
This is unfortunate as we seem to have the same mother tongue but have to
use English here.
You could join the German mailing list "pgsql-de-allgemein" on
http://www.postgresql.org/community/lists/subscribe and send your
questions in German. That might work better.
If you insert all the members of a "continuous" series in one go from
one client it should not be hard to create a numbering without "holes".
But if values are added over time and/or from different clients, it will
be hard. Imagine that a client gets the next sequence number, but dies
from a hardware problem before it can INSERT. Imagine that the INSERT
fails because of constraint violation. Imagine somebody deletes a row
from the table.
The "best" solution in this case is something like lock the table, get
the maximum ID, add one and use that. That is of course terrible as it
will result in very poor performance (lots of rows scanned for every
insert) and no write concurrency at all (table lock).
Usually it is the best to allow holes in the sequence.
Yours,
Laurenz Albe
#22
| |||
| |||
|
|
My series data (that a new table with 1:n series -?> series_data) must not have any holes. The series data values must be continuously. |
Why does it have to be that way? Just because somebody says so or is there
a technical reason?
Quote:
|
My reflection: Every call is within my actually session, that means if I call my stored procedure, create my series id, i will get a new value without collision, than I insert my data without using a sequence (I will use a counter variable). Can be run this code in problems? I think it doesn't, because I can create a series completly or not and if the series is created all series data have a continuiusly numbering. Is that right? Is this a good way or can I do it better? |
This is unfortunate as we seem to have the same mother tongue but have to
use English here.
You could join the German mailing list "pgsql-de-allgemein" on
http://www.postgresql.org/community/lists/subscribe and send your
questions in German. That might work better.
If you insert all the members of a "continuous" series in one go from
one client it should not be hard to create a numbering without "holes".
But if values are added over time and/or from different clients, it will
be hard. Imagine that a client gets the next sequence number, but dies
from a hardware problem before it can INSERT. Imagine that the INSERT
fails because of constraint violation. Imagine somebody deletes a row
from the table.
The "best" solution in this case is something like lock the table, get
the maximum ID, add one and use that. That is of course terrible as it
will result in very poor performance (lots of rows scanned for every
insert) and no write concurrency at all (table lock).
Usually it is the best to allow holes in the sequence.
Yours,
Laurenz Albe
#23
| |||
| |||
|
|
My series data (that a new table with 1:n series -?> series_data) must not have any holes. The series data values must be continuously. |
Why does it have to be that way? Just because somebody says so or is there
a technical reason?
Quote:
|
My reflection: Every call is within my actually session, that means if I call my stored procedure, create my series id, i will get a new value without collision, than I insert my data without using a sequence (I will use a counter variable). Can be run this code in problems? I think it doesn't, because I can create a series completly or not and if the series is created all series data have a continuiusly numbering. Is that right? Is this a good way or can I do it better? |
This is unfortunate as we seem to have the same mother tongue but have to
use English here.
You could join the German mailing list "pgsql-de-allgemein" on
http://www.postgresql.org/community/lists/subscribe and send your
questions in German. That might work better.
If you insert all the members of a "continuous" series in one go from
one client it should not be hard to create a numbering without "holes".
But if values are added over time and/or from different clients, it will
be hard. Imagine that a client gets the next sequence number, but dies
from a hardware problem before it can INSERT. Imagine that the INSERT
fails because of constraint violation. Imagine somebody deletes a row
from the table.
The "best" solution in this case is something like lock the table, get
the maximum ID, add one and use that. That is of course terrible as it
will result in very poor performance (lots of rows scanned for every
insert) and no write concurrency at all (table lock).
Usually it is the best to allow holes in the sequence.
Yours,
Laurenz Albe
#24
| |||
| |||
|
|
My series data (that a new table with 1:n series -?> series_data) must not have any holes. The series data values must be continuously. |
Why does it have to be that way? Just because somebody says so or is there
a technical reason?
Quote:
|
My reflection: Every call is within my actually session, that means if I call my stored procedure, create my series id, i will get a new value without collision, than I insert my data without using a sequence (I will use a counter variable). Can be run this code in problems? I think it doesn't, because I can create a series completly or not and if the series is created all series data have a continuiusly numbering. Is that right? Is this a good way or can I do it better? |
This is unfortunate as we seem to have the same mother tongue but have to
use English here.
You could join the German mailing list "pgsql-de-allgemein" on
http://www.postgresql.org/community/lists/subscribe and send your
questions in German. That might work better.
If you insert all the members of a "continuous" series in one go from
one client it should not be hard to create a numbering without "holes".
But if values are added over time and/or from different clients, it will
be hard. Imagine that a client gets the next sequence number, but dies
from a hardware problem before it can INSERT. Imagine that the INSERT
fails because of constraint violation. Imagine somebody deletes a row
from the table.
The "best" solution in this case is something like lock the table, get
the maximum ID, add one and use that. That is of course terrible as it
will result in very poor performance (lots of rows scanned for every
insert) and no write concurrency at all (table lock).
Usually it is the best to allow holes in the sequence.
Yours,
Laurenz Albe
#25
| |||
| |||
|
|
My series data (that a new table with 1:n series -?> series_data) must not have any holes. The series data values must be continuously. |
Why does it have to be that way? Just because somebody says so or is there
a technical reason?
Quote:
|
My reflection: Every call is within my actually session, that means if I call my stored procedure, create my series id, i will get a new value without collision, than I insert my data without using a sequence (I will use a counter variable). Can be run this code in problems? I think it doesn't, because I can create a series completly or not and if the series is created all series data have a continuiusly numbering. Is that right? Is this a good way or can I do it better? |
This is unfortunate as we seem to have the same mother tongue but have to
use English here.
You could join the German mailing list "pgsql-de-allgemein" on
http://www.postgresql.org/community/lists/subscribe and send your
questions in German. That might work better.
If you insert all the members of a "continuous" series in one go from
one client it should not be hard to create a numbering without "holes".
But if values are added over time and/or from different clients, it will
be hard. Imagine that a client gets the next sequence number, but dies
from a hardware problem before it can INSERT. Imagine that the INSERT
fails because of constraint violation. Imagine somebody deletes a row
from the table.
The "best" solution in this case is something like lock the table, get
the maximum ID, add one and use that. That is of course terrible as it
will result in very poor performance (lots of rows scanned for every
insert) and no write concurrency at all (table lock).
Usually it is the best to allow holes in the sequence.
Yours,
Laurenz Albe
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.