Re: Writeable CTEs Desgin Doc on Wiki

We (Marko, David Fetter and I) discussed on IRC about design of
writeable CTEs. It does and will contain not only syntax but also
miscellaneous specifications, general rules and restrictions. I hope
this will help the patch reviews and stop dangerous design at the
early stage. If you find something wrong, or have request, please
notify.

http://wiki.postgresql.org/wiki/WriteableCTEs

We will keep to add details. Any comments are welcome.

Regards,

--
Hitoshi Harada

On Sun, Aug 15, 2010 at 7:44 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> We (Marko, David Fetter and I) discussed on IRC about design of
> writeable CTEs. It does and will contain not only syntax but also
> miscellaneous specifications, general rules and restrictions. I hope
> this will help the patch reviews and stop dangerous design at the
> early stage. If you find something wrong, or have request, please
> notify.
>
> http://wiki.postgresql.org/wiki/WriteableCTEs
>
> We will keep to add details. Any comments are welcome.

There are really two separate features here, and it might be worth
giving them separate names and separate designs (and separate
patches). Allowing the main query to be an insert, update, or delete
seems easier than allowing the toplevel CTEs to contain those
constructs, although I might be wrong about that.

Under features, what is DCL? There has been previous talk of allowing
WITH (COPY ...) and I am personally of the opinion that it would be
nice to be able to do WITH (EXPLAIN ...). DDL seems like a poor idea.

P.S. Call me a prude, but your choice of shorthand for
insert-update-delete may not be the best.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, Aug 16, 2010 at 02:25:50PM -0400, Robert Haas wrote:
> On Sun, Aug 15, 2010 at 7:44 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> > We (Marko, David Fetter and I) discussed on IRC about design of
> > writeable CTEs. It does and will contain not only syntax but also
> > miscellaneous specifications, general rules and restrictions. I hope
> > this will help the patch reviews and stop dangerous design at the
> > early stage. If you find something wrong, or have request, please
> > notify.
> >
> > http://wiki.postgresql.org/wiki/WriteableCTEs
> >
> > We will keep to add details. Any comments are welcome.
>
> There are really two separate features here, and it might be worth
> giving them separate names and separate designs (and separate
> patches). Allowing the main query to be an insert, update, or delete
> seems easier than allowing the toplevel CTEs to contain those
> constructs, although I might be wrong about that.

Interesting. I'd kinda seen them as the same thing.

> Under features, what is DCL?

Data Control Language, i.e. GRANT and REVOKE.

> There has been previous talk of allowing WITH (COPY ...) and I am
> personally of the opinion that it would be nice to be able to do
> WITH (EXPLAIN ...). DDL seems like a poor idea.

It may be, but I can see use cases for partitioning...

> P.S. Call me a prude, but your choice of shorthand for
> insert-update-delete may not be the best.

Then I presume you'll be supporting my idea of using the word "span"
for temporal data types rather than the current idea whose name
appears in academic literature.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Aug 16, 2010 at 3:00 PM, David Fetter <david(at)fetter(dot)org> wrote:
>> There has been previous talk of allowing WITH (COPY ...) and I am
>> personally of the opinion that it would be nice to be able to do
>> WITH (EXPLAIN ...).  DDL seems like a poor idea.
>
> It may be, but I can see use cases for partitioning...

Like what?

>> P.S. Call me a prude, but your choice of shorthand for
>> insert-update-delete may not be the best.
>
> Then I presume you'll be supporting my idea of using the word "span"
> for temporal data types rather than the current idea whose name
> appears in academic literature.

Wise guy.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

On Mon, Aug 16, 2010 at 04:34:07PM -0400, Robert Haas wrote:
> On Mon, Aug 16, 2010 at 3:00 PM, David Fetter <david(at)fetter(dot)org> wrote:
> >> There has been previous talk of allowing WITH (COPY ...) and I am
> >> personally of the opinion that it would be nice to be able to do
> >> WITH (EXPLAIN ...).  DDL seems like a poor idea.
> >
> > It may be, but I can see use cases for partitioning...
>
> Like what?

Managing partitions, or creating partitions in some way the partition
syntax doesn't anticipate, whatever it turns out to be.

> >> P.S. Call me a prude, but your choice of shorthand for
> >> insert-update-delete may not be the best.
> >
> > Then I presume you'll be supporting my idea of using the word
> > "span" for temporal data types rather than the current idea whose
> > name appears in academic literature.
>
> Wise guy.

In all seriousness, the temporal stuff is giving us a fantastic
opportunity, as a project, to break our tradition of lousy naming.

"Span" is descriptive, mnemonic, and easy to spell.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Aug 16, 2010 at 5:08 PM, David Fetter <david(at)fetter(dot)org> wrote:
> On Mon, Aug 16, 2010 at 04:34:07PM -0400, Robert Haas wrote:
>> On Mon, Aug 16, 2010 at 3:00 PM, David Fetter <david(at)fetter(dot)org> wrote:
>> >> There has been previous talk of allowing WITH (COPY ...) and I am
>> >> personally of the opinion that it would be nice to be able to do
>> >> WITH (EXPLAIN ...).  DDL seems like a poor idea.
>> >
>> > It may be, but I can see use cases for partitioning...
>>
>> Like what?
>
> Managing partitions, or creating partitions in some way the partition
> syntax doesn't anticipate, whatever it turns out to be.

So, what is the syntax you're hoping will work?

>> >> P.S. Call me a prude, but your choice of shorthand for
>> >> insert-update-delete may not be the best.
>> >
>> > Then I presume you'll be supporting my idea of using the word
>> > "span" for temporal data types rather than the current idea whose
>> > name appears in academic literature.
>>
>> Wise guy.
>
> In all seriousness, the temporal stuff is giving us a fantastic
> opportunity, as a project, to break our tradition of lousy naming.
>
> "Span" is descriptive, mnemonic, and easy to spell.

I guess that's a flamewar for another day.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

2010/8/17 David Fetter <david(at)fetter(dot)org>:
> On Mon, Aug 16, 2010 at 04:34:07PM -0400, Robert Haas wrote:
>> On Mon, Aug 16, 2010 at 3:00 PM, David Fetter <david(at)fetter(dot)org> wrote:
>> >> There has been previous talk of allowing WITH (COPY ...) and I am
>> >> personally of the opinion that it would be nice to be able to do
>> >> WITH (EXPLAIN ...).  DDL seems like a poor idea.
>> >
>> > It may be, but I can see use cases for partitioning...
>>
>> Like what?
>
> Managing partitions, or creating partitions in some way the partition
> syntax doesn't anticipate, whatever it turns out to be.

I don't see the good use cases you might be able to imagine, either.
Now we have DO statement, quite various types of DDL can be described
at one time.

Regards,

--
Hitoshi Harada

2010/8/17 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Sun, Aug 15, 2010 at 7:44 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
>> We (Marko, David Fetter and I) discussed on IRC about design of
>> writeable CTEs. It does and will contain not only syntax but also
>> miscellaneous specifications, general rules and restrictions. I hope
>> this will help the patch reviews and stop dangerous design at the
>> early stage. If you find something wrong, or have request, please
>> notify.
>>
>> http://wiki.postgresql.org/wiki/WriteableCTEs
>>
>> We will keep to add details. Any comments are welcome.
>
> There are really two separate features here, and it might be worth
> giving them separate names and separate designs (and separate
> patches).  Allowing the main query to be an insert, update, or delete
> seems easier than allowing the toplevel CTEs to contain those
> constructs, although I might be wrong about that.
>
> Under features, what is DCL?  There has been previous talk of allowing
> WITH (COPY ...) and I am personally of the opinion that it would be
> nice to be able to do WITH (EXPLAIN ...).  DDL seems like a poor idea.

So, there are three? One for allowing the main query to be an insert,
update, or delete, one for the main subject of writeable CTE with
insert, update, delete and one for allowing COPY to return rows. I am
attracted by such COPY functionality.

And the first part seems easier to be committed separately. Is it
possible to get it in by only adding syntax and little parser/planner
modification, or more executor code?

> P.S. Call me a prude, but your choice of shorthand for
> insert-update-delete may not be the best.

I think so, too :(. But there's no good expression in my mind. Suggestions?

Regards,

--
Hitoshi Harada

On 2010-08-17 6:41 AM +0300, Hitoshi Harada wrote:
> 2010/8/17 Robert Haas<robertmhaas(at)gmail(dot)com>:
>> There are really two separate features here, and it might be worth
>> giving them separate names and separate designs (and separate
>> patches). Allowing the main query to be an insert, update, or delete
>> seems easier than allowing the toplevel CTEs to contain those
>> constructs, although I might be wrong about that.
>>
>> Under features, what is DCL? There has been previous talk of allowing
>> WITH (COPY ...) and I am personally of the opinion that it would be
>> nice to be able to do WITH (EXPLAIN ...). DDL seems like a poor idea.
>
> So, there are three? One for allowing the main query to be an insert,
> update, or delete, one for the main subject of writeable CTE with
> insert, update, delete and one for allowing COPY to return rows. I am
> attracted by such COPY functionality.
>
> And the first part seems easier to be committed separately. Is it
> possible to get it in by only adding syntax and little parser/planner
> modification, or more executor code?

It's not that simple, see:
http://archives.postgresql.org/pgsql-hackers/2010-02/msg01065.php

Regards,
Marko Tiikkaja

On Mon, Aug 16, 2010 at 11:41 PM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> 2010/8/17 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Sun, Aug 15, 2010 at 7:44 AM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
>>> We (Marko, David Fetter and I) discussed on IRC about design of
>>> writeable CTEs. It does and will contain not only syntax but also
>>> miscellaneous specifications, general rules and restrictions. I hope
>>> this will help the patch reviews and stop dangerous design at the
>>> early stage. If you find something wrong, or have request, please
>>> notify.
>>>
>>> http://wiki.postgresql.org/wiki/WriteableCTEs
>>>
>>> We will keep to add details. Any comments are welcome.
>>
>> There are really two separate features here, and it might be worth
>> giving them separate names and separate designs (and separate
>> patches).  Allowing the main query to be an insert, update, or delete
>> seems easier than allowing the toplevel CTEs to contain those
>> constructs, although I might be wrong about that.
>>
>> Under features, what is DCL?  There has been previous talk of allowing
>> WITH (COPY ...) and I am personally of the opinion that it would be
>> nice to be able to do WITH (EXPLAIN ...).  DDL seems like a poor idea.
>
> So, there are three? One for allowing the main query to be an insert,
> update, or delete, one for the main subject of writeable CTE with
> insert, update, delete and one for allowing COPY to return rows. I am
> attracted by such COPY functionality.

Yeah, I'd say there are at least three. Maybe more than three.

> And the first part seems easier to be committed separately. Is it
> possible to get it in by only adding syntax and little parser/planner
> modification, or more executor code?

I'm not sure exactly what is involved, but it seems to me that
breaking large features into moderately-sized, self-contained chunks
tends to drastically shorten the time to commit. Of course, this is
only true if each patch is really an independent feature with
independent utility, but in this case it seems fairly easy to draw a
clean line.

>> P.S. Call me a prude, but your choice of shorthand for
>> insert-update-delete may not be the best.
>
> I think so, too :(. But there's no good expression in my mind. Suggestions?

DML?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company