Re: [GENERAL] [NOVICE] Fwd: Process scheduling in postgres

[Sameer Kumar]

On Fri, Apr 22, 2016 at 5:21 PM raghu vineel  wrote:

>
> Hi,
>
> I have a 4 core CPU for postgres and I have submitted 6 queries parallely
> in 6 different sessions. But I am seeing only 1 query per CPU and the
> remainig queries are queued. I am not sure why is it happening. Do you have
> any special scheduler for postgres? I can see that CPU has been shared with
> other non postgres processes but postgres processes are not sharing any CPU
> within a core. The following is the process states.
>
>
> *lms   2868  2209 98 07:20 ?00:02:30 postgres: lms controller
> [local] SELECTlms   2869  2209 98 07:20 ?00:02:30 postgres: lms
> controller [local] SELECT*
> lms   2870  2209  0 07:20 ?00:00:00 postgres: lms controller
> [local] SELECT
> lms   2871  2209  0 07:20 ?00:00:00 postgres: lms controller
> [local] SELECT
> lms   2872  2209  0 07:20 ?00:00:00 postgres: lms controller
> [local] SELECT
> *lms   2873  2209 98 07:20 ?00:02:30 postgres: lms controller
> [local] SELECT*
>
> If you see above, three Select queries have been queued. They are
> acquiring CPU only when there is a core without any postgres query running
> on it.
>
> I know the postgres limitation of one query using only one core. But is
> there any limitation of one CPU running only one query at a time?
>
>
Are all the queries related to the same table(s)?
Which version of PostgreSQL? What's the version of OS?

Check out the locks which are not granted yet - pg_locks.granted


> Your help is appreciated.
>
> Thanks and regards,
> Vineel.
>
> --
--
Best Regards
Sameer Kumar | DB Solution Architect
*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533

T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Shulgin, Oleksandr]

On Apr 22, 2016 19:46, "Melvin Davidson"  wrote:
>
>
> First, tahnk you for your feedback Alex.
>
> "IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall."

I find yor lack of proper email quoting skills disturbing...

> Actually, there is no harm in this. If a database is dumped and restored
to a new database, then there is no need to reset the value of relcreate,
as it is a copy of the original db.

Yes, but that means that the timestamps must be part of the dump file,
which means in turn they can be altered before the restore or--if
implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
point in time, so there is little to zero value in having the timestamps in
the first place. This was already discussed in earlier threads.

> If restoring to the same database. then, by definition, it is a data only
restore, as objects are already in existence.

OK

> There is no need to touch anyplace other than pg_class to capture when an
object is created, and
> leaving relcreated NULL for existing objects has no negative effect.

That is one way to think about it, I do not necessarily agree with it.

> "don't buy the example of "DELETE/DROP TABLE" based on relcreated field.
Do you, by chance, have any other use case?"
>
> Yes, it would greatly assist DBA's in tracking down objects created
outside normal hours/days.
> eg: SELECT * FROM pg_class WHERE EXTRACT(DOW FROM relcreated) IN (0,6);

I hope you do not intend to drop the objects found in this way without a
review? What if such an object was created by a user which is in a
geographically distant location compared to the server and it was still
Friday there, but it was already Saturday on the server's clock? You likely
need to know the user name in addition to the timestamp, so you can verify
the user's reasons.

> I also mentioned that this is already in the catalogs of Oracle and SQL
Server.

This sort of argument doesn't help the discussion: there should be a good
reason to add the feature and merely pointing out that others already doing
that is not a good reason, IMO.

> "Apart from created timestamp would you not like to also know the
user/role who has created it?  What about updates (using ALTER
TABLE)--would you want to know when that *last* happened and who did that?
Would you want to know what exactly was altered?  Would you want to know
the history *before* the last update?  Finally, if someone drops the table,
you can say good bye to its pg_catalog records and there's no hope to know
who did that and when (or if that table has even existed to start with)."
>
> At this point, I am only interested in capturing the creation of
unauthorized objects by rogue users.

If rogue users can create objects in your schema you have bigger problems
than tables created outside of working hours (and frankly, I don't see any
harm in that). Can your rogue users also drop tables?

Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

> If the query shows objects created at suspicious times. A further review
of the logs would reveal necessary
> additional info. Yes, it would be good to capture ALTER's also, but that
complicates things, so I am only
> looking for a simple, safe change.

If you are going to review the logs anyway, why not just set
log_statements=ddl and use logs as the source of timestamp data (together
with user, connection details, etc.)?

> I really wish people would stop focusing on when features will not work
and consider more of the benefit they will gain from the situations where
they do work.
> I also cannot understand why people are paranoid about adding a simple
nullable timestamp column to a system catalog,

There yet to be found a good reason to do that. No one is going to make
even a "simple change" just for you to try and see the idea fails in
practice due to all the other things you didn't think about. But you can
make such a change for yourself and run a patched version for a while if
you want--no one can stop you here.

> especially since no one gave any
> thought to the adverse effect caused by renaming a column ( procpid to
pid) in pg_stat_activity when going from 9.1 to 9.2. I bet more than a few
DBA's had to
> change their scripts that monitored activity.

Well, I didn't participate in that change discussion and I agree it is a
bit annoying then these view change the column names.

--
Alex

Re: [GENERAL] Proper relational database?

[david]

> So, let's just flat-out ask.
> 
> Dear Important People:  would the PostgreSQL project consider supporting
> other query languages? Or creating a plug-in mechanism for them, so that
> alternative interface languages could be added without changing the base
> code?

I very much doubt it. The use case has to be established first.

As it happens, the existing PL extension capability provides enough to get an 
alternative query language (such as Andl) to work. That's why I chose Postgres.

Making it a 'native' would be not so hard if there is real demand.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org







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

Re: [GENERAL] Proper relational database?

[Thomas Munro]

On Sat, Apr 23, 2016 at 2:04 PM,   wrote:
>> Why is starting at a low level important?  A database is truly relational to
>> the extent that it implements the relational model. If you don't want the
>> database to allow tables without keys, or to allow null values, don't let
>> people create them. If the underlying machinery allows them, that seems like
>> a mere performance issue; worrying about that from the outset seems like a
>> perfect example of premature optimization. If PostgreSQL's performance is
>> acceptable now, why wouldn't it be acceptable with a different interface
>> language?
>
> Agreed.
>
>> There are other aspects of what would make a truly relational database, of
>> course. Codd's 0th rule, for instance, that the "system must be able to
>> manage data bases entirely through its relational capabilities" to me says
>> that there should be no data definition language, except as syntactic sugar
>> for relational operations. So you'd create users (thousands in one command,
>> if you liked) by adding tuples to a base relation.
>
> Yes, maybe, but that makes it not part of the language. You can't apply rule 
> 0 to things like creating a type or operator in a language.
>
>> But which things are important? I think a good many of the things one might
>> would be lower-hanging fruit than that. Just having a clean query language
>> would alleviate a lot of (my) discomfort.
>
> Andl is that.
>
>> > I don't know if Postgres exposes the lower-level stuff to plugins or
>> > not — it would be nice if this could be an alternative query language
>> > for Postgres itself,
>>
>> Well, the parser doesn't, but as best I can tell it's also somewhat loosely
>> coupled from the system. It doesn't do table access, for instance.  It builds
>> and returns a parse tree.  There's no reason you couldn't parse a different
>> language and return a tree of the same type.  Or you could just translate
>> your input language into SQL, and pass it along to the existing parser.
>
> I looked into that, and it's too hard as a starting place. There is too much 
> of the tree and the subsequent query planning that is hooked into specific 
> features of SQL. Instead, Andl generates a small subset of SQL. Once each 
> query has been generated and parsed, the prepared statements can be cached 
> and you get most of the benefits.
>
>> > but the assumptions about the two worlds (SQL vs a properly relational
>> > store) are probably too different.
>
>> Are there relational algebra expressions, or other operations necessary to a
>> truly relational database, which cannot be translated into SQL?  I'm not
>> aware that there are, but I'd be interested to hear of it.  If there were,
>> there's a good chance you wouldn't be able to translate them into the parse
>> tree, either.
>
> Absolutely not. SQL is a (nearly) full implementation of the relational 
> algebra, plus other non-relational stuff.  The only thing it really can't 
> handle is a table with no columns! (I have to fake that)

FWIW standard SQL may not allow it but Postgres does, and it's even
possible to exclude duplicates by using an expression that references
the whole row.

postgres=# select;
┌──┐
├──┤
└──┘
(1 row)
postgres=# create table dum ();
CREATE TABLE
postgres=# select * from dum;
┌──┐
├──┤
└──┘
(0 rows)
postgres=# create unique index dum_unique on dum((dum));
CREATE INDEX
postgres=# insert into dum select;
INSERT 0 1
postgres=# select * from dum;
┌──┐
├──┤
└──┘
(1 row)
postgres=# insert into dum select;
ERROR:  duplicate key value violates unique constraint "dum_unique"
DETAIL:  Key ((dum.*))=(()) already exists.

-- 
Thomas Munro
http://www.enterprisedb.com


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

Re: [GENERAL] Proper relational database?

[david]

> On Fri, Apr 22, 2016 at 10:45 PM, John R Pierce  wrote:
> > a table with no columns would have no primary key...  doesn't that
> > violate one of the fundamental tenets of the relational model ?
> 
> Not as I understand it.  A relation must have at least one candidate key.
> That will be the set of all the fields, if no proper subset qualifies.
> Calling one key "primary" is merely convention, so far as I am aware (talking
> relational theory, here, not how databases regard primary keys).

This is a 'soft' requirement. If there is no other key, then the set of all 
attributes is the key.

> In a table with no columns, the only candidate key is the set of all fields,
> which is the empty set.  If you want to call that the primary key, it
> shouldn't be a problem.  The tuples (all 0 of them) are guaranteed to be
> unique.

The relation with no attributes may have a tuple, which itself has no 
attributes. Thus there are two such relations, one empty and one of degree one. 
They can be referred to as DUM and DEE, or as false and true. See 
http://c2.com/cgi/wiki?TableDum for example.

Many experienced users of SQL are aware of situations where they are useful. In 
Andl they are automatically available as literals.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org







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

Re: [GENERAL] Proper relational database?

[Raymond Brinzer]

On Fri, Apr 22, 2016 at 10:45 PM, John R Pierce  wrote:
> a table with no columns would have no primary key...  doesn't that violate
> one of the fundamental tenets of the relational model ?

Not as I understand it.  A relation must have at least one candidate
key.  That will be the set of all the fields, if no proper subset
qualifies. Calling one key "primary" is merely convention, so far as I
am aware (talking relational theory, here, not how databases regard
primary keys).

In a table with no columns, the only candidate key is the set of all
fields, which is the empty set.  If you want to call that the primary
key, it shouldn't be a problem.  The tuples (all 0 of them) are
guaranteed to be unique.

-- 
Ray Brinzer


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

Re: [GENERAL] Proper relational database?

[John R Pierce]

On 4/22/2016 7:04 PM, da...@andl.org wrote:

Absolutely not. SQL is a (nearly) full implementation of the relational 
algebra, plus other non-relational stuff. The only thing it really can't handle 
is a table with no columns! (I have to fake that)



a table with no columns would have no primary key...  doesn't that 
violate one of the fundamental tenets of the relational model ?




--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Proper relational database?

[david]

> Why is starting at a low level important?  A database is truly relational to
> the extent that it implements the relational model. If you don't want the
> database to allow tables without keys, or to allow null values, don't let
> people create them. If the underlying machinery allows them, that seems like
> a mere performance issue; worrying about that from the outset seems like a
> perfect example of premature optimization. If PostgreSQL's performance is
> acceptable now, why wouldn't it be acceptable with a different interface
> language?

Agreed.

> There are other aspects of what would make a truly relational database, of
> course. Codd's 0th rule, for instance, that the "system must be able to
> manage data bases entirely through its relational capabilities" to me says
> that there should be no data definition language, except as syntactic sugar
> for relational operations. So you'd create users (thousands in one command,
> if you liked) by adding tuples to a base relation.

Yes, maybe, but that makes it not part of the language. You can't apply rule 0 
to things like creating a type or operator in a language.

> But which things are important? I think a good many of the things one might
> would be lower-hanging fruit than that. Just having a clean query language
> would alleviate a lot of (my) discomfort.

Andl is that.

> > I don't know if Postgres exposes the lower-level stuff to plugins or
> > not — it would be nice if this could be an alternative query language
> > for Postgres itself,
> 
> Well, the parser doesn't, but as best I can tell it's also somewhat loosely
> coupled from the system. It doesn't do table access, for instance.  It builds
> and returns a parse tree.  There's no reason you couldn't parse a different
> language and return a tree of the same type.  Or you could just translate
> your input language into SQL, and pass it along to the existing parser.

I looked into that, and it's too hard as a starting place. There is too much of 
the tree and the subsequent query planning that is hooked into specific 
features of SQL. Instead, Andl generates a small subset of SQL. Once each query 
has been generated and parsed, the prepared statements can be cached and you 
get most of the benefits.

> > but the assumptions about the two worlds (SQL vs a properly relational
> > store) are probably too different.

> Are there relational algebra expressions, or other operations necessary to a
> truly relational database, which cannot be translated into SQL?  I'm not
> aware that there are, but I'd be interested to hear of it.  If there were,
> there's a good chance you wouldn't be able to translate them into the parse
> tree, either.

Absolutely not. SQL is a (nearly) full implementation of the relational 
algebra, plus other non-relational stuff. The only thing it really can't handle 
is a table with no columns! (I have to fake that)

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org






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

Re: [GENERAL] Proper relational database?

[david]

Eventual consistency is not part of the language, so outside the scope of Andl.

 

Easy distribution depends on a standardised language. SQL is a definite fail. 
There is only one Andl and it works identically on all platforms. That should 
help.

 

Why schema-on-demand? Can you explain what you mean by that?

 

Regards

David M Bennett FACS

  _  

Andl - A New Database Language - andl.org

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guyren Howe
Sent: Saturday, 23 April 2016 5:54 AM
To: PostgreSQL General 
Subject: Re: [GENERAL] Proper relational database?

 

The SQL language is terrible but we can live with it.

 

But the answer to "Are there any relational data stores that offer eventual 
consistency, easy distribution, schema-on-demand or any such things a large 
modern application can use?" appears to be no. And that's just awful.

 

On Apr 22, 2016, at 12:40 , David G. Johnston  > wrote:

 

On Fri, Apr 22, 2016 at 12:25 PM, Raymond Brinzer  > wrote:

So, let's just flat-out ask.

Dear Important People:  would the PostgreSQL project consider
supporting other query languages? Or creating a plug-in mechanism for
them, so that alternative interface languages could be added without
changing the base code?

 

​

If by important you mean possessing a commit-bit then I don't count...but for 
me, such a project would have to gain significant adoption as a fork of the 
PostgreSQL code base before it would ever be considered for take-over by the 
mainline project.

​

​David J.​

 

 

Re: [GENERAL] Proper relational database?

[david]

Andl is a "proper relational database" language.

Andl is an original implementation of the language D described in The Third 
Manifesto. http://www.dcs.warwick.ac.uk/~hugh/TTM/. 

I have a working implementation of Andl on Postgres. See 
http://www.andl.org/2016/04/postgres-meet-andl/. This version is not ready for 
release, but there is a download that supports Sqlite, Thrift and REST.

The syntax of Andl is quite different from Tutorial D, or SQL. It does what SQL 
does, including any relational query you can think of, and a few you haven't! 
Check out the web site for some code samples.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Paul Jungwirth
> Sent: Saturday, 23 April 2016 5:38 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Proper relational database?
> 
> On 04/21/2016 01:36 PM, Guyren Howe wrote:
> > Anyone familiar with the issue would have to say that the tech world
>  > would be a significantly better place if IBM had developed a real  >
> relational database with an elegant query language
> 
> I'm surprised no one yet has mentioned Tutorial D by C. J. Date. His book
> _Database in Depth_ is pretty much an extended argument for how superior it
> is to SQL. RelDB is apparently an open source implementation of it, and D4 is
> a commercial one. That's about all I know in terms of practically using it
> for something. But Date & Tutorial D seems like a good place to start if SQL
> isn't relational enough for you. The book I mentioned is short and easy to
> read.
> 
> Paul
> 
> 
> 
> 
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

Re: [GENERAL] Proper relational database?

[Manuel Gómez]

On Fri, Apr 22, 2016 at 3:07 PM, Paul Jungwirth
 wrote:
> I'm surprised no one yet has mentioned Tutorial D by C. J. Date. His book
> _Database in Depth_ is pretty much an extended argument for how superior it
> is to SQL. RelDB is apparently an open source implementation of it, and D4
> is a commercial one. That's about all I know in terms of practically using
> it for something. But Date & Tutorial D seems like a good place to start if
> SQL isn't relational enough for you. The book I mentioned is short and easy
> to read.

This is a relevant project: https://github.com/agentm/project-m36


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

Re: [GENERAL] Fwd: Process scheduling in postgres

[John R Pierce]

On 4/22/2016 1:30 AM, raghu vineel wrote:


I have a 4 core CPU for postgres and I have submitted 6 queries 
parallely in 6 different sessions. But I am seeing only 1 query per 
CPU and the remainig queries are queued. I am not sure why is it 
happening. Do you have any special scheduler for postgres? I can see 
that CPU has been shared with other non postgres processes but 
postgres processes are not sharing any CPU within a core. The 
following is the process states.

*
lms   2868  2209 98 07:20 ?00:02:30 postgres: lms 
controller [local] SELECT
lms   2869  2209 98 07:20 ?00:02:30 postgres: lms 
controller [local] SELECT*
lms   2870  2209  0 07:20 ?00:00:00 postgres: lms 
controller [local] SELECT
lms   2871  2209  0 07:20 ?00:00:00 postgres: lms 
controller [local] SELECT
lms   2872  2209  0 07:20 ?00:00:00 postgres: lms 
controller [local] SELECT
*lms   2873  2209 98 07:20 ?00:02:30 postgres: lms 
controller [local] SELECT*


If you see above, three Select queries have been queued. They are 
acquiring CPU only when there is a core without any postgres query 
running on it.


I know the postgres limitation of one query using only one core. But 
is there any limitation of one CPU running only one query at a time?


no such limitation.  any chance there's locking involved here?  take a 
look at pg_locks and pg_stat_activity the next time you run such a test.



--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Guyren Howe]

On Apr 22, 2016, at 15:03 , Merlin Moncure  wrote:
> 
> On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra  
> wrote:
>> I'm finally having performance issues with PostgreSQL when doing big
>> analytics queries over almost the entire database of more than 100gb of
>> data.
>> 
>> And what I keep reading all over the web is many databases switching to
>> columnar store (RedShift, Cassandra, cstore_fdw, etc) and having great
>> performance on queries in general and giant boosts with big analytics
>> queries.
>> 
>> I wonder if there is any plans to move postgresql entirely to a columnar
>> store (or at least make it an option), maybe for version 10?
>> 
>> The current extensions are rather limited (types support for example) and
>> require quite some configuration and data migration to work, besides they
>> don't work in services like AWS RDS.
> 
> Column stores are better at one case (selecting a few columns from a
> very wide table) and worse at just about every other case.  Also,
> beware database benchmarks -- as they say, there is no free lunch
> There is a reason why databases store things in rows.
> 
> Analytics in traditional postgres tables is definitely possible, but
> you have to be smart.

There are tradeoffs; a column store is faster at queries that select a subset 
of columns. The *big* tradeoff is that insert time increases linearly with the 
number of columns. Queries that pull a large subset of the columns can also be 
slower.

I would quite like to set a table to columnar in Postgres, but really you can 
achieve much the same thing with multiple tables in a 1:1 relationship, so I 
don't think this would be worth putting much effort into.



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

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[Merlin Moncure]

On Thu, Apr 21, 2016 at 5:08 AM, Bráulio Bhavamitra  wrote:
> Hi all,
>
> I'm finally having performance issues with PostgreSQL when doing big
> analytics queries over almost the entire database of more than 100gb of
> data.
>
> And what I keep reading all over the web is many databases switching to
> columnar store (RedShift, Cassandra, cstore_fdw, etc) and having great
> performance on queries in general and giant boosts with big analytics
> queries.
>
> I wonder if there is any plans to move postgresql entirely to a columnar
> store (or at least make it an option), maybe for version 10?
>
> The current extensions are rather limited (types support for example) and
> require quite some configuration and data migration to work, besides they
> don't work in services like AWS RDS.

Column stores are better at one case (selecting a few columns from a
very wide table) and worse at just about every other case.  Also,
beware database benchmarks -- as they say, there is no free lunch
There is a reason why databases store things in rows.

Analytics in traditional postgres tables is definitely possible, but
you have to be smart.

merlin


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

[GENERAL] Fwd: Process scheduling in postgres

[raghu vineel]

Hi,

I have a 4 core CPU for postgres and I have submitted 6 queries parallely
in 6 different sessions. But I am seeing only 1 query per CPU and the
remainig queries are queued. I am not sure why is it happening. Do you have
any special scheduler for postgres? I can see that CPU has been shared with
other non postgres processes but postgres processes are not sharing any CPU
within a core. The following is the process states.


*lms   2868  2209 98 07:20 ?00:02:30 postgres: lms controller
[local] SELECTlms   2869  2209 98 07:20 ?00:02:30 postgres: lms
controller [local] SELECT*
lms   2870  2209  0 07:20 ?00:00:00 postgres: lms controller
[local] SELECT
lms   2871  2209  0 07:20 ?00:00:00 postgres: lms controller
[local] SELECT
lms   2872  2209  0 07:20 ?00:00:00 postgres: lms controller
[local] SELECT
*lms   2873  2209 98 07:20 ?00:02:30 postgres: lms controller
[local] SELECT*

If you see above, three Select queries have been queued. They are acquiring
CPU only when there is a core without any postgres query running on it.

I know the postgres limitation of one query using only one core. But is
there any limitation of one CPU running only one query at a time?

Your help is appreciated.

Thanks and regards,
Vineel.

Re: [GENERAL] Columnar store as default for PostgreSQL 10?

[jje]

An interesting topic we have also discussed in our team.

Realistically - this is more about picking the right software for the job.

PostgreSQL has come so far up in it's performance for more general workloads
that it is fast becoming a bit of a darling in the world of cloud - being
able to handle lots of db's and their associated web (fast response
required) queries. So to start to take it down this path I believe would be
detrimental.

There are plenty of DB's out there designed for exactly what you are trying
to do - so maybe a better option is to build yourself someway of having data
stored in another system where you require this sort of data handling.

Some of us are already doing that with great success - so maybe asking how
you accomplish that would be a better question.

Jonathan




--
View this message in context: 
http://postgresql.nabble.com/Columnar-store-as-default-for-PostgreSQL-10-tp5899731p5899973.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Proper relational database?

[David G. Johnston]

On Fri, Apr 22, 2016 at 12:54 PM, Guyren Howe  wrote:

> The SQL language is terrible but we can live with it.
>

​If anything, and especially something as pervasive and esoteric as SQL,
makes you question your desire to live please get help.​


> But the answer to "Are there any relational data stores that offer
> eventual consistency, easy distribution, schema-on-demand or any such
> things a large modern application can use?" appears to be no. And that's
> just awful.
>
>
​The axiom "a bird in hand is worth two in the bush" comes to mind here.
This applies even if the bird-in-hand is ugly and the glimpses of the ones
in the bush indicates they are beautiful.

David J.

Re: [GENERAL] Proper relational database?

[Joshua D. Drake]

On 04/22/2016 12:25 PM, Raymond Brinzer wrote:

So, let's just flat-out ask.

Dear Important People:  would the PostgreSQL project consider
supporting other query languages? Or creating a plug-in mechanism for
them, so that alternative interface languages could be added without
changing the base code?


Probably not considering our mission.

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


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

Re: [GENERAL] Proper relational database?

[Guyren Howe]

The SQL language is terrible but we can live with it.

But the answer to "Are there any relational data stores that offer eventual 
consistency, easy distribution, schema-on-demand or any such things a large 
modern application can use?" appears to be no. And that's just awful.

> On Apr 22, 2016, at 12:40 , David G. Johnston  
> wrote:
> 
> On Fri, Apr 22, 2016 at 12:25 PM, Raymond Brinzer  > wrote:
> So, let's just flat-out ask.
> 
> Dear Important People:  would the PostgreSQL project consider
> supporting other query languages? Or creating a plug-in mechanism for
> them, so that alternative interface languages could be added without
> changing the base code?
> 
> ​If by important you mean possessing a commit-bit then I don't count...but 
> for me, such a project would have to gain significant adoption as a fork of 
> the PostgreSQL code base before it would ever be considered for take-over by 
> the mainline project.
> ​
> ​David J.​
> 

Re: [GENERAL] Proper relational database?

[David G. Johnston]

On Fri, Apr 22, 2016 at 12:25 PM, Raymond Brinzer 
wrote:

> So, let's just flat-out ask.
>
> Dear Important People:  would the PostgreSQL project consider
> supporting other query languages? Or creating a plug-in mechanism for
> them, so that alternative interface languages could be added without
> changing the base code?
>

​
If by important you mean possessing a commit-bit then I don't count...but
for me, such a project would have to gain significant adoption as a fork of
the PostgreSQL code base before it would ever be considered for take-over
by the mainline project.
​
​David J.​

Re: [GENERAL] Proper relational database?

[Paul Jungwirth]

On 04/21/2016 01:36 PM, Guyren Howe wrote:

Anyone familiar with the issue would have to say that the tech world

> would be a significantly better place if IBM had developed a real
> relational database with an elegant query language

I'm surprised no one yet has mentioned Tutorial D by C. J. Date. His 
book _Database in Depth_ is pretty much an extended argument for how 
superior it is to SQL. RelDB is apparently an open source implementation 
of it, and D4 is a commercial one. That's about all I know in terms of 
practically using it for something. But Date & Tutorial D seems like a 
good place to start if SQL isn't relational enough for you. The book I 
mentioned is short and easy to read.


Paul







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

Re: [GENERAL] Proper relational database?

[Raymond Brinzer]

So, let's just flat-out ask.

Dear Important People:  would the PostgreSQL project consider
supporting other query languages? Or creating a plug-in mechanism for
them, so that alternative interface languages could be added without
changing the base code?


On Thu, Apr 21, 2016 at 4:36 PM, Guyren Howe  wrote:
> Anyone familiar with the issue would have to say that the tech world would be 
> a significantly better place if IBM had developed a real relational database 
> with an elegant query language rather than the awful camel of a thing that is 
> SQL.
>
> If I had a few $million to spend in a philanthropical manner, I would hire 
> some of the best PG devs to develop a proper relational database server. 
> Probably a query language that expressed the relational algebra in a 
> scheme-like syntax, and the storage model would be properly relational (eg no 
> duplicate rows).
>
> It's an enormous tragedy that all the development effort that has gone into 
> NoSQL database has pretty much all gotten it wrong: by all means throw out 
> SQL, but not the relational model with it. They're all just rehashing the 
> debate over hierarchical storage from the 70s. Comp Sci courses should 
> feature a history class.
>
> It's a bit odd to me that someone isn't working on such a thing.
>
> Just curious what folks here have to say…
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
Ray Brinzer


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

Re: [GENERAL] Enhancement request for pg_dump

[Adrian Klaver]

On 04/22/2016 11:16 AM, Pierre Chevalier Géologue wrote:

Le 22/04/2016 19:11, Adrian Klaver a écrit :

Last time I had to do this kind of exercise, a few years ago, I was in a
remote place without Internet access, so I could not get any information
or ask any help.  I was kind of surprised/frustrated by the (apparent)
lack of order of the pg_dump output.  So I manually wrote scripts to
export the tables and views' definitions separately, one by one, (using
pg_dump, of course) and stack them in the order I wished into a large
file.  That was quite suboptimal, but it worked as expected, and I was
able to diff and patch correctly.


You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html


Yes, thanks for the advice.  Now I remember that I had used it also: I
just found these notes in my numeric attic:

   # pierre@autan: ~< 2013_08_17__17_00_23 >
pg_restore -l database_2013_08_14_20h34.pg_dump -n pierre >
tt_schema_pierre


Yea, you can also use the filtering switches to create a filtered TOC, so:

pg_restore -l -s test.out > test_s.toc

Now the TOC has only a summary line of what is being done, but it is 
easy enough to feed it back to pg_restore and have it restore to a plain 
text file instead of a database:


pg_restore -L test_s.toc  -f test_s.sql




   # pierre@autan: ~< 2013_08_17__17_00_23 >
vi tt_schema_pierre

   # pierre@autan: ~< 2013_08_17__17_00_23 >
cat tt_schema_pierre
DROP VIEW IF EXISTS pierre.dh_collars;
DROP VIEW IF EXISTS pierre.dh_litho;
DROP VIEW IF EXISTS pierre.dh_sampling_grades;
DROP VIEW IF EXISTS pierre.dh_sampling;
DROP VIEW IF EXISTS pierre.topo_points;
DROP VIEW IF EXISTS pierre.baselines;
DROP VIEW IF EXISTS pierre.dh_devia;
...


I just cannot remember why it did not fulfill my needs, so that I rather
opted to pg_dump's.


...

It does not solve all problems but it does do some ordering and is
amenable to be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql
2705; 0 0 COMMENT - EXTENSION plpgsql

...

Thanks, I'll dig a bit more in pg_restore, for my current issues.

Pierre



--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Proper relational database?

[Guyren Howe]

On Apr 22, 2016, at 10:45 , Raymond Brinzer  wrote:
> 
> Are there relational algebra expressions, or other operations
> necessary to a truly relational database, which cannot be translated
> into SQL?  I'm not aware that there are, but I'd be interested to hear
> of it.  If there were, there's a good chance you wouldn't be able to
> translate them into the parse tree, either.

The fundamental storage model needs to at least be a bit different. In 
particular, relations can't allow duplicates. You could have nulls (Codd 
proposed two different forms of null IIRC: a single null value and two 
different null values), although they should be more principled than the mess 
they are in SQL.

I am no expert on database optimization, but I understand that it is 
significantly easier to do query optimization in a properly relational 
database, as it forms a reasonably simple algebra, which can be optimized much 
as you would optimize evaluation of a numeric expression.

Major gains from a proper relational store would be:

- a better language, easier to parse, read and generate. Perhaps multiple 
equivalent query languages;
- other storage models (distributed and eventually consistent, say);
- simpler (in implementation and use);

We may also get some degree of faster and other good things. It also might be 
implemented in such a way that it can run as a server or more like SQLite.

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

Re: [GENERAL] Proper relational database?

[Eric Schwarzenbach]

On 04/22/2016 06:21 AM, David Goodenough wrote:

On Thursday 21 April 2016 13:36:54 Guyren Howe wrote:

Anyone familiar with the issue would have to say that the tech world would
be a significantly better place if IBM had developed a real relational
database with an elegant query language rather than the awful camel of a
thing that is SQL.

If I had a few $million to spend in a philanthropical manner, I would hire
some of the best PG devs to develop a proper relational database server.
Probably a query language that expressed the relational algebra in a
scheme-like syntax, and the storage model would be properly relational (eg
no duplicate rows).

It's an enormous tragedy that all the development effort that has gone into
NoSQL database has pretty much all gotten it wrong: by all means throw out
SQL, but not the relational model with it. They're all just rehashing the
debate over hierarchical storage from the 70s. Comp Sci courses should
feature a history class.

It's a bit odd to me that someone isn't working on such a thing.

Just curious what folks here have to say…

Well when IBM were first developing relational databases there were two
different teams.  One in California which produced System-R which became
what we now know as DB2 and spawned SQL, and the other in Peterlee in
the UK which was called PRTV (the Peterlee Relational Test Vehicle).  PRTV
rather died but bits of it survived.  In particular it was the first to system
to include a relational optimiser.  You can find some details on the PRTV
page in Wikipedia.

It was written in PL/1, although it also used some modified microcode
and therefore some assembler.

It never appeared as a product, but there was a geographical system
which built on top of it which was if I recall corrected used by the Greater
London Council and Central Region Scotland, which did something of
what postgis does for PostgreSQL.

According to the Wikipedia page it did have a language (ISBL) but from what
I recall (and it was nearly 40 years ago) there were a series of PL/1
function calls we used rather than encoding the request as a string
as SQL systems require.

The IBM centre in Peterlee was closed, and the lab moved to Winchester
where I think it still resides.
One of the people involved in that was Hugh Darwen, who is one of the 
authors of The Third Manifesto, which is an attempt to define what a 
properly relational language and system should look like. So you could 
say the experience of ISBL vs SQL has been folded into that effort.





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

Re: [GENERAL] Enhancement request for pg_dump

[Pierre Chevalier Géologue]

Le 22/04/2016 19:11, Adrian Klaver a écrit :

Last time I had to do this kind of exercise, a few years ago, I was in a
remote place without Internet access, so I could not get any information
or ask any help.  I was kind of surprised/frustrated by the (apparent)
lack of order of the pg_dump output.  So I manually wrote scripts to
export the tables and views' definitions separately, one by one, (using
pg_dump, of course) and stack them in the order I wished into a large
file.  That was quite suboptimal, but it worked as expected, and I was
able to diff and patch correctly.


You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html


Yes, thanks for the advice.  Now I remember that I had used it also: I 
just found these notes in my numeric attic:


  # pierre@autan: ~< 2013_08_17__17_00_23 >
pg_restore -l database_2013_08_14_20h34.pg_dump -n pierre > tt_schema_pierre

  # pierre@autan: ~< 2013_08_17__17_00_23 >
vi tt_schema_pierre

  # pierre@autan: ~< 2013_08_17__17_00_23 >
cat tt_schema_pierre
DROP VIEW IF EXISTS pierre.dh_collars;
DROP VIEW IF EXISTS pierre.dh_litho;
DROP VIEW IF EXISTS pierre.dh_sampling_grades;
DROP VIEW IF EXISTS pierre.dh_sampling;
DROP VIEW IF EXISTS pierre.topo_points;
DROP VIEW IF EXISTS pierre.baselines;
DROP VIEW IF EXISTS pierre.dh_devia;
...


I just cannot remember why it did not fulfill my needs, so that I rather 
opted to pg_dump's.



...

It does not solve all problems but it does do some ordering and is amenable to 
be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql
2705; 0 0 COMMENT - EXTENSION plpgsql

...

Thanks, I'll dig a bit more in pg_restore, for my current issues.

Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
  Tél+fax  :09 75 27 45 62
06 37 80 33 64
  Émail  :   pierrechevaliergeolCHEZfree.fr
  icq#   :   10432285
  jabber: pierre.chevalier1...@jabber.fr
  http://pierremariechevalier.free.fr/pierre_chevalier_geologue



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

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Melvin Davidson]

On Fri, Apr 22, 2016 at 12:49 PM, Shulgin, Oleksandr <
oleksandr.shul...@zalando.de> wrote:

> On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson 
> wrote:
>
>> And so far, NO ONE has shown any proof that this enhancement could
>> possibly cause ANY negative result.
>>
>
> Searching through the list archives[1] I can see that you've asked this
> question a number of times already.  And I'm pretty sure it was asked quite
> a number of times by the others.
>
> IMO, every time it was conclusively demonstrated that when you consider
> dump/restore semantics, this feature can have exactly zero value if
> implemented *inside pg_catalog*.  And it would have to be a pretty invasive
> change (it's not enough to just add the attribute, you also need to touch
> probably a dozen of places where it will be populated or read), so without
> any positive effect it results in negative effect overall.
>
>
>> All that has been presented so far are corner cases where this "might"
>> not be useful.
>> If the PostgreSQL developers are really worried about unexpected
>> drawbacks, then, based on that,  ALL future development should stop
>> immediately.
>> This is total insanity! I am asking for a simple, safe enhancement that
>> would add what compatibility with what is already in other databases, yet
>> everyone seems to be terrified about it.
>> We have already modified system catalogs previously with no ill effect.
>>
>
> I believe system catalogs are modified on a regular basis with every major
> release.  But in every instance there has to be a good reason for a change.
>
> So please, someone present a logical explanation of why this should not be
>> done, or how it will negatively impact the PostgreSQL project.
>> If you cannot do so, then start thinking positively.
>>
>
> As said before a number of times: what you propose looks easy, but it's
> just the tip of an iceberg.  Even if the community comes to an agreement
> what dump/restore semantics should be and it is implemented, the feature is
> still not *that* useful on its own to justify its existence (no, I don't
> buy the example of "DELETE/DROP TABLE" based on relcreated field. Do you,
> by chance, have any other use case?)
>
> Apart from created timestamp would you not like to also know the user/role
> who has created it?  What about updates (using ALTER TABLE)--would you want
> to know when that *last* happened and who did that?  Would you want to know
> what exactly was altered?  Would you want to know the history *before* the
> last update?  Finally, if someone drops the table, you can say good bye to
> its pg_catalog records and there's no hope to know who did that and when
> (or if that table has even existed to start with).
>
> When you just start thinking in this direction, it becomes apparent that a
> proper audit solution is a much better fit to tackle these problems.  There
> are features continuously added in the recent releases that will facilitate
> building such solutions in form of extensions: DDL event triggers and
> Logical decoding, to name a few.
>
> Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
>> to present enhancement requests.
>>
>
> There is plenty of information on PostgreSQL sites about this[2,3,4].  Are
> you suggesting something was add yesterday on top of that?
>
> Now that it has been verified this is the correct list,
>>
>
> Probably it is the most appropriate one, unless you have the patch ready
> (then it would be for -hackers).  I'm still puzzled as to how have you
> found that completely unrelated feature request voting site given the
> abundance of information on the official sites and lack of links to that
> site from there.
>
> It is true that some visibility of what majority of users consider to be
> the most useful enhancement could benefit the project, but it has to be
> maintained by the community in order to provide some value.  Otherwise it
> is going to have only the negative impact: an impression that PostgreSQL
> developers doesn't listen to the users.
>
> There still exists no formal requirements for presenting an enhancement
>> request.
>>
>
> Just follow the requirements for a good problem report, especially[5].
> After all you have a problem of a missing feature, right?
>
>
>> WHY am I being vilified for making a simple request? How is it that
>> developers proceed with other enhancements, yet so much negative attention
>> is being given to my request because of unjustified fear that something
>> bad will happen?
>>
>
> Less colorful^W^W plain text mails without top-posting might help here.
> Seriously, not everyone has the time to present the same arguments over and
> over again: searching the archives should have given you some perspective
> on the destiny of this feature request.
>
> Should we really put this on Todo with a mark that we actually don't want
> it?
>
> Regards,
> --
> Alex
>
> [1] http://www.postgresql.org/search/?m=1=relcreated
> [2] 

Re: [GENERAL] Proper relational database?

[Raymond Brinzer]

On Fri, Apr 22, 2016 at 2:05 AM, Guyren Howe  wrote:
> If I had the time and money to put together a team to do this, I would start
> with the lower-level guts of either Postgres or SQLite (or, heck, MySQL) so
> you had a thing that did BTrees and other data structures on disk and
> indexes, and provide access to that from a high level, portable and
> efficient language. Perhaps Scheme.
>
> Then you could write a high-level relational logic engine on top of that, in
> the high level language, perhaps with the odd bit of C or D or Go for
> anything really critical.

Why is starting at a low level important?  A database is truly
relational to the extent that it implements the relational model. If
you don't want the database to allow tables without keys, or to allow
null values, don't let people create them. If the underlying machinery
allows them, that seems like a mere performance issue; worrying about
that from the outset seems like a perfect example of premature
optimization. If PostgreSQL's performance is acceptable now, why
wouldn't it be acceptable with a different interface language?

There are other aspects of what would make a truly relational
database, of course. Codd's 0th rule, for instance, that the "system
must be able to manage data bases entirely through its relational
capabilities" to me says that there should be no data definition
language, except as syntactic sugar for relational operations. So
you'd create users (thousands in one command, if you liked) by adding
tuples to a base relation.

But which things are important? I think a good many of the things one
might would be lower-hanging fruit than that. Just having a clean
query language would alleviate a lot of (my) discomfort.

> I don't know if Postgres exposes the lower-level stuff to plugins or not —
> it would be nice if this could be an alternative query language for Postgres
> itself,

Well, the parser doesn't, but as best I can tell it's also somewhat
loosely coupled from the system. It doesn't do table access, for
instance.  It builds and returns a parse tree.  There's no reason you
couldn't parse a different language and return a tree of the same
type.  Or you could just translate your input language into SQL, and
pass it along to the existing parser.

> but the assumptions about the two worlds (SQL vs a properly
> relational store) are probably too different.

Are there relational algebra expressions, or other operations
necessary to a truly relational database, which cannot be translated
into SQL?  I'm not aware that there are, but I'd be interested to hear
of it.  If there were, there's a good chance you wouldn't be able to
translate them into the parse tree, either.

-- 
Ray Brinzer


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

Re: [GENERAL] Enhancement request for pg_dump

[Adrian Klaver]

On 04/22/2016 09:44 AM, Pierre Chevalier Géologue wrote:
> Hi,
> Le 18/04/2016 02:26, Sergei Agalakov a écrit :
> 
>> If you never encountered a situation when in the dozens of
>> environments the databases has diverged because somebody has
>> done something manually - good for you, you are lucky guy then.
> 
> I'm definitely not a lucky guy at all! :-)
> And this is happening to me *right now*... My case is a little bit more
> complicated, but I'll come back to this later on, probably in another
> thread, in order to avoid confusion.
> 
> Last time I had to do this kind of exercise, a few years ago, I was in a
> remote place without Internet access, so I could not get any information
> or ask any help.  I was kind of surprised/frustrated by the (apparent)
> lack of order of the pg_dump output.  So I manually wrote scripts to
> export the tables and views' definitions separately, one by one, (using
> pg_dump, of course) and stack them in the order I wished into a large
> file.  That was quite suboptimal, but it worked as expected, and I was
> able to diff and patch correctly.

You realize there is pg_restore -l and pg_restore -L :

http://www.postgresql.org/docs/9.5/interactive/app-pgrestore.html

-l
--list

List the contents of the archive. The output of this operation can be used 
as input to the -L option. Note that if filtering switches such as -n or -t are 
used with -l, they will restrict the items listed.

-L list-file
--use-list=list-file

Restore only those archive elements that are listed in list-file, and 
restore them in the order they appear in the file. Note that if filtering 
switches such as -n or -t are used with -L, they will further restrict the 
items restored.

list-file is normally created by editing the output of a previous -l 
operation. Lines can be moved or removed, and can also be commented out by 
placing a semicolon (;) at the start of the line. See below for examples.


It does not solve all problems but it does do some ordering and is amenable to 
be
imported as space separated file for further ordering :

aklaver@panda:~> pg_restore -l test.out 
;
; Archive created at Fri Apr 22 10:07:50 2016
; dbname: test
; TOC Entries: 67
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.4.6
; Dumped by pg_dump version: 9.4.6
;
;
; Selected TOC Entries:
;
2702; 1262 983301 DATABASE - test postgres
9; 2615 1298825 SCHEMA - MASTER_USER postgres
8; 2615 2200 SCHEMA - public postgres
2703; 0 0 COMMENT - SCHEMA public postgres
2704; 0 0 ACL - public postgres
2; 3079 12456 EXTENSION - plpgsql 
2705; 0 0 COMMENT - EXTENSION plpgsql 
1; 3079 1730602 EXTENSION - plpythonu 
2706; 0 0 COMMENT - EXTENSION plpythonu 
191; 1255 1057054 FUNCTION public fn_plpgsqltestmulti(character varying) aklaver
210; 1255 1730594 FUNCTION public measurement_insert_trigger() postgres
205; 1255 1065246 FUNCTION public myfunc(refcursor, refcursor) aklaver
206; 1255 1065247 FUNCTION public myfunc(refcursor, refcursor, integer) aklaver
209; 1255 1065248 FUNCTION public myfunc(refcursor, refcursor, character 
varying) aklaver
208; 1255 1730587 FUNCTION public pg_stat_allusers() postgres
211; 1255 1730607 FUNCTION public test() postgres
207; 1255 1730585 FUNCTION public trigger_test() aklaver
198; 1255 1299304 FUNCTION public user_update() postgres
180; 1259 1298826 TABLE MASTER_USER test_tbl postgres
175; 1259 1016073 TABLE public a aklaver
178; 1259 1057055 TABLE public cash_journal aklaver
2707; 0 0 COMMENT public COLUMN cash_journal.click aklaver
2708; 0 0 COMMENT public COLUMN cash_journal.cash_journal_id aklaver
2709; 0 0 COMMENT public COLUMN cash_journal.fairian_id aklaver 
 
2710; 0 0 COMMENT public COLUMN cash_journal.debit aklaver  
 
2711; 0 0 COMMENT public COLUMN cash_journal.credit aklaver 
 
2712; 0 0 COMMENT public COLUMN cash_journal.balance aklaver
 
2713; 0 0 COMMENT public COLUMN cash_journal.description aklaver
 
182; 1259 1299634 TABLE public company postgres 
 
2714; 0 0 ACL public company postgres   
 
183; 1259 1727447 TABLE public final hplc_admin 
 
189; 1259 1730617 TABLE public 

Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

[Shulgin, Oleksandr]

On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson 
wrote:

> And so far, NO ONE has shown any proof that this enhancement could
> possibly cause ANY negative result.
>

Searching through the list archives[1] I can see that you've asked this
question a number of times already.  And I'm pretty sure it was asked quite
a number of times by the others.

IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall.


> All that has been presented so far are corner cases where this "might" not
> be useful.
> If the PostgreSQL developers are really worried about unexpected
> drawbacks, then, based on that,  ALL future development should stop
> immediately.
> This is total insanity! I am asking for a simple, safe enhancement that
> would add what compatibility with what is already in other databases, yet
> everyone seems to be terrified about it.
> We have already modified system catalogs previously with no ill effect.
>

I believe system catalogs are modified on a regular basis with every major
release.  But in every instance there has to be a good reason for a change.

So please, someone present a logical explanation of why this should not be
> done, or how it will negatively impact the PostgreSQL project.
> If you cannot do so, then start thinking positively.
>

As said before a number of times: what you propose looks easy, but it's
just the tip of an iceberg.  Even if the community comes to an agreement
what dump/restore semantics should be and it is implemented, the feature is
still not *that* useful on its own to justify its existence (no, I don't
buy the example of "DELETE/DROP TABLE" based on relcreated field. Do you,
by chance, have any other use case?)

Apart from created timestamp would you not like to also know the user/role
who has created it?  What about updates (using ALTER TABLE)--would you want
to know when that *last* happened and who did that?  Would you want to know
what exactly was altered?  Would you want to know the history *before* the
last update?  Finally, if someone drops the table, you can say good bye to
its pg_catalog records and there's no hope to know who did that and when
(or if that table has even existed to start with).

When you just start thinking in this direction, it becomes apparent that a
proper audit solution is a much better fit to tackle these problems.  There
are features continuously added in the recent releases that will facilitate
building such solutions in form of extensions: DDL event triggers and
Logical decoding, to name a few.

Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
> to present enhancement requests.
>

There is plenty of information on PostgreSQL sites about this[2,3,4].  Are
you suggesting something was add yesterday on top of that?

Now that it has been verified this is the correct list,
>

Probably it is the most appropriate one, unless you have the patch ready
(then it would be for -hackers).  I'm still puzzled as to how have you
found that completely unrelated feature request voting site given the
abundance of information on the official sites and lack of links to that
site from there.

It is true that some visibility of what majority of users consider to be
the most useful enhancement could benefit the project, but it has to be
maintained by the community in order to provide some value.  Otherwise it
is going to have only the negative impact: an impression that PostgreSQL
developers doesn't listen to the users.

There still exists no formal requirements for presenting an enhancement
> request.
>

Just follow the requirements for a good problem report, especially[5].
After all you have a problem of a missing feature, right?


> WHY am I being vilified for making a simple request? How is it that
> developers proceed with other enhancements, yet so much negative attention
> is being given to my request because of unjustified fear that something
> bad will happen?
>

Less colorful^W^W plain text mails without top-posting might help here.
Seriously, not everyone has the time to present the same arguments over and
over again: searching the archives should have given you some perspective
on the destiny of this feature request.

Should we really put this on Todo with a mark that we actually don't want
it?

Regards,
--
Alex

[1] http://www.postgresql.org/search/?m=1=relcreated
[2] http://www.postgresql.org/support/
[3] https://wiki.postgresql.org/wiki/FAQ#Where_can_I_get_support.3F
[4] https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
[5]
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_Not_To_Do

Re: [GENERAL] I need testers for incremental backups (similar Oracle)

[Yury Zhuravlev]

John R Pierce wrote:

doesn't a wal archive give you pretty much the same thing?


No, because WAL quickly enough may become larger than the database.
In the WAL can be any number of records that change only one page of 
memory.
In addition recover from the WAL too long, because it should be "play" by 
Postgres.

In my case pg_arman + ptrack it's almost as simple copy files.

Thanks.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


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

Re: [GENERAL] Enhancement request for pg_dump

[Pierre Chevalier Géologue]

Le 18/04/2016 03:10, Sergei Agalakov a écrit :

I just wanted to check that my request will have the peoples support.
So far it doesn't.


Well, you can count on my support, for sure!



It looks like that or people never need to compare two PG databases
to find the differences in the schemas or security, or happy to use
the third party tools to do it, and don't want any native support.


I definitely share your opinion.  But I understand that pg_dump wasn't 
originally designed for this purpose, although such a feature would seem 
so natural, at first glance.
So, another idea would be to implement another utility, something very 
similar to pg_dump (probably sharing most of its code with it, or 
calling pg_dump like my scripts did), but giving an ordered output.

Implementing such a tool outside of postgres would bring a bit more chaos.
So, the tool should be preferably bundled with postgres, to avoid the 
need to get a third-party tool.

Hm. When I think about it twice, it sounds like overkilling.

Opinions?



If I see any support from other people for this idea then I shall
go to https://postgresql.uservoice.com/forums/21853-general,


I'd say +1, but the idea should be a bit more matured first, maybe?

À+
Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1...@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue




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

Re: [GENERAL] Enhancement request for pg_dump

[Pierre Chevalier Géologue]

Hi,
Le 18/04/2016 02:26, Sergei Agalakov a écrit :


If you never encountered a situation when in the dozens of
environments the databases has diverged because somebody has
done something manually - good for you, you are lucky guy then.


I'm definitely not a lucky guy at all! :-)
And this is happening to me *right now*... My case is a little bit more 
complicated, but I'll come back to this later on, probably in another 
thread, in order to avoid confusion.


Last time I had to do this kind of exercise, a few years ago, I was in a 
remote place without Internet access, so I could not get any information 
or ask any help.  I was kind of surprised/frustrated by the (apparent) 
lack of order of the pg_dump output.  So I manually wrote scripts to 
export the tables and views' definitions separately, one by one, (using 
pg_dump, of course) and stack them in the order I wished into a large 
file.  That was quite suboptimal, but it worked as expected, and I was 
able to diff and patch correctly.


And today, I thought: "time has passed, I'm sure that pg_dump must 
magically have an option to get the output in some kind of order, by 
now"... 'man pg_dump' didn't help.  And as I can read this discussion (I 
haven't finished yet, obviously), this is not the case.


À+
Pierre
--

Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
Mesté Duran
32100 Condom
Tél+fax : 09 75 27 45 62
06 37 80 33 64
Émail : pierrechevaliergeolCHEZfree.fr
icq# : 10432285
jabber: pierre.chevalier1...@jabber.fr
http://pierremariechevalier.free.fr/pierre_chevalier_geologue



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

[GENERAL] pg_stat_archiver Enhancement Request

[Rob Brucks]

I'd like to request the following enhancement to enable better monitoring of a 
PostgreSQL cluster.

I propose adding a column to "pg_stat_archiver" to determine how many WAL logs 
are waiting to be archived.  This would enable external monitoring systems to 
query the status via SQL (without being a superuser) and alert when an 
excessive number of WAL logs are waiting to be archived. That way intervention 
can be performed before an out-of-space condition occurs.

I've implemented it as a separate function below, but would rather have it 
built in to postgres.

Thanks,
Rob Brucks


CREATE OR REPLACE FUNCTION
wal_archives_pending_func(OUT wal_archives_pending int) AS $$
  DECLARE result record;
  BEGIN
SELECT count(*) as count
  INTO result
  FROM (SELECT pg_ls_dir('pg_xlog/archive_status')) a
 WHERE pg_ls_dir ~ '[0-9A-F]{24}.ready';
wal_archives_pending := result.count;
  END;
$$
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER;

Re: [GENERAL] I need testers for incremental backups (similar Oracle)

[John R Pierce]

On 4/22/2016 8:36 AM, Yury Zhuravlev wrote:
I'm working on incremental backup based on tracking of memory pages 
(ptrack).
Now project in beta stage. And I did not have many feedbacks for catch 
bugs. 



doesn't a wal archive give you pretty much the same thing?

--
john r pierce, recycling bits in santa cruz



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

[GENERAL] I need testers for incremental backups (similar Oracle)

[Yury Zhuravlev]

Hello all.
I'm working on incremental backup based on tracking of memory pages 
(ptrack).
Now project in beta stage. And I did not have many feedbacks for catch 
bugs.


I think it is important project for postgres exploitation. This approach 
can really speed up incremental backup with small overhead.
This project append patch to postgres core and add new features to 
pg_arman.


Small HOWTO and all links I place here: 
https://gist.github.com/stalkerg/dda6fed9ca4bd7cc424ad439bac04303


I will be answered here and at github. 


Thanks!
--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


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

Re: [GENERAL] Initdb --data-checksums by default

[Bob Lunney]

> On Apr 22, 2016, at 3:21 AM, Stuart Bishop  wrote:
> 
> On 20 April 2016 at 14:43, Alex Ignatov  wrote:
>> Hello everyone!
>> Today in Big Data epoch silent data corruption becoming more and more issue
>> to afraid of. With uncorrectable read error rate ~ 10^-15   on multiterabyte
>> disk bit rot is the real issue.
>> I think that today checksumming data  must be mandatory  set by default.
>> Only if someone doesn't care about his data he can manually turn this option
>> off.
>> 
>> What do you think about defaulting --data-checksums in initdb?
> 
> I think --data-checksums should default to on.
> 
> Databases created 'thoughtlessly' should have safe defaults. Operators
> creating databases with care can elect to disable it if they are
> redundant in their environment, if they cannot afford the overhead, or
> consider their data low value enough to not want to pay the overheads.
> 
> If the performance impact is deemed unacceptable, perhaps the ability
> to turn them off on an existing database is easily doable (a one way
> operation).
> 
> -- 
> Stuart Bishop 
> http://www.stuartbishop.net/
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

+1

Bob Lunney
Lead Data Architect
MeetMe, Inc.

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

Re: [GENERAL] Proper relational database?

[John McKown]

On Fri, Apr 22, 2016 at 1:05 AM, Guyren Howe  wrote:

>
> A brief review of it says it would be better than SQL, but then almost
> anything would be. But the syntax looks a bit… baroque. Quell similarly.
>
> If I had the time and money to put together a team to do this, I would
> start with the lower-level guts of either Postgres or SQLite (or, heck,
> MySQL) so you had a thing that did BTrees and other data structures on disk
> and indexes, and provide access to that from a high level, portable and
> efficient language. Perhaps Scheme.
>
> Then you could write a high-level relational logic engine on top of that,
> in the high level language, perhaps with the odd bit of C or D or Go for
> anything really critical.
>
> I don't know if Postgres exposes the lower-level stuff to plugins or not —
> it would be nice if this could be an alternative query language for
> Postgres itself, but the assumptions about the two worlds (SQL vs a
> properly relational store) are probably too different.
>
> As I say, it amazes and somewhat depresses me that someone isn't doing
> this. The NoSQL movement shows that the world is ready for change. Someone
> should be offering folks something better than bloody MongoDB.
>
> And the project could adopt the spirit of the good parts of the NoSQL
> movement. I should be able to have a lightweight, distributed
> schema-on-demand, eventually consistent etc etc *relational* data store.
>
> Please don't get me wrong. I *adore* Postgres. It is for most projects
> hands-down the best data store available. It's just tragic that this
> amazing project is so wedded to the awfulness that is SQL.
>
> I wrote about such issues at a bit more length at
> http://relevantlogic.com/2015/11/04/no-sql-is-fixing-the-wrong-problem.html
>

​I am not a developer, but one thing interesting about SQLite is that it
appears to "compile" the SQL into a virtual machine language (ala Java &
byte code), then execute that. Now, if someone wanted to & had the talent,
it might be interesting to have another language which would compile into
the same VM language and so be executable by the SQLite VM interpreter. I
don't know if PostgreSQL does something similar or not. It may do a SQL to
VM, like Python. Or it may do something else. I need to read the
"internals" documentation on the web site.​


-- 
"He must have a Teflon brain -- nothing sticks to it"
Phyllis Diller

Maranatha! <><
John McKown

Re: [GENERAL] Partition table data not found in pg_dump

[Adrian Klaver]

On 04/15/2016 11:35 PM, vinothcanwin wrote:

Partition tables are empty in backup while using pg_dump to take backup, but
those tables having data in database. Below is the command i am using to
take backup.

./pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f /tmp/dump.sql
db_mydb



Not seeing much wrong above. FYI, -i is deprecated/ignored and -b is 
only needed if you are doing a selective dump, which is not the case here.


Are you sure you are pointing at the right database?



--
Adrian Klaver
adrian.kla...@aklaver.com


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

Re: [GENERAL] Proper relational database?

[Geoff Winkless]

On 22 April 2016 at 07:05, Guyren Howe  wrote:
> As I say, it amazes and somewhat depresses me that someone isn't doing this.
> The NoSQL movement shows that the world is ready for change. Someone should
> be offering folks something better than bloody MongoDB.
>
> Please don't get me wrong. I *adore* Postgres. It is for most projects
> hands-down the best data store available. It's just tragic that this amazing
> project is so wedded to the awfulness that is SQL.

Can I make a counter-argument?

SQL is excellent for beginners and adequate for most users. The basic
syntax of SQL (enough that people can produce useful queries with it)
can be presented and understood by a novice in an afternoon. I would
balk at the idea of trying to present the sort of syntax that appears
on the ANDL website to people who aren't programmers, which (I'd be
tempted to suggest) is a significant proportion of the userbase of
SQL.

The fact that ORMs are horrible and involve far too much work to
maintain isn't the fault of SQL, it's the fault of the people who
believe that they have to have their data fed to them by an ORM,
because of this idea (that has sadly propagated widely) that the
separation of code from the data is somehow helpful (as is probably
obvious I'm yet to be convinced of that!).

The world is not "ready for a change". I think of NoSQL as being like
Kim Kardashian: it gets an awful lot of publicity without providing
much justification for it; it brings a lot of column inches without
giving anything of substance in return and a lot of people talk about
it an awful lot without really knowing much about it at all.

There's a (very) small set of users for whom NoSQL makes a lot of
sense. Most of those are large corporations with huge budgets for
development, or academics who can afford to spend many hours tweaking
and figuring out their optimum storage requirements. The average SQL
user, on the other hand, just wants something that brings consistent
data storage for their database that probably numbers in the tens of
thousands of records at most (and if it scales to tens of millions
then great).

Geoff


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

Re: [GENERAL] different empty array syntax requirements

[Scott Ribe]

On Apr 21, 2016, at 8:37 PM, Tom Lane  wrote:
> 
> The parens turn into an actual parsetree node when
> operator_precedence_warning is on, and the cast-of-an-array hack doesn't
> know it should look through such a node.  

I figured that. The mystery is why on my pg, and not on the other. I've asked 
the other guy to try it in a newly-created database.

> That's a bug.  Will fix it.

OK, cool.

-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







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

Re: [GENERAL] different empty array syntax requirements

[Scott Ribe]

On Apr 21, 2016, at 8:25 PM, Alvaro Aguayo Garcia-Rada 
 wrote:
> 
> Looks like one has the appropiate cast operator, while the other hasn't. Have 
> you tried doing the same, on both server, on an empty database created from 
> template0?

Excellent suggestion:

pedcard=# create database test;
CREATE DATABASE
pedcard=# \c test
SSL connection (protocol: TLSv1, cipher: DHE-RSA-AES256-SHA, bits: 256, 
compression: off)
You are now connected to database "test" as user "admin".
test=# select (ARRAY[])::text[];
ERROR:  cannot determine type of empty array
LINE 1: select (ARRAY[])::text[];
^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[].


-- 
Scott Ribe
scott_r...@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice







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

Re: [GENERAL] Initdb --data-checksums by default

[Alex Ignatov]

On 21.04.2016 20:26, Vick Khera wrote:


On Thu, Apr 21, 2016 at 9:00 AM, Alex Ignatov 
> wrote:


Ms Windows doesnt have ZFS support. AIX also doesnt. Z/OS also. 
Any other commercial Linux distros don't have ZFS support. Yes you

can compile it and use on production but...
But PG runs on the above OS, but have check sum off by default.
Thats the deal. And it is not related to ZFS existence or any
other FS with checksums in any way. The question is only in
performance hit when you turn it on and  now I am in the process
of testing it...


I don't care about those platforms, so changing the default is just 
making more work for me. :)
I see ;). By  the way following my tests turn this option on is only 
1-2% overhead in throughput.


--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: [GENERAL] Proper relational database?

[David Goodenough]

On Thursday 21 April 2016 13:36:54 Guyren Howe wrote:
> Anyone familiar with the issue would have to say that the tech world would
> be a significantly better place if IBM had developed a real relational
> database with an elegant query language rather than the awful camel of a
> thing that is SQL.
> 
> If I had a few $million to spend in a philanthropical manner, I would hire
> some of the best PG devs to develop a proper relational database server.
> Probably a query language that expressed the relational algebra in a
> scheme-like syntax, and the storage model would be properly relational (eg
> no duplicate rows).
> 
> It's an enormous tragedy that all the development effort that has gone into
> NoSQL database has pretty much all gotten it wrong: by all means throw out
> SQL, but not the relational model with it. They're all just rehashing the
> debate over hierarchical storage from the 70s. Comp Sci courses should
> feature a history class.
> 
> It's a bit odd to me that someone isn't working on such a thing.
> 
> Just curious what folks here have to say…
Well when IBM were first developing relational databases there were two
different teams.  One in California which produced System-R which became
what we now know as DB2 and spawned SQL, and the other in Peterlee in
the UK which was called PRTV (the Peterlee Relational Test Vehicle).  PRTV
rather died but bits of it survived.  In particular it was the first to system
to include a relational optimiser.  You can find some details on the PRTV
page in Wikipedia.  

It was written in PL/1, although it also used some modified microcode 
and therefore some assembler.  

It never appeared as a product, but there was a geographical system
which built on top of it which was if I recall corrected used by the Greater
London Council and Central Region Scotland, which did something of
what postgis does for PostgreSQL.

According to the Wikipedia page it did have a language (ISBL) but from what
I recall (and it was nearly 40 years ago) there were a series of PL/1 
function calls we used rather than encoding the request as a string
as SQL systems require.

The IBM centre in Peterlee was closed, and the lab moved to Winchester
where I think it still resides.

David


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

Re: [GENERAL] Initdb --data-checksums by default

[Stuart Bishop]

On 20 April 2016 at 14:43, Alex Ignatov  wrote:
> Hello everyone!
> Today in Big Data epoch silent data corruption becoming more and more issue
> to afraid of. With uncorrectable read error rate ~ 10^-15   on multiterabyte
> disk bit rot is the real issue.
> I think that today checksumming data  must be mandatory  set by default.
> Only if someone doesn't care about his data he can manually turn this option
> off.
>
> What do you think about defaulting --data-checksums in initdb?

I think --data-checksums should default to on.

Databases created 'thoughtlessly' should have safe defaults. Operators
creating databases with care can elect to disable it if they are
redundant in their environment, if they cannot afford the overhead, or
consider their data low value enough to not want to pay the overheads.

If the performance impact is deemed unacceptable, perhaps the ability
to turn them off on an existing database is easily doable (a one way
operation).

-- 
Stuart Bishop 
http://www.stuartbishop.net/


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

Re: [GENERAL] Proper relational database?

[Guyren Howe]

On Apr 21, 2016, at 13:56 , John McKown  wrote:
> 
> Just as a curiosity, what do you think of ANDL?
> 
> http://www.andl.org/welcome-to-andl/ 
> 
> The developer has been posting some questions here about interfacing it to 
> PostgreSQL. But he doesn't just want to do a "translate the ANDL language to 
> SQL language"

A brief review of it says it would be better than SQL, but then almost anything 
would be. But the syntax looks a bit… baroque. Quell similarly.

If I had the time and money to put together a team to do this, I would start 
with the lower-level guts of either Postgres or SQLite (or, heck, MySQL) so you 
had a thing that did BTrees and other data structures on disk and indexes, and 
provide access to that from a high level, portable and efficient language. 
Perhaps Scheme.

Then you could write a high-level relational logic engine on top of that, in 
the high level language, perhaps with the odd bit of C or D or Go for anything 
really critical.

I don't know if Postgres exposes the lower-level stuff to plugins or not — it 
would be nice if this could be an alternative query language for Postgres 
itself, but the assumptions about the two worlds (SQL vs a properly relational 
store) are probably too different.

As I say, it amazes and somewhat depresses me that someone isn't doing this. 
The NoSQL movement shows that the world is ready for change. Someone should be 
offering folks something better than bloody MongoDB.

And the project could adopt the spirit of the good parts of the NoSQL movement. 
I should be able to have a lightweight, distributed schema-on-demand, 
eventually consistent etc etc *relational* data store.

Please don't get me wrong. I *adore* Postgres. It is for most projects 
hands-down the best data store available. It's just tragic that this amazing 
project is so wedded to the awfulness that is SQL.

I wrote about such issues at a bit more length at 
http://relevantlogic.com/2015/11/04/no-sql-is-fixing-the-wrong-problem.html