Comment Panel Postgres

Ignition
1

Anything unique with Postgres? I can’t get insert 1 to work on the comments panel.

Insert 1

INSERT INTO notes (note, whoId, tstamp, attachment, filename, sticky) VALUES (?, %s, CURRENT_TIMESTAMP, ?, ?, ?)

GatewayException: Gateway Error 301: SQL error for “INSERT INTO Notes (Note, WhoId, TStamp, Attachment, Filename, Sticky) VALUES (?, (SELECT Id FROM Users WHERE Username=‘admin’), CURRENT_TIMESTAMP, ?, ?, ?)”: Returning autogenerated keys is not supported.
caused by PSQLException: Returning autogenerated keys is not supported.

2

How are the columns defined in the table?

3

id = int primary key
whoid= charvar
tstamp=timestamp without time zone
note=charvar
filename=charvar
sticky=boollean

4

When the comments panel does it’s Insert into the database it is basically calling the system.db.runPrepUpdate() which has the option to return an auto-generated key value that’s created as a result of the update completing successfully. Postgres appears to not support the returning of auto-generated keys (as the error message is stating).

As of now the comments panel is not going to work with Postgres. I’ll put in a ticket to have this looked at and see if we can re-factor it in some way that allows the comment panel to be used with Postgres in the future.

5

Postgres does not return autogenerated keys. Mainly because it does not support an autogenerated datatype. More info here.

What you have to do in this case is to select currval() on the sequence name.
Or use a returning clause on the insert statement as discussed here.

6

As of 7.2.7 if you clear out query2, then the comments panel will not run the insert query with the request keys flag, which will let the component work on postgres.

7

Actually, that's not really true any more. It's true that it doesn't have an "autogenerated datatype" per se, but I know for a fact that the new JDBC driver will return generated keys, at least when using the "serial" type (which isn't really a type, but a shortcut to create a sequence and use it for the column).

If you try upgrading the Postgres JDBC driver as described in the other post recently on postgres, you might have better luck.

Regards,

8

I stand corrected. As you say, it’s a new thing.