Thread: CREATE TABLE AS SELECT hangs
Hello,
I am trying to populate/create a database with CREATE TABLE AS SELECT like so:
CREATE TABLE test_table AS
SELECT row_number() over() as gid, cells.geom
FROM test_geom_function(1,2,3) AS cells;
This works on one database instance, but not on another. On the database it doesn't work on it seems to hang on executing and when I cancel it I get
ERROR: canceling statement due to user request
CONTEXT: while inserting index tuple (6,13) in relation "pg_type_typname_nsp_index"
SQL state: 57014
Any help/suggestions would be greatly appreciated. I am running PostgreSQL 9.6 with PostGIS also for some spatial functionality.
Thanks,
Derek
derek <jmorgan3@uwf.edu> writes: > I am trying to populate/create a database with CREATE TABLE AS SELECT > like so: > CREATE TABLE test_table AS > SELECT row_number() over() as gid, cells.geom > FROM test_geom_function(1,2,3) AS cells; > This works on one database instance, but not on another. On the database > it doesn't work on it seems to hang on executing and when I cancel it I get > ERROR: canceling statement due to user request > CONTEXT: while inserting index tuple (6,13) in relation > "pg_type_typname_nsp_index" > SQL state: 57014 Hmm (pokes around) ... That error context message seems to only be possible if we were blocked waiting for some other transaction. I theorize that you have an uncommitted transaction someplace that has created the same table name. Cancelling it would fix things. regards, tom lane
Tom. Thanks so much for your response. Your theory appears to have been correct, and it is working like a champ now. Best, Derek On 10/1/2018 4:29 PM, Tom Lane wrote: > derek <jmorgan3@uwf.edu> writes: >> I am trying to populate/create a database with CREATE TABLE AS SELECT >> like so: >> CREATE TABLE test_table AS >> SELECT row_number() over() as gid, cells.geom >> FROM test_geom_function(1,2,3) AS cells; >> This works on one database instance, but not on another. On the database >> it doesn't work on it seems to hang on executing and when I cancel it I get >> ERROR: canceling statement due to user request >> CONTEXT: while inserting index tuple (6,13) in relation >> "pg_type_typname_nsp_index" >> SQL state: 57014 > Hmm (pokes around) ... That error context message seems to only be > possible if we were blocked waiting for some other transaction. > I theorize that you have an uncommitted transaction someplace that > has created the same table name. Cancelling it would fix things. > > regards, tom lane