topic Query results on greenplum/Postgres boolean variable in SAS Programming

Query results on greenplum/Postgres boolean variable

BijZuiderduin — Tue, 13 Jun 2023 16:10:31 GMT

I query a Greenplum/Postgres database on a Boolean variable with a similar query in two different ways. Functionaly they are the same but I get different results. Can anybody explain this and perhaps help me with a solution?

Functionally I need the two different ways in the application as a before and after check
Unfortunately I cannot access the database directly.

Example 1 directly on the greenplum table

create table countdirect as

"before" as method length=15,

count(*) as countdirect

from lib.table

where booleanfield;

Example 2 in 2 steps via SAS table

create table temp_table (compress=yes) as select

base.c_custom_documentid as id ,

from lib.table base

where booleanfield ;

create table countindirect as

"after" as method length=15,

count(*) as countindirect

from temp_table;

Remarks:

1. ‘booleanfield’ is a Greenplum/Postgres Boolean variable

2. Running in SAS DI and SAS EG both examples deliver matching results

3. Running in LSF the results of Example 1 and 2 are different

4. When running from LSF the results of the first example differ per run

5. The result of Example 2 are constant and seem correct

The results per run:

	before	after
Day 1 run 1	1.285.585	1.287.365
Day 1 run 2	1.285.521	1.287.365
Day 2 run 1	1.285.586	1.287.365
Day 2 run 2	1.285.521	1.287.365

Re: Query results on greenplum/Postgres boolean variable

s_lassen — Thu, 15 Jun 2023 07:39:05 GMT

I assume the by "LSF" you mean either the IBM Load Sharing Facility, or the similar named facility from Platfor Computing (or maybe the two are the same?). I am not sure, but I think that what happens is that when querying in LSF, some records are not read, possibly because they are locked by other users, as the result from the first run is always smaller than the result from the second run.

What to do about it? Sorry, no idea.

Re: Query results on greenplum/Postgres boolean variable

BijZuiderduin — Thu, 15 Jun 2023 08:12:07 GMT

Thx for your reaction. I think the 2 LSF's are indeed the same.
Regarding your suggestion that records may be locked using the first example: this does not happen using the second example; to me this seems to indicate another cause.