texts: PL/pgSQL issues when function used twice (caching problem ?)

texts:
I am facing an absolutely weird problem that feels much like a Postgres bug than an algorithm problem.

I have this function:

CREATE FUNCTION sp_connect(mail character varying, passwd character varying, role character varying)
  RETURNS json LANGUAGE plpgsql STABLE AS
$$
DECLARE
    user_info record;
BEGIN
  IF role = 'Role1' THEN
    SELECT u.id, r.name INTO user_info
    FROM users u
    INNER JOIN users_roles ur ON ur.user_id = u.id
    INNER JOIN roles r ON ur.role_id = r.id
    WHERE u.email = mail
      AND u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
      AND r.name = 'Role1';
  ELSIF role = 'Role2' THEN
    SELECT h.id, 'Role1' AS name INTO user_info
    FROM history h
    WHERE h.email = mail
      AND h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');
  ELSE
    RAISE 'USER_NOT_FOUND';
  END IF;

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;
END;
$$;

The problem I'm facing is when I use this function to log in with a Role1-user, then when I use it with a Role2-user, I get this error message:

type of parameter 7 (character varying) does not match that when preparing the plan (unknown)

Which is... well, I just don't understand where does it come from. If you wipe the database and change the login order (i.e. Role2 then Role1), this time, Role1 gets the error.

Strange issue, strange solutions... If I just use ALTER FUNCTION sp_connect but without modify anything inside the function, then magically, the two roles can login without any problem. I also tried this solution:

  IF NOT FOUND THEN
      RAISE 'USER_NOT_FOUND';
  ELSE
      IF role = 'Seeker'
      THEN
          RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
      ELSE
          RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
  END IF;

And by adding an IF and ELSE that is absolutely useless and use the same RETURN-clause, this does not trigger any error.

I know DBA StackExchange is not for developers but this kind of problem seems to be more like a caching problem or whatever. Can somebody can tell me if I am doing something wrong with PostgreSQL functions? Or where I may get help with this weird problem?


texts: Would comparison of strings to a postgres 'text' type column for equality be a performance bottleneck?

texts:
I have a Postgresql database with a column type of text. Coming from a SQL Server background, is this equivalent to the (n)varchar(max) type?

My specific example/reason for asking this question, is that I have a table with a column of type text in which I would like to store unique values. The table is updated according to regular CSV imports, meaning that for every row in the CSV the the text column is checked for an existing entry (a column-value in the CSV), and if none is found then that value is inserted into the table.

My understanding is that this could mean checking thousands (or maybe hundreds of thousands) of text values against other text values. I imagine this to be incredibly inneficient. Is this the case?


texts: Alter default privileges in postgres for other roles

texts:
Let's say I have a role called user1, user2 and postgres.

Is it possible to alter the default privileges while logged in as postgres for objects created by user1 so that user2 can see them?

This is what I currently have, but this only works if I'm logged in as user1:

alter default privileges grant usage on schemas to user2;
alter default privileges grant select on tables to user2;
alter default privileges grant usage, select on sequences to user2;

Is it possible to get this to work while logged in as postgres?


texts: Change Tracking Bad Plans in SQL Server 2016

texts:
Has anyone else experience bad plans when calling CHANGETABLE in SQL Server 2016?

I have an application that uses change tracking to update a cache. Application servers get the changes for multiple tables every second. The CHANGETABLE is called so often that usually returns just a few rows. This works great in 2008 R2 and 2012. When I tested 2016 I found that the CPU spiked and found that the plan is scanning change tracking internal tables where it used to do a seek.

If you want to see this behavior here are the steps to replicate it:

Create a database and setup change tracking.
Create a Table:

CREATE TABLE [dbo].[ChangeTable_Test](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
CONSTRAINT [PK_Test_ChangeTable_Id] PRIMARY KEY CLUSTERED
(
     [Id] ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ChangeTable_Test] ENABLE CHANGE_TRACKING
GO
INSERT INTO ChangeTable_Test (Name) VALUES ('Test')
GO 1000

Include the actual execution plan and run the following.

SET STATISTICS IO ON;
SET NOCOUNT OFF;
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 110
GO
DECLARE @CurrentVersion INT;
SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C
GO
ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 130
GO
DECLARE @CurrentVersion INT;
SELECT  @CurrentVersion=CHANGE_TRACKING_CURRENT_VERSION ()-1;
SELECT * FROM CHANGETABLE(CHANGES ChangeTable_Test,@CurrentVersion) AS C

The above demo works for me on SQL Server 2016 SP1-CU4. I uploaded two plans to Paste the Plan. The first one is under COMPAT 110 and the second is under  130.

Microsoft support acknowledged the problem but do not plan to fix it. They suggested workarounds using a USE PLAN hint or Plan Guides. But it would make a maintenance nightmare for this product.

The funny thing is that 2014 and 2017 do not pick the bad plans and they use the new Cardinality Estimator.

Do you have any suggestions beside of the ones I mentioned?


texts: Restoring a Database in an Availability Group

texts:
I located an extremely helpful process outlined in the thread below, but needed some clarification. This may be a silly question, but I'm a bit new to Availability Groups. In the steps outlined in the thread's answer, is it true that after restoring, you only have to add the newly restored database into the secondary? Will it appear back into the primary automatically? Thank you for any assistance!

Restoring a SQL Server 2012 database in high availability


texts:
Using pgAdmin4.
I have live client data in columns in my db- the identifying information is first name and last name. I want to replace that with randomly generated names for demo purposes.
How can I copy and replace, en masse, the entire column with my fake names?
In my db, the first name is firstName and the last name is lastName.
Thanks.


texts: Postgres: flatten Key-Value (K-V) store with jsonb_agg

texts:
I have two tables: bar and foo (one to many respectively).

The foo table has an internalId and an external reference. The issue here is that the external reference can be duplicated.

I want my query result to have two columns: bar_id and mappings (where bar_id is not duplicated) where the key is the duplicate id and the values list are all the internal ids it is related to.
So far I have been able to aggregate the duplicate external ids to the internal id but not grouped those one more level
i.e
with data:

('100001', 1, '1a'),
('100001', 2, '1a'),
('100001', 3, '1b')

I can get the result:

100001  {"1a": [1, 2]},
100001  {"1b": [3]}

But I want:

100001  {"1a": [1, 2], "1b": [3]}

Fiddle here: https://www.db-fiddle.com/f/pKbFeRedD59s4f3VTnuyih/0


texts: SQL Server on Linux: Allocation unit on ext4

texts:
I'm testing SQL Server on Linux and now on the following point:
Allocation Unit Size of the partition: I learned at SQL Server on Windows the Allocation Unit Size (sector size) must be 64K because of the pages.

Is this also required for the SQL Server on Linux and if yes is this supported on ext4? At the system requirements on the docs of Microsoft is only the information of which file system but not more details.

Thanks


texts: I have a table in SQL Server 2008 R2 with close to a billion rows with Composit PK Column. I want to change the Datatype of Columns from Date to Datetime. Two times ALTER TABLE zzz ALTER COLUMN yyy works, but it's very slow. How can I speed the process up? I was thinking to copy the data to another table, drop, create, copy back and switching to simple recovery mode or somehow doing it with a cursor a 1000 rows a time but I'm not sure if those will actually lead to any improvement.


texts: Tuning Clustered Indexes in case of Soft / Logical delete

texts:
I have a table (say dbo.AppUsers) with a clustered index on the always increasing, application generated column AppUserId.
I have also a Bit column IsDisabled, that is used to logically delete/disable users.
Now, the majority of the queries on the table will include a Where IsDisabled = 0 clause, which is fine.
I'm wondering if I should include the IsDisabled column in the clustered index or if there is a best practice in this scenario.
My thoughts on this:
I should have IsDisabled in the clustered index, because list queries should not read the logically deleted rows from disk.
or...
I should not have IsDisabled in the clustered index, because it could mess up with table fragmentation.
Is there some sort of trade off?
Thanks!


texts: Secure view on row level with custom security tables

texts:
Let's say I have a following table that lists all member of my application.

CREATE TABLE dbo.Member
(
    MemberId BIGINT,
    LoginName NVARCHAR(64),
    SecurityRole NVARCHAR(64)
)

I assume that LoginName would be maintained in such a way that it will map to result of SYSTEM_USER function. Also, for the sake of example, I assume only 'Product' and 'Territory' values are possible for SecurityRole attribute and LoginName would be unique. I have also following tables:

CREATE TABLE dbo.MemberToProduct
(
    MemberId BIGINT,
    ProductId BIGINT
)

CREATE TABLE dbo.MemberToTerritory
(
    MemberId BIGINT,
    TerritoryId BIGINT
)

Those tables would handle relation of a given member to a product or territory given that they are assigned to 'Product' or 'Territory' security role.

Let's set up table that binds those tables together:

CREATE TABLE dbo.Contact
(
    ContactId BIGINT,
    ContactName NVARCHAR(64),
    TerritoryId BIGINT,
    ProductId BIGINT
)

and a view, which is the core part of my question:

CREATE VIEW dbo.ContactView
AS
WITH CurrentMember AS
(
    SELECT TOP (1)
        MemberId,
        SecurityRole
    FROM
        dbo.Member
    WHERE
        LoginName = SYSTEM_USeR
)
SELECT
    ContactId
FROM
    dbo.Contact
CROSS JOIN CurrentMember
INNER JOIN dbo.MemberToProduct
   ON MemberToProduct.ProductId = Contact.ProductId
  AND MemberToProduct.MemberId = CurrentMember.MemberId
WHERE CurrentMember.SecurityRole = 'Product'

UNION ALL

SELECT
    ContactId
FROM
    dbo.Contact
CROSS JOIN CurrentMember
INNER JOIN dbo.MemberToTerritory
   ON MemberToTerritory.TerritoryId = Contact.TerritoryId
  AND MemberToTerritory.MemberId = CurrentMember.MemberId
WHERE CurrentMember.SecurityRole = 'Territory'

This view will be used by users, who should have access to Contacts assigned either to Product or Territory they are allowed to see. This is to be decided by Member assignment to SecurityRole.

My question is: current design of a view is inefficient in my opinion as it needs to process data set that is not necessary (f.e. 'Territory' for 'Product' Member). For the sake of example I have simplified those tables but in reality I have a Contact table that have milions of rows and much more complicated security relations and doing an f.e. an union for each SecurityRole is very slow.

I have tried an approach based on creating a view that would call a stored procedure via OPENQUERY. This stored procedure would then have an IF statement and check for each security role and process Contacts separately for each security role. However this is not feasible to do, because if a view is then joined to another result set, the view returns all data from itself when in most situations it is not necessary.

I would like to know what are the best practices for this kind of problem and what are the options to have a view-like object that could return the same metadata but different data and have that advantage of stored procedure so it could decide what result set it is going to present.


texts:
I started working on an existing SQL Server Database System in which most of the fields are stored as text. Except some IDs all fields are varchar (phone numbers, zip codes, dates, addresses, monetary values etc.).

This is not how I learned to build a database. When asked collegues said that it is easier this way. Is it bad practice to keep all fields in varchar? How could I argue that it should be changed?


texts:
My problem is that I have following tables: packages, orders, order_items

order_items is halding id of package_id, order_id, and credits_number (which I need to retrieve)

order table can be with status = 1 (important for column credits_deducted_total) or 0 (important for credits_reserved_total)

credits_deducted_total and credits_reserved_total are giving me a problems because they should represent the number of deducted or reserved credits per project (which with following query is not the case).

Also 0 should be return as value for each project where I don't have orders with status = 0

Query which I was able to write:

select `packages`.`id` as `id`, `external_id`, `project_name`, `credits_available`, `credits_aq`,
`price`, `currency_code`, `packages`.`status`, `packages`.`updated_at`,
SUM(order_items.credits_number) as credits_deducted_total,
SUM(order_items_reserved.credits_number) as credits_reserved_total from `packages`
left join `order_items` on `order_items`.`package_id` = `packages`.`id`
left join `order_items` as `order_items_reserved` on `order_items_reserved`.`package_id` = `packages`.`id`
left join `orders` on `orders`.`id` = `order_items`.`order_id`
left join `orders` as `orders_reserved` on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
where (`orders`.`status` = 1 or `orders_reserved`.`status` = 0)
group by `packages`.`id`;


texts: Why do those Update statements fail using a datetime format?

texts:
Given:

Query #1:

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000';
rollback

Query #2: (changing month/day)

begin tran
UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00.000';
rollback

Why does the QUERY #1 fail to run on a new installed SQL Server while query #2 runs succesfully?

  Msg 242, Level 16, State 3, Line 6 Bei der Konvertierung eines
  varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb
  des gültigen Bereichs.

In comparison why does Query #1 run on the old server while query #2 fails on the new server?

  Msg 242, Level 16, State 3, Line 7 The conversion of a varchar data
  type to a datetime data type resulted in an out-of-range value.

The servers have different versions (2008 / 2016). And as you can see from error messages there are two different languages installed. Please don't tell me just because the language of the Sql-Server installation is different it is not able to handle different date formats?

Is this configurable?
Will I get away with uniform formats like 20150129 08:00:00.000 ?


texts:
Installing SQL Server 2017 Standard on Windows 2016 and hitting errors when entering the new service accounts for SQL agent, service, SSIS.

'The RPC Service is unavailable, Click retry the failed action'

At the bottom of the installer page it complains that the user accounts are invalid.  I know the AD accounts work and can log into the windows server as the service accounts.

My network admin forced me to enable WINS on this server and it now works, does anyone know why or what could be being blocked as the server is behind a firewall but the local firewalls are turned off.


texts: How to set addtime(current_timestamp, '15:00:00') as MySQL timestamp column default value?

texts:
I want to set addtime(current_timestamp, '15:00:00') as the default value of one MySQL table column. I tried to do this with the following command but failed:

mysql> alter table mytable change c1 c2 timestamp null default addtime(current_timestamp, '15:00:00');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'addtime(current_timestamp, '15:00:00')' at line 1

How to do this? Thanks.


texts:
I have a trigger function that just copies inserted values to another table.
Then, each night I need to drop and recreate this trigger for some export operation.

--trigger function
CREATE OR REPLACE FUNCTION mev_copy() RETURNS trigger AS $mev_copy$
        BEGIN   INSERT INTO measurement_events_copy SELECT NEW.*;
            RETURN NEW;
        END;

The problem is that sometimes the trigger cannot start: CREATE TRRIGGER command never returns, and the DB seems locked-up and puts all queries in "PARSE waiting" status.  Yesterday it was like that for 6 hours until I killed the create trigger.

The problem manifests consistently when table measurement_events is being autovacuumed. At the same time there is a new pending AccessExclusiveLock request for this table.

This is the create trigger, nothing special:

--CREATE TRIGGER that hangs sometimes
CREATE TRIGGER mev_copy AFTER INSERT ON measurement_events FOR EACH ROW EXECUTE PROCEDURE mev_copy();

Postgres version is 8.4.
Any insights?


texts: Upsert - Should I verify the row exists first, attempt an update, or try to insert first?

texts:
In a software that I'm working, I have a table with a few columns and a id. In some events in my software, I need to update some column of a record with a certain unique field (other column), but sometimes the value that i'm supposed to update is the same in the table, it have been updated by a previous event, so i just need get the key in my software.
When the key doesn't exist yet, i need to insert.

So, what is better in my software?

1 - First try update, searching by the unique field (with returning into clause to get the primary key), if the key exists, it will works even if the update wasn't really needed, i.e, if the value updated is the same that the older. But, if the key doesn't exist yet, i would get a error, so I do the INSERT (and returning into) with the correct values. I never perform a SELECT statement in this option.

OR

2 - First search the record  with a select statement (where certain_unique_column = certain_val_in_event) , i just try a update if the key exists and if the new value is different from the old value. If the key doesn't exist, I just do INSERT.

An other option would be try the 'INSERT' first, but i guess this is worse alternative, because for something like 4 in 5 queries, the key was already insert before.  And I need increment a sequence when i try to insert (passing mysequence.nextVal), so i would do unnecessary increments in 4 in each 5 queries.


texts: Failed to update SQL Server 2008 R2 RTM to SP3

texts:
I'm trying to update SQL Server 2008 R2 RTM to SP3 by running SQLServer2008R2SP3-KB2979597-x64-ENU.exe, on Windowns Server 2008 R2 SP1.

It failed with the following error in the log,

  2015-11-26 10:24:01 SQLEngine: : Version-copying file from 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\sqlscriptdowngrade.dll' to 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll'.
  2015-11-26 10:24:01 SQLEngine: : Failed to version-copy file 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\sqlscriptdowngrade.dll' to 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll'. Exception data is: System.UnauthorizedAccessException: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
  at System.IO.File.InternalCopy(String sourceFileName, String destFileName, Boolean overwrite)
  at Microsoft.SqlServer.Configuration.SqlEngine.Globals.VersionCopy(ServiceContainer context, String source, String target).
  2015-11-26 10:24:01 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
  2015-11-26 10:24:01 Slp: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  2015-11-26 10:24:01 Slp: The configuration failure category of current exception is ConfigurationFailure
  2015-11-26 10:24:01 Slp: Configuration action failed for feature SQL_Engine_Core_Inst during timing ConfigRC and scenario ConfigRC.
  2015-11-26 10:24:01 Slp: System.UnauthorizedAccessException: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied.
  2015-11-26 10:24:01 Slp:
  at Microsoft.SqlServer.Configuration.SqlEngine.Globals.VersionCopy(ServiceContainer context, String source, String target)
  2015-11-26 10:24:01 Slp:
  at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch_ConfigRC(EffectiveProperties properties)
  at Microsoft.SqlServer.Configuration.SqlEngine.SqlEngineSetupPrivate.Patch(ConfigActionTiming timing, Dictionary`2 actionData, PublicConfigurationBase spcb)
  at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.ExecuteAction(String actionId)
  at Microsoft.SqlServer.Configuration.SqlConfigBase.SlpConfigAction.Execute(String actionId, TextWriter errorStream)
  2015-11-26 10:24:01 Slp: Exception: System.UnauthorizedAccessException.
  2015-11-26 10:24:01 Slp: Source: Microsoft.SqlServer.Configuration.SqlServer_ConfigExtension.
  2015-11-26 10:24:01 Slp: Message: Access to the path 'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlscriptdowngrade.dll' is denied..

I get the exact same error every time I run setup and even after rebooting.

I checked the path d:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\ it's owned by SYSTEM, and regardless of whether I ran the setup as admin, in TaskManager it's still showing that setup100.exe is running under by account. I tried and can't rename the file, unless if I take over the ownership of the folder, though I worried that doing so will break SQL Server.

Any idea what went wrong?

EDIT: I've compared the same folder on another DB server with the same installation (not scheduled to update SP3 yet), I can rename the sqlscriptdowngrade.dll file after an admin prompt. The permission for the folder is the same for both machine.


texts: How to define shardKey on GridFS collections to achieve Location/Data Centre affinity (MongoDB)

texts:
I have set up a mongo cluster as follows:

3 shards, one per global region APAC/EMEA/AMER (in different data centres)
Shard Key is REGION + 'MONOTONICALLY INCREASING PRIMARY KEY'
The "writing" clients for the cluster [typically] connect to the mongos in their region, and will be writing documents with their region's region.
The "reading" clients for the cluster [typically] connect to the mongos in their region, and will be reading documents from across multiple regions (broadcast query).

The system is highly write intensive, with very few reads. Thus, overall I believe the above setup is optimal, since writers get fast "local" writes, while readers get convenience of single query to read across the shard, albeit slightly slow [and just to underline, for reading, I value convenience over speed].

OK so that's the background. But then suddenly I realized: I'm also using GridFS to store files, and I want the files to get stored in same way (i.e. write to local region).

Hopefully in the context of the above, my original question makes sense:

How to define shardKey on GridFS collections (fs.files/fs.chunks) to achieve Location/Data Centre affinity?

This is what I've tried:

I've noticed that I can add meta-data in during the upload operation, such that the fs.FILES collection has the region in as an accessible field that could be used as part of the sharding key for fs.files (i.e. sharding key REGION + "FILE_ID").

However, what about fs.chunks? will they follow around their "parent" fs.file record and get routed to the same shard as the "parent" fs.file?

Thanks in advance!


texts: Simple PostgreSQL lookup table is inexplicably slow

texts:
I'm trying PostgreSQL 8.4.14 for storing triples, pieces of data of the form (String, String, String).
For speed, I'm not repeatedly storing strings but rather using two tables:

main table triples (subject BigInt, predicate BigInt, object BigInt)
lookup table entities (entityId BigInt, name Varying(40000))

I've added indexes and foreign keys on triples:

"nsubject_idx" hash (subject)
"npredicate_idx" btree (predicate)
"nobject_idx" hash (object)
"triples_subject_fkey" FOREIGN KEY (subject) REFERENCES entities(entityid)
"triples_predicate_fkey" FOREIGN KEY (predicate) REFERENCES entities(entityid)
"triples_object_fkey" FOREIGN KEY (object) REFERENCES entities(entityid)

and also indexes on entities:

"entities_pkey" PRIMARY KEY, btree (entityid) CLUSTER
"name_idx" hash (name)

Now it would be reasonable to assume that lookups are fast. They aren't.

With 151M elements in triples and 44M in entities, the following query is immensely slow:

SELECT entityId FROM entities WHERE entityId in (SELECT object FROM triples LIMIT 10000);

It's only 10.000 lookups, so I'd expect this to complete at high speed. Query plan:

 Nested Loop  (cost=288.69..3856.26 rows=43806140 width=8) (actual time=25.226..40110.699 rows=6959 loops=1)
   ->  HashAggregate  (cost=288.69..290.69 rows=200 width=8) (actual time=19.445..24.087 rows=6959 loops=1)
         ->  Limit  (cost=0.00..163.69 rows=10000 width=8) (actual time=0.013..15.792 rows=10000 loops=1)
               ->  Seq Scan on triples  (cost=0.00..2474009.68 rows=151135968 width=8) (actual time=0.012..14.101 rows=10000 loops=1)
   ->  Index Scan using entities_pkey on entities  (cost=0.00..17.82 rows=1 width=8) (actual time=5.756..5.759 rows=1 loops=6959)
         Index Cond: (entities.entityid = triples.object)
 Total runtime: 40112.383 ms

What would be happening here?

Note that this is even a trick query: because of the foreign key constraint, it is actually equivalent to SELECT object FROM triples LIMIT 10000.
For my use case, I'd need the actual lookup.


texts: Use of remote indexes in distributed queries

texts:
I have a MySQL server linked in SQL Server and I can query it like this:

select * from [MYSQL]...[table] where [column] = 1

this works, but if the table contains many rows performance drops, even if column is indexed in MySQL.

I understand that generally speaking a distributed query cannot make use of remote indexes and optimizations, while OPENQUERY does, but here I cannot use OPENQUERY since queries are generated from a JavaEE JPA application.

Question is: are there any OLE DB Providers for MySQL which can take advantage of remote indexes, or is it just not supported at all by distributed queries?


texts:
I have a postgres table test with a geometry column:

         Column         |          Type          |                                    Modifiers
------------------------+------------------------+----------------------------------------------------------------------------------
 id                     | integer                | not null default nextval('converted_assessments_to_properties_id_seq'::regclass)
 parcel                 | geometry               |

In another table, I have a set of coordinates for the geometry (polygon) stored in a string field. My goal is to convert the coordinates to a geometry and insert into the geometry column within a rake task. I've tried to convert the coordinate string to an array and then convert this to a Geokit Polygon: geometry = Geokit::Polygon.new(coordinates). After I am trying to create the new record with:

Test.create(
  parcel: geometry
)

I'm getting the error that TypeError: can't cast Geokit::Polygon to, so I assume that Geokit geometries cannot be interpreted by postgres geometries? Can anybody help me to convert either the coordinates to a postgis polygon, or convert the Geokit::Polygon to a postgis polygon?

Thanks!


texts: Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

texts:
I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data will be the same (at least for now). There will be 10s of millions of rows. I could either:

create one table for boats and one for cars, or
create one table for both with an extra column indicating whether it's a boat or a car record

The second option would obviously mean I would need to write my procedures twice but perhaps there are good reasons for doing this, like keeping the number of rows smaller per table which might help in performance.

Which approach would perform better? Which approach is more appropriate from a design standpoint?


texts: Verify conditions before creating a constraint

texts:
I've taken over an application database that has been grown from an Access db. As such, the database is in need of some significant refactoring.

The first step that I'm planning is adding a number of Foreign (and at least one Primary) Key constraints. One-by-one, I've been tracking down constraints that have failed when I try to apply them to my development copy, but  I plan on adding a significant number of check constraints in the future.

I'd like to keep a running report of problem areas I need to focus on before I can apply any structural changes. Is there a way to verify the constraint criteria on the live system without attempting any schema or other changes?


texts: Is there a faster way to get UNION ALL behavior in Postgres?

texts:
I have a table (t0), in my Postgres DB, with data that looks something like this:

t1_id  t2_id
1      1
2      1
2      1
4      null
4      null
5      null

And I have a query to return my desired results of:

t1_id  t2_id
1      1
4      null
5      null

My query looks something like this:

(
  SELECT DISTINCT ON (t2_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NOT NULL
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NULL
)

Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.

Here's the query in fiddle form: http://sqlfiddle.com/#!15/d41d8/3603


texts: Data warehouse: Can I tell any details from Source System on delays or blocks?

texts:
I have a MS SQL Server 2012 machine that is acting as our data warehouse currently.  We are having issues with long running jobs, and am curious if there is a way to get detailed historical logging on reasons that a job is being held up/running long?

I know this is kind of a vague question and I apologize.

Thanks, Drew


texts: Dead Raid Card SQL Server Running in Memory

texts:
Wanted some opinions, on a predicament I’m currently facing. I just got thrown with this mess..

SQL Server database files saving to Raid volume, long story short raid card died so volume is not accessible. SQL service is still running and applications accessing the databases are still responding.

The backups aren’t upto date of course.

I have tried to export the databases out but error occurs because the files do not exist as the volume isn’t present.

However the ram is currently at 47GB.

My question is what does SQL Server store in memory, as my understanding is it stores everything in ram and then dumps to file?

Is the data recoverable by specialist if I do a raw ram dump?

Could I get backup of the database and remount another volume with the same drive letter and file structure and try exporting the database?


texts: How to import json table into PostgreSQL table?

texts:
I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data:

[{
"token": "a978f9793a594d11860e9cf7962996d7",
"name": "vehicle.moving",
"description": "Vehicle is moving."
},
{
"token": "95e38bf08c2842f39f2d64ba8152b265",
"name": "vehicle.stopped",
"description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move."
}]

Would like the result to look like:

token                            | name            | description
---------------------------------+-----------------+--------------------
a978f9793a594d11860e9cf7962996d7 | vehicle.moving  | Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 | vehicle.stopped | Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.

My SQL currently looks like this:

\set content `type "F:\Datasets\attribute.json"`
insert into attribute select * from jsonb_array_elements(:'content');

Which just adds rows of quoted json to my table.
Have been bashing at this all day (SQL rookie) and would be very grateful for help.


texts: Any way of checking mirroring monitoring whilst the primary is unavailable?

texts:
I have two sql boxes, one primary and one mirror.

The mirror stopped synchronising last night for about 12 hours and so now the primary is transferring about 25GB of logs that built up.

It's now synchronising again but I can't connect to the primary db server to check the status and to see how much there is left to transfer.

Is it possible to do this through the mirror/witness?


texts:
I have a ~300 GB database on a development server (SQL Server 2016 SP2 - Developer) that is stuck on DB STARTUP.  I am unable to restore over or drop the database. Would shutting down the SQL Server service, deleting the files, and restarting the service work to bring the instance back online and leave me able to restore the database from a backup?

Backstory: After a failed application level data import the database was left in an inconsistent state. I restarted the SQL Server service and all other database except this one came back online. This is a development instance to I was able to leave it alone to see if the process ever completed. Two weeks later when I run sp_who2 there is still an entry for this database with the command DB STARTUP and ever increasing CPUTime and DiskIO. I now need to make this database available. Would deleting the files directly work or are there other options?


texts: Ola Hallengren Parameters for Index Rebuild/Reorganize on SharePoint

texts:
I plan on using the IndexOptimize script by Ola Hallengren to replace the SharePoint 2013 On Premise Defragmentation Timer Job.

Anyone out there using it for this purpose? What params do you use and how? Here is what I put together to target the databases that the OOOTB Timer job would normally handle:

EXEC sp_msforeachdb
'if exists(select 1 from [?].sys.objects where name=''proc_DefragmentIndices'')

EXECUTE dbo.IndexOptimize
@Databases = ''?'',
@FragmentationLow = NULL,
@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

'


texts: psql variable holding text with multiple lines

texts:
This works as expected,

\set x '''2\n3'''

but this doesn't

\set x '''2
3'''

Is there a workaround for later example so I don't have to use \n instead of newline?


texts: Nvarchar column getting truncated to 256 characters

texts:
I have a SQL Server Express 2012 database. There is a table which previously had a column of type nvarchar(255). Now my requirements have changed and I am required to store long strings in the same column, so I modified the columns to have size 512.

alter Table [dbo].[mytable] ALTER COLUMN [mycol] nvarchar(512) NULL;

Now when I try updating data in the column whose size was increased, the update goes through fine. But when I query the data, the column in question only returns 256 chars of data.

I have tried updates from application (jdbc) and manual update SQL queries in SSMS. But in both cases the results are the same. The data some how seems to be truncated at 256 chars.

update [dbo].[mytable]
set mycol1 = 'Lot+hrJBVaD2hX1PjF34M2K8nLxyC3csgjZEnXOgzhyQ4iVNU8DYsPIePaMuHB4nC4cJghRSvbwQGw4me3mpynGxWeTIiCaxMFMJofF7u+yhEpa5S4hmeKaADCMHv/Mr+J/Ao3wbtJPO5VQcp6q7nUkQbVUTTAZjJ0GKLq3trpTxqNT52WrXFP+ZL1rN6Ho8rMMO74BvGxzojHo7ly9aO9iqfoi+LxJKvHK54QKpQLc/2AT5bjdUnupafURzmJvCII4DvL8R7ROSu3PbXDEEN/BmBZSY7Gune64ADh0q6R4RmLMBJFxviFMxxcjIgkKngma/uSVzYTdNLQJqD9nW2w==',
mycol2 ='a0rfSnMgCSun8J6+LmElAY4Kx1ZmCEuCkCHeVW7wi6Jo7apAZ/qiGj0kRD+ZMlK5r4icgoBufv7Lzs1zlWZkoMMdWuula5keNo8X4TH3Y8cyEL3Ujio/Co6UVL7xngdXYY6woFxPQQtW0FB+/miGlY6KrByRV9vMFsPTGc77+dc0wgFQxE3CsPU97nRVe8m/LsT97qoolSvtyB0xHtDeQ70q2VRNB2oAyeVpsqoVN7/MQb7xW9glqYk0w/uHhZ6kx5QkSEbn1Ssq77KN/96yQluzcDm9E9eiMBdDMXFsu3FjqPy5BSyyvLi13NKaJcpaGo+do8jz0Rl6iXuorfSNyw=='
where ... conditions;

select max(len(mycol1)), max(len(mycol2)) from [dbo].[mytable];

...returns 255. No errors were shown when running the queries.

I checked that elsewhere in the same database I have longer columns (nvarchar(998)) and there the updates go through fine - I can store longer values and get that data successfully.

I also tried rebooting the database just in case the alter did not go through properly but still data is truncated at the same size. I have already confirmed that the table shows the correct column size when I check the properties.

I have this database sitting on AWS RDS. Incidentally same column level alter works fine on the database on my local machine.

I do not have any views in the database and I have tried querying the exact records I update. This alter script works fine on my own local instance of SQL Server; the problems show up only on the SQL Server instance on my test env (AWS RDS).

I can't share the table definition due to its proprietary nature. I am not using a stored procedure. I tested by running a plain SQL update query using SSMS.
The application has parameter set as String, and does not do any datatype truncation. It leaves that to the database.

Any ideas on what could be wrong appreciated.


texts: Timeout on remote query over database link

texts:
We are trying to verify if a database link is up before inserting data into a remote database.  We have no control over the remote database's behavior and status.  We need to set a timeout on a query which is using a database link before deciding whether we can insert data.

Currently when the link or the remote database goes down in the middle of a transaction, the user has to wait for up to 20 minutes before regaining control on the session.  We want to set the limit to a minute or less.

We're running Oracle 10gR2.

How can we set a timeout for a query like this?


texts: How to make sure vacuum is happening in postgres sql db

texts:
I have created a table with two columns and created index for the first column. Then inserted 2 million data using generate series function for first column (2nd column is having null). And executed below query

    explain select * from mytable;

Getting cost as 19116.00 (something like this)

Then I am removing 1 million record from the mytable and running the explain query again, but getting more cost

Enabled vacuum parameters

track_counts = on
autovacuum = on
log_autovacuum_min_duration = 0

I could not understand, why cost is getting increased (expecting less cost). Is there any other parameter needs to be enabled?

(Using Postgres 9.1)

Edit 1

create table mytable (c1 numeric(19,0), c2 numeric(19,0));

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..21.60 rows=1160 width=40) (actual time=0.002..0.002 rows=0 loops=1)"

"Total runtime: 0.033 ms"

create index c1_idx on mytable using btree (c1);

insert into mytable (c1) values (generate_series(1,2000000))

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..19116.00 rows=1026600 width=40) (actual time=0.048..251.834 rows=2000000 loops=1)"

"Total runtime: 293.915 ms"

select pg_size_pretty(pg_table_size('mytable')) --"69 MB"

delete from mytable where c1 between 1 and 1000000

explain analyze  select * from mytable;

"Seq Scan on mytable  (cost=0.00..28850.00 rows=2000000 width=26) (actual time=356.616..458.278 rows=1000000 loops=1)"

"Total runtime: 478.645 ms"

(Actually, cost increased and still table size is same)

Edit 2

After runs vacuum on the table

"Seq Scan on mytable  (cost=0.00..18850.00 rows=1000000 width=26) (actual time=31.308..117.244 rows=1000000 loops=1)"

"Total runtime: 138.911 ms"

Cost reduced as expected, but got another doubt on it. When mytable is having only 1M record cost is less than above.

truncate mytable;

insert into mytable (c1) values (generate_series(1000001,2000000))

explain analyze select * from mytable;

"Seq Scan on mytable  (cost=0.00..10841.25 rows=641625 width=26) (actual time=0.058..121.894 rows=1000000 loops=1)"

"Total runtime: 154.454 ms"

Old cost is around 18K where for the same rows around 10K. Kindly ignore if its changing the topic :-)


texts: Mysql error "#1030 - Got error -1 from storage engine" when doing insert/update queries

texts:
I am starting to learn manipulating some long list of data from datalogs in mysql, around 300k entries, and it is being added by about 1k off entries everyday, it was working fine in a week then suddently I'm not able to write in the db. Here are the errors that I'm getting:

I can do select queries but when I do insert and update I am
getting this error:

  #1030 - Got error -1 from storage engine

When I try to change the engine type from InnoDB to MyISAM, I'm
getting this error:

  #1025 - Error on rename of './dbname/tablename'
  to './dbname/#sql2-500-37' (errno: -1

I can create new tables

These are the things that I have checked:

Any of my disk space is still more than 50%, I have checked it using df
Last line in the MySQL error log (/var/log/mysql/error.log):

InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.

Tried restarting server & mysql service
I also tried doubling the values in the MySQL config file but it seems it doesn't help, here is my config file (/etc/mysql/my.cnf)

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

bind-address            = 127.0.0.1
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M

expire_logs_days        = 10 .cnf', otherwise they'll be ignored.
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M.cnf', otherwise they'll be ignored.

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/
innodb_force_recovery = 0

My server info is:

Server: Localhost via UNIX socket
Server type: MySQL
Server version: 5.5.43-0ubuntu0.14.04.1 - (Ubuntu)
Protocol version: 10
User: sfere01@localhost
Server charset: UTF-8 Unicode (utf8)

Any other tips on how to further troubleshoot & resolve this kind of issue? What other things should I have checked or missed?


texts: Alternative to using MIN() in a subquery?

texts:
I have the following foo table:

+----+--------+-------+
| id |  name  |  qty  |
+----+--------+-------+
| 1  |  John  |   3   |
| 2  |  John  |   1   |
| 3  |  Mary  |   5   |
| 4  |  Mary  |   2   |
| 5  |  Gary  |   3   |
| 6  |  Gary  |   4   |
| 7  |  Gary  |   5   |
+----+--------+-------+

I would like to select only id and name of minimal Qty grouped by name, with this result:

+----+--------+
| id |  name  |
+----+--------+
| 2  |  John  |
| 4  |  Mary  |
| 5  |  Gary  |
+----+--------+

The only way I could do this was by means of the following operation:

SELECT id, name FROM (SELECT id, name, MIN(qty) FROM foo GROUP BY name) AS a;

Is there a more "beautiful" (or less redundant) way to do that?


texts: Restarting SQL Server's services remotely programmatically

texts:
I have the need to restart Microsoft SQL Server's group of services on a given computer, remotely, through some sort of programmatic method I can insert into a workflow. I'm looking for the best way to do this, I've seen a few different ways and I'm wondering if there's anything I'm missing:

Use Net Stop and Net Start - This would work if I only needed to restart the SQL Service and there were no dependencies, not really an option if SQL Server Agent is running.
Use SC //<computername This would allow me some access into the servicecontroller so I could query whether or not there's a dependent service running.
Use the .NET Servicecontroller method, which would let me build an array of services running on the target computer and then iterate through them to stop what I need to stop.

Am I missing any ways?


texts: Inserting date from string: CAST vs CONVERT

texts:
Consider the following two ways to convert a datetime varchar string into a date field:

SELECT convert(date, '2012-12-21 21:12:00', 20) -- Only date is needed
SELECT cast('2012-12-21 21:12:00' as date) -- Only date is needed

Both return what I expect: The date excluding the time, in as a date datatype.

My question is: Is there any pro and cons of doing either way?


texts: With partitioned table, How can I use hints to group each partition separately

texts:
Suppose I have the following table
 data(partitioned_key_index, some_dummy_measure)

Assume that partitions are of an equal large size. with Oracle 11g.

The end result should be like this

select partitioned_key_index, sum(some_dummy_measure)
  from data group by partitioned_key_index

Each partition will be grouped independently, the optimizer should be
clever enough to come up with a plan in which each partition will be aggregated then a simple 'union all' to get the desired output.

What I want to do is something close to this

 select 1 as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = 1
 Union All
 select 2 as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = 2
 Union All
  .
  .
  .
 select i as partitioned_key_index, sum(some_dummy_measure)
  from data where partitioned_key_index = i

My intuition with the above method is to to serialize the hash group operation
thus each partition will be moved from the disk to the buffer cache with the hope of not spilling into disk for the group by operation.

Any ideas how to tune this kind of queries?


texts: Join and count boolean columns by logical OR

texts:
There are two tables: user_table and user_schedule. Each user has four schedule rows (one for each 6 hour period per day) with one column for every workday.

The desired result is to count the number of users that are present for at least one period per day of the work week. For example, if a user is present two quarters of a day, that counts as one.

This is what I thought would work for a Monday, but it's incorrect:

SELECT
 count(CASE WHEN TRUE = ANY(ARRAY_AGG(mon)) THEN 1 ELSE null END) as mon,
FROM user_table
LEFT JOIN schedule_table ON user_table.id = schedule_table.user_id
GROUP BY user_id

I created a SQLFiddle to demonstrate.

EDIT:

I was looking for an summary table like this

+-----+-----+-----+-----+-----+
| Mon | Tue | Wed | Thu | Fri |
+-----+-----+-----+-----+-----+
|   4 |   3 |   4 |   4 |   4 |
+-----+-----+-----+-----+-----+

I am running PostgreSQL 9.3

Here are the table definitions I am using:

CREATE TABLE user_table
(
  id integer,
  start_dt date
);

CREATE TABLE schedule_table
(
  id integer,
  user_id integer,
  mon boolean,
  tue boolean,
  wed boolean,
  thu boolean,
  fri boolean
);


texts: Exclude rows with unique values in one column from grouped mysql query

texts:
The following table represents interactions form users with different projects:

id         user_id    project_id
17373      6336       1001
17374      6336       1001
17375      6336       1001
529044     6336       1001
17305      6342       1003
17350      6318       1003
17388      6318       1003
811062     6342       1003
811063     6342       1003
1552793    6342       1003

I want to see which users are interacting with each other in the same projects. In other words, I would like to exclude

rows where a project_id is associated with only one user (row 1-4), because there are no interactions then.
and duplicate rows (last 4 rows), because I only want to know if there is an interaction or not (the intensity does not matter to me).

The query should result in:

id         user_id    project_id
17305      6342       1003
17350      6318       1003

Showing that user 6342 and 6318 are interacting in project 1003.

To achieve that I would start with grouping the results:

SELECT id, user_id, project_id
FROM t
GROUP BY user_id, project_id

Result:

id         author_id  project_id
17268      6336       1001
17350      6318       1003
17305      6342       1003

How can I now exclude the results with a unique project_id (row 1)? Adding HAVING COUNT(*) > 1
does not work.

This is the desired result:

id         author_id  project_id
17350      6318       1003
17305      6342       1003


texts: SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

texts: I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables explicitly specified. What is the easiest way to create schema which includes/has access not to all objects in the database. Is it possible at all? I'm trying to do this in MS SQL Server 2012 R2.


texts: Sqlyog: copy table from localhost to remote server without duplicate records

texts:
Hi guys i want to copy record from table which is stored in localhost into remote server but when i tried to copy there is pop up like below:

If i checked Drop if exists in target it would delete all of data in table, but if unchecked it would duplicate several record,that's why i want to copy record from table in localhost into remote server without duplicate data or delete existing data, so just adding new data from localhost table and ignoring existing data. Is there any way to use such a feature like that in sqlyog ? if not how could i to do this task ?


texts: Convert column from string of numbers to a subtotal on select statment

texts:
This probably very basic but I have struggled to figure out. I have looked around but couldn't find any similar problem. I am using SQL Server 2008 R2

I have a view:

ID  |           VALUE
1   | 151;181;179;185;160;187
2   | 185;160
3   | 151;181;179;185;150
4   | 185;160;187
5   | 187

I want a select statement to produce this results:

ID  |           VALUE
1   |           1043
2   |           345
3   |           846
4   |           532
5   |           187


texts:
I currently have a on-premise SQL Server 2014 High-Availability cluster.
I will setup a active geo-replication/auto failover group in Azure.
I need to do a one-time data migration---Should I use Azure Data factory?
Also, how do i ensure compatibility with the legacy Sql Server 2014 ?

Thanks, Peter


texts: Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

texts:
Oracle has a favorite numerical class called NUMBER.  It is a 22 byte value that just seems ludicrous in an index.  I have some values that would easily fit in a 6 byte integer and would therefore fit in a nice native datatype that is 64 bits long.  However oracle keeps making my numbers 22 bytes long.

The only place I can see this is if I put the value in an index, and there is shows me the value is a 22 byte number.

I understand it is nice to have numbers that don't have rounding errors, I can appreciate that, but I just want an integer that can be used as a ForeignKey to another table, and rounding won't be an issue.

Mysql, Postgres, MSSQL all have variants that let me specify a datasize (via some mechanism).  Oracle wants to map almost every other numeric type back to NUMBER(size, precision), but no matter what values I use for size or precision, oracle insists the number takes up 22 bytes in my index.  That is nearly 3x longer than I need and effectively cuts the M of my btree.

Oracle does have BINARY_FLOAT and BINARY_DOUBLE, it even has a ROWID which is nothing like an integer at all.

Is there some secret sauce that Oracle has that makes NUMBER uber efficient?  Are there secret docs somewhere that show datatypes that map to native datatypes?


texts: Cannot select all data from a table php/sql

texts:
$sql = $conn->prepare('SELECT * FROM users WHERE user_id != null');
if($sql->rowCount() > 0){
}
else{
echo "there are no users"
}

I want to select all data from a table however the code above does not work. It echoes/prints out "there are no users." No errors are shown too.


texts: using a column that references an object_id in your own table

texts:
Is it safe to use OBJECT_ID() as a reference in your own tables, with the assumption that the value will always refer to the same database object no matter what?
The msdn states

All metadata functions are nondeterministic. This means these
functions do not always return the same results every time they are
called, even with the same set of input values.

so that would indicate it's not safe, but I couldn't find any (other) hard facts that it shouldn't be done, and, to be fair, no examples of people actually doing it, which in itself might be evidence not to do it.
But the value OBJECT_ID() is (or seems to be) used in the system tables and views as keys from one object to another, so there must be some form of integrity and reliability.
For the record: I don't necessarily need it, but it just popped into my mind as an option while designing my system, and got curious.


texts: Prevent read access on primary AG replica, but allow it on the readable secondary

texts:
We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We have setup read-only routing to accomplish this. This is also all on SQL 2016.

My initial thought was to create the login on both the primary and secondary replicas and grant read access to the database in question. Then we'd DENY CONNECT or disable the login on the current primary replica. In SSMS, the users could then connect to the Listener with ApplicationIntent=ReadOnly and be routed to the secondary replica without ever touching the primary.

We'd setup a simple job on both the primary and secondary replica servers with basic logic: IF current server = primary then disable login; if current server = secondary then enable login.

The problem is that I'm getting login failures when connecting to the listener with readonly intent when the login is disabled on the primary server. When I re-enable the login on the primary replica, it works just fine and the connection is properly routed to the readable secondary.

I setup a trace on the primary server and sure enough, I can see the login connect and run some system type queries in both master and msdb on the primary replica - even though I'm connecting with ApplicationIntent=ReadOnly in SSMS. I'm not sure if this is something that SSMS does behind the scenes or if it is the default behavior of a login going through the read-only routing process.

Here are the queries I captured with the quick profiler trace on the primary:

--msdb query

    select
        case
            when object_id('dbo.sysdac_instances') is not null then 1
        else
            0
        end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

Has anyone had to deal with this situation before? It seems we basically need to allow a login connect permission on the primary replica while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

The other alternative is to create a DNS entry that points directly to the secondary replica, but we can't guarantee that replica will ALWAYS be the secondary as a failover could happen.


texts: Loop Block with execution doesn't persist

texts:
I need to iterate over a result set and update records from the data.  I am using an anonymous block but the information does not persist.

Like this...

do $$
declare dados Record;
begin
   for dados in
        select
           vd."Id"
        from "Vendas" vd
           inner join "ItensVendas" iv on iv."VendaId" = vd."Id"
        where vd."Especie" = 'NFE' and coalesce(vd."ClienteId", 0) = 0 and iv."CodCFOP" in (5949, 5927)
        group by vd."ChaveNFE", vd."Id", vd."DataCadastro"
   loop
        execute format('update "Vendas" set "EhConsumoProprio" = true where "Id" = %L', dados."Id");
   end loop;
end $$;

And again...

do $$
declare dados Record;
declare cmd varchar(300);
begin
    for dados in
        select
            vd."ChaveNFE",
            vd."Id",
            vd."DataCadastro"
        from
            "Vendas" vd
        inner join "ItensVendas" iv on
            iv."VendaId" = vd."Id"
        where
            vd."Especie" = 'NFE'
            and coalesce(vd."ClienteId",0)= 0
            and iv."CodCFOP" in (5949, 5927)
        group by vd."ChaveNFE",
                 vd."Id",
                 vd."DataCadastro"
    loop
        raise notice '%',dados."Id";
        update "Vendas" set "EhConsumoProprio"=true where "Id"=dados."Id"; -- It don't persists
        cmd := format('update "Vendas" set "EhConsumoProprio"=true where "Id"=%L', dados."Id");
        raise notice '%', cmd;
            execute cmd; -- It don't persists
    end loop;
end $$;


texts: How to auto start oracle listener on Linux with machine restart?

texts:
I've a Linux VM with Oracle database serve and it gets restarted periodically and hence next time, oracle listener is not running. So I've to start it manually with following commands -
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl;
$ORACLE_HOME/bin/lsnrctl start LISTENER

sqlplus
sys as sysdba/password
startup

This activity I've to do every time machine is restarted.
Is there a way with which oracle listener is started along with machine start? i.e. something like windows services which gets automatically started with machine start.


texts:
I'm using sqlalchemy to store objects in a database.  The kind of object I'm creating is unknown until run time.  I could be storing something as simple as a string.  I could be storing a jpg.  I might create my own file format and store that.  The point is it's flexible.

The table I envision contains at least two columns.  One describes the file type (.jpg, .txt, .py, etc), and the other is a blob with the file data.  What I want to know is whether I need to define the blob size when I create the table.  Is it possible to let mysql dynamically set the size of blob?  This would simplify my design nicely.

I'm aware there are other ways to do it, but I have good reasons to do it this way.  I don't mind going down this path and doubling back later if it turns into a dead end.


texts: Is there any way to increase the mysql reserved pool for super users beyond 1?

texts:
http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

States that there is 1 connection beyond your maximum number of connections for users with the super privilege.

My question is can you increase that number?  Eventually I want to put limits in for all users that leave additional connections open for administrative jobs, but I'm not in a position to do that right now.

I want to increase that number to 10 so I can have replication jobs and monitoring jobs not affected by application connections.


texts:
Are there any tools out there comparable to SSMS Tools Pack 2012? The licensing ($30 per machine, $100 for any number of machines... for 3 months) leaves a lot to be desired and I wasn't sure of what other options are available.

For example, one thing that I really miss is the "Save every query you run". When tinkering and researching, it's been invaluable to have a running backup of different versions of queries as I run them. Or when I realize I don't have a backup of a query I was working on 2 months ago.

Clarification: SQL Server Management Studio's have no official addon support, but there are some tools out there. SSMS Tools Pack is one that I've been very fond of (2005, 2008 versions), but the licensing fee for 2012 is horrible. (I would pay for a reasonable license, but that's not the question here.)

I've found SSMS Boost for example that has some cool additions to SSMS that seem worth it.

What other addins are available for SQL Server 2012? I'd love to have something that saves a query when I hit F5 like SSMS Tools Pack does, or anything out there besides the two tools listed?


texts:
I have table with the columns of type NTEXT. And for some peculiar reasons, the client wants to migrate the column type to NVARCHAR(MAX).

While doing so, what all potential threat should I be careful about?

Is there any possibility of data loss for the NVARCHAR(MAX) limitations?


texts:
I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.

server 1 windows 2003 server 192.168.10.1 connected by vpn SQL Server 2005 domain1.local

server 1
windows 2003 server
192.168.10.1 connected by vpn
SQL Server 2005
domain1.local

server 2 windows 2003 server 192.168.10.6 connected by vpn SQL Server 2005 domain2.local

server 2
windows 2003 server
192.168.10.6 connected by vpn
SQL Server 2005
domain2.local

When I setup up Replication everything looked fine until I looked at the sync status and it said:

  The Agent could not be started.

  An exception occurred while executing a transact-sql statement or batch
  sqlserveragent error request to run job
  server1-username blah blah blah
  From user sa refused because the job is already running from a request by user sa changed database context to technical error 22022.

I have cleared jobs in the server agent as well as restarted the service.

Could this be something to do with authentication between two non trusted domains as I can browse and even control each SQL Server via SQL Server Management Studio but just not setup replication?

Yes I can manage each SQL Server in SSMS and we are using merge with snapshot.


texts:
I need to understand how partitioning can be applied to tables for an oracle 11.2.0.4.0 database.
Please start a guide with me that hopefully with helps others who would like to learn as well.  After the go live , the production database will grow very quickly. Currently a DBA on client side is partitioning the tables on the fly when it is needed and I am not sure what scripts or logic he is using.  I will have to take over this responsibility soon.

My Job/Task/Goal:
Each important table has specifications for the type of partitioning that will need to be implemented to handle the data. Which I will be applying.

For example using the following parameters to partition a table:

Table: Clogs
Schema: Schema name
Range Partition on ISSUEDATE ( MAJORITY of the table will need range partitionin)
Group partition by daily.
Partition Naming Convention : clogmd_YYYYMMDD

Note: I have no idea where to start. I have a 5 years of IT and Systems Admin experience but I am not an expert but I am not a complete newbie either. I have paid my dues so please take it easy on me.

Some of the code I am working with is.

ALTER TABLE tableowner.tablename drop partition
partitionname_YYYYMMDD (respective partitionname)

ALTER TABLE tableowner.tablename split partition
maxpartitionname_YYYYMMmax at (to_date('YYYY-MM-DD
00:00:00','SYYYY-MM-DDHH24:MI:SS'))  into (partition NAME_YYYYMMDD
tablespace name_WXY1, partition NAME_201602MAX tablespace NAME_WXY2;

ALTER TABLE tableowner.tablename add partition NAME_YYYYMMDD
(respective PARTITIONNAME);

Please guide me and let me know of any considerations I should be taking. I greatly appreciate any help.


texts: UPDATE Query replacing string primary key taking days to resolve

texts:
I'm working with a DB (not designed by me) that had the primary key set to a 47 character length string, which is causing problems due to the fact that the table has over 20 million rows, and this number increases daily. I'm currently working from my development laptop, which is definitely not as powerful as the server, but I only downloaded part of the DB to test changes/speed of execution.

The pk has 4 parts, 2 are static and so I'm hoping to cut them, one is a semi-unique identifier (aka it should be unique but is not guaranteed to be unique) that could be stored as a bigint, and the other is a UUID-like value that is a pk in another table (with ~30,000 rows) that could be represented as a hex string literal (aka I can store as bytea and use it to output as a string when needed). So, I added serials to both tables, but I need to keep the semi-unique identifier and the reference to the other table as they will be necessary for output purposes, although after this they should be ignorable for joins etc.

So I wrote the following query (tables etc. renamed):

UPDATE bigtable set foreign_key = foreigntable.new_id,
semi_unique_id = (split_part(bigtable.old_id,'.',3))::bigint
FROM foreigntable
WHERE foreigntable.old_id = split_part(bigtable.old_id,'.',2);

I tested this query on 50 rows (AND WHERE bigtable.new_id IN (1,2,3....) and it looked like this would take several days to run.
I thought, maybe it was too much work to do lookups this way, maybe I'll do it in parts, the first query simply to split out the two pieces of information I need, and the second to actually get the information I want from the other table.

So it would look something like this:

UPDATE bigtable SET old_foreign_pk = decode(split_part(old_id,'.',2),'hex'),
semi_unique_id = (split_part(old_id,'.',3))::bigint;

followed by

UPDATE bigtable SET foreign_key = foreigntable.new_id
FROM foreigntable
WHERE foreigntable.old_id = split_part(bigtable.old_id,'.',2);

However, running the query on 50-100 rows (using the method above) seems to suggest it will take 1-2 days to run. That kind of down-time is not really acceptable, can these queries be optimized or is this the cost of having a bad pk for so long?

Edit:

the old pk is stored as character varying and has this form:

log.501ad896c4960b089dd8638f.1344902965419.txt
log.4fe5242a785d0b080de707fa.1344903775142.txt

of which the first number uniquely identifies a row in another table, and the second is theoretically unique but is not guaranteed to be so. so the first is essentially 96 bits stored as a hex string that is a unique identifier (which is why it's only UUID-like), whereas the second is bigger than a integer but smaller than a bigint. the two, when combined do reference files, so they can't be discarded but it's not very efficient in terms of storage.


texts: SQL Row concatenation with XML PATH and STUFF giving aggregate sql error

texts:
I am trying to query two tables and get results like the following:

Section    Names
shoes      AccountName1, AccountName2, AccountName3
books      AccountName1

The tables are:

CREATE TABLE dbo.TableA(ID INT, Section varchar(64), AccountId varchar(64));

INSERT dbo.TableA(ID, Section, AccountId) VALUES
(1 ,'shoes','A1'),
(2 ,'shoes','A2'),
(3 ,'shoes','A3'),
(4 ,'books','A1');

CREATE TABLE dbo.TableB(AccountId varchar(20), Name varchar(64));

INSERT dbo.TableB(AccountId, Name) VALUES
('A1','AccountName1'),
('A2','AccountName2'),
('A3','AccountNAme3');

I saw a few questions answered saying to use "XML PATH" and "STUFF" to query the data to get the results I am looking for, but I think there is something missing.  I have tried the below query and get the error message:

  Column 'a.AccountId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I don't have it in the SELECT clause of either query, but I assume the error is because AccountId is not unique in TableA.

Here is the query I am currently trying to get working correctly.

SELECT section, names= STUFF((
    SELECT ', ' + Name FROM TableB as b
WHERE AccountId = b.AccountId
FOR XML PATH('')), 1, 1, '')
FROM TableA AS a
GROUP BY a.section


texts: Why is restart_lsn not set, even on checkpoint - postgresql

texts:
A logical slot will emit each change just once in normal operation.
  The current position of each slot is persisted only at checkpoint, so
  in the case of a crash the slot may return to an earlier LSN, which
  will then cause recent changes to be resent when the server restarts.
  Logical decoding clients are responsible for avoiding ill effects from
  handling the same message more than once. Clients may wish to record
  the last LSN they saw when decoding and skip over any repeated data or
  (when using the replication protocol) request that decoding start from
  that LSN rather than letting the server determine the start point

Quoted from here

I'm implementing a logical (streaming) replication.

Thus far things are looking good but I'm unable to understand why restart_lsn entry is not changed.

i.e I do this..

SELECT * FROM pg_replication_slots;
 slot_name |  plugin  | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
-----------+----------+-----------+--------+----------+--------+------------+------+--------------+-------------
 pgx_test  | wal2json | logical   |  12641 | postgres | t      |      16045 |      |      8544916 | 2/B1BC5330

Insert 2/3 record

SELECT * FROM pg_stat_replication;
  pid  | usesysid |     usename     | application_name | client_addr | client_hostname | client_port |          backend_start           | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+-----------------+------------------+-------------+-----------------+-------------+----------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
 16315 |  1601109 | pgx_replication |                  | 127.0.0.1   |                 |       50112 | 2019-03-11 17:33:18.150179+05:30 |              | streaming | 2/B1BCA0F8    |                |                |                 |             0 | async

The sent_location gets updated but not the restart_lsn, even though I ran a checkpoint command had set the checkoint_timeout=1m but still the replay_lsn does not change.

SELECT * FROM pg_replication_slots;
 slot_name |  plugin  | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
-----------+----------+-----------+--------+----------+--------+------------+------+--------------+-------------
 pgx_test  | wal2json | logical   |  12641 | postgres | t      |      16315 |      |      8544916 | 2/B1BC5330
(1 row)

Any reason why this happens?


texts:
I am running Merge Replication on Microsoft SQL Server 2012 through web sync.

Is there a way to recognize when regenerating a new snapshot and applying it would be more efficient than running a merge?

For instance my system has a way that a user can change settings to determine which data each subscriber receives. This won't happen often, but if it does then millions of INSERTs could be sent to the subscriber representing the new data.

Is there a way to say "hey, I have over a million updates, actually recreate a new snapshot and apply it instead"?


texts: SSDT project with working linked server references gets SQL71562 warning

texts:
I have a VS 2013 solution with two database projects.

One of them, let's call it the "worker" is supposed to get deployed to about 10 different production servers.

The other one, that is supposed to work as an "Controller" instance, is basically supposed to run different stored procedures on the "worker" database on the 10 production servers.

So to realize this, I have scripted 10 Linked Server objects within the "controller" and call the different SPs in the "worker" databases.

Here is a sample for the Linked Server scripts:

EXEC sp_addlinkedserver @server = N'PROD1', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'1.2.3.4\prod1'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname=N'PROD1',@useself=N'False',@locallogin=NULL,@rmtuser=N'aaaaa',@rmtpassword='aaaaa'
GO
EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC sp_serveroption @server=N'PROD1', @optname=N'rpc out', @optvalue=N'true'
GO

So far so good, this works and the Linked Servers are created on the controller SQL Server and I can use them.

BUT:
Within the SSDT project / solution, I get broken references whenever I use the Linked server objects. Sure, the schema is not aware of the linked server names, since they are only in a string constant in the above script.

Sample: (I translated the message on my own, no guarantee it's exactly right)

  Warning   14  SQL71562: "Procedure: [dbo].[SP1]" contains an unresolved rerference to object [PROD1].[db1].[dbo].[SP1]".

So my problem is that I have kind of a reference from the Controller database schema to the "worker" database schema, but I can't use / insert a database-reference, because I use several different references (10 production servers) to "access" the worker's schema...
I don't want to add 10 database references to my project with the "OtherServer" option (even if I could, I guess it won't allow to add 10 references to the same database project...)

I have a feeling that it should be possible to address the linked server objects in a correct manner in the solution, but I can't figure out how.

I tried Aaron's suggestion with Synonyms, but there I end in the same problem as soon as I use the linked server's name resulting in unknown objects "behind" the name.

I edited the question to make the problem more clear and hope anyone has an idea, because I want a clean solution without hundreds of WARNING messages.


texts: Handle concurrency on sql server for a booking system

texts:
I've been banging my head against the wall trying to solve this problem and I couldn't come up with a solution that absolutely guarantees two users can't book an appointment for the same time.

The flow and scenario:

1 - The user enters the system and selects a service, say a haircut, and then clicks "Book".

2 - Upon clicking "Book", the server receives the date and time and the staff the user selected to perform the haircut. Based on this information, the available hours the selected staff has are calculated on the fly and sent to the user, who will see something like: 10:00, 12:00, 13:00, 15:00, for example.

3 - The user, then, selects one of the available hours and clicks "Confirm".

The problem:

Two (or more) users can select the same service and staff to perform that service, and when that happens, all the users will be presented the same available hours.

Now, what happens is that when more than one user selects the same available hour, I need to be able to guarantee 100% that only the first will be able to schedule an appointment, and the other will be faced with an error.

I could not come up with a mechanism that ensures no racing condition will occur.
Note that this is a bit different from transport and theather domains, because I do not have a table with all the available hours to simply flag them.

The closest I've come to a solution is to create an extra table, such as "AppointmentsInProgress", and add the starting date and time and the staff to the table, and make both columns unique.
Then, before proceeding to process the user request, check this table to see if nobody else is currently scheduling for the same date, time, and staff.

However, the performance of this solution is a big concern, and I read that the order of sql commands is not guaranteed, which means if two sqls hit the server with a microsecond of difference, there is no guarantee the first will have precedence. Furthermore, something tells me this doesn't really solve my problem.

Any ideas?


texts: Query failing on Oracle 12c and 18c when changing NLS_SORT to BINARY_CI

texts:
I have the following query which correctly returns records when I execute it via code or Oracle SQL Developer.

SELECT TABLE_T.COL_P,
       1234 AS COL_C,
       TABLE_T.COL_D,
       SUM(SOME_COLUMN) Value
FROM TABLE_T
INNER JOIN TABLE_E E ON TABLE_T.COL_P = E.COL_P
AND TABLE_T.COL_C = E.COL_C
AND TABLE_T.COL_CC = E.COL_CC
AND TABLE_T.COL_CL = E.COL_CL
INNER JOIN TABLE_C C1 ON C1.COL_P = E.COL_P
AND C1.COL_C = E.COL_C
INNER JOIN TABLE_C C2 ON C2.COL_P = C1.COL_P
AND C2.COL_CX = C1.COL_CX
AND C2.COL_CY = C1.COL_CY
AND C2.COL_CZ = C1.COL_CZ
WHERE TABLE_T.COL_P = 'Some Text'
  AND C2.COL_C = 1234
  AND TABLE_T.COL_CL IN
    (SELECT COL_CL
     FROM TABLE_CL
     WHERE COL_P = 'Some Text'
       AND ((COL_CLTYPE = 'VALUE_A')
            OR (COL_CLTYPE = 'VALUE_B')
            OR (COL_CLTYPE = 'VALUE_C')
            OR (COL_CLTYPE = 'VALUE_D')) )
GROUP BY TABLE_T.COL_P,
         TABLE_T.COL_D

However, it fails to return records once I execute the following session commands:

ALTER SESSION SET NLS_COMP = LINGUISTIC;

ALTER SESSION SET NLS_SORT = BINARY_CI;

This problem only occurs when I'm running against an Oracle 12c or 18c database.

It works find with/without the session commands when running against an Oracle 12C R2 or 11g database.

I've already checked the Explain Plan for 12c/18c and 12cR2 and its creating the same plan.

I found out that by adding an ORDER BY clause to the query (ORDER BY TABLE_T.COL_D, it resolves the problem.

Any ideas on what might be causing this problem?

I know the ORDER BY solution works, but I'd like to know what the underlying cause is and if there's a better solution to it.


texts: An error was encountered in the transport layer - SSAS 2012 from Excel 2013

texts:
When connecting to an SSAS 2012 tabular model from Excel 2013 (Data... From Other Sources... From Analysis Services... fill in Server Name... click Next), I get the error:

  An error was encountered in the transport layer.

Excel, SQL Server, and SSAS are all running on the same server (Windows Server 2012).

Because the server needs to be accessed from the Internet, there is a DNS entry for the server x.mydomain.com but the server's actual Windows computer name is something else servername.

In the Excel (on the server's desktop) connection dialog, if I use servername it works without error.  If I use x.mydomain.com I get the above error.

I have added a hosts file entry so that x.mydomain.com points to "::1".  From the server, pinging x.mydomain.com or servername both return the same "::1" for the address.  This did not help the problem.


texts: Can you get multiple values from a table into one row?

texts:
I'm extremely green at database design and have been given a project to complete as part of an assignment.

My question is, in regards to values from a table, in this instance, it relates to health club facilities.

Let's say there are multiple facilities that a health club could have such as 24-hours gym, swimming pool, sauna, kids playroom, outdoor training park, cycle studio. My plan would be to store these and have a sequential ID number relating to each.

If I store this information in one table, can multiple ID numbers be referenced in a single row in another?

for example, if the health club "Kingsway" has 4 of those facilities, can I show in the row relating the "Kingsway" have under the facilities column ID numbers 1,2,5,6?

Cheers


texts:
Is the best way to implement a large database is XML? Or is there a better way?

1 - reduce the time complexity
2 - Reduce the amount of traffic between the tables
And other factors ...

Which example is better, one or two?

1.Student Table: (without XML)
 id
 name
 family
 age
 address

2.Student Table With XML
 id
 XMLDT (contains name,family,age,address)


texts: Table design: paired values of genetic data

texts:
I'm trying to decide the appropriate way to design my tables to store my genetic data. Basically, I have samples, and each sample has multiple loci (a locus is basically a point on the genome), and each locus has a pair of values representing different alleles, or variants (basically, one allele is obtained from the father, and one from the mother).

There are potentially dozens or even hundreds of loci, depending on the project. The two alleles may or may not have the same value, and there is no meaning in the order they appear.

This is what the raw data might look like, and is also typical of what some programs expect for import:

On the plus side, it's still relatively easy to visually interpret and manually enter or change individual values without some other tool. On the negative side, there is no relationship between allele1 and allele2, they just get entered in a random order. This can make querying the data somewhat more cumbersome in some situations, and ends up involving a union. What really bothered me, though, was that it just doesn't seem to meet normalization rules with having alleles spread between multiple columns. It'd be like having pet1, pet2, pet3... as columns in a table listing owners and their pets.

So, I came up with this, which is what I'm currently using:

I like it more because it seems like it's properly normalized, but the downside is that it is visually harder to work with. I don't mind this, because although I'm a biologist, I have a relatively strong CS background and am used to thinking about data abstractly. But my colleagues do not have that background, making it harder for them than it is for me.

Ultimately, I can write scripts to convert the raw data to the proper format for import, as well as converting data exported from the table for use in other programs, but this all depends on finalizing the table design. So, have I properly designed the table for this data? Or am I mistaken about it being normalized? Is there some other alternative I'm missing?

Thank you.


texts:
I have a table with 85M rows :

CREATE TABLE emissions_totales (
"schema_source" varchar(150),
"snap" varchar(20),
"gid" varchar(255),
"polluant" varchar(50),
"emi" float8,
"numreg" varchar(3),
"unites" varchar(60),
"annee_ref" varchar(4),
"type_source_n1" varchar(255),
"type_source_n2" varchar(255),
"type_source_n3" varchar(255),
"napfue" varchar(15),
"naf_rev2" varchar(10),
"entite" varchar(255),
"id" int4)

but a query like this one:

SELECT
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites,
    sum(emi) as emi
FROM emissions_synthese.emissions_totales
WHERE schema_source='residentiel'
GROUP BY
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites;

takes more than 3 hours to perform. What is the best way to make indexes? Do I have to make unique index on schema_source, numreg, annee_ref, type_source_n1, polluant and unites or a multicolumn index?

I have modified the postgresql.conf file by reading various sites, do I have to change something?

shared_buffers = 956MB
work_mem = 5MB
maintenance_work_mem = 128MB
checkpoint_segments = 64
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
effective_cache_size = 2304MB

(postgresql 8.4 on Debian Squeeze, 3.0 Go RAM, 2 Intel Xeon E5345 CPU)


texts: Drop and restore PostgresSQL database without losing settings

texts:
This seems like a simple, easy question, but I'm very new to PostgreSQL administration and I'm not sure of the best way to do things. I have a PostgreSQL (9.1) database that somehow got corrupted (there were errors in the Write-Ahead Log) and I'm trying to dump, re-initialize, and restore it to get it back in a stable state.

However, it seems like initdb, the recommended way to create a fresh stable database, actually creates an entire database cluster. In order to run this command, I'd first have to drop my database cluster, but the documentation for the pg_dropcluster command says it removes "all files that belong to a given PostgreSQL cluster; that includes the data directory, the log file, and all configuration files." I don't want to delete the logs and configuration files, just the data.

Since there's only one user-created database in my cluster (i.e. the one I'm trying to restore), I could just connect to the cluster, drop that database, and recreate it from the dump. But then I wouldn't be running initdb, and maybe that would leave my database cluster in some corrupted state.

Is there a way to drop and restore the data contents of a cluster without deleting all of the logs and configuration settings? Alternatively, is it OK to just drop and restore the database to recover it from corruption?


texts: Find changes made in the table in sql server 2012

texts:
I am using a SQL Server.I have more 1000 tables in a database which i want to track for data changes.I have read about CDC(Change Data Capture) and will enable it on all the 1000 tables.It creates multiple cdc tables to keep track of changed data.

But the problem here is on daily basis less than 200 tables are gonna be modified. But still i have to check all the 1000 tables cdc.[capture_instance]_CT tables to find if new data is been added to that particular table.

So is there a way i can find the list of tables which are modified with new data only.

Thanks in advance.


texts: I have configured log shipping between instances, everything was working fine, but recently I have noticed that time on my secondary server differs from time on primary server. I changed time according to the primary server, but now my copy and restore jobs are not executing. They don`t even give exceptions about failure, they are simply not executing. Could you please advise ?


texts: Backup and restore of TDE Databases to different server

texts:
There seems to be a lot of information on backing up and restoring TDE databases, but it's generally all the same information, and there seems to be some contradiction about certain points.  I have some specific questions I hope someone can answer.

Do I have to backup the Database Master Key and restore it?  Or is it enough to recreate it with the same password?
Similarly to #1, do I need to backup and restore the Service Master Key?
If I've backed up the TDE encryption certificate and cert key, can I restore it to a different database that has a different DMK (and SMK) without losing data?
If #3 is yes, then what is the purpose of backing up the DMK and SMK if you can just generate new ones when restoring the Cert?


texts: Function taking a string as table and column name

texts:
I'm trying to write a function that will create a new column in a participation table based on the presence of a id in survey_name table:

CREATE OR REPLACE FUNCTION survey_participated(_sn text)
  returns void AS
$func$
BEGIN
  EXECUTE format($$ALTER TABLE participation ADD COLUMN %I integer DEFAULT 0$$,_sn);
  EXECUTE format($$UPDATE TABLE participation SET %I = 1 WHERE participation.participant_id = %I.participant_id$$,_sn,_sn);
END
$func$ LANGUAGE plpgsql;

I keep getting the error:

[2017-10-19 12:38:09] [42601] ERROR: syntax error at or near "TABLE"
[2017-10-19 12:38:09] Where: PL/pgSQL function survey_participated(text) line 4 at EXECUTE

Can someone point out my mistake?


texts: Database-design for a small filter / search tag function

texts:
I'm building a search function in php for a database with 1k to 4k articles. It's going to be built on a tag system. I've been reading some tutorials, blog posts, q&a etc about it.

Most important for me is performance, I want it to be fast and light. In my special case I'm only going to have a set of few fixed tags. Example: Color:blue, Size:large etc. So if user filters/search "Large Blue" only the articles with these tags should show. There's going to be "Ajaxed" queries, so a light and fast query is important.

I can think of three ways of doing this:

Option 1:
Articles table
 - id
 - name
 - tags (with a data string of tags comma separated)
 - etc

Option 2.
Articles table
- id
- name
- etc

Tags table
- id
- name
- article ids (with a data string of article ids comma separated)

Option 3.
Articles table
- id
- name
- etc

Relational Tag Arts table
- Fkey tags id
- Fkey article id

Tags table
- id
- name
- etc

Which one of these would be fastest / best performance. I know it's not good practice to use comma separated data strings in a table field. But for me, since I just have a few set of tags, option 1 or 2 looks simple and faster than option 3?

Any other suggestions is of course welcome! Thanks


texts: Why hardcoded date filter works much better than dynamic parameter?

texts:
We have a stored procedure where we are getting the list of customer IDs. Within that procedure, we have a filter condition for Date. When we use hard-coded date there the query performance becomes much much better than when we use a "date parameter":

ALTER PROCEDURE [dbo].[Sp_GetCustomers]
(
    @StartDate      DATE,
    @ProductCode    VARCHAR(20) = NULL

)
AS
  BEGIN

    Select  DISTINCT c.CustomerId
    From    Custmer c
            INNER Join [Order] o On c.CustomerId = o.CustomerId

    WHERE   o.ProductCode = @ProductCode AND
            -- Filter# 1: Below hard coded date results better performance
            o.date_inserted >= '2019-06-01'
            -- Filter# 2: Below dynamic date (variable) kills the performance
            o.date_inserted >= @StartDate
END

In above query, see Filter# 1 and Filter#2

If we enable Filter# 1, it works great. But if we enable filter# 2 it kills the performance.

Any idea what could be the reason?


texts: Sql Server database not shown after creating Roles

texts:
I have created a Role in sql Server  2008 and allocate particular DB to that Role.
After Shut down the machine,and I am not able to see the Databases and Able to see only the System Databases.unable to see the Created roles also.

All the MDf files are present in default mdf location. I am unable to attach a mdf files.
How to get back mydatabase .


texts: Cluster design: if I expect to insert data into several tables every week, is it a bad idea to cluster them?

texts:
I have some tables that I join that I think would be perfect for putting in a cluster. But I also expect to insert data into them every week. Not a huge amount of data- something like 5 - 20 records a week. My understanding is that clusters are good for tables that you join and select from, but no so good if you plan to use DML statements on them.
My question is: when Oracle documentation says that clustering isn't efficient to use with DML statements, are they referring to updating and deleting hundreds of records? Or would a small amount of records being inserted also fall under this guideline? My question is essentially, does the scale of DML statements impact the efficiency of clustering? Or is it more of a binary situation. Like, if i was updating one record every day, should I avoid putting my tables in a cluster?


texts: Challenging MySQL query, compare two records to find if they match or not

texts:
I have a large (88K rows) table that holds data in pairs.

For a specific entity, there are 2 records, with UID's and then a load of additional data.

I need to take each pair (pairs can be found by matching another ID number) and compare the additional data.

So, I'd find a pair, compare the data and if either of the data values is the same (or different) flag that pair (and allow me to potentially run an update on one of the rows)

I can script this out n my PHP application if it requires multiple queries, but if this can be done just in SQL, that'd be great.

Any updates to a specific row would just be change a certain value to 0.

Table:

CREATE TABLE `ad_transitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `asset_id` int(11) NOT NULL,
  `left` varchar(50) DEFAULT '0',
  `top` varchar(50) DEFAULT '0',
  `width` varchar(50) DEFAULT '0',
  `height` varchar(50) DEFAULT '0',
  `boundingWidth` varchar(50) DEFAULT NULL,
  `boundingHeight` varchar(50) DEFAULT NULL,
  `fill` varchar(50) DEFAULT '0',
  `shadow` varchar(128) DEFAULT NULL,
  `filter` varchar(512) DEFAULT NULL,
  `overlayFill` varchar(50) DEFAULT NULL,
  `opacity` varchar(50) DEFAULT '1',
  `scaleX` varchar(50) DEFAULT '1',
  `scaleY` varchar(50) DEFAULT '1',
  `angle` varchar(50) DEFAULT '0',
  `wait` int(11) DEFAULT '0',
  `duration` int(11) DEFAULT '0',
  `easing` varchar(50) DEFAULT 'Linear.easeNone',
  `originX` varchar(128) DEFAULT 'center',
  `originY` varchar(128) DEFAULT 'center',
  PRIMARY KEY (`id`)
)

Data:

INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('497', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '0', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('498', '161', '0', '0', '1280', '720', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '5000', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('499', '162', '269.16666666667', '-104', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '0', '0', 'Linear.easeNone', 'left', 'top');
INSERT INTO `database`.`ad_transitions` (`id`, `asset_id`, `left`, `top`, `width`, `height`, `boundingWidth`, `boundingHeight`, `fill`, `shadow`, `filter`, `overlayFill`, `opacity`, `scaleX`, `scaleY`, `angle`, `wait`, `duration`, `easing`, `originX`, `originY`) VALUES ('500', '162', '269.16666666667', '24', '986', '104', NULL, NULL, '#000000', NULL, NULL, NULL, '1', '1', '1', '0', '5000', '1000', 'Bounce.easeOut', 'left', 'top');


texts: mysql - How to remove duplicate rows in parent table and leave one row with the lowest ID, and replace foreign key in child's table with this ID?


texts: Why and how sometimes I get the "absent table error" while there are definitely Sch-M lock?

texts:
I have a synthetic test, which reproduces some error we have on the production environment. Here are the 2 scripts to reproduce it:

1st

DBCC TRACEOFF(-1,3604,1200) WITH NO_INFOMSGS;
SET NOCOUNT ON;
IF @@TRANCOUNT > 0 ROLLBACK
IF object_id('test') IS NOT NULL
    DROP TABLE test
IF object_id('TMP_test') IS NOT NULL
    DROP TABLE TMP_test
IF object_id('test1') IS NOT NULL
    DROP TABLE test1
CREATE TABLE test(Id INT IDENTITY PRIMARY KEY)
GO
INSERT test DEFAULT VALUES
GO 2000
WHILE 1 = 1
BEGIN
    CREATE TABLE TMP_test(Id INT PRIMARY KEY)
    INSERT TMP_test SELECT * FROM test

    WAITFOR DELAY '0:00:00.1'
    BEGIN TRAN

    EXEC sp_rename 'test', 'test1'
    EXEC sp_rename 'TMP_test', 'test'
    EXEC sp_rename 'test1', 'TMP_test'

    DROP TABLE TMP_test
    commit
END

2nd

SET NOCOUNT ON;

DECLARE @c INT
WHILE 1 = 1
BEGIN
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK

    /* and repeat this 10-20 times more*/
    SELECT @c = COUNT(*) FROM Test IF @@ERROR <> 0 BREAK
END

So, the problem is that when I run the 1st script in one session and leave it running, and then run the 2nd in separate session I can get this type of error:

  Msg 208, Level 16, State 1, Line 13 Invalid object name 'Test'.

The question is - WHY i see this error in case when there is a COMMIT in the cycle end of the 1st script and never get one if there is a ROLLBACK?

I have a feeling, that it is somehow connected to the situation, when the script commits, there is still the table with the name test but it is a different object and the 2nd script have to recompile itself. And this is OK. But why it gets the missed table error? AFAIK - when I rename the table inside a transaction - it holds Sch-M lock to the tran end?

Can somebody answer or guide me to the tech papers I can read deeply and understand the reason?


texts: How does name resolution work in Oracle 10g?

texts:
In Oracle 10g (10.2.0), there are 4 types of names resolution:

Easy Connect(10g added this new method)
Local naming
Directory
External naming

It is said that if Easy connect is used by the clients, then it may overload the listener on the server (as client queue increases). How can this happen as it is the server's duty to distribute load from one listener to another? (Please correct me if my concepts are wrong.)

And how exactly does Directory and External naming work.


texts: Connecting to Active Active Cluster Services

texts:
I have a two node active active cluster setup as below:

NODE 1
  - SQLINST1 ( with DTC1 )

NODE 2
  - SQLINST2 ( with DTC2 )

I wish to now have the application connect to SQLINST2 whenever SQLINST1 fails. Is it possible in SQL server 2012.

Once the SQLINST1 comes backup the connections should reconnect back to SQLINST1