https://kotlinlang.org logo
Join Slack
Powered by
Unrelated question. I am trying to get `RETURNING`...
# squarelibraries
j
Unrelated question. I am trying to get 
RETURNING
working for an 
INSERT
query using postgresql, and seems that only using this 
WITH
style it is possible. Still I'm getting errors:
Copy code
CREATE TABLE IF NOT EXISTS userEntity (
    user_id SERIAL PRIMARY KEY,
    slack_user_id VARCHAR NOT NULL,
    slack_channel_id VARCHAR
);

insert:
WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id, slack_channel_id)
  VALUES (:userId, :channelId)
  RETURNING user_id AS insert_id
) SELECT insert_id FROM inserted_ids;
Pasting the stacktrace on the thread to avoid bloating the chat
Copy code
ERROR: INSERT INTO userEntity(slack_user_id, slack_channel_id)
  VALUES (:userId, :channelId)
  RETURNING user_id AS insert_id
 parent=WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id, slack_channel_id)
  VALUES (:userId, :channelId)
  RETURNING user_id AS insert_id
)
java.lang.Throwable: INSERT INTO userEntity(slack_user_id, slack_channel_id)
  VALUES (:userId, :channelId)
  RETURNING user_id AS insert_id
 parent=WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id, slack_channel_id)
  VALUES (:userId, :channelId)
  RETURNING user_id AS insert_id
)
	at sqldelight.com.intellij.openapi.diagnostic.Logger.error(Logger.java:146)
	at sqldelight.com.intellij.psi.impl.PsiElementBase.notNullChild(PsiElementBase.java:284)
	at sqldelight.com.alecstrong.sql.psi.core.psi.impl.SqlWithClauseAuxiliaryStmtImpl.getCompoundSelectStmt(SqlWithClauseAuxiliaryStmtImpl.java:32)
	at com.squareup.sqldelight.core.lang.util.SelectStmtUtilKt.referencedTables(SelectStmtUtil.kt:34)
	at com.squareup.sqldelight.core.lang.util.SelectStmtUtilKt.tablesObserved(SelectStmtUtil.kt:17)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery$tablesObserved$2.invoke(NamedQuery.kt:120)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery$tablesObserved$2.invoke(NamedQuery.kt:46)
	at kotlin.SynchronizedLazyImpl.getValue(LazyJVM.kt:74)
	at com.squareup.sqldelight.core.compiler.model.NamedQuery.getTablesObserved$sqldelight_compiler(NamedQuery.kt)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator$queriesUpdated$1.invoke(MutatorQueryGenerator.kt:70)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator$queriesUpdated$1.invoke(MutatorQueryGenerator.kt:11)
	at com.squareup.sqldelight.core.lang.SqlDelightQueriesFile.iterateSqlFiles(SqlDelightQueriesFile.kt:130)
	at com.squareup.sqldelight.core.compiler.MutatorQueryGenerator.queriesUpdated$sqldelight_compiler(MutatorQueryGenerator.kt:22)
	at com.squareup.sqldelight.core.compiler.ExecuteQueryGenerator.notifyQueries(ExecuteQueryGenerator.kt:42)
	at com.squareup.sqldelight.core.compiler.ExecuteQueryGenerator.function(ExecuteQueryGenerator.kt:65)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$addExecute$1.invoke(QueriesTypeGenerator.kt:140)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$addExecute$1.invoke(QueriesTypeGenerator.kt:22)
	at com.squareup.sqldelight.core.compiler.SqlDelightCompilerKt.tryWithElement(SqlDelightCompiler.kt:220)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator.addExecute(QueriesTypeGenerator.kt:131)
	at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator.generateType(QueriesTypeGenerator.kt:119)
	at com.squareup.sqldelight.core.compiler.SqlDelightCompiler.writeImplementations(SqlDelightCompiler.kt:85)
	at com.squareup.sqldelight.core.SqlDelightEnvironment.generateSqlDelightFiles(SqlDelightEnvironment.kt:187)
	at com.squareup.sqldelight.gradle.SqlDelightTask$GenerateInterfaces.execute(SqlDelightTask.kt:97)
	at org.gradle.workers.internal.DefaultWorkerServer.execute(DefaultWorkerServer.java:63)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:49)
	at org.gradle.workers.internal.AbstractClassLoaderWorker$1.create(AbstractClassLoaderWorker.java:43)
	at org.gradle.internal.classloader.ClassLoaderUtils.executeInClassloader(ClassLoaderUtils.java:97)
	at org.gradle.workers.internal.AbstractClassLoaderWorker.executeInClassLoader(AbstractClassLoaderWorker.java:43)
	at org.gradle.workers.internal.IsolatedClassloaderWorker.run(IsolatedClassloaderWorker.java:49)
	at org.gradle.workers.internal.IsolatedClassloaderWorker.run(IsolatedClassloaderWorker.java:30)
	at org.gradle.workers.internal.IsolatedClassloaderWorkerFactory$1.lambda$execute$0(IsolatedClassloaderWorkerFactory.java:57)
	at org.gradle.workers.internal.AbstractWorker$1.call(AbstractWorker.java:44)
	at org.gradle.workers.internal.AbstractWorker$1.call(AbstractWorker.java:41)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$CallableBuildOperationWorker.execute(DefaultBuildOperationRunner.java:200)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$CallableBuildOperationWorker.execute(DefaultBuildOperationRunner.java:195)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$3.execute(DefaultBuildOperationRunner.java:75)
	at org.gradle.internal.operations.DefaultBuildOperationRunner$3.execute(DefaultBuildOperationRunner.java:68)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:153)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.execute(DefaultBuildOperationRunner.java:68)
	at org.gradle.internal.operations.DefaultBuildOperationRunner.call(DefaultBuildOperationRunner.java:62)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.lambda$call$2(DefaultBuildOperationExecutor.java:76)
	at org.gradle.internal.operations.UnmanagedBuildOperationWrapper.callWithUnmanagedSupport(UnmanagedBuildOperationWrapper.java:54)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.call(DefaultBuildOperationExecutor.java:76)
	at org.gradle.workers.internal.AbstractWorker.executeWrappedInBuildOperation(AbstractWorker.java:41)
	at org.gradle.workers.internal.IsolatedClassloaderWorkerFactory$1.execute(IsolatedClassloaderWorkerFactory.java:49)
	at org.gradle.workers.internal.DefaultWorkerExecutor.lambda$submitWork$2(DefaultWorkerExecutor.java:206)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runExecution(DefaultConditionalExecutionQueue.java:214)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.runBatch(DefaultConditionalExecutionQueue.java:164)
	at org.gradle.internal.work.DefaultConditionalExecutionQueue$ExecutionRunner.run(DefaultConditionalExecutionQueue.java:131)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:64)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:48)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:56)
	at java.base/java.lang.Thread.run(Thread.java:829)

FAILURE: Build failed with an exception.

* What went wrong:
Execution failed for task ':generateMainSubscriptionsDbInterface'.
> A failure occurred while executing com.squareup.sqldelight.gradle.SqlDelightTask$GenerateInterfaces
   > Failed to compile PostgreSqlInsertStmtImpl(INSERT_STMT): [] :
     INSERT INTO repositoryEntity(owner, repository)
     VALUES (?, ?)

* Try:
Run with --stacktrace option to get the stack trace. Run with --info or --debug option to get more log output. Run with --scan to get full insights.

* Get more help at <https://help.gradle.org>

BUILD FAILED in 687ms
What am I doing wrong?
I don't get why, but seems that commenting out some other very specific insert and delete operations in other tables make it work. As if there was some sort of conflict.
These are the ones I had to comment out:
Copy code
-- delete:
-- DELETE FROM userEntity
-- WHERE user_id = ?
-- RETURNING *;

-- delete:
-- DELETE FROM subscriptionEntity
-- WHERE user_id = ? AND repository_id = ?
-- RETURNING *;

-- delete:
-- DELETE FROM repositoryEntity
-- WHERE repository_id = ?
-- RETURNING *;

-- insert:
-- INSERT INTO repositoryEntity(owner, repository)
-- VALUES (?, ?);

-- insert:
-- INSERT INTO subscriptionEntity(subscribed_at, user_id, repository_id)
-- VALUES (?, ?, ?);
v
Can you paste the full (self-contained) code which is producing this error? Because judging by the stacktrace, it looks like a different line is causing the error outside of the code initially pasted:
> Failed to compile PostgreSqlInsertStmtImpl(INSERT_STMT): [] :
INSERT INTO repositoryEntity(owner, repository)
VALUES (?, ?)
j
UserEntity.sq
Copy code
CREATE TABLE IF NOT EXISTS userEntity (
    user_id SERIAL PRIMARY KEY,
    slack_user_id VARCHAR NOT NULL,
    slack_channel_id VARCHAR
);

insert:
WITH inserted_ids AS (
  INSERT INTO  userEntity(slack_user_id, slack_channel_id)
  VALUES (:slack_user_id, :slack_channel_id)
  RETURNING user_id AS insert_id
) SELECT insert_id FROM inserted_ids;

selectAll:
SELECT *
FROM userEntity;

select:
SELECT *
FROM userEntity
WHERE slack_user_id = ? AND slack_channel_id = ?;

selectByUserId:
SELECT *
FROM userEntity
WHERE user_id = ?;

selectBySlackUserId:
SELECT *
FROM userEntity
WHERE slack_user_id = ?;

selectBySlackChannelId:
SELECT *
FROM userEntity
WHERE slack_channel_id = ?;

-- delete:
-- DELETE FROM userEntity
-- WHERE user_id = ?
-- RETURNING *;
// SubscriptionEntity.sq
Copy code
import java.time.LocalDateTime;

CREATE TABLE IF NOT EXISTS subscriptionEntity (
    subscription_id SERIAL PRIMARY KEY,
    subscribed_at DATE AS LocalDateTime NOT NULL, -- TIMESTAMPTZ is not supported by sqldelight
    user_id SERIAL NOT NULL,
    repository_id SERIAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES userEntity(user_id) ON DELETE CASCADE,
    FOREIGN KEY (repository_id) REFERENCES repositoryEntity(repository_id) ON DELETE CASCADE
);

-- insert:
-- INSERT INTO subscriptionEntity(subscribed_at, user_id, repository_id)
-- VALUES (?, ?, ?);

selectAll:
SELECT *
FROM subscriptionEntity;

selectByUserId:
SELECT *
FROM subscriptionEntity
LEFT JOIN repositoryEntity ON repositoryEntity.repository_id = subscriptionEntity.repository_id
WHERE user_id = ?;

select:
SELECT *
FROM subscriptionEntity
LEFT JOIN repositoryEntity ON repositoryEntity.repository_id = subscriptionEntity.repository_id
WHERE user_id = ? AND repositoryEntity.owner = ? AND repositoryEntity.repository = ?;

selectByRepoId:
SELECT *
FROM subscriptionEntity
WHERE repository_id = ?;

-- delete:
-- DELETE FROM subscriptionEntity
-- WHERE user_id = ? AND repository_id = ?
-- RETURNING *;
// RepositoryEntity.sq
Copy code
CREATE TABLE IF NOT EXISTS repositoryEntity (
    repository_id SERIAL PRIMARY KEY,
    owner VARCHAR NOT NULL,
    repository VARCHAR NOT NULL
);

-- insert:
-- INSERT INTO repositoryEntity(owner, repository)
-- VALUES (?, ?);

selectAll:
SELECT *
FROM repositoryEntity;

select:
SELECT *
FROM repositoryEntity
WHERE owner = ? AND repository = ?;

selectByRepoId:
SELECT *
FROM repositoryEntity
WHERE repository_id = ?;

selectByOwner:
SELECT *
FROM repositoryEntity
WHERE owner = ?;

selectByRepo:
SELECT *
FROM repositoryEntity
WHERE repository = ?;

-- delete:
-- DELETE FROM repositoryEntity
-- WHERE repository_id = ?
-- RETURNING *;
It'd start failing as soon as you uncomment any of the commented operatiosn
👍 1
v
I’m using a simplified version of you code for a minimal reproducible example:
Copy code
CREATE TABLE IF NOT EXISTS userEntity (
    user_id SERIAL PRIMARY KEY,
    slack_user_id VARCHAR NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptionEntity (
    user_id2 SERIAL NOT NULL,
    FOREIGN KEY (user_id2) REFERENCES userEntity(user_id)
);

insertSubscription:
INSERT INTO subscriptionEntity(user_id2)
VALUES (?);

insertUser:
WITH inserted_ids AS (
  INSERT INTO userEntity(slack_user_id)
  VALUES (?)
  RETURNING user_id AS insert_id
) SELECT insert_id FROM inserted_ids;
The above fails, but if you comment the line 
insertSubscription:
while keeping the actual SQL uncommented, the code compiles. Please could you create a GitHub Issue in SQLDelight to track this? It’s unlikely I’ll have much time over the next couple of weeks to investigate this any further
j
Sure. I’ll do it tomorrow first thing in the morning. Not in the laptop anymore
46 Views
Open in Slack
PreviousNext
Powered by