postgres-xc-general

[Postgres-xc-general] (Global) nextval() from sequences support

[David H. <dav...@me...>]

After playing around with postgres-xc 0.9.5, I wanted to give my actual 
application a try. I get the following error message when setting up the 
base schema: "ERROR:  Postgres-XC does not support DEFAULT with 
non-immutable functions yet". This happens when setting up a column as 
follows (this is btw copied verbatim from the postgresql documentation, 
explaining how a serial type works):

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

I've read that (global) sequences and serial are not yet supported. I 
checked the roadmap and couldn't find mention of this explicitly. I was 
wondering if support is planned, and if so, when? (And if not, why :-).

Thanks,
David

Re: [Postgres-xc-general] (Global) nextval() from sequences support

[Michael P. <mic...@gm...>]

On Fri, Jul 15, 2011 at 8:01 PM, David Hartveld
<dav...@me...>wrote:

> After playing around with postgres-xc 0.9.5, I wanted to give my actual
> application a try. I get the following error message when setting up the
> base schema: "ERROR:  Postgres-XC does not support DEFAULT with
> non-immutable functions yet". This happens when setting up a column as
> follows (this is btw copied verbatim from the postgresql documentation,
> explaining how a serial type works):
>
> CREATE SEQUENCE tablename_colname_seq;
> CREATE TABLE tablename (
>     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
> );
> ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
>
> I've read that (global) sequences and serial are not yet supported. I
> checked the roadmap and couldn't find mention of this explicitly. I was
> wondering if support is planned, and if so, when? (And if not, why :-).
>

Supporting serial and default values with nextval is pretty complicated
because sequences are only created on Coordinators. It is not currently
taken into account in the development plans.
However, I am working currently on the support of this feature because me
too I believe it is important to support it.
My patch is half-way done, and needs some rework on the planner side.
I may have some results within the next couple of weeks.

Regards,
-- 
Michael Paquier
http://michael.otacoo.com

Re: [Postgres-xc-general] (Global) nextval() from sequences support

[David H. <dav...@me...>]

Op 15-07-11 13:27, Michael Paquier schreef:
> On Fri, Jul 15, 2011 at 8:01 PM, David Hartveld
> <dav...@me... <mailto:dav...@me...>> wrote:
>
>     After playing around with postgres-xc 0.9.5, I wanted to give my actual
>     application a try. I get the following error message when setting up the
>     base schema: "ERROR: Postgres-XC does not support DEFAULT with
>     non-immutable functions yet". This happens when setting up a column as
>     follows (this is btw copied verbatim from the postgresql documentation,
>     explaining how a serial type works):
>
>     CREATE SEQUENCE tablename_colname_seq;
>     CREATE TABLE tablename (
>     colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
>     );
>     ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
>
>     I've read that (global) sequences and serial are not yet supported. I
>     checked the roadmap and couldn't find mention of this explicitly. I was
>     wondering if support is planned, and if so, when? (And if not, why :-).
>
>
> Supporting serial and default values with nextval is pretty complicated
> because sequences are only created on Coordinators. It is not currently
> taken into account in the development plans.
> However, I am working currently on the support of this feature because
> me too I believe it is important to support it.
> My patch is half-way done, and needs some rework on the planner side.
> I may have some results within the next couple of weeks.

I understand the problem, however, the application for which I'm 
experimenting does need the functionality (currently). I might be 
interested in testing your patches, do let me know if you need some 
testing done!

Greetings,
David

Re: [Postgres-xc-general] (Global) nextval() from sequences support

[Michael P. <mic...@gm...>]

Hi,

Sorry for the late reply on this topic.
Here is a little bit of feedback about your problem.

After looking closer at what was currently missing in Postgres-XC to support
SERIAL, I found that some basic mechanism to feed values on Coordinator for
stable and volatile functions is still missing.
For example, the lack of support for SERIAL is not only limited to the query
itself, it is a more fundamental error that happens for all the queries of
the type:
select * from table where column_1 = volatile_function('one_value');
By that I mean SELECT queries using functions in their WHERE clause that
cannot be evaluated on Datanodes and values have to be taken from
Coordinator to insure data consistency among the cluster. This is also the
case for INSERT as values are not determined correctly on Coordinator. This
is also the case for DELETE and UPDATE clauses using non-immutable functions
in their WHERE clause.

For example, a simple INSERT query symbolizing this problem would be:
create table aa (int serial) distribute by replication;
insert into aa values (nextval('sequ'));
insert into aa values (DEFAULT);
In those cases we need to calculate the value on Coordinator and then
distribute the same value among nodes.

>From August, we will begin to investigate those problems. But it needs some
fundamental work and may need a bit of time.
However, once such a mechanism will be in place, supporting serial tables is
a piece of cake.

Regards,
-- 
Michael Paquier
http://michael.otacoo.com