Odd behavior with 'currval'

On Thu, Feb 8, 2018 at 9:09 AM, Steven Hirsch <snhirsch@gmail.com> wrote:

I have a body of code using JDBC to work with a PostgreSQL 9.6 database.
All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids. All are
working correctly in terms of using the next value as a default. However,
reading back the most recently applied (currval) value is failing for one
table, where it always returns '0'. Note that the table data shows the
expected value when queried by SELECT! It is only the currval() function
that is wrong. I am properly guarding for SQL exceptions and none are
being thrown.

The code being used in the failing case is not the slightest bit different
from the working cases in terms of structure and transaction control - only
the SQL, column count, etc. is different (but correctly formed and in all
other ways functional).

I'm not sure where to start debugging this. Can anyone give me even a
working theory to explain how returning a bogus value is possible? When I
look at the sequences in pgAdmin, they are as expected in terms of
ownership, etc. And, again, the table IS getting the correct value.

Its seems that whatever name you are passing into currval must match an
existing sequence ​but it doesn't match the sequence name that is attached
to the table.col default expression in question.

search_path considerations might play a role if its not a simple copy-paste
type error...but its hard to blindly suggest potential reasons.

David J.

On 02/08/2018 08:09 AM, Steven Hirsch wrote:

I have a body of code using JDBC to work with a PostgreSQL 9.6 database.
All tables use 'SERIAL' or 'BIGSERIAL' types to generate ids.  All are
working correctly in terms of using the next value as a default.
However, reading back the most recently applied (currval) value is
failing for one table, where it always returns '0'. Note that the table
data shows the expected value when queried by SELECT!  It is only the
currval() function that is wrong.  I am properly guarding for SQL
exceptions and none are being thrown.

The code being used in the failing case is not the slightest bit
different from the working cases in terms of structure and transaction
control - only the SQL, column count, etc. is different (but correctly
formed and in all other ways functional).

Can we see the table schema and the currval code?

Is there anything in the Postgres logs that might help?

I'm not sure where to start debugging this.  Can anyone give me even a
working theory to explain how returning a bogus value is possible?  When
I look at the sequences in pgAdmin, they are as expected in terms of
ownership, etc.  And, again, the table IS getting the correct value.

Thanks much for any ideas!

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 8, 2018 at 5:09 PM, Steven Hirsch <snhirsch@gmail.com> wrote:

I have a body of code using JDBC to work with a PostgreSQL 9.6 database. All

......

The code being used in the failing case is not the slightest bit different
from the working cases in terms of structure and transaction control - only
the SQL, column count, etc. is different (but correctly formed and in all
other ways functional).

Something must be different. As requested by others, try posting the
SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

I'm not sure where to start debugging this. Can anyone give me even a
working theory to explain how returning a bogus value is possible? When I
look at the sequences in pgAdmin, they are as expected in terms of
ownership, etc. And, again, the table IS getting the correct value.

It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Francisco Olarte.

On Thu, 8 Feb 2018, Francisco Olarte wrote:

Something must be different. As requested by others, try posting the
SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

Fair enough. Here is the DDL:

CREATE TABLE udm_asset_type_definition (
def_id BIGSERIAL NOT NULL,
def_name VARCHAR(32) NOT NULL,
PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

I get back NULL (doesn't matter if I qualify with schema - everything is
in a schema called 'main' and that is first on the search path). All
other sequences in the database (created exactly the same way, through
definition as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but
that too returns NULL. So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_asset_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly. But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two
different databases on different servers that were created with the same
DDL.

--

On 02/08/2018 09:58 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Francisco Olarte wrote:

Something must be different. As requested by others, try posting the
SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

Fair enough.  Here is the DDL:

CREATE TABLE udm_asset_type_definition (
  def_id BIGSERIAL NOT NULL,
  def_name VARCHAR(32) NOT NULL,
  PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition > out.sql

I get back NULL (doesn't matter if I qualify with schema - everything is
in a schema called 'main' and that is first on the search path).  All
other sequences in the database (created exactly the same way, through
definition as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0',
but that too returns NULL.  So, where is the '0' coming from when I do:

SELECT currval(
pg_get_serial_sequence('udm_asset_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

It shouldn't be, this I why several perople are requesting to see the
relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly.  But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two
different databases on different servers that were created with the same
DDL.

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/08/2018 09:58 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Francisco Olarte wrote:

Something must be different. As requested by others, try posting the

SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

Fair enough. Here is the DDL:

CREATE TABLE udm_asset_type_definition (
def_id BIGSERIAL NOT NULL,
def_name VARCHAR(32) NOT NULL,
PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition > out.sql

I get back NULL (doesn't matter if I qualify with schema - everything is
in a schema called 'main' and that is first on the search path). All other
sequences in the database (created exactly the same way, through definition
as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but
that too returns NULL. So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_as
set_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

It shouldn't be, this I why several perople are requesting to see the

relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly. But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two
different databases on different servers that were created with the same
DDL.

--
Adrian Klaver
adrian.klaver@aklaver.com

*I believe your problem is in your usage.In order for currval(regclass) to
work, you must first do a SELECT nextval(regclass) in your _current
transaction_!https://www.postgresql.org/docs/9.6/static/functions-sequence.html
<https://www.postgresql.org/docs/9.6/static/functions-sequence.html&gt;Function
Return Type Descriptioncurrval(regclass) bigint
Return value most recently obtained with nextval for specified sequence*--

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Thu, 8 Feb 2018, Adrian Klaver wrote:

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

I get:

udm_asset_type_definition_def_id_seq 21 1 1 9223372036854775807 1 1 32 false true

SELECT currval('udm_asset_type_definition_id_seq');

I get:

[Code: , SQL State: 42P01] ERROR: relation
"udm_asset_type_definition_id_seq" does not exist
Position: 16

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition > out.sql

This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner:
asset_registry_admins
--

CREATE TABLE udm_asset_type_definition (
def_id bigint DEFAULT nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
def_name character varying(32) NOT NULL,
);

ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition udm_asset_type_definition_def_name_key;
Type: CONSTRAINT; Schema: main; Owner: asset_registry_admins
--

ALTER TABLE ONLY udm_asset_type_definition
ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE
(def_name);

--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner:
asset_registry_admins
--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;

--

On Thu, Feb 8, 2018 at 1:17 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

On Thu, Feb 8, 2018 at 1:09 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/08/2018 09:58 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Francisco Olarte wrote:

Something must be different. As requested by others, try posting the

SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

Fair enough. Here is the DDL:

CREATE TABLE udm_asset_type_definition (
def_id BIGSERIAL NOT NULL,
def_name VARCHAR(32) NOT NULL,
PRIMARY KEY (def_id)
);

When I look at the column definition, I see:

nextval('udm_asset_type_definition_def_id_seq'::regclass)

When I look at the catalog, I can see a sequence:

udm_asset_type_definition_def_id_seq

That appears identical to the column default definition and it has the
expected 'last_value'.

Here's the odd part: If I issue

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

SELECT currval('udm_asset_type_definition_id_seq');

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition > out.sql

I get back NULL (doesn't matter if I qualify with schema - everything is
in a schema called 'main' and that is first on the search path). All other
sequences in the database (created exactly the same way, through definition
as 'BIGSERIAL' type) are properly found.

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0',
but that too returns NULL. So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_as
set_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

It shouldn't be, this I why several perople are requesting to see the

relevant code. Experience says lots of this fails are pilot error.

As an aside, with recent postgres versions you can normally use the
returning construct to grab autogenerated id. I.e., instead of "insert
blah-blah-blah, select currval(), whatever else" you can many times do
"insert balh-blah-blah returning auto_gen_column, whatever else". I've
used it a lot, and normally leads to shorter/easier/faster code.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly. But, again, not necessary for any of the other tables.

This problem is not a transient fluke - I can reproduce it in two
different databases on different servers that were created with the same
DDL.

--
Adrian Klaver
adrian.klaver@aklaver.com

*I believe your problem is in your usage.In order for currval(regclass) to
work, you must first do a SELECT nextval(regclass) in your _current
transaction_!https://www.postgresql.org/docs/9.6/static/functions-sequence.html
<https://www.postgresql.org/docs/9.6/static/functions-sequence.html&gt;Function
Return Type Descriptioncurrval(regclass) bigint
Return value most recently obtained with nextval for specified sequence*--

*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

*FYI, as an alternative, you can also do:SELECT last_value FROM
udm_asset_type_definition_def_id_seq;*

*That should always work.*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 02/08/2018 10:20 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Adrian Klaver wrote:

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

I get:

udm_asset_type_definition_def_id_seq    21    1    1
9223372036854775807    1    1    32    false    true

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');

I get:

[Code: , SQL State: 42P01]  ERROR: relation
"udm_asset_type_definition_id_seq" does not exist
  Position: 16

Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition >
out.sql

This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner:
asset_registry_admins
--

CREATE TABLE udm_asset_type_definition (
    def_id bigint DEFAULT
nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
    def_name character varying(32) NOT NULL,
);

ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition
udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main;
Owner: asset_registry_admins
--

ALTER TABLE ONLY udm_asset_type_definition
    ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE
(def_name);

--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner:
asset_registry_admins
--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;

--
Adrian Klaver
adrian.klaver@aklaver.com

On 02/08/2018 10:20 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Adrian Klaver wrote:

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

I get:

udm_asset_type_definition_def_id_seq    21    1    1
9223372036854775807    1    1    32    false    true

What does the below show?:

\d udm_asset_type_definition_def_id_seq

SELECT currval('udm_asset_type_definition_id_seq');

I get:

[Code: , SQL State: 42P01]  ERROR: relation
"udm_asset_type_definition_id_seq" does not exist
  Position: 16

Also what happens if you do:

pg_dump -d db_name -U some_user -s  -t udm_asset_type_definition >
out.sql

This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner:
asset_registry_admins
--

CREATE TABLE udm_asset_type_definition (
    def_id bigint DEFAULT
nextval('udm_asset_type_definition_def_id_seq'::regclass) NOT NULL,
    def_name character varying(32) NOT NULL,
);

ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition
udm_asset_type_definition_def_name_key; Type: CONSTRAINT; Schema: main;
Owner: asset_registry_admins
--

ALTER TABLE ONLY udm_asset_type_definition
    ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE
(def_name);

--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner:
asset_registry_admins
--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, Feb 8, 2018 at 1:27 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 02/08/2018 10:20 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Adrian Klaver wrote:

What if you do?:

SELECT * FROM udm_asset_type_definition_def_id_seq;

I get:

udm_asset_type_definition_def_id_seq 21 1 1
9223372036854775807 1 1 32 false true

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');

I get:

[Code: , SQL State: 42P01] ERROR: relation "udm_asset_type_definition_id_seq"
does not exist
Position: 16

Also what happens if you do:

pg_dump -d db_name -U some_user -s -t udm_asset_type_definition >
out.sql

This:

--
-- Name: udm_asset_type_definition; Type: TABLE; Schema: main; Owner:
asset_registry_admins
--

CREATE TABLE udm_asset_type_definition (
def_id bigint DEFAULT nextval('udm_asset_type_definition_def_id_seq'::regclass)
NOT NULL,
def_name character varying(32) NOT NULL,
);

ALTER TABLE udm_asset_type_definition OWNER TO asset_registry_admins;

--
-- Name: udm_asset_type_definition udm_asset_type_definition_def_name_key;
Type: CONSTRAINT; Schema: main; Owner: asset_registry_admins
--

ALTER TABLE ONLY udm_asset_type_definition
ADD CONSTRAINT udm_asset_type_definition_def_name_key UNIQUE
(def_name);

--
-- Name: udm_asset_type_definition; Type: ACL; Schema: main; Owner:
asset_registry_admins
--

GRANT SELECT ON TABLE udm_asset_type_definition TO asset_registry_readers;
GRANT ALL ON TABLE udm_asset_type_definition TO asset_registry_writers;

--
Adrian Klaver
adrian.klaver@aklaver.com

*FYI, further down in Sequence Manipulation Functions is*

*" **currval*

*Return the value most recently obtained by nextval for this sequence in
the current session. (An error is reported if nextval has never been called
for this sequence in this session.) Because this is returning a
session-local value, it gives a predictable answer whether or not other
sessions have executed nextval since the current session did.*

* " *

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Steven:

On Thu, Feb 8, 2018 at 6:58 PM, Steven Hirsch <snhirsch@gmail.com> wrote:

On Thu, 8 Feb 2018, Francisco Olarte wrote:

Something must be different. As requested by others, try posting the
SQL code chunks, more eyeballs make bugs shallower ( it's happened
several times to me, make a typo, go over it for half an hour, grab a
colleague, she immediately points to it )

Fair enough. Here is the DDL:

... nice and fast response, good explanation. Although it doesn't seem
to be ( due to the formatting ) the actual JDBC code chunks. I meant
that ones for the more eyeballs thing because you may have an actual
typo in them. Given you have taken a nice effort I suppose you have
already triple checked them, so you do not have something like

stmt.execute("select currval('uMD_asset_type_definition......

or a more devious one.

.....

SELECT pg_get_serial_sequence('udm_asset_type_definition','def_id')
I get back NULL (doesn't matter if I qualify with schema - everything is in
a schema called 'main' and that is first on the search path). All other
sequences in the database (created exactly the same way, through definition
as 'BIGSERIAL' type) are properly found.

That is certainly strange. Seems like the sequence wasn't created by
serial code.

I would try to look carefully at the dumps and insure the columns /
schemas are all correct, that nothing has fallen into the public
schema instead of the min one you told us you used. By I do not
remember the exact commands, it should not be too difficult.

Try comparing the definitions of this sequence and a working one in
the catalogs, and the definition of the associated columns, to see if
any field varies, I would suspect something like this.

Yes, I changed the code to 'INSERT .. RETURNING ..' and that works
correctly. But, again, not necessary for any of the other tables.

I pointed it not as a necesity, but as a normally better way to grab
autogenerated id's, as this is a useful way to get the values inserted
by defaults without having to know what expression is in the default,
and even when the expression is non-repeateable, something like
"default generate_random_uuid()", or when you insert more than one
row.

The problem with the sequence must be solved, even if I convince you
my way is better and you stop using it. I would never be in peace
knowing I have a malfunctioning sequence in the database.

Francisco Olarte.

On Thu, 8 Feb 2018, Melvin Davidson wrote:

I believe your problem is in your usage.
In order for currval(regclass) to work, you must first do a
SELECT nextval(regclass) in your _current transaction_!

https://www.postgresql.org/docs/9.6/static/functions-sequence.html

I AM doing that. It is returning zero. The code is identical to that
used in all other tables in the schema - all of them work. This one does
not - unless I use the INSERT .. RETURNING .. approach.

I suspect the weirdness with the sequence not being found is part of the
underlying problem.

--

On 02/08/2018 10:45 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Melvin Davidson wrote:

I believe your problem is in your usage.
In order for currval(regclass) to work, you must first do a
SELECT nextval(regclass) in your _current transaction_!

https://www.postgresql.org/docs/9.6/static/functions-sequence.html

I AM doing that.  It is returning zero.  The code is identical to that
used in all other tables in the schema - all of them work.  This one
does not - unless I use the INSERT .. RETURNING ..  approach.

I suspect the weirdness with the sequence not being found is part of the
underlying problem.

I suspect that also, hence my previous suggestion:

\d udm_asset_type_definition_def_id_seq

This should show whether the sequence is 'owned' by the table.column.

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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

--

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

On Thu, 8 Feb 2018, Adrian Klaver wrote:

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

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”.

Agggh. That's it! I'll fix the ownership.

So, a few questions:

1. How on earth did this happen? I do not recall doing any manual fiddling
with either database - they were (as far as I know) built from the same
DDL. We may never have an answer for this. Being human, who knows what I
may or may not have done 4 months ago...

But,

2. Why is the currval() function being so blasted dumb? If
'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL.
As such, shouldn't the outer currval() also be returning NULL? I cannot
imagine a rationale for the current behavior.

THANKS to everyone who chimed in on this. I was beginning to think I was
losing my marbles.

--

On Thu, Feb 8, 2018 at 10:58 AM, Steven Hirsch <snhirsch@gmail.com> wrote:

On a hunch, I tried 'SELECT currval(NULL)' to see if it returned '0', but
that too returns NULL. So, where is the '0' coming from when I do:

SELECT currval( pg_get_serial_sequence('udm_as
set_type_definition','def_id'))

? I've already established that the inner expression evaluates to NULL!

​This is indeed unusual...to be specific here pg_get_serial_sequence
returns null in lieu of an error for being unable to locate the indicated
sequence. currval is returning null because it is defined "STRICT" and so
given a null input it will always return null. currval itself, when
provided a non-null input, is going to error or provide a number (which
should never be zero...).

I'm wondering whether someone didn't like the fact that currval errors and
instead wrote a overriding function that instead returns zero?

David J.

On 02/08/2018 11:12 AM, Steven Hirsch wrote:

On Thu, 8 Feb 2018, Adrian Klaver wrote:

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

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”.

Agggh.  That's it!  I'll fix the ownership.

So, a few questions:

1. How on earth did this happen? I do not recall doing any manual
fiddling with either database - they were (as far as I know) built from
the same DDL. We may never have an answer for this.  Being human, who
knows what I may or may not have done 4 months ago...

https://www.postgresql.org/docs/10/static/datatype-numeric.html#DATATYPE-SERIAL

"The data types smallserial, serial and bigserial are not true types,
but merely a notational convenience for creating unique identifier
columns (similar to the AUTO_INCREMENT property supported by some other
databases). In the current implementation, specifying:

CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
"

So the only way I know this can happen is:

ALTER SEQUENCE seq_name OWNED BY NONE;

But,

2. Why is the currval() function being so blasted dumb?  If
'pg_get_serial_sequence' cannot resolve the sequence, it returns NULL.
As such, shouldn't the outer currval() also be returning NULL?  I cannot
imagine a rationale for the current behavior.

Someone else will need to comment on this as I have no idea.

THANKS to everyone who chimed in on this.  I was beginning to think I
was losing my marbles.

--
Adrian Klaver
adrian.klaver@aklaver.com