Zabbix partitioning autovacuum causes locking I have a Zabbix database on a postgresql backend that grew to ~1TB of disk space. The Zabbix housekeeper (a process for removing old data from the tables) was running for over 18 hours so I used this script to enable partitioning.

But then - exactly at midnight - Zabbix stopped inserting data into the database, right when a new partitioned table should get created.

I found out that when I kill the running autovacuum process (mostly on the table history or history_uint) via pg_cancel_backend() the Zabbix server can insert again as some locks go away 1.

I understand that the vacuum process is needed in order to free up transaction ID's so I should NOT do this every day.

Is partitioning a bad idea? (at least in an already running setup?)
Should I have started with partitioned tables all along?
Should I throw out all our monitoring data, and insert it again into correctly partitioned tables? (a huge amount of work...)
Should I disable autovacuum on those partitioned tables? (assuming that the issue comes from a global lock on all schemas .public and .partitions)

The final goal is to reduce the load on the database and improve performance.

By enabling partitioning I was able to reduce the runtime of the housekeeper down to a few minutes/seconds via disabling the housekeeping of history and trend_data. But it's no positive result if the monitoring stops working after midnight...

Additional Information:

The Hardware is quite oversized (all SSD and performing only OK-ish). I guess because I don't really know what knobs to tweak I better not touch them at all.

1: Unfortunately, I only figured this out 7 Hours later, in the morning. Now I have 2 days of missing monitoring data from 00:00 - 07:00

A normal autovacuum process will succumb silently if it blocks another transaction, but if that happens all the time and autovacuum can never finish, you will eventually have old table rows that have never been vacuumed. To prevent the danger of losing data as soon as the transaction ID counter wraps around, PostgreSQL then triggers a less benevolent autovacuum run that is not willing to back down.

Now creating a new partition for a table requires an ACCESS EXCLUSIVE lock on the partitioned table. Since this seems to happen every day, and autovacuum seems to run too slow to finish in one day, you end up getting stuck at midnight, when the ghosts of automatic table partitioning start rattling their chains.

Killing autovacuum is not a solution, because it will only make the problem worse. The work will be lost, and PostgreSQL will start autovacuum again, and even if you manage to silence it, eventually the database will refuse data modifications. Then your only option is to take down the database start it in single user mode and run a manual VACUUM. That causes a longer outage, so you don't want that.

The best solution is to tune autovacuum so that it can run as fast as possible, in the hope that it gets done within 24 hours:

set autovacuum_work_mem as high as you can
set autovacuum_vacuum_cost_delay to 0

Additionally, if you have many partitions, you should increase autovacuum_max_workers somewhat.

Now keep your fingers crossed and hope that autovacuum gets done before midnight.

You can monitor the state of affairs with something like

SELECT s.last_autovacuum, s.last_vacuum, age(t.relfrozenxid)
FROM pg_stat_all_tables AS s
   JOIN pg_class AS t ON s.relid = t.oid
WHERE age(t.relfrozenxid) > 200000000
ORDER BY age(t.relfrozenxid) DESC;

Keep working until that list is empty!

If you cannot get autovacuum to complete a single table in 24 hours, your only choice is to skip creating new partitions for a while (or at least not attaching them to the partitioned table).


Replicating some tables from one postgres database to another I've got the following situation: I have three machines running postgresql databases. One machine holds client account information (call this machine C), the other two machines hold client logging information (call these L1 and L2). The reason for the split is to separate loading over multiple machines (so some clients send logging information to L1, some to L2 ... and maybe some time L3, L4, ...).

When retrieving logging information, in principal I'd like to be able to JOIN between logging tables on Ln and the client account tables on C. In reality I can't do JOINs like this (and even if I could, I'd want to avoid loading C).

My thought is to replicate the tables on C onto each of L1, L2, ... so that I can do the joins. As far as the tables from C are concerned, C is master and L1, L2, ... are slaves. But for the other tables on L1, L2, ... these machines are masters. Its not exactly master-master replication, and is it not exactly master-slave.

Can postgres (I'm running 9.1) replication be persuaded to do this, or if not are there any other packages that would do the job. In the last resort, I can write some code the periodically sync's the tables (I can tolerate some delay), but it would be nice not to!

Thanks in advance.

On PostgreSQL 9.3, you could use postgres_fdw to transparently query the foreign table on the other machine.

On older versions, dblink can be an option as mentioned by Andrew.

Another option is to use a tool like Londiste or Slony-I to replicate the tables you want. I recommend using Londiste for this, it's going to be much simpler. It creates triggers on the table to detect insert/update/delete, and replicates these using its own client/server and a queueing system to the other database, where it does corresponding insert/update/deletes. I use it in production on several client sites and it works very well.

A future option (hopefully in PostgreSQL 9.5) will be log streaming logical replication, logical changeset extraction and bidirectional replication, which will allow individual databases or tables to be replicated at the SQL level. Part of the work for this was committed to PostgreSQL 9.4, but not enough to make it useful for what you want to do.


how to represent what functional dependencies are? I'm trying to understand how to represent what functional dependencies are:

A1, A2,..., An → B
If two tuples of R have the same attributes for A1, A2,..., An then they have the same value for the attributes of B.

I am not able to represent it abstractly speaking... Can you give me an example?

Not sure if this is such a brilliant example, but assume a relation like:

CREATE TABLE OWNERSHIP
( item_id
, ssn
, firstname
,    unique(item_id, ssn) );

Assume the f.d.:

ssn -> firstname

For the tuples:

(1, 'X', 'a name')
(2, 'X', 'a name')

We can see that X in both cases has the name 'a name'.

Another way of thinking about it is: Assume two tuples of R have the same value of A but different values for B. Then by definition B can't be functionally dependent of A.


Oracle 11g installation stuck at installation I'm trying to install oracle 11g on fedora by following this tutorial but it stuck after I click Next in step 8. The screen does not go away even after several hours. There's no log file in oraInventory folder.

What could be the possible problem? How can I debug it?

I've struggled the same problem and I think the problem comes from gnome failing to pop up the confirmation dialog. That's why there is nothing in the error logs.
So what I did is changing the window manager from gnome to xfce. That solved the problem.
It would probably work with another manager or there may be a fix for gnome but here is how to install xfce:

yum -y install epel-release #adding epel repository
sed -i -e "s/enabled=1/enabled=0/g" /etc/yum.repos.d/epel.repo # using it only when needed
yum --enablerepo=epel -y groups install "Xfce" #installing xfce

echo "exec /usr/bin/xfce4-session" >> ~/.xinitrc
startx

If you prefer to install oracle via vnc then skip the last two lines and change the /home/vnc-user/.vnc/xstartup ( vnc-user is oracle at my box ) like this:

#unset DBUS_SESSION_BUS_ADDRESS
#exec /etc/X11/xinit/xinitrc
/usr/bin/xfce4-session

If you don't know how to install vnc server on your CentOS 7 or Fedora box here is a guide: https://www.howtoforge.com/vnc-server-installation-on-centos-7


Is IF ELSE better in this stored procedure than a Parameter? I found a stored procedure in our sql server 2016 db

Create PROCEDURE XXX
   @complicatedQuery bit
AS
BEGIN
   SELECT some fields FROM
     Table A join Table B on something
     JOIN Giant Table C on @complicatedQuery=1 and SomeOtherCondition
END

Will we get bit better performance if I do this:

Create PROCEDURE XXX
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0 then
       SELECT some fields FROM
         Table A join Table B on something
       ELSE
       SELECT some fields FROM
         Table A join Table B on something
         JOIN Giant Table C on complicatedQuery=1 and SomeOtherCondition

    END

I think in both cases,sql search will create a plan that goes through both branches. But will second one be better or in reality there is no performance difference (as the first sp has @complicatedQuery in the table join condition anyway)?

I read another post which says that it is best to create sub stored procedures to cover different scenarios.

Is there a way to write some tests to prove it?

Many Thanks

Testing
I changed the inner join with TABLE C to a LEFT JOIN so our data does not get filtered out by the INNER JOIN condition (JOIN  DBO.TABLEC C ON @complicatedQuery=1 when @complicatedQuery = 0 ) as an INNER JOIN ON 1=0 AND SomeOtherCondition returns zero rows.
Unless that is, what you want, but then calling the original or conditional procedure
with @complicatedQuery = 0 will return different results. (Among other remarks)
Test data at the bottom
Procedures
Original procedure example with left join
Create PROCEDURE dbo.ProcOriginal
   @complicatedQuery bit
AS
BEGIN
   SELECT A.VAL FROM
     DBO.TABLEA A
     JOIN  DBO.TABLEB B on A.B_ID =B.ID
     LEFT JOIN  DBO.TABLEC C ON @complicatedQuery=1 and C.A_ID = A.ID
END

Conditional procedure example, also with left join to keep it logically the same
Create PROCEDURE dbo.ProcConditional
       @complicatedQuery bit
    AS
    BEGIN
       IF @complicatedQuery =0
        BEGIN
        SELECT A.VAL FROM
        DBO.TABLEA A
        JOIN  DBO.TABLEB B on A.B_ID =B.ID;
        END
        IF @complicatedQuery =1
        BEGIN
        SELECT A.VAL FROM
            DBO.TABLEA A
            JOIN  DBO.TABLEB B on A.B_ID =B.ID
            LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;
    END
END

Running them with parameter = 0
SET STATISTICS IO, TIME ON;
EXEC dbo.ProcConditional 0
EXEC dbo.ProcOriginal 0

The estimated execution plan's differ the most, the actual query plan's less.

We can see why the estimates are different, based on a part of the estimated execution plan from ProcOriginal :

While in reality, 0 rows are returned.

We can improve this by adding this index:
CREATE INDEX IX_A_ID
ON DBO.TABLEC(A_ID);

Causing no table access

But, the estimated rows are now at 2.5K, even though the variable eliminates the table access.
This can still be a potential problem due to increasingly higher memory grants if the join between A and B returns more results.

The ProcConditional actual execution plan seems ok.

A NC Index on B_ID on TableA could improve both queries.

Running them with parameter = 1
EXEC dbo.ProcConditional 1;
EXEC dbo.ProcOriginal 1;

The only difference here is the extra filter operator with a startup expression predicate as seen earlier @complicatedQuery=1 or 1=1 .

Filter

Added due to
ON @complicatedQuery=1 and C.A_ID = A.ID

All this due to SQL Server not knowing what @complicatedQuery will be at runtime.
You could remove this filter operator by adding an OPTION(RECOMPILE) to the procedure, making a new plan to be created on each execution.

Which is better
I do think that the conditional method would be the better choice between the two.

Another method
Using Dynamic SQL to build the strings depending on @complicatedQuery  and then executing them directly.
CREATE PROCEDURE dbo.ProcDynamic
   @complicatedQuery bit = 0
AS
BEGIN
DECLARE @CMD nvarchar(1000)

    SET @CMD =
    'SELECT A.VAL FROM
    DBO.TABLEA A
    JOIN  DBO.TABLEB B on A.B_ID =B.ID'

    IF @complicatedQuery =1
    BEGIN
    SET @CMD += ' LEFT JOIN  DBO.TABLEC C ON C.A_ID = A.ID;'
    END
    ELSE
    BEGIN
        SET @CMD+= ';'
    END
EXEC SP_EXECUTESQL @CMD
END

When working with dynamic sql, you do have several threats and considerations, but without working with parameters added to the dynamic string you should be ok.
For further reference on dynamic sql, check out this walkthrough by Erland Sommarskog.

Test data
Small A table, bigger B table (1M) and 6x bigger C table
CREATE TABLE DBO.TABLEA(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,B_ID INT, VAL VARCHAR(255));
INSERT INTO DBO.TABLEA(B_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) , 'val'
FROM MASTER.dbo.spt_values;
--2540

CREATE TABLE DBO.TABLEB(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, VAL VARCHAR(255));

INSERT INTO DBO.TABLEB(VAL)
SELECT top(1000000) 'valB'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--1000000

CREATE TABLE dbo.TABLEC(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, A_ID  INT, VAL VARCHAR(255))

INSERT INTO DBO.TABLEC(A_ID,VAL)
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 'val'
FROM MASTER.dbo.spt_values spt1
CROSS APPLY MASTER.dbo.spt_values spt2;
--6451600


Using 'Listagg' function and 'Regular Expression' to REVERSE the value of a specific column in Oracle I have a table with below structure:
create table TBL_TEST
(
  col_id   NUMBER,
  col_name VARCHAR2(500)
)

Some example data :
col_id | col_name
-----------------
  1    | aetnap
  2    | elppa
  3    | ananab

What I want to do is to reverse the value of col_name column like this :
col_id | col_name
-----------------
  1    | pantea
  2    | apple
  3    | banana

I used listagg and regexp_replace to write the query:
select col_id,
       listagg(val) within group(order by val_row desc) original_value
  from (select col_id,
               val,
               row_number() over(partition by col_id order by col_id) val_row
          from (select col_id, trim(column_value) val
                  from tbl_test,
                       xmltable(trim(trailing ',' from
                                     regexp_replace(col_name, '(.)', '"\1",')))))
 group by col_id;

The query above works fine and gives me the desired result, I want to know if there are better ways of writing the query cause two sub queries are used in the query above and I want to know whether there are better ways than using a sub query.
Thanks in advance

select col_id, reverse(col_name) as col_name from tbl_test;

    COL_ID COL_NAME
---------- ----------
         1 pantea
         2 apple
         3 banana


PostgreSQL trigger doesn't insert all records I'm working with PostgreSQL 10. I'm updating another table when a record is inserted into the original table.  The trigger doesn't insert the last record if there are more than one. If it's just one record it won't get inserted at all. Any ideas why it's doing this?

create table employee
(
   empID SERIAL unique PRIMARY KEY NOT NULL,
   first_name text not null,
   mi text not null,
   last_name text not null,
   full_name character varying (150) UNIQUE NOT NULL,
   employee_email character varying(150) UNIQUE
);

CREATE TABLE empmerge
(
  empid integer NOT NULL REFERENCES employee(empID) MATCH FULL
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  full_name character varying NOT NULL REFERENCES employee(full_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  org_section text,
  date_of_xfer date

);

The employee trigger:

create or replace function concat_full_name() returns trigger as $$
begin
    if NEW.full_name is null
      then NEW.full_name := concat(new.first_name, ' ',new.mi,'.', ' ', new.last_name);
    end if;
    return new;
end;
$$ language plpgsql;

create trigger trig_concat_full_name
before insert
on employee
for each row
execute procedure concat_full_name();

The merge trigger

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  SELECT employee.empID, employee.full_name
  FROM employee
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE employee.empID = empMerge.empID
                     and  employee.full_name = empMerge.full_name);
    return new;
end;
$$ language plpgsql;

create trigger trig_insert_empMerge
before insert
on employee
for each row
execute procedure insert_empMerge();

The insert statement:

insert into employee(first_name, mi, last_name)
values
 ('dora','l', 'explorer'),
 ('atticus','m', 'finch'),
 ('nancy','s', 'drew');

select * from empMerge;

The select from employee table won't work as it is a before trigger and thus the new rows are not yet in the employees table.

You can use the new record directly.

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  SELECT new.empID, new.full_name
  WHERE NOT EXISTS (SELECT 1
                    FROM empMerge
                    WHERE new.empID = empMerge.empID
                     and  new.full_name = empMerge.full_name);
  return new;
end;
$$ language plpgsql;

You could make that a bit shorter (and faster) if you declare a unique constraint on empmerge(empid, full_name) and then use insert on conflict:

alter table empmerge
   add constraint unique_employee
   unique (empid, full_name);

Then change the trigger function:

create or replace function insert_empMerge()
  returns trigger as $$
begin
  INSERT INTO empMerge(empID, full_name)
  values (new.empID, new.full_name)
  on conflict on constraint unique_employee do nothing;
  return new;
end;
$$ language plpgsql;

Another option would be to make the existing trigger an AFTER trigger. In that case the new row is already inserted into the employee table and it would be visible for the trigger.


ERROR: "4.9E-324" is out of range for type double precision I saw this error when trying to perform the following insert into a PostgreSQL 8.4.20:

java.sql.BatchUpdateException: Batch entry 12 insert into perf_raw_2017_03_16 values ('3129', '182', '818', '2017-03-20 16:01:17.507000 +00:00:00', '4.9E-324') was aborted.  Call getNextException to see the cause.
org.postgresql.util.PSQLException: ERROR: "4.9E-324" is out of range for type double precision

However, when I try the same insert on a different machine with PostgreSQL 8.4.4, the insert finishes fine. When I do a select to verify that the value is there, it also finishes fine:

select * from perf_raw_2017_03_20 where guid_key=3129 and property_key=182;

 guid_key | property_key | instance_id_key |       time_stamp        |         value
----------+--------------+-----------------+-------------------------+-----------------------
     3129 |          182 |             818 | 2017-03-20 16:01:17.507 | 4.94065645841247e-324

The type of the column is double precision, which, according to the documentation, is "15 decimal digits precision". The error makes sense, I am indeed outside of its range, so I am not very surprised by the error. Here are my questions though:

The binary format of the DB does not change with minor releases. Why did this even work in 8.4.4 and not 8.4.20?

I did not find anything in the changelog that would explain this:
https://bucardo.org/postgres_all_versions.html

Why does the DB still allow me to insert the value if I use a select to get it from an existing record?

I will probably post an answer to this question based on what I observed, but it would be cool if somebody could provide a deeper insight into this issue.

You're asking for software that was EOL in July 2014. It's far from supported. I'm not sure I trust git on those releases. The work transferring over the repo may have been sloppy. You may have to go back to SVN to get an authoritative answer but this seems to be responsible...

  On some platforms, strtod() reports ERANGE for a denormalized value (ie,
  one that can be represented as distinct from zero, but is too small to have
  full precision).  On others, it doesn't.  It seems better to try to accept
  these values consistently, so add a test to see if the result value
  indicates a true out-of-range condition.  This should be okay per Single
  Unix Spec.  On machines where the underlying math isn't IEEE standard, the
  behavior for such small numbers may not be very consistent, but then it
  wouldn't be anyway.

  Marti Raudsepp, after a proposal by Jeroen Vermeulen

You can see the thread about this here.


What is the query that updates the Column in a table I have a column in a table that I don't know who is updating it .

The table name is dbo.Events and the column name is [Status] and this column values are 1 or 2 .

I am trying to find the query that is updating rows in this column

I am familiar with the table

select * from sys.sql_modules m with(nolock);

Are there other options ?

THANKS

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table:

CREATE TABLE dbo.UpdateLog
(
  EventType  SYSNAME,
  Parameters INT,
  EventInfo  NVARCHAR(MAX),
  EventDate  DATETIME NOT NULL DEFAULT GETDATE(),
  AppName    SYSNAME  NOT NULL DEFAULT APP_NAME(),
  HostName   SYSNAME  NOT NULL DEFAULT HOST_NAME(),
  LoginName  SYSNAME  NOT NULL DEFAULT SUSER_SNAME()
);

Then create a trigger for updates to the source table:

CREATE TRIGGER dbo.tr_whatever
ON dbo.whatever
WITH EXECUTE AS OWNER
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS
  (
    SELECT 1 FROM inserted AS i
      INNER JOIN deleted AS d
      ON i.id = d.id -- use key column(s) here
      AND i.x <> d.x -- assumes column x is not nullable
  )
  BEGIN
    INSERT dbo.UpdateLog(EventType, Parameters, EventInfo)
      EXEC sys.sp_executesql N'DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS;';
  END
END
GO

Now you'll be able to see not only what query fired the trigger, but also who ran it, when, and from which application.


Transactions/Locks in Sql Server I have a table, which has a "LockedBy" field, which can either be null (no one has locked this record for editing) or it can have a user ID.

The idea is to make it so that only one team member can "check out" the record (and all of the records in other tables that are tied to this record) at a time, which sounded easy in my head, until I realized I don't really know how locking works with transactions in SQL Server.

Currently, I am updating tables using transactions and save points in a stored procedure, but I don't know how to incorporate the "lock":

BEGIN TRANSACTION TR1
BEGIN TRY

    UPDATE LOCK_TABLE SET UpdatedDate=GETDATE() WHERE ID=@ID
    --======================================================================
    --only update this table if the lock field is null; make sure no one
    --else can modify the lock field until the whole of TR1 is committed
    --or rolled back
    --======================================================================

    --Table 1
    SAVE TRANSACTION TR2
    BEGIN TRY
        UPDATE TABLE1 SET somefield='something' WHERE LockTableID=@ID
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION TR2;
        PRINT ERROR_MESSAGE();
    END CATCH

    COMMIT TRANSACTION TR1;
    --======================================================================
    --release the lock
    --======================================================================
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION TR1;
    SET @RETURN=-1;
    PRINT ERROR_MESSAGE();
END CATCH

I understand locks from a concurrent programming point of view, but every SQL Server introduction to locks seems to either be about preventing deadlocks or how to use different granularities of locks.

I think what I want is just "from here to here, don't let anyone else execute this stored procedure" (like a lock in a multi-threaded function), but maybe I'm coming at it from the wrong angle.

Does SQL Server do what I want automatically when I use transactions?  Or is there something special I need to add so that I can lock out other calls to this stored procedure?

SQL Server has very extensive locking options. Most are automatic but you can force them if needed for specific cases. You can also implement non-default locking behaviour on various levels. What you are looking for (as far as I can tell) can best be handled by a table hint called rowlock. Depending on your isolation level, you might need additional hints to ensure its function. You can read more about it in the following link: https://msdn.microsoft.com/en-us/library/ms187373.aspx


Help in Performance Tuning Required I have a query which involves joins across 7 different tables.

Below is the query:

SELECT        /*+qb_name(select1) dynamic_sampling(nbtd 2) */
               td.tsf_no                      C1
              ,td.tsf_seq_no                  C2
              ,NVL(th.from_loc,-999)          C3
              ,NVL(th.to_loc,-999)            C4
              ,NVL(th.to_loc_type,'-1')       C5
              ,NVL (s.to_loc,'-999')          C6
              ,td.item                        C7
              ,NVL(ss.qty_expected, 0)        C8
              ,NVL(ss.carton,'-1')            C9
              ,NVL(ss.distro_no,'-1')         C10
              ,NVL(ss.distro_type,'-1')       C11
              ,NVL(ss.qty_expected, 0)        C12
              ,NVL(ss.weight_received, 0)     C13
              ,NVL(ss.weight_received_uom, 0) C14
              ,s.shipment                     C15
              ,NVL(s.bol_no,'-1')             C16
              ,NVL(s.order_no, 1)             C17
              ,ils.av_cost                    C18
              ,il.unit_retail                 C19
              ,user                           C20
              ,to_char(sysdate, 'YYYYMMDD')   C21
              ,'T'                            C22
              ,ss.seq_no                      C23
              ,nbtd.expense_ind               C24
              ,nbtd.process_expense_ind       C25
              ,to_char(th.create_date, 'YYYYMMDD')  C26
          FROM nb_tsfdetail nbtd
              ,tsfdetail td
              ,tsfhead th
              ,shipsku ss
              ,shipment s
              ,item_loc il
              ,item_loc_soh ils
         WHERE td.tsf_no        = nbtd.tsf_no
           AND td.tsf_seq_no    = nbtd.tsf_seq_no
           AND td.tsf_no        = th.tsf_no
           AND ss.distro_no     = td.tsf_no
           AND ss.item          = td.item
           AND ss.shipment      = s.shipment
           AND td.item          = il.item
           AND il.loc           = th.from_loc
           AND ils.item         = il.item
           AND ils.loc          = il.loc
           AND nbtd.process_expense_ind = 'N'
           AND nbtd.expense_ind = 'Y';

The data in each table is:

select count(1) from nb_tsfdetail;--692,336,190
select count(1) from tsfdetail;--693,317,818
select count(1) from tsfhead;--3,697,876
select count(1) from shipsku;-- 1,273,823,438
select count(1) from shipment;--514,593,396
select count(1) from item_loc;-- 109,587,391
select count(1) from item_loc_soh;--108,435,856

I have attached the explain of the above query.

Where to start
Start by defining the performance problem that you're experiencing as precisely as you can. I'm going to assume that the query that you have is running too slowly. How quickly does it need to finish? You need that information to figure out if certain trade-offs are worth it. For example, adding an index to an underlying table might improve the performance of this query but it will take up additional space on the database and require extra maintenance during DML.
Notes on EXPLAIN
Next understand as much as you can about the explain plan for the query that you have. Pay particular attention to the row estimates. If you think that Oracle is executing a query too slowly then coming up with an explain plan that you think would finish faster is a great exercise. You may think of an index that needs to be added. Worst case you can add a hint to test out a theory and figure out a way to rewrite the SQL (or keep the hint if absolutely necessary) to improve performance. An explain plan can give good clues but it only has what Oracle thinks will happen. If your database is licensed for the tuning pack then you can use real-time SQL monitoring in SQL Developer. That is a great feature for figuring out why a query is running slowly because it gives you runtime information about the actual versus estimated number of rows, wait times, and the amount of time spent on each step. All you posted is an explain plan so I'll do my best based on that alone.
For your query, the driving table is NB_TSFDETAIL. Oracle expects to filter down 700 M rows to 168k using a full scan on the table. Oracle expects the nested loop join to ITEM_LOC to filter down the result set further to 35k rows. The rest of the joins aren't expected to meaningfully change the cardinality. Every join is a nested loop join except for a hash join on TSFHEAD. That may have been implemented as a hash join simply because there wasn't a good index candidate on that table.
Importance of join order, table access method, and join type
When I'm looking at a query I focus on three main things: join order, table access method, and join type. If any of those seem inefficient or don't match what I expected to happen then I dig deeper into that.
To give an example of when join order can go wrong, if there's a join that eliminates many of the rows from the result set then I generally want that join to be processed as earlier as possible in the query plan. If it's processed as the final join then I may have done a lot of unnecessary work on rows that won't be returned in the final result set. For your query Oracle chose the NB_TSDETAIL table as the first table to access. That seems pretty reasonable because the filters are expected to eliminate over 99.9% of the rows from one of the largest ones in the query. Most of the joins aren't expected to change the cardinality so I'm not that concerned about join order for this query, assuming that the estimated cardinalities are close to reality. If there is an INNER JOIN that you know will eliminate many rows then you may need to give Oracle more information so it can build a better query plan with that knowledge.
To give an example of when table access method can go wrong, sometimes tables aren't indexed properly for queries. It looks like your query requires a full table scan on NB_TSFDETAIL. If that full scan takes longer than the maximum response time that you can tolerate then it doesn't matter how you tune your query. You need to fix the underlying data model problem, perhaps by creating an index. Another example of when table access method can go wrong is if an index is used when it shouldn't be. Using an index can be very inefficient if Oracle needs to access a large performance of rows from a table. In that scenario a full table scan is often more efficient.
To give an example of when the join type can go wrong, a nested loop join can be inefficient if the outer table has too many rows. Here Oracle estimates that only 168k rows will be returned from NB_TSDETAIL. That isn't that many rows relatively speaking so a nested loop join using that result set as an outer table may have a lower cost than doing a hash join or a merge join. However, what if the actual number of rows returned from the table is 168M? That means that the cost of the time will be roughly off by a factor of a thousand. If Oracle had more accurate cardinality information it may have picked hash joins instead which likely would have performed much better.
There are of course other possible issues with query plans that can lead to poor performance. The one most worth mentioning for this query I think is the lack of parallelism. Perhaps this is intentional and your server is too busy to allow for it, but the amount of work required here by the RDBMS makes me think that the query could benefit from parallelism.
Conclusion
In summary, the following are the most likely causes of the performance issue that you're experiencing:

Oracle is estimating the number of rows returned from NB_TSFDETAIL as 168982 which is a significant underestimate. If that estimate is fixed then Oracle will pick a plan with fewer nested loop joins that will perform better. Ways to improve that estimate including creating multi-column statistics (there may be a hidden relationship between the PROCESS_EXPENSE_IND and EXPENSE_IND columns that Oracle doesn't know about), updating statistics on the table, or by using a larger sample size for your dynamic sampling hint.

The number of estimated rows from NB_TSDETAIL is about right but it takes a long time to find them all with a table scan on a 700 million row table. Defining an index on those columns could speed up query performance.

You're running a query on 700 M row tables but not using parallelism. That may have been configured by the DBA and isn't intentional on your part. If your workload allows it and this query is important enough you should consider a PARALLEL(X) hint to see if that improves the execution time.

Some other database or hardware setting is causing the slowness of the query. This could be disk configuration, database parameters, or any number of things.

Further actions
Actionable next steps for you include:

Define your performance problem and response time requirement.

Determine the accuracy of the 168k row estimate on the NB_TSFDETAIL table.

Benchmark the time Oracle needs to perform a full table scan on the NB_TSFDETAIL. You may be able to do this with a carefully constructed COUNT(*) query.

If necessary, talk to your DBA about allowing this query to run in parallel.

You know more about the table structures and data than we do, so try out other theories to see if they improve query performance.


Execution plan of on-premises sql server and Azure sql server is not the same I've been having some problems with moving databases from on-premises (2014 sql server) to Azure sql server. It's using the exact same database structure and data, but for some reason the query uses a different execution plan and takes much longer.
On the on-premises server it returns almost immediately and on the Azure server it takes about 6 seconds when using standard with 50 DTU's and when using e-DTU's it takes about 3 seconds, which is better, but still much longer.
Any ideas why the execution plan is different (even the rows are returned in another order), or how I can make it use the same query plan?
The query (generated by entity framework) is:
SELECT  DISTINCT
    [Extent4].[Id] AS [Id],
    [Extent4].[Name] AS [Name],
    [Extent4].[Background] AS [Background],
    [Extent4].[Code] AS [Code],
    [Extent4].[DeactivatedOn] AS [DeactivatedOn]
FROM    [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[User_Id] = [Extent2].[Id]
INNER JOIN [dbo].[UserRegistrationGroups] AS [Extent3] ON [Extent2].[UserRegistration_Id] = [Extent3].[UserRegistration_Id]
INNER JOIN [dbo].[UserGroups] AS [Extent4] ON [Extent3].[Group_Id] = [Extent4].[Id]

execution plan of the on-premises server:

https://www.brentozar.com/pastetheplan/?id=S1cfDgpzD
Execution plan of the azure sql server:

https://www.brentozar.com/pastetheplan/?id=HyyshlTMw
The waits from the Azure plan are:
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />

I've already tried to set the compatibility level the same (100).
My customer pays 10 euro per month for his current service provider, which provides a web and sql server in the cloud as well.
I'm trying to convince him to go to azure, but the azure sql, which costs 60 euro per month for an s2 is a lot slower in most queries, which makes it difficult for me to convince him. I would have expected about the same performance.
This query was just an example.

If the plan is the same (which it looks to be), and the query speeds up when you bump up to more DTUs, it's likely you're simply being limited by the Azure tier you're on.
You can confirm this by looking in the execution plan at the wait stats - if you're being CPU-capped by your Azure tier, there will be RESOURCE_GOVERNOR_IDLE waits (and related SOS_SCHEDULER_YIELD waits as well).
I've blogged about this here: RESOURCE_GOVERNOR_IDLE in Azure
Looking at the Azure plan you provided:
<WaitStats>
  <Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" />
  <Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />
</WaitStats>
<QueryTimeStats CpuTime="2756" ElapsedTime="3351" />

The difference between elapsed and CPU time is 595 - which is almost exactly the amount of RESOURCE_GOVERNOR_IDLE waits that were incurred.  Without those waits, the query would have finished in 2.7 seconds in theory, which is still slower than the 1.1 seconds it took the SQL Server 2014 query.
The main solutions are to either increase the service tier, or change the query / indexes so it uses less CPU.
My guess would be that the other hosting provider machine has a faster clock speed on the CPU, as well as not being limited by the resource governor.
To be clear though, you're client may not be considering the manageability "wins" with Azure, even at a more expensive / higher tier.  You'll get many advantages (including high availability, automated backups, automated tuning, all the latest database engine and security patches, and not having to deal with SQL Server instance level or VM maintenance) that are included in the slightly higher cost.

For this specific query, you might try creating an indexed view of this data if it is needed frequently, to "pre-calculate" this distinct list of user groups that have placed orders.  Then the query would only need to read the 20 rows from that view, which would be less likely to be impacted by the service tier.
Note that you would have to use GROUP BY instead of DISTINCT in the view definition due to the limitations on indexed views.


PostgreSQL bug with JSON type? This query not working, but the error not make sense

SELECT count(*) FROM t
WHERE json_typeof(info->'vatids')='array' AND json_array_length(info->'vatids')>1;

ERROR:  cannot get array length of a scalar

PS: idem with JSONb.

You didn't show us the data that is in that column nor did you show us the table definition. But I guess that you have something like this:

create table t (info json);
insert into t
  (info)
values
  ('{"vatids" : ["one"]}'),
  ('{"vatids" : ["one", "two"]}'),
  ('{"vatids" : "n/a"}');

There is no "short-circuit" of conditions in SQL, all conditions in the WHERE clause are applied (logically) at the same time, so json_array_length(info->'vatids') will fail for the third row even though you have json_typeof(info->'vatids')='array' as well.

The following will also not work, because the optimizer is smart enough to push the condition into the derived table:

select *
from (
  select info
  from t
  where json_typeof(info->'vatids') = 'array'
) x
where json_array_length(info->'vatids') > 1;

In this case you need a CTE that completely filters out the invalid rows, only then you can apply the condition on the array length:

with only_arrays as (
  select info
  from t
  where json_typeof(info->'vatids') = 'array'
)
select count(*)
from only_arrays
where json_array_length(info->'vatids') > 1;

This works because CTEs in Postgres act as an optimization fence and thus conditions from the outer query are not pushed into the CTE. Most of the times this is an disadvantage, but in this case it is indeed helpful.


How to group joined tables by aggregate function, specifically by average group-name type? I'm trying to build a query selecting two columns (category types) from one table JOINED to another table-column displaying average grades as percentage number-types; here is my first attempt:

SELECT gt.grade_type_code,
       gt.description,
       gg.numeric_grade
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE;

Here is a screenshot of output for clarification:

My second attempt is closer to the expected result, sans "DESCRIPTION" column, necessitating the JOIN clause; I've also included the query and output below:

SELECT grade_type_code,
       count(grade_type_code),
       Round(avg(numeric_grade),2)
FROM student.grade
GROUP BY grade_type_code
ORDER BY GRADE_TYPE_CODE;

Lastly, here is my most recent attempt to combine the above logic:

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2)
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code
ORDER BY gt.grade_type_code;

And the error message:

ORA-00979: not a GROUP BY expression
00979. 00000 -  "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 1 Column: 28

So essentially, I'm attempting to display the average NUMERIC_GRADE for the correspondingly grouped category types (i.e. Participation = avg percentage) but my GROUP_BY attempts invariably return errors (not sure why).  I believe my logic is directionally correct, albeit flawed; could someone please advise?  Thank you!

ORA-00979 not a GROUP BY expression

  Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause.
  Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments.

Gt.description column is not listed in the group by clause. Columns not participating in group function should be listed in group by clause.

SELECT gt.grade_type_code,
       gt.description,
       round(avg(gg.numeric_grade),2) AS "Average Grade"
FROM student.grade_type gt
JOIN student.grade gg ON gt.grade_type_code = gg.GRADE_TYPE_CODE
GROUP BY gt.grade_type_code,
         gt.description
ORDER BY gt.grade_type_code;


What is the best query to get the latest row version based on time? I'm wondering what can be the most efficient query to get the latest rows from the following table.

CREATE TABLE AGE_DATA
(
  INSERTED_DAY TIMESTAMP(6),
  HOSTNAME VARCHAR2(255 CHAR),
  CONCEPT  VARCHAR2(255 CHAR),
  REPORTED_VALUE NUMBER(10)
);

If I want to know the latest data inserted by hostname, I have 2 approaches, and I want to know which would be the most efficient, depending the cases a) having ~100 rows, b) having more than ~1M rows.

with latest as (select hostname,max(inserted_day) inserted_day
from AGE_DATA group by hostname)
select *
from latest l
join age_data ad on (l.hostname = ad.hostname and l.inserted_day = ad.inserted_day)

or

select * from age_data
where (hostname,inserted_day) in (select hostname,max(inserted_day) inserted_day
    from AGE_DATA group by hostname)

There is a 3rd option, using window functions, either max():

with latest as
(select *,
        max(inserted_day) over (partition by hostname)
          as max_inserted_day
 from age_data
)
select *
from latest l
where inserted_day = max_inserted_day ;

or rank():

with latest as
(select *,
        rank() over (partition by hostname order by inserted_day desc)
          as rnk
 from age_data
)
select *
from latest l
where rnk = 1 ;

If there are ties (rows with same maximum inserted_day for a hostname), all the above queries (and yours) will return all the tied rows. If you want to resolve ties (and return only 1 or only 2 rows), you can replace rank() with row_number() and change the order by clause inside the over according to the requirements.

Regarding efficiency, an index on (hostname, inserted_day) will help, in all the queries.


Realtime Update MySQL on every Update occurred on PostgreSQL I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they are on linux operating system(ubuntu),

How can i do this work?

Two basic approaches.

1) Connect the servers via ForeignDataWrapper, DatabaseLink, Linked Server, External Table, or whatever your source system calls their technology.

2) Replication

Replication changes can be collected by triggers, or puled from database logs.  High throughput systems should use a log based solution, although those usually cost money (Oracle GoldenGate, Sybase RepServer, IBM IIDR, etc).  You may want to check out https://www.symmetricds.org as an open source alternative.

The downside to #1 is that your transaction success may (or will?) be dependent on BOTH systems being available.  Also, you will take a performance hit to update both systems at once.


SQL Error: Must be GROUPed or used in aggregate function I have just created a SQL statement in Crystal Reports using the command feature, but I am having issues with displaying the data. The error reads:

  Attribute FUND_ACCT_REG_VIEW.FUND._ACCT_NO must be GROUPed or used in an aggregate function.

Here is the code:

SELECT substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) p2
    ,substr(work_item.wi_name, 1, 4) wi_name
    ,substr(pr_line_item.prac_no, 7, 16) prac_no
    ,moa_code
    ,resource_code
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0) COMMITTED
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0) obligated
    ,unoblig_us_bal_amt unoblig
    ,max(receive_org_code) receive
    ,substr(org_name, 1, 13) org_name
    ,fund_acct_reg_view.fund_acct_no fund_acct
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN') type
FROM pr_line_item
    ,work_item
    ,pr_amend
    ,wm_organization
    ,fund_acct_reg_view
WHERE ((substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6) = '%153719%'))
    AND work_item.wi_code = pr_line_item.wi_code
    AND pr_line_item.prac_no = pr_amend.prac_no
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'OA%'
    AND (
        ABS(nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0)) > 0
        OR nvl(obli_excess_commit_amt, 0) > 0
        )
    AND fund_acct_reg_view.fund_acct_no NOT LIKE 'J169479'
    AND receive_org_code = org_code
    AND pr_line_item.fund_acct_no = fund_acct_reg_view.fund_acct_no
GROUP BY substr(work_item.wi_name, 1 + instr(work_item.wi_name, '['), 6)
    ,substr(pr_line_item.prac_no, 7, 16)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) + nvl(pr_interest_amt, 0)
    ,nvl(certified_us_amt, 0) - nvl(deob_amt, 0) + nvl(obli_excess_commit_amt, 0) - nvl(unoblig_us_bal_amt, 0) + nvl(pr_interest_amt, 0)
    ,unoblig_us_bal_amt
    ,resource_code
    ,pr_line_item.fund_acct_no
    ,decode(fund_acct_reg_view.fund_acct_no, 'J177777', 'SA', 'J177778', 'SA', 'J177779', 'SA', 'J177768', 'OTHER CWE', 'J176716', 'OTHER CWE', 'J176717', 'OTHER CWE', 'J177780', 'CONST', 'DESIGN')
    ,substr(org_name, 1, 13)
    ,substr(work_item.wi_name, 1, 4)
    ,moa_code
ORDER BY type
    ,fund_acct
    ,moa_code
    ,resource_code
    ,receive

After testing the code multiple times, I keep having the issued with either this field or another field. I am hoping that the issue it at the tail end of my code and someone may know a simple quick fix. I have spent almost all day trying to read up on this error, but I can't see how other solution apply to my situation.

You use fund_acct_reg_view.fund_acct_no fund_acct, in SELECT but use pr_line_item.fund_acct_no in GROUP BY. Use fund_acct_no from the same table in both cases.


Help me understanding this Exercise Question This question is from a Book "SQL with and Introduction to PL/SQL by Lannes L. and Morris Murphy. This question is from one of its exercises. I cant understand what this question is asking.

  Q. Determine which books generate less than 55 percent profit and how many copies of those books have been sold recently. Summarize your findings for management and include a copy of the query necessary to retrieve the data from the database tables.

To retrieve the results, Books table joins Orderitems on ISBN and Orderitems joins Orders table on ORDER#

BOOKS TABLE

ISBN       TITLE                               PUBDATE        PUBID       COST     RETAIL CATEGORY
---------- ----------------------------------- --------- ---------- ---------- ---------- ------------------
1059831198 BodyBuilding in 10 Minutes a Day    21-JAN-01          4      18.75      39.95 Fitness
0401140733 Revenge of Mickey                   14-DEC-01          1       14.2         22 Family Life
4981341710 Bulding a Car with Toothpicks       18-MAR-02          2       37.8      59.95 Childern
8843172113 Database Implementation             04-JUN-99          3       31.4      55.95 Computer
3437212490 Cooking with Mashrooms              28-FEB-00          4       12.5      19.95 Cooking
3957136468 Holy Grail of Oracle                31-DEC-01          3      24.25      75.95 Computer
1915762492 HandCranked Computers               21-JAN-01          3       21.8         25 Computer
9959789321 E-Business the Easy Way             01-MAR-02          2       37.9       54.5 Computer
2491748320 Painless Child-Rearing              17-JUL-00          5         48      89.95 Family Life
0299282519 The Wok Way to Cook                 11-SEP-00          4         19      28.75 Cooking
8117949391 Big Bear and Little Dove            08-NOV-01          5       5.32       8.95 Childern
0132149871 How to get Faster Pizza             11-NOV-02          4      17.85      29.95 SelfHelp
9247381001 How to Manage the Manager           09-MAY-99          1       15.4      31.95 Business
2148428890 Shortest Poems                      01-MAY-01          5      21.85      39.95 Literature

ORDERS TABLE

ORDER#  CUSTOMER# ORDERDATE SHIPDATE  SHIPSTREET                SHIPCITY             SH SHIPZ
------- --------- --------- --------- ------------------------- -------------------- -- -----
  1000       1005 31-MAR-03 02-APR-03 1201 Orange Ave           Seattle              WA 98114
  1001       1010 31-MAR-03 01-APR-03 114 East Svannah          Atlanta              GA 30314
  1002       1011 31-MAR-03 01-APR-03 58 Tilla Circle           Chicago              IL 60605
  1003       1001 01-APR-03 01-APR-03 958 Magnolia lane         Eastpoint            FL 32328
  1004       1020 01-APR-03 05-APR-03 561 Roundabout Way        Trenton              NJ 08601
  1005       1018 01-APR-03 02-APR-03 1008 Grand Avenue         Macon                GA 31206
  1006       1003 01-APR-03 02-APR-03 558A Capital Hwy          Tallahassee          FL 32307
  1007       1007 02-APR-03 04-APR-03 9153 Main Street          Austin               TX 78710
  1008       1004 02-APR-03 03-APR-03 69821 Southe Avenue       Boise                ID 83707
  1009       1005 03-APR-03 05-APR-03 9 Lightining Rd           Seattle              WA 98110

ORDERITEMS TABLE

ORDER#      ITEM# ISBN         QUANTITY
----------- ----- ---------- ----------
  1000          1 3437212490          1
  1001          1 9247381001          1
  1001          2 2491748320          1
  1002          1 8843172113          2
  1003          1 8843172113          1
  1003          2 1059831198          1
  1003          3 3437212490          1
  1004          1 2491748320          2
  1005          1 2147428890          1
  1006          1 9959789321          1
  1007          1 3957136468          3
  1007          2 9959789321          1
  1007          3 8117949391          1

I'm not really sure this is truly a DBA question, as it sounds like your problem is understanding the question itself.

The question asks which books generate less than 55% profit. Profit would be the difference between retail and cost. So, the question is, what should the profit be less than 55% of? The basic options are 55% of retail, or 55% of cost. I would assume that retail is intended.

So, you'd need to calculate which books show retail - cost as less than 55% of retail. Then, make a call as to what "recently" means (given orders from just two months, I'd go for all of those orders), and sum the total quantities ordered for each of those books.


How to relate to information_schema Is it possible to create a foreign key that relates to information_schema?

For an application implementation, I would like to store extra column information in another table, but I want to ensure that it is not describing columns that do not exist.

Is it possible to have a foreign key that relates to information_schema?  If so, how?

Is it possible to have a foreign key that relates to information_schema

No.

information_schema contains views. You can't have a foreign key to a view.

You can't set a foreign key to point at the underlying pg_catalog.pg_attribute table that defines columns, either, because foreign keys to system catalogs are not supported.


Amazon RDS Database privileges question I have created a database and have my username which is part of the rds_superuser group. I granted a friend access to my database and whenever he creates a new table I am unable to view his table and get an access denied error code [42501].

Also, If I try to change any privileges to my user account, I get an error that I don't have permission on his tables. How do I fix this?

rds_superuser is not the same as a PostgreSQL superuser
From one of the best blogs on PostgreSQL, 2ndquadrant, Andrew Dunstan writes,

The Amazon RDS documentation blithely contains this statement: “When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions.” But just how super is it?
One of the things I came up against recently was that, unlike the usual postgres superuser, this role has no access other than what is explicitly granted to objects owned by other users. From a table and function privileges point of view, it’s just an ordinary user.
So if you’re using more than one user in your RDS database, even if one or even all of them are rds_superusers, you’re going to become very familiar with the GRANT command if you aren’t already. And if your schema has objects owned by more than one user, then the relevant “GRANT .. ON ALL ..” option fails too, since you probably won’t have sufficient privileges on all of them. Perhaps we should have a “GRANT … ON ALL POSSIBLE …” which would skip those things you don’t have GRANT privilege on.

From our own Craig Ringer (also with 2nd Quadrant)

It’s not very super at all. It cannot LOAD libraries, access the file system, override permissions, get an unrestricted view of pg_stat_activity

Another user, Robins, also points out that rds_superuser can't run pg_dumpall so this may be a source of vendor lock-in.


How to create scheduler for manually refresh Materialized View if the MV get need_compile stallness status I've a problem to Automatically Refresh Materialized View, if the stallness condition is NEED_COMPILE / STALE / UNUSABLE OR NOT FRESH.

I've MV with this PL/SQL :

CREATE MATERIALIZED VIEW "some_schema"."MV_TEST_BRO" ("some_field", "some_field", "some_field")
ORGANIZATION HEAP PCTFREE 10 PCTUSED 0 INITRANS 2 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "some_tablespace"
BUILD IMMEDIATE
USING INDEX
REFRESH COMPLETE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS SELECT * FROM some_table;

btw, my MV is too complex to put it here so I made it simple in here.
After I got an update in table master I test it with below query to get status stallness of my MV.

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

And, I create PL/SQL to refresh that MV like this :

BEGIN
DBMS_REFRESH.make(
    name                 => 'some_schema.MINUTE_REFRESH',
    list                 => '',
    next_date            => SYSDATE,
    interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
    implicit_destroy     => FALSE,
    lax                  => FALSE,
    job                  => 0,
    rollback_seg         => NULL,
    push_deferred_rpc    => TRUE,
    refresh_after_errors => TRUE,
    purge_option         => NULL,
    parallelism          => 4,
    heap_size            => NULL);
END;

BEGIN
DBMS_REFRESH.add(
    name => 'some_schema.MINUTE_REFRESH',
    list => 'some_schema.MV_TEST_BRO',
    lax  => TRUE);
END;

But the interval of that dbms_refresh is not working at all. So I created some scheduler to do the job, this is my PL/SQL :

BEGIN
    sys.dbms_scheduler.create_job(
    job_name => '"some_schema"."REFRESH_MV"',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin
       DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH'');
    end;',
    schedule_name => '"some_schema"."EJS_MV_REFRESH"',
    job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Scheduler untuk merefresh MV pada schema some_schema',
    auto_drop => FALSE,
    enabled => FALSE);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
    sys.dbms_scheduler.set_attribute( name => '"some_schema"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
    sys.dbms_scheduler.enable( '"some_schema"."REFRESH_MV"' );
END;

So far this solve my problem to automatically refresh MV every 1 minute. But it's not effective at all. So, My Problem is :

How to make this job_action to execute DBMS_REFRESH.REFRESH(name => ''some_schema.MINUTE_REFRESH''); every 1 minute if the status of stallness is not FRESH ?

I don't know much about how to use pointer or whatever the name to get the result of query to check SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME; and use this status to trigger the refresh, it's just like IF-THEN condition.

Please help me, if you know how to get that...

I'm successfully able to create a scheduler for my MV to refresh every minutes if the status stallness is not FRESH. Here's my solutions :

I created a schedules to be used in my scheduler:

BEGIN
    sys.dbms_scheduler.create_schedule(
    repeat_interval => 'FREQ=MINUTELY',
    start_date => to_timestamp_tz('2015-06-09 01:41:55 Asia/Jakarta', 'YYYY-MM-DD HH24:MI:SS TZR'),
    comments => 'Interval refresh 1 menit',
    schedule_name => '"SOME_SCHEMA"."REFRESH_MV"');
END;

After that I create DBMS_SCHEDULER to create a job like this :

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_type => 'PLSQL_BLOCK',
job_action => 'DECLARE
    mv_name USER_MVIEWS.MVIEW_NAME%type;
    mv_status USER_MVIEWS.STALENESS%type;
    mv_last USER_MVIEWS.LAST_REFRESH_TYPE%type;
    mv_state USER_MVIEWS.COMPILE_STATE%type;

    CURSOR check_mv
    IS SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS WHERE MVIEW_NAME = ''MV_TEST_BRO'' ORDER BY MVIEW_NAME;

    BEGIN
        OPEN check_mv;

        LOOP
            FETCH check_mv INTO mv_name, mv_status, mv_last, mv_state;
            IF mv_status != ''FRESH'' THEN
                DBMS_REFRESH.REFRESH(name => ''SOME_SCHEMA.MINUTE_REFRESH'');
            ELSE
                EXIT;
            END IF;
            EXIT WHEN check_mv%NOTFOUND;

        END LOOP;

    CLOSE check_mv;
END;',
schedule_name => '"SOME_SCHEMA"."REFRESH_MV"',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Refresh MV every 1 Minutes',
auto_drop => FALSE,
enabled => FALSE);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'raise_events', value => dbms_scheduler.job_failed);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
sys.dbms_scheduler.set_attribute( name => '"SOME_SCHEMA"."REFRESH_MV"', attribute => 'restartable', value => TRUE);
sys.dbms_scheduler.enable( '"SOME_SCHEMA"."REFRESH_MV"' );
END;

In the job action, I checked stallness status for specified MV like MV_TEST_BRO but you can check the status for every MV you had. And I activated some attribut to raise event if the job failed, logging level, and restartable.

This job was running for a week now and no problem at all. My MV always FRESH every time. May be this post will help another who face the same problem like me. :D


Complete list of possible database "objects"? When I finished my degree in Programming and Database I remember there being a general list of database "objects" like this:

data types (a list unto itself)
fields
records
tables
views (sort of like stored queries you can re-run)
relations between tables (foreign keys)
transactions (not really an object but something in the database)
connections
users
groups
permissions
triggers
stored procs

Have there been any additions to this list since then?

Some more:

domains
synonyms
sequences
rules
constraints
assertions (though I don't know of any DBMS supporting them)
indexes
roles
user defined (object) types


Can I fail the current transaction from within a trigger? Table DDL:

CREATE TABLE trigtest(a INTEGER IDENTITY(1,1) PRIMARY KEY, b INT)

I currently have

CREATE TRIGGER MaxDelete ON trigtest INSTEAD OF DELETE AS
IF @@rowcount < 1000
DELETE trigtest  WHERE a IN (SELECT a FROM deleted)

However, it requires me to write the last line in a customized manner for every table I apply it to

Instead of the above, is it possible to write something of the sort:

IF @@rowcount > 1000 (OR SELECT COUNT(*) FROM DELETED)
Kill statement
ELSE
Continue statement

IF @@ROWCOUNT > 0
BEGIN
   ROLLBACK TRANSACTION;
END
ELSE
BEGIN
  ... continue ...
END


What signals do the DBWn processes send to LGWR? I'm preparing to take the OCA exam.

I would really like to know what signals are in the following quote from the Process Architecture docs, and how does DBWn know LGWR has completed?

  Before DBWn can write a modified buffer, all redo records that are associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it can write out the data buffers.

Actually, you're not required to have that deep knowledge for OCA exam, you just need to know which processes are required for database to function (PMON, SMON, DBWR, LGWR, CKPT, MMON, MMNL, RECO), some other processes that may be required when you configure other database options such as ARCHIVELOG database mode, what each of these processes does, and how they communicate with each other in rough outline.

As far as inter-process communication (IPC) is concerned, it is quite possible, for example, that on *nix, Oracle uses POSIX calls for communication, whereas on Windows it uses Windows API calls, in other words, the way Oracle implements inter-process communication differs between platforms because these platforms are implemented differently.

If you're preparing for OCA exam, you may already know (and you really should know) the memory architecture of Oracle, and that there's an area of memory called System Global Area (SGA). This memory area is used to keep the information used by many background and foreground processes including server processes and shared server processes (when Shared Server Architecture is used).

There's also the Fixed SGA area within the SGA which contains:

  Information communicated between processes, such as information about
  locks

You can also determine if Oracle allocates some IPC resources on *nix if you run ipcs command before and after you start up the database. (I stumbled upon this in this AskTom article).

Therefore, you can see that Oracle uses both system calls, and shared memory to share information between Oracle processes.


How to change results cell padding in Azure Data Studio In Azure Data Studio, I am trying to change the padding on the cells in the results grid.

According to the settings page, this is editable in the settings.json:

However, when I navigate to that file there is no mention of padding anywhere. Plus, searching online for the correct option to use gives no results.

maybe this helps:

https://github.com/real-napster/azuredatastudio_settings

...
sqlops_settings

My Azure Data Studio Settings

Settings
{
    "sql.messagesDefaultOpen": false,
    "mssql.intelliSense.lowerCaseSuggestions": true,
    "resultsGrid.fontSize": 12,
    "resultsGrid.fontFamily": "Microsoft Sans Serif",
    "editor.fontFamily": "Fira Code, 'Courier New', monospace",
    "editor.fontSize": 14,
    "editor.minimap.enabled": true,
    "editor.minimap.maxColumn": 120,
    "resultsGrid.cellPadding": [
        5,   !!!
        10,  !!!
        15   !!!
    ],
    "editor.cursorBlinking": "phase",
    "editor.smoothScrolling": true,
    "workbench.colorTheme": "One Dark Pro",
    "workbench.iconTheme": "vscode-icons",
    "files.autoGuessEncoding": true,
    "window.titleBarStyle": "custom",
    "editor.smoothScrolling": true,
    "editor.cursorBlinking": "smooth",
    "workbench.enablePreviewFeatures": true
}

marked the Padding entries using !!!


Create hstore from key-value result set I have following columns:

key | value
-----------
foo | 1
bar | 2

Is there a recommended way to turn this into hstore?

{foo => 1, bar => 2}

Just use the function hstore().

There are several overlaoded versions of that functions. One takes a single text array with keys and values. Another one takes two text arrays, one with keys, the other one with values. Either works for you. Demo:

SELECT hstore(array_agg(ARRAY[key, value::text]))     AS option1
     , hstore(array_agg(key), array_agg(value::text)) AS option2
FROM  (VALUES ('foo', 1), ('bar', 2)) AS t(key, value);

option1 requires Postgres 9.5 or later, where array_agg() for multidimensional arrays was introduced. For older versions see:

Is there something like a zip() function in PostgreSQL that combines two arrays?

If value is not type text, you may need the cast I added: value::text

The additional module hstore has to be installed in the database, of course.

CREATE EXTENSION IF NOT EXISTS hstore;

Related:

Assignment of a column with dynamic column name
Is it recommended to install extensions into pg_catalog schema?


Automated SP_WhoIsActive SQL Activity Capturing Issue I have an SQL Agent Job on a production server that keeps failing with the below messages. It is supposed to be capturing SQL Server activity using the SP_WHOISACTIVE stored proc, at regularly scheduled intervals.

  Executed as user:  Warning: Null value is eliminated by an aggregate
  or other SET operation. [SQLSTATE 01003] (Message 8153)

  Warning: Null value is eliminated by an aggregate or other SET
  operation. [SQLSTATE 01003] (Message 8153)

  Warning: Null value is eliminated by an aggregate or other SET
  operation. [SQLSTATE 01003] (Message 8153)

  Violation of PRIMARY KEY constraint 'PK_WhoIsActive'. Cannot insert
  duplicate key in object 'monitoring.WhoIsActive'. The duplicate key
  value is (Jan 20 2017  8:25AM, 109). [SQLSTATE 23000] (Error 2627)

  The statement has been terminated. [SQLSTATE 01000] (Error 3621).

  The step failed.

Any idea what may be causing this?

What steps should I follow to fix this error?

It appears from the Primary Key name that you are running Adam Machanic's sp_WhoIsActive and storing the result in a table.
If that is the case, the warnings are normal and caused by the options you set when calling sp_WhoIsActive. The Primary Key violation is what is causing the job to fail.  Take a look at the definition of the primary key.
I believe you will find that the Primay Key needs to be changed in order to insure uniqueness.
In my implementations for this I leave the table as a heap and do not define a primary key on this table. I only keep a few days of data in the table and do not use it for replication.
Here is an article on how to implement it:
Brent Ozar Implementation of sp_WhoIsActive


SQL Server Identifying Connections that are utilising SQL from an application Is it possible to identify connections in SQL Server 2014 that are utilising SQL that is passed from an application as opposed to calling an SP ?

So you want to differentiate between the app calling stored procedures vs sending T-SQL directly? Sounds like an Extended Event trace would so. Assuming the programmers call these procedures correctly from the API (and not just send a text string with EXEC procname in it).
If you capture sqlserver.sql_batch_completed and sqlserver.rpc_completed I think you will cover the submissions into SQL Server.
When an app doesn't call a proc you should either see sql_batch_completed or rpc_completed. For the later, you can check out the object_name column and if it is sp_executesql, then you have the "not calling stored procedure" case.
When it does call a proc, you will have (hopefully, if they do it right) rpc_completed with something else in the object_name column.
Depending on how sophisticated you want to be, you can play around with event_counter and the histogram targets. Or even store the trace data in a table and query that table.


How do I replace a substring within a string, but only if substring is matched within a regex group? I need to extract information from a malformed json-formatted string. Due to an error, all quotes have been stripped, so instead of:
{"receipt_type":"Production","adam_id":1233,"app_item_id":1233,"more":[{"h":234}]}

I have:
 {receipt_type:Production,adam_id:1233,app_item_id:1233,more:[{h:234}]}

Using
replace(replace(replace(replace(replace(replace("transactionReceipt", ':', '":"'),',','","'),'{','{"'),'}','"}'),':"[{',':[{'), ']"}', ']}')

I can get mostly fine json, HOWEVER this incorrectly turns "receipt_creation_date":"2021-01-24 03:11:53 Etc/GMT" into "receipt_creation_date":"2021-01-24 03":"11":"53 Etc/GMT"
Using
REGEXP_REPLACE(replace(replace(replace(replace(replace(replace("transactionReceipt", ':', '":"'),',','","'),'{','{"'),'}','"}'),':"[{',':[{'), ']"}', ']}'),'\d\d\"\:\"\d\d\"\:\"\d\d','','g')

I can match the 03":"11":"53 and remove it totally, yielding correct JSON.
But I want to retain the time, meaning that I swap ":" with : but ONLY if ":" is within the regex pattern of '\d\d\"\:\"\d\d\"\:\"\d\d'?
Thank you!
edit: a complete malformed record (it's actually an IAP transaction receipt from Apple) looks like
{receipt_type:Production,adam_id:123456789,app_item_id:123456789,bundle_id:example.app.yo,application_version:101,download_id:123456789,version_external_identifier:123456789,receipt_creation_date:2020-03-19 16:18:27 Etc/GMT,receipt_creation_date_ms:1584634707000,receipt_creation_date_pst:2020-03-19 09:18:27 America/Los_Angeles,request_date:2020-03-19 16:18:28 Etc/GMT,request_date_ms:1584634708876,request_date_pst:2020-03-19 09:18:28 America/Los_Angeles,original_purchase_date:2020-02-28 17:55:33 Etc/GMT,original_purchase_date_ms:1582912533000,original_purchase_date_pst:2020-02-28 09:55:33 America/Los_Angeles,original_application_version:101,in_app:[{quantity:1,product_id:com.eample.iap.id,transaction_id:123456789,original_transaction_id:123456789,purchase_date:2020-03-19 16:18:27 Etc/GMT,purchase_date_ms:1584634707000,purchase_date_pst:2020-03-19 09:18:27 America/Los_Angeles,original_purchase_date:2020-03-19 16:18:27 Etc/GMT,original_purchase_date_ms:1584634707000,original_purchase_date_pst:2020-03-19 09:18:27 America/Los_Angeles,is_trial_period:false}]}

I would use two nested regexp_replace() calls:

first to enclose all keys with double quotes
then to enclose all values with double quotes

with data (input) as (
  values ('{receipt_type:Production,adam_id:1233,app_item_id:1233,more:[{h:234}]}')
)
select regexp_replace(
          regexp_replace(input, '(\w+)(:)', '"\1"\2', 'g'),
          '(:)(\w+)(,|})', '\1"\2"\3', 'g')
from data;

returns:
{"receipt_type":"Production","adam_id":"1233","app_item_id":"1233","more":[{"h":"234"}]}

If you want have the integers without quotes, you could run a third regexp_replace that removes the quotes from integers with quotes:
regexp_replace(..., '"([0-9]+)"', '\1', 'g')


MySql service on Windows won't start after changing datadir Tried to change the data directory for a default Windows installation of MySQL. Here's the steps I've taken.

Stopped the MySql service.
Located the data directory of MySql and moved it to another drive, ensuring the path name had no spaces.
Made a copy of the my-default.ini file in C:\Program Files\MySQL\MySQL Server 5.7 then renamed it to my.ini and edited it to look like this:

    [mysqld]
       datadir = F:\ProgramData\MySQL\MySQLServer\Data

Copied the contents of the original data folder into this new data folder.
The MySql Service is running under the NETWORK SERVICE user, so I have given this user full control permission on the new folder.
Deleted the original data folder.
MySql Service fails to start, with no useful error messages appearing in the windows event logs.

As far as I know, the MySql error logs are located in the datadir, so it's not much help looking there?

How can I find and fix what I've done wrong?

If You open Windows Services and check the service properties, You will find something like this:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57

not enough to change datadir in my.ini file, need also point windows to run service with proper my.ini file


Long time, sporadic 'String or Binary data would be truncated.' Wisdom would be appreciated TL;DR

How can I diagnose the source of a very inconsistent, un-reproducable 'String or Binary data would be truncated.' error, when I am quite positive the problem isn't related to user data being to large to fit into SQL objects?

I have a project that is a C# application which uses SQL Server Express 2005 as a local db engine.  The database is used to mostly cache data and very little processing or transforming is done within the local database.

The exception to this is when I need to prepare locally cached data to use when a user prints a report from their application.  In this case, the user calls a stored procedure and passes in a few variables.  The stored procedure starts by deleting all data associated with the document being printed.  In other words, it fully cleans the data cache before it begins.

Next, it goes through a very long and complex process of consolidating and building information.  Much of this information is stored in temp tables and after the process is finished, it copies the temp information into the report data tables.

The problem:  This application has existed for nearly 6 years.  About once every few months, since the inception of the application, a random user has called me telling me that they have received a 'String or Binary data would be truncated.' error.  Furthermore, their application has historically worked perfectly, some times for years.  Then, all of a sudden, they can't print from their application and they receive the above message from SQL Server.

After receiving the error, they can't recover from it.  In fact, every document they try to load (even one's that have worked minutes before the message appeared) are now impossible to generate because SQL Server keeps throwing this error.

I know and understand the nature of this error-- it's telling me that some data is larger than a specific field in which it's being inserted.  However, this doesn't make since in my context for two, main reasons.  Reason #1, I've never been able to reproduce this bug on any PC, but it is 100% consistent on the user's PC once it starts.  Reason #2, across the years I've never been able to force this error to occur, regardless of the inputs I've stored in my associated data tables or passed to the stored procedure as parameters.

The only way I've been able to fix this problem is to flag the user account to delete their local database.  Once the user deletes their db, they rebuild and sync it with the server and everything works perfectly again. The failing stored procedure also has a ludicrous amount of data checks and washing routines, all because of this dumb error.  I've never been able to isolate the problem nor solve it.

I know that this type of question typically begs for the posting of code but I can't provide that.  This is a BIG stored procedure and it contains some propitiatory details. Still, if anyone is aware of a SQL bug that could be caching something within the internals of SQL Server and produces this generic exception, I'd appreciate the information or your expert advice of where else to look for a solution.

Please ask questions.  I will comment and update my post as needed.  This annoyance has gone on long enough.

I had similar error using different databases. All these errors were related to storing unicode varchar into non unicode varchar field. The problem was not displaying always, but it happened only when the text used contained multi byte characters.

So, first of all: does you database uses multi byte NVARCHAR instead of old style VARCHAR? And, second, are you really really really sure that your client cannot input text transmitted to your database server as multi byte characters?


Sql select slow on filter for key, but not for primary key, how can I hint the priority I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?

Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records

fast:

select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
  and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))

https://www.brentozar.com/pastetheplan/?id=S1t8EDQ4V

slow:

select *
from Tourneys t, TourneyPlayers tp
where 1=1
  and t.StartDate > DATEADD(hour,-1,GETDATE())
  and t.Id = tp.TourneyId

https://www.brentozar.com/pastetheplan/?id=rJKzNvmEV

Your two queries are fundamentally different. The fast query is right-joining the TourneyPlayers table to Tourney, and then filtering. Based on the order of operations, it is likely seeking records on Tourney first, then seeking records on TourneyPlayers that match the TourneyId using the correct index.

In the slow query, you're inner joining the tables, so the engine is scanning the TourneyPlayers clustered index and seeking the Tourney records using the date field index. It then has to combine these record sets to produce the final result set.

You need to determine which join you require for the relationship between your tables and the result set you need, then from there you can construct your query correctly and assess your indexing strategy for these tables.

For example, do you want to return every Tourney record regardless of whether there is an associated TourneyPlayer record? Do you want every TourneyPlayer record, regardless of whether there is an associated Tourney record? Do you want only those Tourney records that have an associated TourneyPlayer record?

Once you've answered this question, you will have the answer for your join and can proceed from there with query design and index optimisation.


Merge Problems SQL Server I'm trying to create a procedure that will take the values from a temporary table, and load them into another table. The second table has a composite unique constraint across its three columns (Not including the primary key) I'm using a merge statement to try and handle the upload, and keep getting an error saying
Msg 2627, Level 14, State 1, Line 13

Violation of UNIQUE KEY constraint 'IX_table'.
Cannot insert duplicate key in object 'dbo.table'. The duplicate key value is (aaa, aaa, aaa).

I'm really confused because the whole reason I am using the merge statement is to prevent exactly this from happening. Here's the relevant portion of the procedure (With names changed, and the insertion to the temporary table changed)

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1
           ,str2
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa')

MERGE dbo.table AS t
    USING #TempTable AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable

I'm really confused as to why this isn't detecting duplicates, any ideas?

Thanks in advance

You will get that any time your input file has duplicates.  In theory the MERGE command is doing everything in a batch so if you have the same row in the input data more than once it will try to insert it into the output table more than once giving you the error.  Either dedupe your input file or use a CTE at the beginning of the MERGE to take care of it for you.

create table #TempTable(Id int
                        , str1 varchar(3)
                        , str2 varchar(15)
                        , str3 varchar(10))

INSERT INTO #TempTable
           (str1
           ,str2
           ,str3)
     VALUES
           ('aaa','aaa','aaa'), ('bbb', 'bbb', 'bbb'), ('aaa','aaa','aaa');

WITH dedupe AS (SELECT DISTINCT str1, str2, str3 FROM #TempTable)
MERGE dbo.[table] AS t
    USING dedupe AS s
    ON (t.str1 = s.str1
    AND t.str2 = s.str2
    AND t.str3 = s.str3)
    WHEN MATCHED THEN
        UPDATE SET t.str1 = s.str1
    WHEN NOT MATCHED THEN
        INSERT (str1, str2, str3)
        VALUES (s.str1, s.str2, s.str3);

drop table #TempTable


Can memcached improve performance for InnoDB in mem with a large innodb_buffer_pool_size? I learned that innodb_buffer_pool_size makes InnoDB behave like an in-memory db. If I give innodb_buffer_pool_size enough ram for the entire db could memcached improve further the performance, or memcached would be unnecessary in such a setup?

The InnoDB Buffer Pool is the home of three classes of informational structures

Table Data Pages that have been accessed
Table Index Pages that have been accessed
Insert Buffer Changes to Update Secondary Indexes in ibdata1 (System Tablespace)
See Pictorial Representation of the InnoDB Buffer Pool and ibdata1

If you make the InnoDB Buffer Pool large enough, then all data you have accessed will essentially stay in RAM. Any data you have never read but finally decided to read will then have data and indexes cached in the InnoDB Buffer Pool. Please read my post : What are the main differences between InnoDB and MyISAM? on how to determine the size of a good InnoDB Buffer Pool.

Given a large enough InnoDB Buffer Pool, I would rule out using memcached.


How to Convert a Table into XML Document I have a table say SCOTT.DEPT and I want to convert it into an XML document. Is there anyway to accomplish this task?

Kind Regards,

The easiest way to do this is:

select dbms_xmlgen.getxml( 'select * from dept') as xml
from dual;

There are other way more complicated ways of achieving similar results, but the answer would be significantly longer!


Change entire MySQL drive location? We cloned a VM server and on Server A MySQL is located on the F drive, but on Server B it's located on the E drive. I updated the ini file and the Windows path variables, but it's still trying to pull the data, config file from the F drive.

Is there anyway to update the paths to get this to work?

You can try the following.

  Stop the server a source
  Change the config file parameters such as datadir,basedir,log-directory etc(All variables which are path specific)
  You may need to change log-bin.index file as well in logs directory
  Move the directory to new path
  Start the server
  verify the services

Hope it helps and share any error message if you face any.


Casting an array of texts to an array of UUIDs How can I cast an array of texts into an array of UUIDs?

I need to do a join between two tables: users and projects.

The users table has an array field named project_ids containing the project IDs as text.

The projects table had a UUID field named id.

My initial idea was a query looks like:

SELECT * FROM projects
JOIN users ON
projects.id = ANY(users.project_ids)

But that does not work since users.project_ids are not UUIDs so I tried:

projects.id = ANY(users.project_ids::uuid[])

and even:

projects.id = ANY(ARRAY[users.project_ids]::uuid[])

but neither one works:

ERROR: invalid input syntax for type uuid: ""

UPDATE

@a_horse_with_no_name is definitely right. The best option should be using an array of UUIDs.

The question now is how can I alter an array of text into an array of uuid?

The users table is currently empty (0 records).

I have tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID USING "project_ids"::uuid[];

which generates

ERROR:  result of USING clause for column "product_ids" cannot be cast automatically to type uuid
HINT:  You might need to add an explicit cast.

  ALTER TABLE "users" ALTER COLUMN "product_ids" SET DATA TYPE UUID
  USING "product_ids"::UUID;

I have also tried

ALTER TABLE "users" ALTER COLUMN "project_ids" SET DATA TYPE UUID[] USING "project_ids"::uuid[];

which generates

  ERROR:  default for column "project_ids" cannot be cast automatically
  to type uuid[]

The column is set to an empty array as default.

I'm running PG version 10.4 and project_ids is currently text[] nullable.

Like has been commented, the column project_ids should be uuid[], which would preclude the problem. It would also be more efficient.
To change (with no illegal data in column like you asserted):
ALTER TABLE users ALTER COLUMN project_ids DROP DEFAULT;

ALTER TABLE users
ALTER COLUMN project_ids SET DATA TYPE uuid[] USING project_ids::uuid[];

You had uuid instead of uuid[] by mistake.
And this:

ERROR: default for column "product_ids" cannot be cast automatically to type uuid[]

.. means you have a default value set for the column. That expression cannot be transformed automatically. Remove it before altering the type. You can add a new DEFAULT later.
Fix to original problem
The efficient fix in your original situation is to remove empty strings from the array with array_remove() before the cast (requires Postgres 9.3+):
SELECT *
FROM   users    u
JOIN   projects p ON p.id = ANY(array_remove(u.project_ids, '')::uuid[]);

... after investigating why there can be empty stings in that text[] column.
Related:

Delete array element by index
Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

Fine points

The [INNER] JOIN in your query removes users without valid projects in projects_ids from the result. Typically, you'd want to keep those, too: use LEFT [OUTER] JOIN instead (with users first).

The JOIN folds duplicate entries either way, which may or may not be as desired. If you want to represent duplicate entries, unnest before the join instead.

And if your aim is simply to resolve the array of IDs to an array of project names, you'll also want to preserve original order of array elements:
SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT ARRAY(
      SELECT project_name  -- use the actual column(s) of your case
      FROM   unnest (array_remove(u.project_ids, '')::uuid[]) WITH ORDINALITY AS p(id, ord)
      JOIN   projects USING (id)
      ORDER  BY ord
      )
   ) p(projects) ON true;

db<>fiddle here (loosely based on McNets' fiddle)
Related:

How to preserve the original order of elements in an unnested array?


How to do an efficient case insensitive search As an example, take a city table with 2 columns

city_ref (bigint)
city_name (varchar)

with indexes on both.

I want to do a case independent search by city_name.

I know I can use like but that's not very efficient.
One way would be to store the Uppercase variation of the city_name in another field.
But I can't help but think that there should be a way to use charset or collation to do this.

Can someone point me in the right direction please.

There is no problem using LIKE, as long as it's in a format such as:

SELECT * FROM My_Table WHERE City_Name LIKE 'Abc%';

This can use an index. However, the following cannot.

SELECT * FROM My_Table WHERE City_Name LIKE UPPER('Abc%');

The use of a function (UPPER()) negates the index - in technical terms, the query is not sargable. Note that the query is also non-sargable in a case such as:

SELECT * FROM My_Table WHERE City_Name LIKE  '%Abc..';

The wild card at the beginning of the search string renders any index non-functional.

An ON INSERT and and ON UPDATE trigger into a new field (up_City_Name) setting the input City_Name to UPPER(input City_Name).

That way, you will always know that your City_Name is in upper case, and your server will always be able to use an index, provided you search on the new field up_City_Name.


What's stored in data_dir/base of postgresql I suddenly found found my PostgreSQL out of space. It's a tmp instance running on ram disk. Limited space (around 800M) allowed. Data are not actually stored on it, all table will be dropped after some calculation done.

I checked the folder and found tons of data under base folder:

$ du -h
6.0M    ./pgsql_tmp
706M    ./16384
6.0M    ./12780
6.0M    ./12772
6.0M    ./1
730M    .

My questions are:

What is inside that folder?
Any chance I can clean it out?

This folder is where PostgreSQL keeps all the data you insert on your databases (see PostgreSQL Database File Layout). Each sub-directory is used by a database in you cluster, the files inside them are the actual data of the relations (tables, indexes, sequences, ...).

So, your answer is no! You cannot clean it out, and if you do so, you will lose your data and PostgreSQL server will stop with a panic.

Basically, only files inside the pg_log directory (if any) can be removed. A side note, do not, ever remove files inside pg_xlog, it is not useless as the name may suggest (you didn't ask for it, but I saw happening sometimes).

Tips to solve your problem:

There are some things you can do to use less space in your case, like:

Use another (not in RAM) tablespace for some tables or databases;
Run a VACUUM FULL. But notice that you will need some temporary space to do that, so you can't do it while you are really out of space;
Move pg_xlog to another location;
As you said it is a temporary database, try to remove some indexes that are not been used.


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

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

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

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

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

Too long for a comment.
When I did a
declare
cursor_number number;
description_table dbms_sql.desc_tab;
l_cursor SYS_REFCURSOR;
column_count number;
begin
OPEN l_cursor for SELECT dummy as "Dummy" from dual;
cursor_number:=dbms_sql.TO_CURSOR_NUMBER(l_cursor);
dbms_sql.describe_columns(cursor_number,column_count,description_table);
for k in 1..column_count loop
    dbms_output.put_line(description_table(k).col_name);
    end loop;
end;
/

the ouput is
Dummy

as expected. So SYS_REFCURSOR behaves correctly.
Also
create table productnew (productid  number)
/
declare
cursor_number number;
description_table dbms_sql.desc_tab;
l_cursor SYS_REFCURSOR;
column_count number;
begin
OPEN l_cursor for SELECT p.productid as "ProductID" from productnew p;
cursor_number:=dbms_sql.TO_CURSOR_NUMBER(l_cursor);
dbms_sql.describe_columns(cursor_number,column_count,description_table);
for k in 1..column_count loop
    dbms_output.put_line(description_table(k).col_name);
    end loop;
end;
/

returns
ProductID

So the problem isn't at the database.


How to access user information via sys.fn_dump_dblog I am reviewing sys.fn_dump_dblog and I can see the old states of the records, but I can not see which users are doing the operations.
SPID, Transaction SID columns are always null, how to fix it, or any options?

I am using SQL Server 2016.

My query:

SELECT
    *
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\LogBackup\blabla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (
[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (
([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))
)
AND [PartitionID] IN (72057595034533888)
)
OR ([operation] = 'LOP_HOBT_DDL')

By reference to @Kin, I have come to the solve with the query.

SELECT
    SUSER_SNAME([Transaction SID]) UserName
    ,[Current LSN]
   ,[operation]
   ,[Context]
   ,[Transaction ID]
   ,[Transaction Name]
   ,[Begin Time]
   ,[End Time]
   ,[Flag Bits]
   ,[PartitionID]
   ,[Page ID]
   ,[Slot ID]
   ,[RowLog Contents 0]
   ,[Log Record]
   ,[RowLog Contents 1]
   INTO ##temp
FROM sys.fn_dump_dblog(NULL, NULL, NULL, 1, 'E:\balbla.trn', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
WHERE (([operation] IN ('LOP_BEGIN_XACT', 'LOP_COMMIT_XACT', 'LOP_ABORT_XACT'))
OR ([operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
AND (([context] IN ('LCX_HEAP', 'LCX_CLUSTERED', 'LCX_MARK_AS_GHOST'))
OR ([context] = 'LCX_TEXT_MIX'
AND (DATALENGTH([RowLog Contents 0]) IN (0, 14, 28)))))
OR ([operation] = 'LOP_HOBT_DDL'))

SELECT t2.UserName,t.operation,object_name(p.object_id),count(1) FROM ##temp t
LEFT JOIN ##temp t2 ON t.[Transaction ID]=t2.[Transaction ID] AND t2.UserName is not null
INNER JOIN sys.partitions p ON p.partition_id=t.PartitionId
WHERE t.[operation] IN ('LOP_INSERT_ROWS', 'LOP_DELETE_ROWS', 'LOP_MODIFY_ROW')
GROUP BY t2.UserName,t.operation,object_name(p.object_id)


Postgres foreign data wrappers weird join behavior So I have tables in two different databases that I need to analyze together. I am using fdw to do so. However I am running in to weird behavior with my joins.

Keep in mind the following snippets are just accessing the foreign database.

When I do:

SELECT *
FROM tds tr
INNER JOIN dq_infos dq ON tr.id = dq.id
WHERE tr.created_time >= '2020-04-22'

it runs very fast, however the result of this join will have duplicated columns so I can't really do anything with it.

When instead do:

SELECT tr.id
FROM tds tr
INNER JOIN dq_infos dq ON tr.id = dq.id
WHERE tr.created_time >= '2020-04-22'

it takes roughly 10 times as long.

I thought this can't be right so I looked at the query plan and it makes even less sense.

The query plan for the first query is:

Foreign Scan  (cost=100.00..177.23 rows=52 width=908) (actual time=398.276..30161.096 rows=449408 loops=1)
  Output: *
  Relations: (public.tds tr) INNER JOIN (public.dq_infos dq)
  Remote SQL: SELECT * FROM (public.tds r1 INNER JOIN public.dq_infos r2 ON (((r1.id = r2.id)) AND ((r1.created_time >= '2020-04-22 00:00:00'::timestamp without time zone))))
Planning Time: 0.605 ms
Execution Time: 30180.511 ms

(I replaced the full column list with * in this execution plan to make it easier to read)

And the query plan for the second query is:

Merge Join  (cost=580.61..799.37 rows=14259 width=4) (actual time=195048.232..195469.693 rows=449617 loops=1)
  Output: tr.id
  Merge Cond: (tr.id = dq.id)
  Buffers: shared hit=4, temp read=176695 written=178369
  ->  Sort  (cost=214.47..216.91 rows=975 width=4) (actual time=4195.650..4355.824 rows=449618 loops=1)
    Output: tr.id
    Sort Key: tr.id
    Sort Method: external merge  Disk: 6552kB
    Buffers: shared hit=4, temp read=2283 written=2375
    ->  Foreign Scan on public.tds tr  (cost=100.00..166.06 rows=975 width=4) (actual time=412.259..3815.536 rows=475864 loops=1)
      Output: tr.id
      Remote SQL: SELECT id FROM public.tds WHERE ((created_time >= '2020-04-22 00:00:00'::timestamp without time zone))
  ->  Sort  (cost=366.15..373.46 rows=2925 width=4) (actual time=188117.092..190081.599 rows=12577570 loops=1)
    Output: dq.id
    Sort Key: dq.id
    Sort Method: external sort  Disk: 221568kB
    Buffers: temp read=174412 written=175994
    ->  Foreign Scan on public.dq_infos dq  (cost=100.00..197.75 rows=2925 width=4) (actual time=1.041..33499.615 rows=12577570 loops=1)
      Output: dq.id
      Remote SQL: SELECT id FROM public.dq_infos
Planning Time: 0.311 ms
Execution Time: 195648.893 ms

This is clearly the reason for it being slower though I can't figure out why it would do this.

You never ANALYZEd the foreign tables, so the estimates are way off, and PostgreSQL chooses a bad execution plan.

ANALYZE tds, dq_infos;

should improve the situation.

Different from regular tables, foreign tables are not handled by autoanalyze.