Postgresql sequences

Wed Feb 9 17:49:12 UTC 2022

hi mike.

if you have created a ’serial’ column in your table (sounds like you have — called te_id)…

AND

you are using omnis table classes to handle your inserts

THEN

over ride your $insert and make the following change to the sql

assuming omnis creates the sql with bind variables.

insert into te (te_id,val) values ([$cinst.te_id], [$cinst.val])

you need to change that into one of the two below.

option 1:     change the bind variable for  [$cinst.te_id] and replace with  DEFAULT

insert into te (te_id,val) values (DEFAULT, [$cinst.val]) returning te_id

option 2:    remove te_id from the statement altogether

insert into te (val) values ([$cinst.val]) returning te_id

note also, the returning te_id

that will cause postgres to give that back to you.   within the $insert to have to do an additonal fetch and replace into $cinst.

eg steps are
manipulate the sql
add the returning

prepare the statement
send the statement
fetch the result into returnList

calc $cinst.te_id as returrnlist.te_id

we have found this to be about 20% faster than getting a seq # independently and then sticking it into a te_id.

for what it is worth, here is what we have put in our $insert in our base table class used for many many years that handles this case.

yes, I have a function called $prepareinsert which is part of TMObjs and assumes all variable names are prefixed with a key for the table (eg ’te_'  and that all variables in the table have underscores.   such as

te_id and te_val (i n your case).   the purpose of the function is quickly identify ONLY FIELD THAT HAVE CHANGED ….  its saves traffic sending large binaries if they are empty.

you can build your own equivalent — or if you currently use TMobjs (its free), then you can use the statement

the key in the code is that it does all the above in my sample, including the fetch so you. can see it in acton.

parameter pCheckDataYN default kfalse
insertstring char
otherThanStandardField int default 0
returncols char  default xx_id
retrunValuesRow row
Statement char
statusok integer
tablename char
tempPrefix char

# If the PrimaryKey hasn't been assigned, set it now.
# Set the Standard Column values.
# Check the Data if the optional parameter was set to kTrue to check the data before $insert.
# All going well, we can proceed with the insert using "Do default".
#
# -> pCheckDataYN ## If the calling method says kFalse, skip calling $CheckData
# Update the record with any final settings before inserting

# Set the Standard Column values while we are doing insert
Do $cinst.$setRecordChangedSettings
Do $cinst.$preUpdateSettings ## Will never fail.   Old row is empty
# Set the BLOB data type columns to NULL
Do tStringFields.$setsqlzeronullvalues($cinst.$colcount)

Calculate TableName as $cinst.$servertablenames

If pos(',',TableName)>0 ## A comma means more than one table is in the FROM portion of the insert.  This means we are inserting from a query.
# Insertion from query, must do special behaviour.  Ignore all columns that are not from the main table
Calculate TableName as $cinst.$getMainSQLTable ## V81000 DM - Allow inserting from queries
Calculate tempPrefix as con(iFieldNamePrefix,pick(iFieldNamePrefix='CD','','_')) ## V1006112 workaround for bug in $prepareinsert where two tables start with similar prefixes for credit cards
Do tStringFields.$prepareinsert(InsertString,otherThanStandardField,$cinst.$colcount,"DATE_UPDATED`DATE_ENTERED`EDITNUM`CHANGED_BY_E_SEQ",tempPrefix)
Else
Calculate InsertString as $cinst.$insertnames()
End If

# Ensure that we have an insert string before trying to send it to the server.  If not, then we know it will fail.
If len(InsertString)>0
Calculate returnCols as ''
Do $cinst.$replaceInsertDefaults(InsertString,returnCols)
# add the returning clause for fields that have server side defaults or that the table class indicates as being returned
Calculate Statement as con('insert into ',TableName,' ',InsertString)
If len(returnCols)
Calculate Statement as con(Statement,' returning ',returnCols)
End If

# All going well, we can proceed with the insert by calling the default method.
Do $cinst.$sendSQLdirect(Statement) Returns StatusOK

Else
Calculate StatusOK as kFalse
End If

If StatusOK
# Retrieve the last key set
If len(returnCols)
Do iStatementRef.$fetch(returnValuesRow,1)
Do $cinst.$assignrowTM(returnValuesRow,kTrue)
End If

# Do any updates that are required to the fields after the database is updated.
# Also if we updated any child records, then update the parent as well.
Do $cinst.$setVariablesWhenRecordInserted ## only called on insert
Do $cinst.$postUpdateSettings ## called on insert and update

# V100677 set the cache after insertint the record - if same schema, will add to cache record
If iSetCacheAfterUpdate&not(tUpgradeInProgress) ## see if we poke the cache record (never when update in progress)
Do tDataCache.$UpdateCacheRecord($cinst.$getUniqueKeyField,$cinst.$getUniqueKey,$cinst) Returns #F
If flag false
Breakpoint what happened
End If
End If

Quit method kTrue
End If

# Insert was not OK? Need to add code to elegantly figure out why and report to the user.
If tStringFields.$isFullVersion
Do $cinst.$msgOk($cinst.$ref,TMObjs.$makeparamrow('Sound',kTrue,'Message',con('Developer Message:',kCr,kCr,'Insert of record has failed.  We need to complete a generic method for giving the user more information about why the insert failed.')))
Calculate #S1 as iStatementRef.$sqltext
Calculate #S2 as iStatementRef.$obpreparedstatement
Calculate #S3 as iStatementRef.$obexecutedstatement
Calculate #S4 as iStatementRef.$errortext()
Calculate #S5 as iStatementRef.$nativeerrortext()
Breakpoint 
End If
Quit method kFalse

Calculate pCheckDataYN as pCheckDataYN

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at artsman.com
http://www.artsman.com
Phone (403) 650-1978

> On February 8, 2022, at 3:09 PM, Mike Rowan <michael.rowan3 at gmail.com> wrote:
> 
> I could really do with a strong hint on this one.
> 
> Omnis 10.2 on Mac
> Postgresql 14.1
> I have not until now used sequences preferring to roll my own $inserts in
> my tables.  However, I think I now should in order to fully utilise all
> the  Postgresql offers.
> 
> So, I have created new table te with two columns te_id (serial) and val
> (integer)
> 
> In pgAdmin4 I can insert new rows with auto-generated te_id. So, the pg
> part works fine.
> 
> In Omnis I have a test window with a list (iList) and a row (iRow) both
> defined from a table class 'tte'.
> In the window $construct I run Do iList.$select() and then Do
> iList.$fetch(kfetchall) to populate the list. The select and fetch work
> correctly, so I know the table class is constructed correctly.
> 
> In the table class I have left $insert untouched, so it uses the built-in
> method.
> On the window, for iRow, only iRow.val is displayed as an entry field.
> te_id is not displayed.
> 
> When I attempt to save a new value (42), the values in iRow are (null,42).
> An error is thrown by the table class $insert ('Statement could not be
> executed.')
> 
> I don't see how to find out why this is happening, unless some kind soul
> can enlighten me.  After three days trying this and that, I'd be so glad.
> 
> I have a feeling this is to do with the contents of iRow.te_id. If not
> null, what should it be? A real-life example here would be very useful.
> 
> Mike
> _____________________________________________________________
> Manage your list subscriptions at https://lists.omnis-dev.com
> Start a new message -> mailto:omnisdev-en at lists.omnis-dev.com 