Removing Archive Logs after PostgreSQL PITR online-recovery I'm using pgpool-II + PostgreSQL 8.4 on three servers: primary + standby1 + standby2.
Replication mode is "on"
Load balance mode is "on" (between primary and standby1)

I followed the official tutorial to configure online recovery with PITR:
http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery

There is a tar command in script "copy-base-backup":

  tar -C /data -zcf pgsql.tar.gz pgsql

So all my PG cluster directory being copied while script is running:

ls -1 /srv/pg/data/
PG_VERSION
archive # directory with postgres archive files
backup_label.old
base
global
pg_clog
pg_hba.conf
pg_ident.conf
pg_log
pg_multixact
pg_stat_tmp
pg_subtrans
pg_tblspc
pg_twophase
pg_xlog
pgpool_recovery
pgpool_recovery_pitr
pgpool_remote_start
postgresql.conf
postmaster.opts
postmaster.pid
recovery.conf
recovery.done

How can I safely remove old archive logs from primary and from standby nodes? There are about 30Gb of archives per day on my nodes.

The documentation on PITR recovery that comes with pgpool is really incomplete, and downright misleading at points.  It suggests keeping all the archive files on the master than then having a recovery_command that uses scp to copy from there to each standby.  That's worthless.  Each standby needs to have its own copy of the archive, or there's no real redundancy there.  If the master fails in that situation, you have nothing useful.  The archive_command on the master should distribute files to the standby systems instead.  There is no need to save a copy of the archive on the master at all if you do that.

For cleaning up the archives on each standby, the poor recommendation for the recovery_command given there shouldn't be followed either.  Instead pg_standby should be called as the recovery command.  See the real documentation on Warm Standby Servers for more details.  If you're using pg_standby, one of the things it should gets passed is the "%r" parameter, which is the information needed to remove old archive logs.  Use pg_standby that way and it will clean up the archives for you.

The main non-trivial part here is writing an archive_command script that properly copies data to both standby servers.  I don't have a good example to point at, but what you need to do is check for errors after each copy, and provide a success return code only if both copies happen.


It sounds like the table is on the cusp of a cutover for the query optimizer between the estimated total rows and estimated rows being looked up.

The use/ignore index syntax exists for situations like this.

The pitfall to this approach is when the table dynamics change in the future such that it's really not the best choice.


Reason for NOT IN behaviour sql> SELECT * FROM runners;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | John Doe     |
|  2 | Jane Doe     |
|  3 | Alice Jones  |
|  4 | Bobby Louis  |
|  5 | Lisa Romero  |
+----+--------------+

sql> SELECT * FROM races;
+----+----------------+-----------+
| id | event          | winner_id |
+----+----------------+-----------+
|  1 | 100 meter dash |  2        |
|  2 | 500 meter dash |  3        |
|  3 | cross-country  |  2        |
|  4 | triathalon     |  NULL     |
+----+----------------+-----------+

What will be the result of the query below?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

The above query will return nothing because the races table contain null winner_id. But what is the reason behind this?

SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

Is something like this (actual values from races):

SELECT * FROM runners WHERE
id <> 2 and
id <> 3 and
id <> null;

id <> null is unknown by definition, so the whole where clause evaluates to either false (for ids: 2, 3) or unknown (for ids: 1, 4, 5) because of the and operators, thus no result returned.

If you want to run the query disregarding the nulls in races, you can use:

SELECT * FROM runners
WHERE id NOT IN
      (SELECT winner_id FROM races WHERE winner_id IS NOT NULL);


Where do you go to request new features in SSMS? I'd like to ask Microsoft:

To color code the pages in SSMS based on the server they are connected to
Make it go ding when the query results are returned.

Question: How do you ask for those features?

To color code the pages in SSMS based on the server they are connected to

In the registered servers pane, right-click on server, go to properties, then the connection properties tab. Select "custom color".

This is also visible in the connection dialog. Click options -> Connection Properties tab -> Use custom color (at the lower left).

  Make it go ding when the query results are returned.

Tools | Options... | Query Results | Play the Windows default beep when a query batch completes.

The latest version of SQL Server Management Studio is available at Download SQL Server Management Studio (SSMS).


Database Design - Cardinality I am currently studying for my MTA Database Fundamentals cert, and I came across this question during my studies.  However, the answer doesn't make any sense to me.  Here is the question/answer:

It is my understanding that 'cardinality' defines the relationship of the database entities, such as if the relationship is 1:1, 1:many, or many:many.  How does removing a row affect the cardinality of this database entity at all?

Question/Answer source is from EnsurePass.

The answer may be referring to cardinality as in the number of rows within the table.  This link has an explanation of each definition of cardinality in a database:

http://www.programmerinterview.com/index.php/database-sql/cardinality-in-sql/


What is the meaning of Microsoft Compatibility Level? Setting cmptlevel to 80 on a SQL-Server 2005 inhibits the use of PIVOT and APPLY, but doesn't affect the use of Analytical Functions. Is there any rational behind this?

MSDN ALTER DATABASE Compatibility Level explains the behavior of the various levels. (just scroll down and you will find the comparison tables for the following topics )

you can find more specific comparison tables if you just change the Version from MSDN top menu (they are linked for top 3 versions in following list)

80 = SQL Server 2000
90 = SQL Server 2005
100 = SQL Server 2008 and R2
110 = SQL Server 2012
120 = SQL Server 2014
Differences Between Lower Compatibility Levels and Level 120

sample line (The older query optimizer is used instead of new SQL Server 2014 engine which includes substantial improvements to the component that creates and optimized query plans )

Differences Between Lower Compatibility Levels and Level 110


How to determine if an Oracle table is locked or not? We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2.

Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?

Following query gives details of all locks.

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID


sqlcmd is running but database shown no running query We are facing this weird issue on our Azure setup. The database is SQLServer and client is CentOS.
We fire queries using sqlcmd. Sometimes it happens that you see the sqlcmd running, but there is no runnable/sleeping query running in db ( seen using sp_who2 ). This happens only for DML queries, for the queries of the sort insert into < sometable > select < someslectquery >.
And this happens like 25% of time. This occurs even when we run some procedure. The procedure stops at some insert .. select query. But sqlcmd still show s it running.
We have another setup with AWS with same database and same Centos, but we haven't seen this thing happening there.
On same azure database, when we run the queries using windows machine using SSMS then the query completed on back-end. We tested this multiple times.
Has anyone face any such thing? Where to look for the issue?

Probably not too many people using CentOS & sqlcmd so I would investigate that first or
maybe you should try out mssql-cli instead ?
https://github.com/dbcli/mssql-cli/tree/master/doc/installation


Sub Query vs Table Filter I have have been assigned to resolve Performance issue of a Stored Procedure.

Select TableTwo.Column1, DatabaseTwo..TableThree.Column2, Sum(TableTwo.Column3) As Column3
     --- Section 1
    from TableOne
    inner join TableTwo on TableOne.Column4 = TableTwo.Column4
    inner join DatabaseTwo..TableThree on TableTwo.Column1 = DatabaseTwo..TableThree.Column5
    inner join TableFour on TableOne.Column6 = TableFour.Column6
    inner join TableFive on TableFour.Column7 = TableFive.Column7
    inner join TableSix on TableFour.Column8 = TableSix.Column8
    inner join TableSeven on TableSeven.Column7 = TableFive.Column7
    inner join TableEight on TableOne.Column9 = TableEight.Column9
    inner join TableNine on TableFour.Column10 = TableNine.Column10
    inner join TableTen on TableFive.Column11 = TableTen.Column11
    inner join TableEleven on TableFive.Column12 = TableEleven.Column12
    inner join TableTwelve on TableTwelve.Column7 = TableFive.Column7 and TableTwelve.Column9 = TableOne.Column9
    inner join TableThirteen on TableThirteen.Column13 = TableFour.Column13
    inner join TableSeventeen on TableSeventeen.Column14 = TableOne.Column14
    where TableFive.Column15 = 3

    --- Section 2
    and TableOne.Column6 in  (Select TableOne.Column6
                                from TableOne, TableTwo, TableFour
                                Where TableOne.Column4 = TableTwo.Column4
                                and TableOne.Column6 = TableFour.Column6
                                and TableFour.Column16 >= @StartDate
                                and TableFour.Column16< @EndDate )

    --- Section 3
    and TableFour.Column6 In (Select TableFour.Column6
                                from TableFour,TableEightteen,TableNineTeen
                                Where TableFour.Column7 = TableNineTeen.Column7
                                and TableEightteen.Column17 = TableNineTeen.Column17
                                and TableFour.Column10 = TableNineTeen.Column10
                                and TableEightteen.Column18 > TableFour.Column16
                                )

    --- Section 4
    and TableFour.Column7 Not In (SELECT Column7
                                FROM TableFour
                                GROUP BY Column7, Column10
                                HAVING ( COUNT(Column7) > 1 )
                                and Column7 = TableFour.Column7
                                and Column10 = TableFour.Column10
                                )

    --- Section 5
    and TableOne.Column6 in (Select TableOne.Column6
                                 from TableOne, TableTwo, TableFour , TableFive
                                 Where TableOne.Column4 = TableTwo.Column4
                                 and TableOne.Column6 = TableFour.Column6
                                 and TableFive.Column7 = TableFour.Column7
                                 and TableFive.Column12=@Column12
                                 )

    Group By TableTwo.Column1, DatabaseTwo..TableThree.[Column2]
    Order By  TableTwo.Column1, DatabaseTwo..TableThree.[Column2]

My Question is as per my understanding

Since Section2 has all tables that Section1 has with same join conditions therefore filtering condition and TableFour.Column16 >= @StartDate and TableFour.Column16< @EndDate of section2 should be transferred to Section1 and this will result in same query. AM I right?
Same scenario is with section6 which has all tables that Section1 but with one extra filter statement and TableFive.Column12=@Column12

and by removing section2 and section6 we can avoid extra conditions

Let's just look at TableOne, TableTwo, and TableFour
Most of the condition in the "in where" in are already covered in the join
And suggest you use join on for all

Select TableTwo.Column1, DatabaseTwo..
from TableOne
inner join TableTwo  on TableOne.Column4 = TableTwo.Column4
inner join TableFour on TableOne.Column6 = TableFour.Column6
where 1 = 1
--- Section 2
and TableOne.Column6 in  (Select TableOne.Column6
                            from TableOne, TableTwo, TableFour
                            Where TableOne.Column4 = TableTwo.Column4  -- covered in base join
                            and TableOne.Column6 = TableFour.Column6   -- covered in base join
                            and TableFour.Column16 >= @StartDate
                            and TableFour.Column16 <  @EndDate )

Pretty sure this is the same query
Not sure if it is faster but I doubt is is slower
The query optimizer has a better chance of filtering early

Select TableTwo.Column1, DatabaseTwo..
from TableOne
inner join TableTwo  on  TableOne.Column4 = TableTwo.Column4
inner join TableFour on  TableOne.Column6 = TableFour.Column6
                     and TableFour.Column16 >= @StartDate
                     and TableFour.Column16 <  @EndDate

as for

and TableFour.Column6 In (Select TableFour.Column6
                                from TableFour,TableEightteen,TableNineTeen
                                Where TableFour.Column7 = TableNineTeen.Column7
                                and TableEightteen.Column17 = TableNineTeen.Column17
                                and TableFour.Column10 = TableNineTeen.Column10
                                and TableEightteen.Column18 > TableFour.Column16
                                )

pretty sure that can be

join TableEightteen on  TableEightteen.Column18 > TableFour.Column16
join TableNineTeen  on  TableFour.Column7  = TableNineTeen.Column7
                    and TableFour.Column10 = TableNineTeen.Column10
                    and TableEightteen.Column17 = TableNineTeen.Column17


Combine tree SELECT statment into one I am currently in situation that I dont know what to do, and have no idea how to solve problem.
The problem is that I have one SELECT statment like
SELECT PaymentType FROM ata WHERE ATAID = $ataId

After that I have IF/ELSE statment like
if ($paymentType != 2 && $paymentType != 3) {
            $sql = "SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE
                        aa.ProjectId = $projectId
                    AND
                        aa.AtaId = $ataId
                    AND
                        wr.status = 5";
        } else {
            $sql = "SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        ata a
                    ON
                        a.ATAID = aa.ataId
                    INNER JOIN
                        invoices i
                    ON
                        i.Id = a.financeId
                    WHERE
                        aa.ProjectId = $projectId
                    AND
                        aa.AtaId = $ataId
                    AND
                        i.Status = 6";
        }

As you can see the code is write in PHP but I need to  write this in Oracle SQL function.
So far what I try
           CASE WHEN PaymentType !=  2 and paymentType != 3 THEN
             SELECT PaymentType FROM ata WHERE ATAID = 1889
               OR
                    SELECT
                        SUM(aa.Total) AS total
                    FROM
                        ata_articles aa
                    INNER JOIN
                        weekly_report wr
                    ON
                        wr.id = aa.wrId
                    WHERE
                        aa.ProjectId = 141
                    AND
                        aa.AtaId = 1
                    AND
                        wr.status = 5
            ELSE
                SELECT
                    SUM(aa.Total) AS total
                FROM
                    ata_articles aa
                INNER JOIN
                    ata a
                ON
                    a.ATAID = aa.ataId
                INNER JOIN
                    invoices i
                ON
                    i.Id = a.financeId
                WHERE
                    aa.ProjectId = 1
                AND
                    aa.AtaId = 1
                AND
                    i.Status = 6
            END CASE;

I have try a multiple solution but none of these dosn't work. Can someone guide me and tell me what is the best solution to solve this issue ?

The PL/SQL equivalent would be something like this:
create or replace function get_article_total
    ( inProjectId    ata_articles.projectid%type
    , inAtaID        ata_articles.ataid%type
    , inPaymentType  number )
    return number
as
    articleTotal number;
begin
    if inPaymentType in (2,3) then
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join ata a on a.ataid = aa.ataid
               join invoices i on i.id = a.financeid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    i.status = 6;

    else
        select sum(aa.total) into articleTotal
        from   ata_articles aa
               join weekly_report wr on wr.id = aa.wrid
        where  aa.projectid = inProjectId
        and    aa.ataid = inAtaID
        and    wr.status = 5;

    end if;

    return articleTotal;
end get_article_total;


Default instance name in a cluster I have a cluster set up with two virtual instance names

  WIN2K8SQL1\INST1

  WIN2K8SQL2\INST2

that was set up by the IaaS company.
Now developers are complaining about the naming convention so we decided to create an alias in the DNS. But alias only works for WIN2K8SQL1 and not for INST1, so that they need to use that in the connection strings:

  MEANINGFULNAME\INST1

Is there a way to name the instances on the cluster MSSQLSERVER?

DNS CNAMEs take care of the host name only, but do absolutely nothing for the port number, which is what the instance name represents.

If you want to use CNAMEs for this, you will have to make the two instances behave as if they both were default instances: in other words they will both have to listen on port 1433.

Each instance in a cluster has its own IP address, so this is totally possible.
More information can be found on this blog post.

In your case, you could set up something like this:

MEANINGFULNAME1 -- > WIN2K8SQL1\INST1
MEANINGFULNAME2 -- > WIN2K8SQL2\INST2

Where MEANINGFULNAME1 is a CNAME for WIN2K8SQL1 and MEANINGFULNAME2 is a CNAME for WIN2K8SQL2.


What is the correct way to ensure unique entries in a temporal database design? I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read this answer, but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).

This is what I have, but it allows me to insert a new record for a store with a different effective date.

Table Definition:

/****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
DROP TABLE [Z_STORE_TEAM]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U'))
BEGIN
CREATE TABLE [Z_STORE_TEAM](
    [STORENUM] [int] NOT NULL,
    [TEAM] [varchar](10) NULL,
    [EFFECTIVE] [date] NOT NULL,
    [FINISHED] [date] NULL,
PRIMARY KEY CLUSTERED
(
    [STORENUM] ASC,
    [EFFECTIVE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Sample Data:

INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date))
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL)
INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)

Instead of Update Trigger:

CREATE TRIGGER [tr_ZStoreTeam_update]
   ON  [Z_STORE_TEAM]
   INSTEAD OF UPDATE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)
    SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE
    FROM inserted I
    INNER JOIN PCR.Z_STORE_TEAM ST
        ON I.STORENUM = ST.STORENUM

    UPDATE ST
    SET FINISHED = GETDATE()
    FROM PCR.Z_STORE_TEAM ST
    INNER JOIN inserted I
        ON ST.STORENUM = I.STORENUM
        AND ST.EFFECTIVE = I.EFFECTIVE
END

GO

The safest way to do this is to enforce your business rules using the built-in referential integrity constraints, as Alexander Kuznetsov describes in his article, "Storing intervals of time with no overlaps".

Applying the techniques listed there to your sample table results in the following script:

CREATE TABLE [Z_STORE_TEAM](
    [STORENUM] [int] NOT NULL,
    [TEAM] [varchar](10) NULL,
    [EFFECTIVE] [date] NOT NULL,
    [FINISHED] [date] NULL,
    PRIMARY KEY CLUSTERED
    (
        [STORENUM] ASC,
        [EFFECTIVE] ASC
    )
) ON [PRIMARY];

INSERT [Z_STORE_TEAM]
    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
    (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)),
    (1, N'2', CAST(0x81380B00 AS Date), NULL),
    (2, N'1', CAST(0x01380B00 AS Date), NULL);

Modifications:

-- New column to hold the previous finish date
ALTER TABLE dbo.Z_STORE_TEAM
ADD PreviousFinished date NULL;
GO
-- Populate the previous finish date
UPDATE This
SET PreviousFinished = Previous.FINISHED
FROM dbo.Z_STORE_TEAM AS This
CROSS APPLY
(
    SELECT TOP (1)
        Previous.FINISHED
    FROM dbo.Z_STORE_TEAM AS Previous
    WHERE
        Previous.STORENUM = This.STORENUM
        AND Previous.FINISHED <= This.EFFECTIVE
    ORDER BY
        Previous.FINISHED DESC
) AS Previous;
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_PreviousFinished
UNIQUE (STORENUM, PreviousFinished);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_PreviousFinished_NotAfter_Effective
CHECK (PreviousFinished = EFFECTIVE);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_FINISHED
UNIQUE (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT FK_STORENUM_PreviousFinished
FOREIGN KEY (STORENUM, PreviousFinished)
REFERENCES dbo.Z_STORE_TEAM (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_EFFECTIVE_Before_FINISHED
CHECK (EFFECTIVE < FINISHED);

An attempt to insert the fourth row of sample data now fails with an error message:

INSERT [Z_STORE_TEAM]
    ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
    (2, N'2', '20140201', NULL);

Please read Alex's article to understand how these constraints ensure your table data will always be valid. Having a set of constraints enforce your data integrity means no trigger code is required.

Related article by the same author:

Modifying Contiguous Time Periods in a History Table


How are transactions logs inserted? I have an ms-access database at work that has a table that's a transaction log.

The table contains the following fields:

Date - The date the transaction took place.

Table_Name  - Name of the table the field was changed in.
Table_Field - The field in the table that was changed.
Old_Value - The original value of the field (if any).
New_Value - The new value of the field that was set during this transaction.

Now in a transaction log table like this, are these records normally set by the programming  API of the system that modifies the database, a function of the database itself, or are they tiggered by something like a database trigger?   Or does that depend on the rdbms that is being used?

Firstly, the correct term for this is an audit table (or maybe audit history). Transaction logging is something entirely different (that's a core part of the DBMS which logs all active transactions to guarantee the ACID properties) - the DBMS transaction log (which is in a binary, very-hard-to-read format) is reused once it's no longer needed ("once it's no longer needed" is a bit vague, but a full dissertation on transaction log semantics is a bit beyond the scope of this answer), where an audit history stays forever, as long as you don't clear the table.

To answer the actual question - it should be done inside the database (triggers being the easiest way). The basic reason for that is you want anything that's integral to the data (i.e. constraints, security rules, audit history etc.) to happen, no matter which application is using the database.

Additionally:

What data type are you using for the Old_Value and New_Value fields?
The style of audit table you're using there is going to lead to one row in the table for each field of each row you insert/update/delete, which means your audit history table is going to grow as fast as all of your other tables put together (and then some). You'll need to keep an eye on it to make sure it doesn't spiral out of control (I'd recommend clearing the table periodically - only after you've backed it up, of course).
Another option (which I'd probably use if you need to refer to audit history frequently) is to have an audit table for each table in your database - so if you have Person(ID, Name), you'd have audit_Person(whoChanged, whenChanged, ID, Name), and then on update or delete, store the state of the row before the change (you don't need to do insert because you can just look in the table for the current state), along with the login of the user that changed it and the time.

This means no more audit table with 2 billion rows and makes querying for audit history significantly more intuitive (you get entire rows back in one piece, instead of having to go field-by-field) but does have the downside of duplicating schema changes (if you add a column to Person, you also need to add it to 'audit_Person'), and quite possibly being larger overall than the single-table approach.


PostgreSQL failover: Who is responsible for handling it? How do clients connect to the new leader? On a high level, consider two PostgreSQL instances on two separate nodes: the leader and its hot standby. Replication from leader to follower is configured.

Consider that the leader node crashes. Two questions:

Who is responsible for promoting the follower to be the new leader? Do you have to setup some kind of heartbeat system, or is this handled automatically by PostgreSQL?
The clients relying on PostgreSQL now have to be aware that the leader was changed. What is the best way to make this seamless for the client? It there an automatic procedure that PostgreSQL offers?

For question 2. some ideas come to my mind, in which an external server or the hot standby follower would heartbeat the leader. If the leader is considered dead, do one of the following:

The client connects to the PostgreSQL instance via hostname. On failover, the DNS entry would be changed to point to the new leader node by the heartbeat server.
The client connects to the PostgreSQL instance via IP. Something along the veins of a floating IP, in which the heartbeat server broadcasts an ARP request to update the MAC tables of the switches, in order to point the IP to the new leader.
The client connects to the PostgreSQL instance via an IP that is provided by ZooKeeper. The heartbeat server would update the entry on ZooKeeper to the new leader.

Could you help me answer the first two questions and assert the validity of the proposals for question 2.?

https://www.postgresql.org/docs/12/warm-standby-failover.html says:

  PostgreSQL does not provide the system software required to identify a
  failure on the primary and notify the standby database server. Many
  such tools exist and are well integrated with the operating system
  facilities required for successful failover, such as IP address
  migration.

Yes you have to setup something.

PostgreSQL does not provide automatic procedure for clients.


How to use GENERATED from SELECT with JOIN? This is my database. FoodItems is just normal table but I want to use virtual columns to generate all the related data, e.g. Servings.kcal is equal to related (FieldItem.kcal*unit)/100 (the data is specified in kilocalories per 100g/100ml):

CREATE TABLE IF NOT EXISTS `DietDB`.`Servings` (
      `id` INT UNSIGNED NOT NULL,
      `FoodItem_id` INT UNSIGNED NOT NULL,
      `name` VARCHAR(255) NOT NULL,
      `units` DOUBLE UNSIGNED NULL,
      `kcal` INT GENERATED ALWAYS AS (SELECT (FoodItems.kcal*units)/100 FROM Servings JOIN FoodItems ON Servings.FoodItem_id = FoodItems.id) VIRTUAL,
      `carbohydrate` DOUBLE UNSIGNED NULL,
      `carbohydrate_sugar` DOUBLE UNSIGNED NULL,
      `fat` DOUBLE UNSIGNED NULL,
      `fat_saturated` DOUBLE UNSIGNED NULL,
      `fat_trans` DOUBLE UNSIGNED NULL,
      `protein` DOUBLE UNSIGNED NULL,
      `fibre` DOUBLE UNSIGNED NULL,
      PRIMARY KEY (`id`, `FoodItem_id`),
      INDEX `fk_Servings_FoodItems1_idx` (`FoodItem_id` ASC),
      UNIQUE INDEX `name_UNIQUE` (`name` ASC),
      CONSTRAINT `fk_Servings_FoodItems1`
        FOREIGN KEY (`FoodItem_id`)
        REFERENCES `DietDB`.`FoodItems` (`id`)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB

This is obviously wrong as the query returns the whole table, not single value and it yells an error... but it presents what I'm trying to achieve there. How could it be done?

According to the MySQL documentation, GENERATED columns cannot have subqueries:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

Subqueries, parameters, variables, stored functions, and user-defined functions are not permitted.

...

What you could do, is use a view:
CREATE VIEW DietDB.Servings_All        -- pick an appropriate name
AS
SELECT s.*,
       CAST((fi.kcal * s.units) / 100 AS UNSIGNED INT) AS kcal
FROM DietDB.Servings AS s
  LEFT JOIN DietDB.FoodItems AS fi
    ON s.FoodItem_id = fi.id ;


Select rows with line breaks: Unexpected result I'm trying to find rows in a table where there are line breaks.

I found some sample code that lets me do this:

where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

However, I'm getting an unexpected result.

create table test1 (f1 varchar(25));

insert into test1(f1) values ('Note');
insert into test1(f1) values ('Nota');   --<-- Slight spelling difference

select * from test1 where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

F1
-------------------------
Note

Why does the query return a record, if there isn't a carriage return? And why does it only return a single record?

Prediction: I'm having a brain fart and/or I have no idea what I'm doing. It is Monday morning, after all.

DUMP returns a VARCHAR2.

select f1, dump(f1) from test1;

F1            DUMP(F1)
------------- ----------------------------
Note          Typ=1 Len=4: 78,111,116,101
Nota          Typ=1 Len=4: 78,111,116,97

2 rows selected.

So searching for '%,10%' will only return 'Note' because it's dump representation contains 101, where as 'Nota' does not.

If you insert those values with a return character/line break, the result would be:

select  f1, dump(f1)
from test1 where
dump(f1) like '%,10%'
and  Not (dump(f1) like '%100%');

F1            DUMP(F1)
------------  -----------------------------------------------
Note          Typ=1 Len=4: 78,111,116,101
Note          Typ=1 Len=5: 78,111,116,101,10
Nota          Typ=1 Len=5: 78,111,116,97,10

You get 3 rows returned. The 2 rows ending with ',10' contain the the line breaks.


How do I selectively purge MySQL table rows using a list of column values? I am new to databases and working with MySQL. I have a large MySQL table with the following columns: Id, Link, Timestamp.

And I have a list of links. I want to keep only the rows with link column that matches an entry in this list.

Any pointers to how I accomplish this?

DELETE FROM MyBigUberTable WHERE Link NOT IN (...);

That should do it. Just replace the ellipsis with a comma-separated list of of Link values (or a SELECT statement that produces said list).


MySQL said "InnoDB: Initializing buffer pool, size = 128.0M", does it mean it is using 128MB RAM? I am just using a very few InnoDB tables (e.g. less than 1MB), but during MySQL startup, it said

InnoDB: Initializing buffer pool, size = 128.0M

Does it mean even I am using in such a small size, the server still use 128M RAM?

According to the MySQL Documentation, the InnoDB Buffer Pool is set to 128MB by default in MySQL 5.5.

You can show how much of the InnoDB Buffer Pool is in use and reserved as follows:

SELECT
    BufferPoolUsed BytesUsed,
    (BufferPoolUsed / power(1024,1)) UsedKB,
    (BufferPoolUsed / power(1024,2)) UsedMB,
    (BufferPoolUsed / power(1024,3)) UsedGB,
    BufferPoolReserved BytesReserved,
    (BufferPoolReserved / power(1024,1)) ReservedKB,
    (BufferPoolReserved / power(1024,2)) ReservedMB,
    (BufferPoolReserved / power(1024,3)) ReservedGB
FROM
    (
        SELECT
            (A.num * B.num) BufferPoolUsed,
            (A.num * C.num) BufferPoolReserved
        FROM
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_page_size') A,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_data') B,
            (SELECT variable_value num FROM information_schema.global_status
            WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ) AA
;

I have it set to 256M in MySQL 5.5.12 for Windows. Here is my output:

mysql> SELECT
    ->     BufferPoolUsed BytesUsed,
    ->     (BufferPoolUsed / power(1024,1)) UsedKB,
    ->     (BufferPoolUsed / power(1024,2)) UsedMB,
    ->     (BufferPoolUsed / power(1024,3)) UsedGB,
    ->     BufferPoolReserved BytesReserved,
    ->     (BufferPoolReserved / power(1024,1)) ReservedKB,
    ->     (BufferPoolReserved / power(1024,2)) ReservedMB,
    ->     (BufferPoolReserved / power(1024,3)) ReservedGB
    -> FROM
    ->     (
    ->         SELECT
    ->             (A.num * B.num) BufferPoolUsed,
    ->             (A.num * C.num) BufferPoolReserved
    ->         FROM
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_page_size') A,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_data') B,
    ->             (SELECT variable_value num FROM information_schema.global_status
    ->             WHERE variable_name='Innodb_buffer_pool_pages_total') C
    ->     ) AA
    -> ;
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
| BytesUsed | UsedKB | UsedMB   | UsedGB             | BytesReserved | ReservedKB | ReservedMB | ReservedGB |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
|   6864896 |   6704 | 6.546875 | 0.0063934326171875 |     268435456 |     262144 |        256 |       0.25 |
+-----------+--------+----------+--------------------+---------------+------------+------------+------------+
1 row in set (0.00 sec)

mysql>

You can set this value when MySQL starts up. For example:

To set it to use 1G of RAM

[mysqld]
innodb_buffer_pool_size=1G

To set it to use 256MB of RAM

[mysqld]
innodb_buffer_pool_size=256M

After changing it in my.cnf, you must restart mysql for it to take effect.


Postgres 9.3 - Problem to initialize folder as a data folder I have a problem when I want to initialize a folder as a data folder:

postgres@ccruzado-test01:~$ /usr/lib/postgresql/9.3/bin/initdb -D /datadrive/postgresql/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /datadrive/postgresql ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
creating configuration files ... ok
creating template1 database in /datadrive/postgresql/base/1 ... LOG:  could not link file "pg_xlog/xlogtemp.1716" to "pg_xlog/000000010000000000000001" (initialization of log file): Operation not supported
FATAL:  could not open file "pg_xlog/000000010000000000000001": No such file or directory
child process exited with exit code 1
initdb: removing data directory "/datadrive/postgresql"

The directory /datadrive/postgresql/ is a drive mounted by CIFS in FSTAB

//prueba /datadrive cifs vers=2.1,credentials=/home/user/.smbcredentials,dir_mode=0777,file_mode=0777,nounix 0 0.

The OS is Ubuntu 14.04

Creating the data directory on Linux includes creating hard links, which is not supported on CIFS shares. This is why the creation is failing.

It's usually not a good idea to create data stores / clusters on remote shares, it might lead to issues if the share disappears in the middle of using it.


Optimizing Negative Comparison in SQL In a SQL Server 2016 environment, I have a SELECT query which queries one table, which joins to itself on 2 indexed columns (both non-clustered):

SELECT
    A.REFERENCE,
    A.MEMBERID
FROM
    TRANS A INNER JOIN
    TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID = B.MEMBERID

This one above returns in about 2 seconds.

But, when using the same query, but looking for matching references across different memberids e.g. changing = to <>, it takes about 20 seconds.

SELECT
    A.REFERENCE,
    A.MEMBERID,
    B.MEMBERID AS MEMBERID_B
FROM
    TRANS A INNER JOIN
    TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID <> B.MEMBERID

I realize that this 2nd query uses a negative comparison and thus, must fully review table B vs. the 1st query which can just find the single match and move on (I think). In the query plan, it uses the same index and both are seeks, but the = estimates a single row involved, and the <> estimates 20 million rows (entire table).

My question is: without being able to alter any indexes or table structure, etc. how might I go about optimizing the query with the negative comparison? (or re-writing it to achieve the same results?) I've searched google, and found lots of info saying not to use negative comparisons where possible, but not much on optimizing when you must.

Unfortunately I can't post the exact code or anything from the db environment because I am at work and we're not permitted to post any actual code etc. e.g. Clipboard won't even work across the remote connection and can't use a browser in the machine I am accessing the DB from. My example is a dummy example to mimic the similar situation. I realize the best way is to examine indexes, structure, and query plans, but I was hoping to learn some general advice for the situation.

It's hard to say much given your need to keep your code and data confidential, but sometimes you can get better performance just by trying equivalent rewrites. The following query should return the same results but it's very likely to have a different query plan:

SELECT
A.REFERENCE,
A.MEMBERID,
B.MEMBERID AS MEMBERID_B
FROM
TRANS A INNER JOIN
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID > B.MEMBERID

UNION ALL

SELECT
A.REFERENCE,
A.MEMBERID,
B.MEMBERID AS MEMBERID_B
FROM
TRANS A INNER JOIN
TRANS B ON A.REFERENCE = B.REFERENCE AND A.MEMBERID < B.MEMBERID

As you said in chat, this rewrite finishes in 6 seconds which is a decent improvement over the original 20 seconds you were seeing with <>.


Insert data into table where 4 fields are the same and the fifth changes I have a table with this structure:

username varchar(35)
startdate datetime
enddate datetime
saledate datetime
packageid int

for each username there are 5 packages, so one username has 5 records in the table, and the only field that changes is packageid. Now I have been asked to insert for all the users (around 4200) another package in the table, so the statement should be like this:

insert into subscription(username,name,startdate,enddate,saledate,pkgid)
values (@username,@name,@startdate,@enddate,@saledate,41)

  41 is the value of the package field

I have included this statement in a procedure, but my problem is in how to access the username field (it logically should access a distinct(username), loop this field until there are no more users left,and in each cycle execute the procedure). The id column of the table is of no help, because the packages at the beginning were not added in group (so I cannot set the id=id+5). Any idea?

No loops are needed in this (please!).

You should be doing an INSERT...SELECT.

Always run the SELECT part of the statement on it's own first, to make sure it is only selecting what you expect to be inserted.

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
  username,
  name,
  startdate,
  enddate,
  saledate,
  41
FROM subscription;

If there is a specific startdate and enddate you want to use for all users, then you'll need to set them as variables.

DECLARE @startdate DATETIME;
DECLARE @enddate DATETIME;

SET @startdate = '2015-05-21 10:00:00.000';
SET @enddate = '2015-06-21 00:00:00.000';

INSERT INTO subscription(username,name,startdate,enddate,saledate,pkgid)
SELECT DISTINCT
   username,
   name,
   @startdate,
   @enddate,
   NULL,
   41
FROM subscription;


SQL - I have just Differential backup but database exists I have Differential backup from my database.one table is deleted.my data is lost.i create that table again but i cannot restore Differential backup because sql wants a full backup first.if i get full back up an restore it with norecovery.when i want restore Differential backup sql get error.it says that:

  this Differential backup cannot be restored because the database has
  not been restored to the correct earlier state.

What should i do?

You are pretty much out of luck unless you have a full backup previous to the differential backup you want to restore. Your backup chain is incomplete if you do not and with that your data is simply gone.

It sounds as if you received the error for the need of a full backup, and then took a full backup and restored that, then your differential. Sorry to tell you that is not going to work either.

Your only hope is that the data can be recovered manually because you are not going to be able to restore that database with out a previous full backup.

I would highly suggest you adjust your backup process now so this can be prevented. You need to ensure your backup chain is complete in order to provide a proper recovery process (along with ensuring integrity).


mysqldump slow on Windows unless piped to file Running mysqldump without piping to file such as:

mysqldump -u root -pPASS db

is really slow on windows. If I do

mysqldump -u root -pPASS db > db.sql

it runs fast.

This might not seem like a big deal as I can always pipe to a file. BUT I am using mysqldump from a script and I just want the raw output so I don't have to load the file in memory or save a temp file.

Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Is there a way to fix this? This is for mysql 5.6.x

The full command I run is:

mysqldump --user=$db_username --password=$db_password --tz-utc=false --single-transaction --routines --triggers db

but I have tried a lot of combos and nothing makes it fast.

Based on the bounty message Looking for an answer drawing from credible and/or official sources., the only thing I could do is just mention that mysqldump is already buffering as little as possible. Why can I say that ???

By default, the option --opt is enabled. The MySQL Documentation says this about --opt:

  This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

  Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

Please note that --quick is already enabled because --opt is enabled.

What does --quick do ???

  This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Since --quick is already enabled, each row is being dumped directly to stdout without buffering. You may have to just bite the bullet and disable --quick by using --skip-quick. What that will do is buffer a whole table into RAM (which you didn't want to do in the first place) and then spill it out to stdout.

In your question, you said something interesting

  Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Since you said that, you have reached a critical moment

I can say that mysqldump is not really the problem. The problem is the OS and/or hardware.

Evidently, the computer you are using for Windows is not performant for screen I/O.

Since MacOS runs like Linux, I would tend to blame Windows OS. My guess is that even with a far better computer (faster CPU, more RAM), mysqldump may still not perform better if Windows cannot buffer screen I/O effectively.

Your fix for this would be to tune Windows. Godspeed, Spiderman !!!


Sybase ASE: How to know if a device has free space to extend a database on it I am new to sybase ASE.

I would like to know what are all the databases that have been using a device in sybase instance. sp_helpdevice or sp_helpdb are just giving not enough information.

Is there any such way to know the information in the below format.

device_name   db_id   allocated used free
.             .       .         .    .
.             .       .         .    .
.             .       .         .    .

sp_helpdevice does list all allocated fragments on a device when you specify the device name. This includes the size of each fragment, but indeed not the free/used metrics. Those would not be difficult to write yourself however.


TSQL retrieve backup date from database after restore Usecase: MSSQL Database is hosted in the cloud, I download the database thrue FTP and restore it on-premise automatically for reporting.

Now I want to retrieve the date/time when the backup was made, from the restored database, to show my users how old the data is that they are working with.

In my local MSDB, I only see when the database was restored, not when it was originally back-upped in the cloud. I know the date-time is in the .bak, but that is hard to query.

Any suggestions?

Answer from author's comment converted to community wiki

select  top 1
        s.backup_finish_date
from    msdb.dbo.backupfile f
left outer join
        msdb.dbo.backupset s
    on  s.backup_set_id = f.backup_set_id
where   f.logical_name  = '<name of mdf-file>'
order by
        f.backup_set_id desc

In case the original MSDB is not available, or backup history has been purged, the data is available within backup headers. Use RESTORE HEADERONLY to retrieve the header contents. In the header, there's about 60 pieces of metadata; refer to the Microsoft documents for detailed description.

For example,

restore headeronly from disk = N'<path-to-backup-file.bak>';


Indexes not used with OR I have query such as this:

SELECT t0.id
FROM platform_conversations t0
LEFT OUTER JOIN contacts t1 ON t1.id = t0.contact_id
WHERE t0.user_id = 5340
AND (
     t0.participant ILIKE '%baa%'  -- (1)
     OR t1.first_name ILIKE '%baa%' -- (2)
     )
LIMIT 50;

and

CREATE INDEX ix_conversations_participant
  ON platform_conversations USING GIN (participant gin_trgm_ops);

CREATE INDEX ix_trgm_contacts_search
  ON contacts USING GIN (first_name gin_trgm_ops);

And can't figure out why indexes are not used with OR condition. If I use only (1), or only (2), or use AND instead, they are both used.

Here's the plan:

For only (1)

Limit  (cost=12.43..203.68 rows=50 width=37) (actual time=0.037..0.037 rows=0 loops=1)
  ->  Bitmap Heap Scan on platform_conversations t0  (cost=12.43..222.80 rows=55 width=37) (actual time=0.030..0.030 rows=0 loops=1)
        Recheck Cond: ((participant)::text ~~* '%baa%'::text)
        Filter: (user_id = 5340)
        ->  Bitmap Index Scan on ix_conversations_participant  (cost=0.00..12.42 rows=55 width=0) (actual time=0.012..0.012 rows=0 loops=1)
              Index Cond: ((participant)::text ~~* '%baa%'::text)
Planning time: 0.397 ms
Execution time: 0.092 ms

For only (2)

Limit  (cost=16.71..446.06 rows=50 width=37) (actual time=0.034..0.034 rows=0 loops=1)
  ->  Nested Loop  (cost=16.71..471.82 rows=53 width=37) (actual time=0.028..0.028 rows=0 loops=1)
        ->  Bitmap Heap Scan on contacts t1  (cost=16.29..158.99 rows=37 width=37) (actual time=0.022..0.022 rows=0 loops=1)
              Recheck Cond: ((first_name)::text ~~* '%baa%'::text)
              ->  Bitmap Index Scan on ix_trgm_contacts_search  (cost=0.00..16.28 rows=37 width=0) (actual time=0.016..0.016 rows=0 loops=1)
                    Index Cond: ((first_name)::text ~~* '%baa%'::text)
        ->  Index Scan using ix_platform_conversations_contact_id on platform_conversations t0  (cost=0.42..8.45 rows=1 width=74) (never executed)
              Index Cond: ((contact_id)::text = (t1.id)::text)
              Filter: (user_id = 5340)
Planning time: 0.840 ms
Execution time: 0.121 ms

Slow one (with OR):

Limit  (cost=25771.43..47419.63 rows=50 width=37) (actual time=6652.292..6652.292 rows=0 loops=1)
  ->  Hash Left Join  (cost=25771.43..72531.55 rows=108 width=37) (actual time=6652.282..6652.282 rows=0 loops=1)
        Hash Cond: ((t0.contact_id)::text = (t1.id)::text)
        Filter: (((t0.participant)::text ~~* '%baa%'::text) OR ((t1.first_name)::text ~~* '%baa%'::text))
        Rows Removed by Filter: 553477
        ->  Seq Scan on platform_conversations t0  (cost=0.00..20627.29 rows=553512 width=87) (actual time=0.045..1741.899 rows=553477 loops=1)
              Filter: (user_id = 5340)
              Rows Removed by Filter: 386
        ->  Hash  (cost=17578.19..17578.19 rows=384819 width=46) (actual time=2372.508..2372.508 rows=384819 loops=1)
              Buckets: 65536  Batches: 16  Memory Usage: 2359kB
              ->  Seq Scan on contacts t1  (cost=0.00..17578.19 rows=384819 width=46) (actual time=0.014..1168.218 rows=384819 loops=1)
Planning time: 0.741 ms
Execution time: 6652.333 ms

Can't it just do bitmap index scans on t1, t2 and then bitmap ORs on them? Why is OR such a problem?

Postgres 9.6 (can't create tag, not enough rep)

Can't it just do bitmap index scans on t1, t2 and then bitmap ORs on
  them? Why is OR such a problem?

The question is: "Do you want filter data AFTER doing join ? OR Do you want filter data BEFORE doing join?"

Because of lacking data when using OR. That's why PG needs to do a sequence scan on those tables.

Your condition is t1.id = t2.contract_id, (1) or (2).

In your case, supposed that PG will use bitmap index, i.e. t1 uses (1) and t2 uses (2). Here, you can see if (1) is false (!), then check (2). The problem is if (2) is true, data of columns of (1) will be lacked.

(!) If PG really uses bitmap index, this case has never occurred (because (1) is always true) .

Here, my simple example (Postgres 9.6) about AFTER-BEFORE join:

create table t1 (a int, b text, c text);
insert into t1 select a, md5(a::text), md5((a+1)::text) from generate_series(1, 1e6)a ;
create table t2 as select * from t1;
CREATE INDEX idx1 ON t1 USING GIN (b gin_trgm_ops);
CREATE INDEX idx2 ON t2 USING GIN (c gin_trgm_ops);
explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and t1.b like '%abcd%';
explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and t2.c like '%abcd%';

explain analyze select * from t1 left outer join t2 on t1.a = t2.a where t1.a > 10 and ( t1.b like '%abcd%' or t2.c like '%abcd%') ;
----------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=47565.00..90289.93 rows=10199 width=140) (actual time=749.517..8782.051 rows=848 loops=1)
   Workers Planned: 3
   Workers Launched: 3
   ->  Hash Left Join  (cost=46565.00..88270.03 rows=10199 width=140) (actual time=781.632..8641.975 rows=212 loops=4)
         Hash Cond: (t1.a = t2.a)
         Filter: ((t1.b ~~ '%abcd%'::text) OR (t2.c ~~ '%abcd%'::text))
         Rows Removed by Filter: 249786
         ->  Parallel Seq Scan on t1  (cost=0.00..16378.26 rows=322548 width=70) (actual time=0.069..105.463 rows=249998 loops=4)
               Filter: (a > 10)
               Rows Removed by Filter: 2
         ->  Hash  (cost=22346.00..22346.00 rows=1000000 width=70) (actual time=754.846..754.846 rows=1000000 loops=4)
               Buckets: 65536  Batches: 32  Memory Usage: 3636kB
               ->  Seq Scan on t2  (cost=0.00..22346.00 rows=1000000 width=70) (actual time=0.039..270.865 rows=1000000 loops=4)
 Planning time: 0.262 ms
 Execution time: 8790.209 ms

explain analyze
select *
from ( select * from t1 where t1.a > 10 and t1.b like '%abcd%' ) a
left outer join  (select * from t2 where t2.a > 10 and t2.c like '%abcd%' ) b on a.a = b.a ;
------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=522.81..12999.45 rows=10100 width=140) (actual time=2.647..3.316 rows=424 loops=1)
   Hash Cond: (t1.a = t2.a)
   ->  Bitmap Heap Scan on t1  (cost=118.28..12557.04 rows=10100 width=70) (actual time=1.048..1.604 rows=424 loops=1)
         Recheck Cond: (b ~~ '%abcd%'::text)
         Rows Removed by Index Recheck: 47
         Filter: (a > 10)
         Heap Blocks: exact=465
         ->  Bitmap Index Scan on idx1  (cost=0.00..115.76 rows=10101 width=0) (actual time=0.974..0.974 rows=471 loops=1)
               Index Cond: (b ~~ '%abcd%'::text)
   ->  Hash  (cost=403.28..403.28 rows=100 width=70) (actual time=1.590..1.590 rows=424 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 51kB
         ->  Bitmap Heap Scan on t2  (cost=28.77..403.28 rows=100 width=70) (actual time=0.988..1.533 rows=424 loops=1)
               Recheck Cond: (c ~~ '%abcd%'::text)
               Rows Removed by Index Recheck: 47
               Filter: (a > 10)
               Heap Blocks: exact=465
               ->  Bitmap Index Scan on idx2  (cost=0.00..28.75 rows=100 width=0) (actual time=0.922..0.922 rows=471 loops=1)
                     Index Cond: (c ~~ '%abcd%'::text)
 Planning time: 0.262 ms
 Execution time: 3.397 ms
(20 rows)

UPDATE from Kadek

That's great, union is a good choice.
Based on your query, you can create a new first_name column on platform_conversations table, then create an index for two columns first_name and participant. Of course, you should balance between write and read flow.

CREATE INDEX idx1 ON t1 USING GIN (b gin_trgm_ops);
CREATE INDEX idx2 ON t1 USING GIN (c gin_trgm_ops);
explain analyze select * from t1 where t1.a > 10 and ( t1.b like '%abcd%' or t1.c like '%abcd%' );
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=149.61..12643.62 rows=10199 width=70) (actual time=2.315..3.423 rows=848 loops=1)
   Recheck Cond: ((b ~~ '%abcd%'::text) OR (c ~~ '%abcd%'::text))
   Rows Removed by Index Recheck: 94
   Filter: (a > 10)
   Heap Blocks: exact=470
   ->  BitmapOr  (cost=149.61..149.61 rows=10201 width=0) (actual time=2.244..2.244 rows=0 loops=1)
         ->  Bitmap Index Scan on idx1  (cost=0.00..115.76 rows=10101 width=0) (actual time=1.203..1.203 rows=471 loops=1)
               Index Cond: (b ~~ '%abcd%'::text)
         ->  Bitmap Index Scan on idx2  (cost=0.00..28.75 rows=100 width=0) (actual time=1.040..1.040 rows=471 loops=1)
               Index Cond: (c ~~ '%abcd%'::text)
 Planning time: 0.323 ms
 Execution time: 3.544 ms


How to use an RSA public key as the primary key to a table in postgres I have a data set that needs to be looked up by RSA public keys.  These keys are 392 text characters long.

What is the best way to make that a primary and/or unique key?

Should it be a BYTEA field?

Is it too long for a primary key?  Should I store a shorter hashed version of it to index on and filter by first before checking for an exact match?

I would say store them as bytea, but not for that reason. Simply because pg_crypto expects the keys as bytea. That establishes a convention. They likely have good reason for it: it seems intuitively faster.

Also there is no reason to store them as a PRIMARY KEY. You can sanely just store them in an indexed field.


want to use logical replication with a postgresql10 cluster (that is not using logical replication) We presently have an active/standby postgresql 10 cluster (based on PAF/clusterlabs). Anyways, the servers are kept in sync using:
wal_level = replica
This is fine and working.
However, we would like to replicate a single database from the cluster to a standalone instance (for analytics), for this we figured a logical replication of the single database would do nicely, however we then realized we would need change the replication type on the cluster (which would essentially break it)
wal_level = logical
Is the above assumption correct (cant mix "replica and logical")? Are there any other suggestion on how to achieve the same result (replicating a single database out of the cluster with many databases)
Thanks for any feedback/suggestions

Replicating a single database can only be done using logical replication.
You just need to change the wal_level from replica to logical.
The level logical includes all the information that replica includes, plus some more that's required for logical replication. So changing it, won't break your streaming (physical) replication.


Revoke alter permissions on service account I had run the following statements to grant alter permissions on specific tables for the service account

ALTER AUTHORIZATION ON [PartyMsci] TO  "INT\svc-w-corerefdata-de";
ALTER AUTHORIZATION ON [Product] TO  "INT\svc-w-corerefdata-de";
ALTER AUTHORIZATION ON [SimpleIndustryPICS8] TO  "INT\svc-w-corerefdata-de";

How do I remove these permissions ?

You should be able to reset the ownership of the object by executing the following:

ALTER AUTHORIZATION ON PartyMsci TO SCHEMA OWNER;
ALTER AUTHORIZATION ON Product TO SCHEMA OWNER;
ALTER AUTHORIZATION ON SimpleIndustryPICS8 TO SCHEMA OWNER;

You can determine who the current owner is for objects in the database using this query:

SELECT ObjectName = o.name
    , ObjectOwner = CASE WHEN dp.name IS NOT NULL THEN dp.name ELSE so.name END
FROM sys.objects o
    LEFT JOIN sys.database_principals dp ON o.principal_id = dp.principal_id
    LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals so ON s.principal_id = so.principal_id
ORDER BY o.name;

The Microsoft Docs on ALTER AUTHORIZATION makes this note:

  If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

As a result, you should analyze the required permissions on those objects before you change the owner via ALTER AUTHORIZATION, and re-apply appropriate permissions once you have changed the owner.


Calculate hours based on a course schedule in mysql I am trying to calculate user's attendance hours based on a course schedule.
The user enters a room and I'm tracking in & out time.
So I have the following tables

user_id     |   room_id      |  in                  |  out
-----------------------------------------------------------
1           | 1              |  2016-01-01 08:30:00 |  2016-01-01 12:30:00
1           | 1              |  2016-01-01 12:33:00 |  2016-01-01 14:00:00

course_id  |   room_id      |  start                |  end
-----------------------------------------------------------
1           | 1              |  2016-01-01 09:00:00 |  2016-01-01 11:00:00
2           | 1              |  2016-01-01 11:15:00 |  2016-01-01 12:00:00
3           | 1              |  2016-01-01 12:05:00 |  2016-01-01 13:00:00

Based on above schedule scenario user attended 3 hours and 37 mins.

Any idea on the query because I'm pretty confused and can't get it to work.
Maybe I have to rethink of how I'm storing the values?

Firstly, join the two tables on this condition:

attendance.room_id = schedule.room_id
AND schedule.`start` < attendance.`out`
AND attendance.`in` < schedule.`end`

The first predicate matches the rooms, obviously, and the other two determine if the ranges intersect. Any two ranges intersect if the beginning of each is less than the end of the other.

Secondly, take the time difference between GREATEST(attendance.`in`, schedule.`start`) and LEAST(attendance.`out`, schedule.`end`).

Finally, just aggregate the time differences per user.

So, the complete query might look like this:

SELECT
  a.user_id,
  SUM(TIMESTAMPDIFF(MINUTE,
                    GREATEST(a.`in`, s.`start`),
                    LEAST(a.`out`, s.`end`))) AS total_attendance
FROM
  attendance AS a
  INNER JOIN schedule AS s
    ON a.room_id = s.room_id
    AND s.`start` < a.`out`
    AND a.`in` < s.`end`
GROUP BY
  a.user_id
;

The result of this query is in minutes, as determined by the first argument of the TIMESTAMPDIFF function. Other than simply changing the argument to return the value in different units, you can also return it as time using the SEC_TO_TIME function like this:

SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, ...))) AS total_attendance

Specific formatting, like e.g. 3 hours 37 minutes, may be achieved using MySQL too, but normally it is better to use the presentation level for that.


Separate table for 'scheduled payments' and 'completed payments'? In a previous financial payments related database I'd worked on, it just had one large transactions table. We would insert scheduled transactions for the future, and when that time came, we'd execute them, and update that row to status completed.

I've kept wondering why they didn't have a separate 'completed transactions' table? Is that bad practice?

There is one entity - financial transaction, that can be in a various states. Completed, scheduled, postponed, rolled back etc. State is the property of the entity, only part of the whole, like the field is a part of the row.

When the state is just a field of the table it is possible to create DB structure and code that are invariant regardless of how many states are possible. In the opposite case each new state produce the new table and lot of code should be rewritten to reflect the changes.


MySQL Client can Connect but Replication Slave Can't We have an odd issue we've never come across. We have roughly 14 pairs of master/slaves all running MySQL 5.6.35 without issue. We are trying to add a slave to a slave (which we have two other instances of running in other data centers). We can connect from the command line of the new backup DB (slave) server as follows:
[root@BackupDB ~]# mysql -u slave -p -h xx.xx.xx.xx

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 47075
Server version: 5.6.35-log MySQL Community Server (GPL)

mysql xx.xx.xx.xx:3306 <(none)>

However when the slave attempts to connect we get the following status:
mysql localhost:mysql.sock <(none)> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: xx.xx.xx.xx
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay.000002
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 120
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@xx.xx.xx.xx:3306' - retry-time: 60  retries: 32
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 200922 16:06:29
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

We have verified that the slave user has SLAVE REPLICATION privileges on the master as follows:
grant replication slave on *.* TO 'slave'@'xx.xx.xx.xx' identified by 'xxxxx';
flush privileges

We have set the master information on the slave as follows:
change master to master_host='xx.xx.xx.xx', master_port=3306, master_user='slave', master_password='abc', master_log_file='bin.000001', master_log_pos=0;

For the purposes of this exercise we have made the password "abc" to avoid a fat-finger situation. We have had two people double-check that the IP address, ports and passwords were the same.
We are stumped and any help would be appreciated.

A 2003 error is normally some form of connectivity error, you may want to check firewall settings, routing and SELinux status.
For the latter, you can either go and fix the SELinux permissions properly but as a quick and dirty test you can do the following
sudo setenforce 0

Hope the above helps.


02270. 00000 - "no matching unique or primary key for this column-list" For some reason I keep coming up with the 02270 error. However I'm not sure what's wrong with it. All I know is that the problem lies with the room table and the conferencesession table.

CREATE TABLE BUILDING (
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));

CREATE TABLE SPEAKER (
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE NUMBER(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY (SPEAKERID));

CREATE TABLE ROOM (
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(ROOMNO,BUILDINGNO),
CONSTRAINT ROOM_FK1 FOREIGN KEY(BUILDINGNO)REFERENCES BUILDING(BUILDINGNO));

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));

This:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES
BUILDING(BUILDINGNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT CONFERENCESESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));

Should be changed to:

CREATE TABLE CONFERENCESESSION (
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT CONFERENCESESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT CONFERENCESESSION_FK1 FOREIGN KEY(BUILDINGNO, ROOMNO) REFERENCES
ROOM(BUILDINGNO, ROOMNO),
CONSTRAINT CONFERENCESESSION_FK2 FOREIGN KEY(SPEAKERID) REFERENCES
SPEAKER(SPEAKERID));

A foreign key requires a unique or primary key constraint on the referenced columns in the parent table. A multi-column constraint is not interchangable with single-column constraints.


Remove a character from a records enclosed between double quotes I have a table in which every row have data like:

0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014

What I want is to remove every comma (,) that is enclosed between double quotes " ". And then split the rest of the string with comma (,)

I don't want to do it checking every single character setting flags for start and end of double quotes.

Can i implement some sort of regex?

Is there a simple way?

What I have tried so far is just to split the string based on the comma (,) but it is also splitting values inside the quotes.

This is serving the purpose: how can I implement this in complete table (currently it is working if I have only one instance of double quotes block)?

Declare @Query nvarchar(max)

Set @Query = 'Item1,Item2,"Item,Demo,3",New'

Declare @start int, @len int
SELECT @start = PATINDEX('%"%"%', @Query) + 1

print @start

select @len = CHARINDEX('"', SUBSTRING(@Query, @start, LEN(@Query))) - 1

select
        SUBSTRING(@Query, 1, @start - 2) +
        REPLACE((SUBSTRING(@Query, @start, @len)), ',', '') +
        SUBSTRING(@Query, @start + @len + 1, LEN(@Query))

This is the function I am using to split

ALTER FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (id int, item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
Declare @ID as int
Set @ID=0
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 Set @ID=@ID+1
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @ID,@sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @ID,@sInputList -- Put the last item in
RETURN
END

The problem is that this is an application in MS Access, which narrows down my possibilities.  All I can do is passing the name of the file which is going to get imported by SQL Server.

Well, this is really awful, but okay, if you are going to refuse to even consider better alternatives... first, create a set-based split function that will track the order of the string (your looping function is really not optimal):

CREATE FUNCTION [dbo].[SplitStrings_Ordered]
(
    @List       VARCHAR(8000),
    @Delimiter  VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT [Index] = ROW_NUMBER() OVER (ORDER BY Number), Item
    FROM (SELECT Number, Item = SUBSTRING(@List, Number,
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY [object_id])
      FROM sys.all_columns) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);

(If you have a numbers table in this database, use that instead of sys.all_columns, and add WITH SCHEMABINDING to the function definition.)

Now, let's look at a few examples of strings with commas embedded inside double quotes, and removing those before splitting and re-concatenating:

DECLARE @x TABLE(n VARCHAR(8000));

INSERT @x VALUES
('0150566115,"HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014'),
('0150566115,HEALTH 401K,"IC,ON","ICON HEALTH 401K",,,1,"08/21/2014"'),
('"01505,66115,","HEALTH 401K","IC,ON","ICON HEALTH 401K",,,1,08/21/2014');

;WITH x AS
(
  SELECT x.n, s.[Index], s = REPLACE(s.Item, ',',
    CASE s.[Index]%2 WHEN 0 THEN '' ELSE ',' END)
  FROM @x AS x
  CROSS APPLY dbo.SplitStrings_Ordered(x.n, '"') AS s
)
SELECT x.n, fixed = (SELECT x2.s
  FROM x AS x2
  WHERE x2.n = x.n
  ORDER BY [Index]
  FOR XML PATH, TYPE).value(N'.[1]',N'varchar(max)')
FROM x
GROUP BY x.n;

Results in the fixed column for all three strings:

0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014
0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014
0150566115,HEALTH 401K,ICON,ICON HEALTH 401K,,,1,08/21/2014

Now, you can feed those results back into the split function, using the comma this time, depending on your ultimate goal. The question seemed to resolve only around being able to ignore the double quotes and any commas contained only inside double-quote pairs.

For more on splitting and concatenating strings:

http://sqlperformance.com/2012/07/t-sql-queries/split-strings
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-follow-up
http://sqlperformance.com/2012/08/t-sql-queries/splitting-strings-now-with-less-t-sql
http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation
http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation-2
https://www.sentryone.com/blog/aaronbertrand/grouped-concatenation-use-cases

For more on numbers tables and generating sets without loops:

Why should I consider using an auxiliary numbers table?
Why are numbers tables "invaluable"?
http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


MySQL - How much physical space will be reduced by changing the column type? Lets say I have the following table:

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

and lets say this table have lots of records: 100M

I have 2 possible values to relationship_status: 'following' or 'not_following'

since I want to reduce the size of DB (size on hard drive), how would it effect if I would change the relationship_status to Boolean instead of varchar(48) (where if can define 0 as not following and 1 as following)?

which column type would you use? tinyint?

I think TINYINT would be a good choice. I would suggest not indexing relationship_status at all. You should not index relationship_status by itself because the index cardinality is 2. The Query Optimizer would never use the index. You could index as follows:
ALTER TABLE my_table ADD INDEX (relationship_status,user_id);

That way, in the worst case, the Query Optimizer would perform an index scan on queries like:
SELECT user_id FROM my_table WHERE relationship_status = 1;
SELECT relationship_status,COUNT(1) statuscount
FROM my_table GROUP BY relationship_status;

The EXPLAIN plan should improve in that sorting and temp table usage would be minimized or eliminated.
Now concerning diskspace, let's compute the savings in space. First, What is the length of each string?
mysql> select length('following') followlength,length('not_following') notfollowlength;
+--------------+-----------------+
| followlength | notfollowlength |
+--------------+-----------------+
|            9 |              13 |
+--------------+-----------------+
1 row in set (0.01 sec)

Each VARCHAR has an additional byte for tracking string length. The values are really 10 and 14. That gets ignored since you are shrinking the field to 1 byte. If you collapse relationship_status to TINYINT, the savings would be as follows
SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        fcount*length('following')+ncount*length('not_following') BB
    FROM
        (
            SELECT COUNT(1) fcount FROM my_table
            WHERE relationship_status='following'
        ) F,
        (
            SELECT COUNT(1) ncount FROM my_table
            WHERE relationship_status='not_following'
        ) N
);

Since the the number of rows is about 100M, the Savings in Diskspace would have to be between 900M and 1.3G.
UPDATE 2012-08-23 13:00 EDT
To answer your comment

lets say the table has 2 more columns, 1 which is varchar(255) and another one which is DATETIME, lets say I can change both column types to int(11) unsigned, how much disk space that would save?

First of all, a DATETIME is 8 bytes and INT(11) is 4 bytes. Since 8 - 4 = 4, your savings would be 4 bytes per row. For the table, SELECT COUNT(1)*4 FROM my_table;
Now, the varchar(255) field. As I mentioned before, a varchar has an extra byte for length management. Sum up all the lengths of all the varchar fields and add the row count (the extra from field on every row) and subtract 4 bytes times the row count.
Putting the DATETIME and VARCHAR calculations together, you get this
SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        (VarCharBytes - (RowCount * 3)) BB
    FROM
    (
        SELECT SUM(LENGTH(varcharfield)) VarCharBytes
        FROM my_table
    ) A,
    (SELECT COUNT(1) RowCount FROM my_table) T
);


ERDs and modelling ownership of roles within Job table I'm currently in the process of modelling what I see as a relatively
  simple relationship through my ERD. I'm not even certain that strictly
  speaking this is Many-to-Many but nevertheless there are Many
  Employees who are associated with Job and many Jobs with which an
  Employee is associated.

  I think that this differs from what I'd assume is a "real"
  Many-to-Many relationship (and by that I mean one which requires a
  link table in the database for example: Students to Courses), as I'm
  really talking about two different roles. One employee being
  responsible for the Authorisation of Many jobs and One employee being
  responsible for the Administration of Many jobs (although, that could
  potentially be the same Employee)

EDIT: Trying to clear up any ambiguity, @Avarkx got me thinking and what I really need to be able to do is to control the overall permissions via some sort of "CanManage" table for allowing functionality to Invoices, Quotes, Drawings & Jobs; rather than allowing an Employee certain functionality on a Job by Job basis (which would be the case in the diagram below)

But, I also need to be able to record which Employee actually carried out the action within Job, which is what I was trying to get across in the paragraphs above (albeit in a roundabout way... Sorry!)

Here's my attempt at modelling the previous suggestion:

I think you'll want an EmployeeCanAuthorizeJob table and an EmployeeCanAdministrateJob table for a straight many-to-many representation ( right now, I think you'll find you'd be duplicating your job records with the current structure ).  This way, you could tie many EmployeeIDs to JobIDs and vice versa for either of the management tasks.

That said, I would likely only end up creating an EmployeeCanManageJob table, along with possibly a secondary look-up table ManageJobType.  The purpose of the second table would be to include the functions an employee can perform on a particular job.  Today you've got two, but who knows if CanDelete of whatever needs to be broken out later down the line?

EmployeeCanManageJob
------------------------------------------------
 PK | Employee_FK        FOREIGN KEY Employee.EmployeeID
 PK | Job_FK             FOREIGN KEY Job.JobID
(PK | ManageJobType_FK   FOREIGN KEY ManageJobType.ManageJobType_PK)

This way, if ManageJobType_PK 1 was "Administrate" and 2 was "Authorize", employee 1 could administrate job 1 with a ( 1, 1, 1 ) record and authorize the same job with a ( 1, 1, 2 ) record.

As per the edit:

There at least two popular methods of change tracking: the first being an audit table, the second being some form of slowly changing dimension.  To save you a little grief down the line, you are not dealing so much with a "slowly changing dimension" here as you are a "rapidly changing fact," if you will, so I expect you will find yourself looking at one of the numerous ways to implement the audit table scenario.  Which method you choose will largely be subject to you and your team's comfort level with the technologies involved ( or more specifically, each options advantages and disadvantages ).  I've personally always been a fan of the log trigger approach and think it would be more than suitable for your needs here, but if you have out-of-the-box capabilities available to you, like in Sql Server, perhaps you and your team would be more comfortable with that method.


Best Database specifications for my IOT solution i'm working on a IOT environment that basically is a sensor that can measure more then 300 times a second. We receive this measurements remotely using MQTT protocol. I want to set up a painel where i can watch in real time the measures my sensor is doing, i can do this with MQTT with no problem at all. But someone have ever done this using some database function? I know there are something called "Queue watch" for database that can do something similar.

The problem starts when i need to store those measurements, because i need to do analysis on them, draw graphics, and things like that.

As i'm not familiar with databases, there is a lot of data to store ( i imagine ) what would be the best database environment for this kind of thing?

----UPDATE1 -----

How many do you need to store? Last hour? Last day? Last decade? – Basil Bourque

As it's a service for clients, i would say that the lest month of measurements would be "hot data", i want to make them easily accessible and as fast as possible and anything older then that i can store as "cold data" just for eventual analysis, or any request for old measurements from clients.

You have two question, let's start with the easiest.

Math: rows / hours is:  300 Hz * 3600 s / hour = 1.08 Million rows

Which database can handle ~1 Million rows / hour?

Just about all of them.

You just need to use the correct technique in order to achieve those numbers.
Single row insert then commit can potential kill performance.

This blog describes the different Oracle method to insert 30 seconds worth of data (10000 rows).
The fastest method is done in 6ms.

https://geraldonit.com/2012/03/31/loading-data-fast-regular-insert-vs-bulk-insert/

MySQL can insert 3 seconds worth of data (1000 rows) in 10ms.

http://brian.pontarelli.com/2011/06/21/jdbc-batch-vs-multi-row-inserts/

Don't pick your RDBMS based off of those posts.  The point of those blogs is to show you that a performance difference exists based on HOW you insert the data.

What you really need to look for in an RDBMS is to see if it supports partitioning (and still fits in your budget ).

Partitioning allows you to break up the data into smaller chunks.  You can then toss those smaller chunks away ( truncate ) in milliseconds when you no longer need them.

Can I use a Database Queue instead of MQTT

You could try.

The only one I'm familiar with uses actual tables under-the-hood.  That means you'll need to commit after every row is added in order for you clients to see the data.
As mentioned before, this could be bad for performance.

Personally, I'd stick with the MQTT method for the Live Stream of data.  You got it to work.  No sense in trying other methods.

You'll need to write a subscriber to the MQTT so that it can buffer the inserts using one of the methods mentioned in the blogs.


Is it possible to enlist a TSX to an MSX in a different domain? In Domain1, I have an MSX with 75+ TSXes registered. Now I've been given some SQL Server instances in another domain. Is it possible to enlist Domain2 instances to an MSX in Domain1 ?

The answer is yes, if

The two domains have sufficient trust relationships configured, or
You can figure out how to set it up using pass-through authentication (I believe this requires running SQL Agent under matching accounts).

Here is the Master Server setup screen:

In other words, there is no bypassing Windows authentication.

IMO, if there are no trusts between the domains, and there shouldn't be for business or security reasons, I think it would be a better idea to set up a new master server in the other domain, instead of circumventing security just for this.


MongoDB - overloading PRIMARY with multiple replica set MongoDB replica sets read data from Primary oplogs. So, Does it mean that having too many replica sets will put load on Primary node as all replica nodes will have to read data from Primary oplog?  We have 7 nodes cluster though the load is not heavy and hence checking if we need to downsize it to 5 or so ..

Thanks

MongoDB replica sets read data from Primary oplogs.

Secondaries in replica sets sync from another member of the replica set with a newer oplog, but that does not necessarily have to be the current primary.

MongoDB supports chained replication, where a secondary member can replicate from another secondary member instead of the primary. This feature is enabled by default, and is particularly useful to minimise network traffic for deployments spanning multiple data centres. Unless chained replication is specifically disallowed, secondaries will periodically evaluate the member they are syncing from and may choose a lower latency secondary (with a more current oplog) to sync from.

You can check which member a secondary is syncing from via rs.status(). There should be a syncSourceHost field (MongoDB 4.0+) or a syncingTo field (deprecated name for the same field in older versions of MongoDB).

  We have 7 nodes cluster though the load is not heavy and hence checking if we need to downsize it to 5 or so ..

If you aren't seeing any performance issues with your current deployment and workload, there should be no need to proactively downgrade unless you feel the extra replica set members are no longer needed for your use case.


How to keep an unique counter per row with PostgreSQL? I need to keep an unique (per-row) revision number in a document_revisions table, where the revision number is scoped to a document, so it's not unique to the whole table, only to the related document.

I initially came up with something like:

current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);

But there is a race condition!

I'm trying to solve it with pg_advisory_lock, but the documentation is a bit scarce and I don't fully understand it, and I don't want to lock something by mistake.

Is the following acceptable, or am I doing it wrong, or is there a better solution?

SELECT pg_advisory_lock(123);
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(123);

Shouldn't I lock the document row (key1) for a given operation (key2) instead? So that would be the proper solution:

SELECT pg_advisory_lock(id, 1) FROM documents WHERE id = 123;
current_rev = SELECT MAX(rev) FROM document_revisions WHERE document_id = 123;
INSERT INTO document_revisions(rev) VALUES(current_rev + 1);
SELECT pg_advisory_unlock(id, 1) FROM documents WHERE id = 123;

Maybe I'm not used to PostgreSQL and a SERIAL can be scoped, or maybe a sequence and nextval() would do the job better?

SEQUENCE is guaranteed to be unique, and your use-case looks applicable if your number of documents isn't too high (else you have a lot of sequences to manage). Use the RETURNING clause to get the value that was generated by the sequence.
For example, using 'A36' as a document_id:

Per document, you could create a sequence to track the increment.
Managing the sequences will need to be handled with some care. You could perhaps keep a separate table containing the document names and the sequence associated with that document_id to reference when inserting/updating the document_revisions table.

 CREATE SEQUENCE d_r_document_a36_seq;

 INSERT INTO document_revisions (document_id, rev)
 VALUES ('A36',nextval('d_r_document_a36_seq')) RETURNING rev;


What if we skip the slave by counter 1 when it failed at event 1 of a transaction which has events 2 and 3 We have a master-slave replication which has all Innodb data.

I ran 3 update statements under one transaction on master.

 =============
 start transaction
 update T1
 update T2
 update T3
 end transaction
 =============

Through replication,these went on to slave.

With some error, replication stopped while executing first update(update T1) on salve.

Now if we issue set global sql-slave-skip-counter=1 on slave and start slave, will it skip update T2 and update T3 also ?

NOTE: All tables T1, T2 and T3 are Innodb tables

Thanks in advance.

Correct, skipping skips the transaction - not the statement - and that is why it is better to investigate the failure than blindly skipping.

You should use pt-table-checksum and pt-table-sync to resolve drift after a skip since you will likely have diverged.


SQL Server - Nonclustered Index with Include on User Defined Table Type? I was working with in-memory User Defined Table Types and got stuck on creating indexes on them.

Here's what I am trying to do:

CREATE TYPE dbo.CustomersTbl
AS TABLE
(
        customerID int NOT NULL,
        customerName nvarchar(100),
        customerAddress nvarchar(100),
        customerCity nvarchar(100),
        customerEmail nvarchar(100)

        INDEX IDX1 NONCLUSTERED (customerID) INCLUDE(customerName,customerAddress,customerCity)
)

    WITH
    (MEMORY_OPTIMIZED = ON);

This works fine if I do not include the INCLUDE part.

If this were a standard table I would create the index with the INCLUDE.

So my question, is there a way to create that index on the User Defined Table Type with the INCLUDE, or would there be a reason why I would not even want to? I am using these table types to create in-memory table variables.

I am currently using SQL Server 2017.

INCLUDE is not supported for memory-optimized tables or table variables. For these objects, the purpose of INCLUDE is moot, as all columns are available once you arrive at the row location in memory.

Memory-optimized indexes are inherently covering, see Guidelines for Memory-Optimized indexes

Disk-based indexes use row locators that are either a RID or a Clustered Index Key, and following those costs 1-4 Logical IOs, followed by locating the target row in the slot table on the page and going there.  The row locator for an In-Memory index is a pointer.  Following a pointer costs a single CPU instruction, and a main memory read, somewhere in the 100ns-1000ns range.


SQL Server Management Studio login prompt will never forget users other than 'sa' When I login to a database, using some other user than 'sa', that username will stick the next time I login.
If I input the username sa and password then log in, the next time I open the login prompt for that server, it will not remember that I logged in as sa, but show the user not being sa.
Why?

Management Studio remembers each server that you connect to and the logins used to connect to them.

The logins are remembered in from the last new login used.  For example if you connect to a server for the first time as 'sa' and then connect as 'bob', 'bob' will appear at the top of the login drop down.  Then if you connect as 'tony' then 'tony' will appear at the top of the drop down.

Reconnecting to the different users will not change their order in the list and 'tony' will remain as the default login until a new user is used.

This list can removed by removing the Server from the Server Name drop down.  This is done by expanding the server name list, hovering over the server name and pressing delete as detailed here: Deleting Old server names from "Connect to Server" dialog in SSMS


How to find what tablespace a table/index is in on PostgreSQL? How do I find out what tablespace a table (or index) is in with PostgreSQL (9.3)?

For example, you have a table example_table (optionally in more than one schema):

SELECT tablespace
FROM pg_tables
WHERE tablename = 'example_table' [AND schemaname = 'your_schema'];

The same thing for the index example_index:

SELECT tablespace
FROM pg_indexes
WHERE indexname = 'example_index' [AND schemaname = 'your_schema'];


Nested Loop Output Estimate Low when expanding dataset I have a stored procedure that is taking some header and details information of a tactic, that goes on to expand the data from a date range (Start/End) to the day level. This is then used further on in the procedure to calculate some day-level weightings. As I've been performance tuning, I've noticed one specific issue that I can't seem to solve.

If I have a "Tactic" that covers a year's worth of dates and the tactic item has 10,000 combinations that all need to expand out to the day level, I seem to get a low row estimate on the output of the nested loop. Investigating my plan, I believe it seems to be estimating the correct number of iterations at the nested loop, but at the output and subsequent inserts seem to be low. Is there something that I'm missing to gain better cardinality estimates?

Here's an extract of the behaviour:

DROP TABLE IF EXISTS dbo.Calendar
DROP TABLE IF EXISTS dbo.Tactic
DROP TABLE IF EXISTS dbo.TacticItems

CREATE TABLE dbo.Calendar (
    ddate   DATE NOT NULL
  , Day_Idx INT
  , PRIMARY KEY (Day_Idx)
)

CREATE TABLE dbo.Tactic (
  TacticId      INT NOT NULL PRIMARY KEY
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
)

CREATE TABLE dbo.TacticItems (
  TacticId      INT NOT NULL
, ItemKey_A     INT NOT NULL
, ItemKey_B     INT NOT NULL
, From_Day_Idx  INT NOT NULL
, To_Day_idx    INT NOT NULL
, PRIMARY KEY (TacticId, ItemKey_A, ItemKey_B)

)

/*
  Prime Dates For the Year
*/

INSERT INTO dbo.Calendar (ddate, Day_Idx)
SELECT  ddate = DATEADD(DAY, A.Increment, A.Base_Date)
      , Day_Idx = CONVERT(INT,CONVERT(CHAR(8),DATEADD(DAY, A.Increment, A.Base_Date),(112)))
FROM    (
          SELECT TOP 366
                  Base_Date = CONVERT(DATE, '2019-12-31')
                , Increment = ROW_NUMBER() OVER (ORDER BY Object_Id)
          FROM    sys.objects
        ) A

INSERT INTO dbo.Tactic
        (TacticId , From_Day_Idx, To_Day_idx)
VALUES  (29       , 20200101    , 20200630)

INSERT INTO dbo.TacticItems
            (
                TacticId
              , ItemKey_A
              , ItemKey_B
              , From_Day_Idx
              , To_Day_idx
            )
SELECT      1
          , A.ItemKey_A
          , B.ItemKey_B
          , 20200101
          , 20201231
FROM    (SELECT TOP 63 ItemKey_A = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) A
CROSS JOIN (SELECT TOP 162 ItemKey_B = ROW_NUMBER() OVER (ORDER BY Object_Id) FROM sys.objects) B

DROP TABLE IF EXISTS #Results
SELECT      *
INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

Here is the plan that i'm getting:

And the nested loop input that seems to be correct:

That's the optimal plan for that query.  So you should consider storing the result.  One easy way is to use an Indexed View.

create view TacticItemsByDay
with schemabinding
as
SELECT  [TacticId], [ItemKey_A], [ItemKey_B], [From_Day_Idx], [To_Day_idx], [ddate], [Day_Idx]
--INTO        #Results
FROM        dbo.TacticItems    TI
INNER JOIN  dbo.Calendar       CAL
ON          CAL.Day_Idx BETWEEN TI.From_Day_Idx AND TI.To_Day_idx

go

create unique clustered index ix_Results on TacticItemsByDay(TacticId, ItemKey_A, ItemKey_B, Day_Idx)


3rd Normal Form violation? I've got a photo review type of application, where the customer wants to have an option for the user to flag issues with the photos. So, for instance, a photo could be tagged as "fuzzy," "grainy," or "other." If "Other" is selected, a brief text description is required.

I am thinking I should create one table with all the issue types. Another table, will have foreign key relationships to the photo id, and the issue type id. I am thinking this table should also have a text type column for the "other" text if "other" is selected, otherwise that column will be null.

My dilemma, is, will this be a violation of normalization rules? If the "Other" text column is not null, then the assumption can be made that the issue type id will always be the one for "Other." So, that would be a transitive dependency, right? Any ideas on how to implement this without violating 3rd normal form?

If the column OtherText is null, you do not know the value of the column IssueType.  It could be fuzzy or grainy.  So IssueType is not dependent on OtherText.

Your design is not a 3NF violation.