Dynamic table queries are failing in v3

[iby]

Trying to migrate to from v2 to v3 and all of my dynamic table queries are failing. From the logs the difference seems to be that dynamic attribute is missing, but I guess the API could have changed and it's no longer needed?

await pg`SELECT * FROM ${pg('channel')};`

// v2.0.0-beta.11
// 5 SELECT * FROM "channel"; [ dynamic: true ]
// Works as expected…

// v3.2.4
// 28 SELECT * FROM "channel"; [] []
// PostgresError: relation "channel" does not exist

What's interesting, if i pass along the table schema, then it also works…

await pg`SELECT * FROM ${pg('public.channel')};`
// 28 SELECT * FROM "public"."channel"; [] []
// Works as expected…

Any ideas what's wrong with v3 and how to make it work without the public schema?

[iby]

Actually, even with public schema prepended it fails at a later stage with the same error… 🤔

[porsager]

Hmm... There shouldn't be any issue.. I just added tests specifically for that and didn't experience any issues. Could you maybe make a reproducible example?

[iby]

Hey Rasmus, hope you've been well! Thanks a bunch for the quick response. Alrighty, I narrowed this down to 2.5 issues. Some context how I ended up here:

1. I have about 1k tests running in parallel using Mocha – it runs files (suites) in parallel, and tests in each suite (file) serially.
2. For each suite (file) I create a new database to avoid clashes since suites go in parallel. To set up the schema I do sql.file(…) – that file is generated by dbmate.
3. Then for each following test in a suite (file) I simply truncate all tables since they are executed serially. This saves A LOT of time vs. creating a separate database for each separate test.

Here's a narrowed down example and 3 things that seem to be wrong with it:

1. Doing sql(tables) over array of identifiers no longer works in v3 and fails with Error: Could not infer helper mode @ types.js:70:13. It works if I do sql(tables[0]) but then couldn't figure out how I can make it work with unknown number of array elements? Am I missing something?

2. The original error PostgresError: relation "test1" does not exist seems to be coming from SELECT pg_catalog.set_config('search_path', '', false); line in the test.sql file when doing sql.file(test.sql). If I comment out that line, everything seems to work. This could happen before (see the next point) but I didn't observe it due to high number of short lived connections, but this no longer works in v3. The error also happens in the first test right away in v3, not in every 3rd like in v2.0.0-beta.11. I looked around and found a few search_path-related issues and tried adding ?search_path=public to the URL as per #367, but no luck. Should I handle this somehow separately in v3?

3. In v2.0.0-beta.11, If I remove that { max: 100}, every third test also fails with PostgresError: relation "test1" does not exist error, but I don't see that in my actual code because of { max: 100, idle_timeout: 0.1 } – we talked about it #208. But the interesting thing that I noticed is that if I comment out that SELECT pg_catalog.set_config('search_path', '', false); in test.sql I don't need to set { max: 100, idle_timeout: 0.1 } at all. How come? 🤔

let sql: Sql<any> | undefined;

const connection = async (): Promise<Sql<any>> => {
    if (sql) {
        const tables = ['test1', 'test2'];
        await sql`TRUNCATE ${sql(tables)} CASCADE;`;
    } else {
        sql = postgres('postgres://postgres@localhost/lookback_testing', { max: 100 });
        await sql.file(path.join(__dirname, 'test.sql'));
    }
    return sql;
};

it('t1', async () => {
    const sql = await connection();
    await sql`select * from ${sql('test1')}`;
});

it('t2', async () => {
    const sql = await connection();
    await sql`select * from ${sql('test1')}`;
});

it('t3', async () => {
    const sql = await connection();
    await sql`select * from ${sql('test1')}`;
});

SET statement_timeout = 0;
SET lock_timeout = 0;
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

drop table if exists public.test1;
create table public.test1(a int);

drop table if exists public.test2;
create table public.test2(a int);

[porsager]

Hi @iby - sorry for the late reply, been on vacation ;)

Do you have any updates on this, or can I answer your latest post ? :)

[iby]

No worries @porsager, hope it was great? 😃

I don't actually, no. Just pinned the latest that's working, but was hoping to update. Any tips and hints would be much appreciated! 🙏

[iby]

@porsager a very polite ping to check if you have any thoughts on this? 🙏

[iby]

@porsager I'm gonna look into this this week, it sounded like you know what's going on, I just wanted to check if you can share any insights or point in the right direction? Thanks for any tips! 👍

[porsager]

Well I had the best intentions of taking a look, but the time hasn't come up yet :-( .. Hopefully I'll find a hole soon, but if you figure anything out - let me know.

[clouedoc]

Hi there, I'm encountering random "this relation does not exist" errors randomly.

The queries are not really dynamic, so I'm uncertain if it fits here.

And maybe it's a general SQL problem, but I'm not experienced enough to differentiate... So please let me know your thoughts 🙏

PostgresError: relation "recurrent_order" does not exist.

    await sql`UPDATE orders SET recurrent_order_id = NULL WHERE uri = 'spotify:album:XXX'`;
    await sql`DELETE FROM recurrent_order WHERE uri = 'spotify:album:XXX'`;

It reproduces with these versions: v3.2.4 and v3.3.2 (latest)

It also seems to come into bursts. Like it happens for a while, then stops, then comes back.

If it helps, here are some information on my stack:

• TimescaleDB
• pgbouncer
• tons of queries
• inexperienced dbadmin (me 🙂)

EDIT: I think that it's an issue with pgBouncer, I apologize for cluttering your issue! You can hide this if you like.

[ayZagen]

@porsager I ve been playing around with the library (thanks for the great effort btw) to see its capabilities.

Functionally, followings should work and behave same. But they are not.

const orderClause = sql`ORDER BY ${ sql( [ 'col' ] ) } DESC`;
await sql`SELECT * FROM "test" ${ orderClause }`;

// Throws Error: Could not infer helper mode

However, when we inline the sub clause it works ok.

await sql`SELECT * FROM "test" ORDER BY ${ sql( [ 'col' ] ) } DESC`;

version: 3.3.3

[jgnieuwhof]

I'm experiencing issues with dynamic table management as well, on 3.3.5. The CREATE TABLE ... statements will execute, promises resolve, and subsequent calls will fail claiming that the tables do not exist.

The tricky thing is that the issue is only showing intermittently, on upper (hosted) environments. I haven't been able to reproduce the issue locally, and it hasn't shown once on CI.

I know this isn't super helpful, but there does seem to be something funky here with dynamic tables.

We circumvented the issue by using partitions on stable tables, rather than dynamic tables. I know partition management involves similar statements as dynamic tables, but we haven't had an issue with the library creating, attaching, and truncating partitions (both by executing the statements directly, or wrapping the statements in some postgres functions and invoking those).

[porsager]

@jgnieuwhof I don't think what you're experiencing has anything to do with the dynamic query helpers. I think you might have queries running on different connections in parallel, so that some of them are getting handled before your table queries are handled. You're very welcome to create an issue with this, and I'll try to help you spot where the issue is 😉

[porsager]

Related to this issue, it was fixed in https://github.com/porsager/postgres/commit/58aac2052a43f062d1d6f7c1bbe10c37d343de29

[jgnieuwhof]

Thanks for the quick response @porsager!

It's the same connection, all promises awaited. I'll try to create a minimal reproduction and raise a new issue :+1: