Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [limits]

86 questions
26
votes
4 answers

How to limit maximum number of rows in a table to just 1

I have a configuration table in my SQL Server database and this table should only ever have one row. To help future developers understand this I'd like to prevent more than one row of data being added. I have opted to use a trigger for this, as…
Dib
  • 447
  • 1
  • 6
  • 12
19
votes
2 answers

MongoDB terminates when it runs out of memory

I have the following configuration: a host machine that runs three docker containers: MongoDB Redis A program using the previous two containers to store data Both Redis and MongoDB are used to store huge amounts of data. I know Redis needs to…
Simone Bronzini
  • 293
  • 1
  • 2
  • 6
19
votes
5 answers

SQL Server Error 8632 due to over 100,000 entries in WHERE clause

My problem (or at least the error message) is very similar to query processor ran out of internal resources - extremely long sql query. My customer is working with an SQL select-query, containing a where-clause with exactly 100,000 entries. The…
Dominique
  • 497
  • 1
  • 3
  • 12
18
votes
1 answer

"Limit 1000,25" vs "limit 25 Offset 1000"

Recently I've found out that MySQL has a offset feature. I've been trying to find documentation about the results of offset, or the difference in between offset and the limit variant, but I can't seem to find what I'm looking for. Lets say I have…
Martijn
  • 283
  • 1
  • 2
  • 8
13
votes
1 answer

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for index "foo_idx" HINT: Values larger than 1/3 of…
Erwin Brandstetter
  • 162,308
  • 22
  • 403
  • 549
12
votes
1 answer

ANSI/ISO plans for LIMIT standardization?

Are there currently plans to standardize one best way of limiting the number of results returned by a query? The stack overflow question at Is there an ANSI SQL alternative to the MYSQL LIMIT keyword? lists the various ways to handle this behavior…
chucksmash
  • 535
  • 1
  • 6
  • 9
10
votes
3 answers

How to check if I am hitting the Express Edition size limit?

I am confused. AFAIK SQL Server 2005 Express has a limit of 4GB database data size. However I have the following results from sp_spaceused: How can I check if my DB is hitting the size limit? Is unallocated space the space left untill I hit the…
g00fy
  • 211
  • 1
  • 2
  • 4
9
votes
3 answers

COUNT(*) gives more than 1 with LIMIT 1?

I'm trying to count old records. Why does Postgres give a result of 1160, even though I set some limit, LIMIT 1 in this case? SELECT COUNT(*) FROM data WHERE datetime < '2015-09-23 00:00:00' LIMIT 1; count -------- 1160 (1 row) I expected a…
happy_marmoset
  • 459
  • 2
  • 6
  • 10
7
votes
2 answers

Postgres slow query with order by, index and limit

I am trying to improve the performance of a postgres(9.6) query. Here is my schema and table contains about 60 million rows. Column | Type |…
Zeeshan
  • 71
  • 1
  • 6
7
votes
1 answer

UPDATE Only one row (using update and join)

I'm trying to get a fast, simple sql query to update only one row at a time while using join too. I have tried LIMIT, but to no success. Query: UPDATE table1 JOIN table2 ON table2.col=table1.col SET table1.row1='a value' WHERE table1.row2 LIKE…
Abdelhafidh Belalia
  • 71
  • 1
  • 1
  • 3
5
votes
1 answer

Limits of applock in MS SQL Server

Microsoft SQL Server offers an arbitrary application-defined locking mechanism through the sp-getapplock command and related commands. The key, or resource_name, is specified as a string, a varchar 255. But the documentation also mentions that the…
Basil Bourque
  • 9,896
  • 17
  • 53
  • 90
5
votes
2 answers

How to deal with large offsets in select?

Table jtest with 200k rows, each row contains jsonb { id: "", key: } ( is integer 1-200k incremented per each row). There is also btree index on data->'key'. create extension if not exists pgcrypto; create table jtest (data…
user606521
  • 1,287
  • 5
  • 19
  • 28
4
votes
2 answers

MySQL LIMIT within JOIN

I have an 1:m association. symbols table has many rows in company_key_statistics table. company_key_statistics has column createdAt which is timestamp indicating when row has been created. I need to join latest symbols with company_key_statistics,…
Aren Hovsepyan
  • 181
  • 1
  • 2
  • 6
4
votes
1 answer

PostgreSQL Size Quota on Table or Schema

How do one limit the size of a PostgreSQL table? (or schema) Limit in either bytes or rows would be OK. I'm starting to think that there's no easy and obvious solution. I have a lot of identical tables in multiple schemas (my take on multi-tenancy)…
kirilian
  • 41
  • 2
4
votes
2 answers

PostgreSQL How to optimize a query with ORDER BY and LIMIT 1?

I have the following PostgreSQL schema: CREATE TABLE User ( ID INTEGER PRIMARY KEY ); CREATE TABLE BOX ( ID INTEGER PRIMARY KEY ); CREATE SEQUENCE seq_item; CREATE TABLE Item ( ID INTEGER PRIMARY KEY DEFAULT nextval('seq_item'), …
Ivaylo Toskov
  • 141
  • 1
  • 5
1
2 3 4 5 6