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.

--