On Fri, Apr 19, 2019 at 12:50 PM Adrian Klaver <adrian.kla...@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. 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.tan...@agency-software.org (253) 245-3801 Subscribe to the mailing list <agency-general-requ...@lists.sourceforge.net?body=subscribe> to learn more about AGENCY or follow the discussion.