Error when creating temporary tables

[duncangroenewald]

Describe the issue

I am trying to create a temporary table and populate it from multiple queries but keep getting an error indicating the temp table does not exist.

Vapor version

postgres-nio client

Operating system and version

macoOS15

Swift version

5.10.0-dev

Steps to reproduce

Here is what I am trying to do

try await client.query("CREATE TEMP TABLE ABC (....)")

try await client.query("INSERT INTO ABC (....) SELECT ... FROM XYZ")
try await client.query("INSERT INTO ABC (....) SELECT ... FROM GHI")

try await client.query("SELECT * FROM ABC")

However the temp table is either not created or disappears before the insert statement is executed.

Strangely I had some of these defined as Postgres FUNCTIONS and if I execute a prepared statement and then the code above it seemed to work. But as soon as I no longer execute a prepared statement calling a function I get the error about missing temp table.

Is there some specific way to create temp tables with Postgres-nio to get this to work.

Outcome

I am always getting an error indicating the temp table does not exist when the INSERT statement if executed.

Additional notes

N/A

[duncangroenewald]

OK it seems that the PostgresClient will use the connection pool to execute the queries and may not always uses the same connection - hence why the missing temp tables.

So as a workaround I am limiting the number of connections to 1 on the client.

Is there a more elegant way to request a specific connection from PostgresClient to avoid the possibility of the PostgresClient dropping the 1 connection and creating a new one ?

How do things work if one has a "BEGIN;" query and a "COMMIT;" query ?

How does the PostgresClient know to keep all the things in-between as part of the same transaction ?

Any pointers to documentation that might explain how this works with PostgresClient ?

[adam-fowler]

You can use PostgresClient.withConnection

[duncangroenewald]

@adam-fowler - thanks is there any documentation or examples for this usage ?

Where do you even find out about that ?

[adam-fowler]

@adam-fowler - thanks is there any documentation or examples for this usage ?

Where do you even find out about that ?

I'm not really involved in the development of this package but that is what I've used before. The reference documentation is here https://api.vapor.codes/postgresnio/documentation/postgresnio/postgresclient/withconnection(_:). You can run queries on the connection provided to the closure in a similar way you do with PostgresClient.

[duncangroenewald]

I can't figure out how to process results

the usual call let rows = conn.query(...) generates the error below when using for try await row in rows {

• note that I have no understanding of EventLoopFuture or why the query isn't returning a PostgresRowSequence - the API docs seem to indicate it should.

For-in loop requires 'EventLoopFuture<PostgresQueryResult>' to conform to 'AsyncSequence'

[duncangroenewald]

OK so I finally figured out one needs to call try await conn.query().get() but now I discover that the query only accepts a string not a PostgresQuery - so the same SQL strings cannot be used now !

I hate to say it but this API is barely usable in its current form.

[adam-fowler]

Somehow you seem to be using the old connection APIs. Someone else is going to have to step in here not sure how that is happening.

[duncangroenewald]

@adam-fowler - that makes sense - I can see the correct API listed but it doesn't seem to be available.

[duncangroenewald]

Hmm it seems I need to pass in the logger: parameter to get the newer API

[adam-fowler]

Hmm it seems I need to pass in the logger: parameter to get the newer API

😬That's not a great way to differentiate between APIs.