Allow partial pipelining within a transaction

[astralarya]

Allow pipelining the some requests in a transaction without requiring that all the requests are pipelined. This would allow selecting to get the id of a row, then issuing multiple statements based on the id of the row in a pipelined fashion.

For example:

await sql.begin(async sql => {
  const [movie] = await sql`
    SELECT * FROM Movie
    WHERE name = 'Star Wars'
  `;
  return [
    sql`
      INSERT INTO MovieActor
      -- some statement using ${movie.id}
    `,
    sql`
      INSERT INTO MovieReleases
      -- some statement using ${movie.id}
    `,
     // etc
  ]
})

[astralarya]

Alternatively, maybe there could be a specific sql.pipeline() function, which would accept an array of sql statements to pipeline. This would allow opening a transaction and running some non-pipelined statements before and after a block of pipelined statements.

[porsager]

I think your example is great, and ought to work :) Also for doing it in the middle of other queries this should be supported:

await sql.begin(async sql => {
  const [movie] = await sql`
    SELECT * FROM Movie
    WHERE name = 'Star Wars'
  `
  
  await Promise.all([
    sql`
      INSERT INTO MovieActor
      -- some statement using ${movie.id}
    `,
    sql`
      INSERT INTO MovieReleases
      -- some statement using ${movie.id}
    `,
     // etc
  ])
  
  await sql` ... something else `
})

I might have some time to toy with this soon, but if you want to take a stab at it, feel free :)

[porsager]

Until then, are you sure your use case can't work with CTE's ?

[astralarya]

I think Promise.all doesn't guarantee any sequencing, while pipelining with libpq does. This doesn't matter in many cases, but it can occasionally. For example, you could do an INSERT, then another INSERT that uses some aggregate that includes the first INSERT, etc.

CTEs can work in most situations, but it would be nice to have the option to pipeline statements.

[astralarya]

Another place where partial pipelining would be great is when using temporary tables. For example:

await sql.pipeline(sql => [
  sql`
    CREATE TEMP TABLE ActorsInYear
    ON COMMIT DROP AS
    SELECT Actor.*
    FROM Actor
    JOIN MovieActor USING (ActorId)
    JOIN Movie USING (MovieId)
    WHERE Movie.Year = ${year}
  `,
  sql`
    CREATE INDEX ON ActorsInYear(ActorId)
  `,
  sql`
    ANALYZE ActorsInYear
  `
]);
// Do something with ActorsInYear