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.