Search Postgresql Archives

Re: Odd behavior with 'currval'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.



--

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux