Re: Primary key data type: integer vs identity

On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.