Re: Odd behavior with 'currval'

On Thu, 8 Feb 2018, David G. Johnston wrote:

new_db=# \dfS+ currval

List of functions
    Schema   |  Name   | Result data type | Argument data types |  Type  | 
Volatility | Parallel |  Owner   | Security | Access privileges | Language 
| Source code |      Des
cription

------------+---------+------------------+---------------------+--------+------------+----------+----------+----------+-------------------+----------+-------------+---------
---------------
  pg_catalog | currval | bigint           | regclass            | normal | 
volatile   | unsafe   | postgres | invoker  |                   | internal 
| currval_oid | sequence
  current value
(1 row)

> I'll agree this would be highly unusual but I so would this being a 
> bug.  And the oddity with the lost sequence ownership...

So, I believe I have gotten to the bottom of the issue.  Your suggestion 
to stay within psql was the secret.  Not too surprisingly, when I run:

--- code ----

\pset null '(null)'

CREATE TABLE bugtest (
   id BIGSERIAL NOT NULL,
   name VARCHAR(32) NOT NULL,
   PRIMARY KEY (id)
);

INSERT INTO bugtest(name)
VALUES ('one');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

INSERT INTO bugtest(name)
VALUES ('two');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

ALTER SEQUENCE bugtest_id_seq
OWNED BY NONE;

INSERT INTO bugtest(name)
VALUES ('three');

SELECT currval( pg_get_serial_sequence('bugtest','id'));

--- end code ---

I see:

--- output ---

Null display is "(null)".
CREATE TABLE
INSERT 0 1
  currval
---------
        1
(1 row)

INSERT 0 1
  currval
---------
        2
(1 row)

ALTER SEQUENCE
INSERT 0 1
  currval
---------
   (null)  <---- !!!!
(1 row)

-- end output ---

The culprit is in the JDBC domain, NOT PostgreSQL!  According to the 
documentation I found, the ResultSet 'getLong()' method returns a value of 
zero when it sees NULL as an input. Why the JDBC libs don't treat this as 
an invalid numeric conversion is beyond me.

Once again, thanks to everyone who took their time to help me out on this 
issue.  It's a great reminder of the high-quality community that surrounds 
PostgreSQL.



--