Search Postgresql Archives

Re: Primary key data type: integer vs identity

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux