Error after second read from CTE or TEMPORARY TABLE in Postgres

Question

I am trying to write a query where I select records by some condition, update one column of those records and then return them, all in one transaction.

I have tried two options. First with CTE:

BEGIN;
WITH unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
)
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

In this case I get an error relation "unwatched_rows" does not exist. How I learned this is because i can't use CTE more than once. And as I understand it, CTE is not suitable for my situation

Second option with CREATE TEMPORARY TABLE:

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

I am running this query through DBeaver. For first time it works fine, but when I try to run this query again I get an error relation "unwatched_rows" already exists. I don't understand why this is happening. Shouldn't the temporary table be dropped right after the commit? What am I doing wrong and how to solve it?

same result. it says relation "unwatched_rows" already exists — SorryForAsking, Apr 17 '23 at 11:32

SelVazi · Answer 1 · 2023-04-17T11:51:01.277

1

If you want the temporary table to be removed right after the commit, You will have to specify ON COMMIT DROP when create it :

BEGIN;
CREATE TEMPORARY TABLE unwatched_rows ON COMMIT DROP AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

An other solution is to drop your temporary table before created it again :

BEGIN;
DROP TABLE IF EXISTS unwatched_rows;
CREATE TEMPORARY TABLE unwatched_rows AS (
        SELECT * from my_table WHERE is_readed = false FOR UPDATE
);
UPDATE my_table SET is_readed = true WHERE id IN (SELECT id FROM unwatched_rows);
SELECT * FROM unwatched_rows;
COMMIT;

edited Apr 17 '23 at 11:51

answered Apr 17 '23 at 11:26

SelVazi

10,028
2
13
29

thanks, it works but without "temporary" in drop command. But for some reason, if I use ON COMMIT DROP fetching result takes about 3 sec for table with one record – SorryForAsking Apr 17 '23 at 11:41
I have No idea what could cause this – SelVazi Apr 17 '23 at 11:50
Is there way to wrap this query in a view or stored procedure? – SorryForAsking Apr 17 '23 at 11:53
1

Yes its doable .. this is an example https://stackoverflow.com/questions/7932962/using-temp-table-in-pl-pgsql-procedure-for-cleaning-tables – SelVazi Apr 17 '23 at 12:00

score 1 · Accepted Answer · answered Apr 17 '23 at 22:19

You can certainly use a CTE (with ...) once. However you used it incorrectly. A CTE is essentially a view which exists only for the duration of the query where it is created. In query the semi-colon (;) after the update terminated the statement and thus the CTE itself goes out-of-scope. So for theselect statement it no longer exists. Postgres allows DML in a CTE so to correct your statement create a second CTE that does the update. So:

with unwatched_rows as (
        select id 
          from my_table 
         where is_readed = false for update
     )
   , upt as (
       update my_table 
          set is_readed = true 
        where id in (select id from unwatched_rows)
     ) 
select * 
  from unwatched_rows;

As far as wrapping into a function this is actually quite simple. It does not require a temp table not, for that matter, a CTE. It reduces to a single SQL statement:

create or replace function unwatched_rows()
  returns setof my_table.id%type 
 language sql 
as $$ 
    update my_table 
       set is_readed = true
     where not is_readed    -- same as: is_readed = false
    returning id;
$$;

See here for demo illustrating both.

Error after second read from CTE or TEMPORARY TABLE in Postgres

2 Answers2