Removing all columns with given name I am converting a MSSQL schema to PostgreSQL and in that schema most tables have a column called Timestamp that are of MSSQL timestamp datatype which is effectively rowversion.

When inserting records into those tables you do not need to specify values for timestamp columns as MSSQL auto updates that column.  I believe this is the same as xmin in PG.

So now when my app tries to insert into the table PG blows up saying it needs a value for the Timestamp column.

I'm therefore thinking after importing the schema writing something that drops all columns from my tables that are called Timestamp and have timestamp datatype.

I have found I can get column and table info from pgclass and pgattribute however I"m now stuck how to loop and do checks and then drop columns. Could someone point me in the right direction please?

select
    t.relname,
    a.attname,
    d.typname
from
  pg_class t
  INNER JOIN pg_attribute a
  on a.attrelid = t.oid
  INNER JOIN pg_type d
  on d.oid = a.atttypid
  where relkind='r' and attname = 'timestamp' and d.typname = 'timestamp'
ORDER BY t.relname

For a one-time use, you don't need to persist a function. Use a DO statement:

DO
$do$
DECLARE
   rec record;
BEGIN
FOR rec IN
   SELECT a.attrelid::regclass::text AS tbl, a.attname
   FROM   pg_class c
   JOIN   pg_attribute a ON a.attrelid = c.oid
   WHERE  c.relkind = 'r'
   AND    a.attname = 'timestamp'
   AND    a.atttypid = 'timestamp'::regtype
   AND    a.attnum > 0
   AND    NOT a.attisdropped
LOOP
   RAISE NOTICE '%', format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
   -- Check test output before uncommenting EXECUTE!
   -- EXECUTE format('ALTER TABLE %s DROP COLUMN %I', rec.tbl, rec.attname);
END LOOP;
END
$do$;

Major points

As mentioned at the top, probably no need for a function. The body of a DO statement is identical, default language is plpgsql.
Use the implicit cursor of a FOR loop. Simpler and faster. Explicit cursors are rarely necessary in plpgsql.
Table names are not unique in a Postgres database. There can be any number of tables with the same name in multiple schemas.
Avoid SQL injection and basic exceptions with non-standard (double quoted) identifiers.

A cast to regclass takes care of the table name. At the same time, table names are schema-qualified automatically where necessary (taking the current search_path into account).
format() with %I sanitizes the column names.

Details:

SQL injection in Postgres functions vs prepared queries

I assume you are aware of timestamp and timstamptz data types? This only removes columns with data type timestamp [without time zone].
Identify the data type with a.atttypid = 'timestamp'::regtype, so you don't have to join to pg_type. Simpler.
Exclude system columns and dead columns:
AND    a.attnum > 0
AND    NOT a.attisdroppedand


SQL SSIS Internal ETL at Company Business Departments at our Finance company want IT to import various spreadsheets (companyid, productid, balance, transactions) into the databases.

Lets say, the SQL physical server harddrive contains G:\ [SQLFinance].

When business departments want to drop excel or flat files,

(1) Should we have them place excel/flat files in G:\ SQL harddrive or,

(2) Should we have them place files Not in SQL harddrives, but in another share drive Outside the SQL Server box? Then we copy files over with Powershell/Commandprompt/Robocopy (whatever) into our SQL Server harddrives? From there, we can apply SSIS to transform the excel-flat files into the sql tables.

Are there any benefits/disadvantages to each approach?

Your proposed project sounds fairly decent. However, the more hands involved the greater complexity security is.

If this was my request, I would ask some of these questions:

Is the data stored currently on multiple data centers or centrally?
Are these sources in the same domain? Or do we need multiple SSL’s for our ETL?
If we choose to rely on external sources to transport this data to a local/network drive, how secure and consistent is this system?
Can the system of question 3 be integrated into SQL Server or another software to copy or pull the data?

Why Network drives are best

For one, durability of the directories is independent of a SAN, disk, or single blade, so the data remains available even during outages.

Because they are on a single mount point, where each drive is additionally mounted, your security is simplified dramatically.
You could give the files to SQL Server by using a single account that only SSIS has privy to, or a Domain Account that your AD manages.

But really, you should have an ETL process developed

You ensure the validity and security of your documents as you go from SOURCE to TARGET.
the process is encapsulated away so that additional sources is a mere plug ‘n play.

Proper ETL involves 3 stages:

Extraction (your primary concern)

this involved programmatic pulling and staging of your source data.
Unless it is absolutely impossible, this process should use as little manual effort as possible.
If you find yourself with a lot of manual effort in this stage, often the data source may not be clean enough for your purposes.

Transformation (inside the database/SSIS package)

This involves all processes that clean and ready the data for loading onto the final target tables.
cleanup usually follows this: Removing immediately bad data rows.  Separating multifaceted values into their own column. Performing transformation steps that changes these values to a pre-final step. Last combinations of said columns that create the final tables OR allow your final loading to be collections of these values in a organized fashion (such as collecting from each sporting Team website details of their teams/history and then aggregating this in a final table)

Loading.  The final, cleaned, and final form for your data.


Storing Tlogs and data files on the same drive I'm looking for some clarification of various things I've read.  I understand it to be best practice to store your transaction logs on a separate drive (spindle) than where you MDFs and NDFs are housed.

Does this same principal hold true in a virtualized environment on a SAN?

It's a good idea to keep them separated, even if the drives are currently backed by the same storage device. It buys a lot of future flexibility, at a slight expense in management overhead for both the DBAs and the Windows/storage admins.

If you want to change the physical storage that backs either of the drives in the future, you won't have to go through all the databases and move the files at that point -- they'll already be in the right spot. Simply present different storage to the VM with the same drive letter, and assuming all the files and permissions are there, you're done. Simple.
If you collect Performance Monitor (PerfMon) physical disk stats, it's a good idea to record data file activity separately from log file activity. If that's even possible with all the files on the same drive, it probably won't be very easy.

Really I can't think of any significant downsides. There is the possibility that if you want to use a storage-level snapshot technology in the future, the SAN may not support having the files in two different physical locations. Consult your SAN admins or vendor on this; it may not be applicable now necessarily, but might come into play in the future. This isn't a negative, but it may play into the setup strategy as a constraint/requirement.

Aside from that, it's just a bit of extra management to keep things in the right spot, and you have to make sure that any automation scripts you have also respects the difference in location by the type of file. But the latter is something you should be doing anyway when writing scripts, so I don't see that as a negative.

It may be prudent to separate tempdb specifically into another location as well.


How to Optimise Query I have a database structure similar to this,

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED
(
    [DispatchId] ASC,
    [ContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

The point of the DispatchLink table is to link two Dispatch records together. By the way I am using a composite primary key on my dispatch table because of legacy, so I cannot change that without a lot of pain. Also the link table may not be the correct way to do it? But again legacy.

So my question, if I run this query

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

I can never get it to do an index seek on the DispatchLink table. It always does a full index scan. That is fine with a few records, but when you have 50000 in that table it scans 50000 records in the index according to the query plan. It is because there are 'ands' and 'ors' in the join clause, but I can't get my head around why SQL can't do a couple of index seeks instead, one for the left side of the 'or', and one for the right side of the 'or'.

I would like an explanation for this, not a suggestion to make the query faster unless that can be done without adjusting the query. The reason is that I am using the above query as a merge replication join filter, so I cannot just add in another type of query unfortunately.

UPDATE: For instance these are the types of indexes I have been adding,

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

So it uses the indexes, but does an index scan across the whole index, so 50000 records it scans 50000 records in the index.

The optimizer can consider many plan alternatives (including ones with multiple seeks) but for disjunctions (OR predicates) it does not consider plans involving index intersections by default.  Given the indexes:

CREATE CLUSTERED INDEX cx
ON dbo.DispatchLink (DispatchLink1, ContractLink1);

CREATE NONCLUSTERED INDEX nc1
ON dbo.DispatchLink (DispatchLink2, ContractLink2);

We can force index seeks (assuming SQL Server 2008 or later):

SELECT *
FROM dbo.Dispatch AS d
INNER JOIN dbo.DispatchLink AS dl WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

Using your sample data, the seek plan costs at 0.0332551 units compared with 0.0068057 for the scan plan:

There are all sorts of possible query rewrites and hints we can try.  One example of a rewrite to promote an option the optimizer does not consider for the original plan is:

SELECT *
FROM dbo.Dispatch AS d
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.DispatchLink AS dl
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;

This execution plan does not seek the second index if it finds a match on the first:

This may perform very slightly better than the default FORCESEEK plan.

Without adding any new indexes, we can also force a seek into the Dispatch table:

SELECT *
FROM dbo.DispatchLink AS dl
JOIN dbo.Dispatch AS d WITH (FORCESEEK) ON
    (d.DispatchId = dl.DispatchLink1 AND d.ContractId = dl.ContractLink1)
    OR (d.DispatchId = dl.DispatchLink2 AND d.ContractId = dl.ContractLink2);

This may be better or worse than the first example depending on things like how many rows are in each of the tables.  The APPLY + TOP improvement is still possible:

SELECT *
FROM dbo.DispatchLink AS dl
CROSS APPLY
(
    SELECT TOP (1) * FROM
    (
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink1 = d.DispatchId
        AND dl.ContractLink1 = d.ContractId
        UNION ALL
        SELECT * FROM dbo.Dispatch AS d
        WHERE dl.DispatchLink2 = d.DispatchId
        AND dl.ContractLink2 = d.ContractId
    ) SQ1
) AS F1;


PostgreSQL JSONB update array elements query Given a table in PostgreSQL 10:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES (
  1, '"id":"a uuid_v1 here",
      "widgets": [ {"id":"1", "permissions": ["user1", "user2"]},
                   {"id":"2", "permissions": ["user1", "user3"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"id":"a uuid_v1 here",
      "widgets": [ {"id":"1", "permissions": ["user1", "user2"] },
                   {"id":"2", "permissions": ["user3", "user4"]}]');

I need to remove a user, say "user1" from all assigned permissions in any widget. I also need to add a user, say "user5" to all assigned permissions in any widget.

This is not a trivial task.

Assuming the two nested JSON arrays reliably exist in every row, this works:

UPDATE forms f
SET    form = u.form1
FROM  (
   SELECT f.id, f.form || jsonb_build_object('widgets', jsonb_agg(w.widget1)) AS form1
   FROM   forms f
   CROSS  JOIN LATERAL (
      SELECT w.widget || p.permissions AS widget1
      FROM   jsonb_array_elements(f.form ->'widgets') w(widget)
      LEFT   JOIN LATERAL (
         SELECT jsonb_build_object('permissions', jsonb_agg(p1.permission)) AS permissions
         FROM  (
            SELECT *
            FROM   jsonb_array_elements(w.widget->'permissions') p(permission)
            WHERE  p.permission <> jsonb '"user1"'       -- delete user1
            UNION
            SELECT jsonb '"user5"'                       -- add user5
            ) p1
         ) p ON true
      ) w
   GROUP  BY 1
   ) u
WHERE  u.id = f.id
AND    u.form1 IS DISTINCT FROM f.form;

But it is complex and expensive. I'd rather not use it. Instead, I would aim to normalize the DB schema if at all possible.

db<>fiddle here - with intermediate steps to help understanding.

There is too much to explain here ...

Most importantly:

It works even with empty permissions arrays because we add at least one user. Else we would get NULL instead of an empty array, and might need to do more.
The manual about the concatenation operator ||:

  Note

  The || operator concatenates the elements at the top level of each
  of its operands. It does not operate recursively. For example, if both
  operands are objects with a common key field name, the value of the
  field in the result will just be the value from the right hand
  operand.

Avoid updating rows where nothing changes with the last line added to the WHERE clause. See:

How do I (or can I) SELECT DISTINCT on multiple columns?

While you only delete a user and most rows don't have that user to begin with, we could make it much faster with a condition:

WHERE  f.form @> '{"widgets": [{"permissions":["user1"]}]}'

Checks whether any permissions array has a "user1" element, and can use a matching index. If the table is big you'll want a matching index. See:

Find rows containing a key in a JSONB array of records

To identify rows where not all permissions arrays have "user5", the check is not as simple, and index support even less. Though still possible.


SQL Server job timeout So, it seems that it is not possible to set the timeout for each single step in a SQL Server job (article from StackExchange).

MSDN points out that you can of course set the Agent timeout:

Sql Server Agent properties.
Set Job execution shutdown.

Questions

How does Agent time-out work?
Is there any other way (preferably by configuration) to setup the single step timeout?
Is there any other way (preferably by configuration) to setup the whole job timeout?

(1) is referring to the service itself. If I go stop the SQL Server Agent service this value specifies how long it will wait for any active job to complete before forcible stopping it (the service) so it can shutdown.

With regards to (2) and (3), you would have to setup some monitoring script/job that tracks that information and performs an action based on your criteria.


What is the JDBC URL for Oracle's Data Guard Fast-Start Failover? Currently I have...

url=
"
  jdbc:oracle:thin:@
    (
    DESCRIPTION=
      (
      ADDRESS_LIST=
        (LOAD_BALANCE=OFF)
        (FAILOVER=ON)
        (
        ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.0.0.6)
          (PORT=1521)
        )
        (
        ADDRESS=
          (PROTOCOL=TCP)
          (HOST=10.0.0.7)
          (PORT=1521)
        )
      )
      (
      CONNECT_DATA=
        (SERVICE_NAME=mySID.nowhere.org)
        (SERVER=DEDICATED)
      )
     )
"

You don't setup Fast-Start Failover in the URL.  It's something that you setup on the data source using the setFastConnectionFailoverEnabled(true) function call.

Per here

  An application enables Fast Connection Failover by calling
  setFastConnectionFailoverEnabled(true) on a DataSource instance, before
  retrieving any connections from that instance.

  You cannot enable Fast Connection Failover when reinitializing a connection
  cache. You must enable it before using the OracleDataSource instance.

For example

    ods = new OracleDataSource();
    ...
    ods.setFastConnectionFailoverEnabled(true);

Also, it mentions:

  FAST CONNECTION FAILOVER PREREQUISITES

  1.) The implicit connection cache is enabled. Fast Connection Failover
  works in conjunction with the JDBC connection caching mechanism.
  This helps applications manage connections to ensure high
  availability.

  2.) The application uses service names to connect to the database; the
  application cannot use service IDs (ORACLE_SID). Use a service name
  rather than an SID when setting the OracleDataSource url property.

  3.) The underlying database has Release 10 (10.1) or Release 10 (10.2)
  Real Application Clusters (RAC) capability. If failover events are
  not propagated, connection failover cannot occur.

  4.) Oracle Notification Service (ONS) is configured and available on the
  node where JDBC is running and on the Oracle RAC. JDBC depends on
  ONS to propagate database events from RAC and notify JDBC of them.
  If ONS is not correctly set up, then implicit connection cache
  creation fails and an ONSException is thrown at the first
  getConnection request.

  5.) The JVM in which your JDBC instance is running must have
  oracle.ons.oraclehome set to point to your ORACLE_HOME. For example:

  -Doracle.ons.oraclehome=C:\oracle\product\10.2.0\db_1

  6.) Set the FastConnectionFailoverEnabled property before making the
  first getConnection request to an OracleDataSource. When Fast
  Connection Failover is enabled, the failover applies to all
  connections in the connection cache. If your application explicitly
  creates a connection cache using the Connection Cache Manager, then
  you must first set FastConnectionFailoverEnabled before retrieving
     any connections.

  7.) The ons.jar must be part of the CLASSPATH for the application.
  The ons.jar can be found in the Oracle Client installation.

Edit:
One last thought, if there ever is a failover, you will need to handle the SQLException:

  ORA-17008: Closed Connection


Multiple roundtrips vs cross joins I have a feeling the answer is "it depends on...", but I'm wondering if there's a concrete answer.
In Entity Framework, you build a query using C# code, and the framework converts to SQL and sends to the server to pull down the data. Suppose I want to retrieve a record from three tables. I've got at least three choices:

Use straight SQL (manual ADO.NET), send three SELECT statements in the same command, and map the results one resultset at a time with a DataReader. This is clearly the best approach from the database perspective, but it's the most work since I wouldn't be able to use the Entity Framework approach.

Send three separate commands from Entity Framework - this requires three roundtrips to the database server:

Person person1 = context.Persons.First(p => p.PersonID == 1);
Car car1 = context.Cars.First(c => c.CarID == 1);
House house1 = context.Houses.First(h => h.HouseID == 1);
// translates to the following SQL, one roundtrip at a time:
SELECT TOP(1) [p].[PersonID], [p].[PersonName] FROM [Person] AS [p] WHERE [p].[PersonID] = 1
SELECT TOP(1) [c].[CarID], [c].[CarName] FROM [Car] AS [c] WHERE [c].[CarID] = 1
SELECT TOP(1) [h].[HouseID], [h].[HouseName] FROM [House] AS [h] WHERE [h].[HouseID] = 1

Make one big ugly statement which still uses Entity Framework with a single command and a single statement:

var query = (from p in context.Persons.Where(p => p.PersonID == 1)
             from c in context.Cars.Where(c => c.CarID == 1)
             from h in context.Houses.Where(h => h.HouseID == 1)
             select new { p, c, h }).First();
Person person2 = query.p;
Car car2 = query.c;
House house2 = query.h;
// which makes the following SQL:
SELECT TOP(1) [p].[PersonID], [p].[PersonName], [t].[CarID], [t].[CarName], [t0].[HouseID], [t0].[HouseName]
FROM [Person] AS [p]
CROSS JOIN (
    SELECT [c].[CarID], [c].[CarName] FROM [Car] AS [c] WHERE [c].[CarID] = 1
) AS [t]
CROSS JOIN (
    SELECT [h].[HouseID], [h].[HouseName] FROM [House] AS [h] WHERE [h].[HouseID] = 1
) AS [t0]
WHERE [p].[PersonID] = 1

The cross joins end up just flattening out the result since there's only one record returned per table, and the right indexes fire, so I would think the database would be happy with it - fewer roundtrips but the same amount of data returned.
The code in #3 is uglier, but that's not what I'm asking - I'm just asking from a performance perspective. Do the cross joins in the third example cause any potential issues? Maybe locking problems or extra CPU or memory that wouldn't otherwise be needed by using individual commands?

It's worth mentioning up front that those two queries might not return the same results.  Three separate queries by ID will always return your three rows.  In the CROSS JOIN approach, if PersonID = 1 doesn't exist, the information about the other two entities won't be returned.  So your application has to be certain that all of these IDs exist, or you might get unexpected results.

As long as your tables each have a unique clustered index (or primary key) on the ID column (or a covering nonclustered index - the point is a seek to the correct row is possible), the performance difference between the two approaches, from a SQL Server perspective, is negligible.
Both approaches will result in 3 seeks against the clustered index.  Neither should go parallel, or require sorting, and the cross join should use nested loops - so no memory grants would be needed in either case.
The three batches approach is pretty obvious:

The CROSS JOIN approach looks like this:

The way this plan operates, each seek operator executes, at most, once:

The seek on PK_House returns one row to the Nested Loops operator
This triggers execution of the seek on PK_Person, which returns one row to the same Nested Loops operator
This "flattened" row is passed to the next Nested Loops operator...
...which triggers execution of the seek on PK_Car, returning one row to that second Nested Loops operator
This single flattened row is returned to the top operator, which satisfies its request for one row, so no more rows are requested

If you want to not write cross joins all over your app (potentially confusing to other developers), you can avoid the overhead of multiple roundtrips by using one of the Entity Framework extensions that provides "future" queries.  These let you queue up multiple queries, and they will all be sent and resolved in one batch when the result of any of them is needed.
One such extension is Entity Framework Plus - query-future.


System.OutOfMemoryException SQL Server 2012 and 2008R2 I have a 64-bit and 64gb memory for both SQL Server 2012 and 2008R2, when I generate a report w/c has 900K rows, it finished the query smoothly and no problem, but when I'm going to copy the result with header. The error System.OutOfMemoryException is prompting. What do you think causes this problem?

The most likely problem is a memory limitation on the machine running Management studio. We have this problem some times when the client laptop only has 2GB-4GB of RAM. You could use the Export data function in Management Studio or the SQL Server "bcp Utility"

SQL Server Export Wizard: Right-Click on the Database > Tasks > Export Data

Bulk copy utility (bcp): Below is the sample syntax that we use and also links to the Microsoft documentation, note that xp_cmdshell needs to be enabled.
Sample syntax (all one line):
exec master..xp_cmdshell 'bcp mydatabase.dbo.mytable out C:\Myfolder\MyFile.csv -c -t, -T -S ServerName\InstanceName'

Microsoft Link: https://msdn.microsoft.com/en-us/library/ms162802.aspx


PostgreSQL performance citext I'm trying to determine the performance characteristics of citext. In Microsoft SQL Server, the nvarchar(max) has performance hits when there is excessive memory grants required to use it versus something like a varchar(50). There are other performance hits that come in to play also. I'm trying to determine if citext in PostgreSQL has similar issues. We have a PostgreSQL backed web-app that is horribly slow and I'm wondering if the extensive use of the citext data type is contributing. Any thoughts on this? Thank you!

Case insensitive comparison is always costly than case sensitive one. The documentation also cites that in Limitations section. As I understood, the main problem is that you have a performance issue and you cannot locate the culprit(s). I suggest you to use pgBadger or pg_stat_statements to detect slow queries.


Error with pg_upgrade and PostGIS I have a PostgreSQL 9.2 setup (with Postgis) and am trying to upgrade it to 9.6.

When performing the pg_upgrade, I get the following error:

ERROR:  could not find function "geography_analyze" in file "/usr/lib/postgresql/9.6/lib/postgis-2.0"
STATEMENT:  CREATE FUNCTION "public"."geography_analyze"("internal") RETURNS boolean
        LANGUAGE "c" STRICT
        AS '$libdir/postgis-2.0', 'geography_analyze';

    -- For binary upgrade, handle extension membership the hard way
    ALTER EXTENSION "postgis" ADD FUNCTION "public"."geography_analyze"("internal");

I've tried to upgrade Postgis on my 9.2 clusters, but I get an error as well:

postgres=# ALTER EXTENSION postgis UPDATE;
ERROR:  cannot drop function "geometry_gist_sel_2d"("internal","oid","internal",integer) because other objects depend on it
DETAIL:  operator &&("geometry","geometry") depends on function "geometry_gist_sel_2d"("internal","oid","internal",integer)
operator class "gist_geometry_ops_2d" for access method gist depends on operator &&("geometry","geometry")
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

How should I proceed?

How should I proceed?

I would try to load the newer version of PostGIS fresh on a 9.6 before you load your dataset. If successful, I would edit the dataset to remove anything to do with PostGIS's internal functions. I would then load my dataset without those offending functions (make sure whatever you're loading does not have PostGIS internal stuff).

Also worth mentioning PostGIS 2.0 is EOL already and unsupported.


Recovery with Postgresql logical replication I'm using Postgresql 13 on Debian 10.6 and learning about logical replication.
I've set up logical replication with one publisher and one subscriber of one table. I'm wondering what my options are for recovering data (or rolling back) when, for example, someone accidentally does something on the publisher side like updating all the data in the table with the wrong value, or even deleting everything from a table. With logical replication these unintentional changes will of course be applied to the subscriber.
I've relentlessly searched online but have had no luck finding out what my options are.
I read about PITR but I'm thinking that's suited more for physical replication, whereas I want to test rolling back changes on a specific database on a server.

Logical replication doesn't have anything to offer you here.  PostgreSQL doesn't offer "AS OF" queries, but even if it did you could just do them in the master, rather than in the replica.
PITR is the thing you want.  It is its own thing, it is not particularly tied to physical replication.  About the only thing they have in common is that they both involve WAL files.  I think most modern implementations of physical replication would use replication slots, not a WAL archive, which means the WAL needed to do PITR is likely not even available unless you have gone out of your way to design specifically for PITR as well as for replication.
The logical replica records all changes into its own WAL stream, including changes driven by logical replication.  So if you keep a WAL archive and base backups of the replica, you could restore a copy of the replica and put it through PITR if you wanted to (but again, why not just do PITR on a copy of the master?  What does doing it on a copy of the replica instead earn you?)
There are way to do in-table or out-of-table logging of row changes, and these can be used to restore tables to a prior state.  But they don't have anything to do with the logical replication feature.


sp_BlitzWho saving to a table How to save result of sp_BlitzWho to a table? An option to save to table is not there compared to other procs like sp_Blitz.

You can declare table variable with fields fully equal to SP output and save results to it. E.g.:

    DECLARE @who_results TABLE
    ([run_date] DATETIME,
     ...
    )
    INSERT INTO @who_results EXEC sp_blitzwho

After that, you can do everything you want with data in table variable.


Proper use of database functions for business logic I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.

These functions are called by an application in java that has almost no business logic in the code.

This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.

This approach where most of the logic is in the database is correct?
If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To don’t delete or replace a change in client database by error?

Two queries - two replies:

a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):

natural decomposition of your application between data processing and data presentation layers
very well accessible API in heterogeneous environment - logic in database is accessible from shell scripts, all languages, ...
very efficient data processing due closeness data and processing unit (in PostgreSQL it is same process). It removes a network latency, conversions between protocols, layers, drivers - PL/pgSQL runs in same process as PostgreSQL core database engine and it use same data types.
you can create a API based on stored procedures, so any changes on database layer are transparent for you and for your application.

Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.

b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.


What is the significance of the principal_id in sys.schemas? What is the meaning of the principal_id in sys.schemas and when would it ever be different from schema_id?

1> SELECT LEFT(name,20), schema_id, principal_id FROM sys.schemas;
2> GO
                     schema_id   principal_id
-------------------- ----------- ------------
dbo                            1            1
guest                          2            2
INFORMATION_SCHEMA             3            3
sys                            4            4
db_owner                   16384        16384
db_accessadmin             16385        16385
db_securityadmin           16386        16386
db_ddladmin                16387        16387
db_backupoperator          16389        16389
db_datareader              16390        16390
db_datawriter              16391        16391
db_denydatareader          16392        16392
db_denydatawriter          16393        16393

(13 rows affected)

Internally, I see that the schema_id comes from sys.sysclsobjs while the principal_id comes from sys.syssingleobjrefs's r.indepid field.

It should be the default "owner" of all objects in the schema. Schema-bound objects have a principal_id column that is NULL by default, in which case it uses the principal_id of the Schema that the object is in.

The object owner is used for ownership chaining, and most likely also when determining if rights exist to perform operations that can be done by the object owner or a User in a the db_owner Database Role, such as TRUNCATE TABLE, SET IDENTITY_INSERT, etc.

The principal_id in sys.schemas can be any User if changed via ALTER AUTHORIZATION.

If using Schemas for logical separation of objects more than security separation, then it would make sense to use the same principal_id across multiple Schemas. For example, executing the following in the AdventureWorks2012 Database:

SELECT * FROM sys.schemas;

shows that the following Schemas are all owned by "dbo":

dbo
HumanResources
Person
Production
Purchasing
Sales

Having the same "owner" across Sales and Purchasing (and the others) allows ownership chaining to imply permissions of referenced objects within top-level objects. Meaning, if you execute a Stored Procedure in the Sales Schema, and it selects from a View in the Purchasing Schema, then permissions won't be re-checked and everything just works. But, if you then specify the principal_id of the View in the Purchasing Schema such that the column is no longer NULL (and not the same as the "dbo" User) then it will re-check permissions to see if the User executing the Stored Procedure has SELECT permissions on that View.


How to preserve the number of errors at the level of the package in SQL or .txt file? This is my package.

Does anyone know how to preserve the number of errors at the level of the package in SQL or .txt file? I'm new in SSIS.
Thank you.

Package variables to the rescue!

If you need to capture the number of times the failure is triggered, you can create a package variable then connect an Expression Task to the failure pathway that increments the variable by 1.

If you need to track the total rows affected, you need to create two variables, one for row count and row count total.  You can add a Row Count task within your Flat File Destination, then have an Expression Task afterwards that adds that to the row count total variable.

Visit https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/ for an introduction to SSIS variables.


Automating database restoration of SQL Server transaction log files on standby mode I am wondering if I get help from this house on the following issue.
I have a standby database that should be restored with the set of transaction log files I get from the vendor's(eClincalWorks) sftp site daily.

The automation I am looking for is to do the following tasks:

1- Download the daily transaction files from the vender's sftp site and save to the local drive.
Note: the files in the sftp server are in a zip folder named as db_xxxx_today'sDate contains the daily transaction log files that are backed-up every 30 min at the production server.

Extracting the zipped folder into a different folder in the local drive
Scheduling restoration of the extracted-log files into the SQL database in the correct LSN order and in standby mode.

Please let me know if I need to explain more to my question and thanks in advance for any of your help on this matter!

Lilly.

Keep in mind that the first time your client does full backup, you will not be able to restore log files before full backup restore, so that step should be implemented in your automation.


Meaning of precision 1, scale 0 in error message? What datatype does this error message refer to?

ERROR:  numeric field overflow
DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

I'm getting the error listed above, it's coming from an insert trigger. Since none of my datatype are explicitly defined as NUMERIC(1,0) and since the maximum value is 10 to the power of 1 (i.e. 10) I'm guessing that this error is actually referring to a BOOLEAN field. However, I can't find confirmation of this.
UPDATE
Solved. My trigger is attempting to parse some strings into numbers. The error listed is returned when to_number() doesn't get a string in the expected format.
This returns 1.4 (correct) -
select to_number('1.4','9D9');

This returns the aforementioned error
select to_number('1d4','9D9');

Still not quite sure why it's complaining about NUMERIC(1,0) but at least I know why there's an error.

That's because to_number() drops anything but data characters from the input string. Data characters are: digits, sign, decimal point, and comma. Noise characters are not insignificant though, as they still count against the length of the format pattern. So:
SELECT to_number('1d4','9D9');

is effectively the same as:
SELECT to_number('14','9D9');

Both fail because the pattern '9D9' only allows a single digit before the comma.
But this valid expression:
SELECT to_number('1X4','99D9');  -- added digit to pattern

is not the same as:
SELECT to_number('1XXX4','99D9');

To much noise truncates the input.
db<>fiddle here
And, yes, D is a format specifier for a decimal point (independent of locale) in the pattern string (2nd function argument), but not in the input string (1st function argument). You found that yourself already.

To address the question in the title - Postgres has a definition in the manual:

The precision of a numeric is the total count of significant
digits in the whole number, that is, the number of digits to both
sides of the decimal point. The scale of a numeric is the count of
decimal digits in the fractional part, to the right of the decimal
point. So the number 23.5141 has a precision of 6 and a scale of 4.
Integers can be considered to have a scale of zero.

That said, I see two Postgres issues here:
1. Missing documentation
It's not documented in the manual that to_number() ignores non-data characters in the input. Nor that those still count against the total length of the pattern.
2. Bug in error message
There seems to be an error in the DETAIL of the error message. The observed:

DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

Should really be:

DETAIL:  A field with precision 2, scale 1 must round to an absolute value less than 10^1.

The effect is the same, so the behavior is coherent. But the information is misleading.


Something like this should work.

SELECT gs.dt, count(*)
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
INNER JOIN foo AS f
  ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

First we need to get the min and the max...

SELECT
  date_trunc('second',min(dt)) AS min,
  date_trunc('second',max(dt))+interval '1 second' AS max
FROM foo;
         min         |         max
---------------------+---------------------
 2017-08-17 21:36:10 | 2017-08-17 21:37:22
(1 row)

now we generate a series with that...

SELECT gs.dt
FROM (
  SELECT
    date_trunc('second',min(dt)) AS min,
    date_trunc('second',max(dt))+interval '1 second' AS max
  FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt);
         dt
---------------------
 2017-08-17 21:36:10
 2017-08-17 21:36:12
 2017-08-17 21:36:14
 2017-08-17 21:36:16
 2017-08-17 21:36:18
 2017-08-17 21:36:20
 2017-08-17 21:36:22
 2017-08-17 21:36:24
... etc.

now we just need to join that to our data. Using integer division on the epoch by 2, we can reduce this to every other second..

INNER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)

However if you want the groups with 0, we make that LEFT OUTER JOIN and count just the right side.

SELECT gs.dt, count(f.*)
FROM (
        SELECT
                date_trunc('second',min(dt)) AS min,
                date_trunc('second',max(dt))+interval '1 second' AS max
        FROM foo
) AS t
CROSS JOIN LATERAL generate_series(t.min,t.max,'2 second') AS gs(dt)
LEFT OUTER JOIN foo AS f
        ON (EXTRACT(epoch FROM f.dt)::int/2) = (EXTRACT(epoch FROM gs.dt)::int/2)
GROUP BY gs.dt;

If they're out of order, add an ORDER BY gs.dt

         dt          | count
---------------------+-------
 2017-08-17 21:36:10 |     5
 2017-08-17 21:36:12 |     0
 2017-08-17 21:36:14 |     0
 2017-08-17 21:36:16 |     1
 2017-08-17 21:36:18 |     0
 2017-08-17 21:36:20 |     2
 2017-08-17 21:36:22 |     0
 2017-08-17 21:36:24 |     0
 2017-08-17 21:36:26 |     0
 2017-08-17 21:36:28 |     0
 2017-08-17 21:36:30 |     0
 2017-08-17 21:36:32 |     0
 2017-08-17 21:36:34 |     0
 2017-08-17 21:36:36 |     0
 2017-08-17 21:36:38 |     0
 2017-08-17 21:36:40 |     0
 2017-08-17 21:36:42 |     0
 2017-08-17 21:36:44 |     0
 2017-08-17 21:36:46 |     0
 2017-08-17 21:36:48 |     0
 2017-08-17 21:36:50 |     0
 2017-08-17 21:36:52 |     0
 2017-08-17 21:36:54 |     0
 2017-08-17 21:36:56 |     0
 2017-08-17 21:36:58 |     0
 2017-08-17 21:37:00 |     0
 2017-08-17 21:37:02 |     0
 2017-08-17 21:37:04 |     0
 2017-08-17 21:37:06 |     0
 2017-08-17 21:37:08 |     0
 2017-08-17 21:37:10 |     0
 2017-08-17 21:37:12 |     0
 2017-08-17 21:37:14 |     0
 2017-08-17 21:37:16 |     0
 2017-08-17 21:37:18 |     0
 2017-08-17 21:37:20 |     1
 2017-08-17 21:37:22 |     0
(37 rows)


SQL Server 2014 Triggers and where are they created I am on SQL Server 2014 Standard edition. I am developing a Supply Chain DW and I am currently in the process of building a Landing schema which will be used to Land data coming out of the source system and then move that data into a Staging schema to begin the ETL process. I am truncating the landing tables after I have moved that days data into Staging. I am using Hashbytes in a Trigger which will add a hash value to each record for comparison while being moved upstream.

CREATE TRIGGER trgr_hashval_mara ON land.mara
FOR UPDATE, INSERT AS
UPDATE land.mara
SET HashVal = (SELECT hashbytes('md5', (SELECT material, MaterialDesc FOR xml raw)));

I can create these all day long, but I am unable to Drop them using conventional syntax as below. Executing the DROP statement produces an error.

 DROP TRIGGER trgr_hashval_mara;

"Can't find trigger or you don't have permission to drop trigger."

However the following statement comes back with a list of Triggers I have created.

select * from ibscm01.sys.triggers

I also can't seem to find them in the DB Tree. I see the Database Triggers child, but there are no Items/triggers shown. I am using Toad for SQL server 6.7 which should be the current release. also using Windows Authentication with AD and I have dbo privileges.

Thanks,
Pat

Triggers will be in the same Schema as the Table that they are created on. This is either handled automatically if the Schema is not specified in the CREATE TRIGGER statement, or it is enforced if it is specified but not the same as the Schema of the Table. The MSDN documentation for CREATE TRIGGER states:

  DML triggers are scoped to the schema of the table or view on which they are created.

And a simple test confirms the enforcement of this rule:

CREATE SCHEMA [TriggerTest];
GO

CREATE TABLE dbo.TriggerSchemaTest (Col1 INT);
GO

CREATE TRIGGER [TriggerTest].[DifferentSchemaThanTable]
ON [dbo].[TriggerSchemaTest]
AFTER INSERT
AS
BEGIN
  DECLARE @DoNothing INT;
END;
GO

produces the following error:

  Msg 2103, Level 15, State 1, Procedure DifferentSchemaThanTable, Line 6
  Cannot create trigger 'TriggerTest.DifferentSchemaThanTable' because its schema is different from the schema of the target table or view.

The syntax for dropping a Trigger, according to the SQL Server 2012 & 2014 MSDN page for DROP TRIGGER, is (essentially):

DROP TRIGGER [schema_name.]trigger_name;

So, if you want to know what Schema a particular Trigger (and its parent Table) is in, use the following query:

SELECT ss.[name] AS [TableAndTriggerSchemaName],
       so.[name] AS [TriggerName],
       st.[name] AS [TableName]
FROM   sys.objects so
INNER JOIN sys.schemas ss
        ON ss.[schema_id] = so.[schema_id]
INNER JOIN sys.tables st
        ON st.[object_id] = so.[parent_object_id]
WHERE  so.[name] = N'trgr_hashval_mara';

Also:

This is one reason (readability) why it is best to always specify the Schema name when creating Schema-bound objects (rather than relying upon default behavior). Hence you should be using: CREATE TRIGGER land.trgr_hashval_mara ON land.mara...
If that is your entire, actual Trigger code, there is a huge problem: it is updating all rows of the table for each and every INSERT and UPDATE statement. Is that really what is desired? You need to be joining to the INSERTED pseudo-table so that you only update rows that are actually changing.
You might want to add SET NOCOUNT ON; at the top of each Trigger.


MySQL very long-running queries I have a quite large db (800+Mb dump file) that I imported to my local server. It's a Wordpress db from witch I need to extract certain posts. There are around 160,000 posts inside.

Currently I'm testing a bit with MySQL Workbench running simple queries with JOIN and it requires a lot of time, so long actually that Workbench stops processing.

Here's an example:

SELECT
    COUNT(*)
FROM
    wp_posts
        LEFT JOIN
    wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
        LEFT JOIN
    wp_term_taxonomy ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
        LEFT JOIN
    wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE
    wp_terms.term_id = 195;

Running over 600 seconds. Here is the wordpress db schema:
https://codex.wordpress.org/images/2/2a/WP3.9.4-ERD.png

Of course chance is I'm just bad at SQL, not really my field...

Remove all the LEFTs.

Make sure there is an index on term_id in wp_terms.  It is probably the PRIMARY KEY, which is good.


Multiple filegroups vs. multiple files in a single filegroup From what I've read online, it seems that multiple filegroups offer only two advantages over having multiple files in a single filegroup:

The ability to isolate a specific table to a specific drive - the only way to accomplish this is to add a new filegroup with a file on the target drive, and then move the table there.
Restores can be sped up by doing a piecemeal restore with a read-only filegroup.

Are there any other advantages that multiple filegroups would have over multiple files in a single filegroup?  I'm most interested in performance advantages, but welcome any ideas.

Both multiple files in a filegroup and multiple filegroups are tools for managing your database. The first lets you manage your IO and both will let you manage your backups.

It is possible to backup a single file of a database as well as a single filegroup. Be sure to backup the tail of the transaction log when you do if you are planning on restoring it somewhere.

Database files allow your multi-core CPUs to have multiple read/write streams to the database without hitting higher disk queuing values.

It may help to think of the filegroup as a logical division and the file as a physical division. If you have multiple filegroups you will automatically have multiple files as a file can only belong to one filegroup. It can also help (if you have enough cores on your server) to have multiple files in your filegroups. That can give you the best of both worlds. You assign database objects to a filegroup not a file. You can put the files on different physical disk arrays. When I first started doing database work it was common knowledge that you put your data and log on separate disks. If you had the budget you would put your non-clustered indexes on another disk. It's tough to get that these days with SAN technology everywhere. However, SAN is a management tool not a performance tool.

As you pointed out having different filegroups will allow you to isolate high traffic tables from each other and from lower traffic tables. It will also allow you a limited additional protection from a corrupted database potentially limiting data corruption to a smaller part of the data.


PL/SQL Can't grant privileges while logged as system I can't grant the execute privilege on DBMS_LOB (I need it to operate on files), even when I'm logged as system. What can I do? Granting DBA ROLE doesn't help at all.

Error starting at line 7 in command:
grant execute on sys.DBMS_LOB TO PGADMIN
Error report:
SQL Error: ORA-01031:
01031. 00000 -  "insufficient privileges"

I've been racking my brain about it for few hours. I hope someone can provide a solution that will work in my case.

SYSTEM is a typical database user (such as HR or OE)

The DBMS_LOB package belongs to SYS schema so that only the owner(Or user having the GRANT OPTION) can grant the object privilieges to other users. So if you connect as SYSDBA using SYSTEM user you will be connected to SYS schema.

SQL> conn system/oracle
Connected.
SQL> show user
USER is "SYSTEM"
SQL>

SQL> grant execute on dbms_lob to hr;
grant execute on dbms_lob to hr
                 *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn system/oracle as sysdba
Connected.
SQL>
SQL> show user
USER is "SYS"
SQL> grant execute on dbms_lob to hr;

Grant succeeded.

SQL> revoke execute on dbms_lob from hr;

Revoke succeeded.

OR

You can grant EXECUTE on DBMS_LOB to SYSTEM user with GRANT OPTION then user SYSTEM user to grant that object privilege to other users.

SQL> conn / as sysdba
Connected.
SQL> grant execute on dbms_lob to system with grant option;

Grant succeeded.

SQL> conn system/oracle
Connected.
SQL> grant execute on dbms_lob to hr;

Grant succeeded.

SQL>


Will hacking away ibdata1 destroy databases with only MyISAM tables? I made a mistake.

I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so.

I know that safely shrinking ibdata1 requires all databases to be nuked then reloaded from an SQL dump.

But, if I don't care about my InnoDB tables any more, can I remove the ibdata1 file without breaking the original database that only contains MyISAM tables? Dropping it and reloading it from an SQL dump is kind of possible, but since the database is approaching 250GB in size, it will take a lot longer than I'd like.

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i686) using readline 5.1
$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries.

First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko)

STEP 01 : Make sure there is no InnoDB

Run the following

SELECT
    table_schema,table_name,
    (data_length+index_length)/POWER(1024,3) table_sizegb
FROM information_schema.tables
WHERE
    engine='InnoDB' AND
    table_schema NOT IN ('information_schema','performance_schema','mysql');

If you get nothing, you can proceed.

If you do get something, either drop those tables, convert them to MyISAM, or mysqldump them to reload later.

Assuming datadir is /var/lib/mysql

STEP 02 : Configure innodb_file_per_table

You should add this to `my.cnf

[mysqld]
innodb_file_per_table=ON

This will allow future for CREATE TABLEs where you want .ibd files

STEP 03 : Flush everything from InnoDB

SET GLOBAL innodb_fast_shutdown = 0;

STEP 04 : Shutdown mysql

service mysql stop

STEP 05 : Delete ibdata and the redo logs

cd /var/lib/mysql
rm -rf ibdata1
rm -rf ib_logfile0
rm -rf ib_logfile1

STEP 06 : Start mysql

service mysql start

This will regenerate ibdata1, ib_logfile0, and ib_logfile1

NOTE: MyISAM tables are completely unaffected. Why ?

Suppose you have a table mytable in the mydb database.

The files for that one table are the following:

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.MYD
/var/lib/mysql/mydb/mytable.MYI

The InnoDB file ibdata1 is usually located in /var/lib/mysql. Therefore, all MyISAM tables are safely tucked away in their respect database folders.

CAVEAT for MyISAM only users

If you know for a certainty that you will never use InnoDB, you could add this parameter to my.cnf

[mysqld]
skip-innodb

That way, when you start up mysqld in STEP 06, the process will not regenerate ibdata1, ib_logfile0, and ib_logfile1.

CAVEAT FOR MySQL 5.6/5.7 users

In MySQL 5.6, there are 5 tables in the mysql schema (/var/lib/mysql/mysql) that are InnoDB. MySQL 5.7 has 19.

Just run this query to see what those tables are

SELECT table_name FROM information_schema.tables
WHERE engine='InnoDB' and table_schema='mysql';

See my old post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 and the MySQL Documentation.

In this instance, you would mysqldump the mysql schema, blow away the InnoDB files, and manually delete the .ibd file in /var/lib/mysql/mysql. Then, reload the mysql schema from that dump.


How to display Postgres version in the CLI pompt? I whish to display the version of the server I am connected to in the command-line (console) interface prompt. From what I read in the documentation, it is possible to execute a shell command, it is possible to display a psql variable value.

The idea is to get the server's version information on connection and use it in the client's prompt. How can I (in the .psqlrc file ?) assign the server version in a psql variable ?

Edit: Jack Douglas♦ has the right answer by pointing out the \gset feature. It ended up with a

show server_version
\gset
\set PROMPT1 '%:server_version: >'

Thank you.

Since 9.3, you can do this using \gset in psqlrc:

select split_part(version(),' ',2) pmt
\gset
\set PROMPT1 '%:pmt:'

Or, as 'the value of the selected prompt variable is printed literally, except where a percent sign (%) is encountered':

select split_part(version(),' ',2) "PROMPT1"
\gset

If your version is <9.3, it's still possible, but much uglier, e.g. something like:

\set PROMPT1 `psql postgres postgres -Atc "select split_part(version(),' ',2)"`


Using character set en_us.364 in Informix database DB_LOCALE I am trying to change my db_locale to en_us.364 which is an ASCII character set. It says on IBM's Knowledge Center that 364 is a valid character set:

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.jdbc_pg.doc/ids_jdbc_270.htm

I am able to change my DB_LOCALE=en_us.364. However, after changing the db_locale to en_us.364 I cannot access dbaccess. I get this error:

  -23101 Unable to load locale categories.

So I also tried changing my client_locale to en_us.364 as well. I was able to set the environment variable. However it still gave the same error when trying to use dbaccess.

I then looked around in the GLS/lc11/en_us and could not find 016c.lco which is the character set 364/ASCII. However, I have found this character set in the GLS/lc11/pl_pl folder which means that ASCII is supported. I was wondering where I could get either the 016c.lco or the 016c.lc for the locale en_us.

For better or worse (and mostly worse), although some of the files necessary to support en_us.364 are present, the file needed in lc11/en_us is not present.  Thus, you can have de_de.364 or es_es.364 or fr_fr.364 or pl_pl.364 but you can't have en_us.364.

You have a few options at this point.

One is to try downloading the ILS — International Language Supplement.  This has many extra locales in it; it might have en_us.364 (but no promises).
Another is to try copying an existing locale (maybe gls/lc11/fr_fr/016c.lco or gls/lc11/de_de/016c.lco to gls/lc11/en_us/016c.lco. You'd probably want to edit the LANGUAGE and TERRITORY lines.  However, be aware that the French and German locales are different; it will matter which you choose.
Report a problem to IBM Informix Tech Support, requesting the file for gls/lc11/en_us/016c.lco.

I'm not sure which is best.  If the relevant file is in the ILS, then that's simple.  Copying a locale file is mostly straight-forward, but you need to consider what will happen if that's not available.


Implicit privileges for schema owner I am confused about schema owners in Postgres.

When I create a schema with the owner defined by the AUTHORIZATION keyword, owner can create objects in that schema (no explicit privileges are shown on that schema in pgAdmin4).

But when I alter the schema owner to another role, this role cannot create objects in that schema (until I explicitly grant USAGE and CREATE).

I suppose, that schema owner has implicit privileges to do anything with schema. What is the right way to change schema ownerschip?

I suppose, that schema owner has implicit privileges to do anything with schema.

No, that is not the case. The manual on GRANT:

  PostgreSQL allows an object owner to revoke their own ordinary
  privileges: for example, a table owner can make the table read-only to
  themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE
  privileges. This is not possible according to the SQL standard. The
  reason is that PostgreSQL treats the owner's privileges as having been
  granted by the owner to themselves; therefore they can revoke them
  too. In the SQL standard, the owner's privileges are granted by an
  assumed entity "_SYSTEM". Not being "_SYSTEM", the owner cannot revoke
  these rights.

Bold emphasis mine.

There are also no default privileges for public:

  PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. (...)

So even the owner needs privileges for a schema - which are given by default! If all you do is:

CREATE SCHEMA test AUTHORIZATION dummy;

Or:

CREATE SCHEMA test;

Then default privileges are NULL, i.e. default to system defaults (which is USAGE and CREATE for the owner of a schema). The manual on GRANT:

  If the "Access privileges" column is empty for a given object, it
  means the object has default privileges (that is, its privileges
  column is null). Default privileges always include all privileges for
  the owner, and can include some privileges for PUBLIC depending on the
  object type, as explained above. The first GRANT or REVOKE on an
  object will instantiate the default privileges (producing, for
  example, {miriam=arwdDxt/miriam}) and then modify them per the
  specified request. Similarly, entries are shown in "Column access
  privileges" only for columns with nondefault privileges. (Note: for
  this purpose, "default privileges" always means the built-in default
  privileges for the object's type. An object whose privileges have been
  affected by an ALTER DEFAULT PRIVILEGES command will always be shown
  with an explicit privilege entry that includes the effects of the ALTER.)

If you revoke privileges from the owner (can be done by owner himself or superuser), then the role does not have these privileges. (But the owner can always grant these privileges to himself as well.) And if you assign a new owner, the status is inherited:

-- as user postgres:
CREATE SCHEMA test1;
REVOKE ALL ON SCHEMA test1 FROM postgres;
ALTER schema test1 owner TO dummy;

You'll now see an empty array ({}) instead of NULL:

SELECT nspname AS schema, nspacl AS privileges
FROM   pg_namespace
WHERE  nspname LIKE 'ow%';

(pgAdmin4 reads from the same system table.)

If you did not explicitly revoke USAGE and CREATE, then the new owner has these privileges, too. That's what's confusing in your description. Did you revoke privileges from the old or new owner?


SQL Job Fails Logging In to Integration Services I have a SQL Server 2012 where all databases are set up in instances.

I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder.

Integration Services is NOT running in an instance.

My domain account doesn't have rights to login to Integration Services, so the DBA used his account and deployed the DTS package to Integration Services MSDB relying on the package for access and it runs from there without any issues. The DTS package uses my domain account to login to the database and access the table.

Then I created a SQL Job to run the package. It didn't run, of course. I create a proxy account to run the package. The proxy account uses the credentials of my domain account (which doesn't have access to SSIS, but the DBA temporarily made it a local admin on the SQL Server assuming that would give it the rights that it needed but that didn't work either. The error message states that it can't access the Integration Services database.

We gave the proxy SQL Agent operator role so we run the job with it.

The account running the SQL Agent for the instance is NT Service\SQLAgent$MyInstance

I don't know what else to try.

If there's any information missing please let me know. I apologize, there are a lot of moving parts.

In SQL Server 2012, you should typically use the SSIS Catalog (database name is SSISDB) for storing your packages for execution. You can still store them in MSDB, but this is not recommended.

Double-check your SQL Agent job that it is not trying to connect to the SSIS catalog to execute the package, in which case it will fail because A) you probably don't have access to SSISDB (it may not even be configured), and B) the package is not in SSISDB, but MSDB.

Check the agent job and confirm that the package source is set to SQL Server and not SSIS Catalog to verify you're trying to run from the correct location.

Also, if you can post the full details of the error message it will help to identify your root issue quicker.

Link


Can I prove my database does not have memory issues? (This is a continuation of Do indexes consume memory?)

A fellow database developer believes we are having memory issues.  He has seen increased run times in some standard queries - going from under 10 seconds to about two and a half minutes.  He looked at Task Manager on the server and found high memory usage and now wants to take some of the memory currently allocated to the OS and free it up for SQL Server.

We are on SQL Server 2008, a 64-bit machine, AWE is not enabled, minimum 4096 MB, max 10240 MB.

I found Brent Ozar's A Sysadmin’s Guide to Microsoft SQL Server Memory which indicates Task Manager is not reliable.  I have also found that our Page Life Expectancy is not indicating memory pressure. (Checked via Pinal Dave's query.)

Where else should I look?  What else should I check?  I'd like to report back to the database developer to either confirm his suspicions or prove them incorrect.

EDIT: modified my actual question.  I appreciate, and agree, that these queries are overwhelming more likely slower for reasons other than memory problems.  I am in a situation, however, that I need to prove memory is not the culprit across the board.  That is, proving that a handful of queries are slower for other reasons won't accomplish my task.  I'd like to understand where I can get such information and what metrics I should check.

The only way to prove is not a memory issue is by prooving is something else. And this requires you to identify the root cause of your performance problems. I recommend you follow a methodology like Waits and Queues. The SQLCAT team has also published a Troubleshooting Flowchart poster that you can follow.

As a general side comment: if someone is offering you to buy more memory on the server, just say YES, and then go ahead and do the root cause analysis. It doesn't matter that if the root cause of the problems are table scans, SQL Server can always use more memory.


Suppressing SELECT output before \gexec? This answer is great for when I needed to reown 150 tables, each with 40 tables, but displays the 6000 rows from SELECT before running the 6000 ALTER statements.
Thus, as stated in the Subject, is there any way to suppress the SELECT output?
sides=> SELECT format(
sides(>   'ALTER TABLE %I.%I.%I OWNER TO sides_owner;',
sides(>   table_catalog,
sides(>   table_schema,
sides(>   table_name
sides(> )
sides-> FROM information_schema.tables
sides-> WHERE table_schema = 'strans';
                                       format
---------------------------------------------------------------------
 ALTER TABLE sides.strans.foo OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_02 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_03 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_p2020_04 OWNER TO sides_owner;
 ALTER TABLE sides.strans.blarg_error_p2019_01 OWNER TO sides_owner;
 etc
 etc
 etc

Thus, as stated in the Subject, is there any way to suppress the SELECT output?

Yes, by omitting the semi-colon after the SQL statement.
That is, \gexec itself ends the SQL statement.
test=> select 'select 1' \gexec
 ?column?
----------
        1
(1 row)


SQL update multiple rows in destination table with same id but different values from source table I have two different tables with a common column called id:

Table1
----  -------
id  |  Date
----  -------
1      null
1      null
2      null
2      null
2      null
2      null
3      null
4      null
4      null

Table2
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000
2      2013-01-31 11:56:00.000
2      2013-03-11 16:08:00.000
2      2013-01-31 14:04:00.000
2      2013-01-31 14:08:00.000
3      2013-02-28 23:44:00.000
4      2013-01-31 14:04:00.000
4      2013-01-31 14:08:00.000

I need to write an update statement that will basically put the value of the first row with id=1 from Table2 into Table1 with an id=1 and the second row with id=1 from Table2 into Table1 with an id=1 so that Table1 now looks like this

Table1
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000

Currently this SQL statement

UPDATE Table1
  SET Table1.date = T2.date
  FROM
  Table2 T2
  INNER JOIN Table1 T1
  ON T1.date = T2.date

is making Table1 look like this, where it applies the first date it finds for that id to all the rows with matching ids in the destination table

Table2
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 08:50:00.000

Is there a way to udpate Table1 to look like this below:

Table1
----  -------
id  |  Date
----  -------
1      2013-01-29 08:50:00.000
1      2013-01-29 15:28:00.000
2      2013-01-31 11:56:00.000
2      2013-03-11 16:08:00.000
2      2013-01-31 14:04:00.000
2      2013-01-31 14:08:00.000
3      2013-02-28 23:44:00.000
4      2013-01-31 14:04:00.000
4      2013-01-31 14:08:00.000

Is there a way to do this in a SQL statement? I don't want to use a cursor preferably.

There are other columns in both the tables and not all id values in Table1 might be there in Table2 and vice versa. Those other rows and columns I don't want to touch or alter.

Couldn't find a SQL Server 2008 fiddle engine so I had to opt for a SQL Server 2014 ... so not sure if the following will work in SQL Server 2008, but fwiw ...

Setup some sample data:

create table Table1(id int, Date datetime null);
create table Table2(id int, Date datetime);

insert Table1 values (1,null)
insert Table1 values (1,null)
insert Table1 values (2,null)
insert Table1 values (2,null)
insert Table1 values (2,null);

insert Table2 values (1,'2013-01-29 08:50:00.000')
insert Table2 values (1,'2013-01-29 15:28:00.000')
insert Table2 values (2,'2013-01-31 11:56:00.000')
insert Table2 values (2,'2013-03-11 16:08:00.000')
insert Table2 values (2,'2013-01-31 14:04:00.000');

Keeping in mind that we haven't been provided (yet) with any means to determine which rows to match between Table1 and Table2 for a given id value, I'll just let row_number() generate a 'matching' rowid.

And then we'll make use of SQL Server's ability to update Table1 via a derived table definition:

update T1
set    T1.Date=T2.Date

from   (select row_number() over(partition by id order by Date) as rowid,
               id,
               Date
        from   Table1
        where  Date is NULL) T1

join   (select row_number() over(partition by id order by Date) as rowid,
               id,
               Date
        from   Table2) T2

on      T1.id    = T2.id
and     T1.rowid = T2.rowid;

And the results:

select * from Table1;

id  Date
--- --------------------
1   2013-01-29T08:50:00Z
1   2013-01-29T15:28:00Z
2   2013-01-31T11:56:00Z
2   2013-01-31T14:04:00Z
2   2013-03-11T16:08:00Z

And here's a SQL Fiddle for the above.


/ Character in a stored procedure I am trying to recompile/update a stored procedure in SQL Developer that uses a few division operations to populate tables.

A sample section looks like this:

  Insert Into Production(A, B, C)
  SELECT A, B, (SELECT SUM(Month_Production)
  FROM Production / SELECT COUNT(Month_Production) FROM Production2)  FROM DUAL;

The problem I run into is in the subquery, it thinks the division symbol is an "END OF FILE" marker, and then can't process the rest of the procedure (There are about 10 of these types of insert statements in this procedure). I have been researching online how to "escape" this character.

I tried using a \ to escape it out,

   Insert Into Production(A, B, C)
   SELECT A, B, (SELECT SUM(Month_Production)
   FROM Production \/ SELECT COUNT(Month_Production) FROM Production2)
   FROM DUAL;

but this is not working to help compile. Any idea on how I can compile this while keeping the division operations in here?

Thanks!

edit:I rewrote the queries as I think the focus was on my structure and not the problem. The queries I posted were just quick write ups to demonstrate what I am having issues with. The queries in question are very long and work fine, I pulled a "Backup" query from the procedure but even when I rerun it it feels the / is the end of the file and not a division symbol. I've tried to rewrite it with various ways to surround the code with () but still am having issues.

Your outer select is incomplete, it has no FROM clause.

Once that's fixed your query may work, if not try putting each of the sub selects inside of a pair of parenthesis, and possibly the whole operation.

Insert Into Production(A, B, C)
SELECT A
     , B
     , ( (SELECT SUM(Month_Production) FROM Production)
       / (SELECT COUNT(Month_Production) FROM Production)
       ) C
  from some_table;  -- <== This is missing

By the way since both the numerator and the denominator are coming from the same table, this would be a more efficient query:

SELECT A
     , B
     , (SELECT SUM(Month_Production)
             / COUNT(Month_Production)
          FROM Production
       ) C
  from some_table;

Since it only has to access the whole Production table once instead of twice


Setting rowlock order We have a deadlock issue in our environment. We need to select some rows and then update them.
Those queries are very frequent so we use a rowlock and updlock hints in select statement.
Those queries sometimes are causing deadlocks.
We believe that the reason is that two queries start locking the same rows on the same index but in different index "direction".
So when the same queries run simultaneously they can sometimes lock each other.
Do this assumption makes sense?
If yes, is there anything we can do to force "rowlock locking order"?

Follow the Microsoft prescribed Detecting and Ending Deadlocks article. You should never "guess" at the reason for the deadlocks, otherwise you will be guessing at solutions.  The short list of steps to take is enable trace flags 1204 and 1222.  The output will give you explicitly the resource ID of what what's actually involved.  Could be a foreign key constraint, index page.  Only knowing that can you target what to try to fix with your queries, indexes or even isolation level.


How to enable Toolbox window in SQL Server Management Studio? I am assuming that the Toolbox windows in SSMS is used to store code samples.  So I am able to highlight code in the editor and drag it to the Toolbox window.
However, if I wanted to use the code sample and drag it back onto the editor (or double click on it), I can't.  All entries in the Toolbox are grayed out.  And when I drag an entry, it turns the mouse cursor into a stop sign.
What do I need to do to enable this functionality?

P.S.  Using SSMS v17.9.1 and the only add-in installed in SQL Prompt 8.
P.P.S. The only workaround that works is clicking on the entry, Ctrl+C, then pasting it into the editor.

After your updated comment, I believe my understanding is correct, that the Toolbox was only ever implemented to be used with the Maintenance Plan designer at this point. It's briefly mentioned in the Microsoft Books Online Use the Toolbox. My guess is they may have designed the Toolbox to be flexible should they implement it for other scenarios, but at this point I think Maintenance Plans is the only one, and I don't believe it's (natively) possible to enable it in other contexts of SSMS.
Sorry, that would've been cool. But something pretty similar to what you're trying to accomplish does exist in SSMS, called Code Snippets which that linked article goes through, and shows you how to customize with your own snippets via XML.


Best way to store products of Multiple Fields of different information I have around 6 table in one of 3rd party cloud database. I am planning to Build it MSSQL. My problem in each of these table there are 20-40 field of different information and Item. Which approach better to manage my data having a separate table for each or in a single table. if i store all in single table the number of field will in a table will be very large. I also plan to use C# for the Front end to develop Product Management System.

Thanks

If you have 6 entity types with different attributes then they should be stored in 6 different tables.

If they have attributes that are largely overlapping then you might consider storing multiple different types of entities in the same table.


CPU Time Slower in SQL Server 2014 than in SQL Server 2008 R2 We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v).  We were doing some performance testing and to our surprise it seems that CPU times are worse in the new server system compared to the old, crackling one.
These are the systems:

Old Server:

Runs in Virtual Machine VMWare ESX 5.1
Windows Server 2008 R2 32 bits
SQL Server 2008 R2
Quad Core Xeon ES-2620 2Ghz
5600MB RAM

New Server:

Runs in Virtual Machine Hyper-V UEFI Release v1.0
Windows Server 2012 R2
SQL Server 2014
Quad Core Xeon ES-2640 2.6Ghz
32766 MB RAM

A little history of what we did:

Two different benchmark tests shows that CPU is about 80% faster, memory access is about 100% faster and IO disc access is about 1000% faster in the new server.
Testing queries are using multiple joins with non-clustered indexes.
In both servers the testing database is exactly the same.
In both servers energy power option is set as High Performance and also checked with CPU-Z that the processor is always at max speed.
In both servers “locked pages” are enabled.
In both servers the statistics are properly updated and query plans are the same when executing the test queries.
To make sure that the plan are always the same we’re executing the queries in SQL Server 2014 with compatibility mode 100 (SQL Server 2008), but as well without this compatibility-mode the outcome is the same.
Max memory options is set to default value in both servers (2147483647) and we have also checked the parallelism (max degree is set the same for both and also the cost)
The new server is dedicated (there is nothing else running there)

After all these checks we’re getting better time results for cached queries (caused only by CPU time) still in the old server.

Test query:

SELECT ROW_NUMBER() OVER (
        ORDER BY c.field1
            ,c.field2
        ) AS RowNumber
    ,a.t1_t3ID
    ,c.field1
    ,c.field2
    ,a.t1_t2ID
    ,b.field1
    ,a.BeginDate
    ,a.EndDate
    ,c.field3
FROM table1 a
INNER JOIN table3 c ON a.t1_t3_ID = c.t3_PKID
INNER JOIN table2 b ON a.t1_t2ID = b.t2_PKID
WHERE '20140101' BETWEEN a.BeginDate
        AND a.EndDate

Query Execution Plan (is the same for both servers, with the same percentages):

Query Execution Plan Here

The Results for CPU time on both servers:

Old server:

CPU time = 93 ms,  elapsed time = 65 ms.
CPU time = 94 ms,  elapsed time = 99 ms.
CPU time = 94 ms,  elapsed time = 70 ms.
CPU time = 93 ms,  elapsed time = 63 ms.
CPU time = 93 ms,  elapsed time = 67 ms.

New server:

CPU time = 125 ms,  elapsed time = 309 ms.
CPU time = 141 ms,  elapsed time = 304 ms.
CPU time = 139 ms,  elapsed time = 288 ms.
CPU time = 156 ms,  elapsed time = 277 ms.
CPU time = 142 ms,  elapsed time = 323 ms.

  Note: we have also checked that the CONVERT_IMPLICIT is not affecting
  performance in this case.

  What we observed is as well if we force to use only one processor-core
  in the old server CPU time keep roughly the same. If we do same in the
  old server the execution-plan is switching to table-scans and CPU time
  increase up to 800ms.

Somebody have an idea what still can be checked/tested or at least have an explanation on this observation? Or is it for sure the virtualization systems (vmware vs. hyper-v) which cause such a difference in CPU time for databases?

Differences in CPU time in this scenario can be affected by many things and it could be just that SQL 2014 is a bit slower but scales better. However, I think more interestingly, the elapsed time is more than double the CPU time on the new server whereas it is less on the old. This could point to IO (as well as many others I concede) issues. Have you run perfmon on both servers to see what the underlying OS is reporting?

Other than that, any chance you can load 2014 on the same VMWare stack as the old server to remove the virtualisation layer from the equation?


when to use embedded PL/SQL gateway with Oracle APEX? Oracle Application Express (APEX) requires one of the following three options for an HTTP server: (1) Oracle HTTP Server (with mod_plsql), (2) Oracle Application Express Listener, or (3) an embedded PL/SQL gateway).

I understand the embedded PL/SQL gateway shouldn't be used when interfacing with browsers outside the firewall, which my application requires.

But, could I simply put an Apache Tomcat between it and the external browsers?

If so, what would be the advantages/disadvantages of using this solution compared to Oracle Application Express Listener (assuming it similarly has an Apache Tomcat between it and browsers)?

The options you note:

(1) Oracle HTTP Server with MOD_PLSQL
(2) Oracle APEX Listener
(3) Embedded PL/SQL Gateway

For each option, pro/con:

(1) Original solution. Full flexibility of an http server with rewrite rules, access rules, etc. However, loading additional modules for mixed application server use can be painful because of the custom Apache build. Also, licensing additional products from Oracle can be pricy.

(2) Latest solution. Most flexible, it can be used in any j2ee container (Tomcat, Glassfish, Weblogic, old OC4J, etc), but should be combined with a true http server with access rules, etc for security (Tomcat is typically combined with HTTPD for this purpose). This does have the problem in that currently this must be deployed for each connection configuration, while (1) is deployed for the whole server with the single configuration.

(3) This is typically not recommended for external deployment in any case. Included is the issue with direct access to the database server from external clients. This can be helped with HTTPD as a proxy server (like Tomcat above).

In existing installs, where you already have mod_plsql applications in place, put the APEX dad along side those and you should be set using solution (1). For new installs, I would go with (2). (3) is a viable option for initial setup and testing, but should be replaced prior to production deployment.


Automatic Failover At Always On High Avability Group SQL SERVER after i read on documentation
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-clustering-and-always-on-availability-groups-sql-server?view=sql-server-ver15
i still confused about part protection level

if nodes within an FCI protection level = Instance & Failed-over resources = Server, instance, and database
if replica within an availability group protection level = Database & Failed-over resources=Database only

this mean if i choose or setup replica within an availability group if primary instance is down automatic failover is not running ?

That's not what it means.
With a FCI, the protection is done at the service level. It means that if the primary node have an issue, the service will restart on the other node. As all node share the same storage, you end up with the exact same "instance" after a failover (same jobs, same logins, same DBs, etc..) but running from a different server.
In AG, the services is up and running on both node. The protection is set on at the database level (you can have some DB on the primary node that are in AG and some other that aren't for example). Each nodes also have their own storage.
If you are in sync mode and have the failover set to "automatic", then if the primary node of you AG fail, all DBs that are in your AG will failover to the other node.
P.s. The system databases are not part of the AG so you need to be carefull with everything releated to those (ex: Jobs, logins, etc) as they won't "failover".


Issues installing MySQL server on Ubuntu 13.04 I'm currently trying to install a MySQL server on my Ubuntu 13.04 machine. The problem is, when I try to install it, I get error messages indicating that not all packages could be downloaded. When I run sudo apt-get install mysql-server, and after hanging for a while at reading headers, the console reads as follows:

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5
Suggested packages:
  libipc-sharedcache-perl tinyca mailx
The following NEW packages will be installed:
  libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libnet-daemon-perl libplrpc-perl mysql-client-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
0 upgraded, 12 newly installed, 0 to remove and 3 not upgraded.
Need to get 8,077 kB/24.5 MB of archives.
After this operation, 84.9 MB of additional disk space will be used.
Do you want to continue [Y/n]? y
Err http://us.archive.ubuntu.com/ubuntu/ raring-updates/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.91.13 80]
Err http://security.ubuntu.com/ubuntu/ raring-security/main mysql-client-5.5 i386 5.5.32-0ubuntu0.13.04.1
  Connection failed [IP: 91.189.92.190 80]
Failed to fetch http://security.ubuntu.com/ubuntu/pool/main/m/mysql-5.5/mysql-client-5.5_5.5.32-0ubuntu0.13.04.1_i386.deb  Connection failed [IP: 91.189.92.190 80]
E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?

I've tried the suggested fix or running apt-get update and such, I've made sure I have no connections blocked, I've restarted, uninstalled, reinstalled, etc, all to no avail. I've been searching the web all day for solutions I haven't yet tried, but most people with this issue are running an older version of Ubuntu. Suggestions?

Assuming this isn't an Internet connection issue ("Connection failed" is a needlessly obscure message), then this looks like a problem with the particular Ubuntu mirrors your system is trying to use, rather than something specific to MySQL.

The answer posted on What to try when apt-get install xxx Results in Connection Failed seems like it should get you pointed in the correct direction to resolve this.

It's also possible that sudo apt-get update might fix your problem now, even if it didn't fix your problem before, since some time has passed.  I'm speculating, but it could be that if the information fetched by the update operation causes your system behave differently when it's trying to fetch the files you're needing to install, that might also resolve the problem.


mysql select increment group row number i had a table like following:

OBJECTID            FRUIT
----------------------------
XA13AC              Apple
HAS13X              Grape
UTHQ1G              Orange
YQ1GXQ              Kiwi
OQP1GV              Apple
GPQ1VA              Grape
GQ1GQ1              Manggo
GYX1GQ              Banana
OPI1G2              Apple

Table:

CREATE TABLE `FRUITS` (
  `OBJECTID` varchar(6) DEFAULT NULL,
  `FRUIT` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Data:

insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('XA13AC','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('HAS13X','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('UTHQ1G','Orange');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('YQ1GXQ','Kiwi');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OQP1GV','Apple');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GPQ1VA','Grape');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GQ1GQ1','Manggo');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('GYX1GQ','Banana');
insert into `FRUITS` (`OBJECTID`, `FRUIT`) values('OPI1G2','Apple');

i tried following SQL not working:

SELECT *,
             (
                CASE FRUIT
                WHEN @curType
                THEN @curRow := @curRow + 1
                ELSE @curRow := 1 AND @curType := FRUIT END
              ) + 1 AS GROUP_NUM
FROM FRUITS
JOIN (SELECT @curRow := 0, @curType := '') r
GROUP BY FRUIT

expected result:

OBJECTID            FRUIT       GROUP_NUM
-----------------------------------------
XA13AC              Apple           1
HAS13X              Grape           2
UTHQ1G              Orange          3
YQ1GXQ              Kiwi            4
OQP1GV              Apple           1
GPQ1VA              Grape           2
GQ1GQ1              Manggo          5
GYX1GQ              Banana          6
OPI1G2              Apple           1

trying to group the fruit by name and its increment group_num on different fruit name.

any help would be great.
Thanks

For version 5+

SELECT OBJECTID,
       CASE WHEN FRUIT = @curType
            THEN @curRow
            ELSE @curRow := @curRow + 1 END AS GROUP_NUM,
       @curType := FRUIT FRUIT
FROM FRUITS, (SELECT @curRow := 1, @curType := '') r
ORDER BY FRUIT

For version 8+

SELECT OBJECTID, FRUIT, DENSE_RANK() OVER (ORDER BY FRUIT) GROUP_NUM
FROM FRUITS

fiddle


select t1.*
from table t1
          join (select t2.session, max(t2.created_at) max_created_at
                from table t2
                group by t2.session
                order by max_created_at desc
                limit 10
               ) t3
          on t1.session = t3.session
order by t3.max_created_at desc, t3.session ;


Understanding the differences in the storage space used by two tables I have two tables in which, more or less, one contains a subset of data of the other one.  There is some data I delete from the first table, which is inserted in the second one as historical data.

These are the defintions:

CREATE TABLE [dbo].[CIC_DESLUNGHE](
    [CD_CIC_DESLUNGHE] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE] PRIMARY KEY CLUSTERED
(
    [CD_CIC_DESLUNGHE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[CIC_DESLUNGHE_STORICI](
    [CD_CIC_DESLUNGHE_STORICI] [bigint] IDENTITY(1,1) NOT NULL,
    [CD_CIC_PRODUZIONE_STORICI] [bigint] NOT NULL,
    [CD_CIC_OPERAZIONI_STORICI] [bigint] NOT NULL,
    [AFNMPROG] [int] NOT NULL,
    [AFDSLINE] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_CIC_DESLUNGHE_STORICI_1] PRIMARY KEY CLUSTERED
(
    [CD_CIC_DESLUNGHE_STORICI] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The second table contains a subset of data that I need it as historicization of my data.

Table                   Rows        TotalSpace MB
---------------------   ----------  -------------
CIC_DESLUNGHE_STORICI   12.709.497  48214,75
CIC_DESLUNGHE           24.827.337  5675,49

What leaves me astonished is that the total storage space utilized. The subset table use 10 times the space of the other.

Any idea why this is happening?

I tried rebuilding the clustered index of CIC_DESLUNGHE_STORICI, but the storage only dropped from 48 GB to 47 GB.

-- EDIT --

This is the result I got with the query of Josh Darnell

Based on the information given, one explanation is that the size of the data being stored in the nvarchar(max) column used to be much larger than it is now.

~12 million historical rows averaging 3.88 KB per row
~24 million live rows averaging 0.23 KB per row

You can double check this by using the dynamic management function sys.dm_db_index_physical_stats on each of the two tables, specifically looking at the "avg_record_size_in_bytes" column.  Something like this:

SELECT
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(N'YourDatabaseName'),
    OBJECT_ID(N'CIC_DESLUNGHE'),
    NULL,
    NULL ,
    'DETAILED'
) ips;

And

SELECT
    DB_NAME(ips.database_id),
    OBJECT_NAME(ips.[object_id]),
    ips.index_id,
    ips.index_type_desc,
    ips.index_level,
    ips.alloc_unit_type_desc,
    ips.page_count,
    ips.record_count,
    ips.min_record_size_in_bytes,
    ips.max_record_size_in_bytes,
    ips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(N'YourDatabaseName'),
    OBJECT_ID(N'CIC_DESLUNGHE_STORICI'),
    NULL,
    NULL ,
    'DETAILED'
) ips;

Compare the results of the two queries and see if there are any surprising discrepancies.

I left other filters off the function call because this will also show nonclustered indexes.  You didn't mention how you arrived at the numbers in your question, but if they include NC indexes on the tables, perhaps those are contributing to the size difference.


Data Domain Boost viewing backups Does anyone have any insight on how to view backups in Data Domain? Preferably with T-SQL. Thanks in advance.

Found the following T-SQL statement in EMC's documentation but I'm receiving the following error when using the correct "Client":

T_SQL Statement_______________________

USE [master]
GO
DECLARE @returnCode int
EXEC @returnCode = dbo.emc_run_delete ' -b "2 months ago" -e
"now" -N mssql -a "DDBOOST_USER=user" -a
"DEVICE_PATH=/path" -a "DEVICE_HOST=host" -a
"CLIENT=name"'
IF @returnCode <> 0
BEGIN
RAISERROR ('Fail!', 16, 1)
END

Error______________________

4690:ddbmexptool:The name 'CLIENT' does not correspond to a valid LNM parameter.
Msg 50000, Level 16, State 1, Line 10
Fail!

For client parameter, please mention the database server name along with FQDN.

For example if your server name is test and domain is abc.com, then enter the value for Client as "test.abc.com".

Also, please execute the statement  in one Line without any Returns/space:

EXEC @returnCode = dbo.emc_run_delete ' -b "2 months ago" -e
"now" -N mssql -a "DDBOOST_USER=user" -a
"DEVICE_PATH=/path" -a "DEVICE_HOST=host" -a
"CLIENT=test.abc.com"'


Get User for Audit Entries I am trying to construct history entries for my application tables.  (Basically a "who changed what and when" set of tables.)

My problem is that I have a lot of users.  Well over a thousand.  My users use UI applications that then call services.  The users authenticate to those services using OAuth2 and Open Id Connect (OIDC).  So I can't use "Integrated Security" to access SQL Server.  Instead I access SQL Server using a service account.

This means a call to SUSER_NAME() will return the service account instead of the user.

I have thousands of SQL queries.  It will be A LOT of work to go change each one to add a user parameter.

I am really hoping there is some way I can pass the real user to SQL Server.  I know this does not work, but an example would be something similar to adding it to a header in a call.

Is there some way to get the user to SQL Server besides the logged in user?

Or, failing that, is there some way to get SQL Server to work with access tokens (OAuth and OIDC)?

Depending on how the application is setup, one (maybe the only?) way to pass data at the connection level would be to utilize the ApplicationName portion of the connection string.

You could have the application set this value based on the logged in user, making it accessible via APP_NAME() on the database side.

Example:

Server=myServer;User Id=myUsername;Password=Pass;ApplicationName="MyApp - User LowlyDBA"


Identify rows that match on a key but differ in other columns Supposed I have table temp A and table temp B:

IF OBJECT_ID('tempdb..#A') IS NOT NULL
BEGIN DROP TABLE #A END

IF OBJECT_ID('tempdb..#B') IS NOT NULL
BEGIN DROP TABLE #B END

CREATE TABLE #A
(
   ID Int,
   Descr VARCHAR(100),
   Qty Int
)

CREATE TABLE B
(
   ID INT,
   Descr VARCHAR(100),
   Qty INT
)

INSERT INTO #A VALUES (1, 'Data 1' , 10)
INSERT INTO #A VALUES (2, 'Data 2', 5)

INSERT INTO #B VALUES (1, 'Data x' , 8)
INSERT INTO #B VALUES (2, 'Data x' , 1)

The ID is a key in this case. The problem is, how we can fetch the column value differences and showing the column differ on our select query?

I have tried the following:

using except

SELECT * FROM A
EXCEPT
SELECT * FROM B

using join and case.. when

select Case When #A.Descr = Before.Descr THen Null Else #A.Descr End,
       Case When #A.Qty = Before.Qty THen Null Else #A.Qty End
from #A
Left Join #B Before on #A.ID = Before.ID

This query shows me if different values exist then it will show the differences column, else if no difference value found on certain column, then will show null value.

Can you recommend a better solution?

I think that this is what you're looking for...

WITH AllData AS (
  -- Get All Records that exist in both tables
  SELECT ID, Descr, Qty FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.ID)
  UNION ALL
  SELECT ID, Descr, Qty FROM B WHERE EXISTS (SELECT * FROM A WHERE B.ID = A.ID)
)
SELECT ID, Descr, Qty
FROM AllData
GROUP BY ID, Descr, Qty
HAVING COUNT(*) = 1
ORDER BY ID

It first selects all of the rows where the ID exists in both tables (since if an ID exists in only one table, it can't have different values in the other table.)  Then it groups by the columns that you want to compare.  If the count is > 1, then they matched.  If the count = 1 then there was a difference.  Here is the SQL Fiddle that I used to verify this.

Edit:  For large data sets, the performance will likely be poor.  But indexing your tables should help with that.


Postgresql Create ForeignKey with repeated table name in value of ref contrain key I am working on a database which does not contain any foreign key. When i open table I see strange format key because table of parent reference is stored in the value.
exemple
CREATE TABLE foo
  (
  id SERIAL PRIMARY KEY,
  name text
  );

CREATE TABLE bar
  (
  id SERIAL PRIMARY KEY,
  foo_id text,
  name text
  );

INSERT INTO foo("name")
VALUES ('john');

INSERT INTO bar("foo_id","name")
VALUES (CONCAT('foo.', 1),'doe');
INSERT INTO bar("foo_id","name")
VALUES (CONCAT('foo.', 1),'kelly');

Can I create an FK with this type of structure? I have never seen this before

Can I create an FK with this type of structure?

No, you can't create a FK because the value of a FK column must match the value of the referenced PK column. But there is no value foo.1  in the table foo.
That design is horrible. I understand you didn't create that mess, but if you do have the chance to fix it, then do it as soon as possible.