9.4 Proposal: Initdb creates a single table

Simon Riggs <simon@2ndQuadrant.com> writes:

By now, some of you will be doubled over laughing as if this is an
April fool joke.

Indeed.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/23/2014 02:11 PM, Simon Riggs wrote:

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);

Without particular comment on the need for the table, I'd be concerned
about calling it "postgres".

My personal impression from Stack Overflow etc has been that users are
readily confused by the fact that we have:

- Database engine/system "postgres"
- backend binary "postgres" (they see it in ps)
- unix user "postgres"
- Pg superuser "postgres"
- database "postgres"

Sure, there's an argument for running with the theme here, but I suspect
using the name "postgres" for a default table will just muddy the waters
a bit more.

Even "postgres_table" would help.

It *absolutely must* be lower case, whatever it is, IMO. If you're going
for newest-of-the-newbies, the last thing you want to do is having them
dealing with it being just Postgres in some places, and having to be
"Postgres" in others.

Personally, don't know if I'm convinced it's overly worth doing - but I
think it's silly to dismiss without actually corralling up some users
who're unfamiliar with Pg and watching them get started. I'd love to see
some properly conducted usability studies of Pg, and something like this
would fit in well.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello

if you are thinking about this direction, then store there some demo
project.

I am don't think so isolated table has significant price.

Regards

Pavel

2014-04-23 8:45 GMT+02:00 Craig Ringer <craig@2ndquadrant.com>:

Simon Riggs wrote:

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);
COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
design your database';

The purpose of this is to make the database immediately usable. By
including this table in the default initdb it will mean that programs
can rely on the existence of this table and begin working quicker.

By now, some of you will be doubled over laughing as if this is an
April fool joke. I don't mean it to be at all.

The idea is to have a stupidly obvious and easy table that will
potentially be usable by just about everyone, in any language.

I am a PostgreSQL newbie.
How is this table useful for me?
I want to develop a database application.
I want to store personal data like name and birth date!

Actually, I feel confused. What should I do with this table?
Is it part of the database system? Will the database be broken if
I drop it? Do I have to ship it with my application?

If you don't like it, don't use it. If you really dislike it, drop it.

No, I'm not the kind of person who reads a manual to figure out what to
do with this table. I want to start coding *right now*.

But for new people coming to Postgres, they will have a data object to
access and begin using the database immediately. Their code will work,
their examples will work. OK, so they need to go back and think about
the design, but at least they got it to work and will be encouraged to
do more.

I have found a sample application for personal data on the internet.
How can I make it work with this table?

Remember when we didn't have a database called Postgres? Remember how
much simpler life is now? Remember that now.

Good that you mention that! I have wondered what to do with it.
When I first connected to PostgreSQL, I created a sample table, but the
senior developer from the other office told me that this is the "postgres"
database and that I shouldn't create any objects there.

What is it good for? Can I delete it?

Yours,
Laurenz Albe

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 04/23/2014 03:20 PM, Albe Laurenz wrote:

Good that you mention that! I have wondered what to do with it.
When I first connected to PostgreSQL, I created a sample table, but the
senior developer from the other office told me that this is the "postgres"
database and that I shouldn't create any objects there.

What is it good for? Can I delete it?

A key difference between the "postgres" DB and a default table is that
the "postgres" DB is very convenient with PostgreSQL's default of
connecting to a DB of the same name as the user.

We don't have a corresponding

INSERT VALUES (42);

SELECT fred;

where there is some invisible implicit table name.

Personally I wish Pg was able to start w/o "connecting" to any specific
DB, but that's just not how the architecture works, and with that
limitation the "postgres" DB seemed like a good compromise. (That said,
it's really weird that the username of the superuser defaults to the
current unix user when initdb'ing, but the db created by default is
still always "postgres").

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 23 April 2014 07:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

By now, some of you will be doubled over laughing as if this is an
April fool joke.

Indeed.

I do like to share the odd joke now and then, it has to be said. So
I'm glad I enriched your day.

I was taught that ideas are accepted in this order: first we think
them a joke, then we perceive them as a threat, then they become
obvious. I can't find a Wikipedia article to give that idea more
weight (OK, that was a joke).

Just trying to think about how to improve the out of the box
experience in ways that others already consider obvious.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Craig Ringer wrote:

Good that you mention that! I have wondered what to do with it.
When I first connected to PostgreSQL, I created a sample table, but the
senior developer from the other office told me that this is the "postgres"
database and that I shouldn't create any objects there.

What is it good for? Can I delete it?

A key difference between the "postgres" DB and a default table is that
the "postgres" DB is very convenient with PostgreSQL's default of
connecting to a DB of the same name as the user.

I did not seriously want to dispute the value of the "postgres" DB,
I just think that making things easier to understand for the newbie
is *not* its greatest merit.

It is mostly for the convenience of the administrator, right?

Yours,
Laurenz Albe

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);
COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
design your database';

The purpose of this is to make the database immediately usable. By
including this table in the default initdb it will mean that programs
can rely on the existence of this table and begin working quicker.

I'm not quite sure it would serve the same purpose as to what you're
proposing here, but for a long time I've thought that it would be nice if
PostgreSQL came with an example database that had a number of tables,
perhaps that mock up some easy to relate to real-world application. These
would be very useful to use as examples in the documents instead of
inventing them in the ad-hoc way that we currently do. Like here:
http://www.postgresql.org/docs/9.3/static/tutorial-window.html

In the above link we have some table called empsalary, but the new user
can't go an execute that query to test it without first working out how to
first create a table and insert some data into that table.

It would be really nice if new users could create this example database
somehow and then they could play around with the example queries we put in
the manual.

Regards

David Rowley

On 04/23/2014 08:11 AM, Simon Riggs wrote:

We start with a database called Postgres and a schema called Public.
Yet we don't start up with any usable tables.

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);
COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
design your database';

The purpose of this is to make the database immediately usable.

With default access controls it still aint :(

And most of complaints I have heard are about the need of
fidgeting with pg_hba.conf to get *anything* started.

So maybe we could start with something like this at the end of initdb:

createdb example

psql example

sample# create table stuff(id serial primary key, data jsonb);
sample# grant all on stuff to public;

And also have the following lines in pg_hba.conf for it

local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust

This would solve most of the frustration with starting pg development
for newcomers

Cheers
Hannu

By
including this table in the default initdb it will mean that programs
can rely on the existence of this table and begin working quicker.

By now, some of you will be doubled over laughing as if this is an
April fool joke. I don't mean it to be at all.

The idea is to have a stupidly obvious and easy table that will
potentially be usable by just about everyone, in any language.

If you don't like it, don't use it. If you really dislike it, drop it.

But for new people coming to Postgres, they will have a data object to
access and begin using the database immediately. Their code will work,
their examples will work. OK, so they need to go back and think about
the design, but at least they got it to work and will be encouraged to
do more.

Remember when we didn't have a database called Postgres? Remember how
much simpler life is now? Remember that now.

We can also insert a single row, Id = 0 with "Postgres sample data" in
it, but that seems a step too far.

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 23 April 2014 09:26, David Rowley <dgrowleyml@gmail.com> wrote:

On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs <simon@2ndquadrant.com> wrote:

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);
COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
design your database';

The purpose of this is to make the database immediately usable. By
including this table in the default initdb it will mean that programs
can rely on the existence of this table and begin working quicker.

I'm not quite sure it would serve the same purpose as to what you're
proposing here, but for a long time I've thought that it would be nice if
PostgreSQL came with an example database that had a number of tables,
perhaps that mock up some easy to relate to real-world application. These
would be very useful to use as examples in the documents instead of
inventing them in the ad-hoc way that we currently do. Like here:
http://www.postgresql.org/docs/9.3/static/tutorial-window.html

+1 to the idea of an example database, used throughout the docs

Sounds like a summer of code project.

Since we don't have that now, it won't work for 9.4.

I still like the idea of a database installed by default on initdb, by
default. Packagers can of course do what they like.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

This seems like a much better idea - whereas a single table, related to
nothing - on the other hand, is at best not very helpful (and it could
be argued, might contribute to teaching poor data data design).

Regards

Mark

On 23/04/14 19:13, Pavel Stehule wrote:

Hello

if you are thinking about this direction, then store there some demo
project.

I am don't think so isolated table has significant price.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

* Simon Riggs (simon@2ndQuadrant.com) wrote:

+1 to the idea of an example database, used throughout the docs
Sounds like a summer of code project.

Agreed. I'll add it to the GSoC ideas page.

Since we don't have that now, it won't work for 9.4.

None of this is on the table for 9.4 as far as I'm concerned..

I still like the idea of a database installed by default on initdb, by
default. Packagers can of course do what they like.

I fail to see the point of adding something that's targetted at novice /
end-users which 90% (yes, it's a random # that I pulled, but it's surely
the majority, at least) of installs won't have.

For my 2c, it'd also be a disservice to our users and to ourselves to
encourage a design that minimizes the database's understanding of the
data and greatly reduces the set of PG's capabilities that can be used.

Thanks,

Stephen

On 04/23/2014 03:28 PM, Stephen Frost wrote:

* Simon Riggs (simon@2ndQuadrant.com) wrote:

+1 to the idea of an example database, used throughout the docs
Sounds like a summer of code project.

Agreed. I'll add it to the GSoC ideas page.

I don't think this is a good GSoC project. Documentation-only projects
are not eligible for GSoC
(https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work).
Perhaps you can argue that a sample database is not documentation, but
it's certainly not in the spirit of the program.

- Heikki

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 23 April 2014 13:46, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:

On 04/23/2014 03:28 PM, Stephen Frost wrote:

* Simon Riggs (simon@2ndQuadrant.com) wrote:

+1 to the idea of an example database, used throughout the docs
Sounds like a summer of code project.

Agreed. I'll add it to the GSoC ideas page.

I don't think this is a good GSoC project. Documentation-only projects are
not eligible for GSoC
(https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work).
Perhaps you can argue that a sample database is not documentation, but it's
certainly not in the spirit of the program.

Out of curiosity, had anyone seen this which some of us were briefly
working on last year at PgCon's unconference?

https://github.com/pvh/postgresql-sample-database

--
Thom

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Heikki,

* Heikki Linnakangas (hlinnakangas@vmware.com) wrote:

On 04/23/2014 03:28 PM, Stephen Frost wrote:

Agreed. I'll add it to the GSoC ideas page.

I don't think this is a good GSoC project. Documentation-only
projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work).

Ah, right, of course. Sorry, I've gotten to thinking of the GSoC page
as being a "TODO" but for small projects. :)

Perhaps you can argue that a sample database is not documentation,
but it's certainly not in the spirit of the program.

Nah, that wasn't my intent at all.

Thanks,

Stephen

On Wed, Apr 23, 2014 at 10:11 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

We start with a database called Postgres and a schema called Public.
Yet we don't start up with any usable tables.

I propose we add a single table called Postgres when we Initdb

CREATE TABLE Postgres (Id Integer, Data Jsonb);
COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
design your database';

The purpose of this is to make the database immediately usable. By
including this table in the default initdb it will mean that programs
can rely on the existence of this table and begin working quicker.

By now, some of you will be doubled over laughing as if this is an
April fool joke. I don't mean it to be at all.

I can propose contrib PostgreNoSQL providing following:
1) Table postgres as you proposed.
2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id
integer, data jsonb) returns void, search_postgres(query jsonb) returns
setof postgres. search_postgres will have semantics of @> jsonb operator
3) Background workers which provides HTTP wrapper over those functions.

------
With best regards,
Alexander Korotkov.

On Wed, Apr 23, 2014 at 4:50 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On 23 April 2014 07:14, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

By now, some of you will be doubled over laughing as if this is an
April fool joke.

Indeed.

I do like to share the odd joke now and then, it has to be said. So
I'm glad I enriched your day.

I was taught that ideas are accepted in this order: first we think
them a joke, then we perceive them as a threat, then they become
obvious. I can't find a Wikipedia article to give that idea more
weight (OK, that was a joke).

Just trying to think about how to improve the out of the box
experience in ways that others already consider obvious.

Mahatma Gandhi said:

"First they ignore you, then they laugh at you, then they fight you, then
you win."

;-)

More info in [1]http://en.wikiquote.org/wiki/Mahatma_Gandhi [2]http://www.brainyquote.com/quotes/quotes/m/mahatmagan103630.html.

Regards,

[1]: http://en.wikiquote.org/wiki/Mahatma_Gandhi
[2]: http://www.brainyquote.com/quotes/quotes/m/mahatmagan103630.html

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

On 04/23/2014 07:43 AM, Alexander Korotkov wrote:

I can propose contrib PostgreNoSQL providing following:
1) Table postgres as you proposed.
2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id
integer, data jsonb) returns void, search_postgres(query jsonb) returns
setof postgres. search_postgres will have semantics of @> jsonb operator
3) Background workers which provides HTTP wrapper over those functions.

You're forgetting ... sharding/replication over multiple masters.

Also, the id should be a text value so that folks can use hash keys, or
whatever.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Josh Berkus wrote:

On 04/23/2014 07:43 AM, Alexander Korotkov wrote:

I can propose contrib PostgreNoSQL providing following:
1) Table postgres as you proposed.
2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id
integer, data jsonb) returns void, search_postgres(query jsonb) returns
setof postgres. search_postgres will have semantics of @> jsonb operator
3) Background workers which provides HTTP wrapper over those functions.

You're forgetting ... sharding/replication over multiple masters.

Also, the id should be a text value so that folks can use hash keys, or
whatever.

We should totally have a type "uuidserial".

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers