Обсуждение: BUG #13993: Temp sequence does not seem to be properly deleted

The following bug has been logged on the website:

Bug reference:      13993
Logged by:          Zajaczkowski
Email address:      mathias.zajaczkowski@ubik.ch
PostgreSQL version: 9.4.4
Operating system:   Windows 7 64 bits
Description:

I use pgAdmin V. 1.20.0 (Jul 13 2015, rev: REL-1_20_0)
Within a script I created temporary sequence:
CREATE TEMP SEQUENCE 'rateSeq' with S in uppercase to be used for rate table
pk.
Script run fine.
Next time script gave an error "relation rateseq already exists".
I modified the creation of the sequence  to:
CREATE TEMP SEQUENCE rateseq (all in lower case)
Same error when running the script.
I tried: DROP SEQUENCE rateseq
Result: sequence <<rateseq>> does not exist.
I completely changed the name of my sequence to "seq4rate" => same error.
I hade a look in pg_class table where I found a record with relname =
rateseq.
I deleted this line. It was not a good idea, but I'm not in production and
had this try.
Since then trying to run the script I had a message "... with Oid can not be
found ..."
I restored the database. Problem persisted.
I stoped and restarted the service "postgresql-x64-9.4 - PostgreSQL Server
9.4".
That solved my problem.

Kind regards

On 28-02-2016 17:08, mathias.zajaczkowski@ubik.ch wrote:
> Within a script I created temporary sequence:
> CREATE TEMP SEQUENCE 'rateSeq' with S in uppercase to be used for rate table
> pk.
You can't use single quotes. Instead, use double quotes.

$ psql -q
euler=# create temp sequence 'rateSeq';
ERROR:  syntax error at or near "'rateSeq'"
LINE 1: create temp sequence 'rateSeq';
                             ^
euler=# create temp sequence "rateSeq";
euler=# select relname from pg_class where relname ~ 'rate';
 relname
---------
 rateSeq
(1 row)

> Next time script gave an error "relation rateseq already exists".
> I modified the creation of the sequence  to:
> CREATE TEMP SEQUENCE rateseq (all in lower case)
> Same error when running the script.
> I tried: DROP SEQUENCE rateseq
> Result: sequence <<rateseq>> does not exist.
>
Temporary relations can't be dropped in another session. However, the
temp sequence will gone away when you close the connection that created it.

If you want to run your script many times in the same session, make sure
you include a DROP SEQUENCE at the end of the script.


--
   Euler Taveira                   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento