Sequence in Session

#1

Hi,

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

Thanks for helping

Phil

#2

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#3

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#4

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#5

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#6

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#7

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#8

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#9

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:

Quote:

I would like to insert some data series (aroung 60.000 values / series).
My table have this columns: master_id, series_id, position, value (all ints)
Now I would like to create two sequences, first series_id_seq and
second position_seq.
The position sequence must reset when the series sequence increases,
the series sequence must
then reset if the create a new master_id.

In my code I would like to do this:

loStatament("reset series seq");
for int n=0; n < series.count; n++) {
nSeries = loStatement("select nextval(series seq)");
loStatament("reset position seq");
for int i=0; i < series[n].size; i++) {
loStatement("insert into (master_id, series_id, value)
values(?,?,?)");
loStatement.setInt(1, master_id);
loStatament.setInt(2, nSeries);
loStatament.setint(3, series[n].getValue(i));
loStatament.executeQuery();
}
}

The PK of the table is: master_id + series_id + position. The position
id will create by the insert trigger.
Is there any problem with shared connection (I know the sequences are
used in the session).
The position and series sequence must increase continuously for the
master id. Is it also a good way
to do this with sequences or should I create the numbers on my client
application?

When does "master_id" or "series_id" change to the new value?
After a new series_id has been created, will there no more INSERTs for
older series_ids?

Do you generate all IDs yourself or are they given to you?

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.
If you want "no holes", ask yourself why and try to come up with a solution
for what should happen if somebody deletes a record.

It may well be that sequences cannot solve your problem; you can either
resort to other data structures (tables?) or use client code, but the latter
has the problem that you'll get problems with concurrent clients.

Yours,
Laurenz Albe

#10

On 2008-10-13 12:09:15 +0200, Laurenz Albe <invite (AT) spam (DOT) to.invalid> said:

Quote:

When does "master_id" or "series_id" change to the new value?

master_id becomes the value von another table (PK with serial)
series_id becomes the new value if it is created (insert on series table)

Quote:

After a new series_id has been created, will there no more INSERTs for
older series_ids?

yes. if the series is created, the user (the application) can only read
the data or
delete the whole series.

Quote:

Do you generate all IDs yourself or are they given to you?

master_id, series_id will created by the Postgres Serial. At this time
it runs fine. In master_id and series_id can be "holes".

Quote:

I hope that with "must increase continuously" you do not mean that there
may not be any "holes" in the sequence, just that it should increase.

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.

In my way I will create one transaction with a stored procedure. The proc.
will start and get a new series id, call the insert on my series table.
Then I can
insert the series data values.

Now I see a problem:
How can I guarantee that the series data values become countinuously values
within the series? The numbering must start at 1 and ended by n (n =
count of elements).
On the next series it must start also with 1 to n.

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?

Thanks

Phil