topic PGSQL UPDATE - Error updating table entry: the content was changed before updation in SAS Programming

PGSQL UPDATE - Error updating table entry: the content was changed before updation

AllanBowe — Fri, 30 Jul 2021 13:22:40 GMT

Here's my code:

libname mylib postgres
  preserve_col_names=no
  preserve_tab_names=no
  database=defaultdb
  server="db-postgresql-server"
  port=25060 user=doadmin
  password="pass"
  conopts="sslmode=require"
  DBCLIENT_MAX_BYTES=1
  dbmax_text=32767
  schema=myschema;

proc sql;
update mylib.mpe_users
  set last_seen_dt=%sysfunc(today())
  where user_id="allbow";
quit;

And here is the response in the log:

ERROR: Error updating table entry: the content was changed before updation

Any clues? Perhaps there is another libname option required..

Further info:

SYSVLONG=V.03.05M0P111119 SYSSCP=LIN X64

Update - with SASTRACE:

95     proc sql;
96         update mylib.mpe_users
97           set last_seen_dt=%sysfunc(today())
98           where user_id="allbow";
WARNING: This SQL statement is not allowed to be passed directly to the DBMS for processing because the DBIDIRECTEXEC system option 
         is not turned on by the user or is temporarily turned off by Proc SQL.
POSTGRES: AUTOCOMMIT is NO for connection 1 0 1627651254 no_name 0 SQL     
POSTGRES: COMMIT performed on connection 1. 1 1627651254 no_name 0 SQL     
  2 1627651254 no_name 0 SQL     
POSTGRES_1: Prepared: on connection 1 3 1627651254 no_name 0 SQL     
SELECT * FROM "myschema".MPE_USERS 4 1627651254 no_name 0 SQL     
  5 1627651254 no_name 0 SQL     
  6 1627651254 no_name 0 SQL     
POSTGRES_2: Prepared: on connection 1 7 1627651254 no_name 0 SQL     
SELECT  "user_id", "last_seen_dt"  FROM "myschema".MPE_USERS  WHERE  ( "user_id" = 'allbow' ) 8 1627651254 no_name 0 SQL     
  9 1627651254 no_name 0 SQL     
  10 1627651254 no_name 0 SQL     
POSTGRES_3: Executed: on connection 1 11 1627651254 no_name 0 SQL     
Prepared statement POSTGRES_2 12 1627651254 no_name 0 SQL     
  13 1627651254 no_name 0 SQL     
POSTGRES: Called SQLSetPos(UPDATE)  14 1627651255 no_name 0 SQL     
POSTGRES: ROLLBACK performed on connection 1. 15 1627651255 no_name 0 SQL     
ERROR: Error updating table entry: the content was changed before updation

Re: PGSQL UPDATE - Error updating table entry: the content was changed before updation

AllanBowe — Fri, 30 Jul 2021 13:24:48 GMT

The clue was in the SASTRACE response.

options DBIDIRECTEXEC;

fixed it.

Re: PGSQL UPDATE - Error updating table entry: the content was changed before updation

jimbarbour — Fri, 30 Jul 2021 16:36:41 GMT

OK, so in order to update properly, the update really needed to be executed by the DBMS rather than SAS doing some kind of implicit pass through, is that correct?

And since DBIDIRECTEXEC was not turned on, the update could not proceed. The solution then was to turn on DBIDIRECTEXEC, yes?

OK, good to know.

I too have found SASTRACE to be invaluable when working with external databases. For what it's worth, I've found the below to be the most useful set of options for SASTRACE.

OPTIONS 	SASTRACE		=	",,,ds";
OPTIONS 	SASTRACELOC		=	SASLOG;
OPTIONS 	NOSTSUFFIX;

Jim

Re: PGSQL UPDATE - Error updating table entry: the content was changed before updation

AllanBowe — Fri, 30 Jul 2021 19:02:52 GMT

Yes, it seems that way! Google didn't throw up much on this particular error.. Thanks for sharing your trace options!

Re: PGSQL UPDATE - Error updating table entry: the content was changed before updation

jimbarbour — Fri, 30 Jul 2021 19:33:22 GMT

@AllanBowe wrote:
Thanks for sharing your trace options!

You're welcome. I hope they're helpful. A lot of people just code ',,,d' (with out the s), but I like to see how many seconds the DBMS used. The seconds can be helpful in understanding performance and in tuning queries.

Also DEBUG=DBMS_SELECT can be helpful. SASTRACE basically describes what the DBMS is doing. DEBUG=DBMS_SELECT tells you a bit more information than normal about what SAS is doing, primarily with implicit pass through queries. DEBUG=DBMS_SELECT isn't very helpful if all you're doing is explicit pass through queries.

Jim