Topics created by esther

You can grant at a schema scope GRANT VIEW DEFINITION ON SCHEMA::DBO TO SOMEUSER or the whole database GRANT VIEW DEFINITION TO SOMEUSER

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15 does what you are looking for, but if you know the log will grow back again in a relatively short time then that probably isn't what you want. If it is going to grow back anyway you can't reuse the space (you need to leave it ready for that growth) and you are reducing performance in the circumstances where the log does need to grow rather than having the space already allocated. If you think the growth is unnecessary ("out of control") then you should look into what is happening during the periods of growth to see if those operations can be improved. Improving those activities will likely make them more efficient and improve your application's processing efficiency as well as the databases storage space efficiency. A common issue that is easy to improve its large updates that include a lot of noop updates, for instance UPDATE SomeTable SET AnInt = 123 will result in every row being updated even if 99% of them already have AnInt equalling 123, with all the log activity that involves. UPDATE SomeTable SET AnInt = 123 WHERE AnInt <> 123 would fix that simple example (be a little more careful of the columns can be NULL).

database system is ready to accept connections Exactly accept connections, not database system is ready to accept read only connections Which means you started a new primary server, not replication. Also there was no entering standby mode message. You need to make another base backup and start https://www.postgresql.org/docs/13/hot-standby.html : put empty standby.signal file in the data directory. pg_basebackup with option -R can place this file and write corresponding primary_conninfo to postgresql.auto.conf

Writing this question I got an idea, and further investigation showed that this is indeed a oci problem. The required PHP version can't be started correctly, and that's why my pages can't connect to the DB, whereas sqlplus, sqldeveloper etc have no problem. I had to "downgrade" the version of InstantClient (to 18.3) in order to work with Oracle 18c, and apparently that's when something got mixed up as I still have this: php-oci8-19c.x86_64 7.4.28-1.0.1.el7

You want to make sure that any network-mounted solution is also highly available. Loss of access to some log files could result in Oracle services locking up. In the past I have also used logrotate to roll, compress, and archive (to network storage) or delete log files automatically from the file system. I'd probably try this before using NFS mount points to "move" the logs entirely. Rotate or archive as frequently as necessary and move the archives to the network storage. I also used Splunk to read most of those files in real time so that I didn't lose easy/quick access to any of the data as things were rotated.

SELECT DATE_TRUNC('month', arrive_date) AS grouped_date, LOWER(arrive_location) AS location, x.key::int thing_type, sum(x.value::int) total FROM preprocess_things, jsonb_each(data) x GROUP BY grouped_date, location, x.key::int order by grouped_date, location, x.key::int; see https://dbfiddle.uk/?rdbms=postgres_13&fiddle=30dcd885d5581ab83c8f23602cba4b9c

The issue here appears to come from a misunderstanding of the "excluded" keyword. The https://www.sqlite.org/lang_UPSERT.html states: To use the value that would have been inserted had the constraint not failed, add the special "excluded." table qualifier to the column name. The value that would have been inserted into fooID is of course excluded.fooID, not excluded.id as I have in the example above. I believe external.id refers to the row ID of the column, which in this case would have been 2 for the third row. So the final query we end up with is: INSERT INTO bar (key, fooID) SELECT 'key', foo.id FROM foo WHERE foo.name='three' ON CONFLICT (key) DO UPDATE SET fooID=excluded.fooID;

The https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.Maintenance.html#USER_UpgradeDBInstance.OSUpgrades says you should be able to trigger the upgrade by choosing the instance that you want to apply the upgrade to and select “Maintenance and Backup” tab followed by selecting the pending maintenance and clicking “Apply Now” 

Something like this should work. Use a cte to find the first ID for each product that is not null, then join back to the original table. ;WITH cMinProductId AS ( SELECT Product, MIN(ID) MinID FROM dbo.src WHERE Country IS NOT NULL GROUP BY Product ) SELECT s.* FROM dbo.src AS s INNER JOIN cMinProductId AS mp ON s.Product = mp.Product AND s.ID >= mp.MinID https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c4a3c654942ffe1b84e14cf046822d4e

I would ask - what is the the purpose of this HID? If it constructed from database-internal, numeric, surrogate key values, then you should never be showing them to a User anyway, so what's the purpose of assembling them in this way? Anyway ... The most important question to ask before deciding how to store any piece of Data is this: How are you going to use this Data? How are you going to "get to" this Data? (Do you need to index it?) What are you going to do with this Data, having got it? (Do you need to further subdivide it?) This last is interesting, because one thing you might want to do its to find an entry's parent (e.g. Seattle -> Washington). But how can you do that efficiently, armed only with the complete HID? You'd need to dissect the value, extracting the last element (which is almost always more difficult than getting the first) and then go look up the parent record. In such a case, it might be better to hold only the parent record's id and use a recursive query to derive the HID value completely dynamically. If your id values ever changed (which, of course, they never, ever should) then your full HIDs would be all fouled up and would have to regenerated en masse and that's painful.

The answer is described mostly already https://gitlab.com/dalibo/postgresql_anonymizer/-/issues/161 . I'll add it here as well to make it a bit easier to find and understand. ## clone the repo en cd into it git clone https://gitlab.com/dalibo/postgresql_anonymizer.git cd ./postgresql_anonymizer Checkout the version that allows standalone installation git checkout 0.8.1 Create the anon_standalone.sql file make standalone Execute that sql file against your Amazon RDS instance psql -h -U -d -a -f anon_standalone.sql

There is no intrinsic order to the rows in a Relational database table. Given this, how do you intend to identify "Row 1" and "Row 2"? If your data already exists in "Row 1" and "Row 2", why do you want to duplicate that data (into "Row 3")? Duplication of data is generally a Bad Idea. Show us the structure of your table(s) and enough sample data to better understand your scenario. That way, we might be able to offer you better advice. Right now, we're basically guessing.

It's not common for the primary key to match between two different tables (though there's some cases for it). Instead one table should have a second field that is the foreign key reference to the primary key of the other table, to relate the two tables together. Or sometimes both tables will relate by foreign key fields in both tables. Without a bit of more detail, I would guess your use case falls into the latter scenario. I would envision you have a Contracts table whose primary key field would be ContractId, Budgets table whose primary key field is BudgetId, and an Invoices table whose primary key field is InvoiceId. Both the Budgets and Invoices table would have a foreign key referencing field called ContractId as well which would reference the primary key ContractId field from the Contracts table. In the above scenario, this would relate all Invoices you receive for a given Contract to the correlating Budget of that contract. This assumes that your Invoices and Budgets aren't one-to-one with each other per se (e.g. a Contract can have one big Budget but is broken out into multiple Invoices). This is usually the common way budgeting and invoicing works in practice. But if your use case truly has a one-to-one relationship between Budgets and Invoices then all you need to do is have a foreign key referencing field called BudgetId in your Invoices table, which references the BudgetId field from your Budgets table. And you would set the Invoices.BudgetId to the correct value when the Invoice record is created.

Server roles are different than database roles because there are different privileges that can be granted at the server level vs the database level for objects that only exist in either respectively. For example, the SQL Agent Jobs only exist at a server level, and stored procedures, views, and tables only exist at the database level, and therefore the permissions granted to those objects exist only at the same level. It sounds like you just need to create the database role in each database you want to grant permissions for accordingly. I recommend reading https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15 for more information.

You can achieve it using a https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15 and https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15 : ;WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [TEMP] DESC) AS rn FROM mytable ) SELECT cte.ID, COUNT(*) AS COUNT, cte.TEMP AS MAXTEMP, cte.DATE AS MAXTEMPDATE, MAX(mt.DATE) AS MAXDATE FROM cte INNER JOIN mytable mt ON cte.ID = mt.ID WHERE rn = 1 GROUP BY cte.ID, cte.TEMP, cte.DATE;

Short answer - No. The Outside World is full of "Ne'er-Do-Wells" that just want to "mess" with anything and everything they kind find to mess with. This is why Web Servers - like Apache - have so many defensive tools with which to protect themselves against whatever might be thrown at them. Simply put, Databases - like PostgreSql - just aren't built that way. This is why most applications you'll see these days are layered, having a client (usually running in a user's web browser), a web server, which can cope with the nefarious "junk" that will get thrown at it and a database, safely nestled away behind protective firewalls and the like and only accessed by trusted connections, such as that from the web server.

Pre GW BASIC, there was TRS-80 Level ][ BASIC. In that language, you had line numbers for each line of code. The standard method for programming was to use intervals of 10 for your numbers as you go. Then, when you need to add code, you have space to add code. For display/rank order, I recommend the same thing. However, I've seen (and created) a "re-enumerate" procedure that was called after every rearrangement. This kept the numbers in multiples of 10. The procedure was a simple MERGE statement with the enumeration based on RANK() × 10

As far as I know the answer is no. Try to run: SELECT * FROM sys.configurations WHERE name = 'cost threshold for parallelism' while your Azure SQL Database is under heavy load, I'm quite sure you will not see that value change. (I haven't tried) Is well knows that cost threshold for parallelism is set to 5 by default even on the latest versions of SQL Server. My assumption is that nowadays this is part of that legacy code that has been ported to Azure. It stays there in a corner, forgot, and no one notice it. And god only knows how Azure handles cost threshold for parallelism behind the curtains in the cloud. But sure you have no vision of it and you cannot tune it.

There is a much better way to build "Reports" from a large dataset. Build and maintain a "Summary Table" that provides subtotals for each day (or another useful time period). Then build the Report from this table instead of the much bigger "Fact" table. In my experience, a well designed Summary Table allows the Report to be generated in less than a second, thereby obviating the need for a corn job to regenerate -- simply generate it on demand. If you provide more details, I can provide more specifics. Meanwhile, study this: http://mysql.rjweb.org/doc.php/summarytables It may be possible to provide up-to-the second reports instead of your current 10 minute delay. The Summary table can be denormalized and have multiple indexes. It also obviates the need for as many indexes on the Fact table. For more discussion: SHOW CREATE TABLE table size The current SELECT that is run by corn How 'current' the data should be How frequently data is inserted in the Fact table.

Single database with an individual schema for each client Schema-per-client is not real not infrastructure isolation; it's weaker, and available on both PostgreSQL and Azure SQL Database. But that doesn't give you per-client backups, per-client scaling, etc. Even Oracle supports database-per-client via https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234 since Oracle 12. But both SQL Server and PostgreSQL have always supported multiple databases per instance, so the recommended multi-tenant architecture is to have a separate database for each client. This is especially so in Azure where the servers, storage, and backups are managed for you. For instance see https://docs.microsoft.com/en-us/azure/architecture/guide/multitenant/service/sql-database