Questions tagged [rails]
Rails is a cross-platform model-view-controller-centric application framework for Ruby. Use this tag for database-related Rails questions.
46 questions
0
votes
1
answer
76
views
Can I optimize UPDATE statements using EXPLAIN?
I have a few UPDATE calls on my production system that are running slow and I've been instructed to try and optimize them. I'm reading into how to understand EXPLAIN with (ANALYZE, BUFFERS) as well to ...
- 103
0
votes
1
answer
46
views
Recommended way to build database to contain users and chatgpt data
I am looking to create a Rails application to interface between users and Azure ChatGPT. I will include my ERD diagram showing a couple of options in building the database tables and table ...
- 101
0
votes
0
answers
29
views
Managed database or docker image with data-volume?
I've mostly used managed database (AWS RDS) for production. I was fiddling with docker and was wondering if it's a good idea to have containerised postgres database with data-volume?
I feel it may not ...
- 101
0
votes
0
answers
225
views
How does JDBC over LDAP connection work?
The JDBC connection I should use is defined in Oracle SQL Developer with the following syntax:
jdbc:oracle:thin:@ldap://intranet.oid-01.dama.ch:3063/EDWHPD,cn=OracleContext,dc=emea,dc=dama,dc=ch
But ...
- 133
0
votes
0
answers
89
views
How to connect to Oracle through LDAP with Ruby on Rails 5.4.2?
My application is running on Rails 5.4.2/Ruby 2.7 and relies on an Oracle 19c.
I used to query an Oracle database through a classical OCI connection configuration:
config/database.yml
development:
...
- 133
1
vote
0
answers
40
views
Migrations spreading to the wrong database (local environment)
I am working on one Flask project and one Ruby on Rails project, both using Postgres. When I migrate in Rails, the changes are implemented both in this database and in the database for the Flask ...
- 11
1
vote
0
answers
40
views
Is creating table entry from another table entry and copying its value good practices?
Lets say I have inventory_receives table which contain many inventory_receive_entry and also I have inventory table which contain one inventory_receive_entry
Then in my application inventory_receives ...
- 13
1
vote
0
answers
260
views
Extremely slow updates to small Oracle table from rails app (oracle r12 and r19)
I'm not sure if this is a rails issue, or a database issue, but as this seems to be a schema specific issue, I'm looking at it from the database side first. (Other customers with the same setup on ...
- 135
1
vote
1
answer
3k
views
PostgreSQL update table then insert on another table
I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update:
UPDATE ...
- 125
1
vote
1
answer
465
views
Why is the schema/structure dump so slow? [closed]
I'm using Rails 6 with activerecord-oracle_enhanced-adapter adapter for Oracle 11 XE database.
At the beginning of the project I was working with Postgres, but due to customer requirements I had to ...
- 11
0
votes
1
answer
49
views
Multiple Joins with Sums
Here is my basic schema
wbs_item
+----+-----------+-----------+
| id | name | parent_id |
+----+-----------+-----------+
| 1 | Materials | |
+----+-----------+-----------+
| 2 | ...
- 103
0
votes
0
answers
731
views
Postgres INSERT slows down over time until server is restarted
I have a Rails app backed by an Azure-managed Postgres database and I use NewRelic for monitoring. What I've noticed is that the INSERT performance degrades almost linearly over time - what started ...
- 1
-4
votes
1
answer
3k
views
Postgres is Throwing a False Foreign Key Constraint Violation? [closed]
I am trying to delete a LocalTax record from Postgres. Currently, I don't think there are any Employee records that reference the LocalTax in question (8).
=> SELECT COUNT(*) FROM employees WHERE ...
- 7
1
vote
2
answers
568
views
MySQL failover: how application can be switched to a new master in master-slave configuration?
In my case I have two MySQL servers. One is master and second one is slave.
When slave is not available, my application can read and write from/to master.
When master is not available, assume there ...
- 113
1
vote
1
answer
2k
views
PostgreSQL query suddenly very slow after new column addition
I have a query that's performing terribly after adding a new column. Here's the slow version:
SELECT COUNT(*)
FROM activities
INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."...
- 235
0
votes
1
answer
233
views
Postgresql Query getting slow execution time
I did the explain analyze for this query, it was giving 30ms, but if the data is more I will get an execution expired; Using PostgreSQL 10
For normal execution: https://explain.depesz.com/s/gSPP
For ...
- 161
1
vote
1
answer
115
views
SQL row data mismatch for different users
I am using MySQL (version 5.5.43) as my database.
I have a RoR micro-service that does an update column to a model Active Record class:
model.update_column(status: 0)
The next line is an api call ...
- 11
2
votes
2
answers
5k
views
Set search path for Postgres in a Rails app
I have a project in heroku. I have the URI that enables me to connect with the database in my external rails app. How do I change or set the search path in ruby code?
- 23
0
votes
1
answer
88
views
Flashcards and templates
I'm having trouble with an appropriate DB design for flashcards that display their data with one of numerous templates.
A user has many flashcards. They pick the card template during creation. There ...
- 173
1
vote
1
answer
91
views
How can I join this two queries,in one? [closed]
I have this 2 queries, and I would like to join them
First query
SELECT proveedors.nombre as proveedor, COUNT(productos.id) as distintos
FROM proveedors
INNER JOIN productos ON proveedors.id = ...
1
vote
1
answer
2k
views
How can I use value of the id column as default value of another non-nullable column when using postgresql-rails?
Here's one example where such a requirement comes up:
Suppose we have a table 'sample' in our database which has a column 'col1' and an auto-incremental 'id' column. We want the default value of '...
- 121
0
votes
1
answer
321
views
Understanding Simple MongoDB Replication Works
I've been using MongoDB (3.2.9) replication for years and barely know the detail how it works. My Stack is Rails 4.2.5 with Mongoid 5.1.3. I've 3 database servers, let's call them A (Primary), B (...
- 3
2
votes
3
answers
2k
views
How to store short stories for access to individual sentences?
I am designing a database for the first time ever (using PostgreSQL), and am wondering about the most efficient/logical way of storing a body of text (aka, a story).
The conflict stems from the fact ...
- 173
0
votes
1
answer
59
views
re-write active record query to PostgreSQL
I want to rewrite the active record query to Actual PostgreSQL query
Camera.joins("left JOIN users on cameras.owner_id = users.id")
.joins("left JOIN vendor_models vm on cameras....
- 291
4
votes
1
answer
1k
views
What can I do to monitor "out of shared memory" issues?
I'm new to database administration and I would really like some advice on what tools I could use or what I could do to monitor "out of shared memory" issues.
I've been seeing these "out of shared ...
- 51
3
votes
2
answers
2k
views
Efficient schema to handle time interval queries on a huge table?
We have huge table (+10 millions rows) where we aggregate the values by a using a time interval search on a Datetime column. And right now, to build single page on our app, we are querying this table ...
- 135
0
votes
2
answers
7k
views
MySQL database has way too many connections
I have a MySQL version 5.7.12 database that has 4 applications running against them. Two of these applications are single webservers running Rails apps. The other two applications are also Rails ...
1
vote
1
answer
1k
views
Cannot start both neo4j development and neo4j test servers
I have a Rails application backed by a Neo4j database. I've installed both a development server and a test server, as explained here and here. Each server works fine when started up alone, but if I ...
- 81
9
votes
2
answers
20k
views
MySQL create index with LOCK=NONE still locks a table
I have the following MySQL RoR Migrations:
class ReindexRpushNotification < ActiveRecord::Migration
def up
execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
...
- 341
2
votes
1
answer
3k
views
In Rails: How is existing data affected by data type change?
I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in ...
- 41
1
vote
1
answer
2k
views
Postgres: querying jsonb array with an array
My products table has a jsonb column called 'specs'.
This specs hash will often have an array field, let's call it 'spec_options'.
When querying this table, I need to find any product that has ...
- 111
0
votes
1
answer
162
views
Source of error on psql data load
Upon importing a file derived from rails establishment, psql is hitting the error of foreign key constraints:
ERROR: insert or update on table "documents" violates foreign key constraint "...
- 289
-1
votes
2
answers
366
views
Database Design - I have a question regarding joining tables
Forgive me for my dumb question but I am new to Ruby on Rails and data design. We all have to start somewhere right? Well my question is that I want to build a database for a Truck driving company. It ...
0
votes
3
answers
239
views
Constructing 3-step sql query
I have:
a table of Characters with a character_id column;
a join table of Chats with character_id and conversation_id columns;
and a table of Conversations with a conversation_id column.
I am ...
- 103
4
votes
2
answers
11k
views
MySQL: How to decrease sleep process's time out?
When I run 'show processlist;', then I get so many sleep processes.
I set wait_timeout and interactive_timeout to 60 in my.cnf. But, sleep process is not died when time 60 on processlist. I found ...
- 41
4
votes
1
answer
333
views
Inventory management in Rails
I'm creating a inventory management app in rails. I have an User Table and a Recipe table, there is also some tables with types of material (pens, sharpies, paper, and so on). Because there is a lot ...
2
votes
1
answer
2k
views
PostgreSQL: restore a record from a backup
We have a Rails app backed by a PostgreSQL DB and hosted on Heroku.
We recently had a bug that deleted some (very few before we caught it) records.
Excuse my ignorance but is there a straightforward ...
- 777
0
votes
1
answer
160
views
What postgres sync solution should I use to duplicate production, but read it from another application?
I'm a little out of my league with my current problem, but I'm looking for a solution where the following conditions are met:
My production database reads and writes as it currently does
My ...
5
votes
1
answer
28k
views
How to convert time without time zone to timestamp without time zone in PostgreSQL?
I received this error when trying to alter a column of type time to type timestamp:
PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone
It's not ...
- 167
2
votes
1
answer
433
views
Copying database to different hard disk
Total noob at database administration here, excuse my ignorance.
I have a server running a Rails application that's using PostgreSQL. The database is saved on drive /z/, which seems to have filled up....
- 121
6
votes
1
answer
8k
views
PostgreSQL 9.3 Amazon RDS gives error while using UUID-OSSP extension for generating uuid
I have created DB instance on RDS and used below function. It gave error while with the same PostgreSQL version on a local development machine it works fine.
create or replace function uuid() returns ...
- 161
1
vote
0
answers
651
views
On Heroku in production, how can I copy database A to database B on command?
From my local terminal, I can use the following commands to do what I need to do:
heroku pgbackups:capture --expire
heroku pgbackups:restore charcoal --confirm max-kiosk
However, I want to do this ...
- 111
0
votes
1
answer
329
views
Duplicate Relationships in Rails/MySQL Healthcare Application
Overview of the Application
I am building a Ruby on Rails application to organize medications, using MySQL. The goal of this application is to provide dosage specific treatment recommendations, and ...
- 101
0
votes
1
answer
2k
views
While importing 150 GB file on Amazon RDS, why connection is failing?
I am having Postgres database dump file of 150 GB on Amazon EC2 instance. While dumping the data on RDS from EC2 server I am getting error.
The output of the command is as given below. It's giving ...
- 109
1
vote
1
answer
2k
views
Can’t connect to local PostgresSQL server [closed]
I have the Postres app installed an running on my Mac. And it has worked beautifully. I don’t know what changed, but now I cannot connect to it from Rails, PG Commander, PG Admin or the command psql -...
- 111
1
vote
1
answer
109
views
Why add indexes to these columns in the Database?
I am trying to get my head around this code. It's from the Rails Tutorial Book and is part of the process of making a twitter like application.
class CreateRelationships < ActiveRecord::...
- 11