postgresql – How to prevent different connections treating bytea in same query differently?

I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.

A query that demonstrated the behaviour is this:

INSERT INTO "TestTable" ("Input") VALUES (decode('74657374', 'hex'))

74657374 is hexadecimal for ‘test’. In one client, ‘test’ is inserted into the “Input” field, whether that field is text/varchar or bytea. That is the behaviour I desire. In another client, ‘x74657374’ is inserted into the “Input” field, whether it is text/varchar or bytea. This string is the postgresql literal representation of the bytea bytes of ASCII ‘test’. But the sql literal syntax itself is inserted. I do not desire this behaviour.

I use a single piece of hand-written SQL, the bytea value only occurs “within” the query (if column “Input” has type Text then none of literals nor the receiving column have bytea type), and it seems unlikely to me that either client is parsing then rebuilding the query. Therefore it seems that the difference must be happening on the server where the query is executed. That means there must be some connection specific configuration setting which is altering the server behaviour.

Can anyone please tell me what connection specific settings could be altering this behaviour?

I know the queries are really behaving differently and it is not a display issue etc., because I can see the rows in the same table having different values (‘test’ and ‘x74657374’). I have tried various alternative bytea handling methods, but they are all affected by this problem. For those who are interested, the “good” client is pgAdminIII and the “bad” client is Ruby PG gem. Though for the reason I gave above I believe there must be some built-in feature of postgresql supporting this behaviour.

Laravel- DB Query Duplicates record twice

I am using Laravel-5.8 for this Query:

    $published_goalss = DB::table('hr_employees AS e')
                ->join('hr_employees AS em','em.employee_code','=','e.line_manager_id')
                ->join('hr_departments AS d','e.department_id','=','d.id')
                ->join('hr_employees AS eh','eh.employee_code','=','d.dept_head')
                ->join('hr_employees AS eb','eb.employee_code','=','d.hr_business_partner_id')
                ->join('appraisal_goals AS ag','ag.company_id','=','e.company_id')
                 ->select(
                    'e.employee_code as staff_id',
                    DB::raw('CONCAT(e.first_name, " ", e.last_name) AS full_name'),
                    DB::raw('IF(e.gender_code = 0, "Female", "Male") AS gender'),
                    'e.email as official_email',
                    DB::raw('CONCAT(eh.first_name, " ", eh.last_name) AS hod_name'),
                    DB::raw('CONCAT(eb.first_name, " ", eb.last_name) AS hrbp_name'),
                    DB::raw('(CASE WHEN ag.is_approved = 3 THEN "Approved" WHEN ag.is_approved = 2 THEN "Not Approved" WHEN ag.is_approved = 1 THEN "Awaiting Approval" ELSE "Draft" END) AS goal_status')
             )
                ->whereIn('e.employee_code', $publishedgoals)
                ->where('e.company_id', $userCompany)
                ->where('e.hr_status', 0) 
            ->distinct()
            ->get();

Initially it duplicates 4 times, but when I added

 ->distinct()  ->get();

it reduces the duplication to just two.

I dealyy the query should give me 2 records, but it is giving me 4 records.

How do I resolve this.

Thanks

sql server – A query to show all the ways a user gets his SELECT permissions on a specified table, whether direct on indirect

Is there a ( SQL Server 2017 ) query that will list all the ways a specific user has obtained SELECT permission on a specific table?

I’ve revoked SELECT on that table for the user directly, and have revoked SELECT on that table for roles of which the user is a member; however, the user can still issue SELECT statements against the table in SSMS editor and in TOAD. Yet both TOAD and SSMS show an empty list when I check for permissions on the table.

Why not in does not return values with null values in my select query on postgresql 11?

I have the following sql script:

CREATE temporary table if not EXISTS the_values (
 key SERIAL,
 value INTEGER NULL 
);

insert into the_values(value) values (null),(1),(null),(2),(3),(4),(5),(6),(10),(null),(null);

select * from the_values where value not in (1,2,3,4,5,6,10); 

And I noticed that the query:

select * from the_values where value not in (1,2,3,4,5,6,10); 

Does not return the rows having value NULL, and that, caught my attention. Therefore, I want to know why that happens. I am interested more about the technical aspect of this phenomenon rather that the obvious solution:

select * from the_values where value not in (1,2,3,4,5,6,10) or value IS NULL; 

go – Using two handlers for a GraphQL project; handle query with the second one if the first one is not capable of

I’m trying to use both SuperGraph as-a-library and GqlGen.

So I have two handlers:

  1. the first one is SuperGraph; this checks that it can carry out the operation

  2. the second one is GqlGen; this checks that it can carry out the operation if the first one can’t

The code I’m using is this:

type reqBody struct {
    Query string `json:"query"`
}

func Handler(sg *core.SuperGraph, next http.Handler) http.HandlerFunc {
    return func(w http.ResponseWriter, r *http.Request) {
        bodyBytes, _ := ioutil.ReadAll(r.Body)
        err := r.Body.Close()
        r.Body = ioutil.NopCloser(bytes.NewReader(bodyBytes))

        var rBody reqBody
        err = json.NewDecoder(bytes.NewReader(bodyBytes)).Decode(&rBody)

        ctx := context.WithValue(r.Context(), core.UserIDKey, "user_id")
        res, err := sg.GraphQL(ctx, rBody.Query, nil)
        if err == nil {
            render.JSON(w, r, res) // go-chi "render" pkg
        } else {
            next.ServeHTTP(w, r)
        }
    }
}

func main() {
    r := chi.NewRouter()

    r.Group(func(r chi.Router) {
        r.Post("/graphql", Handler(supergraph, gqlgen))
    })
}

QUESTIONS

  1. Can I avoid allocations with these lines?

      bodyBytes, _ := ioutil.ReadAll(r.Body)
      err := r.Body.Close()
      r.Body = ioutil.NopCloser(bytes.NewReader(bodyBytes))
    
  2. Can I use those lines just if I need to call GqlGen with next(), something like this?

      if err == nil {
        render.JSON(w, r, res) // go-chi "render" pkg
      } else {
        bodyBytes, _ := ioutil.ReadAll(r.Body)
        err := r.Body.Close()
        r.Body = ioutil.NopCloser(bytes.NewReader(bodyBytes))
        next.ServeHTTP(w, r)
      }
    
  3. Is there a better way to do all this?

  4. I know I can use a GraphQL gateway to join multiple schemas under the same endpoint, but is it really worth it? Is my idea/code really bad?

wp query – has_excerpt() not working inside wp_query

I am trying to check for an excerpt and if it doesn’t exist then show the content but trimmed. Currently nothing is showing.

<?php if (has_excerpt() ): ?>
    <p><?php echo get_the_excerpt(); ?></p>
<?php else: ?>
    <p><?php echo wp_trim_words(get_the_content(), 25); ?></p>
<?php endif; ?>

However, if I simply do the below then I see content. Is there an issue with my above code? This is running inside a WP_Query loop

<p><?php echo wp_trim_words(get_the_content(), 25); ?></p>

Google Sheets: How can I query all names and display it in another sheet according to it’s category?

You have a table of employees and skills. The skills are represented by checkboxes, and a “checked” box indicates that the employee has the relevant skill for that column.

You want to create a dropdown list of skills that will display the names of the relevant employees

Enter this formula in Cell B6 of the sheet containing the dropdown.

=query(data!A1:D5,"select A where "&SUBSTITUTE(ADDRESS(1,match(B3,data!B1:D1,0)+1,4), "1", "")&" = true",0)

Assumptions:

  • employee/skills data is on sheet=”data”.
  • the headers are in row 1.
  • dropdown is on a separate sheet; the name of the sheet is unimportant.
  • dropdown is in cell B3

Comments:

The select statement is completely predictable:

  • dropdown = WordPress: “select A where B=true”
  • dropdown = HTML; “select A where C=true”
  • dropdown = Bootstrap; “select A where D=true”

In each case, the variable is the column letter for the dropdown choice. This value is supplied by the following formula.

SUBSTITUTE(ADDRESS(1,match(B3,data!B1:D1,0)+1,4), "1", "")

This can be broken down as follows:

match(B3,data!B1:D1,0)+1

  • gets the index number of the dropdown string (from B3) in the data headers.
  • the purpose of +1 is to adjust for Column A.
  • the result is an integer that equals the column number of the dropdown string in Cell B3.

ADDRESS(1,match(B3,data!B1:D1,0)+1,4)

  • returns the cell address of the dropdown string (assuming row#1 – the header row).

SUBSTITUTE(ADDRESS(1,match(B3,data!B1:D1,0)+1,4), "1", "")

  • takes the cell address returned by “ADDRESS()”. The address will refer to a cell in row 1, and the “SUBSTITUTE” function will remove the row number (1).
  • result is the column letter of the dropdown string which is inserted into the where clause of the query.

sql server – High CPU Usage on certain query when changing DB Compatibility Level

We migrated from SQL 2008R2 to SQL 2016 a few years ago and have been running on Compatibility Level 100 since that point.

We are looking to make use of memory optimization introduced in SQL 2016 and that requires setting the compatibility level to 130.

We have had a few instances where a certain query will max out the CPU (that normally only takes a few seconds to run on Compatibility Lvl 100, but when we change it to Level 130 the CPU maxes out and does takes 2 minutes and 37 seconds to run vs about 2 seconds on the old compatibility level)

enter image description here

I did pull the execution plan for each of these

CompatibilityLvl100 Plan

CompatibilityLvl130 Plan

I am not a DBA so I can see there are differences between the execution plans but I could not see any reason for it to max out the CPU and take almost 6000% longer to run the same query with the same indexes etc.

I also executed this query to get some more details about the running query, I have uploaded the results as a comparison to a Google Sheet here.

One thing i noticed is that the slow running query was using 192 worker threads, whereas the fast running query only used 32. But I still couldn’t figure out what was causing the high CPU usage.

Be glad of anyone’s help or direction of something I may have missed!

How can I issue multiple commands from a single query choice in Roll20?

I created a set of macros using the API to generate attributes for characters on Roll20 to refer to their pronouns for use in macros. I currently have it set up with 3 macros, each with 4 lines because I can’t add multiple attributes with a single command.

How can I use a macro to query which set (male, female, or neutral) of pronouns I want to use? I know I should be able to do something like


?{Male, Female, or Neutral? | Male,#Pronouns_M | Female,#Pronouns_F | Neutral,#Pronouns_N}

And that should work, but I’d rather have it where the contents of those sub-macros are in the main one and delete the sub-macros. Problem is, each of the sub macros is 4 lines such as #Pronouns_F


!setattr --sel --subjective|'she'

!setattr --sel --objective|'her'

!setattr --sel --PossessiveA|'her'

!setattr --sel --PossessiveP|'hers'

Is this a thing I can do or do I have to settle for the sub macros?

t sql – Add Total Row To PIVOT Query

I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last employeename that is labeled TOTAL and it shows the SUM() of the sales for ALL employees for that month?

Create Table #empSales
(
    employeename varchar(100)
    ,saleamt decimal(10,2)
    ,saleMonth varchar(100)
)

Insert Into #empSales VALUES
('James', '1.00', 'January')
,('Richard', '3.28', 'January')
,('Barb', '4.13', 'January')


Select 
employeeName
,SUM(January) As JanAMt
,SUM(February) As FebAMt
,SUM(March) As MarAMt
,SUM(April) As AprAMt
,SUM(May) As MayAMt
,SUM(June) As JunAMt
,SUM(July) As JulAMt
,SUM(August) As AugAMt
,SUM(September) As SepAMt
,SUM(October) As OctAMt
,SUM(November) As NovAMt
,SUM(December) As DecAMt
FROM #empSales
PIVOT ( 
    SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December)
) As pvt
GROUP BY employeeName
Order By employeeName