From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
Cc: | Rich Shepard <rshepard(at)appl-ecosys(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Primary key data type: integer vs identity |
Date: | 2019-04-19 20:38:55 |
Message-ID: | 47f1fa10-fb71-0f58-9dcf-c5083680059a@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
>
> Ken
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> /http://agency-software.org//
> /https://demo.agency-software.org/client/
> ken(dot)tanzer(at)agency-software(dot)org <mailto:ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list
> <mailto:agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
> learn more about AGENCY or
> follow the discussion.
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ken Tanzer | 2019-04-19 21:31:38 | Re: Primary key data type: integer vs identity |
Previous Message | Ken Tanzer | 2019-04-19 20:02:10 | Re: Primary key data type: integer vs identity |