CREATE TABLE LIKE and SERIAL

In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is
specified. Otherwise, only the not null constraint is copied. I think
this is the most reasonable behavior and I don't see why it should
have been explicitly stated in the manual.

On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd
<markMLl.pgsql-general@telemetry.co.uk> wrote:

On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd
<markMLl.pgsql-general@telemetry.co.uk> wrote:

What is the "correct" behavior of a serial column when a table is created
with LIKE? The manual is silent on this.

What appears to be happening with 8.2 is that the column in the new table
refers to the original sequence generator.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

silly8888 wrote:

In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is
specified. Otherwise, only the not null constraint is copied. I think
this is the most reasonable behavior and I don't see why it should
have been explicitly stated in the manual.

I didn't say the sequence value, I said the sequence itself. After all
the normal usage will be where the "pattern" table is empty.

In other words the newly-created table is not completely decoupled from
the pattern, the sequence is a shared resource and this is irrespective
of any including/excluding specification.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

2009/10/30 Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk>:

On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd
<markMLl.pgsql-general@telemetry.co.uk> wrote:

What is the "correct" behavior of a serial column when a table is created
with LIKE? The manual is silent on this.

What appears to be happening with 8.2 is that the column in the new table
refers to the original sequence generator.

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or
colleagues]

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

silly8888 wrote:

In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is
specified. Otherwise, only the not null constraint is copied. I think
this is the most reasonable behavior and I don't see why it should
have been explicitly stated in the manual.

I didn't say the sequence value, I said the sequence itself. After all the
normal usage will be where the "pattern" table is empty.

In other words the newly-created table is not completely decoupled from the
pattern, the sequence is a shared resource and this is irrespective of any
including/excluding specification.

I can see why you wouldn't expect it to end up sharing the same
sequence. If you were to manually create a sequence and wanted to use
it on a column, you probably wouldn't bother using the SERIAL
datatype, but use integer instead. So really since we know the first
table has a datatype of SERIAL on one of its columns, we might instead
wish to have it create a new implicit sequence instead of merely
converting it to an INTEGER datatype and adding a default constraint
to the same sequence as the original table.

In theory, you could create a table and subsequently change the SERIAL
column's default value to another sequence, but I imagine that this
would be a very rare case since you wouldn't bother with the SERIAL
datatype in the first place if you knew you were going to do that.

I believe most people would expect a new implicit sequence to be
created, but maybe a PostgreSQL "elder" could enlighten us further.

Thom

Thom Brown <thombrown@gmail.com> writes:

I can see why you wouldn't expect it to end up sharing the same
sequence. If you were to manually create a sequence and wanted to use
it on a column, you probably wouldn't bother using the SERIAL
datatype, but use integer instead. So really since we know the first
table has a datatype of SERIAL on one of its columns, we might instead
wish to have it create a new implicit sequence instead of merely
converting it to an INTEGER datatype and adding a default constraint
to the same sequence as the original table.

Thinking of SERIAL as a type is your first mistake ;-). It is not a
type. It is a shorthand for making a sequence and sticking a suitable
default on a plain integer column. So what LIKE sees is an integer
column with a default, and it copies that.

regards, tom lane

2009/10/30 Tom Lane <tgl@sss.pgh.pa.us>:

Thom Brown <thombrown@gmail.com> writes:

I can see why you wouldn't expect it to end up sharing the same
sequence.  If you were to manually create a sequence and wanted to use
it on a column, you probably wouldn't bother using the SERIAL
datatype, but use integer instead.  So really since we know the first
table has a datatype of SERIAL on one of its columns, we might instead
wish to have it create a new implicit sequence instead of merely
converting it to an INTEGER datatype and adding a default constraint
to the same sequence as the original table.

Thinking of SERIAL as a type is your first mistake ;-).  It is not a
type.  It is a shorthand for making a sequence and sticking a suitable
default on a plain integer column.  So what LIKE sees is an integer
column with a default, and it copies that.

Well I realise SERIAL is a convenience rather than a datatype in its
own right, but I'm surprised that LIKE can't differentiate between a
column created with integer and one created with serial. The table
continues to report a serial datatype after its creation. Is it's
reference too low-level? Could it be altered to support recognition
of serial usage?

Thom

Thom Brown <thombrown@gmail.com> writes:

Well I realise SERIAL is a convenience rather than a datatype in its
own right, but I'm surprised that LIKE can't differentiate between a
column created with integer and one created with serial. The table
continues to report a serial datatype after its creation.

Really?

regression=# create table foo (f1 serial);
NOTICE: CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
CREATE TABLE
regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+--------------------------------------------------
f1 | integer | not null default nextval('foo_f1_seq'::regclass)

regression=#

We used to try to treat serial as more like a real type (in particular
pg_dump used to try to dump the results of this using "serial") but we
found out that that was actively a bad idea, because there were too
many corner cases where it did the wrong thing. I doubt we'll want
to go back in that direction.

regards, tom lane

2009/10/30 Tom Lane <tgl@sss.pgh.pa.us>:

Thom Brown <thombrown@gmail.com> writes:

Well I realise SERIAL is a convenience rather than a datatype in its
own right, but I'm surprised that LIKE can't differentiate between a
column created with integer and one created with serial.  The table
continues to report a serial datatype after its creation.

Really?

regression=# create table foo (f1 serial);
NOTICE:  CREATE TABLE will create implicit sequence "foo_f1_seq" for serial column "foo.f1"
CREATE TABLE
regression=# \d foo
                        Table "public.foo"
 Column |  Type   |                    Modifiers
--------+---------+--------------------------------------------------
 f1     | integer | not null default nextval('foo_f1_seq'::regclass)

regression=#

We used to try to treat serial as more like a real type (in particular
pg_dump used to try to dump the results of this using "serial") but we
found out that that was actively a bad idea, because there were too
many corner cases where it did the wrong thing.  I doubt we'll want
to go back in that direction.

Erk... that's strange. I've obviously made the wrong assumptions
here. I'm basing it on pgAdmin describing the table as it reports a
SERIAL datatype. Not sure how it's deciding that. I created a manual
sequence, then a new table with an integer column that gets it's
default value from the sequence, set that to be NOT NULL and act as
the primary key, but it still reports it as an integer, so it doesn't
seem to be assuming it based on these constraints.

How is pgAdmin determining the serial type in this case?

Thom

Tom Lane wrote:

Thinking of SERIAL as a type is your first mistake ;-). It is not a
type. It is a shorthand for making a sequence and sticking a suitable
default on a plain integer column. So what LIKE sees is an integer
column with a default, and it copies that.

That's entirely fair, and the manual section dealing with types is very
careful to start off with "The data types serial and bigserial are not
true types".

However I think that the description of CREATE TABLE ... LIKE really
could do with a "health warning" for this case.

Looking at this very slightly deeper and assuming that the user is aware
of the pitfalls, it's obviously easy for him to create a new sequence
and to use it as the default value. But what if he wants the new
sequence to inherit the current state of an existing one: might I
suggest CREATE SEQUENCE ... LIKE would be appropriate here?

--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column. However, that's an oversimplification
of reality. I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)

regards, tom lane

Le vendredi 30 octobre 2009 à 23:48:45, Tom Lane a écrit :

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column. However, that's an oversimplification
of reality. I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)

I've just read this. I wasn't aware of this. Should we suppress this behavior
of pgAdmin?

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Fri, Oct 30, 2009 at 11:12 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:

Le vendredi 30 octobre 2009 à 23:48:45, Tom Lane a écrit :

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column.  However, that's an oversimplification
of reality.  I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)

I've just read this. I wasn't aware of this. Should we suppress this behavior
of pgAdmin?

Do you have a proposed fix? I suspect people won't want us to stop
showing columns as 'SERIAL' when they actually were created that way.

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
PGDay.EU 2009 Conference: http://2009.pgday.eu/start

Le dimanche 1 novembre 2009 à 13:24:10, Dave Page a écrit :

On Fri, Oct 30, 2009 at 11:12 PM, Guillaume Lelarge

<guillaume@lelarge.info> wrote:

Le vendredi 30 octobre 2009 à 23:48:45, Tom Lane a écrit :

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column. However, that's an oversimplification
of reality. I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)

I've just read this. I wasn't aware of this. Should we suppress this
behavior of pgAdmin?

Do you have a proposed fix? I suspect people won't want us to stop
showing columns as 'SERIAL' when they actually were created that way.

Other ideas than doing what pg_dump does? I'm afraid not.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

On Thu, Nov 12, 2009 at 8:00 AM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:

Le dimanche 1 novembre 2009 à 13:24:10, Dave Page a écrit :

On Fri, Oct 30, 2009 at 11:12 PM, Guillaume Lelarge

<guillaume@lelarge.info> wrote:

Le vendredi 30 octobre 2009 à 23:48:45, Tom Lane a écrit :

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the sequence
as being "owned by" the column.  However, that's an oversimplification
of reality.  I would imagine that pgAdmin will lie to you in exactly
the same cases that used to break pg_dump (notably, where someone has
manually adjusted either the default expression or the sequence...)

I've just read this. I wasn't aware of this. Should we suppress this
behavior of pgAdmin?

Do you have a proposed fix? I suspect people won't want us to stop
showing columns as 'SERIAL' when they actually were created that way.

Other ideas than doing what pg_dump does? I'm afraid not.

pg_dump isn't designed to make the output look pretty, which is the
major issue here. We're trying to do both.

It seems to me that correctness trumps aesthetics, so we probably will
have to do what pg_dump does. Care to work up a patch?

--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

Le jeudi 12 novembre 2009 à 10:12:00, Dave Page a écrit :

On Thu, Nov 12, 2009 at 8:00 AM, Guillaume Lelarge

<guillaume@lelarge.info> wrote:

Le dimanche 1 novembre 2009 à 13:24:10, Dave Page a écrit :

On Fri, Oct 30, 2009 at 11:12 PM, Guillaume Lelarge

<guillaume@lelarge.info> wrote:

Le vendredi 30 octobre 2009 à 23:48:45, Tom Lane a écrit :

Thom Brown <thombrown@gmail.com> writes:

How is pgAdmin determining the serial type in this case?

Most likely it's looking for the pg_depend entry that shows the
sequence as being "owned by" the column. However, that's an
oversimplification of reality. I would imagine that pgAdmin will lie
to you in exactly the same cases that used to break pg_dump (notably,
where someone has manually adjusted either the default expression or
the sequence...)

I've just read this. I wasn't aware of this. Should we suppress this
behavior of pgAdmin?

Do you have a proposed fix? I suspect people won't want us to stop
showing columns as 'SERIAL' when they actually were created that way.

Other ideas than doing what pg_dump does? I'm afraid not.

pg_dump isn't designed to make the output look pretty, which is the
major issue here. We're trying to do both.

Yes.

It seems to me that correctness trumps aesthetics, so we probably will
have to do what pg_dump does.

That's the main reason I started this thread :)

Care to work up a patch?

Yep. On my TODO list right now.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com