Re: Primary key data type: integer vs identity

On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 4/19/19 1:02 PM, Ken Tanzer wrote:
> > On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)com <mailto: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.
>
>
>
> 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
Owned by: 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/ <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.