I got a database cluster with 1300 databases. I created a role and a user and granted access using the command GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser, so myuser is able to run queries in one particular database. When I connect to a different database with myuser and run select * from person, I get "permission denied", so I need to connect as admin to that database and run GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser again, then login as myuser and then I'm able to run select * from person.

I have 1300 databases in the cluster, as I said. Is there any command which grants read and write privileges in the whole cluster for myuser? Please note: myuser should not be a superuser.

I tried:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO service;`
\t
SELECT '\connect ' || datname || '; ' ||E'\n'|| 'GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to myuser;' FROM pg_database WHERE datistemplate = false;

but these commands only work in the database where I run them, and for all different databases I have to run them again like 1000 times.

I have a table with one field Address - varchar(250), is it possible to search this field for a 6 digit (integer) Pincode. Is it also possible to list out records without any 12 digit Pincode.

Sl No |  Address 
1 | "16/127, Off M G Road, Goregaon (west) Mumbai, Maharashtra, 400062 02228744298"
2 | "113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka, 560002 2229218"
3 | "2, 2, Panchal Estate, Bapunagar Ahmedabad, Gujarat, 380024 079227401432nd Flr, B Wing, Jay Chamber, Service Rd, Near Telephone Exchange, Vile Parle (east) Mumbai, Maharashtra, 400057 02226104168"
4 | "New#27, Gopalakrishna Street, T Nagar Chennai, Tamil Nadu, 600017 04428150211"
5 | "9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi, 110055 01123550815"
6 | "113, 113,ragiptblr-2, Ragi Pet Bangalore, Karnataka,  2229218"
7 | "9360 61/8, Multani Dhanda, Pahar Ganj Delhi, Delhi,  01123550815"

Virtual columns don't precompute anything on their own, you're just storing the expression in the metadata. If you are planning on having filters against these expressions then you might want a virtual column for a few reasons - you don't need to write the entire expression each time, Oracle will gather statistics against the column for you so you get better plans (should your queries be complicated enough for this cardinality to matter).

A (function based) index on the virtual column makes filtering on the expression even faster as the expression is now indexed so you can do quick look ups (it's an index). You don't have to create the virtual column too - you can just create the function based index against the expression, but this means you have to type the whole expression out again each time (Oracle is quite forgiving with how you type it out though). You still get the benefits of statistics as the index will have stats and in the background there will be an invisible column which can have statistics against it (although I believe the default is for this not to happen).

If you are doing this, be careful that your expression is deterministic, the result should only change based on the row. This also includes things like NLS parameters which might give you different results. There was a bug fix a few versions ago where Oracle disabled indexing on regex expressions because they can be dependent on language settings, if you force this then you can end up with corrupt data.

Triggers can work but I've lost count on the number of times I've seen an assumption being made in a trigger which ended up causing corruption. Materialized views can allow you to have even more complex expressions being indexed but you have to store the whole result set and the additional indexes all over again. You'll also need to make sure you can refresh the materialized view appropriately, fast refreshes get more difficult as you add complexity.

I am using the keyset pagination method for uuids on my Postgres database described in this post:

• https://dba.stackexchange.com/questions/267794/how-to-do-pagination-with-uuid-v4-and-created-time-on-concurrent-inserted-data

However, I have noticed when I have two records where the date is the same, rows are being skipped from the result.

For example when I run the query:

SELECT id, created_at FROM collection
ORDER BY created_at DESC, id DESC

I get the records back as I expect them, with created_at being the primary order, then id acting as a tiebreaker:

However when I run a query to paginate through like:

SELECT id, created_at
FROM collection
WHERE (created_at, id) < ('2022-05-23 23:07:22.592','d35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC
LIMIT 3

I would expect to get back the last two records, but my result set is instead

I've also tried some variations on the query to try to fix it, such as:

SELECT id, created_at
FROM collection
WHERE created_at < '2022-05-23 23:07:22.592' OR
     (created_at = '2022-05-23 23:07:22.592' AND id < 'd35c6bb8-06dd-4b86-b5c6-d123340520e2')
ORDER BY created_at DESC, id DESC

But I still get back the same result set.

What's going on with my query?

I have the following table:

for each "running" I need to get the Max resourceid and it's item which give me the expected result:

With this code:

SELECT
b.running,
MAX(b.resourceid)as MaxResourceid,
MAX(b.item) as item
FROM runningResources as b
inner join (SELECT 
    running,
    MAX(resourceid) as MaxValue
    FROM runningResources
    GROUP BY running ) a ON
    a.running=b.running and a.Maxvalue=b.resourceid
    group by b.running

Since the query

SELECT
b.running,
MAX(b.resourceid)as MaxResourceid,
MAX(b.item) as item
FROM runningResources as b
group by b.running

gives the result:

why is C4 in the final result showing 771226 ? I would think that the join would give the item of the outer SELECT (C4,48,990000)

I keep getting the following error on running sudo mysql_secure_installation

... Failed! Error: SET PASSWORD has no significance for user 'root'@'localhost' as the authentication method used doesn't store authentication data in the MySQL server. Please consider using ALTER USER instead if you want to change authentication parameters.

Any help will be appreciated.

Im testing a trigger for an assignment where i need to set an specific value to each row on a table.

I have 2 tables tbl_cursos and tbl_modalidad. After i insert an entry on tbl_cursos the trigger must take 1 value from that table and add it to tbl_modalidad. Also the trigger must insert on tbl_modalidad a specifica value to Tipo_modalidad column, the value is "Presencial", then insert the date.

This is what i have

CREATE TRIGGER bienvenida 
AFTER INSERT 
ON tbl_cursos FOR EACH ROW 
BEGIN
INSERT INTO tbl_modalidad (id_modalidad, tipo_modalidad, fecha_registro) 
VALUES (new.id_modalidad, `Presencial`, now());

After running the trigger i got an error message with Presencial. I've tried to use SET table = 'message' but is wrong. Everything works perfectly if i delete the "presencial" value.

I believe that memory for the cluster and AG are separate from the 94GB max sql memory setting.

For AG memory would come from max server memory. https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15#dynamic-memory-management controls

The SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks). CLR memory is managed under max_server_memory allocations starting with SQL Server 2012 (11.x). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.

So for some Windows cluster DLL's the memory may come from OS but that would be very less and you should hardly bother about that.

In your case if this is dedicated SQL Server instance leave 7-8 GB for OS and assign rest to SQL Server. Monitor memory usage by following counters.

• Total Server memory

• Target Server memory

• Database memory

• Page Life expectancy of NUMA nodes( if you have)

• Buffer Manager: Free Pages

• Memory Manager: Memory grants pending.

PS: You are asking lot of questions answer to which can easily be found by little searching. I strongly suggest you to read books online.

One way you could approach this is to utilise the function https://docs.microsoft.com/en-us/sql/t-sql/functions/databasepropertyex-transact-sql?view=sql-server-ver15 with the property 'Updateability'.

For example:

DECLARE @STATE VARCHAR(100);
SET @STATE = (select CAST(DATABASEPROPERTYEX('foo', 'Updateability') as varchar(100)));        
IF(@STATE)  <> 'READ_ONLY'

BEGIN

Select @@servername as [ServerName]

End;

I recently upgraded to the latest pgAdmin 4 (version 6.9), and to my surprise, drag-selecting in the results grid doesn't work (it's just not selecting anything). I can move the active cell around using the arrow keys and clicking, but drag selecting or shift-arrow selecting just moves the active cell around without selecting the cells. I can't seem to find a setting that changes this.

Is this a bug, or is it configured in some non-obvious way? How do I change this?

Running on Windows 10.