Re: [Postgres-xc-developers] Patch for bug#3013984: Sequence scope

[Mason S. <mas...@en...>]

On 11/18/10 9:44 AM, Michael Paquier wrote:
> This is going to be a pretty long message, but...
> I made a couple of tests with this patch.
>
> Successively I created two databases, sequences on it, then dropped
> the databases.
> I created again and a couple of times databases (and so sequences) and
> didn't notice any error.
> What are the SQL you used for your tests? Is it reproducible?
>
Yes, it is still happening. My sequence of steps was a bit different:

$ inst/bin/createdb -p 5445 mds1
$ inst/bin/createdb -p 5445 mds2
$ inst/bin/psql -p 5445 mds1
psql (8.4.3)
Type "help" for help.

mds1=# create sequence foo;
CREATE SEQUENCE
mds1=# create sequence foo2;
CREATE SEQUENCE
mds1=# \c mds2
psql (8.4.3)
You are now connected to database "mds2".
mds2=# create sequence foo;
CREATE SEQUENCE
mds2=# create sequence foo2;
CREATE SEQUENCE
mds2=# select nextval('foo');
nextval
---------
1
(1 row)

mds2=# select nextval('foo');
nextval
---------
2
(1 row)

mds2=# select nextval('foo2');
nextval
---------
1
(1 row)

mds2=# select nextval('foo2');
nextval
---------
2
(1 row)

mds2=# \q

72:postgres-xc masonsharp$ stopnodes.sh
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped
waiting for server to shut down.... done
server stopped

(this stops the coordinators and datanodes, but not GTM)

72:postgres-xc masonsharp$ startnodes.sh
LOG: database system was interrupted; last known up at 2010-11-18
11:46:52 JST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/4BCF34
LOG: record with zero length at 0/4BCFE4
LOG: redo done at 0/4BCFB8
LOG: last completed transaction was at log time 2010-11-18 11:48:52.44638+09
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
LOG: database system was interrupted; last known up at 2010-11-18
11:46:52 JST
LOG: database system was not properly shut down; automatic recovery in
progress
LOG: redo starts at 0/4BCF34
LOG: record with zero length at 0/4BCFE4
LOG: redo done at 0/4BCFB8
LOG: last completed transaction was at log time 2010-11-18 11:48:52.43121+09
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
14151: 0: LOG: database system was shut down at 2010-11-18 11:49:16 JST
14159: 0: LOG: autovacuum launcher started
14142: 0: LOG: database system is ready to accept connections
14160: 0: LOG: database system was shut down at 2010-11-18 11:49:17 JST
14143: 0: LOG: database system is ready to accept connections
14165: 0: LOG: autovacuum launcher started
72:postgres-xc masonsharp$ inst/bin/psql -p 5445 mds1
psql (8.4.3)
Type "help" for help.

mds1=# drop database mds2;
WARNING: can not connect to GTM: Connection refused
ERROR: Deletion of sequences on database mds2 not completed
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

Regards,

Mason


> -- create databases
> template1=# create database foo1;
> CREATE DATABASE
> template1=# create database foo2;
> CREATE DATABASE
> template1=# \c foo1
> psql (8.4.5, サバ 8.4.3)
> デタベス "foo1" に接しました。.
> foo1=# create sequence foo;
> CREATE SEQUENCE
> foo1=# create sequence foo2;
> CREATE SEQUENCE
> foo1=# \c foo2
> psql (8.4.5, サバ 8.4.3)
> デタベス "foo2" に接しました。.
> foo2=# create sequence foo;
> CREATE SEQUENCE
> foo2=# create sequence foo2;
> CREATE SEQUENCE
> foo2=# select nextval('foo');
> nextval
> ---------
> 1
> (1 行)
>
> foo2=# select nextval('foo');
> nextval
> ---------
> 2
> (1 行)
>
> foo2=# \c template1
> psql (8.4.5, サバ 8.4.3)
> デタベス "template1" に接しました。.
> template1=# drop database foo1;
> DROP DATABASE
> template1=# clean connection to all for database foo2;
> CLEAN CONNECTION
> template1=# drop database foo2;
> DROP DATABASE
> template1=# create database foo2;
> CREATE DATABASE
> template1=# \c foo2
> psql (8.4.5, サバ 8.4.3)
> デタベス "foo2" に接しました。.
> foo2=# create sequence foo;
> CREATE SEQUENCE
> foo2=# create sequence foo2;
> CREATE SEQUENCE
> foo2=# select nextval('foo');
> nextval
> ---------
> 1
> (1 行)
>
> foo2=# select nextval('foo');
> nextval
> ---------
> 2
> (1 行)
>
> foo2=# select nextval('foo2');
> nextval
> ---------
> 1
> (1 行)
>
> foo2=# select nextval('foo2');
> nextval
> ---------
> 2
> (1 行)
>
> foo2=# \c template1
> psql (8.4.5, サバ 8.4.3)
> デタベス "template1" に接しました。.
> template1=# create database foo1;
> CREATE DATABASE
> template1=# \c foo1
> psql (8.4.5, サバ 8.4.3)
> デタベス "foo1" に接しました。.
> foo1=# create sequence foo;
> CREATE SEQUENCE
> foo1=# create sequence foo2;
> CREATE SEQUENCE
> foo1=# select nextval('foo');
> nextval
> ---------
> 1
> (1 行)
>
> foo1=# select nextval('foo');
> nextval
> ---------
> 2
> (1 行)
>
> foo1=# select nextval('foo2');
> nextval
> ---------
> 1
> (1 行)
> foo1=# \c template1
> psql (8.4.5, サバ 8.4.3)
> デタベス "template1" に接しました。.
> template1=# clean connection to all for database foo1;
> CLEAN CONNECTION
> template1=# clean connection to all for database foo2;
> CLEAN CONNECTION
> template1=# drop database foo1;
> DROP DATABASE
> template1=# drop database foo2;
> DROP DATABASE
> template1=# create database foo1;
> CREATE DATABASE
> template1=# create database foo2;
> CREATE DATABASE
> template1=# \c foo1
> psql (8.4.5, サバ 8.4.3)
> デタベス "foo1" に接しました。.
> foo1=# create sequence foo1;
> CREATE SEQUENCE
> foo1=# create sequence foo2;
> CREATE SEQUENCE
> foo1=# create sequence foo;
> CREATE SEQUENCE
> foo1=# select nextval('foo1');
> nextval
> ---------
> 1
> (1 行)
>
> foo1=# alter sequence foo2 increment 2;
> ALTER SEQUENCE
> foo1=# select nextval('foo2');
> nextval
> ---------
> 1
> (1 行)
>
> foo1=# select nextval('foo2');
> nextval
> ---------
> 3
> (1 行)
>
> foo1=# select nextval('foo2');
> nextval
> ---------
> 5
> (1 行)
>
> -- 
> Michael Paquier
> http://michaelpq.users.sourceforge.net
>


-- 
Mason Sharp
EnterpriseDB Corporation
The Enterprise Postgres Company


This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.