Split time in intervals of 15 This is my table with data:

I want to select it as:

Where 0=office, 1=home,2 = telemedice

The time will always be a multiple of 5.

How can i do that?

I have tried this:

WITH CTE
AS (SELECT fromDate AS [Time],
           1 AS RLevel,
           id,
           CASE appointMode
               WHEN 0 THEN
                   'office'
               WHEN 1 THEN
                   'home'
               ELSE
                   'tele'
           END AS mode
    FROM tbl_provider_available_schedule
    UNION ALL
    SELECT DATEADD(MINUTE, 15, [Time]),
           RLevel + 1,
           id,
           CTE.mode
    FROM CTE
    WHERE RLevel < 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] <=
(
    SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];

Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
    practiceid,
    providerid,
    providerlocationid,
    fromDate,
    toDate,
    appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?

I have tried this:

WITH CTE
AS (SELECT fromDate AS [Time],
           1 AS RLevel,
           id,
           CASE appointMode
               WHEN 0 THEN
                   'office'
               WHEN 1 THEN
                   'home'
               ELSE
                   'tele'
           END AS mode
    FROM tbl_provider_available_schedule
    UNION ALL
    SELECT DATEADD(MINUTE, 15, [Time]),
           RLevel + 1,
           id,
           CTE.mode
    FROM CTE
    WHERE RLevel < 4)
SELECT id,
       [Time],
       CTE.RLevel,
       mode
FROM CTE
WHERE [Time] <=
(
    SELECT MAX(toDate) FROM dbo.tbl_provider_available_schedule
)
ORDER BY [Time];

Result that i get is:

My table script is:

CREATE TABLE [dbo].[tbl_provider_available_schedule]
(
[id] [int] NOT NULL IDENTITY(1, 1),
[practiceid] [int] NULL,
[providerid] [int] NULL,
[providerlocationid] [int] NULL,
[fromDate] [datetime] NULL,
[toDate] [datetime] NULL,
[appointMode] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_provider_available_schedule] ADD CONSTRAINT [PK_tbl_provider_available_schedule] PRIMARY KEY CLUSTERED ([id]) ON [PRIMARY]
GO

My data script is:

INSERT INTO dbo.tbl_provider_available_schedule
(
    practiceid,
    providerid,
    providerlocationid,
    fromDate,
    toDate,
    appointMode
)
VALUES
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
(1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
(1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

How can i achieve the above mentioned required results?

Hello I create a temporary table variable type to not create the table

DECLARE @tbl_provider_available_schedule TABLE
    (
    [id] [int] NOT NULL IDENTITY(1, 1),
    [practiceid] [int] NULL,
    [providerid] [int] NULL,
    [providerlocationid] [int] NULL,
    [fromDate] [datetime] NULL,
    [toDate] [datetime] NULL,
    [appointMode] [int] NULL
    );
    INSERT INTO @tbl_provider_available_schedule
    (
        practiceid,
        providerid,
        providerlocationid,
        fromDate,
        toDate,
        appointMode
    )
    VALUES
    (1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
    (1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
    (1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

    WITH CTE
    AS (SELECT fromDate AS [Time],
               1 AS RLevel,
               id,
               toDate,
               CASE appointMode
                   WHEN 0 THEN
                       'office'
                   WHEN 1 THEN
                       'home'
                   ELSE
                       'tele'
               END AS mode
        FROM @tbl_provider_available_schedule
        UNION ALL
        SELECT DATEADD(MINUTE, 15, [Time]),
               RLevel + 1,
               id,
               toDate,
               CTE.mode
        FROM CTE
        WHERE DATEADD(MINUTE, 15, [Time]) <toDate--We tell you not to miss the end date
        )
    SELECT id,
           [Time] AS StartDate,
           DATEADD(MINUTE, 15, [Time])  as EndDate,--add 15 minutes more
    CASE WHEN mode='office' THEN 1 ELSE 0 END AS Office,
    CASE WHEN mode='tele' THEN 1 ELSE 0 END AS tele,
    CASE WHEN mode='home' THEN 1 ELSE 0 END AS home
    FROM CTE
    ORDER BY id;

analyze it and you commit me.

PD:The final result is different because in your insert there is more data, notice that by the ID it looks like it was.

Demostration:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b02953ffbc0d70fe1e863a43e63c551f


What is Accepted NTLM SPNs In SQL Server Configuration Manager on the Advanced tab in TCP/IP Protocol properties box there is an option Accepted NTLM SPNs. What is the use of that option and how it can be used?

Accepted NTLM SPNs

The Accepted NTLM SPNs variable is needed when a server is known by more than one SPN. When a client attempts to connect to the server by using a valid SPN that the server does not know, service binding will fail. To avoid this problem, users can specify several SPNs that represent the server by using Accepted NTLM SPNs. Accepted NTLM SPNs is a series of SPNs separated my semicolons. For example, to allow the SPNs MSSQLSvc/ HostName1.Contoso.com and MSSQLSvc/ HostName2.Contoso.com, type MSSQLSvc/HostName1.Contoso.com;MSSQLSvc/HostName2.Contoso.com in the Accepted NTLM SPNs box. The variable has a maximum length of 2,048 characters. Accepted NTLM SPNs is on the Protocols for MSSQLSERVER Properties (Advanced Tab) in SQL Server Configuration Manager.

Microsoft Documentation


How could a "SECURITY DEFINER" function in PG be insecure with an improper search_path? The PG docs provides this example of a properly written security definer function with a search_path:

CREATE FUNCTION check_password(uname TEXT, pass TEXT)
RETURNS BOOLEAN AS $$
DECLARE passed BOOLEAN;
BEGIN
        SELECT  (pwd = $2) INTO passed
        FROM    pwds
        WHERE   username = $1;

        RETURN passed;
END;
$$  LANGUAGE plpgsql
    SECURITY DEFINER
    -- Set a secure search_path: trusted schema(s), then 'pg_temp'.
    SET search_path = admin, pg_temp;

I don't understand this part:

  But without the SET clause, or with a SET clause mentioning only
  admin, the function could be subverted by creating a temporary table
  named pwds.

If admin.pwds exists and search_path is set to admin only, how is it even possible to create a temporary table, pwds, maliciously?

If pg_temp is not present in search_path at all, then the temp scheme is always searched first, i.e. as if it were implicitly prepended to search_path.

The only way to prevent it from being searched first is to give it an explicit presence in search_path.


How to tell from the OS whether Oracle instance is a standby, assuming no DB access? One can easily check via SELECT database_role FROM v$database; whether your Oracle database is a primary or standby database.  Is it possible to make the same determination without database access, but only having (Unix) host access?

Oracle DBAs are very used to doing things like ps -ef | grep pmon or ps -ef | grep tnslsnr to quickly check if things are running, and these commands require no DB access.  They can even be run from an unprivileged (non-root) Unix user who doesn't own Oracle.  So thinking along those lines, I have 90% of it solved with things like ps -eo args | grep mrp0, but that falls short.  It only works if the standby is mounted and doing managed recovery.  It doesn't work if (i) the standby is simply mounted without starting MRP, or (ii) if the standby has been opened read-only.

So to recap, here are the parameters:

Goal is to determine whether a running DB instance is a Primary or Standby.
No access to the DB (e.g.: via SQL*Plus, JDBC, etc), but you have server access.
Therefore can only use Unix tools (ps, lsof, /proc, awk, ...)
Nice if can be done from unprivileged (non-root) Unix user.
Triple nice if can be done from any unprivileged Unix user (i.e. not the Oracle owner).

Note, I'm running Oracle 11gR2 & 12c on AIX, but I'll gladly take any *nix solution.

Note2, I don't care about the quasi-solution where as root you simply su to the Oracle owner and sqlplus / as sysdba. I want to do this without a database connection. No V$ views allowed, no X$ tables allowed.

You can try using:

ps -ef | grep -v grep | grep ora_pr

Or if data guard is in use:

ps -ef | grep -v grep | grep ora_rsm


How to convert DB2 9.5 to DB2 10.5 online (Linux) How to convert DB2 9.5 to DB2 10.5 online (Linux) ? This can be done with IBM InfoSphere Data Replication? Has anyone done this or have any idea how to do?

You cannot migrate the database online.

You can use InfoSphere DR or any other replication software for migration with minimal downtime, however, if your application itself does not support transparent connection failover you will still experience a brief outage.

The process in brief would look like so:

Install DB2 10.5.
Create a new database in a DB2 10.5 instance, using db2look.
Install and configure replication software.
Perform a full refresh from the old (V9.5) database to the new one.
Continue mirroring until the new database catches up to the old one.
Initiate application failover.
Stop replication once the application has fully failed over.


INSERT trigger dying with with #target columns not equal to #expressions, but they are equal I am trying to write my first trigger.  It is an insert trigger as follows:

<b>Fatal error</b>:  postgres7 error: [-1: ERROR:  INSERT has more target columns than expressions at character 78

INSERT INTO
dash_pre_queue
            (ord_obj_guid,
             ord_entry_type_guid,
             client_obj_guid,
             installer_obj_guid,
             installer_serial_number,
             datetime,
             ord_proc_state_guid,
             created,
             ord_esc_type_guid,
             in_error,
             ord_entry_guid)
SELECT
    (ord_obj_guid,
            NULL,
            client_obj_guid,
            inst_obj.inst_obj_guid,
            inst_obj.installer_serial_number,
            clock_timestamp(),
            ord_proc_state_guid,
            NULL,
            NULL,
            NULL,
            NULL)
    FROM
            ord_obj
    JOIN
            inst_obj ON inst_obj.inst_obj_guid = ord_obj.inst_obj_guid
    WHERE
            ord_obj.ord_obj_guid = NEW.ord_obj_guid

I left the error message on top.  The structure of the target table matches the INSERT INTO columns.  I have verified the NULL values are allowed.

If you count the columns in the Insert clause, they match the SELECT statement, yet, I get the error above stating that there is somehow a disconnect.  Any ideas as to what I am doing wrong?

Remove parens around column list in select. This doesn't do what you think it does.


MongoDB Too many open file while reSync member I get the problem in many past days. I have MongoDB cluster with 3 shards and 3 replicas.

    1   2   3
A   O   S   S
B   S   P   S
C   P   O   P

P - Primary state
S - Secoundary state
O - Other state

Alphabets are replica machine and numberic are shards.

I try to resync all data around 2TB in mongod machine with other state (A1 and C2). But I got error while resync data in primary that mongod service going down because Too many open file

2019-03-16T16:35:22.351+0000 E -        [conn28204] cannot open /dev/urandom Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system
2019-03-16T16:35:22.362+0000 I NETWORK  [listener] Error accepting new connection on 0.0.0.0:27017: Too many open files in system

I already try to fixed ulimit as mongodb recommendation.

> ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 241518
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 64000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 241518
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

and set in /etc/security/limits.conf like this

*            soft    nofile      64000
*            hard    nofile      64000
root         soft    nofile      64000
root         hard    nofile      64000

but all of this is not fix my problem. I still get mongod service down because Too many open file. I stuck for 3 days. Anyone have any ideas or solutions?

Since you have 2TB of data to be resynced, try the following approach.

Follow the below approach for each replica set as required.

Connect to the Primary and back up the database using mongodump, also take the latest document oplog collection.
Shutdown the secondary where you want to resync the data.
Start this secondary as standalone server
Drop the oplog collection in the local database and recreate it by inserting the latest entry from the primary node.
restore the backup data taken from step 1 using mongorestore
Once the restoration is completed, shut down and restart the server as replica set member.


MySQL single large table vs many small tables I'm building a website to monitor a bunch of IOT devices. E.g. Online/Offline status of each devices and some device specific information it may report back, IP address, Temperature etc this will vary. FYI These devices report back to my site via a processor/computer that poles these devices and then reports back (a maximum of 255 devices but in most cases between 10 - 100 devices).

To date, my approach had been that for each processor I would create a new table with just that processors devices would reside within. However in discussions with a colleague he suggested this might not be the best way to go, as it isn't particularly efficient and could be problematic later on e.g. if you wanted to add another column later on, having to add this to possible 50+ different processor tables etc.

Instead because all these tables would have the same structure e.g. identical amount of columns etc just the amount of devices e.g. rows would vary, would one big table with all these rows was a better way to go?

I know that in MySQL terms "scanning" is an expensive operation, and with one big table I would argue there would be more scanning as I would have to filter as I would have to take one big data set each time, and filter it down into a view, e.g. Processor or location against 5000+ rows vs lots of smaller tables of 100 rows. Also I would argue the data in this table would be written to allot e.g. each time a device goes offline the offline flag is updated, so I'm not sure if that makes it more suitable to a single table vs one large table.

Appropriate there's many different ways of approaching this, I just don't want to go down one rabbit hole and regret it later on. Front end will be PHP if that counts for anything.

*Originally posted on stack overflow put reposted here as the question warranted “opinion based answers”.

one big table with all these rows was a better way to go?

Yes, absolutely

With proper table structures and indexing, MySQL will comfortably handle millions of rows in any table.  The "Table-per-?" model is never a Good Idea and invariably comes back to haunt you (usually when you need to summarise across all those tables).


In PostgreSQL, what area in memory does HashSetOp use, work_mem or shared_buffer? I want to know the difference between the hash table in a hash join and the hash table in HashSetOp Except.
Here is the PostgreSQL script I wrote to explain my question.
drop table if exists t1 cascade;
drop table if exists t2 cascade;
create table t1
as
select trunc((i-1)/15) n1,
       trunc((i-1)/15) n2,
       rpad(i::text,180)::text    v2
  from generate_series(1,30000) a(i);
create table t2 as select * from t1;

update t1 set v2 = 'kk' where v2 like '42%';
vacuum t1;
vacuum t2;

Below is the query I executed followed by its execution plan
select  * from t1
except
select * from t2;

**HashSetOp Except** (actual time=32.602..33.485 rows=11 loops=1)
   Buffers: shared hit=1716
   ->  Append (actual time=0.014..15.065 rows=60000 loops=1)
         Buffers: shared hit=1716
         ->  Subquery Scan on "*SELECT* 1" (actual time=0.013..6.388 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t1 (actual time=0.010..2.801 rows=30000 loops=1)
                     Buffers: shared hit=858
         ->  Subquery Scan on "*SELECT* 2" (actual time=0.011..5.596 rows=30000 loops=1)
               Buffers: shared hit=858
               ->  Seq Scan on t2 (actual time=0.009..1.590 rows=30000 loops=1)
                     Buffers: shared hit=858
 Planning:
   Buffers: shared hit=40
 Planning Time: 0.230 ms
 Execution Time: 34.567 ms

Below is another query with the same data followed by its execution plan.
select n1, n2, v2
  from t1
 where not exists (select 1
                     from t2
                    where t1.n1 = t2.n1
                      and t1.n2 = t2.n2
                      and t1.v2 = t2.v2)
  Hash Anti Join (actual time=12.733..35.390 rows=111 loops=1)
   Hash Cond: ((t1.n1 = t2.n1) AND (t1.n2 = t2.n2) AND (t1.v2 = t2.v2))
   Buffers: shared hit=1717, temp read=800 written=800
   ->  Seq Scan on t1 (actual time=0.008..1.985 rows=30000 loops=1)
         Buffers: shared hit=858
   ->  Hash (actual time=12.455..12.455 rows=30000 loops=1)
         Buckets: 32768  Batches: 2  Memory Usage: 3679kB
         Buffers: shared hit=858, temp written=400
         ->  Seq Scan on t2 (actual time=0.003..2.125 rows=30000 loops=1)
               Buffers: shared hit=858
 Planning:
   Buffers: shared hit=33
 Planning Time: 0.484 ms
 Execution Time: 35.457 ms

My question is :

I know that both the hash join and HashSetOp Except above use a hash table.
While the hash table in the first query overflowed work_mem, it seems that HashSetOp Expert operation didn't overflow work_mem (I can't see any indication that it used temp files in the execution plan.)
Do they use different memory areas?
If both the hash join and the HashSetOp Except use the same memory area, how does the HashSetOp Except operation consume smaller area?
I would appreciate it if anyone could give me a clue or answer.

work_mem is not an "area", it is a just value.
HashSetOp is pretty poor at memory estimation and usage.  It has no provision for spilling to disk. If it ends up using more memory than work_mem is set to, then it just goes ahead and uses more memory than it was supposed to.  There is no external enforcement mechanism (until the kernel steps in), and in the case of HashSetOp no internal one either.
If the planner thinks ahead of time that HashSetOp would overrun work_mem, then it just doesn't get chosen, and you will probably get some sort-based method instead.  But again, the estimation is poor, it will often use several times more memory than the planner thought it would, and will happily blow way past the setting of work_mem.
HashSetOp could really use some improvements, but it is so seldom used that no one is very excited about doing the work.


How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the system. I assumed that this was caused because the user was not in the system, but looking in the users database of MySQL the user is listed correct with the password.

The dump was from an earlier version of MySQL (5.0) into the latest one that comes with Xamp 5.527. At first I thought it could be that the latest version was incompatible with the existing one.

Looking on this forum, there are some existing answers covering this problem that refer to making changes to the database and then dumping it again.

However, my on line database works fine, so I dare not make changes to it in case I kill it. That is assuming that I understood what changes I should make and how to do it. I have PHPmyadmin. Can I modify it on the existing dumped database,or do I have to change it on the on line one and then export it?

And exactly what do I need to do to modify it. The original database was a Microsoft Access one, converted to MySQL, and it has worked without error since 2003.

If you will recreate all your views this way (and will create all new ones with this syntax):

CREATE
SQL SECURITY INVOKER
    VIEW applications
AS
SELECT id, product_id
FROM modules     WHERE item_type='application';

you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and SELECT on tables involved.

Typical database user in a web application will have SELECT, INSERT, UPDATE, DELETE, EXECUTE until your web application creates database objects.

You do not need 'fred' to be a database user anywhere in this case even though this database user created this view on your web site.


Possible to get comprehensive view on all defined names in PostgreSQL? The SQL standard prescribes a rather large vocabulary; on top of this, RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing. On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot. Now, when I want to, say, create a function, a table or a column I have to name it. Some names (like all I think) are no-go, some names (for some kinds of objects?) are OK even though they clash (b/c they are not ambiguous). Good names clearly should be not confusing. But how can I get an exhaustive list of all the names that a given PostgreSQL DB instance 'knows' at any given point in time?

FWIW I scanned to Postgres docs for lists, but they don't seem to have any; instead, there's a list of SQL commands that does not list e.g. order and by (b/c that's subsumed under select); I'd have to peruse all the pages of Chapter 9. Functions and Operators to compile a list of all the function names. Not to speak of extensions, which by default put their stuff into the public schema.

Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list? I know there's psql's \d+ and so on, and I already have a small collection of utility views to provide subsets of what I'm looking for, but nothing resembling a Grand Inclusive List of Everything.

RDBMSs implement their own constructs. Then they add hundreds of function names, internal and user-facing.

All of them for PostgreSQL are defined in the schema pg_catalog. You can access them with the S argument in psql. For example \dfS for show all system functions.

  On top of that, users go and define schema(ta)s, tables, indexes, custom functions, types, whatnot.

You can never know what users create. You'll have to check that out for each install by querying the catalog.

  Is there a truly comprehensive way to list all these 'defined words' complete with their roles, origins, comments etc. in a single huge list?

I'm not 100% sure it's what you want, but perhaps you're looking for the Reserved Keyword list?

Update

  "You'll have to check that out for each install by querying the catalog."—exactly, can you tell me such a query? – John Frazer Jun 17 at 8:03

Sure, so what you want to do is run psql -E, then run \?. This gives you the far more than what you've asked for (viz. list tables, views, and sequences , aggregates , access methods , tablespaces , conversions , casts , default privileges , domains , foreign tables , foreign servers , user mappings , foreign-data wrappers , functions , text search configurations , text search dictionaries , text search parsers , text search templates , roles , indexes , large objects, same as \lo_list , procedural languages , materialized views , schemas , operators , collations , table, view, and sequence access privileges , sequences , tables , data types , roles , views , foreign tables , extensions , event triggers , databases). Next pick one of them, like \df which lists functions,

You'll get a query like

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;

This is internally the query that psql uses to get this information. You can cut it up though if you only want the name and don't care about whether or not the function is an aggregate, window, or trigger; and, you can cut it further if you don't care about the result and arguments types.

SELECT n.nspname as "Schema",
  p.proname as "Name"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2;

Etc., or of course you can query the slower information_schema interface which is part of the SQL spec and standardized.

SELECT routine_catalog, routine_schema, routine_name
FROM information_schema.routines
WHERE routine_schema NOT IN ('pg_catalog', 'information_schema');


SQL Server Temporary Table Security I am part of a project to convert an old AS400 system to SQL server.  The server is used for our payroll processing.  Part of the payroll process is to break out the percentage of the biweekly wages to each job we work on.  To do so in the AS400 process a csv file was loaded from the P&B admin's to the server in a data access area that only the admin had access to.  I need to replicate this in the SQL server environment.  I am planning on using SQLCMD to execute an import of the local file into a temporary table.  I know it is highly unlikely that anyone would query the data in the temporary table at the time its in use but is there anyway to grant only access to the user that executed the SQLCMD script?

A #temporary table is only visible from the session that created it.  So even the same user in another session would not be able to see it.  If you load a temporary table in SQLCMD it would be visible to that instance of SQLCMD.  So a subsequent command could load the data into a permanent table, or open a cursor over it and call a stored procedure for each row, etc.

Alternatively you could provision a private schema for the user and load the data into a permanent table there.  Then no other user (other than a database owner or other privileged user) would be able to read it.  The user could then move the data into another schema later.


PostgreSQL to MS Access Is it possible to create a database in PostgreSQL move it into other computer and use this database with MS Access? How to do that?

In your situation, I would just spin up a PostgreSQL database somewhere and have them connect to it in Microsoft Access via ODBC.

http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html

And I would keep Access and its limitations and tradeoffs in mind while designing my PostgreSQL database.

Advanced features will require you writing pass-through queries to leverage PostgreSQL features directly, but they don't play at all with Access tables, and if you're going to be doing any updates, you're going to need custom code because pass-through queries are read-only.

I would definitely familiarize myself with Access as well:

https://msdn.microsoft.com/en-us/library/Bb221186%28v=office.12%29.aspx

and go through some tutorials:

http://www.gcflearnfree.org/access2013

Actually translating the PostgreSQL database to Microsoft Access would likely be a long, tedious process fraught with pain and peril as has been said, and is not recommended.


Using cursor to update if exists and insert if not I am trying to write a cursor. I have created a table for capturing login name and time from sys.dm_exec_sessions. Now, I need to write a cursor to update the login time to last login time if the login already exists and insert a row in case login does not exists in my table. I have come up with following, but unfortunately I am getting an error with subquery returning more than one value for it. Any ideas???

declare @log as varchar(200)
declare @log_time as datetime
declare LoginsSize cursor for
        SELECT  login_name, login_time
        FROM sys.dm_exec_sessions
open LoginsSize
            fetch next from LoginsSize into @log, @log_time
            while( @@fetch_status = 0)
        begin
          If (Select Login from [dbo].[LoginsForDBUserList])  = @log

             Begin
             UPDATE  [dbo].[LoginsForDBUserList]
             SET     LastLoginTime = @log_time
             WHERE   [login]= @log
             END

             Else

             Begin
             Insert Into [dbo].[LoginsForDBUserList]
             SELECT  login_name, login_time
             FROM sys.dm_exec_sessions
             END

        fetch next from LoginsSize into @log, @log_time
        close LoginsSize
        deallocate LoginsSize

end

I think that error is being generated because of this line

If (Select Login from [dbo].[LoginsForDBUserList])  = @log

Multiple rows are being returned from the left side of the equal sign and is attempting to be compared to the single value @log.

I've made some adjustments to your original query and it works for me.  Give this a try:

--demo setup
DROP TABLE IF EXISTS LoginsForDBUserList
GO
CREATE TABLE LoginsForDBUserList (
    LOGIN VARCHAR(200)
    ,LastLoginTime DATETIME
    )

--Adjustments to your original process
DECLARE @log AS VARCHAR(200)
DECLARE @log_time AS DATETIME

DECLARE LoginsSize CURSOR
FOR
SELECT login_name
    ,login_time
FROM sys.dm_exec_sessions

OPEN LoginsSize

FETCH NEXT
FROM LoginsSize
INTO @log
    ,@log_time

WHILE (@@fetch_status = 0)
BEGIN
    IF EXISTS (
            SELECT LOGIN
            FROM LoginsForDBUserList
            WHERE LOGIN = @log
            )
    BEGIN
        UPDATE LoginsForDBUserList
        SET LastLoginTime = @log_time
        WHERE [login] = @log
    END
    ELSE
    BEGIN
        INSERT INTO LoginsForDBUserList
        SELECT login_name
            ,login_time
        FROM sys.dm_exec_sessions
    END

    FETCH NEXT
    FROM LoginsSize
    INTO @log
        ,@log_time
END

CLOSE LoginsSize

DEALLOCATE LoginsSize

SELECT *
FROM LoginsForDBUserList


Automatically replicate table data into another table Is there a way to automatically replicate the table into another table where in if table A has changes, then it will also change the data in the 2nd table.

You  can use triggers or replication,but replication will be a overkill for a single table..See below example for more details

Changes in Table A,should replicate to table B

CREATE TABLE [dbo].[a](
    [id] [int] NULL,
    [name] [char](2) NULL
)

CREATE TABLE [dbo].[b](
    [id] [int] NULL,
    [name] [char](2) NULL
)

Now create trigger which replicates only data changes

ALTER trigger [dbo].[trg_test]
on [dbo].[a]
for insert,update,delete
as

begin

set nocount on

--updated
if exists(select 1 from inserted) and exists(select 1 from deleted)
begin

update b
set b.id=i.id,
    b.name=i.name
    from b
    join
    inserted i
    on b.id<>i.id and b.name<>i.name
    join deleted d
    on b.id=d.id and b.name=d.name
return

end

--inserted
if exists(select 1 from inserted)
begin

insert into b
select * from inserted
end

--deleted
if exists(select 1 from deleted)
begin

delete b
from
deleted d
join
b
on b.id=d.id

end

end

now do some random datachanges

--inserts

    insert into a
    select 1,'d'
    union all
    select 3,'c'

--updates
update
a
set id=4

--deletes
delete from a

you can check at each stage to see data replication to b


Slow remote SELECT statement due to long "client processing time", but fast locally While connected to our production server (SQL Server 2008, very powerful machine), this SELECT statement takes 2 seconds, spitting back all fields (4 MB of data in total).

SELECT TOP (30000) *
FROM person
WITH(NOLOCK);

From any other box on the same network (connecting using SQL authentication or Windows Authentication), the same query takes 1 minute, 8 seconds.

I am testing with this very simple statement to illustrate that it's not an indexing problem or query-related problem.  (We have performance issues with all queries at the moment...)

The rows come in chunks, and not all at once.  I get my first rows instantly, and then wait for over 1 minute for the batches of rows to come in.

Here are the Client Statistics of the query, when it is ran from the remote box:

Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0
  Rows affected by INSERT, DELETE, or UPDATE statements 0
  Number of SELECT statements  2
  Rows returned by SELECT statements 30001
  Number of transactions 0

Network Statistics
  Number of server roundtrips 3
  TDS packets sent from client        3
  TDS packets received from server 1216
  Bytes sent from client         266
  Bytes received from server 4019800

Time Statistics
  Client processing time 72441 ms (72 seconds)
  Total execution time   72441 ms
  Wait time on server replies 0

We can see that the "Client Processing Time" is equal to the total execution time.

Does anyone know what steps I can take to diagnose why the transfer of the actual data is taking a long time?

Is there an SQL configuration parameter that restricts or limits data transfer speed between machines?

This issue is now resolved.

It was a network problem, and the SQL box was using a 100 MB/s NIC card, instead of a 10 GB/s NIC card...

A network configuration change to use the correct network card has fixed the problem.  Now we are getting similar performance for all queries from the Production SQL box and from other boxes on the network.

Thanks everyone for your help.


sp_whoisactive time columns I am looking at data captured with the help of sp_whoisactive.

session_id: 1016
blocked_session_count: 24
Login_time : 29/03/2018  12:14:59
tran_start_time : 29/03/2018  12:14:59
start_time: 29/03/2018  12:15:59
status: sleeping
open_tran_count :1

The login_time and tran_start_time are the same for this session_id.
But start_time is after a minute.

Does this mean the session entered sleeping state @ 29/03/2018  12:15:59 ??

Thank you :)

[tran_start_time] - Date and time that the first transaction opened by a session caused a transaction log write to occur.

[start_time] -  For active requests, shows the time the request started |   For sleeping sessions, shows the time the last batch completed

[login_time] -  Shows the time that the session connected


What is effective way to delete duplicate records based on multiple columns in MySQL? I have a table named users having approx 70 Million records, out of which I guess 10 Million is duplicate. How to remove those 10 Million records keeping one copy of that.  Two records are said duplicate if both have same email, alt_email, mobile and alt_mobile.I searched on stackexchange, but couldn't found any solution effective with such a huge data. Any help would be appreciated. My table is in MySQL 5.5.45 with InnoDB engine.

This is the users table. Apart from mentioned columns, I have few more columns but the mentioned ones are relevant to the question.

CREATE TABLE users
(
  id int(11) NOT NULL AUTO_INCREMENT,
  email varchar(200) DEFAULT NULL,
  alt_email varchar(200) DEFAULT NULL,
  mobile varchar(100) DEFAULT NULL,
  alt_mobile varchar(100) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY mobile (mobile),
  KEY email (email)
) ENGINE=InnoDB;

Consider enforcing this combination of columns as a unique constraint at the database level. That won't be possible until the existing duplicates are cleaned out. But it will help prevent this problem from creeping back into the data.

You should define the 4 columns as a non-unique index now since this process will involving lots of comparisons using them. You should define the 4 columns as a single index listing them in the same order you plan to list them in your query. 70M records is gonna take a long time to query. But with an index on the column in question it may drop to a feasible time frame. And you may end up needing to use FORCE INDEX anyway.

https://www.percona.com/blog/2014/01/03/multiple-column-index-vs-multiple-indexes-with-mysql-56/

Is there a preferred strategy in terms of picking the version of the record to keep? Assuming you want to keep the most recent on file, for example, you can use a date column (I'll call it cdate) or the value of an auto-increment column (I'll call it id).

You may want to create a new table users_test or something similar using an otherwise unchanged CREATE TABLE statement. Then shunt over a more manageable portion of your data to experiment on there. Especially if you already know a few of the duplicate records so that you can be sure to include them. Then you can start out with something like this:

SELECT
    t1.id,
    t2.id,
    t1.cdate,
    t2.cdate,
    t1.email,
    t2.email,
    t1.alt_email,
    t2.alt_email,
    t1.mobile,
    t2.mobile,
    t1.alt_mobile,
    t2.alt_mobile
    # Include whatever other columns may be helpful
FROM
    # Or users_test if it exists
    users t1
    JOIN users t2 ON (
        # We want t2 to always contain a duplicate but older record
        t1.cdate > t2.cdate
        AND
        t1.email = t2.email
        AND
        t1.alt_email = t2.alt_email
        AND
        t1.mobile = t2.mobile
        AND
        t1.alt_mobile = t2.alt_mobile
    )
;

Sanity check the results. You want to make damn sure your data matches your expectations before running a DELETE statement in production. But once you are happy with the results you can safely collect t2.id values from that query. Those will all be records with a newer duplicate row.

If you don't have a date to compare use id. If you would rather keep the oldest record on file switch the > to an <. Or you may have a completely different strategy in mind for picking the version of the record to keep. That's left as an exercise for the reader.

Adding an index on these columns will slow down writes. So if it is vitally important that writes to the users table in production are super fast then you may not be able to define the index (unique or not) in production. But with this method you can still dump the whole thing to a staging environment, set up the index there, collect the IDs, and then use just the ID list in DELETE statements in production.

DELETE FROM users WHERE id in (/* list of t2.id values here */);

Personally I find slightly slower writes a small price to pay for the potential read boost and data sanitation that comes along with defining natural keys as UNIQUE constraints. But I've also never dealt with a project with 70M rows in the users table. My experiences may not mean much for your particular situation.

If this proves unworkable I can think of two other strategies worth investigating.

Use a cloned table similar to user_test discussed above. Move some much smaller subset of user records into it. Join the two different tables rather than the self join on users. Comparing 10k rows to 70M rows should be much faster than comparing 70M to 70M. But checking all the records will require running 7,000+ iterations. You'd want to use the full users table in the t2 position. And you could potentially pick up some optimizations depending on how you chunk your rows into the table for comparison.

Or you could rename your existing users table to something like users_old. Then you can create a new users table with the unique constraint in place across those 4 columns. Then use a combination of INSERT INTO … SELECT … and INSERT … ON DUPLICATE KEY UPDATE … statements to populate the new table using values from the old table.

INSERT INTO users
SELECT * FROM users_old
ON DUPLICATE KEY UPDATE
some_column = VALUES(some_column),
some_other_column = VALUES(some_other_column), …
;

You can leave the columns from the UNIQUE constraint out of your UPDATE clause because you know they wouldn't change anyway. And this strategy goes out the window is enforcing a UNIQUE constraint in production isn't an option for you.


Good practice for hiding primary keys from a client Let's say I have a table like this:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Later on in my client, I may fetch a RESTful endpoint like this:

GET /v1/users/1

The query to retrieve the users would look like this:

SELECT * FROM user WHERE id = 1;

I want to avoid the approaches above so I can hide primary keys from user-facing web apps.

I thought I could do something like this instead to solve that problem:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    _id VARCHAR(20) NOT NULL UNIQUE
);

The HTTP call now becomes something like this:

GET /v1/users/a129fjf24f0jwef94f

...and the query looks like this:

SELECT * FROM user WHERE _id = a129fjf24f0jwef94f;

What are the downsides to such an implementation at the database level, if any, other than excess storage? I would also have to use the _id on queries where I involve joins of other tables, etc.

Plan A:

Your basic is probably good.

_id could be computed as MD5(CONCAT('salt', id)).  'salt' is some secret string; it's purpose is to make it harder for a hacker to discover your mapping between id and _id.

An MD5 can be stored in CHAR(32) CHARACTER SET ascii or, via UNHEX(...) in BINARY(16) -- 32 or 16 bytes, respectively.

Since that is 32 hex digits, you may have an issue with such a long string.  If so, then you could use some shorter 1-way hash, but that increases the risk of an accidental dup.  At that point, you would need to take advantage of the UNIQUE index, toss the id, and move on to the next.

Plan B:

Create a table with all the numbers from, say, 100,000 to 999,999.  This would give you 900K 6-digit numbers.  Sort them (ORDER BY RAND()) and add an AUTO_INCREMENT id.  Now you have a mapping that is unique, and limited to 6 characters.  (Variants would involve hex or base64 or all letters.  But watch out for "4-letter words" buried in strings.)  (MariaDB has an easy way to build such a "sequence" table.)

Plan C:

When you need a new _id, compute RIGHT(RAND(), 6), then verify that it is not a dup.  (A simple Stored Function can do that task.)  (Caution: When you get close to a million, the function will bog down due to dups.)  6 limits you to 1M numbers; increase the 6 to get more.

Minor bug:  It is possible to get a decimal point; any of these would be safer:

REPLACE(RIGHT(RAND(), 6), '.', '')
FLOOR(1000000*RAND())
100000 + FLOOR(900000*RAND())  -- always 6 digits long


MySQL Double many-to-many on an Entity I have a project where I have Products and SKU's. Each Product has one or many SKU's.

I also have Shops that can have many to many Products, So a Product can belong to multiple Shops and a Shop can have multiple Products.

To finish, each Shop can choose which SKU's they want for which Products.
So I as a Shop can choose to sell the Tshirt White, which may have (XS,S,M,L) and I can choose (XS,S) as SKU's I want for my Shop.

Currently

As you can see A Shop is connected to the Products, and to the SKU's. But I have a feeling there's something wrong

  My shop_skus and shop_products are not connected, So there can be a mismatch between the products a shop has, and the sku's..

How would I go about fixing that?

Thank you for your time!

Edit: I didn't notice that this has been suggested already in comments by ypercube. So crediting him here.

Ultimately its up to you and your exact requirements. But here are following suggestions:

SKUs does not exists if Products does not exist. In other words, you cannot make a SKU as a standalone entity. The basic entity therefore is Products. And as a Product can have multiple SKU, make this relationship many to many.
So if you follow point 1, You can safely remove shop_skus relationship because now, a shop can select products via shop_products and as products are in many-many relationship with SKU you can enable shop to pick suitable SKU directly using suitable query.


Syncing complete database with server I am developing Point of sales software, which have multiple branches and one main branch.
Main branch is web based while all other branches (9 now, which may exceed) are desktop based.
Client required that all branches must sync main branch whenever internet connectivity is available.
So my question is that which sync functionality should i use?
I have not much idea about syncing as never work before on syncing.
Some of basic requirements are as follow:
Main branch can purchase or sell any item for any branch and stock + accounts will be hit.
Other branches can purchase and sale for them not for other branches but they can see other branches stock, their customers and accounts.
Remote branch can move stock to any branch.
These are some basic requirements which may change according to clients wish.
So kindly guide me how can i sync complete data bases. Should i use my own logic or some builtin functions.
Thanks

Merge replication (link) might be a suitable solution for synchronising your data. Controlling the access so that your branches can only edit certain parts of the data should be controlled within your application.


Databse design for a part/subassembly list We work with tracking the fabrication of large assemblies and sub-assemblies with a large flat Excel files. Usually separate spreadsheets for estimating material costs, fabrication costs, keeping track of submittals etc.

I've done some basic work in MS-Access over the years-enough to know there's probably a better way to manage this data--and have thought of developing a tool in Access.  The few times I've sat down to diagram out what the different tables would look like and how they would relate I get stumped by how to treat parts and subbassemblies.  To borrow the diagram from this question. The data could look like this:

Assembly (id:1)
    |
    |-Part 1: Rivet
    |-Part 2: Rivet
    |-Group 1: SubAssembly
    |   |
    |   |-Part 1: Rivet
    |   |-Part 2: Bolt
    |   |-Part 3: Bolt
    |   |-Group 1: SubSubAssembly
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-Group 2: SubAssembly
       |-Rivet
       |-Bolt

There could be parts or subassemblies (a collection of parts or more assemblies) at each level.  Practicalities aside, there's no limit to the number of levels.

The best I could think of (that doesn't involve hard coding some set max number of levels) is to have a flat table with a field listing the next higher level and somehow sort and organize on that.  That is similar to what appears on the drawing-a box labeled "Next higher level" that may contain another drawing number.  That seems to be counter to the point of using a relational database though.  I'm not looking to develop a central repository of all data, just a desktop tool that I can use to manage my data and if it proves to be useful, share with my co-workers.

What is the proper way to organize this data?

To reproduce this structure, you need an Assembly table referencing itself through a ParentAssemblyID and a Part table. The diagram in Access looks like this:

Note that the assembly table appears twice in this diagram. But there's really only one unique table named Assembly in the DB.

AssemblyID and PartID must be declared as primary key (PK). Its easiest to declare them as AutoNumber column.

In the Realtionships diagram, insert the Assembly table twice, so that you can draw a relation between them. Right click the relation lines and check the boxes "Enforce Referential Integrity" and "Cascade Delete Related Records".

Your example data would have to be entered like this

Assembly
-----------------------------------------------------
AssemblyID  Assembly                 ParentAssemblyID
----------  -----------------------  ----------------
  1         Main Assembly
  2         Group 1: SubAssembly        1
  3         Group 2: SubAssembly        1
  4         Group 1: SubSubAssembly     2
-----------------------------------------------------

Part
---------------------------------
PartID  AssemblyID  Part
------  ----------  -------------
  1       1         Part 1: Rivet
  2       1         Part 2: Rivet
  3       2         Part 1: Rivet
  4       2         Part 2: Bolt
  5       2         Part 3: Bolt
  6       4         Rivet
  7       4         Rivet
  8       3         Rivet
  9       3         Bolt
---------------------------------

Answer to follow-up question: "How would I structure it if I wanted the same part to be usable in many assemblies?"


How to connect to specific session id & serial# ? Blocked sessions I have blocked sessions and see which session is blocking them, having session_id and serial#. Is it possible to connect to such session? How?

Background: using V$SESSION_BLOCKERS I see sessions are blocked by each other by INSERT statements to one of two tables: _REQUEST and _RESPONSE

Blocked sessions wait event: enq: TX - row lock contention

Query 1:

insert into _REQUEST (creation_date, IS_PROCESSED, name, packet, PARENT_SKID, BATCH_SKID, retry_delay, revision, SERVICE_NAME, ttl, type, REQUEST_SKID)
values (:1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 , :11 , :12 )

Query 2a (from DB trigger, :B1 is a payload):

SELECT COALESCE(ERROR_DETAILS, ERROR_MESSAGE, FAULT_REASON, SOAP_ERROR) AS ERROR_MESSAGE, ERROR_CODE
FROM XMLTABLE( '//*:DataHandlerWebServiceException | //*:Fault | //*:PLMDataHandlerError'
     PASSING XMLTYPE(:B1 )
     COLUMNS ERROR_DETAILS VARCHAR2(256) PATH '*:errorDetails'
            ,ERROR_MESSAGE VARCHAR2(256) PATH '*:errorMessage'
            ,ERROR_CODE VARCHAR2(8) PATH '*:errorCode'
            ,FAULT_REASON VARCHAR2(256) PATH '*:Reason/*:Text'
            ,SOAP_ERROR VARCHAR2(256) PATH '*:COMMON_LOG_MESSAGE/*:LOG_MESSAGE' )

Query 2b (we already know APPEND is ignored here, see Note 1):

INSERT /*+ append */ INTO _response( RESPONSE_SKID, REQUEST_SKID, HAS_FAILED, WAS_TIMEOUT, IS_PROCESSED, SOAP_REQUEST_TIMESTAMP, SOAP_RESPONSE_TIMESTAMP, RESPONSE_HTTP_STATUS, ERROR_MESSAGE, RESPONSE_BODY )
values ( :1 , :2 , :3 , :4 , :5 , :6 , :7 , :8 , :9 , :10 )

How is it possible that INSERTs, to different tables, are blocking the sessions? I was looking into V$ACTIVE_SESSION_HISTORY but don't find anything yet (I'm not a DBA yet)

Note 1: APPEND hint ignored

That will not happen. You can kill a session, or cancel the SQL it is currently executing from another session, but you can not end its transaction (with leaving the session alive). Usually such blocking sessions are just simply killed.

For your other question: blocking another transaction in another session that inserts to another table may be blocked through foreign keys. Example:

create table request(c1 number(10, 0) not null primary key);
create table response (c1 number(10, 0) not null,
                       request_c1 number(10, 0) not null references request(c1));

Session 1:

SQL> insert into request values(1);

1 row created.

No commit yet, session 1 is waiting. In session 2:

SQL> insert into response values(1, 1);

And session 2 hangs there until session 1 performs a commit or rollback.


Trigger or rule to convert all strings to lower case I have about 26 applications interacting with our database, some of these will store email addresses in mixed case. Since I made the mistake of using varchar as the column type and not a case insensitive one, I would like to make some kind of trigger or rule, which will convert all attempts to save email addresses as mixed case, into lower case. I could achieve the same using the following query:

ALTER TABLE your_table
ADD CONSTRAINT your_table_the_column_lowercase_ck
CHECK (the_column = lower(the_column));

But that would throw error messages in all the applications, and potentially stopping new customers from registering. Therefore, I am looking for this auto-converting rule/trigger which will handle any updates or inserts into this table. Is it possible to do this at all, so it won't come back and bite me later?

Because we're talking about email addresses you should first read my answer to the question, What is the best way to store an email address in PostgreSQL?. I would adopt that method. It's not too late.

That said, the code above is certainly not ideal. You should instead just migrate to citext. This overrides the = to essentially be lower(a) = lower(b)

ALTER TABLE your_table
  ALTER COLUMN the_column
  SET DATA TYPE citext;


mysql error connecting to the server did not ask for password during the installation process I wanted to learn MySQL, so I decided to download it from https://dev.mysql.com/downloads/installer/. However, during the installation it did not ask me for any password, now I cannot connect to the server as it needs a password. I have tried reinstalling it but it still does not ask for the password. Can somebody tell what the problem is?

It happens to me several times, if you want to solve this download the full package installer. The second installer from the link you posted. I think that will save you from unnecessary headache.
I hope I’m able to help?


Oracle: changing a global temporary table to a normal table and vice versa Our code make use of a global temporary table which the option 'ON COMMIT DELETE ROWS'.  Works well and does the job as expected.  But when running my automated tests, i would like the rows to persist, during the session and why not, after the session ended.

Is it possible to alter the table to become a normal table before my test starts and revert it back to a temporary table after the test?

My test suite could drop and recreate the table, but that requires my test suite to know the DDL of the table.  I want to avoid this.  My testing code only needs to fetch one column.  If with time, columns are added, options are changed, my testing code will endure the changes if it can only alter the nature of the table.

No, you cannot change from GTT to a normal table.  But, you could create another table to copy your sessions specific data to in order to preserve the information.

More on GTT's


Using LIKE to search case insensitive over language specific characters (PostgreSQL) Using a PostgreSQL database (Collation C, Encoding UTF8) we store data from various languages. This means we also get the special characters for free (e.g. Å, å, ...)

Within an application we now want to perform a case-insensitive search.  Meaning if the user types 'Å' in the search box, the query must also take into account 'å'.
However, using the lower(string) or upper(string) method completely ignores this values when casting.  And as such, no matching records are found...

Is there any way around this?
Using regex it seems one could use \p{L} to compare values, but I need it at the query level so I guess that's a no-go.

On a side-note: we are using Hibernate to create the query.

unaccent

The traditional, non-PG 10 method of this is to the extension unaccent.

CREATE EXTENSION unaccent;
SELECT 'a' = lower(unaccent('Å'));

You jam this into a TEXT SEARCH CONFIGURATION

WITH unaccent, french_stem;

And then magically all of your fts stuff works as English speakers desire.


Locking and checkout mechanism Tables:

Clients (20+ fields)
  Id int,
  Name varchar,
  [State] varchar,
  Address varhcar

ClientCheckout
  ClientId
  CheckedOutAt Datetime null

I'm using a separate table for managing the checkout process to avoid putting any locks on the Clients table.

I need to ensure that a client can't be checked out more than once.  Once it is checked out I need to return the id of the client that was checked out.

This is what I came up with but I feel like there should be a better way

Procedure:

Create procedure (@state varchar(2))
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE @clientid INT

SELECT TOP 1
    @clientid = CustomerId
FROM ClientCheckout
WHERE CheckedOutAt IS NULL AND [state] = @state

BEGIN TRANSACTION T

    UPDATE ClientCheckout
    SET CheckedOutAt = getdate()
    WHERE ClientId = @clientid;
commit transaction t
SELECT
@clientid

I left the initial selection of @clientid outside of the transaction so as not to lock the Clients table.

This seems to work but it doesn't feel quite right.  Suggestions?

You can combine the select and update into a single statement that runs atomically, by doing something like:

CREATE TABLE dbo.Clients
(
    ClientID INT NOT NULL
        CONSTRAINT PK_Clients
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ClientState VARCHAR(255)
);

CREATE TABLE dbo.CallCheckout
(
    ClientID INT NOT NULL
    , CheckedOutAt DATETIME
);

INSERT INTO dbo.Clients(ClientState)
VALUES ('AA')
    , ('AB')
    , ('AC');

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (1, NULL);

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (2, GETDATE());

INSERT INTO dbo.CallCheckout (ClientId, CheckedOutAt)
VALUES (3, NULL);
GO

CREATE PROCEDURE dbo.CheckOut
(
    @state VARCHAR(3)
)
AS
BEGIN
    UPDATE TOP(1) dbo.CallCheckout
    SET CheckedOutAt = getdate()
    OUTPUT inserted.clientid
    FROM dbo.CallCheckout cc
        INNER JOIN dbo.Clients c ON cc.ClientID = c.ClientID
    WHERE cc.CheckedOutAt IS NULL
        AND c.ClientState = @state;
END
GO

EXEC dbo.CheckOut 'AA';
EXEC dbo.CheckOut 'AC';

The results are:

Notice, I've specified the schema in the CREATE PROCEDURE and UPDATE statements.  You should always specify the schema, even if you only ever use dbo, since it prevents the server from having to make that decision, and potentially making the wrong decision.

Also, please do not use reserved keywords for column names.  I'm talking about [state].  Notice, I've named that column CheckoutState which is not a reserved keyword, and makes the column name more meaningful.


How can I prove / disprove If A ↠ BC and A → B then A → C / From GATE 2007 IT:

  Consider the following implications relating to functional and multi
  valued dependencies given below, which may or may not be correct.

A) If A ↠ B and A ↠ C then A → BC
B) If A → B and A → C then A ↠ BC
C) If A ↠ BC and A → B then A → C
D) If A → BC and A → B then A ↠ C

  Exactly how many of the above implications are valid?

  A)    0 B)    1 C)    2 D)    3

I've just studied 4th Normal Form & Multi Valued Dependencies & I'm struggling to apply inference rules for multi Valued dependencies.

I've searched online though I've not found any resource on it.

I tried:

A) If A ↠ B and A ↠ C then A → BC.  This is incorrect. It is very obvious to see.

B) If A → B and A → C then A ↠ BC. This can be proved by using Augmentation rule of FD. A ->B & A-> C. Then we get A->BC. Using Replication rule we get  ↠ BC.

D) If A → BC and A → B then A ↠ C. This is true, because A->BC. From which we can get A->C. From which using Replication rule we can get  A ↠ C.

C) If A ↠ BC and A → B then A → C. Here using Coalescence rule I'm getting A->B back. I'm not able to prove/disprove A → C.

Please help me prove/disprove option C.
What does it means to have a multi valued dependency on two attributes like A ↠ BC? All examples I've seen in books are like Employee ↠ Project | Dependent, where Employee simultaneously determines two attributes. So we end up storing duplicated. But I have never encountered a multi valued dependency with two attributes on the right hand side. Please give an example of such a dependency.

A,B and C are sets of attributes.

  All examples I've seen in books are like Employee ↠ Project |
  Dependent, where Employee simultaneously determines two attributes.

A ↠ B | C is a special notation different from A ↠ BC. It's useful because MVDs always come in pairs. It says that both A ↠ B and A ↠ C hold. This implies that the relation equals AB JOIN AC. Ie the relation satisfies join dependency *{AB,AC}.

  What does it means to have a multi valued dependency on two attributes like A ↠ BC?

  Please give an example of such a dependency.

Just think of values for subrows the way you would think of values for one attribute. Suppose {W} ↠ {X}. Then whenever W appears it's with all of some set of X values. Suppose each X has a numerator N and denominator D. Then we could have a relation {W} ↠ {N,D}. Whenever W appears it's with all of some set of < N, D > subrow values.

  Please help me prove/disprove option C.

  C) If A ↠ BC and A → B then A → C. Here using Coalescence rule I'm getting A->B back. I'm not able to prove/disprove A → C.

Hint: In my last paragraph if {W} → {N} then the variation must come from D.

Hint: If you are permitted to prove by counterexample then it's good to try to disprove things via guessing at one or more trivial examples. Eg with {W} ↠ {N,D} and {W} → {N} (A={W}, B={N}, C={D}):

  V   W   N   D
=================
| v | w | n | 1 |
-----------------
| v | w | n | 2 |
-----------------

(
How do I apply this definition of multivalued dependency (MVD) to this example relation?
)


Is it possible to have both a subquery and a function in a WITH clause? In Oracle 12c, we can include subqueries in the WITH clause:

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;

We can also include functions in the WITH clause:

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1

Is it possible to include both a subquery and a function in the WITH clause of a single query?

This is just a hypothetical question that came to mind. I don't have any real-world examples, but would be interested to see how this might work. I'm not really experienced enough to come up with an example that's useful.

Here is an example:

with
  function f1 return number is begin return 1; end;
  t1 as (select * from dual connect by level <= 3)
  select f1 from t1;
  /

        F1
----------
         1
         1
         1


Optimistic locking with postgresql blocking due to locks I'm trying to use optimistic lock in postgresql but it seems I'm misunderstanding how it should work. I thought that if I used 'serializable' isolation level, each transaction would act as if the other transactions didn't exist and just at compile time any checking would be done and the transaction would eventually abort. However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock. This is the test I'm doing:

First, create the db:

CREATE USER myuser WITH PASSWORD '1234';
CREATE DATABASE tempdb;
GRANT ALL PRIVILEGES ON DATABASE tempdb to myuser;
\connect tempdb;
CREATE TABLE temptable (
    id        integer PRIMARY KEY,
    name       varchar(40) NOT NULL
);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

Then, create two python programs.

The first one will try to insert a row and then will sleep 10 seconds before committing the transaction:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my name');")
    time.sleep(10)
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

The second will try to do almost the same, however it won't sleep for 10 seconds:

import time
import psycopg2

def main():
    conn = psycopg2.connect(dbname='tempdb', host='localhost', user='myuser', password='1234')
    cur = conn.cursor()
    cur.execute("begin transaction isolation level serializable")
    cur.execute("insert into temptable (id, name) values (1, 'my other name');")
    cur.execute("commit")
    print('test finished')

if __name__ == '__main__':
    main()

If I run temp1.py and then, in other window, temp2.py, temp2.py will hang until temp1.py commits. But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

Am I doing anything wrong? Is it possible to do what I want in postgresql?

(I'm using postgresql 10.12)

Q: However, in the test I'm doing, the transactions are affecting others, in the sense that one transaction might block in a lock.

Concurrent transactions not affecting each other at the serializable isolation level does not mean that any one transaction does not lock any other. Locks are inevitable in databases as soon as concurrent transactions work on the same data.

Not affecting each other means that the result of any particular transaction does not differ from what it would be if other transactions were not working concurrently with the same data.

The time it takes to complete a transaction, or whether it had to wait for locks to be released do not count as results. Results are rows and values that are read or written or returned to the caller.

Q: But, what I would expect is that temp2.py would insert the row, commit and then, when temp1.py tries to commit, it would get an error.

The error concerns a violation of a unique constraint. Constraint checks can be deferred or immediate, independently of the isolation level,
and they're immediate by default.

If the unique column  was declared as id integer PRIMARY KEY DEFERRABLE INITIALLY DEFERRED, the INSERT in transaction #2 would not hang, but transaction #2 would hang at COMMIT time until transaction #1 commits or aborts.

Q: Is it possible to do what I want in postgresql?

If you want the second transaction (the one that inserts in second) to be guaranteed to succeed because it's faster to commit than the other one that inserted first, I don't think it's possible. From the point of view of the serializable isolation logic, it doesn't really matter which transaction fails. Any transaction may fail and the caller must always be prepared to react appropriately according to SQLSTATE (the error code).


How does one export/import registered servers with passwords? I have a pretty extensive collection of registered servers on SQL Server Management Studio 2016 on my box.  However, I got a new box and want to move the entire collection there.  So I right-clicked on Local Server Groups, and exported the entire set with user names and passwords.

When I try to import the set onto a new box, I get this error for every server:

If I try to export/import without user names and password, it all works fine.  But I do need user names and passwords.

What am I missing?

I am using the same windows login (my domain login) and exactly the same version of SSMS (13.0.15900.1).

It will only work on the same machine, not a different one.


Modeling invoices and orders I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless.

Model of the Orders table:

Now I had another column called payment_deadline, that well had the payment deadline. What I'm thinking about is, what info should my Orders table have and what info should my Invoices table have?

Which one has Employee and Customer id? Which one has the prices? Which one has the payment info? Which one has the deadline info? I'm totally lost.

I understand that in the business world, an order signals that the provider of the goods has to act, and the invoice represents that the goods provider has fulfilled his part and the customer now has to pay for the goods/services. But I'm lost as to how model it in the database. Any insight is much appreciated.

Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could mean any of dozens of things as mentioned in the Answer by Neil McGuigan: the person who first took customer inquiry phone call, the sales person who responded, the sales assistant who filled out Sales Order, the production manager who acknowledged receipt of the Sales Order, the workers who produced the items on the work order, and so on.

But I'll give you a simplified overview that might provide some of the insight you requested.

Business Transaction Life-Cycle

Using the definitions from Wikipedia, the usual work flow of vendor-customer is shown in this diagram. Colors mean:

Pink items = customer-vendor contact/interaction without documents
Blue items = documents from/to the customer
Yellow items = documents internal to the vendor.

After consulting with a sales person (optional), the customer submits a Purchase Order to a vendor, describing exactly what service/product they have decided to buy. If accepted by the vendor, a legally-binding contract is established.

The Sales Order is produced by the sales people of the vendor, reflecting the content of the Purchase Order but using the format and lingo that makes sense to the internal staff of the vendor.

The vendor's internal staff, the production-related managers (senior artisans, manufacturing managers, warehouse supervisors, etc.) create one or more Work Order documents directing their staff on what to do to render the services/products desired by the customer.

When any part or whole of the Purchase Item has been rendered to the customer, an Invoice is prepared by the vendor and delivered to the customer to demand payment. The Invoice should reference the Purchase Order number/id.

Lastly, the customer makes payment along with some kind of Remittance Advice referencing the Invoice number/id. This step completes the transaction (ignoring returns, Change Orders, or other issues).

The diagram above is not a table or entity diagram. For example, you may well have multiple work orders for one sales order. You may have multiple invoices if partial deliveries are made to the customer. Or, imagine if the manufacturing staff only builds two units of WonderWidget at a time to avoid wastage. So we must wait for another customer to order a second widget, while waiting we do not yet produce a Work Order. Meanwhile we can start on other items on the sales order, and we produce Work Orders for these early items. In this scenario we have a Many-to-Many relationship where any Sales Order can have more than one Work Order (customer wants several items with some of those items being produced while waiting for WonderWidget production to begin) while each Work Order can have more than one Sales Order attached (a pair of Sales Orders from different customers in case of double-WonderWidget production).

In-Person Retail

In-person retail is much simpler, with an work flow of three instantaneous steps, only two of which would be documents (or database entities).

A customer grabs some stuff from the store shelf, presents at the checkout counter, a register tallies up the items (akin to a sales order), payment is executed, and a sales receipt is produced. The Sales Receipt is basically the combination of the register's tally (sales order) plus the payment details (partial credit card number, date-time, and so on).

Again, this is simplified because we ignore returns and such issues.

Again this diagram is not a table or entity diagram.

Web/Phone Retail

With online web or telephone ordering, the work flow is probably a combination of the two above. If the customer pays immediately, then we don't need the Invoice & Remittance Advice. If it's a small shop, the Sales Order and Work Order may be the same single entity, whereas if this is an Amazon.com style business with multiple warehouses, then we will have multiple work orders.

Over-Simplified Partial Table Diagram

Just to give you a flavor as a newbie to table design, here is a grossly over-simplified design. Partial design, just for the sales order. This diagram uses simple crows-foot notation and Postgres data types. Abbreviations: pk means primary key, and fk means foreign key.


Powershell script executed by SQL Server doesn't take effect I'm doing some testing and I want to execute a PowerShell script through xp_cmdshell. Below is the PowerShell script (c:\temp\sqltotext.ps1).
echo "haha" | Out-File "c:\temp\haha.txt"

Then I execute the script through xp_cmdshell like this.
DECLARE @result INT;
EXEC @result = sys.xp_cmdshell N'powershell.exe -Command "Start-Process powershell.exe -Verb RunAs ''-NoProfile -ExecutionPolicy Bypass -File c:\temp\sqltotext.ps1''"';
IF (@result = 0) PRINT 'Success';
ELSE PRINT 'Failure';

It runs successfully and I can find the file c:\temp\haha.txt with the content haha.
However, when I change the content of c:\temp\sqltotext.ps1 to:
echo Process CurrentUser LocalMachine | %{Set-ExecutionPolicy bypass -Force -Scope $_}

and execute the same aforementioned TSQL command, the TSQL reports success but I didn't get the expected result (the execution policy in all scopes were NOT changed).
When I execute the PowerShell script manually (in a PowerShell console and type c:\temp>.\sqltotext.ps1), it works as expected (the execution policy in all scopes were changed). Why this happens?
I did some investigation though. EXEC xp_cmdshell 'whoami.exe' reports I'm running with nt service\mssqlserver. I also manually added nt service\mssqlserver into the administrators local user group. By using process explorer, I can confirm the PowerShell session indeed was started and all related processes have admin permission (Integrity = High).

The cmd.exe command line looks like this:

When I execute this command manually, it can change the execution policy without any issue (I changed all execution policy back to the original values after this).

The PowerShell process command line looks like this:

When I execute the PowerShell process command line manually, I can change the execution policies too. I don't have any idea why when I run the command through xp_cmdshell, it doesn't change anything.
BTW, I know there are multiple ways to write the command. I'm just talking about the technical skills here, so please don't suggest me changing command syntax etc.

xp_cmdshell is not PowerShell.
If you run: xp_cmdshell 'whoami.exe'; you will discover that you are nt service\mssqlserver and not Administrator.

xp_cmdshell is impersonating that user (which is a service in our case).
So the solution of the problem should be to impersonate an Administrator.
But you cannot do that.
I have tried using Proxy Credentials:
EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'ds35efg##65';
GO

But I couldn't impersonate Administrator.
I have also tried adding this code at the beginning of the script:
if (!([Security.Principal.WindowsPrincipal][Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator")) { Start-Process powershell.exe "-NoProfile -ExecutionPolicy Bypass -File `"$PSCommandPath`"" -Verb RunAs; exit }

But the code just run forever because is expecting you to click on the YES and impersonate the Administrator.
I have also tried to run it from an Agent job: no joy.
This guide says not to impersonate a domain Admin. Which is exactly what you want to do. I let you try that because I cannot do that on my PC.
For start debugging try to run this:
runas /user:domain\administrator $^

and check if you can at least be Administrator on that machine.
So the recipe is:

Try to impersonate Administrator and execute the .ps1 script from command line
In alternative add a user with Administrator privileges
Impersonate it through SSMS

Good luck.


Does creating View cause an overhead to DB? As my subject suggests, I would like to know does creating views cause an overhead to DB in terms of space, storage, performance?

Refer the the Concepts Guide - Overview of Views for this sort of question:

  Overview of Views

  A view is a logical representation of one or more tables. In essence, a view is a stored query.
  [...]

  Characteristics of Views

  Unlike a table, a view is not allocated storage space, nor does a view contain data. Rather, a view is defined by a query that extracts or derives data from the base tables referenced by the view. Because a view is based on other objects, it requires no storage other than storage for the query that defines the view in the data dictionary.

Whether creating a view can have an impact on performance or not isn't answerable. If you don't use it, it won't impact anything. If you use it and it happens that the query the view "represents" has poor performance characteristics, then it will have essentially the same bad impact as running the underlying query directly.

Now materialized views are a completely different concept, and do actually contain data. They can also impact the performance of SQL statements that don't explicitly mention them via query rewriting - but again, what sort of impact that would have isn't answerable without specific details.

(Materialized views are more frequently (but not exclusively) used in data warehousing-type scenarios, whereas plain views are simply all over the place.)


How to find Oracle is configured for OLTP or OLAP? Is there any specific query or configuration to check Oracle is configured for OLTP or OLAP?I have gone through many sites.But I can find the difference between OLTP and OLAP and not query or configuration to check that difference?

OLTP does not require any specific configuration.

Checking the availability of OLAP libraries:

SQL> select * from v$option where parameter = 'OLAP';

PARAMETER  VALUE
---------- -----
OLAP       TRUE

Checking the availability of OLAP components in the database:

SQL> select comp_name, version, status
     from dba_server_registry where comp_name like '%OLAP%';

COMP_NAME                 VERSION    STATUS
------------------------- ---------- ----------
OLAP Analytic Workspace   11.2.0.4.0 VALID
Oracle OLAP API           11.2.0.4.0 VALID
OLAP Catalog              11.2.0.4.0 VALID

Checking if OLAP is in use (note that, this works with sampled data, the sample interval by default is 1 week, it does not represent the real-time situation):

SQL> select name, detected_usages, currently_used from
     dba_feature_usage_statistics where name like 'OLAP%' order by name;

NAME                           DETECTED_USAGES CURRE
------------------------------ --------------- -----
OLAP - Analytic Workspaces                   0 FALSE
OLAP - Cubes                                 0 FALSE


Does having a .ldf for a index file cause issues? Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files.

My understanding is the extensions don't really matter. Whatever is first is first and anything else is secondary regardless of the extension. Does that apply to .ldf in this case when clearly it's being used for the clustered indexes?

I ask because I would assume SQL Server treats .ldf differently than say, mdf's.

(And before you ask, yes, there is already a .ldf assigned for the transaction log on another spindle)

By convention, the following file extensions are used in SQL Server:

Primary Data File:  .mdf
Secondary Data File(s):  .ndf
Log File:  .ldf

Having said that, you're actually free to use any file extension you like for any SQL Server file.  In fact, it's completely fine to use no extension at all.

SQL Server does not look at or care about the file name in any way other than that it needs to be compliant with NTFS file name standards.

You can see what files are being used by a database with this query:

SELECT d.name
    , mf.name
    , mf.physical_name
    , SizeInMB = CONVERT(decimal(10, 2), mf.size) * 8192 / 1048576
    , FileGrowth =
        CASE mf.growth
        WHEN 0 THEN 'fixed size, will not grow'
        ELSE
            CASE mf.is_percent_growth
            WHEN 1 THEN CONVERT(varchar(10), mf.growth) + ' percent'
            WHEN 0 THEN CONVERT(varchar(30), mf.growth * 8192 / 1048576.0) + ' MB'
            END
        END
    , MaxSizeInMB = CASE mf.max_size
        WHEN 0 THEN 'fixed size, will not grow'
        WHEN -1 THEN 'file will grow until disk is full'
        WHEN 268435456 THEN 'log file will grow to maximum size of 2TB'
        ELSE CONVERT(varchar(30), CONVERT(decimal(10, 2), mf.max_size) * 8192 / 1048576.0) + ' MB'
        END
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = '<database_name>';

Replace <database_name> with the name of the database you're concerned about, or remove the entire WHERE clause to see files for all databases.


How to add 1100 columns in SQL Server I need 1100 columns in a single table.I already have 928 columns in same table, now I need to add new 150 columns.

I know SQL Server allow maximum 1024 columns.

Now I will not create new table. If I create new table and join my require table, I need to make changes in many places and this is not possible for me. So I want to add all columns in same table(require table).I know this is so tough to execute this table but presently I need to serve this requirement.

Bad idea, but if you insist and your data qualifies for the 8K bytes limitation -

Wide Tables

  A wide table is a table that has defined a column set. Wide tables use
  sparse columns to increase the total of columns that a table can have
  to 30,000. The number of indexes and statistics is also increased to
  1,000 and 30,000, respectively. The maximum size of a wide table row
  is 8,019 bytes. Therefore, most of the data in any particular row
  should be NULL. To create or change a table into a wide table, you add
  a column set to the table definition. The maximum number of nonsparse
  columns plus computed columns in a wide table remains 1,024. By using
  wide tables, you can create flexible schemas within an application.
  You can add or drop columns whenever you want. Keep in mind that using
  wide tables has unique performance considerations, such as increased
  run-time and compile-time memory requirements. For more information,
  see Performance Considerations for Wide Tables.


Returning a variable from Dynamic SQL I'm converting a procedure to use a Linked Server, because the databases that it references have now been migrated to separate Clusters. I've almost finished this work, but I'm not stuck on the following part;

declare @CustomerId int

set @CustomerId = 2

exec (N'
USE [DatabaseName]

SELECT  @DiskUsageDataObjectsKB = SUM(alloUni.used_pages) * 8
FROM        sys.tables sysTab
            INNER JOIN sys.indexes ind
            ON sysTab.OBJECT_ID = ind.OBJECT_ID  and ind.Index_ID<=1
            INNER JOIN sys.partitions parti
            ON ind.OBJECT_ID = parti.OBJECT_ID AND ind.index_id = parti.index_id
            INNER JOIN sys.allocation_units alloUni
            ON parti.partition_id = alloUni.container_id
WHERE       SCHEMA_NAME(sysTab.SCHEMA_ID) = CAST('+ @CustomerId +' AS NVARCHAR(MAX))') AT [LinkedServer]

select @DiskUsageDataObjectsKB

As you can see, I'm passing the CustomerId variable to this using Dynamic SQL, but I'm not sure how I get the @DiskUsageDataObjectsKB value back to the procedure, so that It can be added to our reporting database.

Would anyone have any suggestions to how this can be done, please?

Many thanks!
Tom

You should use sp_executesql with output parameter.

declare @S nvarchar(max) = 'select @x = 1'

declare @xx int
set @xx = 0

exec sp_executesql @S, N'@x int out', @xx out

select @xx

I've got the example from here.


Storing repetetive file metadata in databse I have a situation where I want to store data like the following.

Path: String
isDirectory: Bool
isSymLink: Bool
linksTo: File
lastmodifiedData: Long
size: Long,
permissions: String

I will be storing this kind of data for many differet directory structures that very often have the same file metadata.

I do not want to have many repeated entries in the database, but I also do not want to have a 5 column Primary key.

I was considering a table, lets call it FILE_BASE

Path: String
isDirectory: Bool
isSymLink: Bool
linksTo: File

and another called FILE_INSTANCE

directory-root:String
fileBase:            // should this be an ID, or a multi-column FK
lastmodifiedData: Long
size: Long,
permissions: String

I am trying to understand the best way to go about creating a key for the first table that can be referenced in the second table.

If I use a composite key, then basically all of the columns in the first table are part of the primary key.

If I use surrogate keys, then I need to do a query before every insert to determine if the file(s) base that i want to add already exists in the table, and recover the surrogate ID. If I am dealing with thousands of files per insert, then this could get cumbersome quickly.

I could generate the ID's myself using some sort of UUID algorithm on a concatenation of the table columns, but that could potentially generate some pretty big ID's for many of the file bases, depending on their path in the file system.

I feel like this is just a trade-off between space and time complexity. I also feel like I am overthinking this, as I do not have a lot of database experience. Luckily this is just a test project that will likely get thrown away anyway, but I could use some input, or suggestions on reading so that I can understand what a good implementation would look like.

Is there something obvious that I am missing here?

--Edited: Added directory root identifier to second table.

I will be storing this kind of data for many different directory structures that very often have the same file metadata. I do not want to have many repeated entries in the database, but I also do not want to have a 5 column Primary key.

In order to normalize, you need to do is accommodate for LVM, and partition information. Then you should only have to keep inode, and meta data, (stat and lsattr if you want to be super anal). You may or may not want emulate hierarchy in path. We do in this example. The repetitive meta-data isn't an issue.

CREATE TABLE physical_volume (
  pv_id      int PRIMARY KEY,
  hw_uud     uuid
);

CREATE TABLE volume_group (
  vg_id int PRIMARY KEY
);

CREATE TABLE pv_vg (
  pv_id int REFERENCES physical_volume,
  vg_id int REFERENCES volume_group,
);

CREATE TABLE logical_volume (
  lv_id int PRIMARY KEY,
  name  text
);

CREATE TABLE partition (
  partition_id int PRIMARY KEY
  fs           text,
  mount_attr   jsonb,
);

CREATE TABLE files (
  file_id       int PRIMARY KEY
  partition_id  int REFERENCES partition
  inode         int,
  .. repetitive metadata
  UNIQUE (partition_id, inode)
);

CREATE TABLE path (
  path_id            int PRIMARY KEY,
  parent_path        int REFERENCES path
  file_id            int REFERENCES files
  name               text
);


Postgresql-12 replication status in recovery mode I have postgresql master server and a standby server. The standby will be in recovery mode(ie. will be consuming the wals shipped from the master node). The master will be read/write and the standby will not be able to query until its promoted(DR setup).
I wanted to know the replication status of the standby node(ie. Each wals restored in standby with time to time).
Version - postgresql 12.3
OS - centos 8
recovery.signal
Also is it possible to use streaming replication along with wal file shipping.

You can ask primary server about replication status of standby server(s) by using pg_stat_replication which is a system statistics view.
You can use WAL Archiving to setup a replication instead of / in addition to streaming replication


How to add new column based on calculation of another column in postgresql? I have the following table:

id   time_start           area
1   2019-09-01T10:12:32Z  london
2   2019-08-29T10:13:32Z  chicago
3   2019-07-31T10:14:32Z  paris
4   2019-09-28T10:17:32Z  madrid
5   2019-07-04T10:18:32Z  spain

I would like to add a new column called correct_date which is just the time_start column:

the new column should be added to the end and look like this:

date_start
2019-09-01
2019-08-29
2019-07-31
2019-09-28
2019-07-04

however I am filtering the data before hand based on a where statement and am a bit stuck on how to alter the table after the where condition has been satisified - or which order to do the process in? I know I will have to use timestamp::date to attain the date.

my current query is

select *
from
my_table
where area in ('london','chicago','paris')

how can I add an alter table statement to the above code so that it will add a new column based on the dates of the time_start column - would I do the alter statement at the beginning?

Couldn't find a clear answer online using postgresql!

I would like to add a new column ...
... I am filtering the data before hand based on a where statement

ALTER'ing a table changes the whole table.
There is no possibility for "filtering"; either every single row gets itself a shiny, new column or none of them do.
alter table t1
add column date_start date ;

update t1
set date_start = time_start::date ;

But, as others have said, why create a whole new column to store data that is readily available within the table already?


SELECT w/ INNER JOIN Query on Optional Columns I have five tables: File2, Branches_of_Study, Schools, Education_Establishments, and Certificates. File2 is the transaction table with all the other tables as reference tables.

File2 is all about the educational background of an employee. The employee has to select from a dropdown list in the site the education establishment first, e.g. Highschool, College, etc. Branch of Study refers to the course the employee has taken, e.g. IT, HRM, Photography, etc.

Upon selecting College, the employee must select a Branch of Study. But when selecting Highschool, selecting a branch of study is not allowed.

I have a gridview in the site that shows all of the employee's educational attainment. Here is my query:

SELECT File2.file2_id,
    File2.candidate_id,
    ee.education_establishment,
    s.school,
    File2.school_others,
    File2.start_date,
    File2.end_date,
    bs1.branch_of_study AS branch_of_study_1,
    c.certificate,
    File2.course_appraisal,
    bs2.branch_of_study AS branch_of_study_2
FROM File2
    INNER JOIN Education_Establishments ee
            ON File2.education_establishment_code = ee.education_establishment_code
    INNER JOIN Schools s
            ON File2.school_code = s.school_code
    INNER JOIN Branches_of_Study bs1
            ON File2.branch_of_study_1_code = bs1.branch_of_study_code
    INNER JOIN Branches_of_Study bs2
            ON File2.branch_of_study_2_code = bs2.branch_of_study_code
    INNER JOIN Certificates c
            ON File2.certificate_code = c.certificate_code
 WHERE File2.candidate_id = 100003

The query returns only results for the College entries for employee number 100003. I assume this is caused by having null values in the Branch of Study fields for Highschool entries.

I want a query that will show all entries (College and Highschool) and at the same time showing the text value (not the code) from the reference tables.

If joining is "optional", then it is not an inner join, by definition.
Consider investigating left joins.


Postgres 10 and partitioning by week i'm trying to understand how partitioning works, and main manual doesn't help me a lot.

Also, PostgreSQL 10 supports partitioning natively... but what does it mean?

is it possible to declare table:

CREATE TABLE public.audit_logs (
        log_type varchar(31) NOT NULL,
        id int8 NOT NULL DEFAULT nextval('audit_logs_id_seq'::regclass),
        additional_data varchar(1024) NULL DEFAULT NULL::character varying,
        week int NOT NULL DEFAULT date_trunc('week', CURRENT_TIMESTAMP),
        CONSTRAINT audit_logs_pkey PRIMARY KEY (id)
)
WITH (
        OIDS=FALSE
)
PARTITION BY  (week) ;

and how to create auto tables public.audit_logs_N, where N would be week number?

Do i need to create a trigger?
or what?

main manual doesn't help me a lot.

I hope you've read the manual, both entries:

ddl_partitioning (& the Limitations 5.10.2.3. Limitations)

create table

If yes, please mention specifically what you don't understand about it.

  partitioning natively... but what does it mean?

You don't have to create check constraints, trigger functions or triggers.

The SQL example above needs to be modified thusly:

e07=# CREATE TABLE public.audit_logs (
        log_type varchar(31) NOT NULL,
        id serial NOT NULL ,
        additional_data varchar(1024) NULL DEFAULT NULL::character varying,
        week int NOT NULL DEFAULT date_part('week', CURRENT_TIMESTAMP)
)
PARTITION BY list  (week) ;

--catch this error in the code:
--ERROR:  no partition of relation "audit_logs" found for row

  and how to create auto tables public.audit_logs_N, where N would be week number?

Depending on the requirements, create the partitions in advance or use a pgagent (or crontab) to create them one at a time in the prior week.

create table audit_logs_not_1 partition of audit_logs for values in (1);
...
create table audit_logs_not_53 partition of audit_logs for values in (53);

  Do i need to create a trigger? or what?

A trigger is only needed for Implementation Using Inheritance


De-duplication method for fields that are identical except for Unique ID I'm looking for a decent de-duplication method. Basically I have a table which has five fields plus a Unique ID (key) field. I've run a duplicate which detection query which finds any records where ALL fields match except the unique ID, which gives me this:

So the first two records are identical apart from the unique ID (end column), and the next two are identical, etc. I have a few hundred of these so rather than just clicking and deleting, what would be the quickest way of just deleting ONE of these two records? I've tried a couple of append queries, such as

INSERT INTO tblDupScanLines ( ScanTime, ScannerNo, ScanDate, EventName, PID, )
SELECT DISTINCT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate, qryDupScanLines.EventName, qryDupScanLines.PID
FROM qryDupScanLines;

But I get stuck because I then don't have anything to dedupe them against on the main table (I've done something similar before but using MinOfID to delete whichever one had the lowest ID number) but trying to import the unique ID column as well would import ALL records and not the DISTINCT ones.

Can anyone help?

It seems to me that this should work:

INSERT INTO tblDupScanLines (ScanTime, ScannerNo, ScanDate, EventName, PID, ID)
SELECT qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate,
    qryDupScanLines.EventName, qryDupScanLines.PID, MIN(qryDupScanLines.ID) AS minID
FROM qryDupScanLines
GROUP BY qryDupScanLines.ScanTime, qryDupScanLines.ScannerNo, qryDupScanLines.ScanDate,
    qryDupScanLines.EventName, qryDupScanLines.PID