Alter a SELECT Query to Limit

Question

I'm working on a 1M+ row table. The software that inserts the data sometimes tries to select all rows. If it tries to do that; It crashes.

I'm not able to modify the software so I'm trying to implement a fix on the Postgresql side.

I want Postgresql to limit SELECT query results that are coming from a special user to 1.

I tried to implement a RULE but haven't been able to do it with success. Any suggestions are welcome.

Br,

score 0 · Answer 1 · answered Dec 30 '16 at 22:50

0

You could rename the table and create a view with the name of the table (selecting from the renamed table).

Then you can include a LIMIT clause in the view definition.

answered Dec 30 '16 at 22:50

In that case, how can I keep the INSERTs coming to the rights table ? By using a trigger ? – Orçun Başlak Dec 30 '16 at 22:52
Yes, because of the `LIMIT` you would need an instead of trigger – Dec 30 '16 at 22:54
this will make some rows invisible to the software... that may cause other bugs. – Jasen Dec 31 '16 at 00:33

score 0 · Answer 2 · edited May 23 '17 at 12:00

There is a chance you need an index. Let me give you a few scenarios

There is a unique constraint on one of the fields but no corresponding index. This way when you insert a record PostgreSQL has to scan the table to see if there is an existing record with the same value in that field.
Your software mimics unique field constraint. Before inserting a new record it scans the table for a record with the same value in one of the fields to check if such a record already exists. Index on the right field would definitely help.
You software wants to compute the next "id" value. In this case it runs SELECT MAX(id) in order to find the next available value. "id" needs an index.

Try to find out if indexing one of the table fields helps. You can also try to trace and analyze queries submitted to the server and see if those queries can benefit from indexing the table. You can enable query logging this way How to log PostgreSQL queries?

Another guess is that your software buffers all records before processing them. Reading 1M records into memory may crash it. Limiting fetchSize (e.g. if your software uses JDBC you could add defaultRowFetchSize connection parameter to the connection string) may help though I realize you may not have means to change the way the existing software fetches data from DB.

"*There is a unique constraint on one of the fields but no corresponding index*" - not possible — , Dec 31 '16 at 08:10
Actually, I have a btree index at the table. The issue is not the search speed of SELECT but rather the time required to return all rows. I need someting like to return 100 rows if a query lets say exceed 100 rows. — Orçun Başlak, Dec 31 '16 at 08:21

Alter a SELECT Query to Limit

2 Answers2