Postgres Pagination page() fetch one duplicate field if created_at value are the same
Nobody has claimed this yet.
- Dominant language
- JavaScript
- Stars
- 7.3k
- Forks
- 637
- PR merge metrics
- No merged PRs in 30d
Description
Objection js Version: "3.1.3"
It seems this error happen when there are more than 50 rows of field created_at with the same value when I use page(). And it only fetched one duplicate field between page 0 and page 1 with page_size 10, and using created_at('created_at', 'desc | asc'). I'm not sure how to host this reproducible code but here's the code
/**
*
* install:
* npm install objection knex
* npm install pg --save
*
* run:
* node reproduction-template
*/
let Model;
try {
Model = require('./').Model;
} catch (err) {
Model = require('objection').Model;
}
const Knex = require('knex');
async function main() {
await createSchema();
///////////////////////////////////////////////////////////////
// Your reproduction
///////////////////////////////////////////////////////////////
const structure = () => {
return {
name: 'test',
};
};
const rise = generateData(structure, 50);
await RiseModel.query().insert(rise)
for(let i =0; i<3; i++){
const data = await getData(i);
console.log(data.results.map(e=>e.id));
}
}
async function getData(page){
return await RiseModel.query().orderBy("created_at", "desc").page(page, 10);
}
function generateData(
structure,
amount
){
const data = [];
for (let i = 0; i < amount; i++) {
data.push(structure());
}
return data;
}
///////////////////////////////////////////////////////////////
// Database
///////////////////////////////////////////////////////////////
const knex = Knex({
client: "pg",
connection: {
host: "127.0.0.1",
database: "Rise",
user: // YOUR USERNAME,
password: // YOUR PASSWORD,
},
useNullAsDefault: true,
debug: false,
});
Model.knex(knex);
///////////////////////////////////////////////////////////////
// Models
///////////////////////////////////////////////////////////////
class RiseModel extends Model {
static get tableName() {
return "rise";
}
}
///////////////////////////////////////////////////////////////
// Schema
///////////////////////////////////////////////////////////////
async function createSchema() {
await knex.schema
.dropTableIfExists('rise');
await knex.schema
.createTable('rise', table => {
table.increments('id').primary();
table.string('name');
table.timestamps(true, true);
})
}
main()
.then(async () => {
console.log('success');
await knex.schema.dropTable('rise');
return knex.destroy();
})
.catch(err => {
console.error(err);
return knex.destroy();
});
Here's the result of id fetched:
[
2, 3, 4, 5, 6,
7, 8, 9, 10, 1
]
[
12, 13, 14, 15, 16,
17, 18, 19, 20, 1
]
[
21, 22, 23, 24, 25,
26, 27, 28, 29, 30
]
success
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start at the page() pagination implementation and run the supplied PostgreSQL reproduction with equal created_at values. Trace how consecutive pages are ordered, then add a regression test showing that page 0, page 1, and later pages contain no duplicate rows when timestamps tie.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- javascript, node.js, postgresql
- Domain
- backend, database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100