Call UPSERT function multiple times to different inputs I have a function:

merge_vehicles(vid, cid, vname, reg_no, name, name_1st)

Can i call it multiple times on inputs like

(2335, 55, '246BDH', '246BDH', '811', 1),
(2336, 55, '038THX', '038THX', '831', 1),
....

The function is an UPSERT implementation returning void. This is meant to be a warehouse database (fact table). We have a live database that gets more than 1000 inputs per hour. We want to use this command to merge our data with the new data.

It is a table function. (I have defined alike functions to every table we have - that's 5.) Result is, it will update existing rows, if they exist, otherwise inserts new row.

Postgres version is 9.3.

Function is a version of the accepted answer here:

Idiomatic way to implement UPSERT in PostgreSQL

UPSERT 1 row at a time with function
A "table-function" is a function returning a set of rows (acting like a table when called with SELECT * FROM myfunc()). What you have is not a table-function. Since nothing is returned you can use a simple call:
SELECT merge_vehicles(vid, cid, vname, reg_no, name, name_1st)
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names

While that works, upserting 1 row at a time is very inefficient for big sets.
Bulk UPSERT
Assuming (vid, cid) is the primary key of our table test (information missing), I suggest a data-modifying CTE for the UPSERT after taking out an EXCLUSIVE LOCK on the table.
The EXCLUSIVE LOCK still allows concurrent transactions to read from the table, but no writes. It is only necessary if you actually can have concurrent transactions writing to the same table.
BEGIN;

LOCK TABLE test IN EXCLUSIVE MODE;

-- Or use a temp table for new rows. See below.
WITH my_rows(vid, cid, vname, reg_no, name, name_1st) AS (
   VALUES
      (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
     ,(2336, 55, '038THX', '038THX', '831', 1)
     , ...
   )
, upd AS (
    UPDATE test t
    SET   (  vname,   reg_no,   name,   name_1st)
        = (m.vname, m.reg_no, m.name, m.name_1st)
    FROM   my_rows m
    WHERE  t.vid = m.vid        -- match on PK columns
    AND    t.cid = m.cid
    RETURNING t.vid, t.cid      -- return only PK columns
    )
INSERT INTO test
      (  vid,   cid,   vname,   reg_no,   name,   name_1st)
SELECT m.vid, m.cid, m.vname, m.reg_no, m.name, m.name_1st
FROM   my_rows m
LEFT   JOIN upd USING (vid, cid)
WHERE  u.vid IS NULL;           -- only remaining rows

COMMIT;                         -- releases all locks

If the list is long, consider a temp table populated with COPY to replace the CTE my_rows and its VALUES expression.
Related:

Issue with composite type in an UPSERT function
How do I do an UPSERT (MERGE, INSERT … ON DUPLICATE UPDATE) in PostgreSQL?
Optimizing bulk update performance in PostgreSQL
Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3

Call set-returning function multiple times in one SELECT
(Before update to the question)
Assuming we are dealing with a set-returning function (a.k.a. "table-function").
You can use a VALUES expression for literal input of a derived table of parameters (or any other derived or actual table in its place) and conveniently use a LATERAL JOIN (Postgres 9.3+) to get the united results in one query. You may want to order in some way ...
SELECT f.*
FROM  (
   VALUES
     (2335, 55, '246BDH', '246BDH', '811', 1::numeric) -- example for explicit cast
    ,(2336, 55, '038THX', '038THX', '831', 1)
    , ...
   ) t(vid, cid, vname, reg_no, name, name_1st)        -- arbitrary column names
     , merge_vehicles(vid, cid, vname, reg_no, name, name_1st) f;

The LATERAL JOIN is implicit with function calls in the FROM list. More details here:

How to turn JSON array into Postgres array?

Numeric literals in this example default to type integer and string literals become text. Add explicit casts if you need specific types.


Sort Records in Original Order After UNION ALL My question seems to have been worded poorly the first time around, so this is my attempt to explain it more clearly + provide table structure and data below.
Please consider table dt:
Create Table dt
(LogID Int Not Null Identity Primary Key, StartTime DateTime, EndTime DateTime)

Insert Into dt(StartTime,EndTime)
Values
    ('2019-07-31 07:00', '2019-07-31 7:30'),
    ('2019-07-31 06:30', '2019-07-31 07:00'),
    ('2019-07-31 12:00', '2019-07-31 13:15'),
    ('2019-07-31 7:30', '2019-07-31 12:00'),
    ('2019-7-31 05:30', '2019-07-31 06:30')

LogID
StartTime
EndTime

1
2019-07-31 07:00:00.000
2019-07-31 07:30:00.000

2
2019-07-31 06:30:00.000
2019-07-31 07:00:00.000

3
2019-07-31 12:00:00.000
2019-07-31 13:15:00.000

4
2019-07-31 07:30:00.000
2019-07-31 12:00:00.000

5
2019-07-31 05:30:00.000
2019-07-31 06:30:00.000

I'm trying to come up with a query that would return

each record twice, once with StartTime and once with EndTime as EventTime
Order by EventTime and then by StartTime first, EndTime second.

Desired result:

LOGID
EVENTTIME

5
31/07/2019 5:30

5
31/07/2019 6:30

2
31/07/2019 6:30

2
31/07/2019 7:00

1
31/07/2019 7:00

1
31/07/2019 7:30

4
31/07/2019 7:30

4
31/07/2019 12:00

3
31/07/2019 12:00

3
31/07/2019 13:15

I have tried
Select
LogID, StartTime As EventTime, 'Start' As EventType from dt
Union All
Select LogID, EndTime, 'End'from dt
Order By
EventTime ASC, EventType Desc

But the order is not correct
Thank you for your help!

How about:
select LOGID, DESCRIPTION, STARTTIME Time from T
union all
select LOGID, DESCRIPTION, ENDTIME from T
order by LOGID, Time

If you want to order the event pairs by STARTTIME instead of LOGID, then
with q as
(
    select LOGID, DESCRIPTION, STARTTIME, STARTTIME Time from T
    union all
    select LOGID, DESCRIPTION, STARTTIME, ENDTIME Time from T
)
select LOGID, DESCRIPTION, Time
from q
order by STARTTIME, Time


Data too long for column for longtext datatype - MySqlException I am trying to insert string in LONGTEXT column. Length of string is 75402 and byte count is 150804 (about 147 KB)

Throws an exception Data too long for column On inserting record.

MySQL document state that LONGTEXT can contains maximum 4GB of data

Any suggestion will be welcome

UPDATE:

Exception occurs when characters length is greater than 65535

But 65536 is a max length of TEXT datatype and I am using LONGTEXT here.

http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

  LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

  A TEXT column with a maximum length of 4,294,967,295 or 4GB (232 − 1)
  characters. The effective maximum length is less if the value contains
  multibyte characters. The effective maximum length of LONGTEXT columns
  also depends on the configured maximum packet size in the
  client/server protocol and available memory. Each LONGTEXT value is
  stored using a 4-byte length prefix that indicates the number of bytes
  in the value.

http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet
http://dev.mysql.com/doc/refman/5.7/en/memory-use.html


Count number of common column names among all tables in a database Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over sys.columns, if possible.

For example, if I have 4 tables, I'd expect output like this:

list1        list2        commonColumns
-----       --------     --------------------
table1     table2         1
table1     table3         2
table1     table4         0
table2     table1         1
table2     table3         5
table2     table4         2
table3     table1         3
table3     table2         0
table3     table4         1
table4     table1         2
table4     table2         3
table4     table3         0

You probably want schema name along with table name, and here is a way to get the information from the proper catalog views instead of INFORMATION_SCHEMA (more on why here).

;WITH c AS
(
  SELECT
    t = QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
      + '.' + QUOTENAME(OBJECT_NAME([object_id])),
    name
  FROM sys.columns
  WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
)
SELECT
   list1 = c1.t,
   list2 = c2.t,
   commonColumns = COUNT(CASE WHEN c1.name = c2.name THEN 1 END)
 FROM c AS c1
 INNER JOIN c AS c2
 ON c1.t < c2.t
 GROUP BY c1.t, c2.t
 ORDER BY c1.t, c2.t;

SQLFiddle demo


dm_exec_describe_first_result_set_for_object for multiple result sets Does a procedure exist to return metadata for all result sets in a stored procedure?

Something like sys.dm_exec_describe_first_result_set_for_object, but for all result sets?

Eventually, I will want to discover the metadata for every result set in every stored procedure in the database. For now, I would settle for just the result sets that sys.dm_exec_describe_first_result_set_for_object cannot describe. That is, the 2nd, 3rd, and Nth result sets.

Looking at using SQLCLR to do this now:

How to save results of a procedure with more than one result set
tSQLt - DB Unit Testing for SQL Server - ResultSetFilter.cs

Does a procedure exist to return metadata for all result sets in a stored procedure?

No and Yes.

No

There is no pure T-SQL means of accessing more than the first result set. Even OPENROWSET and OPENQUERY have the same limitation:

  Although the query might return multiple result sets, OPEN(ROWSET|QUERY) returns only the first one.

For the record, I am neither saying nor implying that there is any common technical reason for this limitation. I am just pointing out that the limitation is not confined to sp_describe_first_result_set, sys.dm_exec_describe_first_result_set, and sys.dm_exec_describe_first_result_set_for_object.

Yes

The only way to capture info -- result set meta-data and even the results -- for result sets 2 - n is via application code. You would first execute the queries / stored procedure(s) by using SqlCommand.ExecuteReader(CommandBehavior) with a CommandBehavior of KeyInfo. Then, you can get the result set meta-data using the SqlDataReader.GetSchemaTable method, and calling the SqlDataReader.NextResult method to cycle through the result sets. Just keep in mind that while doing this via app code does not have the limitations of not working with Dynamic SQL and temporary tables, it does actually run the SQL code, and if you have DML statements and only want the result set meta-data without causing any data changes, then you will have to wrap the SQL you are testing in a BEGIN TRAN / ROLLBACK TRAN.

The type of application can be a regular Windows App, Console App, Web App, etc, or it can even be a SQLCLR function / stored procedure.

With regards to doing this via SQLCLR, a stored procedure already exists that does what is described here. It is called DB_DescribeResultSets and is part of the SQL# library (which I am the author of, and while there is a Free version, DB_DescribeResultSets is only available in the Full version).


How to write search query to retrevie the rows with biggest DateTime <= 2010102248 in SQL I am creating a table and now trying to create my first Search Query.

I have created the table successfully like this:
 CREATE TABLE testTable
 (
   DateTime INT    NOT NULL, /*yyyyddhhmm, 2010102345*/
   FeatureNbr      SMALLINT  NOT NULL,
   Val FLOAT(53)   NOT NULL
 );

I have successfully inserted values to the table like this:
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 0, 12.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 1, 13.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102245, 2, 14.56789);

 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 0, 15.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 1, 16.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102246, 2, 17.56789);

 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102247, 0, 18.56789);
 INSERT INTO testTable(DateTime, FeatureNbr, Val) VALUES(2010102247, 1, 19.56789);

Now I have a little problem and questions. I am trying to create a search query to retreive ALL FeatureNbrm,Val,DateTime where the biggest datetime is found <= 2010102248 for each of the three features. The lines that should be retrevied would then be the below 3 lines, - for ALL the three features which are: 0,1,2 (We don't know how many features it is. We just want ALL of them/rows with the biggest datetime)

2010102246, 2, 17.56789
2010102247, 0, 18.56789
2010102247, 1, 19.56789

But I only retrieve those lines in this format for the features: 0 and 1
2, 2010102246, 17.56789 <-- is missing
0, 2010102247, 18.56789
1, 2010102247, 19.56789

The code I use is the below but I don't understand how I should write this correctly?
SELECT FeatureNbr, DateTime, Val
FROM
  testTable
WHERE
     testTable.DateTime=
      (
        SELECT
          MAX(DateTime)
        FROM
          testTable
        WHERE
          DateTime <= 2010102248
      )

You need to make sure your subquery is correlated with the main query, so you are getting the max timestamp for each feature:
SELECT
  test.FeatureNbr,
  test.DateTime,
  test.Val
FROM
  testTable test
WHERE
  DateTime =
    (
      SELECT
        MAX(DateTime)
      FROM
        testTable
      WHERE
        FeatureNbr = test.FeatureNbr  --This is what you are missing
          AND DateTime <= '2020-10-06 12:55:00' --Or whatever
    )

See this DB Fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a57b5d632f53a973a6225718e2baea3a
A few other points:

Always store date/time values as DATETIME2 in SQL Server.  Otherwise you can end up with invalid date/time combinations and won't be able to use the native date functions without first converting.
You need to make sure your primary key is defined - the right clustered index (in this example (FeatureNbr,Datetime)) is critical - it will allow the query to complete reading the table once and without sorts.
This was addressed in my answer here, but I know you are starting out so it might not have been apparent due to the length.
Should also be mentioned that with the proper clustered index, SQL Server will recognize the queries provided by nbk and Andy Mallon (which are identical since a CTE is just another way of writing the same thing) to be equivalent to the above query - they will produce the exact same query plan and return the exact same result.  It's just more code.

To address your comment:

Yes I begin to understand it a bit better but its so new. I know C#
for 10 years but this is alot of "nesting" like it feels.
After WHERE there should be a condition. You put: WHERE DateTime = (...). Is this  some kind of "temp table" that is happening here, where you later:
FeatureNbr = test.FeatureNbr? I also wonder if it will be correct to
put this condition against DateTime like this? WHERE FeatureNbr =
test.FeatureNbr AND DayTime <= 2010102248 (I know the datetime format
got wrong here but it will work for the example as an integer)

Making the transition from object oriented/traditional programming to database development is a hard one as you're used to specifying exactly HOW things are done.  SQL is a declarative language, we specify what we want and allow the database engine to determine the optimal plan.
So the "nesting" here is just specifying that the Datetime must equal the max Datetime less than or equal to the specified value for each FeatureNbr.  The query engine then looks at the table, the indexes, the column statistics, and decides the best way to fulfill that query.  If you have a good data model, you rarely have to tune queries.
You will probably want to start to familiarize yourself with how to read query/execution plans and the specific operators.  Then you can experiment with how the engine actually fulfills the requests, how your clustered index influences the plan, etc.
If you were to generate query plans for three of the answers (nbk's, Andy's, and my own) they would be identical with the right clustered index.  The answer that uses ROW_NUMBER() forces a sort, which is instructive in its own way.
Additional comment:

If I understand, it doesn't matter in what order you write this(When
you combine columns): CONSTRAINT PK_testTable PRIMARY KEY
(FeatureNbr,DateTime) it could be written like this also: CONSTRAINT
PK_testTable PRIMARY KEY (DateTime,FeatureNbr)

So this brings up a very important concept for the physical implementation of tables in SQL Server, which is the rows are stored (more or less) in the order according to the position of the rows specified during the creation of the clustered index.
So: (FeatureNbr,Datetime) sorts first by FeatureNbr (let's call this option 1) while (Datetime,FeatureNbr) sorts first by Datetime (option 2).
If we do a query that looks for one row and one row only (say, FeatureNbr = 2 AND Datetime = '2020-10-06 10:33:00'), both indexes will perform identically.  But if we do a query that has to look across ranges of Datetime for a given FeatureNbr, option 1 would allow the clustered index to be utilized, option 2 would result in a table scan.  This is because for option 1 the engine knows all values for a FeatureNbr will be on the same page(s) (more or less), but for option 2 the relevant rows could be stored anywhere in the table.
For this reason, a date or datetime column should be the last row in an index as a general rule (in databases there are always exceptions).


Oracle - Extract user DDL for migration I need to export and import  data from a Oracle 9i to Oracle 12.2 database.

This old app doesn't log in as one privileged user - it logs in for each user.  I have hundreds of users with nothing but synonyms.

I need to pre-create the users and their privileges so I can import.

There are scripts all over the internet:
* Ask Tom suggests import and an indexfile (which I find kludgy).
* DBMS_METADATA.GET_DDL scripts, as well.

However, being in a rush, I haven't found one script to cycle through all the users and generate their DDL and grants, etc.

I want to run one 9i script to grab everything and one 12.2 script to create users, but can't find anything useful.

Can you suggest how I should solve this? Or am I making this too complicated?

I resolved my problem using the SQL Developer tool.

I followed the steps in this article to generate the DDL for anything in the database.  I had an 11g DB that I needed to migrate in addition to a 9i and this process was easy for 11g.

However, for 9i I used the script in this post and ran it in SQLDeveloper to pre-create my users.  Just save the output of the script to a file and then run in SQLDeveloper for ease.


simple RECOVERY mode, but with not static data About recovery modes, I know that if you want point in time recovery, we should use FULL recovery mode. if this is not necessary, you can use SIMPLE recovery mode.

But, I read about Simple recovery mode, and it says that we should use this if point in time recovery is not important and if your data is static.

Here we don't need LOG backups, we only use Full backups and Diffs, and I thought that should be good for performance and disk space to use Simple recovery databases. But As I said, we don't have static data. there's a lot of insert, updates and development updates ( yes I don't understand why they don't need point in time recovery , but, anyway...).

My question is, is still a good Idea to use simple recovery, or by not having static data, the log will still grow, and etc?

Ultimately, your recovery model is dictated by your business needs.

It all boils down to the question "How much data is it okay to lose if the server goes belly-up?".

With FULL RECOVERY, that time is dictated by the time between log backups (and can be reduced if a tail-log backup is available).  You manage the log backups and how full they get by taking LOG backups.  If you don't take log backups, the log will simply continue to fill until it runs out of disk space.  At that time, transactions will fail due to insufficient disk space.

With SIMPLE RECOVERY, that time is increased to the time between the current time and your last differential or full backup.  The log is managed by CHECKPOINT operations that occur automatically behind the scenes (Recovery Interval).  Unless you are doing transactions whose size exceeds the size of the current log file, it won't grow.

This is also under the assumption that your backups do, in fact, work.  Backups are only valid if they can be restored successfully.

I'd recommend putting these decisions in the hands of your superiors after you present to them what the differences are and what that means in terms of RPO/RTO.


Undesirable Nest Loop vs. Hash Join in PostgreSQL 9.6 I've a trouble with PostgreSQL 9.6 query planning. My query looks like this:

SET role plain_user;

SELECT properties.*
FROM properties
JOIN entries_properties
  ON properties.id = entries_properties.property_id
JOIN structures
  ON structures.id = entries_properties.entry_id
WHERE structures."STRUKTURBERICHT" != ''
  AND properties."COMPOSITION" LIKE 'Mo%'
  AND (
    properties."NAME" LIKE '%VASP-ase-preopt%'
    OR properties."CALCULATOR_ID" IN (7,22,25)
  )
AND properties."TYPE_ID" IN (6)

I have Row-Level Security enabled for the above-used tables.

with set enable_nestloop = True, the query planner runs
Nested Loop joining with a total running time of about 37 seconds: https://explain.depesz.com/s/59BR
with set enable_nestloop = False, the Hash Join method is used and the query time is about 0.3 sec:  https://explain.depesz.com/s/PG8E

I did VACUUM ANALYZE before running the queries, but it didn't help.

I know that it is not a good practice to set enable_nestloop = False, and any other similar options for the planner. But how could I "convince" the planner to use hash joins without disabling nested loops?

Rewriting the query is an option.

If I run the same query under a role that bypasses RLS, then it is executed very fast. The row-level security policy looks like this:

CREATE POLICY properties_select
ON properties
FOR SELECT
USING (
  (
    properties.ouid = get_current_user_id()
    AND properties.ur
  )
  OR (
    properties.ogid in (select get_current_groups_id())
    AND properties.gr
  )
  OR properties.ar
);

Any ideas or suggestions would be greatly appreciated.

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side of the Nested Loop which already has 3,444 rows. That's a hella-lot when you're only expecting one (which isn't even much of a "loop"). Anywayy..

Further examination as we move down shows that the Hash Join is really borked by the estimations arising from this,

Filter: (((properties."COMPOSITION")::text ~~ 'Mo%'::text) AND (((properties."NAME")::text ~~ '%VASP-ase-preopt%'::text) OR (properties."CALCULATOR_ID" = ANY ('{7,22,25}'::integer[]))))

PostgreSQL expects that to return one row. But it doesn't. And, that's really your problem. So some options here, that don't involve taking out a sledge hammer and disabling nested_loop

You can add an index or two to properties to help it potentially skip the seq scan entirely, or better estimate the return.

CREATE INDEX ON properties USING ( "TYPE_ID", "CALCULATOR_ID" );
-- the gist_trgm_ops may or may not be needed depending on selectivity of above.
CREATE INDEX ON properties USING GIST (
  "COMPOSITION" gist_trgm_ops,
  "NAME"        gist_trgm_ops
);
ANALYZE properties;

Alternatively, you can move the properties stuff to a CTE or subselect with OFFSET 0 which creates a fence.

WITH t AS (
  SELECT *
  FROM properties.
  WHERE "COMPOSITION" LIKE 'Mo%'
  AND (
    "NAME" LIKE '%VASP-ase-preopt%'
    OR "CALCULATOR_ID" IN (7,22,25)
  )
  AND "TYPE_ID" IN (6)
)
SELECT * FROM structures
JOIN t ON (
  structures.id = entries_properties.entry_id
)


ORA-39142: incompatible version number 3.1 error work-around I am trying to import an Oracle backup .DMP file into a new Oracle DB and I'm getting the error "ORA-39142: incompatible version number 3.1".  My research tells me that this is because the version of Oracle that the .DMP file is coming from is newer than the version I have running (running on a VM), but I have no control over the export process for this .DMP file so I can't play games there to "downgrade" the export.

Is there a way import this backup file without fiddling with the export process and without upgrading the Oracle VM I'm using?

Here's an interesting blog entry from Burleson computing that appears potentially useful for your circumstance.

From the site:

  There are two cases where it is possible to import into an earlier
  release:

  1 - Using the version parameter in Data Pump.  See my notes on
  importing between different releases of Oracle with Data Pump
  export/import

  2 - Using a exp dump file with a Data Pump impdp utility.


SQL Server High Availability and Sharepoint - log file grows when no database activity Just started as a dba with a company running SQL Server 2012 High Availability and SharePoint.

Short description: Database with no user activity still has growing log file.

Long description:

They have many, many SharePoint dbs where the logfile size dwarfs the datafile size.  They've just been throwing extra disk space at the issue.

They were in Full Recovery (mandatory for HA) but weren't taking Log backups.  Simply by taking a log backup and a DBCC Shrinkfile to a manageable size I've started to bring things under control.

However, on my trial database the logfile is growing rapidly again.  As far as I can ascertain, no user activity in the database has taken place (insert/update/deletes) since I did the log backup.

Running

select [Current LSN], Operation, Context, [Transaction ID]
    , SPID, [Begin Time], [Transaction Name]
from fn_dblog(null,null)

shows hundreds of rows of GhostCleanup Task (looking for deletes) followed by a Begin Transaction and a Commit Transaction:

Can anyone explain what's causing this?  I can obviously set up Maintenance Plans to do Log backups much more frequently to keep the size in check, but I'd like to understand why there are all these transactions occurring in a 'static' database.

I've read about setting trace flag 661 to disable ghost cleanup, but don't want to do a server-wide change at this proof of concept stage.

I'm not sure if this is a HA issue, SharePoint issue, neither or both!  I've read lots of posts about SharePoint causing runaway logs but no real explanation.

It's the primary AOAG server. There are two synchronous secondaries and an asynchronous DR secondary.

dbcc opentran(EXT_Metadata_DB)

reports:

  No active open transactions.

Log file is still growing despite no user activity. There were 5 Autogrowth events overnight.  No transactional replication on this db, though HA obviously needs to write the log to the secondary servers. No blocked processes visible.

select d.name, d.log_reuse_wait_desc
from sys.databases d
where d.database_id = 15

gives:

EXT_Metadata_DB    LOG_BACKUP

This seems to imply that all transactions are in the current VLF which can't be cleared. Puzzling thing is, I haven't done any log backups since I did the Shrinkfile. I'm using a tiny database for this investigation.  Data file is only 14MB and I shrank the log file down to 4MB (13% full).  A day or so later, data file remains at 14MB, but log file has now grown to 69MB and is 92% full.  The particular SQL Server instance I'm looking at has 675GB of log files.

select name, size
from sys.database_files
where type = 1

gives:

EXT_Metadata_DB_log 8872

before a checkpoint.  Ran checkpoint but size remains at 8872.  Server recovery interval is the default 0.

If I run:

SELECT context AS Context, [Transaction Name] as Description, COUNT(context) AS Count
from fn_dblog(null,null)
GROUP BY context, [Transaction Name]
order by count desc

I get 19 rows:

LCX_NULL            NULL                        12681
LCX_NULL            GhostCleanupTask            12326
LCX_BOOT_PAGE       NULL                        12185
LCX_CLUSTERED       NULL                        451
LCX_NULL            UpdateQPStats               81
LCX_FILE_HEADER     NULL                        35
LCX_NULL            SetFileSize                 33
LCX_PFS NULL                                    12
LCX_TEXT_MIX        NULL                        10
LCX_BOOT_PAGE_CKPT  NULL                        10
LCX_DIFF_MAP        NULL                        7
LCX_NULL            DeallocUnlinkedReorgPage    6

No user activity, but 37847 transaction entries in 24 hours. Is this to be expected? I've not modified any data.  It's a static database of metadata.

The cleanup is normal behind the curtain SQL server functionality.  Never ever ever disable this.  I would increase the transaction log backup frequency.  I like mine set to every 15 minutes to keep the backups small and fast.


MySQL 100% CPU Usage Reading from a 500.000 Rows table I'm using MariaDB 10.1

My config is the follow

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

key_buffer_size = 2G
read_buffer_size = 2G

symbolic-links=0
skip-name-resolve=1
query_cache_limit       = 4M
query_cache_size        = 256M

expire_logs_days        = 10
max_binlog_size         = 100M

max_connections  = 8096
back_log = 4096
open_files_limit = 20240
innodb_open_files = 20240

table_open_cache = 8096
table_definition_cache = 8096

tmp_table_size = 2G
max_heap_table_size = 2G

innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
performance_schema = 0
innodb-file-per-table = 1
innodb_table_locks = 0

thread_cache_size = 8096
thread_handling = pool-of-threads
thread_pool_size = 20
thread_pool_idle_timeout = 20
thread_pool_max_threads = 2048

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have one table MYISAM (DESCRIBE)

MariaDB [ip2proxy]> DESCRIBE ip2proxy_px2;
+--------------+------------------+------+-----+---------+-------+
| Field        | Type             | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-------+
| ip_from      | int(10) unsigned | NO   | PRI | NULL    |       |
| ip_to        | int(10) unsigned | NO   | PRI | NULL    |       |
| proxy_type   | varchar(3)       | YES  |     | NULL    |       |
| country_code | char(2)          | YES  |     | NULL    |       |
| country_name | varchar(64)      | YES  |     | NULL    |       |
+--------------+------------------+------+-----+---------+-------+
5 rows in set (0.03 sec)

MariaDB [ip2proxy]> SELECT COUNT(*) FROM ip2proxy_px2;
+----------+
| COUNT(*) |
+----------+
|   482450 |
+----------+
1 row in set (0.00 sec)

We do more than 8.000 reads / second against that database to search for IPs.
The MySQL Is going to 100% in all Cores (12 Threads) and then it is crashing.

We want all the requests to be executed without any delays.

The query we execute is:

SELECT proxy_type FROM `ip2proxy_px2` WHERE {IPLONG} BETWEEN ip_from AND ip_to LIMIT 1;

Any idea what im doing wrong?

How much RAM?  Many of the entries in my.cnf look "too big" unless you have a lot of RAM.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

InnoDB would be faster because the data is clustered with the PRIMARY KEY.

What is happening...  Each run of that query is scanning half the index, starting with IPLONG=ip_from and going to the end of the index.  Meanwhile, it is reaching over to the data (since it is separate in MyISAM) for the other columns.

Are the ip_from..ip_to pairs non-overlapping?  If so, you could check one row, then quit.  If they are overlapping, there is no hope.

The LIMIT 1 will not be sufficient without ORDER BY ip_from.

You realize that the table cannot handle IPv6?

Make sure that country_code is CHARACTER SET ascii.  Don't include country_name in the same table; build a lookup (normalization) table mapping country_code to country_name.


SQL Server BPA 2008R2 I have been using SQL Server BPA for getting good information from Microsoft. I was using its 2005 version in which I used to export the results in CSV format but recently I got two new servers which has got SQL Server 2008 R2 installed on it and I know I can't run BPA 2005 on these, so chose the R2 version of it, but it doesn't have an option to save the report on CSV format, only XML, I have tried using Excel to convert it into CSV but no use, even-though it display the details but can't narrow down the results any idea of converting the results to CSV format?

While PowerShell is high on my list of things to learn I don't know much about it right now and I needed a csv quick, so I wrote a simple query in T-SQL to get the data I needed out of the XML in a tabular format and then just copied that into excel and saved it.

-- get XML from file
DECLARE @xml XML;
SELECT
    @xml = CAST(A.[BulkColumn] AS XML)
FROM OPENROWSET(BULK 'C:\bpa_results.xml', SINGLE_BLOB) A;

-- query XML data
WITH XMLNAMESPACES ('http://schemas.microsoft.com/powershell/2004/04' as x)
SELECT
    S.*
FROM @xml.nodes('x:Objs/x:Obj') A ([obj]) -- "root" objects
CROSS APPLY A.[obj].nodes('x:LST/x:Obj') B ([obj]) -- findings objects
CROSS APPLY
    (
        SELECT
            A.[obj].value('@RefId', 'NVARCHAR(500)') [root_obj_RefId],
            B.[obj].value('@RefId', 'NVARCHAR(500)') [findings_obj_RefId],
            B.[obj].value('x:Props[1]/x:S[@N="ResultNumber"][1]', 'NVARCHAR(500)') [ResultNumber],
            B.[obj].value('x:Props[1]/x:S[@N="ResultId"][1]', 'NVARCHAR(500)') [ResultId],
            B.[obj].value('x:Props[1]/x:S[@N="ModelId"][1]', 'NVARCHAR(500)') [ModelId],
            B.[obj].value('x:Props[1]/x:S[@N="SubModelId"][1]', 'NVARCHAR(500)') [SubModelId],
            B.[obj].value('x:Props[1]/x:S[@N="RuleId"][1]', 'NVARCHAR(500)') [RuleId],
            B.[obj].value('x:Props[1]/x:S[@N="ComputerName"][1]', 'NVARCHAR(500)') [ComputerName],
            B.[obj].value('x:Props[1]/x:S[@N="Context"][1]', 'NVARCHAR(500)') [Context],
            B.[obj].value('x:Props[1]/x:S[@N="Source"][1]', 'NVARCHAR(500)') [Source],
            B.[obj].value('x:Props[1]/x:S[@N="Severity"][1]', 'NVARCHAR(500)') [Severity],
            B.[obj].value('x:Props[1]/x:S[@N="NeutralSeverity"][1]', 'NVARCHAR(500)') [NeutralSeverity],
            B.[obj].value('x:Props[1]/x:S[@N="Category"][1]', 'NVARCHAR(500)') [Category],
            B.[obj].value('x:Props[1]/x:S[@N="Title"][1]', 'NVARCHAR(500)') [Title],
            B.[obj].value('x:Props[1]/x:S[@N="Problem"][1]', 'NVARCHAR(500)') [Problem],
            B.[obj].value('x:Props[1]/x:S[@N="Impact"][1]', 'NVARCHAR(500)') [Impact],
            B.[obj].value('x:Props[1]/x:S[@N="Resolution"][1]', 'NVARCHAR(500)') [Resolution],
            B.[obj].value('x:Props[1]/x:S[@N="Compliance"][1]', 'NVARCHAR(500)') [Compliance],
            B.[obj].value('x:Props[1]/x:S[@N="Help"][1]', 'NVARCHAR(500)') [Help],
            B.[obj].value('x:Props[1]/x:S[@N="Excluded"][1]', 'NVARCHAR(500)') [Excluded]
    ) S
WHERE   S.[ResultNumber] IS NOT NULL
    AND S.[Severity] != 'Information';


SQL Server - SQL script to restore database backup file into a new database I am trying to create a SQL script to restore a database backup file into a new database in SQL Server.

I can do it manually without problems:

Right click on Databases  =>  Restore Database
Tick on Device
[General] Browse to the location where the BAK files is located and add it (click ok)
        Destination => Database => Type in name of new database
[Files] Change the names of the mdf and ldf files in 'Restore As'
[Options] Un-tick Tail-Log backup

In order to do this with a SQL script I am trying with the following:

RESTORE DATABASE new_db
FROM DISK = 'E:\test\test.BAK' WITH
MOVE 'E:\MSSQLSERVER\DATA\old_db.mdf' TO 'E:\test\new_db.mdf',
MOVE 'E:\MSSQLSERVER\LOG\old_db_log.ldf' TO 'E:\test\new_db.ldf'

I get the following error:

Logical file 'E:\MSSQLSERVER\DATA\old_db.mdf' is not part of database 'new_db'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

If I run this:

RESTORE FILELISTONLY
FROM DISK = 'E:\test\test.BAK'

...and then I run the RESTORE script above, I get the same error.

Putting here as the comment I made did solve the problem.

The source of the MOVE syntax is the logical name of the file, not it's old physical name.  You can get the logical name by doing a FILELISTONLY restore or by querying sys.database_files if you still have access to the source.

MOVE <logical name> TO <path and name of file to use>

Additionally, most (all?) of the various wizards through Management Studio have an option to script the commands out for you.  This is invaluable for learning the syntax and various options.


Information about youngest child with inner subquery Consider a table like this :

CREATE TABLE people (
  id INTEGER NOT NULL PRIMARY KEY,
  motherId INTEGER REFERENCES people(id),
  fatherId INTEGER REFERENCES people(id),
  name VARCHAR(30) NOT NULL,
  age INTEGER NOT NULL
);

Populated with data like this :

INSERT INTO people(id, motherId, fatherId, name, age) VALUES(1, NULL, NULL, 'John', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(2, NULL, NULL, 'Marry', 50);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(3, 2, 1, 'Bob', 30);
INSERT INTO people(id, motherId, fatherId, name, age) VALUES(4, 2, 1, 'Alice', 20);

The expectation is to have output like this (order does not matter) :

 name  | age
-------+-----
 John  | 20
 Marry | 20

The explanation for the output is John and Marry are parents and have two children: Bob and Alice. Alice is younger and is 20 years old.

This is my solution :

select parents.name as name, kids.age as age from people kids
  inner JOIN
  (
    SELECT id, name FROM people where motherId is null and fatherId is null
  ) parents
  ON (parents.id = kids.motherId OR parents.id = kids.fatherId)
  group by parents.id

However this trips when several generations of parents have the same name.  Any ideas why this might be happening?

You should use minimum age in the select clause so that group by is meaningful. Also, why do you need the motherId and fatherId to be null in nested subquery? If you do not add these conditions then you do not need a nested subquery:

select parents.name as name, min(kids.age) as age
from
people kids
inner JOIN
people  parents
ON (parents.id = kids.motherId OR parents.id = kids.fatherId)
group by parents.id

By the way, does the query you posted really works? In PostgreSQL 9.3.23 I am getting an error:

  column "parents.name" must appear in the GROUP BY clause or be used in
  an aggregate function


Amazon RDS MySQL limitations I intend to use Amazon RDS MySQL with one db.t2.micro instance.

Are there any limitations of max_connections and max_questions ?

I searched on the Internet and I found that there is a default value for max_connections, but I can change it through parameters, is this correct ?

And I didn't find anything about max_questions.

Can I set any value for max_connections and max_questions ? Or I am limited to a certain value ?

Thanks

Yes, these values can be changed (within limits).

1. max_connections

You can change this value via parameter groups. The maximum allowed value is 100 000. Note that you are also constrained by available memory (1 GB in your case).

2. max_questions

This value is stored per user in the mysql database table user. The default value is 0, which means "unlimited". The column type is INT, so the maximum value is 4294967295.

You can check the current values like this:

SELECT `User`,`max_questions` FROM `mysql`.`user`;


Would index lookup be noticeably faster with char vs varchar when all values are 36 chars I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is:

922475bb-ad93-43ee-9487-d2671b886479

There is no possible hope of changing this approach, however performance with index access is poor. Setting aside the myriad of reasons this might be, there is one thing I noticed that seemed less than optimal - despite all id values in all many tables being exactly 36 characters in length, the column type is varchar(36), not char(36).

Would altering the column types to fixed length char(36) offer any significant index performance benefits, beyond the very small increase in the number of entries per index page etc?

Ie does postgres perform much faster when dealing with fixed-length types than with variable length types?

Please don't mention the minuscule storage saving - that's not going be matter compared with the surgery required to make the change to the columns.

No. No gain at all. The manual explicitly states:

  Tip: There is no performance difference among these three types, apart
  from increased storage space when using the blank-padded type, and a
  few extra CPU cycles to check the length when storing into a
  length-constrained column. While character(n) has performance
  advantages in some other database systems, there is no such advantage
  in PostgreSQL; in fact character(n) is usually the slowest of the
  three because of its additional storage costs. In most situations text
  or character varying should be used instead.

Bold emphasis mine.

char(n) is a largely outdated, useless type. Stick with varchar(n). Without need to enforce a maximum length, varchar or text are a tiny bit faster. (You won't be able to measure a difference.)

If all strings are exactly 36 characters in length, there is no storage saving either way, not even a minuscule one. Both have exactly the same size on disk and in RAM. You can test with pg_column_size() (on an expression and on a table column).

And if all strings must have 36 characters, rather make it text with a CHECK (length(col) = 36) constraint enforcing exact length, not varchar(36) only enforcing max. length.

Related:

Any downsides of using data type “text” for storing strings?

You didn't ask for other options, but I'll mention two:

COLLATION - unless you are running your DB with the "C" collation. Collation is often overlooked and possibly expensive. Since your strings don't seem to be meaningful in a natural language, there is probably no point in following COLLATION rules. Related:

How do I efficiently get "the most recent corresponding row"?
EXECUTE within function not using index?

Extensive benchmark comparing (among other) the effect of COLLATE "C" on performance:

Slow query ordering by a column in a joined table

UUID, obviously. Your string suspiciously looks like a UUID (32 hex digits plus 4 delimiters). It would be much more efficient to store these as actual uuid data type, which is faster in multiple ways and only occupies 16 bytes - as opposed to 37 bytes in RAM for either char(36) or varchar(36) (stored without delimiters, just the 32 defining char), or 33 bytes on disk. But alignment padding would result in 40 bytes either way in many cases.) COLLATION would be irrelevant for the uuid data type, too.

SELECT '922475bb-ad93-43ee-9487-d2671b886479'::uuid

This may be helpful (last chapters):

Convert hex in text representation to decimal number

See also:

What is the overhead for varchar(n)?
What is the optimal data type for an MD5 field?


Grant select on all synonyms of schema to another schema in Oracle I need to write a query for granting the select privilege to all synonyms and tables of one schema to another schema in Oracle.
Example:
Admin schema has all the tables. I want the User schema to access all the tables owned by Admin. So I want to grant select privilege to User. How can I write a query to accomplish this task?

Here the script to generate grant select on all the tables and synonyms.

select 'grant select on Admin.' || object_name || ' to User;'
from user_objects
where object_type in('TABLE','SYNONYM');

Then you have to create a script to run these grant statements at once or you can use PL/SQL as well. Type the following in the SQL prompt.

SQL> spool grant_statements.sql
SQL> set pagesize 0
SQL> set feedback off
SQL> set linesize 300
SQL> set trimout on
SQL> set trimspool on
SQL> select 'grant select on Admin.' || object_name || ' to User;' from user_objects where object_type in('TABLE','SYNONYM')
/
SQL> spool off

And you have got the script file you can run it.
OR
You can run the following PL/SQL block (Run as admin user).

BEGIN
  FOR s IN (SELECT *
              FROM user_objects where object_type in('TABLE','SYNONYM'))
  LOOP
    EXECUTE IMMEDIATE  'grant select on admin.' || s.object_name || ' to user';
  END LOOP;
END;


Why is a duplicate record inserted when it should not be? Why does this command

insert into "deliveryLog" select * from temp_dellog where "deliveryID" < 15620652

give this output?

********** Error **********

ERROR: duplicate key value violates unique constraint "deliveryLog_pkey"
SQL state: 23505
Detail: Key ("deliveryID")=(15620652) already exists.

Both tables have identical structure, deliveryID is a bigint.

This turned out to be a red herring.  When pgadmin gets disconnected midquery, it returns the last error again even if it was an entirely different command! :)


GIST exclude constraint with a specific value Is there a way to represent a constraint, where I prevent date range overlaps, but only when a column equals a specific value? So I can have zero or many inactive, but only ever one active?
I'm running into a problem where i need a gist constraint to check date range overlaps but only if a column equals a specific value ('active' in this case).
We have a table which has dates and there can be overlap, if a column equals a specific value (active can only ever have one, but inactive can be zero or many).
GIST seems to be able to say prevent any duplicate (2 active, but also excludes 1 active, 1 inactive, or inactive > 1).
Putting this in two constraints fails, because the gist only covers the range overlap and join id.
e.g.
ALTER TABLE "a_table"
  ADD CONSTRAINT "CHK_some_name"
  EXCLUDE USING gist ("a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

ALTER TABLE "a_table"
  ADD CONSTRAINT "CHK_some_name"
  EXCLUDE USING gist ("an_enum" with =, "a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

Any help/insight would be greatly appreciated

It sounds like you need a partial constraint.
alter table a_table add constraint foobar exclude using gist
    (tstzrange("start_time", "end_time") WITH &&) where (active);


Restoring system databases on Availability group replicas I'll start this off by saying I'm very new to availability groups.

Looking at information on availability groups, it looks like you can't add system databases to a group. MSDN seems to indicate that we should manually replicate logins, jobs, etc.

Are there any consequences of restoring system databases from backups taken on a primary replica to a secondary replica in an availability group?

All of your server configuration is stored in the master database along with your users logins so I wouldn't recommend restoring the master database from the primary.

As for copying users and jobs, have a look at dbatools . Its a set of Powershell tools that include ones for copying users and jobs.


RMAN Backups - Can more than one media management software solution be used? Does anyone know if it's possible for two different backup utilities to be used to backup the same Oracle database? Both utilities would use RMAN.
So, for example, take an Oracle 11g standby DB with a daily online backup to tape via Netbackup (using the RMAN recovery catalog) and then also take another separate daily backup via TIVOLI (also using the RMAN catalog), could they co-exist in some way? Obviously it's a not a sensible approach, this is just a question of what is or is not possible. Many thanks.

Creating the symlink $ORACLE_HOME/lib/libobk.so pointing to the media management library is not a strict requirement. As part of RMAN channel allocation, the path of vendor speficic library can be specified in the SBT_LIBRARY parameter.
I never tried this, but just by allocating the SBT channels with different parameters should make this work.
UPDATE:
For your other question about restoring backup pieces from different media:
RMAN has a feature called Autolocation. The RMAN SET AUTOLOCATE ON command is deprecated since 10.2 because this feature is enabled by default.  RMAN only needs to track that for example the backup of datafile 1 is included in backuppiece vlv6su4u_1_1 with channel type SBT_TAPE. Using this information and the autolocation feature, RMAN is able to recognize which channel it should use from multiple channels allocated with different options, simply by querying all channels for this backuppiece. This feature was invented for handling backups taken on different RAC nodes, but I was able to use it in another scenario as well: for example after a TSM backup server was migrated (e.g full backup from old TSM server + file space, incremental and archivelog backups from new TSM server + file space, channels allocated with different tdpo.opt files pointing to old and new TSM servers).
The same may work with 2 different MMLs as well.


SSAS Tabular model table load from query with #temp table fails We have several situations where complex queries are being used for creating/populating tables in our SSAS tabular model.

The actual use cases cannot be presented here, due to both proprietary information and pure length of queries, but the following test case illustrates the situation:

SELECT s.Servername, i.instanceID, i.InstanceName, i.ClientVersion, i.MajorVersion
  INTO #InstanceList
  FROM ServerLookup s
 INNER JOIN InstanceLookup i
         ON i.ServerID = s.ServerID

SELECT i.Servername, i.InstanceName, i.ClientVersion, i.MajorVersion, im.EntryDescription, im.MonitorID
  INTO #InstanceMonitoring
  FROM #InstanceList i
 INNER JOIN InstanceMonitor im
         ON im.InstanceID = i.InstanceID

SELECT i.ServerName, i.instanceName, m.MonitorName, m.MonitorDescription
  FROM #InstanceMonitoring i
 INNER JOIN MonitorLookup m on m.MonitorID = i.MonitorID

The query runs fin in SSMS, but when we attempt to create a table in our tabular model, it throws the following error message:

  Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error. '.

There is no further information on the error.

I haven't been able to locate any documentation stating that temp tables can't be used in creating a tabular model table, and I'm reluctant to tell my developer community it can't be done without some information to back it up.

The SQL query can be converted to a stored procedure and then executed as the source SQL statement for the SSAS dimension/fact tables.  As with a typical stored procedure, you can then use temp tables without any problems.  A stored procedure can be called from SSAS by using an EXEC statement with the result set then used in the same manner as one from a view or table would be.

EXEC Schema.SP_Name


PostgreSQL, how to extract all the possible couples of a primary key in a table? I would like to find out a PostgreSQL command that reads the content of a table, and returns all the possible couplings of the primary key of the table.

Something like this:

input_table (ID is the primary key):

- - - - - - - - - -
ID | label | name
- - - - - - - - - -
11 | aaa | Jenny

24 | bbb | Larry

35 | ccc | Trevor

48 | ddd | Jules
- - - - - - - - - -

The query should return all the possible couplings of the primary keys:

- - - - - - - - - -
ID_left | ID_right
- - - - - - - - - -
11 | 24

11 | 35

11 | 48

24 | 35

24 | 48

35 | 48
- - - - - - - - - -

Is it possible to design this query? If yes, how?

Thanks

Sounds like a simple cross join:

select a.id, b.id
from input_table a
  cross join input_table b
where a.id <> b.id;


How to create two database copies in two users from same dump file I have a database dump file with me, I need to make two copies of the database using the same dump file in two users.

Is it possible?

If your dump has been created with exp then you may use imp for importing it. If you exported schema USERA and want to import these objects in schema USERB, then use command:

imp user/password@sid FROMUSER=USERA TOUSER=USERB FILE=yourfile.dmp

you may get more example of imp on the Import Export FAQ. You may import your dump two times using different USERB.


hierarchical retreival of records in postgresql I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.

PRODUCT MASTER TABLE

MASTER_ID                NAME

1                      PRODUCT1
2                      PRODUCT2
3                      PRODUCT3
4                      PRODUCT4
5                      PRODUCT5
6                      PRODUCT6
7                      PRODUCT7
8                      PRODUCT8
9                      PRODUCT9
10                     PRODUCT10

PRODUCT_CONTENT TABLE

PRODUCT_CONTENT_ID                     PRODUCT_MASTER_ID        CONTENT_ID
A1                                          1                       2
A2                                          1                       3
A3                                          1                       4
A4                                          2                       3
A5                                          2                       4
A6                                          2                       5
A7                                          3                       5
A8                                          3                       7
A9                                          3                       8
A10                                         5                       8
A11                                         7                       9
A12                                         8                       10

I am writing a query to retrieve all the products(ids associated with them) with their content products which should display something like this hierarchy

MAINPRODUCT                     SUBPRODUCT
    2                               3
    2                               4
    2                               5
    3                               5
    3                               7
    3                               8
    5                               8
    7                               9
    8                               10

I have written the following query

 SELECT PM1.PRODUCT_MASTER_ID AS MAIN,PC.CONTENT_ID AS CONTENT
    FROM PRODUCT_CONTENT PC
    LEFT JOIN PRODUCT_MASTER PM ON PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
    LEFT JOIN PRODUCT_MASTER PM1 ON
    PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
    WHERE PC.PRODUCT_MASTER_ID IN
      (
       SELECT PC.CONTENT_ID FROM PRODUCT_CONTENT PC
       LEFT JOIN PRODUCT_MASTER PM ON
          PM.PRODUCT_MASTER_ID = PC.CONTENT_ID
       LEFT JOIN PRODUCT_MASTER PM1 ON
          PM1.PRODUCT_MASTER_ID = PC.PRODUCT_MASTER_ID
       WHERE PC.PRODUCT_MASTER_ID IN ('1')
      )

IT RETURNS SOMETHING LIKE THIS

MAIN            CONTENT

2                       3
2                       4
2                       5
3                       5
3                       7
3                       8

This is a simple adjacency list traversal problem. You're not even trying to build paths, just find nodes from a root node or set. That's pretty much a textbook example of what a recursive CTE is for.

http://sqlfiddle.com/#!12/8c406/12

WITH RECURSIVE children(product_content_id, product_master_id, content_id) AS (
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  WHERE pc.product_master_id = '1'
  UNION
  SELECT pc.product_content_id, pc.product_master_id, pc.content_id
  FROM product_content pc
  INNER JOIN children c ON (pc.product_master_id = c.content_id)
)
SELECT
  product_master_id AS mainproduct,
  content_id AS subproduct
FROM children;

You could pretty much adapt this from the PostgreSQL manual, which includes an example on edgelist graph traversal.

Database design side-note: Please don't use character fields for numbers. It's also not usually a good idea to use keys like A10 if the A and 10 parts have any kind of separate meaning at all.


I am Trying to Write an Access Query to Get a person having a First Name and Last Name but not all People having any of those names I am using Microsoft Access. In my search form I have 3 text boxes that I enter text to search for: First Name; Last Name; Last 4 of SSN. With the current query when I enter text into multiple boxes it finds all the people with those names and/or last 4 of SSN. For example, I want to find Bill James with last 4 0111: If I enter text into all 3 boxes I end up with all the people that match my search parameters.

First Name      Last Name      Last4
Bill            Atherton       0123
Bill            James          0111
Bill            Wallace        0122
Frank           James          0101
Henry           James          0011

So you see I get all the people having a name of Bill and all the people having a name of James. I want to write a query so that if I enter Bill in the "First Name" text Box AND James in the "Last Name" text box I should only get people named Bill James not the rest of the people having either of those names. But I still want to be able to enter into one text box if I want to.

Here's my code so far:

SELECT Appointment.[First Name], Appointment.[Last Name], Appointment.DOB, Appointment.Last4
FROM Appointment
WHERE Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value
            OR Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value
            OR Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value
ORDER BY [Last name], [First Name];

I'm not familiar enough with Access' syntax to give you a cut-and-paste answer.  In pseudo-code, though, you can use something of this form:

WHERE
    (   (Appointment.[First Name] = Forms![Search_frm]![FirstName_tbx].Value)
     OR (Forms![Search_frm]![FirstName_tbx].Value = <an empty value>))
AND (   (Appointment.[Last Name] = Forms![Search_frm]![LastName_tbx].Value)
     OR (Forms![Search_frm]![LastName_tbx].Value = <an empty value>))
AND (Appointment.Last4 LIKE Forms![Search_frm]![Last4_tbx].Value)
     OR (Forms![Search_frm]![Last4_tbx].Value = <an empty value>))

In the above replace <an empty value> with whatever Access delivers from your search field when you don't type anything there explicitly.

To force the user to enter at least one search field add another AND to the query (again, pseudocode):

AND (FirstName Is Not NULL OR LastName Is Not NUll OR Last4 Is Not Null)


Determine Nodes in Network with PostgreSQL I have a table where every entry is a node and the table contains the direct connections of each node to other nodes. I am looking to create a view with a column for each node containing all the nodes in the chain, not just the nodes the node itself is connected to.
An example would be generating the Nodes in Chain column from the first two columns of the following table:
CREATE TABLE example
(
    node text,
    connections text[],
    nodes_in_chain text[]
)

INSERT INTO example VALUES
('a', ARRAY['a','b'],         null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'],         null),
('d', ARRAY['b','d'],         null),
('e', ARRAY['e','f'],         null),
('f', ARRAY['e','f'],         null);

Node   Connections    Nodes in Chain
"a"    "{a,b}"        "{a,b,c,d}"
"b"    "{a,b,c,d}"    "{a,b,c,d}"
"c"    "{b,c}"        "{a,b,c,d}"
"d"    "{b,d}"        "{a,b,c,d}"
"e"    "{e,f}"        "{e,f}"
"f"    "{e,f}"        "{e,f}"

That is a small simplified version of the real problem. If I can solve the example, the full table should be no problem.
The data of this table can be visualized in the following way:

I have looked into several different methods to solve this problem. I have looked into recursive CTEs but I have not managed to make them work.
Each node is connected to itself currently in the database. Not a problem to remove the connection to itself in the database if that is necessary.
Probably unnecessary background to the problem:
The origin of this problem comes from attempting to identify vehicles in traffic. The original database contains a vehicles location and speed at every time step t in a given area. The goal is to determine the time spent at a traffic light. To solve this problem a stopping area for the traffic light was identified. Each vehicle in this area with a speed below a certain threshold is considered to be waiting for the traffic light. Due to a long queue line, vehicles may however be queuing outside this area. Therefore a traffic line ("Chain of Nodes") is made with all vehicles that are within a certain distance of each other and have a low speed below. Starting from a vehicle within the identified queue area. The problem is part of scientific research into aircraft taxi times. The vehicles are therefore aircraft and the stoplights are runway thresholds.
I first performed the calculation to identify vehicles in an area with Python and pandas. However the code took 10x longer to run which made it prohibitive to the project. The code was very simple without manually introduced loops and could therefore not be accelerated (I believe). I will also be comparing the speed of performing the queuing algorithm in Python versus PostgreSQL.

Approach 1:
At first sight, it seems that I could apply a basic solution because, according to your sample data, each single connection is included in another connection.
SELECT
    e1.node,
    e1.connections,
    COALESCE(e2.connections, e1.connections) nodes_in_chain
FROM
    example e1
LEFT JOIN
    example e2
    ON e2.node <> e1.node
    AND e1.connections <@ e2.connections;

node | connections | nodes_in_chain
:--- | :---------- | :-------------
a    | {a,b}       | {a,b,c,d}
b    | {a,b,c,d}   | {a,b,c,d}
c    | {b,c}       | {a,b,c,d}
d    | {b,d}       | {a,b,c,d}
e    | {e,f}       | {e,f}
f    | {e,f}       | {e,f}

Approach 2:
But, as @ypercube pointed out, this solution doesn't work if there are 3 or more linear points in a row.
Ex: e -> f -> g -> h
As a reference to solve this question I used the answers in another related question:

Grouping on any one of multiple columns in Postgres

It uses a method called transitive closure to solve the problem.

Transitive closure
In mathematics, the transitive closure of a binary relation R on a set X is the smallest relation on X that contains R and is transitive.
For example, if X is a set of airports and xRy means "there is a direct flight from airport x to airport y" (for x and y in X), then the transitive closure of R on X is the relation R+ such that x R+ y means "it is possible to fly from x to y in one or more flights". Informally, the transitive closure gives you the set of all places you can get to from any starting place.

First let me change your sample data by adding a linear connection of 4 nodes.
DELETE FROM example WHERE node = 'f';
INSERT INTO example VALUES
('f', ARRAY['e','f','g'],     null),
('g', ARRAY['f','g','h'],     null),
('h', ARRAY['g','h'],         null);

Now applying the math:
WITH RECURSIVE al (dst, src) AS --adjacent list or list of all related nodes
(
  SELECT e1.node, e2.node
  FROM   example e1
  JOIN   example e2
         ON e1.node = any(e2.connections)
), tc (dst, src) AS
   (
     SELECT dst, src FROM al -- transitive closure
     UNION
     SELECT a1.dst, a2.src
     FROM   al as a1
     JOIN   tc as a2
            ON a1.src = a2.dst
   )
   SELECT   src, array_agg(DISTINCT dst ORDER BY dst) AS nodes_in_chain
   FROM     tc
   GROUP BY src;

Give us this result:
src | nodes_in_chain
:-- | :-------------
a   | {a,b,c,d}
b   | {a,b,c,d}
c   | {a,b,c,d}
d   | {a,b,c,d}
e   | {e,f,g,h}
f   | {e,f,g,h}
g   | {e,f,g,h}
h   | {e,f,g,h}

db<>fiddle here

NOTE: The original relation has only the immediate connections, which can be seen as paths of length 1 (2 nodes each). Approach 1 finds all paths of length 2 (3 nodes) as it applies a method of connecting once. To find paths of length N, you have to apply the methods N-1 times. To find all paths of arbitrary lengths (alas the transitive closure), you need a recursive solution, or a while loop.
It can't be done with simple SQL. (ie: one query without CTEs.)
@ypercube


What's a good SQL Server backup schedule? There seems to be a plethora of information regarding the process of setting up backup tasks, but not much information about the big-picture view of database backups. At least, it's hard to formulate a search engine query that gives you that information. I know there are three different types of backups:

Full Database Backups
Differential Database Backups
Transaction Log Backups

It seems I'm supposed to use all three of them. So, is this a schedule that makes sense?

1st of each month -- Do a full database backup.
Every day at midnight -- Do a differential database backup.
Every 15 minutes -- Do a transaction log backup.

This way, if my database fails on, say, the 12th, I'd just restore the full database backup from the 1st, do the 12 differential backups from the 1st to the 12th, and then finally restore the most recent transaction log (are the transaction logs differential?).

Finally, is a full database backup self-contained? i.e. Once I make a full database backup on February 1st, can I delete all the files from January? Of course, I'd keep a couple previous months' sets around just in case, but the question is conceptual.

As with all things in SQL Server, it depends.

First thing you need to do is make sure you understand what each type of backup does.

Books Online has all the gooey details, but here's my summary.

A FULL backup contains everything within the database.  A DIFFERENTIAL backup is cumlative NOT incremental.  In your example, if your database failed on the 12th, then you'd only need to restore the full backup from the 1st and then the most recent differential on the 12th, then followed by all the transaction log backups upto the failure.  A TRANSACTION LOG backup is only needed for databases using the full or bulk-logged recovery model.  If you're using the simple recovery model then transaction log backups are not needed.

Now that we've cleared that up...Designing a backup schedule really depends on how much data you need to recovery and how fast you need to recover it in the event of a diaster.  I would recommend starting with a full backup each day.  You can always reduce the frequency later.  Remember the differential backup is cumlative since the last full, so depending on the amount change going on in your database the differential could be larger than the full backup after a few days.  If you do a full backup each day, then you may not need to use differentials at all; however you could still do it once a day and schedule it at 12 noon.  The transaction log backup only backs up the log.  The frequency of the log backup will determine how much data you're willing to lose in the event of a failure.  If you run your log backup every 15 minutes, then you would expect to lose up to the last 15 minutes of data that changed.  15 minutes is a good frequency, but every 30 minutes works perfectly for my environment.

As I said earlier, it all depends on your environment.  After you've designed and setup your backup schedule, remember to test it on an alternate server.  Practice restoring your full, diff, and log backups so that you know everything works like you designed it.

Books Online has some good info if you plan use Maintenance Plans, but if you really want flexibility then check out Ola Hallengren's backup scripts.


User Mapping on a DB that's part of an AG. DB is READ only? I'm a sys admin being tossed in to a DBA role. I currently have two SQL Server boxes, 2016 Enterprise, in an AG. The DB has synchronized without any issues.

A third party vendor needs to have an identical user on both servers with the same User Mapping. But when I try to adjust the mappings on the secondary I get an error stating that the DB is in READ ONLY mode. How can I create this user with the correct mappings?

You'll need to create the logins on each instance (box), but you can only set up the user mappings on the one that is currently acting as the "primary" for that AG (the one that is not read only).

If you fill out all the necessary information in the "New Login" dialog, including the "User Mapping" tab, then click the "Script" button at the top, you end up with something like this:

USE [master]
GO
CREATE LOGIN [ACME\jdarnell] FROM WINDOWS
GO
USE [VersionStoreTestDB]
GO
CREATE USER [ACME\jdarnell] FOR LOGIN [ACME\jdarnell]
GO
USE [VersionStoreTestDB]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ACME\jdarnell]
GO

For this test, I said to create a new login named "test-user" and map it to the "VersionStoreTestDB" database in the "db_datareader" role.

What you'll need to do is run the "CREATE LOGIN" portion on both boxes, and then run the "CREATE USER" and "ALTER ROLE" portions on just the primary box.  The mappings will be replicated to the other box.

Note that this example uses a Windows-based login.  If you're creating SQL logins, you will need to make sure and specify the SID on both servers.  See Transferring Logins to a Database Mirror for more details on that, and a script for making the job easier.

At this point, you already know which server is the primary (since the one acting as the secondary gave you the error about the database being read only).  You can check to if you're looking at the primary or the secondary in SSMS by expanding the "Always On High Availability" folder, then the "Availability Groups" folder.  The availability groups listed will either be suffixed with "(Primary)" or "(Secondary)."


Using tsvectors with gin_trgm_ops I have a table like so.

ALTER TABLE clients ADD tsv tsvector;
CREATE INDEX index_clients_on_tsv
  ON clients
  USING gin (tsv);
CREATE TRIGGER clientstsvectorupdate BEFORE INSERT OR UPDATE
  ON clients FOR EACH ROW EXECUTE PROCEDURE
  tsvector_update_trigger(
    tsv, 'pg_catalog.english', first_name, middle_name, last_name, email, birthday_string
  );

But then I noticed that fuzzy searching was not working: "Chris", would not look up "Christopher". I did a little digging and found gin_trgm_ops Naturally the first thing I tried to do was update the index via

CREATE INDEX index_clients_on_tsv
  ON clients
  USING gin (tsv gin_trgm_ops);

But now I see an error relating to the fact I'm passing a tsvector to gin_trgm_ops.

  (Postgrex.Error) ERROR 42804 (datatype_mismatch): operator class "gin_trgm_ops" does not accept data type tsvector

What is the best way to address what I'm trying to implement?

The gin_trgm_ops is an operator class over text. Your tsv column is a tsvector, and not text. So simply that won't work. A tsvector stores lexemes not words. That means the data you're searching for may not even be there. Observe,

SELECT to_tsvector('Christopher');
  to_tsvector
---------------
 'christoph':1
(1 row)

That means after you convert Christopher to a tsvector, you lose some data in the algorithmic affix stemmer. That's fine for FTS, it's part of the compromise and how you achieve the amazing speed. It may not be desirable if that's what you're looking for though, so you have two options.

Use lexeme prefix matching with :* (assuming the lexeme has the needed information, if it doesn't you may be able to hack either the text search configuration to not-stem, or the stemmer to stem to your workload)

## returns false
SELECT to_tsvector('Christopher') @@ to_tsquery('Chris');
## returns true
SELECT to_tsvector('Christopher') @@ to_tsquery('Chris:*');

Index the actual column you're using to generate the TSV or wish to search in, using the trigram operator classes (like gin_trgm_ops). If you're always doing left-anchored search, you may consider the more efficient text_pattern_ops.


SQL 2017 CU10 on Windows Server 2019 - Availability Group Listener fails to obtain IP from DHCP We have a few instances of SQL 2017 CU10 Standard on several Windows Server 2016 machines, and normally we create Basic Availability Groups with Listeners obtaining IPs from our DHCP (192.168.112.0/22 network).

We have static IPs for servers and DHCP for Basic AG Listeners.

Everything worked fine, however Listeners for AGs created on new (fully updated) Windows 2019 servers fail to get an IP address from DHCP.

I've checked our DHCP on new servers and it works perfect, server itself does obtain IP from our DHCP which means that DHCP is working fine.
I've tried to create a Listener with Static IP on new Windows Server 2019 and it works good, which means that AD permissions on OU for cluster computer account are okay (double checked it too).
I've tried to create an AG with a Listener on existing Server (Windows 2016) and Listener created there successfully obtains an address from DHCP, which means that our DHCP pool is not exhausted.
Actually there is one thing different, our new servers are connected to a freshly set up VLT-stack of Dell S4148F-ON switches, however as far as it has all VLANs configured and DHCP is working normally (see #1) I don't see any reason to blame the network.
When I capture traffic on physical interface in Wireshark on 2019 machine I see a number of broadcasts from DHCP server (Dell N3000 core switch) containing what I assume is a leased client address: Your (client) IP address: 192.168.113.143.

But it looks like the server does not accept them (?), the error I'm receiving is: Cannot bring the Windows Server FailoverClustering (WSFC) resource (ID ...) online (Error code 5942). <...>

In System Event Log I see two instances (based on two attempts, according to Wireshark) of Event ID 1240: Cluster IP address resource 'db11_192.168.112.0' failed to obtain a leased IP address.

And I've found nothing of interest in Failover Cluster Event Logs, just a detailed log of resource transitioning to failed state.

Maybe I'm missing something, but I don't see any other difference between old and new servers except new ones run Windows Server 2019.

Would be happy if someone suggests any further diagnostics since I've run out of ideas, thanks in advance.

Just wanted to confirm that the trouble was Server 2019 - after we reinstalled to Server 2016 everything works fine.


Sybase ASE doing showplan for all requests We have a strange situation where we are seeing showplan information in packets returned from a Sybase ASE 12.5 database server to a JDBC client. ie using tcpdump we see showplan output being sent back. We are trying to avoid this as the database is performing badly and it can't help having to generate detailed plan information.

We can't see any configuration settings client-side which would make this the case - is there some setting server-side which forces this option to be on?

SET SHOWPLAN [ON|OFF] is a session-level command in Sybase ASE, it's not a server-level setting (if it was a server-level setting you'd be able to alter it via sp_configure). Can you run wireshark (or something similar) on the packets being sent from the JDBC client to see if it's setting showplan on as part of the session initialization?

That said - showing the plan should not affect database CPU or memory usage, the plan is generated by the query optimiser anyway. However, showing the plan will increase network utilization.


When was second log file created for database? Is it possible to see when a second log file was created for a database and by who?  I have found an additional log file that was created for one of our databases in our environment and I'm trying to see who and when it was created.  We are using SQL server 2012 SP2 Enterprise edition.

I'm trying to find out who and when the second ldf file was created for a database.

This will query the default trace for any changes to the database, which will include the addition of files (but it will also include other things that you can't differentiate, like changing a database's compatibility level). It will also only include data that is currently in the default trace files (they age away; see this answer for details, but basically, how far back the history goes depends largely on how many trace events are captured), and requires that the default trace has not been disabled.

With all those caveats out of the way, this may at least narrow down the set of logins that have made changes to your database recently, and then you can ask them directly (assuming you can map logins to humans):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.trace WHERE is_default = 1;

SELECT LoginName, NTUserName, HostName, StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 164                 -- Object:Altered
  AND DatabaseName = N'my_database_name' -- ***CHANGE THIS***
  AND ObjectID IS NULL                   -- not an object
  AND ObjectType = 16964                 -- database
  AND EventSubClass = 1                  -- only need one of the row pair
  ORDER BY StartTime DESC;


Creating a MySQL Trigger I want to create a trigger such that whenever a user is inserted in
TABLE A(userid,type) then his "userid" along with some predefined value, depending in his "type", is inserted in TABLE B(userid,value)

For example in TABLE A if following value is inserted:

userid  |  Type

23      |  admin

then, depending on condition if Type=admin value=1 OR if Type=user value=2, the following insertions are made:

TABLE B
userid  |  Value

23      |  1

Is this possible?

Try

CREATE TRIGGER tg_ai_tablea
AFTER INSERT ON tablea
FOR EACH ROW
  INSERT INTO tableb (userid, value)
  VALUES (NEW.userid, CASE NEW.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END);

Here is SQLFiddle demo

You can also consider instead of storing value just evaluating it on the fly

SELECT b.userid,
       CASE a.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END value
       -- , other columns from tableb
  FROM tableb b JOIN tablea a
    ON b.userid = a.user.id;


Re-writing a sub-select as a condition on a column generated with 'over (partition by ...)' I am wondering if it would be possible to use over (partition by...) clause in order to avoid using a sub-select in the example below:

declare @t table (
     id         int
    ,code       char(2)
    ,descriptor int
)

insert into @t
      select 1, 'a1', 10
union select 1, 'a1', 20
union select 1, 'a1', 30
union select 2, 'b1', 10
union select 2, 'b1', 20
union select 2, 'b1', 30
union select 2, 'b2', 10
union select 2, 'b2', 20
union select 2, 'b2', 30
union select 3, 'c4', 10
union select 3, 'c4', 20
union select 3, 'c4', 30
union select 3, 'c7', 10
union select 3, 'c7', 20
union select 3, 'c7', 30

select *
from @t as t
where code = (select min(code) from @t where id = t.id)

I want to see only the records with the code equal to a1, b1 and c4. Can over (partition by...) assign 1 to all those and 2 to b2 and c7 codes, etc, so that at the end I could say where row_number = 1 instead of using a sub-query?

Yes but not with row_number(). You can use either the window aggregate min():

select id, code, descriptor
from
  ( select id, code, descriptor,
           min_code = min(code) over (partition by id)
    from @t
  ) as t
where code = min_code ;

Or the rank() window function (or the dense_rank() one, they'll both work the same for the rnk = 1 check):

select id, code, descriptor
from
  ( select id, code, descriptor,
           rnk = rank() over (partition by id order by code)
    from @t
  ) as t
where rnk = 1 ;

I was about to comment that there is no way to do this without subqueries of any kind (correlated, derived tables, ctes) but alas, there is. Not in 2008R2 but in 2012 version using one more window function.

Note: Do not use this. The efficiency will probably be the worse. The following query might be good for an obfuscation contest but not for production.

Instead, check the nice execution plan of the above 2nd query, when there is a suitable index.

Just for fun - and horror of the future maintainer of the code:

select distinct
    id,
    code = min(code) over (partition by id),
    descriptor =
        case when code = min(code) over (partition by id)
            then descriptor
            else first_value(descriptor) over
                    (partition by id order by code, descriptor)
        end
from @t ;

Tested at SQLFiddle


Cannot connect to SQL Server after setting sa password to blank I have installed SQL Server 2008 R2 on a Windows 2008 server. I need to set the sa password to blank for some reasons to make an application works; but it seems like the password changes automatically some hours later.

I log in to SQL Server with windows authentication and set sa password to blank and I am able to log into sql with sa and blank password, but another day when I try to log in with sa and blank password and the error is:

  no process is on the other end of the pipe error 233

This error does not sound like a problem with the password at all, but rather a connectivity issue. It could be that some policy is re-establishing firewall rules, or it could be simply that you have flaky DNS, an unreliable connection to the domain controller, or some other network issue.

As @Thomas pointed out, the "pipe" in the error message implies that this is a named pipes error. In most cases I have tended to avoid named pipes altogether. On the server where the SQL Server instance resides, you should go to SQL Server Configuration Manager and experiment with disabling named pipes and making sure TCP/IP is enabled:

Then you'll want to make sure that your app's connection string uses an IP address if feasible or edit the hosts file so that the name resolves to an IP locally, instead of using a physical machine name or FQDN. Doing either of these eliminates your dependency on external name resolution from a domain controller (but obviously won't be a good idea if you are using DHCP for some reason).

Of course you may also want to review network logs (or set up some kind of network monitor) to determine if there is some issue outside of this, and make sure that neither machine has a firewall enabled (or at least ensure that it is open on the right ports and no group policy is coming in and "fixing" that). But these changes will be better for your environment even if they don't solve this immediate issue.

As an aside, you absolutely must fix that application's connection string anyway. A blank sa password is like leaving your wallet at the bus station on purpose.


Postgres not using indexes when ORing across join I'm doing a query with a join on two tables, where I need to do an ILIKE filter on a field in each of the tables with wildcards at both ends, and it should return a row if either of them match.

Approx:

SELECT * FROM "tableA" LEFT OUTER JOIN "tableB" ON
"tableA"."tableB_id" = "tableB"."id"
WHERE "tableA"."name" ILIKE '%rich%' OR "tableB"."name" ILIKE '%rich%';

As both of these tables are quite large I decided to try using trigram indexes to speed things up. However, this didn't appear to help, and having done an EXPLAIN ANALYZE I discovered that the indexes are not used. They are used if the filters are combined with AND instead of OR, and they are used if I do an OR between two indexed columns in the same table, just not with an OR between indexed columns in different tables.

Anyone have any ideas why?

It's sometimes useful to rewrite a query with OR conditions as a UNION (ALL) query. This usually leads to a different execution plan which may be more efficient. Testing will identify which of the two methods/plans is more efficient in each case (eg. if the trigram indexes are used for both parts of the union).

The rewrite:

SELECT *
FROM "tableA" AS a
    LEFT OUTER JOIN "tableB" AS b
    ON a."tableB_id" = b."id"
WHERE a."name" ILIKE '%rich%'

UNION ALL

SELECT *
FROM "tableA" AS a
    INNER JOIN "tableB" AS b
    ON a."tableB_id" = b."id"
WHERE b."name" ILIKE '%rich%'
  AND (a."name" IS NULL OR a."name" NOT ILIKE '%rich%')
 ;


How should I shift values to one place back in SqLite? I have a SqLite DB in which i have 16 columns as id, and f1 to f15.
Now Mistakenly I entered data from 2 column,  leaving the first one empty. Now my all values are one place ahead i.e value of last column of first row is in 1st column of 2nd row.
Now I wish to correct this so that all values at their right place . Any query for this problem?

Assuming your ID column is correct and sequential, you can join the table to itself on the ID column = ID column + 1.
Psuedo-Example:
SELECT T1.id, T1.f2 AS f1, T1.f3 AS f2, etc, T2.f1 AS f15
FROM YourTable AS T1
INNER JOIN YourTable AS T2
ON T1.id = T2.id + 1

Then with that dataset you can put it in a CTE (https://sqlite.org/lang_with.html) and then do an update statement on your table. I.e. you can update YourTable with the values from the CTE, e.g. SET YourTable.f15 = CTE.f15
Psuedo-Example:
UPDATE YourTable
SET f1 = (SELECT f1
              FROM YourCte
              WHERE YourTable.id = YourCte.id)
 WHERE EXISTS (SELECT id
                  FROM YourCte
                  WHERE YourTable.id = YourCte.id)

Just fill in the rest of the columns in the SELECT of the CTE, and the UPDATE statement.
Your last record in the table will need to be manually fixed assuming it's last value is just missing.


Inheritance and its alternatives in PostgreSQL I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it:

1) to violate third normal form

It is the dirtiest and probably the worst solution. I could check potencial duplicities by triggers, but I suspect it would be the hardest way, for there are quite a lot of type specific columns.

2) inheritance

This is better and might be good enough, but my "finds" table should have a foreign key to the "context" table regardles of its type. According to the manual this would cause problems. With all data in tables "layer", "cut", "structure" etc., would the foreign key to "context" table really point to the data stored in the inherited tables? If there's any caveat, is there some (relatively) easy workaround?

3) some sort of 1:1 relation

The "finds" table would be connected to "context" table, and tables for various types of context would have a foreign key pointing to the "context" table. But how to assure that exactly one row in all the dependent tables corresponds to each row in "context"?

4) ???

There might be some other solution which I didn't know about.

Which of these is easiest to implement (including avoiding duplicity)? And which is best in the terms of performance?
The database will never be really big, so slightly suboptimal performance is no problem, but big differences in performance might be more important than easiness to write it.

I'd be glad for answers telling me which option I should use, why, and especially: how to overcome its drawbacks (or that I missed something and there's no real drawback).

It is hard to answer the question as written.  The simplest solution would probably be something like a context table with types listed as they are.  What I am not sure about is what you need in terms of linking to this table.  In other words, have all the common fields for context in the context table plus a type field that tells you what kind of context it is.  Other tables can then join that to provide more information about the types.

The problems you mention with the 1:1 design can be solved with composite foreign and primary keys.  It may seem like field duplication but keep in mind your primary key is multi-field so your foreign keys would need to be also.  This allows you to ensure that different dependent tables do not overlap where you do not want them to do so.

The second solution is to use inheritance.  In my view, inheritance is going to be a good solution if the following are true:

The context records have foreign keys that reference other tables depending on subtype, and
No tables reference context as such but rather the subtype directly.

My guess is that in your case the second condition will not be met and therefore I would not recommend table inheritance for you.


Cannot connect with OLE DB OLE DB connection...

Can anyone suggest me on this.

Authentication with windows security works in 2012-2005

  "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;
      Initial Catalog=TESTDB;Isolation Level = ReadUncommitted;Data Source=SERVER"

In 2012 this gives me an error in 2012

  Provider=SQLNCLI11;Persist Security Info=False;Initial Catalog=TESTDB;Data Source=SERVER;Language=us_english;User Id=AA;Password=SSS

error conn.open
Property access must assign to the property or use its value.
Sql client not registered

The answer is right there on the last line of the error message:

  Sql client not registered

You need to install the SQL Server Client Library on the machine in question.

You can obtain that package from http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

Also, Isolation Level = ReadUncommitted may be problematic if you don't fully understand the implications of ReadUncommitted See http://msdn.microsoft.com/en-us/library/ms173763.aspx


MySQL `where...in` statement - does it remove duplicates? I'm curious about if MySQL's where ... in (...) statement removes duplicates as an optimization.
For example, if I use a subquery, is it important that I use DISTINCT to remove duplicates?
An example query:
SELECT * FROM foo WHERE bar_id IN (
    SELECT id FROM bar where user_id = 4
);

The subquery has the potential to return duplicate bar.id's.
Does MySQL query optimization make this query better?
SELECT * FROM foo WHERE bar_id IN (
    SELECT DISTINCT id FROM bar where user_id = 4
);

From my tests on a database with about 2.5mm rows in the bar table (with indexes where appropriate for such a query), the result time is roughly the same (on a large RDS instance with plenty of overhead for now).

I should note that I'm hoping for an explanation more than "the subquery returns less results when using DISTINCT, so of course it's better", as that ignores MySQL's query optimizer.
For example, perhaps DISTINCT uses more resources and is therefore slower overall, especially if a where...in(...) statement easily optimizes duplicates. These are the details I'm unsure about.

Assuming that user_id is not a PRIMARY KEY, then MySQL will need to de-duplicate the subquery to apply the correct semantics. The DISTINCT keyword should not be required here since it doesn't change the semantics.

MySQL actually has more than one strategy for how to remove duplicates: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html

To see the strategy used, you need to paste the output from EXPLAIN FORMAT=JSON (it does not appear in the regular tabular EXPLAIN). You will see something like:

     "transformation": {
                   "select#": 2,
                   "from": "IN (SELECT)",
                   "to": "semijoin",
                   "chosen": true
                 }


The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions I want ascending wise data CID wise
    select f.Category,f.Name,(f.Bigbale_QTY-f.Dispatch_QTY) as [Balance],(f.Bigbale_weight-f.Dispatch_Weight) as [W_Balance] from(
select e.Category,e.Name,isnull(min(e.[Bigbale_QTY]),0) as [Bigbale_QTY],isnull(min(e.[Bigbale_weight]),0) as [Bigbale_weight],isnull(sum(c.QTY),0) as [Dispatch_QTY],isnull(sum(c.Bweight),0) as [Dispatch_Weight] from (
select ca.CName as Category,a.Descriptionitem as Name,min(a.CodeItem) as CodeItem,isnull(sum(b.Bpqty),0) as [Bigbale_QTY],isnull(sum(b.Bweight),0) as [Bigbale_Weight]
from ItemMasterFile a
inner join Catagory ca on ca.CID=a.CID
left join Bigbalprd b on a.CodeItem=b.CodeItem
 where a.Packsize ='bigbale' and b.delID is null  and (b.trans is null or b.Trans='b')
  group by a.Descriptionitem, ca.CName
  ORDER BY CA.CID ASC)e
left join Dispatch_BD c on e.CodeItem=c.CodeItem
 where c.Delidd is null
group by e.Name,e.Category

)f

I tried below but error is coming (  ORDER BY CA.CID ASC)
Msg 1033, Level 15, State 1, Line 9
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

This is an error and No, you can't use ORDER BY to sort rows in the derived table for the reason given in the error message. The order of rows in the result set is ultimately controlled by an ORDER BY clause in the outer SELECT, not by the OVER clause. The OVER clause "specifies the logical order in which the window functioncalculation is performed". It doesn't sort the result set. There are workarounds for this issue though and you can check solution proposed at https://stackoverflow.com/questions/18031421/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu either by using ROW_NUMBER function or ORDER BY column OFFSET 0 ROWS.
Alternative method is explained by Mr. Pinal Dave at this link.
Hope this helps.


Enforcing database integrity Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc?

How much of a performance improvement one can expect for not enforcing database integrity through internal database tools?

Truth be told, not only will you not see much performance loss from having foreign key constraints in the database, but you will see performance enhancements. The SQL Server query optimizer is built around the concept of primary and foriegn keys as well as other types of data constraints. If these are in place and enforced the optimizer can take advantage of them to get you better performance. Here's a blog post with a simple example that shows it in action.

If you are in an edge case where you truly have more inserts than reads (and updates & deletes require reads, so they usually end up adding to the read count), then it might make sense to remove constraints from the data for performance, maybe. But since the overwhelming majority of databases are read oriented, you're sacrificing performance, not enhancing it.

And none of this mentions the fact that data integrity is better handled at the database since you only have to create it once where as if you do all the work in code, you may have to do it multiple times for multiple apps (unless you design your data access layer carefully and require every app access the db to go through that same layer).

If you're using a relational database system, I say, why not really use it. If you don't need relational data, go with Hadoop or something else.