vr7bd Posts - Answer Overflow

vr7bd

•Created by vr7bd on 6/10/2024 in #help

too many clients already, remaining slots reserved for superuser

I have a production deployment where I'm facing this issue. I ran SELECT count(*) FROM pg_stat_activity and noticed that there are indeed 100 connections which is the max. However, I noticed most of them are idle. Temporarily I've increased the max_connections to 500. I have couple of questions to which I'm not getting straightforward answers: 1. Why are there so many idle connections? I understand if there are many active connections. But why idle? 2. If there are that many idle connections, shouldn't the connection pool of postgres.js kick in as it says here (https://github.com/porsager/postgres?tab=readme-ov-file#the-connection-pool) that the previous open connection will be used? So my understanding is that the idle connections will be made active with the new queries. Why isn't that happening? 3. Will I have to manually set the idle_timeout and max_lifetime values to lower and optimal values for my use case to get rid of idle connections? 4. Along with the increasing max_connections, should I increase anything else? Do I need something like pg_bouncer and how would that help?

1 replies

DTDrizzle Team

•Created by vr7bd on 6/4/2024 in #help

Conditional query failing

I have a query like so:

const userId = 'abcdefg'
let selectAll = true; // or false (Condition)
let arr = [];
if (urlCondition1) {
    statusFilter.push(eq(table.status, param1));  
}
if (urlCondition2) {
    statusFilter.push(eq(table.status, param2));  
}
if (urlCondition3) {
  nameFilter.push(or(ilike(table.name, `%${param3}%`)))
}
// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? (or(...statusFilter), or(...nameFilter))
      : inArray(table.userId, arr)
  )
);
// Below produces correct output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    or(...statusFilter),
    or(...nameFilter))
  )
);

const userId = 'abcdefg'
let selectAll = true; // or false (Condition)
let arr = [];
if (urlCondition1) {
    statusFilter.push(eq(table.status, param1));  
}
if (urlCondition2) {
    statusFilter.push(eq(table.status, param2));  
}
if (urlCondition3) {
  nameFilter.push(or(ilike(table.name, `%${param3}%`)))
}
// Below produces wrong output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    selectAll
      ? (or(...statusFilter), or(...nameFilter))
      : inArray(table.userId, arr)
  )
);
// Below produces correct output
await db.select().from(table).where(
  and(
    eq(table.userId, userId),
    or(...statusFilter),
    or(...nameFilter))
  )
);

Why is this happening?

16 replies

DTDrizzle Team

•Created by vr7bd on 5/15/2024 in #help

upsert with excluded using column name

I'm trying to do a an upsert like so

await db.insert(user).values(values).onConflictDoUpdate({
  target: user.id,
  set: {
    // Defined in schema as full_name
    fullName: sql`EXCLUDED.${user.fullName.name}`
}
})

await db.insert(user).values(values).onConflictDoUpdate({
  target: user.id,
  set: {
    // Defined in schema as full_name
    fullName: sql`EXCLUDED.${user.fullName.name}`
}
})

When I try to do this, I'm getting error PostgresError: syntax error at or near "$265". If I do

fullName: sql`EXCLUDED.full_name`

fullName: sql`EXCLUDED.full_name`

, it works. But I would like to do so using the .name property as it will be easier to catch if there's an issue. How do I do it?

4 replies

DTDrizzle Team

•Created by vr7bd on 4/4/2024 in #help

jsdoc for json type column

I'm trying to add a column of type json. I would like to type it. I'm aware of the .$type<..>(). Is there an equivalent way for jsdoc?

1 replies

DTDrizzle Team

•Created by vr7bd on 3/1/2024 in #help

case insensitive orderBy

I want to do an orderBy that is case insensitive. How do I do it?

4 replies

DTDrizzle Team

•Created by vr7bd on 11/25/2023 in #help

Getting types for update query

Similar to $inferInsert, is there a $inferUpdate to get types while performing a db.update()

10 replies

DTDrizzle Team

•Created by vr7bd on 10/31/2023 in #help

How to combine queries from 2 tables?

I feel dumb asking this question... So I have 2 queries which looks like this:

// guranteed to have 1 entry
const result1 = await db.select().from(Table1).where(condition1);
// can have many entries
const result2 = await db.select().from(Table2).where(condition2);
const finalResult = { result1, result2 };

// guranteed to have 1 entry
const result1 = await db.select().from(Table1).where(condition1);
// can have many entries
const result2 = await db.select().from(Table2).where(condition2);
const finalResult = { result1, result2 };

The 2 tables don't have a relationship with each other. I want to optimize this to make it a single query to obtain finalResult - Should I use a db.transaction? I'm not sure if a transaction helps as transactions are useful for write operations . - Is there any other way or am I just overthinking?

1 replies

Gaming

Programming