After several strategies to find it, the location (in MacOS BigSur 11.4) is:

~/Library/Logs/Xray Exploratory App/main.log

Hope that helps.

SELECT First Name, 
       Job, 
       MIN(Day of material)  
FROM table  
GROUP BY First Name, Job

EASY!!!

Schema:

CREATE TABLE item (
    id        int primary key,
    parent_id int,
    FOREIGN KEY(parent_id) REFERENCES item(id)
);
INSERT INTO item

(id, parent_id)

VALUES

(1, NULL),

(2, 1),

(3, 1),

(4, 2),

(5, 3),

(6, 3),

(7, 6)

;

Query:

WITH RECURSIVE descendants(id, parent_id) AS (
    SELECT 1, parent_id FROM item
        UNION ALL
    SELECT item.id, descendants.parent_id
    FROM item, descendants
    WHERE item.parent_id=descendants.id
)
SELECT * FROM descendants;

Fiddle: http://sqlfiddle.com/#!5/27c03/4

Goal: given some parents (eg 2, 3) get all of its descendants.

I came up with the following CTE, but it returns far too many items.

SELECT * FROM descendants WHERE parent_id IN (2, 3);

Should give

id | parent_id
4  | 2
5  | 3
6  | 3
7  | 3

The answer here is close, but my schema varies: https://dba.stackexchange.com/a/94944/253249

Reads go to the raft leader and writes as well but wait for the quorum. So it depends on the topology. A Replication Factor RF=3 on one AWS region, for example, doesn't suffer as latency between AZs is low. For cross-region distribution, this is addressed with placement preferences. Like having all leaders near the users, and one nearby region will answer the quorum. Those preferences can be mapped to business info (like customer's country) through PostgreSQL partitioning and tablespaces

OK - I've figured out this was a badly formed question. I was conflating (at least) two different questions:

1. What do you do when NULL can mean different things?

2. How do you store non-datatype data in a typed column?

The answer to (1) is: model the meanings you wish to store. And the answer to (2) is: place those models in other columns and tables. If your column is a series of integer facts, (eg, year of death) - don't overload it with coded integers, otherwise your whole app needs to understand the overloaded meanings.

When I have multiple errors in my query, and I use try catch and error_message() function in my catch block, I only get the last error message.

For example, if my errors are:

Msg 2714, Level 16, State 5, Line 14
There is already an object named 'IX_EntityTypeConfigs' in the database.
Msg 1750, Level 16, State 1, Line 14
Could not create constraint or index. See previous errors.

in the catch block I just get:

Could not create constraint or index. See previous errors.

How can I get the whole message?

I have searched and saw that this is a limitation of SQL Server, but articles belonged to 2014 and older than that.

I wonder if things has changed since.

This is currently expected behavior: the queries in question are the preparation and closing of prepared statements. Readwritesplit currently routes these to all nodes in the cluster instead of only on the node where it ends up being executed. The messages tell that the SQL is not actually executed on the node as there are no Execute stmt messages.

Readwritesplit does this both to verify that all nodes respond similarly and to provide a way of distributing load without having to prepare the statement again in case a need for it arises.

The most common case where SELECT statments are routed to a master node in the cluster is when they are done inside a transaction. The second most common case is when they use some special function or variable (e.g. @@last_insert_id, https://mariadb.com/kb/en/sequences/ ) that forces it to be treated as if it were a write.

Some frameworks disable autocommit by default which makes all statements a part of a transaction. If you explicitly open a read-only transaction with START TRANSACTION READ ONLY, readwritesplit will route all reads inside it to the same slave server in the cluster.

A good way to debug these sort of routing problems is to enable the info log level. Add log_info=true under the [maxscale] section of your configuration file or run maxctrl enable log-priority info to do it with MaxCtrl. The log will contain all executed SQL statements, their classification result (read or write), whether a transaction is open as well as other relevant bits of information. Using this you should be able to determine why the statements are routed the way they are.

I am looking at SQL lock wait time perf mon counter and it is indicating high value ranging from 5 to 20 seconds during certain times of the day.

I have setup extended events which gives me the T-SQL statements that are executing. Is there any way to get the list of:

1. Specific queries that are contributing to high locking time?

2. Specific queries that are actually blocking other queries?

If you're not disabling these LOGINs afterwards, it gives someone malicious more avenues for access. – Larnu

Also we have found that the more logins that exist, the longer login creation takes. Which isn't a big deal for a single CREATE LOGIN but it can be a bigger deal if you have automation in place that tries to create 1,000 or 10,000 at a time. – Aaron Bertrand

There are also the basic UI symptoms involved with large numbers of anything, where expanding nodes in Object Explorer or right-clicking for properties can take a long time and block other users and/or time out. – Aaron Bertrand

You wont have perf issues as the logins you identified are dormant. You will have issues in terms of manageability and UI experience as Aaron mentioned. Better disable them and if no one shouts .. drop them when you feel comfortable. – Kin Shah

Start with Page Life Expectancy, which should be well above the 300. This tells you how long pages are staying in the buffer pool, and a value of 300 equates to 5 minutes. If you have 120GB of buffer pool and it is churning over 5 minutes, that equates to 409.6 MB/sec sustained disk I/O for the system which is a lot of disk activity to have to sustain.

Link: https://deltabravo.ai/delta-bravo-performance-counters-sql-server-target-vs-total-memory/

The formula used seems to be -> Disk IO throughout = Buffer pool memory * 1024 / PLE

Is this a correct way to determine the disk IO throughput?

I've to do fuzzy search on multiple fields (in an attempt to create something like autocomplete similar to product search in amazon). I tried this through ElasticSearch but was wondering if there's something equivalent to it in postgreSQL.

Here's sample code for elasticsearch: (both the fields, title and description, are index as type: text)

GET index_name/_search
{
    "query": {
          "bool": {
                "must": [
                      {
                           "multi_match": {
                                 fields: ["description", "title"],
                                 query: "postgres",
                                 fuzziness: 1
                           }
                      }
                ]
          }
    }
}

I've tried the same using pg_tram in postgreSQL, it worked for one field with similarity() (% operator) but I don't know how to extend this on multiple fields.

This is what I did in postgreSQL, not sure if it's good way though:

select * from table t
   where similarity("title", "postgres") > 0.5;

select * from table t
   where similarity("title", "postgres") > 0.5 OR similarity("description", "postgres") > 0.5;

Also is there any way to introduce fuzziness in ts_vector (FTS) query ?
Will appreciate any help/guidance in this context.
P.S: let me know if my description is missing something.

Thanks