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

[Alvaro Herrera]

Tom Lane wrote:
> Melvin Davidson  writes:

> > However, Customer Feedback (
> > https://postgresql.uservoice.com/forums/21853-general
> >  ) does seem to
> > indicate it and give positive results.
> 
> I had never heard of postgresql.uservoice.com before this thread, and
> I daresay most other community members had not either.  It has NO
> standing or influence on our development work.

Actually, to be fair, Peter Eisentraut set it up back in 2009 and
continues to keep it updated.  I thought I had seen it announced, but
now that I look, it seems he only mailed sysadm...@postgresql.org and
never any public list.  From the comments there, it's pretty obvious
that the list is helpful; the number of things marked "done" in recent
times is not small.

I'm not saying that *anything* listed there is useful.  (In particular I
don't think the "relcreated" column provides a lot of value.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Clarify "allow_system_table_mods"

[Tom Lane]

Adrian Klaver  writes:
> On 04/25/2016 05:29 PM, Stephen Frost wrote:
>> It's not an option *to* initdb, it's an option which is used *by*
>> initdb.

> That really did not clear things up:) Does it mean that you can 
> pre-populate the $DATA directory with a postgresql.conf that has 
> allow_system_table_mods set to on and initdb will pick it up?

No, initdb will complain if you point it at a nonempty directory...

The way that initdb uses this option is to pass it as a command-line
switch to the standalone backend it invokes.

> Personally, I think tampering with the system catalogs is foolish. Still 
> if you have documentation for something(even if it is a foot gun) it 
> should be understandable. If the intent is for end users/dba's not use 
> these options I would say take then out of the user docs and put them in 
> the developer Wiki section.

It's already in a section titled "Developer Options", with the heading

The following parameters are intended for work on the PostgreSQL
source code, and in some cases to assist with recovery of severely
damaged databases. There should be no reason to use them on a
production database.

I'm not sure what else we should do, short of writing "HERE BE DRAGONS"
set in blackletter type.

(Having said that, we could change "This is used by initdb." to "This
is meant only for use by initdb".  But I don't know that that really
would discourage anybody.)

regards, tom lane


-- 
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?

[Adam Brusselback]

>It is not difficult to simulate column store in a row store system if
>you're willing to decompose your tables into (what is essentially)
>BCNF fragments.  It simply is laborious for designers and programmers.

I could see a true column store having much better performance than
tricking a row based system into it.  Just think of the per-row overhead we
currently have at 28 bytes per row.  Breaking up data manually like that
may help a little, but if you don't have a very wide table to begin with,
it could turn out you save next to nothing by doing so.  A column store
wouldn't have this issue, and could potentially have much better
performance.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 8:41 PM, Adrian Klaver 
wrote:

> On 04/25/2016 05:29 PM, Stephen Frost wrote:
>
>> * Melvin Davidson (melvin6...@gmail.com) wrote:
>>
>>> Hmmm, if you go back a few comments, you will note that per initdb --help
>>> there is no such option available.
>>>
>>
>> It's not an option *to* initdb, it's an option which is used *by*
>> initdb.
>>
>
> That really did not clear things up:) Does it mean that you can
> pre-populate the $DATA directory with a postgresql.conf that has
> allow_system_table_mods set to on and initdb will pick it up?
>
> Personally, I think tampering with the system catalogs is foolish. Still
> if you have documentation for something(even if it is a foot gun) it should
> be understandable. If the intent is for end users/dba's not use these
> options I would say take then out of the user docs and put them in the
> developer Wiki section.
>
>
>
>> I'm afraid I'm done with this particular discussion.  Hopefully it's
>> purpose is now clear and you understand a bit better what is required to
>> actually add a column to pg_class.
>>
>> Thanks!
>>
>> Stephen
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

I am completely in agreement with what Adrian said.
I only started this conversation because my enhancement request to add
relcreated to pg_class seemingly was going nowhere. I was told if I wanted
to do that, I should write the patch myself
and submit it, but I am not a developer, so that option is out.

I also pointed to a "Customer Feedback" url that has apparently been active
for quite awhile, but the developers deny knowledge of that.

So if we are going to use this list as a medium for enhancement requests, I
feel it is only fair that
1. A formal committee to review all such requests be created.
2. That committee should meet (or review) periodically all enhancement
requests.
3. If an enhancement request is approved, then the list should be updated
with it, or else
   if rejected, then a reason (such as dangerous code or negative
performance) should be stated.
4. Using, "it won't work in all cases" is not a reason for rejection. Think
of positive benefits!

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[George Neuner]

On Thu, 21 Apr 2016 09:08:22 -0700, "David G. Johnston"
 wrote:

>?I have little experience (and nothing practical) with columnar store but
>at a high level I don't see the point.  

At the high level, it's about avoiding fetching data you don't need.
In a row store system, in general you must fetch the whole row to
extract any of its columns.

It is not difficult to simulate column store in a row store system if
you're willing to decompose your tables into (what is essentially)
BCNF fragments.  It simply is laborious for designers and programmers.


>I would hope that anyone interested in working on a columnar store
>database would pick an existing one to improve rather than converting 
>a very successful row store database into one.  

+1

>And I don't immediately understand how a dual setup would even be
>viable - it seems like you'd have to re-write so much
>?of the code the only thing left would be the SQL parser.

If you are willing to go to BCNF and manage the physical location of
your tables [which any performance system will be doing anyway], then
any decent row store system can mix in "column" tables where desired.

IMO, the only real added value of a dedicated column store system is
to developers: the automagic table fragmentation and the ability to
query virtual tables rather than specify table fragments individually.
Convenient, but not necessary.

YMMV,
George



-- 
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] Clarify "allow_system_table_mods"

[Adrian Klaver]

On 04/25/2016 05:29 PM, Stephen Frost wrote:

* Melvin Davidson (melvin6...@gmail.com) wrote:

Hmmm, if you go back a few comments, you will note that per initdb --help
there is no such option available.


It's not an option *to* initdb, it's an option which is used *by*
initdb.


That really did not clear things up:) Does it mean that you can 
pre-populate the $DATA directory with a postgresql.conf that has 
allow_system_table_mods set to on and initdb will pick it up?


Personally, I think tampering with the system catalogs is foolish. Still 
if you have documentation for something(even if it is a foot gun) it 
should be understandable. If the intent is for end users/dba's not use 
these options I would say take then out of the user docs and put them in 
the developer Wiki section.




I'm afraid I'm done with this particular discussion.  Hopefully it's
purpose is now clear and you understand a bit better what is required to
actually add a column to pg_class.

Thanks!

Stephen




--
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] Clarify "allow_system_table_mods"

[Stephen Frost]

* Melvin Davidson (melvin6...@gmail.com) wrote:
> Hmmm, if you go back a few comments, you will note that per initdb --help
> there is no such option available.

It's not an option *to* initdb, it's an option which is used *by*
initdb.

I'm afraid I'm done with this particular discussion.  Hopefully it's
purpose is now clear and you understand a bit better what is required to
actually add a column to pg_class.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 8:18 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, April 25, 2016, Stephen Frost  wrote:
>
>> Melvin,
>>
>> * Melvin Davidson (melvin6...@gmail.com) wrote:
>> > So in essence "*Allows modification of the structure of system tables"
>> does
>> > NOT allow _structural_ changes (probably only dml changes)
>> > and the documentation should be changed to clarify.
>>
>> That would imply that other changes are acceptable.  That is not the
>> case.  Certain DML changes could cause crashes too.
>>
>> The documentation is pretty clear that this option is for initdb, and
>> not anyone else.  I'm unconvinced that we need anything more.
>>
>>
> If anything it should be shortened to simply that.
>
> David J.
>

>The documentation is pretty clear that this option is for initdb
Hmmm, if you go back a few comments, you will note that per initdb --help
there is no such option available.
It is further confusing for allow_system_table_mods to be in
postgresql.conf, since it is initdb that creates postgresql.conf.
IOW, it is putting the cart before the horse, or catch-22.
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[David G. Johnston]

On Monday, April 25, 2016, Stephen Frost  wrote:

> Melvin,
>
> * Melvin Davidson (melvin6...@gmail.com ) wrote:
> > So in essence "*Allows modification of the structure of system tables"
> does
> > NOT allow _structural_ changes (probably only dml changes)
> > and the documentation should be changed to clarify.
>
> That would imply that other changes are acceptable.  That is not the
> case.  Certain DML changes could cause crashes too.
>
> The documentation is pretty clear that this option is for initdb, and
> not anyone else.  I'm unconvinced that we need anything more.
>
>
If anything it should be shortened to simply that.

David J.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Stephen Frost]

Melvin,

* Melvin Davidson (melvin6...@gmail.com) wrote:
> So in essence "*Allows modification of the structure of system tables" does
> NOT allow _structural_ changes (probably only dml changes)
> and the documentation should be changed to clarify.

That would imply that other changes are acceptable.  That is not the
case.  Certain DML changes could cause crashes too.

The documentation is pretty clear that this option is for initdb, and
not anyone else.  I'm unconvinced that we need anything more.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 8:05 PM, Stephen Frost  wrote:

> * Melvin Davidson (melvin6...@gmail.com) wrote:
> > Then could you please clarify exactly what structural mods are permitted
> by
> > *"*
> > *Allows modification of the structure of system tables" ?*
>
> I would say, in short, those modifications which are implemented by PG
> developers who know what's safe to do with the catalog tables.
>
> Beyond that, I'm afriad you'd need to read the source code.  I don't
> know offhand the complete set of "what's safe to do", though it's
> probably a pretty short list and certainly doesn't include adding
> columns.
>
> Ultimately, that switch isn't for end users to use to modify the
> catalogs.  If you'd like to modify the structure of pg_class, you would
> start by looking at src/include/catalog/pg_class.h, though there's quite
> a few other bits that would need to change too.
>
> Thanks!
>
> Stephen
>


>allow_system_table_mods only exists so that initdb can do some setup steps
that would otherwise be inconvenient to manage.

So in essence "*Allows modification of the structure of system tables" does
NOT allow _structural_ changes (probably only dml changes)
and the documentation should be changed to clarify.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Stephen Frost]

* Melvin Davidson (melvin6...@gmail.com) wrote:
> Then could you please clarify exactly what structural mods are permitted by
> *"*
> *Allows modification of the structure of system tables" ?*

I would say, in short, those modifications which are implemented by PG
developers who know what's safe to do with the catalog tables.

Beyond that, I'm afriad you'd need to read the source code.  I don't
know offhand the complete set of "what's safe to do", though it's
probably a pretty short list and certainly doesn't include adding
columns.

Ultimately, that switch isn't for end users to use to modify the
catalogs.  If you'd like to modify the structure of pg_class, you would
start by looking at src/include/catalog/pg_class.h, though there's quite
a few other bits that would need to change too.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Clarify "allow_system_table_mods"

[Tom Lane]

"David G. Johnston"  writes:
> On Monday, April 25, 2016, Melvin Davidson  wrote:
>> I need clarification on allow_system_table_mods parameter

> If it's going to prevent the action it should be able to do so without
> crashing the server..and I doubt it's intended to be opinionated aside from
> covering all DDL.  What little there is on Google seems to support this.

You've got that backwards.  Turning allow_system_table_mods on simply
disables a bunch of safety/permissions checks, allowing incautious
superusers to break their databases by changing system catalog structure.
It does not promise to make things work that are incapable of being
supported by the underlying C code.  For the most part, no change in the
rowtype of any system catalog is going to work; the C code has compiled-in
notions about the numbers and types of the columns in each catalog.

allow_system_table_mods only exists so that initdb can do some setup steps
that would otherwise be inconvenient to manage.  It's not intended to be
used by average mortals; and that's the reason for the lack of
documentation, as well as the lack of concern about whether you can crash
the system with it.

regards, tom lane


-- 
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] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 7:57 PM, Stephen Frost  wrote:

> David, Melvin,
>
> * David G. Johnston (david.g.johns...@gmail.com) wrote:
> > On Monday, April 25, 2016, Melvin Davidson  wrote:
> > > I need clarification on allow_system_table_mods parameter
> > > Per the documentation:
> > > *Allows modification of the structure of system tables.* This is used
> by
> > > initdb. This parameter can only be set at server start.
> > >
> > > However, attempting to modify pg_class to add another column fails with
> > > "STATUS_ACCESS_VIOLATION" as below.
> > > So either only certain system catalogs may be changed, or only certain
> > > types of structure changes are allowed.
>
> There might be some things about system catalogs you can modify, but
> generally speaking, you can't add a column or otherwise change the
> structure.  System catalogs are represented in memory by C structures
> (and more), so it's not too surprising that adding a column causes a
> crash.
>
> > If it's going to prevent the action it should be able to do so without
> > crashing the server..and I doubt it's intended to be opinionated aside
> from
> > covering all DDL.  What little there is on Google seems to support this.
>
> Sorry, but as a superuser, there's a lot of things you can do to crash
> the server, this is just one way.  The effort required to prevent
> anything bad from happening when a user is running as a superuser is far
> from trivial.
>
> In short, no, you can't just add a column to pg_class via SQL, and I
> don't think we're going to be very interested in trying to "fix" such
> cases.
>
> Thanks!
>
> Stephen
>

Thank you Stephen.
Then could you please clarify exactly what structural mods are permitted by
*"*
*Allows modification of the structure of system tables" ?*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 7:50 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Monday, April 25, 2016, Melvin Davidson  wrote:
>
>> I need clarification on allow_system_table_mods parameter
>> Per the documentation:
>> *Allows modification of the structure of system tables.* This is used by
>> initdb. This parameter can only be set at server start.
>>
>> However, attempting to modify pg_class to add another column fails with
>> "STATUS_ACCESS_VIOLATION" as below.
>> So either only certain system catalogs may be changed, or only certain
>> types of structure changes are allowed.
>> eg: add indexes, triggers
>>
>> Windows 10
>> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>>
>> Current user is postgres
>> allow_system_table_mods = on
>>
>> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>> current_timestamp;
>>
>> Fails with:
>>
>> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
>> COLUMN relcreated timestamp DEFAULT current_timestamp;
>> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated by
>> exception 0xC005
>> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
>> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>> current_timestamp;
>> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
>> description of the hexadecimal value.
>> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server
>> processes
>>
>> From ntstatus.h
>> *#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
>> winnt*
>>
>>
> If it's going to prevent the action it should be able to do so without
> crashing the server..and I doubt it's intended to be opinionated aside from
> covering all DDL.  What little there is on Google seems to support this.
>
> David J.
>

>I doubt it's intended to be opinionated aside from covering all DDL

*So, IOW "*
*Allows modification of the structure of system tables" does not allow
ALTER TABLE ADD column on syscats? That seems a bit contradictory.*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Stephen Frost]

David, Melvin,

* David G. Johnston (david.g.johns...@gmail.com) wrote:
> On Monday, April 25, 2016, Melvin Davidson  wrote:
> > I need clarification on allow_system_table_mods parameter
> > Per the documentation:
> > *Allows modification of the structure of system tables.* This is used by
> > initdb. This parameter can only be set at server start.
> >
> > However, attempting to modify pg_class to add another column fails with
> > "STATUS_ACCESS_VIOLATION" as below.
> > So either only certain system catalogs may be changed, or only certain
> > types of structure changes are allowed.

There might be some things about system catalogs you can modify, but
generally speaking, you can't add a column or otherwise change the
structure.  System catalogs are represented in memory by C structures
(and more), so it's not too surprising that adding a column causes a
crash.

> If it's going to prevent the action it should be able to do so without
> crashing the server..and I doubt it's intended to be opinionated aside from
> covering all DDL.  What little there is on Google seems to support this.

Sorry, but as a superuser, there's a lot of things you can do to crash
the server, this is just one way.  The effort required to prevent
anything bad from happening when a user is running as a superuser is far
from trivial.

In short, no, you can't just add a column to pg_class via SQL, and I
don't think we're going to be very interested in trying to "fix" such
cases.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Clarify "allow_system_table_mods"

[David G. Johnston]

On Monday, April 25, 2016, Melvin Davidson  wrote:

> I need clarification on allow_system_table_mods parameter
> Per the documentation:
> *Allows modification of the structure of system tables.* This is used by
> initdb. This parameter can only be set at server start.
>
> However, attempting to modify pg_class to add another column fails with
> "STATUS_ACCESS_VIOLATION" as below.
> So either only certain system catalogs may be changed, or only certain
> types of structure changes are allowed.
> eg: add indexes, triggers
>
> Windows 10
> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>
> Current user is postgres
> allow_system_table_mods = on
>
> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
>
> Fails with:
>
> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
> COLUMN relcreated timestamp DEFAULT current_timestamp;
> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated by
> exception 0xC005
> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes
>
> From ntstatus.h
> *#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
> winnt*
>
>
If it's going to prevent the action it should be able to do so without
crashing the server..and I doubt it's intended to be opinionated aside from
covering all DDL.  What little there is on Google seems to support this.

David J.

Re: [GENERAL] Error: no connection to the server

[Melvin Davidson]

On Mon, Apr 25, 2016 at 7:32 PM, Sameer Kumar 
wrote:

>
>
> On Mon, 25 Apr 2016 15:59 Marco Bambini,  wrote:
>
>> Hi,
>> I have a multithreaded C client and sometimes I receive the "no
>> connection to the server" error message.
>>
>
> Please add more details of the
>
> - version of Postgres.
> - Platform and OS details
>
> Are you using any Middleware like pgpool or pgbouncer?
>
> I haven't found any documentation about it and about how to fix this issue.
>>
>> Anyone can point me to the right direction?
>> How can I fix it?
>>
>> Thanks a lot.
>> --
>> Marco Bambini
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
> --
> --
> 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
>

Have you tried looking in the Postgres log for additional information?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Error: no connection to the server

[Sameer Kumar]

On Mon, 25 Apr 2016 15:59 Marco Bambini,  wrote:

> Hi,
> I have a multithreaded C client and sometimes I receive the "no connection
> to the server" error message.
>

Please add more details of the

- version of Postgres.
- Platform and OS details

Are you using any Middleware like pgpool or pgbouncer?

I haven't found any documentation about it and about how to fix this issue.
>
> Anyone can point me to the right direction?
> How can I fix it?
>
> Thanks a lot.
> --
> Marco Bambini
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
-- 
--
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] Clarify "allow_system_table_mods"

[Melvin Davidson]

On Mon, Apr 25, 2016 at 4:26 PM, Melvin Davidson 
wrote:

> >my previous answer did not make sense as postgresql.conf is set up by
> initdb
> Yeah, that kind of puzzles me too.
>
> >-O  allow_system_table_mods = on
> Thanks, I'll give that a try and create a new cluster, then get back to
> you afterwards.
>
> On Mon, Apr 25, 2016 at 4:05 PM, Adrian Klaver 
> wrote:
>
>> On 04/25/2016 09:30 AM, Melvin Davidson wrote:
>>
>>> I need clarification on allow_system_table_mods parameter
>>> Per the documentation:
>>> *Allows modification of the structure of system tables.* This is used by
>>> initdb. This parameter can only be set at server start.
>>>
>>
>> A millisecond after hitting enter I realized my previous answer did not
>> make sense as postgresql.conf is set up by initdb. Then I found this:
>>
>> http://www.postgresql.org/docs/9.2/static/runtime-config-short.html
>>
>> -O  allow_system_table_mods = on
>>
>> So use that option when doing initdb?
>>
>>
>>> However, attempting to modify pg_class to add another column fails with
>>> "STATUS_ACCESS_VIOLATION" as below.
>>> So either only certain system catalogs may be changed, or only certain
>>> types of structure changes are allowed.
>>> eg: add indexes, triggers
>>>
>>> Windows 10
>>> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>>>
>>> Current user is postgres
>>> allow_system_table_mods = on
>>>
>>> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>>> current_timestamp;
>>>
>>> Fails with:
>>>
>>> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
>>> COLUMN relcreated timestamp DEFAULT current_timestamp;
>>> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
>>> by exception 0xC005
>>> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
>>> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>>> current_timestamp;
>>> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
>>> description of the hexadecimal value.
>>> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server
>>> processes
>>>
>>>  From ntstatus.h
>>> *#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
>>> winnt*
>>>
>>> --
>>> *Melvin Davidson*
>>> I reserve the right to fantasize.  Whether or not you
>>> wish to share my fantasy is entirely up to you.
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
> >my previous answer did not make sense as postgresql.conf is set up by
> initdb
> Yeah, that kind of puzzles me too.
>
> >-O  allow_system_table_mods = on
> Thanks, I'll give that a try and create a new cluster, then get back to
> you afterwards.
>
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>










*ok, so I triedE:\PostgresDB\bin>initdb -U postgres -O -D E:\PGtest\dataand
that failed with:initdb: illegal option -- OTry "initdb --help" for more
information.*


*which confirmed -O is _not_ a valid option.*

*So it's back to square one (and apparently catch 22) of
allow_system_table_mods :(*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

>my previous answer did not make sense as postgresql.conf is set up by
initdb
Yeah, that kind of puzzles me too.

>-O  allow_system_table_mods = on
Thanks, I'll give that a try and create a new cluster, then get back to you
afterwards.

On Mon, Apr 25, 2016 at 4:05 PM, Adrian Klaver 
wrote:

> On 04/25/2016 09:30 AM, Melvin Davidson wrote:
>
>> I need clarification on allow_system_table_mods parameter
>> Per the documentation:
>> *Allows modification of the structure of system tables.* This is used by
>> initdb. This parameter can only be set at server start.
>>
>
> A millisecond after hitting enter I realized my previous answer did not
> make sense as postgresql.conf is set up by initdb. Then I found this:
>
> http://www.postgresql.org/docs/9.2/static/runtime-config-short.html
>
> -O  allow_system_table_mods = on
>
> So use that option when doing initdb?
>
>
>> However, attempting to modify pg_class to add another column fails with
>> "STATUS_ACCESS_VIOLATION" as below.
>> So either only certain system catalogs may be changed, or only certain
>> types of structure changes are allowed.
>> eg: add indexes, triggers
>>
>> Windows 10
>> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>>
>> Current user is postgres
>> allow_system_table_mods = on
>>
>> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>> current_timestamp;
>>
>> Fails with:
>>
>> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
>> COLUMN relcreated timestamp DEFAULT current_timestamp;
>> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
>> by exception 0xC005
>> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
>> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
>> current_timestamp;
>> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
>> description of the hexadecimal value.
>> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server
>> processes
>>
>>  From ntstatus.h
>> *#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
>> winnt*
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

>my previous answer did not make sense as postgresql.conf is set up by
initdb
Yeah, that kind of puzzles me too.

>-O  allow_system_table_mods = on
Thanks, I'll give that a try and create a new cluster, then get back to you
afterwards.

*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Clarify "allow_system_table_mods"

[Adrian Klaver]

On 04/25/2016 09:30 AM, Melvin Davidson wrote:

I need clarification on allow_system_table_mods parameter
Per the documentation:
*Allows modification of the structure of system tables.* This is used by
initdb. This parameter can only be set at server start.


A millisecond after hitting enter I realized my previous answer did not 
make sense as postgresql.conf is set up by initdb. Then I found this:


http://www.postgresql.org/docs/9.2/static/runtime-config-short.html

-O  allow_system_table_mods = on

So use that option when doing initdb?



However, attempting to modify pg_class to add another column fails with
"STATUS_ACCESS_VIOLATION" as below.
So either only certain system catalogs may be changed, or only certain
types of structure changes are allowed.
eg: add indexes, triggers

Windows 10
PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit

Current user is postgres
allow_system_table_mods = on

ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;

Fails with:

2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
COLUMN relcreated timestamp DEFAULT current_timestamp;
2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
by exception 0xC005
2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;
2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes

 From ntstatus.h
*#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
winnt*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
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] Clarify "allow_system_table_mods"

[Adrian Klaver]

On 04/25/2016 09:30 AM, Melvin Davidson wrote:

I need clarification on allow_system_table_mods parameter
Per the documentation:
*Allows modification of the structure of system tables.* This is used by
initdb. This parameter can only be set at server start.


I am reading the above as meaning that the parameter only takes affect 
if it has been set in the conf file and you then initdb a new database 
cluster. So is that what you did or did you add the parameter to a 
previously initialized cluster conf file?




However, attempting to modify pg_class to add another column fails with
"STATUS_ACCESS_VIOLATION" as below.
So either only certain system catalogs may be changed, or only certain
types of structure changes are allowed.
eg: add indexes, triggers

Windows 10
PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit

Current user is postgres
allow_system_table_mods = on

ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;

Fails with:

2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
COLUMN relcreated timestamp DEFAULT current_timestamp;
2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
by exception 0xC005
2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;
2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes

 From ntstatus.h
*#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
winnt*

--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
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] Types of blocks in the `pg_stat_statements`

[Guillaume Lelarge]

2016-04-25 15:06 GMT+02:00 Victor Yegorov :

> Greetings.
>
> I cannot get understanding about what different types of blocks means here:
> http://www.postgresql.org/docs/current/interactive/pgstatstatements.html
>
>
> I assume, that `shared` are blocks from the `shared_buffers`
> and `temp` are blocks used by temporary files (absence of `dirtied` and
> `hit` also speaks for this).
>
> But what `local` blocks mean?
>
>
This is for the local buffer for temporary objects (temporary tables and
indexes), which you can set the size with temp_buffers.


-- 
Guillaume.
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com

Re: [GENERAL] Calculating Minkowski distance between two rows

[Begin Daniel]

I am inclined to go with Francisco's solution
Daniel

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Francisco Olarte
Sent: April-25-16 10:46
To: Babak Alipour
Cc: Adrian Klaver; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Calculating Minkowski distance between two rows

Hi:

On Mon, Apr 25, 2016 at 4:26 PM, Babak Alipour  wrote:
> That is correct. The function I've written only works when the two 
> tables are named table_train and table_test; is it possible to 
> generalize that to take in any two tables?

And only when all table_train columns are numbers AND table_test contains at 
least all of them AND they are numbers too.

Wouldn't it be easier to use numeric arrays to represent coordinate vectors?

Anyway, I'm not versed in all of this, wbut I would try to make a function to 
turn a table record to a numeric array and then write the numeric array version 
of the func and call them, divide and conquer.

Francisco Olarte.


--
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

[GENERAL] Clarify "allow_system_table_mods"

[Melvin Davidson]

I need clarification on allow_system_table_mods parameter
Per the documentation:
*Allows modification of the structure of system tables.* This is used by
initdb. This parameter can only be set at server start.

However, attempting to modify pg_class to add another column fails with
"STATUS_ACCESS_VIOLATION" as below.
So either only certain system catalogs may be changed, or only certain
types of structure changes are allowed.
eg: add indexes, triggers

Windows 10
PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit

Current user is postgres
allow_system_table_mods = on

ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;

Fails with:

2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
COLUMN relcreated timestamp DEFAULT current_timestamp;
2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated by
exception 0xC005
2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
current_timestamp;
2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
description of the hexadecimal value.
2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes

>From ntstatus.h
*#define STATUS_ACCESS_VIOLATION  ((NTSTATUS)0xC005L)//
winnt*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Intemittendly get "server process (PID 5884) exited with exit code 3"

[Tom Lane]

"Wetzel, Juergen (Juergen)"  writes:
> I'm working with postgres version 9.3.10 on Windows. From time to time a 
> postgres process terminates with following messages:

> LOG:  server process (PID 5884) exited with exit code 3

As far as I can recall at the moment, no Postgres child process would ever
choose to exit with exit code 3; we use 0, 1, and 2, but not 3.  I am
thinking that maybe you've got some custom code in plperl or plpython or
some such that does an exit(3).

> This ends up in an endless loop of these messages. Only possibility to 
> recover is to restart postgres service.

Or maybe the exit(3) is in an extension that is preloaded into all
processes via shared_preload_libraries or similar?

regards, tom lane


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

[GENERAL] Intemittendly get "server process (PID 5884) exited with exit code 3"

[Wetzel, Juergen (Juergen)]

Hi,

I'm working with postgres version 9.3.10 on Windows. From time to time a 
postgres process terminates with following messages:

LOG:  server process (PID 5884) exited with exit code 3
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the 
current transaction and exit, because another server process exited abnormally 
and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat 
your command.

This ends up in an endless loop of these messages. Only possibility to recover 
is to restart postgres service.

Has anybody an idea what can cause this? Or is there a possibility to get more 
(useful) log messages? Every time when I change log_min_messages to a value 
providing more information than "warning" the bug does not occur anymore.

Regards

Re: [GENERAL] Calculating Minkowski distance between two rows

[Francisco Olarte]

Hi:

On Mon, Apr 25, 2016 at 4:26 PM, Babak Alipour  wrote:
> That is correct. The function I've written only works when the two tables
> are named table_train and table_test; is it possible to generalize that to
> take in any two tables?

And only when all table_train columns are numbers AND table_test
contains at least all of them AND they are numbers too.

Wouldn't it be easier to use numeric arrays to represent coordinate vectors?

Anyway, I'm not versed in all of this, wbut I would try to make a
function to turn a table record to a numeric array and then write the
numeric array version of the func and call them, divide and conquer.

Francisco Olarte.


-- 
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] Calculating Minkowski distance between two rows

[Adrian Klaver]

On 04/25/2016 07:26 AM, Babak Alipour wrote:

That is correct. The function I've written only works when the two
tables are named table_train and table_test; is it possible to
generalize that to take in any two tables?


I'm heading out the door and off the top of my head:

1) Get tables names as text.

2) Get the row conditions as text.

3) Use EXECUTE to build a query string:
http://www.postgresql.org/docs/9.5/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

In particular:
"A cleaner approach is to use format()'s %I specification for table or 
column names (strings separated by a newline are concatenated):"


http://www.postgresql.org/docs/9.5/static/functions-string.html#FUNCTIONS-STRING-FORMAT

4) SELECT the result INTO a record variable:

http://www.postgresql.org/docs/9.5/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS

5) Do your calculations

6) Not considered, validating that number of table columns are the same 
and the types are compatible.




Thanks in advance.


Babak


On Mon, Apr 25, 2016 at 10:24 AM, Adrian Klaver
> wrote:

On 04/25/2016 07:07 AM, Babak Alipour wrote:

Greetings everyone,

I'm a novice plpgsql user.
For an application, I'm trying to write a user-defined function that
takes a row of some table (let's say with k fields) and takes
another
row from another table (again with k fields); then calculate the
Euclidean, Manhattan or generally Minkowski distance (with some
p) and
then return an integer.
I've written this:

CREATE FUNCTION euclidean_distance(row1 table_train, row2
table_test,
OUT distance DOUBLE PRECISION) AS $$
DECLARE
tmp DOUBLE PRECISION;
BEGIN
FOR col IN SELECT column_name FROM information_schema.columns WHERE
table_name=table_train LOOP
   tmp := (row1.col - row2.col);
   distance += tmp*tmp;
END LOOP;
distance := sqrt(distance);
END;
$$ LANGUAGE plpgsql;

Could anyone please help me fix this function so that I can pass
any two
rows of two tables (with same number of columns) and have their
distance
returned.


You are already doing that, so do you mean any two rows of any two
tables?


Best regards,
Babak Alipour

--
*/Babak Alipour ,/*
*/University of Florida/*



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




--
*/Babak Alipour ,/*
*/University of Florida/*



--
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] Columnar store as default for PostgreSQL 10?

[Merlin Moncure]

On Mon, Apr 25, 2016 at 9:20 AM, Alvaro Herrera
 wrote:
> 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?
>
> This is a pretty interesting question.  I wrote an answer, then thought
> it would make a good blog post, so it's at
> http://blog.2ndquadrant.com/column-store-plans/
> I reproduce it below.
>
> Completely replacing the current row-based store wouldn't be a good
> idea: it has served us extremely well and I’m pretty sure that replacing
> it entirely with a columnar store would be disastrous performance-wise
> for OLTP use cases.
>
> That doesn't mean columnar stores are a bad idea in general -- because
> they aren't. They just have a more limited use case than "the whole
> database". For analytical queries on append-mostly data, a columnar
> store is a much more appropriate representation than the regular
> row-based store, but not all databases are analytical.
>
> However, in order to attain interesting performance gains you need to do
> a lot more than just change the underlying storage: you need to ensure
> that the rest of the system can take advantage of the changed
> representation, so that it can execute queries optimally; for instance,
> you may want aggregates that operate in a SIMD mode rather than
> one-value-at-a-time as it is today. This, in itself, is a large
> undertaking, and there are other challenges too.
>
> As it turns out, there's a team at 2ndQuadrant working precisely on
> these matters. We posted a patch last year, but it wasn’t terribly
> interesting -— it only made a single-digit percentage improvement in
> TPC-H scores; not enough to bother the development community with (it
> was a fairly invasive patch). We want more than that.
>
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.
>
> We don’t have a timescale yet. Stay tuned.

Please keep us posted.

merlin


-- 
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] psql color hostname prompt

[Francisco Olarte]

Hi:

On Mon, Apr 25, 2016 at 4:04 PM, Achilleas Mantzios
 wrote:
> Hello, have done that, looked really nice, but unfortunately this resulted
> in a lot of garbled output, in case of editing functions, huge queries, up
> arrows, etc...

Did you  use %[ %] to delimit non-printing sequences as Cal did? I've
tested his prompt ( substituting 'echo tarari' for his sh script ) and
it works fine ( at least in xfce4-terminal / ubuntu / 6 terminal lines
long input line ). I've seen a lot of color prompts worked by
forgetting them ( even when the doc for them specially speaks of color
changes ).
Frnacisco Olarte.


-- 
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] psql color hostname prompt

[hubert depesz lubaczewski]

On Mon, Apr 25, 2016 at 08:55:21AM -0500, Cal Heldenbrand wrote:
> The only outside tool it requires is lsof to determine the hostname of the
> remote socket.  Otherwise it uses plain stuff like awk / sec and bash tools.

Why would you need lsof to get hostname for remote connection, when you
can use %M ?

Best regards,

depesz



-- 
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] psql color hostname prompt

[Francisco Olarte]

Hi Cal:

On Mon, Apr 25, 2016 at 3:55 PM, Cal Heldenbrand  wrote:
> I whipped up a psqlrc and companion shell script to provide a colored prompt
> with the hostname of the machine you're connected to.  It works for both
> local sockets and remote connections too.

Color may be nice, but as previously pointed it can distort things.

> /usr/local/pgsql/etc/psqlrc
> ==
> -- PROMPT1 is the primary prompt
> \set PROMPT1
> '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%]
> %n@%/%R%#%x '

And you are exec'ing in every round ( not too big, but I come from a
time of slow forks).

I think psql ( at least in 9.3 didn't bother looking behind ) gives you enough:

cdrs=> \set PROMPT1 %M:%>-%x-%:AUTOCOMMIT:-:PROMPT1
db1:5432--on:-cdrs=> begin;
BEGIN
db1:5432-*-on:-cdrs=> commit;
COMMIT
db1:5432--on:-cdrs=> \c apc -
psql (9.4.7, server 9.3.10)
You are now connected to database "apc" as user "folarte".
db1:5432--on:-apc=>

And you can color it:

db1:5432--on:-apc=> \set PROMPT1 '%[%033[1;31m%]%M%[%033[0m%]%n@%/%R%#%x '
db1folarte@apc=> -- not a fan of html mail.

lsof may give you longer or more acurate names, but I think std
escapes are enough.

Francisco Olarte.


-- 
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?

[Bráulio Bhavamitra]

On Mon, Apr 25, 2016 at 11:20 AM Alvaro Herrera 
wrote:

> 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?
>
> This is a pretty interesting question.  I wrote an answer, then thought
> it would make a good blog post, so it's at
> http://blog.2ndquadrant.com/column-store-plans/
> I reproduce it below.
>
> Completely replacing the current row-based store wouldn't be a good
> idea: it has served us extremely well and I’m pretty sure that replacing
> it entirely with a columnar store would be disastrous performance-wise
> for OLTP use cases.
>
> That doesn't mean columnar stores are a bad idea in general -- because
> they aren't. They just have a more limited use case than "the whole
> database". For analytical queries on append-mostly data, a columnar
> store is a much more appropriate representation than the regular
> row-based store, but not all databases are analytical.
>
> However, in order to attain interesting performance gains you need to do
> a lot more than just change the underlying storage: you need to ensure
> that the rest of the system can take advantage of the changed
> representation, so that it can execute queries optimally; for instance,
> you may want aggregates that operate in a SIMD mode rather than
> one-value-at-a-time as it is today. This, in itself, is a large
> undertaking, and there are other challenges too.
>
> As it turns out, there's a team at 2ndQuadrant working precisely on
> these matters. We posted a patch last year, but it wasn’t terribly
> interesting -— it only made a single-digit percentage improvement in
> TPC-H scores; not enough to bother the development community with (it
> was a fairly invasive patch). We want more than that.
>
> In our design, columnar or not is going to be an option: you're going to
> be able to say "Dear server, for this table kindly set up columnar
> storage for me, would you? Thank you very much." And then you’re going
> to get a table which may be slower for regular usage but which will rock
> for analytics. For most of your tables the current row-based store will
> still likely be the best option, because row-based storage is much
> better suited to the more general cases.
>
Nice Alvaro, I think that's the right approach.

Wish a good work for you on that :)

cheers,
bráulio

>
> We don’t have a timescale yet. Stay tuned.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [GENERAL] Calculating Minkowski distance between two rows

[Babak Alipour]

That is correct. The function I've written only works when the two tables
are named table_train and table_test; is it possible to generalize that to
take in any two tables?

Thanks in advance.

>Babak

On Mon, Apr 25, 2016 at 10:24 AM, Adrian Klaver 
wrote:

> On 04/25/2016 07:07 AM, Babak Alipour wrote:
>
>> Greetings everyone,
>>
>> I'm a novice plpgsql user.
>> For an application, I'm trying to write a user-defined function that
>> takes a row of some table (let's say with k fields) and takes another
>> row from another table (again with k fields); then calculate the
>> Euclidean, Manhattan or generally Minkowski distance (with some p) and
>> then return an integer.
>> I've written this:
>>
>> CREATE FUNCTION euclidean_distance(row1 table_train, row2 table_test,
>> OUT distance DOUBLE PRECISION) AS $$
>> DECLARE
>> tmp DOUBLE PRECISION;
>> BEGIN
>> FOR col IN SELECT column_name FROM information_schema.columns WHERE
>> table_name=table_train LOOP
>>tmp := (row1.col - row2.col);
>>distance += tmp*tmp;
>> END LOOP;
>> distance := sqrt(distance);
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> Could anyone please help me fix this function so that I can pass any two
>> rows of two tables (with same number of columns) and have their distance
>> returned.
>>
>
> You are already doing that, so do you mean any two rows of any two tables?
>
>
>> Best regards,
>> Babak Alipour
>>
>> --
>> */Babak Alipour ,/*
>> */University of Florida/*
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
*Babak Alipour ,*
*University of Florida*

Re: [GENERAL] Calculating Minkowski distance between two rows

[Adrian Klaver]

On 04/25/2016 07:07 AM, Babak Alipour wrote:

Greetings everyone,

I'm a novice plpgsql user.
For an application, I'm trying to write a user-defined function that
takes a row of some table (let's say with k fields) and takes another
row from another table (again with k fields); then calculate the
Euclidean, Manhattan or generally Minkowski distance (with some p) and
then return an integer.
I've written this:

CREATE FUNCTION euclidean_distance(row1 table_train, row2 table_test,
OUT distance DOUBLE PRECISION) AS $$
DECLARE
tmp DOUBLE PRECISION;
BEGIN
FOR col IN SELECT column_name FROM information_schema.columns WHERE
table_name=table_train LOOP
   tmp := (row1.col - row2.col);
   distance += tmp*tmp;
END LOOP;
distance := sqrt(distance);
END;
$$ LANGUAGE plpgsql;

Could anyone please help me fix this function so that I can pass any two
rows of two tables (with same number of columns) and have their distance
returned.


You are already doing that, so do you mean any two rows of any two tables?



Best regards,
Babak Alipour

--
*/Babak Alipour ,/*
*/University of Florida/*



--
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] Columnar store as default for PostgreSQL 10?

[Alvaro Herrera]

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?

This is a pretty interesting question.  I wrote an answer, then thought
it would make a good blog post, so it's at
http://blog.2ndquadrant.com/column-store-plans/
I reproduce it below.

Completely replacing the current row-based store wouldn't be a good
idea: it has served us extremely well and I’m pretty sure that replacing
it entirely with a columnar store would be disastrous performance-wise
for OLTP use cases.

That doesn't mean columnar stores are a bad idea in general -- because
they aren't. They just have a more limited use case than "the whole
database". For analytical queries on append-mostly data, a columnar
store is a much more appropriate representation than the regular
row-based store, but not all databases are analytical.

However, in order to attain interesting performance gains you need to do
a lot more than just change the underlying storage: you need to ensure
that the rest of the system can take advantage of the changed
representation, so that it can execute queries optimally; for instance,
you may want aggregates that operate in a SIMD mode rather than
one-value-at-a-time as it is today. This, in itself, is a large
undertaking, and there are other challenges too.

As it turns out, there's a team at 2ndQuadrant working precisely on
these matters. We posted a patch last year, but it wasn’t terribly
interesting -— it only made a single-digit percentage improvement in
TPC-H scores; not enough to bother the development community with (it
was a fairly invasive patch). We want more than that.

In our design, columnar or not is going to be an option: you're going to
be able to say "Dear server, for this table kindly set up columnar
storage for me, would you? Thank you very much." And then you’re going
to get a table which may be slower for regular usage but which will rock
for analytics. For most of your tables the current row-based store will
still likely be the best option, because row-based storage is much
better suited to the more general cases.

We don’t have a timescale yet. Stay tuned. 

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

[GENERAL] Calculating Minkowski distance between two rows

[Babak Alipour]

Greetings everyone,

I'm a novice plpgsql user.
For an application, I'm trying to write a user-defined function that takes
a row of some table (let's say with k fields) and takes another row from
another table (again with k fields); then calculate the Euclidean,
Manhattan or generally Minkowski distance (with some p) and then return an
integer.
I've written this:

CREATE FUNCTION euclidean_distance(row1 table_train, row2 table_test, OUT
distance DOUBLE PRECISION) AS $$
DECLARE
tmp DOUBLE PRECISION;
BEGIN
FOR col IN SELECT column_name FROM information_schema.columns WHERE
table_name=table_train LOOP
   tmp := (row1.col - row2.col);
   distance += tmp*tmp;
END LOOP;
distance := sqrt(distance);
END;
$$ LANGUAGE plpgsql;

Could anyone please help me fix this function so that I can pass any two
rows of two tables (with same number of columns) and have their distance
returned.

Best regards,
Babak Alipour

-- 
*Babak Alipour ,*
*University of Florida*

Re: [GENERAL] psql color hostname prompt

[Achilleas Mantzios]

On 25/04/2016 16:55, Cal Heldenbrand wrote:

Hi everyone,

The default psql prompt can be a little frustrating when managing many hosts.  
Typing the wrong command on the wrong host can ruin your day.  ;-)

I whipped up a psqlrc and companion shell script to provide a colored prompt 
with the hostname of the machine you're connected to.  It works for both local 
sockets and remote connections too.

The only outside tool it requires is lsof to determine the hostname of the 
remote socket.  Otherwise it uses plain stuff like awk / sec and bash tools.

If everyone gives this a thumbs up, I'd like to submit this for inclusion in 
the official postgres source.  (Maybe as an additional 
psqlrc.sample.color_hostname file or similar)


Hello, have done that, looked really nice, but unfortunately this resulted in a 
lot of garbled output, in case of editing functions, huge queries, up arrows, 
etc...
You might want to test with those before submitting.



Inline paste of the two files below.  Replace the paths with your environment:

/usr/local/pgsql/etc/psqlrc
==
-- PROMPT1 is the primary prompt
\set PROMPT1 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%] 
%n@%/%R%#%x '

-- PROMPT2 is the secondary (query continue) prompt
\set PROMPT2 '%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`[%033[0m%] 
%n@%/%R %# '
==

/usr/local/pgsql/etc/psql_hostname.sh
==
#!/bin/bash
# Intelligently return local hostname, or remote server connection

# - list file descriptors of my parent PID (psql command)
# - include only FD #3, which is the postgres socket
# - print the NAME column
name=$(/usr/sbin/lsof -p $PPID -a -d 3 | tail -1 | awk '{print $9}')

if [[ "$name" == "socket" ]]; then
  # We're on the local socket
  hostname -f
else
  # Cut out the destination machine from the socket pair
  echo $( sed 's/.*->\(.*\):postgres/\1/' <<< $name )
fi
==

Thank you!

---
Cal Heldenbrand
   Web Operations at FBS
   Creators of flexmls ® and Spark Platform 

c...@fbsdata.com 



--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

[GENERAL] psql color hostname prompt

[Cal Heldenbrand]

Hi everyone,

The default psql prompt can be a little frustrating when managing many
hosts.  Typing the wrong command on the wrong host can ruin your day.  ;-)

I whipped up a psqlrc and companion shell script to provide a colored
prompt with the hostname of the machine you're connected to.  It works for
both local sockets and remote connections too.

The only outside tool it requires is lsof to determine the hostname of the
remote socket.  Otherwise it uses plain stuff like awk / sec and bash tools.

If everyone gives this a thumbs up, I'd like to submit this for inclusion
in the official postgres source.  (Maybe as an additional
psqlrc.sample.color_hostname file or similar)

Inline paste of the two files below.  Replace the paths with your
environment:

/usr/local/pgsql/etc/psqlrc
==
-- PROMPT1 is the primary prompt
\set PROMPT1
'%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`%[%033[0m%] %n@%/%R%#%x
'

-- PROMPT2 is the secondary (query continue) prompt
\set PROMPT2
'%[%033[1;31m%]%`/usr/local/pgsql/etc/psql_hostname.sh`[%033[0m%] %n@%/%R
%# '
==

/usr/local/pgsql/etc/psql_hostname.sh
==
#!/bin/bash
# Intelligently return local hostname, or remote server connection

# - list file descriptors of my parent PID (psql command)
# - include only FD #3, which is the postgres socket
# - print the NAME column
name=$(/usr/sbin/lsof -p $PPID -a -d 3 | tail -1 | awk '{print $9}')

if [[ "$name" == "socket" ]]; then
  # We're on the local socket
  hostname -f
else
  # Cut out the destination machine from the socket pair
  echo $( sed 's/.*->\(.*\):postgres/\1/' <<< $name )
fi
==

Thank you!

---
Cal Heldenbrand
   Web Operations at FBS
   Creators of flexmls ® and Spark Platform

   c...@fbsdata.com

[GENERAL] Types of blocks in the `pg_stat_statements`

[Victor Yegorov]

Greetings.

I cannot get understanding about what different types of blocks means here:
http://www.postgresql.org/docs/current/interactive/pgstatstatements.html


I assume, that `shared` are blocks from the `shared_buffers`
and `temp` are blocks used by temporary files (absence of `dirtied` and
`hit` also speaks for this).

But what `local` blocks mean?


-- 
Victor Y. Yegorov

Re: [GENERAL] Background worker plus language handler for Andl: OK?

[david]

> ow...@postgresql.org] On Behalf Of David Wilson

> I've been reading your posts over the past few days and while I find it
fun
> to follow, I can't help but wonder why there there is urgency in
> reimplementing a protocol within PG itself.

I think it's an interesting problem -- glad you find it so.

No, I don't plan to implement any more protocols. The problem here is
callbacks, and probably transaction boundaries.

Andl is designed to be a relational language filling a similar niche to SQL
with PLSQL or SQL/PSM. It contains a full implementation of the relational
algebra, but is also a general purpose programming language. [The code is
all compiled and the front end is RPC, nothing like libpq or ODBC.]

A query is a relational expression and may evaluate arbitrary expressions.
Example:

// Q8. Get all shipments where the quantity is in the range 300 to 750
inclusive.
// SQL> select spj.* from spj where spj.QTY>=300 and spj.QTY<=750; 
Andl: SPJ .where(QTY>=300 and QTY<=750)

The JOIN can be generated as SQL but the where predicate requires a callback
into the Andl runtime. I would be quite happy to run queries through libql,
but I can see no way to handle callbacks without running in-process.

[Yes, in some cases the query planner will replace this by an operation on
an index, but this is about the general case.]

I have it working as a PL extension, but then the entire query has to be
embedded inside a PL function call which is (a) messy (b) cannot manage
transaction boundaries.

> It seems to me this is a much larger undertaking than you realize, for
> example, you would at least need to reinvent PG's existing authentication
and
> authorization mechanisms, or perhaps patch PG somehow to expose them
usefully
> to your own code.

It's a useful point, but I'm not sure it applies. Andl is not intended to
have an SQL-like execution or security model. Perhaps this is something that
needs some more thought, but it's unlikely to be a critical factor.

> Is there a hard requirement that this stuff be in-process? Most of the
cost
> of a SQL query will be lost in planning and execution, the actual time
spent
> copying some strings around and context switching will be pretty minimal
for
> a query of any significance.

See above. The cost of setting up the query is trivial compared to the cost
of a callback every time an expression is evaluated, perhaps many times per
row.
> 
> If I were you I'd start with building a robust proxy server first, serving
up
> your custom protocol and rewriting it to a PG client connection
internally,
> and only then look at how that might be merged in-proess if indeed there
was
> a real need for it.

If there really is another way to go, I'm happy to hear about it. I think
this is not a job for a proxy server -- the external interface for Andl is
RPC, not shipping query text around. That's all working -- it's the backend
I need.


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] Background worker plus language handler for Andl: OK?

[David Wilson]

On Mon, Apr 25, 2016 at 03:18:30PM +1000, da...@andl.org wrote:

> It seems to me that it should be possible to create a language front
> end for Andl by creating a background worker and a language handler.

I've been reading your posts over the past few days and while I find it
fun to follow, I can't help but wonder why there there is urgency in
reimplementing a protocol within PG itself.

It seems to me this is a much larger undertaking than you realize, for
example, you would at least need to reinvent PG's existing
authentication and authorization mechanisms, or perhaps patch PG somehow
to expose them usefully to your own code.

Is there a hard requirement that this stuff be in-process? Most of the
cost of a SQL query will be lost in planning and execution, the actual
time spent copying some strings around and context switching will be
pretty minimal for a query of any significance.

If I were you I'd start with building a robust proxy server first,
serving up your custom protocol and rewriting it to a PG client
connection internally, and only then look at how that might be merged
in-proess if indeed there was a real need for it.


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] Error: no connection to the server

[Albe Laurenz]

Marco Bambini wrote:
> I have a multithreaded C client and sometimes I receive the "no connection to 
> the server" error
> message.
> I haven't found any documentation about it and about how to fix this issue.
> 
> Anyone can point me to the right direction?

The error message is reported in interfaces/libpq/fe-exec.c:

static bool
PQsendQueryStart(PGconn *conn)
{
if (!conn)
return false;

/* clear the error string */
resetPQExpBuffer(>errorMessage);

/* Don't try to send if we know there's no live connection. */
if (conn->status != CONNECTION_OK)
{
printfPQExpBuffer(>errorMessage,
  libpq_gettext("no connection to the server\n"));
return false;
}

So it looks like you closed the connection (maybe in some other thread)
and then try to reuse it.

Yours,
Laurenz Albe

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

[GENERAL] Error: no connection to the server

[Marco Bambini]

Hi,
I have a multithreaded C client and sometimes I receive the "no connection to 
the server" error message.
I haven't found any documentation about it and about how to fix this issue.

Anyone can point me to the right direction?
How can I fix it?

Thanks a lot.
--
Marco Bambini

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