On 4/19/19 2:31 PM, Ken Tanzer wrote:
On Fri, Apr 19, 2019 at 1:39 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
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
Well the table was created by one user and the serial was changed to
IDENTITY by another.
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".
That is covered here:
https://www.postgresql.org/docs/11/sql-altersequence.html
"OWNED BY table_name.column_name
...
The specified table must have the same owner and be in the same schema
as the sequence. ..."
Which to me means the opposite is true also.
Though you can:
" Specifying OWNED BY NONE removes any existing association, making
the sequence “free-standing”.
"
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 <http://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//
/https://demo.agency-software.org/client/
ken.tanzer@xxxxxxxxxxxxxxxxxxx <mailto:ken.tanzer@xxxxxxxxxxxxxxxxxxx>
(253) 245-3801
Subscribe to the mailing list
<mailto:agency-general-request@xxxxxxxxxxxxxxxxxxxxx?body=subscribe> to
learn more about AGENCY or
follow the discussion.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
