postgres-xc-general

[Postgres-xc-general] Question about dirty reads

[Thom B. <th...@li...>]

Hi,

I've just started trying to familiarise myself with Postgres-XC, but
I'm a bit concerned about dirty reads that occur:

postgres=# begin;
BEGIN
postgres=# insert into moo (thing) values ('alpha');
INSERT 0 1

--another transaction inserts (6,beta)

postgres=# select * from moo;
 id | thing
----+-------
  1 | hello
  2 | howdy
  5 | alpha
  6 | beta
  3 | bark
  4 | hiss
(6 rows)

-- we're seeing (6,beta) but shouldn't have visibility of it,
effectively a dirty read.

-- another transaction inserts (7,gamma)

postgres=# select * from moo;
 id | thing
----+-------
  1 | hello
  2 | howdy
  5 | alpha
  6 | beta
  3 | bark
  4 | hiss
  7 | gamma
(7 rows)

-- (7, gamma) shouldn't be visible, another dirty read

postgres=# delete from moo where thing = 'beta';
DELETE 1
-- we successfully deleted a row we shouldn't have been able to see.

postgres=# rollback;
ROLLBACK
postgres=# select * from moo;
 id | thing
----+-------
  1 | hello
  2 | howdy
  6 | beta
  3 | bark
  4 | hiss
  7 | gamma
(6 rows)

-- the (1, alpha) insert and (6,beta) delete were both rolled back.

Is this supposed to be one of the trade-offs with write-scaling?

I also noticed that savepoints aren't supported.  Will those be added?

Thanks

Thom

Re: [Postgres-xc-general] Question about dirty reads

[Masataka S. <pg...@gm...>]

Hello Thom,

As far as I know, Postgres-XC supports the read committed isolation
level and it is the default level.
I think it is a critical problem that uncommitted data is visible in
other READ COMMITTED transaction.

I tested your report on 1.2 beta in my hand with 2 coordinators and 2
datanodes, but it is not reproduced.

=================================== TERMINAL 1
===================================
$ pgxc_ctl Psql postgres
bash_handler.c:install_pgxc_ctl_bash(33) Installing pgxc_ctl_bash
script as /home/cx/pgxc_ctl/pgxc_ctl_bash.
bash_handler.c:install_pgxc_ctl_bash(33) Installing pgxc_ctl_bash
script as /home/cx/pgxc_ctl/pgxc_ctl_bash.
pgxc_ctl.c:read_configuration(246) Reading configuration using
/home/cx/pgxc_ctl/pgxc_ctl_bash --home /home/cx/pgxc_ctl
--configuration /home/cx/pgxc_ctl/pgxc_ctl.conf
pgxc_ctl.c:read_configuration(256) Finished to read configuration.
pgxc_ctl.c:main(535)    ******** PGXC_CTL START ***************

pgxc_ctl.c:main(536) Current directory: /home/cx/pgxc_ctl
do_command.c:do_singleLine(2330) Selected coord2.
psql (PGXC , based on PG 9.3beta1)
Type "help" for help.

postgres=# create table t (id serial, val text);
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into t (val) values('alpha');
INSERT 0 1
postgres=# select * from t;
 id |  val
----+-------
  1 | alpha
(1 row)
=================================== TERMINAL 2
===================================
> pgxc_ctl Psql postgres
bash_handler.c:install_pgxc_ctl_bash(33) Installing pgxc_ctl_bash
script as /home/cx/pgxc_ctl/pgxc_ctl_bash.
bash_handler.c:install_pgxc_ctl_bash(33) Installing pgxc_ctl_bash
script as /home/cx/pgxc_ctl/pgxc_ctl_bash.
pgxc_ctl.c:read_configuration(246) Reading configuration using
/home/cx/pgxc_ctl/pgxc_ctl_bash --home /home/cx/pg
xc_ctl --configuration /home/cx/pgxc_ctl/pgxc_ctl.conf
pgxc_ctl.c:read_configuration(256) Finished to read configuration.
pgxc_ctl.c:main(535)    ******** PGXC_CTL START ***************

pgxc_ctl.c:main(536) Current directory: /home/cx/pgxc_ctl
do_command.c:do_singleLine(2330) Selected coord2.
psql (PGXC , based on PG 9.3beta1)
Type "help" for help.

postgres=# begin;
BEGIN
postgres=# show transaction_isolation ;
 transaction_isolation
-----------------------
 read committed
(1 row)

postgres=# insert into t (val) values('beta');
INSERT 0 1
=================================== TERMINAL 1
===================================
postgres=# select * from t;
 id |  val
----+-------
  1 | alpha
(1 row)
=================================== TERMINAL 2
===================================
postgres=# insert into t (val) values('gamma');
INSERT 0 1
postgres=# select * from t;
 id |  val
----+-------
  2 | beta
  3 | gamma
(2 rows)
=================================== TERMINAL 1
===================================
postgres=# select * from t;
 id |  val
----+-------
  1 | alpha
(1 row)
=================================== TERMINAL 2
===================================
postgres=# commit;
COMMIT
postgres=# select * from t;
 id |  val
----+-------
  2 | beta
  3 | gamma
(2 rows)
=================================== TERMINAL 1
===================================
postgres=# select * from t;
 id |  val
----+-------
  1 | alpha
  2 | beta
  3 | gamma
(3 rows)

postgres=# rollback;
ROLLBACK
postgres=# select * from t;
 id |  val
----+-------
  2 | beta
  3 | gamma
(2 rows)
=================================== TERMINAL 2
===================================
postgres=# select * from t;
 id |  val
----+-------
  2 | beta
  3 | gamma
(2 rows)

Can you provide your PGXC version (or commit ID), deployment,
configuration and whole the script?

Regards.

On 17 March 2014 01:56, Thom Brown <th...@li...> wrote:
> Hi,
>
> I've just started trying to familiarise myself with Postgres-XC, but
> I'm a bit concerned about dirty reads that occur:
>
> postgres=# begin;
> BEGIN
> postgres=# insert into moo (thing) values ('alpha');
> INSERT 0 1
>
> --another transaction inserts (6,beta)
>
> postgres=# select * from moo;
>  id | thing
> ----+-------
>   1 | hello
>   2 | howdy
>   5 | alpha
>   6 | beta
>   3 | bark
>   4 | hiss
> (6 rows)
>
> -- we're seeing (6,beta) but shouldn't have visibility of it,
> effectively a dirty read.
>
> -- another transaction inserts (7,gamma)
>
> postgres=# select * from moo;
>  id | thing
> ----+-------
>   1 | hello
>   2 | howdy
>   5 | alpha
>   6 | beta
>   3 | bark
>   4 | hiss
>   7 | gamma
> (7 rows)
>
> -- (7, gamma) shouldn't be visible, another dirty read
>
> postgres=# delete from moo where thing = 'beta';
> DELETE 1
> -- we successfully deleted a row we shouldn't have been able to see.
>
> postgres=# rollback;
> ROLLBACK
> postgres=# select * from moo;
>  id | thing
> ----+-------
>   1 | hello
>   2 | howdy
>   6 | beta
>   3 | bark
>   4 | hiss
>   7 | gamma
> (6 rows)
>
> -- the (1, alpha) insert and (6,beta) delete were both rolled back.
>
> Is this supposed to be one of the trade-offs with write-scaling?
>
> I also noticed that savepoints aren't supported.  Will those be added?
>
> Thanks
>
> Thom
>
> ------------------------------------------------------------------------------
> Learn Graph Databases - Download FREE O'Reilly Book
> "Graph Databases" is the definitive new guide to graph databases and their
> applications. Written by three acclaimed leaders in the field,
> this first edition is now available. Download your free book today!
> http://p.sf.net/sfu/13534_NeoTech
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general

Re: [Postgres-xc-general] Question about dirty reads

[Pavan D. <pav...@gm...>]

On Sun, Mar 16, 2014 at 10:26 PM, Thom Brown <th...@li...> wrote:

> Hi,
>
> I've just started trying to familiarise myself with Postgres-XC, but
> I'm a bit concerned about dirty reads that occur:
>
> postgres=# begin;
> BEGIN
> postgres=# insert into moo (thing) values ('alpha');
> INSERT 0 1
>
> --another transaction inserts (6,beta)
>
>
You did not say whether this "another transaction" is still open or already
committed. If that "another transaction" is committed, then I don't see any
problem with the results. The transaction running select query is in
read-committed mode and hence it can see the results of the other
transaction.

Thanks,
Pavan

-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee

Re: [Postgres-xc-general] Question about dirty reads

[Thom B. <th...@li...>]

On 17 March 2014 07:27, Pavan Deolasee <pav...@gm...> wrote:
>
>
>
> On Sun, Mar 16, 2014 at 10:26 PM, Thom Brown <th...@li...> wrote:
>>
>> Hi,
>>
>> I've just started trying to familiarise myself with Postgres-XC, but
>> I'm a bit concerned about dirty reads that occur:
>>
>> postgres=# begin;
>> BEGIN
>> postgres=# insert into moo (thing) values ('alpha');
>> INSERT 0 1
>>
>> --another transaction inserts (6,beta)
>>
>
> You did not say whether this "another transaction" is still open or already
> committed. If that "another transaction" is committed, then I don't see any
> problem with the results. The transaction running select query is in
> read-committed mode and hence it can see the results of the other
> transaction.

Sorry guys.  Yes, I've clearly confused isolation levels and this is
indeed fine.  The other session did commit its data by the time the
first session went to query the table again, so there's nothing wrong
with it.

Apologies for the noise.

Although my question about savepoints still stands.  Will those be
supported in future?

Thanks

Thom