Sequences/defaults and pg_dump

Maybe it was discussed already, but I think it's very strange behavior
and things should be changed (please correct me if I'm wrong)

Suppose we have database containing only one simple table:

***
template1=# CREATE DATABASE testseq;
template1=# \c testseq
testseq=# CREATE TABLE test(id SERIAL, data TEXT);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
CREATE TABLE
***

Look at the table 'test':
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass))
data | text |
***

So, if we don't know the history we cannot understand that id is of
type SERIAL. We think as it's INTEGER with DEFAULT
'nextval('test_id_seq'::regclass)' [as expression]

This is the question #1 - how I can distinguish pure SERIAL and
INTEGER with corresponding DEFAULT setting?

Then... Imagine that we should use sequence in some other manner. For example:
***
ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
***
All is OK:
***
testseq=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+----------------------------------------------------------
id | integer | not null default (nextval('test_id_seq'::regclass) * 10)
data | text |
***
... and it works as is supposed to do.

But after simple dump&restore procedure (I use 'pg_dump -U ns testseq
> ~/testseq.dump' and then in psql - '\i /home/ns/testseq.dump') we
have:
***
testseq=# \d test;
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
data | text |
***
In dump file I see SERIAL as the type for test.id ...
So, the question #2 (the main Q): why pg_dump didn't dump my expression?

For me as end-user this is very-very strange and I consider it as
'gotcha' of PosgreSQL.

--
Best regards,
Nikolay

On Tue, Feb 07, 2006 at 02:33:56PM +0300, Nikolay Samokhvalov wrote:
> Maybe it was discussed already, but I think it's very strange behavior
> and things should be changed (please correct me if I'm wrong)
>
> Suppose we have database containing only one simple table:

<snip>

> So, if we don't know the history we cannot understand that id is of
> type SERIAL. We think as it's INTEGER with DEFAULT
> 'nextval('test_id_seq'::regclass)' [as expression]
>
> This is the question #1 - how I can distinguish pure SERIAL and
> INTEGER with corresponding DEFAULT setting?

You can't because there is no difference. SERIAL is just a shortcut. If
there is no difference, why would you want to distinguish them?

> Then... Imagine that we should use sequence in some other manner. For example:
> ***
> ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;

<snip>

> In dump file I see SERIAL as the type for test.id ...
> So, the question #2 (the main Q): why pg_dump didn't dump my expression?

Well, it's a very contrived example (I can't think of a reason why one
would do that) but I agree it is a bug. You could acheive the same
effect by setting the step of the sequence to 10.

> For me as end-user this is very-very strange and I consider it as
> 'gotcha' of PosgreSQL.

Well, I would hardly call the latter a gotcha, given you're probably
the first person to notice it. As for the first question, I'm not sure
what you expect. SERIAL has always just been a sort of macro, so I
don't see how this could be changed.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

On 2/7/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Tue, Feb 07, 2006 at 02:33:56PM +0300, Nikolay Samokhvalov wrote:
<snip>
> Well, it's a very contrived example (I can't think of a reason why one
> would do that) but I agree it is a bug. You could acheive the same
> effect by setting the step of the sequence to 10.

'* 10' is just an example.
The real situation would be as the following.
I want to use some algorithm to hide real number of registered users
in my table user. So, I don't want to use simple sequence, when every
new registered user in my system can guess what is the number of
registered users simply observing his ID. So, I use following
algorithm:
(nextval('...name of the sequnence...') * N) mod % M,
where N and M are quite big numbers that have no common multiples
besides 1 (sorry, do not remember the English term for those numbers
;-) ).

> > For me as end-user this is very-very strange and I consider it as
> > 'gotcha' of PosgreSQL.
>
> Well, I would hardly call the latter a gotcha, given you're probably
> the first person to notice it. As for the first question, I'm not sure
> what you expect. SERIAL has always just been a sort of macro, so I
> don't see how this could be changed.

I wonder why people didn't notice this bug earlier, but I'm sure that
there are many situations when it could be revealed. For example, what
if I want to use nextval('seq1') * nextval('seq2') ?.. I'm sure that
if you think you'll discover new examples that would be used in real
world.

Anyway, this is a bug, and I'll write the bug report to bugs mailing list.

>
> Have a nice day,
> --
> Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFD6IsBIB7bNG8LQkwRAi2bAJ9H4Sl3u2YF9rt7mzyeu7Ixk1RCawCfddBN
> S7SicAM05rTpTipucoEN/yw=
> =ygVy
> -----END PGP SIGNATURE-----
>
>
>

--
Best regards,
Nikolay

On Tue, Feb 07, 2006 at 03:28:31PM +0300, Nikolay Samokhvalov wrote:
> The real situation would be as the following.
> I want to use some algorithm to hide real number of registered users
> in my table user. So, I don't want to use simple sequence, when every
> new registered user in my system can guess what is the number of
> registered users simply observing his ID. So, I use following
> algorithm:
> (nextval('...name of the sequnence...') * N) mod % M,
> where N and M are quite big numbers that have no common multiples
> besides 1 (sorry, do not remember the English term for those numbers
> ;-) ).

Even then you could do it by saying:

ALTER SEQUENCE x MAXVALUE M INCREMENT N CYCLE;

> I wonder why people didn't notice this bug earlier, but I'm sure that
> there are many situations when it could be revealed. For example, what
> if I want to use nextval('seq1') * nextval('seq2') ?.. I'm sure that
> if you think you'll discover new examples that would be used in real
> world.
>
> Anyway, this is a bug, and I'll write the bug report to bugs mailing list.

Please do. It wouldn't be noticed much due to most people regarding
sequences as opaque ie the numbers themselves don't mean anything. But
if you alter the sequence or the default, pg_dump should dump it
correctly.

(This may be a know bug btw, I don't know).
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> testseq=# CREATE TABLE test(id SERIAL, data TEXT);
> NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
> serial column "test.id"
> CREATE TABLE
> ***
> ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;

The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
a serial column, but we haven't gotten around to enforcing that yet.

regards, tom lane

Tom Lane wrote:
> Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> > testseq=# CREATE TABLE test(id SERIAL, data TEXT);
> > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
> > serial column "test.id"
> > CREATE TABLE
> > ***
> > ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
>
> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> a serial column, but we haven't gotten around to enforcing that yet.

TODO has:

* %Disallow changing default expression of a SERIAL column

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On 2/7/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> > testseq=# CREATE TABLE test(id SERIAL, data TEXT);
> > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
> > serial column "test.id"
> > CREATE TABLE
> > ***
> > ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
>
> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> a serial column, but we haven't gotten around to enforcing that yet.
That's wrong!
Forget about SERIAL. I have INTEGER column with some expression as
DEFAULT in it. I use sequence in that expression and want this to be
dumped correctly.
The bug doesn't concerns SERIALs, in concerns general usage of sequences.
>
> regards, tom lane
>

--
Best regards,
Nikolay

Nikolay Samokhvalov wrote:
> On 2/7/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> > > testseq=# CREATE TABLE test(id SERIAL, data TEXT);
> > > NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
> > > serial column "test.id"
> > > CREATE TABLE
> > > ***
> > > ALTER TABLE test ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
> >
> > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > a serial column, but we haven't gotten around to enforcing that yet.
> That's wrong!
> Forget about SERIAL. I have INTEGER column with some expression as
> DEFAULT in it. I use sequence in that expression and want this to be
> dumped correctly.
> The bug doesn't concerns SERIALs, in concerns general usage of sequences.

Uh, I can't reproduce the failure:

test=> CREATE SEQUENCE xx;
CREATE SEQUENCE
test=> CREATE TABLE test5(id integer DEFAULT nextval('xx'), data TEXT);
CREATE TABLE
test=> ALTER TABLE test5 ALTER COLUMN id SET DEFAULT nextval('test_id_seq') * 10;
ALTER TABLE

pg_dump has:

CREATE TABLE test2 (
id integer DEFAULT (nextval('test_id_seq'::regclass) * 10),
data text
);

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> writes:
> Forget about SERIAL. I have INTEGER column with some expression as
> DEFAULT in it.

No, you have a SERIAL column that you've improperly mucked with the
implementation of. If you'd declared it as INTEGER to start with,
you could do whatever you wanted to its default expression.

regards, tom lane

Tom Lane wrote:

> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> a serial column, but we haven't gotten around to enforcing that yet.

Is this per the Standard? If so, then the oft-repeated mantra that
SERIAL is simply a macro for an INTEGER column with a particular
DEFAULT seems a bit misleading ...

- John D. Burger
MITRE

There is no SERIAL type in the standard at all. Moreover, standard
defines following expression for SEQUENCE GENERATORs:

<next value expression> ::= NEXT VALUE FOR <sequence generator name>

Postgres has non-standard equivalent - nextval(<sequence generator name>)...

So, sequences implementation in PostgreSQL isn't standard-compliant.

On 2/7/06, John D. Burger <john(at)mitre(dot)org> wrote:
> Tom Lane wrote:
>
> > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > a serial column, but we haven't gotten around to enforcing that yet.
>
> Is this per the Standard? If so, then the oft-repeated mantra that
> SERIAL is simply a macro for an INTEGER column with a particular
> DEFAULT seems a bit misleading ...
>
> - John D. Burger
> MITRE
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Best regards,
Nikolay

"John D. Burger" <john(at)mitre(dot)org> writes:
> Tom Lane wrote:
>> The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
>> a serial column, but we haven't gotten around to enforcing that yet.

> Is this per the Standard?

SERIAL isn't in the standard.

> If so, then the oft-repeated mantra that
> SERIAL is simply a macro for an INTEGER column with a particular
> DEFAULT seems a bit misleading ...

It started out that way, but we've been gradually moving in the
direction of making it a more integrated thing. It's already true that
you're not allowed to drop the serial column's sequence, and pg_dump has
special behavior for it (this is the bit that the OP thinks is a bug).

This is all in the name of preventing people from shooting themselves
in the foot, so forbidding changing the default expression seems like
a logical next step.

Now, the other direction we could go in is to back off all that and try
to make SERIAL just a table-creation macro again, as it was in the
beginning. It occurs to me that 8.1 has better solutions for the key
problems that the sequence-to-column binding was intended to prevent.
Even without SERIAL, you can't drop a sequence that some default is
depending on:

regression=# create sequence s1;
CREATE SEQUENCE
regression=# create table foo (f1 int default nextval('s1'));
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+---------------------------------
f1 | integer | default nextval('s1'::regclass)

regression=# drop sequence s1;
NOTICE: default for table foo column f1 depends on sequence s1
ERROR: cannot drop sequence s1 because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.
regression=#

and the former bugaboo of renaming the sequence is gone too:

regression=# alter table s1 rename to foobar;
ALTER TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-------------------------------------
f1 | integer | default nextval('foobar'::regclass)

regression=#

So the only extra thing that a SERIAL column still does for you is
to auto-drop the sequence if you drop the table or column. Maybe
that bit of hand-holding isn't worth the inconsistency of having
SERIAL be a little bit more than a macro.

regards, tom lane

On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote:
> > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > a serial column, but we haven't gotten around to enforcing that yet.

> TODO has:

> * %Disallow changing default expression of a SERIAL column

This should go along with another command like

... ALTER COLUMN DROP SERIAL

which drops the serial and removes the dependencies to get rid of it in a
clean way, right?

Joachim

--
Joachim Wieland joe(at)mcknight(dot)de
C/ Usandizaga 12 1°B ICQ: 37225940
20002 Donostia / San Sebastian (Spain) GPG key available

Joachim Wieland wrote:
> On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote:
> > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > > a serial column, but we haven't gotten around to enforcing that yet.
>
> > TODO has:
>
> > * %Disallow changing default expression of a SERIAL column
>
> This should go along with another command like
>
> ... ALTER COLUMN DROP SERIAL
>
> which drops the serial and removes the dependencies to get rid of it in a
> clean way, right?

Uh, you can drop a column or change its data type, but not drop the data
type of a column.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote:
> Joachim Wieland wrote:
> > On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote:
> > > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > > > a serial column, but we haven't gotten around to enforcing that yet.

> > > TODO has:

> > > * %Disallow changing default expression of a SERIAL column

> > This should go along with another command like

> > ... ALTER COLUMN DROP SERIAL

> > which drops the serial and removes the dependencies to get rid of it in a
> > clean way, right?

> Uh, you can drop a column or change its data type, but not drop the data
> type of a column.

Sure, the "DROP SERIAL" I proposed would rather "change" the data type
to int by dropping the default and would delete referring pg_depend entries.
Read it more as a kind of "drop autoincrement functionality for this
column".

The problem I see (but you might see it differently) is that you can't drop
this autoincrement stuff without also dropping the column once you forbid to
change the default (yeah I know, changing the default is even worse and
leaves you with incorrect dependencies).

Joachim

--
Joachim Wieland joe(at)mcknight(dot)de
C/ Usandizaga 12 1°B ICQ: 37225940
20002 Donostia / San Sebastian (Spain) GPG key available

Joachim Wieland wrote:
> On Wed, Feb 08, 2006 at 09:03:54AM -0500, Bruce Momjian wrote:
> > Joachim Wieland wrote:
> > > On Tue, Feb 07, 2006 at 09:49:02AM -0500, Bruce Momjian wrote:
> > > > > The correct solution to this is to forbid ALTER COLUMN SET DEFAULT on
> > > > > a serial column, but we haven't gotten around to enforcing that yet.
>
> > > > TODO has:
>
> > > > * %Disallow changing default expression of a SERIAL column
>
> > > This should go along with another command like
>
> > > ... ALTER COLUMN DROP SERIAL
>
> > > which drops the serial and removes the dependencies to get rid of it in a
> > > clean way, right?
>
> > Uh, you can drop a column or change its data type, but not drop the data
> > type of a column.
>
> Sure, the "DROP SERIAL" I proposed would rather "change" the data type
> to int by dropping the default and would delete referring pg_depend entries.
> Read it more as a kind of "drop autoincrement functionality for this
> column".
>
> The problem I see (but you might see it differently) is that you can't drop
> this autoincrement stuff without also dropping the column once you forbid to
> change the default (yeah I know, changing the default is even worse and
> leaves you with incorrect dependencies).

I assume an ALTER COLUMN ... TYPE INTEGER would drop the SERIAL part.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Feb 08, 2006 at 10:57:20PM -0500, Bruce Momjian wrote:
> > > > > TODO has:

> > > > > * %Disallow changing default expression of a SERIAL column

> > Sure, the "DROP SERIAL" I proposed would rather "change" the data type
> > to int by dropping the default and would delete referring pg_depend entries.
> > Read it more as a kind of "drop autoincrement functionality for this
> > column".

> > The problem I see (but you might see it differently) is that you can't drop
> > this autoincrement stuff without also dropping the column once you forbid to
> > change the default (yeah I know, changing the default is even worse and
> > leaves you with incorrect dependencies).

> I assume an ALTER COLUMN ... TYPE INTEGER would drop the SERIAL part.

So far it doesn't because it doesn't know the difference between serial
and int.

What about this proposal for serial columns:

- DROP DEFAULT drops serial and removes dependencies
- SET DEFAULT forbidden, issues a hint to DROP DEFAULT first

Is it also desired to convert an int column to a serial column?

(moving to -hackers)

Joachim

--
Joachim Wieland joe(at)mcknight(dot)de
C/ Usandizaga 12 1°B ICQ: 37225940
20002 Donostia / San Sebastian (Spain) GPG key available

Joachim Wieland wrote:
> On Wed, Feb 08, 2006 at 10:57:20PM -0500, Bruce Momjian wrote:
> > > > > > TODO has:
>
> > > > > > * %Disallow changing default expression of a SERIAL column
>
> > > Sure, the "DROP SERIAL" I proposed would rather "change" the data type
> > > to int by dropping the default and would delete referring pg_depend entries.
> > > Read it more as a kind of "drop autoincrement functionality for this
> > > column".
>
> > > The problem I see (but you might see it differently) is that you can't drop
> > > this autoincrement stuff without also dropping the column once you forbid to
> > > change the default (yeah I know, changing the default is even worse and
> > > leaves you with incorrect dependencies).
>
> > I assume an ALTER COLUMN ... TYPE INTEGER would drop the SERIAL part.
>
> So far it doesn't because it doesn't know the difference between serial
> and int.
>
> What about this proposal for serial columns:
>
> - DROP DEFAULT drops serial and removes dependencies
> - SET DEFAULT forbidden, issues a hint to DROP DEFAULT first
>
>
> Is it also desired to convert an int column to a serial column?

I think the only sane solution is if a SERIAL column is changed to
INTEGER, the default and dependencies are removed. Do you want a TODO
for that?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> Is it also desired to convert an int column to a serial column?

> I think the only sane solution is if a SERIAL column is changed to
> INTEGER, the default and dependencies are removed. Do you want a TODO
> for that?

If we are going to do something like that, I think we should take a hard
look at the idea I floated of putting SERIAL back to a pure
creation-time macro for type and default expression. This is getting to
have way too much hidden behavior, and what we are buying for it is very
little as of 8.1.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> >> Is it also desired to convert an int column to a serial column?
>
> > I think the only sane solution is if a SERIAL column is changed to
> > INTEGER, the default and dependencies are removed. Do you want a TODO
> > for that?
>
> If we are going to do something like that, I think we should take a hard
> look at the idea I floated of putting SERIAL back to a pure
> creation-time macro for type and default expression. This is getting to
> have way too much hidden behavior, and what we are buying for it is very
> little as of 8.1.

OK, but I was confused how 8.1 has improved the way SERIAL works.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> If we are going to do something like that, I think we should take a hard
>> look at the idea I floated of putting SERIAL back to a pure
>> creation-time macro for type and default expression. This is getting to
>> have way too much hidden behavior, and what we are buying for it is very
>> little as of 8.1.

> OK, but I was confused how 8.1 has improved the way SERIAL works.

I already said this up-thread, but: a plain old "DEFAULT nextval('foo')"
now has the properties that you can't drop sequence foo without dropping
the default expression, and renaming the sequence isn't a problem. That
takes care of the worst problems that we invented the SERIAL dependency
for. If we dropped the special sequence-to-column dependency that
SERIAL now adds, and got rid of the special pg_dump behavior for
serials, we'd have less code instead of more and it would work a lot
more transparently. The only thing we'd lose is that dropping a column
originally declared as serial wouldn't implicitly drop the sequence.
That's somewhat annoying but I'm not convinced that preserving that one
thing is worth the amount of infrastructure that's getting built (and
I hope you don't think that Joachim's proposal will be the end of it).
Basically we're sticking more and more band-aids on a design that wasn't
such a great idea to start with.

regards, tom lane

Tom Lane wrote:

> The only thing we'd lose is that dropping a column
> originally declared as serial wouldn't implicitly drop the sequence.

Wasn't that the primary purpose that the main coder for dependencies did
the work for? AFAIR the fact that the sequence wasn't dropped was a big
gotcha. Everyone was annoyed any time they wanted to experiment with
creating and dropping a table.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> The only thing we'd lose is that dropping a column
>> originally declared as serial wouldn't implicitly drop the sequence.

> Wasn't that the primary purpose that the main coder for dependencies did
> the work for?

My recollection is that the dependency for serials was added as an
afterthought without too much consideration of the long-term
implications. It was a cheap way of sort-of solving an immediate
problem using a mechanism that we were putting in place anyway.
But what we've got now is a misbegotten cross between the theory that
a SERIAL is a unitary object you mustn't muck with the innards of,
and the theory that SERIAL is just a macro that sets up an initial
state you can ALTER to your heart's content later.

IMHO we should make a choice between those plans and stick to it,
not add more and more infrastructure to let you ALTER things you
shouldn't be altering. Either a SERIAL is a black box or it isn't.
If it is not to be a black box, we need to reduce rather than increase
the amount of hidden semantics.

regards, tom lane

On Tue, Feb 07, 2006 at 15:28:31 +0300,
Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> wrote:
> The real situation would be as the following.
> I want to use some algorithm to hide real number of registered users
> in my table user. So, I don't want to use simple sequence, when every
> new registered user in my system can guess what is the number of
> registered users simply observing his ID. So, I use following
> algorithm:
> (nextval('...name of the sequnence...') * N) mod % M,
> where N and M are quite big numbers that have no common multiples
> besides 1 (sorry, do not remember the English term for those numbers
> ;-) ).

(N and M are said to be "relatively prime".)

The above method isn't very secure. You might be better off using a block
cipher in counter mode, depending on how badly you want to keep the number
of users secret. Even that won't be foolproof as the users might cooperate
with each other to estimate how many of them there are.

Bruno Wolff III <bruno(at)wolff(dot)to> writes:

> On Tue, Feb 07, 2006 at 15:28:31 +0300,
> Nikolay Samokhvalov <samokhvalov(at)gmail(dot)com> wrote:
>> The real situation would be as the following.
>> I want to use some algorithm to hide real number of registered users
>> in my table user. So, I don't want to use simple sequence, when every
>> new registered user in my system can guess what is the number of
>> registered users simply observing his ID. So, I use following

> (N and M are said to be "relatively prime".)
>
> The above method isn't very secure. You might be better off using a block
> cipher in counter mode, depending on how badly you want to keep the number
> of users secret. Even that won't be foolproof as the users might cooperate
> with each other to estimate how many of them there are.

Or, just start your sequence counting at 1000000. Or use bigint and
start it at a billion.

-Doug

On Fri, Feb 10, 2006 at 07:34:35 -0500,
Doug McNaught <doug(at)mcnaught(dot)org> wrote:
> Bruno Wolff III <bruno(at)wolff(dot)to> writes:
>
> Or, just start your sequence counting at 1000000. Or use bigint and
> start it at a billion.

That may work if you only have access to one id number, but you don't need
too many id numbers before you can start making good statistical estimates
of the number of users.

In one of my math classes, I was told a story about how statisticians
estimnated the number of tanks produced by germany during world war II
based on serial numbers found on captured tanks. This was supposedly despite
the Germans doing things (leaving gaps and the like) to make this harder.
And supposedly after the war, the statistical results were supposed to have
been more accurate than estimates obtain via other means (such as spies).

On 2/7/06, Martijn van Oosterhout <kleptog(at)svana(dot)org> wrote:
> On Tue, Feb 07, 2006 at 03:28:31PM +0300, Nikolay Samokhvalov wrote:
> > The real situation would be as the following.
> > I want to use some algorithm to hide real number of registered users
> > in my table user. So, I don't want to use simple sequence, when every
> > new registered user in my system can guess what is the number of
> > registered users simply observing his ID. So, I use following
> > algorithm:
> > (nextval('...name of the sequnence...') * N) mod % M,
> > where N and M are quite big numbers that have no common multiples
> > besides 1 (sorry, do not remember the English term for those numbers
> > ;-) ).
>
> Even then you could do it by saying:
>
> ALTER SEQUENCE x MAXVALUE M INCREMENT N CYCLE;
>
it's a pity, but no, I can't :-(
after reaching MAXVALUE sequence starts with MINVALUE (1 by default)...
for example with following sequence:

test=# CREATE SEQUENCE testseq INCREMENT BY 3 MAXVALUE 10 CYCLE;

...I always obtain only 1, 4, 7 and 10... ;-(

--
Best regards,
Nikolay