Re: Primary key data type: integer vs identity - Mailing list pgsql-general

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