next »

[Kevin L.]

We are experiencing an issue with Genero and PostgreSQL with the SQLCA.SQLERRD[2] register that we are having difficulty in explaining and/or troubleshooting. We were hoping someone may have seen something similar to this.

The problem is that one of our PostreSQL instances (we have others that are working fine) is incorrectly returning the value zero (0) from the SQLCA.SQLERRD[2] register after an insert into a serial column.

The code we are executing is the following:
 

Code

INSERT INTO table VALUES table.*
LET l_serial_value = SQLCA.SQLERRD[2]

In the instance where this is not working, the table serial value is being set properly to the next sequence and the data in the table is correct. The issue is only that the  SQLCA.SQLERRD[2] register does not return the value. It returns the value zero (0). This instance had been working for months prior to this issue showing up.

This particular PostgreSQL instance is in the Cloud (Amazon RDS). However we have both internal PostgresSQL and Amazon RDS instances that are working correctly.

The initial problem was found in a PostgreSQL 9.6 instance.

To test we created a brand new PostgresSQL 9.5 instance (suspecting the issue might be 9.6 specific). We loaded our data and tested the SQLCA.SQLERRD[2] and everything worked correctly during our initial tests. However, the next day the same tests failed and the SQLCA.SQLERRD[2] was back to returning zero values.

We are looking for ideas or possible troubleshoot techniques to try and identify what might be causing the SQLCA.SQLERRD[2] register to fail.

We appreciate any assistance. Thanks.

[Sebastien F.]

Hi Kevin,

What type of serial emulation do you use?

How big are the last generated serials?
SQLCA.SQLERRD[2] can only hold INTEGERs, not BIGINTs.

http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_008.html

What shows the output with FGLSQLDEBUG=5?

Seb

[Sebastien F.]

Do INSERTs never fill SQLCA.SQLERRD[2] or is the register initialized with serial of first INSERTs and then in subsequent INSERTs it is zero?

Is this happening with temporary tables?

https://4js.com/support/issue/?id=FGL-04825#startissue

Seb

[Kevin L.]

The value is not larger than INT. Serial is in the 7000 range.
The table is a permanent table not temporary.

We have come up with a clue.

Currently any time we insert into table_1 (first, second, doesn't matter) the SQLCA.SQLERRD[2] comes back as zero.

We created a new table, called it  table_2, and did an insert into that table. The SQLCA.SQLERRD[2] worked correctly.

We then unloaded table_1, deleted the table/sequence and then recreated the table and sequence and loaded the data.
Now when we do an insert into that table the SQLCA.SQLERRD[2] is working.

[Kevin L.]

Another note, the problem appears to affect all the DB tables that had a sequence specified.  It is not limited to a specific table. It is something more global.
So we are thinking either something that we did on creation/import of the DB or that occurred after that corrupted or affected the sequences or 4GL driver.

We are going to try and create a fresh DB and confirm that things initial worked correctly on import and then see if they stop working as we originally believe occurred. But we'll do this test under more controlled circumstances.

[Sebastien F.]

I think it is related to the procedure you have used to create the tables.

Knowing what serial emulation you use would help (native/regtable/trigseq?).

http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_odiagpgs_008.html

Have you re-created the table from a BDL program...?
In such case, the ODI driver creates the db objects needed for the specified serial emulation are available (I mean triggers and sequences).
Otherwise, you need to do it by hand (SQL) and make sure that you mimic what the ODI driver does (you can see that with FGLSQLDEBUG log).

Maybe the table was created without the trigger/sequence or, the trigger was disabled?

FGLSQLDEBUG of a program doing an INSERT would also help if you can still repro with other tables.

Seb

[Kevin L.]

We are using native emulation.

Ok the FGLSQLDEBUG does show an error. It cannot find the SEQUENCE for our problem table. 

SQL: INSERT INTO stn_data (jurisdiction,station_id,station_name) VALUES ('XX','XX','XX')
| 4gl source      : stn_data_crud.4gl line=26
| pgs.c:01513(3)  : Nat stmt1 = insert into stn_data (jurisdiction,station_id,station_name) VALUES ('XX','XX','XX')
| pgs.c:00799(4)  : Find table sequence with: [select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('stn_data') and a.adsrc like 'nextval%']
| pgs.c:00809(4)  : No SEQUENCE found for table stn_data
| sqlcode         : 0
| curr driver     : ident='dbmpgs_9'
| curr connection : ident='_1' (dbspec=[rms_8_01_SSD_Impl@impl.ckjkjzkdlnuo.us-gov-west-1.rds.amazonaws.com:5432])
| Execution time  :   0 00:00:00.29758
SQLCA.SQLERRD[1]:          0 - is not used at this time.
SQLCA.SQLERRD[2]:          0 - contains the last SERIAL or the native SQL error code.
SQLCA.SQLERRD[3]:          1 - contains the number of rows processed in the last statement (server dependent).
SQLCA.SQLERRD[4]:          0 - contains the estimated CPU cost for the query (server dependent).
SQLCA.SQLERRD[5]:          0 - contains the offset of the error in the SQL statement text (server dependent).
SQLCA.SQLERRD[6]:    2727320 - contains the ROWID of the last row that was processed (server dependent).

The "find table sequence" works for the table we fixed and does not work the corrupted tables.
So now we just need to figure out why we did not get this entry in the pg_attrdef table.

[Kevin L.]

Thank you for the assistance we have identified the root cause of the issue and we believe it is something you already corrected in BDL 3.00.15:

https://4js.com/support/issue/?id=FGL-04601&back=%2F%3Fstat%3DCLOSED%26fixed_in%3D3.00%26words%3Dpostgres

The issue just presented itself a little differently.

We had been running BDL 3.00.10 in our tests. The PostgreSQL driver SELECT for getting the sequence changed in 3.0015:

BDL 3.00.10
select a.adsrc from pg_class p join pg_attrdef a on (p.relfilenode = a.adrelid) where lower(p.relname) = lower('stn_data') and a.adsrc like 'nextval%'

BDL 3.00.19
select a.adsrc from pg_class p join pg_attrdef a on (p.oid = a.adrelid) where lower(p.relname) = lower('change_log') and a.adsrc like 'nextval%'

When using pg_dump the relfilenode (which is a position on the disk) does not necessarily match the adrelid value any longer.
This value can also change due to a truncate (as in support issue FGL-04601) and we suspect if a vacuum occurs after significant data change.  Which is why we suspect it was working for a long time and then suddenly stopped working.

The issue is resolved by moving to BDL 3.00.19 or higher as the PostgreSQL driver uses the oid field introduced in PostgreSQL 9.4 which does not change due to data changes.

[Sebastien F.]

Ok that's good news, and thank you for sharing the conclusion!

Sorry for the inconvenience.
As you can see the SELECT to find out if a table has a sequence trigger is not that simple and subject of mistakes.
FYI, the other bug FGL-4825 about temp tables + serial will be fixed in next 3.10 maintenance release.

I take the opportunity the remember all readers how important it is to provide the context of your application, with software version information.
If you don't tell us, we assume that you are using the very latest versions of Genero products.

Always try to provide logs (FGLSQLDEBUG for SQL issues, FGLGUIDEBUG for GUI issues, etc...) see:
http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_logging.html

And if your problem cannot be solved quickly in this forum, you will get better assistance through the regular support channel.

About Informix SERIAL emulation:

Keep in mind that SERIAL/BIGSERIAL emulation is provided to ease migration from Informix to PostgreSQL or other DB brands.
At some point, for simplification and better performances, you should consider using portable SQL features.
For auto-generated primary keys, you can for ex use SEQUENCEs as described here:
http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_sql_programming_076.html

To mimic Informix SERIALs, we have to do more as normally required:
After each INSERT in a given program instance, we have to execute the SELECT to check if a trigger/sequence exists for the table, in order to fill SQLCA.SQLERRD[2].
This is optimized: If no serial is found we consider that the table has no serial column and no SELECT will be done after the next INSERT for this table.
But to be 100% Informix compatible with SQLCA.SQLERRD[2] we have no other way...
We could eventually use the .sch schema file to identify what tables have serials but then you would need to ship the .sch file in your application program files.
And that introduces another dependency and complexity.
Compared to a little SELECT after first INSERT it's really not a big cost.

Understand how it works under the cover, and consider suggestions we provide in the SQL Portability chapter.

Once you are using portable SQL,  you can disable the corresponding Informix emulation with dbi.database.* FGLPROFILE entries:
http://4js.com/online_documentation/fjs-fgl-manual-html/#fgl-topics/c_fgl_Connections_015.html

Cheers!
Seb