The way you configured PostgreSQL, it does not use the operating system's time zone database, but its own copy, which is located in pg_config --sharedir. So your symbolic link has no effect.

You'd have to create that symbolic link in PostgreSQL's copy of the timezone database, but the correct solution is different: set timezone to America/Yellowknife.

Do not use unsupported PostgreSQL versions.

I have a mysql database with this structur:

Every year comes new lines and a column is added.

I want to transpose this table to the structur:

The number of columns and rows are dynamic.

There are many threads with a similar problem (partially transpose with pivot). But I found no solution for this problem.

Thanks for help.

ASYNC_NETWORK_IO wait types are mostly caused because the client application cannot process the data that arrives from SQL Server fast enough.

As the query executes, and as the results start getting available for consumption, the client application starts consuming the results. Assuming the result is held by say 4 threads, then, until the result is entirely consumed by the client application, do the threads that hold the data remain locked?

I run query

select cpu_count, hyperthread_ratio, softnuma_configuration, softnuma_configuration_desc
from sys.dm_os_sys_info

and got results, where said, that numa is off:

cpu_count   hyperthread_ratio   softnuma_configuration  softnuma_configuration_desc
32          16                  0                       OFF

Then I run another query

SELECT name, value, value_in_use, minimum, maximum, [description], is_dynamic, is_advanced 
FROM sys.configurations WITH (NOLOCK) 
ORDER BY name OPTION (RECOMPILE);

and got result, where said, that auto-numa is not disabled

name                          value value_in_use    
automatic soft-NUMA disabled    0            0      

Where is the right info?

In Task Manager I see node0 and node1.

We use Microsoft SQL Server 2017 (RTM-GDR) (KB4583456) - 14.0.2037.2 (X64) Nov 2 2020 19:19:59 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

I have this message in the log - does this mean NUMA is being used correctly and that I don't need to do anything else?

Shows 8 cores:

upon the session timeout expiry the AG will automatically get converted into async mode

It's been a while since I've worked with AlwaysOn Availability Groups, so I'm not sure that statement is entirely accurate. The secondary replica in your example will however leave the Healthy state and enter a Disconnected state, whereby allowing the primary replica to continue to transact without waiting to synchronize to it (similar to how an asynchronous replica works).

I think it's still fair to call synchronous AlwaysOn Availability Groups a valid HA/DR solution with minimal chance of data loss (no solution in the world offers 100% guarantee of 0 data loss) despite its session timeout functionality. The situation where a replica is unresponsive is an error situation you would want to be aware of, and therefore it's fair for it to be marked as Disconnected.

It would be worse if everything carried on as normal, and a days worth of transactions just continued to build up on the primary replica waiting to be committed but couldn't because the secondary is unresponsive. There's a lot of risk for data loss in such a situation. And we'd be none the wiser without any indication that the secondary is in a bad state. Even scarier of a situation would be if the primary became unresponsive. The secondaries should have a way to acknowledge that there's some sort of a failure occuring on the primary, and the session timeout is one way of doing so.

The session timeout is configured for 10 seconds by default, such that one can be alerted and respond to an unresponsive replica situation rather quickly. But it can be adjusted to the needs of your use case, and increased if a longer timeout time is necessary (due to normal anomalies, etc).

Let's say I have a Library database and the following tables in it:

• Books(ID, Title, Author, PublihserID, LanguageCode, Genre, DatePublished, ISBN) - contains examples of books as abstract items, intellectual work

• Branches(ID, Name, City, Address) - contains the branches of the library in different cities

• BookCopies(ID, BookID, BranchID, Condition) - contains books as physical items and what branch they are located in

I want to write a query, which for each Book in Books displays how many physical copies of the book there are in each branch of the library. So far my data in the database has three Branches with IDs 1, 2 and 3. I have written the following query, which properly displays what I want (with some added extra such as filtering by author Agatha Christie):

SELECT
    Books.Title,
    Books.PublisherID,
    Books.DatePublished,
    Books.ISBN,
    r1.Branch1,
    r1.Branch2,
    r1.Branch3
FROM
    Books
    JOIN
    (SELECT
        copies.BookID,
        br1.Branch1,
        br2.Branch2,
        br3.Branch3
    FROM
        BookCopies copies
        JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch1
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=1) br1
        ON copies.BookID=br1.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch2
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=2) br2
        ON copies.BookID=br2.BookID
        FULL JOIN
        (SELECT
            BookCopies.BookID,
            COUNT(BookCopies.BranchID) AS Branch3
        FROM
            BookCopies
        GROUP BY BookCopies.BookID, BookCopies.BranchID
        HAVING BookCopies.BranchID=3) br3
        ON copies.BookID=br3.BookID
    GROUP BY copies.BookID, br1.Branch1, br2.Branch2, br3.Branch3) r1
    ON Books.ID=r1.BookID
WHERE Books.Author='Agatha Christie'

But there are two fundamental problems with such a query:

1. I am sure it's really slow, ineffective and full of bad practices
2. It is hardcoded for three branches. It wouldn't work if the library opened a fourth, fifth etc branch.
   
   How can I improve my query so that it works for all branches (no matter their count) and preferrably uses resources efficiently?

Both, bit and bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value. set_bit() won't automatically increase the size of the bit string (if using bit varying), and get_bit() will throw an error when trying to access a bit beyond the length of the current value.

create table numbers (flags bit(10000000));
insert into numbers (flags)
values (repeat('0', 10000000)::bit(10000000));

That creates a single bit string with 10 million bits (all set to zero).

Note that the numbering of the bits starts at 0, not 1. So to test for the "last value" you need:

select get_bit(flags, 10e6::int - 1)
from numbers;

To change a value, use set_bit() to set the bit for Number 10000

update numbers
   set flags = set_bit(flags, 10000 - 1, 1)

You can wrap the logic into functions if you want.

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=d3e37762c1610f1aaa5312e5194566b5

In theory you could use a bit varying to support an unknown range of numbers, you just need to have some logic that extends the current value to include the new bit position e.g. by appending the appropriate number of bits between the new one and the existing ones to "fill the gap".

You’re seeing soft-NUMA counts.

As mentioned in this https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option …

Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine places logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node.

In your case, it looks like you have 2 CPUs, each with 12 cores. With those being greater than 8, SQL Server is breaking them up into 4 soft-NUMA nodes at startup, each with 6 cores. With this configuration, you’d want to set MAXDOP to 6.

As Tibor points out, the practical answer here is "in the database", so it follows the database through backup/restore or an AG failover. That is the reason database users with passwords were added to partially contained databases and Azure SQL Database.

If you examine the object_definition of sys.database_principals you can discover that it's actually in a system table called sys.sysowners, stored as a hash, which can only be queried from the Dedicated Administrator Connection.

I have 2 nodes in an AG. Non-readable secondary, sync commit mode.

There are sql agent jobs setup on both machines. The schedules are turned off on secondary so those jobs wont run. If there is failover then we manually enable the schedules for jobs on secondary.

The schedules run various jobs almost every minute.

When the primary goes down (technically because of network issue), but the machine is running, how do I immediately prevent the scheduled sql agent jobs on primary from running?