Re: Odd behavior with 'currval'

On 02/08/2018 10:54 AM, Steven Hirsch wrote:
> On Thu, 8 Feb 2018, Adrian Klaver wrote:
> 
>>>> SELECT currval('udm_asset_type_definition_id_seq');
>>
>> Arrgh my mistake, the above should have been
>>
>> SELECT currval('udm_asset_type_definition_def_id_seq');
> 
> [Code: , SQL State: 55000]  ERROR: currval of sequence 
> "udm_asset_type_definition_def_id_seq" is not yet defined in this session
> 
> Which is what I'd expect at the psql command line.
> 
> In real-life, I am calling from JDBC in a single session - in fact, from 
> inside a single transaction.
> 
> I will post the JDBC code as soon as I can cut it down to the pertinent 
> area.
> 
> Also, since you request it:
> 
> [hirschs@ui24-lin ~]$ psql -d asset_registry -U hirschs
> psql (9.6.5)
> Type "help" for help.
> 
> asset_registry=# \d udm_asset_type_definition_def_id_seq
>        Sequence "main.udm_asset_type_definition_def_id_seq"
>      Column     |  Type   |                Value
> ---------------+---------+--------------------------------------
>   sequence_name | name    | udm_asset_type_definition_def_id_seq
>   last_value    | bigint  | 21
>   start_value   | bigint  | 1
>   increment_by  | bigint  | 1
>   max_value     | bigint  | 9223372036854775807
>   min_value     | bigint  | 1
>   cache_value   | bigint  | 1
>   log_cnt       | bigint  | 32
>   is_cycled     | boolean | f
>   is_called     | boolean | t

For comparison, from one of my databases:

hplc=> \d student_attendance_attendance_id_seq
      Sequence "public.student_attendance_attendance_id_seq"
     Column     |  Type   |                Value
---------------+---------+--------------------------------------
  sequence_name | name    | student_attendance_attendance_id_seq
  last_value    | bigint  | 39590
  start_value   | bigint  | 1
  increment_by  | bigint  | 1
  max_value     | bigint  | 9223372036854775807
  min_value     | bigint  | 1
  cache_value   | bigint  | 1
  log_cnt       | bigint  | 0
  is_cycled     | boolean | f
  is_called     | boolean | t
Owned by: public.student_attendance.attendance_id


> 
> To me, the greater mystery is why 'pg_get_serial_sequence' is unable to 
> find that sequence when invoked by table + column.

Assuming you showed the complete output I am not seeing the Owned by: 
for your sequence. I would do the \d on one of your sequences that 
'works', I am guessing you will see Owned by: .

To correct see:

https://www.postgresql.org/docs/10/static/sql-altersequence.html

"OWNED BY table_name.column_name
OWNED BY NONE

     The OWNED BY option causes the sequence to be associated with a 
specific table column, such that if that column (or its whole table) is 
dropped, the sequence will be automatically dropped as well. If 
specified, this association replaces any previously specified 
association for the sequence. The specified table must have the same 
owner and be in the same schema as the sequence. Specifying OWNED BY 
NONE removes any existing association, making the sequence “free-standing”.
"

> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com