Thread: Primary key data type: integer vs identity

Primary key data type: integer vs identity

From
Rich Shepard
Date:
When I created the database I set primary key data types as integer; for
example:

Column    |   Type          | Collation | Nullable |  Default
--------------+-----------------------+-----------+----------+-------------
  org_id   | integer         |           | not null |
nextval('organizations_org_id_seq'::regclass)

I probably should have used the serial type, but didn't.

If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.

Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.

Regards,

Rich



Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 10:55 AM, Rich Shepard wrote:
> When I created the database I set primary key data types as integer; for
> example:
> 
> Column    |   Type          | Collation | Nullable |  Default
> --------------+-----------------------+-----------+----------+-------------
>   org_id   | integer         |           | not null |
> nextval('organizations_org_id_seq'::regclass)
> 
> I probably should have used the serial type, but didn't.

If you want it to be like serial(assuming the sequences are unique to 
each table) then:

https://www.postgresql.org/docs/11/sql-altersequence.html
"OWNED BY table_name.column_name
OWNED BY NONE

     The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) is 
dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence “free-standing”.
"



> 
> If it would be advisable for me to convert from integer to identity please
> point me to the appropriate work flow.

If it is working for you now I see no reason to switch.

> 
> Reading the CREATE TABLE pages in the manual did not give me sufficient
> insights to appreciate the differences or indicate how to change the
> column's data type.

IDENTITY is a SQL standard.

The difference:

https://www.postgresql.org/docs/11/sql-createtable.html
"GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

     This clause creates the column as an identity column. It will have 
an implicit sequence attached to it and the column in new rows will 
automatically have values from the sequence assigned to it.

     The clauses ALWAYS and BY DEFAULT determine how the sequence value 
is given precedence over a user-specified value in an INSERT statement. 
If ALWAYS is specified, a user-specified value is only accepted if the 
INSERT statement specifies OVERRIDING SYSTEM VALUE. If BY DEFAULT is 
specified, then the user-specified value takes precedence. See INSERT 
for details. (In the COPY command, user-specified values are always used 
regardless of this setting.)

     The optional sequence_options clause can be used to override the 
options of the sequence. See CREATE SEQUENCE for details.
"

Basically the ALWAYS/DEFAULT choices.

> 
> Regards,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Michel Pelletier
Date:


On Fri, Apr 19, 2019 at 10:55 AM Rich Shepard <rshepard@appl-ecosys.com> wrote:
When I created the database I set primary key data types as integer; for
example:

Column    |   Type          | Collation | Nullable |  Default
--------------+-----------------------+-----------+----------+-------------
  org_id   | integer         |           | not null |
nextval('organizations_org_id_seq'::regclass)

I probably should have used the serial type, but didn't.

If it would be advisable for me to convert from integer to identity please
point me to the appropriate work flow.


 
"The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns"

so really there's no difference, what you have is fine. 

-Michel



Reading the CREATE TABLE pages in the manual did not give me sufficient
insights to appreciate the differences or indicate how to change the
column's data type.

Regards,

Rich


Re: Primary key data type: integer vs identity

From
Rich Shepard
Date:
On Fri, 19 Apr 2019, Michel Pelletier wrote:

> https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL
> "The data types smallserial, serial and bigserial are not true types, but
> merely a notational convenience for creating unique identifier columns"
> so really there's no difference, what you have is fine.

Michel,

That's what I thought. And confirmation is always valuable.

Regards,

Rich



Re: Primary key data type: integer vs identity

From
Rich Shepard
Date:
On Fri, 19 Apr 2019, Adrian Klaver wrote:

> If it is working for you now I see no reason to switch.

Adrian,

It does work. I just learned about the SQL identity type and want to learn
when it's most appropriate to use. The databases I develop all work with
integers as primary keys and reading about the type didn't clarify (for me)
when it should be used.

Regards,

Rich



Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 11:14 AM, Rich Shepard wrote:
> On Fri, 19 Apr 2019, Adrian Klaver wrote:
> 
>> If it is working for you now I see no reason to switch.
> 
> Adrian,
> 
> It does work. I just learned about the SQL identity type and want to learn
> when it's most appropriate to use. The databases I develop all work with
> integers as primary keys and reading about the type didn't clarify (for me)
> when it should be used.

Mainly for folks that want cross database SQL compliance. It is not a 
type so much as a way of specifying an auto-increment column.

> 
> Regards,
> 
> Rich
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Ken Tanzer
Date:
On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 11:14 AM, Rich Shepard wrote:
> On Fri, 19 Apr 2019, Adrian Klaver wrote:
>
>> If it is working for you now I see no reason to switch.
>
> Adrian,
>
> It does work. I just learned about the SQL identity type and want to learn
> when it's most appropriate to use. The databases I develop all work with
> integers as primary keys and reading about the type didn't clarify (for me)
> when it should be used.

Mainly for folks that want cross database SQL compliance. It is not a
type so much as a way of specifying an auto-increment column.


It also sounds like it has advantages in terms of tying your sequence directly to the column.  If you drop a serial column, it doesn't drop the sequence.
Once I've upgraded to 10+, I might look at converting my existing serial columns.  Peter Eisentraut wrote a good piece(1) on identity columns, including a function for converting existing serial columns.  I've copied the function below, but had two questions about it:

1)  Would the function as written also reassign ownership to that table column?  (I see the update to pg_depend and pg_attribute, but don't know enough about them to know if that changes ownership)
2)  Would one have to be a superuser to do this?

Thanks,
Ken


CREATE OR REPLACE FUNCTION upgrade_serial_to_identity(tbl regclass, col name)
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  colnum smallint;
  seqid oid;
  count int;
BEGIN
  -- find column number
  SELECT attnum INTO colnum FROM pg_attribute WHERE attrelid = tbl AND attname = col;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'column does not exist';
  END IF;

  -- find sequence
  SELECT INTO seqid objid
    FROM pg_depend
    WHERE (refclassid, refobjid, refobjsubid) = ('pg_class'::regclass, tbl, colnum)
      AND classid = 'pg_class'::regclass AND objsubid = 0
      AND deptype = 'a';

  GET DIAGNOSTICS count = ROW_COUNT;
  IF count < 1 THEN
    RAISE EXCEPTION 'no linked sequence found';
  ELSIF count > 1 THEN
    RAISE EXCEPTION 'more than one linked sequence found';
  END IF;  

  -- drop the default
  EXECUTE 'ALTER TABLE ' || tbl || ' ALTER COLUMN ' || quote_ident(col) || ' DROP DEFAULT';

  -- change the dependency between column and sequence to internal
  UPDATE pg_depend
    SET deptype = 'i'
    WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
      AND deptype = 'a';

  -- mark the column as identity column
  UPDATE pg_attribute
    SET attidentity = 'd'
    WHERE attrelid = tbl
      AND attname = col;
END;
$$;



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

From
Rich Shepard
Date:
On Fri, 19 Apr 2019, Adrian Klaver wrote:

> Mainly for folks that want cross database SQL compliance. It is not a type
> so much as a way of specifying an auto-increment column.

Thank you, Adrian. I saw that it was a column specification and your
explanation of its application is really helpful.

Carpe weekend,

Rich



Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 4/19/19 11:14 AM, Rich Shepard wrote:
>      > On Fri, 19 Apr 2019, Adrian Klaver wrote:
>      >
>      >> If it is working for you now I see no reason to switch.
>      >
>      > Adrian,
>      >
>      > It does work. I just learned about the SQL identity type and want
>     to learn
>      > when it's most appropriate to use. The databases I develop all
>     work with
>      > integers as primary keys and reading about the type didn't
>     clarify (for me)
>      > when it should be used.
> 
>     Mainly for folks that want cross database SQL compliance. It is not a
>     type so much as a way of specifying an auto-increment column.
> 
> 
> It also sounds like it has advantages in terms of tying your sequence 
> directly to the column.  If you drop a serial column, it doesn't drop 
> the sequence.

A serial column will:

test=> create table serial_test(id serial);
CREATE TABLE
test=> \d serial_test
                             Table "public.serial_test"
  Column |  Type   | Collation | Nullable |                 Default 

--------+---------+-----------+----------+-----------------------------------------
  id     | integer |           | not null | 
nextval('serial_test_id_seq'::regclass)

test=> select * from serial_test_id_seq ;
  last_value | log_cnt | is_called
------------+---------+-----------
           1 |       0 | f
(1 row)

test=> drop table serial_test ;
DROP TABLE 
 

test=> select * from serial_test_id_seq ; 
 

ERROR:  relation "serial_test_id_seq" does not exist 
 

LINE 1: select * from serial_test_id_seq ;

If you just use a sequence as a default value it will not  unless you 
make it OWNED by the table per the link I posted upstream.


> Once I've upgraded to 10+, I might look at converting my existing serial 
> columns.  Peter Eisentraut wrote a good piece(1) on identity columns, 
> including a function for converting existing serial columns.  I've 
> copied the function below, but had two questions about it:
> 
> 1)  Would the function as written also reassign ownership to that table 
> column?  (I see the update to pg_depend and pg_attribute, but don't know 
> enough about them to know if that changes ownership)
> 2)  Would one have to be a superuser to do this?
> 
> Thanks,
> Ken



-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 4/19/19 11:14 AM, Rich Shepard wrote:
>      > On Fri, 19 Apr 2019, Adrian Klaver wrote:
>      >
>      >> If it is working for you now I see no reason to switch.
>      >
>      > Adrian,
>      >
>      > It does work. I just learned about the SQL identity type and want
>     to learn
>      > when it's most appropriate to use. The databases I develop all
>     work with
>      > integers as primary keys and reading about the type didn't
>     clarify (for me)
>      > when it should be used.
> 
>     Mainly for folks that want cross database SQL compliance. It is not a
>     type so much as a way of specifying an auto-increment column.
> 
> 
> It also sounds like it has advantages in terms of tying your sequence 
> directly to the column.  If you drop a serial column, it doesn't drop 
> the sequence.

Misread your post the first time still:

  create table serial_test(id serial, fld_1 text); 
 

CREATE TABLE 
 

test=> \d serial_test 
 

                             Table "public.serial_test" 
 

  Column |  Type   | Collation | Nullable |                 Default 
 

--------+---------+-----------+----------+----------------------------------------- 
 

  id     | integer |           | not null | 
nextval('serial_test_id_seq'::regclass) 

  fld_1  | text    |           |          | 
 

 
 

test=> alter table serial_test drop column id; 
 

ALTER TABLE 
 

test=> \d serial_test 
 

            Table "public.serial_test" 
 

  Column | Type | Collation | Nullable | Default 
 

--------+------+-----------+----------+--------- 
 

  fld_1  | text |           |          | 
 

 
 

test=> select * from serial_test_id_seq ; 

ERROR:  relation "serial_test_id_seq" does not exist
LINE 1: select * from serial_test_id_seq ;

> Once I've upgraded to 10+, I might look at converting my existing serial 
> columns.  Peter Eisentraut wrote a good piece(1) on identity columns, 
> including a function for converting existing serial columns.  I've 
> copied the function below, but had two questions about it:
> 
> 1)  Would the function as written also reassign ownership to that table 
> column?  (I see the update to pg_depend and pg_attribute, but don't know 
> enough about them to know if that changes ownership)
> 2)  Would one have to be a superuser to do this?
> 
> Thanks,
> Ken

-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Ken Tanzer
Date:
On Fri, Apr 19, 2019 at 12:02 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 11:32 AM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 11:20 AM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 4/19/19 11:14 AM, Rich Shepard wrote:
>      > On Fri, 19 Apr 2019, Adrian Klaver wrote:
>      >
>      >> If it is working for you now I see no reason to switch.
>      >
>      > Adrian,
>      >
>      > It does work. I just learned about the SQL identity type and want
>     to learn
>      > when it's most appropriate to use. The databases I develop all
>     work with
>      > integers as primary keys and reading about the type didn't
>     clarify (for me)
>      > when it should be used.
>
>     Mainly for folks that want cross database SQL compliance. It is not a
>     type so much as a way of specifying an auto-increment column.
>
>
> It also sounds like it has advantages in terms of tying your sequence
> directly to the column.  If you drop a serial column, it doesn't drop
> the sequence.

A serial column will:


Thanks Adrian.  You are as usual correct.  (I had a bunch of tables created by a function that I assumed were serial, but were not.)  Identity columns still seem tidier and more manageable.  Can you tell if the function I referenced would change the ownership or not?

Cheers,
Ken 

 
--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 12:35 PM, Ken Tanzer wrote:

> 
> Thanks Adrian.  You are as usual correct.  (I had a bunch of tables 
> created by a function that I assumed were serial, but were not.)  
> Identity columns still seem tidier and more manageable.  Can you tell if 
> the function I referenced would change the ownership or not?

I believe in 'when it doubt try it, whats the worst that can happen?:)':

<NOTE> I needed to be a superuser to run due to this:
ERROR:  permission denied for table pg_depend
CONTEXT:  SQL statement "UPDATE pg_depend
     SET deptype = 'i'
     WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
       AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at 
SQL statement


test=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
                                 Access privileges
  Schema |    Name     | Type  | Access privileges | Column privileges | 
Policies
--------+-------------+-------+-------------------+-------------------+----------
  public | serial_test | table |                   |                   |
(1 row)

test=# select upgrade_serial_to_identity('serial_test', 'id');
  upgrade_serial_to_identity
----------------------------

(1 row)

test=# \d serial_test
                          Table "public.serial_test"
  Column |  Type   | Collation | Nullable |             Default 

--------+---------+-----------+----------+----------------------------------
  id     | integer |           | not null | generated by default as identity
  fld_1  | text    |           |          |


test=# \dp+ serial_test
                                 Access privileges
  Schema |    Name     | Type  | Access privileges | Column privileges | 
Policies
--------+-------------+-------+-------------------+-------------------+----------
  public | serial_test | table |                   |                   |
(1 row)

> 
> Cheers,
> Ken
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Ken Tanzer
Date:
On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 12:35 PM, Ken Tanzer wrote:

>
> Thanks Adrian.  You are as usual correct.  (I had a bunch of tables
> created by a function that I assumed were serial, but were not.) 
> Identity columns still seem tidier and more manageable.  Can you tell if
> the function I referenced would change the ownership or not?

I believe in 'when it doubt try it, whats the worst that can happen?:)':


I agree, and if I had a copy of 10+ running, I probably would have! :)
 
<NOTE> I needed to be a superuser to run due to this:
ERROR:  permission denied for table pg_depend
CONTEXT:  SQL statement "UPDATE pg_depend
     SET deptype = 'i'
     WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
       AND deptype = 'a'"
PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
SQL statement


test=# create table serial_test(id serial, fld_1 text);
CREATE TABLE
test=# \dp serial_test
                                 Access privileges
  Schema |    Name     | Type  | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
  public | serial_test | table |                   |                   |
(1 row)

test=# select upgrade_serial_to_identity('serial_test', 'id');
  upgrade_serial_to_identity
----------------------------

(1 row)

test=# \d serial_test
                          Table "public.serial_test"
  Column |  Type   | Collation | Nullable |             Default

--------+---------+-----------+----------+----------------------------------
  id     | integer |           | not null | generated by default as identity
  fld_1  | text    |           |          |


test=# \dp+ serial_test
                                 Access privileges
  Schema |    Name     | Type  | Access privileges | Column privileges |
Policies
--------+-------------+-------+-------------------+-------------------+----------
  public | serial_test | table |                   |                   |
(1 row)


Maybe I'm missing it, but I'm not really sure what that is supposed to be telling me about the ownership of the sequence.

The scenario I'm wondering about is:

Table A owned by User 1, and has column created as serial
The created sequence is altered to be owned by User 2 (with User 1 granted select & update)
upgrade_serial_to_identity applied to Table A
At that point, who owns the sequence?

I can wait until I've got 10+ running and try it myself, but I thought maybe someone would know the answer and be willing to share.

Thanks!

Ken


--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 1:02 PM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 4/19/19 12:35 PM, Ken Tanzer wrote:
> 
>      >
>      > Thanks Adrian.  You are as usual correct.  (I had a bunch of tables
>      > created by a function that I assumed were serial, but were not.)
>      > Identity columns still seem tidier and more manageable.  Can you
>     tell if
>      > the function I referenced would change the ownership or not?
> 
>     I believe in 'when it doubt try it, whats the worst that can happen?:)':
> 
> 
> I agree, and if I had a copy of 10+ running, I probably would have! :)
> 
>     <NOTE> I needed to be a superuser to run due to this:
>     ERROR:  permission denied for table pg_depend
>     CONTEXT:  SQL statement "UPDATE pg_depend
>           SET deptype = 'i'
>           WHERE (classid, objid, objsubid) = ('pg_class'::regclass,
>     seqid, 0)
>             AND deptype = 'a'"
>     PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
>     SQL statement
> 
> 
>     test=# create table serial_test(id serial, fld_1 text);
>     CREATE TABLE
>     test=# \dp serial_test
>                                       Access privileges
>        Schema |    Name     | Type  | Access privileges | Column
>     privileges |
>     Policies
>     --------+-------------+-------+-------------------+-------------------+----------
>        public | serial_test | table |                   |               
>         |
>     (1 row)
> 
>     test=# select upgrade_serial_to_identity('serial_test', 'id');
>        upgrade_serial_to_identity
>     ----------------------------
> 
>     (1 row)
> 
>     test=# \d serial_test
>                                Table "public.serial_test"
>        Column |  Type   | Collation | Nullable |             Default
> 
>     --------+---------+-----------+----------+----------------------------------
>        id     | integer |           | not null | generated by default as
>     identity
>        fld_1  | text    |           |          |
> 
> 
>     test=# \dp+ serial_test
>                                       Access privileges
>        Schema |    Name     | Type  | Access privileges | Column
>     privileges |
>     Policies
>     --------+-------------+-------+-------------------+-------------------+----------
>        public | serial_test | table |                   |               
>         |
>     (1 row)
> 
> 
> Maybe I'm missing it, but I'm not really sure what that is supposed to 
> be telling me about the ownership of the sequence.
> 
> The scenario I'm wondering about is:
> 
> Table A owned by User 1, and has column created as serial
> The created sequence is altered to be owned by User 2 (with User 1 
> granted select & update)
> upgrade_serial_to_identity applied to Table A
> At that point, who owns the sequence?
> 
> I can wait until I've got 10+ running and try it myself, but I thought 
> maybe someone would know the answer and be willing to share.



select version();
                                                           version 


----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
7.3.1 20180323 [gcc-7-branch revision 258812], 64-bit

select session_user, current_user;
  session_user | current_user
--------------+--------------
  aklaver      | aklaver

  create table serial_test(id serial, fld_1 text);
CREATE TABLE

\d

                 List of relations
  Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------

public | serial_test        | table    | aklaver
public | serial_test_id_seq | sequence | aklaver



test_(aklaver)> \c - postgres
You are now connected to database "test" as user "postgres".
test_(postgres)# select session_user, current_user;
  session_user | current_user
--------------+--------------
  postgres     | postgres
(1 row)

test_(postgres)# select upgrade_serial_to_identity('serial_test', 'id');
  upgrade_serial_to_identity
----------------------------

(1 row)


                  List of relations
  Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------

  public | serial_test        | table    | aklaver
  public | serial_test_id_seq | sequence | aklaver


The function is working directly on the system catalogs and I do not 
anything that changes ownership:

  UPDATE pg_depend
     SET deptype = 'i'
     WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
       AND deptype = 'a';

   -- mark the column as identity column
   UPDATE pg_attribute
     SET attidentity = 'd'
     WHERE attrelid = tbl
       AND attname = col;

> 
> Thanks!

> 
> Ken
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Ken Tanzer
Date:
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 4/19/19 1:02 PM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 4/19/19 12:35 PM, Ken Tanzer wrote:
>
>      >
>      > Thanks Adrian.  You are as usual correct.  (I had a bunch of tables
>      > created by a function that I assumed were serial, but were not.)
>      > Identity columns still seem tidier and more manageable.  Can you
>     tell if
>      > the function I referenced would change the ownership or not?
>
>     I believe in 'when it doubt try it, whats the worst that can happen?:)':
>
>
> I agree, and if I had a copy of 10+ running, I probably would have! :)
>
>     <NOTE> I needed to be a superuser to run due to this:
>     ERROR:  permission denied for table pg_depend
>     CONTEXT:  SQL statement "UPDATE pg_depend
>           SET deptype = 'i'
>           WHERE (classid, objid, objsubid) = ('pg_class'::regclass,
>     seqid, 0)
>             AND deptype = 'a'"
>     PL/pgSQL function upgrade_serial_to_identity(regclass,name) line 31 at
>     SQL statement
>
>
>     test=# create table serial_test(id serial, fld_1 text);
>     CREATE TABLE
>     test=# \dp serial_test
>                                       Access privileges
>        Schema |    Name     | Type  | Access privileges | Column
>     privileges |
>     Policies
>     --------+-------------+-------+-------------------+-------------------+----------
>        public | serial_test | table |                   |               
>         |
>     (1 row)
>
>     test=# select upgrade_serial_to_identity('serial_test', 'id');
>        upgrade_serial_to_identity
>     ----------------------------
>
>     (1 row)
>
>     test=# \d serial_test
>                                Table "public.serial_test"
>        Column |  Type   | Collation | Nullable |             Default
>
>     --------+---------+-----------+----------+----------------------------------
>        id     | integer |           | not null | generated by default as
>     identity
>        fld_1  | text    |           |          |
>
>
>     test=# \dp+ serial_test
>                                       Access privileges
>        Schema |    Name     | Type  | Access privileges | Column
>     privileges |
>     Policies
>     --------+-------------+-------+-------------------+-------------------+----------
>        public | serial_test | table |                   |               
>         |
>     (1 row)
>
>
> Maybe I'm missing it, but I'm not really sure what that is supposed to
> be telling me about the ownership of the sequence.
>
> The scenario I'm wondering about is:
>
> Table A owned by User 1, and has column created as serial
> The created sequence is altered to be owned by User 2 (with User 1
> granted select & update)
> upgrade_serial_to_identity applied to Table A
> At that point, who owns the sequence?
>
> I can wait until I've got 10+ running and try it myself, but I thought
> maybe someone would know the answer and be willing to share.



select version();
                                                           version

----------------------------------------------------------------------------------------------------------------------------
  PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux)
7.3.1 20180323 [gcc-7-branch revision 258812], 64-bit

select session_user, current_user;
  session_user | current_user
--------------+--------------
  aklaver      | aklaver

  create table serial_test(id serial, fld_1 text);
CREATE TABLE

\d

                 List of relations
  Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------

public | serial_test        | table    | aklaver
public | serial_test_id_seq | sequence | aklaver



test_(aklaver)> \c - postgres
You are now connected to database "test" as user "postgres".
test_(postgres)# select session_user, current_user;
  session_user | current_user
--------------+--------------
  postgres     | postgres
(1 row)

test_(postgres)# select upgrade_serial_to_identity('serial_test', 'id');
  upgrade_serial_to_identity
----------------------------

(1 row)


                  List of relations
  Schema |        Name        |   Type   |  Owner
--------+--------------------+----------+----------

  public | serial_test        | table    | aklaver
  public | serial_test_id_seq | sequence | aklaver


The function is working directly on the system catalogs and I do not
anything that changes ownership:

  UPDATE pg_depend
     SET deptype = 'i'
     WHERE (classid, objid, objsubid) = ('pg_class'::regclass, seqid, 0)
       AND deptype = 'a';

   -- mark the column as identity column
   UPDATE pg_attribute
     SET attidentity = 'd'
     WHERE attrelid = tbl
       AND attname = col;

Thanks, though I don't see what this shows, since there were not separate users involved.  So I loaded up a copy of 11.2, and discovered that you actually can't change the ownership of a sequence created by serial.

ag_tz_test=# ALTER SEQUENCE t_serial_id_seq OWNER TO develop;
ERROR:  cannot change owner of sequence "t_serial_id_seq"
DETAIL:  Sequence "t_serial_id_seq" is linked to table "t_serial".

I also missed the part in the article where it talks about assigning ownership to the column.  In psql, it's easy to miss because a \ds (or \ds+) (as opposed to a \d) shows the user that owns the sequence, not a column:


test=# \ds+
                            List of relations
 Schema |       Name        |   Type   | Owner |    Size    | Description 
--------+-------------------+----------+-------+------------+-------------
 public | t_serial_id_seq   | sequence | u1    | 8192 bytes | 

Whereas if you look at \d it shows the column:


test=# \d+ t_serial_id_seq 
                  Sequence "public.t_serial_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache 
---------+-------+---------+------------+-----------+---------+-------
 integer |     1 |       1 | 2147483647 |         1 | no      |     1

(Side note: it is surprising that the Size and Description don't show up with \d+.  I always thought that a \d+ was the best way to get all the detail on an object.)

But even if you drop the default on the column, it doesn't seem like you can change the sequence's owner:

test=# ALTER TABLE t_serial ALTER COLUMN id DROP DEFAULT;
ALTER TABLE
test=# alter sequence t_serial_id_seq OWNER TO u2;
ERROR:  cannot change owner of sequence "t_serial_id_seq"
DETAIL:  Sequence "t_serial_id_seq" is linked to table "t_serial".

Although you can drop it:

DROP SEQUENCE t_serial_id_seq;
DROP SEQUENCE

Anyhoo, I've learned a bit more today, and thanks for your help!

Cheers,
Ken



--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Primary key data type: integer vs identity

From
Adrian Klaver
Date:
On 4/19/19 2:31 PM, Ken Tanzer wrote:
> On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@aklaver.com 

> 
> Thanks, though I don't see what this shows, since there were not 
> separate users involved.  So I loaded up a copy of 11.2, and discovered 

Well the table was created by one user and the serial was changed to 
IDENTITY by another.

> that you actually can't change the ownership of a sequence created by 
> serial.
> 
> ag_tz_test=# ALTER SEQUENCE t_serial_id_seq OWNER TO develop;
> ERROR:  cannot change owner of sequence "t_serial_id_seq"
> DETAIL:  Sequence "t_serial_id_seq" is linked to table "t_serial".

That is covered here:

https://www.postgresql.org/docs/11/sql-altersequence.html

"OWNED BY table_name.column_name
...
The specified table must have the same owner and be in the same schema 
as the sequence. ..."

Which to me means the opposite is true also.

Though you can:

"    Specifying OWNED BY NONE removes any existing association, making 
the sequence “free-standing”.
"

> 
> I also missed the part in the article where it talks about assigning 
> ownership to the column.  In psql, it's easy to miss because a \ds (or 
> \ds+) (as opposed to a \d) shows the user that owns the sequence, not a 
> column:
> 
> 
> test=# \ds+
>                              List of relations
>   Schema |       Name        |   Type   | Owner |    Size    | Description
> --------+-------------------+----------+-------+------------+-------------
>   public | t_serial_id_seq   | sequence | u1    | 8192 bytes |
> 
> Whereas if you look at \d it shows the column:
> 
> 
> test=# \d+ t_serial_id_seq
>                    Sequence "public.t_serial_id_seq"
>    Type   | Start | Minimum |  Maximum   | Increment | Cycles? | Cache
> ---------+-------+---------+------------+-----------+---------+-------
>   integer |     1 |       1 | 2147483647 |         1 | no      |     1
> Owned by: public.t_serial.id <http://public.t_serial.id>
> 
> (Side note: it is surprising that the Size and Description don't show up 
> with \d+.  I always thought that a \d+ was the best way to get all the 
> detail on an object.)
> 
> But even if you drop the default on the column, it doesn't seem like you 
> can change the sequence's owner:
> 
> test=# ALTER TABLE t_serial ALTER COLUMN id DROP DEFAULT;
> ALTER TABLE
> test=# alter sequence t_serial_id_seq OWNER TO u2;
> ERROR:  cannot change owner of sequence "t_serial_id_seq"
> DETAIL:  Sequence "t_serial_id_seq" is linked to table "t_serial".
> 
> Although you can drop it:
> 
> DROP SEQUENCE t_serial_id_seq;
> DROP SEQUENCE
> 
> Anyhoo, I've learned a bit more today, and thanks for your help!
> 
> Cheers,
> Ken
> 
> 
> 
> -- 
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken.tanzer@agency-software.org <mailto:ken.tanzer@agency-software.org>
> (253) 245-3801
> 
> Subscribe to the mailing list 
> <mailto:agency-general-request@lists.sourceforge.net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.


-- 
Adrian Klaver
adrian.klaver@aklaver.com



Re: Primary key data type: integer vs identity

From
Peter Devoy
Date:
Hi Rich

I think you may have serial already there (indicated by sequence in the the default value).  If you wish to change to identity columns this should be useful: https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

Regards


Peter

Re: Primary key data type: integer vs identity

From
Rich Shepard
Date:
On Tue, 23 Apr 2019, Peter Devoy wrote:

> I think you may have serial already there (indicated by sequence in the the
> default value).

Peter,

As serial is an alias for integer (the actual data type) using integer and
specifying it as the primary key works.

> If you wish to change to identity columns this should be useful:
> https://www.2ndquadrant.com/en/blog/postgresql-10-identity-columns/

Nah, I don't see a reason to change. Identity columns obviously have a
benefit for some but not for me.

Thanks for the suggestion,

Rich