Question

SQLExecutor deadlocking on PostgreSQL DDL

K

Trying to run the following SQL/DDL combination:

SELECT setval('@Value(table_name)_$(Column_Name)_seq', (SELECT (coalesce(max("$(Column_Name)"),0) + 1) FROM "@Value(table_schema)"."@Value(table_name)"));
ALTER TABLE "@Value(table_schema)"."@Value(table_name)"  ALTER COLUMN "$(Column_Name)" SET DEFAULT nextval('@Value(table_name)_$(Column_Name)_seq'::regclass);

But the workspace freezes with a deadlock on ALTER TABLE.  Tried explicitly BEGINning a transaction before the first statement and COMMITting after the second, but that fails with "transaction already in progress" followed by "no transaction in progress".  Not even separating the two statements into different SQLExecutors helps.

 

Thinking I'm going to need to split this into separate workspaces, or worst case scenario, doing some of it in standalone Python.  But wondering if anyone else has run into this and has better suggestions.

2 replies

david_r
Rank
Userlevel 4

I'm assuming that you already have the following first line in your SQLExecutor:

FME_SQL_DELIMITER ;

If not, try adding it. Note the space before the semi-colon.

K

Thanks for the idea! It didn't seem like a problem with the delimiter, but I know looks can be deceiving. I wound up separating the statements into different workspaces, which did the trick thanks to separate database connection sessions.

Reply


Community Stats

28,670
Posts
109,367
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.